未雨绸缪 - 一次 数据库的TSM 完全恢复的实验记录

环境:AIX5.3ML03+Oracle 10.2.0.2+TSM 5.2

[@more@]

- RMAN 配置 情况
注意:CONFIGURE CONTROLFILE AUTOBACKUP ON;

[testdb:oradev] /oradev1/home/rman> rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Tue May 22 12:54:50 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: testdb (DBID=3996427334)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oradev1/app/oracle/product/10.2.0/dbs/s
napcf_testdb.f'; # default

- 备份脚本:

run {
allocate channel t1 type 'sbt_tape' parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/cli
ent/oracle/bin64/tdpo.opt)';
allocate channel t2 type 'sbt_tape' parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/cli
ent/oracle/bin64/tdpo.opt)';
backup incremental level 0 database format 'dbfull_%t_%s_%p' diskratio=0 databas
e include current controlfile;
sql 'alter system archive log current';
backup format 'arch%t_%s_%p' diskratio=0 archivelog all delete input;
release channel t1;
release channel t2;
}

- 创建新TABLESPACE 和USER T 以及新TABLE T ,并且插入数据, 这样的状态和备份前的状态完全不一样了

[testdb:oradev] /oradev1/home> sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Tue May 22 11:44:41 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> create tablespace t
2 datafile '/devtestdbdata/oradata/testdb/test01.dbf' size 5m;

Tablespace created.

SQL> create user t identified by test
2 default tablespace t;

User created.

SQL> grant connect,resource,dba to t;

Grant succeeded.

SQL>
SQL> conn t/test
Connected.
SQL>
SQL>
SQL> create table t (x int);

Table created.

SQL> begin
2 for i in 1 .. 300000
loop
3 4 insert into t values(i);
5 end loop;
6 end;
/ 7

PL/SQL procedure successfully completed.

SQL> SQL> commit;

Commit complete.

SQL> select count(*) from t;

COUNT(*)
----------
300000

- 切换一次日志, 以查看RECOVER ARCH的恢复情况
SQL> alter system switch logfile;

System altered.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64
bit Production
With the Partitioning, OLAP and Data Mining options

[testdb:oradev] /devtestdbdata/oradata/testdb> ls
TBSRMAN.dbf control02.ctl redo02.log system01.dbf test01.dbf
UNDOTBS01.dbf control03.ctl redo03.log system02.dbf users01.dbf
control01.ctl redo01.log sysaux01.dbf tem01.dbf users02.dbf
- 删掉TBSRMAN.dbf 文件,模拟故障出现
[testdb:oradev] /devtestdbdata/oradata/testdb> rm TBSRMAN.dbf
[testdb:oradev] /devtestdbdata/oradata/testdb> sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Tue May 22 11:51:34 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 3758096384 bytes
Fixed Size 2075728 bytes
Variable Size 721421232 bytes
Database Buffers 3019898880 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
- 可以看到 7 文件需要恢复
SQL> select file#,error from v$recover_file;

FILE# ERROR
---------- -----------------------------------------------------------------
7 FILE NOT FOUND

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64
bit Production
With the Partitioning, OLAP and Data Mining options

[testdb:oradev] /devtestdbdata/oradata/testdb> sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Tue May 22 11:56:36 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown abort
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64
bit Production
With the Partitioning, OLAP and Data Mining options
[testdb:oradev] /devtestdbdata/oradata/testdb> rman target/

Recovery Manager: Release 10.2.0.2.0 - Production on Tue May 22 11:58:23 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database (not started)

RMAN> quit

Recovery Manager complete.
- 由于没有用到RMAN CATALOG 并且DB 在没启动状态,所以,RMAN 显示不可以用

[testdb:oradev] /devtestdbdata/oradata/testdb> sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Tue May 22 12:02:54 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 3758096384 bytes
Fixed Size 2075728 bytes
Variable Size 721421232 bytes
Database Buffers 3019898880 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64
bit Production
With the Partitioning, OLAP and Data Mining options

- 现在执行 如下恢复脚本
run {
allocate channel t1 type 'sbt_tape' parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/cli
ent/oracle/bin64/tdpo.opt)';
allocate channel t2 type 'sbt_tape' parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/cli
ent/oracle/bin64/tdpo.opt)';
restore database;
recover database;
sql'alter database open';
release channel t1;
release channel t2;
}
--会报错,说TEST01.DBF 在使用.不能创建,于是删掉TEST01.DBF ..模拟文件丢失

[testdb:oradev] /devtestdbdata/oradata/testdb> mv test01.dbf test01.dbf.bak

