MISTECH 技術手抄本

系統環境：

系統環境先放在前面，是為了後面說明概念時，可以加以標示註解，以便更加瞭解 RMAN 的各個環節。

1. Oracle DB 11g  on Windows：記錄 RMAN 所有備份的記錄，在 RMAN 稱為 RMAN Repository （RMAN 恢復目錄資料庫）。

ORACLE_SID：RMANDB
執行記錄 recovery catalog 的帳號/密碼：RMAN_USER/RMAN_PWD

2. Taget Database （要備份的目標資料庫）：

Oracle DB 12c on Linux：正式資料庫，日常的交易要作備份。

ORACLE_SID：orcl
執行備份的帳號/密碼：sys/oracle

3. 兩者的 tnsnames.ora 均要有雙方連線的資訊:

RMAN 概念：

Oracle Database 的備份，一般建議是利用 RMAN 來作業，一來可以由系統自行管理，二來也可以省掉自已下一堆的指令。在建立 RMAN 的機制前，下面幾個概念要有：

1. Target Database：（目標資料庫）：本實例為 ORACLE_SID：orcl

就是需要RMAN對其進行備份與恢復的資料庫，RMAN 可以備份資料檔案，控制檔，歸檔日誌，spfile.（注意：RMAN不能用於備份連線日誌、初始化參數檔：pfile 和命令檔）

2. recovery catalog（恢復目錄）：

用來保存備份與恢復資訊的一個資料庫，不建議建在目標資料庫上。RMAN 利用恢復目錄記載的資訊去判斷如何執行需要的備份恢復操作。

如果不採用恢復目錄(nocatalog)，備份資訊可以存在於目標資料庫的 control file 中。如果存放在目標資料庫的 control file 中，控制項檔會不斷增長，不能保存 RMAN 的Script. CONTROL_FILE_RECORD_KEEP_TIME （default=7）：控制項檔中 RMAN 資訊保存的最短時間。

使用恢復目錄(catalog)的優勢： 可以存儲腳本，並記載較長時間的備份恢復操作。


3. RMAN Repository（RMAN 恢復目錄資料庫）： 本實例為 ORACLE_SID：rmandb

存放 recovery catalog（恢復目錄）的資料庫。建議為 recovery catalog（恢復目錄）資料庫創建一個單獨的資料庫。

設置 RMAN ( Configuration RMAN )實作：

Step 01: 在資料庫 ORACLE_SID：orcl 建立 RMAN 所需的環境

1.) 連上資料庫：rmandb

2.) 建立 tablespace：RMAN_TS

3.) 建立執行記錄 recovery catalog 的帳號：RMAN_USER，並給 CONNECT,RESOURCE,RECOVERY_CATALOG_OWNER 這三個權限

註：Oracle Database 11g 以後，帳號/密碼 有分大小寫，(To Enable/ Disable set SEC_CASE_SENSITIVE_LOGON to TRUE/ FALSE ，預設：TRUE ) 若要關閉需用指令 alter system set sec_case_sensitive_logon=false scope=both;

4.) 利用帳號：RMAN_USER 來建立 recovery catalog（恢復目錄）

Step 02: 連到目標資料庫  Connect to the target database

1.a ) 若是在 ORACLE_SID：rmandb 資料庫主機上作業

1.b ) 若是在 ORACLE_SID: orcl （目標資料庫）資料庫主機上作業

Step 03: 註冊目標資料庫 Register the database

Step04: 驗證是否有註冊成功

PS: 也可以用SQL：select * from rc_database;

RMAN 備份實作：

0.) 先將 ORACLE_SID: orcl 改為 archive log mode

註: log_archive_format 的參數設定請參考： http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams103.htm

1.) 建立一個全備份的 script (Script 會存在RC_STORED_SCRIPT及RC_STORED_SCRIPT_LINE的table中): fullbackup

2.) 查看 RMAN 的備份 script

 3.) 執行備份 script

基本上 Oracle Database 資料庫在 RMAN 的備份，ORACLE_SID: orcl （目標資料庫）應該是處於 Archive log mode 才可以線上備份。以上是 Oracle RMAN 的基本概念與資料庫全備份實作

附註：

若您是使用 Oracle XE 版本的資料庫，應該會出現下列的訊息，代表資料庫 ORACLE_SID：xe 是11g，但目標資料庫 ORACLE_SID:orcl 是 12c ，package RMAN_XE.DBMS_RCVCAT 不相容。所以不能用 oracle XE 版本的資料庫來作。

就算用指令UPGRADE CATALOG; 來升級 catalog，雖然會成功，也不能使用

參考：

http://fecbob.pixnet.net/blog/post/38195291-oracle-rman%E7%9A%84%E5%9F%BA%E6%9C%AC%E6%A6%82%E5%BF%B5

Oracle Database 設定檔中，這個設定檔：listener.ora  是要在主機端啟動的服務設定檔；而 tnsnames.ora & sqlnet.ora 則是在使用者諯的設定檔，主要是連到主機端資料庫的連線設定。關於比較詳細的說明如下：

sqlnet.ora：

作用類似於 linux 或者其他 unix 的 nsswitch.conf 文件，Oracle Database 利用這個設定檔來決定連接資料庫系統的連線方式順序　

例如使用者端輸入：sqlplus sys/oracle@orcl

且使用者端的 sqlnet.ora 是下面這個樣子

那麼，使用者端就會首先在 tnsnames.ora 文件中找 orcl 的記錄.如果沒有相應的記錄則嘗試把 orcl 當作一個主機名，通過網路的途徑去解析它的 ip 地址然後去連接這個 ip 上GLOBAL_DBNAME=orcl 這個實例，當然我這裡orcl並不是一個主機名，如果我是這個樣子 NAMES.DIRECTORY_PATH= (TNSNAMES)，那麼使用者端就只會從 tnsnames.ora 查找 orc l的記錄，括號中還有其他選項，如LDAP等並不常用。

