Skip to content

Converting Standby Database to Snapshot (2/4) - Convert Manually

  • by

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.

Leave a Reply

Your email address will not be published.