oracle 19c rac 异机不完全恢复pdb到单机总结

环境:源库 os:redhat7,DB:oracle 19.9 RAC cdb目标库os:redhat7,DB:oracle 19.9 单机 cdb目的:RAC部分表空间恢复到单机
本次恢复背景为测试备份有效性恢复的可用性,将生产库的备份进行异地恢复。恢复流程与nocdb模式基本一致,在restore和recover时需要注意几个地方。

本次恢复的操作流程简介

  1. 从生产库创建PFILE参数文件,修改成单机的PFILE参数文件;
  2. 创建相关目录;
  3. 从生产库拷贝控制文件到恢复环境;
  4. Restore数据文件;
  5. Restore归档文件;
  6. 重建控制文件;
  7. Recover 数据库;
  8. 创建日志组
  9. 检查;

具体操作步骤和命令

1. 从生产库创建PFILE

SQL> create pfile='/home/oracle/XXXXdb_20210521' from spfile;

2. 编辑参数文件,修改成单机的原rac参数文件:

XXXXdb1.__data_transfer_cache_size=0
XXXXdb2.__data_transfer_cache_size=0
XXXXdb2.__db_cache_size=274743689216
XXXXdb1.__db_cache_size=274743689216
XXXXdb1.__inmemory_ext_roarea=0
XXXXdb2.__inmemory_ext_roarea=0
XXXXdb1.__inmemory_ext_rwarea=0
XXXXdb2.__inmemory_ext_rwarea=0
XXXXdb1.__java_pool_size=0
XXXXdb2.__java_pool_size=0
XXXXdb1.__large_pool_size=2684354560
XXXXdb2.__large_pool_size=2684354560
XXXXdb1.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
XXXXdb2.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
XXXXdb1.__pga_aggregate_target=81067507712
XXXXdb2.__pga_aggregate_target=81067507712
XXXXdb1.__sga_target=243202523136
XXXXdb2.__sga_target=243202523136
XXXXdb2.__shared_io_pool_size=134217728
XXXXdb1.__shared_io_pool_size=134217728
XXXXdb1.__shared_pool_size=26843545600
XXXXdb2.__shared_pool_size=26843545600
XXXXdb2.__streams_pool_size=9261023232
XXXXdb1.__streams_pool_size=9261023232
XXXXdb1.__unified_pga_pool_size=0
XXXXdb2.__unified_pga_pool_size=0
*._and_pruning_enabled=FALSE
*._ash_size=52428800
*._b_tree_bitmap_plans=FALSE
*._bloom_filter_enabled=FALSE
*._cleanup_rollback_entries=20000
*._clusterwide_global_transactions=FALSE
*._connect_by_use_union_all='OLD_PLAN_MODE'
*._cursor_obsolete_threshold=1024
*._datafile_write_errors_crash_instance=FALSE
*._db_link_sources_tracking=FALSE
XXXXdb1._drop_stat_segment=1
XXXXdb2._drop_stat_segment=1
*._fix_control='14142884:ON','8560951:ON','8893626:OFF','9344709:OFF','9195582:OFF','9380298:ON','13704562:OFF','16053273:OFF','8611462:OFF','17760375:OFF','17938754:OFF'
*._gc_bypass_readers=FALSE
*._gc_policy_time=0
*._gc_read_mostly_locking=FALSE
*._gc_undo_affinity=FALSE
*._ksmg_granule_size=33554432
*._lm_drm_disable=7
*._lm_lms_priority_dynamic=FALSE
*._lm_sync_timeout=1200
*._memory_imm_mode_without_autosga=FALSE
*._optim_peek_user_binds=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_ads_use_result_cache=FALSE
*._optimizer_aggr_groupby_elim=FALSE
*._optimizer_dsdir_usage_control=0
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_mjc_enabled=FALSE
*._optimizer_partial_join_eval=FALSE
*._optimizer_reduce_groupby_key=FALSE
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._rollback_segment_count=4000
*._securefiles_concurrency_estimate=50
*._smu_debug_mode=134217728
*._sql_plan_directive_mgmt_control=0
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.archive_lag_target=1200
*.audit_file_dest='/oracle/app/oracle/admin/XXXXdb/adump'
*.audit_trail='NONE'
*.cell_offload_processing=FALSE
*.cluster_database=true
*.compatible='19.0.0'
*.control_file_record_keep_time=31
*.control_files='+DATADG1/XXXXDB/CONTROLFILE/current.257.1048091377'#Restore Controlfile
*.db_block_checking='MEDIUM'
*.db_block_checksum='FULL'
*.db_block_size=8192
*.db_cache_size=274743689216
*.db_create_file_dest=''
*.db_file_name_convert='+DATADG1','+DATADG1','+DATADG2','+DATADG2'
*.db_files=8000
*.db_lost_write_protect='TYPICAL'
*.db_name='XXXXdb'
*.db_writer_processes=10
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/oraclelog'
*.dispatchers=''
*.distributed_lock_timeout=600
*.enable_ddl_logging=TRUE
*.enable_goldengate_replication=TRUE
*.enable_pluggable_database=true
*.event='10949 trace name context forever:28401 trace name context forever, level 1:44951 trace name context forever, level 32:trace[krb.*] disk disable, memory disable'
*.fal_client='PRIXXXXDB'
*.fal_server='XXXXDBSTD'
*.inmemory_query='DISABLE'
*.inmemory_size=0
family:dw_helper.instance_mode='read-only'
XXXXdb2.instance_number=2
XXXXdb1.instance_number=1
*.java_pool_size=2147483648
*.job_queue_processes=100
*.large_pool_size=8589934592
*.local_listener='-oraagent-dummy-'
*.log_archive_config='dg_config=(XXXXdb,XXXXdbstd)'
*.log_archive_dest_1='LOCATION=+ARCHIVEDG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=XXXXdb'
*.log_archive_dest_2='service=XXXXdbstd LGWR ASYNC valid_for=(all_logfiles,primary_role) db_unique_name=XXXXdbstd'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_dest_state_4='ENABLE'
*.log_file_name_convert='+DATADG1','+DATADG1','+DATADG2','+DATADG2'
*.memory_target=0
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=2500
*.open_links=64
*.open_links_per_instance=256
*.optimizer_adaptive_plans=FALSE
*.optimizer_index_cost_adj=80
*.parallel_execution_message_size=32768
*.parallel_force_local=TRUE
*.parallel_max_servers=300
*.parallel_min_servers=0
*.pga_aggregate_target=96636764160
*.processes=16000
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.result_cache_max_size=0
*.session_cached_cursors=600
*.session_max_open_files=500
*.sga_max_size=387620798464
*.sga_target=0
*.shared_pool_size=92341796864
*.standby_file_management='AUTO'
XXXXdb2.thread=2
XXXXdb1.thread=1
*.undo_retention=7200
*.undo_tablespace='UNDOTBS1'
XXXXdb2.undo_tablespace='UNDOTBS2'
XXXXdb1.undo_tablespace='UNDOTBS1'