tnsnames.ora：

這個文件類似於 unix 的 hosts 文件，tnsnames.ora 是設定連線簡稱(alias)對應於主機名或者ip address 的解析，只有當 sqlnet.ora中有 NAMES.DIRECTORY_PATH= (TNSNAMES) 的設定，也就是使用者端解析連接字符串的順序中有 TNSNAMES ，才會嘗試使用這個文件。

listener.ora：

listener 監聽器服務的配置文件：是接受使用者端對數據庫的連接進入申請，並轉交給 oracle database 的服務。LISTENER 監聽器的名字，一台數據庫可以有不止一個監聽器，Listener.ora 例子如下：

監聽器的操作命令：lsnrctl、tnsping

$ORACLE_HOME/bin/lsnrctl start，其他諸如 stop,status 等。可以進入 lsnrctl 後，輸入 help 看其它的參數說明來作偵錯，也可以利用基本的指令 tnsping orcl 來作基本偵錯。　　

上面說到的三個文件都可以通過圖形的配置工具 $ORACLE_HOME/netca & $ORACLE_HOME/netmgr 來完成配置，但以 netmgr 較為方便。

幾種連接用到的命令形式：

• 1.) sqlplus / as sysdba 這是典型的操作系統認證，不需要listener進程
• 2.) sqlplus system/oracle 這種連接方式只能連接本機數據庫，同樣不需要listener進程
• 3.) sqlplus sysrem/oracle@orcl 這種方式需要listener進程處於可用狀態。最普遍的通過網路連接。

Oracle Database 12c 因為有 multitenant container database (CDB) 及 pluggable databases (PDBs) 的功能，所以在建立帳號時，就必需要指定。不然會有錯誤訊息：ORA-65096: invalid common user or role name

這時候，您要決定建立一個共用(common)帳號於 CDB (a "common" user in the CDB which could be used to manage multiple PDBs )，或是 Local 帳號於 PDB，可以使用 SQL 指令 select pdb from v$services; 來查詢目前有多少服務

若要建立一個共用(common)帳號在 CDB$ROOT ，則必需在帳號前加入 c##

若要將帳號建在 Local PDB：PDBORCL ，必需先將 session 指定在 container=PDBORCL;

ORACLE_SID: orcl 資料庫不是明明已 open 了嗎? 奇怪，怎麼還告訴我 database not open，其實它是說 PDB：PDBORCL 沒有open，可以用 SQL 指令：ALTER PLUGGABLE DATABASE PDBORCL OPEN | CLOSE ; 或 ALTER PLUGGABLE DATABASE ALL OPEN|CLOSE; 來開啟 pluggable databases (PDBs)

 再來試一次吧

ORA-65096 的意思:

ORA-65049 的意思:

參考：

http://docs.oracle.com/cd/E16655_01/server.121/e17633/cdblogic.htm#CNCPT89259

http://blog.contractoracle.com/2013/06/oracle-12c-ora-65096.html

http://www.oracle-base.com/articles/12c/multitenant-manage-tablespaces-in-a-cdb-and-pdb-12cr1.php

spfile & pfile 差異：

Oracle Database 自從 9i 以後的版本，就開始支援 spfile ，spfile & pfile 差異處如下：

• pfile  ：client 端參數文件，是一個文字檔，不能動態修改，可用 notepad 或 vi 來編輯，DBA 通常是指這個 init.ora 文件；修改之後需要重啟資料庫才會生效。
• spfile：服務器端參數文件(Server Parameter File)，這是一個二進位的檔案，編輯 SPFILE 檔案會破壞它，您將無法啟動您的數據庫，要更改參數值，需用指令："ALTER SYSTEM SET" 來變更； 有了spfile，資料庫的參數可以線上動態修改，部分參數修改之後無需重啟資料庫就可以直接生效。。

可以用下列指令，得知您的資料庫是用那一種模式起動的。

另一種查詢的方式，是查 view: V$SPPARAMETER，如：SHOW PARAMETERS pfile | spfile ; 若值為 null ，則是使用 pfile (init.ora) 啟動資料庫。

show parameters 各位 view 的意思：

spfile 的優點：

• 1. 可以用 RMAN 備份 spfile ( spfile 是 Server Parameter File )， RMAN 無法備份 pfile ( client 端參數文件 )
• 2. 滅少人為修改參數造成的錯誤，spfile 是儲存在資料庫系統裡，用 "ALTER SYSTEM SET" 來變更時，系統會先作檢查。
• 3. 只有一個 spfile 參數檔，很容易找到與維護。

從 spfile 獲取 pfile：

Create pfile='d:pfileSID.ora' from spfile;
SQL> Create pfile='C:\oraclexe\app\oracle\admin\XE\pfile\pfileSID.ora' from spfile;


從pfile獲取spfile：

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

動態修改參數：
alter system set parameter=Value scope=spfile | both | memory ( Startup nomount 的時候需要讀去 spfile 或 pfile 或 兩者共存，系統以 spfile 優先 )

The SCOPE parameter can be set to SPFILE, MEMORY or BOTH:

• - MEMORY: Set for the current instance only. This is the default behaviour if a PFILE was used at STARTUP.
• - SPFILE: update the SPFILE, the parameter will take effect with next database startup
• - BOTH: affect the current instance and persist to the SPFILE. This is the default behaviour if an SPFILE was used at STARTUP.
• The COMMENT parameter (optional) specifies a user remark.

強制用 pfile 啟動：

參數檔案 spfile 的備份：

RMAN (Oracle's Recovery Manager) 可以備份 SPFILE ，但參數 "CONFIGURE CONTROLFILE AUTOBACKUP" 要設定為 ON (the default is OFF). PFILEs 則無法由 RMAN 備份.如下範例設定：

參考：

http://orafaq.com/node/5

http://weicheng331.blogspot.tw/2009/05/oracle-spfile-pfile.html

在談到 Oracle DB 12c 的安裝 install on linux 如何建立資料庫後，手動啟動/關閉資料庫總是覺得煩索了點，可以像 Windows Service  一樣自動化嗎? 這是可行的，接著我們來實作看看。

以上先用帳號：oracle 來編輯

Step 01: 建立 /home/oracle/scripts 目錄及 /home/oracle/scripts/startup.sh & /home/oracle/scripts/shutdown.sh 檔案

startup.sh

shutdown.sh

編輯好 /home/oracle/scripts/startup.sh & /home/oracle/scripts/shutdown.sh 兩個檔案後，記得用指令 chmod u+x /home/oracle/scripts/startup.sh /home/oracle/scripts/shutdown.sh 讓這兩個檔案變成可執行檔，並用手動執行，測試一下是否可以正常開啟與關閉資料庫。

以下以用帳號：root來編輯檔案

Step 02 : 手動測試可以正常開啟與關閉資料庫無誤後，再來建立 /etc/init.d/dbora 檔案，這是用來建立 Linux 上自建的服務啟動檔，內容如下：

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_OWNER to the user id of the owner of the 
# Oracle database software.

ORA_OWNER=oracle

case "$1" in
    'start')
        # Start the Oracle databases:
        # The following command assumes that the oracle login 
        # will not prompt the user for any values
        su $ORA_OWNER -c "/home/oracle/scripts/startup.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1"
        touch /var/lock/subsys/dbora
        ;;
    'stop')
        # Stop the Oracle databases:
        # The following command assumes that the oracle login 
        # will not prompt the user for any values
        su $ORA_OWNER -c "/home/oracle/scripts/shutdown.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1"
        rm -f /var/lock/subsys/dbora
        ;;
esac

記得用指令 chmod 750 /etc/init.d/dbora 讓 dbora 它變成可執行檔案，這時 Linux 的 Service 應有一個 dbora 的服務。

最後，用指令 chkconfig --add dbora 將它與系統啟動與關閉作關連。

您可以使用下列兩個指令，並用帳號：root 來作測試

# service dbora start
# service dbora stop

資料庫啟動與關閉的記錄，則可以查看 /home/oracle/scripts/startup_shutdown.log 這個記錄檔。

參考：

http://www.oracle-base.com/articles/linux/automating-database-startup-and-shutdown-on-linux.php#oracle-11gr2-update

瞭解 Oracle Linux Install Using LVM 與 LVM (Logical Volume Mamager) on Linux 動態放大檔案系統 的基本概念後，接下來就可以來安裝 Oracle Database 12c Release 1 (12.1)，所需要的檔案有 linuxamd64_12c_database_1of2.zip & linuxamd64_12c_database_2of2.zip 可以從下列兩個網址取得：

• OTN: Oracle Database 12c Release 1 (12.1.0.1) Software (64-bit).
• edelivery: Oracle Database 12c Release 1 (12.1.0.1) Software (64-bit)

實作環境說明：

host name : oradb12clinux.globeunion.com
ip address : 192.168.0.210

DB 安裝在 /u01 目錄下

以下均以帳號 root 執行

Step00: 建立資料庫相關帳號與群組

groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper
#groupadd -g 54324 backupdba
#groupadd -g 54325 dgdba
#groupadd -g 54326 kmdba
#groupadd -g 54327 asmdba
#groupadd -g 54328 asmoper
#groupadd -g 54329 asmadmin

useradd -u 54321 -g oinstall -G dba,oper oracle

再設定 oracle 帳號的密碼：

passwd oracle

Step 01: Unpack Files 解壓檔案

unzip linuxamd64_12c_database_1of2.zip
unzip linuxamd64_12c_database_2of2.zip

Step 02: 編輯 /etc/hosts

127.0.0.1       localhost.localdomain  localhost
192.168.0.210   oradb12clinux.globeunion.com    oradb12clinux

Step03: 修改檔案 /etc/sysctl.conf 增加下列內容

fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500

然後執行指令 /sbin/sysctl -p 讓它生效

Step04: 修改檔案 /etc/security/limits.conf 增加下列內容

oracle   soft   nofile    1024
oracle   hard   nofile    65536
oracle   soft   nproc    2047
oracle   hard   nproc    16384
oracle   soft   stack    10240
oracle   hard   stack    32768

Step05: 若安裝 Oracle Linux Install Using LVM 時沒選相關的套件，則請以 root 的帳號來裝下列套件

# From Public Yum or ULN
yum install binutils -y
yum install compat-libcap1 -y
yum install compat-libstdc++-33 -y
yum install compat-libstdc++-33.i686 -y
yum install gcc -y
yum install gcc-c++ -y
yum install glibc -y
yum install glibc.i686 -y
yum install glibc-devel -y
yum install glibc-devel.i686 -y
yum install ksh -y
yum install libgcc -y
yum install libgcc.i686 -y
yum install libstdc++ -y
yum install libstdc++.i686 -y
yum install libstdc++-devel -y
yum install libstdc++-devel.i686 -y
yum install libaio -y
yum install libaio.i686 -y
yum install libaio-devel -y
yum install libaio-devel.i686 -y
yum install libXext -y
yum install libXext.i686 -y
yum install libXtst -y
yum install libXtst.i686 -y
yum install libX11 -y
yum install libX11.i686 -y
yum install libXau -y
yum install libXau.i686 -y
yum install libxcb -y
yum install libxcb.i686 -y
yum install libXi -y
yum install libXi.i686 -y
yum install make -y
yum install sysstat -y
yum install unixODBC -y
yum install unixODBC-devel -y

