Ora-01157

Dear all,

I had space problem in my disc.So I manually shifted oradata folder (containing all control and dbf files) to a different location.

Then I created spfile after necessary changes.

But when I try to open the database , its showing error.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 790096 bytes
Variable Size 170914224 bytes
Database Buffers 440401920 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'P:\ORACLE\PRODUCT\10.1.0\ORADATA\TCENG\SYSTEM01.DBF'

Is there any way to resolve it?
Any way I have to shift the dbf files.
Please help.

Quote:

---

Originally Posted by nilay

Dear all,

I had space problem in my disc.So I manually shifted oradata folder (containing all control and dbf files) to a different location.

Then I created spfile after necessary changes.

But when I try to open the database , its showing error.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 790096 bytes
Variable Size 170914224 bytes
Database Buffers 440401920 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'P:\ORACLE\PRODUCT\10.1.0\ORADATA\TCENG\SYSTEM01.DBF'

Is there any way to resolve it?
Any way I have to shift the dbf files.
Please help.

---

You shifted them on the OS level, right? But have you informed Oracle that the locations have changed? You do this by renaming the datafiles within Oracle pointing to the new location, tablespace being offline, and MOST important shifting your Control Files. You would have to rename the location in the Pfile, right?.

Dear Tuma,

Thanks a lot for reply.
I changed in pfile. Then created spfile.
Please go through the commands below.


SQL> startup nomount pfile=P:\oracle\product\10.1.0\db_1\database\INITtceng.ORA
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 790096 bytes
Variable Size 170914224 bytes
Database Buffers 440401920 bytes
Redo Buffers 262144 bytes
SQL> create spfile from pfile;

File created.

SQL> create pfile from spfile;

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 790096 bytes
Variable Size 170914224 bytes
Database Buffers 440401920 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'P:\ORACLE\PRODUCT\10.1.0\ORADATA\TCENG\SYSTEM01.DBF'


SQL> alter database recover database;
alter database recover database
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 1: 'P:\ORACLE\PRODUCT\10.1.0\ORADATA\TCENG\SYSTEM01.DBF'
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'P:\ORACLE\PRODUCT\10.1.0\ORADATA\TCENG\SYSTEM01.DBF'


SQL> select * from tab;
select * from tab
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> ALTER SYSTEM CHECK DATAFILES;

System altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'P:\ORACLE\PRODUCT\10.1.0\ORADATA\TCENG\SYSTEM01.DBF'


Please advice.

Only thing I have done is in pfile I have changed the path of control files(Database was shutdown). Then created spfile.
But is this enough for 'renaming the datafiles within Oracle pointing to the new location, tablespace being offline, and MOST important shifting the Control Files'?
If any other command I have to run, Please advice.

Quote:

---

Originally Posted by nilay

Only thing I have done is in pfile I have changed the path of control files(Database was shutdown). Then created spfile.
But is this enough for 'renaming the datafiles within Oracle pointing to the new location, tablespace being offline, and MOST important shifting the Control Files'?
If any other command I have to run, Please advice.

---

I see it complaining about the system tablespace? You may have to recreate your control file from scratch.

alter database backup controfile to trace;
pick it up at your udump
NB edit the text file putting in the locations of the files shifted. save it with .sql
shutdown database;
startup nomount;
create controfile reuse database "database name''
recover DATABASE;
alter databse OPEN;

NB...cross check these steps...

Tanks for reply Tuma.

The process I have followed as :

shutdown database

Shifted oradata folder to desired location

Edited INITtceng.ORA file

created spfile accordingly.

shutdown database

startup mount

alter database backup controlfile to trace;

copied the trace file and paste it at the new oradata location

Edited the trace file and saved it as .sql as:
-----------------------------------------------------------
Before Edit:

LOGFILE
GROUP 1 'P:\ORACLE\PRODUCT\10.1.0\ORADATA\TCENG\REDO01.LOG' SIZE 10M,
GROUP 2 'P:\ORACLE\PRODUCT\10.1.0\ORADATA\TCENG\REDO02.LOG' SIZE 10M,
GROUP 3 'P:\ORACLE\PRODUCT\10.1.0\ORADATA\TCENG\REDO03.LOG' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'P:\ORACLE\PRODUCT\10.1.0\ORADATA\TCENG\SYSTEM01.DBF',
'P:\ORACLE\PRODUCT\10.1.0\ORADATA\TCENG\UNDOTBS01.DBF',
'P:\ORACLE\PRODUCT\10.1.0\ORADATA\TCENG\SYSAUX01.DBF',
'P:\ORACLE\PRODUCT\10.1.0\ORADATA\TCENG\IDATA01.DBF',
'P:\ORACLE\PRODUCT\10.1.0\ORADATA\TCENG\ILOG01.DBF',
'P:\ORACLE\PRODUCT\10.1.0\ORADATA\TCENG\INDX01.DBF'

ALTER DATABASE REGISTER LOGFILE 'P:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TCENG\ARCHIVELOG\2008_06_26\O1_MF_1_1_%U_.ARC';

ALTER TABLESPACE TEMP ADD TEMPFILE 'P:\ORACLE\PRODUCT\10.1.0\ORADATA\TCENG\TEMP01.DBF' REUSE;

..........................................
After Edit:

LOGFILE
GROUP 1 'C:\ORADATA\TCENG\REDO01.LOG' SIZE 10M,
GROUP 2 'C:\ORADATA\TCENG\REDO02.LOG' SIZE 10M,
GROUP 3 'C:\ORADATA\TCENG\REDO03.LOG' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'C:\ORADATA\TCENG\SYSTEM01.DBF',
'C:\ORADATA\TCENG\UNDOTBS01.DBF',
'C:\ORADATA\TCENG\SYSAUX01.DBF',
'C:\ORADATA\TCENG\IDATA01.DBF',
'C:\ORADATA\TCENG\ILOG01.DBF',
'C:\ORADATA\TCENG\INDX01.DBF'

ALTER DATABASE REGISTER LOGFILE 'P:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TCENG\ARCHIVELOG\2008_06_26\O1_MF_1_1_%U_.ARC';

ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORADATA\TCENG\TEMP01.DBF' REUSE;
------------------------------------------------------
then

Shutdown database

then
..........................................................................


SQL> startup nomount;
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 790096 bytes
Variable Size 170914224 bytes
Database Buffers 440401920 bytes
Redo Buffers 262144 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "tceng" NORESETLOGS;
CREATE CONTROLFILE REUSE DATABASE "tceng" NORESETLOGS
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file
'%ORACLE_HOME%\DATABASE\DBS1%ORACLE_SID%.ORA'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


SQL> CREATE CONTROLFILE REUSE DATABASE "tceng" RESETLOGS;
CREATE CONTROLFILE REUSE DATABASE "tceng" RESETLOGS
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file
'%ORACLE_HOME%\DATABASE\DBS1%ORACLE_SID%.ORA'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL>
......................................................

I have not edited trace file.

Please advice.