Using Oracle Active Data Guard in 11g

Using Oracle Active Data Guard in 11g

SQL> select recovery_mode from v$archive_dest_status;

RECOVERY_MODE

-----------------------

IDLE

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL> select recovery_mode from v$archive_dest_status;

RECOVERY_MODE

-----------------------

MANAGED

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select recovery_mode from v$archive_dest_status;

RECOVERY_MODE

-----------------------

IDLE

Alert Log Contents

MRP0: Background Media Recovery cancelled with status 16037

Errors in file /u01/app/oracle/diag/rdbms/stdy/stdy/trace/stdy_pr00_18386.trc:

ORA-16037: user requested cancel of managed recovery operation

Recovery interrupted!

Waiting for MRP0 pid 18384 to terminate

Errors in file /u01/app/oracle/diag/rdbms/stdy/stdy/trace/stdy_pr00_18386.trc:

ORA-16037: user requested cancel of managed recovery operation

MRP0: Background Media Recovery process shutdown (stdy)

Managed Standby Recovery Canceled (stdy)

Completed: alter database recover managed standby database cancel

2012-03-03 16:40:11.937000 +05:30

SQL> alter database open read only;

Database altered.

Alert log

Physical standby database opened for read only access.

2012-03-03 16:41:17.796000 +05:30

Completed: alter database open read only

SQL> alter database recover managed standby database disconnect;

Database altered.

Alert log

alter database recover managed standby database disconnect

Attempt to start background Managed Standby Recovery process (stdy)

MRP0 started with pid=35, OS id=18613

MRP0: Background Managed Standby Recovery process started (stdy)

2012-03-03 16:42:01.136000 +05:30

 started logmerger process

Managed Standby Recovery not using Real Time Apply

Parallel Media Recovery started with 2 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Media Recovery Waiting for thread 1 sequence 31 (in transit)

Completed: alter database recover managed standby database disconnect

SQL> select recovery_mode from v$archive_dest_status;

RECOVERY_MODE

-----------------------

MANAGED

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLY WITH APPLY

SQL>

On Primary

SQL> alter system switch logfile;

