2 Replies Latest reply on Oct 17, 2013 11:06 AM by Anar Godjaev

    Unable to restore TABLESPACE using RMAN backups

    User356375

      Hi,

       

      I am not able to restore tablespace using RMAN (TSPITR).

       

      I have full backup of database,

       

      While try to restore it's failed.

       

      RMAN> recover tablespace TEST1 until logseq 706 auxiliary destination '/tmp';

       

       

      Starting recover at 16-OCT-13

      using channel ORA_DISK_1

      RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

       

       

      List of tablespaces expected to have UNDO segments

      Tablespace SYSTEM

      Tablespace UNDOTBS2

       

       

      Creating automatic instance, with SID='CsFz'

       

       

      initialization parameters used for automatic instance:

      db_name=TRAINEE

      db_unique_name=CsFz_tspitr_TRAINEE

      compatible=11.2.0.0.0

      db_block_size=8192

      db_files=200

      sga_target=280M

      processes=50

      db_create_file_dest=/tmp

      log_archive_dest_1='location=/tmp'

      #No auxiliary parameter file used

       

       

       

       

      starting up automatic instance TRAINEE

       

       

      Oracle instance started

       

       

      Total System Global Area     292933632 bytes

       

       

      Fixed Size                     1336092 bytes

      Variable Size                100666596 bytes

      Database Buffers             184549376 bytes

      Redo Buffers                   6381568 bytes

      Automatic instance created

       

       

       

       

      List of tablespaces that have been dropped from the target database:

      Tablespace TEST1

       

       

      contents of Memory Script:

      {

      # set requested point in time

      set until  logseq 706 thread 1;

      # 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;';

      }

      executing Memory Script

       

       

      executing command: SET until clause

       

       

      Starting restore at 16-OCT-13

      allocated channel: ORA_AUX_DISK_1

      channel ORA_AUX_DISK_1: SID=81 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 /oracle/product/11.2.0/dbhome_1/dbs/c-332232391-20131016-09

      channel ORA_AUX_DISK_1: piece handle=/oracle/product/11.2.0/dbhome_1/dbs/c-332232391-20131016-09 tag=TAG20131016T144951

      channel ORA_AUX_DISK_1: restored backup piece 1

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

      output file name=/tmp/TRAINEE/controlfile/o1_mf_95wbkpvj_.ctl

      Finished restore at 16-OCT-13

       

       

      sql statement: alter database mount clone database

       

       

      sql statement: alter system archive log current

       

       

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

       

       

      contents of Memory Script:

      {

      # set requested point in time

      set until  logseq 706 thread 1;

      # set destinations for recovery set and auxiliary set datafiles

      set newname for clone datafile  1 to new;

      set newname for clone datafile  7 to new;

      set newname for clone datafile  2 to new;

      set newname for clone tempfile  1 to new;

      set newname for datafile  6 to

      "/oracle/oradata/TRAINEE/datafile/o1_mf_test1_95w9fln9_.dbf";

      # switch all tempfiles

      switch clone tempfile all;

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

      restore clone datafile  1, 7, 2, 6;

      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 /tmp/TRAINEE/datafile/o1_mf_temp_%u_.tmp in control file

       

       

      Starting restore at 16-OCT-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 /tmp/TRAINEE/datafile/o1_mf_system_%u_.dbf

      channel ORA_AUX_DISK_1: restoring datafile 00007 to /tmp/TRAINEE/datafile/o1_mf_undotbs2_%u_.dbf

      channel ORA_AUX_DISK_1: restoring datafile 00002 to /tmp/TRAINEE/datafile/o1_mf_sysaux_%u_.dbf

      channel ORA_AUX_DISK_1: restoring datafile 00006 to /oracle/oradata/TRAINEE/datafile/o1_mf_test1_95w9fln9_.dbf

      channel ORA_AUX_DISK_1: reading from backup piece /tmp/1iomi9rv_1_1

      channel ORA_AUX_DISK_1: piece handle=/tmp/1iomi9rv_1_1 tag=TAG20131016T144935

      channel ORA_AUX_DISK_1: restored backup piece 1

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

      Finished restore at 16-OCT-13

       

       

      datafile 1 switched to datafile copy

      input datafile copy RECID=11 STAMP=828975325 file name=/tmp/TRAINEE/datafile/o1_mf_system_95wbkybb_.dbf

      datafile 7 switched to datafile copy

      input datafile copy RECID=12 STAMP=828975325 file name=/tmp/TRAINEE/datafile/o1_mf_undotbs2_95wbkycy_.dbf

      datafile 2 switched to datafile copy

      input datafile copy RECID=13 STAMP=828975325 file name=/tmp/TRAINEE/datafile/o1_mf_sysaux_95wbkybz_.dbf

       

       

      contents of Memory Script:

      {

      # set requested point in time

      set until  logseq 706 thread 1;

      # online the datafiles restored or switched

      sql clone "alter database datafile  1 online";

      sql clone "alter database datafile  7 online";

      sql clone "alter database datafile  2 online";

      sql clone "alter database datafile  6 online";

      # recover and open resetlogs

      recover clone database tablespace  "TEST1", "SYSTEM", "UNDOTBS2", "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  7 online

       

       

      sql statement: alter database datafile  2 online

       

       

      sql statement: alter database datafile  6 online

       

       

      Starting recover at 16-OCT-13

      using channel ORA_AUX_DISK_1

       

       

      starting media recovery

       

       

      archived log for thread 1 with sequence 702 is already on disk as file /oracle/product/11.2.0/dbhome_1/dbs/arch1_702_807275402.dbf

      archived log for thread 1 with sequence 703 is already on disk as file /oracle/product/11.2.0/dbhome_1/dbs/arch1_703_807275402.dbf

      archived log for thread 1 with sequence 704 is already on disk as file /oracle/product/11.2.0/dbhome_1/dbs/arch1_704_807275402.dbf

      archived log for thread 1 with sequence 705 is already on disk as file /oracle/product/11.2.0/dbhome_1/dbs/arch1_705_807275402.dbf

      archived log file name=/oracle/product/11.2.0/dbhome_1/dbs/arch1_702_807275402.dbf thread=1 sequence=702

      archived log file name=/oracle/product/11.2.0/dbhome_1/dbs/arch1_703_807275402.dbf thread=1 sequence=703

      archived log file name=/oracle/product/11.2.0/dbhome_1/dbs/arch1_704_807275402.dbf thread=1 sequence=704

      archived log file name=/oracle/product/11.2.0/dbhome_1/dbs/arch1_705_807275402.dbf thread=1 sequence=705

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

      Finished recover at 16-OCT-13

       

       

      database opened

       

       

      contents of Memory Script:

      {

      # make read only the tablespace that will be exported

      sql clone 'alter tablespace  TEST1 read only';

      # create directory for datapump import

      sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

      /tmp''";

      # create directory for datapump export

      sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

      /tmp''";

      }

      executing Memory Script

       

       

      sql statement: alter tablespace  TEST1 read only

       

       

      sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp''

       

       

      sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp''

       

       

      Performing export of metadata...

         EXPDP> Starting "SYS"."TSPITR_EXP_CsFz":

         EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

         EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

         EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

         EXPDP> Master table "SYS"."TSPITR_EXP_CsFz" successfully loaded/unloaded

         EXPDP> ******************************************************************************

         EXPDP> Dump file set for SYS.TSPITR_EXP_CsFz is:

         EXPDP>   /tmp/tspitr_CsFz_17454.dmp

         EXPDP> ******************************************************************************

         EXPDP> Datafiles required for transportable tablespace TEST1:

         EXPDP>   /tmp/TRAINEE/datafile/o1_mf_test1_95wbkyck_.dbf

         EXPDP> Job "SYS"."TSPITR_EXP_CsFz" successfully completed at 14:56:02

      Export completed

       

       

       

       

      contents of Memory Script:

      {

      # shutdown clone before import

      shutdown clone immediate

      }

      executing Memory Script

       

       

      database closed

      database dismounted

      Oracle instance shut down

       

       

      Performing import of metadata...

         IMPDP> Master table "SYS"."TSPITR_IMP_CsFz" successfully loaded/unloaded

         IMPDP> Starting "SYS"."TSPITR_IMP_CsFz":

         IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

       

       

      Removing automatic instance

      Automatic instance removed

      auxiliary instance file /tmp/TRAINEE/datafile/o1_mf_temp_95wblk08_.tmp deleted

      auxiliary instance file /tmp/TRAINEE/onlinelog/o1_mf_3_95wblj14_.log deleted

      auxiliary instance file /tmp/TRAINEE/onlinelog/o1_mf_2_95wblhn8_.log deleted

      auxiliary instance file /tmp/TRAINEE/onlinelog/o1_mf_1_95wblh8q_.log deleted

      auxiliary instance file /tmp/TRAINEE/datafile/o1_mf_sysaux_95wbkybz_.dbf deleted

      auxiliary instance file /tmp/TRAINEE/datafile/o1_mf_undotbs2_95wbkycy_.dbf deleted

      auxiliary instance file /tmp/TRAINEE/datafile/o1_mf_system_95wbkybb_.dbf deleted

      auxiliary instance file /tmp/TRAINEE/controlfile/o1_mf_95wbkpvj_.ctl deleted

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

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

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

      RMAN-03002: failure of recover command at 10/16/2013 14:56:16

      RMAN-06963: Error received during import of metadata

      RMAN-06961:    IMPDP> ORA-39123: Data Pump transportable tablespace job aborted

      ORA-01565: error in identifying file '/oracle/oradata/TRAINEE/datafile/o1_mf_test1_95w9fln9_.dbf'

      ORA-27037: unable to obtain file status

      Linux Error: 2: No such file or directory

      Additional information: 3