Step06: 修改檔案 /etc/security/limits.d/90-nproc.conf

# Change this
*          soft    nproc    1024

# To this
* - nproc 16384

Step07: 修改檔案 /etc/selinux/config

SELINUX=permissive

然後執行指令 setenforce Permissive 讓它生效

Step08: 關閉防火牆

# service iptables stop 
# chkconfig iptables off

Step09: 建立目錄及設定權限

mkdir -p /u01/app/oracle/product/12.1.0/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

Step10: 編輯帳號：oracle ，目錄下的 /home/oracle/.bash_profile

# Oracle Settings
export TMP=/tmp
export TMPDIR=$TMP

export ORACLE_HOSTNAME=oradb12clinux.globeunion.com
export ORACLE_UNQNAME=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1
export ORACLE_SID=orcl

export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

最後，以帳號 oracle 登入執行 ./runInstaller 執行資料庫 Oracle DB 12c 安裝即可。

註: 在安裝過程中，會要求您切換帳帳，以 root 的權限執行兩個檔案 orainstRoot.sh & root.sh

[oracle@oradb12clinux database]$ su -
[root@oradb12clinux ~]# /u01/app/oraInventory/orainstRoot.sh
[root@oradb12clinux ~]# /u01/app/oracle/product/12.1.0/db_1/root.sh

安裝完成後的資訊

可以用WEB進入管理

若是沒有辦法顯示此登錄劃面，可以用下列指令檢查 EM (Enterprise Manager Database Express) 的 port 是否為 5500，若不是則更改之。

SQL> SELECT dbms_xdb_config.gethttpsport FROM dual;

GETHTTPSPORT
------------
        5500

SQL> EXEC dbms_xdb_config.sethttpsport(5500);

PL/SQL procedure successfully completed.

SQL>

資料庫的啟動與關閉

TNS Listenser 的啟動與關閉：lsnrctl start/stop 及 TNS 測試：tnsping

註：若要建立另一個資料庫(SID)，可以使用指令 dbca 來建.

參考文件：

http://www.oracle-base.com/articles/12c/oracle-db-12cr1-installation-on-oracle-linux-6.php

http://figaro.neo-info.net/?cat=218

loging to oracle DB  在進入 OS 之後：

Q:Oracle的啟動指令為何?
A:>startup [force] [restrict] [open[RECOVER] |mount|nomount] file=filename
例:>startup nomount pfile=init.ora (資料庫未建立時應以此方式載入參數檔)

Q:續上,如何掛上資料庫? (若資料庫已建立時)
A:>alter database [ mount | open[read write|read only] ]

Q:Oracle的啟動程序為何?
A:shutdown-> nomount(instance started) -> mount(control file
open) -> open(all files opened)

Q:Oracle的shutdown程序為何?(快 -> 慢)
A:>shutdown[abort | immediate | transactional | normal]

Q:顯示參數值?
A:>show parameter

Q:動態更改參數值?
A:>alter session set parameter_name = values
  >alter system set parameter_name = values [DEFERRED]
例:
>alter session set SQL_TRACE=true;
  >alter system set TIMED_STATISTICS=true;
  >alter system set SORT_AREA_SIZE=131072 DEFERRED;

Q: 如何增加 Control File
A:
1. 將Oracle的Database Shut down
2. 用OS(operation system)的命令將現行的Control file 複制到另一目錄
3. 在參數檔(init.ora)中加入此Control file(需含完整路徑)
4. 重新起動DataBase

Q: v$logfile 中的 STATUS是何意?
A:
空白:表現在正在使用
INVALID : 表可能有問題
STALE: 表剛加入的Log File
DELETED: 表很久沒用的Log File

Q: 如何強迫轉換到另一個 Redo Log File (Forcing Log Switches)
A: >alter system switch logfile;

Q: Forcing Checkpoints for Redo Log File
A: >alter system checkpoint;

Q: 如何控制Redo Log File 的檢查時間?
A:
LOG_CHECKPOINT_INTERVAL
LOG_CHECKPOINT_TIMEOUT
FAST_START_IO_TARGET

Q: 如何增加 Redo Log Groups ?
A: >alter database [database] add logfile [group integer] filespace size integer m/k
例: >alter database add logfile group 3 ‘$HOME/data/disk4/redo0401.rdo’ size 500k;

Q: 如何加 Redo Log Members
A: >alter database [database] add logfile member ‘filename’ [reuse],’filename’ [reuse] to group integer
例: >alter database add logfile member '$HOME/data/disk4/redo0402.rdo' to group 4

Q: 如何重新配置/更名online Redo Log Files?
A: 1. 被重新配置/更名 的檔名需存在
例: >alter database rename file '$HOME/data/disk4/redo0401.rod'  to '$HOME/data/disk4/redo0401.rdo'
Q: 如何Drop Redo Log Group
A: > alter database [database] drop logfile {group integer|‘filename’}
例: >alter database drop logfile group 2;
註:檔案仍存在,需用OS命令移除,如:#rm $HOME/redo0402.rdo

Q: 如何 Drop Redo Log Members
A: >alter database [database] drop logfile member ‘filename’
例: >alter database drop logfile member ‘$HOME/redo0402.rdo’;
註:檔案仍存在,需用OS命令移除,如:#rm $HOME/redo0402.rdo

Q: 如何查詢有多少DataFile?
A: >select name from v$datafile;
Or >select file_name, tablespace_name from dba_data_files;

Q: 查詢 Redo Log File?
A: >select * from v$logfile;

Q: 如何清除Redo Log File內的資料?
A: >alter database clear [unarchived] logfile group integer|
例:>alter database clear logfile group 2
註: 1.參數unarchived 是當不在備份模式時才加入的
   2.正在作用的Redo Logfile無法清除

Q: 查詢TableSpace的狀態
A: select tablespace_name, status from dba_tablespaces;

Q: 如何查詢Table的欄位?
A: >describe TableName
 

Q: 如何改變tablespace的狀態在online或offline
A:
>alter tablespace user_indx online;
>alter tablespace user_indx offline;

Q:何種情況要將tablespace改為offline的狀態?
A:
1.離線備份(雖然也可以線上備份)
2.當資料庫是open狀態時,進行回存tablespace
3.當資料庫是open狀態時,刪除data file
4.不讓使用者存取時
 

Oracle的優化器(Optimizer)有兩種優化方式, Hint也不例外，除了/*+rule*/其他的都是CBO優化方式

• 基於規則的優化方式(Rule-Based Optimization，簡稱為RBO)
• 基於成本的優化方式(Cost-Based Optimization，簡稱為CBO)

RBO(Rule-Based Optimization)方式：優化器在分析SQL語句時,所遵循的是Oracle內部預定的一些規則。比如我們常見的，當一個where子句中的一列有索引時去走索引。
CBO(Cost-Based Optimization)方式：它是看語句的成本(Cost),這裡的成本主要指CPU和記憶體RAM。優化器在判斷是否用這種方式時,主要參照的是表及索引的統計資訊。統計資訊給出表的大小、 有多少行、每行的長度等資訊。這些統計資訊起初在庫內是沒有的，是做 Analyze 後才出現的，很多的時侯過期統計資訊會令優化器做出一個錯誤的執行計畫, 因些應及時更新這些資訊。

優化模式(Optimization)包括Rule、Choose、First rows、All rows四種方式：

•     Rule：基於規則的方式。
•     Choose：預設的情況下Oracle用的便是這種方式。指的是當一個表或索引有統計資訊，則走CBO的方式，如果表或索引沒統計資訊，表又不是特別的小，而且相應的列有索引時，那麼就走索引，走RBO的方式。
•     First Rows：它與Choose方式是類似的，所不同的是當一個表有統計資訊時，它將是以最快的方式返回查詢的最先的幾行，從總體上減少了響應時間。
•     All Rows:也就是我們所說的Cost的方式，當一個表有統計資訊時，它將以最快的方式返回表的所有的行，從總體上提高查詢的輸送量。沒有統計資訊則走RBO的方式

Oracle DB 可以在哪些設定配置上修改預設的優化規則
    A、Instance 層級：我們可以通過在 initSID.ora 檔中設定 OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS 如果沒設定OPTIMIZER_MODE參數則預設用的是Choose方式。
    B、Sessions 層級：可通過ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS來設定。
    C、SQL語句層級：用Hint（/*+ ... */）來設定

為什麼表的某個欄位明明有索引，但執行計畫卻不走索引，原因有下列幾項：
    1、優化模式是 all_rows 的方式
    2、表作過 Analyze，有統計資訊
    3、表很小，Oracle DB 的優化器認為不值得走索引。

SQL Optimizer Hints的用法：

1. 不區分大小寫, 多個提示用空格分開
如：select /*+ hint1(tab1) hint2(TAB1 idx1) */ col1, col2 from tab1 where col1='xxx';

2. 如果表使用了別名, 那麼提示裡也必須使用別名
如：select /*+ hint1(t1) */ col1, col2 from tab1 t1 where col1='xxx';

3. 如果使用同一個表的多個欄位用逗號 "," 號分開
如： select /*+ index(t1.A,t1.B) */ col1, col2
    from   tab1 t1
    where  col1='xxx';

Oracle DB hints 概念：10g資料庫可以使用更多新的optimizer hints來控制優化行為。現在讓我們快速解析一下這些強大的新hints：

1、spread_min_analysis

   使用這一hint，你可以忽略一些關於如詳細的關係，像依賴圖分析等試算表的編譯時間優化規則。其他的一些優化，如創建過濾以有選擇性的定位試算表訪問結構並限制修訂規則等，得到了繼續使用。

   由於在規則數非常大的情況下，試算表分析 ( Analyze ) 會很長。這一提示可以幫助我們減少由此產生的數以百小時計的編譯時間。

例：SELECT /*+ SPREAD_MIN_ANALYSIS */ ...

2、spread_no_analysis

   通過這一hint，可以使無試算表分析( Analyze )成為可能。同樣，使用這一hint可以忽略修訂規則和過濾產生。如果存在一試算表分析，編譯時間可以被減少到最低程度。

例：SELECT /*+ SPREAD_NO_ANALYSIS */ ...

3、use_nl_with_index

   這項hint使CBO通過嵌套迴圈把特定的表格加入到另一原始行。只有在以下情況中，它才使用特定表格作為內部表格：如果沒有指定標籤，CBO必須可以使 用一些標籤，且這些標籤至少有一個作為索引鍵值加入判斷；反之，CBO必須能夠使用至少有一個作為索引鍵值加入判斷的標籤。

例： SELECT /*+ USE_NL_WITH_INDEX (polrecpolrind) */ ...

4、CARDINALITY

  此hint定義了對由查詢或查詢部分返回的基數的評價。注意如果沒有定義表格，基數是由整個查詢所返回的總行數。

例：SELECT /*+ CARDINALITY ( [tablespec] card ) */

5、SELECTIVITY

  此hint定義了對查詢或查詢部分選擇性的評價。如果只定義了一個表格，選擇性是在所定義表格裡滿足所有單一表格判斷的行部分。如果定義了一系列表格，選擇性是指在合併以任何順序滿足所有可用判斷的全部表格後，所得結果中的行部分。

例： SELECT /*+ SELECTIVITY ( [tablespec] sel ) */

然而，注意如果hints CARDINALITY 和 SELECTIVITY都定義在同樣的一批表格，二者都會被忽略。

6、no_use_nl

  Hint no_use_nl 使CBO執行迴圈嵌套，通過把指定表格作為內部表格，把每個指定表格連接到另一原始行。通過這一hint，只有hash join和sort-merge joins會為指定表格所考慮。

例： SELECT /*+ NO_USE_NL ( employees ) */ ...

7、no_use_merge

  此hint使CBO通過把指定表格作為內部表格的方式，拒絕 sort-merge 把每個指定表格加入到另一原始行。

例： SELECT /*+ NO_USE_MERGE ( employees dept ) */ ...

8、no_use_hash

  此hint使CBO通過把指定表格作為內部表格的方式，拒絕hash joins把每個指定表格加入到另一原始行。

例： SELECT /*+ NO_USE_HASH ( employees dept ) */ ...

9、no_index_ffs

  此hint使CBO拒絕對指定表格的指定標籤進行fast full-index scan。 Syntax: /*+ NO_INDEX_FFS ( tablespecindexspec ) */


在SQL優化過程中常見HINT的用法(前10個比較常用, 前3個最常用)：

1. /*+ INDEX */ 和 /*+ INDEX(TABLE INDEX1, index2) */ 和 /*+ INDEX(tab1.col1 tab2.col2) */ 和 /*+ NO_INDEX */ 和 /*+ NO_INDEX(TABLE INDEX1, index2) */

表明對表選擇索引的掃描方法：
第一種：不指定索引名是讓 oracle 對表中可用索引比較並選擇某個最佳索引;
第二種：指定索引名且可指定多個索引;
第三種：是10g開始有的, 指定列名, 且表名可不用別名;
第四種：即全資料表掃描;
第五種：表示禁用某個索引, 特別適合於準備刪除某個索引前的評估操作. 如果同時使用了 INDEX 和 NO_INDEX 則兩個提示都會被忽略掉.
例如:SELECT /*+ INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';

2. /*+ ORDERED */
FROM子句中默認最後一個表是驅動表,ORDERED將from子句中第一個表作為驅動表. 特別適合於多表連接非常慢時嘗試.
例如:SELECT /*+ ORDERED */ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

3. /*+ PARALLEL(table1,DEGREE) */ 和 /*+ NO_PARALLEL(table1) */
該提示會將需要執行全資料表掃描的查詢分成多個部分（並行度）執行, 然後在不同的作業系統進程中處理每個部分. 該提示還可用於DML語句. 如果SQL裡還有排序操作, 進程數會翻倍,此外還有一個一個負責組合這些部分的進程,如下面的例子會產生9個進程. 如果在提示中沒有指定DEGREE, 那麼就會使用創建表時的預設值. 該提示在預設情況下會使用APPEND提示. NO_PARALLEL是禁止平行作業,否則語句會使用由於定義了並行物件而產生的並行處理.
例如:select /*+ PARALLEL(tab_test,4) */ col1, col2 from tab_test order by col2;

4. /*+ FIRST_ROWS */ 和 /*+ FIRST_ROWS(n) */
表示用最快速度獲得第1/n行, 獲得最佳回應時間, 使資源消耗最小化.
在 update 和 delete 語句裡會被忽略, 使用分組語句如 group by/distinct/intersect/minus/union 時也會被忽略.
例如:SELECT /*+ FIRST_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

5. /*+ RULE */
表明對語句塊選擇基於規則的優化方法.
例如:SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

6. /*+ FULL(TABLE) */
表明對表選擇全域掃描 Full Table Scan 的方法.
例如:SELECT /*+ FULL(A) */ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';

7. /*+ LEADING(TABLE) */
類似於ORDERED提示, 將指定的表作為連接次序中的驅動表.

8. /*+ USE_NL(TABLE1,TABLE2) */
將指定表與嵌套的連接的行源進行連接,以最快速度返回第一行再連接,與 USE_MERGE 剛好相反.
例如:SELECT /*+ ORDERED USE_NL(BSEMPMS) */ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

9. /*+ APPEND */ 和 /*+ NOAPPEND */
直接插入到表的最後,該提示不會檢查當前是否有插入操作所需的塊空間而是直接添加到新塊中, 所以可以提高速度. 當然也會浪費些空間, 因為它不會使用那些做了delete操作的塊空間. NOAPPEND提示則相反,所以會取消PARALLEL提示的默認APPEND提示.
例如:insert /*+ append */ into test1 select * from test4;
insert /*+ parallel(test1) noappend */ into test1 select * from test4;

10. /*+ USE_HASH(TABLE1,table2) */
將指定的表與其它行源通過雜湊連接方式連接起來.為較大的結果集提供最佳回應時間. 類似於在連接表的結果中遍歷每個表上每個結果的嵌套迴圈, 指定的hash表將被放入記憶體, 所以需要有足夠的記憶體（hash_area_size或pga_aggregate_target）才能保證語句正確執行, 否則將在磁片裡進行.
例如:SELECT /*+ USE_HASH(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

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

11. /*+ USE_MERGE(TABLE) */
將指定的表與其它行源通過合併排序連接方式連接起來.特別適合於那種在多個表大量行上進行集合操作的查詢, 它會將指定表檢索到的的所有行排序後再被合併, 與USE_NL剛好相反.
例如:SELECT /*+ USE_MERGE(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

12. /*+ ALL_ROWS */
表明對語句塊選擇基於開銷的優化方法,並獲得最佳輸送量,使資源消耗最小化. 可能會限制某些索引的使用.
例如:SELECT /*+ ALL+_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

13. /*+ CLUSTER(TABLE) */
提示明確表明對指定表選擇簇掃描的存取方法. 如果經常訪問連接表但很少修改它, 那就使用集群提示.
例如:SELECT /*+ CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

14. /*+ INDEX_ASC(TABLE INDEX1, INDEX2) */
表明對表選擇索引昇冪的掃描方法. 從8i開始, 這個提示和INDEX提示功能一樣, 因為預設oracle就是按照昇冪掃描索引的, 除非未來oracle還推出降冪掃描索引.
例如:SELECT /*+ INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

15. /*+ INDEX_COMBINE(TABLE INDEX1, INDEX2) */
指定多個點陣圖索引, 對於B樹索引則使用INDEX這個提示,如果INDEX_COMBINE中沒有提供作為參數的索引,將選擇出點陣圖索引的布林組合方式.
例如:SELECT /*+ INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI) */ * FROM BSEMPMS WHERE SAL<5000000 AND HIREDATE<SYSDATE;

