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

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

XXXXdb1.__data_transfer_cache_size=0XXXXdb2.__data_transfer_cache_size=0XXXXdb2.__db_cache_size=274743689216XXXXdb1.__db_cache_size=274743689216XXXXdb1.__inmemory_ext_roarea=0XXXXdb2.__inmemory_ext_roarea=0XXXXdb1.__inmemory_ext_rwarea=0XXXXdb2.__inmemory_ext_rwarea=0XXXXdb1.__java_pool_size=0XXXXdb2.__java_pool_size=0XXXXdb1.__large_pool_size=2684354560XXXXdb2.__large_pool_size=2684354560XXXXdb1.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environmentXXXXdb2.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environmentXXXXdb1.__pga_aggregate_target=81067507712XXXXdb2.__pga_aggregate_target=81067507712XXXXdb1.__sga_target=243202523136XXXXdb2.__sga_target=243202523136XXXXdb2.__shared_io_pool_size=134217728XXXXdb1.__shared_io_pool_size=134217728XXXXdb1.__shared_pool_size=26843545600XXXXdb2.__shared_pool_size=26843545600XXXXdb2.__streams_pool_size=9261023232XXXXdb1.__streams_pool_size=9261023232XXXXdb1.__unified_pga_pool_size=0XXXXdb2.__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=FALSEXXXXdb1._drop_stat_segment=1XXXXdb2._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=0family:dw_helper.instance_mode='read-only'XXXXdb2.instance_number=2XXXXdb1.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=2XXXXdb1.thread=1*.undo_retention=7200*.undo_tablespace='UNDOTBS1'XXXXdb2.undo_tablespace='UNDOTBS2'XXXXdb1.undo_tablespace='UNDOTBS1'
复制

*.cluster_database=trueXXXXdb2._drop_stat_segment=1XXXXdb2.thread=2XXXXdb2.instance_number=2XXXXdb2.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=FALSEXXXXdb1._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=0family: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/archivelogmkdir -p /oracle/app/oracle/admin/XXXXdb/adumpmkdir -p /data/XXXXdb/archmkdir -p /data/XXXXdb/datafile
复制

vi restore_datafile_20200519.shrman target / log restore_datafile_20210528.log << EOFrun{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;}EOFecho "===========complete time is [`date +%Y%m%d_%H:%M:%S`]=======">>restore_datafile_20210528.log
复制

vi restore_archivelog_20210606.shrman target / log restore_archivelog_20210606.log <<EOFrun {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;}exitEOFecho "===========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 tablespaceXXXXPDB: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_INDEXuntil time "to_date('2021-06-06 20:00','YYYY-MM-DD HH24:mi')";}exitEOF
复制

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;……--renamealter 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';……
复制