ORACLE 11G does not have backup file parameter files on the same machine through rman backup recovery was accidentally deleted data, 11 grman
Background:
Colleagues accidentally deleted online data, so they need to recover data from the backup. Real house leaks may occur due to Night Rain, late ships, and bad disk blocks backed up two days ago. Currently, only rman is used for full backup. bak files do not have control files and parameter files. Therefore, we need to consider how to recover data from the backup database based on the bak files and find the accidentally deleted data.
1. Use catalog start with ''to restore 1.1 Manual Control File Creation
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/power000001.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 10 M,
GROUP 2 ('/home/oradata/powerdes/redo02.log') SIZE 10 M,
GROUP 3 ('/home/oradata/powerdes/redo01.log') SIZE10M,
GROUP 4 ('/home/oradata/powerdes/redo_dg_01.log') SIZE 10 M,
GROUP 5 ('/home/oradata/powerdes/redo_dg_02.log') SIZE 10 M,
GROUP 6 ('/home/oradata/powerdes/redo_dg_03.log') SIZE 10 M
Character set ZHS16GBK;
1.2. Set the 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
Do you 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 already upted
1.3 start restore database recovery 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 9 found to restore
RMAN-06023: no backup or copy of datafile 8 found to restore
RMAN-06023: no backup or copy of datafile 7 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
RMAN>
PS: Unfortunately, the recovery failed. After google for a long time, it was found that the inconsistency can only be restored through sys. dbms_backup_restore.restoreDatafileTo.
2. Use sys. dbms_backup_restore.restoreDatafileTo perform inconsistent data recovery.
2.1. First extract the restoreDatafileTo data, that is, extract the data files from the backup set. Remember that the path of the data file must be consistent with that of the online file.
Run the following command:
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/power1_01.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. Invoke (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 bak file for backup; the final/should not be small.
SQL>
The execution takes a while and waits slowly. The alert Log in the background displays the following information in sequence, prompting that the extraction is 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 10 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 11 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/power000001.dbf. Elapsed time: 0:39:12
Checkpoint is 11029786628
Last deallocation scn is 11029438448
2.2 After data extraction is successful, you must create a new control file.
If you do not know how to create the controlfile command, you can generate a trace file online.
Generally, the default control file is binary and the code is garbled. Back up a trace and you can see the statement. In the $ ORACLE_BASE/admin/$ ORACLE_SID/udump directory, in the generated new trace file, the trace file has a script to generate the control file, run the following command to alter database backup controlfile to trace as '/oracle/app/oracle/admin/powerdes/pfile/control. SQL '; you can obtain the SQL command for creating the control file.
Run the following command to create a control file:
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/power000001.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 50 m blocksize 512,
GROUP 2'/home/oradata/powerdes/redo02.log 'SIZE 50 m blocksize 512,
GROUP 3 '/home/oradata/powerdes/redo03.log' SIZE 50 m blocksize 512
Character set ZHS16GBK;
2.3 recover data and open 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>
An error is reported when opening the file. open the file directly.
SQL> alter database open resetlogs
2;
Database altered.
SQL>
3. Then exp exports the table data and imp imports it to the production environment.
Export: exp system/manager @ myoracle file =/tmp/qd. dmp tables = (hr_resume_qingdao) system is the user name, manager is the password, and myoracle is the database name.
Transmit 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
Find 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'
Solution:
Dd if =/dev/zeroof =/home/oradata/powerdes/sysaux01.dbf bs = 8192 count = 1 seek = 340480
Bytes ----------------------------------------------------------------------------------------------------------------
<All Rights Reserved. This document can be reprinted, but the source address must be indicated by link. Otherwise, we will be held legally responsible.>
Original blog address: http://blog.itpub.net/26230597/viewspace-1484099/
Original Author: Huang Shan (mchdba)
Bytes ----------------------------------------------------------------------------------------------------------------