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

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

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
    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
;

Comments