ORACLE备份&恢复案例--ORACLE BACKUP&RESTORE SCHEME

转贴 Oracle备份&恢复案例--ORACLE BACKUP&RESTORE SCHEME

第一章. 理解什么是数据库恢复

当我们使用一个数据库时，总希望数据库的内容是可靠的、正确的，但由于计算机系统的故障（硬件故障、软件故障、网络故障、进程故障和系统故障）影响数据库系统的操作，影响数据库中数据的正确性，甚至破坏数据库，使数据库中全部或部分数据丢失。因此当发生上述故障后，希望能重构这个完整的数据库，该处理称为数据库恢复。恢复过程大致可以分为复原(Restore)与恢复(Restore)过程。

数据库恢复可以分为以下两类：

1.1实例故障的一致性恢复

当实例意外地（如掉电、后台进程故障等）或预料地（发出SHUTDOUM ABORT语句）中止时出现实例故障，此时需要实例恢复。实例恢复将数据库恢复到故障之前的事务一致状态。假如在在线后备发现实例故障，则需介质恢复。在其它情况ORACLE在下次数据库起动时（对新实例装配和打开），自动地执行实例恢复。假如需要，从装配状态变为打开状态，自动地激发实例恢复，由下列处理：

（1）为了解恢复数据文件中没有记录的数据，进行向前滚。该数据记录在在线日志，包括对回滚段的内容恢复。

（2）回滚未提交的事务，按步1重新生成回滚段所指定的操作。

（3）释放在故障时正在处理事务所持有的资源。

（4）解决在故障时正经历一阶段提交的任何悬而未决的分布事务。

1.2介质故障或文件错误的不一致恢复

介质故障是当一个文件、一个文件的部分或磁盘不能读或不能写时出现的故障。

文件错误一般指意外的错误导致文件被删除或意外事故导致文件的不一致。

这种状态下的数据库都是不一致的，需要DBA手工来进行数据库的恢复，这种恢复有两种形式，决定于数据库运行的归档方式和备份方式。

（1）完全介质恢复可恢复全部丢失的修改。一般情况下需要有数据库的备份且数据库运行在归档状态下并且有可用归档日志时才可能。对于不同类型的错误，有不同类型的完全恢复可使用，其决定于毁坏文件和数据库的可用性。

（2）不完全介质恢复是在完全介质恢复不可能或不要求时进行的介质恢复。重构受损的数据库，使其恢复介质故障前或用户出错之前的一个事务一致性状态。不完全介质恢复有不同类型的使用，决定于需要不完全介质恢复的情况，有下列类型：基于撤消、基于时间和基于修改的不完全恢复。

基于撤消(CANCEL)恢复：在某种情况，不完全介质恢复必须被控制，DBA可撤消在指定点的操作。基于撤消的恢复地在一个或多个日志组（在线的或归档的）已被介质故障所破坏，不能用于恢复过程时使用，所以介质恢复必须控制，以致在使用最近的、未损的日志组于数据文件后中止恢复操作。

基于时间(TIME)和基于修改(SCN)的恢复：假如DBA希望恢复到过去的某个指定点，是一种理想的不完全介质恢复，一般发生在恢复到某个特定操作之前，恢复到如意外删除某个数据表之前。

第二章. 数据库恢复案例测试环境

2.1 数据库环境

以下的所有案例都是通过测试经过，环境为：

OS：windows 2000 Server

DB：Oracle 816

DBNAME：TEST

数据文件：

SQL> select file#,status,enabled,name from v$datafile;

FILE# STATUS ENABLED NAME

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

1 SYSTEM READ WRITE D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF

2 ONLINE READ WRITE D:\ORACLE\ORADATA\TEST\RBS01.DBF

3 ONLINE READ WRITE D:\ORACLE\ORADATA\TEST\USERS01.DBF

4 ONLINE READ WRITE D:\ORACLE\ORADATA\TEST\TEMP01.DBF

5 ONLINE READ WRITE D:\ORACLE\ORADATA\TEST\TOOLS01.DBF

6 ONLINE READ WRITE D:\ORACLE\ORADATA\TEST\INDX01.DBF

控制文件：

SQL> select * from v$controlfile;

STATUS NAME

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

D:\ORACLE\ORADATA\TEST\CONTROL01.CTL

D:\ORACLE\ORADATA\TEST\CONTROL02.CTL

D:\ORACLE\ORADATA\TEST\CONTROL03.CTL

联机日志：

SQL> select * from v$logfile;

GROUP# STATUS MEMBER

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

1 STALE D:\ORACLE\ORADATA\TEST\REDO01.LOG

2 D:\ORACLE\ORADATA\TEST\REDO02.LOG

3 STALE D:\ORACLE\ORADATA\TEST\REDO03.LOG

2.2 数据库备份脚本

冷备份脚本

rem script:coldbak.sql

rem creater:chenjiping

rem date:5.8.2003

rem descffline full backup database

--connect database

connect internal/passWord;

--shutdown database

shutdown immediate;

--Copy Data file

!xcopy d:\oracle\oradata\test\*.dbf d:\database/H/R;

--Copy Control file

!xcopy d:\oracle\oradata\test\*.ctl d:\database/H/R;

--Copy Log file

!xcopy d:\oracle\oradata\test\*.log d:\database/H/R;

--startup database

startup;

说明：

1、以上脚本在数据库关闭状态下备份数据库所有的数据文件，联机日志，控制文件（在一个目录下），假如成功备份，所有文件是一致的。

2、没有备份参数文件，参数文件可以另外备份，没有必要每次都备份，只需要在改变设置后备份一次。

3、假如以上命令没有成功依次执行，那么备份将是无效的，如连接数据库不成功，那么肯定关闭数据库也不成功，那么备份则无效

4、冷备份建议下人工干预下执行。

数据库OS热全备份脚本

rem script:hotbak.sql

rem creater:chenjiping

rem date:5.8.2003

rem desc:backup all database datafile in archive

--connect database

connect internal/password;

--archive

alter system archive log current;

--start

alter tablespace system begin backup;

!xcopy d:\oracle\oradata\test\system01.dbf d:\databak/H/R;

alter tablespace system end backup;

alter tablespace rbs begin backup;