16. /*+ INDEX_JOIN(TABLE INDEX1, INDEX2) */
合併索引, 所有資料都已經包含在這兩個索引裡, 不會再去訪問表, 比使用索引並通過rowid去掃描表要快5倍.
例如:SELECT /*+ INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI) */ SAL,HIREDATE FROM BSEMPMS WHERE SAL<60000;

17. /*+ INDEX_DESC(TABLE INDEX1, INDEX2) */
表明對表選擇索引降冪的掃描方法.
例如:SELECT /*+ INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

18. /*+ INDEX_FFS(TABLE INDEX_NAME) */
對指定的表執行快速全索引掃描,而不是全資料表掃描的辦法.要求要檢索的列都在索引裡, 如果表有很多列時特別適用該提示.
例如:SELECT /*+ INDEX_FFS(BSEMPMS IN_EMPNAM) */ * FROM BSEMPMS WHERE DPT_NO='TEC305';

19. /*+ NO_EXPAND */
對於WHERE後面的OR 或者IN-LIST的查詢語句,NO_EXPAND將阻止其基於優化器對其進行擴展, 縮短解析時間.
例如:SELECT /*+ NO_EXPAND */ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

20. /*+ DRIVING_SITE(TABLE) */
強制與ORACLE所選擇的位置不同的表進行查詢執行.特別適用於通過dblink連接的遠端表.
例如:SELECT /*+ DRIVING_SITE(DEPT) */ * FROM BSEMPMS,DEPT@BSDPTMS DEPT WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

21. /*+ CACHE(TABLE) */ 和 /*+ NOCACHE(TABLE) */
當進行全資料表掃描時,CACHE提示能夠將表全部緩存到記憶體中,這樣訪問同一個表的使用者可直接在記憶體中查找資料. 比較適合資料量小但常被訪問的表, 也可以建表時指定cache選項這樣在第一次訪問時就可以對其緩存. NOCACHE則表示對已經指定了CACHE選項的表不進行緩存.
例如:SELECT /*+ FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

22. /*+ PUSH_SUBQ */
當SQL裡用到了子查詢且返回相對少的行時, 該提示可以盡可能早對子查詢進行評估從而改善性能, 不適用於合併連接或帶遠端表的連接.
例如:select /*+ PUSH_SUBQ */ emp.empno, emp.ename, itemno from emp, orders where emp.empno = orders.empno and emp.deptno = (select deptno from dept where loc='XXX');
遠端連接其他資料庫，注意判斷資料庫是否啟動，或者是否有需要的表，否則會出錯

23. /*+ INDEX_SS(TABLE INDEX1,INDEX2) */
指示對特定表的索引使用跳躍掃描, 即當組合索引的第一列不在where子句中時, 讓其使用該索引

參考資料：

http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#autoId0

http://jojo117.iteye.com/blog/584845

本篇文章主要在介紹 Oracle SQL Loader： SQLLDR 的使用方法，如何將檔案資料快速上載到 Oracle 資料庫的 Table 中。

• Input data file for SQL*Loader

準備要匯入的資料，以逗點'，'作為每一欄位的分隔，每一行則為每一筆資料。以下列的來說，則有六筆資料，每一筆資料則有 4 個欄位值。

• SQL*Loader Control File

依匯入資料的格式，作好SQL Loader 控制檔

參數說明如下：

• infile – 資料檔放置的路徑及檔名
• into table – 要載入到那一個 table name
• fields terminated by – 欄位與欄位之間的分隔符號
• ( id, name, dept, salary ) – 列出是要將資料放入那幾個欄位

• 在資料庫中建立一個table：employee

•  執行 sqlldr 上載資料

• 驗證一下資料是否有上載到資料庫的 table:employee 中

• 若有新的資料，要加入到 table：employee ，則必須在控制檔增加 參數：append

要增加的資料如下：

• 再執行一次 sqlldr

• 再次驗證資料

• 若要上載到資料庫的資料格式是固定位置如下：

• 控制檔可以修改如下：

驗證資料：

　　Oracle 從 9i 開始提供了 Flashback Query 功能，可用於恢復錯誤的 DML 操作。在 Oracle 10g 中對 Flashback Query 做了較大改進，不再局限於 Flashback Query ，還可用於恢復錯誤的 DDL(Drop) 操作。

　　Flashback Query  是透過一個新的 Package：DBMS_FLASH 來實現。DBA 可使用 Flashback Query 可以及時取得錯誤操作 DML（Delete、Update、Insert） 在當前某一時間點資料庫的映射視圖，DBA 可以利用系統時間或系統改變號（SCN：System Change Number）來指定這個唯讀視圖，並可以針對錯誤進行相應的恢復措施。

　　以下來實作一下整個過程：

1. 可以先用下列 SQL 語法來查詢 recyclebin 是否有被 drop 的 table，此時應該是沒有的。

SQL>select object_name,original_name,operation,type,droptime from recyclebin;

 2. 建立測試的 table : demo_users

