Oracle Database

Oracle Database，又名Oracle RDBMS，或簡稱Oracle。是甲骨文公司的一款關聯式資料庫管理系統。到目前仍在資料庫市場上占有主要份額。勞倫斯·埃里森和他的朋友，之前的同事Bob Miner和Ed Oates在1977年建立了軟體開發實驗室諮詢公司。SDL開發了Oracle軟體的最初版本。

spfile v.s. pfile
Patch Set Update (PSU)
Web sites
SQLPlus

spfile v.s. pfile

pfile 純文字檔案， client端參數檔案；不能動態修改，可以用普通的編輯器修改，修改之後需要重啟。pfile可能會導致伺服器啟動不一致，因為可以在客戶端啟動。
spfile 二進制檔案，伺服器端參數檔案，有了spfile，oracle可以實現動態參數在線修改，部分參數修改之後無需重啟。但是，因為是二進制檔案，所以不能用普通的編輯器修改，要用alter命令從sql裡面來修改。spfile保證伺服器每次的啟動都是一致的。

spfile 與 pfile 設定檔可以互相轉換，只有 spfile 而沒有 pfile 文件時

create pfile='位置＋名字' from spfile;

例如:

create pfile='E:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\spfileorcl_bak.ora' from spfile;

同理，只有pfile而沒有spfile時，可以透過：

create spfile='位置＋名字' from pfile;

檢查系統是以pfile還是spfile啟動

如果 isspecified 裡有 true，表明用 spfile 啟動，如果全為 false，則表明用pfile啟動

select isspecified,count(*) from v$spparameter group by isspecified;

檢查 spfile 的檔案路徑

show parameters spfile

從 spfile 取得 pfile

Create pfile='d:pfileSID.ora' from spfile='spfile_location';

從 pfile 取得 spfile

Create spfile='spfile_location' from pfile='Your_pfile_location';

Backup current spfile

exp: spfile = spfilewin.ora

CREATE pfile='/opt/oracle/product/10.2.0/db_1/dbs/pfile.tmp' FROM spfile='/opt/oracle/product/10.2.0/db_1/dbs/spfilewin.ora'
CREATE spfile='/opt/oracle/product/10.2.0/db_1/dbs/spfilewin.ora.20140313' FROM pfile='/opt/oracle/product/10.2.0/db_1/dbs/pfile.tmp'

使用其他 spfile/pfile 啟動資料庫

startup nomount

startup pfile='my_pfile'

startup spfile='my_spfile'

Patch Set Update (PSU)

安裝 PSU 10.2.0.5.4 (p12419392_10205_Linux-x86_64.zip)

教學參考：Zip 檔解壓後 Readme.html

事前準備

Oracle DB 必須已經安裝完成 patch 10.2.0.5。
下載需要的檔案
- p6880880_102000_LINUX.zip (OPatch_upgrade)
- p12419392_10205_Linux-x86-64.zip (PSU 10.2.0.5.4)

如何檢查目前 DB 的版本訊息

方法一：

> su - oracle
> cd $ORACLE_HOME/OPatch
> ./opatch lsinventory

Invoking OPatch 10.2.0.4.9

Oracle Interim Patch Installer version 10.2.0.4.9
Copyright (c) 2009, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/oracle/product/10.2.0/db_1
Central Inventory : /opt/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.9
OUI version       : 10.2.0.5.0
OUI location      : /opt/oracle/product/10.2.0/db_1/oui
Log file location : /opt/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2014-06-11_10-55-50AM.log

Patch history file: /opt/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /opt/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2014-06-11_10-55-50AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (3):

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 4                            10.2.0.5.0
There are 3 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

OPatch succeeded.

方法二：

$ ./opatch  lsinventory -bugs_fixed  | grep -i -E 'DATABASE PSU|DATABASE PATCH SET UPDATE'
9952230    12419392  Wed Jun 11 11:45:57 CST 2014   DATABASE PSU 10.2.0.5.1 (INCLUDES CPUOCT2010)
10248542   12419392  Wed Jun 11 11:45:57 CST 2014   DATABASE PSU 10.2.0.5.2 (INCLUDES CPUJAN2011)
11724962   12419392  Wed Jun 11 11:45:57 CST 2014   DATABASE PSU 10.2.0.5.3 (INCLUDES CPUAPR2011)
12419392   12419392  Wed Jun 11 11:45:57 CST 2014   DATABASE PSU 10.2.0.5.4 (INCLUDES CPUJUL2011)

方法三：

> sqlplus / as sysdba
SQL> col comments for a20
SQL> col version for a20
SQL> col bundle_series for a20
SQL> select comments, version, bundle_series
from sys.registry$history
where bundle_series = 'PSU'
order by action_time;

COMMENTS             VERSION              BUNDLE_SERIES
-------------------- -------------------- --------------------
PSU 10.2.0.5.4       10.2.0.5             PSU

升級 OPatch 至 12.0.5.1

要安裝 PSU 之前，Oracle 的 OPatch 版本必須是 10.2.0.5 以上。

檢查 OPatch 版本的指令有二：

> su - oracle
> cd $ORACLE_HOME/OPatch
> ./opatch lsinventory
或
> ./opatch version

升級 OPatch

> su - oracle
> mkdir p6880880_102000_LINUX/
> unzip "p6880880_102000_LINUX(OPatch_upgrade).zip" -d p6880880_102000_LINUX
> mv $ORACLE_HOME/OPatch/ $ORACLE_HOME/OPatch_old/
> cp -a p6880880_102000_LINUX/OPatch/ $ORACLE_HOME
> cd $ORACLE_HOME/OPatch/
> ./opatch version

NOTE:

上述步驟請改用 root 執行，避免有些檔案因為權限問題會有遺漏。

> cp -a /mnt/tpeitpfs02-share/ora10g_install/patch_10205/p6880880_102000_LINUX/OPatch /opt/oracle/product/10.2.0/db_1
> chown -R oracle.dba /opt/oracle/product/10.2.0/db_1/OPatch

安裝 PSU

NOTE: 不需要桌面環境

安裝前：檢查 PSU patch 是否會與目前的 DB 有所衝突

> mkdir p12419392_10205_Linux-x86-64
> unzip "p12419392_10205_Linux-x86-64(PSU_patch).zip" -d p12419392_10205_Linux-x86-64
> cd p12419392_10205_Linux-x86-64/
> $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./12419392

Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /opt/oracle/product/10.2.0/db_1
Central Inventory : /opt/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.5.0
OUI location      : /opt/oracle/product/10.2.0/db_1/oui
Log file location : /opt/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2014-06-11_11-20-16AM.log

Patch history file: /opt/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

安裝開始

NOTE：執行前必須將所有的 DB Instances 與 Listeners 停止。

Shutdown All Instances and Listeners
> lsnrctl stop
> sqlplus /nolog
SQL> conn / as sysdba
SQL> shutdown immediate

> cd p12419392_10205_Linux-x86-64/12419392
> $ORACLE_HOME/OPatch/opatch apply

狀況一: 現有資料庫的升級

Enter email
Enter password：<Enter>

** 耐心等候 **

Unable to establish a network connection to Oracle. If your systems require a
proxy server for outbound Internet connections, enter the proxy server details
in this format:
[<proxy-user>@]<proxy-host>[:<proxy-port>]
If you want to remain uninformed of critical security issues in your
configuration, enter NONE
Proxy specification:
An invalid proxy specification was given. Please re-enter the proxy information.

Unable to establish a network connection to Oracle. If your systems require a
proxy server for outbound Internet connections, enter the proxy server details
in this format:
[<proxy-user>@]<proxy-host>[:<proxy-port>]
If you want to remain uninformed of critical security issues in your
configuration, enter NONE
Proxy specification: NONE <輸入NONE>

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/opt/oracle/product/10.2.0/db_1')

Is the local system ready for patching? [y|n]
y <輸入 y>

...
...
Patching component oracle.sysman.bsln, 10.2.0.5.0...
Patching component oracle.sysman.repository.core, 10.2.0.5.0a...
Patching component oracle.sysman.console.db, 10.2.0.5.0...
Patching component oracle.xdk.rsf, 10.2.0.5.0...
Patching component oracle.precomp.common, 10.2.0.5.0...

Patching component oracle.rdbms.rman, 10.2.0.5.0...
Running make for target client_sharedlib
Running make for target ioracle
Running make for target iwrap
Running make for target client_sharedlib
Running make for target proc
Running make for target irman
ApplySession adding interim patch '12419392' to inventory

Verifying the update...
Inventory check OK: Patch ID 12419392 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 12419392 are present in Oracle Home.

The local system has been patched and can be restarted.

OPatch succeeded.

安裝後：套用新 Patch

> cd $ORACLE_HOME/rdbms/admin
> sqlplus /nolog
SQL> connect / as sysdba
SQL> startup
SQL> @catbundle.sql psu apply
SQL> @utlrp.sql
SQL> quit

TIP:

執行 @catbundle.sql .... 時，最後顯示訊息如下：

Updating registry...

1 row created.

Commit complete.

Check the following log file for errors:
/opt/oracle/product/10.2.0/db_1/cfgtoollogs/catbundlez/catbundle_PSU_WIN_APPLY_2014Jun11_11_55_31.log

最後確認

SQL :

select owner,count(1)
from dba_objects
where status='INVALID'
group by owner;

SQL:

select * from registry$history;

狀況二: 全新安裝，無任何資料庫

Email address/User Name: 按 Enter
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N] 輸入 Y
Is the local system ready for patching? 輸入 y

Verifying the update...
Inventory check OK: Patch ID 12419392 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 12419392 are present in Oracle Home.

The local system has been patched and can be restarted.

OPatch succeeded.

完成後檢視

> cd $ORACLE_HOME/OPatch
> ./opatch lsinventory
Invoking OPatch 10.2.0.5.1

Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/oracle/product/10.2.0/db_1
Central Inventory : /opt/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.5.1
OUI version       : 10.2.0.5.0
OUI location      : /opt/oracle/product/10.2.0/db_1/oui
Log file location : /opt/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2014-06-11_13-05-53PM.log

Patch history file: /opt/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /opt/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2014-06-11_13-05-53PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (3):

Oracle Client                                                        10.2.0.1.0
Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 4                            10.2.0.5.0
There are 3 products installed in this Oracle Home.


Interim patches (1) :

Patch  12419392     : applied on Wed Jun 11 11:45:57 CST 2014
Unique Patch ID:  13794201
   Created on 29 May 2011, 22:29:59 hrs PST8PDT
   Bugs fixed:
     6402302, 10269717, 10327190, 8865718, 10017048, 9024850, 8394351, 8546356
     9360157, 9770451, 9020537, 9772888, 8664189, 10091698, 12551710, 7519406
     10132870, 8771916, 9109487, 10173237, 10068982, 8350262, 11792865
     11724962, 11725006, 9184754, 8544696, 9320130, 7026523, 8277300, 9726739
     8412426, 12419392, 6651220, 9150282, 9659614, 9949948, 10327179, 8882576
     7612454, 9711859, 9714832, 10248542, 9952230, 9469117, 9952270, 8660422
     10324526, 12419258, 9713537, 10010310, 9390484, 9963497, 12551700
     12551701, 10249537, 12551702, 12551703, 8211733, 12551704, 9548269
     12551705, 12551706, 9337325, 12551707, 7602341, 12551708, 9308296
     10157402, 11737047



--------------------------------------------------------------------------------

OPatch succeeded.

Web sites

Oracle Official

Forum: https://forums.oracle.com/ords/apexds/domain/dev-community

SQLPlus

常用技巧

show user
conn system/pass       -- 登入資料庫
r                      -- 執行前一個SQL
/                      -- 執行前一個SQL
@?/rdbms/xxx.sql       -- @ 執行外部SQL檔, ? Oracle_HOME 
!ls                    -- ! 執行Linux 系統指令
set timing on          -- 顯示SQL執行耗費時間 
Rem This is a comment  -- 註解, 不要加分號
l                      -- 顯示前一個 SQL 內容, 直接輸入分號也可以
3                      -- 移動至 SQL 內容裡的第3行
3 where status='INVALID'      -- 將第3行改成 where status='INVALID'  
c/INVALID/VALID/              -- 將目前行內容的 INVALID 取代成 VALID, c = change

輸出格式化

set pagesize 60  -- 輸出每頁行數，預設為24,為了避免分頁，可設定為0, 若設0, 不會顯示欄位名稱。
set linesize 350  -- 每行長度(太小會斷行，太大效能差)
set wrap off   -- 不斷行
set space 1  -- 欄位間空幾個space
set echo on  -- Show SQL語法在畫面上

col tablespace_mb heading 'TABLESPACE|TOTAL MB' -- 設定長標題以斷行顯示 
col os_user_name head "OS|Username" form a10 truncate
col process head "Client|Process|ID" form 99999999

--ttitle 'SQL By CPU Usage'
prompt
prompt +----------------------------------------------------+
prompt | SQL BY CPU USAGE
prompt +----------------------------------------------------+

set termout off      --顯示腳本中的命令的執行結果
set trimout on       --刪除標準輸出每行的行尾空格
set trimspool on     --刪除行尾空白

Select 欄位A||'┴'|| replace(欄位B ,CHR(10),CHR(13))||'┴' from 資料庫.資料表 where 欄位B is not null order by 欄位A;

因為欄位B 裡有斷行符號^p，要將他轉換成分行符號^l^p, ^l為word 的代碼，ASCII碼分別為 CHR(10),CHR(13)欄位A與欄位B間，用┴作區隔。

set feedback off     -- 不顯示結果筆數的資訊
set heading off      -- 不顯示欄位的標題欄

去除 TAB 與格式化輸出

set colsep "|"
set tab off

定義變數

var tbs_source varchar2(50);  
var tbs_dest varchar2(50);  
var schema_user varchar2(50); 

exec :tbs_source  := 'SOURCE_TBS';  
exec :schema_user     := 'SOURCE_USER';   
exec :tbs_dest := 'DEST_TBS';  

select 'Transporting tablespace ' || :tbs_source || ' or user ' || :schema_user || ' to tablespace ' || :tbs_dest from dual;

特殊字元

-- 換行
chr(10)

註解文字

-- 單行說明

/* 這是多行
   說明 */

顯示系統時間

select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') Begin_Datetime from dual;

!sleep 5

select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') End_Datetime from dual;