System altered.

 SQL> select max(sequence#) from v$log;

MAX(SEQUENCE#)

--------------

            32

 

SQL> create table test (col1 number);

Table created.

SQL> desc test

 Name                                                                             Null?    Type

 -----------------------------------------------------------------------------------------------------------

 COL1                                                                              NUMBER

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL>

On Standby

SQL> select max(sequence#) from v$log;

MAX(SEQUENCE#)

--------------

            32

SQL>

 

SQL> desc test

 Name                                                                             Null?    Type

 -----------------------------------------------------------------------------------------------------------

 COL1                                                                              NUMBER

SQL>

 Alert log of Standby

Primary database is in MAXIMUM PERFORMANCE mode

Re-archiving standby log 4 thread 1 sequence 32

2012-03-03 16:56:45.733000 +05:30

Archived Log entry 4 added for thread 1 sequence 32 ID 0x4a53118e dest 1:

RFS[40]: Assigned to RFS process 19113

RFS[40]: Identified database type as 'physical standby': Client is ARCH pid 10428

2012-03-03 16:56:47.136000 +05:30

Media Recovery Log +FRA/stdy/archivelog/2012_03_03/thread_1_seq_32.266.776969805

2012-03-03 16:56:48.360000 +05:30

RFS[39]: Selected log 4 for thread 1 sequence 33 dbid 1246923666 branch 719142938

2012-03-03 16:56:51.337000 +05:30

Media Recovery Waiting for thread 1 sequence 33 (in transit)

2012-03-03 16:57:12.349000 +05:30

Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST

2012-03-03 16:57:51.429000 +05:30

RFS[41]: Assigned to RFS process 19150

RFS[41]: Identified database type as 'physical standby': Client is ARCH pid 10428

 

Real Time Apply

Now create standby redo files if not created (pre-req to enable "Real Time Apply")

SQL> select * from v$logfile;

Configure a Standby Redo Log On Standby

A Standby Redo log is added to enable Data Guard Maximum Availability and Maximum Protection modes. It is important to configure the Standby Redo Logs (SRL) on Primary and Standby Database with the same size as the online redo logs.

SQL> SELECT group#, member, status FROM V$LOGfile

SQL> ALTER DATABASE ADD STANDBY LOGFILE  size 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE  size 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE  size 50M;

Database altered.

SQL> SELECT group#, member, status FROM V$LOGfile;

SQL> select * from v$logfile

  where type='STANDBY';

Real Time Apply Enable

Now create standby redo files (pre-req to enable "Real Time Apply")

NOTE:

1)This should match exactly what was done on primary database (see above "Configure Standby Redo Logs")

2)This is optional in fact the first time around I forgot to create these logs on standby and recovery process was still working it just reports the following error: RFS[1]: Unable to open standby log 5: 313 and uses ARCHIVE logs instead

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

Alert log

alter database recover managed standby database using current logfile disconnect from session

Attempt to start background Managed Standby Recovery process (stdy)

MRP0 started with pid=32, OS id=19330

MRP0: Background Managed Standby Recovery process started (stdy)

2012-03-03 17:03:32.891000 +05:30

 started logmerger process

Managed Standby Recovery starting Real Time Apply

Parallel Media Recovery started with 2 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Completed: alter database recover managed standby database using current logfile disconnect from session

2012-03-03 17:03:33.912000 +05:30

Media Recovery Waiting for thread 1 sequence 33 (in transit)

Recovery of Online Redo Log: Thread 1 Group 4 Seq 33 Reading mem 0

  Mem# 0: +DATA/stdy/onlinelog/group_4.265.776967679

  Mem# 1: +FRA/stdy/onlinelog/group_4.259.776967685

2012-03-03 17:03:52.680000 +05:30

RFS[47]: Assigned to RFS process 19362

RFS[47]: Identified database type as 'physical standby': Client is ARCH pid 10428

Real Time Apply Disable

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Alert log

alter database recover managed standby database disconnect from session

Attempt to start background Managed Standby Recovery process (stdy)

MRP0 started with pid=32, OS id=19441

MRP0: Background Managed Standby Recovery process started (stdy)

2012-03-03 17:06:07.994000 +05:30

 started logmerger process

Managed Standby Recovery not using Real Time Apply

Drill to test Real-time apply:

SQL> select recovery_mode from v$archive_dest_status;

RECOVERY_MODE

-----------------------

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

MANAGED REAL TIME APPLY

SQL> alter database recover managed standby database cancel;

SQL> alter database recover managed standby database disconnect;

SQL> select recovery_mode from v$archive_dest_status;

RECOVERY_MODE

-----------------------

MANAGED

MANAGED

Switch logfiles on Primary

SQL> alter system switch logfile;

System altered

On Standby

SQL> select process,status,client_process,sequence#,block#,active_agents,known_agents

from v$managed_standby;

PROCESS   STATUS       CLIENT_P  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS

PROCESS   STATUS       CLIENT_P  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS

--------- ------------ -------- ---------- ---------- ------------- ------------

RFS       RECEIVING    N/A               0          0             0            0

MRP0      WAIT_FOR_LOG N/A              33          0             3            3

PROCESS   STATUS       CLIENT_P  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS

--------- ------------ -------- ---------- ---------- ------------- ------------

RFS       OPENING      LGWR             34          0             0            0

MRP0      APPLYING_LOG N/A              33      29753             3            3

8 rows selected.

SQL>

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY FIRST_TIME DESC;

SEQUENCE# FIRST_TIM NEXT_TIME

---------- --------- ---------

        34 03-MAR-12 03-MAR-12

        33 03-MAR-12 03-MAR-12

        32 03-MAR-12 03-MAR-12

        31 03-MAR-12 03-MAR-12

        30 03-MAR-12 03-MAR-12

        29 03-MAR-12 03-MAR-12

Remember, we could have used Real Time Apply in this case, which causes the changes made to the primary appear instantly on the standby, provided the network is available? RTA is not an absolute necessity for ADG but makes the ADG even more useful as you can expect to see the latest changes on the primary.

Security conscious readers might be little concerned however. The database is in read only mode, so nothing can be written to it. If the audit_trail parameter is set to DB on the primary (the default in Oracle Database 11g), it will be the same on standby as well, but the audit trails can't be written to the database since it's read only. So where do they go?

Note a line that shows up innocuously in alert log:

AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with readonly access Aha! The audit trails don't stop; rather, they automatically switch to OS files when the database is open. When you activate the standby database, the audit_trail is automatically resets to DB.

Thanks (command) .