Mir Sayeed Hassan – Oracle Blog

Oracle DBA – Tips & Techniques | Learn with real-time examples

  • Translate

  • It’s Me






  • My Certificates

  • Links

    My Acclaim Certification : Credly Profile
    My Oracle ACE Pro Profile

  • Achievements

    Awarded Top 100 Oracle Blogs from Worldwide - #RANK 39
  • VISITORS COUNT

  • Verified International Academic Qualification from World Education Service (WES)

    Verified International Academic Qualification from World Education Service (WES)

  • Jobs

How to perform the Snapshot Standby database in Oracle Database 11gR2(11.2.0.4)

Posted by Mir Sayeed Hassan on June 25th, 2020

How to perform the Snapshot Standby database in Oracle Database 11gR2(11.2.0.4)

The below scenario is to test the snapshot standby database in Oracle database 11gR2(11.2.0.4), Its an feature which allow a read-write operation on standby DB.

We are performing the convert of physical standby db to the snapshot standby, after its conversion is completed successfully we can do the DML operation for testing purpose.

Once the testing is completed we can revert back standby db to the physical standby db & sync it to primary database.

Check the status of database

SQL> select status, version, open_mode from V$instance, v$database;

STATUS VERSION OPEN_MODE
------------ ----------------- --------------------
MOUNTED 11.2.0.4.0 MOUNTED

Stop the recovery process of standby

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

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

Startup database with mount

SQL> startup mount
ORACLE instance started.

Total System Global Area 1.8547E+11 bytes
Fixed Size 2263656 bytes
Variable Size 3.4897E+10 bytes
Database Buffers 1.5032E+11 bytes
Redo Buffers 244588544 bytes
Database mounted.

Check the recovery process

SQL> @/home/oracle/script/stbytest.sql;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0

THREAD# MAX(SEQUENCE#)
---------- --------------
1 653083
2 329147

Note:Flashback database should not be enabled to the conversion operation.

Check the flashback database

SQL> select flashback_on FROM v$database;

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

Convert the standby to a snapshot standby

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.
SQL> alter database open;
Database altered.

Check the restore point created.

SQL> select name,guarantee_flashback_database from V$restore_point;

NAME                                              GUA
------------------------------------------------------
SNAPSHOT_STANDBY_REQUIRED_06/25/2020 14:42:38     YES
SQL> select flashback_on FROM v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

Check the status of database

SQL> select status, open_mode from V$instance, V$database;

STATUS OPEN_MODE
------------ --------------------
OPEN READ WRITE

Now Database is in read-write mode & you can perform any testing changes.

—————————————————————————————-
Convert back from snapshot database to the physical standby database

Note:
All the changes done for testing purpose will be lossing it when its convert back to physical standby database.

Stutdown the database

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

Startup the database in mount mode

SQL> startup mount

ORACLE instance started.
Total System Global Area 1.8547E+11 bytes
Fixed Size 2263656 bytes
Variable Size 3.4897E+10 bytes
Database Buffers 1.5032E+11 bytes
Redo Buffers 244588544 bytes
Database mounted.

Convert the database from snapshot standby database to physical standby database

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

Shutdown the database

SQL> shu immediate
ORA-01507: database not mounted
ORACLE instance shut down.

Startup in NOMOUNT Mode

SQL> startup nomount;

ORACLE instance started.
Total System Global Area 1.8547E+11 bytes
Fixed Size 2263656 bytes
Variable Size 3.4897E+10 bytes
Database Buffers 1.5032E+11 bytes
Redo Buffers 244588544 bytes

Mount database as standby database

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.

Check the status of recovery process

SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby ;
SQL> select thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 653198 489472 1121
RFS IDLE 0 0 0 0

THREAD# MAX(SEQUENCE#)
----------------------
1           653083
2           329147

Check the status of standby database

SQL> select open_mode,database_role from v$database;

OPEN_MODE        DATABASE_ROLE
--------------------------------
MOUNTED         PHYSICAL STANDBY

Start the recovery process of standby database

SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.

Note: Issue few “alter system switch” on primary database.

Check the status of current standby database

SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby ;
SQL> select thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
ARCH CLOSING 1 653200 1232896 1840
ARCH CLOSING 1 653201 106496 130
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS RECEIVING 1 653202 13243267 16
MRP0 APPLYING_LOG 2 329150 7939055 18415190
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
-----------------------------------------------
RFS IDLE 0 0 0 0
RFS IDLE 2 329235 7844055 52

13 rows selected.

THREAD# MAX(SEQUENCE#)
-----------------------
1          653087
2          329148

Therefore the database is completely sync with primary database & its in the state of previous

====Hence tested & verified in our test env====