Best Practices for Configuring Redo Transport for Data Guard and Active Data Guard 12c

I have three standby databases db01 located in HQ, db02 located in DR, db03 located in DR and it should be late standby with delay 15 days.

My task is to configure the following standby architecture:

When db01 is primary it should send logs in SYNC mode to db02 and at the same time db02 should send logs in ASYNC mode to db03.

When db02 is in primary role it should send logs in SYNC mode to db01 and at the same time db01 should send logs in ASYNC mode to db03.

So db01 and db02 database should be in sync mode with real-time apply and db03 should be late standby with delay 15 days and it should receive logs from standby database in ASYNC mode.

I have underlined the above sentence, because for now this cannot be achieved with cascading standby. Read bellow…

I have found very useful documentation so here is the link: http://www.oracle.com/technetwork/database/availability/broker-12c-transport-config-2082184.pdf

It introduces data broker new feature that is available in 12c. Property RedoRoutes.

So in my broker configuration I will set RedoRoutes property by the following way:

DGMGRL> show configuration

Configuration – DB_HQ_DR

Protection Mode: MaxPerformance
Members:
db01 – Primary database
db02 – Physical standby database
db03- Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> DGMGRL> edit database ‘db01′ set property RedoRoutes='(LOCAL:db02)(db02:db03)’;
Property “redoroutes” updated
DGMGRL> edit database ‘db02′ set property RedoRoutes='(LOCAL:db01)(db01:db03)’;
Property “redoroutes” updated

Normally, delayed apply can be configured by property DelayMins:

DGMGRL> edit database ‘db03′ set property DelayMins=’21600’;
Property “delaymins” updated

21600 is 15 days.

BUT, I must tell you a bad news:  according to this article https://docs.oracle.com/database/121/SBYDB/log_arch_dest_param.htm#SBYDB01105

“The DELAY value that a cascaded standby uses is the value that was set for the LOG_ARCHIVE_DEST_n parameter on the primary that shipped the redo to the cascading standby.”

So I cannot have the following architecture:

db01 —-real_time_apply—-db02—-delayed_apply—–db03

because db03 will take delay parameter from db02 that is no delay.

Async/Sync mode can be configured by property LogXptMode:

DGMGRL> edit database ‘db01′ set property LogXptMode=’SYNC’;
DGMGRL> edit database ‘db02′ set property LogXptMode=’SYNC’;
DGMGRL> edit database ‘db03′ set property LogXptMode=’ASYNC’;

If I want to achieve my goal I should not use cascading standby but primary must be the sender for db02(with DelayMins=0) and db03(with DelayMins=21600)

I hope it helps.

Advertisements

ORA-01184: logfile group 5 already exists

Command:  

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 11 (‘+DATA’) size 52428800;

Error:

ORA-01184: logfile group 5 already exists

Troubleshoot:

SYS> SELECT thread#, group#
FROM gv$log;

THREAD# GROUP#
———- ———-
1                 1
1                 2
1                 3
1                 4
1                 5
1                 6
1                 7
1                 8
1                 9
1                 10

As we see there are just 10 groups.

Check standby redo logs:

SYS> SELECT group#
FROM v$standby_log;

GROUP#
———-
11

So we have standby redo log with group id 11.

Solution: 

Add logfile group with number more than 11.

I prefer to save numbers for standby logs and add new log with number 22(10 redo log groups+(10+1) recommended number of standby logs+ 1)

SYS> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 22 (‘+DATA’) SIZE 52428800;

Database altered.

Oracle Clusterware is restarting database “” | shut down instance “” of database “” | start up instance “” of database “”

When I used data guard broker to switchover primary database to standby database sometimes broker writes “Oracle Clusterware is restarting database”  … hangs and times out.

The problem was that database was not registered with srvctl.

srvctl add database -d db_unique_name -o oracle_home
srvctl add instance -d db_unique_name -i instance_name1 -n node_name1
srvctl add instance -d db_unique_name -i instance_name2 -n node_name2

instead of db_unique_name, oracle_home , instance_name1, node_name1,  instance_name2, node_name2 enter values according to your database. 

Switchover: convert primary database to standby

–On Primary

— Convert primary database to standby

CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

— Shutdown primary database

SHUTDOWN IMMEDIATE;

— Mount old primary database as standby database, open and enable real-time apply

STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE OPEN;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using current logfile DISCONNECT FROM SESSION;

–On Standby

— Convert standby database to primary

CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

— Shutdown standby database

SHUTDOWN IMMEDIATE;

— Open old standby database as primary

STARTUP;

Enable Real-Time apply on Standby(Standalone,RAC)

In this post we will discuss how to enable real-time apply feature on standalone and Real Application Cluster standby databases.

On standalone standby:

1. See what is recovery mode for now:

SQL> select dest_name,status,type,recovery_mode
from v$archive_dest_status
where dest_id=1;

DEST_NAME               STATUS    TYPE           RECOVERY_MODE
———————– ——— ————– ————————-
LOG_ARCHIVE_DEST_1      VALID     LOCAL          MANAGED

2. Enable real-time apply

On physical:

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.

On logical:

SQL> alter database start logical standby apply immediate;

3. Check

SQL>  select dest_name,status,type,recovery_mode
from v$archive_dest_status
where dest_id=1;

DEST_NAME               STATUS    TYPE           RECOVERY_MODE
———————– ——— ————– ————————-
LOG_ARCHIVE_DEST_1      VALID     LOCAL          MANAGED REAL TIME APPLY

On RAC standby:

1. See what is recovery mode for now:

SQL> select dest_name,status,type,recovery_mode
from v$archive_dest_status
where dest_id=1;

DEST_NAME               STATUS    TYPE           RECOVERY_MODE
———————– ——— ————– ————————-
LOG_ARCHIVE_DEST_1      VALID     LOCAL          MANAGED

2. Enable real-time apply

On node1:

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.

3. Check

SQL>  select dest_name,status,type,recovery_mode
from v$archive_dest_status
where dest_id=1;

DEST_NAME               STATUS    TYPE           RECOVERY_MODE
———————– ——— ————– ————————-
LOG_ARCHIVE_DEST_1      VALID     LOCAL          MANAGED REAL TIME APPLY