Reinstating a Failed Oracle Rac Primary Database using Dataguard Broker

In the previous article, we have performed the Oracle RAC to RAC physical standby database failover steps in 11gR2. Now, let us continue with the reinstate process of the failed primary RAC database.

Below is the environment details:-Note:-

As per the current status, the primary site ORCL_PRODRAC1 is failed and physical standby site ORCL_PRODRAC2 already converted as primary by failover process. So, now ORCL_PRODRAC2 is primary and ORCL_PRODRAC1 is failed primary which should be reinstated.

Steps involved in reinstate of failed primary RAC database:-

A) Check Database Configuration
B) Performing Reinstate
C) Verify the Reinstate of new physical standby database

A) Check Database Configuration:-

Configuration check in Cluster:-

Failed Primary:

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

New Primary:

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

DG Broker configuration:-

$ dgmgrl
DGMGRL> connect sys/manager@ORCL_PRODRAC2
DGMGRL> show configuration verbose;

Configuration - ORACLEDBWR

Protection Mode: MaxPerformance
Databases:
ORCL_PRODRAC2 - Primary database
ORCL_PRODRAC1 - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

B) Performing Reinstate:-

During the reinstate using dataguard broker, the failed primary site will be flashbacked and converted as physical standby database, then the media recovery process will be started automatically started. These can be view in the alert log of the database which is to be reinstated.

DGMGRL> reinstate database 'ORCL_PRODRAC1';

Reinstating database "ORCL_PRODRAC1", please wait...
Operation requires shutdown of instance "ORCL1" on database "ORCL_PRODRAC1"
Shutting down instance "ORCL1"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "ORCL1" on database "ORCL_PRODRAC1"
Starting instance "ORCL1"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "ORCL_PRODRAC1" ...
Operation requires shutdown of instance "ORCL1" on database "ORCL_PRODRAC1"
Shutting down instance "ORCL1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "ORCL1" on database "ORCL_PRODRAC1"
Starting instance "ORCL1"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "ORCL_PRODRAC1" ...
Reinstatement of database "ORCL_PRODRAC1" succeeded

Attached the output of alert log of instance ORCL1 of database  ORCL_PRODRAC1.

C) Verify the Reinstate of new physical standby database:-

Configuration check in Cluster:-

Failed Primary:

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

New Primary:

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Verify in DG Broker configuration:-

$ dgmgrl
DGMGRL> connect sys/manager@ORCL_PRODRAC1
DGMGRL> show configuration verbose;

Configuration - ORACLEDBWR

Protection Mode: MaxPerformance
Databases:
ORCL_PRODRAC2 - Primary database
ORCL_PRODRAC1 - Physical standby database

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

From the above we can notice that the failed primary is now converted as physical standby database. If needed we can switchback it to primary. Refer here for the switchover process.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Oracle Rac Database Failover using Dataguard Broker

In the previous article, we have configured the dataguard broker and performed the switchover from Oracle 11gR2 two node RAC primary to two node physical standby. In this article, let us perform the failover process. The environment details are provided below.

Steps involved in Failover using dataguard broker:-

A) Check Database Configuration before Failover
B) Performing Failover
C) Check database configuration after Failover

A) Check Database Configuration before Failover:-

Configuration check in Cluster:-

Primary:

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Standby:

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

B) Performing Failover:-

Here, connect to the standby site and perform the failover process.

$ dgmgrl
DGMGRL> connect sys/manager@ORCL_PRODRAC2
DGMGRL> failover to 'ORCL_PRODRAC2';
Performing failover NOW, please wait...
Operation requires a connection to instance "ORCL1" on database "ORCL_PRODRAC2"
Connecting to instance "ORCL1"...
Connected.
Failover succeeded, new primary is "ORCL_PRODRAC2"

C) Check database configuration after Failover:-

Configuration check in Cluster:-

Failed Primary(ORCL_PRODRAC1):

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Failedover Primary (ORCL_PRODRAC2):

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

From the above configuration we can see that the Database role for both failed primary site(ORCL_PRODRAC1) and new primary site(ORCL_PRODRAC2) is ‘Primary’ since the failed primary site crashed due to some unplanned outage and that site should be reinstated using dataguard broker.

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Oracle Rac Database Switchover Using Dataguard Broker

Description:-

In the previous article, we have configured dataguard broker for Oracle RAC database with physical standby in 11gR2. Here, we will continue with the switchover process. Below is the environment.Steps Involved in Switchover using dataguard broker:-

A) Check Database Configuration before Switchover
B) Performing Switchover
C) Check database configuration after Switchover

A) Check Database Configuration before Switchover:-

Configuration check in Cluster:-

Primary:

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Standby:

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Primary Node 1:-

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2018 01:09:45

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 24-AUG-2018 02:18:33
Uptime 0 days 22 hr. 51 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradb/app/11.2.0.4/grid/network/admin/listener.ora
Listener Log File /oradb/app/oracle/diag/tnslsnr/prodrac101/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.211)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.214)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC1" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC1_DGB" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC1_DGMGRL" has 1 instance(s).
Instance "ORCL1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Primary Node 2:-

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2018 01:11:02

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 24-AUG-2018 02:18:45
Uptime 0 days 22 hr. 52 min. 17 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradb/app/11.2.0.4/grid/network/admin/listener.ora
Listener Log File /oradb/app/oracle/diag/tnslsnr/prodrac102/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.212)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.215)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
Instance "ORCL2", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC1" has 1 instance(s).
Instance "ORCL2", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC1_DGB" has 1 instance(s).
Instance "ORCL2", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC1_DGMGRL" has 1 instance(s).
Instance "ORCL2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Standby Node 1:-

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2018 01:11:29

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 24-AUG-2018 02:18:46
Uptime 0 days 22 hr. 52 min. 43 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradb/app/11.2.0.4/grid/network/admin/listener.ora
Listener Log File /oradb/app/oracle/diag/tnslsnr/prodrac201/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.221)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.224)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC2" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC2_DGB" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC2_DGMGRL" has 1 instance(s).
Instance "ORCL1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Standby Node 2:-

lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2018 01:12:10

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 24-AUG-2018 02:18:55
Uptime 0 days 22 hr. 53 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradb/app/11.2.0.4/grid/network/admin/listener.ora
Listener Log File /oradb/app/oracle/diag/tnslsnr/prodrac202/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.222)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.225)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC2" has 1 instance(s).
Instance "ORCL2", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC2_DGB" has 1 instance(s).
Instance "ORCL2", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC2_DGMGRL" has 1 instance(s).
Instance "ORCL2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Configuration check in database level (Primary):-

$ sqlplus / as sysdba
SQL> select name, db_unique_name, database_role, switchover_status, open_mode from v$database;

NAME   DB_UNIQUE_NAME  DATABASE_ROLE  SWITCHOVER_STATUS  OPEN_MODE
------ --------------  -------------  -----------------  ---------
ORCL   ORCL_PRODRAC1   PRIMARY        TO STANDBY         READ WRITE

SQL> @stby_gap

DG Broker configuration:-

$ dgmgrl
DGMGRL> connect sys/manager@ORCL_PRODRAC1
DGMGRL> show configuration verbose;


Configuration - ORACLEDBWR

Protection Mode: MaxPerformance
Databases:
ORCL_PRODRAC1 - Primary database
ORCL_PRODRAC2 - Physical standby database

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Primary database setting:-

DGMGRL> show database verbose 'ORCL_PRODRAC1';

Database - ORCL_PRODRAC1

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORCL1
ORCL2

Properties:
DGConnectIdentifier = 'orcl_prodrac1'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value

Database Status:
SUCCESS


DGMGRL> show instance verbose 'ORCL1' on database 'ORCL_PRODRAC1';

Instance 'ORCL1' of database 'ORCL_PRODRAC1'

  Host Name: prodrac101.oracledbwr.com
  PFILE:
  Properties:
    SidName                         = 'ORCL1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.214)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_PRODRAC1_DGMGRL)(INSTANCE_NAME=ORCL1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '+DBWR_FRA'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'ORCL_%t_%s_%r.arc'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

DGMGRL> show instance verbose 'ORCL2' on database 'ORCL_PRODRAC1';

Instance 'ORCL2' of database 'ORCL_PRODRAC1'

Host Name: prodrac102.oracledbwr.com
PFILE:
Properties:
SidName = 'ORCL2'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.215)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_PRODRAC1_DGMGRL)(INSTANCE_NAME=ORCL2)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '+DBWR_FRA'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ORCL_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'

Instance Status:
SUCCESS

Standby database setting:-

DGMGRL> show database verbose 'ORCL_PRODRAC2';

Database - ORCL_PRODRAC2

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Rate: 187.00 KByte/s
Real Time Query: ON
Instance(s):
ORCL1
ORCL2

Properties:
DGConnectIdentifier = 'orcl_prodrac2'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value

Database Status:
SUCCESS

DGMGRL> show instance verbose 'ORCL1' on database 'ORCL_PRODRAC2';

Instance 'ORCL1' of database 'ORCL_PRODRAC2'

Host Name: prodrac201.oracledbwr.com
PFILE:
Properties:
SidName = 'ORCL1'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.224)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_PRODRAC2_DGMGRL)(INSTANCE_NAME=ORCL1)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '+DBWR_FRA'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ORCL_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'

Instance Status:
SUCCESS

DGMGRL> show instance verbose 'ORCL2' on database 'ORCL_PRODRAC2';

Instance 'ORCL2' of database 'ORCL_PRODRAC2'

Host Name: prodrac202.oracledbwr.com
PFILE:
Properties:
SidName = 'ORCL2'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.225)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_PRODRAC2_DGMGRL)(INSTANCE_NAME=ORCL2)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '+DBWR_FRA'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ORCL_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'

Instance Status:
SUCCESS

B) Performing Switchover:-

It is ready for the switchover. Note that we do not need to shutdown any instances

DGMGRL> switchover to 'ORCL_PRODRAC2';
Performing switchover NOW, please wait...
Operation requires a connection to instance "ORCL1" on database "ORCL_PRODRAC2"
Connecting to instance "ORCL1"...
Connected.
New primary database "ORCL_PRODRAC2" is opening...
Operation requires startup of instance "ORCL1" on database "ORCL_PRODRAC1"
Starting instance "ORCL1"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "ORCL_PRODRAC2"

C) Check database configuration after Switchover:-

Old Primary:-

$ srvctl status database -d ORCL_PRODRAC1 -v
Instance ORCL1 is running on node prodrac101. Instance status: Open.
Instance ORCL2 is running on node prodrac102. Instance status: Mounted (Closed).

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

New Primary:-

$ srvctl status database -d ORCL_PRODRAC2 -v
Instance ORCL1 is running on node prodrac201. Instance status: Open.
Instance ORCL2 is running on node prodrac202. Instance status: Open.

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

In the above configuration we can see that database role for both the databases has been changed automatically in cluster, whereas in the manual switchover this has to be done manually. Also, we can notice that the “Start options” is open and read only in old primary and new primary respectively. It is recommended to change this in the cluster manually.

Old Primary:-

$ srvctl modify database -d ORCL_PRODRAC1 -s 'READ ONLY'
$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

New Primary:-

$ srvctl modify database -d ORCL_PRODRAC2 -s 'OPEN'
$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Let us verify in the database for archive gap.

$ sqlplus / as sysdba

SQL> @stby_gap

We can also verify the log shipping from the new primary to old primary by switch log in new primary and tail the alert log in old primary.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Step by Step Configuration Of Data Guard Broker For Rac Database

Description:-

We have already configured Oracle RAC database with physical standby in 11gR2 here. Now, we are going to configure dataguard broker for the same environment.

Below is the High level steps involved in the configuring dataguard broker:-

1) Listener Entry for dataguard broker
2) Enable DG Broker Parameters
3) Create configuration for dataguard broker using dgmgrl
4) Check the configuration

Environment Details:-

Primary Configuration:-

$  srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Standby Configuration:-

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

1) Listener Entry for dataguard broker:-

Make the below entry in the listener.ora file which already exist in the GRID_HOME:-

