Oracle - 中文輸入 出現 ORA-01756: quoted string not properly terminated

今天使用 sqlplus 測試 insert資料時 ，出現了 ORA-01756: quoted string not properly terminated  錯誤


insert into test (name) values ('大家好');


什麼...... 左看右看我的括號也沒少怎麼會出錯呢，那把中文改成英文， sql 是可以執行沒問題的

看來是中文字的問題，原來是 Client 端 與 db 端 語系不同，我們來設一下Client 端 語系

1.查詢一下DB 端的語系設定

code

SQL> select userenv('language') from dual; USERENV('LANGUAGE') ---------------------------------------------------- AMERICAN_AMERICA.UTF8 2.再來設定 Client 端語系 >export NLS_LANG=AMERICAN_AMERICA.UTF8

後來某天執行.sql 檔案時出現了

SP2-0734: unknown command beginning "嚜窺et SER..." - rest of line ignored.

從錯誤的訊息來看，好像不是SQL本身的問題

這時突然想到 .sql 檔編碼的問題

改成 UTF-8 編碼 檔首無BOM儲存，就可以成功執行囉。

Oracle 資料加密功能 - dbms_crypto

Oracle 在10G 之前必須使用DBMS_OFUSCATION_TOOLKIT 來做加密與解密，10GR2之後提供了 DBMS_CRYPTO 功能上更為強大。


DBMS_CRYPTO 除了一般資料類型外也有支援RAW、LOBS、大數據類型 BLOB與CLOB (Oracle 稱聲音與圖像也可以)。


可以使用以下的加密技術
Data Encryption Standard (DES), Triple DES (3DES, 2-key and 3-key)
Advanced Encryption Standard (AES)
MD5, MD4, and SHA-1 cryptographic hashes
MD5 and SHA-1 Message Authentication Code (MAC)

DBMS_CRYPTO 加密算法
ENCRYPT_DES ：數據加密標準。分組密碼。使用56位密鑰長度。
ENCRYPT_3DES_2KEY ：數據加密標準。分組密碼。經營一個塊上3次，2把鑰匙。 112位有效密鑰長度。
ENCRYPT_3DES ：數據加密標準。經營一個塊上3倍。
ENCRYPT_AES128 ：高級標準加密。使用128位的密鑰大小。
ENCRYPT_AES192 ：高級標準加密。使用192位的密鑰大小。
ENCRYPT_AES256 ：高級標準加密。使用256位的密鑰大小。
ENCRYPT_RC4 ：被用於加密數據流，隨機生成的密鑰是唯一的。

 DBMS_CRYPTO 填充方式
PAD_PKCS5 ：提供填充它符合PKCS＃5：基於密碼的加密標準
PAD_NONE ：不去填充。必須確保長度大小是8字元，否則會出現錯誤。
PAD_ZERO ：用零去填充。

DBMS_CRYPTO 連接方式
CHAIN_ECB ：電子密碼本。獨立加密每個明文塊。
CHAIN_CBC ：密碼塊鏈接。明文或與以前的密文塊之前被加密。
CHAIN_CFB ：密碼反饋。啟用加密數據的單位的塊大小。
CHAIN_OFB ：輸出反饋。允許運行一個分組密碼作為同步流密碼。類似於CFB，除了前面的輸出塊的是n位被移動到數據隊列的等待要被加密的最右邊的位置上。

注意
.1.DBMS_CRYPTO 包在 sys 底下，所以使用前還需授權給user
    SQL> grant execute on sys.dbms_crypto to Test ;

2.另外varchar2 必須轉換成RAW 才能輸入 DBMS_CRYPTO，可以使用UTL_RAW 或者 UTL_I18N 來做類型轉換

範例

1. 加密 - 使用的是 AES128 加密算法 + pkcs5 填充方式 + CBC 連結

code

set serveroutput on  declare      input_data varchar2(20) := 'HELLO WORL'; --資料      E_type pls_integer := dbms_crypto.encrypt_aes128 +                            dbms_crypto.pad_pkcs5 +                            dbms_crypto.chain_cbc;      E_key varchar2(16) := '0123456789123456'; --鑰匙密碼      E_encval raw(2000);   begin      E_encval := dbms_crypto.encrypt(                     src=>utl_i18n.string_to_raw(input_data,'AL32UTF8'),                     typ=>E_type,                     key=>utl_i18n.string_to_raw(E_key,'AL32UTF8'));      dbms_output.put_line(E_encval);   end;   / putout 0637CFF59C55D05E8D22A82B6CC49CF8

