Cloning an Oracle Database using RMAN
1. Introduction:
These are the steps that I take to restore or clone an Oracle Database using RMAN on a Windows platform. Since I am constantly creating test environments of my production databases, I decided to create a post explaining the steps. These steps are assuming you already have a good backup of your production database using RMAN and that you have a separate machine or VM to restore the database to.
2. Building the Server
Once you have a server or VM to install the database to, you will want to make sure that the disk layout with the proper drive letters of your production machine are exactly the same as on your new server or VM. If they are not, then you will have to alter your control file when you get to the step to restore your data files. Here is a list of things you want to keep in mind when setting up your server:
A. Disk Configuration: Again, the disk configuration should be very similar to what you have on your production machine. This will include the disk space and the identical drive letters.
B. Operating system, Service Packs and Patches: The operating system environment should be the same as the original, right up to service pack and patch level.
C. Memory: The new server must have enough memory to handle Oracle and the operating system / other software requirements. Oracle memory structures (Shared pool, db buffer caches etc) will be sized identically to the original database instance. Use of the backup server parameter file will ensure this.
3. Retrieve the backup media
The next step is to get your backup data from wherever you store your backup information whether it’s from a tape drive or a network storage device and have it available on the local disk. I usually put this in a backup folder such as e:\backup.
4. Install Oracle Software
Now it’s time to install the Oracle database software. Please keep in mind the following when installing the software:
A. Install the same version of Oracle that is on the original server that you want to restore from. The version number should match right down to the patch level, so this may be a multi-step process involving installation followed by the application of one or more patchsets and patches.
B. Do not create a new database at this stage.
C. Make sure that you create a listener using the Network Configuration Assistant. Ensure that it has the same name and listening ports as the original listener. Relevant listener configuration information can be found in the backed up listener.ora file.
5. Create directory structure for database files
After software installation is completed, create all directories required for datafiles, (online and archived) logs, control files and backups. All directory paths should match those on the original server. This, though not mandatory, saves additional steps associated with renaming files during recovery.
Don’t worry if you do not know where the database files should be located. You can obtain the required information from the backup spfile and control file at a later stage. Continue reading – we’ll come back to this later.
6. Create Oracle service
As described in section 2, an Oracle service must be exist before a database is created. The service is created using the oradim utility, which must be run from the command line. The following commands show how to create and modify a service (comments in italics, typed commands in bold):
–set your Oracle_Home and Oracle_Sid environment variables
C:>set ORACLE_HOME=c:\oracle\product\10.2.0\db_1
C:>set ORACLE_SID=orcl
–create a new service with manual startup
C:>oradim -new -sid ORCL -startmode m
–modify service to startup automatically
C:>oradim -edit -sid ORCL -startmode a
Unfortunately oradim does not give any feedback, but you can check that the service exists via the Services administrative panel. The service has been configured to start automatically when the computer is powered up. Note that oradim offers options to delete, startup and shutdown a service. See the documentation for details.
7. Restore and recover database
Now it is time to actually start the database recovery process. There are several steps, so we’ll list them in order:
A. Copy password and tnsnames file from backup: The backed up password file and tnsnames.ora files should be copied from the backup directory (e:backup, in our example) to the proper locations. Default location for password and tnsnames files are ORACLE_HOMEdatabase ORACLE_HOMEnetworkadmin respectively.
B. Set ORACLE_SID environment variable: ORACLE_SID should be set to the proper SID name (ORCL in our case). This can be set either in the registry (registry key: HKLMSoftwareOracleHOME<X>ORACLE_SID) or from the system applet in the control panel.
C. Invoke RMAN and set the DBID: We invoke rman and connect to the target database as usual. No login credentials are required since we connect from an OS account belonging to ORA_DBA. Note that RMAN accepts a connection to the database although the database is yet to be recovered. RMAN doesn’t as yet “know” which database we intend to connect to. We therefore need to identify the (to be restored) database to RMAN. This is done through the database identifier (DBID). The DBID can be figured out from the name of the controlfile backup. Example: if you use the controlfile backup format suggested in Part I, your controlfile backup name will be something like “CTL_SP_BAK_C-1507972899-20050228-00″. In this case the DBID is 1507972899. Here’s a transcript illustrating the process of setting the DBID:
Recovery Manager: Release 10.2.0.4.0 – Production
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database (not started)
RMAN> set dbid 807337625
executing command: SET DBID
RMAN>
D. Restore spfile from backup: To restore the spfile, you first need to startup the database in the nomount state. This starts up the database using a dummy parameter file. After that you can restore the spfile from the backup (which has been restored from tape in Section 3). Finally you restart the database in nomount state. The restart is required in in order to start the instance using the restored parameter file. Here is an example RMAN transcript for the foregoing procedure. Note the difference in SGA size and components between the two startups:
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘C:\ORACLEPRODUCT\10.2.0\DB_1\DATABASE\INITORCL.ORA’
trying to start the Oracle instance without parameter files …
Oracle instance started
Total System Global Area 97590928 bytes
Fixed Size 454288 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
RMAN> restore spfile from ‘j:\oradata\orcl\flash_recovery_area\ControlBCK\C-807337625-20090621-00′;
Starting restore at 19/MAR/09
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
channel ORA_DISK_1: autobackup found: e:backupCTL_SP_BAK_C-807337625-20090621-00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 19/MAR/09
E. Restore control file from backup: The instance now “knows” where the control files should be restored, as this is listed in the CONTROL_FILES initialisation parameter. Therefore, the next step is to restore these files from backup. Once the control files are restored, the instance should be restarted in mount mode. A restart is required because the instance must read the initialisation parameter file in order to determine the control file locations. At the end of this step RMAN also has its proper configuration parameters, as these are stored in the control file.Here is a RMAN session transcript showing the steps detailed here:
allocated channel: ORA_DISK_1
hannel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=D:\ORACLE_DATA\CONTROLFILE\ORCL\CONTROL01.CTL
output filename=E:\ORACLE_DATA\CONTROLFILE\ORCL\CONTROL02.CTL
output filename=C:\ORACLE_DUP_DEST\CONTROLFILE\ORCL\CONTROL03.CTL
Finished restore at 01/MAR/09
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup mount pfile=’c:\oracle\product\10.2.0\admin\orcl\pfile\init.ora’;
Oracle instance started
database mounted
Total System Global Area 1520937712 bytes
Fixed Size 457456 bytes
Variable Size 763363328 bytes
Database Buffers 754974720 bytes
Redo Buffers 2142208 bytes
RMAN> show all;
using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘e:backupctl_sp_bak_%F’;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT ‘e:backup%U.bak’ MAXPIECESIZE 4G;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT ‘e:backup%U.bak’ MAXPIECESIZE 4G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘C:ORACLEORA92DATABASESNCFORCL.ORA’; # default
RMAN>
At this stage we can determine the locations of data files and redo logs if we don’t know where they should go. This is done from SQL Plus as follows:
SQL> connect / as sysdba
Connected.
SQL> select name from v$datafile;
…output not shown
SQL> select member from v$logfile;
…output not shown
The directories shown in the output should be created manually if this hasn’t been done earlier.
If you are restoring a database to a point that is past the recovery window of 7 days, you will want to perform the following to remove the backups from the local database’s catalog:
RMAN> delete noprompt backup;
…output not shown
You will notice the backups being removed from the local repository. If you don’t do this then RMAN will be using the current (within 7 days) backup files to restore your database. Once this is done, you’ll have to add the backup files you want to use to the repository.
RMAN> catalog start with ‘F:\oradata\orcl\flash_recovery_area’;
…output not shown
F.Recovery and Restore Script: Most of the time when you want to clone a database or just refresh your test database with production data, the data file locations are different as in my case. So what you have to do is get the data locations and redo log locations from your production machine and create a RMAN script to rename those to your new location in the control file. This is done by using a bunch of ‘Set newname for datafile’ statements. Below is a sample script what I use to restore/refresh the database.
run
{
set until time “to_date(’07/28/09 00:00:00′,’mm/dd/yy hh24:mi:ss’)”;
set newname for datafile ‘L:\oradata\orcl\data\system01.dbf’ to ‘D:\oradata\orcl\data\system01.dbf’;
set newname for datafile ‘L:\oradata\orcl\data\cwmlite01.dbf’ to ‘D:\oradata\orcl\data\cwmlite01.dbf’;
set newname for datafile ‘L:\oradata\orcl\data\drsys01.dbf’ to ‘D:\oradata\orcl\data\drsys01.dbf’;
set newname for datafile ‘L:\oradata\orcl\data\example01.dbf’ to ‘D:\oradata\orcl\data\example01.dbf’;
set newname for datafile ‘L:\oradata\orcl\data\odm01.dbf’ to ‘D:\oradata\orcl\data\odm01.dbf’;
set newname for datafile ‘L:\oradata\orcl\data\rollback_001.dbf’ to ‘D:\oradata\orcl\data\rollback_001.dbf’;
set newname for datafile ‘L:\oradata\orcl\data\rollback_02.dbf’ to ‘D:\oradata\orcl\data\rollback_02.dbf’;
set newname for datafile ‘L:\oradata\orcl\data\tools01.dbf’ to ‘D:\oradata\orcl\data\tools01.dbf’;
set newname for datafile ‘L:\oradata\orcl\data\sysaux01.dbf’ to ‘D:\oradata\orcl\data\sysaux01.dbf’;
set newname for datafile ‘L:\oradata\orcl\data\users01.dbf’ to ‘D:\oradata\orcl\data\users01.dbf’;
set newname for datafile ‘L:\oradata\orcl\data\xdb01.dbf’ to ‘D:\oradata\orcl\data\xdb01.dbf’;
set newname for datafile ‘N:\oradata\orcl\data\undotbs01.dbf’ to ‘F:\oradata\orcl\data\undotbs01.dbf’;
sql “alter database rename file ”L:\oradata\orcl\redo\redo01a.log” to ”D:\oradata\orcl\redo\redo01a.log””;
sql “alter database rename file ”L:\oradata\orcl\redo\redo01b.log” to ”D:\oradata\orcl\redo\redo01b.log””;
sql “alter database rename file ”L:\oradata\orcl\redo\redo02a.log” to ”D:\oradata\orcl\redo\redo02a.log””;
sql “alter database rename file ”L:\oradata\orcl\redo\redo02b.log” to ”D:\oradata\orcl\redo\redo02b.log””;
sql “alter database rename file ”L:\oradata\orcl\redo\redo03a.log” to ”D:\oradata\orcl\redo\redo03a.log””;
sql “alter database rename file ”L:\oradata\orcl\redo\redo03b.log” to ”D:\oradata\orcl\redo\redo03b.log””;
restore database;
switch datafile all;
}
Once this script has completed with no errors, you should be able to open the database with resetlogs.
RMAN> alter database open resetlogs;
database opened
RMAN>
Note that RMAN automatically applies all available archive logs. It first applies the backed up log and then searches for subsequent logs in the archive destination. This opens the door for further recovery if the necessary logs are available. In our case, however, we have no more redo so we open the database with resetlogs. The error message above simply indicates that RMAN has searched, unsuccessfully, for subsequent logs.
That’s it. The database has been recovered. Now once the database (clone) is up and running, Make sure you change any triggers, procedures, jobs, dblinks, etc that are affiliated with the production machine otherwise it will cause some havoc!
8. Options for better recovery
The above recovery leaves one with a sense of dissatisfaction: one could have done much better had the necessary logs been available. Clearly, one would have to copy the logs to a remote machine in order to guarantee access in a disaster situation. A couple of ways to do this include:
- Copy archive logs to a remote destination using OS scripts: This is achieved simply by a script scheduled to run every hour or so. The script copies, to a remote network computer, all the archive logs generated since the script last ran. This achieves better recoverability than before. However, it does not achieve up to the minute recovery. Further, one has to ensure that the a log switch is performed before the logs are copied, so as to ensure that redo associated with recent transactions (within the last hour) is copied to the remote destination.The log switch can be performed using the “alter system archive log current” command.
- Configure the Oracle ARC process to copy logs to the remote destination: This is done by defining a secondary archive destination via one of the LOG_ARCHIVE_DEST_N initialisation parameters. This method is not recommended because it is somewhat fragile – see thisdiscussion on Tom Kyte’s site, for example. Be sure to use a mapped network drive as the archive destination if you choose to go down this path. Oracle 9i will not recognise archive log destinations specified using UNC (Universal Naming Convention).
Finally, any article on disaster recovery should mention Oracle Data Guard – which is Oracle Corporation’s recommended disaster recovery solution for mission critical systems. This is essentially a standby database that is kept synchronised with the primary through the continuous application of redo. There are different levels of synchronisation depending on required availability, performance and (most important!) the acceptable data loss. There are two types of standby databases depending on how redo is applied: 1) Physical standby – which is an identical, block for block, copy of the primary, and 2) Logical standby – which is kept synchronised by applying SQL mined from redo logs. Interested readers are referred to the Oracle documentation on Data Guard for further details, as it is a vast subject, appropriate for a book rather than an article this size. A good starting point is Oracle Data Guard Concepts and Administration guide.
9. Concluding Remarks
This brings me to the end of the three part series on RMAN. I hope the material covered helps you plan your backup and recovery strategy. Remember this: your backup strategy should be dictated by business requirements rather than the latest and greatest technology. It is technically not hard to implement up-to-the-minute recovery, given the appropriate hardware and network bandwidth. However, unless you work for a bank or similar business, your end-users will likely accept some data loss once they hear the costs involved in up-to-the-minute recovery. If your requirements do allow for some data loss, it should be possible to implement a robust recovery strategy using the concepts and procedures discussed in this series of articles.