RMAN backup-based duplicate database without connection to the source in 11GR2
11g release 1 introduced the RMAN active database duplication feature, where you can duplicate a database without having to take a backup of the source database.
A new feature in 11g release 2 is the ability to use RMAN to duplicate a database without having to connect to the source database. At the time of writing, most of the RMAN documentation tends to refer you back to 10gR2 documents for backup-based restores, whilst 11gR2 documents tend to focus on active database duplication, so I thought that a blog on this topic could be of use.
This functionality would be useful if you have thought about using the 11g active database duplication feature, but are concerned about possible network performance issues during the process.
Another situation where this procedure could be used, is where you have been asked to create a copy of a database on an isolated test server, where connection to the source database is not possible.
As in previous Oracle versions, it is still possible to create a clone of the source database by just extracting the database files from the RMAN backuppieces using DBMS_BACKUP_RESTORE. (See my previous blog “Extracting database files from RMAN backuppieces using DBMS_BACKUP_RESTORE” for an example of this). 11gR2 introduces a much more straightforward method of cloning the database without a connection to the source database.
An example of this process is shown below. In this scenario we’ll assume that the server that you’re duplicating the database to, has the same directory structure as the source server and that you want the cloned database to have the same name as the original source database. The source database is using an SPFILE. We’ll also assume that you’re not using a recovery catalog on the source or target servers, as this would probably be the situation if restoring to a remote test server. The SID of the database is ORCL, so replace with your own SID where appropriate.
The cloned database needs to be on the same platform as the source database and also have 11gR2 installed.
WARNING/DISCLAIMER: Before running any of these commands, you should ensure that you have current backups of any databases on the server. Refer to the more comprehensive Oracle support site notes and Oracle documentation before carrying out any work in a formal environment. The author accepts no responsibility for any damage to your data, server or database, by carrying out the commands below.
1) Take a full backup of the source database.
——————————————-
Our example database is running in archivelog mode, so we can take a hot backup.
a) create a directory to store the output from the backup. (For this example, I created the directory /u02/oradata/backup_orcl ).
b) Set your environment to the required database. (e.g. by running . oraenv)
c) Run RMAN commands to take the backup. We’ll specify an alternate location for the backups, otherwise they’ll just default to the flash recovery area (FRA). Whilst you could use the contents of the FRA for the clone, there may be files from other database in the same shared area and there could also be files here from previous backups, which you don’t need for this clone. Sending the backup to a separate location makes it easier to identify the files that you need.
rman
connect target
spool log to ‘/u02/oradata/backup_orcl/full_database_orcl.log’;
configure channel device type disk format ‘/u02/oradata/backup_orcl/orcl_%U’;
backup database plus archivelog;
exit
(You can run tail -f /u02/oradata/backup_orcl/full_database_orcl.log as the backup runs, if you wish to monitor progress).
2) Configure the environment on the destination server
—————————————————
a) Create a password file in $ORACLE_HOME/dbs using the orapwd utility.
b) Configure the $ORACLE_HOME/network/admin/listener.ora file. The only entries used for this work were:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/opt/oracle/product/11gR2/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
c) Configure the $ORACLE_HOME/network/admin/tnsnames.ora file. The only entry added for this work was:
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
d) Create directories on the target server that are present on the source server. (e.g. archive destination, scripts, application directories, utl_file/dba_directories, flash recovery area, external table locations, database/redo log/controlfile/admin/diag directories, backup locations etc.)
e) Create the $ORACLE_HOME/dbs/initSID.ora file for the duplicate database. The only entry needed is:
db_name=’orcl’
No other information is required, as we’ll extract the source database SPFILE from the backup.
f) Add an entry to /etc/oratab for the database.
g) Set your environment to the new database and then startup the clone instance.
sqlplus / as sysdba
startup nomount
exit
h) Run RMAN command to clone the database. (We don’t need to specify the database name or DBID, as these are obtained from the backups).
rman auxiliary /
DUPLICATE DATABASE TO orcl
SPFILE
BACKUP LOCATION ‘/u02/oradata/backup_orcl’
NOFILENAMECHECK;
exit
Note: Be careful when running any duplicates on the same host as the source database when specifying the NOFILENAMECHECK option. This option could mean that you over-write your source database files.
i) Finally, you will need to re-create a tempfile for your temporary tablespace. From 10gR2, this would normally be created for you in a standard RMAN clone, but it is not re-created at present when using this method.
A tempfile is created, but it is not recognised as being part of the database - i.e. select file_name from dba_temp_files; returns the errors: “ORA-01157: cannot identify/lock data file X“ From the trace files it looks like there are some issues with the tempfile creation process. Sample errors are “Cannot re-create tempfile dbf, the same name file exists“ and “ORA-03214: File Size specified is smaller than minimum required“. It seems that the file creation works, but the file created is too small to be usable at 8MB - the original tempfile was 29MB in this test database.
This may be due to a bug, or due to the current design of this feature, or it could be due to the configuration of the test database used for this scenario. Fixing the issue is pretty straightforward - just drop and re-create the tempfile:
alter database tempfile ‘/u01/opt/oracle/oradata/orcl/temp01.dbf’ drop;
alter tablespace temp add tempfile ‘/u01/opt/oracle/oradata/orcl/temp01.dbf’ size 100M reuse autoextend on maxsize 5120M;
Other useful features of the duplicate process are that you can choose to exclude selected tablespaces and you can also choose to carry out a point-in-time duplicate of the source database.
References used: Oracle Support notes 228257.1, 259694.1, 452868.1, 452868.1, 568034.1, 374934.1,
Oracle Database Backup and Recovery User’s Guide 11g Release 2 (11.2) - ch 23: Duplicating a Database