--排除集群和节点2相关参数
*.cluster_database=true
XXXXdb2._drop_stat_segment=1
XXXXdb2.thread=2
XXXXdb2.instance_number=2
XXXXdb2.undo_tablespace='UNDOTBS2'
*.log_file_name_convert='+DATADG1','+DATADG1','+DATADG2','+DATADG2'
*.db_file_name_convert='+DATADG1','+DATADG1','+DATADG2','+DATADG2'

--修改参数
*.log_archive_dest_1='LOCATION=/data/XXXXdb/archivelog'
*.control_files='/data/XXXXdb/datafile/current01.ctl'
修改后pfile:
*._and_pruning_enabled=FALSE
*._ash_size=52428800
*._b_tree_bitmap_plans=FALSE
*._bloom_filter_enabled=FALSE
*._cleanup_rollback_entries=20000
*._clusterwide_global_transactions=FALSE
*._connect_by_use_union_all='OLD_PLAN_MODE'
*._cursor_obsolete_threshold=1024
*._datafile_write_errors_crash_instance=FALSE
*._db_link_sources_tracking=FALSE
XXXXdb1._drop_stat_segment=1
*._fix_control='14142884:ON','8560951:ON','8893626:OFF','9344709:OFF','9195582:OFF','9380298:ON','13704562:OFF','16053273:OFF','8611462:OFF','17760375:OFF','17938754:OFF'
*._gc_bypass_readers=FALSE
*._gc_policy_time=0
*._gc_read_mostly_locking=FALSE
*._gc_undo_affinity=FALSE
*._ksmg_granule_size=33554432
*._lm_drm_disable=7
*._lm_lms_priority_dynamic=FALSE
*._lm_sync_timeout=1200
*._memory_imm_mode_without_autosga=FALSE
*._optim_peek_user_binds=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_ads_use_result_cache=FALSE
*._optimizer_aggr_groupby_elim=FALSE
*._optimizer_dsdir_usage_control=0
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_mjc_enabled=FALSE
*._optimizer_partial_join_eval=FALSE
*._optimizer_reduce_groupby_key=FALSE
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._rollback_segment_count=4000
*._securefiles_concurrency_estimate=50
*._smu_debug_mode=134217728
*._sql_plan_directive_mgmt_control=0
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.archive_lag_target=1200
*.audit_file_dest='/oracle/app/oracle/admin/XXXXdb/adump'
*.audit_trail='NONE'
*.cell_offload_processing=FALSE
*.compatible='19.0.0'
*.control_file_record_keep_time=31
*.control_files='/data/XXXXdb/datafile/current01.ctl'
*.db_block_checking='MEDIUM'
*.db_block_checksum='FULL'
*.db_block_size=8192
*.db_cache_size=274743689216
*.db_create_file_dest=''
*.db_files=8000
*.db_lost_write_protect='TYPICAL'
*.db_name='XXXXdb'
*.db_writer_processes=10
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/oraclelog'
*.dispatchers=''
*.distributed_lock_timeout=600
*.enable_ddl_logging=TRUE
*.enable_goldengate_replication=TRUE
*.enable_pluggable_database=true
*.event='10949 trace name context forever:28401 trace name context forever, level 1:44951 trace name context forever, level 32:trace[krb.*] disk disable, memory disable'
*.fal_client='PRIXXXXDB'
*.fal_server='XXXXDBSTD'
*.inmemory_query='DISABLE'
*.inmemory_size=0
family:dw_helper.instance_mode='read-only'
XXXXdb1.instance_number=1
*.java_pool_size=2147483648
*.job_queue_processes=100
*.large_pool_size=8589934592
*.local_listener='-oraagent-dummy-'
*.log_archive_config='dg_config=(XXXXdb,XXXXdbstd)'
*.log_archive_dest_1='LOCATION=/data/XXXXdb/archivrlog'
*.log_archive_dest_2='service=XXXXdbstd LGWR ASYNC valid_for=(all_logfiles,primary_role) db_unique_name=XXXXdbstd'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_dest_state_4='ENABLE'
*.memory_target=0
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=2500
*.open_links=64
*.open_links_per_instance=256
*.optimizer_adaptive_plans=FALSE
*.optimizer_index_cost_adj=80
*.parallel_execution_message_size=32768
*.parallel_force_local=TRUE
*.parallel_max_servers=300
*.parallel_min_servers=0
*.pga_aggregate_target=96636764160
*.processes=16000
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.result_cache_max_size=0
*.session_cached_cursors=600
*.session_max_open_files=500
*.sga_max_size=387620798464
*.sga_target=0
*.shared_pool_size=92341796864
*.standby_file_management='AUTO'
XXXXdb1.thread=1
*.undo_retention=7200
*.undo_tablespace='UNDOTBS1'
XXXXdb1.undo_tablespace='UNDOTBS1'

