VirtualObjectives

Using Archive Logs to Make a Copy of a Production Database

This procedure explains how to set up and apply archive logs to a newly set up Physical Standby server which can be split off and used as a copy of a Production database.
This approach can be used to duplicate a Production database into another new test environment OR for the migration to a new Production environment.

The chief benefit of this method is a minimized cut-over time frame to a new system. You can prepare everything days beforehand, and cut across quickly. This is because a backup, restore and recovery will take longer than waiting for archive logs to copy and apply.

This procedure does not make configuration changes to the current Primary or Standby environments. It relies on copies of archive logs from the current Standby system to keep the new system up to date.

Things to note:

  • Users cannot access the new system while it is recovering (i.e. applying archive logs).
  • On the new system, once the recovery process is stopped and the database is "activated" then it can not later apply new archive logs from the source system.
  • This procedure is for a Windows environment running Oracle 10g.
  • Due to the limitations of ROBOCOPY, you will need enough disk space on the new server (TSVWDBSP04) to store at least one days worth of archive logs.

In this example:

  • SVRODBP01 is the Production Primary server. (sid=prod)
  • SVRODBS01 is the Production Physical Standby server. (sid=standby)
  • SVRODBP06 is the new server which will eventually be the new Primary system. (sid=newdb)
  • As always, adapt the file names and paths used in the example to suit your own requirements.

Restore a Physical Standby Database on the New Server

Ensure there is a recent full backup of the Production database on server SVRODBP01. This backup should also contain a Standby copy of the Control Files using the following RMAN script...

backup current controlfile for standby format 'N\somepath\STANDBY_CTRL_%T_%s_%p';

Copy the backup files from your Primary environment (SVRODBP01) into D:\RMAN_BACKUP\ on the new environment (SVRODBP06)
Move all non-backup files (control files, log files, etc.) from D:\RMAN_BACKUP\ to D:\RMAN_BACKUP_OTHER\

We are assuming that there is already a database set up on the new server, so connect to the database on the new server SVRODBP06 as the RMAN target.

Shut down the database...

Now remove all datafiles. We are assuming that these exist on the file system. Otherwise use ASMCMD to remove the datafiles from ASM.
In this example our datafiles exist in D: and E: under \oracle\oradata\db\
Delete all files under \oracle\oradata\db\ except *.CTL

Startup database in nomount...

Restore the Standby control file...

Mount the database...

Catalog the backup files...

Type YES at the prompt.

Now we need to generate a script to rename datafiles to match where they will reside on the new server environment.
Use Oracle SQL Developer to connect to Standby on SVRODBP06 and run the the following script using [F9] key.
(Obviously, you will need to adapt the script to fit your requirements)

Select all results using [Ctrl] + [A] and copy.
Insert the results into a file called restore.rman

In the file restore.rman, insert the following at the top...

run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;

... and at the end, append the following...

restore database force check readonly;
switch datafile all;
}

Save the file restore.rman.

Now we will begin the restore the database. This may take a long time to complete.

Check\Set Parameter Settings

While you are waiting for the restore to complete, we need to check and set up various parameter settings on the new server to make sure Data Guard is set correctly.
For the following settings, you can either modify them via SQL or add\update them in the pfile...

LOG_ARCHIVE_FORMAT specifies the default filename format when archiving redo log files.

STANDBY_ARCHIVE_DEST specifies the location of archive logs arriving from a primary database.

LOG_ARCHIVE_DEST_1 specifies the default location and root of the disk file or tape device when archiving redo log files.

LOG_ARCHIVE_DEST_STATE_1 status of LOG_ARCHIVE_DEST_1.

Copying Archive Logs

Now we need to set up a batch script to copy recent archive logs from the Production Standby server SVRODBS01 to the new server SVRODBP06. This will be scheduled (via Windows Scheduler) to run every 30 minutes or so.
Even though the tag file CopyArchiveLogsFromStandby.INPROGRESS is created to stop multiple instances of this batch file from running, make sure that the job finishes by the time it is scheduled to start again.
On the server SVRODBP06, create a file called CopyArchiveLogsFromStandby.cmd under the path C:\Scripts\

Above, we use ROBOCOPY to copy the last days archive logs from SVRODBS01 to SVRODBP06. The source and destination folders are where the archive logs are normally located. The parameter STANDBY_ARCHIVE_DEST is the path you want.
ROBOCOPY has been set to use restartable mode with an inter-packet gap of 10ms to help prevent saturation of network bandwidth.
The CALL to Catalog_Archivelogs_Automated.cmd is remarked out at this stage and will be enabled later.

NOTE: If the time between when the full backup was taken and the restore is greater than 24 hours then you will need to initially copy across all the logs produced after the backup, otherwise you will have missing archive logs.

Recovery Process

