国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

oracle 19c rac 異機不完全恢復pdb到單機總結

IT那活兒 / 1814人閱讀
oracle 19c rac 異機不完全恢復pdb到單機總結
點擊上方“IT那活兒”，關注后了解更多精彩內(nèi)容！！

一 

 前  言 


環(huán)境：

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

目標庫os：redhat7，DB：oracle 19.9 單機 cdb

目的：RAC部分表空間恢復到單機


本次恢復背景為測試備份有效性恢復的可用性，將生產(chǎn)庫的備份進行異地恢復。恢復流程與nocdb模式基本一致，在restore和recover時需要注意幾個地方。


二 

操作流程簡介


1. 從生產(chǎn)庫創(chuàng)建PFILE參數(shù)文件，修改成單機的PFILE參數(shù)文件；

2. 創(chuàng)建相關目錄；

3. 從生產(chǎn)庫拷貝控制文件到恢復環(huán)境；

4.  Restore數(shù)據(jù)文件；

5.  Restore歸檔文件；

6.  重建控制文件；

7.  Recover 數(shù)據(jù)庫；

8. 創(chuàng)建日志組

9. 檢查。

三 

具體操作步驟和命令


1. 從生產(chǎn)庫創(chuàng)建PFILE


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


2. 編輯參數(shù)文件，修改成單機的

原rac參數(shù)文件：
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
--排除集群和節(jié)點2相關參數(shù)：
*.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
--修改參數(shù)：
*.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. 創(chuàng)建oracle相關目錄
創(chuàng)建oracle 數(shù)據(jù)文件、控制文件等目錄：
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狀態(tài)
6. 恢復數(shù)據(jù)文件
--本次恢復表空間如下：
--腳本實例如下：
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數(shù)據(jù)庫
這部分是關鍵，嘗試了許多次才成功。
常規(guī)步驟是重建控制文件，修改數(shù)據(jù)文件路徑為當前主機路徑，剔除不需要恢復的數(shù)據(jù)文件，然后recover database到歸檔結束時間點，最后open數(shù)據(jù)庫。
按照流程走，前面幾步都沒有問題，直到alert database open resetlogs報錯。
看到這個錯誤，有2種方案嘗試。
第一種，還是按照當前思路來做，在通過腳本重新創(chuàng)建了控制文件，這個時候控制文件的SCN肯定要小于當前數(shù)據(jù)庫的SCN，這個時候數(shù)據(jù)庫的recover需要加上參數(shù)using backup controlfile，用來告訴數(shù)據(jù)庫，不要以controlfile中的scn作為恢復的終點。
（我失敗了，但應該也是可行的，有空再研究）
第二種，不重建控制文件，將不需要的數(shù)據(jù)文件offline drop，然后recover database到歸檔結束時間點，最后open數(shù)據(jù)庫。（成功！）
手工將不需要的數(shù)據(jù)文件offline drop，但recover的時候還是提示需要restore那些不要的數(shù)據(jù)文件。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_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
這一次成功了，查看日志，發(fā)現(xiàn)使用skip，會自動將不需要的數(shù)據(jù)文件offline drop，然后再recover，似乎和手工操作并沒有什么區(qū)別。
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數(shù)據(jù)庫以及核查。
最后就是alter database open resetlogs以及核查恢復出來的表空間數(shù)據(jù)。

本 文 原 創(chuàng) 來 源：IT那活兒微信公眾號（上海新炬王翦團隊）

文章版權歸作者所有，未經(jīng)允許請勿轉載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/129708.html

相關文章

發(fā)表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<