Solution for ORA-01516: nonexistent log file, data file, or temporary file in Oracle

August 20, 2022
()

Solution for ORA-01516: nonexistent log file, data file, or temporary file in Oracle

In the previous blog, we saw steps to restore RMAN consistent backup using duplicate database command Click here to read more. In this blog, we are going to restore the same consistent RMAN backup using the manual method. With this method we are getting the error “ORA-01516: nonexistent log file, data file, or temporary file” while doing database resetlogs open. In this blog, we will see how we can fix this error.


Below are the high-level steps we will follow.

1. Restore controlfile
2. Catalog RMAN backups
3. Clear expired old backups from controlfile
4. Initiate manual restore command
5. Try to open DB with resetlogs option
6. Rename logfile
7. Recreate Controlfile
8. Open DB with resetlogs option


Step 1. Restore controlfile: RMAN consistent backup is already copied to target server in location “F:\rman_backup\“. Startup DB with nomount option and restore controlfile using below restore controlfile rman command.

C:\Users\jkhan>
C:\Users\jkhan>sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 15 16:00:51 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.0255E+10 bytes
Fixed Size                  2290120 bytes
Variable Size            1845497400 bytes
Database Buffers         8388608000 bytes
Redo Buffers               18817024 bytes
SQL>
SQL>exit
C:\Users\jkhan>
C:\Users\jkhan>rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Aug 15 16:35:18 2022

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

connected to target database: UCAS (not mounted)

RMAN> restore controlfile from 'F:\rman_backup\ctl_db_UCAS_S_757_P_1_T_1112789852';

Starting restore at 15-AUG-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=201 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=G:\UCAS\ORADATA_2\CONTROL01.CTL
output file name=H:\UCAS\ORADATA_2\CONTROL02.CTL
Finished restore at 15-AUG-22
RMAN>


Step 2. Catalog RMAN backups: Mount the DB. Register rman backup with controlfile using catalog command.

RMAN>
RMAN> sql "alter database mount";

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN>

RMAN> catalog start with 'F:\rman_backup\*';

Starting implicit crosscheck backup at 15-AUG-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=201 device type=DISK
Crosschecked 75 objects
Finished implicit crosscheck backup at 15-AUG-22

Starting implicit crosscheck copy at 15-AUG-22
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-AUG-22

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern F:\rman_backup\*

List of Files Unknown to the Database
=====================================
File Name: F:\RMAN_BACKUP\backup_db_UCAS_S_754_P_1_T_1112789456
File Name: F:\RMAN_BACKUP\backup_db_UCAS_S_755_P_1_T_1112789456
File Name: F:\RMAN_BACKUP\backup_db_UCAS_S_756_P_1_T_1112789456
File Name: F:\RMAN_BACKUP\ctl_db_UCAS_S_757_P_1_T_1112789852

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: F:\RMAN_BACKUP\backup_db_UCAS_S_754_P_1_T_1112789456
File Name: F:\RMAN_BACKUP\backup_db_UCAS_S_755_P_1_T_1112789456
File Name: F:\RMAN_BACKUP\backup_db_UCAS_S_756_P_1_T_1112789456
File Name: F:\RMAN_BACKUP\ctl_db_UCAS_S_757_P_1_T_1112789852

RMAN>


Step 3. Clear expired old backups from controlfile: Run the below commands to delete metadata of expired rman backups from controlfile.

RMAN>
RMAN> crosscheck backup;
RMAN> crosscheck backup of controlfile;
RMAN> delete noprompt expired backup;
RMAN> delete noprompt expired archivelog all;
RMAN> delete noprompt expired backup of controlfile;
MAN>  delete noprompt expired copy;
MAN>  delete noprompt obsolete orphan;
RMAN> delete noprompt expired backup;

RMAN>

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
754     B  F  A DISK        15-AUG-22       1       1       YES        FULL BACKUP
755     B  F  A DISK        15-AUG-22       1       1       YES        FULL BACKUP
756     B  F  A DISK        15-AUG-22       1       1       YES        FULL BACKUP
757     B  F  A DISK        15-AUG-22       1       1       YES        CONTROL FILE BACKUP

RMAN>


Step 4. Initiate manual restore command: Initiate the manual restore using the below command. Please note we need to use the set newname rman command to specify the datafile location in the target. If in case source and target directory structure are the same, set newname command is not required in this case.

RMAN>

RMAN> run{
2> allocate channel ch1 device type disk;
3> allocate channel ch2 device type disk;
4> allocate channel ch3 device type disk;
5> set newname for datafile 1 to  'H:\UCAS\ORADATA_2\SYSTEM01.DBF';
6> set newname for datafile 2 to  'H:\UCAS\ORADATA_2\SYSAUX01.DBF';
7> set newname for datafile 3 to  'H:\UCAS\ORADATA_2\UNDOTBS01.DBF';
8> set newname for datafile 4 to  'H:\UCAS\ORADATA_2\USERS01.DBF';
9> set newname for datafile 5 to  'H:\UCAS\ORADATA_2\UCAS_01.DBF';
10> set newname for datafile 6 to  'H:\UCAS\ORADATA_2\UCAS_DATA_01.DBF';
11> set newname for datafile 7 to  'H:\UCAS\ORADATA_2\UCAS_INDEX_01.DBF';
12> set newname for datafile 8 to  'G:\UCAS\ORADATA_2\GGS_DATA_01.DBF';
13> set newname for datafile 9 to  'G:\UCAS\ORADATA_2\TELEX_01.DBF';
14> set newname for datafile 10 to 'G:\UCAS\ORADATA_2\UCAS_02.DBF';
15> set newname for datafile 11 to 'G:\UCAS\ORADATA_2\UCAS_DATA_02.DBF';
16> set newname for datafile 12 to 'G:\UCAS\ORADATA_2\UCAS_INDEX_02.DBF';
17> restore database;
18> switch datafile all;
19> }

using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=201 device type=DISK

allocated channel: ch2
channel ch2: SID=209 device type=DISK

allocated channel: ch3
channel ch3: SID=217 device type=DISK

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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 15-AUG-22

channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00003 to H:\UCAS\ORADATA_2\UNDOTBS01.DBF
channel ch1: restoring datafile 00006 to H:\UCAS\ORADATA_2\UCAS_DATA_01.DBF
channel ch1: restoring datafile 00007 to H:\UCAS\ORADATA_2\UCAS_INDEX_01.DBF
channel ch1: restoring datafile 00009 to G:\UCAS\ORADATA_2\TELEX_01.DBF
channel ch1: reading from backup piece F:\RMAN_BACKUP\BACKUP_DB_UCAS_S_755_P_1_T_1112789456
channel ch2: starting datafile backup set restore
channel ch2: specifying datafile(s) to restore from backup set
channel ch2: restoring datafile 00001 to H:\UCAS\ORADATA_2\SYSTEM01.DBF
channel ch2: restoring datafile 00002 to H:\UCAS\ORADATA_2\SYSAUX01.DBF
channel ch2: restoring datafile 00004 to H:\UCAS\ORADATA_2\USERS01.DBF
channel ch2: restoring datafile 00010 to G:\UCAS\ORADATA_2\UCAS_02.DBF
channel ch2: reading from backup piece F:\RMAN_BACKUP\BACKUP_DB_UCAS_S_756_P_1_T_1112789456
channel ch3: starting datafile backup set restore
channel ch3: specifying datafile(s) to restore from backup set
channel ch3: restoring datafile 00005 to H:\UCAS\ORADATA_2\UCAS_01.DBF
channel ch3: restoring datafile 00008 to G:\UCAS\ORADATA_2\GGS_DATA_01.DBF
channel ch3: restoring datafile 00011 to G:\UCAS\ORADATA_2\UCAS_DATA_02.DBF
channel ch3: restoring datafile 00012 to G:\UCAS\ORADATA_2\UCAS_INDEX_02.DBF
channel ch3: reading from backup piece F:\RMAN_BACKUP\BACKUP_DB_UCAS_S_754_P_1_T_1112789456
channel ch1: piece handle=F:\RMAN_BACKUP\BACKUP_DB_UCAS_S_755_P_1_T_1112789456 tag=FULL BACKUP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:04:35
channel ch2: piece handle=F:\RMAN_BACKUP\BACKUP_DB_UCAS_S_756_P_1_T_1112789456 tag=FULL BACKUP
channel ch2: restored backup piece 1
channel ch2: restore complete, elapsed time: 00:05:35
channel ch3: piece handle=F:\RMAN_BACKUP\BACKUP_DB_UCAS_S_754_P_1_T_1112789456 tag=FULL BACKUP
channel ch3: restored backup piece 1
channel ch3: restore complete, elapsed time: 00:07:46
Finished restore at 15-AUG-22

