OraFAQ Forum: Backup & Recovery » Cannot open the database

Home » RDBMS Server » Backup & Recovery » Cannot open the database (oracle 9i ,Xp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Cannot open the database [message #322724]

Mon, 26 May 2008 01:45

ram anand
Messages: 244
Registered: February 2008
Location: india

Senior Member

Hi,
I cannot able open my database through both RMAN and as SYSDBA , it throws the following error

RMAN> startup mount;

database is already started

RMAN> shutdown abort;

Oracle instance shut down

RMAN> startup force;

Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 05/26/2008 12:04:56
ORA-01190: controlfile or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\RAMANAN\SYSTEM01.DBF'

can anyone pls help on this to resolve the issue and make my database open

Thanks,

Report message to a moderator

Re: Cannot open the database [message #322731 is a reply to message #322724]

Mon, 26 May 2008 02:01

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use the correct file and not one that aged before the last "open resetlogs" you made.

Regards
Michel

Report message to a moderator

Re: Cannot open the database [message #322736 is a reply to message #322731]

Mon, 26 May 2008 02:12

ram anand
Messages: 244
Registered: February 2008
Location: india

Senior Member

Hi,
I cannt able to understand why because this concept is very new to me so pls help me regarding this,

Thanks,

Report message to a moderator

Re: Cannot open the database [message #322742 is a reply to message #322736]

Mon, 26 May 2008 02:22

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Mon, 19 May 2008 10:20

Database Backup and Recovery Basics

Regards
Michel

Report message to a moderator

Re: Cannot open the database [message #322745 is a reply to message #322724]

Mon, 26 May 2008 02:25

varu123
Messages: 754
Registered: October 2007

Senior Member

You have to apply the most recent backup and recover the database.

Report message to a moderator

Re: Cannot open the database [message #322752 is a reply to message #322745]

Mon, 26 May 2008 02:38

ram anand
Messages: 244
Registered: February 2008
Location: india

Senior Member

Hi,
I have even tried that , I took backup of my database on 22nd of may and now i tried to restore and recover but i cannot able to do that ,below shows the backup of the datafile

RMAN> LIST BACKUP OF DATAFILE 1;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
95      Full    376M       DISK        00:02:23     22-MAY-08
        BP Key: 71   Status: AVAILABLE   Tag: TAG20080522T160930
        Piece Name: D:\ORACLE\ORABACKUP\ORA_DF655402171_S97_S1
  List of Datafiles in backup set 95
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\SYSTEM01.DBF

I have tried using the TAG opition to restore and recover

RMAN> restore database from tag='TAG20080522T160930' validate;

Starting restore at 26-MAY-08

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=12 devtype=DISK
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_2: starting validation of datafile backupset
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ORACLE\ORABACKUP\ORA_DF655402171_S96_S1 tag=TAG20080522T160930 p
arams=NULL
channel ORA_DISK_1: validation complete
channel ORA_DISK_2: restored backup piece 1
piece handle=D:\ORACLE\ORABACKUP\ORA_DF655402171_S97_S1 tag=TAG20080522T160930 p
arams=NULL
channel ORA_DISK_2: validation complete
Finished restore at 26-MAY-08

RMAN> recover database from tag='TAG20080522T160930'
2> ;

Starting recover at 26-MAY-08
using channel ORA_DISK_1
using channel ORA_DISK_2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/26/2008 12:23:29
RMAN-20300: operation not supported without the recovery catalog

and tried to connect through catalog mode ,but still database is closed i cannot able to login into catalog mode also
so pls help me

Thanks,

Report message to a moderator

Re: Cannot open the database [message #322880 is a reply to message #322752]

Mon, 26 May 2008 10:19

ebrian
Messages: 2794
Registered: April 2006

Senior Member

What happened to your database to require recovery?

Is your database in ARCHIVELOG mode?

BTW...you didn't actually restore anything, you just did a VALIDATE.

Report message to a moderator

Re: Cannot open the database [message #322986 is a reply to message #322880]

Tue, 27 May 2008 00:20

ram anand
Messages: 244
Registered: February 2008
Location: india

Senior Member

Hi,
Yes I need to recover my db and its in ARCHIVELOG mode only
but i could not able to connect the catalog ,when i try to open my database it shows following error

[CODE]Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01190: controlfile or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\RAMANAN\SYSTEM01[/CODE].DBF'

Can u tell whether my db had been corrupted there is no way to resolve it pls its so urgent

Thanks,

Report message to a moderator

Re: Cannot open the database [message #322990 is a reply to message #322880]

Tue, 27 May 2008 00:25

ram anand
Messages: 244
Registered: February 2008
Location: india

Senior Member

Hi,
I think so there is no autobackup taken for which i took the backup on the following days, i have listed the backupset below,

 D:\>rman

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

RMAN> connect catalog rmancat/rman@ramanan

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04004: error from recovery catalog database: ORA-01033: ORACLE initializati
on or shutdown in progress

RMAN> connect target sys/simple@ramanan

connected to target database: RAMANAN (DBID=3720747608)

RMAN> list backupset of database;

using target database controlfile instead of recovery catalog

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
94      Full    364M       DISK        00:01:24     22-MAY-08
        BP Key: 70   Status: AVAILABLE   Tag: TAG20080522T160930
        Piece Name: D:\ORACLE\ORABACKUP\ORA_DF655402171_S96_S1
  List of Datafiles in backup set 94
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  4       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\DRSYS01.DBF
  6       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\INDX01.DBF
  9       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\USERS01.DBF
  10      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\XDB01.DBF
  12      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\SAMPLETEMP1_DAT01.ORA
  14      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\SAMPLE\TEMPL_DAT01.ORA
  15      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\SAMPLE1\TSTSMLTBS_DAT01.OR
A
  17      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\SAMPLE1\TSTSMLNDX_DAT01.OR
A
  18      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\SAMPLE1\TSTMDMNDX_DAT01.OR
A
  19      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\RMAN01.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
95      Full    1G         DISK        00:02:23     22-MAY-08
        BP Key: 71   Status: AVAILABLE   Tag: TAG20080522T160930
        Piece Name: D:\ORACLE\ORABACKUP\ORA_DF655402171_S97_S1
  List of Datafiles in backup set 95
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\SYSTEM01.DBF
  2       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\UNDOTBS01.DBF
  3       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\CWMLITE01.DBF
  5       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\EXAMPLE01.DBF
  7       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\ODM01.DBF
  8       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\TOOLS01.DBF
  13      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\SAMPLE\TEMP1_DAT01.ORA
  16      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\SAMPLE1\TSTMDMTBS_DAT01.OR
A
  20      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\SAMPLE1\TSTSMLTBS_DAT02.OR
A

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
99      Full    126M       DISK        00:00:07     22-MAY-08
        BP Key: 75   Status: AVAILABLE   Tag: TAG20080522T182256
        Piece Name: D:\ORACLE\ORABACKUP\ORA_DF655410176_S101_S1
  List of Datafiles in backup set 99
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5       Full 19784042   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\EXAMPLE01.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
115     Full    376M       DISK        00:00:22     26-MAY-08
        BP Key: 91   Status: AVAILABLE   Tag: TAG20080526T122635
        Piece Name: D:\ORACLE\ORABACKUP\ORA_DF655734395_S122_S1
  List of Datafiles in backup set 115
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\SYSTEM01.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
119     Full    364M       DISK        00:01:21     26-MAY-08
        BP Key: 95   Status: AVAILABLE   Tag: TAG20080526T165603
        Piece Name: D:\ORACLE\ORABACKUP\ORA_DF655750563_S126_S1
  List of Datafiles in backup set 119
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  4       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\DRSYS01.DBF
  6       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\INDX01.DBF
  9       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\USERS01.DBF
  10      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\XDB01.DBF
  12      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\SAMPLETEMP1_DAT01.ORA
  14      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\SAMPLE\TEMPL_DAT01.ORA
  15      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\SAMPLE1\TSTSMLTBS_DAT01.OR
A
  17      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\SAMPLE1\TSTSMLNDX_DAT01.OR
A
  18      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\SAMPLE1\TSTMDMNDX_DAT01.OR
A
  19      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\RMAN01.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
120     Full    1G         DISK        00:02:18     26-MAY-08
        BP Key: 96   Status: AVAILABLE   Tag: TAG20080526T165603
        Piece Name: D:\ORACLE\ORABACKUP\ORA_DF655750564_S127_S1
  List of Datafiles in backup set 120
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\SYSTEM01.DBF
  2       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\UNDOTBS01.DBF
  3       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\CWMLITE01.DBF
  5       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\EXAMPLE01.DBF
  7       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\ODM01.DBF
  8       Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\RAMANAN\TOOLS01.DBF
  13      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\SAMPLE\TEMP1_DAT01.ORA
  16      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\SAMPLE1\TSTMDMTBS_DAT01.OR
A
  20      Full 19725667   22-MAY-08 D:\ORACLE\ORADATA\SAMPLE1\TSTSMLTBS_DAT02.OR
A

Can u help me reg this

Report message to a moderator