1 2 Previous Next 23 Replies Latest reply on Aug 10, 2012 10:46 AM by mseberg Go to original post
      • 15. Re: Error restoring backup from other database.
        mseberg
        No inconvenience - Interesting problem.

        Besides you are a nice guy!

        Status - 2nd server built, RMAN NOCATALOG backup taken, document started. ETA - A few more hours. ( Small delay, first backup had issue )

        Updated status - creating restore script - Almost ready to test.


        Best Regards

        mseberg

        Edited by: mseberg on Aug 8, 2012 6:52 AM

        Edited by: mseberg on Aug 8, 2012 8:08 AM

        Edited by: mseberg on Aug 8, 2012 9:16 AM

        Edited by: mseberg on Aug 8, 2012 10:13 AM
        • 17. Re: Error restoring backup from other database.
          mseberg
          Hello; ( Summary below )

          I know its getting late there. Have 11 step document but I'm getting :
          RMAN-03002: failure of alter db command at 08/08/2012 11:45:38
          ORA-01103: database name 'RCATALOG' in control file is not 'RECLONE'
          Have tried Duplicate and (restore/recover). Neither is playing fair.

          Morning killer, but interest is still high.

          Will continue to work on.

          The closest I have come is not changing the ORACLE_SID and keeping the file structure the same including the same as the old system. Used Pfile, password file etc.

          $ORACLE_HOME/bin/rman
          
          CONNECT TARGET / 
          STARTUP NOMOUNT;
          SET DBID 3754763357;  #DBID of PROD database
          RUN
          {
            ALLOCATE CHANNEL c1 DEVICE TYPE disk format '/u03/oradata/RCATALOG_rman';
            RESTORE CONTROLFILE FROM '/u03/oradata/RCATALOG_rman/sb_t790762003_s85_p1'; 
            ALTER DATABASE MOUNT;
            RESTORE DATABASE;
            RECOVER DATABASE;
            release channel c1;
          }
          ALTER DATABASE OPEN RESETLOGS; 
          Barks here : ( thinking a switch log in the backup would help )
          RMAN-00571: ===========================================================
          RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
          RMAN-00571: ===========================================================
          RMAN-03002: failure of recover command at 08/08/2012 13:44:36
          RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 111 and starting SCN of 1934774
          
          RMAN> ALTER DATABASE OPEN RESETLOGS;
          
          database opened
          If I shutdown the database and do it over with one SET NEWNAME it works again ( notice I only use this SWITCH DATAFILE ALL; )
          $ORACLE_HOME/bin/rman
          
          CONNECT TARGET / 
          STARTUP NOMOUNT;
          SET DBID 3754763357;  #DBID of PROD database
          RUN
          {
            ALLOCATE CHANNEL c1 DEVICE TYPE disk format '/u03/oradata/RCATALOG_rman';
            RESTORE CONTROLFILE FROM '/u03/oradata/RCATALOG_rman/sb_t790762003_s85_p1'; 
            ALTER DATABASE MOUNT;
            SET NEWNAME FOR DATAFILE 1 TO '/u01/app/oracle/oradata/RECLONE/system01.dbf'; 
            RESTORE DATABASE;
            SWITCH DATAFILE ALL;
            RECOVER DATABASE;
            release channel c1;
          }
          ALTER DATABASE OPEN RESETLOGS; 
          I confirmed the file /u01/app/oracle/oradata/RECLONE/system01.dbf is present.

          So I can add the rest of renames :
          SET NEWNAME FOR DATAFILE 3 TO '/u01/app/oracle/oradata/RECLONE/undotbs01.dbf'; 
          SET NEWNAME FOR DATAFILE 4 TO '/u01/app/oracle/oradata/RECLONE/users01.dbf'; 
          SET NEWNAME FOR DATAFILE 5 TO '/u02/oradata/RECLONE/rman02.dbf'; 
          SET NEWNAME FOR DATAFILE 6 TO '/u02/oradata/RECLONE/things01.dbf';
          SET NEWNAME FOR DATAFILE 7 TO '/u02/oradata/RECLONE/otn01.dbf';
          SET NEWNAME FOR DATAFILE 8 TO '/u01/oradata/RECLONE/app_idx01.dbf';
          SET NEWNAME FOR DATAFILE 9 TO '/u01/oradata/RECLONE/app_dat01.dbf';
          SET NEWNAME FOR DATAFILE 10 TO '/u01/app/oracle/oradata/RECLONE/audit_aux01.dbf';
          SET NEWNAME FOR TEMPFILE 1 TO '/u01/app/oracle/oradata/RECLONE/temp01.dbf
          h2. Summary

          So its appears from my testing duplicate is not an option. However if you leave the ORACLE_SID the same, and have a similar backup location several options are available. Keeping the DBID the same for restore probably is OK, in fact unless you need to backup test, its probably a non issue. Will run another test.


          Best Regards

          mseberg

          Edited by: mseberg on Aug 8, 2012 1:47 PM

          Edited by: mseberg on Aug 8, 2012 3:49 PM
          • 18. Re: Error restoring backup from other database.
            Shivananda Rao
            Hi,

            Please refer this http://shivanandarao.wordpress.com/2012/04/19/duplicating-primary-database-to-a-new-host-without-connecting-to-the-primary-database-in-oracle-10g11g/

            Might prove out to be helpful.
            1 person found this helpful
            • 19. Re: Error restoring backup from other database.
              mseberg
              OK;

              Not sure if this is the solution you are looking for or not. In a nutshell what I did was setup the remote to be much like the source database and did the restore using its ORACLE_SID. Besides the DBID and the control file(s) restoring to the same location it seems good. Here are my steps :

              h2. Step 1
              On the source database a table is created and some data added
              CREATE TABLE DEL_ME_LATER (
                 NAME          VARCHAR2(30),
                 ENTER_DATE     DATE  DEFAULT SYSDATE
              );
              
              
              INSERT INTO DEL_ME_LATER ( NAME) VALUES ( 'ME_101');
              COMMIT;
              
              
              SELECT * FROM DEL_ME_LATER;
              
              NAME                           ENTER_DATE                
              ------------------------------ ------------------------- 
              ME_101                         08-AUG-12 
              h2. Step 2
              Source database is backup using :
              $ORACLE_HOME/bin/rman target / nocatalog  << EOF
              
              
              run {
              allocate channel d1 type disk;
              backup format '/u03/oradata/RCATALOG_rman/df_t%t_s%s_p%p' database;
              sql 'alter system archive log current';
              backup format '/u03/oradata/RCATALOG_rman/al_t%t_s%s_p%p' archivelog all;
              backup current controlfile format '/u03/oradata/RCATALOG_rman/sb_t%t_s%s_p%p';
              release channel d1;
              }
              
              This creates these files
              
              al_t790761999_s84_p1
              df_t790761919_s79_p1
              df_t790761976_s80_p1
              df_t790761991_s81_p1
              df_t790761995_s82_p1
              h2. Step 3
              The source database ID is obtained     
              select DBID from v$DATABASE
              
              DBID                   
              ---------------------- 
              3754763357   
              h2. Step 4
              Create folder structure on remote server
              mkdir -p /u01/oradata/RECLONE/
              mkdir -p /u01/app/oracle/oradata/RECLONE/
              mkdir -p /u01/app/oracle/admin/RECLONE/pfile
              mkdir -p /u01/app/oracle/admin/RECLONE/adump
              mkdir -p /u01/app/oracle/admin/RECLONE/dpdump
              mkdir -p /u02/oradata/RECLONE/
              mkdir -p /u03/oradata/RECLONE_rman
              mkdir -p /u01/oradata/RCATALOG/
              mkdir -p /u01/app/oracle/oradata/RCATALOG/
              mkdir -p /u01/app/oracle/admin/RCATALOG/pfile
              mkdir -p /u01/app/oracle/admin/RCATALOG/adump
              mkdir -p /u01/app/oracle/admin/RCATALOG/dpdump
              mkdir -p /u02/oradata/RCATALOG/
              mkdir -p /u03/oradata/RCATALOG_rman
              
              Note: After several tests I found the simple thing was to use RMAN restore without changing the ORACLE_SID so I created dual folders on the remote system as shown above.
              h2. Step 5
              SCP the backup to the remote server
              scp /u03/oradata/RCATALOG_rman/al_t790761999_s84_p1 192.168.0.20:/u03/oradata/RCATALOG_rman
              scp /u03/oradata/RCATALOG_rman/df_t790761919_s79_p1 192.168.0.20:/u03/oradata/RCATALOG_rman
              scp /u03/oradata/RCATALOG_rman/df_t790761976_s80_p1 192.168.0.20:/u03/oradata/RCATALOG_rman
              scp /u03/oradata/RCATALOG_rman/df_t790761991_s81_p1 192.168.0.20:/u03/oradata/RCATALOG_rman
              scp /u03/oradata/RCATALOG_rman/df_t790761995_s82_p1 192.168.0.20:/u03/oradata/RCATALOG_rman
              scp /u03/oradata/RCATALOG_rman/df_t790761996_s83_p1 192.168.0.20:/u03/oradata/RCATALOG_rman
              scp /u03/oradata/RCATALOG_rman/sb_t790762003_s85_p1 192.168.0.20:/u03/oradata/RCATALOG_rman
              h2. Step 6
              Use Report Schema command to get file information
              Report of database schema for database with db_unique_name RCATALOG
              
              List of Permanent Datafiles
              ===========================
              File Size(MB) Tablespace           RB segs Datafile Name
              ---- -------- -------------------- ------- ------------------------
              1    1000     SYSTEM               ***     /u01/app/oracle/oradata/RCATALOG/system01.dbf
              2    700      SYSAUX               ***     /u01/app/oracle/oradata/RCATALOG/sysaux01.dbf
              3    195      UNDOTBS1             ***     /u01/app/oracle/oradata/RCATALOG/undotbs01.dbf
              4    5        USERS                ***     /u01/app/oracle/oradata/RCATALOG/users01.dbf
              5    200      RMANCAT              ***     /u02/oradata/RCATALOG/rman02.dbf
              6    200      THINGS               ***     /u02/oradata/RCATALOG/things01.dbf
              7    250      OTNDATA              ***     /u02/oradata/RCATALOG/otn01.dbf
              8    20       APP_INDEX            ***     /u01/oradata/RCATALOG/app_idx01.dbf
              9    450      APP_DATA             ***     /u01/oradata/RCATALOG/app_dat01.dbf
              10   100      AUDIT_AUX            ***     /u01/app/oracle/oradata/RCATALOG/audit_aux01.dbf
              
              List of Temporary Files
              =======================
              File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
              ---- -------- -------------------- ----------- --------------------
              1    67       TEMP                 32767       /u01/app/oracle/oradata/RCATALOG/temp01.dbf
              h2. Step 7
              Skipped after testing - was create INIT file
              h2. Step 8
              Move the rcatalog spfile and password file and the current archive(todays up to backup) to the new server.
              /u01/app/oracle/oradata/RCATALOG/archive/ARC0000000111_0717990814.0001
              /u01/app/oracle/oradata/RCATALOG/archive/ARC0000000112_0717990814.0001
              
              /u01/app/oracle/product/11.2.0.2/dbs/orapwRCATALOG
              /u01/app/oracle/product/11.2.0.2/dbs/spfileRCATALOG.ora
              h2. Step 9
              Listener and tnsname changes on remote server. None.
              Not used on remote for copy
              h2. Step 10
              I used a Linux script for restore

              restore_test.sh
              echo "Starting RMAN..."
              $ORACLE_HOME/bin/rman  << EOF
              
              CONNECT TARGET / 
              STARTUP NOMOUNT;
              SET DBID 3754763357;  #DBID of PROD database
              
              RUN
               {
                 ALLOCATE CHANNEL c1 DEVICE TYPE disk format '/u03/oradata/RCATALOG_rman';
                 RESTORE CONTROLFILE FROM '/u03/oradata/RCATALOG_rman/sb_t790762003_s85_p1'; 
                 ALTER DATABASE MOUNT;
                 SET NEWNAME FOR DATAFILE 1 TO '/u01/app/oracle/oradata/RECLONE/system01.dbf'; 
                 SET NEWNAME FOR DATAFILE 2 TO ' /u01/app/oracle/oradata/RCATALOG/sysaux01.dbf';
                 SET NEWNAME FOR DATAFILE 3 TO '/u01/app/oracle/oradata/RECLONE/undotbs01.dbf'; 
                 SET NEWNAME FOR DATAFILE 4 TO '/u01/app/oracle/oradata/RECLONE/users01.dbf'; 
                 SET NEWNAME FOR DATAFILE 5 TO '/u02/oradata/RECLONE/rman02.dbf'; 
                 SET NEWNAME FOR DATAFILE 6 TO '/u02/oradata/RECLONE/things01.dbf';
                 SET NEWNAME FOR DATAFILE 7 TO '/u02/oradata/RECLONE/otn01.dbf';
                 SET NEWNAME FOR DATAFILE 8 TO '/u01/oradata/RECLONE/app_idx01.dbf';
                 SET NEWNAME FOR DATAFILE 9 TO '/u01/oradata/RECLONE/app_dat01.dbf';
                 SET NEWNAME FOR DATAFILE 10 TO '/u01/app/oracle/oradata/RECLONE/audit_aux01.dbf';
                SET NEWNAME FOR TEMPFILE 1 TO '/u01/app/oracle/oradata/RECLONE/temp01.dbf';
                 RESTORE DATABASE;
                 SWITCH DATAFILE ALL;
                 RECOVER DATABASE;
                 release channel c1;
               }
              ALTER DATABASE OPEN RESETLOGS; 
              
              
              exit
              EOF
              h2. Summary
              Barks a little
              
              unable to find archived log
              archived log thread=1 sequence=113
              released channel: c1
              RMAN-00571: ===========================================================
              RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
              RMAN-00571: ===========================================================
              RMAN-03002: failure of recover command at 08/08/2012 19:04:40
              RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 113 and starting SCN of 1948130
              
              RMAN> 
              database opened
              
              But no log 113 exists.
              h2. Test
              Connected.
              SQL> SELECT * FROM DEL_ME_LATER;
              
              NAME                           ENTER_DAT
              ------------------------------ ---------
              ME_101                         08-AUG-12
              
              SQL>
              h2. Final script
              echo "Starting RMAN..."
              $ORACLE_HOME/bin/rman  << EOF
              
              CONNECT TARGET / 
              STARTUP NOMOUNT;
              SET DBID 3754763357;  #DBID of PROD database
              
              RUN
               {
                 ALLOCATE CHANNEL c1 DEVICE TYPE disk format '/u03/oradata/RCATALOG_rman';
                 RESTORE CONTROLFILE FROM '/u03/oradata/RCATALOG_rman/sb_t790762003_s85_p1'; 
                 ALTER DATABASE MOUNT;
                 SET NEWNAME FOR DATAFILE 1 TO '/u01/app/oracle/oradata/RECLONE/system01.dbf'; 
                 SET NEWNAME FOR DATAFILE 2 TO '/u01/app/oracle/oradata/RECLONE/sysaux01.dbf';
                 SET NEWNAME FOR DATAFILE 3 TO '/u01/app/oracle/oradata/RECLONE/undotbs01.dbf'; 
                 SET NEWNAME FOR DATAFILE 4 TO '/u01/app/oracle/oradata/RECLONE/users01.dbf'; 
                 SET NEWNAME FOR DATAFILE 5 TO '/u02/oradata/RECLONE/rman02.dbf'; 
                 SET NEWNAME FOR DATAFILE 6 TO '/u02/oradata/RECLONE/things01.dbf';
                 SET NEWNAME FOR DATAFILE 7 TO '/u02/oradata/RECLONE/otn01.dbf';
                 SET NEWNAME FOR DATAFILE 8 TO '/u01/oradata/RECLONE/app_idx01.dbf';
                 SET NEWNAME FOR DATAFILE 9 TO '/u01/oradata/RECLONE/app_dat01.dbf';
                 SET NEWNAME FOR DATAFILE 10 TO '/u01/app/oracle/oradata/RECLONE/audit_aux01.dbf';
                 SET NEWNAME FOR TEMPFILE '/u01/app/oracle/oradata/RCATALOG/temp01.dbf' TO '/u01/app/oracle/oradata/RECLONE/temp01.dbf';
                 SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/RCATALOG/redo01.log'' TO ''/u01/app/oracle/oradata/RECLONE/redo01.log'' ";
                 SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/RCATALOG/redo01A.log'' TO ''/u01/app/oracle/oradata/RECLONE/redo01A.log'' ";
                 SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/RCATALOG/redo02.log'' TO ''/u01/app/oracle/oradata/RECLONE/redo02.log'' ";
                 SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/RCATALOG/redo02A.log'' TO ''/u01/app/oracle/oradata/RECLONE/redo02A.log'' ";
                 SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/RCATALOG/redo03.log'' TO ''/u01/app/oracle/oradata/RECLONE/redo03.log'' ";
                 SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/RCATALOG/redo03A.log'' TO ''/u01/app/oracle/oradata/RECLONE/redo03A.log'' ";
                 
                 
                 
                 RESTORE DATABASE;
                 SWITCH DATAFILE ALL;
                 SWITCH TEMPFILE ALL;
                 RECOVER DATABASE;
                 release channel c1;
               }
              ALTER DATABASE OPEN RESETLOGS; 
              
              
              exit
              EOF
              Best Regards

              mseberg
              • 20. Re: Error restoring backup from other database.
                Me_101
                really really thanks. I will try it creating a new databases and follow your steps and advices. :)
                • 21. Re: Error restoring backup from other database.
                  mseberg
                  h2. Additional Information

                  No need to create database restore will do that.

                  Can rerun the script for refresh after shutdown database, no cleanup needed, just update backup.

                  Script will need ENV variables if run from Job or cron, or a call to an ENV file.

                  Spile and password file don't need to be refreshed often.

                  Moving "alter system archive log current" in the backup script to before any backup might help.

                  Watch for "NEWNAME FOR TEMPFILE" instead "NEWNAME FOR DATAFILE", the  Report Schema command in step 6 should help.

                  Watch out for typos and white space in the RMAN script. White space in front of a path can cause an error. Missing semi colons can trick you.

                  Best Regards

                  mseberg
                  • 22. Re: Error restoring backup from other database.
                    Me_101
                    Thanks for this advices.

                    i just create two news databases and try with them. IT WORKS !!!! :) again....thanks so much!!

                    When i come back from my vacances i have to do it at production database, it is a RAC with 2 nodes+ASM, i think I can use this script to do it, just change the path of datafile/tempfile/redo logs, db_unique_name, .... right?

                    password file is stricted necessary? i did the tests without it and works fine.
                    • 23. Re: Error restoring backup from other database.
                      mseberg
                      Great job sticking with it.
                      password file is stricted necessary?
                      probably force of habit on my part.

                      Best Regards

                      mseberg
                      1 2 Previous Next