:

Using a Physical Standby Database in Read Write Mode

Using a Physical Standby Database in Read Write Mode

OS: Windows Server 2003, SP2
DB: 10.2.0.4, patchset 3 (base release)

Requirements

We currently have two physical standby databases, one of which is at a physically different location several hundreds of miles away from the primary database site. We use these physical standby databases in case of a Disaster Recovery (DR) scenario and we also use them for refreshing our development databases with production data.

The way in which we refresh some of our environments differs from others because we have a requirement not to hold any sensitive data in the DEV DB. We achieve this by importing a “cut down” set of data which includes only configuration data and no client data. In this way we never hold client sensitive data on the DEV DB.

Process Overview

In order to get this data from our physical standby database to our DEV DB we use the data pump utility. The summary of how it works is as follows:

1. Export required data from physical standby databases
2. Make the export files available on the DEV DB server for import
3. Drop the existing users from the DEV DB
4. Import the users and fresh configuration data to the DEV DB

In this next section I will detail how each of the above 4 points is achieved.

Process Detail

1. The first problem that we come across in this scenario is that you can’t run a data pump export job in a physical standby database even when it is open read only because the data pump utility creates jobs in the DB and that requires write access to the DB. To solve this problem, I have used flashback database to open the DB in read/write mode and then flashback the database so that it is put back into a physical standby DB. I have used a guaranteed restore point so I know I will be able to flashback the DB to the exact point in time.

I will also tell you at this point about how to convert the previously physical standby database (you’ve opened it for read/write use at this point) back to a physical standby database once you have finished running the export jobs. This is a very simple flashback database command which is included with the Flashback DB Part 2 script at the bottom of this article.

The scripts to perform both parts of this operation are at the bottom of this article labelled “Flashback DB Part 1” and “Flashback DB Part 2“, respectively. To run these scripts you can need to call it with a variable which is used throughout the remainder of the script. I have done this so that the script can be used for multiple databases at the same time. You need to make sure that the correctly named directories are created before running this. Here is an example of how I would run it:

@flashback_DB_Part1.sql TEST

Once the DB has been opened in read/write mode you can run the export command(s), run in scripts to make changes, or do anything else that you want to do in the database.

2. Now that we have the data exported that we need to refresh our DEV DBs, we have to make these files available to the DEV DB servers. The issue we have here is that the physical standby databases, which are using Data Guard, are within the PROD domain but the DEV DB servers are in the DEV domain.

To get around this we use SAN snapshot technology. The best way that I can think of to describe this is to say that it is equivalent to a materialized view in Oracle terminology. So, when you create the snapshot of the PROD disk and make it available as a disk on the DEV DB server you get a view of how the disk looked at that point in time. Future updates to the source (PROD) disk will not be available to the destination (DEV) disk until you take another snap of the disk (like a refresh of a materialized view). You can also write to the destination (DEV) disk without affecting the source disk; the writes just get written to separate disks in the background.

So, that’s how we make the exported data pump files available on our DEV DB server.

3 & 4. The dropping and importing of the users is a fairly straight forward task, so there is no need to go into the details here. One thing I would comment on is that it’s a good idea to start the DB up in restricted mode before dropping the users so that you make sure that none of the users are connected as this will mean that you script fails as you can’t drop a user that is currently connected.

Conclusion

We can see here how the use of Oracle and SAN technologies can help to overcome different problems and give us many options when used together. The flashback database and data guard technologies within the Oracle suite can be used to accomplish previously difficult tasks with ease. The options that the DBA now has available to him by using a combination of these technologies is greatly increased and allows for very flexible solutions to be implemented based upon a specific set of requirements.

You may find that you come across the error ORA-38785: Media recovery must be enabled for guaranteed restore point when trying to create your guaranteed restore point which I have also written about.

If you see errors such as ORA-39125: Worker unexpected fatal error in KUPW$WORKER then read the article that describes more about why this is happening.

It is also possible to open your physical standby database for read only access if you just want to run in some queries against it.

Scripts

Flashback DB Part 1

set lines 120
set lines 5000
set trimspool on
whenever sqlerror exit rollback;
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
SET DEFINE ON
DEFINE dbsid=&1
SPOOL U:\flash_recover_&dbsid\flashbackdb_&dbsid..log
PROMPT Create flashback recovery area for &dbsid
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=50G scope=both;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='U:\flash_recover_&dbsid' scope=both;
PROMPT Cancelling recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
PROMPT Details from before restore point for &dbsid
SELECT SEQUENCE#, APPLIED, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG
WHERE sequence# > (SELECT max(sequence#-10)
FROM v$archived_log)
ORDER BY SEQUENCE#;
SELECT LOG_MODE, CHECKPOINT_CHANGE#, ARCHIVE_CHANGE#, OPEN_RESETLOGS, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE, GUARD_STATUS, 
RECOVERY_TARGET_INCARNATION#, LAST_OPEN_INCARNATION#, CURRENT_SCN, FLASHBACK_ON, STANDBY_BECAME_PRIMARY_SCN
FROM v$database;
PROMPT Creating restore point for &dbsid
CREATE RESTORE POINT post_refresh_&dbsid GUARANTEE FLASHBACK DATABASE;
PROMPT Activating physical standby database
ALTER DATABASE ACTIVATE STANDBY DATABASE;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
ALTER DATABASE OPEN;
SPOOL OFF

Flashback DB Part 2

SET DEFINE ON
DEFINE dbsid=&1
set lines 50
set lines 500
set trimspool on
whenever sqlerror exit rollback;
SPOOL U:\flash_recover_&dbsid\flashbackdb_&dbsid..log APPEND
PROMPT Details after use of DB, but before flashback of &dbsid
select LOG_MODE, CHECKPOINT_CHANGE#, ARCHIVE_CHANGE#, OPEN_RESETLOGS, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE, GUARD_STATUS, 
RECOVERY_TARGET_INCARNATION#, LAST_OPEN_INCARNATION#, CURRENT_SCN, FLASHBACK_ON, STANDBY_BECAME_PRIMARY_SCN
FROM v$database;
PROMPT Flashing back DB for &dbsid
STARTUP MOUNT FORCE;
FLASHBACK DATABASE TO RESTORE POINT post_refresh_&dbsid;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
STARTUP MOUNT FORCE;
PROMPT Dropping restore point for &dbsid
DROP RESTORE POINT post_refresh_&dbsid;
set lines 50
set lines 500
set trimspool on
PROMPT Details after DB has been flashed back on &dbsid
select LOG_MODE, CHECKPOINT_CHANGE#, ARCHIVE_CHANGE#, OPEN_RESETLOGS, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE, GUARD_STATUS, 
RECOVERY_TARGET_INCARNATION#, LAST_OPEN_INCARNATION#, CURRENT_SCN, FLASHBACK_ON, STANDBY_BECAME_PRIMARY_SCN
FROM v$database;
PROMPT Enable recovery again for &dbsid
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SPOOL OFF

Like it, share it...

Category: Data Guard


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *