11g Migration using RMAN

The steps for 10g database:

1- Run the utlu112i.sql Pre-upgrade script. You can find this script under @$ORACLE_HOME/rdbms/admin/. It must be copied from the 11g database software.

SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql

This script adds a column named tz_version to table named registry$database. Pre-upgrade script updates this column with the value of the following query.

SQL> select version from v$timezone_file;

So it performs following operation.

SQL> ALTER TABLE registry$database ADD (tz_version NUMBER);
SQL> UPDATE registry$database set tz_version =4;

SQL> ALTER  PACKAGE “SYS”.”DBMS_REGISTRY”  COMPILE BODY;
SQL> ALTER VIEW “SYS”.”DBA_REGISTRY_DATABASE”  COMPILE;

2- Connect to 10g database and take RMAN full backup.

#rman target /
RMAN> backup as backupset database;

3- Copy 10g database backup files and archive files to 11g database server.

The steps for 11g database:

1- Create temporary pfile in $ORACLE_HOME/dbs

*.audit_file_dest=’/oracle/admin/TALIPDB/adump’
*.compatible=’11.2.0.0.0′
*.control_files=’+DATA/talipdb/controlfile/current.257.787742981′,’+DATA/talipdb/controlfile/current.258.787742983′
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_create_online_log_dest_1=’+RECO’
*.db_domain=”
*.db_name=’TALIPDB’
*.diagnostic_dest=’/oracle’
*.job_queue_processes=0
*.open_cursors=300
*.pga_aggregate_target=1G
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=2G
*.undo_tablespace=’UNDOTBS1′

2- Open the database in NOMOUNT state.

# rman target /

RMAN> startup nomount;

3- Restore controlfile from backup.

RMAN> restore controlfile from ‘/oracle/ora11g/talipdb/backup/c-784951186-20120620-02′;

4- Open the database in MOUNT state.

RMAN> alter database mount;

5- Catalog RMAN backup files and archive log files.

RMAN> catalog start with ‘/oracle/ora11g/talipdb/backup’;
RMAN> catalog start with ‘/oracle/ora11g/talipdb/archive’;

6- Restore 10g database backup to +DATA diskgroup and perform incomplete recovery.

RMAN> run
{
allocate channel c1 device type disk;
SET NEWNAME FOR DATAFILE 1 TO ‘+DATA’;
SET NEWNAME FOR DATAFILE 2 TO ‘+DATA’;
SET NEWNAME FOR DATAFILE 3 TO ‘+DATA’;
SET NEWNAME FOR DATAFILE 4 TO ‘+DATA’;
restore database until sequence 4;
switch datafile all;
recover database until sequence 4;
}

7-  Open the database with RESETLOGS UPGRADE.

# sqlplus / as sysdba
SQL> alter database open resetlogs upgrade;

8- Run the upgrade script.

SQL> SPOOL upgrade.log
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
SQL> SPOOL off

9- If our 10g and 11g database os platforms are different then you must run utlmmig.sql script.

————–Changing 32 bit to 64 bit————–
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE
SQL> SPOOL migrate.log
SQL> @$ORACLE_HOME/rdbms/admin/utlmmig.sql
SQL> SPOOL off
——————————————-

10- Now, you can open the database.

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

11- Run the Post-Upgrade script to check problems.

SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql

12- Compile invalid objects.

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

13- Drop the temporary file and create new one in +DATA diskgroup.

SQL> alter tablespace temp drop tempfile ‘/data_TALIPDB/temp01.dbf’;
SQL> alter tablespace temp add tempfile ‘+DATA’ size 1024M;