以下是《深入解析Oracle》一書(shū)第一章的第一部分內(nèi)容。
通常所說(shuō)的Oracle Server主要由兩個(gè)部分組成:Instance和Database。Instance是指一組后臺(tái)進(jìn)程(在Windows上是一組線程)和一塊共享內(nèi)存區(qū)域;Database是指存儲(chǔ)在磁盤(pán)上的一組物理文件。通過(guò)Instance與Database協(xié)同,Oracle數(shù)據(jù)庫(kù)才能形成一個(gè)動(dòng)態(tài)的可訪問(wèn)關(guān)系型數(shù)據(jù)庫(kù)系統(tǒng)。
本章將由數(shù)據(jù)庫(kù)如何啟動(dòng)與關(guān)閉入手,開(kāi)始和大家一起進(jìn)入Oracle數(shù)據(jù)庫(kù)的國(guó)度。
1.1 數(shù)據(jù)庫(kù)的啟動(dòng)
從表象來(lái)看,數(shù)據(jù)庫(kù)的啟動(dòng)極其簡(jiǎn)單,只需要以SYSDBA/SYSOPER身份登陸,敲一條startup命令既可啟動(dòng)數(shù)據(jù)庫(kù)。然而在這條命令之后,Oracle需要執(zhí)行一系列復(fù)雜的操作,深入理解這些操作不僅有助于了解Oracle數(shù)據(jù)庫(kù)的運(yùn)行機(jī)制,還可以在故障發(fā)生時(shí)幫助大家快速的定位問(wèn)題的根源所在,所以接下來(lái)讓我們一起分析一下數(shù)據(jù)庫(kù)的啟動(dòng)過(guò)程。
Oracle數(shù)據(jù)庫(kù)的啟動(dòng)主要包含三個(gè)步驟:
1.1.1 啟動(dòng)數(shù)據(jù)庫(kù)到Nomount狀態(tài)
在啟動(dòng)的第一步驟,Oracle首先尋找參數(shù)文件(pfile/spfile),然后根據(jù)參數(shù)文件中的設(shè)置(如內(nèi)存分配等設(shè)置),創(chuàng)建實(shí)例(INSTANCE),分配內(nèi)存,啟動(dòng)后臺(tái)進(jìn)程。Nomount的過(guò)程也就是啟動(dòng)數(shù)據(jù)庫(kù)實(shí)例的過(guò)程。這個(gè)過(guò)程在后臺(tái)是啟動(dòng)Oracle可執(zhí)行程序的過(guò)程,Windows上是oracle.exe文件的初始化,在Unix/Linux上是oracle可執(zhí)行文件的初始化。
Windows上Oracle11g的執(zhí)行文件大小約為86M,而Linux下Oracle11g的執(zhí)行文件達(dá)到145M左右:
D:\oracle\product\11.1.0\BIN>dir oracle.exe
2007-10-03 17:42 89,702,400 oracle.exe
[oracle@localhost bin]$ ls -al $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle dba 151901909 Jul 4 15:13 /opt/oracle/product/11.1.0/bin/oracle
[oracle@localhost bin]$ file $ORACLE_HOME/bin/oracle
/opt/oracle/product/11.1.0/bin/oracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.2.5, dynamically linked (uses shared libs), not stripped
了解Oracle可執(zhí)行文件還有另外一個(gè)用途,在Unix/Linux上通過(guò)strings命令可以將oracle可執(zhí)行文件中的字符文本轉(zhuǎn)儲(chǔ)出來(lái),在轉(zhuǎn)儲(chǔ)的文本中可以找到很多有意思的信息,比如一些Oracle未公開(kāi)的Hints信息、數(shù)據(jù)庫(kù)字典基表創(chuàng)建信息等,類似如下一條命令可以完成這樣的工作:
strings $ORACLE_HOME/bin/oracle > oracle.txt
在Nomount初始化的過(guò)程中,只要擁有了一個(gè)參數(shù)文件,就可以憑之啟動(dòng)實(shí)例(INSTANCE),這一步驟并不需要任何控制文件或數(shù)據(jù)文件等的參與。
1.1.1.1 實(shí)例以及進(jìn)程的創(chuàng)建
以下是正常情況下啟動(dòng)到nomount狀態(tài)的過(guò)程:
[oracle@eygle bdump]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 28 12:42:30 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.Total System Global Area 1073741824 bytes
Fixed Size 1223488 bytes
Variable Size 264242368 bytes
Database Buffers 801112064 bytes
Redo Buffers 7163904 bytes
注意這里,Oracle根據(jù)參數(shù)文件的內(nèi)容,創(chuàng)建了instance,分配了相應(yīng)的內(nèi)存區(qū)域,啟動(dòng)了相應(yīng)的后臺(tái)進(jìn)程。SGA的分配信息從以上輸出中可以看到。
觀察告警日志文件(alert_
Wed Jun 28 12:42:40 2006
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
processes = 150
__shared_pool_size = 255852544
__large_pool_size = 0
__java_pool_size = 4194304
__streams_pool_size = 0
sga_target = 1073741824
control_files = /opt/oracle/oradata/eygle/control01.ctl, /opt/oracle/oradata/eygle/control02.ctl, /opt/oracle/oradata/eygle/control03.ctl
db_block_size = 8192
__db_cache_size = 805306368
...............
background_dump_dest = /opt/oracle/admin/eygle/bdump
user_dump_dest = /opt/oracle/admin/eygle/udump
db_name = eygle
PMON started with pid=2, OS id=6290
PSP0 started with pid=3, OS id=6292
MMAN started with pid=4, OS id=6294
DBW0 started with pid=5, OS id=6296
LGWR started with pid=6, OS id=6298
CKPT started with pid=7, OS id=6300
SMON started with pid=8, OS id=6302
RECO started with pid=9, OS id=6304
CJQ0 started with pid=10, OS id=6306
MMON started with pid=11, OS id=6308
MMNL started with pid=12, OS id=6310
在這里提醒大家注意一下Oracle不同版本告警日志信息的變化,在Oracle9i早期版本中,后臺(tái)進(jìn)程啟動(dòng)的日志信息里并不包含OS ID,以下是Oracle 9.2.0.4的日志信息(在Oracle 9.2.0.8中已經(jīng)包含了OS ID信息):
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
Sat Jul 05 09:53:55 2008
PMON started with pid=2, OS id=13898
Sat Jul 05 09:53:55 2008
VKTM started with pid=3, OS id=13900 at elevated priority
VKTM running at (20)ms precision
Sat Jul 05 09:53:55 2008
DIAG started with pid=4, OS id=13904
Sat Jul 05 09:53:55 2008
DBRM started with pid=5, OS id=13906
Sat Jul 05 09:53:55 2008
PSP0 started with pid=6, OS id=13908
提示:從Oracle不同版本中的變化來(lái)體會(huì)Oracle的技術(shù)進(jìn)步、甚至借鑒這些變化是學(xué)習(xí)Oracle的方法之一。任何細(xì)微的變化都值得注意,認(rèn)真、細(xì)致、嚴(yán)謹(jǐn)是對(duì)DBA的基本素質(zhì)要求。
細(xì)心的讀者朋友或許可以注意到,在前面日志里的進(jìn)程啟動(dòng)信息里,并沒(méi)有pid=1的進(jìn)程,那么這個(gè)進(jìn)程是否存在呢?
1.1.1.2 V$PROCESS視圖
通過(guò)數(shù)據(jù)庫(kù)中的v$process視圖,可以找到對(duì)應(yīng)于操作系統(tǒng)的每個(gè)進(jìn)程信息:
SQL> select addr,pid,spid,username,program from v$process;
ADDR PID SPID USERNAME PROGRAM
-------- ---------- ---------- -------- ----------------------------------------
5FE162AC 1 PSEUDO
5FE16860 2 6290 oracle oracle@eygle (PMON)
5FE16E14 3 6292 oracle oracle@eygle (PSP0)
5FE173C8 4 6294 oracle oracle@eygle (MMAN)
5FE1797C 5 6296 oracle oracle@eygle (DBW0)
5FE17F30 6 6298 oracle oracle@eygle (LGWR)
5FE184E4 7 6300 oracle oracle@eygle (CKPT)
。。。。。。。。。。。。
注意以上輸出,pid=1的進(jìn)程是一個(gè)PSEUDO進(jìn)程,這個(gè)進(jìn)程被認(rèn)為是初始化數(shù)據(jù)庫(kù)的進(jìn)程,啟動(dòng)其他進(jìn)程之前即被占用,并在數(shù)據(jù)庫(kù)中一直存在。v$process的查詢輸出中,SPID列代表的就是操作系統(tǒng)上的進(jìn)程號(hào),通過(guò)SPID可以將進(jìn)程從操作系統(tǒng)到數(shù)據(jù)庫(kù)關(guān)聯(lián)起來(lái):
[oracle@eygle bdump]$ ps -ef|grep ora_
oracle 6290 1 0 12:42 ? 00:00:00 ora_pmon_eygle
oracle 6292 1 0 12:42 ? 00:00:00 ora_psp0_eygle
oracle 6294 1 0 12:42 ? 00:00:00 ora_mman_eygle
oracle 6296 1 0 12:42 ? 00:00:00 ora_dbw0_eygle
oracle 6298 1 0 12:42 ? 00:00:00 ora_lgwr_eygle
oracle 6300 1 0 12:42 ? 00:00:00 ora_ckpt_eygle
。。。。。。。。。。。。。
如果在操作系統(tǒng)上發(fā)現(xiàn)某個(gè)進(jìn)程表現(xiàn)異常(如占用很高的CPU資源),那么通過(guò)操作系統(tǒng)上的PID和V$PROCESS視圖中的SPID關(guān)聯(lián),就可以找到這個(gè)OS上的進(jìn)程在數(shù)據(jù)庫(kù)內(nèi)部的化身,從而可以進(jìn)行進(jìn)一步的跟蹤診斷。
V$PROCESS視圖包含當(dāng)前數(shù)據(jù)庫(kù)中活動(dòng)進(jìn)程的相關(guān)信息,這些進(jìn)程在操作系統(tǒng)上都存在與之對(duì)應(yīng)的OS進(jìn)程。其中LATCHWAIT列代表進(jìn)程當(dāng)前正在等待的LATCH信息,LATCHSPIN則記錄進(jìn)程正在通過(guò)SPIN進(jìn)行LATCH的競(jìng)爭(zhēng)。Latch通常被稱為閂,是數(shù)據(jù)庫(kù)內(nèi)部的串行鎖機(jī)制,主要用來(lái)控制內(nèi)存上的并發(fā),在多處理器系統(tǒng)上,Oracle進(jìn)程通過(guò)自旋(spin)來(lái)進(jìn)行Latch爭(zhēng)奪。
這個(gè)視圖結(jié)構(gòu)如下所示(Oracle10gR2信息):
SQL> desc v$process
Name Null? Type
----------------------------------------- -------- -------------
ADDR RAW(4)
PID NUMBER
SPID VARCHAR2(12)
USERNAME VARCHAR2(15)
SERIAL# NUMBER
TERMINAL VARCHAR2(30)
PROGRAM VARCHAR2(48)
TRACEID VARCHAR2(255)
BACKGROUND VARCHAR2(1)
LATCHWAIT VARCHAR2(8)
LATCHSPIN VARCHAR2(8)
PGA_USED_MEM NUMBER
PGA_ALLOC_MEM NUMBER
PGA_FREEABLE_MEM NUMBER
PGA_MAX_MEM NUMBER
進(jìn)程的地址(Address of process)進(jìn)一步的被縮寫(xiě)為PADDR,在V$SESSION視圖中記錄的PADDR就是V$PROCESS.ADDR的進(jìn)一步延伸,通過(guò)兩者關(guān)聯(lián),可以向數(shù)據(jù)庫(kù)進(jìn)一步深入。
如果向操作系統(tǒng)端延伸,則SPID代表的正是操作系統(tǒng)進(jìn)程標(biāo)識(shí)符(Operating system process identifier),通過(guò)SPID和OS中看到的進(jìn)程PID關(guān)聯(lián),就可以建立從操作系統(tǒng)到數(shù)據(jù)庫(kù)的關(guān)聯(lián)。
所以V$PROCESS被認(rèn)為是從操作系統(tǒng)到數(shù)據(jù)庫(kù)的入口。此外,和PGA相關(guān)的幾個(gè)字段則記錄了進(jìn)程的PGA使用情況。
1.1.1.3 參數(shù)文件的選擇
接下來(lái)關(guān)注一下啟動(dòng)過(guò)程中Oracle選擇參數(shù)文件的順序。
從Oracle9i開(kāi)始,spfile被引入Oracle數(shù)據(jù)庫(kù),Oracle首選spfile<ORACLE_SID>.ora文件作為啟動(dòng)參數(shù)文件;如果該文件不存在,Oracle選擇spfile.ora文件;如果前兩者都不存在,Oracle將會(huì)選擇init<ORACLE_SID>.ora文件;如果以上三個(gè)文件都不存在,Oracle將無(wú)法創(chuàng)建和啟動(dòng)instance。Oracle在啟動(dòng)過(guò)程中,會(huì)在特定的路徑中尋找參數(shù)文件,在Unix/Linux下的路徑為$ORACLE_HOME/dbs目錄,在WINDOWS上的路徑為$ORACLE_HOME\database目錄。
可以在SQL*PLUS中通過(guò)show parameter spfile命令來(lái)檢查數(shù)據(jù)庫(kù)是否使用了spfile文件,如果value不為Null,則數(shù)據(jù)庫(kù)使用了spfile文件:
SQL> show parameter spfile
NAME TYPE VALUE
------- ------- -----------------------------------------------
spfile string ?/dbs/spfile@.ora
[oracle@jumper dbs]$ mv spfileconner.ora spfileconner.ora.bak
SQL> startup nomount
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string ?/dbs/spfile.ora
[oracle@jumper dbs]$ mv spfile.ora spfile.ora.bak
SQL> startup nomount
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
[oracle@jumper dbs]$ mv initconner.ora initconner.ora.bak
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/opt/oracle/product/9.2.0/dbs/initconner.ora'
1.1.1.4 實(shí)例啟動(dòng)最小參數(shù)需求
在參數(shù)文件中,通常需要最少的參數(shù)是db_name ,設(shè)置了這個(gè)參數(shù)之后,數(shù)據(jù)庫(kù)實(shí)例就可以啟動(dòng),來(lái)看一個(gè)簡(jiǎn)單的測(cè)試。
可以隨意命名一個(gè)ORACLE_SID(測(cè)試來(lái)自于Linux下,適用于Linux/Unix,對(duì)于Windows平臺(tái),需要通過(guò)oradim.exe工具創(chuàng)建服務(wù)),然后嘗試啟動(dòng)到nomount狀態(tài):
[oracle@jumper dbs]$ export ORACLE_SID=julia
[oracle@jumper dbs]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Mon May 8 11:08:36 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.SQL> startup nomount;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/opt/oracle/product/9.2.0/dbs/initjulia.ora'
SQL> ! echo "db_name=julia" > /opt/oracle/product/9.2.0/dbs/initjulia.ora
SQL> startup nomount;
ORACLE instance started.Total System Global Area 97588504 bytes
Fixed Size 451864 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
SQL> show parameter background_dump
NAME TYPE VALUE
------------------------------------ ---------------------- ----------------------
background_dump_dest string ?/rdbms/log
SQL> show parameter dump_dest
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------
background_dump_dest string ?/rdbms/log
core_dump_dest string ?/dbs
user_dump_dest string ?/rdbms/log
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ---------------------- -----------------------
control_files string ?/dbs/cntrl@.dbf
[oracle@jumper dbs]$ cat $ORACLE_HOME/rdbms/log/alert_julia.log
Mon May 8 11:09:04 2006
Starting ORACLE instance (normal)
Mon May 8 11:09:04 2006
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:
db_name = julia
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
1.1.1.5 ORACLE_SID的含義
回顧一下前面的內(nèi)容可以注意到,SID和ORACLE_SID已經(jīng)多次出現(xiàn),那么SID是什么?在數(shù)據(jù)庫(kù)啟動(dòng)過(guò)程中又起到什么作用呢?
SID是System IDentifier的縮寫(xiě),而ORACLE_SID就是Oracle System IDentifier 的縮寫(xiě),在Oracle系統(tǒng)中,ORACLE_SID以環(huán)境變量的形式出現(xiàn),當(dāng)Oracle實(shí)例啟動(dòng)時(shí),在操作系統(tǒng)上fork的進(jìn)程就依據(jù)這個(gè)ORACLE_SID來(lái)創(chuàng)建,這就是SID的作用。
Oracle的實(shí)例(instance)是由一塊共享內(nèi)存區(qū)域(SGA)和一組后臺(tái)進(jìn)程(background processes)共同組成,而后臺(tái)進(jìn)程正是數(shù)據(jù)庫(kù)和操作系統(tǒng)進(jìn)行交互的通道,這些進(jìn)程的名稱就是通過(guò)ORACLE_SID決定的。
通過(guò)前面的討論可以知道,實(shí)例的啟動(dòng)需要一個(gè)參數(shù)文件,參數(shù)文件的名稱就是由ORACLE_SID決定的,對(duì)于init文件,缺省的文件名稱是init<ORACLE_SID>.ora,對(duì)于spfile文件,缺省的文件名為spfile<ORACLE_SID>.ora,Oracle依據(jù)ORACLE_SID來(lái)決定和尋找參數(shù)文件啟動(dòng)實(shí)例。
在同一個(gè)$ORACLE_HOME下,通過(guò)參數(shù)文件,Oracle能夠根據(jù)ORACLE_SID將實(shí)例區(qū)分開(kāi)來(lái);但是注意如果在不同的$ORACLE_HOME下,即使在同一臺(tái)主機(jī)上,Oracle也是能夠創(chuàng)建相同ORACLE_SID的實(shí)例的。
以下一個(gè)測(cè)試,首先啟動(dòng)一個(gè)Oracle8i下ORACLE_SID為eygle的實(shí)例:
$ export ORACLE_SID=eygle
$ sqlplus "/ as sysdba"
SQL*Plus: Release 8.1.7.0.0 - Production on Fri Feb 16 10:23:58 2007
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to an idle instance.SQL> startup nomount;
ORACLE instance started.
SQL> ! ps -ef|grep ora_smon_eygle
oracle8 11123 11076 0 10:24:15 pts/1 0:00 grep ora_smon_eygle
oracle8 11092 1 0 10:24:02 ? 0:00 ora_smon_eygle
$ export ORACLE_SID=eygle
$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Feb 16 10:24:43 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.SQL> startup nomount;
ORACLE instance started.
SQL> ! ps -ef|grep ora_smon_eygle
oracle9 11214 11172 0 10:24:58 pts/1 0:00 grep ora_smon_eygle
oracle8 11092 1 0 10:24:02 ? 0:00 ora_smon_eygle
oracle9 11188 1 0 10:24:48 ? 0:00 ora_smon_eygle
$ ipcs -i
IPC status fromas of Fri Feb 16 10:30:02 CST 2007
T ID KEY MODE OWNER GROUP
Message Queues:
q 0 0x2e781d5 --rw-r--r-- root root
T ID KEY MODE OWNER GROUP ISMATTCH
Shared Memory:
m 4096 0xabdc9b64 --rw-r----- oracle8 dba 12
m 1025 0x79552064 --rw-r----- oracle9 dba 11
Semaphores:
s 1245184 0x79978bac --ra-r----- oracle8 dba
s 458753 0xa0e9f594 --ra-r----- oracle9 dba
$ sysresv -l eygle julia
IPC Resources for ORACLE_SID "eygle" :
Shared Memory:
ID KEY
2560 0x79552064
Semaphores:
ID KEY
720896 0xa0e9f594
Oracle Instance alive for sid "eygle"
IPC Resources for ORACLE_SID "julia" :
Shared Memory:
ID KEY
514 0xab281214
Semaphores:
ID KEY
196610 0xa7645a54
Oracle Instance alive for sid "julia"
1.1.1.6 INSTANCE_NAME的含義
在數(shù)據(jù)庫(kù)內(nèi)部和ORACLE_SID相關(guān)聯(lián)的概念就是INSTANCE_NAME。
Oracle數(shù)據(jù)庫(kù)內(nèi)部存在一個(gè)初始化參數(shù)INSTANCE_NAME,用于標(biāo)示數(shù)據(jù)庫(kù)實(shí)例的名稱,其缺省值通常就是ORACLE_SID;但是初始化參數(shù)INSTANCE_NAME和ORACLE_SID可以不同,不同實(shí)例可以擁有相同的INSTANCE_NAME。
在同一個(gè)ORACLE_HOME下,只要ORACLE_SID不同,數(shù)據(jù)庫(kù)并不校驗(yàn)INSTANCE_NAME參數(shù);通過(guò)簡(jiǎn)單的參數(shù)文件復(fù)制,我們就可以在同一臺(tái)服務(wù)器上創(chuàng)建多個(gè)具有相同instance_name的實(shí)例(注意以下測(cè)試來(lái)自O(shè)racle9i數(shù)據(jù)庫(kù)):
bash-2.03$ cd $ORACLE_HOME/dbs
bash-2.03$ cp initeygle.ora initjulia.ora
bash-2.03$ export ORACLE_SID=julia
bash-2.03$ sqlplus "/ as sysdba"
SQL> startup nomount;
ORACLE instance started.Total System Global Area 303532408 bytes
Fixed Size 731512 bytes
Variable Size 184549376 bytes
Database Buffers 117440512 bytes
Redo Buffers 811008 bytes
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ---------------------- -----------------------
instance_name string eygle
SQL> select instance_name from v$instance;
INSTANCE_NAME
--------------------------------
julia
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------
instance_name string eygle
所以存在這種歧義是因?yàn)?strong>在Oracle9i中,當(dāng)創(chuàng)建數(shù)據(jù)庫(kù)進(jìn)行相關(guān)配置時(shí),數(shù)據(jù)庫(kù)將INSTANCE_NAME參數(shù)寫(xiě)入了參數(shù)文件,這就導(dǎo)致了當(dāng)我們修改參數(shù)文件名稱變更ORACLE_SID時(shí)可能并不修改INSTANCE_NAME參數(shù)的情況;值得注意的是,從Oracle10g開(kāi)始,參數(shù)文件中缺省不再記錄INSTANCE_NAME,此時(shí)INSTANCE_NAME可以動(dòng)態(tài)從系統(tǒng)獲得,從而消除了以前可能常見(jiàn)的歧義:
D:\oracle\product>grep instance_name 9.2.0\database\SPFILEEEYGLE.ORA
*.instance_name='eeygle'
D:\oracle\product>grep instance_name 10.2.0\database\SPFILEEYGLE.ORA
D:\oracle\product>grep instance_name 11.1.0\database\SPFILEEYGLEE.ORA
Services Summary...
Service "julia" has 1 instance(s).
Instance "eygle", status READY, has 1 handler(s) for this service...
SQL> COLUMN STARTED_AT format a25
SQL> COLUMN UPTIME format a50
SQL> SELECT TO_CHAR (startup_time, 'DD-MON-YYYY HH24:MI:SS') started_at,
2 TRUNC (SYSDATE - (startup_time))
3 || ' day(s), ' || TRUNC ( 24 * ((SYSDATE - startup_time) -
4 TRUNC (SYSDATE - startup_time)))
5 || ' hour(s), '|| MOD (TRUNC ( 1440 * ( (SYSDATE - startup_time) -
6 TRUNC (SYSDATE - startup_time))),60)
7 || ' minute(s), '|| MOD (TRUNC ( 86400 * ( (SYSDATE - startup_time) -
8 TRUNC (SYSDATE - startup_time))),60)
9 || ' seconds' uptime
10 FROM v$instance;
STARTED_AT UPTIME
------------------------- --------------------------------------------------
05-JUL-2005 10:36:58 803 day(s), 2 hour(s), 27 minute(s), 55 seconds
1.1.1.7 DB_NAME與INSTANCE_NAME
相較INSTANCE_NAME參數(shù)來(lái)說(shuō),對(duì)于Oracle數(shù)據(jù)庫(kù)更為重要的一個(gè)參數(shù)是DB_NAME。DB_NAME代表了實(shí)例即將掛接的數(shù)據(jù)庫(kù)名稱,關(guān)系到具體的物理文件。通常缺省的數(shù)據(jù)庫(kù)instance_name和db_name可以設(shè)置相同(在RAC環(huán)境下,由于多個(gè)實(shí)例對(duì)應(yīng)一個(gè)數(shù)據(jù)庫(kù),所以instance_name和db_name不同)。
在創(chuàng)建數(shù)據(jù)庫(kù)的過(guò)程中,下圖是用于定義數(shù)據(jù)庫(kù)名稱(db_name)和影響INSTANCE_NAME的SID:
Oracle文檔中對(duì)于db_name的定義如下:
DB_NAME用來(lái)定義數(shù)據(jù)庫(kù)名稱,必須是一個(gè)不超過(guò)8個(gè)字符的文本串,在數(shù)據(jù)庫(kù)創(chuàng)建過(guò)程中,db_name被記錄在數(shù)據(jù)文件,日志文件和控制文件中。如果數(shù)據(jù)庫(kù)實(shí)例啟動(dòng)過(guò)程中參數(shù)文件中的db_name和控制文件中的數(shù)據(jù)庫(kù)名稱不一致,則數(shù)據(jù)庫(kù)不能啟動(dòng)。
此外常見(jiàn)的幾個(gè)結(jié)論有:
Services Summary...
Service "julia" has 1 instance(s).
Instance "eygle", status READY, has 1 handler(s) for this service...
[oracle@jumper oracle]$ cd $ORACLE_HOME/dbs
[oracle@jumper dbs]$ grep name initeygle.ora
*.db_name='eygle'
*.instance_name='eygle'
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string eygle
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string eygle
[oracle@jumper oracle]$ cd $ORACLE_HOME/dbs
[oracle@jumper dbs]$ cp initeygle.ora initjulia.ora
[oracle@jumper dbs]$ ll init*
-rw-r--r-- 1 oracle dba 982 Jul 25 14:03 initeygle.ora
-rw-r--r-- 1 oracle dba 982 Jul 25 14:04 initjulia.ora
[oracle@jumper dbs]$ grep name initjulia.ora
*.db_name='eygle'
*.instance_name='julia'
[oracle@jumper dbs]$ export ORACLE_SID=julia
[oracle@jumper dbs]$ sqlplus "/ as sysdba"
SQL> startup mount;
ORACLE instance started.Total System Global Area 139531744 bytes
Fixed Size 452064 bytes
Variable Size 121634816 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode
[oracle@jumper dbs]$ ps -ef|grep dbw
oracle 27323 1 0 Jul14 ? 00:00:00 ora_dbw0_eygle
oracle 15447 1 0 14:04 ? 00:00:00 ora_dbw0_julia
oracle 25030 25000 0 18:38 pts/2 00:00:00 grep dbw
[oracle@jumper dbs]$ export ORACLE_SID=eygle
[oracle@jumper dbs]$ sqlplus "/ as sysdba"
SQL> shutdown immediate;
[oracle@jumper dbs]$ export ORACLE_SID=julia
[oracle@jumper dbs]$ sqlplus "/ as sysdba"
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01990: error opening password file '/opt/oracle/product/9.2.0/dbs/orapw'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> alter database open;
Database altered.
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------------
/opt/oracle/oradata/eygle/system01.dbf
/opt/oracle/oradata/eygle/undotbs01.dbf
/opt/oracle/oradata/eygle/users01.dbf
/opt/oracle/oradata/eygle/eygle01.dbf
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string julia
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string eygle
[oracle@jumper dbs]$ grep name initjulia.ora
*.db_name='julia'
*.instance_name='julia'
SQL> startup nomount;
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01103: database name 'EYGLE' in controlfile is not 'JULIA'
1.1.1.8 RMAN的缺省實(shí)例
在使用RMAN(Recovery Manager)時(shí)存在更為特殊的情況,Oracle允許在不存在參數(shù)文件的情況下啟動(dòng)一個(gè)實(shí)例,數(shù)據(jù)庫(kù)的db_name會(huì)被缺省的命名為DUMMY,這是最為極端的情況,在某些恢復(fù)過(guò)程中,這個(gè)功能可以幫助我們減少很多麻煩:
[oracle@jumper dbs]$ rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/opt/oracle/product/9.2.0/dbs/initconner.ora'
trying to start the Oracle instance without parameter files ...
Oracle instance started
Total System Global Area 97588504 bytes
Fixed Size 451864 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytesRMAN> host ;
[oracle@jumper dbs]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Mar 12 14:17:07 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string DUMMY
Starting up ORACLE RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:
remote_login_passwordfile= EXCLUSIVE
db_name = DUMMY
PMON started with pid=2
DBW0 started with pid=3
.....
1.1.1.9 Nomount案例兩則
在創(chuàng)建數(shù)據(jù)庫(kù)時(shí),如果在這一步驟就出現(xiàn)問(wèn)題,那么通??赡苁窍到y(tǒng)配置(如內(nèi)核參數(shù)等)存在問(wèn)題,你需要檢查是否分配了足夠的系統(tǒng)資源等。
以下是一個(gè)啟動(dòng)到nomount狀態(tài)可能會(huì)遇到的常見(jiàn)錯(cuò)誤:
$ export ORACLE_SID=julia
$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Feb 28 09:55:24 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to an idle instance.
SQL> startup nomount;
ORA-00600: internal error code, arguments: [OSDEP_INTERNAL], [], [], [], [], [], [], []
ORA-27302: failure occurred at: skgpwreset1
ORA-27303: additional information: invalid shared ctx
ORA-27146: post/wait initialization failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpsemsper
在Nomount狀態(tài)就出現(xiàn)問(wèn)題,通常是系統(tǒng)問(wèn)題,OS類錯(cuò)誤一般說(shuō)明是系統(tǒng)資源不足,這在Linux/Unix下和信號(hào)量等參數(shù)設(shè)置有關(guān),多出現(xiàn)在同一主機(jī)運(yùn)行多個(gè)數(shù)據(jù)庫(kù)實(shí)例的情況(在Solaris上需要修改/etc/system文件中的內(nèi)核參數(shù),重起系統(tǒng)后修改生效)。在這個(gè)錯(cuò)誤提示中,600錯(cuò)誤的第一個(gè)參數(shù)是OSDEP_INTERNAL,我們大致可以猜測(cè)到這是一個(gè)OS Dependent/Internal Error。很多Oracle的提示可以根據(jù)縮寫(xiě)猜到大致的含義,但是如果是錯(cuò)誤號(hào)那就要依賴Oracle的文檔來(lái)尋找答案。
在另外一個(gè)客戶現(xiàn)場(chǎng),遭遇過(guò)另外一個(gè)案例,當(dāng)時(shí)客戶的服務(wù)器異常斷電,當(dāng)系統(tǒng)重新啟動(dòng)后,數(shù)據(jù)庫(kù)無(wú)法啟動(dòng)(提示:重啟主機(jī)對(duì)于DBA來(lái)說(shuō)應(yīng)當(dāng)極其慎重,很多隱藏的故障可能在重啟時(shí)爆發(fā)出來(lái),在沒(méi)有做好充分 之前,不要貿(mào)然從事)。
數(shù)據(jù)庫(kù)的癥狀是,啟動(dòng)主機(jī)到Nomount狀態(tài)后,后臺(tái)進(jìn)程會(huì)立即將實(shí)例中止,也就是說(shuō)數(shù)據(jù)庫(kù)實(shí)例都無(wú)法穩(wěn)定創(chuàng)建,告警日志文件信息如下:
Mon Dec 3 14:24:30 2007
Errors in file /oraclehx/app/admin/sxlss/bdump/sxlss_pmon_360454.trc:
ORA-07445: exception encountered: core dump [] [] [] [] [] []
PSP0 started with pid=3, OS id=422106
MMAN started with pid=4, OS id=303332
DBW0 started with pid=5, OS id=299324
。。。。。。。。。。。。。。。。。。
SMON started with pid=11, OS id=278882
RECO started with pid=12, OS id=319898
CJQ0 started with pid=13, OS id=295404
MMON started with pid=14, OS id=303428
MMNL started with pid=15, OS id=438776
Mon Dec 3 14:24:33 2007
PSP0: terminating instance due to error 472
Instance terminated by PSP0, pid = 422106
instfix -i|grep ML
All filesets for 5.3.0.0_AIX_ML were found.
All filesets for 5300-01_AIX_ML were found.
All filesets for 5300-02_AIX_ML were found.
All filesets for 5300-03_AIX_ML were found.
All filesets for 5300-04_AIX_ML were found.
All filesets for 5300-05_AIX_ML were found.
Not all filesets for 5300-06_AIX_ML were found.
聯(lián)系客服