As you know, Oracle Database Standard Edition One, as well as Standard Edition, does not have Data Guard feature. Therefore, I had to do everything manually.
The whole process took less than 15 minutes. This includes less than 5 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 controlfile, spfile, and 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 controlfile, spfile, and 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 controlfile and 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 by the copy of primary controlfile 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 controlfile and spfile by the copy of standby controlfile 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 2 things.
Issue 1: How to change the path to the datafiles and redo logs in the controlfiles;
Solution: In Step 9 after the database is mounted and before it is opened, all datafiles and redo logs should be renamed by ‘ALTER DATABASE RENAME FILE’ command.
Issue 2: How not to remove the old datafiles. OMF will remove the old files after RENAME command.
Workaround: Move the files to temporary folder and move them back after 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.
