28 | 03 | 2024
Latest Articles
Popular Articles

Data Guard

Failover to physical standby database using Oracle Standard Edition

User Rating:  / 3
PoorBest 

Failover to physical standby database using Oracle Standard Edition (10.2.0.4)

 

See also Switchover from primary database to physical standby database using Oracle Standard Edition

 

Failover to host2 ( db_unique_name = MOERZEKE )


Physical standby database and primary database are up and running

Database file location is identical on both sites, ie same mount points, directories, file names.

Flash recovery area is used for the archived redo logs

Physical standby database runs in recovery mode in background with

 

SQL > recover managed standby database disconnect from session;

 

STEP 1 : suspend background recovery on the standby database server


SQL > recover managed standby database cancel;


###############################################################################
Waiting for MRP0 pid 5373 to terminate
Wed Nov 19 10:30:06 2014
Errors in file /u01/app/oracle/product/10.2.0/db_1/rdbms/log/drp_mrp0_5373.trc:
ORA-16037: user requested cancel of managed recovery operation
Wed Nov 19 10:30:06 2014
MRP0: Background Media Recovery process shutdown (DRP)
Wed Nov 19 10:30:06 2014
Managed Standby Recovery Canceled (DRP)
Completed: ALTER DATABASE RECOVER managed standby database cancel
###############################################################################


STEP 2: Initiate a few transactions and a log switch at level of the standby database


This is done here only for testing purposes, in real life the failover will normally only occure when the primary database is unaccessible.


SQL > connect DRP/DRP
Connected.

SQL > begin
2 for i in 1 .. 100000 loop
3 insert into T values (S.nextval,'DRP');
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL > commit;

Commit complete.

 

SQL > conn / as sysdba
Connected.

SQL > alter system archive log current;

System altered.

 

SQL > select count(*) from DRP.T;COUNT(*)
----------
941100

 

SQL > select RESETLOGS_TIME, ACTIVATION#, STANDBY_BECAME_PRIMARY_SCN from v$database;

RESETLOGS_TIME   ACTIVATION# STANDBY_BECAME_PRIMARY_SCN
---------------- ----------- --------------------------
21/10/2014 10:09 4250516847  0

 

STEP 3 : Copy over remanining archived redo logs to standby side


In real life this is only possile when the storage on which the primary database server archived redo logs are put is still accessible


