数据库的恢复测试-服务器专区

数据库的恢复测试

作者：网络　佚名编辑： IT168 2006-03-02 00:00

　　【IT168 服务器学院】测试条件:
　　1.有RMAN备份的数据(full backup);
　　2.新添加的数据文件丢失;
　　3.新的数据文件是没有备份的;
　　4.所有的控制文件都丢失;

　　要求:新添加的数据文件必须恢复回来;

　　以下是这个测试过程:
　　
　　在ts_test中添加了一个数据文件
　　SQL> alter tablespace ts_test
　　 2 add datafile ''/u02/oradata/DBAP01/ts_test02.dbf'' size 10M;

　　Tablespace altered.

　　Elapsed: 00:00:01.13

　　重新添加一个Tablespace,以便在其中添加Table.
　　SQL> create tablespace ts_testxx
　　 2 datafile ''/u02/oradata/DBAP01/ts_testxx01.dbf'' size 10m;
　　Tablespace created.

　　Elapsed: 00:00:01.12
　　SQL> create table scott.t3 as select * from dba_segments where 1=3;

　　Table created.

　　Elapsed: 00:00:00.34
　　SQL> insert into scott.t3
　　 2 nologging
　　 3 select * from dba_segments;

　　2352 rows created.

　　Elapsed: 00:00:10.78
　　SQL> insert /*+ APPEND*/ into scott.t3
　　 2 nologging
　　 3 select * from dba_segments;

　　2352 rows created.

　　Elapsed: 00:00:00.92
　　SQL> commit;

　　Commit complete.

　　Elapsed: 00:00:00.01
　　SQL> select count(*) from scott.t3;

　　 COUNT(*)
　　----------
　　 4704

　　Elapsed: 00:00:00.00
　　SQL> select tablespace_name from dba_segments where segment_name =''T3'';

　　TABLESPACE_NAME
　　------------------------------
　　USERS

　　Elapsed: 00:00:00.07
　　SQL> alter table scott.t3 move tablespace ts_testxx;

　　Table altered.

　　Elapsed: 00:00:01.10
　　SQL> l
　　 1* alter table scott.t3 move tablespace ts_testxx
　　SQL> select tablespace_name from dba_segments where segment_name=''T3'';

　　TABLESPACE_NAME
　　------------------------------
　　TS_TESTXX

　　Elapsed: 00:00:00.06

　　现在这个Tablespace中有一个T3的Table了，含有4704行数据；

　　接下来，在RMAN中关闭数据库：
　　RMAN> shutdown abort

　　Oracle instance shut down

　　接下来删除数据文件及所有的控制文件:
　　bash-2.03$ rm ts_testxx01.dbf
　　bash-2.03$ rm control01.ctl
　　bash-2.03$ rm control02.ctl
　　bash-2.03$ rm control03.ctl

　　启动到nomount状态
　　RMAN> startup nomount

　　connected to target database (not started)
　　Oracle instance started

　　Total System Global Area 322929752 bytes

　　Fixed Size                      730200 bytes
　　Variable Size                201326592 bytes
　　Database Buffers             117440512 bytes
　　Redo Buffers                   3432448 bytes

　　从备份中转存控制文件：
　　RMAN> restore controlfile from autobackup;

　　Starting restore at 02-MAR-06

　　allocated channel: ORA_DISK_1
　　channel ORA_DISK_1: sid=13 devtype=DISK
　　channel ORA_DISK_1: looking for autobackup on day: 20060302
　　channel ORA_DISK_1: autobackup found: c-3635722556-20060302-01
　　channel ORA_DISK_1: controlfile restore from autobackup complete
　　replicating controlfile
　　input filename=/u02/oradata/DBAP01/control01.ctl
　　output filename=/u02/oradata/DBAP01/control02.ctl
　　output filename=/u02/oradata/DBAP01/control03.ctl
　　Finished restore at 02-MAR-06

　　把数据库置于mount状态，此时的控制文件中没有包含新添加的数据文件ts_test02.dbf和ts_testxx.dbf。
　　RMAN> alter database mount;

　　database mounted

　　转存数据库，(这一步也许可以省略，现在还不能确定，等下再做测试)

　　RMAN> restore database;

　　Starting restore at 02-MAR-06