2.解密

code

set serveroutput on  declare      Encryption_data raw(100) := hextoraw('0637CFF59C55D05E8D22A82B6CC49CF8');      E_type pls_integer := dbms_crypto.encrypt_aes128 +                            dbms_crypto.pad_pkcs5 +                            dbms_crypto.chain_cbc;      E_key varchar2(16) := '0123456789123456'; --要與加密的鑰匙密碼一致      E_Decryption raw(200);   begin      E_Decryption := dbms_crypto.decrypt(                     src=>Encryption_data,                     typ=>E_type,                     key=>utl_i18n.string_to_raw(E_key,'AL32UTF8'));      dbms_output.put_line(utl_i18n.raw_to_char(E_Decryption));   end;   / putout HELLO WORL

Oracle - compress index 壓縮索引

Oracle 提供了很多優化DB的方式，像 compress 的功能 ，上次有解說 compress table ，這次我們來研究一下 compress index


1.首先建立一個測試的table

SQL> create table test_table1 as select * from dba_objects;

Table created.

SQL> insert into test_table1 select * from test_table1;

95850 rows created.

SQL> /

191700 rows created.

SQL> /

383400 rows created.

SQL> /

766800 rows created.

SQL> /

1533600 rows created.

SQL> /

3067200 rows created.

SQL> select count(*) from test_table1;

  COUNT(*)
----------
   6134400

約6百萬筆數

執行計畫( 因排版關係 則用截圖方式呈現

因為沒有 index 則 table full access ，consistent gets 87635

2.建立一般的index 

SQL> create index t_index on test_table1(object_type);

Index created.

SQL> set autot on

index 這次有使用到了，consisten gets 從87635 降到 1795 ，優化效果很顯著

查詢一下index size.

SQL> SELECT segment_name, header_block, bytes

  2    FROM dba_segments

  3   WHERE segment_name = 'T_INDEX';

SEGMENT_NAME             HEADER_BLOCK        BYTES

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

T_INDEX                                   174162                  142606336

index 大小約136MB

3.這次建立有壓縮的index (效果更強)

SQL> drop index t_index;

Index dropped.

SQL> create index t_index on test_table1(object_type) compress;

Index created.

使用了壓縮的index ，consistent gets 下降到1194，效果更驚人

查詢一下index size.

SQL> SELECT segment_name, header_block, bytes

      FROM dba_segments

     WHERE segment_name = 'T_INDEX';

SEGMENT_NAME             HEADER_BLOCK        BYTES

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

T_INDEX                                   174162                   83886080

index 大小約 80MB

使用壓縮的index 跟沒有壓縮的index 比較

consistent gets 從 1795 降到 1194  -> 33.48 %

cost 從 2272 降到 1287  ->  43.35%

bytes 從 136MB 縮小到 80MB  -> 41.17%

難道compress  index 就真的這麼好用?!，會不會有其他狀況導致compress  index 的優點變成缺點呢，接下來我們在做更深入的實驗

1.建立一個測試 table 

   CREATE TABLE test_com 

      ( a    NUMBER, b  NUMBER) );

2.

  SQL> begin

  2    for i in 1..500000 loop

  3    insert into test_com values(mod(i,2),i);

  4    end loop;

  5    end;

  6    /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>

2. 查詢一下此 table 的欄位重覆的值狀況如何

SQL>  select count(distinct a),count(distinct b) from test_com;

COUNT(DISTINCTA)       COUNT(DISTINCTB)

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

               2                                    500000

SQL>

A欄位 裡面只有兩個不同的值，B欄位裡有 500000 都是不同的值

3.建立一般的 index ，並查看SIZE

SQL> create index test_com_idx1 on test_com(a);

Index created.

SQL> create index test_com_idx2 on test_com(b);

Index created.

SQL>

SQL> select segment_name , bytes/1024/1024 size_mb from dba_segments where segment_name like 'TEST_C

OM_%';

SEGMENT_NAME                               SIZE_MB

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

TEST_COM_IDX1                                     8

TEST_COM_IDX2                                     9

SQL>

4.接著建立 有compress 的index ，並查看SIZE

SQL> drop index test_com_idx1;

Index dropped.

SQL> drop index test_com_idx2;

Index dropped.

SQL>

SQL> create index test_com_idx1 on test_com(a) compress;

Index created.

SQL> create index test_com_idx2 on test_com(b) compress;

Index created.

SQL>

SQL> select segment_name , bytes/1024/1024 size_mb from dba_segments where segment_name like 'TEST_C

OM_%';

SEGMENT_NAME                           SIZE_MB

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

TEST_COM_IDX1                                 7

TEST_COM_IDX2                                13

這邊看到A欄位從未壓縮到壓縮 8MB > 7MB ，而B欄位卻從9MB 增大到13MB，index 對於table 重覆值較少的欄位還會增加更多空間來存放 prefix ，想啟動壓縮的功能卻讓size 變大

當然在很多種狀況下，並不能用同一招來打天下 ，所以這也是為什麼Oracle 提供了很多的解決方案

Oracle  官方 Concepts 裡的說明

Although key compression reduces the storage requirements of an index, it can increase the CPU time required to reconstruct the key column values during an index scan. It also incurs some additional storage overhead, because every prefix entry has an overhead of 4 bytes associated with it.

index compress

優點

1.值的重覆越多壓縮的效果越好，節省更多的空間(但要是欄位值重覆很少的話，建立index compress 其實也沒太大用處)

2.掃描的block 大幅減少 

缺點

1.index 更新時需要重新解壓縮 ->更新 ->壓縮，使用更多的執行時間與CPU

2.block 的爭用，當查詢的結果都集中再同一個block 時就會引起block 的爭用

3.bitmap index 不能壓縮

PS：

1.假如要對現有的index 啟動壓縮功能，可以利用rebuild index 重建index compress 或者解除壓縮功能

alter index T_INDEX rebuild compress;

alter index T_INDEX rebuild nocompress;

2.可以建立compress 的複合式index 

create index T_INDEX on test_com(a,b,c) compress1;  只壓縮第一個欄位

create index T_INDEX on test_com(a,b,c) compress2;   對前兩個欄位進行壓縮

create index T_INDEX on test_com(a,b,c) compress3;   對前三個欄位進行壓縮

Oracle 11G RAC OCR 與 Vote disk 備份與還原

關於 RAC 最重要的 OCR與Voting 的備份與還原，DBA必須在遇到災難時去拯救還原

11G Oracle 預設是把OCR 與 Voting 放再同一個ASM disk group裡，也可以透過設定分開存放

以下是進行備份還原的步驟

1.查看OCR 相關訊息

2. 查看Voting 相關訊息 

3.查看OCR 備份資訊  (要連續執行RAC 4小時才會開啟自動備份)

下面有一個PROT-25的警告，於手動備份時失效

手動備份指令如下

4.用ms_backup 指令備份ASM disk group 的 Metadata，順便查看一下disk group資訊

5.進入ASM instance 順便備份ASM spfile (11G則必須使用 as sysasm 進入)

6.模擬 OCR 與 Voting 所在的硬碟毀損

7.查詢CRS狀態.......... 發現是正常的

8.不過在 check 硬碟時發出ERROR 訊息

查看一下log ，出現 Voting file 不見的ERROR訊息

2014-05-07 13:33:11.052: [    CSSD][2986240912]clssnmCompleteInitVFDiscovery: Completing initial voting file discovery

2014-05-07 13:33:11.052: [    CSSD][2986240912]clssnmvFindInitialConfigs: No voting files found

2014-05-07 13:33:11.052: [    CSSD][2986240912]clssnmCompleteVFDiscovery: Completing voting file discovery

2014-05-07 13:33:11.052: [    CSSD][2986240912]clssnmvVerifyCommittedConfigVFs: Insufficient voting files found, found 0 of 0 configured, needed 1 voting files

9.全部節點停掉CRS 服務

10.接下來開始恢復，先在節點1以獨占模式啟動CRS

11.重新建立ASM disk ，因為剛剛格式化硬碟所以先scandisk再createdisk

建立相同名稱的ASM DISK

查詢確定OCR 有建立

12. 進入 ASM instance 建立 ASM disk group

13.重新建好ASM disk group 後接著就可以還原OCR，使用-import 或 -restore 都可以

14.還需要恢復 ASM disk group 裡的spfile 

15.OCR恢復成功

16. 透過 crsctl replace votedisk 還原 Votedisk

操作到這邊有可能會發生以下錯誤

接著調整ASM 參數

調整完後重新啟動ASM instance 

在重新crsctl replace votedisk 還原 

17.查看恢復的Votedisk

18.退出獨佔模式  crsctl stop has -f 或 crsctl stop crs -f

19. 啟動兩節點的CRS

[root@node1 bin]# ./crsctl start has

20. 檢查CRS狀態- 恢復成功

建立包含 null 值的 index

