Create Restore Point and Flashback Database in RAC environment

1.1    Purpose

To enable the flashback on RAC database , create an restore point , after the testing flashback the database to  the earlier restore point .  This process is widely used in testing during pre-releases of Application code in the database test environment. 

1.2    Scope

This document covers the procedure to enable the flashback  , create an Restore Point , flashback the database to the restore point.

Create Restore Point and Flashback Database in RAC environment.

Introduction : 

To enable the flashback on RAC database , create an restore point , after the testing flashback the database to  the earlier restore point

Description : 

This process is widely used in testing of various parameters like Performance, Load, and Functionality etc during pre-releases of Application code in the database test environment.

1. Check the flashback status in the database

Select flashback_on from v$database;

2. If the flashback is not enabled in the database , enable ON by the below steps

srvctl stop database –d  dbname

In  any one of the node start the instance in mount mode

  
  sqlplus “/ as  sysdba”

  startup mount

  Alter database flashback ON ;

  Select flashback_on from v$database;

From o/s prompt  start the database   ,  srvctl start  database –d  dbname

3. After  starting the database  using srvctl

In sqlplus , create an new restore point

CREATE RESTORE POINT BEF_TEST GUARANTEE FLASHBACK DATABASE ; 
à BEF_TEST is the restore point name

Check the newly created restore point ,

Select * from v$restore_point;

4. After the Application testing on various parameter like Performance , Load , Functionality  etc  are completed . Generate the AWR reports  of the database  for the test period.

5. After the necessary reports like AWR , ADDM reports etc are collected ,  we can flashback the database to the restore point created  earlier  (BEF_TEST) , using the below steps

Srvctl stop database –d  dbname

Start the instance in 1 node  ,

sqlplus “/ as sysdba”

startup mount;

FLASHBACK DATABASE TO RESTORE POINT BEF_TEST;

ALTER DATABASE OPEN RESETLOGS;

6. Start the database ,  srvctl start database –d dbname

7. We can drop the restore point  from the sqlplus prompt

DROP RESTORE POINT  BEF_TEST;

8. Clear the archive logs generated during the testing

rman target / nocatalog

RMAN> delete noprompt archivelog all;

      

Advantages :

1. Before any changes in the Production environment  , it is quite often test in the Test environment , at times it is recursive .

So during each recursion capture the database reports , and compare the results .

2. Best usage of the Oracle 10g flashback feature, instead of the taking the full backup and restoring to the previous state.

Converting a Physical Standby Database into a Snapshot Standby Database

Converting a Physical Standby Database into a Snapshot Standby Database

Before starting into the steps it is worth to know what is snapshot standby database.

A snapshot standby database is created from physical standby database. Once we create a snapshot standby database it continues to receive redo log from primary database and archive the redo logs but it does not apply redo log or archived redo log to the snapshot standby 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. As once we create snapshot standby database in READ WRITE mode then we can update the database locally, so 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.

The following are the steps to convert a physical standby database into a snapshot standby database in RAC environment.

Step 01: Stop Redo Apply in Physical Standby Database, if it is active.

Login to any database instance of the Physical standby database if it is RAC. In case of Non-RAC you have only once instance/one database.

- Check status by,

SQL> select open_mode , database_role, name, DB_UNIQUE_NAME  from v$database;

OPEN_MODE            DATABASE_ROLE    NAME      DB_UNIQUE_NAME

-------------------- ---------------- --------- ------------------------------

MOUNTED              PHYSICAL STANDBY BDDIPDC   BDDIPDRS

- I have also checked the instance to know in which instance I am performing stop redo log.

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

bddipdc1

- Issue stop redo log apply command.

SQL> alter database recover managed standby database cancel;

Database altered.

Step 02: In case of RAC database, shut down all but one instance.

If you are non RAC environment then simply you can ignore this step. In case of RAC environment just you need to shutdown other instances except one. In this case I have 2 node RAC and I am just shutting down bddipdc2 instance.

$ sqlplus sys/oracle@bddipdrs2 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 8 12:48:48 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> select open_mode , database_role, name, DB_UNIQUE_NAME  from v$database;

OPEN_MODE            DATABASE_ROLE    NAME      DB_UNIQUE_NAME

-------------------- ---------------- --------- ------------------------------

MOUNTED              PHYSICAL STANDBY BDDIPDC   BDDIPDRS

SQL>  select instance_name from v$instance;

INSTANCE_NAME

----------------

bddipdc2

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Step 03: Ensure that database is mounted and not open. Also ensure that a fast recovery area has been configured.

It is not necessary for flashback database to be enabled but you should enable FLASHBACK database if you want to convert a snapshot standby database back into a physical standby database.

Checking fast recovery area by,

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

SQL> show parameter db_reco

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      +RECOVERY

db_recovery_file_dest_size           big integer 800G

which means fast recovery area is configured as we are using DB_RECOVERY_FILE_DEST as archival destination.

You can check it is mounted but not opened by,

SQL> select open_mode , database_role, name, DB_UNIQUE_NAME  from v$database;

OPEN_MODE            DATABASE_ROLE    NAME      DB_UNIQUE_NAME

-------------------- ---------------- --------- ------------------------------

MOUNTED              PHYSICAL STANDBY BDDIPDC   BDDIPDRS

You can check flashback database option by,

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

YES

Step 04: Issue the "CONVERT TO SNAPSHOT STANDBY" command.

SQL> alter database convert to snapshot standby;

Database altered.

Step 05: Open the database and check the database_role and open_mode.

SQL> alter database open;

Database altered.

