Cloning the database from 11.2.0.2 to 11.2.0.3 on a new server
RMAN Backup source server in 11.2.0.2.0
++++++++++++++++++++++++++++++++
Recovery Manager: Release 11.2.0.2.0 - Production on Fri AUG 14 15:15:59 2014
RMAN> backup database plus archivelog delete input;
Starting backup at 14-AUG-14
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=17 RECID=1 STAMP=788540852
input archived log thread=1 sequence=18 RECID=2 STAMP=788541371
channel ORA_DISK_1: starting piece 1 at 14-AUG-14
channel ORA_DISK_1: finished piece 1 at 14-AUG-14
piece handle=/u01/app/oracle/flash_recovery_area01/DBUPG/backupset/o1_mf_annnn_TAG20140713T151612_800shf7w_.bkp tag=TAG20140713T151612 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area01/DBUPG/archivelog/o1_mf_1_17_800rz40y_.arc RECID=1 STAMP=788540852
archived log file name=/u01/app/oracle/flash_recovery_area01/DBUPG/archivelog/o1_mf_1_18_800shcsd_.arc RECID=2 STAMP=788541371
Finished backup at 14-AUG-14
Starting backup at 14-AUG-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DS8000_DG/dbupg/datafile/system.271.788537119
input datafile file number=00002 name=+DS8000_DG/dbupg/datafile/sysaux.272.788537167
input datafile file number=00003 name=+DS8000_DG/dbupg/datafile/undotbs1.273.788537199
input datafile file number=00004 name=+DS8000_DG/dbupg/datafile/users.275.788537229
channel ORA_DISK_1: starting piece 1 at 14-AUG-14
channel ORA_DISK_1: finished piece 1 at 14-AUG-14
piece handle=/u01/app/oracle/flash_recovery_area01/DBUPG/backupset/o1_mf_nnndf_TAG20140713T151614_800shgw5_.bkp tag=TAG20140713T151614 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 14-AUG-14
channel ORA_DISK_1: finished piece 1 at 14-AUG-14
piece handle=/u01/app/oracle/flash_recovery_area01/DBUPG/backupset/o1_mf_ncsnf_TAG20140713T151614_800sjm29_.bkp tag=TAG20140713T151614 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-AUG-14
Starting backup at 14-AUG-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=19 RECID=3 STAMP=788541412
channel ORA_DISK_1: starting piece 1 at 14-AUG-14
channel ORA_DISK_1: finished piece 1 at 14-AUG-14
piece handle=/u01/app/oracle/flash_recovery_area01/DBUPG/backupset/o1_mf_annnn_TAG20140713T151652_800sjnf7_.bkp tag=TAG20140713T151652 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area01/DBUPG/archivelog/o1_mf_1_19_800sjn5q_.arc RECID=3 STAMP=788541412
Finished backup at 14-AUG-14
RMAN> backup current controlfile;
Starting backup at 14-AUG-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 14-AUG-14
channel ORA_DISK_1: finished piece 1 at 14-AUG-14
piece handle=/u01/app/oracle/flash_recovery_area01/DBUPG/backupset/o1_mf_ncnnf_TAG20140713T153435_800tkwl2_.bkp tag=TAG20140713T153435 comment=NONE
channel ORA_DISK_1: backup set
complete, elapsed time: 00:00:01
Finished backup at 14-AUG-14
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Create a spfile :
+++++++++++
*.control_files='+DS8000_DG/dbclone/controlfile/Current.277.788541913'
*.db_name='dbupg'
*.db_unique_name='dbclone'
*.audit_file_dest='/u01/app/oracle/admin/dbclone/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.db_block_size=8192
*.db_create_file_dest='+MMC'
*.db_domain=''
*.db_recovery_file_dest_size=107374182400
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area01'
*.diagnostic_dest='/u01/app/oracle'
*.log_file_name_convert='+DS8000_DG','+MMC'
*.memory_target=1031798784
*.open_cursors=300
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Make backup available on new server:
++++++++++++++++++++++++++++
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 13 15:33:24 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1027182592 bytes
Fixed Size 2227936 bytes
Variable Size 599785760 bytes
Database Buffers 419430400 bytes
Redo Buffers 5738496 bytes
SQL> show parameter db_n
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string dbupg
SQL> show parameter db_un
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string dbclone
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Restore the Controlfile :
++++++++++++++++
RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area01/DBUPG/backupset/o1_mf_ncnnf_TAG20140713T153435_800tkwl2_.bkp';
Starting restore at 13-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=290 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+DATA/dbclone/controlfile/current.277.788541913
Finished restore at 13-JUL-12
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Start the service in mount :
++++++++++++++++++++
RMAN> startup mount
database is already started
database mounted
released channel: ORA_DISK_1
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Restore the datafile :
+++++++++++++++
run {
SET NEWNAME FOR DATABASE TO '+DATA';
restore database ;
}
OR
+++
run
{
set newname for datafile 00001 to '+DATA/BDMDEV3/datafile/system.257.795697657';
set newname for datafile 00002 to '+DATA/BDMDEV3/datafile/undotbs1.256.795697551';
set newname for datafile 00003 to '+DATA/BDMDEV3/datafile/sysaux.264.795698741';
set newname for datafile 00004 to '+DATA/BDMDEV3/datafile/users.258.795698093';
set newname for datafile 00005 to '+DATA/BDMDEV3/datafile/ts_admin.261.795698675';
set newname for datafile 00006 to '+DATA/BDMDEV3/datafile/idx_ts_admin.262.795698701';
set newname for datafile 00007 to '+DATA/BDMDEV3/datafile/ts_subj.265.795698757';
set newname for datafile 00008 to '+DATA/BDMDEV3/datafile/idx_ts_subj.266.795698759';
set newname for datafile 00009 to '+DATA/BDMDEV3/datafile/ts_prot.267.795698763';
set newname for datafile 00010 to '+DATA/BDMDEV3/datafile/idx_ts_prot.268.795698765';
set newname for datafile 00011 to '+DATA/BDMDEV3/datafile/ts_gen.260.795698581';
set newname for datafile 00012 to '+DATA/BDMDEV3/datafile/idx_ts_gen.259.795698385';
set newname for datafile 00013 to '+DATA/BDMDEV3/datafile/ts_obs.269.795698769';
set newname for datafile 00014 to '+DATA/BDMDEV3/datafile/idx_ts_obs.270.795698773';
set newname for datafile 00015 to '+DATA/BDMDEV3/datafile/ts_flow.271.795698775';
set newname for datafile 00016 to '+DATA/BDMDEV3/datafile/idx_ts_flow.272.795698777';
set newname for datafile 00017 to '+DATA/BDMDEV3/datafile/ts_usracc.273.795698777';
set newname for datafile 00018 to '+DATA/BDMDEV3/datafile/idx_ts_usracc.274.795698779';
set newname for datafile 00019 to '+DATA/BDMDEV3/datafile/ts_ds_clob.275.795698779';
set newname for datafile 00020 to '+DATA/BDMDEV3/datafile/idx_ts_ds_clob.279.795698785';
set newname for datafile 00021 to '+DATA/BDMDEV3/datafile/ts_col_clob.276.795698781';
set newname for datafile 00022 to '+DATA/BDMDEV3/datafile/idx_ts_col_clob.280.795698785';
set newname for datafile 00023 to '+DATA/BDMDEV3/datafile/ts_lab.277.795698781';
set newname for datafile 00024 to '+DATA/BDMDEV3/datafile/users.263.795698727';
set newname for datafile 00025 to '+DATA/BDMDEV3/datafile/idx_ts_lab.278.795698783';
restore database;
switch datafile all;
}
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Recover the datafile :
+++++++++++++++
RMAN> recover database;
Starting recover at 14-AUG-14
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 14-AUG-14
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Startup with upgrade option :
++++++++++++++++++++++
SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 14 15:39:31 2014
SQL> alter database open resetlogs upgrade;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Now you can upgrade your database:
++++++++++++++++++++++++++++
$ cd $ORACLE_HOME/rdbms/admin
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> spool off
Once This is completed, the database will automatically Shut down.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/as sysdba"
SQL>startup
SQL> @utlu112s.sql
SQL> @utlrp.sql
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
After upgrade database you can change the database name using NID
nid TARGET=SYS/xxxxxx DBNAME=BDMDEV3 SETNAME=YES
Happy Learning
--Sreene
|
Thanks for sharing the information and it was really interesting. Thank you for sharing and please keep update like this
SQL DBA Training in Chennai