Home » RDBMS Server » Backup & Recovery » Recovering lost datafile using OEM (Oracle 10gR2 , Red Hat Linux 4.)
Recovering lost datafile using OEM [message #448709] Wed, 24 March 2010 23:04 Go to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
Can someone guide on what to do. I am receiving the following error when trying to recover lost file /u01/app/oracle/oradata/orcl/example01.dbf :

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Mar 24 23:08:23 2010

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

RMAN>
connected to target database: ORCL (DBID=1237639643)
using target database control file instead of recovery catalog

RMAN>
echo set on


RMAN> run {
2> restore datafile 5;
3> recover datafile 5;
4> sql 'alter database datafile 5 online';
5> }
Starting restore at 24-MAR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=136 devtype=DISK
released channel: ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/24/2010 23:08:24
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library


RMAN> exit;

Recovery Manager complete.

[Updated on: Wed, 24 March 2010 23:07]

Report message to a moderator

Re: Recovering lost datafile using OEM [message #448718 is a reply to message #448709] Thu, 25 March 2010 00:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ORA-27211: Failed to load Media Management Library
 *Cause:  User-supplied SBT_LIBRARY or libobk.so could not be loaded. Call
          to dlopen for media library returned error. See Additional
          information for error code.
 *Action: Retry the command with proper media library. Or re-install
          Media management module for Oracle.

Check all log file from your MML and check its configuration parameters.

Regards
Michel

[Updated on: Thu, 25 March 2010 00:16]

Report message to a moderator

Re: Recovering lost datafile using OEM [message #448833 is a reply to message #448718] Thu, 25 March 2010 06:02 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
Where in OEM can I setup the Device Type=DISK?
I have not setup RMAN for my database. What commands can I use to restore this setting.
Re: Recovering lost datafile using OEM [message #448846 is a reply to message #448833] Thu, 25 March 2010 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I don't know OEM, I can only answer RMAN question.
Have a llok at CONFIGURE command:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;

If your question is a real OEM question we have to move it into EM forum and not keep it in Backup one.

Regards
Michel

[Updated on: Thu, 25 March 2010 06:39]

Report message to a moderator

Re: Recovering lost datafile using OEM [message #448847 is a reply to message #448833] Thu, 25 March 2010 06:38 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:
I have not setup RMAN for my database


If am correct. If you don't have RMAN backup then how your going to restore and recover your database?

Babu
Re: Recovering lost datafile using OEM [message #448916 is a reply to message #448709] Thu, 25 March 2010 13:27 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
"If you don't have RMAN backup then how your going to restore and recover your database?"

I am using OEM. However, as you can see in the error log, it is RMAN commands running in the background.

In response to Michel Cadot: It is not an OEM question,please bare with me. I need to somehow recover the example01.dbf file.
Re: Recovering lost datafile using OEM [message #448919 is a reply to message #448916] Thu, 25 March 2010 13:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If it is on disk then use the command I provided and I recommend to not use OEM but learn how to use tools directly.

Regards
Michel
Re: Recovering lost datafile using OEM [message #449087 is a reply to message #448919] Fri, 26 March 2010 09:56 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
Michel, I promise once I get this issue resolved, I will be all over RMAN. I was meerly following Oracle Database 10g: Administration Workshop I chapter 16 exercise. At this point RMAN has not been covered. The exercise call for the deliberate deletion of the example01.dbf datafile and perform recovery using OEM.

Now I am attempting to recover the file and then setup rman to do my backups from here on.

There is an option in OEM to edit the rman script before running it so I added the show all command to display my current rman settings and maybe someone here can help me furthur. Below is the output of the entire operation including the added "show all" command. Please advise.

using target database control file instead of recovery catalog

RMAN>
echo set on


RMAN> run {
2> show all;
3> restore datafile 5;
4> recover datafile 5;
5> sql 'alter database datafile 5 online';
6> }
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 250 M;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' MAXPIECESIZE 250 M;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_orcl.f'; # default

Starting restore at 26-MAR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=136 devtype=DISK
released channel: ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/26/2010 09:24:54
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library


RMAN> exit;

Recovery Manager complete.


[Updated on: Fri, 26 March 2010 09:57]

Report message to a moderator

Re: Recovering lost datafile using OEM [message #449093 is a reply to message #448919] Fri, 26 March 2010 10:39 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
I just added the following to my script and now I have a new error:

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' clear;

RMAN>
echo set on


RMAN> run {
2> CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' clear;
3> show all;
4> restore datafile 5;
5> recover datafile 5;
6> sql 'alter database datafile 5 online';
7> }
old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' MAXPIECESIZE 250 M;
old RMAN configuration parameters are successfully deleted

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 250 M;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_orcl.f'; # default

Starting restore at 26-MAR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=132 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_03_19/o1_mf_nnnd0_BACKUP_ORCL_000131_0_5t8cf6v1_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_03_19/o1_mf_nnnd0_BACKUP_ORCL_000131_0_5t8cf6v1_.bkp tag=BACKUP_ORCL_000131_031910090617
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_03_19/o1_mf_nnnd0_BACKUP_ORCL_000131_0_5t8cfz4k_.bkp
channel ORA_DISK_1: restored backup piece 2
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_03_19/o1_mf_nnnd0_BACKUP_ORCL_000131_0_5t8cfz4k_.bkp tag=BACKUP_ORCL_000131_031910090617
channel ORA_DISK_1: restore complete, elapsed time: 00:00:11
Finished restore at 26-MAR-10

Starting recover at 26-MAR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_03_19/o1_mf_nnnd1_NIGHTLY_BACKUP_03191_5t8l2fjh_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_03_19/o1_mf_nnnd1_NIGHTLY_BACKUP_03191_5t8l2fjh_.bkp tag=NIGHTLY_BACKUP_031910110001
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

archive log thread 1 sequence 279 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_03_21/o1_mf_1_279_5tcf2lfj_.arc
archive log thread 1 sequence 280 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_03_21/o1_mf_1_280_5tf97pph_.arc
archive log thread 1 sequence 281 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_03_22/o1_mf_1_281_5tg1w72l_.arc
archive log thread 1 sequence 282 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_03_22/o1_mf_1_282_5tj8sjk6_.arc
archive log thread 1 sequence 283 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_03_23/o1_mf_1_283_5tjmdvmy_.arc
archive log thread 1 sequence 284 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_03_23/o1_mf_1_284_5tktz36n_.arc
archive log thread 1 sequence 285 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_03_23/o1_mf_1_285_5tm1ntz3_.arc
archive log thread 1 sequence 286 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_03_24/o1_mf_1_286_5tmkntgx_.arc
archive log thread 1 sequence 287 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_03_24/o1_mf_1_287_5tojy63p_.arc
archive log thread 1 sequence 288 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_03_25/o1_mf_1_288_5toyo55s_.arc
archive log thread 1 sequence 289 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_03_25/o1_mf_1_289_5tqy9wnr_.arc
archive log thread 1 sequence 290 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_03_25/o1_mf_1_290_5tr8tbg3_.arc
archive log thread 1 sequence 291 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_03_26/o1_mf_1_291_5tryk662_.arc
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/26/2010 10:40:11
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 278 lowscn 4081508 found to restore
RMAN-06025: no backup of log thread 1 seq 277 lowscn 4081032 found to restore
RMAN-06025: no backup of log thread 1 seq 276 lowscn 4081019 found to restore
RMAN-06025: no backup of log thread 1 seq 275 lowscn 4081017 found to restore
RMAN-06025: no backup of log thread 1 seq 274 lowscn 4066492 found to restore
RMAN-06025: no backup of log thread 1 seq 273 lowscn 4066487 found to restore
RMAN-06025: no backup of log thread 1 seq 272 lowscn 4066485 found to restore
RMAN-06025: no backup of log thread 1 seq 271 lowscn 4066174 found to restore
RMAN-06025: no backup of log thread 1 seq 270 lowscn 4066172 found to restore
RMAN-06025: no backup of log thread 1 seq 269 lowscn 4066170 found to restore
RMAN-06025: no backup of log thread 1 seq 268 lowscn 4063826 found to restore
RMAN-06025: no backup of log thread 1 seq 267 lowscn 4041447 found to restore
RMAN-06025: no backup of log thread 1 seq 266 lowscn 4009836 found to restore
RMAN-06025: no backup of log thread 1 seq 265 lowscn 3991357 found to restore
RMAN-06025: no backup of log thread 1 seq 264 lowscn 3978304 found to restore


RMAN> exit;

Recovery Manager complete.
Re: Recovering lost datafile using OEM [message #449097 is a reply to message #449093] Fri, 26 March 2010 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It tells you that it can't recover the file because some archived logs are missing: neither on disk nor in backups.

Regards
Michel
Re: Recovering lost datafile using OEM [message #449105 is a reply to message #449097] Fri, 26 March 2010 11:57 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
So what are my options at this point. Is there such thing as imcomplete recovery ?
Re: Recovering lost datafile using OEM [message #449106 is a reply to message #449105] Fri, 26 March 2010 12:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes, there is, have a look at SET UNTIL command or clause.

Regards
Michel
Re: Recovering lost datafile using OEM [message #449110 is a reply to message #448709] Fri, 26 March 2010 13:25 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
I've just had a look at the Admin Workshop 1 course manuals, and if you followed the exercise for chapter 15 as written, you should have made a full level zero open backup (without archive logfiles), and then you should have scheduled an incremental level 1 with archive logfiles to run overnight. Did that scheduled backup actually run?
If it didn't you need to check whether your archive logfiles do still exist somewhere. According to chapter 14, you should have two copies of them: one in your flash recovery area, the other in /u01/app/oracle/archive. If you don't have either, you cannot do an incomplete recovery, and your only option is to drop the example tablespace's datafile, open the database, and drop the example tablespace.
This is quite like some real world situations I have been called in to fix from time to time.
Re: Recovering lost datafile using OEM [message #449113 is a reply to message #449110] Fri, 26 March 2010 13:48 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
You are right I did do a level 0 backup without archive log files, and I also did some incremental backups. The disaster I created was when I was running out of disk space and decided to remove the older archive files. Glad I am making the mistakes in training. Lesson Learned.

I just did more research and decided to run the following and I have successfully restored the file. I will now restart the database. I will let you know how it goes.



Recovery Manager: Release 10.2.0.1.0 - Production on Fri Mar 26 13:41:20 2010

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

RMAN>
connected to target database: ORCL (DBID=1237639643)
using target database control file instead of recovery catalog

RMAN>
echo set on


RMAN> run {
2> restore datafile 5 from tag 'BACKUP_ORCL_000131_031910090617';
3> }
Starting restore at 26-MAR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=131 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_03_19/o1_mf_nnnd0_BACKUP_ORCL_000131_0_5t8cf6v1_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_03_19/o1_mf_nnnd0_BACKUP_ORCL_000131_0_5t8cf6v1_.bkp tag=BACKUP_ORCL_000131_031910090617
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_03_19/o1_mf_nnnd0_BACKUP_ORCL_000131_0_5t8cfz4k_.bkp
channel ORA_DISK_1: restored backup piece 2
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_03_19/o1_mf_nnnd0_BACKUP_ORCL_000131_0_5t8cfz4k_.bkp tag=BACKUP_ORCL_000131_031910090617
channel ORA_DISK_1: restore complete, elapsed time: 00:00:06
Finished restore at 26-MAR-10


RMAN> exit;

Recovery Manager complete.


file is in directory as can be seen:

[oracle@jhlinux orcl]$ ll -rt
total 5064556
-rw-r----- 1 oracle oinstall 20979712 Mar 25 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 52429312 Mar 25 22:00 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Mar 25 22:00 redo01b.log
-rw-r----- 1 oracle oinstall 52429312 Mar 26 04:11 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Mar 26 04:11 redo02b.log
-rw-r----- 1 oracle oinstall 41951232 Mar 26 04:16 users01.dbf
-rw-r----- 1 oracle oinstall 36708352 Mar 26 04:16 inventory01.dbf
-rw-r----- 1 oracle oinstall 104865792 Mar 26 13:41 example01.dbf
-rw-r----- 1 oracle oinstall 534781952 Mar 26 13:52 system01.dbf
-rw-r----- 1 oracle oinstall 3775930368 Mar 26 13:53 undotbs01.dbf
-rw-r----- 1 oracle oinstall 398467072 Mar 26 13:54 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 Mar 26 13:54 redo03b.log
-rw-r----- 1 oracle oinstall 7159808 Mar 26 13:54 control03.ctl
-rw-r----- 1 oracle oinstall 7159808 Mar 26 13:54 control02.ctl
-rw-r----- 1 oracle oinstall 7159808 Mar 26 13:54 control01.ctl
Re: Recovering lost datafile using OEM [message #449114 is a reply to message #449113] Fri, 26 March 2010 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes, the file is restored but not recovered and so you will not be able to open the database.

Regards
Michel
Re: Recovering lost datafile using OEM [message #449122 is a reply to message #449114] Fri, 26 March 2010 16:35 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
I was able to start the database but there is still a problem as you said. I am still not able to do

SQL> connect dba1 / as sysdba
Enter password:
Connected.
SQL> select * from hr.countries;

ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'


When I check the status of the datafile it says recover.

SQL> select FILE#,status,enabled from v$datafile where status in ('OFFLINE','RECOVER');

FILE# STATUS ENABLED
---------- ------- ----------
5 RECOVER READ WRITE


When I try to recover the file I get the following error message. My question now is, how can I get around this ?

SQL> recover datafile 5;
ORA-00279: change 3974947 generated at 03/19/2010 21:06:30 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_03_26/o1_mf_1_263_%u_.a
rc
ORA-00280: change 3974947 for thread 1 is in sequence #263


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

ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_03_19/o1_mf_1_263_5t8g
kr6b_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Re: Recovering lost datafile using OEM [message #449126 is a reply to message #449122] Sat, 27 March 2010 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
how can I get around this ?

You cannot, you just can drop the tablespace (after moving all the objects you can in another tablespace).

Regards
Michel
Re: Recovering lost datafile using OEM [message #449129 is a reply to message #449126] Sat, 27 March 2010 01:56 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
Quote:
According to chapter 14, you should have two copies of them: one in your flash recovery area, the other in /u01/app/oracle/archive

Did you do this, and if so did you delete the archive logs from both destinations? RMAN is looking only in your flash recovery area, I can't remember with release 10 if it will fail over to another destination, if there is one.
Re: Recovering lost datafile using OEM [message #449164 is a reply to message #449126] Sat, 27 March 2010 10:17 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
Unfortunately I deleted both Sad

Below is what I have left. Earlier I was struggling with disk space and I did not know how to add disk space to a mounted filesystem in linux. So I said to myself this could be a real world learning opportunity.

I was hoping that I could just start from where I did level 0 backup. and ignore everything that happened afterwards. Is that possible ?


[oracle@jhlinux archivelog]$ pwd
/u01/app/oracle/flash_recovery_area/ORCL/archivelog
[oracle@jhlinux archivelog]$ ll
total 56
drwxr-x--- 2 oracle oinstall 4096 Mar 21 18:00 2010_03_21
drwxr-x--- 2 oracle oinstall 4096 Mar 22 21:10 2010_03_22
drwxr-x--- 2 oracle oinstall 4096 Mar 23 22:27 2010_03_23
drwxr-x--- 2 oracle oinstall 4096 Mar 24 21:00 2010_03_24
drwxr-x--- 2 oracle oinstall 4096 Mar 25 22:00 2010_03_25
drwxr-x--- 2 oracle oinstall 4096 Mar 26 19:54 2010_03_26
drwxr-x--- 2 oracle oinstall 4096 Mar 27 07:00 2010_03_27

[oracle@jhlinux archive]$ pwd
/u01/app/oracle/archive
[oracle@jhlinux archive]$ ll
total 701464
-rw-r----- 1 oracle oinstall 51229184 Mar 22 01:00 1_281_709858908.dbf
-rw-r----- 1 oracle oinstall 51229184 Mar 22 21:10 1_282_709858908.dbf
-rw-r----- 1 oracle oinstall 51229184 Mar 23 00:11 1_283_709858908.dbf
-rw-r----- 1 oracle oinstall 51229184 Mar 23 11:27 1_284_709858908.dbf
-rw-r----- 1 oracle oinstall 51229184 Mar 23 22:27 1_285_709858908.dbf
-rw-r----- 1 oracle oinstall 51224576 Mar 24 03:00 1_286_709858908.dbf
-rw-r----- 1 oracle oinstall 51229184 Mar 24 21:00 1_287_709858908.dbf
-rw-r----- 1 oracle oinstall 51229184 Mar 25 00:54 1_288_709858908.dbf
-rw-r----- 1 oracle oinstall 51229184 Mar 25 19:01 1_289_709858908.dbf
-rw-r----- 1 oracle oinstall 51229184 Mar 25 22:00 1_290_709858908.dbf
-rw-r----- 1 oracle oinstall 51229184 Mar 26 04:11 1_291_709858908.dbf
-rw-r----- 1 oracle oinstall 51428864 Mar 26 19:54 1_292_709858908.dbf
-rw-r----- 1 oracle oinstall 51229184 Mar 27 00:12 1_293_709858908.dbf
-rw-r----- 1 oracle oinstall 51224576 Mar 27 07:00 1_294_709858908.dbf
Re: Recovering lost datafile using OEM [message #449171 is a reply to message #449164] Sat, 27 March 2010 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You have to restore the whole database at this point then.

Regards
Michel
Re: Recovering lost datafile using OEM [message #449207 is a reply to message #449171] Sat, 27 March 2010 20:50 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
This is now unknown territory for me. I would like a little guidance so that I don't make things worse than they already are.

To restore the whole database I understand that I must be in the mount state. Is the following command appropriate ?

restore database from tag 'BACKUP_ORCL_000131_031910090617';

Is there something I should do to make it ignore unavailable archives ?


I thank you guys for your continued input.
Re: Recovering lost datafile using OEM [message #449208 is a reply to message #449207] Sat, 27 March 2010 21:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>This is now unknown territory for me. I would like a little guidance so that I don't make things worse than they already are.

It is days too late for this belated concern.

You can take full cold backup now, to ensure you can get back to the current state.

Only you know what you did & what you have.

reformatting disk & starting from a clean start would be fastest way to have a functioning DB.
Re: Recovering lost datafile using OEM [message #449215 is a reply to message #449207] Sun, 28 March 2010 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Is there something I should do to make it ignore unavailable archives ?

Think about your question.
What are archived logs? The memory of the actions that have been done?
What is recovery? The application of these actions.
Image you have an insert in deleted archived logs and then an update of this row in an archived logs you still have.
Can Oracle apply an action on something that does not exit?
Can you have grand-children without having any child?

The only thing you can do is recovering up to the last archived log after the backup you took.

If your tag is correct, your statement is correct but you can simply use "restore database;"

Regards
Michel
Re: Recovering lost datafile using OEM [message #449216 is a reply to message #449208] Sun, 28 March 2010 01:32 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
If you restore your database, you will destroy it completely because you cannot recover it. As Michel and I have said, your only option is to drop the example tablespace. I would suggest that you either do that, or follow BlackSwan's dvice and start again.
Re: Recovering lost datafile using OEM [message #449248 is a reply to message #449216] Sun, 28 March 2010 20:09 Go to previous message
jxh461
Messages: 185
Registered: March 2005
Senior Member
Thanks guys, I will start again.

Lesson learned indeed.
Previous Topic: hot backup mode question
Next Topic: change DBID
Goto Forum:
  


Current Time: Thu Nov 14 04:38:38 CST 2024