f 二月 2020 ~ 迪貝之家

Microsoft SQL Server Log Shipping

SQL 7就有的技術,簡單的說就是資料庫複本資料同步的技術,而這份複本可以開放讀取的功能以分散營業資料庫的負載,也可純粹當成備援資料庫,不管是在本地還是異地。怎麼達成? 透過SQL Agent自動化排程進行備份、傳檔及復原等作業。

Oracle GolGateden

機房搬遷跨Site能即時同步資料庫利器

Nagios 資料庫維運自動化

一開始設計這架構時,就排除使用remote agent的想法因為在專業分工的組織下,要求安裝新軟體於既有系統是一件不太可行的方案,既然身為DBA就只能把資料庫instance當作是一個最大的agent 想辦法在資料庫內做到我想做的事情

This is default featured post 4 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured post 5 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

在VM環境建置Oracle GRID 12.2 on 7.x 的環境

我的安裝主要是參考RedHat發佈的一份文件
Deploying Oracle RAC Database 12c Release 2 on Red Hat Enterprise Linux 7
因為是虛擬環境,所以multipath driver 認不到硬碟
反正我拿到就是甚麼/dev/vdX的咚咚
multipath driver 認不到
當然就無從使用UDEV
那就只好用Oracle ASM Library
偏偏kmod模組在RedHat環境得去他們家下載
真是有夠麻煩的
7.2(含)用kmod-oracleasm-2.0.8-15.el7.x86_64
7.3的請用kmod-oracleasm-2.0.8-17.el7.x86_64
這都是實測過的
因為只是我用來測試RAC ADG的建置環境
不會有AP存取
我就懶得申請DNS
只是把所有的IP都寫入到/etc/hosts
這個環境也只需要1個scan IP就夠了
所以安裝的時候,就會出現檢核錯誤的畫面
就是勾選ignore,讓它繼續往下跑就對了
一定能安裝成功的啦~~
cluster verify tool 也沒安裝的必要
GRID 安裝跑到最後的時候
又會跳出錯誤畫面
不需要理會它
大功告成.........
喔...忘了提醒一點了
在7.3(含)以上,grid安裝程式有bug
必須先安裝相應的patch
相關故事可以參考我以前寫的日誌
我的環境跟RedHat文件中不同之處有兩點
1. 精簡帳號建立,依照文件所述
12c Release 2 (12.2) for HP-UX Itanium E85692-04
2. Oracle ASM Library 的使用
可以參考Oracle官方文件
Automatic Storage Management feature of the Oracle Database

Oracle 18c SYSAUX 空間使用因 WRI$_ADV_OBJECTS逐步長大

AWR 的資料預設保留8天,所以正常來說
SYSAUX表格空間的使用量不會有持續成長的狀況
我的案例是已經擴展空間到20GB
看起來還是不夠用
就只好透過@?/rdbms/admin/awrinfo.sql
來查看到底是甚麼咚咚占著茅坑不拉屎
結果如下:
Problem Description
---------------------------------------------------
Part of @?/rdbms/admin/awrinfo output
SM/ADVISOR
COMPONENT  MB      SEGMENT_NAME
------------------ ---------   ---------------------------------------------------
NON_AWR    8,889.0  SYS.WRI$_ADV_OBJECTS TABLE
NON_AWR    4,227.0  SYS.WRI$_ADV_OBJECTS_IDX_01
NON_AWR    2,897.0  SYS.WRI$_ADV_OBJECTS_PK

它佔了至少8GB的空間
查了metalink
文件 2305512.1 解釋了問題的所在
SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 Due To Statistics Advisor
我後來開了Call給global support
我說文件上講的只是一個work around
並不是一個solution
結果support 居然回應我就是只有work around
我回頭再看文件 2305512.1
內容的最末寫了這段話
New Product enhancement has been logged to provide more control to disable the task.
Unpublished Bug 26749785 NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK
好吧~~ 你承認是bug就好
只是不曉得這個"賦予細部控制"的功能甚麼時候會release
接下來當然就是手動清檔
我先是直接執行 drop Statistics Advisor Task
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
8G多的資料,undo 開了20GB還不夠它用
我去看了ASH的報表
雖然看起來是DROP 指令
但是清理資料卻是用delete
我就只好手動把執行該指令的session給砍掉
再手動kill對應的server process
確認沒有留下甚麼手尾後
就依照文件內使用CTAS的清檔方式
進行資料的清理
果然是大絕招
沒幾分鐘就做完了
最好就是整理空間數據給客戶看了
原本數據
                                   占用     已用   剩餘  %
02/11/2020,SYSAUX,20480,18783,1697,92
清檔後數據
02/11/2020,SYSAUX,20480,1022,19458,5

Oracle RAC 12.2 on Linux 2020 第一季的Patch

才剛打上
12.1請參考 (文档 ID 1591616.1)
12.2請參考 (文档 ID 2246888.1)
For Grid Infrastructure Home, as home user:
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/30501932/30593149
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/30501932/30585969
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/30501932/30586063
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/30501932/26839277
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/30501932/30591794
For Database home, as home user:
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/30501932/30593149
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/30501932/30585969
Execute the following on each node of the cluster in non-shared CRS and DB home environment to apply the patch.
1. Stop the CRS managed resources running from DB homes.
If this is a GI Home environment, as the database home owner execute:
$ <ORACLE_HOME>/bin/srvctl stop home -o <ORACLE_HOME> -s <status file location> -n <node name>
srvctl stop home -o $ORACLE_HOME -s /home/oracle/patch.txt -n gidha1
srvctl stop home -o $ORACLE_HOME -s /home/oracle/patch.txt -n gidha2
Note:
You need to make sure that the Oracle ACFS file systems are unmounted (see Section 2.8) and all other Oracle processes are shutdown before you proceed.
2. Run the pre root script.
this is a GI Home, as the root user execute:
# <GI_HOME>/crs/install/rootcrs.sh -prepatch
$ORACLE_HOME/crs/install/rootcrs.sh -prepatch
3. Patch GI home.
As the GI home owner execute:
$ <GI_HOME>/OPatch/opatch apply -oh <GI_HOME> -local <UNZIPPED_PATCH_LOCATION>/%BUGNO%/%OCW TRACKING BUG%
$ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME -local /home/grid/30501932/30593149
$ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME -local /home/grid/30501932/30585969
$ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME -local /home/grid/30501932/30586063
$ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME -local /home/grid/30501932/26839277
$ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME -local /home/grid/30501932/30591794
4. Patch DB home.
注意事項
因oracle的Inventory與grid的Inventory住在一起,所以下面路徑檔案在打第四步Patch DB home的時候記得要先改為oracle:oinstall
打完DB的之後記得要改回來grid:oinstall,改完後才能在繼續往下
/u01/app/oraInventory/ContentsXML]$ ll
total 6
-rw-rw---- 1 grid oinstall 329 Jun 15 13:41 comps.xml
-rw-rw---- 1 grid oinstall 670 Jan 15 10:55 inventory.xml
-rw-rw---- 1 grid oinstall 292 Jun 15 13:41 libs.xml
As the database home owner execute:
$ <UNZIPPED_PATCH_LOCATION>/%BUGNO%/%OCW TRACKING BUG%/custom/scripts/prepatch.sh -dbhome <ORACLE_HOME>
/home/grid/30501932/30585969/custom/scripts/prepatch.sh -dbhome $ORACLE_HOME
$ <ORACLE_HOME>/OPatch/opatch apply -oh <ORACLE_HOME> -local <UNZIPPED_PATCH_LOCATION>/%BUGNO%/%OCW TRACKING BUG%
$ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME -local /home/grid/30501932/30593149
$ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME -local /home/grid/30501932/30585969
$ <UNZIPPED_PATCH_LOCATION>/%BUGNO%/%OCW TRACKING BUG%/custom/scripts/postpatch.sh -dbhome <ORACLE_HOME>
/home/grid/30501932/30585969/custom/scripts/postpatch.sh -dbhome $ORACLE_HOME
5. Run the post script.
As the root user execute:
# <GI_HOME>/rdbms/install/rootadd_rdbms.sh
$ORACLE_HOME/rdbms/install/rootadd_rdbms.sh
If this is a GI Home, as the root user execute:
# <GI_HOME>/crs/install/rootcrs.pl -postpatch
$ORACLE_HOME/crs/install/rootcrs.sh -postpatch
If this is an Oracle Restart Home, as the root user execute:
6. If the message, "A system reboot is recommended before using ACFS is shown, then a reboot must be issued before continuing. Failure to do so will result in running with an unpatched ACFS\ADVM\OKS driver.
7. Start the CRS managed resources that were earlier running from DB homes.
If this is a GI Home environment, as the database home owner execute:
$ <ORACLE_HOME>/bin/srvctl start home -o <ORACLE_HOME> -s <status file location> -n <node name>
srvctl start home -o $ORACLE_HOME -s /home/oracle/patch.txt -n gidha1
srvctl start home -o $ORACLE_HOME -s /home/oracle/patch.txt -n gidha2
8. For each database instance running on the Oracle home being patched, run the datapatch utility as described in next table.
sqlplus / as sysdba
startup
quit
cd $ORACLE_HOME/OPatch
./datapatch -verbose
如果datapatch是空的,not apply
則需要手動處理 (請參考DOC 1955058.1)

會造成困擾的應該是底下這三個畫面
可以忽略
30501932

30501932
30501932

以後找RU 透過這份文件 2118136.2
Metalink 網站”Patch & Updates” 頁籤等同沒用