在某些情況下，會需要查詢 table 是否有null值，根據Oracle index 的特性 b-tree index是不包含null值的，不過我們通過建立複合式index 即可解決這問題。

建立Test Table

建立一般的 Index

查看執行計畫

建立一個普通的 Index，在查詢是否為null時候，因為並沒有使用該 index，則會使用 full table scan

建立常數複合式index

查看執行計畫

因為使用了index ，consistent gets從226大幅下降到只有4

結論：由於常數複合式索引中null值也會被記錄，在查詢是否有null值時Oracle 就會利用此index則避免 full table scan

Linux Oracle 11g R2 - RAC + ASM VirtualBox 安裝

Linux Oracle 11g R2 - RAC + ASM  VirtualBox 安裝

Linux 版本
[root@node1 ~]# uname -rm
2.6.18-164.el5 i686

[root@node1 ~]# cat /etc/*-release

Enterprise Linux Enterprise Linux Server release 5.4 (Carthage)

Red Hat 4 

11GR2 32位元

安裝大致流程如下
1.分配網路IP
2.設定Linux 系統參數
3.分割硬碟
4.安裝ASM
5.安裝Grid
6.安裝11GR2 database software
7.建立db


1.在兩節點先建立用戶與群組

[root@node1 ~]# groupadd -g 501 oinstall

[root@node1 ~]# groupadd -g 502 dba

[root@node1 ~]# useradd -u 501 -g oinstall -G dba oracle

[root@node1 ~]# passwd oracle

## 用戶修改群組
usermod -g oinstall oracle

2.在兩節點建立oracle資料夾

[root@node1 ~]# mkdir /u01/oracle

[root@node1 ~]# mkdir /u01/grid

[root@node1 ~]# chown -R oracle:oinstall /u01

3.配置主機名稱

[root@node1 ~]# vi /etc/sysconfig/network

節點1

節點2

4..在兩節點設置hosts

[root@node1 ~]# vi /etc/hosts

11G還須配置一個SCAN專用IP給它

修改網卡IP
vi /etc/sysconfig/network-script/ifcfg-eth0
vi /etc/sysconfig/network-script/ifcfg-eth1

5.修改Linux參數

[root@node1 ~]# vi /etc/sysctl.conf

在文件的最後加上
kernel.shmmax = 4398046511104
kernel.shmall = 1073741824
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
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
fs.file-max = 6815744


主機 記憶體128G 配置

--128*1024*1024*1024*90% = 123695058124
--123695058124 /4096 = 30198988

kernel.shmmax = 123695058124
kernel.shmall = 30198988
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
fs.aio-max-nr = 1048576
fs.file-max = 6815744

net.ipv4.ipfrag_high_thresh = 16777216
net.ipv4.ipfrag_low_thresh = 15728640
net.ipv4.ipfrag_time = 60
net.core.rmem_max=26214400
net.core.wmem_max=26214400
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.netdev_max_backlog=10000

主機 記憶體256G 配置

kernel.shmmax = 247390116249
kernel.shmall = 60397977
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
fs.aio-max-nr = 1048576

fs.file-max = 6815744

net.ipv4.ipfrag_high_thresh = 16777216
net.ipv4.ipfrag_low_thresh = 15728640
net.ipv4.ipfrag_time = 60
net.core.rmem_max=26214400
net.core.wmem_max=26214400
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.netdev_max_backlog=10000

重新掛載生效
[root@rac1 ~]# sysctl -p


使用 yum 安裝 rpm

yum -y install gcc*
yum -y install libaio-devel*
yum -y install compat-libstdc++*
yum -y install elfutils-libelf-devel*
yum -y install libstdc++*
yum -y install ksh*


6.修改兩節點的用戶限制
[root@node1 ~]# vi /etc/security/limits.conf
文件最後加上

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

7.修改兩節點的 /etc/pam.d/login
[root@node1 ~]# vi /etc/pam.d/login

文件最後加上
session    required     /lib64/security/pam_limits.so

8.分割硬碟

配置完三個硬碟(sdb1、sdc1、sdd1) 後，不要格式化

9.兩節點同步一下硬碟訊息
[root@node1 ~]# partprobe
[root@node1 ~]#  ls /dev/sd*
/dev/sda   /dev/sda2  /dev/sdb   /dev/sdc   /dev/sdd   /dev/sde
/dev/sda1  /dev/sda3  /dev/sdb1  /dev/sdc1  /dev/sdd1  /dev/sde1

10.安裝 oracleasmlib 程式包

依照 Linux 的版本下載RPM
http://www.oracle.com/technetwork/topics/linux/asmlib/index-101839.html

   1.oracleasm-support
          oracleasm-support-2.1.8-1.el5.i386.rpm
   2.oracleasm
          oracleasm-2.6.18-164.el5-2.0.5-1.el5.i686.rpm
   3.asmlib

          oracleasmlib-2.0.4-1.el5.i386.rpm

11.新增裸設備  

a
因為我們這邊使用asmlib 所以第11 可以跳過

以下是自動產生裸設備的UDEV 的語法

[root@node1 grid]#

for i in a b c d e f g;
do
echo "KERNEL==\"sd*\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id -g -u -s %p\", RESULT==\"`scsi_id -g -u -s /block/sd$i`\", NAME=\"asm-disk$i\", OWNER=\"oracle\", GROUP=\"oinstall\", MODE=\"0660\""
done

把產生結果貼到 /etc/udev/rules.d/99-oracle-asmdevices.rules 去

[root@node1 grid]# vi /etc/udev/rules.d/99-oracle-asmdevices.rules

KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="SATA_VBOX_HARDDISK_VB68bef045-aa1801db_", NAME="raw1", OWNER="oracle", GROUP="oinstall", MODE="0660"
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="SATA_VBOX_HARDDISK_VBf301e72f-8895ea4f_", NAME="raw2", OWNER="oracle", GROUP="oinstall", MODE="0660"
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="SATA_VBOX_HARDDISK_VB91e82c7f-0105b071_", NAME="raw3", OWNER="oracle", GROUP="oinstall", MODE="0660"

重新掛載服務
start_udev

驗證
ls /dev/raw/ -l
                                                                                

12. 在兩節點分別配置ASM
[root@node1 ~]#  /etc/init.d/oracleasm configure

13.建立 asm 硬碟：  

[root@rac1 ~]# /etc/init.d/oracleasm enable 

[root@rac2 ~]# /etc/init.d/oracleasm enable

在某一節點執行以下指令
[root@node1 ~]# /etc/init.d/oracleasm createdisk DATA1 /dev/sdb1

[root@node1 ~]# /etc/init.d/oracleasm createdisk DATA2 /dev/sdc1

[root@node1 ~]# /etc/init.d/oracleasm createdisk DATA3 /dev/sdd1

假如出現 Marking disk "xxxxx" as an ASM disk FAILED 錯誤的話

1.有安裝過RAC, 執行
dd if=/dev/zero of=/dev/sda1 bs=1024k count=1
清空硬碟資訊

2.沒安裝過RAC, 查看selinux是否開啟,執行
getenforce

出現Enforcing, 表示開啟
並修改vi /etc/selinux/config  , 將selinux 改為disabled
SELINUX=disabled

14.掃描 asm 硬碟

每個節點都要執行
[root@node1 ~]# /etc/init.d/oracleasm scandisks

[root@node1 ~]# /etc/init.d/oracleasm listdisks

兩節點都要出現剛剛建立的asmdisks，假如其他節點沒有出現的話，就要檢查網路與硬碟是否設定正確

15.手動配置ssh
在安裝Clusterware和 Rac Database 時，在這過程中會以Oracle身分自動複製檔案到Rac各個節點去，在這裡我們配置ssh 讓Oracle用戶不用輸入密碼就可以有訪問各節點的能力，所以我們需要用Oracle身分建立各節點的公鑰匙

我們必須確保各節點間網路是暢通的

ping node1
ping node2
ping node1-priv
ping node2-priv

在來先在node1執行，密碼不用輸入

node2 執行，密碼不用輸入

互相交換公鑰
先在node1執行，傳送檔案到node2時會要求輸入node2的密碼

node2 回拋檔案到node1後，就可以測試看看有無成功

兩節點互相測試看看是否還需要輸入密碼

[oracle@node1 ~]$ ssh node1 date
[oracle@node1 ~]$ ssh node2 date
[oracle@node1 ~]$ ssh node1-priv date
[oracle@node1 ~]$ ssh node2-priv date
換node2 執行
[oracle@node2 ~]$ ssh node1 date
[oracle@node2 ~]$ ssh node2 date
[oracle@node2 ~]$ ssh node1-priv date
[oracle@node2 ~]$ ssh node2-priv date

執行成功就會出現以下畫面

16.安裝Grid Infrastructure

SCAN Name 是 /etc/hosts 配置的SCAN 名

新增節點，名稱也是按照 /etc/host去輸入