3. 创建oracle相关目录

创建oracle 数据文件、控制文件等目录

mkdir -p /data/XXXXdb/archivelog
mkdir -p /oracle/app/oracle/admin/XXXXdb/adump
mkdir -p /data/XXXXdb/arch
mkdir -p /data/XXXXdb/datafile

4. 从源库ASM中copy一份控制文件并传输至恢复主机

asmcmd后直接cp控制文件至文件目录,在scp至恢复主机即可。

5. 启动到mount状态

oracle 19c rac 异机不完全恢复pdb到单机总结
oracle 19c rac 异机不完全恢复pdb到单机总结
6. 恢复数据文件

本次恢复表空间如下:oracle 19c rac 异机不完全恢复pdb到单机总结

脚本实例如下:

vi restore_datafile_20200519.sh 
rman target / log restore_datafile_20210528.log << EOF
run{
allocate channel ch1 type 'SBT_TAPE';
allocate channel ch2 type 'SBT_TAPE';
allocate channel ch3 type 'SBT_TAPE';
allocate channel ch4 type 'SBT_TAPE'; 
set newname for datafile 1 to '/oradata2/xxxxdb1/system01.dbf';
set newname for datafile 2 to '/oradata2/xxxxdb1/undotbs03.dbf';
set newname for datafile 3 to '/oradata2/xxxxdb1/sysaux01.dbf';
set newname for datafile 4 to '/oradata2/xxxxdb1/undotbs01.dbf';
……
restore datafile 1 ;
restore datafile 2 ;
restore datafile 3 ;
restore datafile 4 ;
……
switch datafile all; 
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4; 
}
EOF
echo "===========complete time is [`date +%Y%m%d_%H:%M:%S`]=======">>restore_datafile_20210528.log

7.  恢复所需要的归档文件,时间跨度大于全备时间全备时间2021/06/06 00:01到2021/06/06 19:09

oracle 19c rac 异机不完全恢复pdb到单机总结
oracle 19c rac 异机不完全恢复pdb到单机总结

脚本实例如下:

vi restore_archivelog_20210606.sh

