Ora-01157
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Ora-01157

Hybrid View

  1. #1
    Join Date
    May 2008
    Posts
    12

    Unhappy 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.

  2. #2
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110
    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?.
    Looking for the greatest evil in the world? Look in the mirror.

  3. #3
    Join Date
    May 2008
    Posts
    12
    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.

  4. #4
    Join Date
    May 2008
    Posts
    12
    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.

  5. #5
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110
    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...
    Looking for the greatest evil in the world? Look in the mirror.

  6. #6
    Join Date
    May 2008
    Posts
    12
    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
  •  



Click Here to Expand Forum to Full Width