Testing a Physical Standby using Snapshot Standby in Oracle 10g

TESTING A PHYSICAL STANDBY DATABASE IN ORACLE 10G Using SNapshot Standby

This procedure is very useful when you have physical standby databases for testing and other purposes that require read-write access to the standby database. Also, it improves your checklist in the event of an error or disaster.
By using Snapshot standby databases, redo data is not applied until you convert the snapshot standby database back into a physical standby database, and after all local updates to the snapshot standby database are discarded.
Activation of the standby
On the standby database

1. Stop dataguard brokers on standby:

SQL> alter system set dg_broker_start=FALSE;
Get the SCN

SQL> select current_scn from v$database;

2. Cancel the managed recovery:

SQL> alter database recover managed standby database cancel;
3. Create the restore point.A restore point can be specified such that it guarantees the database can be recovered to a particular point-in-time and eliminates the need to manually record an SCN or timestamp to use with the Flashback database and Flashback table operations:

SQL> create restore point TEST_NEW_FEATURE guarantee flashback database;

Now confirm the scn from restore point:
SQL> col name form a40;
SQL> select scn, time, name from v$restore_point where name = ‘TEST_NEW_FEATURE’;

4. Prepare the primary

Archive logsWhen using the standby redo logs, this step is essential to ensure that the database can be properly flashed back to the restore point.
SQL> alter system archive log current;
SQL> alter system switch logfile;
SQL> alter system switch logfile;

Stop shipment of logs
SQL> alter system set log_archive_dest_state_2=DEFER
5. Activate the Physical Standby
Activation of the standby:

SQL> alter database activate standby database;
SQL> startup mount force;
SQL> alter database set standby database to maximize performance;
(This is used in case you have not set it before)

SQL> alter database open;

disable log_archive_dest_2 (this will prevent archive logs being sent to primary):
SQL> alter system set log_archive_dest_state_2=DISABLE;
6. Revert the snapshot database to physical standby

Flashback to the restore point:
SQL> STARTUP MOUNT FORCE;
SQL> FLASHBACK DATABASE TO RESTORE POINT TEST_NEW_FEATURE;
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE;
SQL> STARTUP MOUNT FORCE;

Re-enable log shipping on the primaryEnable shipment:
SQL> alter system set log_archive_dest_state_2=ENABLE

To re-enable log shipping on the standby, enable this parameter:
SQL> alter system set log_archive_dest_state_2=ENABLE

ORA-38753: Cannot flashback data file ; no flashback log data. ORA-01110: data file

CAUTION !!!! DO NOT DO THIS ON YOUR PRIMARY. IF YOU DO THEN YOU WOULD REQUIRE MEDIA RECOVERY ON THE DATAFILE YOU BROUGHT OFFLINE !!!

 

If your getting this error when you converting a Snapshot Standby Database (11gR2) back to Physical Standby Database.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

ERROR at line 1: ORA-38753: Cannot flashback data file 4; no flashback log data. ORA-01110: data file 4: ‘+DATA/swxstandby/datafile/swx.271.780160475’

When you get this error do as below :

SQL> select a.file#, a.name file_name, b.ts#, b.name ts_name, b.flashback_on from v$datafile a, v$tablespace b where a.ts#=b.ts# ;

TS# TS_NAME                        FLA ———- ——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————— ———- —————————— —          1 +DATA/swxstandby/datafile/system.270.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             0 SYSTEM                         YES          2 +DATA/swxstandby/datafile/sysaux.269.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             1 SYSAUX                         YES          3 +DATA/swxstandby/datafile/undotbs1.268.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           2 UNDOTBS1                       YES          5 +DATA/swxstandby/datafile/undotbs2.267.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           5 UNDOTBS2                       YES          4 +DATA/swxstandby/datafile/swx.271.780160475                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                7 SWX                            NO          6 +DATA/swxstandby/datafile/osms.266.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               9 OSMS                           YES

6 rows selected.

See which datafile has flashback_on colmn set as NO

SQL> alter database datafile 4 offline;

Database altered.

SQL> select flashback_on from v$database ;

FLASHBACK_ON —————— RESTORE POINT ONLY

SQL> select name, flashback_on from v$tablespace ;

NAME                           FLA —————————— — SYSTEM                         YES SYSAUX                         YES UNDOTBS1                       YES SWX                            NO TEMP                           YES UNDOTBS2                       YES OSMS                           YES

SQL> alter tablespace SWX flashback on;

Tablespace altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 6415597568 bytes

Fixed Size                  2171296 bytes

Variable Size            1979719264 bytes

Database Buffers         4429185024 bytes

Redo Buffers                4521984 bytes

Database mounted.

SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount ORACLE instance started.

Total System Global Area 6415597568 bytes

Fixed Size                  2171296 bytes

Variable Size            1979719264 bytes

Database Buffers         4429185024 bytes

Redo Buffers                4521984 bytes Database mounted.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select flashback_on from v$database ;

FLASHBACK_ON —————— NO

SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE            SWITCHOVER_STATUS    DATABASE_ROLE ——————– ——————– —————- MOUNTED              RECOVERY NEEDED      PHYSICAL STANDBY

Oracle 11g Snapshot Standby Database (Read- Write mode for standby db)

Oracle 11g Dataguard introduced powerful new features viz. Active Data Guard and Snapshot Standby Database. Snapshot standby database specially is an amazing feature. Where you can convert your standby database into read-write mode while the log shipping is happening. Once you finish your testing (feel free to drop tables, run rogue update statements or anything under the sun) you can tell the database to apply the already received logs.  You never lose a single transaction. Because in the background the archivelogs are being shipped,  just not being applied. I will demonstrate how to convert you physical standby database into snapshot database and refresh it again.

1. Connect to the Standby database

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

SQL> conn sys as sysdba
Enter password:
Connected.

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
—————- ——————–
PHYSICAL STANDBY NOT ALLOWED

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> show parameter recover

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
SQL> alter system set db_recovery_file_dest=’E:\recovery_dest’ scope=spfile;

System altered.

SQL> alter system set db_recovery_file_dest_size=5g;

System altered.

SQL> show parameter recover

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 5G
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0

SQL> alter system set db_recovery_file_dest=’location=E:\recovery_dest’ scope=spfile;

System altered.

SQL> show parameter recover

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest                string      E:\recovery_dest
db_recovery_file_dest_size           big integer 5G
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
SQL> alter database mount standby database;

Database altered.

SQL> alter database convert to snapshot standby;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
——————– —————-
MOUNTED              SNAPSHOT STANDBY

SQL> alter database open;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
——————– —————-
READ WRITE           SNAPSHOT STANDBY

After you finish your testing, convert the snapshot database back to physical standby again.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  778387456 bytes
Fixed Size                  1383768 bytes
Variable Size             234881704 bytes
Database Buffers          536870912 bytes
Redo Buffers                5251072 bytes

SQL> alter database mount;

Database altered.

SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  778387456 bytes
Fixed Size                  1383768 bytes
Variable Size             234881704 bytes
Database Buffers          536870912 bytes
Redo Buffers                5251072 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
——————– —————-
MOUNTED              PHYSICAL STANDBY

 

Helpful Link:

http://repettas.wordpress.com/2008/10/24/rman-11g-snapshot-standby-database/