• Oracle 主从切换脚本

    一、 切换前预检查

    1. dg_precheck_main_v1.4.sh

    1. #!/bin/bash
    2.  
    3. #**********************************************************************************
    4. # Author: Hehuyi_In
    5. # Date: 2022年06月16日
    6. # FileName: dg_precheck_main_v1.4.sh
    7. # 
    8. # For sys user, execute the script directly. 
    9. # For other sysdba privileged users, you need to manually synchronize the password file to standby server in versions below 12.2.
    10. # The script cannot be executed on the Linux 5 os.
    11. #
    12. # Example: 
    13. # ./dg_precheck_main_v1.4.sh &> dg_precheck.log
    14. # cat dg_precheck.log | grep --color=no -E "Warning|Error" ,or less -R dg_precheck.log
    15. #
    16. #
    17. # Description:
    18. # 2022-06-16 v_1.0 just get and print the infomation we want
    19. # 2022-06-17 v_1.1 auto check the database role
    20. # 2022-06-22 v_1.2 adjust the output format
    21. # 2022-06-28 v_1.3 add compare function, compare two nodes at a time
    22. # 2022-06-28 split to two scripts dg_precheck_main_v1.0.sh and dg_precheck_func_v1.0.sh
    23. # 2022-07-06 dg_precheck_main_v1.1 check os info
    24. # 2022-07-12 dg_precheck_main_v1.2 adjust the output format
    25. # 2022-07-13 dg_precheck_main_v1.3 adjust os info check function
    26. # 2022-10-19 dg_precheck_main_v1.4 add checking items: dblink,standby_file_management,flashback_on,db_flashback_retention_target,db_recovery_file_dest,db_recovery_file_dest_size 
    27. #**********************************************************************************
    28.  
    29. source ./dg_precheck_func_v1.4.sh
    30.  
    31. # TNS列表
    32. TNS_LIST=(test_dg test)
    33.  
    34. # 连接信息
    35. DBUSER='sys/"xxxxx"'
    36.  
    37.  
    38. # --------------------------- 检查并设置TNS_LIST中各tnsname对应数据库角色 ----------------------------------
    39. auto_check_db_role
    40.  
    41. # 字典定义
    42. declare -A PRIMARY_TNS_DIC
    43. declare -A STANDBY_TNS_DIC
    44.  
    45.  
    46. echo -e "--------------------------------------------------------------------------------"
    47. echo -e "- 各节点主要参数检查                                                "
    48. echo -e "--------------------------------------------------------------------------------\n"
    49.  
    50. # 内存参数 及 compatible 检查和对比
    51. PARAMS_LIST=(sga_max_size sga_target pga_aggregate_target shared_pool_size db_cache_size streams_pool_size compatible standby_file_management)
    52.  
    53. for PARAM in ${PARAMS_LIST[@]};
    54. do
    55.  
    56. PRIMARY_TNS_DIC+=([$PARAM]=`get_params_value $PRIMARY_TNS $PARAM`)
    57. STANDBY_TNS_DIC+=([$PARAM]=`get_params_value $STANDBY_TNS $PARAM`)
    58.  
    59. # 主从库间对比参数是否相等
    60. compare_value ${PRIMARY_TNS_DIC[$PARAM]} ${STANDBY_TNS_DIC[$PARAM]} $PARAM
    61.  
    62. done
    63.  
    64. # 获取其他参数值
    65. PARAMS_LIST=(log_archive_max_processes db_create_file_dest db_file_name_convert log_file_name_convert fal_client fal_server log_archive_config db_recovery_file_dest db_recovery_file_dest_size db_flashback_retention_target)
    66.  
    67. for PARAM in ${PARAMS_LIST[@]};
    68. do
    69.  
    70. PRIMARY_TNS_DIC+=([$PARAM]=`get_params_value $PRIMARY_TNS $PARAM`)
    71. STANDBY_TNS_DIC+=([$PARAM]=`get_params_value $STANDBY_TNS $PARAM`)
    72.  
    73. done
    74.  
    75. echo -e "\n"
    76.  
    77. echo -e "--------------------------------------------------------------------------------"
    78. echo -e "- 各节点主要文件数检查                                                          "
    79. echo -e "--------------------------------------------------------------------------------\n"
    80.  
    81. PRIMARY_TNS_DIC+=([redo_count]=`get_file_count $PRIMARY_TNS "LOG"`)
    82. PRIMARY_TNS_DIC+=([standby_count]=`get_file_count $PRIMARY_TNS "STANDBY_LOG"`)
    83. PRIMARY_TNS_DIC+=([tempfile_count]=`get_file_count $PRIMARY_TNS "TEMPFILE"`)
    84. STANDBY_TNS_DIC+=([redo_count]=`get_file_count $STANDBY_TNS "LOG"`)
    85. STANDBY_TNS_DIC+=([standby_count]=`get_file_count $STANDBY_TNS "STANDBY_LOG"`)
    86. STANDBY_TNS_DIC+=([tempfile_count]=`get_file_count $STANDBY_TNS "TEMPFILE"`)
    87.  
    88. PRIMARY_TNS_DIC+=([datafile_online_count]=`get_datafile_online_count $PRIMARY_TNS`)
    89. STANDBY_TNS_DIC+=([datafile_online_count]=`get_datafile_online_count $STANDBY_TNS`)
    90.  
    91. compare_value ${PRIMARY_TNS_DIC[redo_count]} ${STANDBY_TNS_DIC[redo_count]} "redo_count"
    92. compare_value ${PRIMARY_TNS_DIC[standby_count]} ${STANDBY_TNS_DIC[standby_count]} "standby_count"
    93. compare_value ${PRIMARY_TNS_DIC[tempfile_count]} ${STANDBY_TNS_DIC[tempfile_count]} "tempfile_count"
    94. compare_value ${PRIMARY_TNS_DIC[datafile_online_count]} ${STANDBY_TNS_DIC[datafile_online_count]} "datafile_online_count"
    95.  
    96. echo -e "\n"
    97.  
    98. echo -e "--------------------------------------------------------------------------------"
    99. echo -e "- 从节点redo日志状态检查                                                        "
    100. echo -e "--------------------------------------------------------------------------------\n"
    101.  
    102. # 只有从库需要检查
    103. STANDBY_TNS_DIC+=([redo_status_count]=`get_redo_status_count $STANDBY_TNS`)
    104. check_redo_status_count ${STANDBY_TNS_DIC[db_create_file_dest]} ${STANDBY_TNS_DIC[log_file_name_convert]} ${STANDBY_TNS_DIC[redo_status_count]} 
    105. echo -e "\n"
    106.  
    107.  
    108. echo -e "--------------------------------------------------------------------------------"
    109. echo -e "- 闪回开启及保留时间检查                                                        "
    110. echo -e "--------------------------------------------------------------------------------\n"
    111.  
    112. # --------------------------- flashback_on 与 db_flashback_retention_target -------------------------------
    113.  
    114. PRIMARY_TNS_DIC+=([flashback_on]=`get_flashback_on $PRIMARY_TNS`)
    115. echo -e "- primary database                                                           "
    116. compare_value ${PRIMARY_TNS_DIC[flashback_on]} "NO" "flashback_on"
    117. compare_value ${PRIMARY_TNS_DIC[db_flashback_retention_target]} "4320" "db_flashback_retention_target"
    118.  
    119. STANDBY_TNS_DIC+=([flashback_on]=`get_flashback_on $STANDBY_TNS`)
    120. echo -e "- standby database                                                                  "
    121. compare_value ${STANDBY_TNS_DIC[flashback_on]} "YES" "flashback_on"
    122. compare_value ${STANDBY_TNS_DIC[db_flashback_retention_target]} "4320" "db_flashback_retention_target"
    123.  
    124. echo -e "\n"
    125.  
    126. echo -e "--------------------------------------------------------------------------------"
    127. echo -e "- FRA区设置检查                                                                   "
    128. echo -e "--------------------------------------------------------------------------------\n"
    129.  
    130. # --------------------------- db_recovery_file_dest 与 db_recovery_file_dest_size -------------------------------
    131. # 主从库设置是否相同
    132. compare_value ${PRIMARY_TNS_DIC[db_recovery_file_dest]} ${STANDBY_TNS_DIC[db_recovery_file_dest]} "db_recovery_file_dest"
    133. compare_value ${PRIMARY_TNS_DIC[db_recovery_file_dest_size]} ${STANDBY_TNS_DIC[db_recovery_file_dest_size]} "db_recovery_file_dest_size"
    134.  
    135. echo -e "\n"
    136.  
    137.  
    138. echo -e "--------------------------------------------------------------------------------"
    139. echo -e "- 各节点主从相关参数检查                                                        "
    140. echo -e "--------------------------------------------------------------------------------\n"
    141.  
    142. # --------------------------- fal_client 与 fal_server ---------------------------
    143.  
    144. # fal_server的值应该包括对方，因为${PRIMARY_TNS_DIC[fal_server]}返回值包含逗号，因此需要在传参时加""，避免参数传入函数后被截断
    145.  
    146. # 主节点参数值
    147. include_value  $PRIMARY_TNS "fal_server" "${PRIMARY_TNS_DIC[fal_server]}" $STANDBY_TNS
    148. # 从节点参数值
    149. include_value  $STANDBY_TNS "fal_server" "${STANDBY_TNS_DIC[fal_server]}" $PRIMARY_TNS
    150.  
    151. # fal_client的值应该包含自己
    152. # 主节点参数值
    153. include_value  $PRIMARY_TNS "fal_client" "${PRIMARY_TNS_DIC[fal_client]}" $PRIMARY_TNS
    154. # 从节点参数值
    155. include_value  $STANDBY_TNS "fal_client" "${STANDBY_TNS_DIC[fal_client]}" $STANDBY_TNS
    156.  
    157.  
    158. # --------------------------- log_archive_config -------------------------------
    159. # 主节点参数值
    160. include_value $PRIMARY_TNS "log_archive_config" ${PRIMARY_TNS_DIC[log_archive_config]} $STANDBY_TNS
    161. # 从节点参数值
    162. include_value $STANDBY_TNS "log_archive_config" ${STANDBY_TNS_DIC[log_archive_config]} $PRIMARY_TNS
    163.  
    164. # --------------------------- log_archive_max_processes -------------------------------
    165. check_log_archive_max_processes $PRIMARY_TNS ${PRIMARY_TNS_DIC[log_archive_max_processes]} 
    166. check_log_archive_max_processes $STANDBY_TNS ${STANDBY_TNS_DIC[log_archive_max_processes]} 
    167.  
    168. # --------------------------- log_archive_dest_n 与 log_archive_dest_state_n -------------------------------
    169.  
    170. check_archive_dest $PRIMARY_TNS
    171. check_archive_dest $STANDBY_TNS
    172.  
    173. check_archive_dest_state $PRIMARY_TNS
    174. check_archive_dest_state $STANDBY_TNS
    175.  
    176. # --------------------------- OMF 与 db_file_name_convert,log_file_name_convert -------------------------------
    177.  
    178. check_omf_and_convert_params $PRIMARY_TNS ${PRIMARY_TNS_DIC[db_create_file_dest]} ${PRIMARY_TNS_DIC[db_file_name_convert]} ${PRIMARY_TNS_DIC[log_file_name_convert]}
    179. check_omf_and_convert_params $STANDBY_TNS ${STANDBY_TNS_DIC[db_create_file_dest]} ${STANDBY_TNS_DIC[db_file_name_convert]} ${STANDBY_TNS_DIC[log_file_name_convert]}
    180.  
    181. echo -e "\n"
    182.  
    183.  
    184. echo -e "--------------------------------------------------------------------------------"
    185. echo -e "- 从节点延迟 MRP进程与触发器检查                                                "
    186. echo -e "--------------------------------------------------------------------------------\n"
    187.  
    188. check_db_lag $STANDBY_TNS "transport lag"
    189. check_db_lag $STANDBY_TNS "apply lag"
    190.  
    191. check_mrp_process $STANDBY_TNS
    192.  
    193. check_mrp_trigger $PRIMARY_TNS
    194. check_mrp_trigger $STANDBY_TNS
    195. echo -e "\n"
    196.  
    197. echo -e "--------------------------------------------------------------------------------"
    198. echo -e "- dblink检查                                                                   "
    199. echo -e "--------------------------------------------------------------------------------\n"
    200.  
    201. # --------------------------- dblink -------------------------------
    202. # 检查主库即可
    203. check_dblink $PRIMARY_TNS
    204. echo -e "\n"

    2. dg_precheck_func_v1.4.sh

    1. #!/bin/bash
    2.  
    3. #**********************************************************************************
    4. # Author: Hehuyi_In
    5. # Date: 2022年06月16日
    6. # FileName: dg_precheck_func_v1.4.sh
    7. # 
    8. # Description:
    9. # 2022-06-16 v_1.0 just get and print the infomation we want
    10. # 2022-06-17 v_1.1 auto check the database role
    11. # 2022-06-22 v_1.2 adjust the output format
    12. # 2022-06-28 v_1.3 add compare function, compare two nodes at a time
    13. # 2022-06-28 split to two scripts dg_precheck_main_v1.0.sh and dg_precheck_func_v1.0.sh
    14. # 2022-07-06 dg_precheck_func_v1.1 check os info
    15. # 2022-07-12 dg_precheck_func_v1.2 adjust the output format
    16. # 2022-07-13 dg_precheck_func_v1.3 adjust os info check function
    17. # 2022-10-19 dg_precheck_func_v1.4 add checking items: dblink,standby_file_management,flashback_on,db_flashback_retention_target,db_recovery_file_dest,db_recovery_file_dest_size 
    18. #**********************************************************************************
    19.  
    20. # 颜色定义
    21. # 红色
    22. ERROR_COLOR='\e[1;31m'
    23. # 绿色
    24. NARMAL_COLOR='\e[1;32m'
    25. # 黄色
    26. WARNING_COLOR='\e[1;33m'
    27. # 重置
    28. RESET_COLOR='\e[0m'
    29.  
    30. # ------------------------------------------------------------------------
    31. # 检查TNS_LIST中各tnsname对应数据库角色
    32. auto_check_db_role() {
    33.     for TNS in ${TNS_LIST[@]}; do
    34.         DB_ROLE=$(
    35.             sqlplus -s $DBUSER@$TNS as sysdba <<EOF
    37. set heading off feedback off pagesize 0 verify off echo off
    38. select database_role from v\$database;
    39. EOF
    40.         )
    41.         # 主库，则赋值给 PRIMARY_TNS
    42.         if [[ "$DB_ROLE" = "PRIMARY" ]]; then
    43.             PRIMARY_TNS=$TNS
    44.             # 从库，则赋值给 STANDBY_TNS
    45.         elif [[ "$DB_ROLE" = "PHYSICAL STANDBY" ]]; then
    46.             STANDBY_TNS=$TNS
    47.         fi        
    48.     done
    49.  
    50.     if [[ "$PRIMARY_TNS" = "" ]]; then
    51.        echo -e "${ERROR_COLOR} $(date "+%Y-%m-%d %H:%M:%S") | Error! There is no primary database tns ${RESET_COLOR}"
    52.        exit 1
    53.     elif [[ "$STANDBY_TNS" = "" ]]; then
    54.        echo -e "${ERROR_COLOR} $(date "+%Y-%m-%d %H:%M:%S") | Error! There is no standby database tns ${RESET_COLOR}"
    55.        exit 1
    56.     fi
    57. }
    58.  
    59. # ----------------------------------获取操作系统信息--------------------------------------
    60. # 参数1：TNS连接串
    61.  
    62. # get_cpu_info() {
    63. #     sqlplus -s $DBUSER@$1 as sysdba <
    64. # !cat /proc/cpuinfo | grep "processor" | wc -l
    65. # EOF
    66. # }
    67.  
    68. # get_memory_info() {
    69. #     sqlplus -s $DBUSER@$1 as sysdba <
    70. # !free -h | grep 'Mem:' | awk '{print \$2}'
    71. # EOF
    72. # }
    73.  
    74. # get_disk_info() {
    75. #     sqlplus -s $DBUSER@$1 as sysdba <
    76. # !df -m | grep -v 'Filesystem' | awk '{sum+=\$2} END {print sum}'
    77. # EOF
    78. # }
    79.  
    80. # 获取Oracle参数值
    81. # 参数1：TNS连接串，参数2：待检查参数名（不区分大小写）
    82.  
    83. get_params_value() {
    84.     if [[ "$1" != "" && "$2" != "" ]]; then
    85.         sqlplus -s $DBUSER@$1 as sysdba <<EOF
    86. set heading off feedback off pagesize 0 verify off echo off
    87. select value from v\$parameter where upper(name)=upper('$2');
    88. EOF
    89.     else
    90.         echo -e "${ERROR_COLOR} $(date "+%Y-%m-%d %H:%M:%S") | Error! get_params_value() parameter 1 and 2 can't be null ${RESET_COLOR}"
    91.         exit 1
    92.     fi
    93. }
    94.  
    95. # log_archive_max_processes 参数检查
    96. # 参数1：TNS连接串，参数2：待检查参数值
    97.  
    98. check_log_archive_max_processes() {
    99.     if [[ "$1" != "" && "$2" != "" ]]; then
    100.         if [[ "$2" -ge 4 ]]; then
    101.             printf "${NARMAL_COLOR}%-10s | Success | log_archive_max_processes >= 4${RESET_COLOR}\n" $1
    102.         else
    103.             printf "${WARNING_COLOR}%-10s | Warning | log_archive_max_processes < 4${RESET_COLOR}\n" $1
    104.         fi
    105.     else
    106.         echo "${ERROR_COLOR} $(date "+%Y-%m-%d %H:%M:%S") | Error! check_log_archive_max_processes() parameter 1 and 2 can't be null ${RESET_COLOR}"
    107.         exit 1
    108.     fi
    109. }
    110.  
    111. # 检查log_archive_dest_n参数，是否有设置DELAY关键字（延迟从库）
    112. # 参数1：TNS连接串
    113.  
    114. check_archive_dest() {
    115.     if [[ "$1" != "" ]]; then
    116.             COUNT=$(
    117.         sqlplus -s $DBUSER@$1 as sysdba <<EOF
    118. set heading off feedback off pagesize 0 verify off echo off
    119. select count(*) FROM v\$parameter where name like 'log_archive_dest%' and upper(value) like '%DELAY%';
    120. EOF
    121.        )
    122.         if [[ "$COUNT" -eq 0 ]]; then
    123.             printf "${NARMAL_COLOR}%-10s | Success | There is no DELAY attribute in log_archive_dest_n${RESET_COLOR}\n" $1
    124.         else
    125.             printf "${WARNING_COLOR}%-10s | Warning | There is DELAY attribute in log_archive_dest_n${RESET_COLOR}\n" $1
    126.         fi
    127.     else
    128.         echo -e "${ERROR_COLOR} $(date "+%Y-%m-%d %H:%M:%S") | Error! check_archive_dest() parameter 1 can't be null ${RESET_COLOR}"
    129.         exit 1
    130.     fi
    131. }
    132.  
    133. # 检查log_archive_dest_state_n参数，是否有设置DEFER
    134. # 参数1：TNS连接串
    135.  
    136. check_archive_dest_state() {
    137.     if [[ "$1" != "" ]]; then
    138.             COUNT=$(
    139.         sqlplus -s $DBUSER@$1 as sysdba <<EOF
    140. set heading off feedback off pagesize 0 verify off echo off
    141. select count(*) FROM v\$parameter where name like 'log_archive_dest_state%' and upper(value)='DEFER';
    142. EOF
    143.        )
    144.         if [[ "$COUNT" -eq 0 ]]; then
    145.             printf "${NARMAL_COLOR}%-10s | Success | There is no DEFER value in log_archive_dest_state_n${RESET_COLOR}\n" $1 
    146.         else
    147.             printf "${WARNING_COLOR}%-10s | Warning | There is DEFER value in log_archive_dest_state_n${RESET_COLOR}\n" $1
    148.         fi
    149.     else
    150.         echo -e "${ERROR_COLOR} $(date "+%Y-%m-%d %H:%M:%S") | Error! check_archive_dest_state() parameter 1 can't be null ${RESET_COLOR}"
    151.         exit 1
    152.     fi
    153. }
    154.  
    155. # OMF与convert参数检查
    156. # 参数1：TNS连接串；参数2：db_create_file_dest参数值；参数3：db_file_name_convert参数值；参数4：log_file_name_convert参数值
    157.  
    158. check_omf_and_convert_params() {
    159.     if [[ "$2" != ""  ]]; then
    160.              printf "${NARMAL_COLOR}%-10s | Success | db_create_file_dest parameter was set${RESET_COLOR}\n" $1
    161.     else
    162.         if [[ "$3" != "" && "$4" != "" ]]; then
    163.             printf "${NARMAL_COLOR}%-10s | Success | dbfile and logfile convert parameters were set${RESET_COLOR}\n" $1
    164.         else
    165.             printf "${WARNING_COLOR}%-10s | Warning | db_create_file_dest,dbfile and logfile convert parameters were set${RESET_COLOR}\n" $1
    166.         fi
    167.     fi
    168. }
    169.  
    170. # 获取各类文件数 
    171. # 参数1：TNS连接串，参数2：待查询视图名（V$LOG V$STANDBY_LOG V$TEMPFILE）
    172.  
    173. get_file_count() {
    174.     if [[ "$1" != "" ]]; then
    175.         sqlplus -s $DBUSER@$1 as sysdba <<EOF
    176. set heading off feedback off pagesize 0 verify off echo off
    177. SELECT count(*) FROM V\$$2;
    178. EOF
    179.     else
    180.         echo -e "${ERROR_COLOR} $(date "+%Y-%m-%d %H:%M:%S") | Error! get_file_count() parameter 1 and 2 can't be null ${RESET_COLOR}"
    181.         exit 1
    182.     fi
    183. }
    184.  
    185. # 获取指定状态的redo文件数
    186. # 参数1：TNS连接串
    187.  
    188. get_redo_status_count() {
    189.     if [[ "$1" != "" ]]; then
    190.         sqlplus -s $DBUSER@$1 as sysdba <<EOF
    191. set heading off feedback off pagesize 0 verify off echo off
    192. SELECT count(*) FROM V\$LOG WHERE STATUS NOT IN ('UNUSED', 'CLEARING','CLEARING_CURRENT');
    193. EOF
    194.     else
    195.         echo -e "${ERROR_COLOR} $(date "+%Y-%m-%d %H:%M:%S") | Error! get_redo_status_count() parameter 1 can't be null ${RESET_COLOR}"
    196.         exit 1
    197.     fi
    198. }
    199.  
    200. # 检查指定状态的redo文件数
    201. # 参数1：db_create_file_dest参数值；参数2：log_file_name_convert参数值；参数3：${STANDBY_TNS_DIC[redo_status_count]} 
    202.  
    203. check_redo_status_count() {
    204.     # 如果设置了OMF或者LOG_FILE_NAME_CONVERT，则可以跳过此步骤
    205.     if [[ "$1" != "" || "$2" != "" ]]; then
    206.             printf "${NARMAL_COLOR}%-30s | %-8s | There is no need to check this item${RESET_COLOR}\n" redo_status_count Success
    207.     else
    208.         # 否则，指定状态的redo文件数应该为0
    209.         if [[ "$3" -eq 0 ]]; then
    210.             printf "${WARNING_COLOR}%-30s | %-8s | redo_status_count value is 0${RESET_COLOR}\n" redo_status_count Warning
    211.         else
    212.             # 否则，发出告警
    213.             printf "${WARNING_COLOR}%-30s | %-8s | redo_status_count value is not 0${RESET_COLOR}\n" $1 Warning
    214.         fi
    215.     fi
    216. }
    217.  
    218. # 获取online状态的数据文件数量
    219. # 参数1：TNS连接串
    220.  
    221. get_datafile_online_count() {
    222.     if [[ "$1" != "" ]]; then
    223.         sqlplus -s $DBUSER@$1 as sysdba <<EOF
    224. set heading off feedback off pagesize 0 verify off echo off
    225. SELECT count(*) FROM V\$DATAFILE WHERE STATUS='ONLINE';
    226. EOF
    227.     else
    228.         echo -e "${ERROR_COLOR} $(date "+%Y-%m-%d %H:%M:%S") | Error! get_datafile_online_count() parameter 1 can't be null ${RESET_COLOR}"
    229.         exit 1
    230.     fi
    231. }
    232.  
    233. # 获取数据库闪回功能开启状态
    234. # 参数1：TNS连接串
    235. get_flashback_on() {
    236.     if [[ "$1" != "" ]]; then
    237.         sqlplus -s $DBUSER@$1 as sysdba <<EOF
    238. set heading off feedback off pagesize 0 verify off echo off
    239. SELECT FLASHBACK_ON FROM V\$DATABASE;
    240. EOF
    241.     else
    242.         echo -e "${ERROR_COLOR} $(date "+%Y-%m-%d %H:%M:%S") | Error! get_flashback_on() parameter 1 can't be null ${RESET_COLOR}"
    243.         exit 1
    244.     fi
    245. }
    246.  
    247. # mrp触发器检查
    248. # 参数1：TNS连接串
    249.  
    250. check_mrp_trigger() {
    251.     if [[ "$1" != "" ]]; then
    252.         COUNT=$(
    253.             sqlplus -s $DBUSER@$1 as sysdba <<EOF
    254. set heading off feedback off pagesize 0 verify off echo off
    255. select count(*) FROM dba_triggers where trigger_name in ('AUTO_START_STANDBY_MRP','AUTO_START_STANDBY_MRP_PDB');
    256. EOF
    257.        )
    258.         if [[ "$COUNT" -eq 0 ]]; then
    259.             printf "${ERROR_COLOR}%-10s | %-8s | There is no MRP trigger${RESET_COLOR}\n" $1 Error
    260.         else
    261.             printf "${NARMAL_COLOR}%-10s | %-8s | MRP trigger was found${RESET_COLOR}\n" $1 Success
    262.         fi
    263.     else
    264.         echo -e "${ERROR_COLOR}$(date "+%Y-%m-%d %H:%M:%S") | Error! check_mrp_trigger() parameter 1 can't be null ${RESET_COLOR}"
    265.         exit 1
    266.     fi
    267. }
    268.  
    269. # mrp进程检查
    270. # 参数1：TNS连接串
    271.  
    272. check_mrp_process() {
    273.     if [[ "$1" != "" ]]; then
    274.         COUNT=$(
    275.             sqlplus -s $DBUSER@$1 as sysdba <<EOF
    276. set heading off feedback off pagesize 0 verify off echo off
    277. select count(*) FROM v\$managed_standby where process like 'MRP%';
    278. EOF
    279.         )
    280.         if [[ "$COUNT" -eq 0 ]]; then
    281.             printf "${ERROR_COLOR}%-10s | %-8s | There is no MRP process${RESET_COLOR}\n" $1 Error
    282.         else
    283.             printf "${NARMAL_COLOR}%-10s | %-8s | MRP process was found${RESET_COLOR}\n" $1 Success
    284.         fi
    285.  
    286.     else
    287.         echo -e "${ERROR_COLOR} $(date "+%Y-%m-%d %H:%M:%S") | Error! check_mrp_process() parameter 1 can't be null ${RESET_COLOR}"
    288.         exit 1
    289.     fi
    290. }
    291.  
    292. # 检查从库延迟（transport lag,apply lag ）
    293. # 参数1：TNS连接串，参数2：待检查的lag类型
    294.  
    295. check_db_lag(){
    296. if [[ "$1" != "" && "$2" != "" ]]	
    297. then
    298. DB_LAG=`sqlplus -s $DBUSER@$1 as sysdba << EOF 
    300. set heading off feedback off pagesize 0 verify off echo off
    301. select (extract(second from to_dsinterval(value)) + 
    302.        extract(minute from to_dsinterval(value)) * 60 +
    303.        extract(hour from to_dsinterval(value)) * 60 * 60 +
    304.        extract(day from to_dsinterval(value)) * 60 * 60 * 24) as retvalue
    305. from v\\$dataguard_stats 
    306. where name = '$2';
    308. EOF`
    309.  
    310. #去除左侧空格
    311. DB_LAG=`eval echo $DB_LAG`
    312.  
    313. if [[ "$DB_LAG" -gt 30 ]]
    314. then
    315.     printf "${WARNING_COLOR}%-10s | %-8s | the $2 is too large($DB_LAG seconds)${RESET_COLOR}\n" $1 Warning
    316. else 
    317.     printf "${NARMAL_COLOR}%-10s | %-8s | the $2 is $DB_LAG second(s)${RESET_COLOR}\n" $1 Success
    318. fi
    319.  
    320. else
    321.     echo -e "${ERROR_COLOR} $(date "+%Y-%m-%d %H:%M:%S") | Error! check_db_lag() parameter 1 and 2 can't be null ${RESET_COLOR}"
    322.     exit 1  
    323. fi
    324. }
    325.  
    326. # dblink检查
    327. # 参数1：TNS连接串
    328.  
    329. check_dblink() {
    330.     if [[ "$1" != "" ]]; then
    331.         COUNT=$(
    332.             sqlplus -s $DBUSER@$1 as sysdba <<EOF
    333. set heading off feedback off pagesize 0 verify off echo off
    334. select count(*) FROM dba_db_links;
    335. EOF
    336.        )
    337.         if [[ "$COUNT" -eq 0 ]]; then
    338.             printf "${NARMAL_COLOR}%-10s | %-8s | There is no dblink in our database${RESET_COLOR}\n" $1 Success
    339.         else
    340.             printf "${WARNING_COLOR}%-10s | %-8s | dblink was found,please check the tns file and firewall${RESET_COLOR}\n" $1 Warning
    341.         fi
    342.     else
    343.         echo -e "${ERROR_COLOR}$(date "+%Y-%m-%d %H:%M:%S") | Error! check_mrp_trigger() parameter 1 can't be null ${RESET_COLOR}"
    344.         exit 1
    345.     fi
    346. }
    347.  
    348. # 对比两个值是否相等
    349. # 参数1：待比较值1，参数2：待比较值2
    350. compare_value() {
    351.  
    352.     if [[ "$1" = "$2" ]]; then
    353.         printf "${NARMAL_COLOR}%-30s | %-8s | The value is $1 ${RESET_COLOR}\n" $3 Success
    354.     else
    355.         printf "${WARNING_COLOR}%-30s | %-8s | The value 1 is: $1,but value 2 is: $2 ${RESET_COLOR}\n" $3 Warning
    356.     fi
    357. }
    358.  
    359.  
    360. # 待判断参数值中是否包含给定的子字符串
    361. # 参数1：TNS连接串；参数2：待判断参数名；参数3：待判断参数值；参数3：子字符串
    362. # 例如：include_value $PRIMARY_TNS "fal_server" "${PRIMARY_TNS_DIC[fal_server]}" $STANDBY_TNS
    363.  
    364. include_value(){
    365.     # 过滤结果
    366.     cnt=`echo $3 | grep $4 | wc -l`
    367.  
    368.     if [[ "$cnt" -gt 0 ]]; then
    369.         printf "${NARMAL_COLOR}%-10s | %-8s| %-20s | The value is $3,include $4 ${RESET_COLOR}\n" $1 Success $2
    370.     else
    371.         printf "${WARNING_COLOR}%-10s | %-8s| %-20s | The value is $3,doesn't include $4 ${RESET_COLOR}\n" $1 Warning $2
    372.     fi
    373. }

    3. 执行效果

    二、 执行切换

    1. dg_switchover_v1.4.sh

    1. #!/bin/bash
    2.  
    3. #**********************************************************************************
    4. # Author: Hehuyi_In
    5. # Date: 2022年06月10日
    6. # FileName: dg_switchover_v1.4.sh
    7. # 
    8. # For sys user, execute the script directly. 
    9. # For other sysdba privileged users, you need to manually synchronize the password file to standby server and chown file mode to 640 in versions below 12.2.
    10. #
    11. # Example: ./dg_switchover_v1.4.sh or ./dg_switchover_v1.4.sh &>> dg_switchover.log 
    12. # Description: 
    13. # 2022-06-10 v_1.0 only oracle dataguard switchover
    14. # 2022-06-10 v_1.1 check instance status and db role after switchover.
    15. # 2022-06-10 v_1.2 check transport lag,apply lag,switchover_status before switchover.
    16. # 2022-06-13 v_1.3 add logging for main steps 
    17. # 2022-06-14 v_1.4 auto check and select the target primary and standby database.
    18. #**********************************************************************************
    19.  
    20. # 连接信息
    21. DBUSER='sys/xxxxx'
    22.  
    23. # TNS列表，越靠前的被选为目标主库的概率较高
    24. TNS_LIST=(mrptest mrptest_dg mrpuat)
    25.  
    26. # 列表元素个数检查，目前仅支持一主一从或一主两从
    27. if [[ ${#TNS_LIST[@]} -lt 2 || ${#TNS_LIST[@]} -gt 3 ]]
    28. then
    29. 	echo "`date "+%Y-%m-%d %H:%M:%S"` | Error! The number of standby db is less than 1 or greater than 2."
    30. fi
    31.  
    32. # ------------------------------------------------------------------------
    33. # 判断 TNS_LIST 中每个连接串角色，并设置各切换目标参数
    34. # 若为主库，则作为TARGET_STANDBY_TNS
    35. # 若为从库，则检查lag。如果lag均为0，则作为TARGET_PRIMARY_TNS，剩余的从库作为OUTLOOKING_STANDBY_TNS
    36. # 如果所有从库lag都大于0，则报错退出。
    37.  
    38. auto_set_target_db_tns(){
    39. 	
    40. 	for TNS in ${TNS_LIST[@]}
    41. 	do
    42. 	    # 获取TNS对应db角色
    43. 		DB_ROLE=`check_db_role $TNS`
    44.  
    45. 		# 若为主库，则赋值给TARGET_STANDBY_TNS
    46. 		if [[ "$DB_ROLE" = "PRIMARY" ]]
    47. 		then
    48. 			TARGET_STANDBY_TNS=$TNS			
    49. 		# 若为从库
    50. 		elif [[ "$DB_ROLE" = "PHYSICAL STANDBY" ]]
    51. 		then
    52. 			    # 如果TARGET_PRIMARY_TNS不为空，说明已经设置过了目标主库，直接设置旁观从库即可
    53. 				if [[ "$TARGET_PRIMARY_TNS" != "" ]]
    54. 				then
    55. 					OUTLOOKING_STANDBY_TNS=$TNS				
    56. 				else 
    57. 				    # 若TARGET_PRIMARY_TNS为空，则需要再检查lag
    58. 					DB_TRAN_LAG=`check_db_lag $TNS "transport lag"`
    59. 					DB_APPLY_LAG=`check_db_lag $TNS "apply lag"`
    60. 			
    61. 				    # 如果lag均为0，则赋值给TARGET_PRIMARY_TNS
    62. 					if [[ "$DB_TRAN_LAG" -eq 0 && "$DB_APPLY_LAG" -eq 0 ]]
    63. 					then
    64. 						TARGET_PRIMARY_TNS=$TNS
    65. 					fi	
    66. 				fi
    67. 		fi
    68. 	done
    69.  
    70.     # 设置后检查，若TARGET_PRIMARY_TNS或TARGET_STANDBY_TNS为空，直接报错退出
    71. 	if [[ "$TARGET_PRIMARY_TNS" = "" || "$TARGET_STANDBY_TNS" = "" ]]	
    72.     then
    73. 		echo "`date "+%Y-%m-%d %H:%M:%S"` | Error! TARGET_PRIMARY_TNS and TARGET_STANDBY_TNS can't be null."
    74.   		exit 1  		
    75. 	fi
    76.  
    77. }
    78.  
    79. # ------------------------------------------------------------------------
    80. # 检查从库延迟（transport lag,apply lag ）
    81. # 参数1为db对应的tnsname，参数2为待检查的lag
    82. check_db_lag(){
    83. if [[ "$1" != "" && "$2" != "" ]]	
    84. then
    85. sqlplus -s $DBUSER@$1 as sysdba << EOF 
    86. set heading off feedback off pagesize 0 verify off echo off
    87. select (extract(second from to_dsinterval(value)) + 
    88.        extract(minute from to_dsinterval(value)) * 60 +
    89.        extract(hour from to_dsinterval(value)) * 60 * 60 +
    90.        extract(day from to_dsinterval(value)) * 60 * 60 * 24) as retvalue
    91. from v\$dataguard_stats 
    92. where name = '$2';
    93. EOF
    94.  
    95. else
    96.     echo "`date "+%Y-%m-%d %H:%M:%S"` | Error! parameter 1,2 can't be null."
    97.     exit 1  
    98. fi
    99. }
    100.  
    101. # ------------------------------------------------------------------------
    102.  
    103. # 检查切换前switchover_status
    104. # 参数1为db对应的tnsname，参数2为待检查的状态
    105. check_switchover_status(){
    106. if [[ "$1" != "" && "$2" != "" ]]	
    107. then
    108. STATUS=`sqlplus -s $DBUSER@$1 as sysdba << EOF 
    109. set heading off feedback off pagesize 0 verify off echo off
    110. select switchover_status from v\\$database;
    111. EOF`
    112.  
    113. if [[ "$STATUS" = $2 || "$STATUS" = "SESSIONS ACTIVE" ]]
    114. then
    115.     echo "`date "+%Y-%m-%d %H:%M:%S"` | Success! switchover_status of $1 is $STATUS."
    116. else 
    117.     echo "`date "+%Y-%m-%d %H:%M:%S"` | Error! switchover_status of $1 is $STATUS (should be $2 or SESSIONS ACTIVE)."
    118.     exit 1
    119. fi
    120.  
    121. else
    122.     echo "`date "+%Y-%m-%d %H:%M:%S"` | Error! parameter 1 and 2 can't be null."
    123.     exit 1  
    124. fi
    125. }
    126.  
    127. # ------------------------------------------------------------------------
    128. # 检查db角色，参数1为db对应的tnsname
    129.  
    130. check_db_role(){
    131. if [[ "$1" != "" ]]	
    132. then
    133. sqlplus -s $DBUSER@$1 as sysdba << EOF 
    135. set heading off feedback off pagesize 0 verify off echo off
    136. select database_role from v\$database;
    137. EOF
    138.  
    139. else
    140.     echo "`date "+%Y-%m-%d %H:%M:%S"` | Error! parameter 1 should be tnsname."
    141.     exit 1  
    142. fi
    143. }
    144.  
    145. # ------------------------------------------------------------------------
    146. # 检查切换后实例状态
    147. # 参数1为db对应的tnsname
    148.  
    149. check_instance_status(){
    150. if [[ "$1" != "" ]]	
    151. then
    152. STATUS=`sqlplus -s $DBUSER@$1 as sysdba << EOF 
    154. set heading off feedback off pagesize 0 verify off echo off
    155. select status from v\\$instance;
    157. EOF`
    158.  
    159. if [[ "$STATUS" != "OPEN" ]]
    160. then
    161.     echo "`date "+%Y-%m-%d %H:%M:%S"` | Error! instance status of $1 is $STATUS after switchover."
    162.     exit 1
    163. else 
    164.     echo "`date "+%Y-%m-%d %H:%M:%S"` | Success! instance status of $1 is $STATUS after switchover."
    165. fi
    166.  
    167. else
    168.     echo "`date "+%Y-%m-%d %H:%M:%S"` | Error! parameter 1 can't be null."
    169.     exit 1  
    170. fi
    171. }
    172.  
    173. # ------------------------------- main() -----------------------------------------
    174.  
    175. echo "------------------------------------------------------------------------"
    176. echo "`date "+%Y-%m-%d %H:%M:%S"` | Precheck before switchover..."
    177.  
    178. # 判断 TNS_LIST 中每个连接串角色，并设置各切换目标参数
    179. auto_set_target_db_tns
    180.  
    181. # 设置后各目标参数值
    182. echo "`date "+%Y-%m-%d %H:%M:%S"` | TARGET_PRIMARY_TNS: $TARGET_PRIMARY_TNS"
    183. echo "`date "+%Y-%m-%d %H:%M:%S"` | TARGET_STANDBY_TNS: $TARGET_STANDBY_TNS"
    184. echo "`date "+%Y-%m-%d %H:%M:%S"` | OUTLOOKING_STANDBY_TNS: $OUTLOOKING_STANDBY_TNS"
    185.  
    186. # 切换前switchover_status检查
    187. check_switchover_status $TARGET_STANDBY_TNS "TO STANDBY"
    188.  
    189. # 目标从库（原主库）执行：将原主库转切为从库
    190. echo "`date "+%Y-%m-%d %H:%M:%S"` | Begin switchover $TARGET_STANDBY_TNS to standby database..."
    191.     
    192. sqlplus -s $DBUSER@$TARGET_STANDBY_TNS as sysdba << EOF 
    193. alter database commit to switchover to standby with session shutdown;
    194. shutdown immediate
    195. EOF
    196.  
    197. echo "`date "+%Y-%m-%d %H:%M:%S"` | Restarting $TARGET_STANDBY_TNS database..."
    198.  
    199. # 重新启动目标从库
    200. sqlplus -s $DBUSER@$TARGET_STANDBY_TNS as sysdba << EOF 
    201. startup
    202. EOF
    203.  
    204. # 切换后角色检查
    205. DB_ROLE=`check_db_role $TARGET_STANDBY_TNS`
    206.  
    207. if [[ "$DB_ROLE" != "PHYSICAL STANDBY" ]]; 
    208. then
    209.     echo "`date "+%Y-%m-%d %H:%M:%S"` | Error! db role of $TARGET_STANDBY_TNS is $DB_ROLE(should be PHYSICAL STANDBY) after switchover."
    210.     exit 1
    211. else 
    212.     echo "`date "+%Y-%m-%d %H:%M:%S"` | Success! db role of $TARGET_STANDBY_TNS is $DB_ROLE after switchover."
    213. fi
    214.  
    215. # 切换后实例状态检查
    216. check_instance_status $TARGET_STANDBY_TNS
    217.  
    218. echo "`date "+%Y-%m-%d %H:%M:%S"` | Finish switchover $TARGET_STANDBY_TNS to standby database."
    219.  
    220. # ------------------------------------------------------------------------
    221.  
    222. echo "`date "+%Y-%m-%d %H:%M:%S"` | Precheck before switchover $TARGET_PRIMARY_TNS to primary database..."
    223.  
    224. # 切换前switchover_status检查
    225. check_switchover_status $TARGET_PRIMARY_TNS "TO PRIMARY"
    226.  
    227. # 目标主库（原从库）执行
    228. # 将原从库切为主库
    229.  
    230. echo "`date "+%Y-%m-%d %H:%M:%S"` | Begin switchover $TARGET_PRIMARY_TNS to primary database..."
    231.  
    232. sqlplus -s $DBUSER@$TARGET_PRIMARY_TNS as sysdba << EOF 
    233. alter database commit to switchover to primary with session shutdown;
    234. ALTER DATABASE OPEN;
    235. EOF
    236.  
    237. # 切换后角色检查
    238. DB_ROLE=`check_db_role $TARGET_PRIMARY_TNS`
    239.  
    240. if [[ "$DB_ROLE" != "PRIMARY" ]]; 
    241. then
    242.     echo "`date "+%Y-%m-%d %H:%M:%S"` | Error! db role of $1 is $DB_ROLE(should be PRIMARY) after switchover."
    243.     exit 1
    244. else 
    245.     echo "`date "+%Y-%m-%d %H:%M:%S"` | Success! db role of $1 is $DB_ROLE after switchover."
    246. fi
    247.  
    248. # 切换后实例状态检查
    249. check_instance_status $TARGET_PRIMARY_TNS
    250.  
    251. # 切换后从库lag检查
    252. DB_TRAN_LAG=`check_db_lag $TARGET_STANDBY_TNS "transport lag"`
    253. DB_APPLY_LAG=`check_db_lag $TARGET_STANDBY_TNS "apply lag"`
    254.  
    255. echo "`date "+%Y-%m-%d %H:%M:%S"` | After switchover: DB_TRAN_LAG is $DB_TRAN_LAG second(s), DB_APPLY_LAG is $DB_APPLY_LAG second(s)"
    256.  
    257. echo "`date "+%Y-%m-%d %H:%M:%S"` | Finish switchover $TARGET_PRIMARY_TNS to primary database."
    258.  
    259. # ------------------------------------------------------------------------
    260. # 如果有旁观从库
    261. if [ "$OUTLOOKING_STANDBY_TNS" != "" ]; then
    262.  
    263. echo "`date "+%Y-%m-%d %H:%M:%S"` | Begin onlooking standby database setting..."
    264.  
    265. # 目标主库调整参数，指向旁观从库，先清空再设置，否则有可能报参数冲突
    266. sqlplus -s $DBUSER@$TARGET_PRIMARY_TNS as sysdba << EOF 
    267. alter system set LOG_ARCHIVE_DEST_2='';
    268. alter system set LOG_ARCHIVE_DEST_3='';
    269. alter system set LOG_ARCHIVE_DEST_2='SERVICE=$TARGET_STANDBY_TNS ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=$TARGET_STANDBY_TNS';
    270. alter system set LOG_ARCHIVE_DEST_3='SERVICE=$OUTLOOKING_STANDBY_TNS ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=$OUTLOOKING_STANDBY_TNS';
    271. EOF
    272.  
    273. # 目标从库调整参数，断开到旁观从库的连接
    274. sqlplus -s $DBUSER@$TARGET_STANDBY_TNS as sysdba << EOF 
    275. alter system set LOG_ARCHIVE_DEST_3='';
    276. alter system set LOG_ARCHIVE_DEST_2='SERVICE=$TARGET_PRIMARY_TNS ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=$TARGET_PRIMARY_TNS';
    277. EOF
    278.  
    279. # 旁观从库调整参数，指向新主库
    280. sqlplus -s $DBUSER@$OUTLOOKING_STANDBY_TNS as sysdba  << EOF 
    281. alter system set LOG_ARCHIVE_DEST_3='';
    282. alter system set LOG_ARCHIVE_DEST_2='SERVICE=$TARGET_PRIMARY_TNS ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=$TARGET_PRIMARY_TNS';
    283. EOF
    284.  
    285. # 切换后旁观从库lag检查
    286. DB_TRAN_LAG=`check_db_lag $OUTLOOKING_STANDBY_TNS "transport lag"`
    287. DB_APPLY_LAG=`check_db_lag $OUTLOOKING_STANDBY_TNS "apply lag"`
    288.  
    289. echo "`date "+%Y-%m-%d %H:%M:%S"` | After setting: DB_TRAN_LAG is $DB_TRAN_LAG second(s), DB_APPLY_LAG is $DB_APPLY_LAG second(s)"
    290.  
    291. echo "`date "+%Y-%m-%d %H:%M:%S"` | Finish onlooking standby database setting."
    292.  
    293. else # 没有旁观从库
    294. echo "`date "+%Y-%m-%d %H:%M:%S"` | There is no onlooking standby database."
    295. fi

  • 相关阅读:
    你还在凭感觉来优化性能？
    Redis Desktop Manager安装和使用
    读《DevOps实践指南》有感
    Activiti进阶
    如何优化百度搜索引擎？（10个技巧让你的网站更容易被搜索到）
    聊一聊作为高并发系统基石之一的缓存，会用很简单，用好才是技术活
    [CSS入门到进阶] 你真的了解 width height 吗？
    JAVA中常用序列化与反序列化合集
    windows2022远程桌面连接管理员已结束会话解决方法
    ai绘画部署教程
  • 原文地址：https://blog.csdn.net/Hehuyi_In/article/details/104712805