Primary Node 1:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_PRODRAC1_DGMGRL)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL1)
)
)
Primary Node 2:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_PRODRAC1_DGMGRL)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL2)
)
)
Standby Node 1:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_PRODRAC2_DGMGRL)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL1)
)
)
Standby Node 2:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_PRODRAC2_DGMGRL)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL2)
)
)

Stop and start the listener in all the Nodes of both primary & standby:-

$ lsnrctl stop
$ lsnrctl start

Make sure the below entry is available in the tnsnames.ora file available in  ORACLE_HOME of all the nodes.

ORCL_PRODRAC1 =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = prodscn101.oracledbwr.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC1)
)
)

ORCL_PRODVIP101 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodvip101.oracledbwr.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC1)
(INSTANCE_NAME = ORCL1)
)
)

ORCL_PRODVIP102 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodvip102.oracledbwr.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC1)
(INSTANCE_NAME = ORCL2)
)
)

ORCL_PRODRAC2 =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = prodscn201.oracledbwr.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC2)
(UR = A)
)
)

ORCL_PRODRAC201 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodrac201.oracledbwr.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC2)
(INSTANCE_NAME = ORCL1)
(UR = A)
)
)


ORCL_PRODVIP201 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodvip201.oracledbwr.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC2)
(INSTANCE_NAME = ORCL1)
(UR = A)
)
)

ORCL_PRODVIP202 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodvip202.oracledbwr.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC2)
(INSTANCE_NAME = ORCL2)
(UR = A)
)
)

2) Enable DG Broker Parameters:-

Create the required directories in the ASM diskgroup for the dataguard configuration file and start the DMON process by enabling “dg_broker_start” parameter.

Primary:-

ASMCMD> mkdir +DBWR_DATA/ORCL_PRODRAC1/DATAGUARDCONFIG
ASMCMD> mkdir +DBWR_FRA/ORCL_PRODRAC1/DATAGUARDCONFIG

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DBWR_DATA/ORCL_PRODRAC1/DATAGUARDCONFIG/dgb_config01.ora' SCOPE=BOTH sid='*';

System altered.

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+DBWR_FRA/ORCL_PRODRAC1/DATAGUARDCONFIG/dgb_config02.ora' SCOPE=BOTH sid='*';

System altered.

SQL> ALTER SYSTEM SET dg_broker_start=true scope=both sid='*';

System altered.

Standby:-

ASMCMD> mkdir +DBWR_DATA/ORCL_PRODRAC2/DATAGUARDCONFIG
ASMCMD> mkdir +DBWR_FRA/ORCL_PRODRAC2/DATAGUARDCONFIG

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DBWR_DATA/ORCL_PRODRAC2/DATAGUARDCONFIG/dgb_config01.ora' SCOPE=BOTH sid='*';

System altered.

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+DBWR_FRA/ORCL_PRODRAC2/DATAGUARDCONFIG/dgb_config02.ora' SCOPE=BOTH sid='*';

System altered.

SQL> ALTER SYSTEM SET dg_broker_start=true scope=both sid='*';

System altered.

3) Create configuration for dataguard broker using dgmgrl:-

$ dgmgrl

DGMGRL> connect sys/manager@ORCL_PRODRAC1
DGMGRL> show configuration;
DGMGRL> create configuration 'ORACLEDBWR' as primary database is 'ORCL_PRODRAC1' connect identifier is ORCL_PRODRAC1;

Configuration "ORACLEDBWR" created with primary database "ORCL_PRODRAC1"

DGMGRL> show configuration;

Configuration - ORACLEDBWR

Protection Mode: MaxPerformance
Databases:
ORCL_PRODRAC1 - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> ADD DATABASE 'ORCL_PRODRAC2' AS CONNECT IDENTIFIER IS ORCL_PRODRAC2;

Database "ORCL_PRODRAC2" added

DGMGRL> show configuration;

Configuration - ORACLEDBWR
Protection Mode: MaxPerformance
Databases:
ORCL_PRODRAC1 - Primary database
ORCL_PRODRAC2 - Physical standby database

Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> enable configuration;

4) Check the configuration:-

DGMGRL> show configuration;

Configuration - ORACLEDBWR

Protection Mode: MaxPerformance
Databases:
ORCL_PRODRAC1 - Primary database
ORCL_PRODRAC2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database verbose 'ORCL_PRODRAC1';

Database - ORCL_PRODRAC1

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORCL1
ORCL2

Properties:
DGConnectIdentifier = 'orcl_prodrac1'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value

Database Status:
SUCCESS

DGMGRL> show database verbose 'ORCL_PRODRAC2';

Database - ORCL_PRODRAC2

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 242.00 KByte/s
Real Time Query: ON
Instance(s):
ORCL1
ORCL2
Properties:
DGConnectIdentifier = 'orcl_prodrac2'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value

Database Status:
SUCCESS

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Performing Reinstating a Failed Oracle 2 Node Rac Primary Database

Converting a Failed Primary Into a Standby Database Using Flashback Database

Description:-

In this article, we will be performing the reinstate of failed Oracle 11gR2 two node RAC into a physical standby database.

Let us assume, we have primary site with two node RAC in 11gR2 and standby site similar to primary with two nodes in 11gR2 and both the nodes of primary site was completely down and we have already failed over primary to standby. Now instead of building the physical standby using rman backup in the failed primary site, we have the option of reinstate the failed primary into physical standby from Oracle version 11.2.0.3 if we have already enabled the flashback in the failed primary before the failover.

Below are the steps to be followed to reinstate the failed primary RAC database into physical standby database.
1) Flashback the Failed Primary Database into a Physical Standby Database
2) Flash back the failed primary database
3) Convert the failed primary database to a physical standby database
4) Start the transport and apply redo to the new physical standby database

Below is the primary and standby configuration:Let us verify the status and configuration of the RAC database:-

New Primary Site:-

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Standby:- (Failed Primary Site)

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Step 1 – Flashback the Failed Primary Database into a Physical Standby Database:-

The following steps brings the old primary database back into the Data Guard configuration as a physical standby database.

On the new primary database, issue the following query to determine the SCN at which the old standby database became the new primary database:

SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
2398590

Step 2 – Flashback the failed primary database:-
Shut down the old primary database (if necessary), mount it, and flash it back to the value for STANDBY_BECAME_PRIMARY_SCN that was determined in Step 1

$ srvctl status database -d ORCL_PRODRAC1
Instance ORCL1 is not running on node prodrac101
Instance ORCL2 is not running on node prodrac102

$ srvctl start database -d ORCL_PRODRAC1 -i ORCL1 -o mount
$ srvctl status database -d ORCL_PRODRAC1 -v
Instance ORCL1 is running on node prodrac101. Instance status: Mounted.
Instance ORCL2 is not running on node prodrac102

Flashing back in Node1 (ORCL1) of failed primary:

SQL> FLASHBACK DATABASE TO SCN 2398590;

Flashback complete.

Step 3 – Convert the failed primary database to a physical standby database:-

Issue the following statement on the old primary database.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

This statement will dismount the database after successfully converting the control file to a standby control file. We can verify it from the alert log.

$ tail -f alert_ORCL1.log
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (ORCL1)
Flush standby redo logfile failed:1649
Clearing standby activation ID 1512257592 (0x5a233838)
The primary database controlfile was created using the
'MAXLOGFILES 192' clause.
There is space for up to 186 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl6.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl7.f' SIZE 52428800;
Shutting down archive processes
Archiving is disabled
Completed: ALTER DATABASE CONVERT TO PHYSICAL STANDBY

Shut down and restart the database and start the recovery process in new standby database.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.
$ tail -f alert_ORCL1.log
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT
Attempt to start background Managed Standby Recovery process (ORCL1)
Sun Aug 19 16:52:57 2018
MRP0 started with pid=80, OS id=1356
MRP0: Background Managed Standby Recovery process started (ORCL1)
Serial Media Recovery started
Sun Aug 19 16:53:02 2018
Managed Standby Recovery starting Real Time Apply
Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 2398592
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 +DBWR_DATA/orcl_prodrac1/onlinelog/group_1.261.983984787
Clearing online log 1 of thread 1 sequence number 84
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 +DBWR_DATA/orcl_prodrac1/onlinelog/group_2.262.983984795
Clearing online log 2 of thread 1 sequence number 82
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 +DBWR_DATA/orcl_prodrac1/onlinelog/group_3.263.983984807
Clearing online log 3 of thread 1 sequence number 83
Clearing online redo logfile 3 complete
Clearing online redo logfile 4 +DBWR_DATA/orcl_prodrac1/onlinelog/group_4.267.983985363
Clearing online log 4 of thread 2 sequence number 66
Clearing online redo logfile 4 complete
Clearing online redo logfile 5 +DBWR_DATA/orcl_prodrac1/onlinelog/group_5.268.983985375
Clearing online log 5 of thread 2 sequence number 67
Sun Aug 19 16:53:07 2018
Clearing online redo logfile 5 complete
Clearing online redo logfile 6 +DBWR_DATA/orcl_prodrac1/onlinelog/group_6.269.983985387
Clearing online log 6 of thread 2 sequence number 65
Clearing online redo logfile 6 complete
Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_1_seq_84.410.984588465
Identified End-Of-Redo (failover) for thread 1 sequence 84 at SCN 0x0.249980
Resetting standby activation ID 1512257592 (0x5a233838)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Continuing
Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_1_seq_1.412.984588469
Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_1_seq_2.413.984588469
Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_1_seq_3.411.984588467
Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_2_seq_1.407.984588457
Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_2_seq_2.406.984588457
Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_2_seq_3.408.984588457
Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_1_seq_4.414.984588469
Sun Aug 19 16:53:29 2018
Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_1_seq_5.415.984588469
Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_2_seq_4.409.984588461
Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_2_seq_5.417.984588593
Media Recovery Log +DBWR_FRA/orcl_prodrac1/archivelog/2018_08_19/thread_1_seq_6.416.984588577
Media Recovery Waiting for thread 1 sequence 7 (in transit)
Recovery of Online Redo Log: Thread 1 Group 8 Seq 7 Reading mem 0
Mem# 0: +DBWR_DATA/orcl_prodrac1/onlinelog/group_8.272.984080839
Media Recovery Waiting for thread 2 sequence 6 (in transit)
Recovery of Online Redo Log: Thread 2 Group 11 Seq 6 Reading mem 0
Mem# 0: +DBWR_DATA/orcl_prodrac1/onlinelog/group_11.275.984080865

Step 4 – Verify the redo transport and apply to the new physical standby database:-

Perform the following steps on the new primary database:
Issue the following query to see the current state of the archive destinations

SQL> col DEST_NAME for a20
SQL> col DESTINATION for a20
SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, ERROR,SRL FROM V$ARCHIVE_DEST_STATUS where DEST_ID=2;

DEST_ID    DEST_NAME            STATUS    PROTECTION_MODE
---------- -------------------- --------- --------------------
2           LOG_ARCHIVE_DEST_2  VALID     MAXIMUM PERFORMANCE 

DESTINATION     ERROR             SRL
--------------- ----------------- ----------------------------------
ORCL_PRODVIP101                    YES

Here, we have a valid destination. So, let’s verify the redo transport from primary to new physical standby database.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.
$ tail -f alert_ORCL1.log   <-- New Standby Site - Node1

Tue Aug 21 00:47:11 2018
RFS[7]: Selected log 8 for thread 1 sequence 13 dbid 1511765518 branch 984586385
Tue Aug 21 00:47:11 2018
Archived Log entry 265 added for thread 1 sequence 12 ID 0x5a248ea9 dest 1:
Tue Aug 21 00:47:11 2018
Media Recovery Waiting for thread 1 sequence 13 (in transit)
Recovery of Online Redo Log: Thread 1 Group 8 Seq 13 Reading mem 0
Mem# 0: +DBWR_DATA/orcl_prodrac1/onlinelog/group_8.272.984080839

Reference:-

https://docs.oracle.com/cd/E11882_01/server.112/e41134/scenarios.htm#SBYDB00910

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Step by Step Oracle 11gR2 RAC Standby Database Manual Failover

Oracle 11gR2 RAC to RAC Standby Database Manual Failover Steps

 

Description:-

In this article we are going to performing the Oracle 11gR2 two node RAC to RAC manual failover steps to a physical standby database.

Let us consider, we have primary site with two node RAC and standby site also configured with two node RAC in 11gR2. You can refer here, for two node RAC to RAC dataguard setup in 11gR2 and also we are using the same configuration from the referred one.

Here, we can see the primary and standby configuration:-

Let us verify the status and configuration of the RAC database:-

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed
$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Let us assume, the primary and standby sites are geographical segmented and due to a natural disaster or some power outage issue both the nodes of the primary site become unavailable for the application and we may not be able to restore/recover the primary site within a reasonable period of time,. In this situation, we will be pushed to make the available standby database which is with most recent recovery to be converted into primary and reduce the downtime of the database and make the database available for continuation of the business.

So, let’s follow the below steps for the above scenario.

A) Pre-Checks for Failover
1) Ensure primary is using Flashback for reinstate the failed primary
2) Flush any unsent redo in primary
3) Verify the standby database has the most recent archived redo log from each primary redo thread
4) Identify and resolve any archived redo log gaps
5) Repeat Step 4 until all gaps are resolved for each thread

B) Failover
1) Stop Redo Apply
2) Finish applying all received redo data
3) Verify that the target standby database is ready to become a primary database
4) Switch the physical standby database to the primary role
5) Open the new primary database

Let us verify the status and configuration of the RAC database:-

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed
$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

A) Pre-Failover Checks
Step 1 – Ensure primary is using Flashback for reinstate the failed primary:-  (Primary Side)

In order to reinstate the failed primary database, the flashback option must be enabled in the primary

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

If the flashback is disabled, follow the below step to enable flashback in the primary. This should be done before the failover.

I) Ensure db_recovery_file_dest is set

SQL> show parameter db_recovery_file_dest

NAME                          TYPE        VALUE
--------------------------    ----------- ------------------
db_recovery_file_dest         string      +DBWR_FRA
db_recovery_file_dest_size    big integer 12000M

If the above command returns any value, go for step III. Else set a proper directory or diskgroups.

SQL> alter system set db_recovery_file_dest='+DBWR_FRA' SCOPE=spfile sid='*';

System altered.

II) Ensure db_recovery_file_dest_size is set

SQL> alter system set db_recovery_file_dest_size=20G SCOPE=spfile sid='*';

System altered.

III) Stop and start the database

$ srvctl stop database -d ORCL_PRODRAC1
$ srvctl start database -d ORCL_PRODRAC1 -o mount
SQL> alter database flashback on;

Database altered.
SQL> alter database open; <--- Both Nodes

Database altered.
SQL> alter system set db_flashback_retention_target=2880 sid='*';

System altered.

Let our scenario Primary Database Crush Due to several reason like Hardware failure.

B) Failover
Step 1 – Stop Redo Apply:- (Standby Side) 

Issue the following SQL statement on the target standby database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Step 2 – Finish applying all received redo data:-

Issue the following SQL statement on the target standby database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

Step 3 – Verify that the target standby database is ready to become a primary database:-

Query the SWITCHOVER_STATUS column of the V$DATABASE view on the target standby database

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

A value of either TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role. If neither of these values is returned, verify that Redo Apply is active and continue to query this view until either TO PRIMARY or SESSIONS ACTIVE is returned.

Step 4 – Switch the physical standby database to the primary role:-

Issue the following SQL statement on the target standby database

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

Note:-
The WITH SESSION SHUTDOWN clause can be omitted from the switchover statement if the query of the SWITCHOVER_STATUS column performed in the previous step returned a value of TO PRIMARY.

Step 5 – Open the new primary database:-

SQL> ALTER DATABASE OPEN;  <-- Both Nodes

Database altered.
$ srvctl status database -d ORCL_PRODRAC2 -v
Instance ORCL1 is running on node prodrac201. Instance status: Open.
Instance ORCL2 is running on node prodrac202. Instance status: Open.

Do not use srvctl command to start the secondary instance in the new primary database, since we configured the “Start options as read only” and Oracle Cluster Register do not recognize the switchover/failover automatically. So, if we use srvctl the secondary instance will shutdown as below

$ tail -f alert_ORCL2.log
Lost write protection disabled
Completed: ALTER DATABASE MOUNT /* db agent *//* {1:13107:16648} */
ALTER DATABASE OPEN READ ONLY /* db agent *//* {1:13107:16648} */
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
Errors in file /oradb/app/oracle/diag/rdbms/orcl_prodrac2/ORCL2/trace/ORCL2_ora_5999.trc:
ORA-16002: database already open for read/write access by another instance
ORA-16002 signalled during: ALTER DATABASE OPEN READ ONLY /* db agent *//* {1:13107:16648} */...
Sun Aug 19 16:15:39 2018
Shutting down instance (abort)
License high water mark = 1
USER (ospid: 6022): terminating the instance
Instance terminated by USER, pid = 6022
Sun Aug 19 16:15:39 2018
Instance shutdown complete

Note:-
We can modify the database configuration details as below and use srvctl if we need.

$ srvctl modify database -d ORCL_PRODRAC2 -r PRIMARY -s OPEN
$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed
  1. After this we will start the new primary Database using SRVCTL
  2. After failover take fresh rman backup of New primary database

Reference: 

https://docs.oracle.com/cd/E11882_01/server.112/e41134/role_management.htm#SBYDB00625

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Step by Step Oracle 11gR2 RAC Standby Database Switchover

Step by Step Oracle 11gR2 RAC Standby Database Switchover

Description:- 

In this article, let us do the two node RAC to RAC manual switchover steps.

Let us consider, we have already configured primary site with two node RAC and standby site also with two node RAC in 11gR2. You can refer here, for two node RAC to RAC dataguard setup in 11gR2 and also we are using the same configuration from the referred one.

A) Pre-Switchover Checks:-
1) Verify Managed Recovery is running on the standby
2) Verify there are no large Gaps

B) Switchover:-
1) Cancel any apply delay for the target standby
2) Create Guaranteed Restore Points (optional)
3) Verify that the primary database can be switched to the standby role
4) If The Primary is a RAC, then shutdown all secondary primary instances
5) Switchover the primary to a standby database
6) Verify that the standby database can be switched to the primary role
7) Switchover the standby database to a primary
8) Open the new primary database
9) Restart the new standby

C) Post-Switchover Steps:-
1) Drop any Switchover Guaranteed Restore Points

Here, we can see the primary and standby configuration:-

Let us verify the status and configuration of the RAC database:-

Primary:

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Standby:

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

A) Pre-Switchover Checks:-
Step 1 – Verify Managed Recovery is running on the standby:-
Execute the following statement to verify managed recovery is running in standby

SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

no rows selected

Execute the below statement in primary to verify that recovery is running with “REAL TIME APPLY” option
In our environment we have configured LOG_ARCHIVE_DEST_2 as the ship redo for the standby. So verify with LOG_ARCHIVE_DEST_2 parameter in primary

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

RECOVERY_MODE
-----------------------
IDLE

Here, managed standby recovery is not running or not started with real-time apply. So restart manager recovery in standby with below command. But, if the status is “REAL TIME APPLY”, we can go ahead for the next step.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.

Step 2 – Verify there are no large Gaps:-
Identify the current sequence number for each thread on the primary

SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;

THREAD#    SEQUENCE#
---------- ----------
1           53
2           49

Verify the target physical standby database has applied up to, but not including the logs from the primary query. On the standby the following query should be within 1 or 2 of the primary query result.

SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
WHERE APPLIED = 'YES'
AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT')
GROUP BY THREAD#;

THREAD#    MAX(SEQUENCE#)
---------- --------------
1          52
2          47

B) Switchover:-
Step 1 – Cancel any apply delay for the target standby:-
Remove any delay in applying redo that may be in effect on the standby database that will become the new primary database. Execute the following statement in standby, if there is a delay. Here, we don’t have any delay, so we can skip this step

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

Step 2 – Create Guaranteed Restore Points (optional):-
Create a guaranteed restore point on the primary and standby database participating in the switchover as a fallback plan in case of any failure

On the standby:
Stop the apply process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
Create a guaranteed restore point:
SQL> CREATE RESTORE POINT SWITCHOVER_A_TO_B GUARANTEE FLASHBACK DATABASE;

Restore point created.
Start the apply process:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

On the primary
Create a guaranteed restore point

SQL> CREATE RESTORE POINT SWITCHOVER_A_TO_B GUARANTEE FLASHBACK DATABASE;
Restore point created.

Note: If a guaranteed restore points are created, make sure they are dropped post-switchover!

Step 3 – Verify that the primary database can be switched to the standby role:-
Query the SWITCHOVER_STATUS column of the V$DATABASE view on the primary database:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

A value of TO STANDBY or SESSIONS ACTIVE (which requires the WITH SESSION SHUTDOWN clause on the switchover command) indicates that the primary database can be switched to the standby role. If neither of these values is returned, a switchover is not possible because redo transport is either mis-configured or is not functioning properly

Here we have TO STANDBY. So, now we are ready to the switchover

Step 4 – If The Primary is a RAC, then shutdown all secondary primary instances:-
If The Primary is a RAC, then shutdown all secondary instances in the primary site

$ srvctl status database -d ORCL_PRODRAC1
Instance ORCL1 is running on node prodrac101
Instance ORCL2 is running on node prodrac102

Here we know instance ORCL2 is running on prodrac102. So, shutdown it by below command

$ srvctl stop instance -d ORCL_PRODRAC1 -i ORCL2

Step 5 – Switchover the primary to a standby database:-

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
Database altered.
$ tail -f alert_ORCL1.log   <---- In Primary

The primary database controlfile was created using the
'MAXLOGFILES 192' clause.
There is space for up to 186 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl6.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl7.f' SIZE 52428800;
Archivelog for thread 1 sequence 54 required for standby recovery
Switchover: Primary controlfile converted to standby controlfile succesfully.
Switchover: Complete - Database shutdown required
USER (ospid: 3779): terminating the instance
Instance terminated by USER, pid = 3779
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN
Shutting down instance (abort)
License high water mark = 5
Fri Aug 17 21:02:50 2018
Instance shutdown complete

Step 6 – Verify that the standby database can be switched to the primary role:-
Query the SWITCHOVER_STATUS column of the V$DATABASE view on the standby database:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

Step 7 – Switchover the standby database to a primary:-

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Database altered.
$ tail -f alert_ORCL1.log <---- In New Primary

MRP0: Background Media Recovery process shutdown (ORCL1)
Role Change: Canceled MRP
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Fri Aug 17 21:05:17 2018
SMON: disabling cache recovery
Backup controlfile written to trace file /oradb/app/oracle/diag/rdbms/orcl_prodrac2/ORCL1/trace/ORCL1_ora_13556.trc
SwitchOver after complete recovery through change 1976213
Online logfile pre-clearing operation disabled by switchover
Online log +DBWR_DATA/orcl_prodrac2/onlinelog/group_1.265.984104543: Thread 1 Group 1 was previously cleared
Online log +DBWR_DATA/orcl_prodrac2/onlinelog/group_2.264.984104545: Thread 1 Group 2 was previously cleared
Online log +DBWR_DATA/orcl_prodrac2/onlinelog/group_3.263.984104545: Thread 1 Group 3 was previously cleared
Online log +DBWR_DATA/orcl_prodrac2/onlinelog/group_4.262.984104547: Thread 2 Group 4 was previously cleared
Online log +DBWR_DATA/orcl_prodrac2/onlinelog/group_5.261.984104547: Thread 2 Group 5 was previously cleared
Online log +DBWR_DATA/orcl_prodrac2/onlinelog/group_6.260.984104547: Thread 2 Group 6 was previously cleared
Standby became primary SCN: 1976211
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
Switchover: Complete - Database mounted as primary
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN

Step 8 – Open the new primary database:-

