Data Guard

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

User Rating:

/ 2

Details: Parent Category: Articles; Created on Monday, 10 November 2014 13:37; Last Updated on Wednesday, 19 November 2014 13:02; Published on Monday, 10 November 2014 14:33; Written by Guy Lambregts; Hits: 8293

Switchover from primary database to physical standby database using Oracle Standard Edition ( 10.2.0.4 )

Planned switchover from host1 ( db_unique_name = BRUSSELS ) 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 : Simulate primary database transactions

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> 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.

STEP 2 : Shutdown immediate the original primary database

SQL> conn / as sysdba Connected.

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

STEP 3 : Copy over archived redo logs from primary database to flash recovery area of standby database

Use "scp" or similar

[[email protected] archivelog]$ scp This email address is being protected from spambots. You need JavaScript enabled to view it. :/u01/flash_recovery_area/BRUSSELS/archivelog/2014_10_21/* . This email address is being protected from spambots. You need JavaScript enabled to view it. ';s password: o1_mf_1_10_b4d9hgb8_.arc 100% 2189KB 2.1MB/s 00:00 o1_mf_1_11_b4d9pcpz_.arc 100% 1024 1.0KB/s 00:00 o1_mf_1_12_b4d9q9kx_.arc 100% 2048 2.0KB/s 00:00 o1_mf_1_13_b4dohryb_.arc 100% 5522KB 5.4MB/s 00:00 o1_mf_1_14_b4dp3fg9_.arc 100% 48MB 47.9MB/s 00:01 o1_mf_1_15_b4dp3jvb_.arc 100% 48MB 47.9MB/s 00:01 o1_mf_1_16_b4dp3q1d_.arc 100% 48MB 23.9MB/s 00:02 o1_mf_1_17_b4dpfd9m_.arc 100% 48MB 47.9MB/s 00:01 o1_mf_1_18_b4dpfk5q_.arc 100% 48MB 47.9MB/s 00:01 o1_mf_1_19_b4dpfp5w_.arc 100% 48MB 23.9MB/s 00:02

STEP 4 : Catalog the new archived redo logs into the control file

[[email protected] archivelog]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Oct 21 15:04:16 2014

connected to target database: DRP (DBID=4250564207, not open)

RMAN> catalog recovery area;

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/o1_mf_1_18_b4dpfk5q_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/o1_mf_1_19_b4dpfp5w_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/o1_mf_1_17_b4dpfd9m_.arc

Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done

List of Cataloged Files ======================= File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/o1_mf_1_18_b4dpfk5q_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/o1_mf_1_19_b4dpfp5w_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/o1_mf_1_17_b4dpfd9m_.arc

This command avoids the prompt and is more interesting to launch in background

RMAN> catalog recovery area noprompt;

searching for all files in the recovery area no files found to be unknown to the database

RMAN> exit

Recovery Manager complete.

As soon as the archived redo logs are catalogged they are applied, monitor this in the alert file

STEP 5 : Suspend recovery and shutdown immediate physical standby database

[[email protected] DRP]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Oct 21 15:11:35 2014

SQL> recover managed standby database cancel; Media recovery complete.

SQL> exit Disconnected from Oracle Database 10g Release 10.2.0.4.0 - 64bit Production [[email protected] DRP]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Oct 21 15:12:21 2014

SQL> shutdown immediate; ORA-01109: database not openDatabase dismounted. ORACLE instance shut down.

SQL> exit Disconnected from Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

STEP 6 : Rename the current control files and rename the current redo logs files

[[email protected] DRP]$ mv control01.ctl control01.ctl.standby

[[email protected] DRP]$ mv control02.ctl control02.ctl.standby

STEP 7 : Copy over the primary database control file and copy over the primary database redo log files

[[email protected] DRP]$ scp This email address is being protected from spambots. You need JavaScript enabled to view it. :/u01/oradata/DRP/*ctl . This email address is being protected from spambots. You need JavaScript enabled to view it. ';s password: control01.ctl 100% 6896KB 6.7MB/s 00:00 control02.ctl 100% 6896KB 6.7MB/s 00:01

[[email protected] DRP]$ scp This email address is being protected from spambots. You need JavaScript enabled to view it. :/u01/oradata/DRP/redo* . This email address is being protected from spambots. You need JavaScript enabled to view it. ';s password: redo01.log 100% 50MB 50.0MB/s 00:01 redo02.log 100% 50MB 50.0MB/s 00:01 redo03.log 100% 50MB 25.0MB/s 00:02

STEP 8 : Startup mount the physical standby database and do final complete recovery

[[email protected] DRP]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Oct 21 15:14:04 2014

Connected to an idle instance.

SQL> startup mount; ORACLE instance started.

Total System Global Area 524288000 bytes Fixed Size 2085192 bytes Variable Size 150998712 bytes Database Buffers 364904448 bytes Redo Buffers 6299648 bytes Database mounted.

SQL> select database_role from v$database;

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

Check the database redo log files ( should match with the ones copied over from primary )

SQL> select member from v$logfile;

MEMBER -------------------------------------------------------------------------------- /u01/oradata/DRP/redo01.log /u01/oradata/DRP/redo02.log /u01/oradata/DRP/redo03.log

SQL> recover database; -- complete recovery

Media recovery complete.

STEP 9 : Check the database tempfile and open the database

SQL> select name from v$tempfile;

NAME -------------------------------------------------------------------------------- /u01/oradata/DRP/temp01.dbf

Open the database

SQL> alter database open;

Database altered.

Switch back to the original primary database ( assuming a limited amount of time and transactions )

We do not create a standby controlfile nor we put the original primary database in background standby recovery mode

STEP 1 : Simulate some transactions

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.

STEP 2 : Shutdown the new primary database

SQL> conn / as sysdba Connected.

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

STEP 3 : Copy over files

Copy over the control files from new primary database server to original primary database server
Copy over the redo log files from new primary database server to original primary database server
Copy over the archived redo log files from the new primary database server to the flash recovery area of original primary database server

[[email protected] 2014_10_21]$ scp This email address is being protected from spambots. You need JavaScript enabled to view it. :/u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/* .

The authenticity of host '192.168.8.20 (192.168.8.20)' can't be established. RSA key fingerprint is f5:9a:3f:23:80:e5:2a:ba:b1:e1:66:5e:7e:28:df:0d. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.8.20' (RSA) to the list of known hosts. This email address is being protected from spambots. You need JavaScript enabled to view it. ';s password: o1_mf_1_20_b4drg4ql_.arc 100% 50MB 50.0MB/s 00:01 o1_mf_1_21_b4drg7jq_.arc 100% 48MB 47.9MB/s 00:01 o1_mf_1_22_b4drgc4w_.arc 100% 48MB 47.9MB/s 00:01 o1_mf_1_23_b4drgjkq_.arc 100% 48MB 47.9MB/s 00:01

[email protected] DRP]$ mv control01.ctl control01.ctl.old

[[email protected] DRP]$ mv control02.ctl control02.ctl.old [[email protected] DRP]$ rm redo* [[email protected] DRP]$ rm temp*

redo01.log 100% 50MB 50.0MB/s 00:01 redo02.log 100% 50MB 50.0MB/s 00:01 redo03.log 100% 50MB 50.0MB/s 00:01

STEP 4 : Startup mount and catalog the copIED archived redo logs

SQL> startup mount; ORACLE instance started.

Total System Global Area 524288000 bytes Fixed Size 2085192 bytes Variable Size 150998712 bytes Database Buffers 364904448 bytes Redo Buffers 6299648 bytes Database mounted.

SQL> exit Disconnected from Oracle Database 10g Release 10.2.0.4.0 - 64bit Production [[email protected] DRP]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Oct 21 15:52:54 2014

connected to target database: DRP (DBID=4250564207, not open)

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/BRUSSELS/archivelog/2014_10_21/o1_mf_1_20_b4drg4ql_.arc File Name: /u01/flash_recovery_area/BRUSSELS/archivelog/2014_10_21/o1_mf_1_22_b4drgc4w_.arc File Name: /u01/flash_recovery_area/BRUSSELS/archivelog/2014_10_21/o1_mf_1_23_b4drgjkq_.arc File Name: /u01/flash_recovery_area/BRUSSELS/archivelog/2014_10_21/o1_mf_1_21_b4drg7jq_.arc cataloging files... cataloging doneList of Cataloged Files ======================= File Name: /u01/flash_recovery_area/BRUSSELS/archivelog/2014_10_21/o1_mf_1_20_b4drg4ql_.arc File Name: /u01/flash_recovery_area/BRUSSELS/archivelog/2014_10_21/o1_mf_1_22_b4drgc4w_.arc File Name: /u01/flash_recovery_area/BRUSSELS/archivelog/2014_10_21/o1_mf_1_23_b4drgjkq_.arc File Name: /u01/flash_recovery_area/BRUSSELS/archivelog/2014_10_21/o1_mf_1_21_b4drg7jq_.arc

STEP 5 : Recover the database

RMAN> recover database;

Starting recover at 21-OCT-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=157 devtype=DISK

starting media recovery

archive log thread 1 sequence 20 is already on disk as file /u01/flash_recovery_area/BRUSSELS/archivelog/2014_10_21/o1_mf_1_20_b4drg4ql_.arc archive log thread 1 sequence 21 is already on disk as file /u01/flash_recovery_area/BRUSSELS/archivelog/2014_10_21/o1_mf_1_21_b4drg7jq_.arc archive log thread 1 sequence 22 is already on disk as file /u01/flash_recovery_area/BRUSSELS/archivelog/2014_10_21/o1_mf_1_22_b4drgc4w_.arc archive log thread 1 sequence 23 is already on disk as file /u01/flash_recovery_area/BRUSSELS/archivelog/2014_10_21/o1_mf_1_23_b4drgjkq_.arc archive log filename=/u01/flash_recovery_area/BRUSSELS/archivelog/2014_10_21/o1_mf_1_20_b4drg4ql_.arc thread=1 sequence=20 archive log filename=/u01/flash_recovery_area/BRUSSELS/archivelog/2014_10_21/o1_mf_1_21_b4drg7jq_.arc thread=1 sequence=21 media recovery complete, elapsed time: 00:00:10 Finished recover at 21-OCT-14

STEP 6 : Open the original primary database

RMAN> sql "alter database open";

sql statement: alter database open

STEP 7 : Recreate standby controlfile

[[email protected] log]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Oct 21 15:59:28 2014

connected to target database: DRP (DBID=4250564207)

RMAN> backup current controlfile for standby

Starting backup at 21-OCT-14

using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=148 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including standby control file in backupset channel ORA_DISK_1: starting piece 1 at 21-OCT-14 channel ORA_DISK_1: finished piece 1 at 21-OCT-14 piece handle=/u01/flash_recovery_area/BRUSSELS/backupset/2014_10_21/o1_mf_ncnnf_TAG20141021T155936_b4dsp99x_.bkp tag=TAG20141021T155936 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 21-OCT-14

STEP 8 : Remove or rename control files and redo log files

[[email protected] DRP]$ mv control02.ctl control02.ctl.primary [[email protected] DRP]$ mv control01.ctl control01.ctl.primary [[email protected] DRP]$ rm redo*

STEP 9 : Copy over and restore new backup of standby controlfile

[[email protected] 2014_10_21]$ scp This email address is being protected from spambots. You need JavaScript enabled to view it. :/u01/flash_recovery_area/BRUSSELS/backupset/2014_10_21/o1_mf_ncnnf_TAG20141021T155936_b4dsp99x_.bkp . This email address is being protected from spambots. You need JavaScript enabled to view it. ';s password: o1_mf_ncnnf_TAG20141021T155936_b4dsp99x_.bkp 100% 6944KB 6.8MB/s 00:00

RMAN> startup nomount;

Oracle instance started

Total System Global Area 524288000 bytes

Fixed Size 2085192 bytes Variable Size 150998712 bytes Database Buffers 364904448 bytes Redo Buffers 6299648 bytes

RMAN> restore standby controlfile from '/u01/flash_recovery_area/BRUSSELS/backupset/2014_10_21/o1_mf_ncnnf_TAG20141021T155936_b4dsp99x_.bkp';

Starting restore at 21-OCT-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 output filename=/u01/oradata/DRP/control01.ctl output filename=/u01/oradata/DRP/control02.ctl Finished restore at 21-OCT-14

STEP 10 : Resume recovery in background

RMAN> sql "alter database mount";

sql statement: alter database mount released channel: ORA_DISK_1

[[email protected] 2014_10_21]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Oct 21 16:07:55 2014

SQL> select database_role from v$database;

DATABASE_ROLE ---------------- PHYSICAL STANDBY

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

STEP 11 : Simulate transactions at level of primary database and monitor standby database recovery

[[email protected] log]$ sqlplus DRP/DRP

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Oct 21 16:09:46 2014

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.

Copy over the archived redo logs and catalog those files into the standby control file

[[email protected] 2014_10_21]$ scp This email address is being protected from spambots. You need JavaScript enabled to view it. :/u01/flash_recovery_area/BRUSSELS/archivelog/2014_10_21/* .

This email address is being protected from spambots. You need JavaScript enabled to view it. ';s password: o1_mf_1_10_b4d9hgb8_.arc 100% 2189KB 2.1MB/s 00:00 o1_mf_1_11_b4d9pcpz_.arc 100% 1024 1.0KB/s 00:00 o1_mf_1_12_b4d9q9kx_.arc 100% 2048 2.0KB/s 00:00 o1_mf_1_13_b4dohryb_.arc 100% 5522KB 5.4MB/s 00:00 o1_mf_1_14_b4dp3fg9_.arc 100% 48MB 47.9MB/s 00:01 o1_mf_1_15_b4dp3jvb_.arc 100% 48MB 47.9MB/s 00:01 o1_mf_1_16_b4dp3q1d_.arc 100% 48MB 47.9MB/s 00:01 o1_mf_1_17_b4dpfd9m_.arc 100% 48MB 47.9MB/s 00:01 o1_mf_1_18_b4dpfk5q_.arc 100% 48MB 47.9MB/s 00:01 o1_mf_1_19_b4dpfp5w_.arc 100% 48MB 23.9MB/s 00:02 o1_mf_1_20_b4drg4ql_.arc 100% 50MB 16.7MB/s 00:03 o1_mf_1_21_b4drg7jq_.arc 100% 48MB 23.9MB/s 00:02 o1_mf_1_22_b4drgc4w_.arc 100% 48MB 12.0MB/s 00:04 o1_mf_1_23_b4drgjkq_.arc 100% 48MB 23.9MB/s 00:02 o1_mf_1_24_b4dtbymn_.arc 100% 48MB 48.2MB/s 00:01 o1_mf_1_25_b4dtc1k8_.arc 100% 48MB 47.9MB/s 00:01 o1_mf_1_26_b4dtc8op_.arc 100% 48MB 23.9MB/s 00:02

RMAN> catalog recovery area noprompt;

Starting implicit crosscheck backup at 21-OCT-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=152 devtype=DISK Crosschecked 5 objects Finished implicit crosscheck backup at 21-OCT-14

Starting implicit crosscheck copy at 21-OCT-14 using channel ORA_DISK_1 Finished implicit crosscheck copy at 21-OCT-14

searching for all files in the recovery area cataloging files... cataloging doneList of Cataloged Files ======================= File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/o1_mf_1_14_b4dp3fg9_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/o1_mf_1_18_b4dpfk5q_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/o1_mf_1_12_b4d9q9kx_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/o1_mf_1_10_b4d9hgb8_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/o1_mf_1_19_b4dpfp5w_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/o1_mf_1_15_b4dp3jvb_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_24_b4dtbymn_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_25_b4dtc1k8_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_14_b4dp3fg9_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_18_b4dpfk5q_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_12_b4d9q9kx_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_10_b4d9hgb8_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_19_b4dpfp5w_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_15_b4dp3jvb_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_20_b4drg4ql_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_22_b4drgc4w_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_17_b4dpfd9m_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_11_b4d9pcpz_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_23_b4drgjkq_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_13_b4dohryb_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_21_b4drg7jq_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_26_b4dtc8op_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_16_b4dp3q1d_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/o1_mf_1_17_b4dpfd9m_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/o1_mf_1_11_b4d9pcpz_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/o1_mf_1_13_b4dohryb_.arc File Name: /u01/flash_recovery_area/MOERZEKE/archivelog/o1_mf_1_16_b4dp3q1d_.arc

searching for all files in the recovery areaList of Files Unknown to the Database ===================================== File Name: /u01/flash_recovery_area/MOERZEKE/onlinelog/o1_mf_1_b4dom6dv_.log File Name: /u01/flash_recovery_area/MOERZEKE/onlinelog/o1_mf_3_b4dom7jx_.log File Name: /u01/flash_recovery_area/MOERZEKE/onlinelog/o1_mf_2_b4dom6z2_.log cataloging files... no files catalogedList of Files Which Where Not Cataloged ======================================= File Name: /u01/flash_recovery_area/MOERZEKE/onlinelog/o1_mf_1_b4dom6dv_.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /u01/flash_recovery_area/MOERZEKE/onlinelog/o1_mf_3_b4dom7jx_.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /u01/flash_recovery_area/MOERZEKE/onlinelog/o1_mf_2_b4dom6z2_.log RMAN-07529: Reason: catalog is not supported for this file type

Recovery automatically happens ( extract from alert file )

Tue Oct 21 16:14:05 2014 Media Recovery Log /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_24_b4dtbymn_.arc Media Recovery Log /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_25_b4dtc1k8_.arc Media Recovery Log /u01/flash_recovery_area/MOERZEKE/archivelog/2014_10_21/o1_mf_1_26_b4dtc8op_.arc Media Recovery Waiting for thread 1 sequence 27