Please note that Migration is different from Up-gradation. Migration is something where we move database to entirely new system, where as up-gradation refers to just upgrading the bits in the same system.
We are going to Migrate the the database from 10g(10.2.0.4) to 11g(11.2.0.4) with minimal downtime.
Below mentioned high level steps are followed in MIgrating 10g database to 11g using rman .
1) Take Level 0 Rman Backup of source. (No Downtime Required)
2) Restore Level 0 backup in 11g Database.(No Downtime Required)
3) Take the system downtime and take the level 1 backup.(Downtime Required)
4) Recover the 11g database using level 1 backup.(Downtime Required)
5) Run the upgrade scripts.(Downtime Required)
Step 1 & Step 2 are high time consuming steps and we can complete these steps before downtime starts.
In this environment, we are migrating the database “OraDB” from “orahost1″ to “orahost2″.
Pre-Requisites:
1)Install 11g software in orahost2.
2)It is mandatory to copy the ‘utlu112i.sql’ script from 11g home to 10g home and execute the script in 10g database.
This script will add a column named tz_version to table named registry$database.
3) Copy pfile from 10g to 11g home and edit the parameters as per your new box settings.
4) Start the database in nomount state.
Startup Nomount (In orahost2)
Backup & Restore of Level 0 :
1) Take the backup using below script.
run
{
ALLOCATE CHANNEL ch1 type disk;
ALLOCATE CHANNEL ch2 type disk;
ALLOCATE CHANNEL ch3 type disk;
ALLOCATE CHANNEL ch4 type disk;
ALLOCATE CHANNEL ch5 type disk;
ALLOCATE CHANNEL ch6 type disk;
BACKUP INCREMENTAL LEVEL 0 as compressed backupset DATABASE format ‘/DBF_FULLRMAN_BKP_%s’ plus archivelog format ‘/Archivelog_BKP_%s’;
RELEASE CHANNEL ch1;
RELEASE CHANNEL ch2;
RELEASE CHANNEL ch3;
RELEASE CHANNEL ch4;
RELEASE CHANNEL ch5;
RELEASE CHANNEL ch6;
}
2) After the backup, transfer the backup sets to orahost2.
3) Get the Datafiles & Tempfiles from 10g Home and set the new location (Ignore this step if target location has same file system structure).
select file_name,file_id from dba_data_Files;
select file_name,file_id from dba_temp_Files;
4) Get the redolog file names from 10g home as we need to rename it to new location (Ignore this step if target location has same file system structure).
select * from v$log;
5) Register the backup location in catalog.
Run these steps in 11g host.
RMAN> connect target /
connected to target database: oradb (DBID=1400919100)
RMAN> startup nomount
RMAN> restore controlfile from ‘/CONTROLFILE_BKP’; (You can find control file backup in the same location)
RMAN> alter database mount;
RMAN> catalog start with ”;
Register all the backups sets copied from 10g host.
RMAN>run
{
ALLOCATE CHANNEL ch1 type disk;
ALLOCATE CHANNEL ch2 type disk;
ALLOCATE CHANNEL ch3 type disk;
ALLOCATE CHANNEL ch4 type disk;
ALLOCATE CHANNEL ch5 type disk;
ALLOCATE CHANNEL ch6 type disk;
set newname for datafile 1 to ‘/system01.dbf’;
set newname for datafile 2 to ‘/undo01.dbf’;
set newname for datafile 3 to ‘/sysaux.dbf’;
set newname for datafile 3 to ‘/users.dbf’;
set newname for tempfile 1 to ‘/temp01.dbf’;
set newname for tempfile 2 to ‘/temp02.dbf’;
SQL “ALTER DATABASE RENAME FILE ”REDO01.LOG” TO ”REDO01.LOG” “;
SQL “ALTER DATABASE RENAME FILE ”REDO02.LOG” TO ”REDO02.LOG” “;
restore database;
switch datafile all;
switch tempfile all;
RELEASE CHANNEL ch1;
RELEASE CHANNEL ch2;
RELEASE CHANNEL ch3;
RELEASE CHANNEL ch4;
RELEASE CHANNEL ch5;
RELEASE CHANNEL ch6;
}
Once, database is restored. Keep in intact and go to 10g Host and make the database down ( Downtime Starts).
level 1 backup with Downtime :
1) Make the clean shutdown of the 10g database and start it in mount state.
SQL> Shut Immediate;
SQL> Startup mount;
2) Go to RMAN Prompt and run the below script to take the incremental backup in 10g database.
RMAN>run
{
ALLOCATE CHANNEL ch1 type disk;
ALLOCATE CHANNEL ch2 type disk;
BACKUP INCREMENTAL LEVEL 1 as compressed backupset DATABASE format ‘/DBF_INCRRMAN_BKP_%s’ plus archivelog format ‘/Archivelog_INCR_BKP_%s’;
RELEASE CHANNEL ch1;
RELEASE CHANNEL ch2;
}
3) Copy the backups to 11g host and register it in the catalog.
RMAN> catalog start with ”;
4) Before recovering the database , list the backup of archivelogs and search for the archivelog with max sequence number.
RMAN> list backup of archivelog all;
List of Archived Logs in backup set 72
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——— ———- ———
1 5693 270379299 29-OCT-14 270418704 29-OCT-14
BS Key Size Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
73 19.82M DISK 00:00:00 30-OCT-14
BP Key: 80 Status: AVAILABLE Compressed: YES Tag: TAG20141030T133221
Piece Name: \ARCHIVELOG_INCR_BKP_174
List of Archived Logs in backup set 73
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——— ———- ———
1 5694 270418704 29-OCT-14 270494841 30-OCT-14
Here, 5694 is the is the maximum sequence number.
RMAN > Recover database until sequence 5694;
5) Once the database is recovered, open the database with resetlogs upgrade.
SQL> Alter database open resetlogs upgrade;
6) Once the database is open, Run the catupgrade script.
@?\rdbms\admin\catupgrd.sql
Oracle DMA 