Question: I want to use RMAN to copy a
database to a different server, in a different disk directory. Can I use RMAN to
restore a database to another server?
Answer:
Cloning the database on the
remote host requires two servers. Both servers should have
Oracle Software installed on them. Make sure the Operating System is
the same on both the servers. On the first server, we should have
one database which will be duplicated to the second host. The first
server will be named as "production server" and the database on it
will be called "production database" (SID of the production database
is db1). However, the second server will have a name as
"auxiliary server" and the database which will be created on it will
be called "auxiliary database" (SID of the auxiliary database is
aux). The server name of the production database is
PROD_SERVER and the server name of the auxiliary database is
DUP_SERVER
Also see:
RMAN
cloning on a local host
Before cloning the production database,
we should configure some initial actions as follows:
Step 1 - Create a password file for
the auxiliary instance
It's possible to create a password file
on the production server as well as on the auxiliary server. Because
the passwords of target and auxiliary databases password files
should not be similar as it's a security threat. However, it should
be mentioned that when creating standby database from RMAN, the
password file has to be same. But here, as we don't create
standby database, the new password file should be created.
Create a password file using ORAPWD utility as follows:
$ orapwd file=$ORACLE_HOME/dbs/orapwaux
password=test entries=3;
Step 2 - Configure auxiliary instance
listener net configuration
To clone the production database to
auxiliary server, the connection should be configured between two
servers i.e. must be able to access the production database from
auxiliary server and vice versa.
Thus, you need to make changes to
tnsnames.ora and listener.ora files. If you haven't, these files are located in the
$ORACLE_HOME/network/admin directory, create them using netca
utility. First of all, change LISTENER.ORA file on auxiliary server
and add the following lines:
LISTENER.ORA #auxiliary instance
(SID_DESC =
(SID_NAME = aux)
(ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
)
The reason why we made a change to
listener.ora file is because we make auxiliary server to listen
for the connections which comes from the production database.
While duplicating the database, we'll connect to the auxiliary
instance from the production database and as an auxiliary instance
should understand and accept the connections which come from
production database, we configured the listener.ora file. Without
stopping and starting the listener, we apply new changes made to
listener configuration by running the following command:
Now, edit the TNSNAMES.ORA file at the
production database and add the entry about auxiliary database to it
as follows:
AUX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST= DUP_SERVER)(PORT=1521))
)
(CONNECT_DATA=
(ORACLE_SID=aux)
)
)
After changing the file, make sure you
can connect to auxiliary instance by using tnsping utility
$ tnsping aux
Attempting to contact (DESCRIPTION =
(ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST=
DUP_SERVER)(PORT=1521))) (CONNECT_DATA= (ORACLE_SID=aux)))
OK (40 msec)
It means that we have access to
the auxiliary server from production server and we successfully
configured connection between two servers
Step 3 - Create a parameter file for an
Auxiliary database
We'll use this parameter file to start
auxiliary instance in NOMOUNT mode. You can create pfile from
spfile of production database and copy it to the auxiliary server.
You can also create a parameter file which consists only of the
following few parameters:
-
DB_NAME: As we create duplicate database at the
remote host, we can keep database name the same as the production
database name. However, this parameter will be different when we
duplicate database on the local host, because it's impossible to
have two databases with the same name in one host.
-
CONTROL_FILES: This parameter defines
the name of Control Files which will be restored to auxiliary
instance
-
DB_BLOCK_SIZE: This parameter must be
the same as in the target instance
-
COMPATIBLE: If production database uses
specific compatible parameter, add this parameter to auxiliary
parameter file with the same value
-
SGA_TARGET: This parameter specifies the
total size of all SGA components. To automatically size these
components, make it the same as in the production database.
However it is not mandatory that this be the same size as in the
production database. The cloning may be done to create a Test
/ Development environment on a smaller server with lesser RAM.
You need also to create the following
directories on the auxiliary server and add them to the parameter
file:
-
adump
-
bdump
-
udump
-
cdump
-
Flash Recovery
Area
- $ cd $ORACLE_HOME
- $ mkdir admin
- $ cd admin/
- $ mkdir aux
- $ cd aux/
- $ mkdir adump bdump cdump udump
- $ cd ..
- $ mkdir flash_recovery_area
After changes are made to parameter file, it will
look like as follows:
DB_NAME=aux
CONTROL_FILE=(/u02/oradata/aux/control01.ctl,/u02/oradata/aux/control02.ctl,
/u02/oradata/aux/control03.ctl')
DB_BLOCK_SIZE=8192
COMPATIBLE='10.2.0.1.0'
SGA_TARGET=285212672
audit_file_dest='/u01/oracle/product/10.2.0/db_1/admin/aux/adump'
background_dump_dest='/u01/oracle/product/10.2.0/db_1/admin/aux/bdump'
core_dump_dest='/u01/oracle/product/10.2.0/db_1/admin/aux/cdump'
user_dump_dest='/u01/oracle/product/10.2.0/db_1/admin/aux/udump'
db_recovery_file_dest='/u01/oracle/product/10.2.0/db_1/flash_recovery_area'
db_recovery_file_dest_size=2147483648
Now, to make Oracle automatically uses this
parameter file each time, create spfile as follows:
$ export ORACLE_SID=aux
$ export ORACLE_HOME=/u01/oracle/product/10.2.0/db_1/
$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 29
00:48:57 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> CREATE SPFILE FROM
PFILE='/u01/oracle/product/10.2.0/db_1/dbs/pfile.ora';
File created
Step 4 - Startup auxiliary instance in NOMOUNT
mode
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size
1218992 bytes
Variable Size
92276304 bytes
Database Buffers
188743680 bytes
Redo Buffers
2973696 bytes
Step 5 - Backup the production database and
copy all backup files to auxiliary server
As the auxiliary instance is ready, you need to
backup the production database and copy backup files to auxiliary
server. As the backup will be recovered on the auxiliary server, the
production database should be in ARCHIVELOG mode
Connect to target instance and take the compressed
backup of the database using nocatalog. The backup of the database
can be taken by connecting to the RMAN catalog database as
well
$ export ORACLE_SID=db1
$ rman target /
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS
ARCHIVELOG;
Step 6 - Create datafile locations on an
auxiliary server
If you want to create the datafiles in the same
directory as in the production database, create the folder of
datafiles on the auxiliary server
# mkdir u02
# cd u02/
# mkdir oradata
# cd oradata/
# mkdir db1
# cd db1/
# chown -R oracle:oinstall /u02/
Step 7 - Copy the backup of the production
database to the same directory residing on the auxiliary database
server
As we configured the database to use Flash
Recovery Area, all RMAN backups will be stored under the flash
recovery area folder. So the same folder should be created at the
auxiliary server and all backup files should be copied to that
folder. When you issue the DUPLICATE DATABASE command, RMAN searches
the auxiliary server for the backup files which should reside at the
same folder where they were at the production server.
Identify the location of RMAN backups on the
production server and create the same folders on the auxiliary
server. Then copy all backup files from the production server to the
auxiliary server
Step 8 - Duplicate the Database
After all above steps are done correctly, we're
ready to create the clone database. For this, we connect to both
production and auxiliary database from RMAN and issue the command
"DUPLICATE TARGET DATABASE TO aux".
$ rman target sys/my_pass auxiliary sys/test@aux
connected to target database: DB1 (DBID=1298725119)
connected to auxiliary database: AUX (not mounted)
RMAN> DUPLICATE TARGET DATABASE TO aux NOFILENAMECHECK;
Starting Duplicate Db at 06-DEC-09
=================
database opened
Finished Duplicate Db at 06-DEC-09
After performing all steps, we have successfully
duplicated the production database to the remote host (auxiliary
server). RMAN performed the following steps automatically to
duplicate the database :
-
Allocates automatic
auxiliary channel
-
Creates a controlfile
for the clone database
-
Performs an incomplete
recovery of the clone database using incremental backups and
archived redo log files up to the last backed up archived redo log
file.
-
Shutdowns the database
and opens it using RESETLOGS option.
-
Generates a new unique
DBID for the clone database
But remember, all file locations were the same as
in the production database. However, in case we need to change clone
database's directory structure, we can do it in different ways.
Answer on RMAN cloning by
Sorabh Harit:
Applies to: Oracle Server - Enterprise Edition - Version:
10.2.0.0.0
The goal is restoring a database using RMAN on a different node with different
backup directory structures and different database directory structures .
-
You have a database backed up on NODE 1.
-
You need to restore the database on NODE 2.
-
The directory structure is different on NODE 2.
-
You need to put the backups in a new directory structure in NODE 2, unlike as
they were in NODE 1.
-
You need to restore the database files into a new directory structure in NODE
2, unlike as they were in NODE 1.
Solution
Below is the procedure with an example of using RMAN to
copy a database to another directory:
-
Connect to the target database using rman and backup the database ---> NODE 1
$ rman target /Recovery Manager: Release
10.2.0.1.0 - Production on Tue Feb 13 00:29:33
2007Copyright - 1982,
2005, Oracle. All rights reserved.connected to target database: ORA10G
(DBID=3932056136)RMAN>
backup database plus archivelog;Starting
backup at 13-FEB-07current log archivedusing channel ORA_DISK_1channel
ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying
archive log(s) in backup setinput archive log thread=1
sequence=143
recid=109 stamp=614392105channel ORA_DISK_1: starting piece 1 at
13-FEB-07channel ORA_DISK_1: finished piece
1 at 13-FEB-07piece
handle=/u01/oracle/product/oradata/ora10g/fra/ORA10G/backupset/2007_02_13/o1_mf_annnn_TAG20070213T002825_2x21kbds_.bkp
tag=TAG20070213T002825 comment=NONEchannel ORA_DISK_1: backup set
complete, elapsed time: 00:00:02Finished backup at 13-FEB-07Starting
backup at 13-FEB-07using channel ORA_DISK_1channel ORA_DISK_1: starting
full datafile backupsetchannel ORA_DISK_1: specifying
datafile(s) in
backupsetinput datafile fno=00003 name=/u01/oracle/product/oradata/ora10g/data/sysaux01.dbfinput
datafile
fno=00001 name=/u01/oracle/product/oradata/ora10g/data/system01.dbfinput
datafile fno=00002 name=/u01/oracle/product/oradata/ora10g/data/undotbs01.dbfinput
datafile fno=00004 name=/u01/oracle/product/oradata/ora10g/data/users01.dbfinput
datafile fno=00005 name=/home/oracle/1.dbfinput
datafile fno=00006
name=/u01/oracle/product/oradata/ora10g/data/sysaux02.dbfinput datafile
fno=00007 name=/u01/oracle/product/oradata/ora10g/data/undotbs02.dbfchannel
ORA_DISK_1: starting piece 1 at 13-FEB-07channel ORA_DISK_1: finished
piece 1 at 13-FEB-07piece handle=/u01/oracle/product/oradata/ora10g/fra/ORA10G/backupset/2007_02_13/o1_mf_nnndf_TAG20070213T002827_2x21kd12_.bkp
tag=TAG20070213T002827 comment=NONEchannel ORA_DISK_1: backup set
complete, elapsed time: 00:00:55Finished backup at 13-FEB-07Starting
backup at 13-FEB-07current log archivedusing channel ORA_DISK_1channel
ORA_DISK_1: starting archive log backupsetchannel
ORA_DISK_1:
specifying archive log(s) in backup setinput archive log thread=1
sequence=144 recid=110 stamp=614392165channel
ORA_DISK_1: starting
piece 1 at 13-FEB-07channel ORA_DISK_1: finished piece 1 at
13-FEB-07piece handle=/u01/oracle/product/oradata/ora10g/fra/ORA10G/backupset/2007_02_13/o1_mf_annnn_TAG20070213T002925_2x21m6ty_.bkp
tag=TAG20070213T002925 comment=NONEchannel ORA_DISK_1: backup set
complete, elapsed time: 00:00:02Finished backup at 13-FEB-07Starting
Control File and SPFILE Autobackup at 13-FEB-07piece
handle=/u01/oracle/product/ora10g/dbs/c-3932056136-20070213-02 comment=NONEFinished
Control File and SPFILE Autobackup at 13-FEB-07RMAN> exit
-
Move the following files to the NODE 2 :
+ The database backup pieces
+
Controlfile backup piece
+ The parameter file i.e init.ora file
-
Edit the PFILE on NODE 2 to change the environment specific parameters like .
user_dump_dest =
background_dump_dest =
control_files =
-
Once the PFILE is suitably modified invoke Rman on the NODE 2 after setting
the Oracle environment variables and start the database in nomount mode:
[oracle@test-br test]$ export
ORACLE_HOME=/u01/oracle/product/ora10g
[oracle@test-br test]$ export
ORACLE_SID=ora10g
[oracle@test-br test]$ export
PATH=$ORACLE_HOME/bin:$PATH
[oracle@test-br test]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Feb 13 00:36:55 2015
Copyright - 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 205520896 bytes
Fixed Size 1218508 bytes
Variable Size 75499572 bytes
Database
Buffers 121634816 bytes
Redo Buffers 7168000 bytes
-
Restore the controlfile from the backup piece.
RMAN> restore controlfile from
'/u01/oracle/product/ora10g/dbs/c-3932056136-20070213-02';
Starting
restore at 13-FEB-07
using target database control file instead of
recovery catalog
allocated channel: ORA_DISK_1
channel
ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: restoring
control file
channel ORA_DISK_1: restore complete,
elapsed time:
00:00:02
output filename=/u01/oracle/product/oradata/ora10g/cntrl/control01.ctl
Finished restore at 13-FEB-07
-
Mount the database.
RMAN>
alter database mount ;
-
Now catalog the backup pieces that were shipped from NODE 1.
RMAN> catalog backuppiece
'/home/oracle/test/backup/o1_mf_annnn_TAG20070213T002925_2x21m6ty_.bkp';Starting
implicit crosscheck backup at 13-FEB-07allocated channel:
ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKCrosschecked
3
objectsFinished implicit crosscheck backup at 13-FEB-07Starting implicit
crosscheck copy at 13-FEB-07using channel
ORA_DISK_1Finished implicit
crosscheck copy at 13-FEB-07searching for all files in the recovery
areacataloging files...no
files catalogedcataloged backuppiecebackup
piece
handle=/home/oracle/test/backup/o1_mf_annnn_TAG20070213T002925_2x21m6ty_.bkp
recid=41 stamp=614393265RMAN> catalog backuppiece
'/home/oracle/test/backup/o1_mf_annnn_TAG20070213T002825_2x21kbds_.bkp';cataloged
backuppiecebackup piece
handle=/home/oracle/test/backup/o1_mf_annnn_TAG20070213T002825_2x21kbds_.bkp
recid=42 stamp=614393292RMAN>
catalog backuppiece
'/home/oracle/test/backup/o1_mf_nnndf_TAG20070213T002827_2x21kd12_.bkp';cataloged
backuppiecebackup
piece
handle=/home/oracle/test/backup/o1_mf_nnndf_TAG20070213T002827_2x21kd12_.bkp
recid=43 stamp=614393310
-
Get to know the last sequence available in the archivelog backup using the
following command.
RMAN > list backup of archivelog
all;
-
Rename the Redologfiles,so that they can be created in new locations when
opened the database is opened in resetlogs.
SQL>
alter database rename file '/u01/oracle/product/oradata/ora10g/log/redo01.log'
to '/home/oracle/test/log/redo01.log';..................
-
Now restore the datafiles to new locations and recover.
RMAN> run { set until sequence <seq_no>
set newname for datafile 1 to '/home/oracle/test/data/sys01.dbf';
set
newname for datafile 2 to '/home/oracle/test/data/undotbs01.dbf'; set
newname for datafile 3 to '/home/oracle/test/data/sysaux01.dbf';
set
newname for datafile 4 to '/home/oracle/test/data/users01.dbf'; set
newname for datafile 5 to '/home/oracle/test/data/1.dbf';
set newname
for datafile 6 to '/home/oracle/test/data/sysaux02.dbf'; set newname for
datafile 7 to '/home/oracle/test/data/undotbs02.dbf';
restore
database; switch datafile all; recover database; alter database open
resetlogs; }
Here, Rampant TechPress author
Kamran Agayev describes how to copy or clone a database using RMAN.
Steps to clone a database using RMAN:
1: Create a password file on the destination server
2: Establish
connectivity between the target and destination server (tnsnames.ora,
sqlnet.ora)
3: Create the directories for the database files
4: Take the RMAN backup from the target server and copy it to the
destination server.
5: Startup the destination database in nomount
mode
6: Run the RMAN duplicate database command
Also see my notes on the 12c RMAN noopen
clause.