2 Replies Latest reply: Aug 25, 2013 9:08 PM by Hemant K Chitale RSS

    TSPITR:ORA-01516: nonexistent log file, data file, or temporary file "8"

    flighting_sky

      Hello,

      oracle 11.2g&&windows7(32bit)

      When I perform  fully automated RMAN TSPITR,I meet with the problem of ORA-01516: nonexistent log file, data file, or temporary file "8". the following problem appeared:

       

      SQL> HOST RMAN TARGET SYS/123456@WAREHOUSE CATALOG RMAN/123456@CATALOG;

       

       

      Recovery Manager: Release 11.2.0.1.0 - Production on Sat Aug 24 13:56:31 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 TEST01,TEST02 UNTIL SCN 1346235 AUXILIARY DESTINATION 'G:\Oracle\TS';

       

       

      Starting recover at 24-AUG-13

      allocated channel: ORA_DISK_1

      channel ORA_DISK_1: SID=145 device type=DISK

       

       

      Creating automatic instance, with SID='hmpk'

       

       

      initialization parameters used for automatic instance:

      db_name=WAREHOUS

      db_unique_name=hmpk_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 24-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_91JLZ5JX_.CTL

      Finished restore at 24-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;

      plsql <<<-- tspitr_2

      declare

        sqlstatement       varchar2(512);

        offline_not_needed exception;

        pragma exception_init(offline_not_needed, -01539);

      begin

        sqlstatement := 'alter tablespace '||  'TEST01' ||' offline immediate';

        krmicd.writeMsg(6162, sqlstatement);

        krmicd.execSql(sqlstatement);

      exception

        when offline_not_needed then

          null;

      end; >>>;

      plsql <<<-- tspitr_2

      declare

        sqlstatement       varchar2(512);

        offline_not_needed exception;

        pragma exception_init(offline_not_needed, -01539);

      begin

        sqlstatement := 'alter tablespace '||  'TEST02' ||' offline immediate';

        krmicd.writeMsg(6162, sqlstatement);

        krmicd.execSql(sqlstatement);

      exception

        when offline_not_needed then

          null;

      end; >>>;

      # 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  6 to

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

      set newname for datafile  8 to

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

      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, 6, 8, 7;

      switch clone datafile all;

      }

      executing Memory Script

       

       

      executing command: SET until clause

       

       

      sql statement: alter tablespace TEST01 offline immediate

       

       

      sql statement: alter tablespace TEST02 offline immediate

       

       

      executing command: SET NEWNAME

       

       

      executing command: SET NEWNAME

       

       

      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 24-AUG-13

      using channel ORA_AUX_DISK_1

       

       

      the file name for datafile 8 is missing in the control file

      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 00006 to D:\APP\ASUS\ORADATA\WAREHOUSE\TEST01.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:01:05

      Finished restore at 24-AUG-13

       

       

      datafile 1 switched to datafile copy

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

      datafile 3 switched to datafile copy

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

      datafile 2 switched to datafile copy

      input datafile copy RECID=7 STAMP=824306486 file name=G:\ORACLE\TS\WAREHOUSE\DATAFILE\O1_MF_SYSAUX_91JLZNRO_.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  6 online";

      sql clone "alter database datafile  8 online";

      sql clone "alter database datafile  7 online";

      # recover and open resetlogs

      recover clone database tablespace  "TEST01", "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  6 online

       

       

      sql statement: alter database datafile  8 online

       

       

      Removing automatic instance

      shutting down automatic instance

      database dismounted

      Oracle instance shut down

      Automatic instance removed

      auxiliary instance file G:\ORACLE\TS\WAREHOUSE\DATAFILE\O1_MF_SYSAUX_91JLZNRO_.DBF deleted

      auxiliary instance file G:\ORACLE\TS\WAREHOUSE\DATAFILE\O1_MF_UNDOTBS1_91JLZNRY_.DBF deleted

      auxiliary instance file G:\ORACLE\TS\WAREHOUSE\DATAFILE\O1_MF_SYSTEM_91JLZNOC_.DBF deleted

      auxiliary instance file G:\ORACLE\TS\WAREHOUSE\CONTROLFILE\O1_MF_91JLZ5JX_.CTL deleted

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

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

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

      RMAN-03002: failure of recover command at 08/24/2013 14:01:33

      RMAN-03015: error occurred in stored script Memory Script

      RMAN-03009: failure of sql command on clone_default channel at 08/24/2013 14:01:28

      RMAN-11003: failure during parse/execution of SQL statement: alter database datafile  8 online

      ORA-01516: nonexistent log file, data file, or temporary file "8"

       

      And the datafile 8 exists.

       

      SQL> select file#,name from v$datafile;

       

       

           FILE# NAME

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

               1 D:\APP\ASUS\ORADATA\WAREHOUSE\SYSTEM01.DBF

               2 D:\APP\ASUS\ORADATA\WAREHOUSE\SYSAUX01.DBF

               3 D:\APP\ASUS\ORADATA\WAREHOUSE\UNDOTBS01.DBF

               4 D:\APP\ASUS\ORADATA\WAREHOUSE\USERS01.DBF

               5 D:\APP\ASUS\ORADATA\WAREHOUSE\EXAMPLE01.DBF

               6 D:\APP\ASUS\ORADATA\WAREHOUSE\TEST01.DBF

               7 D:\APP\ASUS\ORADATA\WAREHOUSE\TEST02.DBF

               8 D:\APP\ASUS\ORADATA\WAREHOUSE\TEST03.DBF