SQL> ALTER DATABASE OPEN;
Database altered.
$ srvctl status database -d ORCL_PRODRAC2
Instance ORCL1 is running on node prodrac201
Instance ORCL2 is running on node prodrac202

Step 9 – Start the new standby:-

SQL> STARTUP MOUNT;   <--- In New Standby

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.

III) Post-Switchover Steps:-
Step – 1 Drop any Switchover Guaranteed Restore Points:-
On all databases where a Guaranteed Restore point was created

On New Standby:-

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.

SQL> DROP RESTORE POINT SWITCHOVER_A_TO_B;
Restore point dropped.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
On New Primary:-

SQL> DROP RESTORE POINT SWITCHOVER_A_TO_B;
Restore point dropped.

Reference: 

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=579890596063881&id=1304939.1&_adf.ctrl-state=1emnpr0uk_267

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

 

Step by Step Configuration Of Oracle 11gR2 Two Node RAC Dataguard Setup

Step by step configuration of Oracle 11gR2 Two Node RAC Dataguard Setup

Description:-

In this article we are going to configure Oracle 11gR2 two node RAC dataguard setup in RHEL 6.8.

Two Node RAC to Two Node RAC Physical Dataguard:

· Prepare Primary Site
· Prepare Standby Site
· Create RAC Physical Standby Database

Below is the environment details for two node RAC dataguard setup.

Let us consider that we have already configured two node 11gR2 RAC database in prodrac101 & prodrac102 with database name ORCL and database unique name ORCL_PRODRAC1 for primary site.

For two node RAC setup please refer here. It is in 12cR1, whereas both 11gR2 and 12cR1 RAC setup is same. So, we can follow the same steps for two node RAC primary setup in 11gR2 also.

Let’s start the two node dataguard setup for two node RAC in 11gR2.

As similar to primary(prodrac101 & prodrac102) site, the grid software and database software should be installed already in standby(prodrac201 & prodrac202) site.

Overview of Primary RAC environment:-

IP Address Details of Primary:

#Public IP
192.168.1.211  prodrac101.oracledbwr.com  prodrac101
192.168.1.212  prodrac102.oracledbwr.com  prodrac102

#Private IP
192.168.2.211  prodprv101.oracledbwr.com  prodprv101
192.168.2.212  prodprv102.oracledbwr.com  prodprv102

#Virtual IP
192.168.1.214  prodvip101.oracledbwr.com  prodvip101
192.168.1.215  prodvip102.oracledbwr.com  prodvip102

#Scan IP
192.168.1.218  prodscn101.oracledbwr.com  prodscn101
192.168.1.219  prodscn101.oracledbwr.com  prodscn101
192.168.1.220  prodscn101.oracledbwr.com  prodscn101

Overview of Standby RAC environment:-

IP Address Details of Primary:

IP Address Details of Standby:

#Public IP
192.168.1.221  prodrac201.oracledbwr.com  prodrac201
192.168.1.222  prodrac202.oracledbwr.com  prodrac202

#Private IP
192.168.2.221  prodprv201.oracledbwr.com  prodprv201
192.168.2.222  prodprv202.oracledbwr.com  prodprv202

#Virtual IP
192.168.1.224  prodvip201.oracledbwr.com  prodvip201
192.168.1.225  prodvip202.oracledbwr.com  prodvip202

#Scan IP
192.168.1.228  prodscn201.oracledbwr.com  prodscn201
192.168.1.229  prodscn201.oracledbwr.com  prodscn201
192.168.1.230  prodscn201.oracledbwr.com  prodscn201

Prepare Primary Site:-

Prepare Primary RAC database for DR configuration:-

  • Change Database Unique Name & Enable Force Logging.
  •  Enable Archivelog Mode.
  • Modify init Parameters.
  • Create the SLRs (Standby Redo Logs).
  •  Create pfile for standby database.
  • Update the tnsnames.ora.

Change Database Unique Name & Enable Force Logging:-

SQL> ALTER SYSTEM SET DB_UNIQUE_NAME='ORCL_PRODRAC1' SCOPE=SPFILE SID='*';
System altered.

$ srvctl stop database -d ORCL
$ srvctl start database -d ORCL_PRODRAC1 -o mount

SQL> ALTER DATABASE FORCE LOGGING;
Database altered.

SQL> ALTER DATABASE ARCHIVELOG;  <--- If not in archivelog mode
Database altered.
$ srvctl stop database -d ORCL_PRODRAC1
$ srvctl start database -d ORCL_PRODRAC1

SQL> SHOW PARAMETER DB_UNIQUE_NAME
NAME           TYPE        VALUE
-------------  ----------  ------------------------------
db_unique_name string      ORCL_PRODRAC1
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL_PRODRAC1,ORCL_PRODRAC2)' sid='*';
System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCL_PRODVIP201 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_PRODRAC2' sid='*';
System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE sid='*';
System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='ORCL_%t_%s_%r.arc' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 sid='*';
System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET FAL_SERVER='ORCL_PRODRAC2' sid='*';
System altered.

SQL> ALTER SYSTEM SET FAL_CLIENT='ORCL_PRODRAC1' sid='*';
System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH SID='*';
System altered.

Add the standby redolog files in primary:

SQL> alter database add standby logfile thread 1 group 7 '+DBWR_DATA' size 50M;
Database altered.

SQL> alter database add standby logfile thread 1 group 8 '+DBWR_DATA' size 50M;
Database altered.

SQL> alter database add standby logfile thread 1 group 9 '+DBWR_DATA' size 50M;
Database altered.

SQL> alter database add standby logfile thread 2 group 10 '+DBWR_DATA' size 50M;
Database altered.

SQL> alter database add standby logfile thread 2 group 11 '+DBWR_DATA' size 50M;
Database altered.

SQL> alter database add standby logfile thread 2 group 12 '+DBWR_DATA' size 50M;
Database altered.

Run the below query in both the nodes of primary to find the newly added standby redlog files:

set lines 999 pages 999
col inst_id for 9999
col group# for 9999
col member for a60
col archived for a7
select * from
(select '[ ONLINE REDO LOG ]' AS "REDOLOG FILE TYPE", a.inst_id AS inst_id, a.group#, b.thread#, b.sequence#, a.member, b.status, b.archived, (b.BYTES/1024/1024) AS SIZE_MB
from gv$logfile a, gv$log b
where a.group#=b.group#
and a.inst_id=b.inst_id
and b.thread#=(select value from v$parameter where name = 'thread')
and a.inst_id=( select instance_number from v$instance)
UNION
select '[ STANDBY REDO LOG ]' AS "REDOLOG FILE TYPE", a.inst_id AS inst_id, a.group#, b.thread#, b.sequence#, a.member, b.status, b.archived, (b.BYTES/1024/1024) AS SIZE_MB
from gv$logfile a, gv$standby_log b
where a.group#=b.group#
and a.inst_id=b.inst_id
and b.thread#=(select value from v$parameter where name = 'thread')
and a.inst_id=( select instance_number from v$instance))
order by 2,3;

In Node 1:In Node 2:Now, verify all the required values have the appropriate values.

set lines 999 pages 999
col value for a110
col name for a50
select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2',
'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile',
'log_archive_format','log_archive_max_processes','fal_server','fal_client','standby_file_management');


NAME                      VALUE
-------------------       -------------------------------
log_archive_dest_1        LOCATION=+DBWR_FRA
log_archive_dest_2        SERVICE=ORCL_PRODVIP201 NOAFFIRM ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_PRODRAC2
log_archive_dest_state_1  enable
log_archive_dest_state_2  ENABLE
fal_client                ORCL_PRODRAC1
fal_server                ORCL_PRODRAC2
log_archive_config        DG_CONFIG=(ORCL_PRODRAC1,ORCL_PRODRAC2)
log_archive_format        ORCL_%t_%s_%r.arc
log_archive_max_processes 30
standby_file_management   AUTO
remote_login_passwordfile EXCLUSIVE
db_name                   ORCL
db_unique_name            ORCL_PRODRAC1

Create pfile and password in primary and copy to standby servers.

SQL> create pfile='/tmp/initORCL1.ora' from spfile;
File created.


$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=manager entries=5 force=y

Once the password file is copied to other node, rename the file with it’s respective $ORACLE_SID name. For example, Primary & Standby Node1’s password file name should be like orapwORCL1 and Primary & Standby Node2’s password file name should be like orapwORCL2

Create static listener in Oracle database home temporarily in the Standby Node1 and start it for active duplicate dataguard setup

cat listener.ora <-- Standby Node1

SID_LIST_LISTENER1 =
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = ORCL_PRODRAC201)
   (ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
   (SID_NAME = ORCL1)
  )
 )

LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = prodrac201.oracledbwr.com)(PORT = 1525))
    )
  )

ADR_BASE_LISTENER1 = /oradb/app/oracle

Start the new listener:

lsnrctl start LISTENER1

Make the below tns entries in tnsnames.ora file of Oracle Database Home of all the Nodes in the cluster.

cat tnsnames.ora <-- All Nodes

ORCL_PRODRAC1 =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = prodscn101.oracledbwr.com)(PORT = 1521)))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC1)))

ORCL_PRODVIP101 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodvip101.oracledbwr.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC1)
(INSTANCE_NAME = ORCL1)))

ORCL_PRODVIP102 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodvip102.oracledbwr.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC1)
(INSTANCE_NAME = ORCL2)))

ORCL_PRODRAC2 =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = prodscn201.oracledbwr.com)(PORT = 1521)))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC2)
(UR = A)))

ORCL_PRODRAC201 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodrac201.oracledbwr.com)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC2)
(INSTANCE_NAME = ORCL1)
(UR = A)))


ORCL_PRODVIP201 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodvip201.oracledbwr.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC2)
(INSTANCE_NAME = ORCL1)
(UR = A)))

ORCL_PRODVIP202 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodvip202.oracledbwr.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_PRODRAC2)
(INSTANCE_NAME = ORCL2)
(UR = A)))

Prepare Standby Site:-

Prepare Standby Node 1 for DR configuration:-

· Create required directories for Standby Servers.
· Modify the pfile for standby.

Create required dump file directories in the Standby Servers:-

On prodrac201:
mkdir -p /oradb/app/oracle/admin/ORCL_PRODRAC2/adump
mkdir -p /oradb/app/oracle/diag/rdbms/orcl_prodrac2/ORCL1
cd /oradb/app/oracle/diag/rdbms/orcl_prodrac2/ORCL1
mkdir trace cdump

On prodrac202:
mkdir -p /oradb/app/oracle/admin/ORCL_PRODRAC2/adump
mkdir -p /oradb/app/oracle/diag/rdbms/orcl_prodrac2/ORCL2
cd /oradb/app/oracle/diag/rdbms/orcl_prodrac2/ORCL2
mkdir trace cdump

Modify initORCL1.ora file (under $ORACLE_HOME/dbs) which was copied from primary to standby:-

cat $ORACLE_HOME/dbs/initORCL1.ora

__oracle_base='/oradb/app/oracle'#ORACLE_BASE set from environment
*.audit_file_dest='/oradb/app/oracle/admin/ORCL_PRODRAC2/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_create_file_dest='+DBWR_DATA'
*.db_create_online_log_dest_1='+DBWR_DATA'
*.db_domain=''
*.db_name='ORCL'
*.control_files='+DBWR_DATA','+DBWR_DATA','+DBWR_FRA'
*.db_recovery_file_dest='+DBWR_FRA'
*.db_recovery_file_dest_size=12G
*.db_unique_name='ORCL_PRODRAC2'
*.diagnostic_dest='/oradb/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.fal_client='ORCL_PRODRAC2'
*.fal_server='ORCL_PRODRAC1'
*.log_archive_config='DG_CONFIG=(ORCL_PRODRAC1,ORCL_PRODRAC2)'
*.log_archive_dest_1='LOCATION=+DBWR_FRA'
*.log_archive_dest_2='SERVICE=ORCL_PRODVIP101 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_PRODRAC1'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='ORCL_%t_%s_%r.arc'
*.log_archive_max_processes=30
*.memory_target=1048576000
*.nls_date_format='DD-MON-YYYY HH24:MI:SS'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.job_queue_processes=20
*.memory_max_target=1200M
*.memory_target=1200M
*.undo_tablespace='UNDOTBS1'
*.undo_management='AUTO'
*.undo_retention=10800
*.service_names='ORCL_PRODRAC2'
*.remote_listener='prodscn201.oracledbwr.com:1521'

