一
前 言
环境:
源库 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. 编辑参数文件,修改成单机的
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
*.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
*._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
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一份控制文件并传输至恢复主机
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
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
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_INDE
X,XXXXPDB:TBS_DEF,XXXXPDB:TBS_USER_DEF,XXXXPDB:TBS_CRMMS_IND
EX,XXXXPDB:TBS_TOPTEA,HDJHPDB:TBS_MONITORDDL_DATA,HDJHPDB:TB
S_USER_DEF,HNBHPSPDB:HNBHPS_DATA,HNCHECKPDB:TBS_VBLOG_DATA,H
NCHECKPDB:TBS_VBLOG_INDEX,HNCHECKPDB:TBS_DAOSHU_DEF,HNCHECKP
DB:TBS_TOPTEA,HNCHECKPDB:TBS_CHECK_INDEX
until time "to_date(2021-06-06 20:00,YYYY-MM-DD HH24:mi)";
}
exit
EOF
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;
……
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/129708.html
oracle 19c rac 异机不完全恢复pdb到单机总结 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%...
阅读 1229·2023-01-11 13:20
阅读 1535·2023-01-11 13:20
阅读 991·2023-01-11 13:20
阅读 1644·2023-01-11 13:20
阅读 3952·2023-01-11 13:20
阅读 2446·2023-01-11 13:20
阅读 1284·2023-01-11 13:20
阅读 3436·2023-01-11 13:20