| Recovering lost datafile using OEM [message #448709] |
Wed, 24 March 2010 23:04  |
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  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
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 #448916 is a reply to message #448709] |
Thu, 25 March 2010 13:27 |
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 #449087 is a reply to message #448919] |
Fri, 26 March 2010 09:56 |
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 |
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 #449110 is a reply to message #448709] |
Fri, 26 March 2010 13:25 |
John Watson
Messages: 8929
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 |
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 #449122 is a reply to message #449114] |
Fri, 26 March 2010 16:35 |
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 #449129 is a reply to message #449126] |
Sat, 27 March 2010 01:56 |
John Watson
Messages: 8929
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 #449207 is a reply to message #449171] |
Sat, 27 March 2010 20:50 |
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 #449216 is a reply to message #449208] |
Sun, 28 March 2010 01:32 |
John Watson
Messages: 8929
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