Create the Physical Standby Database:-

· Start the Instance in NOMOUNT using the pfile_for_standby.txt.
· Restore the Standby Database using RMAN DUPLICATE command.
· Start the Managed Recovery Process
· Create the spfile and start the database on both the nodes using spfile.
· Register the New Standby database to the OCR.

Start the Instance in NOMOUNT state:-

Set the environment in Standby Node1 and start the instance in nomount:

export ORACLE_SID=ORCL1
export ORACLE_BASE=/oradb/app/oracle
export ORACLE_HOME=/oradb/app/oracle/product/11.2.0.4/db_1
export PATH=$ORACLE_HOME/bin:$PATH

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 13 22:12:49 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=$ORACLE_HOME/dbs/initORCL1.ora
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 788533224 bytes
Database Buffers 452984832 bytes
Redo Buffers 8892416 bytes
SQL> exit

$ rman target sys/manager@ORCL_PRODVIP101 auxiliary sys/manager@ORCL_PRODRAC201

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 14 02:17:48 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1511765518)
connected to auxiliary database: ORCL (not mounted)

RMAN> run
2> {
3> allocate channel ch1 device type disk;
4> allocate channel ch2 device type disk;
5> allocate auxiliary channel ch3 device type disk;
6> allocate auxiliary channel ch4 device type disk;
7> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;
8> release channel ch1;
release channel ch2;
9> 10> release channel ch3;
11> release channel ch4;
12> }

using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=105 instance=ORCL1 device type=DISK
allocated channel: ch2
channel ch2: SID=112 instance=ORCL1 device type=DISK
allocated channel: ch3
channel ch3: SID=29 device type=DISK
allocated channel: ch4
channel ch4: SID=1 device type=DISK
Starting Duplicate Db at 14-AUG-18 02:17:56
contents of Memory Script:
{
backup as copy reuse
targetfile '/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL1' auxiliary format
'/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL1' ;
}
executing Memory Script
Starting backup at 14-AUG-18 02:17:56
Finished backup at 14-AUG-18 02:17:57
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DBWR_DATA/orcl_prodrac2/controlfile/current.272.984104279';
restore clone controlfile to '+DBWR_DATA/orcl_prodrac2/controlfile/current.273.984104279' from
'+DBWR_DATA/orcl_prodrac2/controlfile/current.272.984104279';
restore clone controlfile to '+DBWR_FRA/orcl_prodrac2/controlfile/current.267.984104279' from
'+DBWR_DATA/orcl_prodrac2/controlfile/current.272.984104279';
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''+DBWR_DATA/orcl_prodrac2/controlfile/current.272.984104279'', ''+DBWR_DATA/orcl_prodrac2/controlfile/current.273.984104279'', ''+DBWR_FRA/orcl_prodrac2/controlfile/current.267.984104279'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Starting backup at 14-AUG-18 02:17:58
channel ch1: starting datafile copy
copying standby control file
output file name=/oradb/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_ORCL1.f tag=TAG20180814T021759 RECID=12 STAMP=984104282
channel ch1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 14-AUG-18 02:18:06
Starting restore at 14-AUG-18 02:18:06
channel ch4: skipped, AUTOBACKUP already found
channel ch3: copied control file copy
Finished restore at 14-AUG-18 02:18:14
Starting restore at 14-AUG-18 02:18:15
channel ch4: skipped, AUTOBACKUP already found
channel ch3: copied control file copy
Finished restore at 14-AUG-18 02:18:23
sql statement: create spfile from memory
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 805310440 bytes
Database Buffers 436207616 bytes
Redo Buffers 8892416 bytes
allocated channel: ch3
channel ch3: SID=29 device type=DISK
allocated channel: ch4
channel ch4: SID=30 device type=DISK
sql statement: alter system set control_files = ''+DBWR_DATA/orcl_prodrac2/controlfile/current.272.984104279'', ''+DBWR_DATA/orcl_prodrac2/controlfile/current.273.984104279'', ''+DBWR_FRA/orcl_prodrac2/controlfile/current.267.984104279'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 805310440 bytes
Database Buffers 436207616 bytes
Redo Buffers 8892416 bytes
allocated channel: ch3
channel ch3: SID=29 device type=DISK
allocated channel: ch4
channel ch4: SID=30 device type=DISK
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
datafile 5 auxiliary format new
datafile 6 auxiliary format new
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DBWR_DATA in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 14-AUG-18 02:18:59
channel ch1: starting datafile copy
input datafile file number=00001 name=+DBWR_DATA/orcl_prodrac1/datafile/system.256.983984619
channel ch2: starting datafile copy
input datafile file number=00002 name=+DBWR_DATA/orcl_prodrac1/datafile/sysaux.257.983984621
output file name=+DBWR_DATA/orcl_prodrac2/datafile/sysaux.275.984104341 tag=TAG20180814T021900
channel ch2: datafile copy complete, elapsed time: 00:01:57
channel ch2: starting datafile copy
input datafile file number=00005 name=+DBWR_DATA/orcl_prodrac1/datafile/example.265.983984851
output file name=+DBWR_DATA/orcl_prodrac2/datafile/system.274.984104339 tag=TAG20180814T021900
channel ch1: datafile copy complete, elapsed time: 00:02:24
channel ch1: starting datafile copy
input datafile file number=00003 name=+DBWR_DATA/orcl_prodrac1/datafile/undotbs1.258.983984621
output file name=+DBWR_DATA/orcl_prodrac2/datafile/undotbs1.268.984104487 tag=TAG20180814T021900
channel ch1: datafile copy complete, elapsed time: 00:00:36
channel ch1: starting datafile copy
input datafile file number=00006 name=+DBWR_DATA/orcl_prodrac1/datafile/undotbs2.266.983985173
output file name=+DBWR_DATA/orcl_prodrac2/datafile/example.269.984104459 tag=TAG20180814T021900
channel ch2: datafile copy complete, elapsed time: 00:01:04
channel ch2: starting datafile copy
input datafile file number=00004 name=+DBWR_DATA/orcl_prodrac1/datafile/users.259.983984621
output file name=+DBWR_DATA/orcl_prodrac2/datafile/undotbs2.267.984104523 tag=TAG20180814T021900
channel ch1: datafile copy complete, elapsed time: 00:00:08
output file name=+DBWR_DATA/orcl_prodrac2/datafile/users.266.984104523 tag=TAG20180814T021900
channel ch2: datafile copy complete, elapsed time: 00:00:07
Finished backup at 14-AUG-18 02:22:10
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=12 STAMP=984104541 file name=+DBWR_DATA/orcl_prodrac2/datafile/system.274.984104339
datafile 2 switched to datafile copy
input datafile copy RECID=13 STAMP=984104541 file name=+DBWR_DATA/orcl_prodrac2/datafile/sysaux.275.984104341
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=984104541 file name=+DBWR_DATA/orcl_prodrac2/datafile/undotbs1.268.984104487
datafile 4 switched to datafile copy
input datafile copy RECID=15 STAMP=984104541 file name=+DBWR_DATA/orcl_prodrac2/datafile/users.266.984104523
datafile 5 switched to datafile copy
input datafile copy RECID=16 STAMP=984104541 file name=+DBWR_DATA/orcl_prodrac2/datafile/example.269.984104459
datafile 6 switched to datafile copy
input datafile copy RECID=17 STAMP=984104542 file name=+DBWR_DATA/orcl_prodrac2/datafile/undotbs2.267.984104523
Finished Duplicate Db at 14-AUG-18 02:22:33
released channel: ch1
released channel: ch2
released channel: ch3
released channel: ch4

Now stop the temporarily created listener and add the cluster parameters in standby and convert to two node RAC dataguard.

Stop the new listener:

lsnrctl stop LISTENER1

Create pfile from spfile and add the cluster parameters:

SQL> create pfile from spfile;
File created.

SQL> shut immediate
*.cluster_database_instances=2
*.cluster_database=true
ORCL1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.224)(PORT=1521))'
ORCL2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.225)(PORT=1521))'
ORCL1.instance_number=1
ORCL2.instance_number=2
ORCL1.instance_name='ORCL1'
ORCL2.instance_name='ORCL2'
ORCL1.thread=1
ORCL2.thread=2
ORCL1.undo_tablespace='UNDOTBS1'
ORCL2.undo_tablespace='UNDOTBS2'

Copy the new pfile to Node2 of Standby and startup the instance in both the nodes

Environment variable setup on node1:

$ export ORACLE_SID=ORCL1
$ export ORACLE_HOME=/oradb/app/oracle/product/11.2.0.4/db_1
$ orapwd file=$ORACLE_HOME/dbs/orapwORCL1 password=manager force=y
$ sqlplus / as sysdba
SQL> startup mount pfile=$ORACLE_HOME/dbs/initORCL1.ora

Environment variable setup on node2:

$ export ORACLE_SID=ORCL2
$ export ORACLE_HOME=/oradb/app/oracle/product/11.2.0.4/db_1
$ orapwd file=$ORACLE_HOME/dbs/orapwORCL2 password=manager force=y
$ sqlplus / as sysdba
SQL> startup mount pfile=$ORACLE_HOME/dbs/initORCL2.ora

Register the database instances with CRS framework using this command:

$ srvctl add database -d ORCL_PRODRAC2 -o /oradb/app/oracle/product/11.2.0.4/db_1
$ srvctl add instance -d ORCL_PRODRAC2 -i ORCL1 -n prodrac201
$ srvctl add instance -d ORCL_PRODRAC2 -i ORCL2 -n prodrac202

Now the instances are registered with CRS, shutdown both the instance in standby and start using srvctl command

$ srvctl start database -d ORCL_PRODRAC2 -o mount
$ srvctl status database -d ORCL_PRODRAC2 -v

Start the recovery process in Standby:

alter database recover managed standby database disconnect from session;

Create spfile in ASM and point it using the pfile’s in $ORACLE_HOME/dbs location of both the nodes in Standby.

SQL> create spfile='+DBWR_DATA/ORCL_PRODRAC2/PARAMETERFILE/spfileORCL.ora' from pfile;

File created.

Use the following command to check the status of all instances converted RAC database:

SQL> set lines 180
SQL> col INST_NAME for a80
SQL> select * from v$active_instances;
INST_NUMBER   INST_NAME
------------  -------------------------------------------
          1   prodrac201.oracledbwr.com:ORCL1
          2   prodrac202.oracledbwr.com:ORCL2

In Node1 & Node2 of Standby:-

$ cat initORCL1.ora
spfile='+DBWR_DATA/ORCL_PRODRAC2/PARAMETERFILE/spfileORCL.ora'
$ cat initORCL2.ora
spfile='+DBWR_DATA/ORCL_PRODRAC2/PARAMETERFILE/spfileORCL.ora'

Now shutdown both the instances and startup anyone of the instance using the spfile from ASM and start the media recovery:-

Here, I am starting Node1 of Standby and starting the Media Recovery Process in it to apply archivelog from both nodes of primary.

$ srvctl stop database -d ORCL_PRODRAC2
$ srvctl start instance -d ORCL_PRODRAC2 -i ORCL1 -o mount 
$ srvctl status database -d ORCL_PRODRAC2 -v
$ sqlplus / as sysdba
SQL> alter database recover managed standby database disconnect from session;

We can verify the sync between Primary(Node1 & Node2) and Standby(Node1) by switch logfile in primary and tail alert log from OS or query the alert log from sqlplus. Here, we go with the query.

In Primary – Node1:

SQL> alter system switch logfile;

System altered.

In Standby – Node1:

SQL> @taln.sql

 ID Date Message