SQL>  select open_mode , database_role, name, DB_UNIQUE_NAME  from v$database;

OPEN_MODE            DATABASE_ROLE    NAME      DB_UNIQUE_NAME

-------------------- ---------------- --------- ------------------------------

READ WRITE           SNAPSHOT STANDBY BDDIPDC   BDDIPDRS

Now let's see in the alert log to know what happens in the backend.

alter database recover managed standby database cancel

Tue Mar 08 12:17:24 2011

MRP0: Background Media Recovery cancelled with status 16037

Errors in file /u01/app/oracle/diag/rdbms/bddipdrs/bddipdc1/trace/bddipdc1_pr00_17371.trc:

ORA-16037: user requested cancel of managed recovery operation

Recovery interrupted!

Waiting for MRP0 pid 17366 to terminate

Errors in file /u01/app/oracle/diag/rdbms/bddipdrs/bddipdc1/trace/bddipdc1_pr00_17371.trc:

ORA-16037: user requested cancel of managed recovery operation

Tue Mar 08 12:17:24 2011

MRP0: Background Media Recovery process shutdown (bddipdc1)

Managed Standby Recovery Canceled (bddipdc1)

Completed: alter database recover managed standby database cancel

Tue Mar 08 12:17:42 2011

Reconfiguration started (old inc 8, new inc 10)

List of instances:

 1 (myinst: 1)

 Global Resource Directory frozen

 * dead instance detected - domain 0 invalid = TRUE

 Communication channels reestablished

 Master broadcasted resource hash value bitmaps

 Non-local Process blocks cleaned out

Tue Mar 08 12:17:42 2011

 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived

Tue Mar 08 12:17:42 2011

 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived

 Set master node info

 Submitted all remote-enqueue requests

 Dwn-cvts replayed, VALBLKs dubious

 All grantable enqueues granted

 Post SMON to start 1st pass IR

 Submitted all GCS remote-cache requests

 Post SMON to start 1st pass IR

 Fix write in gcs resources

Reconfiguration complete

Tue Mar 08 12:17:53 2011       

alter database convert to snapshot standby

Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/08/2011 12:17:53

Tue Mar 08 12:17:53 2011

krsv_proc_kill: Killing 219043332100 processes (all RFS)

Begin: Standby Redo Logfile archival

End: Standby Redo Logfile archival

RESETLOGS after incomplete recovery UNTIL CHANGE 449582938

Resetting resetlogs activation ID 1523683415 (0x5ad19057)

Online log +DATA/bddipdrs/onlinelog/group_1.351.728933147: Thread 1 Group 1 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_1.256.728933147: Thread 1 Group 1 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_2.356.728933149: Thread 1 Group 2 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_2.326.728933149: Thread 1 Group 2 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_4.379.728933149: Thread 2 Group 4 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_4.366.728933149: Thread 2 Group 4 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_14.374.728933149: Thread 2 Group 14 was previously cleared

Online log +DATA/bddipdrs/onlinelog/group_14.354.728933149: Thread 2 Group 14 was previously cleared

Standby became primary SCN: 449582936

Tue Mar 08 12:17:55 2011

Setting recovery target incarnation to 6

CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby

Completed: alter database convert to snapshot standby

alter database open

This instance was first to open

Picked broadcast on commit scheme to generate SCNs

Tue Mar 08 12:18:00 2011

Assigning activation ID 1523975741 (0x5ad6063d)

Thread 1 opened at log sequence 1

  Current log# 1 seq# 1 mem# 0: +DATA/bddipdrs/onlinelog/group_1.351.728933147

  Current log# 1 seq# 1 mem# 1: +DATA/bddipdrs/onlinelog/group_1.256.728933147

Successful open of redo thread 1

Tue Mar 08 12:18:01 2011

ARC4: Becoming the 'no SRL' ARCH

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Tue Mar 08 12:18:01 2011

ARC0: Becoming the 'no SRL' ARCH

Tue Mar 08 12:18:01 2011

SMON: enabling cache recovery

Instance recovery: looking for dead threads

Instance recovery: lock domain invalid but no dead threads

Successfully onlined Undo Tablespace 2.

Dictionary check beginning

Dictionary check complete

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is AL32UTF8

No Resource Manager plan active

Starting background process GTX0

Tue Mar 08 12:18:02 2011

GTX0 started with pid=43, OS id=24612

Starting background process RCBG

Tue Mar 08 12:18:02 2011

RCBG started with pid=44, OS id=24614

replication_dependency_tracking turned off (no async multimaster replication found)

Redo thread 2 internally disabled at seq 1 (CKPT)

Tue Mar 08 12:18:02 2011

ARC3: Becoming the 'no SRL' ARCH

ARC3: Archiving disabled thread 2 sequence 1

Archived Log entry 48206 added for thread 2 sequence 1 ID 0x0 dest 1:

Tue Mar 08 12:18:03 2011

Starting background process QMNC

Tue Mar 08 12:18:03 2011

QMNC started with pid=46, OS id=24618

LOGSTDBY: Validating controlfile with logical metadata

LOGSTDBY: Validation complete

Completed: alter database open

Note that, when the physical standby is converted to a snapshot standby database a guaranteed restore point is created. In this case we see the name is SNAPSHOT_STANDBY_REQUIRED_03/08/2011. When the snapshot standby is converted back into a physical standby this restore point will be used to flashback the standby to its original state prior to the conversion.

If you want to convert the snapshot standby database into physical standby database then you simply need to do,

SQL> shutdown immediate

SQL> startup mount

SQL> alter database convert to physical standby;