　　using channel ORA_DISK_1
　　skipping datafile 1; already restored to file /u02/oradata/DBAP01/system01.dbf
　　skipping datafile 17; already restored to file /u02/oradata/DBAP01/mesprod04.dbf
　　skipping datafile 21; already restored to file /u02/oradata/DBAP01/ts_rmantest.dbf
　　skipping datafile 11; already restored to file /u02/oradata/DBAP01/FWPRODMES.dbf
　　skipping datafile 12; already restored to file /u02/oradata/DBAP01/mesprod.dbf
　　skipping datafile 3; already restored to file /u02/oradata/DBAP01/ts_user01.dbf
　　skipping datafile 4; already restored to file /u02/oradata/DBAP01/ts_tools01.dbf
　　skipping datafile 15; already restored to file /u02/oradata/DBAP01/ts_quest2.dbf
　　skipping datafile 5; already restored to file /u02/oradata/DBAP01/TS_WIP1.dbf
　　skipping datafile 8; already restored to file /u02/oradata/DBAP01/mesprod02.dbf
　　skipping datafile 10; already restored to file /u02/oradata/DBAP01/ts_test01.dbf
　　skipping datafile 6; already restored to file /u02/oradata/DBAP01/TS_WIP_I.dbf
　　skipping datafile 16; already restored to file /u02/oradata/DBAP01/TS_WIP_I03.dbf
　　skipping datafile 20; already restored to file /u02/oradata/DBAP01/TS_WIP_I01.dbf
　　skipping datafile 7; already restored to file /u02/oradata/DBAP01/system02.dbf
　　skipping datafile 13; already restored to file /u02/oradata/DBAP01/TS_WIP_I02.dbf
　　skipping datafile 18; already restored to file /u02/oradata/DBAP01/mesprod05.dbf
　　skipping datafile 9; already restored to file /u02/oradata/DBAP01/TS_UNDO_01.dbf
　　skipping datafile 14; already restored to file /u02/oradata/DBAP01/ts_quest1.dbf
　　skipping datafile 19; already restored to file /u02/oradata/DBAP01/mesprod01.dbf
　　channel ORA_DISK_1: starting datafile backupset restore
　　channel ORA_DISK_1: specifying datafile(s) to restore from backup set
　　restoring datafile 00002 to /u02/oradata/DBAP01/undotbs01.dbf
　　channel ORA_DISK_1: restored backup piece 1
　　piece handle=/u01/rman_backup/df_DBAP01_134_1.bak tag=TAG20060302T084502 params=NULL
　　channel ORA_DISK_1: restore complete
　　Finished restore at 02-MAR-06

　　恢复数据库：
　　RMAN> recover database;

　　Starting recover at 02-MAR-06
　　using channel ORA_DISK_1

　　starting media recovery

　　archive log thread 1 sequence 1 is already on disk as file /u02/oradata/DBAP01/redo01.log
　　archive log filename=/u02/oradata/DBAP01/redo01.log thread=1 sequence=1
　　RMAN-00571: ===========================================================
　　RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
　　RMAN-00571: ===========================================================
　　RMAN-03002: failure of recover command at 03/02/2006 09:21:23
　　ORA-00283: recovery session canceled due to errors
　　RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile ''/u02/oradata/DBAP01/redo01.log''
　　ORA-00283: recovery session canceled due to errors
　　ORA-01244: unnamed datafile(s) added to controlfile by media recovery
　　ORA-01110: data file 22: ''/u02/oradata/DBAP01/ts_test02.dbf''

　　错误提示，没有数据文件：ORA-01110: data file 22: ''/u02/oradata/DBAP01/ts_test02.dbf''

　　打开DBA的身份连接数据库：

　　SQL>conn / as sysdba
　　Connected.
　　创建数据文件：
　　SQL> alter database create datafile 22 as ''/u02/oradata/DBAP01/ts_test02.dbf'';

　　Database altered.

　　Elapsed: 00:00:00.35

　　继续恢复数据库：
　　RMAN> recover database;

　　Starting recover at 02-MAR-06
　　using channel ORA_DISK_1

　　starting media recovery

　　archive log thread 1 sequence 1 is already on disk as file /u02/oradata/DBAP01/redo01.log
　　archive log filename=/u02/oradata/DBAP01/redo01.log thread=1 sequence=1
　　RMAN-00571: ===========================================================
　　RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
　　RMAN-00571: ===========================================================
　　RMAN-03002: failure of recover command at 03/02/2006 09:22:40
　　ORA-00283: recovery session canceled due to errors
　　RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile ''/u02/oradata/DBAP01/redo01.log''
　　ORA-00283: recovery session canceled due to errors
　　ORA-01244: unnamed datafile(s) added to controlfile by media recovery
　　ORA-01110: data file 23: ''/u02/oradata/DBAP01/ts_testxx01.dbf''

　　错误提示没有数据文件：ORA-01110: data file 23: ''/u02/oradata/DBAP01/ts_testxx01.dbf''，
　　这个数据文件是TS_TESTXX表空间的，在SQL*PLUS中添加这个数据文件：

　　SQL> alter database create datafile 23 as ''/u02/oradata/DBAP01/ts_testxx01.dbf'';

　　Database altered.

　　Elapsed: 00:00:00.38

　　继续恢复：
　　RMAN> recover database;

　　Starting recover at 02-MAR-06
　　using channel ORA_DISK_1

　　starting media recovery

　　archive log thread 1 sequence 1 is already on disk as file /u02/oradata/DBAP01/redo01.log
　　archive log filename=/u02/oradata/DBAP01/redo01.log thread=1 sequence=1
　　media recovery complete
　　Finished recover at 02-MAR-06

　　现在恢复完成，以resetlogs的方式打开数据库：
　　RMAN> alter database open resetlogs;

　　database opened

　　RMAN>

　　确认恢复是否成功:

　　SQL> select segment_name from dba_segments where tablespace_name=''TS_TESTXX'';

　　SEGMENT_NAME
　　---------------------------------------------------------------------------------
　　T3

　　Elapsed: 00:00:00.17
　　SQL> select count(*) from scott.t3;

　　 COUNT(*)
　　----------
　　 4704

　　Elapsed: 00:00:00.01
　　SQL>

　　至此,我们可以看到,数据库是恢复成功的.在这个过程中我们不需要确定数据文件的创建时间点，rman会自动恢复数据到创建数据文件的时间点.

　　下面测试在这个恢复过程中,是否需要restore database,如果不需要,则可能能说明即便是整个备份的数据都丢失了,也能恢复数据库,当然归档的及没归档的日志文件不能丢失.

关注我们