-------- -------------------- ------------------------------------------------------------------------------------------------------------------------
6161 14.08.2018 07:13:21 RFS[14]: Assigned to RFS process 56469
6162 14.08.2018 07:13:21 RFS[14]: Selected log 10 for thread 2 sequence 34 dbid 1511765518 branch 983984785
6163 14.08.2018 07:15:21 RFS[14]: Selected log 11 for thread 2 sequence 35 dbid 1511765518 branch 983984785
6164 14.08.2018 07:15:21 Archived Log entry 33 added for thread 2 sequence 34 ID 0x5a1b820b dest 1:
6165 14.08.2018 07:40:19 RFS[13]: Selected log 8 for thread 1 sequence 40 dbid 1511765518 branch 983984785
6166 14.08.2018 07:40:19 Archived Log entry 34 added for thread 1 sequence 39 ID 0x5a1b820b dest 1:
6167 14.08.2018 07:40:40 Archived Log entry 35 added for thread 2 sequence 35 ID 0x5a1b820b dest 1:
6168 14.08.2018 07:40:40 RFS[14]: Selected log 10 for thread 2 sequence 36 dbid 1511765518 branch 983984785
6169 14.08.2018 07:41:08 RFS[13]: Selected log 7 for thread 1 sequence 41 dbid 1511765518 branch 983984785
6170 14.08.2018 07:41:08 Archived Log entry 36 added for thread 1 sequence 40 ID 0x5a1b820b dest 1:

In Primary – Node2:

SQL> alter system switch logfile;

System altered.

In Standby – Node1:

SQL> @taln.sql

 ID Date Message
-------- -------------------- ------------------------------------------------------------------------------------------------------------------------
6163 14.08.2018 07:15:21 RFS[14]: Selected log 11 for thread 2 sequence 35 dbid 1511765518 branch 983984785
6164 14.08.2018 07:15:21 Archived Log entry 33 added for thread 2 sequence 34 ID 0x5a1b820b dest 1:
6165 14.08.2018 07:40:19 RFS[13]: Selected log 8 for thread 1 sequence 40 dbid 1511765518 branch 983984785
6166 14.08.2018 07:40:19 Archived Log entry 34 added for thread 1 sequence 39 ID 0x5a1b820b dest 1:
6167 14.08.2018 07:40:40 Archived Log entry 35 added for thread 2 sequence 35 ID 0x5a1b820b dest 1:
6168 14.08.2018 07:40:40 RFS[14]: Selected log 10 for thread 2 sequence 36 dbid 1511765518 branch 983984785
6169 14.08.2018 07:41:08 RFS[13]: Selected log 7 for thread 1 sequence 41 dbid 1511765518 branch 983984785
6170 14.08.2018 07:41:08 Archived Log entry 36 added for thread 1 sequence 40 ID 0x5a1b820b dest 1:
6171 14.08.2018 07:46:20 Archived Log entry 37 added for thread 2 sequence 36 ID 0x5a1b820b dest 1:
6172 14.08.2018 07:46:21 RFS[14]: Selected log 10 for thread 2 sequence 37 dbid 1511765518 branch 983984785

DONE!!!

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Step by Step Configuration of Oracle Cascading Standby Database

Description:

  • In this article we are going to see Step by Step Configuration of Oracle Cascading Standby Database 11.2.0.4 in RHEL-6.8.
  • To reduce the load on your primary system, you can implement cascaded destinations, where by a standby database receives its redo data from another standby database, instead of directly from the primary database  [ Primary->stby1->stby2 ]
  • The cascaded standby database does not receive its redo directly from the Primary database and this type of data guard configuration offloads the overhead associated with performing redo transport from a primary database to a cascading standby database.
  • A physical standby database to retransmit the incoming redo data it receives from the primary database to other remote destinations in the same manner as the primary database

Environment details of the cascaded standby database.

Let’s Start the Demo:-

Primary Server side Configurations:-

Step1:-Change Archivelog mode:
SQL> archive log list
SQL> shut immediate
SQL> startup mount
SQL> alter system set log_archive_dest_1='LOCATION=/oradb/app/oracle/archive/orcl_prim' scope=spfile;
SQL> alter database archivelog;
SQL> alter database open;

Make sure the archive directory is created in physically in OS level.

Step2:-Change force logging mode:
SQL> alter database force logging;
SQL> select force_logging,log_mode from v$database;
Step3:-Adding Redologfile for standby database:

SQL> alter database add standby logfile group 4 '/oradb/app/oracle/oradata/ORCL_PRIM/redo04.log' size 50m;
SQL> alter database add standby logfile group 5 '/oradb/app/oracle/oradata/ORCL_PRIM/redo05.log' size 50m;
SQL> alter database add standby logfile group 6 '/oradb/app/oracle/oradata/ORCL_PRIM/redo06.log' size 50m;
Check the newly added standby redo log files with the below qurey:
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Step4:-Add the network entry in primary and standby side(All the servers):

Listener Entry(Primary):-
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_PRIM)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracledbwr.com)(PORT = 1624))
)
)
ADR_BASE_INTENSITY = /oradb/app/oracle

Listener Entry(Standby1):-
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_STBY1)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby1.oracledbwr.com)(PORT = 1624))
)
)
ADR_BASE_INTENSITY = /oradb/app/oracle

Listener Entry(Standby2):-
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_STBY2)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby2.oracledbwr.com)(PORT = 1624))
)
)
ADR_BASE_INTENSITY = /oradb/app/oracle

Tnsnames entry(All three servers):-
ORCL_PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracledbwr.com)(PORT = 1624))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_PRIM)
)
)

ORCL_STBY1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby1.oracledbwr.com)(PORT = 1624))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_STBY1)
)
)

ORCL_STBY2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby2.oracledbwr.com)(PORT = 1624))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_STBY2)
)
)

Start the listener in all three servers and try to tnsping to each other. If the tnsping is successful, go ahead for the next step, else fix the tnsping issue and go to the next step.

$ tnsping ORCL_PRIM
$ tnsping ORCL_STBY1
$ tnsping ORCL_STBY2

Step5:-Changing parameters in primary database:
SQL> ALTER SYSTEM SET db_unique_name='ORCL_PRIM' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_config='dg_config=(ORCL_PRIM,ORCL_STBY1,ORCL_STBY2)' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/oradb/app/oracle/archive/orcl_prim valid_for=(all_logfiles,all_roles) db_unique_name=ORCL_PRIM' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_2='service=ORCL_STBY1 async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL_STBY1' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_3='service=ORCL_STBY2 async valid_for=(standby_logfiles,standby_role) db_unique_name=ORCL_STBY2' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_state_3='DEFER' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET fal_server='ORCL_STBY1' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET fal_client='ORCL_PRIM' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET db_file_name_convert='/oradb/app/oracle/oradata/ORCL_PRIM','/oradb/app/oracle/oradata/ORCL_STBY1' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_file_name_convert='/oradb/app/oracle/oradata/ORCL_PRIM','/oradb/app/oracle/oradata/ORCL_STBY1' SCOPE=SPFILE;
SQL> SHUT IMMEDIATE
SQL> STARTUP

Step6:-Check the changed parameters:

SQL> @dg_params.sql

Create a pfile and password file and copy to the standby servers:
SQL> create pfile from spfile;
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=manager entries=5

Copy the remote login password file (orapwORCL) from $ORACLE_HOME/dbs of primary database server to standby1 & standby2 database server to the location $ORACLE_HOME/dbs:
$ scp $ORACLE_HOME/dbs/initORCL.ora oracle@standby1:$ORACLE_HOME/dbs/initORCL.ora
$ scp $ORACLE_HOME/dbs/orapwORCL oracle@standby1:$ORACLE_HOME/dbs/orapwORCL
$ scp $ORACLE_HOME/dbs/initORCL.ora oracle@standby2:$ORACLE_HOME/dbs/initORCL.ora
$ scp $ORACLE_HOME/dbs/orapwORCL oracle@standby2:$ORACLE_HOME/dbs/orapwORCL

Standby1 Server side Configurations:-

Step1:-Change the necessary parameters in the copied pfile as like below

$ cat $ORACLE_HOME/dbs/initORCL.ora
*.__oracle_base='/oradb/app/oracle'# ORACLE_BASE set from environment
*.audit_file_dest='/oradb/app/oracle/admin/ORCL_STBY1/adump'
*.audit_trail='DB'
*.compatible='11.2.0.4.0'
*.control_files='/oradb/app/oracle/oradata/ORCL_STBY1/control01.ctl','/oradb/app/oracle/flash_recovery_area/ORCL_STBY1/control02.ctl'
*.db_block_size=8192
*.db_domain='doyensys.com'
*.db_file_name_convert='/oradb/app/oracle/oradata/ORCL_PRIM','/oradb/app/oracle/oradata/ORCL_STBY1'
*.db_name='ORCL'
*.db_recovery_file_dest='/oradb/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=8G
*.db_unique_name='ORCL_STBY1'
*.diagnostic_dest='/oradb/app/oracle'
*.fal_client='ORCL_STBY1'
*.fal_server='ORCL_PRIM'
*.job_queue_processes=20
*.log_archive_config='dg_config=(ORCL_PRIM,ORCL_STBY1,ORCL_STBY2)'
*.log_archive_dest_1='LOCATION=/oradb/app/oracle/archive/orcl_stby1 valid_for=(all_logfiles,all_roles) db_unique_name=ORCL_STBY1'
*.log_archive_dest_2='service=ORCL_PRIM async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL_PRIM'
*.log_archive_dest_3='service=ORCL_STBY2 async valid_for=(standby_logfiles,standby_role) db_unique_name=ORCL_STBY2'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_format='orcls1_%t_%s_%r.arc'
*.log_file_name_convert='/oradb/app/oracle/oradata/ORCL_PRIM','/oradb/app/oracle/oradata/ORCL_STBY1'
*.memory_max_target=692060160
*.memory_target=629145600
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=150
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.skip_unusable_indexes=TRUE
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

Step2:-Create directory Structure in Standby database

$ mkdir -p /oradb/app/oracle/admin/ORCL_STBY1/adump
$ mkdir -p /oradb/app/oracle/oradata/ORCL_STBY1
$ mkdir -p /oradb/app/oracle/flash_recovery_area/ORCL_STBY1
$ mkdir -p /oradb/app/oracle/archive/orcl_stby1

Step3:-Start the standby1 database using pfile

$ sqlplus ‘/ as sysdba’

SQL> startup nomount
SQL> exit

Step4:-Connect to the rman

$ rman target sys/manager@ORCL_PRIM auxiliary sys/manager@ORCL_STBY1

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jul 1 22:53:03 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1508077812)
connected to auxiliary database: ORCL (not mounted)
RMAN> run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database nofilenamecheck;
}
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=37 device type=DISK
allocated channel: p2
channel p2: SID=49 device type=DISK
allocated channel: p3
channel p3: SID=50 device type=DISK
allocated channel: p4
channel p4: SID=51 device type=DISK
allocated channel: s1
channel s1: SID=19 device type=DISK
Starting Duplicate Db at 01-JUL-18 22:53:30
contents of Memory Script:
{
backup as copy reuse
targetfile '/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL' auxiliary format
'/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL' ;
}
executing Memory Script
Starting backup at 01-JUL-18 22:53:30
Finished backup at 01-JUL-18 22:53:31
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/oradb/app/oracle/oradata/ORCL_STBY1/control01.ctl';
restore clone controlfile to '/oradb/app/oracle/flash_recovery_area/ORCL_STBY1/control02.ctl' from
'/oradb/app/oracle/oradata/ORCL_STBY1/control01.ctl';
}
executing Memory Script