create table demo_users (id char(2), name varchar2(10));
insert into demo_users values ('01','David');
insert into demo_users values ('02','Scott');
select * from demo_users;

 可以查詢到有兩筆資料在 demo_users 的資料表( Table )裡。

3. 刪除資料表 demo_users 並在查詢資源回收筒中是否有被刪除的資料表。

SQL>drop table demo_users;
SQL>SELECT object_name,original_name,operation,type,droptime 
FROM recyclebin;

 此時發現有一筆資料

4. 回復此資料表 demo_users

SQL>flashback table demo_users to before drop ;

 這樣子就可以將資料表 Table：demo_users 回復了。

      Oracle Flashback Database 的特性允許透過 SQL 語法 Flashback Database 語句，讓資料庫回到當前的某一個時間點或者SCN，而不需要做時間點的恢復。Flashback Database 可以迅速將資料庫回到錯誤操作或人為錯誤的前一個時間點，如 Ctrl+Z 的 "復原" 操作，可以不利用備份資料 (RM) 就可以快速的實現以時間點為基礎來作恢復。Oracle 通過新建的 Flashback Logs，記錄資料庫的 Flashback 操作。如果希望能 Flashback Database，需要設置如下參數：DB_RECOVER_FILE_DEST日誌的存放位置， DB_RECOVER_FILE_DEST_SIZE恢復區的大小。在建立資料庫的時候，Oracle將自動建立恢復區，但預設是關閉的，需要執行 alter database flashback on 的指令來啟動。

• 若要回復整個資料庫到當前的某一個時間點，SQL 指令為：

SQL>flashback database to time to_timestamp(xxx);

•  若要回復某一個資料表 Table 到當前的某一個時間點，SQL 指令為：

SQL> flashback table table_name to timestamp 
to_timestamp('2006-05-30 14:21:43','yyyy-mm-dd hh24:mi:ss');

• 有關scn值就是記錄在這個table裡：flashback_transaction_query

參考文獻: http://blog.roodo.com/mywork/archives/1684525.html

Configuring Your Database for Oracle Flashback Transaction Query

To configure your database for the Oracle Flashback Transaction Query feature, you or your database administrator must do the following:

• Ensure that Oracle Database is running with version 10.0 compatibility.

• Enable supplemental logging:

  ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

　　Oracle 的 External Tables 被定義為可以讀取資料庫外部的資料，如：文字檔案、CSV...等檔案；您可以把它當成是一個可以不需事先將資料導入( load in ) 資料庫就可以進行 Query 的 View，您可以針對 external table 的 data 作 select、join、sort 等動作，也可以對 external tables 建立 views，synonymes。但是，你不能對這些 external 作DML 的動作(UPDATE, INSERT, or DELETE)，也就是只能讀取( Read Only )，而且也不能建立 index。

下面的範例中，將對不同格式 Comma delimited 及 Fixed field length 的檔案來作示範。

Step I: 建立 External Tables 所需的文字檔

Comma delimited (文字檔案、CSV) 檔案資料格式如下，各欄位以 ',' 作為區隔

200,Whalen,Administration,1987.09.17 00:00:00
201,Hartstein,Marketing,1996.02.17 00:00:00
202,Fay,Marketing,1997.08.17 00:00:00
114,Raphaely,Purchasing,1994.12.07 00:00:00
115,Khoo,Purchasing,1995.05.18 00:00:00
116,Baida,Purchasing,1997.12.24 00:00:00
117,Tobias,Purchasing,1997.07.24 00:00:00
118,Himuro,Purchasing,1998.11.15 00:00:00

 Step II: 建立 Directory 物件 

為了預防 ORA-29913 的錯誤，因此需授權需要讀取資料的人有讀／寫的權限

Step III: 建立 External Tables

ACCESS PARAMETERS 參數指定，此 External Tables 的格式，以 NEWLINE為每筆資料的分隔，每個欄位用 ',' 分隔，若有無值的狀況，當成 NULL，四個欄位分別為 employee_id，last_name，department_name，hire_date，其中 hire_date 的 format 指定為 "YYYY.MM.DD HH24:MI:SS"

Step IV: Querying Data

查詢出來的資料如下圖：
 

Fixed field length 檔案資料格式如下，各欄位的資料長度是固定的，以欄位的起始位址作為區隔，如前兩碼1-2是國家代碼，第3碼是區域碼

AR2
AU3
BE1
BR2
CA2
CH1
CN3
DE1

 Fixed field length所需建立 External Tables 的語法如下：

CREATE TABLE ext_table_fixed (
   country_id CHAR(2),
   region_id NUMBER(1)
)
ORGANIZATION EXTERNAL (
   type       oracle_loader
   default directory ext_dir
   access parameters (
     records delimited by newline
     fields (
       country_id position(1:2) ,
       region_id  position(3:4)
    )
  )
  location ('country_region.txt')
)
reject limit unlimited;

延伸討論

若我們將 External Table 實體目錄 'C:\oraclexe\ExternalTable' 下的檔案更名或刪除，則系統會有錯誤訊息，因此可以得知，它是直接去讀取該目錄下的檔案內容。

Populating Tables using the INSERT command
如果您想對這些資料作進一步的操作，可以是使用"insert into ... select from" 的語法來將這些資料加入 Database 中，這種方式比 SQL*Loader的效率好很多。

Dropping External Tables
如果你不需要這些 External Tables，可以使用 DROP TABLE statement 將它們移除，不過這不會影響到存在於資料庫外的檔案。

Summary
基本上你也可以使用 SQL*Loader來完成以上的事情，但是相較之下 External Tables 彈性跟效率都比較好。External Table尚有其他的定義和使用注意事項( Ex. Performance )，在使用前最好是先評估一下。

參考文獻
External tables in Oracle http://www.adp-gmbh.ch/ora/misc/ext_table.html