ORACLE 11G does not have backup file parameter files in the machine via Rman backup recovery to retrieve the data that was mistakenly deleted

Background:

Colleagues mistakenly delete online data, so they need to recover data from the backup. Real estate leakage of the rain, the ship late and the wind, the first two days of backup disk bad block, now only rman full of. bak files, no control files and parameter files, so now it is necessary to consider how to recover data from a 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 long time, found only through Sys.dbms_backup_restore.restoreDatafileTo the way to do not fully consistent recovery.

2, using Sys.dbms_backup_restore.restoreDatafileTo to do incomplete consistent data recovery

2.1, first do restoredatafileto data extraction work, that is, the data files from the backup set extracted, remember the data file path to be consistent with the line.

The following commands are executed:

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 to perform a backup Bak the last of the documents; / not less.

Sql>

The execution process takes time, waits slowly, the backstage Alert The log will be followed by the following information indicating the extraction success:

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

If you do not know how to create a controlfile command, you can generate the trace file 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 directory, generated by the new trace file, The trace file has a script that generates the control file, using the following command, ALTER DATABASE backup Controlfile to trace as '/oracle/app/oracle/admin/powerdes/pfile/ Control.sql '; You can get the SQL command to create the control file.

To sort out the Create control File command as follows:

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 open all error, so try to open it directly

sql> sql> ALTER DATABASE open Resetlogs
2;

Database altered.

Sql>

3, then exp export table data, IMP Import into production environment

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

Transfer data: 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, the article allows reprint, but must be linked to the source address, otherwise investigate legal liability!>
Original Blog address: http://blog.itpub.net/26230597/viewspace-1484099/
Hara Douglas Fir (MCHDBA)
----------------------------------------------------------------------------------------------------------- -----

ORACLE 11G does not have backup file parameter files in the machine via Rman backup recovery to retrieve the data that was mistakenly deleted