Step by Step how to switchover to physical standby


Switchover the database to physical standby


Primary DB Name: - PROD
Standby DB Name: - STDBY

Prerequisite for switchover to be successful

At least ensure the below before starting the switchover
 Primary and standby must be in sync
 Log shipping must be happening
 Listener parameter on both primary and standby must be up and running
There must not be any error in v$archive_dest_status error column
 No users should be connected (kill the session to optimize switchover time)
 If broker configuration is in place and you want to use SQL for switchover disable the broker configuration and set the db_broker_start parameter to False
 At least cross check the below parameters in both DC and DR and make sure they must have the expected and correct value

db_unique_name
service_names
fal_client
fal_server
db_file_name_convert
log_file_name_convert
log_archive_dest_1
log_archive_dest_2
db_recovery_file_dest
db_recovery_file_dest_size
standby_file_management
dg_broker_start
standby_archive_dest
log_archive_config

Execute the below steps On the Current Primary Database side (PROD)
Stop the Log Shipping from Primary Database

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER scope=spfile;
System altered.

Note: - You cannot proceed for the switchover if the below query output is not “SESSIONS ACTIVE” or “TO STANDBY”

select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
SESSIONS ACTIVE

If we get the "SESSIONS ACTIVE" status use the below query

alter database commit to switchover to physical standby with session shutdown;
Database altered.

 Shutdown immediate =====&; Don’t do this until the standby has received all the redo!
ORA-01507: database not mounted
ORACLE instance shut down.
Note: - At this point your Primary has been switched over

Execute the below steps on the current Standby Database Side (STDBY)

select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
NOT ALLOWED
Wait for some more time, and query again, we should receive status like “TO PRIMARY"
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
TO PRIMARY
Note: if we waited 5 mins, still if status is not changing , issue the following command.
alter database commit to switchover to primary with session shutdown;
If you are getting the status TO PRIMARY you can use the below command to switchover the standby to primary
alter database commit to switchover to primary;
Database altered.
shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.
Startup the new Standby( ie Original primary PROD)

Execute the below commands on PROD database ie Original Primary

startup nomount
ORACLE instance started.
Total System Global Area 4300184864 bytes
Fixed Size                   736544 bytes
Variable Size            4261412864 bytes
Database Buffers           16777216 bytes
Redo Buffers               21258240 bytes
alter system set log_archive_dest_state_2=DEFER scope=both;
System altered.
alter database mount standby database;
Database altered.
alter database recover managed standby database disconnect from session;
Database altered.
SQL> alter database recover managed standby database cancel;
Database altered.

If required Add the standby logfiles

alter database add standby logfile '/u02/oradata /srl01.log' size 50M;
alter database add standby logfile '/u02/oradata /srl02.log' size 50M;
alter database add standby logfile '/u02/oradata /srl03.log' size 50M;
alter database add standby logfile '/u02/oradata /srl04.log' size 50M;
SQL> alter database recover managed standby database disconnect from session;
Database altered.

Execute the below commands on STDBY database ie Original Standby

Startup the new Primary
startup
ORACLE instance started.
Total System Global Area 4300184864 bytes
Fixed Size                   736544 bytes
Variable Size            4261412864 bytes
Database Buffers           16777216 bytes
Redo Buffers               21258240 bytes
Database mounted.
Database opened.

Enable Log Shipping and Switch Logfile to Check Shipping is happening:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SWITCH LOGFILE;


HOW TO CONVERT PHYSICAL STANDBY DATABASE TO SNAPSHOT STANDBY DATABASE

CONVERTING PHYSICAL STANDBY DATABASE TO SNAPSHOT STANDBY DATABASE


All the below steps will be carried out on Physical standby.
Step 1: Check if Flashback is enabled. If not, enable it.

a) SQL> Show parameter db_recovery_file_dest
NAME TYPE VALUE
--------------------------------------------------------------- ----------- ------------------------------
db_recovery_file_dest string  
db_recovery_file_dest_size big integer 20G
Above two parameters are mandatory to enable the flashback. Set them if they are not set

b) SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
The flashback is not enabled so let us enable it first

c)  SQL> recover managed standby database cancel;

d) SQL> Shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

e) SQL> startup mount;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 788524 bytes
Variable Size 162887636 bytes
Database Buffers 41943040 bytes
Redo Buffers 4096000 bytes
Database mounted.

f) SQL> alter database flashback on;
Database altered.

g) SQL> select flashback_on from v$database;
FLASHBACK
---------
YES

h) SQL> alter database open;
Database altered.

i) SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME OPEN_MODE GUARD_S DATABASE_ROLE
---------------------------------------- ---------- ------- ----------------
OLPTP1 READ ONLY NONE PHYSICAL STANDBY
Step 2 : Cancel recovery  on Physical Standby Database if it is running,

