Dear Readers,
In this article, we will see the Oracle : Important Commands for Dataguard (SecureCRT)
Menubar Name : Dataguard
Tab Name : Instance_info
|
1 2 3 4 5 6 7 |
set linesize 300 col machine for a60 select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance; NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ORCLTEST - ORCLTEST1@SERVER1234.sephoraus.com - MSTORCLTEST - 12.1.0.2.0 - READ ONLY WITH APPLY - 04-FEB-2021 00:59:59 - PHYSICAL STANDBY |
Tab Name : DG_GAP
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; select thread#,applied,max(sequence#) from gv$archived_log where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#,applied order by thread#,applied; Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 44791 44791 0 2 42354 42354 0 3 40223 40223 0 SQL> THREAD# APPLIED MAX(SEQUENCE#) ---------- --------- -------------- 1 IN-MEMORY 44791 1 NO 39800 1 YES 44790 2 NO 38782 2 YES 42354 3 NO 38673 3 YES 40223 |
Tab Name : ERR_DEST
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
set linesize 300 col dest_name for a30 col error for a70 select dest_id,dest_name,status,error from v$archive_dest where dest_id in (1,2,3,4); select inst_id,dest_name,status,error from gv$archive_dest where dest_id in (1,2,3,4) and status='VALID' order by 1; DEST_ID DEST_NAME STATUS ERROR ---------- ------------------------------ --------- -------------- 1 LOG_ARCHIVE_DEST_1 VALID 2 LOG_ARCHIVE_DEST_2 INACTIVE 3 LOG_ARCHIVE_DEST_3 INACTIVE 4 LOG_ARCHIVE_DEST_4 DEFERRED SQL> INST_ID DEST_NAME STATUS ERROR ---------- ------------------------------ --------- -------------- 1 LOG_ARCHIVE_DEST_1 VALID |
Tab Name : Date Format
|
1 |
alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS'; |
Tab Name : Checkpoint_time
|
1 2 3 4 5 |
select distinct to_char(checkpoint_time,'DD-MON-YYYY HH24:MI:SS') from v$datafile; TO_CHAR(CHECKPOINT_TIME,'DD-M ----------------------------- 02-MAR-2021 20:04:12 |
Tab Name : Cancel MRP
|
1 2 |
--are you sure you want to cancel recovery? then copy and paste below line with semicolon(;) --recover managed standby database cancel |
Tab Name : Start MRP
|
1 2 3 |
-- are you sure you want to start recovery? if yes please copy and paste below line with semicolon(;) --recover managed standby database disconnect using current logfile --ALTER DATABASE RECOVER managed standby database disconnect from session |
Tab Name : Check MRP
|
1 2 3 4 5 |
select process, thread#, sequence#, status,blocks,block# from gv$managed_standby where process='MRP0'; PROCESS THREAD# SEQUENCE# STATUS BLOCKS BLOCK# --------- ---------- ---------- ------------ ---------- ---------- MRP0 5 4995 APPLYING_LOG 2097152 27883 |
Tab Name : check_apply_redo_rate
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
set linesize 500 col item format a40 col type format a20 col total noprint col units format a20 col timestamp noprint col apply_rate_in_min 999.99 col "app_rt|red_sz_mb" format a20 select start_time , type ,item , units , sofar , total,timestamp , (select round( a.sofar/b.sofar /60 ,2)||' '||round(c.sofar/b.sofar) from v$recovery_progress a , v$recovery_progress b , v$recovery_progress c where a.item='Elapsed Time' and b.item='Log Files' and c.item='Redo Applied' and a.start_time = b.start_time and a.start_time = c.start_time and d.start_time = c.start_time) "app_rt|red_sz_mb" from v$recoverY_progress d; START_TIME TYPE ITEM UNITS SOFAR app_rt|red_sz_mb ------------------- -------------------- ---------------------------------------- -------------------- ---------- -------------------- 02/04/2021 01:04:16 Media Recovery Log Files Files 17477 2.21 414 02/04/2021 01:04:16 Media Recovery Active Apply Rate KB/sec 2001 2.21 414 02/04/2021 01:04:16 Media Recovery Average Apply Rate KB/sec 3201 2.21 414 02/04/2021 01:04:16 Media Recovery Maximum Apply Rate KB/sec 43725 2.21 414 02/04/2021 01:04:16 Media Recovery Redo Applied Megabytes 7238673 2.21 414 02/04/2021 01:04:16 Media Recovery Last Applied Redo SCN+Time 0 2.21 414 02/04/2021 01:04:16 Media Recovery Active Time Seconds 2044133 2.21 414 02/04/2021 01:04:16 Media Recovery Apply Time per Log Seconds 116 2.21 414 02/04/2021 01:04:16 Media Recovery Checkpoint Time per Log Seconds 0 2.21 414 02/04/2021 01:04:16 Media Recovery Elapsed Time Seconds 2315117 2.21 414 02/04/2021 01:04:16 Media Recovery Standby Apply Lag Seconds 1 2.21 414 |
Tab Name : Standby_Log_Details
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
select distinct thread#,GROUP#,BYTES/1024/1024/1024,status from gv$standby_log order by 1,2; THREAD# GROUP# BYTES/1024/1024/1024 STATUS ---------- ---------- -------------------- ---------- 1 101 1 UNASSIGNED 1 102 1 ACTIVE 1 103 1 UNASSIGNED 1 104 1 UNASSIGNED 1 105 1 UNASSIGNED 1 106 1 UNASSIGNED 1 107 1 UNASSIGNED 2 201 1 UNASSIGNED 2 202 1 ACTIVE 2 203 1 UNASSIGNED 2 204 1 UNASSIGNED 2 205 1 UNASSIGNED 2 206 1 UNASSIGNED 2 207 1 UNASSIGNED |
Tab Name : Example : DEFER
|
1 |
--alter system set log_archive_dest_state_2=DEFER scope=BOTH sid='*'(semicolon;) |
Tab Name : Switchover_Status
|
1 2 3 4 5 |
select DB_UNIQUE_NAME,database_role,open_mode,switchover_status from v$database; DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS ------------------------------ ---------------- -------------------- -------------------- MSTORCLTEST PHYSICAL STANDBY READ ONLY WITH APPLY NOT ALLOWED |
Tab Name : GV$_inst_info
|
1 2 |
set linesize 300 select INST_ID,INSTANCE_NAME,host_name,to_char(startup_time,'DD-MON-YYYY HH24:MI:SS'),INSTANCE_ROLE from gv$instance order by inst_id; |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Note: Please test scripts in Non Prod before trying in Production.
Loading...
