Tag Archives: failover standby database

Failover with DG Broker

Failover with DG Broker (No-Flashback)

Overview:

Failover is an irreversible transition of a standby database to the primary role.
This is only done in the event of a catastrophic failure of the primary database

0. Enviroment
1. Primary Database Crashed
2. Steps to perform Failover with Physical Standby database
3. Reinstate database old primary (w5005pr)
4. Rebuild Standby for new primary
5. Enable old primary database (w5005pr)

Note: Flashback database is not enabled on both primary and standby (in our case)

Do you need to recreate all the Data Guard infrastructure once the failover has been performed?

Ans:

1. If flashback enabled on both primary and standby and flashback logs retention time has not exceeded, then it is not necessary to recreate standby from scratch.
2. If flashback not enabled on both primary and standby then we need to recreate standby from scratch.


0. Enviroment

Primary:

Platform: Linuxx86_64
Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
DB Version: Oracle 11.2.0.3, File system: ASM
DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr; Non-RAC
Flashback: Disabled
Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1

Standby:

Platform: Linuxx86_64
Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
DB Version: Oracle 11.2.0.3, , File system: ASM
DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg; Non-RAC
Flashback: Disabled
Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1


1. Primary Database Crashed

DGMGRL> show configuration;

Configuration - w5005pr

Protection Mode: MaxPerformance
Databases:
w5005pr - Primary database
w5005prg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>


[oracle@rac1 ~]$ ps -ef | grep pmon | grep -v grep
oracle 3540 1 0 Jan11 ? 00:00:02 asm_pmon_+ASM
[oracle@rac1 ~]$


Tue Jan 12 00:00:22 2016
CKPT (ospid: 2499): terminating the instance due to error 472
Tue Jan 12 00:00:23 2016
License high water mark = 5
Instance terminated by CKPT, pid = 2499
USER (ospid: 3029): terminating the instance
Instance terminated by USER, pid = 3029 <---- Instance terminated 


2. Steps to perform Failover with Physical Standby database

[oracle@rac2 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys@w5005prg
Connected.
DGMGRL> show configuration;

Configuration - w5005pr

Protection Mode: MaxPerformance
Databases:
w5005pr - Primary database
w5005prg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-16625: cannot reach database "w5005pr"
DGM-17017: unable to determine configuration status

DGMGRL>
DGMGRL> failover to w5005prg;
Performing failover NOW, please wait...
Failover succeeded, new primary is "w5005prg" <-- Succeeded
DGMGRL>
DGMGRL> show configuration

Configuration - w5005pr

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

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>


3. Reinstate database old primary

DGMGRL> reinstate database w5005pr;
Reinstating database "w5005pr", please wait...
Error: ORA-16653: failed to reinstate database

Failed.
Reinstatement of database "w5005pr" failed
DGMGRL>

/*
Failing it would have needed to configure flashback database to be able to do so automatically…

If you try to start w5005pr database in MOUNT state and issue again a request to reinstate you will now get clear error message
*/


[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 12 00:05:33 2016

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size 2227984 bytes
Variable Size 1090519280 bytes
Database Buffers 167772160 bytes
Redo Buffers 8847360 bytes
Database mounted.
SQL>


DGMGRL> reinstate database w5005pr;
Reinstating database "w5005pr", please wait...
Error: ORA-16827: Flashback Database is disabled <-- Actual error

Failed.
Reinstatement of database "w5005pr" failed
DGMGRL>

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size 2227984 bytes
Variable Size 1090519280 bytes
Database Buffers 167772160 bytes
Redo Buffers 8847360 bytes
SQL>


4. Rebuild Standby database for new primary (w5005prg)

[oracle@rac2 ~]$ rman target sys/sys@w5005prg auxiliary sys/sys@w5005pr

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 12 00:22:53 2016

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

connected to target database: W5005PR (DBID=4158252169)
connected to auxiliary database: W5005PR (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 12-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=24 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapww5005prg' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapww5005pr' ;
}
executing Memory Script

Starting backup at 12-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=108 device type=DISK
Finished backup at 12-JAN-16

contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/w5005pr/controlfile/current.268.900456457'' comment=
''Set by RMAN'' scope=spfile";
backup as copy current controlfile for standby auxiliary format '+DATA/w5005pr/controlfile/current.268.900456457';
sql clone "alter system set control_files =
''+DATA/w5005pr/controlfile/current.268.900456457'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set control_files = ''+DATA/w5005pr/controlfile/current.268.900456457'' comment= ''Set by RMAN'' scope=spfile

Starting backup at 12-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_w5005prg.f tag=TAG20160112T002322 RECID=5 STAMP=900894203
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-JAN-16

sql statement: alter system set control_files = ''+DATA/w5005pr/controlfile/current.268.900456457'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1269366784 bytes

Fixed Size 2227984 bytes
Variable Size 1090519280 bytes
Database Buffers 167772160 bytes
Redo Buffers 8847360 bytes

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;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 12-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/w5005prg/datafile/system.264.900546429
output file name=+DATA/w5005pr/datafile/system.274.900894219 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/w5005prg/datafile/sysaux.265.900546465
output file name=+DATA/w5005pr/datafile/sysaux.275.900894265 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/w5005prg/datafile/undotbs1.266.900546499
output file name=+DATA/w5005pr/datafile/undotbs1.276.900894299 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/w5005prg/datafile/users.267.900546507
output file name=+DATA/w5005pr/datafile/users.277.900894303 tag=TAG20160112T002338
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-JAN-16

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=5 STAMP=900894305 file name=+DATA/w5005pr/datafile/system.274.900894219
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=900894305 file name=+DATA/w5005pr/datafile/sysaux.275.900894265
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=900894305 file name=+DATA/w5005pr/datafile/undotbs1.276.900894299
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=900894305 file name=+DATA/w5005pr/datafile/users.277.900894303
Finished Duplicate Db at 12-JAN-16

RMAN> exit


Recovery Manager complete.
[oracle@rac2 ~]$


5. Enable old primary database

[oracle@rac2 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys@w5005prg
Connected.
DGMGRL> show configuration;

Configuration - w5005pr

Protection Mode: MaxPerformance
Databases:
w5005prg - Primary database
w5005pr - Physical standby database (disabled)
ORA-16795: the standby database needs to be re-created

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>
DGMGRL> enable database w5005pr;
Enabled.
DGMGRL> show configuration;

Configuration - w5005pr

Protection Mode: MaxPerformance
Databases:
w5005prg - Primary database
w5005pr - Physical standby database <-- Old Primary

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.