Shulou(Shulou.com)06/01 Report--

Manual full recovery

1. Full recovery is based on three levels

Recover database: all data files are corrupted, or most of the datafile is missing (large area loss)

Recover tablespace: non-critical tablespace corruption, some data files under the tablespace cannot access recover datafile: a single or a small number of data files are corrupted (if the system tablespace is corrupted, you can recover it under mount using recover datafile)

2. Views that can be viewed during the recovery process:

V$recover_file view the datafile that needs to be restored

V$recovery_log views the redo logs required by recover

V$archvied_log to view archived logs

3. Applicable scenarios

(1) recover database (all or most of the data files are corrupted under mount or open)

OS: use cp to restore damaged dbf (not necessarily all, v$recover_file records need to be restored)

SQLPLUS:

① recover database

② alter database open

(2) recover tablespace (corruption of non-critical data files for tablespaces, usually under open)

OS: use cp to restore all data files under this tablespace XXX

SQLPLUS:

① alter tablespace XXX offline

② recover tablespace XXX

③ alter tablespace XXX online

(3) recover datafile (single or several data files are corrupted, key files are carried out under mount, non-critical files are carried out under open)

The first situation

OS: use cp to restore related key data files (mount)

SQLPLUS:

① recover datafile 6,8

② alter database open

The second situation

OS: use cp to restore related non-critical data files (open)

SQLPLUS:

① alter database datafile 6,8 offline

② recover datafile 6,8

③ alter database datafile 6,8 online

Catalogue

Example 1: recover database

Example 2: recover tablespace

Example 3: recover datafile

Case 1: critical data files

Case 2: non-critical data file

Experimental environment:

Operating system: CentOS7.1

Database: Oracle 11.2.0.4

Example 1: recover database (media failure, loss of a large number of data files)

1. Simulated environment:

Create a seiang tablespace and create a table test under the scott user

SYS@seiang11g > create tablespace seiang datafile'/ u01 size size

Tablespace created.

SYS@seiang11g > conn scott

Enter password:

Connected.

SCOTT@seiang11g > create table test (id number,name varchar2 (10)) tablespace seiang

Table created.

