Data Guard Scripts

Index

  • Starting up physical standby
  • Shutting down physical standby
  • Opening Real-Time Apply on physical standby
  • Check database status
  • Check Fast-Start Failover status
  • Check Redo Apply and Redo Transport status on physical standby
  • Show received archived logs on physical standby
  • Show applied archived logs on physical standby
  • Show archived log destination status +++
  • Check transport Lag +++
  • Show message
  • Is physical standby currently performing managed recovery?
  • Check level of synchronization

Starting up physical standby (no Active Data Guard)


1) Start physical standby database to mount state

sys@phystdby> startup mount;

2) Start Media Recovery Process on physical standby:

sys@phystdby> alter database recover managed standby database using current logfile disconnect;

3) Ensure the LOG_ARCHIVE_DEST_n assigned to physical standby on primary database is enabled

sys@primary>select value from v$parameter where name = 'log_archive_dest_state_2';

VALUE
----------------------------------------------------------------------------------------------------
ENABLE

shutting down physical standby


1) Defer the physical standby destination on primary database (e.g. LOG_ARCHIVE_DEST_2 is assigned to physical standby)

sys@primary> alter system set LOG_ARCHIVE_DEST_STATE_2='DEFER';

2) perform a log switch nn primary database

sys@primary> alter system switch logfile;

3) Stop the redo apply service on physical standby

sys@phystdby> alter database recover managed standby database cancel;

4) Shutdown physical standby database

sys@phystdby> shutdown immediate;

Opening Real-Time Apply on physical standby (11g)


1) Initialized parameter COMPATIBLE must be 11.0 and upper

sys@phystdby> select value from v$parameter where name = 'compatible';

VALUE
-------------
11.2.0.0.0

2) Stop Redo Apply if it's active

sys@phystdby> alter database recover managed standby cancel;

3) Open physical standby read only

sys@phystdby> alter database open;

4) Restart Redo Apply

sys@phystdby> alter database recover managed standby using current logfile disconnect;

5) Check open mode

sys@phystdby> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Check database status

The following query displays the data protection mode, data protection level, database role, and switchover status for a primary, physical standby or snapshot standby database


SELECT PROTECTION_MODE,
       PROTECTION_LEVEL,
       DATABASE_ROLE ROLE,
       SWITCHOVER_STATUS
FROM V$DATABASE
/

PROTECTION_MODE      PROTECTION_LEVEL     ROLE             SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PRIMARY          TO STANDBY

or

PROTECTION_MODE      PROTECTION_LEVEL     ROLE             SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

or

PROTECTION_MODE      PROTECTION_LEVEL     ROLE             SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  LOGICAL STANDBY  NOT ALLOWED

Check Fast-Start Failover status

SELECT FS_FAILOVER_STATUS "FSFO STATUS",
 FS_FAILOVER_CURRENT_TARGET TARGET,
 FS_FAILOVER_THRESHOLD THRESHOLD,
 FS_FAILOVER_OBSERVER_PRESENT "OBSERVER PRESENT"
FROM V$DATABASE
/

Check Redo Apply and Redo Transport status on physical standby


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

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING               1        244          1          3
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1        245          1          5
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       OPENING               1        246          0          0
MRP0      APPLYING_LOG          1        245          5     102400

This output shows that RFS is receiving archived log #246, while Redo Apply is busy with applying archived log #245, block# 5 of total 102400 blocks

Show received archived logs on physical standby

Run this query on physical standby

select registrar, creator, thread#, sequence#, first_change#, next_change# from v$archived_log;


Show applied archived logs on physical standby

Run this query on physical standby

select thread#, sequence#, first_change#, next_change# from v$log_history;

Note that v$log_history shows applied archived logs registered in control files. Limited by control file size, it may list only latest applied archived logs.

Show archived log destination status +++

-- on primary databaes

select dest_id,dest_name,status,database_mode, error from  v$archive_dest_status;

DEST_ID DEST_NAME                      STATUS    DATABASE_MODE   ERROR
---------- ------------------------------ --------- --------------- -----------------------------------------------------------------
 1 LOG_ARCHIVE_DEST_1             VALID     OPEN
 2 LOG_ARCHIVE_DEST_2             DEFERRED     OPEN_READ-ONLY
 3 LOG_ARCHIVE_DEST_3             VALID     OPEN
 4 LOG_ARCHIVE_DEST_4             INACTIVE  UNKNOWN
 5 LOG_ARCHIVE_DEST_5             INACTIVE  UNKNOWN
 6 LOG_ARCHIVE_DEST_6             INACTIVE  UNKNOWN

-- on standby database
SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG
where applied='NO';

THREAD#  SEQUENCE# APPLIED
---------- ---------- ---------
 1        235 NO
 1        236 NO
 1        236 NO
 1        237 NO
 1        237 NO

-- on standby database
select SEQUENCE# ,REGISTRAR,ARCHIVED,APPLIED ,status from v$archived_log
where sequence#>(select max(sequence#)-20 from v$log_history);

 SEQUENCE# REGISTR ARC APPLIED   S