[oracle@host2 2014_11_19]$ scp This email address is being protected from spambots. You need JavaScript enabled to view it. :/u01/flash_recovery_area/BRUSSELS/archivelog/2014_11_19/*.arc .
This email address is being protected from spambots. You need JavaScript enabled to view it. ';s password:
o1_mf_1_84_b6rs2q72_.arc 100% 9531KB 9.3MB/s 00:00
o1_mf_1_85_b6rsk03s_.arc 100% 48MB 47.9MB/s 00:01
o1_mf_1_86_b6rskh6v_.arc 100% 48MB 47.9MB/s 00:01
o1_mf_1_87_b6rsky5m_.arc 100% 48MB 23.9MB/s 00:02
o1_mf_1_88_b6rsno1x_.arc 100% 14MB 13.6MB/s 00:00

RMAN> catalog recovery area noprompt;

using target database control file instead of recovery catalog
searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_11_19/o1_mf_1_87_b6rsky5m_.arc
File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_11_19/o1_mf_1_88_b6rsno1x_.arc
File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_11_19/o1_mf_1_85_b6rsk03s_.arc
File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_11_19/o1_mf_1_86_b6rskh6v_.arc
File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_11_19/o1_mf_1_84_b6rs2q72_.arc
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_11_19/o1_mf_1_87_b6rsky5m_.arc
File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_11_19/o1_mf_1_88_b6rsno1x_.arc
File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_11_19/o1_mf_1_85_b6rsk03s_.arc
File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_11_19/o1_mf_1_86_b6rskh6v_.arc
File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_11_19/o1_mf_1_84_b6rs2q72_.arc

 


STEP 4 : On the standby site resume recovery of newest archived redo logs ( all what is possible )

 

SQL > recover managed standby database disconnect from session;

 

#################################################################################################
ALTER DATABASE RECOVER managed standby database
Wed Nov 19 10:55:01 2014
Media Recovery Start: Managed Standby Recovery (DRP)
Wed Nov 19 10:55:01 2014
Managed Standby Recovery not using Real Time Apply
Media Recovery Log /u01/flash_recovery_area/MOERZEKE/archivelog/2014_11_19/o1_mf_1_84_b6rs2q72_.arc
Media Recovery Log /u01/flash_recovery_area/MOERZEKE/archivelog/2014_11_19/o1_mf_1_85_b6rsk03s_.arc
Media Recovery Log /u01/flash_recovery_area/MOERZEKE/archivelog/2014_11_19/o1_mf_1_86_b6rskh6v_.arc
Media Recovery Log /u01/flash_recovery_area/MOERZEKE/archivelog/2014_11_19/o1_mf_1_87_b6rsky5m_.arc
Media Recovery Log /u01/flash_recovery_area/MOERZEKE/archivelog/2014_11_19/o1_mf_1_88_b6rsno1x_.arc
Media Recovery Waiting for thread 1 sequence 89
#################################################################################################


STEP 5 : Failover to the standby site


SQL > recover managed standby database cancel;

Media recovery complete.

#################################################################################################
ALTER DATABASE RECOVER managed standby database cancel
Wed Nov 19 10:56:49 2014
Recovery interrupted!
Wed Nov 19 10:56:49 2014
Managed Standby Recovery Canceled (DRP)
Wed Nov 19 10:56:49 2014
Completed: ALTER DATABASE RECOVER managed standby database cancel
Wed Nov 19 10:56:51 2014
Media Recovery user canceled with status 16037
ORA-16043 signalled during: ALTER DATABASE RECOVER managed standby database ...
#################################################################################################


SQL > alter database recover managed standby database finish force;

Database altered.

 

#################################################################################################
Identified End-Of-Redo for thread 1 sequence 89
Terminal Recovery: Updated next available block for thread 1 sequence 89 lno 4 to value 2
Wed Nov 19 10:59:16 2014
Incomplete recovery applied all redo ever generated.
Recovery completed through change 1270307
Wed Nov 19 10:59:16 2014
Media Recovery Complete (DRP)
Terminal Recovery: successful completion
Begin: Standby Redo Logfile archival
Wed Nov 19 10:59:16 2014
RFS LogMiner: Client disabled from further notification
Wed Nov 19 10:59:19 2014
End: Standby Redo Logfile archival
Resetting standby activation ID 4250516847 (0xfd59bd6f)
Completed: alter database recover managed standby database finish force
#################################################################################################


SQL > alter database commit to switchover to primary;

Database altered.

SQL > select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL > alter database open;

Database altered.

#################################################################################################
Wed Nov 19 11:01:14 2014
alter database commit to switchover to primary
Wed Nov 19 11:01:14 2014
ALTER DATABASE SWITCHOVER TO PRIMARY (DRP)
Wed Nov 19 11:01:14 2014
If media recovery active, switchover will wait 900 seconds
Standby terminal recovery start SCN: 1270306
RESETLOGS after complete recovery through change 1270307
Wed Nov 19 11:01:14 2014
Errors in file /u01/app/oracle/product/10.2.0/db_1/rdbms/log/drp_ora_17727.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/DRP/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Nov 19 11:01:15 2014
Errors in file /u01/app/oracle/product/10.2.0/db_1/rdbms/log/drp_ora_17727.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oradata/DRP/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Nov 19 11:01:16 2014
Errors in file /u01/app/oracle/product/10.2.0/db_1/rdbms/log/drp_ora_17727.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/DRP/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Standby became primary SCN: 1270305
Wed Nov 19 11:01:16 2014
Setting recovery target incarnation to 2
Wed Nov 19 11:01:16 2014
WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is
not set to the value "AUTO".
This may cause recovery of the standby database to terminate
prior to applying all available redo data.
It may be necessary to use the ALTER DATABASE CREATE DATAFILE
command to add datafiles created on the primary database.
Converting standby mount to primary mount.
Wed Nov 19 11:01:16 2014
Switchover: Complete - Database mounted as primary (DRP)
Completed: alter database commit to switchover to primary
Wed Nov 19 11:01:16 2014
ARC1: STARTING ARCH PROCESSES
ARC2: Archival started
ARC1: STARTING ARCH PROCESSES COMPLETE
Wed Nov 19 11:04:57 2014
ARC0: Becoming the 'no SRL' ARCH
Wed Nov 19 11:06:15 2014
alter database open
Wed Nov 19 11:06:15 2014
Assigning activation ID 4251103331 (0xfd62b063)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /u01/oradata/DRP/redo01.log
Successful open of redo thread 1
Wed Nov 19 11:06:15 2014
SMON: enabling cache recovery
Wed Nov 19 11:06:19 2014
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Dictionary check complete
Wed Nov 19 11:06:19 2014
SMON: enabling tx recovery
Wed Nov 19 11:06:19 2014
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=17, OS id=17762
Wed Nov 19 11:06:25 2014
LOGSTDBY: Validating controlfile with logical metadata
Wed Nov 19 11:06:25 2014
LOGSTDBY: Validation complete
Completed: alter database open
Wed Nov 19 11:06:34 2014
Shutting down archive processes
Wed Nov 19 11:06:39 2014
ARCH shutting down
ARC2: Archival stopped

#################################################################################################


STEP 6 : Check the current status of the database


SQL > select count(*) from DRP.T;

COUNT(*)
----------
941100

SQL > alter session set nls_date_format='DD/MM/YYYY HH24:Mi';

Session altered.

 

SQL > select CURRENT_SCN,RESETLOGS_TIME, ACTIVATION#, STANDBY_BECAME_PRIMARY_SCN from v$database;

CURRENT_SCN RESETLOGS_TIME   ACTIVATION# STANDBY_BECAME_PRIMARY_SCN
----------- ---------------- ----------- --------------------------
1272126     19/11/2014 11:01 4251103331  1270305

 

STEP 7 : Review your backup strategy, redo transmission


If not yet done

1. start with taking backups of the new primary database

2. rebuild the failed primary database site