Home » RDBMS Server » Backup & Recovery » I cant open Database. I tried almost all ways...
I cant open Database. I tried almost all ways... [message #251956] Tue, 17 July 2007 04:30 Go to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Here is the scenario. What should I do to open my database???

-bash-3.00$ sqlplus system as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 17 15:24:45 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> startup;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/oracle/app/oradata/Arju/system01.dbf'




SQL> !rman

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jul 17 15:27:28 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

RMAN> connect target;

connected to target database: ARJU (DBID=222697548, not open)

RMAN> list backup;

using target database control file instead of recovery catalog

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    521.71M    DISK        00:01:11     11-JUL-07
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20070711T105344
        Piece Name: /oracle/app/flash_recovery_area/ARJU/backupset/2007_07_11/o1_mf_nnndf_TAG20070711T105344_399vqr8f_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 598070     11-JUL-07 /oracle/app/oradata/Arju/system01.dbf
  2       Full 598070     11-JUL-07 /oracle/app/oradata/Arju/undotbs01.dbf
  3       Full 598070     11-JUL-07 /oracle/app/oradata/Arju/sysaux01.dbf
  4       Full 598070     11-JUL-07 /oracle/app/oradata/Arju/users01.dbf
  5       Full 598070     11-JUL-07 /oradata1/test02.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    6.80M      DISK        00:00:01     11-JUL-07
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20070711T105459
        Piece Name: /oracle/app/flash_recovery_area/ARJU/autobackup/2007_07_11/o1_mf_s_627648694_399vt445_.bkp
  Control File Included: Ckp SCN: 598070       Ckp time: 11-JUL-07
  SPFILE Included: Modification time: 11-JUL-07

RMAN> restore database from tag TAG20070711T105344;

Starting restore at 17-JUL-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK

skipping datafile 1; already restored to file /oracle/app/oradata/Arju/system01.dbf
skipping datafile 2; already restored to file /oracle/app/oradata/Arju/undotbs01.dbf
skipping datafile 3; already restored to file /oracle/app/oradata/Arju/sysaux01.dbf
skipping datafile 4; already restored to file /oracle/app/oradata/Arju/users01.dbf
skipping datafile 5; already restored to file /oradata1/test02.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 17-JUL-07



Re: I cant open Database. I tried almost all ways... [message #251987 is a reply to message #251956] Tue, 17 July 2007 10:57 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
delete the existing files and then try it again.i guess theres some command for validation but i dont remember whats that for you can check that as well

restore database validate;

http://download.oracle.com/docs/cd/B12037_01/server.101/b10735/recov.htm
Re: I cant open Database. I tried almost all ways... [message #252094 is a reply to message #251956] Tue, 17 July 2007 16:49 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Are you trying to restore from a previous incarnation of the database?
Re: I cant open Database. I tried almost all ways... [message #252122 is a reply to message #251956] Tue, 17 July 2007 21:52 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

yes..
Re: I cant open Database. I tried almost all ways... [message #252140 is a reply to message #251956] Tue, 17 July 2007 23:11 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Is there any SQL command by which I can delete datafile without removing tablespaces.
Re: I cant open Database. I tried almost all ways... [message #252155 is a reply to message #251956] Wed, 18 July 2007 00:44 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Yeah I solved this problem.

I ran,

startup force nomount
Restore controlfile from autobackup..
startup mount
Restore database
Then alter database open resetlogs;

My database now works fine.

[Updated on: Wed, 18 July 2007 00:48]

Report message to a moderator

Re: I cant open Database. I tried almost all ways... [message #252350 is a reply to message #252140] Wed, 18 July 2007 12:30 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Arju wrote on Tue, 17 July 2007 22:11
Is there any SQL command by which I can delete datafile without removing tablespaces.



ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf'
OFFLINE DROP;

(only for noarchivelog mode)
Re: I cant open Database. I tried almost all ways... [message #252351 is a reply to message #252155] Wed, 18 July 2007 12:31 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Arju wrote on Tue, 17 July 2007 23:44
Yeah I solved this problem.

I ran,

startup force nomount
Restore controlfile from autobackup..
startup mount
Restore database
Then alter database open resetlogs;

My database now works fine.


Congrulations Cool
Re: I cant open Database. I tried almost all ways... [message #252587 is a reply to message #252350] Thu, 19 July 2007 05:19 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
DreamzZ wrote on Wed, 18 July 2007 13:30
Arju wrote on Tue, 17 July 2007 22:11
Is there any SQL command by which I can delete datafile without removing tablespaces.

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf'
OFFLINE DROP;

(only for noarchivelog mode)

A caveat to this command is that it won't actually remove the datafile from the database. You'd have to be on 10g2+ to actually delete a datafile from a tablespace. Even with this, there are restrictions.
Previous Topic: ld.so.1: rman: fatal: relocation error
Next Topic: Setting Environment Variable in RMAN scripts
Goto Forum:
  


Current Time: Sat Dec 03 00:57:29 CST 2016

Total time taken to generate the page: 0.08348 seconds