Step by step guide to convert physical standby database into snapshot standby database (READ WRITE mode)
What is Standby snapshot Database?
Oracle provides an unique feature where the physical standby database can be opened in READ WRITE mode to perform update able transactions.
A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database. A snapshot standby database
receives and archives, but does not apply redo data from a primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back
into a physical standby database, after discarding all local updates to the snapshot standby database.
A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. Local updates to the
snapshot standby database will cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a
physical standby database at any time, and the redo data received from the primary will then be applied.
A snapshot standby database will allow you to make use of the data available on the physical standby database (which is the same data of the primary database), which allows the users
to test the application on a standby database which has the primary database's data before implementing it into production environment. Whenever a physical standby database is converted
into a snapshot standby database, a guaranteed restore point is automatically created. 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 the transactions
which were made in standby database while it was open 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.
Oracle version :- 11.2.0.1.0
Primary Database : prim
Standby Database : stand
Lets find out few datails from both database.
On primary database:-
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN prim PRIMARY READ WRITE
Find out current Log sequence number.
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 23
Now at Standby database :-
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN stand PHYSICAL STANDBY READ ONLY WITH APPLY
Current Log sequence number at standby :-
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 23
Also lets find out if Flashback is enabled or not.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
So we can see that the standby database in sync with the primary database.
Also the output of below command shows that the Flash Recovery Area is configured on 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
Now Cancel the MRP Process in Standby database and restart it into mount stage.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 310380536 bytes
Database Buffers 96468992 bytes
Redo Buffers 4308992 bytes
Database mounted.
Now Once the standby database is mounted, convert the Physical standby database to snapshot standby database.
SQL> alter database convert to snapshot standby;
Database altered.
Now open the database and check its open 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 stand SNAPSHOT STANDBY READ WRITE
So we can see the database has been opened into read write mode.
Now lets test the scenario
Here on standby database we will do the following things.
1. Create a user called "snapshotstd"
2. Create a table called "test" whose owner is "snapshotstd" and insert some records in it.
SQL> create user snapshotstd identified by snapshotstd;
User created.
SQL> grant connect , resource to snapshotstd;
Grant succeeded.
SQL> conn snapshotstd/snapshotstd
Connected.
SQL> create table employees (name char(20) , Age number );
Table created.
SQL> insert into employees values ('SOUMYA' , '30' );
1 row created.
SQL> insert into employees values ('UDIT' , '24');
1 row created.
SQL> insert into employees values ('ABHISHEK' ,'25');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> select * from employees;
NAME AGE
------------ ----------
SOUMYA 30
UDIT 24
ABHISHEK 25
SQL> update employees set age=25 where name ='UDIT';
1 row updated.
SQL> Commit;
Commit complete.
SQL> select * from employees;
NAME AGE
------------ ----------
SOUMYA 30
UDIT 25
ABHISHEK 25
Now at primary database:-
Lets update a table on primary database and see if its going to be replicated on physical standby when the database is converted.
SQL> conn soumya/soumya
connected
SQL> create table xyz (id number);
Table created.
SQL> insert into xyz values (1);
1 row created.
SQL> /
1 row created.
SQL> commit;
On primary database:-
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 26
On standby database:-
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 25
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CLOSING 26
RFS IDLE 0
RFS IDLE 27
So we can see that the redo data from the primary database is received by the snapshot standby database but it was has been applied.
At standby database shut down the dbase and start it in mount stage:-
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 310380536 bytes
Database Buffers 96468992 bytes
Redo Buffers 4308992 bytes
Database mounted.
Now Lets convert back the snapshot standby database into physical standby database.
SQL> alter database convert to physical standby;
Database altered.
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
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 310380536 bytes
Database Buffers 96468992 bytes
Redo Buffers 4308992 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
------------ ---------------- ---------------- --------------------
OPEN stand PHYSICAL STANDBY READ ONLY
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 CLOSING 28
RFS IDLE 0
RFS IDLE 29
MRP0 WAIT_FOR_LOG 29
7 rows selected.
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 28
Crosscheck whether the physical standby database is in sync with the primary database.
On primary:-
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 28
On standby:-
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 28
Now we can see
SQL> select * from snapshotstd.employees;
select * from snapshotstd.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> Select username,account_status from dba_users where username='SNAPSHOTSTD';
no rows selected
We can see above that the transactions that were carried out when the standby database was opened in READ WRITE mode are flushed out after it was converted back to physical standby
database mode.
Also,
SQL> select * from soumya.xyz;
ID
----------
1
1
So also we see when the standby database was converted into snapshot database, the datas updated in primary database has also replicated into physical standby database now.
Done..
Please share your ideas and opinions about this topic.
If you like this post, then please share with others.
Please subscribe on email for every updates on mail.
What is Standby snapshot Database?
Oracle provides an unique feature where the physical standby database can be opened in READ WRITE mode to perform update able transactions.
A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database. A snapshot standby database
receives and archives, but does not apply redo data from a primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back
into a physical standby database, after discarding all local updates to the snapshot standby database.
A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. Local updates to the
snapshot standby database will cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a
physical standby database at any time, and the redo data received from the primary will then be applied.
A snapshot standby database will allow you to make use of the data available on the physical standby database (which is the same data of the primary database), which allows the users
to test the application on a standby database which has the primary database's data before implementing it into production environment. Whenever a physical standby database is converted
into a snapshot standby database, a guaranteed restore point is automatically created. 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 the transactions
which were made in standby database while it was open 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.
Oracle version :- 11.2.0.1.0
Primary Database : prim
Standby Database : stand
Lets find out few datails from both database.
On primary database:-
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN prim PRIMARY READ WRITE
Find out current Log sequence number.
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 23
Now at Standby database :-
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN stand PHYSICAL STANDBY READ ONLY WITH APPLY
Current Log sequence number at standby :-
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 23
Also lets find out if Flashback is enabled or not.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
So we can see that the standby database in sync with the primary database.
Also the output of below command shows that the Flash Recovery Area is configured on 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
Now Cancel the MRP Process in Standby database and restart it into mount stage.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 310380536 bytes
Database Buffers 96468992 bytes
Redo Buffers 4308992 bytes
Database mounted.
Now Once the standby database is mounted, convert the Physical standby database to snapshot standby database.
SQL> alter database convert to snapshot standby;
Database altered.
Now open the database and check its open 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 stand SNAPSHOT STANDBY READ WRITE
So we can see the database has been opened into read write mode.
Now lets test the scenario
Here on standby database we will do the following things.
1. Create a user called "snapshotstd"
2. Create a table called "test" whose owner is "snapshotstd" and insert some records in it.
SQL> create user snapshotstd identified by snapshotstd;
User created.
SQL> grant connect , resource to snapshotstd;
Grant succeeded.
SQL> conn snapshotstd/snapshotstd
Connected.
SQL> create table employees (name char(20) , Age number );
Table created.
SQL> insert into employees values ('SOUMYA' , '30' );
1 row created.
SQL> insert into employees values ('UDIT' , '24');
1 row created.
SQL> insert into employees values ('ABHISHEK' ,'25');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> select * from employees;
NAME AGE
------------ ----------
SOUMYA 30
UDIT 24
ABHISHEK 25
SQL> update employees set age=25 where name ='UDIT';
1 row updated.
SQL> Commit;
Commit complete.
SQL> select * from employees;
NAME AGE
------------ ----------
SOUMYA 30
UDIT 25
ABHISHEK 25
Now at primary database:-
Lets update a table on primary database and see if its going to be replicated on physical standby when the database is converted.
SQL> conn soumya/soumya
connected
SQL> create table xyz (id number);
Table created.
SQL> insert into xyz values (1);
1 row created.
SQL> /
1 row created.
SQL> commit;
On primary database:-
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 26
On standby database:-
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 25
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CLOSING 26
RFS IDLE 0
RFS IDLE 27
So we can see that the redo data from the primary database is received by the snapshot standby database but it was has been applied.
At standby database shut down the dbase and start it in mount stage:-
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 310380536 bytes
Database Buffers 96468992 bytes
Redo Buffers 4308992 bytes
Database mounted.
Now Lets convert back the snapshot standby database into physical standby database.
SQL> alter database convert to physical standby;
Database altered.
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
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 310380536 bytes
Database Buffers 96468992 bytes
Redo Buffers 4308992 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
------------ ---------------- ---------------- --------------------
OPEN stand PHYSICAL STANDBY READ ONLY
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 CLOSING 28
RFS IDLE 0
RFS IDLE 29
MRP0 WAIT_FOR_LOG 29
7 rows selected.
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 28
Crosscheck whether the physical standby database is in sync with the primary database.
On primary:-
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 28
On standby:-
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 28
Now we can see
SQL> select * from snapshotstd.employees;
select * from snapshotstd.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> Select username,account_status from dba_users where username='SNAPSHOTSTD';
no rows selected
We can see above that the transactions that were carried out when the standby database was opened in READ WRITE mode are flushed out after it was converted back to physical standby
database mode.
Also,
SQL> select * from soumya.xyz;
ID
----------
1
1
So also we see when the standby database was converted into snapshot database, the datas updated in primary database has also replicated into physical standby database now.
Done..
Please share your ideas and opinions about this topic.
If you like this post, then please share with others.
Please subscribe on email for every updates on mail.
If you need to connect to database, you can use different tools such as bigcommerce connector, oracle connector. It depends on your database.
ReplyDelete