rman恢复增加ADG(级联ADG)

当前已经有一套ADG库，分别是prod1和prod1s
计划从pord的ADG备库(prod1s)拷贝一个0级rman全备到新的服务器，恢复后，搭建成新增的ADG，SID叫prod2s，后面激活当做测试库

新服务器上设置
export ORACLE_SID=pord2s

从源库porddb1s生成一个pfile,,拷贝pfile到目标库
修改相关路径参数后，然后转换成spfile

*.audit_file_dest='/pordtestdata/dump/adump'
*.audit_trail='NONE'
*.cluster_database=false
*.compatible='11.2.0.3.0'
*.control_files='/pordtestdata/oradata/control01.ctl','/pordtestdata/oradata/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_create_online_log_dest_1='/pordtestdata/oradata'
*.db_domain=''
*.db_file_name_convert='/porddata/oradata/','/pordtestdata/oradata/'
*.db_files=2048
*.db_name='pord'
*.db_recovery_file_dest='/pordtestdata/fast_recovery_area'
*.db_recovery_file_dest_size=1048576000000
*.db_unique_name='pord2s'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=pordXDB)'
*.event='10949 trace name context forever, level 1'
*.fal_client='pord'
*.fal_server='porddb1s'
*.log_archive_config='DG_CONFIG=(pord,porddb1,porddb1s)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pord'
#*.log_archive_dest_2='SERVICE=porddb1s LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=porddb1s'
*.log_archive_dest_state_1='ENABLE'
#*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='/porddata/oradata/','/pordtestdata/oradata/'
*.open_cursors=900
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_target=10G
*.processes=1000
*.remote_login_passwordfile='exclusive'
*.resource_manager_plan=''
*.service_names='pord'
*.session_cached_cursors=2000
*.sessions=1200
*.sga_target=50G
*.standby_file_management='AUTO'
*.star_transformation_enabled='TRUE'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
#*.use_large_pages='ONLY'
*.utl_file_dir='/home/oracle'

SQL> create spfile from pfile='/pordtestdata/software/pfilepord.ora' ;

 

恢复控制文件
RMAN> shutdown immediate;

RMAN> startup nomount;

RMAN> restore standby controlfile from '/pordtestdata/arch/o1_mf_s_883643262_bs15sq11_.bkp';

RMAN> shutdown immediate;

RMAN> startup mount;

CATALOG命令重新注册备份集；
RMAN> catalog start with '/pordtestdata/arch';

 

4.开始恢复，在rman备份的源库(cascaded standby),在本例中为prod1s库
RMAN> report schema ;
更加相关信息整理成下面格式

弄成shell脚本后台执行
$ cat rman1.sh
#!/bin/bash
source .bash_profile
/u01/app/oracle/product/11.2.0/db_1/bin/rman target / <<EOF
run {
set newname for datafile 1 to '/pordtestdata/oradata/system.259.819420143';
set newname for datafile 2 to '/pordtestdata/oradata/sysaux.260.819420151';
set newname for datafile 3 to '/pordtestdata/oradata/undotbs1.261.819420153';
set newname for datafile 4 to '/pordtestdata/oradata/undotbs2.263.819420163';
set newname for datafile 5 to '/pordtestdata/oradata/users.264.819420163';
set newname for datafile 6 to '/pordtestdata/oradata/pord.295.819889851';
...省略N行
set newname for datafile 133 to '/pordtestdata/oradata/pord_idx08.dbf';
set newname for datafile 134 to '/pordtestdata/oradata/pord_idx09.dbf';
set newname for datafile 135 to '/pordtestdata/oradata/pord_idx10.dbf';
set newname for datafile 136 to '/pordtestdata/oradata/pord_idx11.dbf';
set newname for datafile 137 to '/pordtestdata/oradata/pord_idx12.dbf';
set newname for datafile 138 to '/pordtestdata/oradata/pord_idx13.dbf';
set newname for datafile 139 to '/pordtestdata/oradata/tbs_sysaux03.dbf';
restore database;
switch datafile all;
}
EOF
exit ;

 

通过查看视图 V$SESSION_LONGOPS 这个视图是查看长时间操作的会话,可以粗略的分析完成进度，
不但可以用于rman 也可以用于impdp/expdp
set linesize 200 ;
select sid,opname,serial#,context,sofar,totalwork,round(sofar/totalwork*100,2) "%_complete"
from v$session_longops
where opname like 'RMAN%'
and opname not like '%aggregate%'
and totalwork != 0
and sofar <> totalwork
order by "%_complete" desc ;

修改pord1s库上的tns,指向新的目标库
增加
pord2s =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod2s.test.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pord2s)
)
)

处理tempfile
alter database tempfile '/porddata/oradata/temp.262.819420155' drop;

SQL> alter tablespace temp add tempfile '/pordtestdata/oradata/temp1.dbf' size 10G;

 

--alter system set log_archive_config='DG_CONFIG=(pord1,pord1s,pord2s)';

备份原参数:

修改成下面的:
alter system set log_archive_dest_2='SERVICE=prod2s LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pord2s';

增加一些新的standby log
ALTER DATABASE ADD STANDBY LOGFILE GROUP 21 '/tmpdata/pordredo/redo_21.log' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 22 '/tmpdata/pordredo/redo_22.log' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 23 '/tmpdata/pordredo/redo_23.log' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 24 '/tmpdata/pordredo/redo_24.log' size 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 25 '/tmpdata/pordredo/redo_25.log' size 1G;

清理旧的
alter database clear logfile group 6;
alter database clear logfile group 7;
alter database clear logfile group 8;
alter database clear logfile group 9;
alter database clear logfile group 10;

alter database clear logfile group 16;
alter database clear logfile group 17;
alter database clear logfile group 18;
alter database clear logfile group 19;
alter database clear logfile group 20;

 

alter database recover managed standby database cancel ;
alter database recover managed standby database disconnect from session;

解决gap，从主库或者cascade standby库去找，如果从主库找到了，需要在备库注册，因为日志是从备库(cascade standby）发送到目标库的

RMAN> list archivelog from sequence 74227 until sequence 74231;

scp o1_mf_1_74227_bs0rc32g_.arc 10.45.26.102:/porddata/fast_recovery_area/pordDB1S/archivelog/2015_06_29/
scp o1_mf_1_74228_bs0zvxss_.arc 10.45.26.102:/porddata/fast_recovery_area/pordDB1S/archivelog/2015_06_29/
scp o1_mf_1_74229_bs11pq4p_.arc 10.45.26.102:/porddata/fast_recovery_area/pordDB1S/archivelog/2015_06_29/
scp o1_mf_1_74230_bs1305mk_.arc 10.45.26.102:/porddata/fast_recovery_area/pordDB1S/archivelog/2015_06_29/
scp o1_mf_1_74231_bs147yyl_.arc 10.45.26.102:/porddata/fast_recovery_area/pordDB1S/archivelog/2015_06_29/

RMAN> catalog start with '/porddata/fast_recovery_area/pordDB1S/archivelog/2015_06_30/';

日志追的差不多的时候，可以做failover切换

主库或者cascade standby库
alter system set log_archive_dest_state_2=defer;

要激活的standby
alter system set aq_tm_processes=0 scope=memory;
alter database recover managed standby database cancel;
alter database activate physical standby database;
shutdown immediate
startup
select OPEN_MODE,SWITCHOVER_STATUS,DATABASE_ROLE,PROTECTION_MODE
FROM v$database;

 

激活后，恢复生产库pordadg的log_archive设置
alter system set log_archive_dest_2='SERVICE=pord1 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pord1';
alter system set log_archive_dest_state_2=enable;