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

IT那活儿发布于2023-01-11 13:20 / 2138人阅读

环境:

源库 os：redhat7，DB：oracle 19.9 RAC cdb

目标库os：redhat7，DB：oracle 19.9 单机 cdb

目的：RAC部分表空间恢复到单机

本次恢复背景为测试备份有效性恢复的可用性，将生产库的备份进行异地恢复。恢复流程与nocdb模式基本一致，在restore和recover时需要注意几个地方。

本次恢复的操作流程简介

从生产库创建PFILE参数文件，修改成单机的PFILE参数文件；
创建相关目录；
从生产库拷贝控制文件到恢复环境；
Restore数据文件；
Restore归档文件；
重建控制文件；
Recover 数据库；
创建日志组
检查；

具体操作步骤和命令

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状态

6. 恢复数据文件

本次恢复表空间如下：

脚本实例如下：

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

脚本实例如下：

vi restore_archivelog_20210606.sh

rman target / log restore_archivelog_20210606.log <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报错。

看到这个错误，有2种方案尝试。

第一种，还是按照当前思路来做，在通过脚本重新创建了控制文件，这个时候控制文件的SCN肯定要小于当前数据库的SCN，这个时候数据库的recover需要加上参数using backup controlfile，用来告诉数据库，不要以controlfile中的scn作为恢复的终点。

（我失败了，但应该也是可行的，有空再研究）

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

手工将不需要的数据文件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，似乎和手工操作并没有什么区别。

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以及核查恢复出来的表空间数据。

END

更多精彩干货分享

点击下方名片关注

IT那活儿

云服务器 GPU云服务器 Oracle?RAC?集群 oracle-rac 迁移 oracle10g rac 完全恢复

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129798.html

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

oracle 19c rac 异机不完全恢复pdb到单机总结 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%...

IT那活儿 2023-01-11 13:20 评论0 收藏278

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师

我要关注我要私信

TA的文章

消息中间件故障分析一例

阅读 1229·2023-01-11 13:20
RAC双节点crash回复一例

阅读 1535·2023-01-11 13:20
ORA-600处理一例

阅读 991·2023-01-11 13:20
双节点RAC实例2 HANG 故障分析一例

阅读 1643·2023-01-11 13:20
RAC集群节点1重启分析一例

阅读 3952·2023-01-11 13:20
CRS启动报错CRS-1656处理分享

阅读 2446·2023-01-11 13:20
oracle 12CR2打补丁报错处理一例

阅读 1284·2023-01-11 13:20
分布式缓存组件故障分析及监控优化

阅读 3436·2023-01-11 13:20

资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

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

相关文章