When the restore has completed successfully, clear all standby redo log groups (there may be more than 3)
Find size, total and names of redo log files on the standby database on SVRODBP06.

ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 2;
ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 4;
ALTER DATABASE CLEAR LOGFILE GROUP 5;
ALTER DATABASE CLEAR LOGFILE GROUP 6;
ALTER DATABASE CLEAR LOGFILE GROUP 7;
ALTER DATABASE CLEAR LOGFILE GROUP 8;
ALTER DATABASE CLEAR LOGFILE GROUP 9;
ALTER DATABASE CLEAR LOGFILE GROUP 10;
ALTER DATABASE CLEAR LOGFILE GROUP 11;
ALTER DATABASE CLEAR LOGFILE GROUP 12;
ALTER DATABASE CLEAR LOGFILE GROUP 13;
ALTER DATABASE CLEAR LOGFILE GROUP 14;
ALTER DATABASE CLEAR LOGFILE GROUP 15;
ALTER DATABASE CLEAR LOGFILE GROUP 16;
ALTER DATABASE CLEAR LOGFILE GROUP 17;
ALTER DATABASE CLEAR LOGFILE GROUP 18;
ALTER DATABASE CLEAR LOGFILE GROUP 19;
ALTER DATABASE CLEAR LOGFILE GROUP 20;

You need to add N+1 redo log groups.
There needs to be more redo logs on the Standby than there are on the Primary.
If there are 20 redo log groups on the Primary database (usually of 2 files each) then add group 21 on the Standby server SVRODBP06.
Example below...

Mount the Standby database...

Check the Oracle alert log file for errors.
Take any corrective action required to resolve any issues.

Catalog the archive logs...

Begin recovering the database using copies of the archive logs. There may not be any archive logs present, so that is okay.

Check the Oracle alert log. You should see processes starting where "Media Recovery Log" is applying the logs.
Use the above SQL to start the recovery again should the Oracle instance be stopped.

Processing Archive Logs

Now we will set up scripts on the new server SVRODBP06 to run an RMAN script to CATALOG the archive files after they are copied into the folder D:\oracle\admin\db\archive\ by the scheduled batch job CopyArchiveLogsFromStandby.cmd
In the folder C:\Scripts\ create a file called Catalog_Archivelogs_Automated.rman and add the following in it making sure you have the correct path where the archive logs are copied to...

In C:\Scripts\ create a file called Catalog_Archivelogs_Automated.cmd and add the following in it...

In the file in C:\Scripts\CopyArchiveLogsFromStandby.cmd, remove the REM in front of "call Catalog_Archivelogs_Automated.cmd" and save the file.
This will enable the cataloguing of new archive log files that are copied from the Production Standby server SVRODBS01 to the new server SVRODBP06.

Check the Oracle alert log file for errors.
Also check that the log files CopyArchiveLogsFromStandby.log and Catalog_Archivelogs_Automated.log do not have errors.

Waiting for Go Live

At this point, the archive logs are being copied to the new server and applied after 30 minutes. This means that the database on SVRODBP06 is roughly around 45 minutes behind the Production Primary database.
Please remember that the database is in a recovery state which means it will not accept queries from users or applications.

Regularly check the Oracle alert log file for errors.
Check that the log files CopyArchiveLogsFromStandby.log and Catalog_Archivelogs_Automated.log do not have errors.

You will also need to check that the directory where the archive log files are written to is not getting too large. You may want to employ another scheduled batch file to remove old archive logs for that purpose. Below I have supplied an example called DeleteOldOracleArchiveFiles.cmd ...

You will now be waiting for a go-live decision point. When this happens, continue with the next section.

Making the Database Active

Assuming you want to maintain data integrity, in Production you would normally stop the users and user application(s) from making further changes to the database. When this is done, then you can signal the Production Primary to produce another archive log.
Otherwise, still complete this step as the archive log files produced can be used to "mark" when you can "sever" the connection to the Primary database.
The following is run on any node of the Primary database...

Look in the Oracle alert log for each Primary node and note down the archive log file name produced. Don't be alarmed if you see that these files are smaller in size than normal archive log files.

Back on the new server SVRODBP06, wait for the Scheduled Task job which runs CopyArchiveLogsFromStandby.cmd to finish and then disable it.
Run the batch file CopyArchiveLogsFromStandby.cmd one last time and check the log file CopyArchiveLogsFromStandby.log to see if the recently created archive logs files have been copied to the new server (SVRODBP06).
You will also need to check the Oracle alert log on the new server to make sure all the archive logs have been applied.

Stop the recovery process...

Convert the Standby to an Active database. (This will not affect Production instances)
After run run this step, you will no longer be able to continue recovering archive new logs. To do so, you would need to perform a database restore and recover again.

Check status of the new database:
The OPEN_MODE would have changed from NOT_ALLOWED to READ WRITE

CONTROL OPEN_MODE
------- ----------
CURRENT READ WRITE

The DATABASE_ROLE would have changed from PHYSICAL_STANDBY to PRIMARY

SWITCHOVER_STATUS    DATABASE_ROLE
-------------------- ----------------
SESSIONS ACTIVE      PRIMARY

Check again the database role status...

DB_UNIQUE_NAME                 DATABASE_ROLE    INSTANCE_ROLE
------------------------------ ---------------- ------------------
db                             PRIMARY          PRIMARY_INSTANCE

The database is now active and will accept connections.

NOTE: What is not covered here:

  • Decommissioning of SVRODBP01 and SVRODBS01 or the establishment of a new Physical Standby for SVRODBP06.
  • Copying of password files or changing of system passwords.
  • Removal of processed archive log files from the new server.