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.

1. 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 FILE command.
2. How not to remove the old datafiles. OMF will remove the old files after the RENAME command.Workaround: Move the files to a temporary directory and move them back after the RENAME command. 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.