Source DB Name : TESTDB
Source DB Version : 11.2.0.1.0
Source DB Host Name : HOSTNAME_1
Target DB Name : TESTDB
Target DB Version : 11.2.0.4
Target DB Host Name : HOSTNAME_2
Step-1
=====
Installing the Oracle binary version 11.2.0.4 on HOSTNAME_2.
Step – 2
======
Taking a full backup of Database TESTDB from Source server (HOSTNAME_1),
Below command can be run on the source database RMAN prompt.
While finishing the backup it will be copied to the target location
@ HOSTNAME_1 ———> This should be executed in the Source database.
rman target / nocatalog
run {
allocate channel disk_1 type disk format ‘\\HOSTNAME_2\Source_backup\%U’;
backup keep until time ‘SYSDATE+3’ as BACKUPSET tag ‘%TAG’ format ‘\\HOSTNAME_2\Source_backup\hot_%d_%t’ database;
backup as BACKUPSET tag ‘%TAG’ format ‘\\HOSTNAME_2\Source_backup\%d_log_%d_%t’ archivelog all not backed up 1 times;
delete noprompt force obsolete device type disk;
delete noprompt expired backup;
delete noprompt expired archivelog all;
delete force obsolete;
release channel oem_backup_disk1;
backup current controlfile format ‘\\HOSTNAME_2\Source_backup\clone_TESTDB.ctl’;
}
Step -3
======
D:\programs\product\11204\dbhome_1\BIN>oradim.exe -NEW -SID TESTDB -pfile D:\programs\product\11204\dbhome_1\dbs\initTESTDB.ora
Instance created.
Step-4
=====
Copy the initTESTDB to D:\programs\product\11204\dbhome_1\database
SQL> CONN / AS SYSDBA
Connected to an idle instance
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1081520128 bytes
Fixed Size 2288080 bytes
Variable Size 574621232 bytes
Database Buffers 499122176 bytes
Redo Buffers 5488640 bytes
SQL> exit
Note: In windows init.ora file should be in $ORACLE_HOME/database folder instead of $ORACLE_HOME/dbs folder.
Step -5
======
Restore the control file form the backup.
D:\programs\product\11204\dbhome_1\BIN>rman target /
Recovery Manager: Release 11.2.0.4.0 – Production on Sun Nov 15 09:32:50 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB (not mounted)
RMAN> restore controlfile from ‘D:\Source_backup\CLONE_TESTDB.CTL’;
Starting restore at 15-NOV-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=122 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=D:\PROGRAMS\PRODUCT\11204\DBHOME_1\DATABASE\TESTDB\CONTROL01.CTL
output file name=D:\PROGRAMS\PRODUCT\11204\DBHOME_1\DATABASE\TESTDB\CONTROL02.CTL
output file name=D:\PROGRAMS\PRODUCT\11204\DBHOME_1\DATABASE\TESTDB\CONTROL03.CTL
Finished restore at 15-NOV-15
Step -6
======
Restore and recover the database.
RMAN> catalog start with ‘D:\Source_backup’;
RMAN> run
2> {
3> set newname for datafile 1 to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\SYSTEM01.DBF’;
4> set newname for datafile 2 to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\SYSAUX01.DBF’;
5> set newname for datafile 3 to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\UNDOTBS01.DBF’;
6> set newname for datafile 4 to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\USERS01.DBF’;
7> restore database;
8> switch datafile all;
9> recover database;
10> }
Step -7
=====
Rectifying log location mismatch error.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-create online log
‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG1A.ORA’
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG3A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG3A.ORA’;
alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG3B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG3B.ORA’;
alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG2A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG2A.ORA’;
alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG2B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG2B.ORA’;
alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG1A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG1A.ORA’;
alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG1B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG1B.ORA’;
alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG4A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG4A.ORA’;
alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG4B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG4B.ORA’;
alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG5A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG5A.ORA’;
alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG5B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG5B.ORA’;
alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG6A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG6A.ORA’;
alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG6B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG6B.ORA’;
alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG7A.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG7A.ORA’;
alter database rename file ‘D:\PROGRAMS\ORACLE11GR2\DATABASE\TESTDB\LOG7B.ORA’ to ‘D:\programs\product\11204\dbhome_1\DATABASE\TESTDB\LOG7B.ORA’;
Step – 8
======
Now open the database with “ALTER DATABASE OPEN RESETLOGS UPGRADE” command.
SQL> alter database open resetlogs upgrade;
Database altered.
If you would try opening with just “ALTER DATABASE OPEN RESETLOGS”, then it might fail with the below error.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 6773
Session ID: 19 Serial number: 25
Step -9
======
Once opened, run the CATUPGRD.SQL script on the target database to upgrade the database
SQL> spool catupgrade.log
SQL> @D:\programs\product\11204\dbhome_1\RDBMS\ADMIN\catupgrd.sqlplus SQL> spool catupgrade.log
Note: If any errors are encountered, fix them and re-run the script before proceeding further.
Step -10
=======
Now start the target database normally and look out for any INVALID objects. Compile them by running the UTLRP.SQL script
sqlplus / as sysdba
SQL> startup
SQL> @D:\programs\product\11204\dbhome_1\RDBMS\ADMIN\utlrp.sql
And check all the registry components are valid as per the Source.
SQL> SET PAUSE ON
SQL> SET PAUSE ‘Press Return to Continue’
SQL> SET PAGESIZE 60
SQL> SET LINESIZE 300
SQL> SET VERIFY OFF
SQL> COL comp_name FOR a44 HEA ‘Component’
SQL> COL version FOR a17 HEA ‘Version’
SQL> COL status FOR a17 HEA ‘Status’
SQL>
SQL> SELECT comp_name, version, status FROM dba_registry;
COMP_NAME VERSION STATUS
—————————————- ——————– ———–
Oracle Text 11.2.0.4.0 VALID
Oracle Application Express 3.2.1.00.10 INVALID
Oracle Multimedia 11.2.0.4.0 VALID
Oracle XML Database 11.2.0.4.0 VALID
Oracle Expression Filter 11.2.0.4.0 VALID
Oracle Rules Manager 11.2.0.4.0 VALID
Oracle Workspace Manager 11.2.0.4.0 VALID
Oracle Database Catalog Views 11.2.0.4.0 VALID
Oracle Database Packages and Types 11.2.0.4.0 VALID
JServer JAVA Virtual Machine 11.2.0.4.0 VALID
Oracle XDK 11.2.0.4.0 VALID
Oracle Database Java Packages 11.2.0.4.0 VALID
12 rows selected.
Shanoj 