Oracle Standby Important queries

on

 Sometimes we would be working in the Oracle DR environment and will be in a situation where we need to search different documents for each query. To avoid those problems. I have put all the required queries in one blog post. In case if have missed please add it in the comment section.

To check whether archive shipping is enabled in Primary we can simply issue "show parameter log_archive_dest" but the problem here is that we won't be able to find whether the connection between primary and standby is valid.

To check it use the below query to check whether the connection between Primary and Standby is Valid.

set pagesize 300
set lines 300
col DESTINATION for a45
col ERROR for a10
SELECT inst_id,DESTINATION, STATUS, ERROR FROM gV$ARCHIVE_DEST WHERE DEST_ID=2;
Output: 

  INST_ID  DESTINATION                                   STATUS    ERROR
---------- --------------------------------------------- --------- ----------
         1 ORACLEDBAARENA                                        VALID

Once you start the archive shipping and starting MRP process in standby. We will be monitoring the sync using the alert log. Instead, use the below query to get the number of blocks copied and the total block of the particular log.

SELECT
   PROCESS,
   STATUS,
   THREAD#,
   SEQUENCE#,
   BLOCK#,
   BLOCKS 
FROM
   V$MANAGED_STANDBY;

Output: 


PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1      17820     116000          3
MRP0      APPLYING_LOG          1      17820     116002    1024000
To find the standby redo logs in the standby server. We can use any of the below queries.
 
Query 1: 
set lines 999
col MEMBER for a70
SELECT * FROM V$LOGFILE WHERE TYPE = 'STANDBY';

Query 2: 

SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS 
  FROM V$STANDBY_LOG;

To find the log gap between the primary and standby we can use the below query.
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;

The problem with the above query is that it only shows the difference of archive received and applied. What if we didn't receive the archive itself. In that case please use the script mentioned here which will give the archive received and applied with the time when was the last archive received and applied.

So far we have seen the select queries which we use for the DR environment. 

Stop Archive Shipping 

alter system set log_archive_dest_state_2 = 'defer';


Start Archive Shipping 

alter system set log_archive_dest_state_2 = 'ENABLE';


Stop MRP 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


Start MRP with Real-time Apply 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;


Start MRP

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;




Comments

Post a Comment