Oracle
用於設置 Oracle Database Multitenant 環境的腳本
在使用安裝在不同家庭和不同版本中的多個 Oracle 容器數據庫的共享數據庫伺服器上工作時，可能很難快速找到所需的 pdb。我們使用了一個 shell 腳本來設置環境，我添加了一個 DB 查詢函式來顯示 PDBS。範例輸出：
Valid Oracle SIDs are: DBSXX1 SGA: 4900 MB PGA: 1350 MB DBROLE: PRIMARY DBXXX2 SGA: 4900 MB PGA: 1380 MB DBROLE: PHYSICAL STANDBY CDBXX001 [ PDB$SEED PDBXXX1 PDBXXX2 ...] SGA: 64000 MB PGA: 16777 MB DBROLE: PRIMARY CDBXX002 [ PDB$SEED PDBXXX1 PDBXXX2 ...] SGA: 64000 MB PGA: 4443 MB DBROLE: PRIMARY CDBXX003 [ PDB$SEED PDBXXX1 PDBXXX2 ...] SGA: 32000 MB PGA: 11211 MB DBROLE: PRIMARY CDBXX004 [ PDB$SEED PDBXXX1 PDBXXX2 ...] SGA: 32000 MB PGA: 1111 MB DBROLE: PRIMARY CDBXX005: Database offline ... Enter the Oracle SID you require (default: CDBXX001): Oracle SID is now CDBXX001, Oracle Home is /u01/app/oracle/product/19000/)問題是腳本的性能。查詢導致延遲。是否有更好的解決方案來設置 oracle 多租戶的環境/顯示？改進構想？
完整腳本：
if [ -t 0 ] function query_db() { export ORACLE_SID=$1 export ORACLE_HOME=`grep "^${ORACLE_SID}:" /etc/oratab|cut -d: -f2 -s` sqlplus -s / as sysdba<<SQL set feedback off set lines 1000 set pages 0 $2 SQL } then ORACLE_SID="" while [ -z "${ORACLE_SID}" ] do tput clear; tput rev echo "Valid Oracle SIDs are: " tput rmso for SID in `cat /etc/oratab|grep -v "^#"|cut -f1 -d: -s` do if [[ $SID == `ps -ef | grep -w "ora_smon_$SID" | grep -v grep | cut -d _ -f3` ]] then CDB=$(query_db ${SID} "select cdb from v\$database;") PDBS=$(query_db ${SID} "select name from v\$pdbs;") #DBSIZE=$(query_db ${SID} "select sum (bytes) / 1024/1024/1024 AS SIZE from dba_data_files;") SGA=$(query_db ${SID} "select trunc((sum(value)/1024/1024),-1) SGA from v\$sga;") PGA=$(query_db ${SID} "select trunc((sum(pga_max_mem)/1024/1024),-1) PGA from v\$process;") DBROLE=$(query_db ${SID} "select database_role from v\$database;") if [[ "$CDB" == "YES" ]]; then echo -e "\t\t${SID} "[" "${PDBS}" "]" "SGA:" "${SGA} MB" "PGA:" "${PGA} MB" "DBROLE: " ${DBROLE}" else echo -e "\t\t${SID} "SGA:" "${SGA} MB" "PGA:" "${PGA} MB" "DBROLE: " ${DBROLE} " fi else echo -e "\t\t${SID} : Database offline" fi done DEFAULT=`cat /etc/oratab|grep -v "^#"|cut -d: -f1 -s|head -1` echo -e "\nEnter the Oracle SID you require (default: $DEFAULT): \c" read ANSWER [ "${ANSWER}" = "" ] && ANSWER=$DEFAULT export ORACLE_SID=`grep "^${ANSWER}:" /etc/oratab|cut -d: -f1 -s` export ORACLE_HOME=`grep "^${ANSWER}:" /etc/oratab|cut -d: -f2 -s` export AGENT_HOME="u01/app/oracle/product/emagent/agent_inst/" export OH=$ORACLE_HOME export DIAG="/u01/app/oracle/product/diag/rdbms/"${ORACLE_SID,,}"/"${ORACLE_SID^^}"/trace" export DIAGS="/u01/app/oracle/product/diag/rdbms/"${ORACLE_SID,,}s"/"${ORACLE_SID^^}"/trace" export TNS_ADMIN="/u01/app/oracle/product/network/admin" NEW_VER=`cat /etc/oratab | grep ^\`echo ${ORACLE_SID}:\` | cut -d'/' -f 5` export PATH=`echo $PATH | sed "s@/u01/app/oracle/[a-z]*/[0-9]*/[a-z0-9_]*@$ORACLE_HOME@g"` export LD_LIBRARY_PATH=`echo ${LD_LIBRARY_PATH} | sed "s@/u01/app/oracle/[a-z]*/[0-9]*/[a-z0-9_]*@$ORACLE_HOME@g"` if [ "${ORACLE_SID}" = "" ] then echo -e "\n\n ${ANSWER} : Invalid Oracle SID \c" sleep 2 fi done else # Set to first entry in oratab export ORACLE_SID=`cat /etc/oratab|grep -v "^#"|cut -d: -f1 -s|head -1` export ORACLE_HOME=`cat /etc/oratab|grep -v "^#"|cut -d: -f2 -s|head -1` export ORACLE_SID=`grep "^${ANSWER}:" /etc/oratab|cut -d: -f1 -s` export ORACLE_HOME=`grep "^${ANSWER}:" /etc/oratab|cut -d: -f2 -s` NEW_VER=`cat /etc/oratab | grep ^\`echo ${ORACLE_SID}:\` | cut -d'/' -f 5` export PATH=`echo ${PATH} | sed "s@/u01/app/oracle/[a-z]*/[0-9]*/[a-z0-9_]*@$ORACLE_HOME@g"` export LD_LIBRARY_PATH=`echo ${LD_LIBRARY_PATH} | sed "s@/u01/app/oracle/[a-z]*/[0-9]*/[a-z0-9_]*@$ORACLE_HOME@g"` export OH=$ORACLE_HOME export AGENT_HOME="/u01/app/oracle/product/emagent/agent_inst/" export DIAG="/u01/app/oracle/product/diag/rdbms/"${ORACLE_SID,,}"/"${ORACLE_SID^^}"/trace" export DIAGS="/u01/app/oracle/product/diag/rdbms/"${ORACLE_SID,,}s"/"${ORACLE_SID^^}"/trace" export TNS_ADMIN="/u01/app/oracle/product/network/admin" fi echo echo Oracle SID is now `tput rev`$ORACLE_SID`tput rmso`, Oracle Home is `tput rev`$ORACLE_HOME`tput rmso` echo
在我看來，您正在使這種方法變得比需要的更複雜並重新發明輪子（oraenv ）
這個怎麼樣？
#!/bin/sh cat /etc/oratab |grep -v ^# | grep -v ^$ echo echo Pick one of the above source oraenv
我認為可用的 PDB 列表應該儲存在文件中而不是派生的，類似於可用數據庫列表儲存在 oratab 文件中的方式。儲存列表不僅速度更快，而且在 PDB 未向偵聽器註冊（例如已關閉）的情況下也更可靠。這類似於從 oratab 獲取 SID 列表比從“ps -ef | grep pmon”動態建構 SID 列表更可靠（如果關閉 db 怎麼辦？）。
由於 Oracle 沒有 PDB 列表文件，因此在我的環境中，我維護自己的文件，其中包含兩個欄位：
cat cdb_pdb_list cdb1 pdb11 cdb1 pdb12 ...然後設置 PDB 環境，我使用數據庫選擇器腳本，它允許我從 oratab 的 SID 組合列表和 pdb 列表文件的 PID 組合列表中進行選擇。選擇後，腳本設置 ORACLE_SID 和 ORACLE_PDB_SID 變數，然後為其他變數呼叫標準“oraenv”。與“sqlplus / as sysdba”等的任何後續本地連接都將轉到選定的 PDB。
使用範例： 注意 PDB 如何在列表中顯示為“cdb:pdb”
hostname:+ASM::/home/oracle:> . sid Select your database: 1) -MGMTDB 10) db2 19) db11 2) +ASM 11) db3 20) db12 3) cdb1 12) db4 21) db13 4) cdb1:pdb11 13) db5 22) db14 5) cdb1:pdb12 14) db6 23) db15 6) cdb1:pdb13 15) db7 24) db16 7) cdb2 16) db8 25) db17 8) cdb2:pdb21 17) db9 26) db18 9) cdb2:pdb_dropme 18) db10 27) db19 Your choice (enter number)? 5 Selected database: cdb1:pdb12 ORACLE_SID: cdb11 ORACLE_PDB_SID: pdb12 The Oracle base remains unchanged with value /u01/app/oracle hostname:cdb11:pdb12:/home/oracle:> hostname:cdb11:pdb12:/home/oracle:> sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 18 12:27:10 2021 Version 19.10.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.10.0.0.0 SQL> show con_name CON_NAME ------------------------------ PDB12 SQL>這是數據庫選擇器腳本。用“dot”或“source”呼叫它以將變數持久保存在父 shell 中。
#!/usr/bin/ksh # July 2019 Albert Balbekov # # Interactive database picker # Displays menu of databases defined in oratab then asks which one to use then uses oraenv to set env variables # # This script is normally sourced in user shell to be able to change parent shell variables # alias sid='source $HOME/admin/script/sid' # # History: # July 2019 Initial creation # Mar 2021 Adding PDBs # CDB_PDB_LIST=/home/oracle/admin/scripts/cdb_pdb_list # check if we are on grid GRID_HOME=$( grep '^\+ASM' /var/opt/oracle/oratab | cut -f 2 -d: ) # get list of instances from oratab if [ ! -z "${GRID_HOME}" ]; then # remove trailing digit since we are on RAC # then sort and uniq to remove duplicates # this gives list of databases (as opposite to list of instances) DBS="$( grep -v '^#' /var/opt/oracle/oratab | cut -f 1 -d: | sed 's/[0-9]$//' | sort | uniq )" else DBS="$( grep -v '^#' /var/opt/oracle/oratab | cut -f 1 -d: | sort )" fi # get list of PDBs from cdb_pdb_list PDBS="$( cat $CDB_PDB_LIST | awk '{print $1 ":" $2}' )" # combine and sort DBS="$( printf '%s\n' $DBS $PDBS | sort )" echo "\n" echo 'Select your database:' echo "\n" PS3="Your choice (enter number)? " # if user is in ksh then select menu tends to use all vertical space before wrapping menu to another column # we will limit terminal rows with stty to force select to wrap menu earlier #saverows=$( stty | grep rows | cut -f1 -d";" | cut -f2 -d"=" ) stty rows 7 select db in $DBS; do if [[ -n $db ]]; then echo "Selected database: "$db # can place db-specific actions here ... break else echo "Invalid." fi done # reset stty size back to what it was #stty rows $saverows resize > /dev/null pid="$( echo $db: | cut -d: -f2 )" db="$( echo $db: | cut -d: -f1 )" # construct ORACLE_SID by adding RAC node number to db name if [[ -n "${GRID_HOME}" ]]; then # determine RAC node number RAC_NODE_NUM=$( $GRID_HOME/bin/olsnodes -n | grep `hostname` | cut -f2 ) # add RAC node number to database name to get SID export ORACLE_SID=${db}${RAC_NODE_NUM} else # not a RAC - SID is db export ORACLE_SID=$db fi export ORACLE_UNQNAME=${db} export ORACLE_PDB_SID=$pid echo ORACLE_SID: $ORACLE_SID echo ORACLE_PDB_SID: $ORACLE_PDB_SID SAVE_ORAENV_ASK=$ORAENV_ASK ORAENV_ASK=NO source oraenv ORAENV_ASK=$SAVE_ORAENV_ASK