Home » RDBMS Server » Backup & Recovery » Recovery - Last Resort Options (Oracle 10.2.0, RHEL 5)
icon4.gif  Recovery - Last Resort Options [message #488864] Tue, 11 January 2011 19:14 Go to next message
daragaaz
Messages: 6
Registered: January 2011
Location: US
Junior Member
I have two databases - Production and Dev. Production database works great.

I've inadvertently deleted the archive logs and redo logs on the Dev instance, the database no longer opens; only mounts. I'm not really interested in recovering the data there because all of it exists on the other instance. Is there any way to pry open the database and get the OEM console up so I can import a .DMP file?

Thanks for any help or suggestions. I'm relatively uneducated in the DBA world so please have mercy. Embarassed

Report message to a moderator

Re: Recovery - Last Resort Options [message #488865 is a reply to message #488864] Tue, 11 January 2011 19:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
To import a .dmp file(created by oracle exp/expdp) you no need an OEM Console. Create a new database, import the data back.
You can invoke those commands in the command line from OS.

You may still get back your DB, provided you have done no more damage.

[[Oracle database Backup and Recovery FAQ#I.27ve lost an archived.2Fonline REDO LOG file.2C can I get my DB back.3F| http://www.orafaq.com/wiki/Oracle_database_Backup_and_Recovery_FAQ#I.27ve_lost_an_archived.2Fonline_REDO_LOG_file.2C_can_I_get_my_DB_ back.3F]]

[Updated on: Tue, 11 January 2011 19:32]

Report message to a moderator

Re: Recovery - Last Resort Options [message #488866 is a reply to message #488865] Tue, 11 January 2011 19:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>You can invoke those commands from the command line from OS.
When the DB won't open, exp or imp are useless.
Without REDO log files, the DB won't open.

Report message to a moderator

Re: Recovery - Last Resort Options [message #488870 is a reply to message #488866] Tue, 11 January 2011 19:56 Go to previous messageGo to next message
daragaaz
Messages: 6
Registered: January 2011
Location: US
Junior Member
Mahesh Rajendran: Thanks for the link, I see what I may need there.

Thank you both so much for replying! And so quickly! I'll give it a try and report findings.

Report message to a moderator

Re: Recovery - Last Resort Options [message #488895 is a reply to message #488870] Wed, 12 January 2011 01:31 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
You could set
_allow_reset_logs_corruption=true
and then mount the database and clear your logfile groups. You'll be able to open your database then. But I would never recommend that for anything other than a real emergency because you might have corrupted data (or even corrupted daa dictionary) if the deletion of the logs occurred while a transaction was in progress.

Report message to a moderator

Re: Recovery - Last Resort Options [message #489000 is a reply to message #488895] Wed, 12 January 2011 16:54 Go to previous messageGo to next message
daragaaz
Messages: 6
Registered: January 2011
Location: US
Junior Member
Current status:

I've set _allow_reset_logs_corruption = true in init.ora.

Created a control file with the following script:
create controlfile set database "PCBPROD" resetlogs
logfile
group 1 '/u00/oracle/oradata/PCBPROD/redo01.log' size 50M,
group 2 '/u00/oracle/oradata/PCBPROD/redo02.log' size 50M,
group 3 '/u00/oracle/oradata/PCBPROD/redo03.log' size 50M
datafile
'/u00/oracle/oradata/PCBPROD/alliance_data01',
'/u00/oracle/oradata/PCBPROD/alliance_indx01',
'/u00/oracle/oradata/PCBPROD/impdata01',
'/u00/oracle/oradata/PCBPROD/impindex01',
'/u00/oracle/oradata/PCBPROD/sysaux01.dbf',
'/u00/oracle/oradata/PCBPROD/system01.dbf',
'/u00/oracle/oradata/PCBPROD/undotbs01.dbf',
'/u00/oracle/oradata/PCBPROD/users01.dbf';

I still get this:


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u00/oracle/oradata/PCBPROD/system01.dbf'

So I try:

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 9470753 generated at 01/10/2011 16:18:51 needed for thread 1
ORA-00289: suggestion : /u06/oracle/oradata/PCBPROD/1_763_698928466.arc
ORA-00280: change 9470753 for thread 1 is in sequence #763


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'/u06/oracle/oradata/PCBPROD/1_763_698928466.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


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: '/u00/oracle/oradata/PCBPROD/system01.dbf'

Report message to a moderator

Re: Recovery - Last Resort Options [message #489001 is a reply to message #489000] Wed, 12 January 2011 17:07 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
Hello. I didn't tell you to create a controlfile, I told you to mount the database and clear your logfiles.
You are now in a place I've never been. Sorry I can't help.

Report message to a moderator

Re: Recovery - Last Resort Options [message #489002 is a reply to message #489001] Wed, 12 January 2011 17:10 Go to previous messageGo to next message
daragaaz
Messages: 6
Registered: January 2011
Location: US
Junior Member
Hi John,

I'm fairly new, how should I clear the logfiles?

Report message to a moderator

Re: Recovery - Last Resort Options [message #489003 is a reply to message #489002] Wed, 12 January 2011 17:36 Go to previous messageGo to next message
daragaaz
Messages: 6
Registered: January 2011
Location: US
Junior Member
I've used: alter database clear unarchived logfile group 1, 2 and 3.


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u00/oracle/oradata/PCBPROD/system01.dbf'

Am I just at the point where its time to reinstall?

Report message to a moderator

Re: Recovery - Last Resort Options [message #489004 is a reply to message #489003] Wed, 12 January 2011 17:38 Go to previous messageGo to next message
daragaaz
Messages: 6
Registered: January 2011
Location: US
Junior Member
(John - rolled back to previous control files. Smile )

Report message to a moderator

Re: Recovery - Last Resort Options [message #489005 is a reply to message #489004] Wed, 12 January 2011 17:41 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Am I just at the point where its time to reinstall?
Restore DEV from most recent backup of PROD

research SCN & how "(John - rolled back to previous control files. Smile )" is an exercise in futility

Report message to a moderator

Previous Topic: backup datafile/datafile using ".rman file"
Next Topic: Rman backup backupset question
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Nov 21 21:08:42 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.