SQL> alter database recover managed standby database cancel;
Database altered.
Step 3 : Converting Physical Standby database to Snapshot Standby database

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.
Step 4: Shutdown the Standby Database Normal

SQL> shut immediate
ORACLE instance shut down.
Step 5: Startup Standby database Normal

SQL> Startup
ORACLE instance started.
Total System Global Area 4175568896 bytes
Fixed Size 2166288 bytes
Variable Size 2852127216 bytes
Database Buffers 1308622848 bytes
Redo Buffers 12652544 bytes
Database mounted.
Database opened.

Step 6: Check Database role

SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME OPEN_MODE GUARD_S DATABASE_ROLE
-------------------------------------- ---------- ------- -------------------------------
OLPTP1 READ WRITE NONE SNAPSHOT STANDBY

At this point your physical standby became primary. Database is in read/write mode enjoyJ
Now if you want to revert back it again to standby configuration follow the below steps.

CONVERTING SNAPSHOT STANDBY DATABASE TO PHYSICAL STANDBY DATABASE

Step 1: Check for current database role

SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME OPEN_MODE GUARD_S DATABASE_ROLE
----------------------------------------- ---------- ------- ----------------
OLPTP1 READ WRITE NONE SNAPSHOT STANDBY

Step 2 : Shutdown Snapshot Standby Database

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 3 : Start in Nomount mode

SQL> Startup nomount
ORACLE instance started.
Total System Global Area 4175568896 bytes
Fixed Size 2166288 bytes
Variable Size 2852127216 bytes
Database Buffers 1308622848 bytes
Redo Buffers 12652544 bytes

Step 4: Mounting Snapshot Standby Database

SQL> alter database mount;
Database altered.

Step 5: Converting Snapshot Standby to Physical Standby Database

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.

Step 6: Shutdown the database

SQL> shut immediate
ORACLE instance shut down.

Step 7: Starting database in nomount mode

SQL> startup nomount
ORACLE instance started.
Total System Global Area 4175568896 bytes
Fixed Size 2166288 bytes
Variable Size 2852127216 bytes
Database Buffers 1308622848 bytes
Redo Buffers 12652544 bytes

SQL> alter database mount standby database;
Database altered.

Step 8 : start the media recovery

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

Note:- Do some log switch in production and verify whether the log shipping /log apply is progressing at DR

SQL> select process, status ,sequence# from v$managed_standby;
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
MRP0      WAIT_FOR_LOG         16

Shows that archives apply is under process as we are recovering standby database
(IMPORTANT NOTE : RFS - implies redo transfer from primary to standby is under process.
MRP0 - indicates it is applying archive logs in standby)

Step 10: Check in primary database for any error

SQL>select error from v$archived_dest;

Step 12 : Check for database role

SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME OPEN_MODE GUARD_S DATABASE_ROLE
--------------------------------- ---------- ------- ----------------
OLPTP1 READ ONLY NONE PHYSICAL STANDBY

Step by Step Converting a Failed Primary Into a Standby Database Using Flashback Database

Step by Step Converting a Failed Primary Into a Standby Database Using Flashback Database



On Primary

Flashback must enable on the primary database to bring back the failed primary database in the data guard configuration without rebuilding it. Also minimum flashback logs must be available to flashback the failed primary database to a SCN (ie standby_became_primary_scn;)

SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APXITR2   READ WRITE           PRIMARY
SQL> show parameter recovery
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /apxrdbms/product/oracle/flash_recovery_area                                            
db_recovery_file_dest_size           big integer 20G
If db_recovery_file_des and db_recovery_file_dest_size   is not set follow the below step to set it.
SQL> alter system set db_recovery_file_dest_size=10G;
SQL> alter system set db_recovery_file_dest =< some location where the flashback logs will be created>
Check whether the flashback is enabled or not. And enable the flashback if it not enabled
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
Enable the flashback at database level
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 2154590208 bytes
Fixed Size                  2215104 bytes
Variable Size             872416064 bytes
Database Buffers         1258291200 bytes
Redo Buffers               21667840 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> alter database open;
Database altered.
SQL> alter system switch logfile;
System altered.
(Note:-Before simulating the failover makes sure your primary and physical standby is in sync to avoid any data loss. In real scenarios we don’t have manual control on this step as the failover can occur at any time. And we need not to do the failover manually)
SQL> shut abort
ORACLE instance shut down.

