-
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.
-
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?.
Looking for the greatest evil in the world? Look in the mirror.
-
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.
-
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...
Looking for the greatest evil in the world? Look in the mirror.
-
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.
Last edited by nilay; 06-26-2008 at 03:08 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|