In this article we will see the detailed steps to create a snapshot standby database. Snapshot standby database is new in database 11g and we will create snapshot standby database from physical standby database. This snapshot standby database is fully updatable database and it is opened for read write operation. When the snapshot database is created from physical standby database, redo logs are still transferred to standby site but are not applied.
The practical use of snapshot standby database is in the situation where if you want to clone a production database for testing something, you can convert physical standby database to snapshot standby database which is as close as to the production database wrt data, do required testing on the snapshot standby database and convert it back to physical standby database. When you convert the snapshot standby database back to the physical standby database, all the modifications done to snapshot database will be lost and it is synchronized with the primary database by applying all the pending redo logs transferred but not applied on the standby database.
I assume that I have a physical standby database setup and data guard broker is configured to manage standby database.
1. Convert Physical Standby Database To Snapshot Database :
2. Verify Snapshot Standby Database :
The practical use of snapshot standby database is in the situation where if you want to clone a production database for testing something, you can convert physical standby database to snapshot standby database which is as close as to the production database wrt data, do required testing on the snapshot standby database and convert it back to physical standby database. When you convert the snapshot standby database back to the physical standby database, all the modifications done to snapshot database will be lost and it is synchronized with the primary database by applying all the pending redo logs transferred but not applied on the standby database.
I assume that I have a physical standby database setup and data guard broker is configured to manage standby database.
- Convert Physical Standby Database To Snapshot Database
- Verify Snapshot Standby Database
- Do Some Testing In Snapshot Database
- Convert Snapshot Database Back To Physical Standby Database
- Verify the Modifications Done In Step 3.
1. Convert Physical Standby Database To Snapshot Database :
[oracle@dgaskmpri01 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.2.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL> show configuration;
Configuration - askm_config
Protection Mode: MaxPerformance
Databases:
orcl - Primary database
sbyorcl - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database sbyorcl;
Database - sbyorcl
Role: PHYSICAL STANDBY Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
sbyorcl
Database Status:
SUCCESS
DGMGRL> convert database sbyorcl to snapshot standby;
Converting database "sbyorcl" to a Snapshot Standby database, please wait...
Database "sbyorcl" converted successfully
DGMGRL> show configuration;
Configuration - askm_config
Protection Mode: MaxPerformance
Databases:
orcl - Primary databasesbyorcl - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database sbyorcl;
Database - sbyorcl
Role: SNAPSHOT STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds
Apply Lag: 3 minutes 43 seconds
Instance(s):
sbyorcl
Database Status:
SUCCESS
DGMGRL>
2. Verify Snapshot Standby Database :
[oracle@dgaskmsby01 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Sun Nov 27 04:00:41 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
SNAPSHOT STANDBY
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL>
3. Do Some Testing In Snapshot Database :
For testing on snapshot standby database, I will do the following modifications on the snapshot standby database and verify the same changes again after converting snapshot database back to physical standby database.
4. Convert Snapshot Database Back To Physical Standby Database :
Please leave me your comments if you have any doubts.
Hope this helps
SRI
For testing on snapshot standby database, I will do the following modifications on the snapshot standby database and verify the same changes again after converting snapshot database back to physical standby database.
- Drop any existing user
- Create a new tablespace
- Resize datafile size
[oracle@dgaskmsby01 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Sun Nov 27 04:01:51 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
SNAPSHOT STANDBY
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> drop user scott cascade;
User dropped.
SQL> select file_name,tablespace_name,bytes/1024/1024 from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
/home/oracle/app/oracle/oradata/sbyorcl/users01.dbf
USERS 210
/home/oracle/app/oracle/oradata/sbyorcl/undotbs01.dbf
UNDOTBS1 143
/home/oracle/app/oracle/oradata/sbyorcl/sysaux01.dbf
SYSAUX 1105
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
/home/oracle/app/oracle/oradata/sbyorcl/system01.dbf
SYSTEM 806
/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf
EXAMPLE 82
/home/oracle/app/oracle/oradata/sbyorcl/APEX_1246426611663638.dbf
APEX_1246426611663638 2
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
/home/oracle/app/oracle/oradata/sbyorcl/APEX_1265209995679366.dbf
APEX_1265209995679366 8
7 rows selected.
SQL> create tablespace test datafile '/home/oracle/app/oracle/oradata/sbyorcl/test01.dbf' size 10m;
Tablespace created.
SQL> !ls -lrt /home/oracle/app/oracle/oradata/sbyorcl/test01.dbf
-rw-rw---- 1 oracle oracle 10493952 Nov 27 04:06 /home/oracle/app/oracle/oradata/sbyorcl/test01.dbf
SQL> alter database datafile '/home/oracle/app/oracle/oradata/sbyorcl/users01.dbf' resize 500m;
Database altered.
SQL> select file_name,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name='USERS';
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
/home/oracle/app/oracle/oradata/sbyorcl/users01.dbf
USERS 500
SQL>
4. Convert Snapshot Database Back To Physical Standby Database :
DGMGRL> show configuration;5. Verify the Modifications Done In Step (3) :
Configuration - askm_config
Protection Mode: MaxPerformance
Databases:
orcl - Primary databasesbyorcl - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database sbyorcl;
Database - sbyorcl
Role: SNAPSHOT STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds
Apply Lag: 3 minutes 43 seconds
Instance(s):
sbyorcl
Database Status:
SUCCESS
DGMGRL> convert database sbyorcl to physical standby;
Converting database "sbyorcl" to a Physical Standby database, please wait...
Operation requires shutdown of instance "sbyorcl" on database "sbyorcl"
Shutting down instance "sbyorcl"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "sbyorcl" on database "sbyorcl"
Starting instance "sbyorcl"...
ORACLE instance started.
Database mounted.
Continuing to convert database "sbyorcl" ...
Operation requires shutdown of instance "sbyorcl" on database "sbyorcl"
Shutting down instance "sbyorcl"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "sbyorcl" on database "sbyorcl"
Starting instance "sbyorcl"...
ORACLE instance started.
Database mounted.
Database "sbyorcl" converted successfully
DGMGRL> show configuration;
Configuration - askm_config
Protection Mode: MaxPerformance
Databases:
orcl - Primary databasesbyorcl - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database sbyorcl;
Database - sbyorcl
Role: PHYSICAL STANDBY Intended State: APPLY-ON
Transport Lag: 27 seconds
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s):
sbyorcl
Database Status:
SUCCESS
DGMGRL>
SQL> connect /as sysdba
Connected.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database open;
Database altered.
SQL> select username from dba_users where username='SCOTT';
USERNAME
------------------------------
SCOTT
SQL> select tablespace_name from dba_tablespaces where tablespace_name='TEST';
no rows selected
SQL> !ls -lrt /home/oracle/app/oracle/oradata/sbyorcl/test01.dbfls: /home/oracle/app/oracle/oradata/sbyorcl/test01.dbf: No such file or directory
SQL> select file_name,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name='USERS';
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
/home/oracle/app/oracle/oradata/sbyorcl/users01.dbf
USERS 210
SQL>
Please leave me your comments if you have any doubts.
Hope this helps
SRI
Posts
