ORA-01157: cannot identify/lock data file 1 - see DBWR trace file (After renaming database)

Problem:

After renaming the database we saw the below alert when try to open the database with resetlogs.

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '+DATA/db4_dg/datafile/system.363.845738809'

Solution:

I have created the controlfile with correct path and name. create controlfile script for recreating control files.

1. Create controlfile script for recreating control files. This will create a trace file in the udump directory.

SQL> alter database backup controlfile to trace;

Database altered.

Code:

CREATE CONTROLFILE REUSE DATABASE "DB4_DG" NORESETLOGS FORCE LOGGING ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 1168

LOGFILE

GROUP 1 '+REDO/db4_dg/onlinelog/group_1.269.845738979' SIZE 2048M BLOCKSIZE 512,

GROUP 2 '+REDO/db4_dg/onlinelog/group_2.268.845738981' SIZE 2048M BLOCKSIZE 512,

GROUP 3 '+REDO/db4_dg/onlinelog/group_3.267.845738985' SIZE 2048M BLOCKSIZE 512,

GROUP 4 '+REDO/db4_dg/onlinelog/group_4.266.845738987' SIZE 2048M BLOCKSIZE 512,

GROUP 5 '+REDO/db4_dg/onlinelog/group_5.290.845738991' SIZE 2048M BLOCKSIZE 512

-- STANDBY LOGFILE

-- GROUP 6 '+REDO/db4_dg/onlinelog/group_6.257.845738993' SIZE 2048M BLOCKSIZE 512,

-- GROUP 7 '+REDO/db4_dg/onlinelog/group_7.289.845738995' SIZE 2048M BLOCKSIZE 512,

-- GROUP 8 '+REDO/db4_dg/onlinelog/group_8.258.845738999' SIZE 2048M BLOCKSIZE 512,

-- GROUP 9 '+REDO/db4_dg/onlinelog/group_9.288.845739001' SIZE 2048M BLOCKSIZE 512,

-- GROUP 10 '+REDO/db4_dg/onlinelog/group_10.259.845739003' SIZE 2048M BLOCKSIZE 512,

-- GROUP 11 '+REDO/db4_dg/onlinelog/group_11.279.845739007' SIZE 2048M BLOCKSIZE 512

DATAFILE

'+DATA/db4_dg/datafile/system.363.845738809',

'+DATA/db4_dg/datafile/sysaux.361.845738783',

'+INDX/db4_dg/datafile/undotbs1.336.845737749',

'+INDX/db4_dg/datafile/undotbs1.344.845738025',

'+INDX/db4_dg/datafile/undotbs1.527.845738031',

'+INDX/db4_dg/datafile/undotbs1.345.845738317',

'+DATA/db4_dg/datafile/users.298.880989169',

'+DATA/db4_dg/datafile/statspack.297.880989169',

'+INDX/db4_dg/datafile/ipx_small_index.334.845738319',

'+DATA/db4_dg/datafile/ipx_small_data.514.845738453',

'+INDX/db4_dg/datafile/ipx_medium_index.341.845731703',

'+DATA/db4_dg/datafile/ipx_medium_data.331.845733453',

'+INDX/db4_dg/datafile/ipx_large_index_2.267.845721535',

'+DATA/db4_dg/datafile/ipx_large_index.302.880989143',

'+DATA/db4_dg/datafile/gops_small_data.295.845738965',

'+DATA/db4_dg/datafile/ipx_large_data.512.845717191',

'+DATA/db4_dg/datafile/backup.291.880992665',

'+DATA/db4_dg/datafile/clients_data.519.845738783',

'+INDX/db4_dg/datafile/clients_index.529.845738823',

'+DATA/db4_dg/datafile/clients_raw_data.513.845738965',

'+DATA/db4_dg/datafile/clients_raw_index.296.880989285',

'+DATA/db4_dg/datafile/gops_small_index.295.880989285',

'+DATA/db4_dg/datafile/gops_large_data.290.880992667',

'+DATA/db4_dg/datafile/gops_large_index.294.880989325',

'+DATA/db4_dg/datafile/gops_lob.293.880989325',

'+DATA/db4_dg/datafile/ipx_large_data_2.301.880989163',

'+DATA/db4_dg/datafile/webapp_data.333.845738825',

'+INDX/db4_dg/datafile/webapp_index.333.845738849',

'+INDX/db4_dg/datafile/ipx_large_index_tmp.340.845733309',

'+DATA/db4_dg/datafile/ipx_large_data_tmp.522.845737635',

'+DATA/db4_dg/datafile/ipx_large_data_201502.529.865519111',

'+INDX/db4_dg/datafile/ipx_large_index_3.268.845723301',

'+DATA/db4_dg/datafile/ipx_large_data_201503.526.865519125',

'+DATA/db4_dg/datafile/ipx_large_data_201504.515.865519137',

'+DATA/db4_dg/datafile/ipx_large_data_201505.520.865519147',

'+DATA/db4_dg/datafile/ipx_large_data_201506.360.865519153',

'+DATA/db4_dg/datafile/ipx_large_index_4.365.845737329',

'+DATA/db4_dg/datafile/ipx_large_data_201507.516.865519161',

'+DATA/db4_dg/datafile/ipx_large_data_201508.524.865519167',

'+DATA/db4_dg/datafile/work_small_data.521.845738559',

'+DATA/db4_dg/datafile/ipx_large_data_201509.330.865519175',

'+DATA/db4_dg/datafile/ipx_large_data_201510.528.865519181',

'+DATA/db4_dg/datafile/ipx_large_data_201511.523.865519189',

'+DATA/db4_dg/datafile/ipx_large_data_201512.356.865519195',

'+INDX/db4_dg/datafile/ipx_large_index_201502.346.865519275',

'+INDX/db4_dg/datafile/ipx_large_index_201503.256.865519283',

'+INDX/db4_dg/datafile/ipx_large_index_201504.423.865519291',

'+INDX/db4_dg/datafile/ipx_large_index_201505.530.865519301',

'+INDX/db4_dg/datafile/ipx_large_index_201506.532.865519309',

'+INDX/db4_dg/datafile/ipx_large_index_201507.338.865519317',

'+INDX/db4_dg/datafile/ipx_large_index_201508.339.865519323',

'+INDX/db4_dg/datafile/ipx_large_index_201509.523.865519333',

'+INDX/db4_dg/datafile/ipx_large_index_201510.342.865519341',

'+INDX/db4_dg/datafile/ipx_large_index_201511.343.865519347',

'+INDX/db4_dg/datafile/ipx_large_index_201512.269.865519353'

CHARACTER SET AL32UTF8

;

ALTER DATABASE OPEN RESETLOGS;

and rename it to ctl.sql

2. Edit the file to point the path of the datafiles and redologfiles. Then startup the database in nomount mode and run the ctl.sql file as sys as sysdba user.

Code:

oracle@server ~]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 16 10:47:18 2016

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> startup nomount

ORACLE instance started.

Total System Global Area 80812648 bytes

Fixed Size 453224 bytes

Variable Size 54525952 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL> @/home/oracle/ctl.sql

Control file created.

Database altered.

SQL> select name from v$controlfile;

NAME

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

+DATA/db4_dg/controlfile/current.256.880987965

Note:

To rename the database change reuse to set in the create control file script as shown below

CREATE CONTROLFILE SET DATABASE "DB4_DG" NORESETLOGS FORCE LOGGING ARCHIVELOG