Starting backup at 01-JUL-18 22:53:32
channel p1: starting datafile copy
copying standby control file
output file name=/oradb/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_ORCL.f tag=TAG20180701T225332 RECID=10 STAMP=980376813
channel p1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 01-JUL-18 22:53:35
Starting restore at 01-JUL-18 22:53:35
channel s1: copied control file copy
Finished restore at 01-JUL-18 22:53:36
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/oradb/app/oracle/oradata/ORCL_STBY1/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/oradb/app/oracle/oradata/ORCL_STBY1/system01.dbf";
set newname for datafile 2 to
"/oradb/app/oracle/oradata/ORCL_STBY1/sysaux01.dbf";
set newname for datafile 3 to
"/oradb/app/oracle/oradata/ORCL_STBY1/undotbs01_1.dbf";
set newname for datafile 4 to
"/oradb/app/oracle/oradata/ORCL_STBY1/users01.dbf";
set newname for datafile 5 to
"/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF ";
set newname for datafile 6 to
"/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF";
set newname for datafile 7 to
"/oradb/app/oracle/oradata/ORCL_STBY1/custom01.dbf";
set newname for datafile 8 to
"/oradb/app/oracle/oradata/ORCL_STBY1/custom02.dbf";
set newname for datafile 9 to
"/oradb/app/oracle/oradata/ORCL_STBY1/custom03.dbf";
set newname for datafile 10 to
"/oradb/app/oracle/oradata/ORCL_STBY1/custom04.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/system01.dbf" datafile
2 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/sysaux01.dbf" datafile
3 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/undotbs01_1.dbf" datafile
4 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/users01.dbf" datafile
5 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF " datafile
6 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF" datafile
7 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/custom01.dbf" datafile
8 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/custom02.dbf" datafile
9 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/custom03.dbf" datafile
10 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/custom04.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oradb/app/oracle/oradata/ORCL_STBY1/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 01-JUL-18 22:53:42
channel p1: starting datafile copy
input datafile file number=00008 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom02.dbf
channel p2: starting datafile copy
input datafile file number=00007 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom01.dbf
channel p3: starting datafile copy
input datafile file number=00009 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom03.dbf
channel p4: starting datafile copy
input datafile file number=00004 name=/oradb/app/oracle/oradata/ORCL_PRIM/users01.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/users01.dbf tag=TAG20180701T225342
channel p4: datafile copy complete, elapsed time: 00:17:00
channel p4: starting datafile copy
input datafile file number=00006 name=/oradb/app/oracle/oradata/ORCL_PRIM/APEX01.DBF
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF tag=TAG20180701T225342
channel p4: datafile copy complete, elapsed time: 00:10:54
channel p4: starting datafile copy
input datafile file number=00005 name=/oradb/app/oracle/oradata/ORCL_PRIM/APEX01.DBF
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF tag=TAG20180701T225342
channel p4: datafile copy complete, elapsed time: 00:09:43
channel p4: starting datafile copy
input datafile file number=00001 name=/oradb/app/oracle/oradata/ORCL_PRIM/system01.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/system01.dbf tag=TAG20180701T225342
channel p4: datafile copy complete, elapsed time: 00:08:01
channel p4: starting datafile copy
input datafile file number=00002 name=/oradb/app/oracle/oradata/ORCL_PRIM/sysaux01.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/sysaux01.dbf tag=TAG20180701T225342
channel p4: datafile copy complete, elapsed time: 00:11:19
channel p4: starting datafile copy
input datafile file number=00003 name=/oradb/app/oracle/oradata/ORCL_PRIM/undotbs01_1.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom03.dbf tag=TAG20180701T225342
channel p3: datafile copy complete, elapsed time: 01:01:07
channel p3: starting datafile copy
input datafile file number=00010 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom04.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom04.dbf tag=TAG20180701T225342
channel p3: datafile copy complete, elapsed time: 00:00:17
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/undotbs01_1.dbf tag=TAG20180701T225342
channel p4: datafile copy complete, elapsed time: 00:05:31
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom01.dbf tag=TAG20180701T225342
channel p2: datafile copy complete, elapsed time: 01:23:48
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom02.dbf tag=TAG20180701T225342
channel p1: datafile copy complete, elapsed time: 01:25:21
Finished backup at 02-JUL-18 00:19:04
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/undotbs01_1.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=15 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF
datafile 7 switched to datafile copy
input datafile copy RECID=16 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=17 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom02.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=18 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom03.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=19 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom04.dbf
Finished Duplicate Db at 02-JUL-18 00:19:26
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1

Step5:-Connect to the standby1 database and start the recovery process

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

Step6:-Physical Standby Database is Performing Correctly

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

Standby2 Server side Configurations:-

Step1:-Change the necessary parameters in the copied pfile as like below

$ cat $ORACLE_HOME/dbs/initORCL.ora
*.__oracle_base='/oradb/app/oracle'# ORACLE_BASE set from environment
*.audit_file_dest='/oradb/app/oracle/admin/ORCL_STBY2/adump'
*.audit_trail='DB'
*.compatible='11.2.0.4.0'
*.control_files='/oradb/app/oracle/oradata/ORCL_STBY2/control01.ctl','/oradb/app/oracle/flash_recovery_area/ORCL_STBY2/control02.ctl'
*.db_block_size=8192
*.db_domain='doyensys.com'
*.db_file_name_convert='/oradb/app/oracle/oradata/ORCL_PRIM','/oradb/app/oracle/oradata/ORCL_STBY2'
*.db_name='ORCL'
*.db_recovery_file_dest='/oradb/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=8G
*.db_unique_name='ORCL_STBY2'
*.diagnostic_dest='/oradb/app/oracle'
*.fal_client='ORCL_STBY2'
*.fal_server='ORCL_STBY1'
*.job_queue_processes=20
*.log_archive_config='dg_config=(ORCL_PRIM,ORCL_STBY1,ORCL_STBY2)'
*.log_archive_dest_1='LOCATION=/oradb/app/oracle/archive/orcl_stby2'
*.log_archive_format='orcls2_%t_%s_%r.arc'
*.log_file_name_convert='/oradb/app/oracle/oradata/ORCL_PRIM','/oradb/app/oracle/oradata/ORCL_STBY2'
*.memory_max_target=692060160
*.memory_target=629145600
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=150
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.skip_unusable_indexes=TRUE
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

Step2:-Create directory Structure in Standby database

$ mkdir -p /oradb/app/oracle/admin/ORCL_STBY2/adump
$ mkdir -p /oradb/app/oracle/oradata/ORCL_STBY2
$ mkdir -p /oradb/app/oracle/flash_recovery_area/ORCL_STBY2
$ mkdir -p /oradb/app/oracle/archive/orcl_stby2

Step3:-Start the standby2 database using pfile

$ sqlplus ‘/ as sysdba’

SQL> startup nomount
SQL> exit

Step4:-Connect to the rman

$ rman target sys/manager@ORCL_PRIM auxiliary sys/manager@ORCL_STBY2

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jul 2 18:17:44 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1508077812)
connected to auxiliary database: ORCL (not mounted)
RMAN> run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database nofilenamecheck;
}
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=62 device type=DISK
allocated channel: p2
channel p2: SID=29 device type=DISK
allocated channel: p3
channel p3: SID=59 device type=DISK
allocated channel: p4
channel p4: SID=36 device type=DISK
allocated channel: s1
channel s1: SID=19 device type=DISK
Starting Duplicate Db at 02-JUL-18 18:17:53
contents of Memory Script:
{
backup as copy reuse
targetfile '/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL' auxiliary format
'/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL' ;
}
executing Memory Script
Starting backup at 02-JUL-18 18:17:53
Finished backup at 02-JUL-18 18:17:54
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/oradb/app/oracle/oradata/ORCL_STBY2/control01.ctl';
restore clone controlfile to '/oradb/app/oracle/flash_recovery_area/ORCL_STBY2/control02.ctl' from
'/oradb/app/oracle/oradata/ORCL_STBY2/control01.ctl';
}
executing Memory Script

Starting backup at 02-JUL-18 18:17:54
channel p1: starting datafile copy
copying standby control file
output file name=/oradb/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_ORCL.f tag=TAG20180702T181754 RECID=11 STAMP=980446676
channel p1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 02-JUL-18 18:17:58
Starting restore at 02-JUL-18 18:17:58
channel s1: copied control file copy
Finished restore at 02-JUL-18 18:17:59
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/oradb/app/oracle/oradata/ORCL_STBY2/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/oradb/app/oracle/oradata/ORCL_STBY2/system01.dbf";
set newname for datafile 2 to
"/oradb/app/oracle/oradata/ORCL_STBY2/sysaux01.dbf";
set newname for datafile 3 to
"/oradb/app/oracle/oradata/ORCL_STBY2/undotbs01_1.dbf";
set newname for datafile 4 to
"/oradb/app/oracle/oradata/ORCL_STBY2/users01.dbf";
set newname for datafile 5 to
"/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF ";
set newname for datafile 6 to
"/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF";
set newname for datafile 7 to
"/oradb/app/oracle/oradata/ORCL_STBY2/custom01.dbf";
set newname for datafile 8 to
"/oradb/app/oracle/oradata/ORCL_STBY2/custom02.dbf";
set newname for datafile 9 to
"/oradb/app/oracle/oradata/ORCL_STBY2/custom03.dbf";
set newname for datafile 10 to
"/oradb/app/oracle/oradata/ORCL_STBY2/custom04.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/system01.dbf" datafile
2 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/sysaux01.dbf" datafile
3 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/undotbs01_1.dbf" datafile
4 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/users01.dbf" datafile
5 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF " datafile
6 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF" datafile
7 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/custom01.dbf" datafile
8 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/custom02.dbf" datafile
9 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/custom03.dbf" datafile
10 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/custom04.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oradb/app/oracle/oradata/ORCL_STBY2/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 02-JUL-18 18:18:05
channel p1: starting datafile copy
input datafile file number=00008 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom02.dbf
channel p2: starting datafile copy
input datafile file number=00007 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom01.dbf
channel p3: starting datafile copy
input datafile file number=00009 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom03.dbf
channel p4: starting datafile copy
input datafile file number=00004 name=/oradb/app/oracle/oradata/ORCL_PRIM/users01.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/users01.dbf tag=TAG20180702T181805
channel p4: datafile copy complete, elapsed time: 00:14:02
channel p4: starting datafile copy
input datafile file number=00006 name=/oradb/app/oracle/oradata/ORCL_PRIM/APEX01.DBF
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF tag=TAG20180702T181805
channel p4: datafile copy complete, elapsed time: 00:09:30
channel p4: starting datafile copy
input datafile file number=00005 name=/oradb/app/oracle/oradata/ORCL_PRIM/APEX01.DBF
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF tag=TAG20180702T181805
channel p4: datafile copy complete, elapsed time: 00:17:02
channel p4: starting datafile copy
input datafile file number=00001 name=/oradb/app/oracle/oradata/ORCL_PRIM/system01.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/system01.dbf tag=TAG20180702T181805
channel p4: datafile copy complete, elapsed time: 00:08:15
channel p4: starting datafile copy
input datafile file number=00002 name=/oradb/app/oracle/oradata/ORCL_PRIM/sysaux01.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/sysaux01.dbf tag=TAG20180702T181805
channel p4: datafile copy complete, elapsed time: 00:11:03
channel p4: starting datafile copy
input datafile file number=00003 name=/oradb/app/oracle/oradata/ORCL_PRIM/undotbs01_1.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom03.dbf tag=TAG20180702T181805
channel p3: datafile copy complete, elapsed time: 01:06:02
channel p3: starting datafile copy
input datafile file number=00010 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom04.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom04.dbf tag=TAG20180702T181805
channel p3: datafile copy complete, elapsed time: 00:00:37
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/undotbs01_1.dbf tag=TAG20180702T181805
channel p4: datafile copy complete, elapsed time: 00:07:03
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom01.dbf tag=TAG20180702T181805
channel p2: datafile copy complete, elapsed time: 01:31:51
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom02.dbf tag=TAG20180702T181805
channel p1: datafile copy complete, elapsed time: 01:33:54
Finished backup at 02-JUL-18 19:51:59
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=12 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/undotbs01_1.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=15 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=16 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF
datafile 7 switched to datafile copy
input datafile copy RECID=17 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=18 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom02.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=19 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom03.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=20 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom04.dbf
Finished Duplicate Db at 02-JUL-18 19:52:20
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1

Step5:-Connect to the standby1 database and start the recovery process

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

In Primary switch logfile and the archived log file in both standby servers by executing the below script:

SQL> @stby_log_applied.sql

Primary:

Standby1:

Standby2:

Successfully completed the cascading standby database configuration

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

Reference:-

https://docs.oracle.com/cd/B19306_01/server.102/b14239/cascade_appx.htm#g639625

Step by Step Configuring Dataguard on Different Operating Systems (Heterogeneous)

Step by Step Configuring Dataguard on Different Operating Systems (Heterogeneous)

Description:-

In this article we are going to configure Oracle Heterogeneous Standby with Primary in Linux & Standby in Windows and  see the below configuration details.Primary Server side Configurations:-

Step1:-Change Archivelog mode
SQL> archive log listSQL> shut immediate
SQL> startup mount
SQL> alter system set log_archive_dest_1=’LOCATION=/oradb/app/oracle/archive/orcl_prim’ scope=spfile;
SQL> alter database archivelog;
SQL> alter database open;

Step2:-Change force logging mode
SQL> alter database force logging;
SQL> select force_logging,log_mode from v$database;Step3:-Adding Redologfile for standby database
SQL> alter database add standby logfile group 4 ‘/oradb/app/oracle/oradata/ORCL_PRIM/redo04.log’ size 50m;
SQL> alter database add standby logfile group 5 ‘/oradb/app/oracle/oradata/ORCL_PRIM/redo05.log’ size 50m;
SQL> alter database add standby logfile group 6 ‘/oradb/app/oracle/oradata/ORCL_PRIM/redo06.log’ size 50m;

Now check the all the database files location:-

SQL> @all_db_files.sqlSQL>SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Step4:-Adding the network entry in primary and standby side(Both servers)
Listener Entry(Primary):-
*******************
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_PRIM)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1624))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1624))
)
)

Listener Entry(Standby):-
*******************
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_STBY)
(ORACLE_HOME = D:\app\oracle\product\11.2.0.4\db_1)
(SID_NAME = ORCL)
)
)

Tnsnames entry(Both):-
******************
ORCL_PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1624))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_PRIM)
)
)

ORCL_STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.106)(PORT = 1624))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_STBY)
)
)

Start the listener in both primary & standby and tnsping to primary & standby:-
$ tnsping ORCL_PRIM

TNS Ping Utility for Linux: Version 11.2.0.4.0 – Production on 24-JUN-2018 20:47:15
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1624))) (CONNECT_DATA = (SERVICE_NAME = ORCL_PRIM)))
OK (10 msec)

$ tnsping ORCL_STBY

TNS Ping Utility for Linux: Version 11.2.0.4.0 – Production on 24-JUN-2018 20:48:18
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.106)(PORT = 1624))) (CONNECT_DATA = (SERVICE_NAME = ORCL_STBY)))
OK (10 msec)

Step5:-Changing parameters in primary database
SQL> ALTER SYSTEM SET db_unique_name=’ORCL_PRIM’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(ORCL_PRIM,ORCL_STBY)’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_1=’LOCATION=/oradb/app/oracle/archive/orcl_prim valid_for=(all_logfiles,all_roles) db_unique_name=ORCL_PRIM’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_2=’service=ORCL_STBY async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL_STBY’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET fal_server=’ORCL_STBY’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET fal_client=’ORCL_PRIM’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET db_file_name_convert=’/oradb/app/oracle/oradata/ORCL_PRIM’,’D:\app\oracle\oradata\ORCL_STBY’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_file_name_convert=’/oradb/app/oracle/oradata/ORCL_PRIM’,’D:\app\oracle\oradata\ORCL_STBY’ SCOPE=SPFILE;
SQL> SHUT IMMEDIATE
SQL> STARTUP

Verify All the required parameters are set properly with the below script:-SQL> @stby_parameters.sql

Standby Server side Configurations:-

Step1:-Password file creation
Set ORACLE_BASE,ORACLE_HOME & PATH:-
$ set ORACLE_SID=ORCL
$ set ORACLE_BASE=D:\app\oracle
$ set ORACLE_HOME=%ORACLE_BASE%\product\11.2.0.4\db_1
$ set PATH=%ORACLE_HOME%\bin;%PATH%

Copy the remote login password file (orapwORCL) from the primary database server in $ORACLE_HOME/dbs directory to the
standby database server in $ORACLE_HOME/database:-

$ pscp $ORACLE_HOME/dbs/orapwORCL administrator@192.168.1.106:D:\app\oracle\product\11.2.0.4\db_1\database

Step2:-Changing parameters in standby database:-

In the $ORACLE_HOME/database directory of the standby system, create an initialization parameter file named initORCL.ora with the below parameter.

ORCL.__oracle_base=’D:\app\oracle’#ORACLE_BASE set from environment
*.audit_file_dest=’D:\app\oracle\admin\ORCL_STBY\adump’
*.audit_trail=’db’
*.compatible=’11.2.0.4′
*.control_files=’D:\app\oracle\oradata\ORCL_STBY\control01.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_file_name_convert=’/oradb/app/oracle/oradata/ORCL_PRIM/’,’D:\app\oracle\oradata\ORCL_STBY\’
*.db_name=’ORCL’
*.db_recovery_file_dest=’D:\app\oracle\fast_recovery_area’
*.db_recovery_file_dest_size=4385144832
*.db_unique_name=’ORCL_STBY’
*.diagnostic_dest=’D:\app\oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)’
*.fal_client=’ORCL_STBY’
*.fal_server=’ORCL_PRIM’
*.log_archive_config=’dg_config=(ORCL_PRIM,ORCL_STBY)’
*.log_archive_dest_1=’LOCATION=D:\app\oracle\archive\orcl_stby valid_for=(all_logfiles,all_roles) db_unique_name=ORCL_STBY’
*.log_archive_dest_2=’service=ORCL_PRIM async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL_PRIM’
*.log_archive_max_processes=5
*.log_file_name_convert=’/oradb/app/oracle/oradata/ORCL_PRIM/’,’D:\app\oracle\oradata\ORCL_STBY\’
*.memory_target=1509949440
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′

Step3:-Create directory Structure in Standby database

$ cd %ORACLE_BASE%
$ mkdir admin\ORCL_STBY
$ cd admin\ORCL_STBY
$ mkdir adump pfile dpdump
$ mkdir -p D:\app\oracle\oradata\ORCL_STBY
$ mkdir -p D:\app\oracle\fast_recovery_area\ORCL_STBY

Step4:-Setup the windows service for oracle and start the standby database using pfile

$ oradim -new -sid ORCL -SYSPWD manager -pfile D:\app\oracle\product\11.2.0.4\db_1\database\initORCL.ora

$ sqlplus ‘/ as sysdba’
SQL> create spfile from pfile=’D:\app\oracle\product\11.2.0.4\db_1\database\INITorcl.ora’;
SQL> startup nomount

Step5:-Connect to the rman(Make sure the tnsnames.ora file has the primary & standby database entry in %ORACLE_HOME%\network\admin location)

$ rman target sys/manager@ORCL_PRIM auxiliary sys/manager@ORCL_STBY
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jun 27 00:57:14 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1507437661)
connected to auxiliary database: ORCL (not mounted)
RMAN> run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database nofilenamecheck;
}
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=44 device type=DISK
allocated channel: p2
channel p2: SID=28 device type=DISK
allocated channel: p3
channel p3: SID=48 device type=DISK
allocated channel: p4
channel p4: SID=47 device type=DISK
allocated channel: s1
channel s1: SID=134 device type=DISK
Starting Duplicate Db at 27-JUN-18
contents of Memory Script:
{
backup as copy reuse
targetfile '/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL' auxiliary format
'D:\app\oracle\product\11.2.0.4\db_1\DATABASE\PWDorcl.ORA' ;
}
executing Memory Script
Starting backup at 27-JUN-18
Finished backup at 27-JUN-18
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format 'D:\APP\ORACLE\ORADATA\ORCL_STBY\CONTROL01.CTL';
}
executing Memory Script
Starting backup at 27-JUN-18
channel p1: starting datafile copy
copying standby control file
output file name=/oradb/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_ORCL.f tag=TAG20180627T063140 RECID=3 STAMP=979885900
channel p1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27-JUN-18
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\TEMP01.DBF";
switch clone tempfile all;
set newname for datafile 1 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSTEM01.DBF";
set newname for datafile 2 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSAUX01.DBF";
set newname for datafile 3 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\UNDOTBS01.DBF";
set newname for datafile 4 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\USERS01.DBF";
set newname for datafile 5 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\EXAMPLE01.DBF";
backup as copy reuse
datafile 1 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSTEM01.DBF" datafile
2 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSAUX01.DBF" datafile
3 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\UNDOTBS01.DBF" datafile
4 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\USERS01.DBF" datafile
5 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\EXAMPLE01.DBF" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to D:\APP\ORACLE\ORADATA\ORCL_STBY\TEMP01.DBF in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 27-JUN-18
channel p1: starting datafile copy
input datafile file number=00003 name=/oradb/app/oracle/oradata/ORCL_PRIM/undotbs01.dbf
channel p2: starting datafile copy
input datafile file number=00001 name=/oradb/app/oracle/oradata/ORCL_PRIM/system01.dbf
channel p3: starting datafile copy
input datafile file number=00002 name=/oradb/app/oracle/oradata/ORCL_PRIM/sysaux01.dbf
channel p4: starting datafile copy
input datafile file number=00004 name=/oradb/app/oracle/oradata/ORCL_PRIM/users01.dbf
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\USERS01.DBF tag=TAG20180627T063150
channel p4: datafile copy complete, elapsed time: 00:02:22
channel p4: starting datafile copy
input datafile file number=00005 name=/oradb/app/oracle/oradata/ORCL_PRIM/example01.dbf
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSAUX01.DBF tag=TAG20180627T063150
channel p3: datafile copy complete, elapsed time: 00:03:03
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\EXAMPLE01.DBF tag=TAG20180627T063150
channel p4: datafile copy complete, elapsed time: 00:01:32
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSTEM01.DBF tag=TAG20180627T063150
channel p2: datafile copy complete, elapsed time: 00:04:27
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\UNDOTBS01.DBF tag=TAG20180627T063150
channel p1: datafile copy complete, elapsed time: 00:04:48
Finished backup at 27-JUN-18
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\EXAMPLE01.DBF
Finished Duplicate Db at 27-JUN-18
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1
RMAN> exit
Recovery Manager complete.

Step6:-Copy the control file to FRA

SQL> shut immediate
$ copy D:\app\oracle\oradata\ORCL_STBY\CONTROL01.CTL D:\app\oracle\fast_recovery_area\ORCL_STBY\CONTROL02.CTL
SQL> create pfile from spfile;

Change the control_files parameter value using any text editor in the pfile:

$ notepad D:\app\oracle\product\11.2.0.4\db_1\database\INITorcl.ora

From:-
*.control_files=’D:\app\oracle\oradata\ORCL_STBY\control01.ctl’

To:-
*.control_files=’D:\app\oracle\oradata\ORCL_STBY\control01.ctl’,’D:\app\oracle\fast_recovery_area\ORCL_STBY\CONTROL02.CTL’

Step7:-Startup the standby database and start the media recovery process

SQL> create spfile from pfile;
SQL> startup
SQL> alter database recover managed standby database using current logfile disconnect;

Now the Primary & Standby database status and roles:-

Primary:Standby:Now, check the current sequence of archive log in both primary & standby.

Primary:

SQL> archive log listStandby:

SQL> archive log listFrom the above two snaps from primary & standby has the same archive log sequence applied. Now, switch a redo log in primary and check the alert log in standby.

Primary:

SQL> alter system switch logfile;Standby:

SQL> @taln.sql 20Using the above script, we can see the alert log from sqlplus and from the above snap, the sequence 125 has been shipped from primary to standby.

 

Catch Me On:- Hariprasath Rajaram

LinkedIn:    https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:   https://www.facebook.com/HariPrasathdba
FB Group:   https://www.facebook.com/groups/894402327369506/
FB Page:      https://www.facebook.com/dbahariprasath/?
Twitter:       https://twitter.com/hariprasathdba