!xcopy d:\oracle\oradata\test\rbs01.dbf d:\databak/H/R;

alter tablespace rbs end backup;

alter tablespace users begin backup;

!xcopy d:\oracle\oradata\test\users01.dbf d:\databak/H/R;

alter tablespace users end backup;

alter tablespace tools begin backup;

!xcopy d:\oracle\oradata\test\tools01.dbf d:\databak/H/R;

alter tablespace tools end backup;

alter tablespace indx begin backup;

!xcopy d:\oracle\oradata\test\indx01.dbf d:\databak/H/R;

alter tablespace indx end backup;

--end

--bak control file

--binary

alter database backup controlfile to 'd:\databak\controlbinbak.000';

--ascii

alter database backup controlfile to trace;

alter system archive log current;

说明：

1、热备份必须在数据库归档方式下才可以运行

2、以上脚本可以在数据库运行状态下备份数据库所有的数据文件（除了临时数据文件），没有必要备份联机日志。

3、归档日志至少需要一次完整备份之后的所有日志。

4、假如以上命令没有成功依次执行，那么备份也是无效的，如连接数据库不成功，那么备份则无效

RMAN备份只讲叙有恢复目录的情况，假如没有恢复目录，情形大致相似。以下是RMAN的热备份全备份的脚本：

# script:bakup.rcv

# creater:chenjiping

# date:5.8.2003

# desc:backup all database datafile in archive with rman

# connect database

connect rcvcat rman/rman@back;

connect target internal/virpure;

# start backup database

run{

allocate channel c1 type disk;

backup full tag 'dbfull' format 'd:\backup\full%u_%s_%p' database

include current controlfile;

sql 'alter system archive log current';

release channel c1;

}

# end

说明：

1、数据库必须运行在归档模式下

2、 RMAN将自动备份数据文件，运行可靠

3、归档日志另外备份处理，但至少需要保存一次备份来的日志

4、没有必要用RMAN做冷备份，效果不好

以上举例说明了数据库的恢复案例的测试环境与部分备份测试脚本，其它的备份脚本可以根据以上脚本演变而来或在案例中加以说明。

数据库的自动实例将不加以说明，这里只举例说明媒体错误或人为错误造成的恢复可能。

以上包括以下案例都是在WINDOWS+ORACLE816上测试验证的，在不同的操作系统与不同的数据库版本中略有差别。

第三章. 了解与恢复相关的信息

1、理解报警日志文件

报警日志文件一般记载了数据库的启动/关闭信息，归档信息，备份信息，恢复信息，常见错误信息，部分数据库修改记录等。一般令名规则为<SID>Alrt.log或Alrt<SID>.log，如我的测试数据库的报警日志文件的名称为testalrt.log。

报警日志文件的路径是根据初始化参数background_dump_dest来决定的，如在我的机器上，该参数值为 D:\Oracle\admin\test\bdump，那么，你就可以在该路径下找到该文件

2、后台进程跟踪文件

后台进程跟踪文件的路径与报警日志文件的路径一致，在某些情况下，你可以通过后台跟踪文件的信息了解更多的需要恢复的信息。如在数据库需要恢复的时候，报警日志文件中常有这样的语句：

Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:

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

通过提示的DBWR跟踪文件，可以查询到更具体的信息。

3、v$recover_file与v$recovery_log

这是两个动态性能视图，可以在mount下查看，通过这两个视图，你可以了解具体的需要恢复的数据文件与需要使用

第四章. 数据库恢复案例

4.1非归档模式下的备份与恢复

备份方案：采用OS冷备份

1.连接数据库并创建测试表

SQL*Plus: Release 8.1.6.0.0 - ProdUCtion on Tue May 6 13:46:32 2003

SQL> connect internal/password as sysdba;

Connected.

SQL> create table test(a int);

Table created

SQL> insert into test values(1);

1 row inserted

SQL> commit;

Commit complete

2.备份数据库

SQL> @coldbak.sql 或在DOS下 svrmgrl @coldbak.sql

3.再插入记录

SQL> insert into test values(2);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test;

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

4.关闭数据库

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

5.毁坏一个或多个数据文件，如删除user01.dbf

C:\>del D:\ORACLE\ORADATA\TEST\USERS01.DBF

模拟媒体毁坏

6.重新启动数据库，会发现如下错误

SQL> startup

ORACLE instance started.

Total System Global Area 102020364 bytes

Fixed Size 70924 bytes

Variable Size 85487616 bytes

Database Buffers 16384000 bytes

Redo Buffers 77824 bytes

Database mounted.

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

ORA-01110: data file 3: 'D:\ORACLE\ORADATA\TEST\USERS01.DBF'

在报警文件中，会有更具体的信息

Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:

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

ORA-01110: data file 3: 'D:\ORACLE\ORADATA\TEST\USERS01.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系统找不到指定的文件。

7.拷贝备份复原到原来位置(restore过程)

C:\>xcopy d:\database\*.* d:\oracle\oradata\test/H/R/S

8.打开数据库，检查数据

SQL> alter database open;

Database altered.

SQL> select * from test;

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

这里可以发现，数据库恢复成功，但在备份之后与崩溃之前的数据丢失了。

说明：

1、非归档模式下的恢复方案可选性很小，一般情况下只能有一种恢复方式，就是数据库的冷备份的完全恢复，仅仅需要拷贝原来的备份就可以(restore)，不需要recover。

2、这种情况下的恢复，可以完全恢复到备份的点上，但是可能是丢失数据的，在备份之后与崩溃之前的数据将全部丢失。

3、不管毁坏了多少数据文件或是联机日志或是控制文件，都可以通过这个办法恢复，因为这个恢复过程是Restore所有的冷备份文件，而这个备份点上的所有文件是一致的，与最新的数据库没有关系，就好比把数据库又放到了一个以前的“点”上。

4、对于非归档模式下，最好的办法就是采用OS的冷备份，建议不要用RMAN来作冷备份，效果不好，因为RMAN不备份联机日志，restore不能根本解决问题。

5、假如没有备份联机日志，如RMAN的备份，就需要利用不完全恢复(until cancel)的方法来重新创建联机日志文件

4.2归档模式下丢失或损坏一个数据文件

4.2.1 OS备份方案

在归档方式下损坏或丢失一个数据文件，假如存在相应的备份与该备份以来的归档日志，恢复还是比较简单的，可以作到尽量少的Down机时间，并能作到数据库的完全恢复。

1、连接数据库，创建测试表并插入记录

SQL*Plus: Release 8.1.6.0.0 - Production on Tue May 6 13:46:32 2003

SQL> connect internal/password as sysdba;

Connected.

SQL> create table test(a int) tablespace users;

Table created

SQL> insert into test values(1);

1 row inserted

SQL> commit;

Commit complete

2、备份数据库

SQL> @hotbak.sql 或在DOS下 svrmgrl @hotbak.sql

3、继续在测试表中插入记录

SQL> insert into test values(2);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test;

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

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

4、关闭数据库，模拟丢失数据文件

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down

C:\>del D:\ORACLE\ORADATA\TEST\USERS01.DBF

模拟媒体毁坏

5、启动数据库错误，脱机该数据文件

SQL> startup

ORACLE instance started.

Total System Global Area 102020364 bytes

Fixed Size 70924 bytes

Variable Size 85487616 bytes

Database Buffers 16384000 bytes

Redo Buffers 77824 bytes

Database mounted.

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

ORA-01110: data file 3: 'D:\ORACLE\ORADATA\TEST\USERS01.DBF'

还可以查看报警文件（见上一个恢复案例）或动态视图v$recover_file

如SQL> select * from v$recover_file;

FILE# ONLINE ERROR CHANGE# TIME

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

3 ONLINE 1013500 2003-05-07

脱机数据文件

SQL> alter database datafile 3 offline drop;

Database altered.

6、打开数据库，拷贝备份回来(restore)，恢复(recover)该数据文件，并联机

SQL> alter database open;

Database altered.

拷贝备份从备份处

copy d:\databak\ users01.dbf d:\oracle\oradata\test;

恢复该数据文件

SQL> recover datafile 3;

ORA-00279: change 1053698 generated at 05/07/2003 17:51:26 needed for thread 1

ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00304.ARC

ORA-00280: change 1053698 for thread 1 is in sequence #304

Specify log: {<RET>=suggested filename AUTO CANCEL}

AUTO

ORA-00279: change 1053701 generated at 05/07/2003 17:51:39 needed for thread 1

ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00305.ARC

ORA-00280: change 1053701 for thread 1 is in sequence #305

ORA-00278: log file 'D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00304.ARC' no

longer needed for this recovery

Log applied.

Media recovery complete.

恢复成功，联机该数据文件

SQL> alter database datafile 3 online;

Database altered.

7、检查数据库的数据（完全恢复）

SQL> select * from test;

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

说明：

1、采用热备份，需要运行在归档模式下，可以实现数据库的完全恢复，也就是说，从备份后到数据库崩溃时的数据都不会丢失。

2、可以采用全备份数据库的方式备份，对于非凡情况，也可以只备份特定的数据文件，如只备份用户表空间（一般情况下对于某些写非凡频繁的数据文件，可以单独加大备份频率）

3、假如在恢复过程中，发现损坏的是多个数据文件，即可以采用一个一个数据文件的恢复方法（第5步中需要对数据文件一一脱机，第6步中需要对数据文件分别恢复），也可以采用整个数据库的恢复方法。

4、假如是系统表空间的损坏，不能采用此方法

4.2.2 RMAN备份方案

RMAN也可以进行联机备份，而且备份与恢复方法将比OS备份更简单可靠。

1、连接数据库，创建测试表并插入记录

SQL*Plus: Release 8.1.6.0.0 - Production on Tue May 6 13:46:32 2003

SQL> connect internal/password as sysdba;

Connected.

SQL> create table test(a int) tablespace users;

Table created

SQL> insert into test values(1);

1 row inserted

SQL> commit;

Commit complete

2、备份数据库表空间users

C:\>rman

Recovery Manager: Release 8.1.6.0.0 - Production

RMAN> connect rcvcat rman/rman@back

RMAN-06008: connected to recovery catalog database

RMAN> connect target internal/virpure

RMAN-06005: connected to target database: TEST (DBID=1788174720)

RMAN> run{

2> allocate channel c1 type disk;

3> backup tag 'tsuser' format 'd:\backup\tsuser_%u_%s_%p'

4> tablespace users;

5> release channel c1;

6> }

RMAN-03022: compiling command: allocate

RMAN-03023: executing command: allocate

RMAN-08030: allocated channel: c1

RMAN-08500: channel c1: sid=16 devtype=DISK

RMAN-03022: compiling command: backup

RMAN-03025: performing implicit partial resync of recovery catalog

RMAN-03023: executing command: partial resync

RMAN-08003: starting partial resync of recovery catalog

RMAN-08005: partial resync complete

RMAN-03023: executing command: backup

RMAN-08008: channel c1: starting full datafile backupset

RMAN-08502: set_count=5 set_stamp=494177612 creation_time=16-MAY-03

RMAN-08010: channel c1: specifying datafile(s) in backupset

RMAN-08522: input datafile fno=00003 name=D:\ORACLE\ORADATA\TEST\USER01.DBF

RMAN-08013: channel c1: piece 1 created

RMAN-08503: piece handle=D:\BACKUP\TSUSER_05EN93AC_5_1 comment=NONE

RMAN-08525: backup set complete, elapsed time: 00:00:01

RMAN-03023: executing command: partial resync

RMAN-08003: starting partial resync of recovery catalog

RMAN-08005: partial resync complete

RMAN-03022: compiling command: release

RMAN-03023: executing command: release

RMAN-08031: released channel: c1

RMAN>

3、继续在测试表中插入记录

SQL> insert into test values(2);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test;

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

SQL> alter system switch logfile;

System altered.

SQL>r

1* alter system switch logfile;

System altered.

4、关闭数据库，模拟丢失数据文件

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down

C:\>del D:\ORACLE\ORADATA\TEST\USER01.DBF

5、启动数据库，检查错误

SQL> startup

ORACLE instance started.

Total System Global Area 102020364 bytes

Fixed Size 70924 bytes

Variable Size 85487616 bytes

Database Buffers 16384000 bytes

Redo Buffers 77824 bytes

Database mounted.

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

ORA-01110: data file 3: 'D:\ORACLE\ORADATA\TEST\USER01.DBF'

6、先打开数据库

SQL> alter database datafile 3 offline drop;

Database altered.

SQL> alter database open;

Database altered.

7、恢复该表空间

恢复脚本可以是恢复单个数据文件

run{

allocate channel c1 type disk;

restore datafile 3;

recover datafile 3;

sql 'alter database datafile 3 online';

release channel c1;

}

也可以是,恢复表空间

run{

allocate channel c1 type disk;

restore tablespace users;

recover tablespace users;

sql 'alter database datafile 3 online';

release channel c1;

}

过程如下：

C:\>rman

Recovery Manager: Release 8.1.6.0.0 - Production

RMAN> connect rcvcat rman/rman@back

RMAN-06008: connected to recovery catalog database

RMAN> connect target internal/virpure

RMAN-06005: connected to target database: TEST (DBID=1788174720)

RMAN> run{

2> allocate channel c1 type disk;

3> restore datafile 3;

4> recover datafile 3;

5> sql 'alter database datafile 3 online';

6> release channel c1;

7> }

RMAN-03022: compiling command: allocate

RMAN-03023: executing command: allocate

RMAN-08030: allocated channel: c1

RMAN-08500: channel c1: sid=13 devtype=DISK

RMAN-03022: compiling command: restore

RMAN-03022: compiling command: IRESTORE

RMAN-03023: executing command: IRESTORE

RMAN-08016: channel c1: starting datafile backupset restore

RMAN-08502: set_count=5 set_stamp=494177612 creation_time=16-MAY-03

RMAN-08089: channel c1: specifying datafile(s) to restore from backup set

RMAN-08523: restoring datafile 00003 to D:\ORACLE\ORADATA\TEST\USER01.DBF

RMAN-08023: channel c1: restored backup piece 1

RMAN-08511: piece handle=D:\BACKUP\TSUSER_05EN93AC_5_1 tag=TSUSER params=NULL

RMAN-08024: channel c1: restore complete

RMAN-03023: executing command: partial resync

RMAN-08003: starting partial resync of recovery catalog

RMAN-08005: partial resync complete

RMAN-03022: compiling command: recover

RMAN-03022: compiling command: recover(1)

RMAN-03022: compiling command: recover(2)

RMAN-03022: compiling command: recover(3)

RMAN-03023: executing command: recover(3)

RMAN-08054: starting media recovery

RMAN-03022: compiling command: recover(4)

RMAN-06050: archivelog thread 1 sequence 332 is already on disk as file D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00332.ARC

RMAN-06050: archivelog thread 1 sequence 333 is already on disk as file D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00333.ARC

RMAN-06050: archivelog thread 1 sequence 334 is already on disk as file D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00334.ARC

RMAN-03023: executing command: recover(4)

RMAN-08515: archivelog filename=D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00332.ARC thread=1 sequence=332

RMAN-08055: media recovery complete

RMAN-03022: compiling command: sql

RMAN-06162: sql statement: alter database datafile 3 online

RMAN-03023: executing command: sql

RMAN-03022: compiling command: release

RMAN-03023: executing command: release

RMAN-08031: released channel: c1

RMAN>

8、检查数据是否完整

SQL> alter database open;

Database altered.

SQL> select * from test;

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

说明：

1、RMAN也可以实现单个表空间或数据文件的恢复，恢复过程可以在mount下或open方式下，假如在open方式下恢复，可以减少down机时间

2、假如损坏的是一个数据文件，建议offline并在open方式下恢复

3、这里可以看到，RMAN进行数据文件与表空间恢复的时候，代码都比较简单，而且能保证备份与恢复的可靠性，所以建议采用RMAN的备份与恢复

4.3丢失多个数据文件，实现整个数据库的恢复

4.3.1 OS备份方案

OS备份归档模式下损坏（丢失）多个数据文件，进行整个数据库的恢复

1、连接数据库，创建测试表并插入记录

SQL*Plus: Release 8.1.6.0.0 - Production on Tue May 6 13:46:32 2003

SQL> connect internal/password as sysdba;

Connected.

SQL> create table test(a int);

Table created

SQL> insert into test values(1);

1 row inserted

SQL> commit;

Commit complete

2、备份数据库，备份除临时数据文件后的所数据文件

SQL> @hotbak.sql 或在DOS下 svrmgrl @hotbak.sql

3、继续在测试表中插入记录

SQL> insert into test values(2);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test;

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

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

4、关闭数据库，模拟丢失数据文件

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down

C:\>del D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF

C:\>del D:\ORACLE\ORADATA\TEST\INDX01.DBF

C:\>del D:\ORACLE\ORADATA\TEST\TOOLS01.DBF

C:\>del D:\ORACLE\ORADATA\TEST\RBS01.DBF

模拟媒体毁坏（这里删除多个数据文件）

5、启动数据库，检查错误

SQL> STARTUP

ORACLE instance started.

Total System Global Area 102020364 bytes

Fixed Size 70924 bytes

Variable Size 85487616 bytes

Database Buffers 16384000 bytes

Redo Buffers 77824 bytes

Database mounted.

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

ORA-01110: data file 1: 'D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF'

具体信息可以查看报警文件

ORA-1157 signalled during: ALTER DATABASE OPEN...

Thu May 08 09:39:36 2003

Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:

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

ORA-01110: data file 1: 'D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系统找不到指定的文件。

Thu May 08 09:39:36 2003

Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:

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

ORA-01110: data file 2: 'D:\ORACLE\ORADATA\TEST\RBS01.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系统找不到指定的文件。

Thu May 08 09:39:36 2003

Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:

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

ORA-01110: data file 5: 'D:\ORACLE\ORADATA\TEST\TOOLS01.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系统找不到指定的文件。

Thu May 08 09:39:36 2003

Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:

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

ORA-01110: data file 6: 'D:\ORACLE\ORADATA\TEST\INDX01.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系统找不到指定的文件。

通过查询v$recover_file可以看到

SQL> select * from v$recover_file;

FILE# ONLINE ERROR CHANGE# TIME

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

1 ONLINE FILE NOT FOUND 0

