Oracle备份与恢复案例二

王朝oracle·作者佚名 2008-05-31

宽屏版字体: 小|中|大|超大

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

SQL select * from test;

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

说明：

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

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

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

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

4.2.2 RMAN备份方案

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

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

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.

SQLr

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

Fixed Size70924 bytes

Variable Size85487616 bytes

Database Buffers 16384000 bytes

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

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 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、关闭数据库，模拟丢失数据文件