Snapshot standby is a feature of Oracle Data Guard. It introduced in Oracle 11g and allows the standby database to be opened in Read - Write mode.
After changing a physical standby database to snapshot standby it can be used as a normal read write database.
When this snapshot standby switched back to standby mode , all changes which you have done in read write mode will be lost. This is achieved by Flashback database, but its not necessary that flashback database has to be enabled to achieve this.
Prerequisites of Snapshot standby :-
1. Managed recovery has to be disabled.
2. Standby database has to be in MOUNT mode.
Steps to create a snapshot standby from Physical standby database :-
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------
NO
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
Now we can use this database as a normal database.
SQL> create table amit
2 (
3 id number(12),
4 name varchar(23));
Table created.
SQL> insert into amit values('&id','&name');
Enter value for id: 1
Enter value for name: f
old 1: insert into amit values('&id','&name')
new 1: insert into amit values('1','f')
1 row created.
SQL> /
Enter value for id: 2
Enter value for name: gf
old 1: insert into amit values('&id','&name')
new 1: insert into amit values('2','gf')
1 row created.
SQL> commit;
Commit complete.
SQL> select * from amit;
ID NAME
---------- -----------------------
1 f
2 gf
We will get below mentioned output in alert log of standby database after snapshot standby process completed :-
After changing a physical standby database to snapshot standby it can be used as a normal read write database.
When this snapshot standby switched back to standby mode , all changes which you have done in read write mode will be lost. This is achieved by Flashback database, but its not necessary that flashback database has to be enabled to achieve this.
Prerequisites of Snapshot standby :-
1. Managed recovery has to be disabled.
2. Standby database has to be in MOUNT mode.
Steps to create a snapshot standby from Physical standby database :-
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------
NO
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
Now we can use this database as a normal database.
SQL> create table amit
2 (
3 id number(12),
4 name varchar(23));
Table created.
SQL> insert into amit values('&id','&name');
Enter value for id: 1
Enter value for name: f
old 1: insert into amit values('&id','&name')
new 1: insert into amit values('1','f')
1 row created.
SQL> /
Enter value for id: 2
Enter value for name: gf
old 1: insert into amit values('&id','&name')
new 1: insert into amit values('2','gf')
1 row created.
SQL> commit;
Commit complete.
SQL> select * from amit;
ID NAME
---------- -----------------------
1 f
2 gf
We will get below mentioned output in alert log of standby database after snapshot standby process completed :-
Thu Jul 25 15:17:38 2013
RFS[145]: Assigned to RFS process 7930216
RFS[145]: Identified database type as 'snapshot standby': Client is ARCH pid 12648536
Thu Jul 25 15:17:39 2013
To convert it back to physical standby, we will lose all changes which we have made in sanpshot standby mode.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 1043886080 bytes
Fixed Size 2213384 bytes
Variable Size 817891832 bytes
Database Buffers 213909504 bytes
Redo Buffers 9871360 bytes
Database mounted.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL> SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1043886080 bytes
Fixed Size 2213384 bytes
Variable Size 817891832 bytes
Database Buffers 213909504 bytes
Redo Buffers 9871360 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.
SQL> SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------
NO
Snapshot standby database switched to Physical standby database. Test redo transport with Primary database :-
SQl> alter system switch logfile;
System altered.
PFB output in alert log of Physical standby dataabase :-
RFS[10]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 60030994
Primary database is in MAXIMUM PERFORMANCE mode
RFS[10]: Selected log 4 for thread 1 sequence 31 dbid 2119844559 branch 821698130
Thu Jul 25 15:30:12 2013
Media Recovery Log /pacs/oracle/flash_recovery_area/TEST/archivelog/2013_07_25/o1_mf_1_30_8z1xs2tg_.arc
Media Recovery Waiting for thread 1 sequence 31 (in transit)
Standby Database has been switched back successfully to Physical standby from Snapshot standby.
I hope this article helped you
Regards,
Amit Rath
No comments:
Post a Comment