The post outlines steps to open the standby database as primary with a failover option. Note that primary is not available to switch over. Data guard broker not configured.
1. Verify the open mode for the standby database.
SQL> select name,open_mode ,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- PROD READ ONLY PHYSICAL STANDBY
2. Shutdown the standby database.
SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down.
3. Open the standby database in “mount” stage.
SQL> startup mount; ORACLE instance started. Total System Global Area 3.4206E+10 bytes Fixed Size 2238616 bytes Variable Size 3422553960 bytes Database Buffers 3.0736E+10 bytes Redo Buffers 45682688 bytes Database mounted. SQL>
4. Verify if we can open the database using resetlog.
SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01666: control file is for a standby database
SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- PROD MOUNTED PHYSICAL STANDBY
5. Start recovery for the standb y database.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY * ERROR at line 1: ORA-16139: media recovery required
6. Fail-over:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; Database altered. SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE; Database altered. SQL>
7. Now verify the database status:
SQL> select name,open_mode ,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- PROD MOUNTED PRIMARY
8. Now open the database:
SQL> alter database open; Database altered.
SQL> select name,open_mode ,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- PROD READ WRITE PRIMARY
9. If you have flashback on, make it off if you want.
SQL> alter database flashback off; Database altered.