异机恢复测试
--补充
如果使用DG备份,搭建新测试环境注意,恢复控制文件角色配置为主库,否则恢复为备库角色,其余无特殊注意。
restore primary controlfile from 'STANDBY CONTROLFILE';
使用此方式:异机恢复,类似手工建库:区别在于,数据之前是有的,使用的是备份代替脚本,控制文件也是使用之前的备份
异机恢复应用场景:A:几十个G的测试库,需要将测试数据完全复制,迁移到另一个库,使用RMAN与数据泵/exp相比,具有明显优势,使用此场景快速搭建
B:灾备演练:使用备份,通过异机恢复的方式,验证备份的可用性
备份区分:A全备传输+归档日志传输+控制文件+参数文件+密码口令文件
B全库0级备份+增量备份----
本次实验测试:A/B A-使用0级全备:类似全库备份方式,异机恢复
B-A实验成功后,删除所有数据及备份信息,重新再来,使用全库备份+1级增量备份测试
异机恢复流程:
源端:
实验环境:10.2.0.4
全库备份:参数文件-控制文件-数据文件-归档日志文件
传输: 备份信息从源端- 拷贝至 - 目标端
目标端:
创建准备:创建相应目录:密码文件:参数文件修改名称
参数文件修改:
使用参数文件,启动到Nomount状态
RMAN: 使用备份的控制文件,restore恢复至,参数文件指定的控制文件名称,路径
RMAN:注册备份片:数据文件、归档日志文件注册
RMAN:restore 应用备份,还原数据文件, set修改数据文件目录结构
RMAN: recover 应用日志恢复:open打开数据库
测试:A
#开启块跟踪,本次增量备份
#开启归档模式:
archive log list
Database log mode No Archive Mode
[oracle@jiu ~]$ mkdir -p ./rman/arch
[oracle@jiu ~]$ mkdir -p ./rman/data
SQL> alter system set log_archive_dest_1="location=/home/oracle/rman/arch";
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
#开启块跟踪
SQL> select status,filename from v$block_change_tracking; DISABLED
[oracle@jiu ~]$ mkdir ./rman/block_trc
SQL> alter database enable block change tracking using file '/home/oracle/rman/block_trc/blk.trc';
#备份指令:
RMAN> run{
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter system switch logfile';
crosscheck archivelog all;
delete noprompt expired archivelog all;
backup incremental level 0 database format '/home/oracle/rman/data/%U.bak' include current controlfile plus archivelog format '/home/oracle/rman/arch/%U.bk';
}
#查询备份的控制文件:备份片
RMAN> list backup of controlfile;
/home/oracle/rman/data/05sv2o0d_1_1.bak
prkc-1024
#传输:节省时间:
#data
[oracle@jiu data]$ scp * 192.168.20.55:/home/oracle/rman/data/.
#arch
[oracle@jiu arch]$ scp *.bk 192.168.20.55:/home/oracle/rman/arch/.
#创建最新的pfile文件[如果目标端,选择不同sid,需要修改名称]
SQL> create pfile from spfile;
$ cd $ORACLE_HOME/dbs
$ scp orapwjiu 192.168.20.55:/u02/app/oracle/10.2.0.4/dbhome_1/dbs/.
$ scp initjiu.ora 192.168.20.55:/u02/app/oracle/10.2.0.4/dbhome_1/dbs/.
#目标端:
#创建目录结构,存储源端备份文件
mkdir -p ./rman/arch
mkdir -p ./rman/data
#修改参数文件:
#修改前
*.audit_file_dest='/picclife/app/oracle/admin/jiu/adump'
#修改A:OACLE_BASE不同,更好:替换
%s#/picclife#/u02#g
#替换后
audit_file_dest='/u02/app/oracle/admin/jiu/adump'
*.background_dump_dest='/u02/app/oracle/admin/jiu/bdump'
*.control_files='/u02/app/oracle/oradata/jiu/control01.ctl','
/u02/app/oracle/oradata/jiu/control02.ctl','/u02/app/oracle/o
radata/jiu/control03.ctl'
*.core_dump_dest='/u02/app/oracle/admin/jiu/cdump'
#*.local_listener='LISTENER_JIU'
*.db_name='ceshi'
*.db_recovery_file_dest='/u02/app/oracle/flash_recovery_area
*.log_archive_dest_1='location=/home/oracle/rman/arch'
*.user_dump_dest='/u02/app/oracle/admin/jiu/udump'
创建路径:以上列举的所有路径
mkdir -p /u02/app/oracle/admin/jiu/adump
#启动到nomount阶段:
SQL> startup nomount;
#源库端:事先查询,启动到Mount状态
RMAN> restore controlfile from '/home/oracle/rman/data/05sv2o0d_1_1.bak';
SQL> alter database mount;
#由于两端环境目录不同,需要修改日志文件 and datafile文件的路径文件名称
SQL> select name from v$datafile
union
select member from v$logfile;
/picclife/app/oracle/oradata/jiu/redo03.log
/picclife/app/oracle/oradata/jiu/sysaux01.dbf
#失策:无法修改rename datafile NO:本次失败
select 'alter database rename file '''||name||''' to '||'''/u02/app/oracle/oradata/jiu/'||substr(name,instr(name,'/','-1')+1)||'''' ||';' from v$datafile;
#日志文件OK
select 'alter database rename file '''||member||''' to '||'''/u02/app/oracle/oradata/jiu/'||substr(member,instr(member,'/','-1')+1)||''''||';' from v$logfile;
#数据文件:修改语句查询:
SQL> select 'set newname for datafile '||file#||' to "/u02/app/oracle/oradata/jiu/'||substr(name,instr(name,'/','-1')+1)||'";' from v$datafile;
#注册备份集:源库传输的备份集未注册无法使用
RMAN> catalog start with '/home/oracle';
#如果上述:rename datafile and logfile 可以使用如下,否则,无法使用
run{
allocate channel ch00 type disk;
allocate channel ch01 type disk;
set newname for database to '/u02/app/oracle/oradata/jiu/%b';
restore database;
release channel ch00;
release channel ch01;
}
######本次使用此方式:
RMAN> run{
set newname for datafile 1 to "/u02/app/oracle/oradata/jiu/system01.dbf";
set newname for datafile 2 to "/u02/app/oracle/oradata/jiu/undotbs01.dbf";
set newname for datafile 3 to "/u02/app/oracle/oradata/jiu/sysaux01.dbf";
set newname for datafile 4 to "/u02/app/oracle/oradata/jiu/users01.dbf";
set newname for datafile 5 to "/u02/app/oracle/oradata/jiu/example01.dbf";
restore database;
switch datafile all;
}
#RESTORE NEXT RECOVER
RMAN> recover database;
RMAN-06054: media recovery requesting unknown log: thread 1 seq 8 lowscn 599018
#源端查询:其实最开始查询最好
SQL> select NAME,FIRST_CHANGE#,NEXT_CHANGE# from V$ARCHIVED_LOG order by NEXT_CHANGE#;
/home/oracle/rman/arch/1_7_971921498.dbf
598918 599018 --源库查询:最后的一个归档日志SCN
#OPEN数据库
RMAN> alter database open resetlogs;
ORA-19750: change tracking file: '/home/oracle/rman/block_trc/blk.trc'
#本次测试环境:由于控制文件中,记录了开启块跟踪,本次解决思路,源端传输块跟踪文件
mkdir -p ./rman/block_trc/
]$ scp blk.trc 192.168.20.55:/home/oracle/rman/block_trc/.
#???????
RMAN> alter database open;
#删除创建新的临时表空间
SQL> create temporary tablespace temp001 tempfile '/u02/app/oracle/oradata/jiu/temp.dbf' size 20m autoextend on;
SQL> alter database default temporary tablespace temp001
SQL> select tablespace_name,CONTENTS from dba_tablespaces where contents='TEMPORARY';
TABLESPACE_NAME CONTENTS
------------------------------ ---------
TEMP TEMPORARY
TEMP001 TEMPORARY
SQL> drop tablespace temp;
B:使用全库备份0级备份 +增量备份进行异机恢复
#使用A的测试环境,清空环境,避免干扰:
#删除内容:
块跟踪文件,数据文件,控制文件,日志文件
#本次为了方便,测试,参数文件未修改,为了达到测试效果
源库,创建一个新的表空间,创建一个测试表,已经在system表空间,创建一个测试表,模拟变化
切换几次归档
#模拟环境
SQL> create tablespace ceshi datafile '/home/oracle/ceshi.dbf' size 20m;
SQL> create table a tablespace system as select * from dba_objects;
SQL>
1* alter system switch logfile
r
r
r
r
SQL> create table b tablespace ceshi as select * from dba_objects;
SQL> alter system switch logfile;
#备份指令: 原0级备份
RMAN> run{
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter system switch logfile';
crosscheck archivelog all;
delete noprompt expired archivelog all;
backup incremental level 0 database format '/home/oracle/rman/data/%U.bak' include current controlfile plus archivelog format '/home/oracle/rman/arch/%U.bk';
}
#RMAN> list backup;
BS 6最新
#备份指令: 现1级备份,为了区分标识:名称前缀数值
RMAN> run{
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter system switch logfile';
crosscheck archivelog all;
delete noprompt expired archivelog all;
backup incremental level 1 database format '/home/oracle/rman/data/20180414%U.bak' include current controlfile plus archivelog format '/home/oracle/rman/arch/20180414%U.bk';
}
RMAN> list backup of controlfile;
Piece Name: /home/oracle/rman/data/201804140lsv2uij_1_1.bak
#检测备份归档文件的有效性:
--检测所有备份级
RMAN> crosscheck backup; RMAN> crosscheck archivelog all; --检测归档
--列举出所有无效的备份集
RMAN> list expired backup; RMAN> list expired archivelog all; --查询归档
--删除所有无效的归档
RMAN> delete noprompt expired archivelog all;
#传输:
$ scp 20180414* 192.168.20.55:/home/oracle/rman/arch/.
$ scp 201804140* 192.168.20.55:/home/oracle/rman/data/.
目标端:
修改口令文件,参数文件
SQL> startup nomount;
数据库启动到mount状态:
RMAN> restore controlfile from '/home/oracle/rman/data/201804140lsv2uij_1_1.bak';
SQL> alter database mount;
注册备份集:
RMAN> catalog start with '/home/oracle';
#修改日志文件,数据文件路径:rename 如果数据文件无法alter rename 则通过RMAN RUN块指定
#日志文件OK
select 'alter database rename file '''||member||''' to '||'''/u02/app/oracle/oradata/jiu/'||substr(member,instr(member,'/','-1')+1)||''''||';' from v$logfile;
#数据文件:修改语句查询:
SQL> select 'set newname for datafile '||file#||' to "/u02/app/oracle/oradata/jiu/'||substr(name,instr(name,'/','-1')+1)||'";' from v$datafile;
#使用的是最新的控制文件,经过发现,有记录新的数据文件名称:
RMAN> run{
set newname for datafile 1 to "/u02/app/oracle/oradata/jiu/system01.dbf";
set newname for datafile 2 to "/u02/app/oracle/oradata/jiu/undotbs01.dbf";
set newname for datafile 3 to "/u02/app/oracle/oradata/jiu/sysaux01.dbf";
set newname for datafile 4 to "/u02/app/oracle/oradata/jiu/users01.dbf";
set newname for datafile 5 to "/u02/app/oracle/oradata/jiu/example01.dbf";
set newname for datafile 6 to "/u02/app/oracle/oradata/jiu/ceshi.dbf";
restore database;
switch datafile all;
}
#测试没有块跟踪文件,是否可以open库
RMAN> recover database;
#在缺失块跟踪文件中,recover会话恢复断开:无法继续
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/arch/201804140hsv2uic_1_1.bk
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/rman/arch/201804140hsv2uic_1_1.bk tag=TAG20180330T111315
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/home/oracle/rman/arch/1_8_971921498.dbf thread=1 sequence=8
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/home/oracle/rman/arch/1_8_971921498.dbf'
ORA-00283: recovery session canceled due to errors
ORA-19755: could not open change tracking file
ORA-19750: change tracking file: '/home/oracle/rman/block_trc/blk.trc'
#不用想了
]$ scp blk.trc 192.168.20.55:/home/oracle/rman/block_trc/.
#有条件的情况下,恢复到了最后一个22号
RMAN> recover database;
archive log filename=/home/oracle/rman/arch/1_22_971921498.dbf thread=1 sequence=22
unable to find archive log
archive log thread=1 sequence=23
RMAN-06054: media recovery requesting unknown log: thread 1 seq 23 lowscn 603484
SQL> select sequence#,FIRST_CHANGE#,NEXT_CHANGE# from V$ARCHIVED_LOG order by NEXT_CHANGE# ;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ------------- ------------
7 598918 599018
8 599018 603069
9 603069 603071
10 603071 603080
@@@@省略9个归档文件
20 603400 603454
21 603454 603470
22 603470 603484
#查询测试的表空间的create change#号
SQL> select name,CREATION_CHANGE# from v$datafile;
/home/oracle/ceshi.dbf
602971
#查询验证:之前recover database,恢复中断,因为缺少块跟踪文件,无法恢复,而断点在create tablespace SCN时的归档日志
猜测:检测读取归档日志,读取到序列号8,发现有创建表空间的动作,缺少块跟踪文件,无法恢复
#OPEN数据库:无法直接open
#RMAN> alter database open;
RMAN> alter database open resetlogs;
#照常,修改临时表空间---