This discussion is archived
2 Replies Latest reply: Aug 25, 2013 7:08 PM by Hemant K Chitale RSS

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

flighting_sky Newbie
Currently Being Moderated

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points