2 ONLINE FILE NOT FOUND 0

5 ONLINE FILE NOT FOUND 0

6 ONLINE FILE NOT FOUND 0

有四个数据文件需要恢复

6、拷贝备份回到原地点(restore)，开始恢复数据库(recover)

restore过程:

C:\>copy D:\DATABAK\SYSTEM01.DBF D:\ORACLE\ORADATA\TEST\

C:\>copy D:\DATABAK\TEST\INDX01.DBF D:\ORACLE\ORADATA\TEST\

C:\>copy D:\DATABAK\TEST\TOOLS01.DBF D:\ORACLE\ORADATA\TEST\

C:\>copy D:\DATABAK\TEST\RBS01.DBF.DBF D:\ORACLE\ORADATA\TEST\

Recover过程:

SQL> recover database;

ORA-00279: change 1073849 generated at 05/08/2003 08:58:35 needed for thread 1

ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00311.ARC

ORA-00280: change 1073849 for thread 1 is in sequence #311

Specify log: {<RET>=suggested filename AUTO CANCEL}

auto

ORA-00279: change 1073856 generated at 05/08/2003 09:03:27 needed for thread 1

ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00312.ARC

ORA-00280: change 1073856 for thread 1 is in sequence #312

ORA-00278: log file 'D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00311.ARC' no

longer needed for this recovery

ORA-00279: change 1073858 generated at 05/08/2003 09:11:43 needed for thread 1

ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00313.ARC

ORA-00280: change 1073858 for thread 1 is in sequence #313

ORA-00278: log file 'D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00312.ARC' no

longer needed for this recovery

ORA-00279: change 1073870 generated at 05/08/2003 09:11:46 needed for thread 1

ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00314.ARC

ORA-00280: change 1073870 for thread 1 is in sequence #314

ORA-00278: log file 'D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00313.ARC' no

longer needed for this recovery

Log applied.

Media recovery complete.

7、打开数据库，检查数据库的数据（完全恢复）

SQL> alter database open;

Database altered.

SQL> select * from test;

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

说明：

1、只要有备份与归档存在，就可以实现数据库的完全恢复（不丢失数据）

2、适合于丢失大量数据文件，或包含系统数据文件在内的数据库的恢复

3、恢复过程在mount下进行，假如恢复成功，再打开数据库，down机时间可能比较长一些。

4.3.2 RMAN备份方案

RMAN备份归档模式下损坏（丢失）多个数据文件，进行整个数据库的恢复

1、连接数据库，创建测试表并插入记录

SQL*Plus: Release 8.1.6.0.0 - Production on Tue May 6 13:46:32 2003

SQL> connect internal/password as sysdba;

Connected.

SQL> create table test(a int);

Table created

SQL> insert into test values(1);

1 row inserted

SQL> commit;

Commit complete

2、备份数据库

DOS下 C:>\ rman cmdfile=bakup.rcv msglog=backup.log;

以下是backup.log内容。

Recovery Manager: Release 8.1.6.0.0 - Production

RMAN> # script:bakup.rcv

2> # creater:chenjiping

3> # date:5.8.2003

4> # desc:backup all database datafile in archive with rman

6> #connect database

7> connect rcvcat rman/rman@back;

8> connect target internal/virpure;

10> #start backup database

11> run{

12> allocate channel c1 type disk;

13> backup full tag 'dbfull' format 'd:\backup\full%u_%s_%p' database

14> include current controlfile;

15> sql 'alter system archive log current';

16> release channel c1;

17> }

18> #end

19>

RMAN-06008: connected to recovery catalog database

RMAN-06005: connected to target database: TEST (DBID=1788174720)

RMAN-03022: compiling command: allocate

RMAN-03023: executing command: allocate

RMAN-08030: allocated channel: c1

RMAN-08500: channel c1: sid=15 devtype=DISK

RMAN-03022: compiling command: backup

RMAN-03023: executing command: backup

RMAN-08008: channel c1: starting full datafile backupset

RMAN-08502: set_count=4 set_stamp=494074368 creation_time=15-MAY-03

RMAN-08010: channel c1: specifying datafile(s) in backupset

RMAN-08522: input datafile fno=00002 name=D:\ORACLE\ORADATA\TEST\RBS01.DBF

RMAN-08522: input datafile fno=00001 name=D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF

RMAN-08011: including current controlfile in backupset

RMAN-08522: input datafile fno=00005 name=D:\ORACLE\ORADATA\TEST\TOOLS01.DBF

RMAN-08522: input datafile fno=00004 name=D:\ORACLE\ORADATA\TEST\TEMP01.DBF

RMAN-08522: input datafile fno=00006 name=D:\ORACLE\ORADATA\TEST\INDX01.DBF

RMAN-08522: input datafile fno=00003 name=D:\ORACLE\ORADATA\TEST\USER01.DBF

RMAN-08013: channel c1: piece 1 created

RMAN-08503: piece handle=D:\BACKUP\FULL04EN5UG0_4_1 comment=NONE

RMAN-08525: backup set complete, elapsed time: 00:01:16

RMAN-03023: executing command: partial resync

RMAN-08003: starting partial resync of recovery catalog

RMAN-08005: partial resync complete

RMAN-03022: compiling command: sql

RMAN-06162: sql statement: alter system archive log current

RMAN-03023: executing command: sql

RMAN-03022: compiling command: release

RMAN-03023: executing command: release

RMAN-08031: released channel: c1

Recovery Manager complete.

到这里表示备份成功。

3、继续在测试表中插入记录

SQL> insert into test values(2);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test;

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

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

4、关闭数据库，模拟丢失数据文件

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down

C:\>del D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF

C:\>del D:\ORACLE\ORADATA\TEST\INDX01.DBF

C:\>del D:\ORACLE\ORADATA\TEST\TOOLS01.DBF

C:\>del D:\ORACLE\ORADATA\TEST\RBS01.DBF

5、启动数据库，检查错误

SQL> STARTUP

ORACLE instance started.

Total System Global Area 102020364 bytes

Fixed Size 70924 bytes

Variable Size 85487616 bytes

Database Buffers 16384000 bytes

Redo Buffers 77824 bytes

Database mounted.

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

ORA-01110: data file 1: 'D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF'

查询v$recover_file

SQL> select * from v$recover_file;

FILE# ONLINE ERROR CHANGE# TIME

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

1 ONLINE FILE NOT FOUND 0

2 ONLINE FILE NOT FOUND 0

5 ONLINE FILE NOT FOUND 0

6 ONLINE FILE NOT FOUND 0

可以知道有四个数据文件需要恢复

6、利用RMAN进行恢复

C:\>rman

Recovery Manager: Release 8.1.6.0.0 - Production

RMAN> connect rcvcat rman/rman@back

RMAN-06008: connected to recovery catalog database

RMAN> connect target internal/virpure

RMAN-06005: connected to target database: TEST (DBID=1788174720)

RMAN> run{

2> allocate channel c1 type disk;

3> restore database;

4> recover database;

5> sql 'alter database open';

6> release channel c1;

7> }

RMAN-03022: compiling command: allocate

RMAN-03023: executing command: allocate

RMAN-08030: allocated channel: c1

RMAN-08500: channel c1: sid=17 devtype=DISK

RMAN-03022: compiling command: restore

RMAN-03025: performing implicit partial resync of recovery catalog

RMAN-03023: executing command: partial resync

RMAN-08003: starting partial resync of recovery catalog

RMAN-08005: partial resync complete

RMAN-03022: compiling command: IRESTORE

RMAN-03023: executing command: IRESTORE

RMAN-08016: channel c1: starting datafile backupset restore

RMAN-08502: set_count=4 set_stamp=494074368 creation_time=15-MAY-03

RMAN-08089: channel c1: specifying datafile(s) to restore from backup set

RMAN-08523: restoring datafile 00001 to D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF

RMAN-08523: restoring datafile 00002 to D:\ORACLE\ORADATA\TEST\RBS01.DBF

RMAN-08523: restoring datafile 00003 to D:\ORACLE\ORADATA\TEST\USER01.DBF

RMAN-08523: restoring datafile 00004 to D:\ORACLE\ORADATA\TEST\TEMP01.DBF

RMAN-08523: restoring datafile 00005 to D:\ORACLE\ORADATA\TEST\TOOLS01.DBF

RMAN-08523: restoring datafile 00006 to D:\ORACLE\ORADATA\TEST\INDX01.DBF

RMAN-08023: channel c1: restored backup piece 1

RMAN-08511: piece handle=D:\BACKUP\FULL04EN5UG0_4_1 tag=DBFULL params=NULL

RMAN-08024: channel c1: restore complete

RMAN-03023: executing command: partial resync

RMAN-08003: starting partial resync of recovery catalog

RMAN-08005: partial resync complete

RMAN-03022: compiling command: recover

RMAN-03022: compiling command: recover(1)

RMAN-03022: compiling command: recover(2)

RMAN-03022: compiling command: recover(3)

RMAN-03023: executing command: recover(3)

RMAN-08054: starting media recovery

RMAN-03022: compiling command: recover(4)

RMAN-06050: archivelog thread 1 sequence 327 is already on disk as file D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00327.ARC

RMAN-06050: archivelog thread 1 sequence 328 is already on disk as file D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00328.ARC

RMAN-06050: archivelog thread 1 sequence 329 is already on disk as file D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00329.ARC

RMAN-06050: archivelog thread 1 sequence 330 is already on disk as file D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00330.ARC

RMAN-03023: executing command: recover(4)

RMAN-08515: archivelog filename=D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00327.ARC thread=1 sequence=327

RMAN-08515: archivelog filename=D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00328.ARC thread=1 sequence=328

RMAN-08055: media recovery complete

RMAN-03022: compiling command: sql

RMAN-06162: sql statement: alter database open

RMAN-03023: executing command: sql

RMAN-03022: compiling command: release

RMAN-03023: executing command: release

RMAN-08031: released channel: c1

RMAN>

7、检查数据库的数据（完全恢复）

SQL> select * from test;

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

说明：

1、只要有备份与归档存在，RMAN也可以实现数据库的完全恢复（不丢失数据）

2、同OS备份数据库恢复，适合于丢失大量数据文件，或包含系统数据文件在内的数据库的恢复

3、目标数据库在mount下进行，假如恢复成功，再打开数据库。

4、RMAN的备份与恢复命令相对比较简单并可靠，建议有条件的话，都采用RMAN进行数据库的备份。

4.4 不完全恢复案例

4.4.1 OS备份下的基于时间的恢复

不完全恢复可以分为基于时间的恢复，基于改变的恢复与基于撤消的恢复，这里已基于时间的恢复为例子来说明不完全恢复过程。

基于时间的恢复可以不完全恢复到现在时间之前的某一个时间，对于某些误操作，如删除了一个数据表，可以在备用恢复环境上恢复到表的删除时间之前，然后把该表导出到正式环境，避免一个人为的错误。

1、连接数据库，创建测试表并插入记录

SQL*Plus: Release 8.1.6.0.0 - Production on Tue May 6 13:46:32 2003

SQL> connect internal/password as sysdba;

Connected.

SQL> create table test(a int);

Table created

SQL> insert into test values(1);

1 row inserted

SQL> commit;

Commit complete

2、备份数据库，这里最好备份所有的数据文件，包括临时数据文件

SQL> @hotbak.sql 或在DOS下 svrmgrl @hotbak.sql

或冷备份也可以

3、删除测试表，假定删除前的时间为T1，在删除之前，便于测试，继续插入数据并应用到归档。

SQL> insert into test values(2);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test;

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

SQL> alter system switch logfile;

Statement processed.

SQL> alter system switch logfile;

Statement processed.

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

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

2003-05-21 14:43:01

SQL> drop table test;

Table dropped.

4、预备恢复到时间点T1，找回删除的表，先关闭数据库

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

5、拷贝刚才备份的所有数据文件回来

C:\>copy D:\DATABAK\*.DBF D:\ORACLE\ORADATA\TEST\

6、启动到mount下

SQL> startup mount;

ORACLE instance started.

Total System Global Area 102020364 bytes

Fixed Size 70924 bytes

Variable Size 85487616 bytes

Database Buffers 16384000 bytes

Redo Buffers 77824 bytes

Database mounted.

7、开始不完全恢复数据库到T1时间

SQL> recover database until time '2003-05-21:14:43:01';

ORA-00279: change 30944 generated at 05/21/2003 14:40:06 needed for thread 1

ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00191.ARC

ORA-00280: change 30944 for thread 1 is in sequence #191

Specify log: {<RET>=suggested filename AUTO CANCEL}

auto

Log applied.

Media recovery complete.

8、打开数据库，检查数据

SQL> alter database open resetlogs;

Database altered.

SQL> select * from test;

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

说明：

1、不完全恢复最好备份所有的数据，冷备份亦可，因为恢复过程是从备份点往后恢复的，假如因为其中一个数据文件的时间戳(SCN)大于要恢复的时间点，那么恢复都是不可能成功的。

2、不完全恢复有三种方式，过程都一样，仅仅是recover命令有所不一样，这里用基于时间的恢复作为示例。

3、不完全恢复之后，都必须用resetlogs的方式打开数据库，建议马上再做一次全备份，因为resetlogs之后再用以前的备份恢复是很难了。

4、以上是在删除之前获得时间，但是实际应用中，很难知道删除之前的实际时间，但可以采用大致时间即可，或可以采用分析日志文件(logmnr)，取得精确的需要恢复的时间。

5、一般都是在测试机后备用机器上采用这种不完全恢复，恢复之后导出/导入被误删的表回生产系统

4.4.2 RMAN备份下的基于改变的恢复

以上用OS备份说明了一个基于时间的恢复，现在用RMAN说明一个基于改变的恢复

1、连接数据库，创建测试表并插入记录

SQL*Plus: Release 8.1.6.0.0 - Production on Tue May 6 13:46:32 2003

SQL> connect internal/password as sysdba;

Connected.

SQL> create table test(a int);

Table created

SQL> insert into test values(1);

1 row inserted

SQL> commit;

Commit complete

2、备份数据库

C:\>rman

Recovery Manager: Release 8.1.6.0.0 - Production

RMAN> connect rcvcat rman/rman@back

RMAN-06008: connected to recovery catalog database

RMAN> connect target internal/virpure

RMAN-06005: connected to target database: TEST (DBID=874705288)

RMAN> run{

2> allocate channel c1 type disk;

3> backup full tag 'dbfull' format 'd:\backup\full%u_%s_%p' database

4> include current controlfile;

5> sql 'alter system archive log current';

6> release channel c1;

7> }

RMAN-03022: compiling command: allocate

RMAN-03023: executing command: allocate

RMAN-08030: allocated channel: c1

RMAN-08500: channel c1: sid=12 devtype=DISK

RMAN-03022: compiling command: backup

RMAN-03023: executing command: backup

RMAN-08008: channel c1: starting full datafile backupset

RMAN-08502: set_count=1 set_stamp=494607834 creation_time=21-MAY-03

RMAN-08010: channel c1: specifying datafile(s) in backupset

RMAN-08522: input datafile fno=00001 name=D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF

RMAN-08011: including current controlfile in backupset

RMAN-08522: input datafile fno=00002 name=D:\ORACLE\ORADATA\TEST\RBS01.DBF

RMAN-08522: input datafile fno=00003 name=D:\ORACLE\ORADATA\TEST\USERS01.DBF

RMAN-08522: input datafile fno=00004 name=D:\ORACLE\ORADATA\TEST\TEMP01.DBF

RMAN-08522: input datafile fno=00005 name=D:\ORACLE\ORADATA\TEST\TOOLS01.DBF

RMAN-08522: input datafile fno=00006 name=D:\ORACLE\ORADATA\TEST\INDX01.DBF

RMAN-08013: channel c1: piece 1 created

RMAN-08503: piece handle=D:\BACKUP\FULL01ENM7EQ_1_1 comment=NONE

RMAN-08525: backup set complete, elapsed time: 00:00:16

RMAN-03023: executing command: partial resync

RMAN-08003: starting partial resync of recovery catalog

RMAN-08005: partial resync complete

RMAN-03022: compiling command: sql

RMAN-06162: sql statement: alter system archive log current

RMAN-03023: executing command: sql

RMAN-03022: compiling command: release

RMAN-03023: executing command: release

RMAN-08031: released channel: c1

RMAN>

3、删除测试表，在删除之前，便于测试，继续插入数据并应用到归档，并获取删除前的scn号。

SQL> insert into test values(2);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test;

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

SQL> alter system switch logfile;

Statement processed.

SQL> alter system switch logfile;

Statement processed.

SQL> select max(ktuxescnw * power(2, 32) + ktuxescnb) scn from x$ktuxe;

SCN

----------

31014

SQL> drop table test;

Table dropped.

4、预备恢复到SCN 31014，先关闭数据库，然后启动到mount下

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 53126412 bytes

Fixed Size 70924 bytes

Variable Size 26763264 bytes

Database Buffers 26214400 bytes

Redo Buffers 77824 bytes

Database mounted.

5、开始恢复到改变点SCN 31014

RMAN> run{

2> allocate channel c1 type disk;

3> restore database;

4> recover database until scn 31014;

5> sql 'ALTER DATABASE OPEN RESETLOGS';

6> release channel c1;

7> }

RMAN-03022: compiling command: allocate

RMAN-03023: executing command: allocate

RMAN-08030: allocated channel: c1

RMAN-08500: channel c1: sid=10 devtype=DISK

RMAN-03022: compiling command: restore

RMAN-03022: compiling command: IRESTORE

RMAN-03023: executing command: IRESTORE

RMAN-08016: channel c1: starting datafile backupset restore

RMAN-08502: set_count=1 set_stamp=494613682 creation_time=21-MAY-03

RMAN-08089: channel c1: specifying datafile(s) to restore from backup set

RMAN-08523: restoring datafile 00001 to D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF

RMAN-08523: restoring datafile 00002 to D:\ORACLE\ORADATA\TEST\RBS01.DBF

RMAN-08523: restoring datafile 00003 to D:\ORACLE\ORADATA\TEST\USERS01.DBF

RMAN-08523: restoring datafile 00004 to D:\ORACLE\ORADATA\TEST\TEMP01.DBF

