To restore a database into a new host using RMAN there are two methods
1. RESTORE
2. DUPLICATE
--A few points of comparison
The main problem of DUPLICATE command is the RMAN backups from the source db have to be copied to the same file location (path) in the destination host.
Duplicate command changes the DBBID, so that database also can be registered in the same RMAN Catalog as of source database. And also we can change the Database Name.
Same can be achieved using RESTORE option also by using NID after restoring the full db.
Same can be achieved using RESTORE option also by using NID after restoring the full db.
DUPLICATE command is executed in the nomount stage, it restores the controlfile also, so we can use the “DB_FILE_NAME_CONVERT” & “LOG_FILE_NAME_CONVERT” parameters to change the file names while restoring. i.e. the controlfile is restored and it is aware of the file name change done in the pfile.
But , when using RESTORE command to restore a database into new host even if we use the “DB_FILE_NAME_CONVERT” & “LOG_FILE_NAME_CONVERT” parameters to change the file names it will not take effect as we do the restore step by step ,first we nomount ,the restore the controlfile using the backup from the source db , so here the controlfiles knows the datafiles and logfiles as it were on the source db, so to change the location we have to either use alter database rename file command in sqlplus or use set newname for datafile 1 to ..‘ command to rename the files.
============================
**********RESTORE************
============================
**********RESTORE************
============================
Note: Take special care not to overwrite the target databases information recorded in the recovery catalog while cloning, discussed later in this doc
1. Copy parameter file to new host (make the location changes of parameters if required).
2. Restore the backup from tape to the new host.
3. Use catalog command to update the RMAN repository with the new file names (location different)
Use change...uncatalog to remove other old backups from the repository
4. Determine the SCN for Incomplete Recovery (UNTIL TIME ,UNTIL SCN or UNTIL TIME can be used)
Perform incomplete recovery up to the lowest SCN of the most recently archived log in each thread and then open with the RESETLOGS option
SELECT MIN(maxnc) FROM
(SELECT MAX(a.NEXT_CHANGE#) maxnc
FROM V$ARCHIVED_LOG a, V$THREAD t
WHERE a.THREAD# = t.THREAD#
AND a.ARCHIVED='YES'
AND t.ENABLED='DISABLED'
GROUP BY a.THREAD#);
(SELECT MAX(a.NEXT_CHANGE#) maxnc
FROM V$ARCHIVED_LOG a, V$THREAD t
WHERE a.THREAD# = t.THREAD#
AND a.ARCHIVED='YES'
AND t.ENABLED='DISABLED'
GROUP BY a.THREAD#);
5. From the new host connect to the target using RMAN
set oracle_sid=<sid_on_host> -- have to be set if using RESTORE …..FROM AUTOBACKUP
rman target / nocatalog ---connect to rman without connecting to recovery catalog
(Note : even if using recovery catalog don connect to it while restoring db into new host using RECOVER)
(Note : even if using recovery catalog don connect to it while restoring db into new host using RECOVER)
6. set dbid=<DBID>
startup nomount -- rman will fail as the parameter file cannot be found
(You can find the DBID in -
v$database in target db ,RC_DATABASE in catalog
the rman output (cmd line and v$RMAN_STATUS)
the filename of the controlfile autobackup)
----startup fails-----
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file
'\<location>\initSID.ora'
Trying to start the Oracle instance without parameter files ...
Oracle instance started
7.
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...';
RESTORE SPFILE TO PFILE '<Location on the new host\initSID.ora>' FROM AUTOBACKUP;
SHUTDOWN ABORT;
}
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...';
RESTORE SPFILE TO PFILE '<Location on the new host\initSID.ora>' FROM AUTOBACKUP;
SHUTDOWN ABORT;
}
Or RESTORE SPFILE TO PFILE FROM ‘<file path + name>’
---then change the location specific parameters
Those ending with _DEST & _PATH to reflect new directory structure
8. Restart the instance using the pfile
STARTUP FORCE NOMOUNT PFILE='<Location on the new host\initSID.ora>'
9.
b'coz you have edited the location of the control file(CONTROL_FILES) in the pfile it will
get restored into the new location
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...';
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;
}
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...';
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;
}
--restore controlfile from autobackup will work only of the backup is in the location specified in the rman configuration (RMAN CONFIGURE)
Otherwise we can use
RESTORE CONTROLFILE FROM 'N:\DUP\rman\C-190696285-20110219-01'
Starting restore at 19-FEB-11
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
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/19/2011 17:35:49
ORA-19502: write error on file "N:\DUP\CTL\CONTROL02.CTL", blockno 256
(blocksize=16384)
ORA-27072: File I/O error
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-19600: input file is control file (N:\DUP\CTL\CONTROL01.CTL)
ORA-19601: output file is control file (N:\DUP\CTL\CONTROL02.CTL)
RMAN> restore controlfile from 'N:\DUP\rman\C-190696285-20110219-01';
Starting restore at 19-FEB-11
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=N:\DUP\CTL\CONTROL01.CTL
output filename=N:\DUP\CTL\CONTROL02.CTL
output filename=N:\DUP\CTL\CONTROL03.CTL
Finished restore at 19-FEB-11
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
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/19/2011 17:35:49
ORA-19502: write error on file "N:\DUP\CTL\CONTROL02.CTL", blockno 256
(blocksize=16384)
ORA-27072: File I/O error
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-19600: input file is control file (N:\DUP\CTL\CONTROL01.CTL)
ORA-19601: output file is control file (N:\DUP\CTL\CONTROL02.CTL)
RMAN> restore controlfile from 'N:\DUP\rman\C-190696285-20110219-01';
Starting restore at 19-FEB-11
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=N:\DUP\CTL\CONTROL01.CTL
output filename=N:\DUP\CTL\CONTROL02.CTL
output filename=N:\DUP\CTL\CONTROL03.CTL
Finished restore at 19-FEB-11
10. Connect to sqlplus
SQL> COLUMN NAME FORMAT a60
SQL> SPOOL LOG 'db_filenames.out'
SQL> SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE
UNION
SELECT GROUP#,MEMBER FROM V$LOGFILE;
SQL> SPOOL OFF
SQL EXIT
SQL> SPOOL LOG 'db_filenames.out'
SQL> SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE
UNION
SELECT GROUP#,MEMBER FROM V$LOGFILE;
SQL> SPOOL OFF
SQL EXIT
11.
RMAN> SQL 'ALTER DATABASE MOUNT';
12. After copying the RMAN backup into the new host into new location we will have to register the backup file name and remove the other backup file information
--archives need not be copied to the same location instead can be copied to the location mentioned in the parameter file of the new instance to which we are about to clone.
RMAN> CATALOG START WITH 'N:\DUP\rman\' NOPROMPT;
searching for all files that match the pattern N:\DUP\rman\
List of Files Unknown to the Database
=====================================
File Name: N:\DUP\RMAN\1AM52L9Q_1_1
File Name: N:\DUP\RMAN\1BM52LAT_1_1
File Name: N:\DUP\RMAN\C-190696285-20110219-00
File Name: N:\DUP\RMAN\C-190696285-20110219-01
File Name: N:\DUP\RMAN\LOG_T743527910_S45_P1
File Name: N:\DUP\RMAN\LOG_T743527915_S46_P1
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: N:\DUP\RMAN\1AM52L9Q_1_1
File Name: N:\DUP\RMAN\1BM52LAT_1_1
File Name: N:\DUP\RMAN\C-190696285-20110219-00
File Name: N:\DUP\RMAN\C-190696285-20110219-01
File Name: N:\DUP\RMAN\LOG_T743527910_S45_P1
File Name: N:\DUP\RMAN\LOG_T743527915_S46_P1
=======================
File Name: N:\DUP\RMAN\1AM52L9Q_1_1
File Name: N:\DUP\RMAN\1BM52LAT_1_1
File Name: N:\DUP\RMAN\C-190696285-20110219-00
File Name: N:\DUP\RMAN\C-190696285-20110219-01
File Name: N:\DUP\RMAN\LOG_T743527910_S45_P1
File Name: N:\DUP\RMAN\LOG_T743527915_S46_P1
As the other backup files will not be in the target host we can crosscheck and delete the expired backups.
RMAN > CROSSCHECK BACKUP
RMAN > CROSSCHECK ARCHIVELOG ALL
RMAN > DELETE EXPIRED BACKUP
RMAN > DELETE EXPIRED ARCHIVELOG ALL
RMAN > CROSSCHECK ARCHIVELOG ALL
RMAN > DELETE EXPIRED BACKUP
RMAN > DELETE EXPIRED ARCHIVELOG ALL
RUN
{
ALLOCATE CHANNEL C DEVICE TYPE DISK;
SET NEWNAME FOR DATAFILE 1 TO 'N:\DUP\DFS\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO 'N:\DUP\DFS\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 3 TO 'N:\DUP\DFS\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'N:\DUP\DFS\USERS01.DBF';
SET NEWNAME FOR DATAFILE 5 TO 'N:\DUP\DFS\EXAMPLE01.DBF';
SET NEWNAME FOR DATAFILE 6 TO 'N:\DUP\DFS\NEW001.DBF';
SET NEWNAME FOR DATAFILE 7 TO 'N:\DUP\DFS\TBS01.DBF';
SQL "ALTER DATABASE RENAME FILE ''F:\ORACLE\PRODUCT\10.2.0\ORADATA\NEWDB\MYNEWDB\REDO01.LOG'' TO ''N:\DUP\DFS\REDO01.LOG''";
SQL "ALTER DATABASE RENAME FILE ''F:\ORACLE\PRODUCT\10.2.0\ORADATA\NEWDB\MYNEWDB\REDO02.LOG'' TO ''N:\DUP\DFS\REDO02.LOG''";
SQL "ALTER DATABASE RENAME FILE ''F:\ORACLE\PRODUCT\10.2.0\ORADATA\NEWDB\MYNEWDB\REDO03.LOG'' TO ''N:\DUP\DFS\REDO03.LOG''";
#SET UNTIL SCN;
SET UNTIL SEQUENCE 14;
#UNTIL SEQUENCE 14 MEANS IT WILL RESTORE TILL 13
#SET UNTIL TIME "to_date((to_char(SYSDATE,'dd.mm.yyyy')||' 08:00:00'),'dd.mm.yyyy hh24:mi:ss')";
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}
{
ALLOCATE CHANNEL C DEVICE TYPE DISK;
SET NEWNAME FOR DATAFILE 1 TO 'N:\DUP\DFS\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO 'N:\DUP\DFS\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 3 TO 'N:\DUP\DFS\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'N:\DUP\DFS\USERS01.DBF';
SET NEWNAME FOR DATAFILE 5 TO 'N:\DUP\DFS\EXAMPLE01.DBF';
SET NEWNAME FOR DATAFILE 6 TO 'N:\DUP\DFS\NEW001.DBF';
SET NEWNAME FOR DATAFILE 7 TO 'N:\DUP\DFS\TBS01.DBF';
SQL "ALTER DATABASE RENAME FILE ''F:\ORACLE\PRODUCT\10.2.0\ORADATA\NEWDB\MYNEWDB\REDO01.LOG'' TO ''N:\DUP\DFS\REDO01.LOG''";
SQL "ALTER DATABASE RENAME FILE ''F:\ORACLE\PRODUCT\10.2.0\ORADATA\NEWDB\MYNEWDB\REDO02.LOG'' TO ''N:\DUP\DFS\REDO02.LOG''";
SQL "ALTER DATABASE RENAME FILE ''F:\ORACLE\PRODUCT\10.2.0\ORADATA\NEWDB\MYNEWDB\REDO03.LOG'' TO ''N:\DUP\DFS\REDO03.LOG''";
#SET UNTIL SCN;
SET UNTIL SEQUENCE 14;
#UNTIL SEQUENCE 14 MEANS IT WILL RESTORE TILL 13
#SET UNTIL TIME "to_date((to_char(SYSDATE,'dd.mm.yyyy')||' 08:00:00'),'dd.mm.yyyy hh24:mi:ss')";
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}
EXIT
12. ALTER DATABASE OPEN RESTLOGS.
--- if the restored to a heigher version than the original db
we will have to
we will have to
ALTER DATABASE OPEN UPGRADE
---AFTER TESTING TO REMOVE THE TEST DATABASE
RMAN > STARTUP FORCE NOMOUNT PFILE='<LOCATION>'
RMAN > DROP DATABASE
RMAN > DROP DATABASE
============================
**********DUPLICATE**********
============================
**********DUPLICATE**********
============================
Duplicate a Database Using RMAN
A nice feature of RMAN is the ability to duplicate, or clone, a database from a previous backup.
It is possible to create a duplicate database on a remote server with the same file structure, a remote server will a different file structure or the local server with a different file structure. In this article I'll demonstrate the last method, how to duplicate a database on the local server with a different file structure. This can prove useful when you want to recover selected objects from a backup, rather than roll back a whole database or tablespace.
--but if cloning to a different HOST the backup have to copied to the new host into the same file path
--archives need not be copied to the same location instead can be copied to the location mentioned in the pfile of the new instance to which we are about to clone.
--archives need not be copied to the same location instead can be copied to the location mentioned in the pfile of the new instance to which we are about to clone.
1. First we must create a password file for the duplicate instance:
orapwd file=/u01/app/oracle/product/9.2.0.1.0/dbs/orapwDUP password=password entries=10
orapwd file=/u01/app/oracle/product/9.2.0.1.0/dbs/orapwDUP password=password entries=10
2.
Next add the appropriate entries into the tnsnames.ora and listener.ora files in the $ORACLE_HOME/network/admin directory. Remember to load the new configuration into the listener:
# Added to the listener.ora SID_LIST
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/9.2.0.1.0)
(SID_NAME = DUP)
)
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/9.2.0.1.0)
(SID_NAME = DUP)
)
# Added to the tnsnames.ora
DUP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1.tshcomputing.com)(PORT = 1521))
)
(CONNECT_DATA =
(ORACLE_SID = DUP)
)
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1.tshcomputing.com)(PORT = 1521))
)
(CONNECT_DATA =
(ORACLE_SID = DUP)
)
)
# Reload listener
lsnrctl reload
lsnrctl reload
3.
Next create an init.ora file for the duplicate database. Since we are duplicating the database onto the same server as the original we must convert the file names so there is no conflict:
# Minimum Requirement.
DB_NAME=DUP
CONTROL_FILES=(/u02/oradata/DUP/control01.ctl,
/u02/oradata/DUP/control02.ctl,
/u02/oradata/DUP/control03.ctl)
# Convert file names to allow for different directory structure.
DB_FILE_NAME_CONVERT=(/u02/oradata/TSH1/,/u02/oradata/DUP/) LOG_FILE_NAME_CONVERT=(/u01/oradata/TSH1/,/u01/oradata/DUP/)
CONTROL_FILES=(/u02/oradata/DUP/control01.ctl,
/u02/oradata/DUP/control02.ctl,
/u02/oradata/DUP/control03.ctl)
# Convert file names to allow for different directory structure.
DB_FILE_NAME_CONVERT=(/u02/oradata/TSH1/,/u02/oradata/DUP/) LOG_FILE_NAME_CONVERT=(/u01/oradata/TSH1/,/u01/oradata/DUP/)
# make sure block_size and compatible parameters
# match if you are not using the default.
DB_BLOCK_SIZE=8192
COMPATIBLE=9.2.0.0.0
# match if you are not using the default.
DB_BLOCK_SIZE=8192
COMPATIBLE=9.2.0.0.0
Instead we can also restore from the spfie backup which is more simpler, and then change the DB_NAME and file name parameters
Next we connect to the duplicate instance:
ORACLE_SID=DUP; export ORACLE_SID
sqlplus /nolog
conn / as sysdba
sqlplus /nolog
conn / as sysdba
4.
Next we create an SPFILE based on the init.ora:
Next we create an SPFILE based on the init.ora:
CREATE SPFILE FROM PFILE='/u01/app/oracle/admin/DUP/pfile/init.ora';
Next we start the database in NOMOUNT mode:
STARTUP FORCE NOMOUNT;
5.
With the duplicate database started we can now connect to it from RMAN. For the duplication to work we must connect to the original database (TARGET), the recovery catalog (CATALOG) and our duplicate database (AUXILIARY):
ORACLE_SID=DUP; export ORACLE_SID
RMAN TARGET sys/password@connection CATALOG rman/rman@ connection AUXILIARY /
RMAN TARGET sys/password@connection CATALOG rman/rman@ connection AUXILIARY /
We can then duplicate the database using one of the following commands:
# Duplicate database to TARGET's current state.
DUPLICATE TARGET DATABASE TO DUP;
# Duplicate database to TARGET's state 4 days ago.
DUPLICATE TARGET DATABASE TO DUP UNTIL TIME 'SYSDATE-4';
DUPLICATE TARGET DATABASE TO DUP;
# Duplicate database to TARGET's state 4 days ago.
DUPLICATE TARGET DATABASE TO DUP UNTIL TIME 'SYSDATE-4';
The time it takes to complete varies depending on the size of the database and the specification of the server. Once the process is finished RMAN produces a completion message and you have your duplicate instance.
One can use any of the two methords according to the circumstances and specific requirements.
Good article friend
ReplyDeleteI will try this...