How To Restore 10g RMAN Backup to 11g?

In this article we will discuss about restoring 10g database backup to 11g software using RMAN. Suppose our 10g database is running on the ext3 file system and we want to restore it to 11g software running on the Oracle ASM.

After the database restoration using RMAN ,we have to upgrade this database into 11g by catupgrd.sql script.

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;

Talip Hakan Ozturk

Advertisements

29 thoughts on “How To Restore 10g RMAN Backup to 11g?

  1. jc nars

    Hi,
    I get this error when I try to restore the controlfile from the 10g backup piece.

    RMAN> restore controlfile from ‘\\exchum01\oracle\JULY_23_12\RMAN_frm_PROD\DBF_T20120723_S343696_P1.DBF’;

    Starting restore at 24-JUL-12
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=768 devtype=DISK

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of restore command at 07/24/2012 11:35:57
    ORA-19962: compression algorithm \\EXCHUM01\ORACLE\JULY_23_12\RMAN_FRM_PROD\DBF_T20120723_S343696_P1.DBF is not valid with release
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 5) Access is denied.

    Thanks

    1. Hi,

      ORA-27041: unable to open file
      OSD-04002: unable to open file
      O/S-Error: (OS 5) Access is denied.

      You can not access the file. Where is your backup of the controlfile? \\exchum01\oracle\JULY_23_12\RMAN_frm_PROD\DBF_T20120723_S343696_P1.DBF’ this is shared link. Copy the backup of controlfile to the local disk.

  2. Adam Gorge

    My database is also get corrupted due to power failure. But I don’t have database backup to restore my database. Do you have any suggestion to repair Oracle database?

  3. Simply desire to say your article is as amazing. The clarity in your
    post is simply nice and i could assume you’re an expert on this subject. Well with your permission let me to grab your feed to keep updated with forthcoming post. Thanks a million and please keep up the enjoyable work.

  4. rahul Dixit

    Hi,
    Have followed your article to the last word but as soon as I say ‘alter database open resetlogs upgrade’ on the new server running 11gR2 binaries, it says:
    ERROR at line 1:
    ORA-04023: Object SYS.STANDARD could not be validated or authorized

    Have tried this at least 3 times now and the same problem occurs. Did you face this at somepoint and what could be the solution.

    Am upgrading from 10gR2 to 11gR2 to new server.

    Thanks in advance,
    Regards,
    RD.

    1. Hi Rahul,
      I didn’t face it. But there is a document in metalink related to your problem. (During Upgrade Of Database : ORA-04023: Object SYS.STANDARD Could Not Be Validated Or Authorized [ID 1343500.1])

      Symptoms:
      When attempting to STARTUP UPGRADE, the following error occurs:

      ERROR
      ———————–
      ORA-04023: Object SYS.STANDARD could not be validated or authorized

      The above error is seen as soon as STARTUP UPGRADE is executed and SYS connections are established through other terminals.

      Cause:
      SQLPATH defined in the environment when it should not be.

      Once the SQLPATH was removed the upgrade completed successfully.

      Solution:
      1. Remove/unset the SQLPATH environmental variable.
      2. Proceed with the upgrade of the database.

      Hope this helps you

  5. Jeff

    Hi, i was upgraing from 11.1 to 11.2. This article assisted very much as i just substituted 10g for 11.1 and 11g for 11.2.
    Thanks very much for the article.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s