英超

Oracle的db_name和instance_name

2019-09-12 10:22:11来源:励志吧0次阅读

对于Oracle数据库来说,db_name和instance_name可以不同。

我们来看一下Oracle文档中对于db_name的定义:

DB_NAME必须是一个不超过8个字符的文本串.在数据库创建过程中,db_name被记录在数据文件,日志文件和控制文件中。如果数据库实例启动过程中参数文件中的db_name和控制文件中的数据库名称不一致,则数据库不能启动。

一个实例可以mount并打开任何数据库,但是同一时间一个实例只能打开一个数据库。

一个数据库可以被一个或多个实例所mount并打开(在OPS/RAC环境下,一个数据库可以被多个实例所打开).

我们看一下我的数据库:

[oracle@jumper dbs]$ grep name initeygle.ora

*.db_name='eygle'

*.instance_name='eygle'当前参数设置下的数据库:

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 db_name

NAME TYPE VALUE

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

db_name string eygle

SQL> show parameter instance_name

NAME 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

我们创建一个新的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

修改后的参数设置:

[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 2006

Copyright (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 mode

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

注意,此时试图加载数据库时出现错误,因为当前数据库被另外一个实例(instance)加载。在非并行模式(Ops/RAC)下,一个数据库同时只能被一个实例加载。

此时已经启动了两个数据库实例,从后台进程可以看出:

[oracle@jumper dbs]$ ps -ef|grep 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我们关闭eygle这个数据库实例:

[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 2006

Copyright (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 - Production

SQL> 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的数据库了:

[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 2006

Copyright (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 - Production

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> ! ps -ef|grep 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 -ef|grep ora

oracle 15516 15515 0 14:05 pts/2 00:00:00 ps -ef

SQL> show parameter instance_name

NAME TYPE VALUE

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

instance_name string julia

SQL> show parameter db_name

NAME TYPE VALUE

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

db_name string eygle

我们再看看如果参数文件中的db_name和控制文件中的db_name不一致会出现什么错误.

修改参数db_name:

[oracle@jumper dbs]$ grep name initjulia.ora

*.db_name='julia'

*.instance_name='julia'在启动过程中,我们看到,在mount阶段,数据库会对参数文件和控制文件进行比较,如果两者记录的db_name不一致,则数据库无法启动:

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'

另外的一个区别是,db_name通常限制在8个字符之内;instance_name最多应该支持21个字符。通常我们都是设置db_name和 instance_name一致。需要注意的是如果db_name设置过长,则会被Oracle截断为8个字符,而instance_name仍然会保留在21个字符之内,如果你的环境变量设置的instance_name=db_name,则启动时就会出现问题,你需要重建pfile/spfile并且修改环境变量才能启动实例,加载数据库。

这个问题可以参考Itpub曾经出现的案例:

http://www.itpub.net/showthread.php?threadid=604507

本文通过实例来介绍instance_name和db_name的区别,希望大家能对这两个参数以及instance和database有进一步的认识。

查看本文来源

小儿积食发烧怎么办
冠心病怎样治疗
小便黄赤什么原因引起的
宝宝尿黄怎么回事
分享到: