ORACLE 11G does not have backup file parameter files on the same machine through rman backup recovery was accidentally deleted data, 11 grman

Source: Internet
Author: User

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 ----------------------------------------------------------------------------------------------------------------

 

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.