rman target / log restore_archivelog_20210606.log <<EOF
run {
allocate channel ch1 type 'SBT_TAPE';
allocate channel ch2 type 'SBT_TAPE';
allocate channel ch3 type 'SBT_TAPE';
allocate channel ch4 type 'SBT_TAPE'; 
set archivelog destination to '/oradata2/xxxxdb1/archivelog';
restore archivelog from time "to_date('2021-06-05 23:00:00','yyyy-mm-dd hh24:mi:ss')"
until time "to_date('2021-06-06 20:00:00','yyyy-mm-dd hh24:mi:ss')";
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4; 
}
exit
EOF
echo "===========complete time is [`date +%Y%m%d_%H:%M:%S`]=======">>restore_archivelog_20210606.log

8. recover数据库这部分是关键,尝试了许多次才成功。
常规步骤是重建控制文件,修改数据文件路径为当前主机路径,剔除不需要恢复的数据文件,然后recover database到归档结束时间点,最后open数据库。按照流程走,前面几步都没有问题,直到alert database open resetlogs报错。

oracle 19c rac 异机不完全恢复pdb到单机总结
oracle 19c rac 异机不完全恢复pdb到单机总结
看到这个错误,有2种方案尝试。第一种,还是按照当前思路来做,在通过脚本重新创建了控制文件,这个时候控制文件的SCN肯定要小于当前数据库的SCN,这个时候数据库的recover需要加上参数using backup controlfile,用来告诉数据库,不要以controlfile中的scn作为恢复的终点。(我失败了,但应该也是可行的,有空再研究)

第二种,不重建控制文件,将不需要的数据文件offline drop,然后recover database到归档结束时间点,最后open数据库。(成功)

oracle 19c rac 异机不完全恢复pdb到单机总结
oracle 19c rac 异机不完全恢复pdb到单机总结

oracle 19c rac 异机不完全恢复pdb到单机总结
oracle 19c rac 异机不完全恢复pdb到单机总结

手工将不需要的数据文件offline drop,但recover的时候还是提示需要restore那些不要的数据文件。19c之前的版本并没有遇到这样的错误,想到recover的时候可以skip不需要的表空间,查询文档看看pdb模式下的语法。

run { 
set archivelog destination to '/data/XXXXdb/arch/';
recover database skip forever tablespace
XXXXPDB:TBS_RWD_DATA,XXXXPDB:TBS_RWD_INDEX,XXXXPDB:TBS_SJYZX_DATA,XXXXPDB:TBS_IBOSS,XXXXPDB:TBS_DAOSHU_DATA,XXXXPDB:TBS_SJYZX_DEF,XXXXPDB:TBS_MONITORDDL_DATA,XXXXPDB:TBS_SJYZX_INDEX,XXXXPDB:TBS_DEF,XXXXPDB:TBS_USER_DEF,XXXXPDB:TBS_CRMMS_INDEX,XXXXPDB:TBS_TOPTEA,HDJHPDB:TBS_MONITORDDL_DATA,HDJHPDB:TBS_USER_DEF,HNBHPSPDB:HNBHPS_DATA,HNCHECKPDB:TBS_VBLOG_DATA,HNCHECKPDB:TBS_VBLOG_INDEX,HNCHECKPDB:TBS_DAOSHU_DEF,HNCHECKPDB:TBS_TOPTEA,HNCHECKPDB:TBS_CHECK_INDEX
 until time "to_date('2021-06-06 20:00','YYYY-MM-DD HH24:mi')";
}
exit
EOF

这一次成功了,查看日志,发现使用skip,会自动将不需要的数据文件offline drop,然后再recover,似乎和手工操作并没有什么区别。

oracle 19c rac 异机不完全恢复pdb到单机总结
oracle 19c rac 异机不完全恢复pdb到单机总结

9. 修改redo路径

——删除部分inactive的日志组,rename其他redo file

alter database drop  logfile group 1;
alter database drop  logfile group 2;
alter database drop  logfile group 5;
alter database drop  logfile group 6;
alter database drop  logfile group 9;
alter database drop  logfile group 10;
alter database drop  logfile group 11;
alter database drop  logfile group 12;
……


--rename
alter database rename file '+DATADG1/XXXXDB/ONLINELOG/group_7.374.1050079119' to '/data/XXXXdb/datafile/redo07_01.log';
alter database rename file '+DATADG2/XXXXDB/ONLINELOG/group_7.923.1069114131' to '/data/XXXXdb/datafile/redo07_02.log';
alter database rename file '+DATADG1/XXXXDB/ONLINELOG/group_8.375.1050079123' to '/data/XXXXdb/datafile/redo08_01.log';
alter database rename file '+DATADG2/XXXXDB/ONLINELOG/group_8.924.1069114137' to '/data/XXXXdb/datafile/redo08_02.log';
……

10. open数据库以及核查。

最后就是alter database open resetlogs以及核查恢复出来的表空间数据。

发表评论

登录后才能评论
服务中心
服务中心
联系客服
联系客服
返回顶部