SNAP SHOT DATABASE

-

 

Snapshot standby database can be used to perform both READ and WRITE activities. Most importantly, a snapshot standby database keeps receiving the redo data from the primary database but does not apply them. These redo data received from the primary database would be applied only when the snapshot standby database is converted back to the Physical standby mode. There by the snapshot standby database provides data protection on primary database.



A snapshot standby database will allow you to make use of the data available on the physical standby database (which is a mirrored copy of the primary database). This allows the users to test the application on a standby database which has the primary data before implementing it in the Real production environment. When a physical standby database is converted to a snapshot standby database, a guaranteed restore point is automatically created.flash back is automaticlly created a Once when the updateable transactions are completed for testing purposes on the snapshot standby database and when you are converting back the snapshot standby to physical standby, oracle flashbacks to the restore point that was created earlier which means that the transactions that were made when the standby database was opened in READ WRITE mode will be flushed out.


The only requirement to have the snapshot standby is that FRA (Flash Recovery Area) must be configured on physical standby database. It is not necessary to have flashback enabled. Below are the steps on how to convert a physical standby database to a snapshot standby database and viceversa.



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

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

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

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


Standby database name: standby

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

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- ----------------
OPEN        standby            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            206


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      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 3882M

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
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             805306608 bytes
Database Buffers          452984832 bytes
Redo Buffers                8847360 bytes
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         standby          SNAPSHOT STANDBY READ WRITE

Database altered.






Small Test on the snapshot standby database.
1. Create a user called “SNAPTEST”
2. Create a table called “TEST” whose owner is “SNAPTEST” and insert some records in it. You can also update some of the records as well.



SQL> create user snaptest identified by oracle;
User created.

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

SQL> conn snaptest/oracle@standby
Connected.
SQL>
SQL> create table test(code number, name char(20));
Table created.

SQL> insert into test values (100,'AMIN');
1 row created.

SQL> insert into test values(200,'SYED');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from test;


      CODE NAME
---------- --------------------
       100 AMIN
       200 SYED


SQL> update snaptest.test set code=500 where name='AMIN';
1 row updated.

SQL> commit;
Commit complete.

SQL> select * from snaptest.test;
CODE       NAME
---------- --------------------
500        ARUN
200        SHIVU

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 208 and that on the standby database, the RFS process is idle for sequence 209.

Primary

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

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


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                209
 RFS       IDLE                  0

7 rows selected.






Steps on converting back a snapshot standby database to physical standby database.

Step 1: Shut down the snapshot standby database and open it in Mount mode.

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

SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             805306608 bytes
Database Buffers          452984832 bytes
Redo Buffers                8847360 bytes
Database mounted.
SQL>





Step 2: Convert the snapshot standby database to physical standby database.

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





Step 3: Once done, bounce the physical standby database and start the Managed Recovery Process (MRP) on it.

SQL> shut immediate
ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             805306608 bytes
Database Buffers          452984832 bytes
Redo Buffers                8847360 bytes
Database mounted.
Database opened.

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

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      standby          PHYSICAL STANDBY MOUNTED

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

SQL> select process,status,sequence# from v$managed_standby;
 PROCESS   STATUS        SEQUENCE#
 --------- ------------ ----------
 ARCH      CONNECTED             0
 ARCH      CONNECTED             0
 ARCH      CONNECTED             0
 ARCH      CONNECTED             0
 RFS       IDLE                  0
 RFS       IDLE                  0
RFS       IDLE                  0
 MRP0      WAIT_FOR_LOG        213

8 rows selected.



On Primary database:



SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD#    MAX(SEQUENCE#)
---------- --------------
1          212


On Standby database:
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD#    MAX(SEQUENCE#)
---------- --------------
1          212

===============================================================
If you have a physical standby database that you would like to convert to a snapshot standby database, use the DGMGRL CONVERT DATABASE command. Redo data will continue to be received by the database while it is operating as a snapshot standby database, but it will not be applied until the snapshot standby is converted back into a physical standby database.

A physical standby database must be configured with a fast recovery area to convert it to a snapshot standby database. This is because a guaranteed restore point is created during the conversion process, and guaranteed restore points require a fast recovery area.


When you are ready to revert the database back to a physical standby database, use the DGMGRL CONVERT DATABASE command again as follows. Any updates made to the database while it was operating as a snapshot standby database will be discarded. All accumulated redo data will be applied by Redo Apply services after the database is converted back to a physical standby database.



Limitation of Snapshot database;
========================

1)f the standby database is in maximum protection mode it cannot be converted in to snapshot
2)Target of switchover
3) Target of fast start failover

Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify the standby database and see if the co
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12        HEADING 'SID,Serial' COLUMN unix_pid     FORMAT a6         HEADING 'O/S|PID' COLUMN Client_User  FORMAT a14        HEADING 'Cl
Read more