Clone Oracle Physical Standby Database to Read Write Mode

1.   Purpose

Provide a workaround for cloning Oracle physical standby database to a new database followed by open the new database to read write mode while keeping the existing physical standby database running in the same role in the existing data guard configuration.

You can also use this method to HOT backup your physical standby database.

2.   Scope & Application

DBAs and Application Developers might benefit from this document when they need to reproduce the production system scenarios to diagnose the production problem and generate staging report in a separate database.

3.   Symptoms

As there is no Oracle official solution for this kind of operation, according to your particular situation, the following parts should be considered in your mind clearly beforehand, otherwise the CLONE operation may not work properly for your business.

3.1   Database Unique Name

The database name and the database unique name inside the same enterprise environment should be unique in theory. This thoughtful consideration will eliminate unnecessary exceptions and errors of your new system and help to facilitate the development and deployment of your application systems.

Try your best to set the db_name and db_unique_name to a different value rather than the same as the source physical standby database, primary database or other database names inside your enterprise database system environment.

For example, set the new database name to ‘NEWDB’ in PFILE or SPFILE rather than leaving them the same as any of the existing database systems.

*.db_name=’NEWDB’

*.db_unique_name=’NEWDB’

3.2   Ongoing Transaction Frequency

When cloning the new database from physical standby database, there may be some database operations performing on primary database. If your physical standby database is using real time apply mechanism, you need to keep this in mind, because when you are cloning the physical database, the data files of which may be in different SCN which means they may be in the inconsistent status in file headers.

Take a note of in what order you would like to backup the individual data files or in a RUN batch block mode.

3.3   Media Recovery

In many cases of OLTP system, you need to perform a media recovery when cloning the new database system, because each data file may have information up to different SCN.

3.4   Archived Log Destination

You may need to apply archived log files of existing physical standby database, figure out the accessibility of these archived log files, they are crucial for the consistency of existing physical standby database as well as the new database, as they are the durability part of the transaction’s ACID. Missed them means missed transaction, means missed your data.

4.   Solution

In order to make the solution clearer, I have summarised the steps in different sessions in the following table.

Notes:

1)      Primary database in Data Guard  – Primary

2)      Physical standby database in Data Guard – Standby

3)      New database being created – NEWDB

4)      Backup Standby database with RMAN

Timeline Session 1 Session 2 Session 3
Primary Standby NEWDB
T1 Create pfile/spfile, set new database name
T2 BACKUP AS COPY DATAFILE 2
FORMAT ‘/u01/app/oracle/oradata/NEWDB/undotbs01.dbf';
T3 alter system switch logfile;
T4 alter system checkpoint;
T5 BACKUP AS COPY DATAFILE 3
FORMAT ‘/u01/app/oracle/oradata/NEWDB/sysaux01.dbf';
T6 alter system switch logfile;
T7 alter system checkpoint;
T8 BACKUP AS COPY DATAFILE 4
T9 FORMAT ‘/u01/app/oracle/oradata/NEWDB/users01.dbf';
T10 alter system switch logfile;
T11 alter system checkpoint;
T12 BACKUP AS COPY DATAFILE 1
T13 FORMAT ‘/u01/app/oracle/oradata/NEWDB/system01.dbf';
T14 alter database backup controlfile to trace;
T15 STARTUP NOMOUNT
T16 CREATE CONTROLFILE set DATABASE “NEWDB” RESETLOGS  ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u01/app/oracle/oradata/NEWDB/redo01.log’  SIZE 50M,
GROUP 2 ‘/u01/app/oracle/oradata/NEWDB/redo02.log’  SIZE 50M,
GROUP 3 ‘/u01/app/oracle/oradata/NEWDB/redo03.log’  SIZE 50M
DATAFILE
‘/u01/app/oracle/oradata/NEWDB/system01.dbf’,
‘/u01/app/oracle/oradata/NEWDB/undotbs01.dbf’,
‘/u01/app/oracle/oradata/NEWDB/sysaux01.dbf’,
‘/u01/app/oracle/oradata/NEWDB/users01.dbf’
CHARACTER SET AL32UTF8;
T17 select * from v$recover_file;
T18 select file#, status, recover, fuzzy from v$datafile_header;
T19 recover database using backup controlfile until cancel;
T20 Manually apply archive log in standby archived log destination
T21 alter database open resetlogs;
About these ads

Tags: , , , , ,

4 Responses to “Clone Oracle Physical Standby Database to Read Write Mode”

  1. unclog a toilet Says:

    hi!,I like your writing so much! percentage we keep in touch more approximately your post
    on AOL? I need a specialist in this house to resolve my problem.
    May be that is you! Looking forward to peer you.

  2. Chanel Says:

    I have read so many content regarding the blogger lovers however this paragraph is genuinely
    a good piece of writing, keep it up.

  3. toilet comfort Says:

    {
    {I have|I’ve} been {surfing|browsing} online more than {three|3|2|4} hours today, yet I never found any interesting article like yours. {It’s|It
    is} pretty worth enough for me. {In my opinion|Personally|In my view}, if all {webmasters|site owners|websi

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: