Using10gStandbyForTesting&Reporting

 

DBA Tips Archive for Oracle

DBA Tips Archive for Oracle Oracle  

Page 1 of 8

IT Services Queensland All managed IT services, Database Services, Development Projects www.dhanushinfotech.…

Using a Physical Standby Database for Read/Write Testing and Reporting (Oracle 10g) by Jeff Hunter, Sr. Database Administrator

Contents Introduction  Introduction   Enable Flashback Database on the Physical Standby Database  Database  Database   Activate the Physical Standby Database as a Read/Write Clone of the Primary Database  Reporting  Use the Activated Database For Testing or Reporting  Revert Physical Standby Database Back To Its Original State State   About the Author  Author 

Introduction Introduced in Oracle Database 10g  10g , Flashback Database provides users with a quick method for rewinding  an  an entire database to a previous point in time or SCN value. This is a valuable tool used by Database Administrators to protect critical databases against user failure . Consider for example a scenario in which a user accidentally deletes several million records from a few key table on a production database. Prior to Oracle Database 10g  10g  and  and Flashback Database, the DBA would most likely need to perform a complex pointin-time recovery using RMAN to a point in time right before the DELETE occurred. Although a realistic solution, it has the potential for a high (MTTR ) given thebackup database will beby offline during the entire process of restoring theMean-Time-To-Recovery database's datafiles from(MTTR) the most recent followed a point-in-time recovery of the archived redo logs that have accumulated since the last backup. If, however, the database had flashback database enabled, the DBA could avoid all of the costly and risky operations required for a point-in-time recovery operation and simply rewind the database to the same point-in-time in a fraction of the time using the FLASHBACK DATABASE command. In addition to providing a quick and efficient method for recovering from user-induced errors, Flashback Database can also be used in combination with Oracle Data Guard, restore points, and physical standby database to temporarily open a physical standby database in read/write mode for QA testing, development fixes, or reporting purposes and then flashed back to a point in the past (the restore point) to be reverted back to a physical standby database. After the physical database is flashed back, Data Guard will automatically synchronize it with the primary which eliminates the need to formally re-create the physical standby database from a backup copy of the primary pr imary database. Throughout this entire process, the primary database can remain online without the need to log off users or shut it down. 1 provides an overview of how a physical standby database can be temporarily activated as a Figure 1 provides read/write clone of the primary database and reverted back to its role as a physical standby database using Flashback Database. This process can executed any number of times if necessary.

http://www.idevelopment.info/da http://www.ide velopment.info/data/Oracle/DB ta/Oracle/DBA_tips/Data_G A_tips/Data_Guard/DG_52.sh uard/DG_52.shtml tml

30/03/2011

 

DBA Tips Archive for Oracle

Page 2 of 8

Figure 1: 1: Opening Physical Standby Database in Read/Write for Testing

It is assumed that a primary and one physical standby database is already configured in order to perform the steps described in this guide. The examples used in this guide will make use of the Oracle Data Guard configuration described in the article below: Data Guard Configuration Example - (Oracle Standby)  ( Oracle 10g, Physical Standby) 

Enable Flashback Database on the Physical Standby Database The first step is to configure the physical standby database to use flashback logging  for  for Flashback Database operations. Although not a requirement for this exercise, it is best practice to also enable Flashback Database on the the primary. 1. In order to use Fl Flashb ashback ack Database, Database, the the physical physical database database must be running running in Archive Archive Log Mode. Mode. Given the fact that this is a Data Guard configuration, both the primary and physical standby database will already be running in archive log mode and no action is required.

SQL> archive log list  Database log mode Automatic archival Archive destination Oldest online log sequence Next log sequence to archive Current log sequence

Mode  Archive Mode  Enabled USE_DB_RECOVERY_FILE_DEST USE_DB_RECOVERY_FILE_DEST 208 0 210

2. Flash Flashback back Databas Database e requires requires that the flashbac flashback k logs reside reside in the Flash Recove Recovery ry Area (FRA). (FRA). Although the FRA may already be configured for your environment, the flashback logs can significantly increase FRA usage. It is not unheard of to double the size of the FRA to utilize Flashback Database. If a FRA does not exist, it will need to be created. For example, run the following statements on the physical standby database to configure a 32GB flash recovery area in the /u03/flash_recovery_area directory with a retention time of 24 hours:

http://www.idevelopment.info/da http://www.ide velopment.info/data/Oracle/DB ta/Oracle/DBA_tips/Data_G A_tips/Data_Guard/DG_52.sh uard/DG_52.shtml tml

30/03/2011

 

DBA Tips Archive for Oracle

Page 3 of 8

SQL> alter system set db_recovery_ db_recovery_file_dest='/u file_dest='/u03/flash_recov 03/flash_recovery_area'; ery_area';  SQL> alter system set db_recovery_ db_recovery_file_dest_siz file_dest_size=32g; e=32g;  SQL> alter system set db_flashback db_flashback_retention_ta _retention_target=1440; rget=1440; 

The size and location of the FRA and the Flashback Database retention policy time listed above should be modified for your environment. 3. Stop Redo Apply Apply on the the physical physical standby standby database database and and verify verify it is in in MOUNT mode.

SQL> alter database recover managed standby database cancel;  SQL> select open_mode from v$database;  OPEN_MODE ---------MOUNTED

4. Tu Turn rn on Fla Flash shba back ck Logg Loggin ing. g.

SQL> alter database flashback on; 

5. Plac Place e the physical physical standby standby database database back into into recovery recovery mode by st startin arting g Redo Apply. Apply.

SQL> alter database recover managed standby database using current logfile disco

Activate the Physical Standby Database as a Read/Write Clone of the Primary Database Perform the following steps to activate the physical standby database as a production database clone which can be used for QA testing, development fixes, or reporting purposes. The clone c lone database will be resynchronized back with the primary database and returned to its original state as a physical standby database in the next section. section.

In Oracle Database 10g  10g , all redo application is paused while the database is activated.   The standby will not receive any redo from the primary database while it is opened in read/write mode and therefore cannot provide disaster protection. It is recommended that there be at least two physical standby databases participating in the configuration so that the primary database remains protected against data loss.

1. Prepare the physical standby database to be activated.  activated.  On the physical standby database, stop Redo Apply and create a restore point.

SQL> alter database recover managed standby database cancel;  Database altered.

_standby guarantee  guarantee flashback database;  SQL> create restore point before_open point before_open_standby

http://www.idevelopment.info/da http://www.ide velopment.info/data/Oracle/DB ta/Oracle/DBA_tips/Data_G A_tips/Data_Guard/DG_52.sh uard/DG_52.shtml tml

30/03/2011

 

DBA Tips Archive for Oracle

Page 4 of 8

Restore point created.

When you create a guaranteed restore point, you associate an easy-to-remember name such as before_open_standby with a timestamp or SCN so that you can later flash back the database to a name instead of specifying an exact SCN or time.

SQL> select scn, storage_size, time, name from v$restore_point;  SCN STORAGE_SIZE TIME NAME ---------- ------------ ---------------------------------------------------------------- --------------------------------------3497333 15941632 23-FEB-11 10.43.43.000000000 10.43.43.000000000 AM BEFORE_OPEN_STANDBY  BEFORE_OPEN_STANDBY 

2. Prepare the primary database to have the physical standby be diverged.  diverged.   Prepare the primary database to be split from the physical standby by archiving the current log file. This step is necessary in order to write the SCN of the restore point (created in step 1) to the redo stream and archive it to the physical standby database. To accomplish this, switch logs on the primary database.

SQL> alter system archive log current;   System altered.

When using standby redo log files, this step is essential to ensure the database can be properly flashed back to the restore point. On the primary database (on all instances if this is a Real Applications Cluster), defer the archival of redo data to the destination associated with the physical standby database that will be opened, and switch logs one more time to stop redo transport.

SQL> alter system set log_archive_dest_state_2= log_archive_dest_state_2=defer; defer;  System altered.

SQL> alter system switch logfile;   System altered.

3. Activate the physical standby database.  database.  On the physical standby database, perform the following steps: Activate the physical standby database.

SQL> alter database activate standby database;   Database altered.

Skip the next statement if the physical standby has not been opened read-only since the instance was last started.

SQL> startup mount force;  ORACLE instance started.

http://www.idevelopment.info/da http://www.ide velopment.info/data/Oracle/DB ta/Oracle/DBA_tips/Data_G A_tips/Data_Guard/DG_52.sh uard/DG_52.shtml tml

30/03/2011

 

DBA Tips Archive for Oracle

Page 5 of 8

Total System Global Area 1610612736 bytes Fixed Size 2084296 bytes Variable Size 385876536 bytes Database Buffers 1207959552 bytes Redo Buffers 14692352 bytes Database mounted.

The last step is to set the protection mode to maximum performance and open the database for read/write access.

SQL> alter database set standby database to maximize performance;  Database altered.

SQL> alter database open;  Database altered.

If the standby database that was opened read/write temporarily has remote archive log destinations, you should disable them. In this way, the read/write testing or reporting database will not propagate its temporary changes to other standby databases in the original Data Guard environment.

SQL> alter system set log_archive_dest_state_2= log_archive_dest_state_2=defer; defer;  System altered.

After the standby database is activated, its protection mode is downgraded to the maximum performance mode, because there is no standby database configured to protect the database against data loss while it is temporarily activated as a production database. Note that this protection mode setting does not affect the protection mode of the original primary database, it affects only the activated standby database. When the activated standby database is converted back to a physical standby database, its protection mode is automatically changed to match that of the original primary database.

Use the Activated Database For Testing or Reporting Once the physical standby database has been activated, it is a full-blown clone of the primary database. You may perform QA testing, run reports, fix bugs, test new code, or create objects on the new activated database for as long as needed, independent of the primary database. While the database is activated, it is not receiving redo data from the primary database and cannot provide disaster protection. It is recommended that there be at least two physical standby databases participating in the configuration so that the primary database remains protected against data loss. Also, any results stored in the activated database will be lost when you later flash back the database to before the activation time. Results that should be saved must be copied or exported out of the activated database before flashing it back.

Revert Physical Standby Database Back To Its Original State After testing is completed, you need to resynchronize the activated database with the primary database. Issue the following statements on the activated database to quickly flash back to the guaranteed ) and resynchronize it withit the primary database. restore point created earlier (before_open_standby

http://www.idevelopment.info/da http://www.ide velopment.info/data/Oracle/DB ta/Oracle/DBA_tips/Data_G A_tips/Data_Guard/DG_52.sh uard/DG_52.shtml tml

30/03/2011

 

DBA Tips Archive for Oracle

Page 6 of 8

1. Revert Physical Standby Database using Flashback Database.  Database. 

SQL> startup mount force;  ORACLE instance started. Total System Global Area 1610612736 bytes Fixed Size 2084296 bytes Variable Size 385876536 bytes Database Buffers 1207959552 bytes Redo Buffers 14692352 bytes Database mounted.

SQL> flashback database to restore point before_open_s point before_open_standby tandby; ;  Flashback complete.

SQL> alter database convert to physical standby;  Database altered.

SQL> startup mount force;  ORACLE instance started. Total System Global Area 1610612736 bytes Fixed Size Variable Size Database Buffers Redo Buffers Database mounted.

2084296 385876536 1207959552 14692352

bytes bytes bytes bytes

standby; ;  SQL> drop restore point before_open_ point before_open_standby Restore point dropped.

2. Catch up the standby database to the t he primary database. database.   The method you use in this step will depend on how far the activated standby database lags behind the primary database in its application of redo data. Let archive gap resolution fetch all missing archived redo log files and allow Redo Apply to apply the gap.  gap.  If the activated database has not fallen too far behind the original primary database, issue the following statement on the standby database to resynchronize it with the primary database and restart Redo Apply. For example:

SQL> alter system set log_archive_dest_state_2= log_archive_dest_state_2=enable enable scope=both;  System altered.

SQL> alter database recover managed standby database using current logfile Database altered.

3.. Then, go to step 3 Create an incremental backup on the primary and apply it to the standby.  standby. 

http://www.idevelopment.info/da http://www.ide velopment.info/data/Oracle/DB ta/Oracle/DBA_tips/Data_G A_tips/Data_Guard/DG_52.sh uard/DG_52.shtml tml

30/03/2011

 

DBA Tips Archive for Oracle

Page 7 of 8

If the activated database has fallen too far behind the original primary database (for example, if there are not sufficient log files available), you can take an incremental backup from the primary database and apply it to the standby database. The following guide provides the steps necessary on use an RMAN incremental backup to roll forward and resynchronize a physical standby database that lags far behind the primary database: Using RMAN Incremental Backups to Roll Forward a Physical Standby Database (Oracle (Oracle 10g ) 

If the standby database lags very far behind the primary database, it may be quicker to apply an incremental backup created from the primary database.

After you apply an incremental backup to the standby database, you typically need to apply more redo to the standby database to activate the physical standby database again for read/write testing or reporting purposes. More specifically, you might need to apply the redo generated by the primary database while the incremental backup was taken. Otherwise, issuing an ALTER DATABASE ACTIVATE STANDBY DATABASE will return an error. 3. Re-enable archiving to the physical standby database destination.  destination.   On the primary database, issue the following statement to re-enable archiving to the physical standby database:

SQL> alter system set log_archive_ log_archive_dest_state_2= dest_state_2=enable enable scope=both;  System altered.

About the Author Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Inc. located in ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX, Linux, and Windows server environment. Jeff's other interests include mathematical encryption theory, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 16 years and maintains his own website site at: http://www.iDevelopment.info http://www.iDevelopment.info.. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science.

Free Log File Analyzer Search, Alert and Monitor ALL Your IT data. Free Download! www.splunk.com/ITSearch Online Data Backup Protect and secure your business. Nationwide service & local support.  www.anittel.com.au Online Data Backup Australia's Largest Data Protection And Document Storage Services!  www.BackOnline.com.au McAfee Online Backup 2011 Protect Your Digital Memories Now. Buy Now For Easy & Safe Protection! www.McAfee.c

Copyright (c) 1998-2011 Jeffrey M. Hunter. All rights reserved. reserved.   http://www.idevelopment.info is  is the copyright of Jeffrey M. Hunter and is All articles, scripts and material located at the Internet address of http://www.idevelopment.info protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission.. Application to host any of the material elsewhere can be made by contacting me at permission at jhunter@idevel  [email protected] opment.info..

http://www.idevelopment.info/da http://www.ide velopment.info/data/Oracle/DB ta/Oracle/DBA_tips/Data_G A_tips/Data_Guard/DG_52.sh uard/DG_52.shtml tml

30/03/2011

 

DBA Tips Archive for Oracle

Page 8 of 8

I have made every effort and taken great care in making sure that t he material included on my web site is technically accurate, accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on Wednesday, 23-Feb-2011 12:16:34 EST Page Count: 312  312 

http://www.idevelopment.info/da http://www.ide velopment.info/data/Oracle/DB ta/Oracle/DBA_tips/Data_G A_tips/Data_Guard/DG_52.sh uard/DG_52.shtml tml

30/03/2011