datafile 1 switched to datafile copy
input datafile copy RECID=13 STAMP=1112806911 file name=H:\UCAS\ORADATA_2\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=14 STAMP=1112806911 file name=H:\UCAS\ORADATA_2\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=1112806911 file name=H:\UCAS\ORADATA_2\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=16 STAMP=1112806911 file name=H:\UCAS\ORADATA_2\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=17 STAMP=1112806911 file name=H:\UCAS\ORADATA_2\UCAS_01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=18 STAMP=1112806911 file name=H:\UCAS\ORADATA_2\UCAS_DATA_01.DBF
datafile 7 switched to datafile copy
input datafile copy RECID=19 STAMP=1112806911 file name=H:\UCAS\ORADATA_2\UCAS_INDEX_01.DBF
datafile 8 switched to datafile copy
input datafile copy RECID=20 STAMP=1112806911 file name=G:\UCAS\ORADATA_2\GGS_DATA_01.DBF
datafile 9 switched to datafile copy
input datafile copy RECID=21 STAMP=1112806911 file name=G:\UCAS\ORADATA_2\TELEX_01.DBF
datafile 10 switched to datafile copy
input datafile copy RECID=22 STAMP=1112806911 file name=G:\UCAS\ORADATA_2\UCAS_02.DBF
datafile 11 switched to datafile copy
input datafile copy RECID=23 STAMP=1112806912 file name=G:\UCAS\ORADATA_2\UCAS_DATA_02.DBF
datafile 12 switched to datafile copy
input datafile copy RECID=24 STAMP=1112806912 file name=G:\UCAS\ORADATA_2\UCAS_INDEX_02.DBF
released channel: ch1
released channel: ch2
released channel: ch3

RMAN>

RMAN>


Step 5. Try to open DB with the resetlogs option: Once restore is done. Try to open the database with resetlogs option, this command failed. As redo log location registered in controlfile doesn’t exist in the target server.

SQL>
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
H:\UCAS\ORADATA_2\SYSTEM01.DBF
H:\UCAS\ORADATA_2\SYSAUX01.DBF
H:\UCAS\ORADATA_2\UNDOTBS01.DBF
H:\UCAS\ORADATA_2\USERS01.DBF
H:\UCAS\ORADATA_2\UCAS_01.DBF
H:\UCAS\ORADATA_2\UCAS_DATA_01.DBF
H:\UCAS\ORADATA_2\UCAS_INDEX_01.DBF
G:\UCAS\ORADATA_2\GGS_DATA_01.DBF
G:\UCAS\ORADATA_2\TELEX_01.DBF
G:\UCAS\ORADATA_2\UCAS_02.DBF
G:\UCAS\ORADATA_2\UCAS_DATA_02.DBF
G:\UCAS\ORADATA_2\UCAS_INDEX_02.DBF

12 rows selected.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
G:\UCAS\ORADATA_2\CONTROL01.CTL
H:\UCAS\ORADATA_2\CONTROL02.CTL

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/data02/oradata_1/redo07.log
/data02/oradata_1/redo06.log
/data01/oradata_1/redo05.log
/data01/oradata_1/redo04.log

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-create online log '/data01/oradata_1/redo04.log'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.


SQL>


Step 6. Rename logfile: Let’s try to rename logfile with rename command. But all the below commands failed.

SQL>
SQL> 
SQL> alter database rename file '/data02/oradata_1/redo07.log' to 'H:\UCAS\ORADATA_2\redo07.log';
alter database rename file '/data02/oradata_1/redo07.log' to 'H:\UCAS\ORADATA_2\redo07.log'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, data file, or temporary file "/data02/oradata_1/redo07.log"


SQL> show parameter log_file_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_file_name_convert                string      /data01/oradata_1/, G:\UCAS\OR
                                                 ADATA_1\, /data02/oradata_1/,
                                                 H:\UCAS\ORADATA_1\
SQL> alter database clear logfile group 4;
alter database clear logfile group 4
*
ERROR at line 1:
ORA-00344: unable to re-create online log '/data01/oradata_1/redo04.log'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.


