Swicthover Steps
Ensure :
--------
•The standby database to become the new primary database must be placed in archivelog mode.
•No users must be connected Use v$session to verify
•Redo application must not have a delay
Old Primary / New Standby :
Check Switchover possiblity :
select switchover_status from V$database;
---If switchover_status shows ‘sessions active’ then run
select sid, process, program from v$session where type='user' and sid <>(select distinct sid from V$mystat);
If there are rows returned, contact customers and ask if the proceses can be killed. If yes go to step v. Otherwise stop here until they log out.
---If switchover_status shows ‘TO_STANDBY’ continue
Switchover to change to standby , mount standby and start managed recover :
alter database commit to switchover to physical standby with session shutdown
shutdown immediate
startup nomount
LOG_ARCHIVE_DEST destinations were left as it is but their states were changed to DEFER
ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_3='DEFER' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_4='DEFER' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_5='DEFER' SCOPE=BOTH;
ALTER SYSTEM SET standby_archive_dest='/global/taabdb/archieve/oracle/oradata/taabdb/standbyarchivelogs' SCOPE=BOTH;
alter database mount standby database
alter database recover managed standby database disconnect from session
You should see messages like ...Media Recovery Log /global/taabdb/archieve/oracle/oradata/taabdb/standbyarchivelogs/arch_1_86956.arc
Old Standby / New Primary :
Switchover to change to Primary and Start :
alter database commit to switchover to primary
shutdown immediate
startup
ALTER SYSTEM SET log_archive_dest_2='SERVICE=taabDB_PROD' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_3='SERVICE=taabDB3' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_4='SERVICE=taabDB5' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_4='ENABLE' SCOPE=BOTH;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata01/datafiles/taabdb4/temp01.dbf' SIZE 1024M AUTOEXTEND ON NEXT 512M MAXSIZE 4096M
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata01/datafiles/taabdb4/temp01.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 512M MAXSIZE 4096M
ALTER TABLESPACE CEI_TS_TEMP ADD TEMPFILE '/oradata02/datafiles/taabdb4/cei_ts_temp.dbf' SIZE 256M AUTOEXTEND ON NEXT 64M MAXSIZE 4096M
Perform a log switch : ALTER SYSTEM ARCHIVE LOG CURRENT;
Failover Steps
Perform only if Primary is destroyed or not functional
On Standby :
alter database set standby database to maximize performance;
2. Register archived Logs : Identify ,register and resolve any gaps in the archived redo logfiles.
-- select thread#, low_sequence#, high_ sequence# from v$archive_gap
If there are any redo log gaps (see v$archive_gap), copy them from another standby database and register them:
alter database register physical logfile 'some logfile name';
Find other missing archived redo logs (v$archived_log).
select unique thread#, max(sequence#) over (partition by thread#) as last from v$archived_log;
Copy these archived redo logs from other databases and register them as well. Now, there might still be gaps. So find them and repeat.
Also consult the end of the alert.log if there is an instruction that goes like: Latent archivelog 'e:\some\path\ARC00108.001'
If you wish to failover to this standby databse, you should use the
following command to manually register the archivelog for recovery
ALTER DATABASE REGISTER LOGFILE 'e:\some\path\ARC00108.001';
It's the same activity that we are doing here....
3. Finish Recovery : If the target standby database was configured with standby redo logs and there are no manually registered partial archived redo logs:
alter database recover managed standby database finish;
Otherwise:
alter database recover managed standby database finish skip standby logfile;
4. Switchover and Start : Now, transform the standby database to the primary role:
alter database commit to switchover to primary;
--- alter database open;
--- shutdown immediate;
--- startup;
After the standby failover is done, Use the Flashback to immediately convert the old failed Primary to Standby DB ( 10g onwards)
Find the SCN at which the old standby database became the new primary database:
Select to_char(standby_became_primary_scn) from v$database;
Convert the failed primary DB to the physical standby database.
shutdown immediate;
startup mount;
flashback database to scn <SCN from Step 1>;
alter database convert to physical standby;
shutdown immediate;
startup mount;
alter database recover managed standby database using current logfile disconnect;