測試SSH 是否通行

檢查網路是否正確

選擇ASM所使用的硬碟組

按下按鈕來手動選擇ASM DISK， Linux Enterprise 5建立的ASM DISK 在 /dev/oracleasm/disks/ 底下

這邊選擇一個ASM DISK 當作 OCR+Voting 的存放位置 (10g 可以分別放置不同的ASM DISK, 11g 預設是放在同一個DISK, 安裝結束後可以透過設定來分開存放)

輸入ASM 管理的密碼

Grid Infrastructure 安裝的位置必須不同於Oracle Base安裝的位置

檢查軟體與硬體配置，發現缺少的Packages

安裝完缺少的rpm後與把所有的警告解決，我這台電腦記憶體不足才會出現Physical Memory 警告，這邊就先略過

開始安裝

安裝到最後要求在各節點使用root執行兩個程式

orainstRoot.sh
[root@node1 oraInventory]# ./orainstRoot.sh
Changing permissions of /u01/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/oraInventory to oinstall.
The execution of the script is complete.

root.sh 

[root@node1 grid]# ./root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2014-05-07 13:31:50: Parsing the host name
2014-05-07 13:31:50: Checking for super user privileges
2014-05-07 13:31:50: User has super user privileges
Using configuration parameter file: /u01/grid/crs/install/crsconfig_params
Creating trace directory
LOCAL ADD MODE
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
  root wallet
  root wallet cert
  root cert export
  peer wallet
  profile reader wallet
  pa wallet
  peer wallet keys
  pa wallet keys
  peer cert request
  pa cert request
  peer cert
  pa cert
  peer root cert TP
  profile reader root cert TP
  pa root cert TP
  peer pa cert TP
  pa peer cert TP
  profile reader pa cert TP
  profile reader peer cert TP
  peer user cert
  pa user cert
Adding daemon to inittab
CRS-4123: Oracle High Availability Services has been started.
ohasd is starting
CRS-2672: Attempting to start 'ora.gipcd' on 'node1'
CRS-2672: Attempting to start 'ora.mdnsd' on 'node1'
CRS-2676: Start of 'ora.gipcd' on 'node1' succeeded
CRS-2676: Start of 'ora.mdnsd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'node1'
CRS-2676: Start of 'ora.gpnpd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'node1'
CRS-2676: Start of 'ora.cssdmonitor' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'node1'
CRS-2672: Attempting to start 'ora.diskmon' on 'node1'
CRS-2676: Start of 'ora.diskmon' on 'node1' succeeded
CRS-2676: Start of 'ora.cssd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'node1'
CRS-2676: Start of 'ora.ctssd' on 'node1' succeeded

ASM created and started successfully.

DiskGroup OCR created successfully.

clscfg: -install mode specified
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-2672: Attempting to start 'ora.crsd' on 'node1'
CRS-2676: Start of 'ora.crsd' on 'node1' succeeded
CRS-4256: Updating the profile
Successful addition of voting disk 04e3aa63f8d14f67bffa44401277fdb9.
Successfully replaced voting disk group with +OCR.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   04e3aa63f8d14f67bffa44401277fdb9 (/dev/oracleasm/disks/OCR) [OCR]
Located 1 voting disk(s).
CRS-2673: Attempting to stop 'ora.crsd' on 'node1'
CRS-2677: Stop of 'ora.crsd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'node1'
CRS-2677: Stop of 'ora.asm' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'node1'
CRS-2677: Stop of 'ora.ctssd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'node1'
CRS-2677: Stop of 'ora.cssdmonitor' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'node1'
CRS-2677: Stop of 'ora.cssd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'node1'
CRS-2677: Stop of 'ora.gpnpd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'node1'
CRS-2677: Stop of 'ora.gipcd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.mdnsd' on 'node1'
CRS-2677: Stop of 'ora.mdnsd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.mdnsd' on 'node1'
CRS-2676: Start of 'ora.mdnsd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'node1'
CRS-2676: Start of 'ora.gipcd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'node1'
CRS-2676: Start of 'ora.gpnpd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'node1'
CRS-2676: Start of 'ora.cssdmonitor' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'node1'
CRS-2672: Attempting to start 'ora.diskmon' on 'node1'
CRS-2676: Start of 'ora.diskmon' on 'node1' succeeded
CRS-2676: Start of 'ora.cssd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'node1'
CRS-2676: Start of 'ora.ctssd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'node1'
CRS-2676: Start of 'ora.asm' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'node1'
CRS-2676: Start of 'ora.crsd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.evmd' on 'node1'
CRS-2676: Start of 'ora.evmd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'node1'
CRS-2676: Start of 'ora.asm' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.OCR.dg' on 'node1'
CRS-2676: Start of 'ora.OCR.dg' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.registry.acfs' on 'node1'
CRS-2676: Start of 'ora.registry.acfs' on 'node1' succeeded

node1     2014/05/07 13:41:30     /u01/grid/cdata/node1/backup_20140507_134130.olr
Configure Oracle Grid Infrastructure for a Cluster ... succeeded
Updating inventory properties for clusterware
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 2980 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/oraInventory
'UpdateNodeList' was successful.

[root@node1 grid]# 

17.兩節點都執行完後，按下OK

18.安裝的最後出現了錯誤 [INS-20802] Oracle Cluster Verification Utility failed.

查看一下log  /u01/oraInventory/logs/installActions2014-04-21_01-32-02PM.log

INFO: Checking Single Client Access Name (SCAN)...
INFO: Checking name resolution setup for "scan.cluster.com"...
INFO: ERROR:
INFO: PRVF-4664 : Found inconsistent name resolution entries for SCAN name "scan.cluster.com"
INFO: ERROR:
INFO: PRVF-4657 : Name resolution setup check for "scan.cluster.com" (IP address: 192.168.56.111) failed
INFO: ERROR:
INFO: PRVF-4664 : Found inconsistent name resolution entries for SCAN name "scan.cluster.com"
INFO: Verification of SCAN VIP and Listener setup failed

上網查了一下，發現這是因為是在/etc/hosts中配置了SCAN的IP，
手動ping這個IP，如果可以成功，則這個錯誤可以忽略。


19.安裝結束後檢查兩節點群集

20. 安裝 11g R2 database

只安裝database software，等等再利用dbca 建立db

 SSH 連接測試

在這邊一樣略過警告

開始進行安裝

安裝的最後會提示在兩節點執行程式，執行完後按下OK

安裝成功

21.再建立DB 前還需建立 DATA 存放的 ASM DISK

[oracle@node1 grid]# asmca

建立一個group 裡頭分配兩個asm disk , 來存放data file

再建立一個asm disk group 做為db 的 flash_recovery_area 用

22.再利用DBCA建立新DB，選擇第一個

選擇剛剛建立的+DATA並輸入安裝Grid Infrastructure時輸入的ASM管理密碼

配置 flash_recovery_area  專用的asm disk group

DB概要

DB 建立成功

登入sqlplus 檢查

node1

node2

*****************
Oracle 官方建議  linux 主機上 開啟 HugePages 功能且關閉 Transparent HugePages


1.查看  Transparent HugePages 

[root@ora-35 ~]# cat /sys/kernel/mm/redhat_transparent_hugepage/enabled
[always] madvise never
[root@ora-35 ~]# 
[root@ora-35 ~]# 

[root@ora-35 ~]# 
[root@ora-35 ~]# cat /etc/rc.local
#!/bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don't
# want to do the full Sys V style init stuff.

touch /var/lock/subsys/local
[root@ora-35 ~]# 

2. 禁用 transparent_hugepage,在 /etc/rc.local 加入 

if test -f /sys/kernel/mm/transparent_hugepage/enabled; then

echo never > /sys/kernel/mm/transparent_hugepage/enabled

fi

if test -f /sys/kernel/mm/transparent_hugepage/defrag; then

echo never > /sys/kernel/mm/transparent_hugepage/defrag

fi

[root@ora-35 ~]# cat /etc/rc.local
#!/bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don't
# want to do the full Sys V style init stuff.

touch /var/lock/subsys/local

if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi


3.以上做完需要重啟系統,,,, 以下指令執行完可以直接禁用不用重啟

[root@ora-35 ~]# 
[root@ora-35 ~]# echo never > /sys/kernel/mm/redhat_transparent_hugepage/enabled
[root@ora-35 ~]# 
[root@ora-35 ~]# 
[root@ora-35 ~]# 
[root@ora-35 ~]# cat /sys/kernel/mm/redhat_transparent_hugepage/enabled
always madvise [never]
[root@ora-35 ~]# 
[root@ora-35 ~]# 

4.開啟 HugePages

-- 先配置參數
cat  /etc/security/limits.conf

grid soft memlock 209715200
grid hard memlock 209715200
oracle soft memlock 209715200
oracle hard memlock 209715200

[root@ora-35 script]# cat hugepages_settings.sh 
#!/bin/bash
#
# hugepages_settings.sh
#
# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
# on Oracle Linux
#
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
#
# This script is provided by Doc ID 401749.1 from My Oracle Support 
# http://support.oracle.com

# Welcome text
echo "
This script is provided by Doc ID 401749.1 from My Oracle Support 
(http://support.oracle.com) where it is intended to compute values for 
the recommended HugePages/HugeTLB configuration for the current shared 
memory segments on Oracle Linux. Before proceeding with the execution please note following:
 * For ASM instance, it needs to configure ASMM instead of AMM.
 * The 'pga_aggregate_target' is outside the SGA and 
   you should accommodate this while calculating SGA size.
 * In case you changes the DB SGA size, 
   as the new SGA will not fit in the previous HugePages configuration, 
   it had better disable the whole HugePages, 
   start the DB with new SGA size and run the script again.
And make sure that:
 * Oracle Database instance(s) are up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not setup 
   (See Doc ID 749851.1)
 * The shared memory segments can be listed by command:
     # ipcs -m


Press Enter to proceed..."

read

# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`

# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk '{print $2}'`
if [ -z "$HPG_SZ" ];then
    echo "The hugepages may not be supported in the system where the script is being executed."
    exit 1
fi

# Initialize the counter
NUM_PG=0

# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | cut -c44-300 | awk '{print $1}' | grep "[0-9][0-9]*"`
do
    MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
    if [ $MIN_PG -gt 0 ]; then
        NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
    fi
done

RES_BYTES=`echo "$NUM_PG * $HPG_SZ * 1024" | bc -q`

# An SGA less than 100MB does not make sense
# Bail out if that is the case
if [ $RES_BYTES -lt 100000000 ]; then
    echo "***********"
    echo "** ERROR **"
    echo "***********"
    echo "Sorry! There are not enough total of shared memory segments allocated for 
HugePages configuration. HugePages can only be used for shared memory segments 
that you can list by command:

    # ipcs -m

of a size that can match an Oracle Database SGA. Please make sure that:
 * Oracle Database instance is up and running 
 * Oracle Database 11g Automatic Memory Management (AMM) is not configured"
    exit 1
fi

# Finish with results
case $KERN in
     '2.2') echo "Kernel version $KERN is not supported. Exiting." ;;
    '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
           echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
    '2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '3.8') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '3.10') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '4.1') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
esac

# End

-------

[root@ora-35 script]# ./hugepages_settings.sh 

This script is provided by Doc ID 401749.1 from My Oracle Support 
(http://support.oracle.com) where it is intended to compute values for 
the recommended HugePages/HugeTLB configuration for the current shared 
memory segments on Oracle Linux. Before proceeding with the execution please note following:
 * For ASM instance, it needs to configure ASMM instead of AMM.
 * The 'pga_aggregate_target' is outside the SGA and 
   you should accommodate this while calculating SGA size.
 * In case you changes the DB SGA size, 
   as the new SGA will not fit in the previous HugePages configuration, 
   it had better disable the whole HugePages, 
   start the DB with new SGA size and run the script again.
And make sure that:
 * Oracle Database instance(s) are up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not setup 
   (See Doc ID 749851.1)
 * The shared memory segments can be listed by command:
     # ipcs -m


Press Enter to proceed...

Recommended setting: vm.nr_hugepages = 50692


-------
[root@ora-36 11.2.4]#  vi /etc/sysctl.conf

vm.nr_hugepages = 50692

----------

[root@ora-36 11.2.4]# grep Huge /proc/meminfo

AnonHugePages:     22528 kB

HugePages_Total:   50692

HugePages_Free:    50692

HugePages_Rsvd:        0

HugePages_Surp:        0

Hugepagesize:       2048 kB

---------- 這邊重開DB

[oracle@ora-36 ~]$ grep Huge /proc/meminfo

AnonHugePages:     22528 kB

HugePages_Total:   50692

HugePages_Free:    41914

HugePages_Rsvd:    41911

HugePages_Surp:        0

Hugepagesize:       2048 kB

且 alert.log 中可以看到相關訊息

Starting ORACLE instance (normal)

************************ Large Pages Information *******************

Per process system memlock (soft) limit = 200 GB

Total Shared Global Region in Large Pages = 99 GB (100%)

Large Pages used by this instance: 50689 (99 GB)

Large Pages unused system wide = 3 (6144 KB)

Large Pages configured system wide = 50692 (99 GB)

Large Page size = 2048 KB

********************************************************************

就可以看到 已經在使用囉