SQL>
SQL>
SQL>  alter database clear unarchived logfile group 4  ;
 alter database clear unarchived logfile group 4
*
ERROR at line 1:
ORA-00344: unable to re-create online log '/data01/oradata_1/redo04.log'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.


SQL>


Step 7. Recreate Controlfile: Backup controlfile to trace and update the redologfile location in the trace file. And recreate controlfile.

SQL>
SQL> alter database backup controlfile to trace as 'F:\rman_backup\ucas_ctl.trc';

Database altered.

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>exit
F:\>
F:\>cd F:\rman_backup

F:\rman_backup>
F:\rman_backup>
F:\rman_backup>dir
 Volume in drive F is DB GG Soft
 Volume Serial Number is A602-E2D7

 Directory of F:\rman_backup

08/16/2022  01:34 PM    <DIR>          .
08/16/2022  01:34 PM    <DIR>          ..
08/15/2022  02:01 PM     2,830,704,640 backup_db_UCAS_S_754_P_1_T_1112789456
08/15/2022  02:02 PM     1,601,331,200 backup_db_UCAS_S_755_P_1_T_1112789456
08/15/2022  02:02 PM     1,800,183,808 backup_db_UCAS_S_756_P_1_T_1112789456
08/15/2022  02:00 PM         1,261,568 ctl_db_UCAS_S_757_P_1_T_1112789852
08/16/2022  01:35 PM               968 UCAS_CTL.TRC
               5 File(s)  6,233,482,184 bytes
               2 Dir(s)  197,816,340,480 bytes free

F:\rman_backup>type UCAS_CTL.TRC
CREATE CONTROLFILE REUSE DATABASE "UCAS" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 1168
LOGFILE
  GROUP 4 'H:\UCAS\ORADATA_2\redo04.log'  SIZE 500M BLOCKSIZE 512,
  GROUP 5 'H:\UCAS\ORADATA_2\redo05.log'  SIZE 500M BLOCKSIZE 512,
  GROUP 6 'H:\UCAS\ORADATA_2\redo06.log'  SIZE 500M BLOCKSIZE 512,
  GROUP 7 'H:\UCAS\ORADATA_2\redo07.log'  SIZE 500M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  'H:\UCAS\ORADATA_2\SYSTEM01.DBF',
  'H:\UCAS\ORADATA_2\SYSAUX01.DBF',
  'H:\UCAS\ORADATA_2\UNDOTBS01.DBF',
  'H:\UCAS\ORADATA_2\USERS01.DBF',
  'H:\UCAS\ORADATA_2\UCAS_01.DBF',
  'H:\UCAS\ORADATA_2\UCAS_DATA_01.DBF',
  'H:\UCAS\ORADATA_2\UCAS_INDEX_01.DBF',
  'G:\UCAS\ORADATA_2\GGS_DATA_01.DBF',
  'G:\UCAS\ORADATA_2\TELEX_01.DBF',
  'G:\UCAS\ORADATA_2\UCAS_02.DBF',
  'G:\UCAS\ORADATA_2\UCAS_DATA_02.DBF',
  'G:\UCAS\ORADATA_2\UCAS_INDEX_02.DBF'
CHARACTER SET AL32UTF8
;
F:\rman_backup>
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1.0255E+10 bytes
Fixed Size                  2290120 bytes
Variable Size            1845497400 bytes
Database Buffers         8388608000 bytes
Redo Buffers               18817024 bytes
SQL>
SQL>
SQL> @F:\rman_backup\UCAS_CTL.TRC

Control file created.

SQL>
SQL>
SQL> select member from v$logfile;

MEMBER
----------------------------------------------------------------------
H:\UCAS\ORADATA_2\REDO07.LOG
H:\UCAS\ORADATA_2\REDO06.LOG
H:\UCAS\ORADATA_2\REDO05.LOG
H:\UCAS\ORADATA_2\REDO04.LOG

SQL> 


Step 8. Open db with resetlogs option: Once controlfile is recreated open DB with the resetlogs option.

SQL>
SQL> alter database open resetlogs;

Database altered.

SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

This document is just for learning purposes and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article
!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.


Related Articles


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Leave a Reply

Your email address will not be published. Required fields are marked *