[testdb:oradev] /devtestdbdata/oradata/testdb> sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Tue May 22 12:07:50 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/devtestdbdata/oradata/testdb/test01.dbf'
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 3758096384 bytes
Fixed Size 2075728 bytes
Variable Size 721421232 bytes
Database Buffers 3019898880 bytes
Redo Buffers 14700544 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/devtestdbdata/oradata/testdb/test01.dbf'

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64
bit Production
With the Partitioning, OLAP and Data Mining options
bak_timefile restore_timefile scripts
rda rman timezone_patch
rda_4.6-061207.tar rman.tar tools
[testdb:oradev] /oradev1/home> cd rman
[testdb:oradev] /oradev1/home/rman> tail -f rest.log

Recovery Manager: Release 10.2.0.2.0 - Production on Tue May 22 12:08:34 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: testdb (DBID=3996427334, not open)

RMAN> run {
2> allocate channel t1 type 'sbt_tape' parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/
client/oracle/bin64/tdpo.opt)';
3> allocate channel t2 type 'sbt_tape' parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/
client/oracle/bin64/tdpo.opt)';
4> restore database;
5> recover database;
6> sql'alter database open';
7> release channel t1;
8> release channel t2;
9> }
10>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: sid=321 devtype=SBT_TAPE
channel t1: Tivoli Data Protection for Oracle: version 5.2.0.0
allocated channel: t2
channel t2: sid=320 devtype=SBT_TAPE
channel t2: Tivoli Data Protection for Oracle: version 5.2.0.0

Starting restore at 22-MAY-07

creating datafile fno=8 name=/devtestdbdata/oradata/testdb/test01.dbf
channel t1: starting datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /devtestdbdata/oradata/testdb/UNDOTBS01.dbf
restoring datafile 00004 to /devtestdbdata/oradata/testdb/users01.dbf
restoring datafile 00006 to /devtestdbdata/oradata/testdb/system02.dbf
restoring datafile 00007 to /devtestdbdata/oradata/testdb/TBSRMAN.dbf
channel t1: reading from backup piece suiiak7i_1_1
channel t1: restored backup piece 1
piece handle=suiiak7i_1_1 tag=TAG20070521T234906
channel t1: restore complete, elapsed time: 00:03:36
channel t1: starting datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /devtestdbdata/oradata/testdb/system01.dbf
restoring datafile 00003 to /devtestdbdata/oradata/testdb/sysaux01.dbf
restoring datafile 00005 to /devtestdbdata/oradata/testdb/users02.dbf
channel t1: reading from backup piece t1iiam4a_1_1
channel t1: restored backup piece 1
piece handle=t1iiam4a_1_1 tag=TAG20070521T234906
channel t1: restore complete, elapsed time: 00:02:47
Finished restore at 22-MAY-07

Starting recover at 22-MAY-07

starting media recovery

archive log thread 1 sequence 516 is already on disk as file /devtestdbarch1/arch
/1_516_617218793.dbf
archive log thread 1 sequence 517 is already on disk as file /devtestdbarch1/arch
/1_517_617218793.dbf
archive log thread 1 sequence 518 is already on disk as file /devtestdbarch1/arch
/1_518_617218793.dbf
archive log thread 1 sequence 519 is already on disk as file /devtestdbarch1/arch
/1_519_617218793.dbf
archive log thread 1 sequence 520 is already on disk as file /devtestdbarch1/arch
/1_520_617218793.dbf
archive log thread 1 sequence 521 is already on disk as file /devtestdbarch1/arch
/1_521_617218793.dbf
archive log filename=/devtestdbarch1/arch/1_516_617218793.dbf thread=1 sequence=5
16
archive log filename=/devtestdbarch1/arch/1_517_617218793.dbf thread=1 sequence=5
17
archive log filename=/devtestdbarch1/arch/1_518_617218793.dbf thread=1 sequence=5
18
archive log filename=/devtestdbarch1/arch/1_519_617218793.dbf thread=1 sequence=5
19
media recovery complete, elapsed time: 00:00:12
Finished recover at 22-MAY-07

sql statement: alter database open

released channel: t1

released channel: t2

Recovery Manager complete.

-- 验证数据
QL*Plus: Release 10.2.0.2.0 - Production on Tue May 22 12:16:23 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> conn t/test
Connected.
SQL> select count(*) from t;

COUNT(*)
----------
300000

SQL> select file#,error from v$recover_file;

no rows selected

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7318139/viewspace-915856/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7318139/viewspace-915856/