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.
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.
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.
In order to make the solution clearer, I have summarised the steps in different sessions in the following table.
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|
|T1||Create pfile/spfile, set new database name|
|T2||BACKUP AS COPY DATAFILE 2
|T3||alter system switch logfile;|
|T4||alter system checkpoint;|
|T5||BACKUP AS COPY DATAFILE 3
|T6||alter system switch logfile;|
|T7||alter system checkpoint;|
|T8||BACKUP AS COPY DATAFILE 4|
|T10||alter system switch logfile;|
|T11||alter system checkpoint;|
|T12||BACKUP AS COPY DATAFILE 1|
|T14||alter database backup controlfile to trace;|
|T16||CREATE CONTROLFILE set DATABASE “NEWDB” RESETLOGS ARCHIVELOG
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
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;|