ORACLE 11G does not have backup files to recover deleted data by using Rman Backup in different machine

Background:

A colleague mistakenly deletes the online data. So you need to recover the data from the backup.

Real house leakage of the rain overnight, the ship was late and the wind. The disk blocks that were backed up in the first two days are now only available with the. bak file, which has no control and parameter files, so now you need to consider how to recover data from the Bak file on the backup database to find the data that was mistakenly deleted.

1 Recover 1.1 Manually create control files by catalog start with '

CREATE Controlfile Reuse Set DATABASE "Powerdes" Resetlogs ARCHIVELOG

Maxlogfiles 16

Maxlogmembers 3

Maxdatafiles 100

Maxinstances 8

Maxloghistory 454

DataFile

'/home/oradata/powerdes/system01.dbf ',

'/home/oradata/powerdes/sysaux01.dbf ',

'/home/oradata/powerdes/undotbs01.dbf ',

'/home/oradata/powerdes/users01.dbf ',

'/home/oradata/powerdes/powerdesk01.dbf ',

'/home/oradata/powerdes/plas01.dbf ',

'/home/oradata/powerdes/pl01.dbf ',

'/home/oradata/powerdes/help01.dbf ',

'/home/oradata/powerdes/adobelc01.dbf ',

'/home/oradata/powerdes/sms01.dbf ',

'/HOME/ORADATA/POWERDES/PLCRM01.DBF '

LOGFILE

GROUP 1 ('/home/oradata/powerdes/redo03.log ') SIZE 10M,

GROUP 2 ('/home/oradata/powerdes/redo02.log ') SIZE 10M,

GROUP 3 ('/home/oradata/powerdes/redo01.log ') size10m,

GROUP 4 ('/home/oradata/powerdes/redo_dg_01.log ') SIZE 10M,

GROUP 5 ('/home/oradata/powerdes/redo_dg_02.log ') SIZE 10M,

GROUP 6 ('/home/oradata/powerdes/redo_dg_03.log ') SIZE 10M

CHARACTER SET ZHS16GBK;

1.2, set catalog start with path

Searching for all files that match the pattern/tmp/2015-03-30/

List of Files Unknown to the Database

=====================================

File Name:/tmp/2015-03-30/full_powerdes_20150330_3395.bak

File Name:/tmp/2015-03-30/rman_backup.log

File Name:/tmp/2015-03-30/arch_powerdes_20150330_3396.bak

File Name:/tmp/2015-03-30/arch_powerdes_20150330_3394.bak

Really want to catalog the Abovefiles (enter YES or NO)?

Yes

Cataloging files ...

Cataloging done

List of cataloged Files

=======================

File Name:/tmp/2015-03-30/full_powerdes_20150330_3395.bak

File Name:/tmp/2015-03-30/arch_powerdes_20150330_3396.bak

File Name:/tmp/2015-03-30/arch_powerdes_20150330_3394.bak

List of Files which Where not cataloged

=======================================

File Name:/tmp/2015-03-30/rman_backup.log

Rman-07517:reason:the file header is corrupted

1.3 Start RESTORE Database

rman> Restore Database;

Starting restore at 01-apr-15

Using channel Ora_disk_1

rman-00571:===========================================================

RMAN-00569: =============== ERROR Messagestack follows ===============

rman-00571:===========================================================

Rman-03002:failure of restore command at04/01/2015 03:39:01

Rman-06026:some Targets not found-aborting restore

Rman-06023:no Backup or copy of datafile12 found to restore

Rman-06023:no Backup or copy of datafile11 found to restore

Rman-06023:no Backup or copy of datafile10 found to restore

Rman-06023:no Backup or copy of datafile 9found to restore

Rman-06023:no Backup or copy of datafile 8found to restore

Rman-06023:no Backup or copy of datafile 7found to restore

Rman-06023:no Backup or copy of datafile 5found to restore

Rman-06023:no Backup or copy of datafile 4found to restore

Rman-06023:no Backup or copy of datafile 3found to restore

Rman-06023:no Backup or copy of datafile 2found to restore

Rman-06023:no Backup or copy of datafile 1found to restore

Rman>

PS : Unfortunately the recovery failed, Google for a very long time, and found only to pass Sys.dbms_backup_restore.restoreDatafileTo the way to do not completely consistent recovery.

2, use Sys.dbms_backup_restore.restoreDatafileTo to do not completely consistent data recovery

2.1. Do Restoredatafileto data extraction work first. That is to extract the data files from the backup set, remember that the data file path to be consistent with the line.

Run commands such as the following:

Sql> DECLARE

Devtype VARCHAR2 (256);

Doneboolean;

BEGIN

Devtype:=sys.dbms_backup_restore.deviceallocate (type=> ',ident=> ' t1 ');

Sys.dbms_backup_restore.restoreSetDatafile;

Sys.dbms_backup_restore.restoreDatafileTo (dfnumber=>01,toname=> '/home/oradata/powerdes/system01.dbf ');

Sys.dbms_backup_restore.restoreDatafileTo (dfnumber=>02,toname=> '/home/oradata/powerdes/sysaux01.dbf ');

Sys.dbms_backup_restore.restoreDatafileTo (dfnumber=>03,toname=> '/home/oradata/powerdes/undotbs01.dbf ');

Sys.dbms_backup_restore.restoreDatafileTo (dfnumber=>04,toname=> '/home/oradata/powerdes/users01.dbf ');

Sys.dbms_backup_restore.restoreDatafileTo (dfnumber=>05,toname=> '/home/oradata/powerdes/powerdesk01.dbf ') ;

Sys.dbms_backup_restore.restoreDatafileTo (dfnumber=>06,toname=> '/home/oradata/powerdes/plas01.dbf ');

Sys.dbms_backup_restore.restoreDatafileTo (dfnumber=>07,toname=> '/home/oradata/powerdes/pl01.dbf ');

Sys.dbms_backup_restore.restoreDatafileTo (dfnumber=>08,toname=> '/home/oradata/powerdes/help01.dbf ');

Sys.dbms_backup_restore.restoreDatafileTo (dfnumber=>09,toname=> '/home/oradata/powerdes/adobelc01.dbf ');

Sys.dbms_backup_restore.restoreDatafileTo (dfnumber=>10,toname=> '/home/oradata/powerdes/sms01.dbf ');

Sys.dbms_backup_restore.restoreDatafileTo (dfnumber=>11,toname=> '/home/oradata/powerdes/plcrm01.dbf ');

Sys.dbms_backup_restore.restoreBackupPiece (done=>done,handle=> '/pddata2/oracle/backup/data/2015-03-30/ Full_powerdes_20150330_3395.bak ', params=>null);

Sys.dbms_backup_restore.deviceDeallocate;

END;

/

PS : Handle is the one running the backup Bak the last of the documents; / not less.

Sql>

The running process takes a while and waits slowly. The alert log in the background will be followed by information such as the following to indicate that the extraction was successful:

Full restore complete of datafile 7 to DATAFILECOPY/HOME/ORADATA/POWERDES/PL01.DBF. Elapsed time:0:00:41

Checkpoint is 11029786628

Last deallocation SCN is 11022714700

Full restore complete of datafile 8 todatafile copy/home/oradata/powerdes/help01.dbf. Elapsed time:0:00:01

Checkpoint is 11029786628

Last deallocation SCN is 9881798870

Wed APR 01 05:01:54 2015

Full restore complete of datafile 9 todatafile copy/home/oradata/powerdes/adobelc01.dbf. Elapsed time:0:00:00

Checkpoint is 11029786628

Wed APR 01 05:02:12 2015

Full restore complete of datafile todatafile copy/home/oradata/powerdes/sms01.dbf. Elapsed Time:0:00:12

Checkpoint is 11029786628

Wed APR 01 05:05:15 2015

Full restore complete of datafile 3 todatafile copy/home/oradata/powerdes/undotbs01.dbf. Elapsed time:0:04:45

Checkpoint is 11029786628

Last deallocation SCN is 11029784977

Undo optimization current SCN is 11029779851

Wed APR 01 05:06:04 2015

Full restore complete of datafile one todatafile copy/home/oradata/powerdes/plcrm01.dbf. Elapsed time:0:06:01

Checkpoint is 11029786628

Last deallocation SCN is 11007637870

Wed APR 01 05:06:27 2015

Full restore complete of datafile 4 todatafile copy/home/oradata/powerdes/users01.dbf. Elapsed time:0:06:24

Checkpoint is 11029786628

Last deallocation SCN is 11029437543

Wed APR 01 05:10:33 2015

Full restore complete of datafile 1 todatafile copy/home/oradata/powerdes/system01.dbf. Elapsed time:0:09:27

Checkpoint is 11029786628

Last deallocation SCN is 10910611499

Undo optimization current SCN is 11029779851

Wed APR 01 05:13:39 2015

Full restore complete of datafile 2 todatafile copy/home/oradata/powerdes/sysaux01.dbf. Elapsed time:0:13:29

Checkpoint is 11029786628

Last deallocation SCN is 11028968586

Wed APR 01 05:14:44 2015

Full restore complete of datafile 6 todatafile copy/home/oradata/powerdes/plas01.dbf. Elapsed time:0:14:37

Checkpoint is 11029786628

Last deallocation SCN is 11028348038

Wed APR 01 05:39:30 2015

Full restore complete of datafile 5 todatafile copy/home/oradata/powerdes/powerdesk01.dbf. Elapsed Time:0:39:12

Checkpoint is 11029786628

Last deallocation SCN is 11029438448

2.2 After successful data extraction, create a control file

Assuming you don't know how to create a controlfile command, you can generate trace files online

General default control file is binary, open to is garbled, backup a trace out can open see statement, $ORACLE _base/admin/$ORACLE _sid/udump folder. The new trace file that is generated. Trace files have scripts that generate control files. Use for example the following command ALTER DATABASE backup Controlfile to trace as '/oracle/app/oracle/admin/powerdes/pfile/control.sql '; The ability to get SQL commands to create control files.

Sort it out to create a control file command such as the following:

CREATE controlfile Reuse SET DATABASE "Powerdes" Resetlogs ARCHIVELOG

Maxlogfiles 16

Maxlogmembers 3

Maxdatafiles 100

Maxinstances 8

Maxloghistory 2920

DataFile

'/home/oradata/powerdes/system01.dbf ',

'/home/oradata/powerdes/sysaux01.dbf ',

'/home/oradata/powerdes/undotbs01.dbf ',

'/home/oradata/powerdes/users01.dbf ',

'/home/oradata/powerdes/powerdesk01.dbf ',

'/home/oradata/powerdes/plas01.dbf ',

'/home/oradata/powerdes/pl01.dbf ',

'/home/oradata/powerdes/help01.dbf ',

'/home/oradata/powerdes/adobelc01.dbf ',

'/home/oradata/powerdes/sms01.dbf ',

'/HOME/ORADATA/POWERDES/PLCRM01.DBF '

LOGFILE

GROUP 1 '/home/oradata/powerdes/redo01.log ' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/home/oradata/powerdes/redo02.log ' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/home/oradata/powerdes/redo03.log ' SIZE 50M BLOCKSIZE 512

CHARACTER SET ZHS16GBK;

2.3 Recovering data and opening the database

rman> Recover database;

Starting recover at 02-apr-15

Using target database Control file insteadof recovery Catalog

Allocated Channel:ora_disk_1

Channel ora_disk_1:sid=767 Devicetype=disk

Starting Media recovery

Unable to find archived log

Archived Log thread=1 sequence=31023

rman-00571:===========================================================

RMAN-00569: =============== ERROR Messagestack follows ===============

rman-00571:===========================================================

Rman-03002:failure of recover command at04/02/2015 13:35:40

Rman-06054:media recovery Requestingunknown archived log for thread 1 with sequence 31023 and starting SCN of11029786628

Rman>

rman> restore database from '/pddata2/oracle/backup/data/2015-03-30/full_powerdes_20150330_3395.bak ';

Starting restore at 02-apr-15

Using channel Ora_disk_1

rman-00571:===========================================================

RMAN-00569: =============== ERROR Messagestack follows ===============

rman-00571:===========================================================

Rman-03002:failure of restore command at04/02/2015 13:37:05

Rman-06509:only SPFILE or control file canbe restored from Autobackup

Rman>

See the Open all error. So try opening it directly.

sql> sql> ALTER DATABASE open Resetlogs
2;

Database altered.

Sql>

3. Then exp exports the table data, IMP imports into the production environment

Export: Exp system/[email protected] file=/tmp/qd.dmp tables= (Hr_resume_qingdao) system is user name, manager is Password,myoracle is the database name.

Data transfer: Scp/tmp/qd.dmp 192.168.120.217:/tmp/

Import: Imp username/pwd[@sid] file=/tmp/qd.dmp tables= (Hr_resume_qingdao)

4, some Error records

Locate the data file

ERROR at line 1:

Ora-01503:create Controlfile failed

ora-01200:actual file size of 128679 Issmaller than correct size of 340480

Blocks

Ora-01110:data file 2: '/HOME/ORADATA/POWERDES/SYSAUX01.DBF '

Workaround:

DD if=/dev/zeroof=/home/oradata/powerdes/sysaux01.dbf bs=8192 count=1 seek=340480

 ----------------------------------------------------------------------------------------------------------- -----
< copyright All, the article agreed to reprint. However, the source address must be indicated by the link, otherwise the legal liability shall be investigated!>
Original Blog address: http://blog.itpub.net/26230597/viewspace-1484099/
Hara Douglas Fir (MCHDBA)
----------------------------------------------------------------------------------------------------------- -----

ORACLE 11G does not have backup files to recover deleted data by using Rman Backup in different machine