Oracle的db_name和instance_name

2024-08-29 13:34:23

字體：大中小

供稿：網(wǎng)友

對于Oracle數(shù)據(jù)庫來說，db_name和instance_name可以不同。
我們來看一下Oracle文檔中對于db_name的定義:

DB_NAME必須是一個不超過8個字符的文本串.在數(shù)據(jù)庫創(chuàng)建過程中,db_name被記錄在數(shù)據(jù)文件，日志文件和控制文件中。假如數(shù)據(jù)庫實例啟動過程中參數(shù)文件中的db_name和控制文件中的數(shù)據(jù)庫名稱不一致，則數(shù)據(jù)庫不能啟動。

一個實例可以mount并打開任何數(shù)據(jù)庫，但是同一時間一個實例只能打開一個數(shù)據(jù)庫。
一個數(shù)據(jù)庫可以被一個或多個實例所mount并打開（在OPS/RAC環(huán)境下，一個數(shù)據(jù)庫可以被多個實例所打開）.我們看一下我的數(shù)據(jù)庫:[oracle@jumper dbs]$ grep name initeygle.ora
*.db_name='eygle'
*.instance_name='eygle'當(dāng)前參數(shù)設(shè)置下的數(shù)據(jù)庫: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.dbfSQL> show parameter db_nameNAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
db_name                              string      eygle
SQL> show parameter instance_nameNAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
instance_name                        string      eygle
SQL> create pfile from spfile;File created.SQL> exit
Disconnected from Oracle9i EnterPRise Edition Release 9.2.0.4.0 - ProdUCtion
With the Partitioning option
JServer Release 9.2.0.4.0 - Production我們創(chuàng)建一個新的pfile為julia這個新的實例使用:[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
-rw-r--r--    1 oracle   dba          8385 Mar 9 2002 init.ora
修改這個文件更改:
instance_name = julia修改后的參數(shù)設(shè)置:[oracle@jumper dbs]$ grep name initjulia.ora
*.db_name='eygle'
*.instance_name='julia'然后我們啟動實例名稱為julia的instance:[oracle@jumper dbs]$ eXPort ORACLE_SID=julia
[oracle@jumper dbs]$ sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jul 25 14:04:15 2006Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to an idle instance.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 modeSQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production     注重，此時試圖加載數(shù)據(jù)庫時出現(xiàn)錯誤，因為當(dāng)前數(shù)據(jù)庫被另外一個實例(instance)加載。在非并行模式(Ops/RAC)下，一個數(shù)據(jù)庫同時只能被一個實例加載。此時已經(jīng)啟動了兩個數(shù)據(jù)庫實例，從后臺進(jìn)程可以看出：[oracle@jumper dbs]$ ps -efgrep ora
oracle   27321     1 0 Jul14 ?        00:00:00 ora_pmon_eygle
oracle   27323     1 0 Jul14 ?        00:00:00 ora_dbw0_eygle
oracle   27325     1 0 Jul14 ?        00:00:00 ora_lgwr_eygle
oracle   27327     1 0 Jul14 ?        00:00:00 ora_ckpt_eygle
oracle   27329     1 0 Jul14 ?        00:00:32 ora_smon_eygle
oracle   27331     1 0 Jul14 ?        00:00:00 ora_reco_eygle
oracle   27333     1 0 Jul14 ?        00:00:00 ora_cjq0_eygle
root     15388   656 0 14:02 ?        00:00:00 sshd: oracle [priv]
oracle   15390 15388 0 14:02 ?        00:00:00 sshd: oracle@pts/2
oracle   15391 15390 0 14:02 pts/2    00:00:00 -bash
oracle   15445     1 0 14:04 ?        00:00:00 ora_pmon_julia
oracle   15447     1 0 14:04 ?        00:00:00 ora_dbw0_julia
oracle   15449     1 0 14:04 ?        00:00:00 ora_lgwr_julia
oracle   15451     1 0 14:04 ?        00:00:00 ora_ckpt_julia
oracle   15453     1 0 14:04 ?        00:00:00 ora_smon_julia
oracle   15455     1 0 14:04 ?        00:00:00 ora_reco_julia
oracle   15457     1 0 14:04 ?        00:00:00 ora_cjq0_julia
oracle   15459 15391 0 14:04 pts/2    00:00:00 ps -ef
oracle   15460 15391 0 14:04 pts/2    00:00:00 grep ora
我們關(guān)閉eygle這個數(shù)據(jù)庫實例:[oracle@jumper dbs]$ export ORACLE_SID=eygle
[oracle@jumper dbs]$ sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jul 25 14:04:39 2006Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - ProductionSQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production此時就可以通過實例julia加載并打開db_name=eygle的數(shù)據(jù)庫了: [oracle@jumper dbs]$ export ORACLE_SID=julia
[oracle@jumper dbs]$ sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jul 25 14:05:06 2006Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - ProductionSQL> 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.dbfSQL> ! ps -efgrep ora
root     15388   656 0 14:02 ?        00:00:00 sshd: oracle [priv]
oracle   15390 15388 0 14:02 ?        00:00:00 sshd: oracle@pts/2
oracle   15391 15390 0 14:02 pts/2    00:00:00 -bash
oracle   15445     1 0 14:04 ?        00:00:00 ora_pmon_julia
oracle   15447     1 0 14:04 ?        00:00:00 ora_dbw0_julia
oracle   15449     1 0 14:04 ?        00:00:00 ora_lgwr_julia
oracle   15451     1 0 14:04 ?        00:00:00 ora_ckpt_julia
oracle   15453     1 0 14:04 ?        00:00:00 ora_smon_julia
oracle   15455     1 0 14:04 ?        00:00:00 ora_reco_julia
oracle   15457     1 0 14:04 ?        00:00:00 ora_cjq0_julia
oracle   15513 15391 0 14:05 pts/2    00:00:00 sqlplus
oracle   15514 15513 3 14:05 ?        00:00:01 oraclejulia (DESCRipTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   15515 15513 0 14:05 pts/2    00:00:00 /bin/bash -c ps -efgrep ora
oracle   15516 15515 0 14:05 pts/2    00:00:00 ps -ef
SQL> show parameter instance_nameNAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      julia
SQL> show parameter db_nameNAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      eygle 我們再看看假如參數(shù)文件中的db_name和控制文件中的db_name不一致會出現(xiàn)什么錯誤.
修改參數(shù)db_name:[oracle@jumper dbs]$ grep name initjulia.ora
*.db_name='julia'
*.instance_name='julia'在啟動過程中，我們看到，在mount階段，數(shù)據(jù)庫會對參數(shù)文件和控制文件進(jìn)行比較，假如兩者記錄的db_name不一致，則數(shù)據(jù)庫無法啟動:SQL> startup nomount;
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
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01103: database name 'EYGLE' in controlfile is not 'JULIA'
另外的一個區(qū)別是，db_name通常限制在8個字符之內(nèi)；instance_name最多應(yīng)該支持21個字符。通常我們都是設(shè)置db_name和instance_name一致。需要注重的是假如db_name設(shè)置過長，則會被Oracle截斷為8個字符，而instance_name仍然會保留在21個字符之內(nèi)，假如你的環(huán)境變量設(shè)置的instance_name=db_name，則啟動時就會出現(xiàn)問題，你需要重建pfile/spfile并且修改環(huán)境變量才能啟動實例，加載數(shù)據(jù)庫。這個問題可以參考Itpub曾經(jīng)出現(xiàn)的案例:http://www.itpub.net/showthread.php?threadid=604507本文通過實例來介紹instance_name和db_name的區(qū)別，希望大家能對這兩個參數(shù)以及instance和database有進(jìn)一步的熟悉。

上一篇：oracle最重要的9個動態(tài)性能視圖

下一篇：Oracle時間信息特性