ORA-01103: when creating a Standby Database on the same Host as the Primary Database

Typically the standby and the primary databases are located on the different hosts to ensure the full DR capabilities. However, there are some situations when you want to have the primary and standby database on the same Host.

Problem #1: You are not able to start two databases with the same SID on the same server.

Problem #2: You cannot change db_name, because it is used in the controlfile and if you try to duplicate the standby database from the primary using different db_name, you will get the following error:

ORA-01103: database name 'orcldgst' in control file is not 'orcldg'

Assume db_name=orcldg and ORACLE_SID for the primary is orcldg1. To solve problem #1 and problem #2, you need to the following steps:

db_name must be the same for both databases. But during startup nomount of the standby database, you need to set ORACLE_SID to the different value:

$ export ORACLE_SID=orcldgst1
$ sqlplus / as sysdba
SQL> startup nomount pfile='/tmp/mypfile.ora'

After that you will be able to run RMAN duplicate command to create the standby database on the same host as the primary.

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.

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. 

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