基于备份片的恢复测试

一、备份

1.0级备份

rman target /
crosscheck backup;
delete noprompt expired backup;
backup incremental level 0 as compressed backupset database format '/backup/full_%d_%T_%t_%s_%p';
sql 'alter system archive log current';
backup as compressed backupset format '/backup/arch_%d_%T_%t_%s_%p' archivelog all delete all input;
backup current controlfile format '/backup/ctl_%d_%T_%t_%s_%p';
crosscheck backup;
delete noprompt expired backup;

2.增量数据

 

22:06:33 DBAMONITOR@zytrac2>insert into kingzi values(1,1);

1 row created.

Elapsed: 00:00:00.20
22:06:46 DBAMONITOR@zytrac2>
22:06:46 DBAMONITOR@zytrac2>
22:06:46 DBAMONITOR@zytrac2>commit;

Commit complete.

zytrac2>select * from kingzi;

 

3.1级备份

crosscheck archivelog all;
delete noprompt expired archivelog all;
backup incremental level 1 as compressed backupset database format '/backup/inc_%d_%T_%t_%s_%p';
sql 'alter system archive log current';
backup as compressed backupset format '/backup/arch_%d_%T_%t_%s_%p' archivelog all delete all input;
backup current controlfile format '/backup/ctl_%d_%T_%t_%s_%p';

将备份传输至目标机器，进行异机恢复。

二、恢复

1.密码文件

export ORACLE_SID=huifu
orapwd file=$ORACLE_HOME/dbs/orapwhuifu password=oracle force=y entries=5

mkdir -p /backup/huifu/arch
mkdir -p /backup/huifu/controlfile/
mkdir -p /backup/huifu/datafile
chown -R oracle:oinstall /backup

2.参数文件

startup pfile='/picclife/app/oracle/product/11.2.0/db_1/dbs/pfilehuifu.ora';
vi pfilehuifu.ora

*.compatible='11.2.0.4.0'
*.control_files='/backup/huifu/controlfile/current.263.1058372225'
*.db_name='zytrac'

3.控制文件

restore controlfile from '/backup/ctl_ZYTRAC_20221120_1121292610_44_1';
alter database mount;
crosscheck backupset;

4.注册备份片

catalog backuppiece '/backup/arch_ZYTRAC_20221120_1121292251_39_1';
catalog backuppiece '/backup/arch_ZYTRAC_20221120_1121292602_43_1';
catalog backuppiece '/backup/full_ZYTRAC_20221120_1121292076_36_1';
catalog backuppiece '/backup/full_ZYTRAC_20221120_1121292172_37_1';
catalog backuppiece '/backup/inc_ZYTRAC_20221120_1121292460_41_1';
catalog backuppiece '/backup/inc_ZYTRAC_20221120_1121292536_42_1';
crosscheck backupset;
delete noprompt expired backupset;

5.修改控制文件中数据文件的路径，恢复数据文件

--必须放在一个run块中才可以
run {
set newname for datafile 1 to '/backup/huifu/datafile/system.256.1058372141';
set newname for datafile 2 to '/backup/huifu/datafile/sysaux.266.1058372141';
set newname for datafile 3 to '/backup/huifu/datafile/undotbs1.257.1058372141';
set newname for datafile 4 to '/backup/huifu/datafile/users.272.1058372141';
set newname for datafile 5 to '/backup/huifu/datafile/example.265.1058372261';
set newname for datafile 6 to '/backup/huifu/datafile/undotbs2.269.1058372579';
set newname for datafile 7 to '/backup/huifu/datafile/undotbs3.396.1060794123';
set newname for datafile 8 to '/backup/huifu/datafile/tbs_dbamonitor.276.1107907173';
restore database;
switch datafile all;
}

6.恢复数据库

select FILE# ,NAME from v$datafile;
recover database;

SQL> select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
5471651

SQL> select FILE#,NAME,CHECKPOINT_CHANGE# from v$datafile;

FILE# NAME CHECKPOINT_CHANGE#
---------- ---------------------------------------------------------------------------------------------------- ------------------
1 /backup/huifu/datafile/system.256.1058372141 5471651
2 /backup/huifu/datafile/sysaux.266.1058372141 5471651
3 /backup/huifu/datafile/undotbs1.257.1058372141 5471651
4 /backup/huifu/datafile/users.272.1058372141 5471651
5 /backup/huifu/datafile/example.265.1058372261 5471651
6 /backup/huifu/datafile/undotbs2.269.1058372579 5471651
7 /backup/huifu/datafile/undotbs3.396.1060794123 5471651
8 /backup/huifu/datafile/tbs_dbamonitor.276.1107907173 5471651

8 rows selected.

SQL> select FILE#,STATUS,RECOVER,CREATION_CHANGE#,CHECKPOINT_CHANGE# from v$datafile_header;

FILE# STATUS REC CREATION_CHANGE# CHECKPOINT_CHANGE#
---------- ------- --- ---------------- ------------------
1 ONLINE 7 5471041
2 ONLINE 1834 5471041
3 ONLINE 923328 5471041
4 ONLINE 16143 5471041
5 ONLINE 955060 5471041
6 ONLINE 983487 5471041
7 ONLINE 2304908 5471041
8 ONLINE 4869570 5471041

8 rows selected.

7.恢复到某个时间点

recover database until scn 5471651;

8.重命名redo日志文件路径

select 'alter database rename file '''||member||''' to ''/backup/huifu/datafile/redoXXX.log'';' from v$logfile;

alter database rename file '+DATA/zytrac/onlinelog/group_2.267.1058372229' to '/backup/huifu/datafile/redo01.log';
alter database rename file '+DATA/zytrac/onlinelog/group_2.268.1058372231' to '/backup/huifu/datafile/redo02.log';
alter database rename file '+DATA/zytrac/onlinelog/group_1.258.1058372229' to '/backup/huifu/datafile/redo03.log';
alter database rename file '+DATA/zytrac/onlinelog/group_1.259.1058372229' to '/backup/huifu/datafile/redo04.log';
alter database rename file '+DATA/zytrac/onlinelog/group_3.270.1058372821' to '/backup/huifu/datafile/redo05.log';
alter database rename file '+DATA/zytrac/onlinelog/group_3.351.1058372823' to '/backup/huifu/datafile/redo06.log';
alter database rename file '+DATA/zytrac/onlinelog/group_4.262.1058372823' to '/backup/huifu/datafile/redo07.log';
alter database rename file '+DATA/zytrac/onlinelog/group_4.261.1058372823' to '/backup/huifu/datafile/redo08.log';

9.重命名临时文件路径

select 'alter database rename file '''||name||''' to ''/backup/huifu/datafile/temp.dbf'';' from v$tempfile;

10.进行restlog起库，报错后，清空redo再次open;

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 4 of thread 2 is being cleared, operation not allowed
ORA-00312: online log 4 thread 2: '/backup/huifu/datafile/redo07.log'
ORA-00312: online log 4 thread 2: '/backup/huifu/datafile/redo08.log'

SQL> alter database clear logfile group 4;

SQL> alter database open resetlogs;

11.查询增量数据

SQL> select * from KINGZI

ID NAME
---------- ----
1 1