We have demonstrated how to convert a physical standby database to a snapshot standby by the broker in the last article. Next, we will try the very basic way to convert it to a snapshot manually.
Turn the broker off in both primary and standby database for demonstrating conversion manually.
[oracle@primary01 ~]$ sqlplus / as sysdba
...
SQL> alter system set dg_broker_start=FALSE scope=both;
System altered.
...
[oracle@standby01 ~]$ sqlplus / as sysdba
...
SQL> alter system set dg_broker_start=FALSE scope=both;
System altered.
Restart both primary and standby database.
[oracle@standby01 ~]$ srvctl stop database -d compdb
...
[oracle@primary01 ~]$ srvctl stop database -d compdb
...
[oracle@primary01 ~]$ srvctl start database -d compdb
...
[oracle@standby01 ~]$ srvctl start database -d compdb -o mount
Check the status of both databases before conversion.
[oracle@primary01 ~]$ sqlplus / as sysdba
...
SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;
INST_ID OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- -------------------- ---------------- --------------------
1 READ WRITE PRIMARY TO STANDBY
2 READ WRITE PRIMARY TO STANDBY
SQL> select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby where process in ('RFS','LNS','MRP0');
INST_ID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
---------- --------- ------------ ---------- ---------- ---------- ----------
1 LNS WRITING 1 152 525 1
2 LNS WRITING 2 145 117 5
...
[oracle@standby01 ~]$ sqlplus / as sysdba
...
SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;
INST_ID OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- -------------------- ---------------- --------------------
1 MOUNTED PHYSICAL STANDBY NOT ALLOWED
2 MOUNTED PHYSICAL STANDBY NOT ALLOWED
SQL> select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby where process in ('RFS','LNS','MRP0');
INST_ID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
---------- --------- ------------ ---------- ---------- ---------- ----------
1 RFS IDLE 0 0 0 0
1 RFS IDLE 0 0 0 0
1 RFS IDLE 0 0 0 0
1 RFS IDLE 2 145 307 1
2 RFS IDLE 0 0 0 0
2 RFS IDLE 0 0 0 0
2 RFS IDLE 0 0 0 0
2 RFS IDLE 1 152 558 1
2 RFS IDLE 0 0 0 0
2 RFS IDLE 0 0 0 0
2 RFS IDLE 0 0 0 0
11 rows selected.
Enable apply service on node 1 of the standby RAC.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Disable apply service on node 1 of the standby RAC.
SQL> alter database recover managed standby database cancel;
Database altered.
Convert the physical standby database to the snapshot standby.
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.
Restart the snapshot standby database to open state.
[oracle@standby01 ~]$ srvctl stop database -d compdb
[oracle@standby01 ~]$ srvctl start database -d compdb -o open
Check the status of the primary databases.
SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;
INST_ID OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- -------------------- ---------------- --------------------
1 READ WRITE PRIMARY TO STANDBY
2 READ WRITE PRIMARY TO STANDBY
SQL> select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby where process in ('RFS','LNS','MRP0');
INST_ID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
---------- --------- ------------ ---------- ---------- ---------- ----------
1 LNS WRITING 1 156 162 1
2 LNS WRITING 2 146 487 2
You can see the transport service is still working.
Check the status of the standby databases.
SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;
INST_ID OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- -------------------- ---------------- --------------------
1 READ WRITE SNAPSHOT STANDBY NOT ALLOWED
2 READ WRITE SNAPSHOT STANDBY NOT ALLOWED
SQL> select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby where process in ('RFS','LNS','MRP0');
INST_ID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
---------- --------- ------------ ---------- ---------- ---------- ----------
1 RFS IDLE 0 0 0 0
1 RFS IDLE 0 0 0 0
1 RFS IDLE 0 0 0 0
2 RFS IDLE 0 0 0 0
2 RFS IDLE 1 156 163 1
2 RFS IDLE 0 0 0 0
6 rows selected.
Though the transport service is still working, the apply service is off now.
Let's create a table in the snapshot standby database. We can expect that the table will be discarded after the snapshot standby is converted back to the physical standby database.
SQL> CREATE TABLE HR.NAME (FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(20));
Table created.
SQL> desc hr.name;
Name Null? Type
----------------------------------------- -------- ----------------------------
FIRST_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(20)
Restart the snapshot standby to mount state.
[oracle@standby01 ~]$ srvctl stop database -d compdb
[oracle@standby01 ~]$ srvctl start database -d compdb -o mount
...
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
*
ERROR at line 1:
ORA-01105: mount is incompatible with mounts by other instances
Oh, we made a mistake, we should stop all instances and startup only one instance of the snapshot standby RAC.
Restart only one instance of the snapshot standby RAC to mount state.
[oracle@standby01 ~]$ srvctl stop database -d compdb
[oracle@standby01 ~]$ srvctl start instance -d compdb -i standb1 -o mount
Convert the snapshot standby back to the physical standby.
[oracle@standby01 ~]$ sqlplus / as sysdba
...
SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;
INST_ID OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- -------------------- ---------------- --------------------
1 MOUNTED SNAPSHOT STANDBY NOT ALLOWED
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;
select inst_id, open_mode, database_role, switchover_status from gv$database
*
ERROR at line 1:
ORA-01507: database not mounted
You can see the database is dismounted after converting.
Restart and check the physical standby database.
[oracle@standby01 ~]$ srvctl stop database -d compdb
[oracle@standby01 ~]$ srvctl start database -d compdb -o open
[oracle@standby01 ~]$ sqlplus / as sysdba
...
SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;
INST_ID OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- -------------------- ---------------- --------------------
1 READ ONLY PHYSICAL STANDBY RECOVERY NEEDED
2 READ ONLY PHYSICAL STANDBY RECOVERY NEEDED
SQL> desc hr.name;
ERROR:
ORA-04043: object hr.name does not exist
The last check showed that table HR.NAME was eventually discarded after converting back.