PART1: http://www.mssqltips.com/tip.asp?tip=1687
PART2: http://www.mssqltips.com/tip.asp?tip=1698
PART3: http://www.mssqltips.com/tip.asp?tip=1709
PART4: http://www.mssqltips.com/tip.asp?tip=1721
問題一:
基本上按照以上步驟安裝即可~~!!
比較要注意的就是如果OS有裝賽門鐵克防毒那可能會出現一些問題!!
出現的問題就是當一節點重開後~另一節點就會有認不到的問題!!
此BUG現在不知道賽門鐵克是否有修復了!
問題二:
如果SQL Server 2008 是要安裝在Windows Server 2005上的話!!
要把SQL Server 2008 +SP1整合在一起!!這樣裝起來才不會出錯!
相關網址:
http://sharedderrick.blogspot.com/2010/01/sql-server-2008-with-sp1-slipstreaming.html
2010年10月14日 星期四
MSSQL Performance Dashboard Reports
微軟有針對SQL Server 2005 SP2 出了一個效能檢視工具---SQL Server 2005 Performance Dashboard Reports!!
這個工具是不用錢的~~只要有裝SQL Server 2005 SP2 以上的版本都能使用 (SQL Server 2008也可以用喔)~~
以下針對安裝方法及使用方法來介紹!!!
1.安裝方式:
1-0:系統需求:
安裝Performance Dashboard Reports需要SQL Server 2005 SP2以上的版本(SQL Server 2008也可以用喔)
1-1:下載檔案:(SQL Server 2008需下載第二個載點的檔案)
載點1: http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en
載點2:http://blogs.technet.com/b/rob/archive/2009/02/18/performance-dashboard-reports-for-sql-server-2008.aspx
1-2:開始安裝:
SQL SERVER 2005 : 一直按NEXT就可以了!!!
SQL SERVER 2008:
A.更改預設安裝路徑:
C:\Program Files\Microsoft SQL Server\100\Tools\PerformanceDashboard
B.檔案取代:
將載點2的檔案複製到安裝目錄取代檔案!!!(Performance_dashboard_main.rdl 和Setup.sql)
1-3:SSMS執行Setup.sql SSMS執行Setup.sql 前,建議修改datediff(ms, …) 變更為datediff(s, …)~~
通常執行Setup.sql是不會遇到什麼問題!!
1-4:SSMS新增效能報表
STEP1:
STEP2:
C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard
安裝至此算是告一段落囉~~~不會太難!!!滑鼠點一點就裝完了!!
接下來可以開始使用囉!!
2.使用方法始用SSMS點開performance_dashboard_main這張報表就可以囉!
報表裏面有提供一些效能的資訊!Performance Dashboard Reports提供了以下以幾點功能:
1.CPU bottlenecks (and what queries are consuming the most CPU)
2.IO bottlenecks (and what queries are performing the most IO)
3.Index recommendations generated by the query optimizer (missing indexes)
4.Blocking
5.Latch contention
以上這五點我覺得Index 建議這項最好用!!
他可以幫你檢查出該用INDEX卻沒用的欄位!!也可以看到有哪些INDEX被用到的機率極低!
這個工具是不用錢的~~只要有裝SQL Server 2005 SP2 以上的版本都能使用 (SQL Server 2008也可以用喔)~~
以下針對安裝方法及使用方法來介紹!!!
1.安裝方式:
1-0:系統需求:
安裝Performance Dashboard Reports需要SQL Server 2005 SP2以上的版本(SQL Server 2008也可以用喔)
1-1:下載檔案:(SQL Server 2008需下載第二個載點的檔案)
載點1: http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en
載點2:http://blogs.technet.com/b/rob/archive/2009/02/18/performance-dashboard-reports-for-sql-server-2008.aspx
1-2:開始安裝:
SQL SERVER 2005 : 一直按NEXT就可以了!!!
SQL SERVER 2008:
A.更改預設安裝路徑:
C:\Program Files\Microsoft SQL Server\100\Tools\PerformanceDashboard
B.檔案取代:
將載點2的檔案複製到安裝目錄取代檔案!!!(Performance_dashboard_main.rdl 和Setup.sql)
1-3:SSMS執行Setup.sql SSMS執行Setup.sql 前,建議修改datediff(ms, …) 變更為datediff(s, …)~~
通常執行Setup.sql是不會遇到什麼問題!!
1-4:SSMS新增效能報表
STEP1:
STEP2:
C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard
安裝至此算是告一段落囉~~~不會太難!!!滑鼠點一點就裝完了!!
接下來可以開始使用囉!!
2.使用方法始用SSMS點開performance_dashboard_main這張報表就可以囉!
報表裏面有提供一些效能的資訊!Performance Dashboard Reports提供了以下以幾點功能:
1.CPU bottlenecks (and what queries are consuming the most CPU)
2.IO bottlenecks (and what queries are performing the most IO)
3.Index recommendations generated by the query optimizer (missing indexes)
4.Blocking
5.Latch contention
以上這五點我覺得Index 建議這項最好用!!
他可以幫你檢查出該用INDEX卻沒用的欄位!!也可以看到有哪些INDEX被用到的機率極低!
MySQL Innodb 與 Binlog mode關係!!!
今天在做DB(innodb) table schema匯入後要異動資料時有發生下列錯誤
ERROR Log: binary logging not possible. message: transaction level 'read-committed' in innodb is not safe for binlog mode 'statement'
原因:當binlog_format 為statement時, read committed 會發生錯誤!!!
解決方法: (推薦方法4)
方法1.mysql> set session transaction isolation level REPEATABLE READ ; REPEATABLE READ ;
方法2. mysql>set session binlog_format=row;
方法3. mysql>set session binlog_format= mixed;
方法4. my.cnf 增加底下兩行~~在重啟MYSQL
binlog_format=mixed or row
innodb_locks_unsafe_for_binlog=1
所以以後要注意read committed與binlog格式的關係，否則會導致資料無法異動!!!
Binlog format:
從 MySQL 5.1.12 開始，binlog_format可以用以下三種模式:
1. STATEMENT: SQL語句的log(statement-based)
2. ROW:資料行的log(row-based)
3. MIXED: statement && row 混合使用,預設是statement
Binlog主要是做mysql replication或者是恢復某依段時間資料時會用到,也有人拿Binlog來做mysql 的差異備份~~~
所以對於Binlog format的選擇也是蠻重要的~~
如果對資料品質要求高一點的話可以選擇ROW層級的~~~
詳細Binlog format參考資料:
http://homeserver.com.tw/mysql/mysql-%e5%90%84%e7%a8%aebinlog_format%e5%84%aa%e7%bc%ba%e9%bb%9e%e6%af%94%e8%bc%83/
ERROR Log: binary logging not possible. message: transaction level 'read-committed' in innodb is not safe for binlog mode 'statement'
原因:當binlog_format 為statement時, read committed 會發生錯誤!!!
解決方法: (推薦方法4)
方法1.mysql> set session transaction isolation level REPEATABLE READ ; REPEATABLE READ ;
方法2. mysql>set session binlog_format=row;
方法3. mysql>set session binlog_format= mixed;
方法4. my.cnf 增加底下兩行~~在重啟MYSQL
binlog_format=mixed or row
innodb_locks_unsafe_for_binlog=1
所以以後要注意read committed與binlog格式的關係，否則會導致資料無法異動!!!
Binlog format:
從 MySQL 5.1.12 開始，binlog_format可以用以下三種模式:
1. STATEMENT: SQL語句的log(statement-based)
2. ROW:資料行的log(row-based)
3. MIXED: statement && row 混合使用,預設是statement
Binlog主要是做mysql replication或者是恢復某依段時間資料時會用到,也有人拿Binlog來做mysql 的差異備份~~~
所以對於Binlog format的選擇也是蠻重要的~~
如果對資料品質要求高一點的話可以選擇ROW層級的~~~
詳細Binlog format參考資料:
http://homeserver.com.tw/mysql/mysql-%e5%90%84%e7%a8%aebinlog_format%e5%84%aa%e7%bc%ba%e9%bb%9e%e6%af%94%e8%bc%83/
MySQL 讀寫分離 && Load Balance-MYSQL PROXY
這幾天在研究及測試MYSQL PROXY這個架構!
以下為研究之後的一些結果!!參考看看囉!!
MYSQL PROXY架構模型:
MYSQL PROXY優點:
1.Master / Slave讀寫分離
2.Slave DB Connection Load Balance
3.Slave DB Failover
4.程式開發較簡單(不用考慮Connection)
MYSQL PROXY缺點:
1.目前尚是Alpha版!官方不保證他的穩定性!
2.當MYSQL PROXY這個服務掛掉後,client端就無法對Master / Slave DB 作存取
結論:
在對於資料及時性要求不高的環境上,可以考慮用看看!
如果有考慮要用這個solution的話,還需要用實體機器作一下壓力測試!
畢竟這個版本官方也不能保證穩定性!
MySQL 讀寫分離 && Load Balance---Amoeba
如果讀寫分離由程式端控制的話,目前有找到一款套件Amoeba-mysql來控制Slave DB 的Load Balance
這個是大陸人寫的套件(非MYSQL官方套件)!
目前Amoeba-mysql測試Load Balance功能,沒有太大問題發生!! 比之前測試MYSQL_PROXY穩定很多!
雖然穩定~~但是他有限定支援的SQL~~~有些SQL語法不支援~~
Amoeba-mysql架構模型:
根據以上的架構需要
1.MASTER DB:需要 1台
2.SLAVE DB : 需要2台或更多
3.Amoeba-Mysql : 建議至少獨立一台, 兩台以上可以做到Amoeba-Mysql備援 ;
如果不能獨立出來的話Amoeba-Mysql也可以建在Master / Slave 上面
Amoeba-mysql:http://amoeba.sourceforge.net/wordpress/
Windows XP + VMWare Server 1.0.6 + CentOS 5.2 + Oracle 10g Rac (10.2.0.1) 安裝文章 5
Windows XP + VMWare Server 1.0.6 + CentOS 5.2 + Oracle 10g Rac (10.2.0.1) 安裝文章 5
************************************************** ****************
以下是測試項目，來測試發現，RAC是否裝好了。
************************************************** ****************
在運行srvctl的時候，遇到:(rac1,rac2都需要改)
/home/oracle/app/oracle/product/10.2.0/db_1/jdk/jre/bin/java: error while loading shared libraries: libpthread.so.0: cannot open shared object file: No such file or directory
的錯誤，可以按照如下方式解決: (ORACLE)
$ cd /home/oracle/app/oracle/product/10.2.0/db_1/bin/
$ cp srvctl srvctl.bak
$ gedit srvctl
LD_ASSUME_KERNEL=2.4.19
export LD_ASSUME_KERNEL
同樣在其後新增加一行: unset LD_ASSUME_KERNEL
測試資料庫狀態:(在rac1,rac2上分別測試) (ORACLE)
$ cd /home/oracle/app/oracle/product/10.2.0/db_1/bin/
$ srvctl config database -d RAC
rac1 RAC1 /home/oracle/app/oracle/product/10.2.0/db_1
rac2 RAC2 /home/oracle/app/oracle/product/10.2.0/db_1
測試資料庫當前狀態:(在rac1,rac2上分別測試) (ORACLE)
$ cd /home/oracle/app/oracle/product/10.2.0/db_1/bin/
$ unset LANG //這個是在用telnet登錄時，中文會出現亂碼的情況下的設置，改成了英語(缺省設置)
$ srvctl status database -d RAC
Instance RAC1 is running on node rac1
Instance RAC2 is running on node rac2
$ srvctl status database -d rac
Instance RAC1 is running on node rac1
Instance RAC2 is running on node rac2
開來不區分大小寫,rac RAC ,但在ORACLE_SID中就要寫RAC1, RAC2 ,RAC，不能寫rac1,rac2,rac,sqlplus裡面要區別大小寫。
測試集群CRS當前狀態:(在rac1,rac2上分別測試) (ORACLE)
[oracle@rac1 ~]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
[oracle@rac2 ~]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
測試集群當前狀態:(在rac1,rac2上分別測試) (ORACLE)
$ cd /home/oracle/app/crs/bin
$ ./crs_stat -t
Name Type Target State Host
-------------------------------------------------- ----------
ora....C1.inst application ONLINE ONLINE rac1
ora....C2.inst application ONLINE ONLINE rac2
ora.RAC.db application ONLINE ONLINE rac2
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
測試集群應用程序啟動狀態:(在rac1,rac2上分別測試)(ORACLE)
$ cd /home/oracle/app/crs/bin
$ unset LANG
$ srvctl status nodeapps -n rac1
VIP is running on node: rac1
GSD is running on node: rac1
Listener is running on node: rac1
ONS daemon is running on node: rac1
$srvctl status nodeapps -n rac2
VIP is running on node: rac2
GSD is running on node: rac2
Listener is running on node: rac2
ONS daemon is running on node: rac2
測試集群ASM狀態:(在rac1,rac2上分別測試) (ORACLE)
$ cd /home/oracle/app/crs/bin
$ srvctl status asm -n rac1
ASM instance +ASM1 is running on node rac1.
$ srvctl status asm -n rac2
ASM instance +ASM2 is running on node rac2.
測試命令: tnsping rac1 ,tnsping rac2 ,tnsping RAC1 ,tnsping RAC2(在rac1,rac2上分別測試)
具體結果如下:
tnsping [oracle@rac2 ~]$ tnsping rac1
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 16-SEP-2008 23:33:42
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RAC.WORLD) (INSTANCE_NAME = RAC1)) )
OK (80 msec)
[oracle@rac2 ~]$ tnsping rac2
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 16-SEP-2008 23:33:51
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RAC.WORLD) (INSTANCE_NAME = RAC2)) )
OK (20 msec)
windows客戶端連接RAC的配置方法：
Tnsname.ora中的(D:\oracle\product\10.2.0\client_1\NETWORK\ADMIN)
RAC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC.WORLD)
)
)
在C:\windows\system32\drivers\etc中的hosts中加入
192.168.1.200 rac1-vip
192.168.1.201 rac2-vip
測試檢查RAC實例連接:(在rac1,rac2上分別測試)
驗證您能夠連接到每個節點上的實例和服務。 (注意:這裡的password,需要你輸入你創建資料庫的時候，賦予sys,system，那個初始密碼)
sqlplus system/password@rac1
sqlplus system/password@rac2
sqlplus system/password@rac
登錄上後，都執行一下: SQL>select * from gv$instance;有結果返回就正常了。
再執行
SQL>select instance_name,host_name,archiver,thread#,status from gv$instance;
INSTANCE_NAME HOST_NAME ARCHIVER THREAD# STATUS
RAC2 rac2.localdomain STOPPED 2 OPEN
RAC1 rac1.localdomain STOPPED 1 OPEN
測試檢查資料庫配置:(在rac1,rac2上分別測試)
[oracle@rac1 ~]$ export ORACLE_SID=RAC1 // ORACLE_SID=RAC2 ,但不能是ORACLE_SID=RAC,沒有這個資源
[oracle@rac1 ~]$ sqlplus / as sysdba;
SQL> show sga
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 113247824 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 bytes
SQL> select file_name,bytes/1024/1024 from dba_data_files;
FILE_NAME BYTES/1024/1024
+ORCLVOL/rac/datafile/system.262.665602059 400
+ORCLVOL/rac/datafile/undotbs1.263.665602155 200
+ORCLVOL/rac/datafile/sysaux.264.665602193 120
+ORCLVOL/rac/datafile/undotbs2.266.665602251 200
+ORCLVOL/rac/datafile/users.267.665602287 5
+ORCLVOL/rac/datafile/wisettms.273.665626799 100
//wisettms是我剛建的，是我通過http://192.168.1.100:1158/em方式進入創建的。
SQL> select group#, type, member,is_recovery_dest_file from v$logfile order by group#;
GROUP# TYPE MEMBER IS_REC
1 ONLINE +ORCLVOL/rac/onlinelog/group_1.258.665601953 NO
1 ONLINE +ORCLVOL/rac/onlinelog/group_1.259.665601963 YES
2 ONLINE +ORCLVOL/rac/onlinelog/group_2.260.665601973 NO
2 ONLINE +ORCLVOL/rac/onlinelog/group_2.261.665601981 YES
3 ONLINE +ORCLVOL/rac/onlinelog/group_3.268.665608153 NO
3 ONLINE +ORCLVOL/rac/onlinelog/group_3.269.665608175 YES
4 ONLINE +ORCLVOL/rac/onlinelog/group_4.270.665608195 NO
4 ONLINE +ORCLVOL/rac/onlinelog/group_4.271.665608215 YES
SQL> quit
[oracle@rac1 ~]$ export ORACLE_SID=+ASM1 // ORACLE_SID=+ASM2 ,但不能是ORACLE_SID=+ASM,沒有這個資源
[oracle@rac1 ~]$ sqlplus / as sysdba;
SQL> show sga
Total System Global Area 92274688 bytes
Fixed Size 1217884 bytes
Variable Size 65890980 bytes
ASM Cache 25165824 bytes
SQL> show parameter asm_disk
NAME TYPE VALUE
asm_diskgroups string ORCLVOL
SQL> select group_number, name, allocation_unit_size alloc_unit_size, state, type, total_mb,usable_file_mb,FREE_MB from v$asm_diskgroup;
GROUP_NUMBER NAME ALLOC_UNIT_SIZE STATE TYPE TOTAL_MB USABLE_FILE_MB FREE_MB
1 ORCLVOL 1048576 MOUNTED NORMAL 8188 1350 4747
SQL> select name,path,header_status,total_mb free_mb,trunc(bytes_read/1024/1024) read_mb,trunc(bytes_written/1024/1024) write_mb from v$asm_disk;
測試命令: sqlplus / as sysdba ,分別登錄到rac1,rac2上執行select instance_name,host_name from v$instance;
具體結果如下:
[oracle@rac2 ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 16 23:53:32 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> conn sys/password@rac1 as sysdba
Connected.
SQL> select instance_name,host_name from v$instance;
INSTANCE_NAME HOST_NAME
-------------------------------------------------- -------------------------------------------------- -------
RAC1 rac1.localdomain
SQL>quit
測試單個服務:(注意:這裡的password,需要你輸入你創建資料庫的時候，賦予sys,system，那個初始密碼)
[oracle@rac1 admin]$ sqlplus / as sysdba
SQL> conn sys/password@rac1 as sysdba
Connected.
SQL> SELECT instance_name, host_name FROM v$instance;
INSTANCE_NAME HOST_NAME
RAC1 rac1.localdomain
SQL> conn sys/password@rac2 as sysdba;
Connected.
SQL> select instance_name,host_name from v$instance;
INSTANCE_NAME HOST_NAME
RAC2 rac2.localdomain
SQL> quit
測試集群實例服務:(注意:這裡的password,需要你輸入你創建資料庫的時候，賦予sys,system，那個初始密碼)
[oracle@rac1 admin]$ sqlplus / as sysdba
SQL> conn sys/password@rac1 as sysdba
Connected.
SQL> SELECT * FROM v$active_instances;
INST_NUMBER-----------INST_NAME
1 rac1.localdomain:RAC1
2 rac2.localdomain:RAC2
SQL> SELECT inst_id, username, sid, serial# FROM gv$session WHERE username IS NOT NULL;
遇到:
SQL> conn sys/password@rac2 as sysdba;
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Warning: You are no longer connected to ORACLE.
解決方法如下:沒有找到，最好重新裝rac系統。
測試命令:察看listner基本信息,listener.ora, tnsnames.ora
在rac1上:
[oracle@rac2 ~]$ cd app/oracle/product/10.2.0/db_1/network/admin/
[oracle@rac1 admin]$ cat listener.ora
# listener.ora.rac1 Network Configuration File: /home/oracle/app/oracle/product/10.2.0/db_1/network/admin/listener.ora.rac1
# Generated by Oracle configuration tools.
LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521)(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521)(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
在rac2上:
[oracle@rac2 ~]$ cd app/oracle/product/10.2.0/db_1/network/admin/
[oracle@rac2 bin]$ more /home/oracle/app/oracle/product/10.2.0/db_1/network/adm
in/listener.ora
# listener.ora.rac2 Network Configuration File: /home/oracle/app/oracle/product/
10.2.0/db_1/network/admin/listener.ora.rac2
# Generated by Oracle configuration tools.
LISTENER_RAC2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521)(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521)(IP = FIRS
T))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
測試命令:察看tnsnames.ora基本信息, rac1和rac2是一樣的，這裡只顯示rac1上的內容
[oracle@rac1 ~]$ cd app/oracle/product/10.2.0/db_1/network/admin/
[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC.WORLD)
)
)
LISTENERS_RAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
)
RAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC.WORLD)
(INSTANCE_NAME = RAC2)
)
)
RAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC.WORLD)
(INSTANCE_NAME = RAC1)
)
)
測試Listener狀態:(rac1,rac2都需要運行)
在rac1上:
[oracle@rac1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 17-SEP-2008 00:29:55
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_RAC1
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 16-SEP-2008 17:01:55
Uptime 0 days 7 hr. 28 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /home/oracle/app/oracle/product/10.2.0/db_1/network/log/listener_rac1.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.200)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "RAC.WORLD" has 2 instance(s).
Instance "RAC1", status READY, has 2 handler(s) for this service...
Instance "RAC2", status READY, has 1 handler(s) for this service...
Service "RAC_XPT.WORLD" has 2 instance(s).
Instance "RAC1", status READY, has 2 handler(s) for this service...
Instance "RAC2", status READY, has 1 handler(s) for this service...
The command completed successfully
在rac2上:
[oracle@rac2 ~]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 17-SEP-2008 00:32:39
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_RAC2
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 16-SEP-2008 17:02:05
Uptime 0 days 7 hr. 30 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /home/oracle/app/oracle/product/10.2.0/db_1/network/log/listener_rac2.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.201)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.101)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...
Service "RAC.WORLD" has 2 instance(s).
Instance "RAC1", status READY, has 1 handler(s) for this service...
Instance "RAC2", status READY, has 2 handler(s) for this service...
Service "RAC_XPT.WORLD" has 2 instance(s).
Instance "RAC1", status READY, has 1 handler(s) for this service...
Instance "RAC2", status READY, has 2 handler(s) for this service...
The command completed successfully
[oracle@rac2 ~]$
啟動資料庫步驟:
1、首先能使用$crs_stat -t命令查看系統狀態,如果不能,請執行以下方法:
在兩台計算機上都要運行
a、# cd /etc/init.d
# ./init.crs stop
# ./init.crs disable
b、兩台計算機都需要重新啟動
# reboot
c、兩台計算機都需要執行
方法一:
# cd /etc/init.d
# ./init.crs enable
# ./init.crs start
方法二(推薦):
# cd /home/oracle/app/crs/bin
# crsctl stop crs --停止crs服務
# crs_stop -all --停止所有的crs服務
# crsctl start crs
等待90秒鐘,再用crs_stat -t在兩台計算機上測試一下:
只要不是,不管有多少的unknown都行.
2、其次,使用srvctl操作資料庫,不用crs_stop -f ,crs_start -f來啟動服務,因為crs_stop -f,crs_start -f啟動服務.
a、$ ./srvctl start nodeapps -n rac1 //在rac1上執行
$ ./srvctl start nodeapps -n rac2 //在rac2上執行
$ ./srvctl start instance -d rac -i rac1 //在rac1上執行
$ ./srvctl start instance -d rac -i rac2 //在rac2上執行
$ ./crs_stat -t
以下這個文檔有參考價值:
CRS及相關服務的常用命令的演示
1、查看狀態的命令
# su–oracle --先切換到oracle用戶
$ crs_stat -t --查看crs及所有的service的狀態
$ crsctl check crs --用crsctl命令,檢查crs相關服務的狀態
$ srvctl status nodeapps -n rac1 (rac2) --使用srvctl查看所有節點上的服務是否正常
$ srvctl status database -d rac --使用srvctl查看資料庫狀態
啟動和關閉相關服務的命令
# su – oracle
$ crs_start–all --啟動所有的crs服務
$ crs_stop–all --停止所有的crs服務
$ crsctl start crs --啟動crs服務
$ crsctl stop crs --停止crs服務
$ srvctl start nodeapps -n rac1(rac2) --啟動某節點上所有的應用
$ srvctl stop nodeapps -n rac1(rac2) --停止某節點上所有的應用
$ srvctl start listener -n rac1(rac2) --啟動某個節點的listener
$ srvctl stop listener -n rac1(rac2) --停止某個節點的listener
$ srvctl start instance–d rac–i rac1(rac2) --啟動某個instance
$ srvctl stop instance–d rac–i rac1(rac2) --停止某個instance
$ srvctl start database–d rac --啟動資料庫
$ srvctl stop database–d rac --停止資料庫
nodeapps :涉及gsd , ons , lsnr這三個服務
停止Oracle10g RAC的方法:
$ lsnrctl stop (每個節點上停止監聽，也可以用srvctl來操作)
$ emctl stop dbconsole (每個節點停止dbconsole)
$ srvctl stop database -d rac (停止資料庫所有實例)
$ srvctl stop nodeapps -n rac1 (停節點1服務)
$ srvctl stop nodeapps -n rac2 (停節點2服務)
# /etc/init.d/init.crs stop (root停cluster軟件)
或者用$ crs_stop -all
然後# init 0關閉服務器，或者如果重新啟動#init 6
啟動Oracle10g RAC的方法:
(如果OS重新啟動，那麼下面的所有服務是自動打開的【除了dbconsole】，不用手工執行了)。
$ lsnrctl start (每個節點上啟動監聽，也可以用srvctl來操作)
$ emctl start dbconsole (每個節點啟動dbconsole)
$ srvctl start database -d rac (啟動資料庫所有實例)
$ srvctl start nodeapps -n rac1 (啟動節點1服務)
$ srvctl start nodeapps -n rac2 (啟動節點2服務)
# /etc/init.d/init.crs start (root啟動cluster軟件)
或者用$ crs_start -all
$ srvctl start nodeapps -n rac1 (啟動節點1服務)
我估計等於
$ crs_start ora.rac1.LISTENER_RAC1.lsnr
$ crs_start ora.rac1.gsd
$ crs_start ora.rac1.ons
$ crs_start ora.rac1.vip
如果執行srvctl，報告有資源錯誤，首先是在rac1,rac2上都執行
# cd /etc/init.d
# ./init.crs stop
都停下後，執行
# ./init.crs start
等待90秒後，
$ ./crs_stat -t
如果還有沒有ONLINE的資源，就使用
$ ./crs_stop -f ...
$ ./crs_start -f ...
就可以了。
************************************************** ****************
以下是測試項目，來測試發現，RAC是否裝好了。
************************************************** ****************
在運行srvctl的時候，遇到:(rac1,rac2都需要改)
/home/oracle/app/oracle/product/10.2.0/db_1/jdk/jre/bin/java: error while loading shared libraries: libpthread.so.0: cannot open shared object file: No such file or directory
的錯誤，可以按照如下方式解決: (ORACLE)
$ cd /home/oracle/app/oracle/product/10.2.0/db_1/bin/
$ cp srvctl srvctl.bak
$ gedit srvctl
LD_ASSUME_KERNEL=2.4.19
export LD_ASSUME_KERNEL
同樣在其後新增加一行: unset LD_ASSUME_KERNEL
測試資料庫狀態:(在rac1,rac2上分別測試) (ORACLE)
$ cd /home/oracle/app/oracle/product/10.2.0/db_1/bin/
$ srvctl config database -d RAC
rac1 RAC1 /home/oracle/app/oracle/product/10.2.0/db_1
rac2 RAC2 /home/oracle/app/oracle/product/10.2.0/db_1
測試資料庫當前狀態:(在rac1,rac2上分別測試) (ORACLE)
$ cd /home/oracle/app/oracle/product/10.2.0/db_1/bin/
$ unset LANG //這個是在用telnet登錄時，中文會出現亂碼的情況下的設置，改成了英語(缺省設置)
$ srvctl status database -d RAC
Instance RAC1 is running on node rac1
Instance RAC2 is running on node rac2
$ srvctl status database -d rac
Instance RAC1 is running on node rac1
Instance RAC2 is running on node rac2
開來不區分大小寫,rac RAC ,但在ORACLE_SID中就要寫RAC1, RAC2 ,RAC，不能寫rac1,rac2,rac,sqlplus裡面要區別大小寫。
測試集群CRS當前狀態:(在rac1,rac2上分別測試) (ORACLE)
[oracle@rac1 ~]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
[oracle@rac2 ~]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
測試集群當前狀態:(在rac1,rac2上分別測試) (ORACLE)
$ cd /home/oracle/app/crs/bin
$ ./crs_stat -t
Name Type Target State Host
-------------------------------------------------- ----------
ora....C1.inst application ONLINE ONLINE rac1
ora....C2.inst application ONLINE ONLINE rac2
ora.RAC.db application ONLINE ONLINE rac2
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
測試集群應用程序啟動狀態:(在rac1,rac2上分別測試)(ORACLE)
$ cd /home/oracle/app/crs/bin
$ unset LANG
$ srvctl status nodeapps -n rac1
VIP is running on node: rac1
GSD is running on node: rac1
Listener is running on node: rac1
ONS daemon is running on node: rac1
$srvctl status nodeapps -n rac2
VIP is running on node: rac2
GSD is running on node: rac2
Listener is running on node: rac2
ONS daemon is running on node: rac2
測試集群ASM狀態:(在rac1,rac2上分別測試) (ORACLE)
$ cd /home/oracle/app/crs/bin
$ srvctl status asm -n rac1
ASM instance +ASM1 is running on node rac1.
$ srvctl status asm -n rac2
ASM instance +ASM2 is running on node rac2.
測試命令: tnsping rac1 ,tnsping rac2 ,tnsping RAC1 ,tnsping RAC2(在rac1,rac2上分別測試)
具體結果如下:
tnsping [oracle@rac2 ~]$ tnsping rac1
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 16-SEP-2008 23:33:42
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RAC.WORLD) (INSTANCE_NAME = RAC1)) )
OK (80 msec)
[oracle@rac2 ~]$ tnsping rac2
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 16-SEP-2008 23:33:51
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RAC.WORLD) (INSTANCE_NAME = RAC2)) )
OK (20 msec)
windows客戶端連接RAC的配置方法：
Tnsname.ora中的(D:\oracle\product\10.2.0\client_1\NETWORK\ADMIN)
RAC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC.WORLD)
)
)
在C:\windows\system32\drivers\etc中的hosts中加入
192.168.1.200 rac1-vip
192.168.1.201 rac2-vip
測試檢查RAC實例連接:(在rac1,rac2上分別測試)
驗證您能夠連接到每個節點上的實例和服務。 (注意:這裡的password,需要你輸入你創建資料庫的時候，賦予sys,system，那個初始密碼)
sqlplus system/password@rac1
sqlplus system/password@rac2
sqlplus system/password@rac
登錄上後，都執行一下: SQL>select * from gv$instance;有結果返回就正常了。
再執行
SQL>select instance_name,host_name,archiver,thread#,status from gv$instance;
INSTANCE_NAME HOST_NAME ARCHIVER THREAD# STATUS
RAC2 rac2.localdomain STOPPED 2 OPEN
RAC1 rac1.localdomain STOPPED 1 OPEN
測試檢查資料庫配置:(在rac1,rac2上分別測試)
[oracle@rac1 ~]$ export ORACLE_SID=RAC1 // ORACLE_SID=RAC2 ,但不能是ORACLE_SID=RAC,沒有這個資源
[oracle@rac1 ~]$ sqlplus / as sysdba;
SQL> show sga
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 113247824 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 bytes
SQL> select file_name,bytes/1024/1024 from dba_data_files;
FILE_NAME BYTES/1024/1024
+ORCLVOL/rac/datafile/system.262.665602059 400
+ORCLVOL/rac/datafile/undotbs1.263.665602155 200
+ORCLVOL/rac/datafile/sysaux.264.665602193 120
+ORCLVOL/rac/datafile/undotbs2.266.665602251 200
+ORCLVOL/rac/datafile/users.267.665602287 5
+ORCLVOL/rac/datafile/wisettms.273.665626799 100
//wisettms是我剛建的，是我通過http://192.168.1.100:1158/em方式進入創建的。
SQL> select group#, type, member,is_recovery_dest_file from v$logfile order by group#;
GROUP# TYPE MEMBER IS_REC
1 ONLINE +ORCLVOL/rac/onlinelog/group_1.258.665601953 NO
1 ONLINE +ORCLVOL/rac/onlinelog/group_1.259.665601963 YES
2 ONLINE +ORCLVOL/rac/onlinelog/group_2.260.665601973 NO
2 ONLINE +ORCLVOL/rac/onlinelog/group_2.261.665601981 YES
3 ONLINE +ORCLVOL/rac/onlinelog/group_3.268.665608153 NO
3 ONLINE +ORCLVOL/rac/onlinelog/group_3.269.665608175 YES
4 ONLINE +ORCLVOL/rac/onlinelog/group_4.270.665608195 NO
4 ONLINE +ORCLVOL/rac/onlinelog/group_4.271.665608215 YES
SQL> quit
[oracle@rac1 ~]$ export ORACLE_SID=+ASM1 // ORACLE_SID=+ASM2 ,但不能是ORACLE_SID=+ASM,沒有這個資源
[oracle@rac1 ~]$ sqlplus / as sysdba;
SQL> show sga
Total System Global Area 92274688 bytes
Fixed Size 1217884 bytes
Variable Size 65890980 bytes
ASM Cache 25165824 bytes
SQL> show parameter asm_disk
NAME TYPE VALUE
asm_diskgroups string ORCLVOL
SQL> select group_number, name, allocation_unit_size alloc_unit_size, state, type, total_mb,usable_file_mb,FREE_MB from v$asm_diskgroup;
GROUP_NUMBER NAME ALLOC_UNIT_SIZE STATE TYPE TOTAL_MB USABLE_FILE_MB FREE_MB
1 ORCLVOL 1048576 MOUNTED NORMAL 8188 1350 4747
SQL> select name,path,header_status,total_mb free_mb,trunc(bytes_read/1024/1024) read_mb,trunc(bytes_written/1024/1024) write_mb from v$asm_disk;
測試命令: sqlplus / as sysdba ,分別登錄到rac1,rac2上執行select instance_name,host_name from v$instance;
具體結果如下:
[oracle@rac2 ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 16 23:53:32 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> conn sys/password@rac1 as sysdba
Connected.
SQL> select instance_name,host_name from v$instance;
INSTANCE_NAME HOST_NAME
-------------------------------------------------- -------------------------------------------------- -------
RAC1 rac1.localdomain
SQL>quit
測試單個服務:(注意:這裡的password,需要你輸入你創建資料庫的時候，賦予sys,system，那個初始密碼)
[oracle@rac1 admin]$ sqlplus / as sysdba
SQL> conn sys/password@rac1 as sysdba
Connected.
SQL> SELECT instance_name, host_name FROM v$instance;
INSTANCE_NAME HOST_NAME
RAC1 rac1.localdomain
SQL> conn sys/password@rac2 as sysdba;
Connected.
SQL> select instance_name,host_name from v$instance;
INSTANCE_NAME HOST_NAME
RAC2 rac2.localdomain
SQL> quit
測試集群實例服務:(注意:這裡的password,需要你輸入你創建資料庫的時候，賦予sys,system，那個初始密碼)
[oracle@rac1 admin]$ sqlplus / as sysdba
SQL> conn sys/password@rac1 as sysdba
Connected.
SQL> SELECT * FROM v$active_instances;
INST_NUMBER-----------INST_NAME
1 rac1.localdomain:RAC1
2 rac2.localdomain:RAC2
SQL> SELECT inst_id, username, sid, serial# FROM gv$session WHERE username IS NOT NULL;
遇到:
SQL> conn sys/password@rac2 as sysdba;
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Warning: You are no longer connected to ORACLE.
解決方法如下:沒有找到，最好重新裝rac系統。
測試命令:察看listner基本信息,listener.ora, tnsnames.ora
在rac1上:
[oracle@rac2 ~]$ cd app/oracle/product/10.2.0/db_1/network/admin/
[oracle@rac1 admin]$ cat listener.ora
# listener.ora.rac1 Network Configuration File: /home/oracle/app/oracle/product/10.2.0/db_1/network/admin/listener.ora.rac1
# Generated by Oracle configuration tools.
LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521)(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521)(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
在rac2上:
[oracle@rac2 ~]$ cd app/oracle/product/10.2.0/db_1/network/admin/
[oracle@rac2 bin]$ more /home/oracle/app/oracle/product/10.2.0/db_1/network/adm
in/listener.ora
# listener.ora.rac2 Network Configuration File: /home/oracle/app/oracle/product/
10.2.0/db_1/network/admin/listener.ora.rac2
# Generated by Oracle configuration tools.
LISTENER_RAC2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521)(IP = FIRST))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521)(IP = FIRS
T))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
測試命令:察看tnsnames.ora基本信息, rac1和rac2是一樣的，這裡只顯示rac1上的內容
[oracle@rac1 ~]$ cd app/oracle/product/10.2.0/db_1/network/admin/
[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC.WORLD)
)
)
LISTENERS_RAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
)
RAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC.WORLD)
(INSTANCE_NAME = RAC2)
)
)
RAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC.WORLD)
(INSTANCE_NAME = RAC1)
)
)
測試Listener狀態:(rac1,rac2都需要運行)
在rac1上:
[oracle@rac1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 17-SEP-2008 00:29:55
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_RAC1
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 16-SEP-2008 17:01:55
Uptime 0 days 7 hr. 28 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /home/oracle/app/oracle/product/10.2.0/db_1/network/log/listener_rac1.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.200)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "RAC.WORLD" has 2 instance(s).
Instance "RAC1", status READY, has 2 handler(s) for this service...
Instance "RAC2", status READY, has 1 handler(s) for this service...
Service "RAC_XPT.WORLD" has 2 instance(s).
Instance "RAC1", status READY, has 2 handler(s) for this service...
Instance "RAC2", status READY, has 1 handler(s) for this service...
The command completed successfully
在rac2上:
[oracle@rac2 ~]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 17-SEP-2008 00:32:39
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_RAC2
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 16-SEP-2008 17:02:05
Uptime 0 days 7 hr. 30 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /home/oracle/app/oracle/product/10.2.0/db_1/network/log/listener_rac2.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.201)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.101)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...
Service "RAC.WORLD" has 2 instance(s).
Instance "RAC1", status READY, has 1 handler(s) for this service...
Instance "RAC2", status READY, has 2 handler(s) for this service...
Service "RAC_XPT.WORLD" has 2 instance(s).
Instance "RAC1", status READY, has 1 handler(s) for this service...
Instance "RAC2", status READY, has 2 handler(s) for this service...
The command completed successfully
[oracle@rac2 ~]$
啟動資料庫步驟:
1、首先能使用$crs_stat -t命令查看系統狀態,如果不能,請執行以下方法:
在兩台計算機上都要運行
a、# cd /etc/init.d
# ./init.crs stop
# ./init.crs disable
b、兩台計算機都需要重新啟動
# reboot
c、兩台計算機都需要執行
方法一:
# cd /etc/init.d
# ./init.crs enable
# ./init.crs start
方法二(推薦):
# cd /home/oracle/app/crs/bin
# crsctl stop crs --停止crs服務
# crs_stop -all --停止所有的crs服務
# crsctl start crs
等待90秒鐘,再用crs_stat -t在兩台計算機上測試一下:
只要不是,不管有多少的unknown都行.
2、其次,使用srvctl操作資料庫,不用crs_stop -f ,crs_start -f來啟動服務,因為crs_stop -f,crs_start -f啟動服務.
a、$ ./srvctl start nodeapps -n rac1 //在rac1上執行
$ ./srvctl start nodeapps -n rac2 //在rac2上執行
$ ./srvctl start instance -d rac -i rac1 //在rac1上執行
$ ./srvctl start instance -d rac -i rac2 //在rac2上執行
$ ./crs_stat -t
以下這個文檔有參考價值:
CRS及相關服務的常用命令的演示
1、查看狀態的命令
# su–oracle --先切換到oracle用戶
$ crs_stat -t --查看crs及所有的service的狀態
$ crsctl check crs --用crsctl命令,檢查crs相關服務的狀態
$ srvctl status nodeapps -n rac1 (rac2) --使用srvctl查看所有節點上的服務是否正常
$ srvctl status database -d rac --使用srvctl查看資料庫狀態
啟動和關閉相關服務的命令
# su – oracle
$ crs_start–all --啟動所有的crs服務
$ crs_stop–all --停止所有的crs服務
$ crsctl start crs --啟動crs服務
$ crsctl stop crs --停止crs服務
$ srvctl start nodeapps -n rac1(rac2) --啟動某節點上所有的應用
$ srvctl stop nodeapps -n rac1(rac2) --停止某節點上所有的應用
$ srvctl start listener -n rac1(rac2) --啟動某個節點的listener
$ srvctl stop listener -n rac1(rac2) --停止某個節點的listener
$ srvctl start instance–d rac–i rac1(rac2) --啟動某個instance
$ srvctl stop instance–d rac–i rac1(rac2) --停止某個instance
$ srvctl start database–d rac --啟動資料庫
$ srvctl stop database–d rac --停止資料庫
nodeapps :涉及gsd , ons , lsnr這三個服務
停止Oracle10g RAC的方法:
$ lsnrctl stop (每個節點上停止監聽，也可以用srvctl來操作)
$ emctl stop dbconsole (每個節點停止dbconsole)
$ srvctl stop database -d rac (停止資料庫所有實例)
$ srvctl stop nodeapps -n rac1 (停節點1服務)
$ srvctl stop nodeapps -n rac2 (停節點2服務)
# /etc/init.d/init.crs stop (root停cluster軟件)
或者用$ crs_stop -all
然後# init 0關閉服務器，或者如果重新啟動#init 6
啟動Oracle10g RAC的方法:
(如果OS重新啟動，那麼下面的所有服務是自動打開的【除了dbconsole】，不用手工執行了)。
$ lsnrctl start (每個節點上啟動監聽，也可以用srvctl來操作)
$ emctl start dbconsole (每個節點啟動dbconsole)
$ srvctl start database -d rac (啟動資料庫所有實例)
$ srvctl start nodeapps -n rac1 (啟動節點1服務)
$ srvctl start nodeapps -n rac2 (啟動節點2服務)
# /etc/init.d/init.crs start (root啟動cluster軟件)
或者用$ crs_start -all
$ srvctl start nodeapps -n rac1 (啟動節點1服務)
我估計等於
$ crs_start ora.rac1.LISTENER_RAC1.lsnr
$ crs_start ora.rac1.gsd
$ crs_start ora.rac1.ons
$ crs_start ora.rac1.vip
如果執行srvctl，報告有資源錯誤，首先是在rac1,rac2上都執行
# cd /etc/init.d
# ./init.crs stop
都停下後，執行
# ./init.crs start
等待90秒後，
$ ./crs_stat -t
如果還有沒有ONLINE的資源，就使用
$ ./crs_stop -f ...
$ ./crs_start -f ...
就可以了。
Windows XP + VMWare Server 1.0.6 + CentOS 5.2 + Oracle 10g Rac (10.2.0.1)安裝文章 4-安裝Database
Windows XP + VMWare Server 1.0.6 + CentOS 5.2 + Oracle 10g Rac (10.2.0.1)安裝文章 4-安裝Database
八、安裝Database軟件
(保證$ ./crsctl check crs $ ./crs_stat -t ,這兩個命令執行的結果都是正常)
(要新開啟一個終端,防止ORACLE_HOME=/home/oracle/app/crs的影響,我們要的位置是ORACLE_HOME=/home/oracle/app/oracle)
1.開始安裝(oracle用戶登錄)
$ cd /home/oracle/databases
$ ./runInstaller -ignoreSysPrereqs -jreLoc /usr/java/jdk1.5.0_16/jre
2.安裝圖形界面的步驟:
下一步(N) -->
企業版(E)(1.26GB)下一步(N) -->
下一步(N) -->
全選(S) -->
下一步(N) -->
(1個警告,3個要求待驗證.)下一步(N) -->
是(Y) -->
//可能出現:問是否升級選擇否(N) -->
僅安裝數據庫軟件(S) -->
下一步(N) -->
安裝(I),現在等著,大概2小時分鐘時間
10:51 - 12:54
然後在rac1,rac2上,執行root.sh
$ su - root
# cd /home/oracle/app/oracle/product/10.2.0/db_1
# ./root.sh
都是默認-->確定
注意點:
1.選擇安裝節點時,把所有節點都選上
2.選擇配置選項時,只安裝數據庫軟件,數據可以留以後再配
選擇：install database software only
3.同樣要注意時間同步問題.
$ su - root
# /etc/init.d/init.crs stop啟動底層服務
報Error while stopping resources.Possible cause : CRSD is down的錯誤
解決辦法:
在重新啟動兩台虛擬機前,關閉服務(兩台都要執行)
# cd /etc/init.d
# ./init.crs disable
重新啟動後,兩台計算機都要執行
# cd /etc/init.d
# ./init.crs enable
# ./init.crs start
# ./init.crs stop
# ./init.crs start
就可以了執行(crs_stat -t遇到unknown節點)
$ ./crs_stop -f ...
$ ./crs_start -f ...
比如說: ora.rac1.gsd UNKNOWN
$ ./crs_stop -f ora.rac1.gsd
$ ./crs_start -f ora.rac1.gsd
就可以了.
[oracle@rac1 bin]$ ./crs_stat -t
Name Type Target State Host
-------------------------------------------------- ----------
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
九、創建數據庫和ASM實例
(保證$ ./crsctl check crs $ ./crs_stat -t ,這兩個命令執行的結果都是正常)
顯示是亂碼的解決方法:
如果你用命令行進入dbca的,先敲入unset LANG以取消語言設定,然後出來的GUI界面就是英文的,我想只能這樣好受一點,總比亂碼好一點吧
1.開始安裝(oracle用戶登錄)
$ cd /home/oracle/app/oracle/product/10.2.0/db_1/bin
$ unset LANG
$ dbca
2.安裝圖形界面的步驟:
Oracle Real Application Clusters database Next -->
Create a Database Next -->
Select All Next -->
Custom Database Next-->
"RAC.WORLD" and "RAC" Next -->
不選擇什麼Next -->
輸入數據庫密碼Next -->
"Automatic Storage Management (ASM)" Next -->
Create initialization parameter file(IFILE) Next -->
輸入sys新的密碼-->
Yes (DBCA will now create and start the ASM instance) -->
Yes (Do you want listener on port 1521) -->
Create New -->
Disk Group Name取名字叫orclvol ,並選中OCL-VOL1,OCL-VOL2,OCL-VOL3,OCL-VOL4點ok -->
Next -->
Data Area: +ORCLVOL(不用改變) Next -->
勾選Specify Flash Recovery Area ,選擇Browse... -->
點擊ok -->
Flash Recovery Area: +ORCLVOL ,Flash Recovery Area Size:2048 MB (不用改變) ,不要勾選Enable Archiving(歸檔日誌) -->
取消Oracle Data Mining ,Oracle Text , Oracle OLAP , Oracle Spatial ,留下Enterprise Manager Repository勾選-->
點擊Standard Database Components...按鈕-->
不選擇Oracle XML DB,勾選Oracle JVM -->
ok -->
Next -->
Next -->
選擇Custom ,其他缺省-->
Next -->
Next -->
Finish -->
ok
安裝完後,exit
現在等著很長的時間,因為我是從晚上0:19,在第二天早上6:19發現有集群上的錯誤,從新啟動,到6:52才才裝好.反正一句話,這個時間安裝得長.
測試一下:
http://192.168.1.100:1158/em
安裝的過程中
出現fatal error:ora.rac2.LISTENER_RAC2.lsnr或者類似的資源不能啟動
解決辦法:
$ crs_stop -f ora.rac2.LISTENER_RAC2.lsnr
$ crs_start -f ora.rac2.LISTENER_RAC2.lsnr
$ crs_stop -f ora.rac2.ASM2.asm
$ crs_start -f ora.rac2.ASM2.asm
[oracle@rac2 bin]$ ./crs_stat -t
Name Type Target State Host
-------------------------------------------------- ----------
ora....C1.inst application ONLINE ONLINE rac1
ora....C2.inst application ONLINE ONLINE rac2
ora.RAC.db application ONLINE ONLINE rac2
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
八、安裝Database軟件
(保證$ ./crsctl check crs $ ./crs_stat -t ,這兩個命令執行的結果都是正常)
(要新開啟一個終端,防止ORACLE_HOME=/home/oracle/app/crs的影響,我們要的位置是ORACLE_HOME=/home/oracle/app/oracle)
1.開始安裝(oracle用戶登錄)
$ cd /home/oracle/databases
$ ./runInstaller -ignoreSysPrereqs -jreLoc /usr/java/jdk1.5.0_16/jre
2.安裝圖形界面的步驟:
下一步(N) -->
企業版(E)(1.26GB)下一步(N) -->
下一步(N) -->
全選(S) -->
下一步(N) -->
(1個警告,3個要求待驗證.)下一步(N) -->
是(Y) -->
//可能出現:問是否升級選擇否(N) -->
僅安裝數據庫軟件(S) -->
下一步(N) -->
安裝(I),現在等著,大概2小時分鐘時間
10:51 - 12:54
然後在rac1,rac2上,執行root.sh
$ su - root
# cd /home/oracle/app/oracle/product/10.2.0/db_1
# ./root.sh
都是默認-->確定
注意點:
1.選擇安裝節點時,把所有節點都選上
2.選擇配置選項時,只安裝數據庫軟件,數據可以留以後再配
選擇：install database software only
3.同樣要注意時間同步問題.
$ su - root
# /etc/init.d/init.crs stop啟動底層服務
報Error while stopping resources.Possible cause : CRSD is down的錯誤
解決辦法:
在重新啟動兩台虛擬機前,關閉服務(兩台都要執行)
# cd /etc/init.d
# ./init.crs disable
重新啟動後,兩台計算機都要執行
# cd /etc/init.d
# ./init.crs enable
# ./init.crs start
# ./init.crs stop
# ./init.crs start
就可以了執行(crs_stat -t遇到unknown節點)
$ ./crs_stop -f ...
$ ./crs_start -f ...
比如說: ora.rac1.gsd UNKNOWN
$ ./crs_stop -f ora.rac1.gsd
$ ./crs_start -f ora.rac1.gsd
就可以了.
[oracle@rac1 bin]$ ./crs_stat -t
Name Type Target State Host
-------------------------------------------------- ----------
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
九、創建數據庫和ASM實例
(保證$ ./crsctl check crs $ ./crs_stat -t ,這兩個命令執行的結果都是正常)
顯示是亂碼的解決方法:
如果你用命令行進入dbca的,先敲入unset LANG以取消語言設定,然後出來的GUI界面就是英文的,我想只能這樣好受一點,總比亂碼好一點吧
1.開始安裝(oracle用戶登錄)
$ cd /home/oracle/app/oracle/product/10.2.0/db_1/bin
$ unset LANG
$ dbca
2.安裝圖形界面的步驟:
Oracle Real Application Clusters database Next -->
Create a Database Next -->
Select All Next -->
Custom Database Next-->
"RAC.WORLD" and "RAC" Next -->
不選擇什麼Next -->
輸入數據庫密碼Next -->
"Automatic Storage Management (ASM)" Next -->
Create initialization parameter file(IFILE) Next -->
輸入sys新的密碼-->
Yes (DBCA will now create and start the ASM instance) -->
Yes (Do you want listener on port 1521) -->
Create New -->
Disk Group Name取名字叫orclvol ,並選中OCL-VOL1,OCL-VOL2,OCL-VOL3,OCL-VOL4點ok -->
Next -->
Data Area: +ORCLVOL(不用改變) Next -->
勾選Specify Flash Recovery Area ,選擇Browse... -->
點擊ok -->
Flash Recovery Area: +ORCLVOL ,Flash Recovery Area Size:2048 MB (不用改變) ,不要勾選Enable Archiving(歸檔日誌) -->
取消Oracle Data Mining ,Oracle Text , Oracle OLAP , Oracle Spatial ,留下Enterprise Manager Repository勾選-->
點擊Standard Database Components...按鈕-->
不選擇Oracle XML DB,勾選Oracle JVM -->
ok -->
Next -->
Next -->
選擇Custom ,其他缺省-->
Next -->
Next -->
Finish -->
ok
安裝完後,exit
現在等著很長的時間,因為我是從晚上0:19,在第二天早上6:19發現有集群上的錯誤,從新啟動,到6:52才才裝好.反正一句話,這個時間安裝得長.
測試一下:
http://192.168.1.100:1158/em
安裝的過程中
出現fatal error:ora.rac2.LISTENER_RAC2.lsnr或者類似的資源不能啟動
解決辦法:
$ crs_stop -f ora.rac2.LISTENER_RAC2.lsnr
$ crs_start -f ora.rac2.LISTENER_RAC2.lsnr
$ crs_stop -f ora.rac2.ASM2.asm
$ crs_start -f ora.rac2.ASM2.asm
[oracle@rac2 bin]$ ./crs_stat -t
Name Type Target State Host
-------------------------------------------------- ----------
ora....C1.inst application ONLINE ONLINE rac1
ora....C2.inst application ONLINE ONLINE rac2
ora.RAC.db application ONLINE ONLINE rac2
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
訂閱：
文章 (Atom)