14 Replies Latest reply: Mar 10, 2010 5:06 AM by 758486 RSS

    recovering a 9i database into 11g from a destroyed database

    758486
      Our 9i database was recently lost due to the computer being physically damaged. I have a recent full database backup made via RMAN, but I have never tried to restore/ recover a database from this type of backup. In recreating the old setup we have reinstalled Windows XP and upgraded to 11g and am hoping to recover the database into this setup.

      I have two files that RMAN created, a snapshot control file (with the name sncf[SID].ora) and a much larger file with no prefix. We have also recovered the hard drive that Oracle resided upon, but I'm assuming the RMAN backup may be the easier method to recover the database. The documents I've read don't seem to cover the situation of recovery using a backup made on a different database. Is there a way to tell RMAN to use these specific files for its recovery attempts, or should I move them to the new database folder and use a simple RESTORE (or RECOVER?) command.

      Thanks for your help,
      brian
        • 1. Re: recovering a 9i database into 11g from a destroyed database
          Kamran Agayev A.
          user7891730 wrote:
          Our 9i database was recently lost due to the computer being physically damaged. I have a recent full database backup made via RMAN, but I have never tried to restore/ recover a database from this type of backup. In recreating the old setup we have reinstalled Windows XP and upgraded to 11g and am hoping to recover the database into this setup.

          I have two files that RMAN created, a snapshot control file (with the name sncf[SID].ora) and a much larger file with no prefix. We have also recovered the hard drive that Oracle resided upon, but I'm assuming the RMAN backup may be the easier method to recover the database. The documents I've read don't seem to cover the situation of recovery using a backup made on a different database. Is there a way to tell RMAN to use these specific files for its recovery attempts, or should I move them to the new database folder and use a simple RESTORE (or RECOVER?) command.

          Thanks for your help,
          brian
          Hi Brian and welcome to the forum!

          Although I haven't tested to restore 9i backup to 11g, you can do the following:

          - Install 9i
          - Restore/Recover the database to that server and open the database
          - Upgrade the database to the 11g

          Edited by: Kamran Agayev A. on Mar 8, 2010 12:50 PM
          • 2. Re: recovering a 9i database into 11g from a destroyed database
            758486
            Thanks, I can install 9i and then upgrade to 11g if needed. However this RMAN compatibility matrix had led me to hope that this step could be eliminated:
            http://download.oracle.com/docs/cd/B28359_01/backup.111/b28273/compat003.htm

            Assuming the backed up database and the target base are the same (or compatible), do you know the RMAN syntax to RESTORE/RECOVER in this case, where the database that RMAN backed up no long exists (except as an RMAN generated backup file)? The documentation I've looked at seems to assume that one is restoring a previous version in the existing database. In this case I am starting from a fresh install of the database.

            -Brian
            • 3. Re: recovering a 9i database into 11g from a destroyed database
              Kamran Agayev A.
              user7891730 wrote:
              Thanks, I can install 9i and then upgrade to 11g if needed. However this RMAN compatibility matrix had led me to hope that this step could be eliminated:
              http://download.oracle.com/docs/cd/B28359_01/backup.111/b28273/compat003.htm

              Assuming the backed up database and the target base are the same (or compatible), do you know the RMAN syntax to RESTORE/RECOVER in this case, where the database that RMAN backed up no long exists (except as an RMAN generated backup file)? The documentation I've looked at seems to assume that one is restoring a previous version in the existing database. In this case I am starting from a fresh install of the database.

              -Brian
              Dear Brian

              Yes, it's written in the documentation of 11g (I missed that) that:

              Any release of Oracle database can restore backup sets and copies created by any prior release back to Oracle8i.

              Have you tested it? What error you get?
              • 4. Re: recovering a 9i database into 11g from a destroyed database
                758486
                Great, thanks for confirming that I likely can restore into 11g (at least give it a try). However I haven't tried it yet, as I'm unsure of the syntax. The examples in the documentation I've seen assumes that the restoration is being performed in the same database that was backed up, thus a simple 'RESTORE DATABASE' suffices.

                In this case I have (what I presume are) the RMAN backup files (a snapshot control file (sncfSID.ora) and a large file with no suffix) in a folder distinct from the new install of the database.

                I'm unsure whether to just point the RESTORE command via:
                RESTORE DATABASE FROM D:/folder/folder/backupfile
                or RESTORE DATABASE FROM D:/folder/folder/sncfSID.ora
                and then similar RECOVER DATABASE FROM commands.

                or whether I need to restore the control file first:
                RESTORE CONTROLFILE FROM D:/folder/folder/sncfSID.ora;
                ALTER DATABASE MOUNT;
                RESTORE DATABASE;
                RECOVER DATABASE;

                Alternatively I can move the RMAN backup files to the new oracle/database folder, and use the default RMAN syntax. However I'm not sure if the two backup files I've identified constitute all the files that RMAN created.
                • 5. Re: recovering a 9i database into 11g from a destroyed database
                  501783
                  Hi Brian,

                  Since the database would need to be upgraded to 11g R1/R2 after recovery, please check the source database version. For upgrade target version of 11gR2, the source version should be 9208 or above and for 11gR1 it has to be 9204 or above.

                  The database (9i) can be restored and recovered under new home(11g) as below.

                  RMAN> RESTORE CONTROLFILE FROM <location of controlfile backup>;
                  RMAN> ALTER DATABASE MOUNT;
                  RMAN> CATALOG BACKUPPIECE '<backuppiece location>'; -- if the location of backup piece is changed
                  RMAN> RESTORE DATABASE;
                  RMAN> RECOVER DATABASE;

                  SQL> alter database open resetlogs upgrade;

                  SQL> create table registry$database ..; ( the definition of the table is as below) -- please test this

                  SQL> <execute the upgrade script>; The upgrade script will fail if the table registry$database ( created by Pre-Upgrade Utility i.e. script utlu112i.sql in the source database
                  version) doesn’t exist.

                  Pre-Upgrade Utility i.e. script utlu112i.sql (11g R2) is available in $ORACLE_HOME/rdbms/admin. It is also available also at MetaLink.
                  ....
                  
                  BEGIN
                        EXECUTE IMMEDIATE 
                           'UPDATE registry$database set tz_version = :1'
                        USING db_tz_version;
                     EXCEPTION WHEN OTHERS THEN 
                        IF sqlcode = -904 THEN  -- registry$database exists but no tz_version
                           EXECUTE IMMEDIATE
                              'ALTER TABLE registry$database ADD (tz_version NUMBER)';
                           EXECUTE IMMEDIATE
                              'UPDATE registry$database set tz_version = :1'
                           USING db_tz_version;
                        END IF;
                        IF sqlcode = -942 THEN -- no registry$database table so create it
                           EXECUTE IMMEDIATE 
                             'CREATE TABLE registry$database( 
                               platform_id   NUMBER,       
                               platform_name VARCHAR2(101),
                               edition       VARCHAR2(30), 
                               tz_version    NUMBER        
                               )';
                  
                  ...
                  Reference:
                  MetaLink Note : 884522.1 - How to Download and Run Oracle's Database Pre-Upgrade Utility
                  MetaLink Note : 369644.1 - Answers To FAQ For Restoring Or Duplicating Between Different Versions And Platforms


                  Regards,
                  Vaibhav
                  • 6. Re: recovering a 9i database into 11g from a destroyed database
                    758486
                    Thanks for the detailed response. Unfortunately I don't know the version number of the old database; it's gone. All I can recall is that it was 9i.

                    I performed the following commands

                    shutdown immediate
                    startup nomount
                    RESTORE CONTROLFILE FROM 'd:\....\SNCFsid.ora'; (this is the snapshot control file created by RMAN, I'm assuming it's the control file used to restore the database?)
                    this command created three CONTROL0#.CTL files (CONTROL01.CTL, CONTROL02.CTL, CONTROL03.CTL)and successfully completed
                    ALTER DATABASE MOUNT;
                    this step failed, giving: ORA-00218 block size 4096 of control file 'd:\oracle11g\oradata\sid\CONTROL03.CTL' does not match DB_BLOCK_SIZE (string)

                    I backed up the original CONTROL0#.CTL files, so presumably I can just replace them. Any ideas what I am doing wrong? I guess the snapshot control file is not an appropriate control file to use. However skipping this step and trying:
                    CATALOG BACKUPPIECE 'd:\....\0O55P3P_1_1'; (the backup created by RMAN of the previous database)
                    gives ORA-19691 'd:\....\0O55P3P_1_1' is from different database

                    Thanks again.
                    • 7. Re: recovering a 9i database into 11g from a destroyed database
                      tychos
                      Hi,

                      Can you check the value of db_block_size in your initsid.ora or spfilesid.ora?
                      It should be set to 4096.

                      Regards,
                      Tycho
                      • 8. Re: recovering a 9i database into 11g from a destroyed database
                        758486
                        The old 9i database was backed up with RMAN via a 'backup database' command, and created a sncf[sdi].ora file and a large backup file. The sncf (snapshot control file) does not seem to have a db_block_size (though it's difficult to read in notepad). I created a new database with the same SID in Oracle 11g, and the db_block_size of the new database's spfilesid.ora is 8192.

                        We recovered the hard drive that the old database was stored. I have been using just the RMAN backup files, but possibly I should use the spfile[sid].ora we recovered from the old database? It's db_block_size is 4096. Possibly this control file is what I should use and ignore the one RMAN generated?

                        Thanks,
                        brian
                        • 9. Re: recovering a 9i database into 11g from a destroyed database
                          758486
                          addendum: I also have tried :

                          RESTORE CONTROLFILE FROM D:\old oracle folders\ora90\database\0OL55P3P_1_1'; (the large backup file that RMAN created)

                          this command successfully (apparently) creates three CONTROL0#.CTL files in the new oracle11g oradata\SID folder, but

                          ALTER DATABASE MOUNT

                          still causes the error ORA-00218 block size 4096 of control file 'd:\oracle11g\oradata\SID\CONTROL03.CTLr' does not match DB_BLOCK_SIZE (0)
                          • 10. Re: recovering a 9i database into 11g from a destroyed database
                            501783
                            Hi Brian,

                            Use the recovered spfile<sid>.ora and DB_BLOCK_SIZE set to 4096. Once the database is mounted, check the output of following query to confirm the database version.
                            SQL> select * from v$version;
                            Regards,
                            Vaibhav

                            Edited by: Vaibhav on Mar 9, 2010 7:17 AM
                            • 11. Re: recovering a 9i database into 11g from a destroyed database
                              758486
                              Pardon my ignorance: can you describe how to use the recovered spfile<sid>.ora? RESTORE CONTROLFILE FROM 'D:\old oracle folders\ora90\database\SPFILEsid.ORA'; probably won't work [just tried it, RMAN declared that 'no autobackup found or specified handle is not a valid copy']

                              Also
                              RESTORE SPFILE FROM 'D:\old oracle folders\ora90\database\SPFILEsid.ORA';
                              or
                              RESTORE SPFILE FROM 'D:\old oracle folders\ora90\database\0OL55P3P_1_1';

                              gives the error 'must use the TO clause when instance is started with spfile'. However the only examples I can find using the TO statement are like:
                              RESTORE spfile to '.../spfilesid.ora' from autobackup;
                              and I don't have an autobackup, just the manual backup.

                              Thanks!
                              • 12. Re: recovering a 9i database into 11g from a destroyed database
                                501783
                                Hi Brian,
                                 We recovered the hard drive that the old database was stored. I have been using just the RMAN backup files, but possibly I should use the spfile[sid].ora we recovered from the old database
                                 
                                So I guess that the initialization parameter file (spfile) was recovered. Use this file to start the instance (nomount) and confirm the block size

                                SQL> show parameter db_block_size

                                If the spfile is not available/recovered, set the value of db_block_size to 4096 in the existing initialization parameter file , start the instance and try to mount the database.

                                Regards,
                                Vaibhav
                                • 13. Re: recovering a 9i database into 11g from a destroyed database
                                  758486
                                  Yes, it appears the entire Oracle folder was recovered, so all the files are intact. But the directory structure of the new database is different, so the old spfile would have to be edited. As it contains binary characters this seems tricky, especially in Windows. I can edit the old pfile to match the new directory of the new database, then
                                  STARTUP pfile=/path to old pfile...
                                  check that parameters seem OK, then
                                  CREATE spfile from pfile

                                  Or given that we have recovered the drive, would it be better to reinstall 9i (and upgrade to 11g later), create a database with the same SID and directory structure as the old database, and then just swap out all the new folders for the old ones?
                                  • 14. Re: recovering a 9i database into 11g from a destroyed database
                                    758486
                                    Thanks for everyone's help. I'm marking this as answered, though it's more of a 'moving on'.

                                    Edited by: user7891730 on Mar 10, 2010 3:06 AM