7 Replies Latest reply: Aug 23, 2013 3:54 AM by Hemant K Chitale RSS

    Fully Automated RMAN TSPITR fails

    flighting_sky

      condition:

      oracle 11.2&&windows7(32bit)

       

             I performed Fully Automated RMAN TSPITR yesterday.When the oracle performed the export of the metadata of the recovery set,the following problem appeared.


      Performing export of metadata...

         EXPDP> Starting "SYS"."TSPITR_EXP_dbtF":

       

       

      Removing automatic instance

      shutting down automatic instance

      RMAN-00571: ===========================================================

      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

      RMAN-00571: ===========================================================

      RMAN-03002: failure of recover command at 08/23/2013 01:01:05

      RMAN-06136: ORACLE error from auxiliary database: ORA-01097: cannot shutdown while in a transaction - commit or rollback first

      RMAN-06962: Error received during export of metadata

      RMAN-06960:    EXPDP> ORA-39123: Data Pump transportable tablespace job aborted

      ORA-39187: The transportable set is not self-contained, violation list is

      ORA-39906: Constraint FK_PK between table SH.PK in tablespace TEST01 and table SH.FK in tablespace TEST02.

       

               The message of the problem tells me that the transprotable set is not self-contained.But I remember that I have dropped the constraint FK_PK.And then I run the following code.And the result tells me that the constraint  surely  be dropped.

             So ,why does the problem occur?

              Thanks!


      SQL> select count(*) from dba_constraints where constraint_name ='FK_PK';

       

       

        COUNT(*)

      ----------

               0

       

       

      SQL>    Begin

        2         dbms_tts.transport_set_check('TEST02,TEST01',true,true);

        3     End;

        4  /

       

       

      PL/SQL procedure successfully completed.

       

       

      SQL> select * from transport_set_violations;

       

       

      no rows selected

        • 1. Re: Fully Automated RMAN TSPITR fails
          jgarry

          Your problem is obvious (ouch, sorry, old joke):

           

          $ oerr ora 1097

          01097, 00000, "cannot shutdown while in a transaction - commit or rollback first"

          // *Cause:  Obvious

          // *Action:

           

           

          Show us your rman connection command, I suspect you connected in such a way that you got the "Executing TSPITR with Your Own Auxiliary Instance" with transactions ongoing, so the metadata you were transporting was old.  Or maybe it is not checking the recycle bin for the associated dropped index?  Try purging.

          • 2. Re: Fully Automated RMAN TSPITR fails
            flighting_sky

            I just drop the constriant FK_PK.There are no index dropped of the tablespace test02 in the dba_recyclebin.


            SQL> select object_name,original_name,type,ts_name from dba_recyclebin;

             

             

            OBJECT_NAME                    ORIGINAL_N TYPE       TS_NAME

            ------------------------------ ---------- ---------- ----------

            BIN$c96+yjANSE6ThcTovMwoPw==$0 TAB        TABLE      USERS

            BIN$MkU0+TSYSJK1qnCAKglJFg==$0 TABLE1     TABLE      USERS

            BIN$YCQ2IgKaQDWw6Cxrl6QRaA==$0 TAB        TABLE      TEST01

             

             

            SQL> show user;

            USER is "SH"

            SQL> select name from v$database;

             

             

            NAME

            ---------

            WAREHOUS

             

            And the following code (starting the RMAN---occur the problem):

             

            SQL> $rman target sys/123456@warehouse catalog rman/123456@catalog;

             

             

            Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 23 10:29:47 2013

             

             

            Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

             

             

            connected to target database: WAREHOUS (DBID=4011143137)

            connected to recovery catalog database

             

             

            RMAN> recover tablespace test02 until scn 1346235 auxiliary destination 'G:\Oracle\TS';

             

             

            Starting recover at 23-AUG-13

            starting full resync of recovery catalog

            full resync complete

            allocated channel: ORA_DISK_1

            channel ORA_DISK_1: SID=22 device type=DISK

             

             

            Creating automatic instance, with SID='atmp'

             

             

            initialization parameters used for automatic instance:

            db_name=WAREHOUS

            db_unique_name=atmp_tspitr_WAREHOUS

            compatible=11.2.0.0.0

            db_block_size=8192

            db_files=200

            sga_target=280M

            processes=50

            db_create_file_dest=G:\Oracle\TS

            log_archive_dest_1='location=G:\Oracle\TS'

            #No auxiliary parameter file used

             

             

             

             

            starting up automatic instance WAREHOUS

             

             

            Oracle instance started

             

             

            Total System Global Area     292933632 bytes

             

             

            Fixed Size                     1374164 bytes

            Variable Size                100665388 bytes

            Database Buffers             184549376 bytes

            Redo Buffers                   6344704 bytes

            Automatic instance created

            Running TRANSPORT_SET_CHECK on recovery set tablespaces

            TRANSPORT_SET_CHECK completed successfully

             

             

            contents of Memory Script:

            {

            # set requested point in time

            set until  scn 1346235;

            # restore the controlfile

            restore clone controlfile;

            # mount the controlfile

            sql clone 'alter database mount clone database';

            # archive current online log

            sql 'alter system archive log current';

            # avoid unnecessary autobackups for structural changes during TSPITR

            sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';

            # resync catalog

            resync catalog;

            }

            executing Memory Script

             

             

            executing command: SET until clause

             

             

            Starting restore at 23-AUG-13

            allocated channel: ORA_AUX_DISK_1

            channel ORA_AUX_DISK_1: SID=59 device type=DISK

             

             

            channel ORA_AUX_DISK_1: starting datafile backup set restore

            channel ORA_AUX_DISK_1: restoring control file

            channel ORA_AUX_DISK_1: reading from backup piece G:\ORACLE\BACKUP\WAREHOUSE\LEVEL1_CUMULATIVE_0NOHKRT6_1_1

            channel ORA_AUX_DISK_1: piece handle=G:\ORACLE\BACKUP\WAREHOUSE\LEVEL1_CUMULATIVE_0NOHKRT6_1_1 tag=LEVEL1_CUMULATIVE

            channel ORA_AUX_DISK_1: restored backup piece 1

            channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

            output file name=G:\ORACLE\TS\WAREHOUSE\CONTROLFILE\O1_MF_91FLFZYS_.CTL

            Finished restore at 23-AUG-13

             

             

            sql statement: alter database mount clone database

             

             

            sql statement: alter system archive log current

             

             

            sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

             

             

            starting full resync of recovery catalog

            full resync complete

             

             

            contents of Memory Script:

            {

            # set requested point in time

            set until  scn 1346235;

            # set destinations for recovery set and auxiliary set datafiles

            set newname for clone datafile  1 to new;

            set newname for clone datafile  3 to new;

            set newname for clone datafile  2 to new;

            set newname for clone tempfile  1 to new;

            set newname for datafile  7 to

            "D:\APP\ASUS\ORADATA\WAREHOUSE\TEST02.DBF";

            # switch all tempfiles

            switch clone tempfile all;

            # restore the tablespaces in the recovery set and the auxiliary set

            restore clone datafile  1, 3, 2, 7;

            switch clone datafile all;

            }

            executing Memory Script

             

             

            executing command: SET until clause

             

             

            executing command: SET NEWNAME

             

             

            executing command: SET NEWNAME

             

             

            executing command: SET NEWNAME

             

             

            executing command: SET NEWNAME

             

             

            executing command: SET NEWNAME

             

             

            renamed tempfile 1 to G:\ORACLE\TS\WAREHOUSE\DATAFILE\O1_MF_TEMP_%U_.TMP in control file

             

             

            Starting restore at 23-AUG-13

            using channel ORA_AUX_DISK_1

             

             

            channel ORA_AUX_DISK_1: starting datafile backup set restore

            channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

            channel ORA_AUX_DISK_1: restoring datafile 00001 to G:\ORACLE\TS\WAREHOUSE\DATAFILE\O1_MF_SYSTEM_%U_.DBF

            channel ORA_AUX_DISK_1: restoring datafile 00003 to G:\ORACLE\TS\WAREHOUSE\DATAFILE\O1_MF_UNDOTBS1_%U_.DBF

            channel ORA_AUX_DISK_1: restoring datafile 00002 to G:\ORACLE\TS\WAREHOUSE\DATAFILE\O1_MF_SYSAUX_%U_.DBF

            channel ORA_AUX_DISK_1: restoring datafile 00007 to D:\APP\ASUS\ORADATA\WAREHOUSE\TEST02.DBF

            channel ORA_AUX_DISK_1: reading from backup piece G:\ORACLE\BACKUP\WAREHOUSE\LEVEL0_0IOHKQV0_1_1

            channel ORA_AUX_DISK_1: piece handle=G:\ORACLE\BACKUP\WAREHOUSE\LEVEL0_0IOHKQV0_1_1 tag=LEVEL0

            channel ORA_AUX_DISK_1: restored backup piece 1

            channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55

            Finished restore at 23-AUG-13

             

             

            datafile 1 switched to datafile copy

            input datafile copy RECID=5 STAMP=824207618 file name=G:\ORACLE\TS\WAREHOUSE\DATAFILE\O1_MF_SYSTEM_91FLGBWL_.DBF

            datafile 3 switched to datafile copy

            input datafile copy RECID=6 STAMP=824207618 file name=G:\ORACLE\TS\WAREHOUSE\DATAFILE\O1_MF_UNDOTBS1_91FLGBYW_.DBF

            datafile 2 switched to datafile copy

            input datafile copy RECID=7 STAMP=824207618 file name=G:\ORACLE\TS\WAREHOUSE\DATAFILE\O1_MF_SYSAUX_91FLGBXY_.DBF

             

             

            contents of Memory Script:

            {

            # set requested point in time

            set until  scn 1346235;

            # online the datafiles restored or switched

            sql clone "alter database datafile  1 online";

            sql clone "alter database datafile  3 online";

            sql clone "alter database datafile  2 online";

            sql clone "alter database datafile  7 online";

            # recover and open resetlogs

            recover clone database tablespace  "TEST02", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

            alter clone database open resetlogs;

            }

            executing Memory Script

             

             

            executing command: SET until clause

             

             

            sql statement: alter database datafile  1 online

             

             

            sql statement: alter database datafile  3 online

             

             

            sql statement: alter database datafile  2 online

             

             

            sql statement: alter database datafile  7 online

             

             

            Starting recover at 23-AUG-13

            using channel ORA_AUX_DISK_1

            channel ORA_AUX_DISK_1: starting incremental datafile backup set restore

            channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

            destination for restore of datafile 00007: D:\APP\ASUS\ORADATA\WAREHOUSE\TEST02.DBF

            destination for restore of datafile 00001: G:\ORACLE\TS\WAREHOUSE\DATAFILE\O1_MF_SYSTEM_91FLGBWL_.DBF

            destination for restore of datafile 00003: G:\ORACLE\TS\WAREHOUSE\DATAFILE\O1_MF_UNDOTBS1_91FLGBYW_.DBF

            destination for restore of datafile 00002: G:\ORACLE\TS\WAREHOUSE\DATAFILE\O1_MF_SYSAUX_91FLGBXY_.DBF

            channel ORA_AUX_DISK_1: reading from backup piece G:\ORACLE\BACKUP\WAREHOUSE\LEVEL1_CUMULATIVE_0MOHKRRO_1_1

            channel ORA_AUX_DISK_1: piece handle=G:\ORACLE\BACKUP\WAREHOUSE\LEVEL1_CUMULATIVE_0MOHKRRO_1_1 tag=LEVEL1_CUMULATIVE

            channel ORA_AUX_DISK_1: restored backup piece 1

            channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07

             

             

            starting media recovery

             

             

            archived log for thread 1 with sequence 14 is already on disk as file D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_18\O1_MF_1_14_911NKBNY_.ARC

            archived log for thread 1 with sequence 15 is already on disk as file D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_19\O1_MF_1_15_912Y0W3B_.ARC

            archived log for thread 1 with sequence 16 is already on disk as file D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_19\O1_MF_1_16_91475BY4_.ARC

            archived log for thread 1 with sequence 17 is already on disk as file D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_19\O1_MF_1_17_9149CJMS_.ARC

            archived log for thread 1 with sequence 18 is already on disk as file D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_20\O1_MF_1_18_915MLYR1_.ARC

            archived log for thread 1 with sequence 19 is already on disk as file D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_20\O1_MF_1_19_915OR34Z_.ARC

            archived log for thread 1 with sequence 20 is already on disk as file D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_20\O1_MF_1_20_916XQMRD_.ARC

            archived log for thread 1 with sequence 21 is already on disk as file D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_20\O1_MF_1_21_91715KYK_.ARC

            archived log for thread 1 with sequence 22 is already on disk as file D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_21\O1_MF_1_22_91884645_.ARC

            archived log for thread 1 with sequence 23 is already on disk as file D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_21\O1_MF_1_23_919JV790_.ARC

            archived log for thread 1 with sequence 24 is already on disk as file D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_22\O1_MF_1_24_91D6GRC1_.ARC

            archived log for thread 1 with sequence 25 is already on disk as file D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_22\O1_MF_1_25_91D7OOOR_.ARC

            archived log file name=D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_18\O1_MF_1_14_911NKBNY_.ARC thread=1 sequence=14

            archived log file name=D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_19\O1_MF_1_15_912Y0W3B_.ARC thread=1 sequence=15

            archived log file name=D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_19\O1_MF_1_16_91475BY4_.ARC thread=1 sequence=16

            archived log file name=D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_19\O1_MF_1_16_91475BY4_.ARC thread=1 sequence=16

            archived log file name=D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_19\O1_MF_1_17_9149CJMS_.ARC thread=1 sequence=17

            archived log file name=D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_20\O1_MF_1_18_915MLYR1_.ARC thread=1 sequence=18

            archived log file name=D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_20\O1_MF_1_19_915OR34Z_.ARC thread=1 sequence=19

            archived log file name=D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_20\O1_MF_1_20_916XQMRD_.ARC thread=1 sequence=20

            archived log file name=D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_20\O1_MF_1_21_91715KYK_.ARC thread=1 sequence=21

            archived log file name=D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_21\O1_MF_1_22_91884645_.ARC thread=1 sequence=22

            archived log file name=D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_21\O1_MF_1_23_919JV790_.ARC thread=1 sequence=23

            archived log file name=D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_22\O1_MF_1_24_91D6GRC1_.ARC thread=1 sequence=24

            archived log file name=D:\APP\ASUS\FLASH_RECOVERY_AREA\WAREHOUSE\ARCHIVELOG\2013_08_22\O1_MF_1_25_91D7OOOR_.ARC thread=1 sequence=25

            media recovery complete, elapsed time: 00:00:44

            Finished recover at 23-AUG-13

             

             

            database opened

             

             

            contents of Memory Script:

            {

            # make read only the tablespace that will be exported

            sql clone 'alter tablespace  TEST02 read only';

            # create directory for datapump import

            sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

            G:\Oracle\TS''";

            # create directory for datapump export

            sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

            G:\Oracle\TS''";

            }

            executing Memory Script

             

             

            sql statement: alter tablespace  TEST02 read only

             

             

            sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''G:\Oracle\TS''

             

             

            sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''G:\Oracle\TS''

             

             

            Performing export of metadata...

               EXPDP> Starting "SYS"."TSPITR_EXP_atmp":

             

             

            Removing automatic instance

            shutting down automatic instance

            RMAN-00571: ===========================================================

            RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

            RMAN-00571: ===========================================================

            RMAN-03002: failure of recover command at 08/23/2013 10:36:08

            RMAN-06136: ORACLE error from auxiliary database: ORA-01097: cannot shutdown while in a transaction - commit or rollback first

            RMAN-06962: Error received during export of metadata

            RMAN-06960:    EXPDP> ORA-39123: Data Pump transportable tablespace job aborted

            ORA-39187: The transportable set is not self-contained, violation list is

            ORA-39906: Constraint FK_PK between table SH.PK in tablespace TEST01 and table SH.FK in tablespace TEST02.

             

             

            RMAN>

            • 3. Re: Fully Automated RMAN TSPITR fails
              Hemant K Chitale

              >recover tablespace test02 until scn 1346235

               

              Is the UNTIL SCN point a time before or after dropping the constraint.  It looks like Oracle is restoring an image of the database before the constraint was dropped.

               

              Hemant K Chitale


              • 4. Re: Fully Automated RMAN TSPITR fails
                flighting_sky

                In fact ,I want the Oracle to restore an image of the database before one table of tablespace test02 was truncated.Before I run the TSPITR,I drop the constraint FK_PK,because it make the tablespace test02 to be not self-contained.

                • 5. Re: Fully Automated RMAN TSPITR fails
                  Hemant K Chitale

                  >until scn 1346235

                  IS this SCN one that was before the DROP CONSTRAINT ?   If so, Oracle restores the database  that still has the constraint to the auxiliary instance.

                   

                  Hemant K Chitale

                   


                  • 6. Re: Fully Automated RMAN TSPITR fails
                    flighting_sky

                    yes,the scn 1346235 is before the DROP CONSTRAINT! What should I do to deal with this problem?

                    Maybe  I should recover the tablespace TEST02 and TEST01,that have relationship?If so ,I should export and import many objects ,which be created after the scn 1346235.There are other ways to solve this problem?Thanks!

                    • 7. Re: Fully Automated RMAN TSPITR fails
                      Hemant K Chitale

                      If you really need the tablespace as of that SCN, you shouldn't do a Fully Automated TSPITR.

                       

                      Do a manual RESTORE+RECOVER (with both tablespaces and SYSTEM, SYSAUX, UNDO) to an auxilary instance.  Then manually copy out the table(s) that you want from that auxiliary instance.

                       

                      Hemant K Chitale