Oracle Technologies Blog

By ASKM

Creating Snapshot Standby Database

Posted by Srikrishna Murthy Annam on January 17, 2012

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
  3. Do Some Testing In Snapshot Database
  4. Convert Snapshot Database Back To Physical Standby Database
  5. 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 database
sbyorcl – 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.

  • 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;

Configuration – askm_config

  Protection Mode: MaxPerformance
Databases:
orcl    – Primary database
sbyorcl – 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 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:   27 seconds
Apply Lag:       (unknown)
Real Time Query: OFF
Instance(s):
sbyorcl

Database Status:
SUCCESS

DGMGRL>

 

5. Verify the Modifications Done In Step (3) :

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.dbf
ls: /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.

 

Thanks

SRI

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: