Oracle备份与恢复案例(三)

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;

                        A

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

                        1

                        2

说明：

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

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

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

4.3.2RMAN备份方案

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

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

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: Release8.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

5>

6> #connect database

7> connect rcvcat rman/rman@back;

8> connect target internal/virpure;

9>

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;

                        A

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

                        1

                        2

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: Release8.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;

                        A

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

                        1

                        2

说明：

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

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

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

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

4.4不完全恢复案例

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

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

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

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

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;

                        A

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

                        1

                        2

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、