Creating and Managing a Snapshot Standby Database
Snapshot Standby
Here's a typical
scenario: Say a new application is being deployed on the database and you are
wondering about the impact on database performance. In Oracle Database 11g,
there is that perfect tool (Database Replay), which captures the SQL statements
and replays them, but there is a caveat: you have to run them to see the
impact. You capture from a test system but replaying on the production system
is not feasible. First, it's
not deployed; and
second, even if it were deployed, you couldn't afford to have the app making
changes to other tables. So what do you do to see the impact of the app?
The perfect answer
awaits you in Oracle Database 11g, where the physical standby database
can be temporarily converted into an up dateable one called Snapshot Standby
Database. In that mode, you can run your app—which may modify a lot of
tables—and gauge its impact. Once the impact is assessed, you can convert the
database into a standby undergoing the normal recovery. This is accomplished by
creating a restore point in the database, using the Flashback database feature
to flashback to that point and undo all the changes. Let's see how it is done:
On Standby
SQL> alter
database flashback on;
Database altered.
SQL> alter
database recover managed standby database cancel;
Database altered.
SQL> alter
database flashback on;
Database altered.
Alert log
Starting background
process RVWR
RVWR started with
pid=25, OS id=21816
2012-03-03
18:18:47.119000 +05:30
Allocated 8388608
bytes in shared pool for flashback generation buffer
2012-03-03
18:18:48.171000 +05:30
Flashback Database
Enabled at SCN 1010028
SQL> alter
database recover managed standby database disconnect from session;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
First, start recovery on the standby, if not going on
already:
SQL> select max(sequence#) from v$log;
MAX(SEQUENCE#)
--------------
40
On Standby
At this point, you may create the snapshot standby database.
Remember, it enables Flashback logging, so if you haven't configured the flash recovery
area, you will get a message like this:
ORA-38784: Cannot create restore point
'SNAPSHOT_STANDBY_REQUIRED_01/12/2008
00:23:14'.
ORA-38786: Flash recovery area is not enabled.
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 10G
Now that the formalities are completed, you can convert this
standby database to snapshot standby using this simple command:
SQL> alter database recover managed standby database
cancel;
Database altered.
SQL> alter
database convert to snapshot standby;
Database altered.
Alert log
Created guaranteed restore point
SNAPSHOT_STANDBY_REQUIRED_03/03/2012 18:23:20
krsv_proc_kill: Killing 3 processes (all RFS)
2012-03-03 18:23:21.732000 +05:30
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
SMON: disabling cache recovery
2012-03-03 18:23:22.882000 +05:30
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 1010028
Resetting resetlogs activation ID 1246957966 (0x4a53118e)
Online log +DATA/stdy/onlinelog/group_1.262.776967645:
Thread 1 Group 1 was previously cleared
Online log +FRA/stdy/onlinelog/group_1.256.776967651: Thread
1 Group 1 was previously cleared
Online log +DATA/stdy/onlinelog/group_2.263.776967657:
Thread 1 Group 2 was previously cleared
Online log +FRA/stdy/onlinelog/group_2.257.776967663: Thread
1 Group 2 was previously cleared
Online log +DATA/stdy/onlinelog/group_3.264.776967673:
Thread 1 Group 3 was previously cleared
Online log +FRA/stdy/onlinelog/group_3.258.776967677: Thread
1 Group 3 was previously cleared 2012-03-03 18:23:23.916000 +05:30 Standby
became primary SCN: 1010026 Setting recovery target incarnation to 3 CONVERT TO
SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby Completed:
alter database convert to snapshot standby
SQL> select * from v$restore_point;
SCN
DATABASE_INCARNATION# GUA STORAGE_SIZE
---------- --------------------- --- ------------
TIME
---------------------------------------------------------------------------
RESTORE_POINT_TIME
PRE
---------------------------------------------------------------------------
---
NAME
--------------------------------------------------------------------------------
1013455 2 YES 15941632
20-FEB-13 05.50.41.000000000 PM
YES
SNAPSHOT_STANDBY_REQUIRED_02/20/2013 17:50:41
Now bounce the database:
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> startup force
ORACLE instance started.
Total System Global Area
422670336 bytes
Fixed Size
1300352 bytes
Variable Size
343935104 bytes
Database Buffers
71303168 bytes
Redo Buffers
6131712 bytes
Database mounted.
Database opened.
Now the database is open for read/write operations:
SQL> select open_mode, database_role from v$database;
OPEN_MODE
DATABASE_ROLE
---------- ----------------
READ WRITE SNAPSHOT STANDBY
Alert log
RFS[1]: Assigned to RFS process 22430
RFS[1]: Identified database type as 'snapshot standby':
Client is ARCH pid 10428
You can do changes in this database now. This is a perfect
place to replay the captured workload using Database Replay. You can then
perform the system changes in this database and replay several times to see the
impact of the changes. As this is a copy of the production database, the replay
will be an accurate representation of the workload.
After your testing is completed, you would want to convert
the snapshot standby database back to a regular physical standby database. Just
follow the steps shown below:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area
422670336 bytes
Fixed Size
1300352 bytes
Variable Size
343935104 bytes
Database Buffers
71303168 bytes
Redo Buffers
6131712 bytes
Database mounted.
SQL> alter database convert to physical standby;
Database altered.
Alert log
krsv_proc_kill: Killing 1 processes (all RFS)
2012-03-03 18:30:01.164000 +05:30
Flashback Restore Start
2012-03-03 18:30:09.992000 +05:30
Flashback Restore Complete
Guaranteed restore point
dropped
Clearing standby activation ID 1304761003 (0x4dc512ab)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to
create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Completed: alter database convert to physical standby
2012-03-03 18:30:21.930000 +05:30
destination database instance is 'started' not 'mounted'
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area
422670336 bytes
Fixed Size
1300352 bytes
Variable Size
343935104 bytes
Database Buffers
71303168 bytes
Redo Buffers
6131712 bytes
Database mounted.
SQL> select open_mode, database_role from v$database;
OPEN_MODE
DATABASE_ROLE
---------- ----------------
MOUNTED PHYSICAL
STANDBY
Alert log
RFS[1]: Assigned to RFS process 22688
RFS[1]: Identified database type as 'physical standby':
Client is ARCH pid 10428
Start the managed recovery process:
SQL> alter database recover managed standby database
disconnect;
Now the standby database is back in managed recovery mode.
Needless to say, when the database was in snapshot standby mode, the archived logs
from primary were not applied to it. They will be applied now and it may take
some time before it completely catches up. Snapshot standby database allows you
to use the standby database to accurately predict changes to production
database before they were made. But that's not it; there is another advantage
as well. Remember, we could have used RTA in this case, which causes the
changes made to the primary appear instantly on the standby, provided the
network is available? Well, what if someone makes a mistake on the primary
database, such as running a massive update or changing some code? In previous
versions we deliberately use a delay in the standby database to stop these
errors propagating to the standby. But that delay also means the standby can't
be activated properly or be used as an active copy of production. Not anymore.
Since you can flashback the standby database, you need not keep the delay. If
there is a problem, you can always flashback to a previous state.
