Cannot open db - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 38

Thread: Cannot open db

  1. #11
    Join Date
    Feb 2001
    Posts
    107
    gandolf989,

    Thans for your input. I tried recreating the controlefiles. Now when I try to open the database I get:
    ERROR at line 1:
    ORA-01194: file 1 needs more recovery to be consistent
    ORA-01110: data file 1: 'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF'

    I don't mind if I can open the database in an inconsistent state.

    Any expert opinion welcomed.

    Thanks

  2. #12
    Join Date
    May 2001
    Posts
    736
    U need to perform a media recovery.
    Issue the following statements from SQL.
    1.Shutdown immediate.
    2.Startup mount
    3.SELECT member FROM v$log l, v$logfile f WHERE l.group# = f.group# AND l.status = 'CURRENT';
    4.Recover database until cancel;
    5.Apply the log member from step 3.
    6.Alter database open resetlogs;
    Once ur database is succssefully open then take immediate system backup.

  3. #13
    Join Date
    Feb 2001
    Posts
    107
    akhadar,

    I get no rows selected


    SQL> startup mount
    ORACLE instance started.

    Total System Global Area 135338868 bytes
    Fixed Size 453492 bytes
    Variable Size 109051904 bytes
    Database Buffers 25165824 bytes
    Redo Buffers 667648 bytes
    Database mounted.
    SQL> SELECT member FROM v$log l, v$logfile f WHERE l.group# = f.group# AND
    2 l.status = 'CURRENT';

    no rows selected

  4. #14
    Join Date
    May 2001
    Posts
    736
    Before procedding further let me know
    1.Is ur database in archive log mode?
    2.Do u have a recent backup?. If so restore it.

  5. #15
    Join Date
    Feb 2001
    Posts
    107
    Hi the database is in noarchive log mode. The only backup I have has the issue with the undo tablespace. This became corrupted during the backup.

  6. #16
    Join Date
    May 2001
    Posts
    736
    Really it is bad that if the database is no archive mode u have a few option.

    Do as following

    1.Mount the database
    2. SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
    FROM V$LOG V1, V$LOGFILE V2
    WHERE V1.GROUP# = V2.GROUP# ;
    This will list all the redo log files and their change numbers.
    3. SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;
    If the CHANGE# is GREATER than the minimum FIRST_CHANGE#
    of your logs, the datafile can be recovered.
    4.Recover datafile 'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF';
    5.keep on applying the redo logs it will request to aplly until u will see the message 'Media recovery complete'.
    5.Then open database and take a full backup.

  7. #17
    Join Date
    Feb 2001
    Posts
    107
    hi got close,

    SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
    2 FROM V$LOG V1, V$LOGFILE V2
    3 WHERE V1.GROUP# = V2.GROUP# ;

    GROUP#
    ----------
    MEMBER
    --------------------------------------------------------------------------------

    SEQUENCE# FIRST_CHANGE#
    ---------- -------------
    1
    E:\ORACLE\ORADATA\TRAIN1\REDO01.LOG
    128 14471300

    2
    E:\ORACLE\ORADATA\TRAIN1\REDO02.LOG
    129 14491303

    GROUP#
    ----------
    MEMBER
    --------------------------------------------------------------------------------

    SEQUENCE# FIRST_CHANGE#
    ---------- -------------

    3
    E:\ORACLE\ORADATA\TRAIN1\REDO03.LOG
    130 14511306


    SQL> SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;

    FILE# CHANGE#
    ---------- ----------
    1 14511307
    3 14511307
    4 14511307
    5 14511307
    6 14511307
    7 14511307
    8 14511307
    9 14511307
    10 14511307
    11 14511307
    12 14511307

    FILE# CHANGE#
    ---------- ----------
    13 14511307

    12 rows selected.

    SQL> Recover datafile 'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF';
    ORA-00283: recovery session canceled due to errors
    ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


    SQL> recover database using backup controlfile until cancel;
    ORA-00279: change 14511307 generated at 10/11/2004 06:19:41 needed for thread 1
    ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC00130.001
    ORA-00280: change 14511307 for thread 1 is in sequence #130


    Specify log: {=suggested | filename | AUTO | CANCEL}
    E:\ORACLE\ORADATA\TRAIN1\REDO01.LOG
    ORA-00310: archived log contains sequence 128; sequence 130 required
    ORA-00334: archived log: 'E:\ORACLE\ORADATA\TRAIN1\REDO01.LOG'


    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
    ORA-01194: file 1 needs more recovery to be consistent
    ORA-01110: data file 1: 'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF'


    SQL> recover database using backup controlfile until cancel;
    ORA-00279: change 14511307 generated at 10/11/2004 06:19:41 needed for thread 1
    ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC00130.001
    ORA-00280: change 14511307 for thread 1 is in sequence #130


    Specify log: {=suggested | filename | AUTO | CANCEL}
    E:\ORACLE\ORADATA\TRAIN1\REDO03.LOG
    Log applied.
    Media recovery complete.
    SQL> alter database open resetlogs;
    alter database open resetlogs
    *
    ERROR at line 1:
    ORA-01092: ORACLE instance terminated. Disconnection forced

    alert log:
    ALTER DATABASE RECOVER LOGFILE 'E:\ORACLE\ORADATA\TRAIN1\REDO03.LOG'
    Tue Oct 12 06:12:51 2004
    Media Recovery Log E:\ORACLE\ORADATA\TRAIN1\REDO03.LOG
    Incomplete recovery applied all redo ever generated.
    Recovery completed through change 14511308
    Media Recovery Complete
    Completed: ALTER DATABASE RECOVER LOGFILE 'E:\ORACLE\ORADA
    Tue Oct 12 06:14:00 2004
    alter database open resetlogs
    Tue Oct 12 06:14:01 2004
    RESETLOGS after complete recovery through change 14511308
    Resetting resetlogs activation ID 3709337036 (0xdd17fdcc)
    Tue Oct 12 06:14:20 2004
    Assigning activation ID 3724130951 (0xddf9ba87)
    Thread 1 opened at log sequence 1
    Current log# 3 seq# 1 mem# 0: E:\ORACLE\ORADATA\TRAIN1\REDO03.LOG
    Successful open of redo thread 1.
    Tue Oct 12 06:14:21 2004
    SMON: enabling cache recovery
    Tue Oct 12 06:14:23 2004
    Errors in file c:\oracle\admin\train1\udump\train1_ora_2432.trc:
    ORA-00704: bootstrap process failure
    ORA-00604: error occurred at recursive SQL level 2
    ORA-08103: object no longer exists

    Tue Oct 12 06:14:23 2004
    Error 704 happened during db open, shutting down database
    USER: terminating instance due to error 704
    Instance terminated by USER, pid = 2432
    ORA-1092 signalled during: alter database open resetlogs...

  8. #18
    Join Date
    Feb 2001
    Posts
    107
    I'm going around in circles on this. Does anyone have any leads? Please see below:

    SQL> connect / as sysdba
    Connected to an idle instance.
    SQL> startup mount
    ORACLE instance started.

    Total System Global Area 135338868 bytes
    Fixed Size 453492 bytes
    Variable Size 109051904 bytes
    Database Buffers 25165824 bytes
    Redo Buffers 667648 bytes
    Database mounted.
    SQL> Recover datafile 'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF';
    Media recovery complete.
    SQL> alter database open;
    alter database open
    *
    ERROR at line 1:
    ORA-01113: file 3 needs media recovery
    ORA-01110: data file 3: 'E:\ORACLE\ORADATA\TRAIN1\CWMLITE01.DBF'

  9. #19
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    Have you done recover datafile 'E:\ORACLE\ORADATA\TRAIN1\CWMLITE01.DBF'; ???

    Every time it tells you to recover a datafile, do a recover datafile for that file.

  10. #20
    Join Date
    Feb 2001
    Posts
    107
    It's exactly what Iam saying. I recover all the datafiles. When I attempt to open the database, I get SQL> alter database open;
    alter database open
    *
    ERROR at line 1:
    ORA-01092: ORACLE instance terminated. Disconnection forced.

    I then start the db mount.. alter database open; and get ERROR at line 1:
    ORA-01113: file 1 needs media recovery
    ORA-01110: data file 1: 'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF'.

    I'm going round in cycles.

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