Explore Oracle RMAN_07 restoration of single data file loss

The ultimate goal of backup is to better recover data and restore data. In the previous chapter, we have already finished the backup of RMAN, but in fact we have also discussed some complex issues.

The ultimate goal of backup is to better recover data and restore data. In the previous chapter, we have already finished the backup of RMAN, but in fact we have also discussed some complex issues.

The ultimate goal of backup is to better recover data and restore data. We have finished focusing on the previous chapter.RMANIn fact, we also talked about complex and complete recovery. Of course, in this section, we will talk in detail about database recovery in several different situations.

Related reading:

Exploring the RMAN_01 concept of Oracle

Explore the basic use of RMAN_02 in Oracle

Explore Oracle-based RMAN_03 non-consistent backup

Explore Oracle-based RMAN_04 non-consistent backup

Explore RMAN_05 Incremental Backup in Oracle

Exploring Oracle's RMAN_06 backup policy

Explore Oracle RMAN_07 restoration of single data file loss

Explore Oracle RMAN_07 recovery of whole business tablespace loss

Explore Oracle RMAN_07 disk corruption data loss recovery

Explore Oracle RMAN_07 recovery of all database files lost

Explore Oracle RMAN_07 redo log redu file loss recovery

Explore Oracle RMAN_07 parameter file loss recovery

Explore Oracle RMAN_07 control file loss recovery

Explore Oracle RMAN_07 system tablespace loss recovery

1,Data File loss recovery

1.1InWwlCreate a tablespace5Table and add data.

SQL> create table wwl01 (id number (3), namevarchar2 (10 ));

Table created.

SQL> insert into wwl01 values (1, 'wwl ');

1 row created.

SQL> insert into wwl01 values (2, 'wm ');

1 row created.

SQL> insert into wwl01 values (3, 'zq ');

1 row created.

SQL> insert into wwl01 values (4, 'wbq ');

1 row created.

SQL> insert into wwl01 values (5, 'wq ');

1 row created.

SQL> create table wwl02 as select * from wwl01;

Table created.

SQL> create table wwl03 as select * from wwl01;

Table created.

SQL> create table wwl04 as select * from wwl01;

Table created.

SQL> create table wwl05 as select * from wwl01;

Table created.

View the data in the table:

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

---------------------------

WWL01 TABLE

WWL02 TABLE

WWL03 TABLE

WWL04 TABLE

WWL05 TABLE

1.2Perform full-Database Backup

[Oracle @ wwldb ~] $ Rman target/

Recovery Manager: Release 10.2.0.1.0-Production on Fri Jun 2200:59:59 2012

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected to target database: WWL (DBID = 5520179)

RMAN> backup database;

1.3Simulate data loss and manually delete data filesWwl001.dbf

[Oracle @ wwldb WWL] $ rm-rfwwl001.dbf

1.4The database cannot be started again and an error is reported. The data file cannot be locked.5, ViewDbwr.

SQL> startup force;

ORACLEinstance started.

TotalSystem Global Area 285212672 bytes

FixedSize 1218968 bytes

VariableSize 92276328 bytes

DatabaseBuffers 184549376 bytes

RedoBuffers 7168000 bytes

Databasemounted.

ORA-01157: cannot identify/lock data file 5-see DBWR trace file

ORA-01110: data file 5: '/DBData/WWL/wwl001.dbf'

1.5Check the trace file and report the following error. The file cannot be found clearly:

Errors in file/DBSoft/admin/WWL/bdump/wwl_dbw0_29185.trc:

ORA-01157: Message 1157 not found; No messagefile for product = RDBMS, facility = ORA; arguments: [5]

ORA-01110: Message 1110 not found; No messagefile for product = RDBMS, facility = ORA; arguments: [5] [/DBData/WWL/wwl001.dbf]

ORA-27037: Message 27037 not found; No messagefile for product = RDBMS, facility = ORA

Linux Error: 2: No such file or directory

Additional information: 3

ORA-1157 signalled during: alter databaseopen...

From the above information, we can conclude that the database fault is caused by data files.Wwl001.dbfIf data files are lost or damaged due to database faults, the loss of data files will inevitably lead to the loss of database data. Fortunately, we have backed up the data before the loss, now let's use the previous backupWwl001.dbfWhen files are recovered, data files can be recovered in two ways: Online recovery without affecting other services of the database, and recovery of stopped services, for details, see:

1.6Restoration Method 1: zero downtime and online recovery

To start the recovery operation, perform the following seven steps:

RMAN> startup force mount;

Oracle instancestarted

Databasemounted

Total System GlobalArea 285212672 bytes

Fixed Size 1218968 bytes

VariableSize 92276328 bytes

DatabaseBuffers 184549376 bytes

RedoBuffers 7168000 bytes

SQL> alter database datafile '/DBData/WWL/wwl001.dbf' offline;

Database altered.

3,Enable Database

SQL> alter database open;

Database altered.

RMAN> restore datafile '/DBData/WWL/wwl001.dbf ';

Starting restore at 22-JUN-12

Using target database control fileinstead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: sid = 141 devtype = DISK

Channel ORA_DISK_1: startingdatafile backupset restore

Channel ORA_DISK_1: specifyingdatafile (s) to restore from backup set

Restoring datafile 00005 to/DBData/WWL/wwl001.dbf

Channel ORA_DISK_1: reading frombackup piece/DBBak/bak_WWL_06_22_0vne4ph6_1_1

Channel ORA_DISK_1: restored backuppiece 1

Piecehandle =/DBBak/bak_WWL_06_22_0vne4ph6_1_1 tag = TAG20120622T010021

Channel ORA_DISK_1: restorecomplete, elapsed time: 00:00:07

Finished restore at 22-JUN-12

[Oracle @ wwldb WWL] $ ll wwl *

-Rw-r ----- 1 oracle oinstall5241_2 Jun 22 wwl001.dbf

-Rw-r ----- 1 oracle oinstall 5251072 Jun 22 wwl002.dbf

-Rw-r ----- 1 oracle oinstall 5251072 Jun 22 wwl003.dbf

RMAN> recover datafile '/DBData/WWL/wwl001.dbf ';

Startingrecover at 22-JUN-12

Using targetdatabase control file instead of recovery catalog

Allocatedchannel: ORA_DISK_1

ChannelORA_DISK_1: sid = 144 devtype = DISK

Starting mediarecovery

Media recoverycomplete, elapsed time: 00:00:01

Finishedrecover at 22-JUN-12

RMAN>

6,Online data files

SQL> conn/as sysdba

Connected.

SQL> alter database datafile '/DBData/WWL/wwl001.dbf' online;

Database altered.

7,Verify whether the data is restored. You can see that all data is recovered.

SQL> conn wwl/wwl

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

-----------------------------------------------

WWL01 TABLE

WWL02 TABLE

WWL03 TABLE

WWL04 TABLE

WWL05 TABLE

SQL> select count (*) from tab;

COUNT (*)

----------

5

SQL>

1.7Method 2: offline recovery

Start the recovery operation in five steps:

SQL> startupforce mount;

ORACLE instancestarted.

Total SystemGlobal Area 285212672 bytes

Fixed Size 1218968 bytes

VariableSize 96470632 bytes

DatabaseBuffers 180355072 bytes

RedoBuffers 7168000 bytes

Databasemounted.

RMAN> restoredatafile '/DBData/WWL/wwl002.dbf'

2>;

Starting restoreat 22-JUN-12

Using targetdatabase control file instead of recovery catalog

Allocatedchannel: ORA_DISK_1

ChannelORA_DISK_1: sid = 156 devtype = DISK

ChannelORA_DISK_1: starting datafile backupset restore

ChannelORA_DISK_1: specifying datafile (s) to restore from backup set

Restoringdatafile 00006 to/DBData/WWL/wwl002.dbf

ChannelORA_DISK_1: reading from backup piece/DBBak/bak_WWL_06_22_0vne4ph6_1_1

ChannelORA_DISK_1: restored backup piece 1

Piecehandle =/DBBak/bak_WWL_06_22_0vne4ph6_1_1 tag = TAG20120622T010021

ChannelORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restoreat 22-JUN-12

RMAN> recover datafile '/DBData/WWL/wwl002.dbf ';

Starting recover at 22-JUN-12

Using channel ORA_DISK_1

Starting media recovery

Media recovery complete, elapsed time: 00:00:02

Finished recover at 22-JUN-12

RMAN>

4,Enable Database

SQL> conn/as sysdba

Connected.

SQL> alterdatabase open;

Databasealtered.

5,Verify data recovery

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

-----------------------------------------------

WWL01 TABLE

WWL02 TABLE

WWL03 TABLE

WWL04 TABLE

WWL05 TABLE

SQL> selectcount (*) from wwl01;

COUNT (*)

----------

5

SQL>

,