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
- Solution for ORA-01516: nonexistent log file, data file, or temporary file in Oracle
- Solution for RMAN-05541: no archived logs found in target database in Oracle
- Steps to Clone Oracle Database from Windows to Oracle Linux
- Clone PDB to another CDB using RMAN Active Duplicate in Oracle on New Host
- RMAN Incremental Level 1 Differential and Cumulative Database Backup Scripts in Oracle