RMAN-08523: restoring datafile 00005 to D:\ORACLE\ORADATA\TEST\TOOLS01.DBF

RMAN-08523: restoring datafile 00006 to D:\ORACLE\ORADATA\TEST\INDX01.DBF

RMAN-08023: channel c1: restored backup piece 1

RMAN-08511: piece handle=D:\BACKUP\FULL01ENMD5I_1_1 tag=DBFULL params=NULL

RMAN-08024: channel c1: restore complete

RMAN-03023: executing command: partial resync

RMAN-08003: starting partial resync of recovery catalog

RMAN-08005: partial resync complete

RMAN-03022: compiling command: recover

RMAN-03022: compiling command: recover(1)

RMAN-03022: compiling command: recover(2)

RMAN-03022: compiling command: recover(3)

RMAN-03023: executing command: recover(3)

RMAN-08054: starting media recovery

RMAN-03022: compiling command: recover(4)

RMAN-06050: archivelog thread 1 sequence 191 is already on disk as file D:\ORACL

E\ORADATA\TEST\ARCHIVE\TESTT001S00191.ARC

RMAN-06050: archivelog thread 1 sequence 192 is already on disk as file D:\ORACL

E\ORADATA\TEST\ARCHIVE\TESTT001S00192.ARC

RMAN-03023: executing command: recover(4)

RMAN-08515: archivelog filename=D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00191.AR

C thread=1 sequence=191

RMAN-08515: archivelog filename=D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00192.AR

C thread=1 sequence=192

RMAN-08055: media recovery complete

RMAN-03022: compiling command: sql

RMAN-06162: sql statement: ALTER DATABASE OPEN RESETLOGS

RMAN-03023: executing command: sql

RMAN-03022: compiling command: release

RMAN-03023: executing command: release

RMAN-08031: released channel: c1

6、检查数据

Database altered.

SQL> select * from test;

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

可以看到，表依然存在

说明：

1、RMAN也可以实现不完全恢复，方法比OS备份恢复的方法更简单可靠

2、RMAN可以基于时间，基于改变与基于日志序列的不完全恢复，基于日志序列的恢复可以指定恢复到哪个日志序列，如

run {

allocate channel ch1 type disk;

allocate channel ch2 type 'sBT_tape';

set until logseq 1234 thread 1;

restore controlfile to '$ORACLE_HOME/dbs/cf1.f' ;

replicate controlfile from '$ORACLE_HOME/dbs/cf1.f';

alter database mount;

restore database;

recover database;

sql "ALTER DATABASE OPEN RESETLOGS";

}

3、与所有的不完全恢复一样，必须在mount下，restore所有备份数据文件，需要resetlogs

4、基于改变的恢复比基于时间的恢复更可靠，但是可能也更复杂，需要知道需要恢复到哪一个改变号(SCN)，在正常生产中，获取SCN的办法其实也有很多，如查询数据库字典表(V$archived_log or v$log_history)，或分析归档与联机日志(logmnr)等。

第五章其它恢复案例

5.1 损坏联机日志的恢复方法

5.1.1 损坏非当前联机日志

大家都清楚，联机日志分为当前联机日志和非当前联机日志，非当前联机日志的损坏是比较简单的，一般通过clear命令就可以解决问题。

1、启动数据库，碰到ORA-00312 or ORA-00313错误，如

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'

从这里我们知道日志组1的数据文件损坏了

从报警文件可以看到更具体的信息

2、查看V$log视图

SQL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARCHIVED STATUS

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

1 1 YES INACTIVE

2 2 YES INACTIVE

3 3 NO CURRENT

可以知道，该组是非当前状态，而且已经归档。

3、用CLEAR命令重建该日志文件

SQL>alter database clear logfile group 1;

假如是该日志组还没有归档，则需要用

SQL>alter database clear unarchived logfile group 1;

4、打开数据库，重新备份数据库

SQL>alter database open;

说明：

1、假如损坏的是非当前的联机日志文件，一般只需要clear就可以重建该日志文件，但是假如该数据库处于归档状态但该日志还没有归档，就需要强行clear。

2、建议clear，非凡是强行clear后作一次数据库的全备份。

3、此方法适用于归档与非归档数据库

5.1.2 损坏当前联机日志

归档模式下当前日志的损坏有两种情况，

一、是数据库是正常关闭，日志文件中没有未决的事务需要实例恢复，当前日志组的损坏就可以直接用alter database clear unarchived logfile group n来重建。

二、是日志组中有活动的事务，数据库需要媒体恢复，日志组需要用来同步，有两种补救办法

A. 最好的办法就是通过不完全恢复，可以保证数据库的一致性，但是这种办法要求在归档方式下，并且有可用的备份

B. 通过强制性恢复，但是可能导致数据库不一致。

下面分别用来说明这两种恢复方法

5.1.2.1 通过备份来恢复

1、打开数据库，会碰到一个类似的错误

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系统找不到指定的文件

2、查看V$log，发现是当前日志

SQL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARCHIVED STATUS

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

1 1 NO CURRENT

2 2 YES INACTIVE

3 3 YES INACTIVE

3、发现clear不成功

SQL> alter database clear unarchived logfile group 1;

alter database clear unarchived logfile group 1

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of thread 1

ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'

4、拷贝有效的数据库的全备份，并不完全恢复数据库

可以采用获取最近的SCN的办法用until scn恢复或用until cnacel恢复

recover database until cancel

先选择auto，尽量恢复可以利用的归档日志，然后重新

recover database until cancel

这次输入cancel，完成不完全恢复，也就是说恢复两次。

如：

SQL> recover database until cancel;

Auto

……

SQL> recover database until cancel;

Cancel;

5、利用alter database open resetlogs打开数据库

说明：

1、这种办法恢复的数据库是一致的不完全恢复，会丢失当前联机日志中的事务数据

2、这种方法适合于归档数据库并且有可用的数据库全备份。

3、恢复成功之后，记得再做一次数据库的全备份。

4、建议联机日志文件一定要实现镜相在不同的磁盘上，避免这种情况的发生，因为任何数据的丢失对于生产来说都是不容许的。

5.1.2.2 假如没有备份，进行强制性恢复

1、打开数据库，会碰到一个类似的错误

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: