Oracle Dataguard Scripts
4 min read1) Information of database (Primary & Standby).
SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;
2) To display current status for Physical Standby Database background processes.
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;
3) Check for messages/errors
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;
4) Show received archived logs on physical standby
SQL> select registrar, creator, thread#, sequence#, first_change#, next_change# from v$archived_log;
5) To check the log status
select 'Last Log applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log where applied='YES') union select 'Last Log received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log);
6) To display various information about the redo data.
SQL> select name, value from V$DATAGUARD_STATS;
7) Verify the last sequence# received and the last sequence# applied to standby database.
SQL> 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;
8) To find last applied log
SQL> select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';
9) Enter the following command on the standby node to determine if you are using managed or real time apply
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;
10) Enter the following command on the standby node to start real time apply:
Note:
This requires standby redo log files.
SQL> alter database recover managed standby database using current logfile disconnect;
11) Enter the following command on the standby node to start managed recovery
SQL> alter database recover managed standby database disconnect;
12) Enter the following command on the standby node to stop the standby Data Guard Apply process
SQL> alter database recover managed standby database cancel;
13) Enter the following command on the standby node to determine the Data Guard transport and apply lag
SQL> set lines 160 pages 200
col NAME format
a25 justify c heading 'Name'
col VALUE format
a20 justify c heading 'Value'
select * from v$dataguard_stats;
14) Enter the following command on the standby node to determine if the managed recovery process is applying logs
SQL> select process, status,client_process, thread#, sequence#, block#, blocks from v$managed_standby where process='MRP0';
15) Enter the following command on the standby node to determine the checkpoint time (or age) of standby data files
SQL> select status, to_char(checkpoint_change#) CHK_NO,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time, count(*)
from v$datafile_header
group by status, checkpoint_change#, checkpoint_time
order by status, checkpoint_change#, checkpoint_time;
16) Enter the following commands to determine if the standby database is in sync with source:
On the source node as the oracle user, check the current sequence
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
17) On the standby as the oracle user, check the last sequence applied:
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
Note: If the source database output and standby database output are the same, the standby database is in sync with the source database.
Helpful Commands
The queries in this section may help determine how long a standby database can remain in an open snapshot standby state before the Automated Storage Management (ASM) disk group space fills with archive logs.
On the source node as the oracle user, query the daily archive log generation:
SQL> select trunc(COMPLETION_TIME,'DD') Day, thread#,
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives_Generated from v$archived_log
where CREATOR='ARCH' and STANDBY_DEST='NO'
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;
On the source node as the oracle user, query the hourly archive log generation:
SQL> select trunc(COMPLETION_TIME,'HH') Hour,thread# ,
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives from v$archived_log
where CREATOR='ARCH' and STANDBY_DEST='NO'
group by trunc(COMPLETION_TIME,'HH'),thread# order by 1 ;
Troubleshooting Commands
The queries in this section can assist with troubleshooting:
Enter the following command on the standby node to identify fatal and error messages being reported for the archive destination:
SQL> select message, to_char(timestamp,'HH:MI:SS') timestamp
from v$dataguard_status
where severity in ('Error','Fatal')
order by timestamp;
Enter the following command on all source nodes to identify any errors being reported for the archive destination
SQL> select dest_name, status, error from v$archive_dest;
Enter the following command on all nodes to identify latest activity
SQL> select timestamp, message from v$dataguard_status;
Enter the following command on the standby to view archiver, RFS, and MRP processes and if they are currently being used
SQL> select process, status, sequence#, block#, blocks, delay_mins from v$managed_standby;
Enter the following command on the standby to identify what time the archive logs are stamped with
set lines 200 pages 2000
col name for a45
SELECT sequence#,
first_time,
substr(name,instr(name,'/',-1)+1) name,
aldly delay
FROM v$archived_log, x$kccal
WHERE recid = alrid
AND first_time > sysdate - 90/1440
ORDER BY first_time;