SCOTT@seiang11g > insert into test values (1memewjq')

1 row created.

SCOTT@seiang11g > commit

Commit complete.

SCOTT@seiang11g > select * from test

ID NAME

1 wjq

SYS@seiang11g > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down

After shutting down the database cleanly, the operating system makes a complete cold backup to the Datafile in the database.

[oracle@seiang11g OraDB11g] $cp. / * / u01/app/oracle/UMAN_Backup/

[oracle@seiang11g OraDB11g] $ll / u01/app/oracle/UMAN_Backup/

Total 2123572

-rw-r- 1 oracle oinstall 9748480 Jul 25 11:53 control01.ctl

-rw-r- 1 oracle oinstall 363077632 Jul 25 11:54 example01.dbf

-rw-r- 1 oracle oinstall 52429312 Jul 25 11:54 redo01.log

-rw-r- 1 oracle oinstall 52429312 Jul 25 11:54 redo02.log

-rw-r- 1 oracle oinstall 52429312 Jul 25 11:54 redo03.log

-rw-r- 1 oracle oinstall 31465472 Jul 25 11:54 rman01.dbf

-rw-r- 1 oracle oinstall 20979712 Jul 25 11:54 seiang01.dbf

-rw-r- 1 oracle oinstall 671096832 Jul 25 11:54 sysaux01.dbf

-rw-r- 1 oracle oinstall 796925952 Jul 25 11:54 system01.dbf

-rw-r- 1 oracle oinstall 30416896 Jul 25 11:54 temp01.dbf

-rw-r- 1 oracle oinstall 110108672 Jul 25 11:54 undotbs01.dbf

-rw-r- 1 oracle oinstall 5251072 Jul 25 11:54 users01.dbf

Start the database

SYS@seiang11g > startup

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size 2252824 bytes

Variable Size 788533224 bytes

Database Buffers 452984832 bytes

Redo Buffers 8892416 bytes

Database mounted.

Database opened.

SYS@seiang11g > conn scott

Enter password:

Connected.

SCOTT@seiang11g > insert into test values (2data jq1'); / / pay attention to the submission of this piece of data

1 row created.

SCOTT@seiang11g > commit

Commit complete.

SCOTT@seiang11g > insert into test values (3authorwjq2'); / / Note that the data has not been submitted.

1 row created.

SCOTT@seiang11g > select * from test

ID NAME

1 wjq

2 wjq1

3 wjq2

View the current log and record the insertion of the second and third pieces of data in redo2

SYS@seiang11g > select group#,sequence#,members,status from v$log

GROUP# SEQUENCE# MEMBERS STATUS

--

1 31 1 INACTIVE

2 32 1 CURRENT

3 30 1 INACTIVE

Perform log switching

SYS@seiang11g > alter system switch logfile

System altered.

SYS@seiang11g > select group#,sequence#,members,status from v$log

GROUP# SEQUENCE# MEMBERS STATUS

--

1 31 1 INACTIVE

2 32 1 ACTIVE

3 33 1 CURRENT

SYS@seiang11g > conn scott

Enter password:

Connected.

SCOTT@seiang11g > insert into test values. / / Note that this record is also submitted.

1 row created.

SCOTT@seiang11g > select * from test

ID NAME

1 wjq

2 wjq1

3 wjq2

4 wjq3

2. Simulated media damage

Delete the data file when the database is open

[oracle@seiang11g OraDB11g] $rm * .dbf

[oracle@seiang11g OraDB11g] $ll

Total 163132

-rw-r- 1 oracle oinstall 9748480 Jul 25 12:06 control01.ctl

-rw-r- 1 oracle oinstall 52429312 Jul 25 11:56 redo01.log

-rw-r- 1 oracle oinstall 52429312 Jul 25 12:02 redo02.log

-rw-r- 1 oracle oinstall 52429312 Jul 25 12:06 redo03.log

Another session shuts down the database, and then restarts, the database can only be started to mount state, open Times error

SYS@seiang11g > startup

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size 2252824 bytes

Variable Size 788533224 bytes

Database Buffers 452984832 bytes

Redo Buffers 8892416 bytes

Database mounted.

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

ORA-01110: data file 1:'/ u01 *

SYS@seiang11g > select file#,error from v$recover_file

FILE# ERROR

--

1 FILE NOT FOUND

2 FILE NOT FOUND

3 FILE NOT FOUND

4 FILE NOT FOUND

5 FILE NOT FOUND

6 FILE NOT FOUND

7 FILE NOT FOUND

View the SCN recorded in the control file and data file header

SYS@seiang11g > select file#,checkpoint_change# from v$datafile

FILE# CHECKPOINT_CHANGE#

--

1 1501761

2 1501761

3 1501761

4 1501761

5 1501761

6 1501761

7 1501761

SYS@seiang11g > select file#,checkpoint_change# from v$datafile_header

FILE# CHECKPOINT_CHANGE#

--

1 0

2 0

3 0

4 0

5 0

6 0

7 0

Since there is no data file, the SCN of the data file header is 0

Restore lost data files from cold standby Datafile

[oracle@seiang11g OraDB11g] $cp / u01/app/oracle/UMAN_Backup/*.dbf. /

[oracle@seiang11g OraDB11g] $ll

Total 2123572

-rw-r- 1 oracle oinstall 9748480 Jul 25 12:15 control01.ctl

-rw-r- 1 oracle oinstall 363077632 Jul 25 12:13 example01.dbf

-rw-r- 1 oracle oinstall 52429312 Jul 25 11:56 redo01.log

-rw-r- 1 oracle oinstall 52429312 Jul 25 12:02 redo02.log

-rw-r- 1 oracle oinstall 52429312 Jul 25 12:07 redo03.log

-rw-r- 1 oracle oinstall 31465472 Jul 25 12:13 rman01.dbf

-rw-r- 1 oracle oinstall 20979712 Jul 25 12:13 seiang01.dbf

-rw-r- 1 oracle oinstall 671096832 Jul 25 12:14 sysaux01.dbf

-rw-r- 1 oracle oinstall 796925952 Jul 25 12:14 system01.dbf

-rw-r- 1 oracle oinstall 30416896 Jul 25 12:14 temp01.dbf

-rw-r- 1 oracle oinstall 110108672 Jul 25 12:14 undotbs01.dbf

-rw-r- 1 oracle oinstall 5251072 Jul 25 12:14 users01.dbf

Check the SCN of the control file and the data file header again, and find that the SCN of the data file header is smaller than the SCN recorded in the control file.

SYS@seiang11g > select file#,checkpoint_change# from v$datafile

FILE# CHECKPOINT_CHANGE#

--

1 1501761

2 1501761

3 1501761

4 1501761

5 1501761

6 1501761

7 1501761

SYS@seiang11g > select file#,checkpoint_change# from v$datafile_header

FILE# CHECKPOINT_CHANGE#

--

1 1501758

2 1501758

3 1501758

4 1501758

5 1501758

6 1501758

7 1501758

Perform a manual full recovery, and compare the SCN of the control file and the data file header. It is found that after the full recovery, the SCN recorded in the control file and the data file is the same.

SYS@seiang11g > recover database

Media recovery complete.

SYS@seiang11g > select file#,checkpoint_change# from v$datafile

FILE# CHECKPOINT_CHANGE#

--

1 1522474

2 1522474

3 1522474

4 1522474

5 1522474

6 1522474

7 1522474

SYS@seiang11g > select file#,checkpoint_change# from v$datafile_header

FILE# CHECKPOINT_CHANGE#

--

1 1522474

2 1522474

3 1522474

4 1522474

5 1522474

6 1522474

7 1522474

SYS@seiang11g > select * from v$recover_file

No rows selected

Open the database and verify it

SYS@seiang11g > alter database open

Database altered.

SYS@seiang11g > select * from scott.test

ID NAME

--

1 wjq

2 wjq1

3 wjq2

4 wjq3

Example 2: recover tablespace

Aiming at the damage recovery of non-critical tablespaces, the full recovery based on tablespaces is actually the recovery of the datafile under it. It is very practical to simulate this situation. Usually, the data file under a non-critical tablespace is damaged, but it does not cause Oracle to crash. We only need to do a separate online recovery operation for individual problematic tablespace, that is, the database as a whole is online, while the local tablespace is offline, and the database does not need shutdown.

1. Simulated environment

Create a table test1 under the scott user and insert the corresponding data

SCOTT@seiang11g > create table test1 (id number,name varchar2 (10)) tablespace seiang

Table created.

SCOTT@seiang11g >

SCOTT@seiang11g > insert into test1 values (100rewjq')

1 row created.

SCOTT@seiang11g > commit

Commit complete.

View current redo information

SYS@seiang11g > select group#,sequence#,members,status from v$log

GROUP# SEQUENCE# MEMBERS STATUS

--

1 34 1 CURRENT

2 32 1 INACTIVE

3 33 1 INACTIVE

Switch logs

SYS@seiang11g > alter system switch logfile

System altered.

SYS@seiang11g > select group#,sequence#,members,status from v$log

GROUP# SEQUENCE# MEMBERS STATUS

--

1 34 1 ACTIVE

2 35 1 CURRENT

3 33 1 INACTIVE

The following two inserted records were not submitted

SCOTT@seiang11g > insert into test1 values (200Japanese wjq2')

1 row created.

SCOTT@seiang11g > insert into test1 values (200Japanese wjq3')

1 row created.

SCOTT@seiang11g > select * from test1

ID NAME

--

100 wjq

200 wjq2

200 wjq3

2. Simulated tablespace damage

Delete the data files under the tablespace directly under the database open

SYS@seiang11g > host rm / u01/app/oracle/oradata/OraDB11g/seiang01.dbf

SYS@seiang11g > host ls-l / u01/app/oracle/oradata/OraDB11g

Total 2103124

-rw-r- 1 oracle oinstall 9781248 Jul 25 14:14 control01.ctl

-rw-r- 1 oracle oinstall 363077632 Jul 25 12:20 example01.dbf

-rw-r- 1 oracle oinstall 52429312 Jul 25 14:10 redo01.log

-rw-r- 1 oracle oinstall 52429312 Jul 25 14:14 redo02.log

-rw-r- 1 oracle oinstall 52429312 Jul 25 12:20 redo03.log

-rw-r- 1 oracle oinstall 31465472 Jul 25 12:20 rman01.dbf

-rw-r- 1 oracle oinstall 671096832 Jul 25 14:13 sysaux01.dbf

-rw-r- 1 oracle oinstall 796925952 Jul 25 14:13 system01.dbf

-rw-r- 1 oracle oinstall 30416896 Jul 25 12:20 temp01.dbf

-rw-r- 1 oracle oinstall 110108672 Jul 25 14:13 undotbs01.dbf

-rw-r- 1 oracle oinstall 5251072 Jul 25 12:20 users01.dbf

Clear the record of data buffer cache

SYS@seiang11g > alter system flush buffer_cache

System altered.

SCOTT@seiang11g > select * from test1

Select * from test1

*

ERROR at line 1:

ORA-03135: connection lost contact

Process ID: 5524

Session ID: 42 Serial number: 91

Restart the database and an error occurred during the database open

SYS@seiang11g > startup

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size 2252824 bytes

Variable Size 788533224 bytes

Database Buffers 452984832 bytes

Redo Buffers 8892416 bytes

Database mounted.

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

ORA-01110: data file 7:'/ u01 *

View the SCN of the control file and data file header

SYS@seiang11g > select file#,checkpoint_change# from v$datafile

FILE# CHECKPOINT_CHANGE#

--

1 1522477

2 1522477

3 1522477

4 1522477

5 1522477

6 1522477

7 1522477

SYS@seiang11g > select file#,checkpoint_change# from v$datafile_header

FILE# CHECKPOINT_CHANGE#

--

1 1527707

2 1527707

3 1527707

4 1527707

5 1527707

6 1527707

7 0

Missing data file 7 has no SCN

SYS@seiang11g > recover database

ORA-00279: change 1501758 generated at 07/25/2017 11:52:18 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/arch/arch_1_949237404_32.log

ORA-00280: change 1501758 for thread 1 is in sequence # 32

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto

Log applied.

Media recovery complete.

Open the database and verify that the SCN of the control file and the data file header are consistent

SYS@seiang11g > alter database open

Database altered.

SYS@seiang11g > select file#,checkpoint_change# from v$datafile

FILE# CHECKPOINT_CHANGE#

--

1 1548052

2 1548052

3 1548052

4 1548052

5 1548052

6 1548052

7 1548052

SYS@seiang11g > select file#,checkpoint_change# from v$datafile_header

FILE# CHECKPOINT_CHANGE#

--

1 1548052

2 1548052

3 1548052

4 1548052

5 1548052

6 1548052

7 1548052

SYS@seiang11g > select * from scott.test1

ID NAME

--

100 wjqs

The experiment found that two pieces of unsubmitted data were rolled back.

Example 3: recover datafile

Case 1: key data files are corrupted

1. Simulated environment

Unlike example 2, the simulation UNDO file is corrupted: because the UNDO data file is also a critical file, it can only be recovered in the mount state.

SCOTT@seiang11g > insert into test1 values (200wonjqgood')

1 row created.

SCOTT@seiang11g > commit

Commit complete.

SCOTT@seiang11g > select * from test1

ID NAME

--

100 wjq

200 wjqgood

SCOTT@seiang11g > select * from test1

ID NAME

--

100 wjq

200 wjqgood

Delete the data in test1, but it is not submitted, and the old value is recorded in UNDO

SYS@seiang11g > delete scott.test1

2 rows deleted.

Online backup of UNDO data files

SYS@seiang11g > host cp / u01/app/oracle/oradata/OraDB11g/undotbs01.dbf / u01/app/oracle/backup_Temp

SYS@seiang11g > host ls-l / u01/app/oracle/backup_Temp

Total 107528

-rw-r- 1 oracle oinstall 110108672 Jul 25 15:21 undotbs01.dbf

2. Analog UNDO data file is missing

After the backup is completed, the online UNDO data file

SYS@seiang11g > host rm / u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

SYS@seiang11g > host ls-l / u01/app/oracle/oradata/OraDB11g/

Total 2016084

-rw-r- 1 oracle oinstall 9781248 Jul 25 15:22 control01.ctl

-rw-r- 1 oracle oinstall 363077632 Jul 25 14:27 example01.dbf

-rw-r- 1 oracle oinstall 52429312 Jul 25 14:27 redo01.log

-rw-r- 1 oracle oinstall 52429312 Jul 25 14:27 redo02.log

-rw-r- 1 oracle oinstall 52429312 Jul 25 15:21 redo03.log

-rw-r- 1 oracle oinstall 31465472 Jul 25 14:27 rman01.dbf

-rw-r- 1 oracle oinstall 20979712 Jul 25 15:21 seiang01.dbf

-rw-r- 1 oracle oinstall 671096832 Jul 25 15:21 sysaux01.dbf

-rw-r- 1 oracle oinstall 796925952 Jul 25 15:20 system01.dbf

-rw-r- 1 oracle oinstall 30416896 Jul 25 12:20 temp01.dbf

-rw-r- 1 oracle oinstall 5251072 Jul 25 14:27 users01.dbf

Clean shut down the database and restart the database

SYS@seiang11g > shutdown abort

ORACLE instance shut down.

SYS@seiang11g > startup

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size 2252824 bytes

Variable Size 788533224 bytes

Database Buffers 452984832 bytes

Redo Buffers 8892416 bytes

Database mounted.

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

ORA-01110: data file 3:'/ u01 *

Restore UNDO data files from backup

SYS@seiang11g > host cp / u01/app/oracle/backup_Temp/undotbs01.dbf / u01/app/oracle/oradata/OraDB11g/

SYS@seiang11g > host ls-l / u01/app/oracle/oradata/OraDB11g/

Total 2123612

-rw-r- 1 oracle oinstall 9781248 Jul 25 15:26 control01.ctl

-rw-r- 1 oracle oinstall 363077632 Jul 25 14:27 example01.dbf

-rw-r- 1 oracle oinstall 52429312 Jul 25 14:27 redo01.log

-rw-r- 1 oracle oinstall 52429312 Jul 25 14:27 redo02.log

-rw-r- 1 oracle oinstall 52429312 Jul 25 15:22 redo03.log

-rw-r- 1 oracle oinstall 31465472 Jul 25 14:27 rman01.dbf

-rw-r- 1 oracle oinstall 20979712 Jul 25 15:21 seiang01.dbf

-rw-r- 1 oracle oinstall 671096832 Jul 25 15:23 sysaux01.dbf

-rw-r- 1 oracle oinstall 796925952 Jul 25 15:23 system01.dbf

-rw-r- 1 oracle oinstall 30416896 Jul 25 12:20 temp01.dbf

-rw-r- 1 oracle oinstall 110108672 Jul 25 15:26 undotbs01.dbf

-rw-r- 1 oracle oinstall 5251072 Jul 25 14:27 users01.dbf

Perform a restore operation

SYS@seiang11g > recover datafile 3

Media recovery complete.

After the restore operation is completed, the database is opened, the data rollback operation for the UNDO tablespace is completed, and the recovery is verified

SYS@seiang11g > alter database open

Database altered.

SYS@seiang11g > select * from scott.test1

ID NAME

--

100 wjq

200 wjqgood

Case 2: non-critical data files are corrupted

1. Simulated environment

Data files that simulate users and seiang table spaces are corrupted, and the data files of these two table spaces are non-critical

SYS@seiang11g > select FILE#,TS#,name,status from v$datafile

FILE# TS# NAME STATUS

-

1 0 / u01/app/oracle/oradata/OraDB11g/system01.dbf SYSTEM

2 1/ u01/app/oracle/oradata/OraDB11g/sysaux01.dbf ONLINE

3 2 / u01/app/oracle/oradata/OraDB11g/undotbs01.dbf ONLINE

4 4 / u01/app/oracle/oradata/OraDB11g/users01.dbf ONLINE

5 6 / u01/app/oracle/oradata/OraDB11g/example01.dbf ONLINE

6 7 / u01/app/oracle/oradata/OraDB11g/rman01.dbf ONLINE

7 8 / u01/app/oracle/oradata/OraDB11g/seiang01.dbf ONLINE

Backup the data files of these two tablespaces

SYS@seiang11g > host cp / u01/app/oracle/oradata/OraDB11g/users01.dbf / u01/app/oracle/backup_Temp

SYS@seiang11g > host cp / u01/app/oracle/oradata/OraDB11g/seiang01.dbf / u01/app/oracle/backup_Temp

SYS@seiang11g > host ls-l / u01/app/oracle/backup_Temp

Total 133144

-rw-r- 1 oracle oinstall 20979712 Jul 25 15:34 seiang01.dbf

-rw-r- 1 oracle oinstall 110108672 Jul 25 15:21 undotbs01.dbf

-rw-r- 1 oracle oinstall 5251072 Jul 25 15:34 users01.dbf

Create two tables under the scott user. Wjq1 belongs to the users table space and wjq2 belongs to the seiang table space.

SCOTT@seiang11g > create table wjq1 (id number)

Table created.

SCOTT@seiang11g > insert into wjq1 values

1 row created.

SCOTT@seiang11g > insert into wjq1 values

1 row created.

SCOTT@seiang11g > commit

Commit complete.

SCOTT@seiang11g > select * from wjq1

ID

-

one hundred and eleven

two hundred and twenty two

SCOTT@seiang11g > create table wjq2 (name varchar2 (10)) tablespace seiang

Table created.

SCOTT@seiang11g > insert into wjq2 values ('wjq100')

1 row created.

SCOTT@seiang11g > insert into wjq2 values ('seiang200')

1 row created.

SCOTT@seiang11g > commit

Commit complete.

SCOTT@seiang11g > select * from wjq2

NAME

-

Wjq100

Seiang200

SYS@seiang11g > select table_name,tablespace_name,status from dba_tables

2 where table_name in ('WJQ1','WJQ2')

TABLE_NAME TABLESPACE_NAME STATUS

-

WJQ1 USERS VALID

WJQ2 SEIANG VALID

2. Simulate the loss of data files corresponding to users and seiang.

SYS@seiang11g > host rm / u01/app/oracle/oradata/OraDB11g/users01.dbf

SYS@seiang11g > host rm / u01/app/oracle/oradata/OraDB11g/seiang01.dbf

SYS@seiang11g > host ls-l / u01/app/oracle/oradata/OraDB11g

Total 2097996

-rw-r- 1 oracle oinstall 9781248 Jul 25 15:44 control01.ctl

-rw-r- 1 oracle oinstall 363077632 Jul 25 15:27 example01.dbf

-rw-r- 1 oracle oinstall 52429312 Jul 25 15:44 redo01.log

-rw-r- 1 oracle oinstall 52429312 Jul 25 15:27 redo02.log

-rw-r- 1 oracle oinstall 52429312 Jul 25 15:27 redo03.log

-rw-r- 1 oracle oinstall 31465472 Jul 25 15:27 rman01.dbf

-rw-r- 1 oracle oinstall 671096832 Jul 25 15:44 sysaux01.dbf

-rw-r- 1 oracle oinstall 796925952 Jul 25 15:44 system01.dbf

-rw-r- 1 oracle oinstall 30416896 Jul 25 15:27 temp01.dbf

-rw-r- 1 oracle oinstall 110108672 Jul 25 15:44 undotbs01.dbf

Clear the record of data buffer cache

SYS@seiang11g > alter system flush buffer_cache

System altered.

SYS@seiang11g > select * from scott.wjq1

Select * from scott.wjq1

*

ERROR at line 1:

ORA-01116: error in opening database file 4

ORA-01110: data file 4:'/ u01 *

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SYS@seiang11g > select * from scott.wjq2

Select * from scott.wjq2

*

ERROR at line 1:

ORA-01116: error in opening database file 7

ORA-01110: data file 7:'/ u01 *

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Restore the media, restore the data files corresponding to users and seiang

SYS@seiang11g > host cp / u01/app/oracle/backup_Temp/users01.dbf / u01/app/oracle/oradata/OraDB11g

SYS@seiang11g > host cp / u01/app/oracle/backup_Temp/seiang01.dbf / u01/app/oracle/oradata/OraDB11g

SYS@seiang11g > host ls-l / u01/app/oracle/oradata/OraDB11g

Total 2123612

-rw-r- 1 oracle oinstall 9781248 Jul 25 15:51 control01.ctl

-rw-r- 1 oracle oinstall 363077632 Jul 25 15:27 example01.dbf

-rw-r- 1 oracle oinstall 52429312 Jul 25 15:51 redo01.log

-rw-r- 1 oracle oinstall 52429312 Jul 25 15:27 redo02.log

-rw-r- 1 oracle oinstall 52429312 Jul 25 15:27 redo03.log

-rw-r- 1 oracle oinstall 31465472 Jul 25 15:27 rman01.dbf

-rw-r- 1 oracle oinstall 20979712 Jul 25 15:51 seiang01.dbf

-rw-r- 1 oracle oinstall 671096832 Jul 25 15:48 sysaux01.dbf

-rw-r- 1 oracle oinstall 796925952 Jul 25 15:48 system01.dbf

-rw-r- 1 oracle oinstall 30416896 Jul 25 15:27 temp01.dbf

-rw-r- 1 oracle oinstall 110108672 Jul 25 15:48 undotbs01.dbf

-rw-r- 1 oracle oinstall 5251072 Jul 25 15:51 users01.dbf

Offline these two data files

SYS@seiang11g > alter database datafile 4 offline

Database altered.

SYS@seiang11g > alter database datafile 7 offline

Database altered.

SYS@seiang11g > select file_id,file_name,online_status from dba_data_files

FILE_ID FILE_NAME ONLINE_

-

4 / u01/app/oracle/oradata/OraDB11g/users01.dbf RECOVER

3 / u01/app/oracle/oradata/OraDB11g/undotbs01.dbf ONLINE

2 / u01/app/oracle/oradata/OraDB11g/sysaux01.dbf ONLINE

1/ u01/app/oracle/oradata/OraDB11g/system01.dbf SYSTEM

5 / u01/app/oracle/oradata/OraDB11g/example01.dbf ONLINE

6 / u01/app/oracle/oradata/OraDB11g/rman01.dbf ONLINE

7 / u01/app/oracle/oradata/OraDB11g/seiang01.dbf RECOVER

Restore these two data files

SYS@seiang11g > recover datafile 4 and 7

Media recovery complete.

SYS@seiang11g > select file_id,file_name,online_status from dba_data_files

FILE_ID FILE_NAME ONLINE_

-

4 / u01/app/oracle/oradata/OraDB11g/users01.dbf OFFLINE

3 / u01/app/oracle/oradata/OraDB11g/undotbs01.dbf ONLINE

2 / u01/app/oracle/oradata/OraDB11g/sysaux01.dbf ONLINE

1/ u01/app/oracle/oradata/OraDB11g/system01.dbf SYSTEM

5 / u01/app/oracle/oradata/OraDB11g/example01.dbf ONLINE

6 / u01/app/oracle/oradata/OraDB11g/rman01.dbf ONLINE

7 / u01/app/oracle/oradata/OraDB11g/seiang01.dbf OFFLINE

Online these two data files

SYS@seiang11g > alter database datafile 4 online

Database altered.

SYS@seiang11g > alter database datafile 7 online

Database altered.

SYS@seiang11g > select file_id,file_name,online_status from dba_data_files

FILE_ID FILE_NAME ONLINE_

-

4 / u01/app/oracle/oradata/OraDB11g/users01.dbf ONLINE

3 / u01/app/oracle/oradata/OraDB11g/undotbs01.dbf ONLINE

2 / u01/app/oracle/oradata/OraDB11g/sysaux01.dbf ONLINE

1/ u01/app/oracle/oradata/OraDB11g/system01.dbf SYSTEM

5 / u01/app/oracle/oradata/OraDB11g/example01.dbf ONLINE

6 / u01/app/oracle/oradata/OraDB11g/rman01.dbf ONLINE

7 / u01/app/oracle/oradata/OraDB11g/seiang01.dbf ONLINE

Verify the correctness of the recovery

SYS@seiang11g > select * from scott.wjq1

ID

-

one hundred and eleven

two hundred and twenty two

SYS@seiang11g > select * from scott.wjq2

NAME

-

Wjq100

Seiang200

Related links:

Oracle manual incomplete recovery (1): use the current control file

Author: SEian.G (hard practice changes in 72, but it is difficult to laugh at 81)

ITPUB: http://blog.itpub.net/31015730/

51CTO: http://seiang.blog.51cto.com/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.