ON PHYSICAL STANDBY
On physical standby database check the media recovery is going on , if not start it and apply the maximum possible archives.
SQL>  recover managed standby database disconnect from session;
Media recovery complete.
SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APXITR2   MOUNTED              PHYSICAL STANDBY
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
Database altered.
SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APXITR2   MOUNTED              PRIMARY
Note :- if you check the alertlog of standby database you will get a line like this "Standby became primary SCN: 7497799799226"
SQL>  ALTER DATABASE OPEN;
Database altered.
SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APXITR2   READ WRITE           PRIMARY
Note:- At this point your physical standby database has become primary and the original primary is no longer available
Now start your application from DR site and enjoy….Let us after 10Days you got your primary back and you want it bring it back in DC/DR configuration without rebuilding it. Just follow the below steps.
Identify the STANDBY_BECAME_PRIMARY_SCN from the current primary database  and note the SCN
SQL>  SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
7497799799226

On the Old primary database (the original primary database that was failed)

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 2154590208 bytes
Fixed Size                  2215104 bytes
Variable Size             872416064 bytes
Database Buffers         1258291200 bytes
Redo Buffers               21667840 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO SCN 7497799799226;ßThis is the SCN that you got in the above step
Flashback complete.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL> SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 2154590208 bytes
Fixed Size                  2215104 bytes
Variable Size             872416064 bytes
Database Buffers         1258291200 bytes
Redo Buffers               21667840 bytes
Database mounted.
SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
APXITR2   MOUNTED              PHYSICAL STANDBY
You are done. The failed primary now successfully became the standby of the current primary. Now configure the log shipping from the current primary to the current standby and start the media recovery on the standby.
SQL>  recover managed standby database disconnect from session;
Media recovery complete.
Check the log progress on the standby.
select process,sequence#,status from v$managed_standby;
PROCESS    SEQUENCE# STATUS
--------- ---------- ------------
ARCH               7 CLOSING
ARCH               0 CONNECTED
ARCH               0 CONNECTED
ARCH               0 CONNECTED
MRP0               8 WAIT_FOR_LOG
RFS                0 IDLE
RFS                0 IDLE
RFS                0 IDLE
RFS                8 IDLE

At this point of time your original primary is in standby mode and the original standby is in primary mode.
If you want to bring the original primary as a PRIMARY and the original standby as STANDBY only do a switchover.
Steps to perform the switchover will be covered in the next post….Enjoy JJ

Leave your questions/comments here. I will be happy to answer your queries


SQL Script to Create the Database

SQL Script to Create the Database


Basic environment Information that I have
ORACLE_HOME==è/u01/app/oracle/product/11.2.0/db_1
ORACLE_SID=orcl  ==èI will use
Database Name =è orcl
Mount point name /u01/oradata &  /u02/oradata

1.  Set ORACLE_SID & ORACLE_HOME
export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin

2.  Create a init.ora file
Init.ora file location is /u01/app/oracle/product/11.2.0/db_1/dbs so create the file here
cd /u01/app/oracle/product/11.2.0/db_1/dbs
cat initorcl.ora
control_files = (/u01/oradata/control1.ctl,/u01/oradata/control2.ctl,/u01/oradata/control3.ctl)
undo_management = AUTO
undo_tablespace = UNDOTBS1
db_name = orcl
db_block_size = 8192
sga_max_size = 1073741824 #one gig
sga_target = 1073741824 #one gig
#sga_max_size and sga_target is directly related to the RAM size put the value suitable for your environment

3. Create a password file
$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/pwdorcl.ora password=oracle entries=5

4.  Start the instance
sqlplus / as sysdba
startup nomount

5.  Create the database
create database orcl
logfile group 1 ('/u02/oradata/redo1.log') size 100M,
            group 2 ('/u02/oradata/redo2.log') size 100M,
            group 3 ('/u02/oradata/redo3.log') size 100M
character set WE8ISO8859P1
national character set utf8
datafile '/u02/oradata/system.dbf' size 500M autoextend on next 10M maxsize unlimited extent management local
sysaux datafile '/u02/oradata/sysaux.dbf' size 100M autoextend on next 10M maxsize unlimited
undo tablespace undotbs1 datafile '/u02/oradata/undotbs1.dbf' size 100M
default temporary tablespace temp tempfile '/u02/oradata/temp01.dbf' size 100M;

Attention

#This is a very basic script
Note: there's some other things you can do here,eg. you can set  "ARCHIVELOG" "SET TIME_ZONE =" and "USER SYS IDENTIFIED BY password" and "USER SYSTEM IDENTIFIED BY password"  etc

# Make sure have proper permission on the mount points (/u01/oradata & /u02/oradata in my case) and sufficient space to hold the datafiles


6.  After successfully executing the above script Run catalog and catproc

SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql

==================================================================
Another create database script sample having something more for you

CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u02/oradata/log1a.dbf',
'/u02/oradata/log1b.dbf') SIZE 30M,
GROUP 2 ('/u02/oradata/log2a.dbf',
'/u02/oradata/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/system01.dbf',
'/u01/oradata/mydatabase.dbf'
;

======================================================================
Leave your comment .......