DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, October 1, 2015

Convert physical standby database to snapshot standby database


A snapshot standby database is a fully updateable standby database created by converting a physical standby database into a snapshot standby database.
Primary Database
Oracle Database version: 11.2.0.3 Enterprise Edition
Primary database: 
DO_PRIMARY
Details with respect to the primary database:

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
 ------------ ---------------- ---------------- --------------------
 OPEN         do_primary           PRIMARY          READ WRITE

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD# MAX(SEQUENCE#)
 ---------- --------------
1                  243


Standby database Details:

Oracle database version: 11.2.0.3 Enterprise Edition
Standby database name: 
DO_PSTBY
Details with respect to the physical standby database:


SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- ----------------
OPEN         do_pstby          PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)
 ---------- --------------
 1            243

SQL>
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

You can observe that the standby database is in sync with the primary database. Below outcome shows that the Flash Recovery Area is configured on the physical standby database.

SQL> show parameter db_recovery_file_dest

NAME                         TYPE         VALUE
---------------------------  -----------  -------------
db_recovery_file_dest        string       +FRA_DO
db_recovery_file_dest_size   big integer  6000M

SQL>

Step 1: Cancel the Managed Recovery Process (MRP) on the physical standby database, shut it down and place it in Mount mode.

SQL> alter database recover managed standby database cancel;
Database altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
Database mounted.

Step 2: Once the standby database is mounted, convert the Physical standby database to snapshot standby database.

SQL> alter database convert to snapshot standby;
Database altered.

Step 3: You can now open the snapshot standby database and check its mode.

SQL> alter database open;
Database altered.

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS      INSTANCE_NAME   DATABASE_ROLE    OPEN_MODE
----------- --------------- ---------------- ------------------
OPEN        do_pstby         SNAPSHOT STANDBY READ WRITE



Small Test on the snapshot standby database.

1. Create a user called “SNAPDOTEST”

2. Create a table called “DOTEST” whose owner is “SNAPDOTEST” and insert some records in it. You can also update some of the records as well.

SQL> create user snapdotest identified by doyensys;
User created.

SQL> grant connect,resource to snapdotest;
Grant succeeded.

SQL> conn snapdotest/doyensys@do_pstby
Connected.
SQL>
SQL> create table dotest(code number, name char(20));
Table created.

SQL> insert into test values (500,'MAX');
1 row created.

SQL> insert into test values(750,'MARK');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from test;
CODE       NAME
---------- --------------------
500        MAX
750        MARK

SQL> update snapdotest.dotest set code=550 where name=’MAX’;
1 row updated.

SQL> commit;
Commit complete.

SQL> select * from snaptest.test;
CODE       NAME
---------- --------------------
500        MAX
750        MARK

In the mean time, you can also see that the redo data from the primary database is received by the snapshot standby database but would not be applied.
On primary database the latest sequence generated is 248 and that on the standby database, the RFS process is idle for sequence 249.
Primary:

1
2
3
4
5
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD#    MAX(SEQUENCE#)
---------- --------------
 1         248

Standby:
SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
 --------- ------------ ----------
 ARCH      CLOSING               1
 ARCH      CONNECTED             0
 ARCH      CONNECTED             0
 ARCH      CONNECTED             0
 RFS       IDLE                  0
 RFS       IDLE                249
 RFS       IDLE                  0

7 rows selected.


No comments: