Oracle 11g SE Switch-Over
Recently, I tested a switchover on Oracle 11g SE1.
As you know, Oracle Database Standard Edition One—as well as Standard Edition—does not have the Data Guard feature. Therefore, I had to do everything manually.
The whole process took less than 15 minutes. This includes less than five minutes of full downtime to restart the database in READ-ONLY mode, and less than 10 minutes of READ-ONLY downtime.
Of course, it depends on the size of Redo logs and the network speed to move Redo logs from the primary server to standby.
Here is what I had. The primary database and one physical standby database:
- OS – SUSE Linux ES10 (SP2) x86_64
- Oracle – Release 11.1.0.7.0 64bit SE1
First of all, I switched the standby database to the primary role.
Step 1. Shutdown the primary database
oracle@ora1 /u01/app/oracle/testdb/oradata> sqlplus / as sysdba SQL*Plus: Release 11.1.0.7.0 - Production Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Release 11.1.0.7.0 - 64bit Production SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
Step 2. Make a copy of the control file, the spfile, and the redo logs:
SQL> !cp control01.ctl copy/control01.ctl.primary SQL> !cp /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora copy/spfiletestdb.ora.primary SQL> !cp *.log copy/
Step 3. Startup the primary database in READ-ONLY mode:
SQL> startup mount ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2159312 bytes Variable Size 754978096 bytes Database Buffers 503316480 bytes Redo Buffers 8912896 bytes Database mounted. SQL> alter database open read only; Database altered. SQL> exit Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
Step 4. Copy all needed archive logs, the copy of the control file, the spfile, and the redo logs to the standby server ora2:
oracle@ora1 /u01/app/oracle/testdb/oradata> scp ../archivelogs/1_152_333215132.dbf oracle@ora2:/u01/app/oracle/testdb/archivelogs/ oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/control01.ctl.primary oracle@ora2:/u01/app/oracle/testdb/oradata/copy/ oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/spfilepb.ora.primary oracle@ora2:/u01/app/oracle/testdb/oradata/copy/ oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/redo01.log oracle@ora2:/u01/app/oracle/testdb/oradata/ oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/redo02.log oracle@ora2:/u01/app/oracle/testdb/oradata/ oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/redo03.log oracle@ora2:/u01/app/oracle/testdb/oradata/
Step 5. Apply all needed archive logs on the standby database:
oracle@ora2 /u01/app/oracle/testdb/oradata> sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
SQL> recover standby database until cancel;
ORA-00279: change 2244877 generated at 10/07/2008 14:40:18 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/testdb/archivelogs/1_152_333215132.dbf
ORA-00280: change 2244877 for thread 1 is in sequence #152
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 2245162 generated at 10/07/2008 14:53:27 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/testdb/archivelogs/1_153_333215132.dbf
ORA-00280: change 2245162 for thread 1 is in sequence #153
ORA-00278: log file '/u01/app/oracle/testdb/archivelogs/1_152_333215132.dbf' no
longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
Step 6. Shutdown the standby database:
SQL> shutdown ORA-01109: database not open Database dismounted. ORACLE instance shut down.
Step 7. Make a copy of the controlfile and the spfile.
To make it easier I just switched spfiles.
SQL> !cp control01.ctl copy/control01.ctl.stndby SQL> !scp copy/control01.ctl.stndby oracle@ora1:/u01/app/oracle/testdb/oradata/copy/ SQL> !cp /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora copy/spfiletestdb.ora.stndby SQL> !scp copy/spfilepb.ora.stndby oracle@ora1:/u01/app/oracle/testdb/oradata/copy/
Step 8. Replace the standby controlfile and spfile with the copy of the primary control file and spfile:
SQL> !rm control*.ctl SQL> !rm /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora SQL> !cp copy/spfiletestdb.ora.primary /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora SQL> !cp copy/control01.ctl.primary control01.ctl SQL> !cp copy/control01.ctl.primary control02.ctl SQL> !cp copy/control01.ctl.primary control03.ctl
Step 9. Switch the standby database to the primary role:
SQL> startup mount ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2159312 bytes Variable Size 754978096 bytes Database Buffers 503316480 bytes Redo Buffers 8912896 bytes Database mounted. SQL> recover database; Media recovery complete. SQL> alter database tempfile '/u01/app/oracle/testdb/oradata/temp01.dbf' drop; Database altered. SQL> alter database open; Database altered. SQL> alter tablespace temp add tempfile '/u01/app/oracle/testdb/oradata/temp01.dbf' size 100m reuse autoextend on next 100m maxsize 2048m; Tablespace altered. SQL> select CONTROLFILE_TYPE, OPEN_MODE, DATABASE_ROLE, STANDBY_BECAME_PRIMARY_SCN from v$database; CONTROL OPEN_MODE DATABASE_ROLE STANDBY_BECAME_PRIMARY_SCN ------- ---------- ---------------- -------------------------- CURRENT READ WRITE PRIMARY 2244877 SQL> exit Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
And finally, I switched the old primary database to the standby role.
Step 10. Shutdown the old primary database:
oracle@ora1 /u01/app/oracle/testdb/oradata> sqlplus / as sysdba SQL*Plus: Release 11.1.0.7.0 - Production Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Release 11.1.0.7.0 - 64bit Production SQL> shutdown Database closed. Database dismounted. ORACLE instance shut down.
Step 11. Replace the old primary control file and spfile with the copy of the standby control file and spfile
SQL> !rm control*.ctl SQL> !rm /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora SQL> !rm temp01.dbf SQL> !rm *.log SQL> !cp copy/spfiletestdb.ora.stndby /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora SQL> !cp copy/control01.ctl.stndby control01.ctl SQL> !cp copy/control01.ctl.stndby control02.ctl SQL> !cp copy/control01.ctl.stndby control03.ctl
Step 12. Switch the old primary database to the standby role:
SQL> startup mount ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2159312 bytes Variable Size 754978096 bytes Database Buffers 503316480 bytes Redo Buffers 8912896 bytes Database mounted. SQL> select CONTROLFILE_TYPE, OPEN_MODE, DATABASE_ROLE from v$database; CONTROL OPEN_MODE DATABASE_ROLE ------- ---------- ---------------- STANDBY MOUNTED PHYSICAL STANDBY SQL> exit Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
So you can see that Oracle 11g SE1 (SE) switchover of primary database and one physical database located on different servers is a very simple process. But if you have the physical standby on the same server and use OMF, you should consider two things.
- How to change the path to the datafiles and redo logs in the control files;Solution:In Step 9, after the database is mounted and before it is opened, all datafiles and redo logs should be renamed using the
ALTER DATABASE RENAME FILEcommand. - How not to remove the old datafiles. OMF will remove the old files after the
RENAMEcommand.Workaround: Move the files to a temporary directory and move them back after theRENAMEcommand. The old primary database should be down.
The same process can be done with a RAC database.
And do not forget to make a database backup in case something goes wrong.