---------- ------- --- --------- -
 235 LGWR    YES YES       A
 235 LGWR    YES NO        A
 235 ARCH    YES YES       A
 236 FGRD    YES YES       A
 236 FGRD    YES NO        A
 236 FGRD    YES NO        A
 237 RFS     YES YES       A
 237 FGRD    YES NO        A
 237 FGRD    YES NO        A
 240 RFS     YES YES       A
 238 RFS     YES YES       A
 239 RFS     YES YES       A
 241 RFS     YES YES       A
 242 RFS     YES YES       A
 243 RFS     YES YES       A
 244 RFS     YES YES       A
 245 RFS     YES YES       A
 246 RFS     YES YES       A
 247 RFS     YES YES       A
 248 RFS     YES YES       A
 249 RFS     YES YES       A
 250 RFS     YES YES       A
 251 RFS     YES YES       A
 252 RFS     YES YES       A
 253 RFS     YES YES       A
 254 RFS     YES YES       A

 THREAD#  SEQUENCE# APPLIED
---------- ---------- ---------
 1        235 NO
 1        236 NO
 1        236 NO
 1        237 NO
 1        237 NO

Check transport Lag +++

Run this script on primary database

SELECT (SELECT name FROM V$DATABASE),
 (SELECT MAX (sequence#)
 FROM v$archived_log
 WHERE dest_id = 1)
 Current_primary_seq,
 (SELECT MAX (sequence#)
 FROM v$archived_log
 WHERE trunc(next_time) > SYSDATE - 1
 AND dest_id = 2)
 max_stby,
 (SELECT NVL (
 (SELECT MAX (sequence#) - MIN (sequence#)
 FROM v$archived_log
 WHERE     trunc(next_time) > SYSDATE - 1
 AND dest_id = 2
 AND applied = 'NO'),
 0)
 FROM DUAL)
 "To be applied",
 ( (SELECT MAX (sequence#)
 FROM v$archived_log
 WHERE dest_id = 1)
 - (SELECT MAX (sequence#)
 FROM v$archived_log
 WHERE dest_id = 2))
 "To be Shipped"
 FROM DUAL;

NAME        CURRENT_PRIMARY_SEQ   MAX_STBY     To be applied  To be Shipped
----------- -------------------- ------------- -------------  --------------
DB11R2      252                   251                     1               1

Show message

Run this query on primary database.


select message, timestamp from v$dataguard_status where dest_id = 2

MESSAGE                                                                                                                  TIMESTAMP
------------------------------------------------------------------------------------------------------------------------ -------------------
ARC0: Standby redo logfile selected for thread 1 sequence 238 for destination LOG_ARCHIVE_DEST_3                         2011-02-20 23:21:49
FAL[server, ARC3]: Error 12537 creating remote archivelog file 'db11r2dr'                                                2011-02-20 23:21:52
LNS: Standby redo logfile selected for thread 1 sequence 239 for destination LOG_ARCHIVE_DEST_3                          2011-02-20 23:21:54
LNS: Standby redo logfile selected for thread 1 sequence 240 for destination LOG_ARCHIVE_DEST_3                          2011-02-20 23:21:55
FAL[server, ARC0]: Error 12537 creating remote archivelog file 'db11r2dr'                                                2011-02-20 23:21:58
Error 12537 for archive log file 3 to 'db11r2dr'                                                                         2011-02-20 23:21:58
PING[ARC2]: Heartbeat failed to connect to standby 'db11r2dr'. Error is 1034.                                            2011-02-20 23:22:00
LNS: Standby redo logfile selected for thread 1 sequence 241 for destination LOG_ARCHIVE_DEST_3                          2011-02-20 23:22:03
Error 1033 for archive log file 3 to 'db11r2dr'                                                                          2011-02-20 23:22:04
PING[ARC2]: Heartbeat failed to connect to standby 'db11r2dr'. Error is 1033.                                            2011-02-20 23:22:11
LNS: Standby redo logfile selected for thread 1 sequence 242 for destination LOG_ARCHIVE_DEST_3                          2011-02-20 23:22:32
LNS: Standby redo logfile selected for thread 1 sequence 243 for destination LOG_ARCHIVE_DEST_3                          2011-02-20 23:23:33
LNS: Standby redo logfile selected for thread 1 sequence 244 for destination LOG_ARCHIVE_DEST_3                          2011-02-20 23:58:13
LNS: Standby redo logfile selected for thread 1 sequence 245 for destination LOG_ARCHIVE_DEST_3                          2011-02-20 23:58:14
LNS: Standby redo logfile selected for thread 1 sequence 246 for destination LOG_ARCHIVE_DEST_3                          2011-02-20 23:58:16
LNS: Standby redo logfile selected for thread 1 sequence 247 for destination LOG_ARCHIVE_DEST_3                          2011-02-21 12:01:01
LNS: Standby redo logfile selected for thread 1 sequence 248 for destination LOG_ARCHIVE_DEST_3                          2011-02-21 12:07:11
LNS: Standby redo logfile selected for thread 1 sequence 249 for destination LOG_ARCHIVE_DEST_3                          2011-02-21 12:08:27
LNS: Standby redo logfile selected for thread 1 sequence 250 for destination LOG_ARCHIVE_DEST_3                          2011-02-21 12:09:08
LNS: Standby redo logfile selected for thread 1 sequence 251 for destination LOG_ARCHIVE_DEST_3                          2011-02-21 12:24:52
LNS: Standby redo logfile selected for thread 1 sequence 252 for destination LOG_ARCHIVE_DEST_3                          2011-02-21 12:31:44
LNS: Standby redo logfile selected for thread 1 sequence 253 for destination LOG_ARCHIVE_DEST_3                          2011-02-21 12:32:02

Is physical standby currently performing managed recovery?

<pre>select process, status from <a href="http://www.adp-gmbh.ch/ora/misc/dynamic_performance_views.html#managed_standby">v$managed_standby</a>;
</pre>

If there is a MRP or a MRP0 process, the database is performing managed recovery.

Check level of synchronization

select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: