网站制作哪家好薇,织梦网站添加视频教程,视频怎么制作,盘锦网站制作最近整理了一篇文章#xff1a;oracle listener 有网友对数据库是否显式设置了instance_name和service_names提出疑问。 由此引发出db_name,instance_name,oracle_sid等等这些常见的参数都代表什么意思#xff0c;怎么取值的#xff0c;有什么区别#xff1f; SQL sele… 最近整理了一篇文章oracle listener 有网友对数据库是否显式设置了instance_name和service_names提出疑问。 由此引发出db_name,instance_name,oracle_sid等等这些常见的参数都代表什么意思怎么取值的有什么区别 SQL select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string ORCL db_unique_name string ORCL global_names boolean FALSE instance_name string ORCL lock_name_space string log_file_name_convert string processor_group_name string service_names string ORCL 看到这么多参数但是服务器参数(spfile)中仅仅设置了db_name那么其它的name比如db_unique_name,instance_name,service_names的值是怎么出来的 官方是这样说的When an Oracle instance starts, it reads initialization parameters from an initialization parameter file. This file must at a minimum specify the DB_NAME parameter. All otherparameters have default values. ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL create pfile/u01/pfile.ora from spfile; File created. [oracleresoft u01]$ vi pfile.ora ORCL.__db_cache_size243269632 ORCL.__java_pool_size4194304 ORCL.__large_pool_size4194304 ORCL.__oracle_base/u01/app/oracle#ORACLE_BASE set from environment ORCL.__pga_aggregate_target293601280 ORCL.__sga_target553648128 ORCL.__shared_io_pool_size0 ORCL.__shared_pool_size289406976 ORCL.__streams_pool_size4194304 *.audit_file_dest/u01/app/oracle/admin/ORCL/adump *.audit_trailDB,EXTENDED *.compatible11.2.0.0.0 *.control_files/u01/app/oracle/oradata/ORCL/control01.ctl,/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl *.db_block_size8192 *.db_domain *.db_nameORCL *.db_recovery_file_dest/u01/app/oracle/fast_recovery_area *.db_recovery_file_dest_size4322230272 *.diagnostic_dest/u01/app/oracle *.dispatchers(PROTOCOLTCP) (SERVICEORCLXDB) *.memory_target847249408 *.nls_languageAMERICAN *.nls_territoryAMERICA *.open_cursors300 *.processes150 *.remote_login_passwordfileEXCLUSIVE *.undo_tablespaceUNDOTBS1 各种name或者id的解释--括弧内中文名称是我们一致认为比较合理的翻译但是如果想准确的表达请直接说英文名称不要去翻译以免造成误解。 db_name(数据库名) Property Description Parameter type String Syntax DB_NAME database_name Default value There is no default value. Modifiable No Basic Yes Oracle RAC You must set this parameter for every instance. Multiple instances must have the same value, or the same value must be specified in the STARTUP OPEN SQL*Plus command or the ALTER DATABASE MOUNT SQL statement. DB_NAME must be set to a text string of no more than eight characters. During database creation, the name provided for DB_NAME is recorded in the datafiles, redo log files, and control file of the database. If during database instance startup the value of the DB_NAME parameter (in the parameter file) and the database name in the control file are different, the database does not start. db_name必须是一个不超过8个字符的文本串。在数据库创建过程中db_name被记录在数据文件日志文件和控制文件中。如果数据库实例启动过程中参数文件中的db_name和控制文件中的db_name名称不一样则数据库不能启动。db_name是最具有稳定意义的参数官网说不能修改修改后会导致数据库不能启动。 DB_NAME 也就是数据库的名字标示。这里数据库里可能有多个实例比如RAC里的多节点这多个节点是不同的实例但是却有相同的名字他们的 DB_NAME是相同的但是Instance_name是不同的。DB_NAME会保持在数据文件头里所以更改DB_NAME不能仅仅修改parameter还需要用nid 来进行更改并且更改后还需要手工做些工作是其生效。 db_unique_name(数据库唯一名) Property Description Parameter type String Syntax DB_UNIQUE_NAME database_unique_name Default value Database instances: the value of DB_NAME Automatic Storage Management instances: ASM Modifiable No Basic Yes Oracle RAC Multiple instances must have the same value. DB_UNIQUE_NAME specifies a globally unique name for the database. Databases with the same DB_NAME within the same DB_DOMAIN (for example, copies of a database created for reporting or a physical standby) must have a unique DB_UNIQUE_NAME. Every databases DB_UNIQUE_NAME must be unique within the enterprise. The value of DB_UNIQUE_NAME can be up to 30 characters and is case insensitive. The following characters are valid in a database name: alphanumeric characters, underscore (_), number sign (#), and dollar sign ($) --E文不好翻译还不如看原版。 DB_UNIQUE_NAME这在另一个HA的应用Dataguard会经常提及的和DB_NAME不一样的作用在DG里要求物理DG主从库都有一样的DB_NAME虽然他们和RAC不一样并不是同一个库。这里是数据库的唯一名字。但是他们的DB_UNIQUE_NAME是不一样的用以进行不同的标示。DB_UNQUIE_NAME的会影响到Service_names也会影响到动态监听的时候的service_name 比如如下片段 Service zxdbdg1 has 1 instance(s). Instance zxdb, status BLOCKED, has 1 handler(s) for this service... Service zxdbdg1_XPT has 1 instance(s). Instance zxdb, status BLOCKED, has 1 handler(s) for this service... The command completed successfully 这里的zxdbdg1就是dg中的从库。从库的db_name和主库保持一样为zxdb,DB_UNIQUE_NAME不同。在动态监听后注册为 zxdbdg1的service启动的instance_name还是zxdb Instance_name简单讲就是ORACLE_SID,oracle里通过ORSCLE_SID来管理不同的数据库实例。 另 上面的动态监听信息里出现了Instance zxdb, status BLOCKED 这里是因为我的从库数据库不是open状态。 instance_name (数据库实例名) Property Description Parameter type String Syntax INSTANCE_NAME instance_id Default value The instances SID Note: The SID identifies the instances shared memory on a host, but may not uniquely distinguish this instance from other instances. Modifiable No Range of values Any alphanumeric characters and the underscore (_) character Basic No In a Real Application Clusters environment, multiple instances can be associated with a single database service. Clients can override Oracles connection load balancing by In a single-instance database system, the instance name is usually the same as the database namespecifying a particular instance by which to connect to the database. INSTANCE_NAME specifies the unique name of this instance. 用于和操作系统之间的联系用于对外部连接时使用。在操作系统中要取得与数据库之间的交互必须使用数据库实例名。该值允许修改但一般设置与数据库名相同即可 INSTANCE_NAME 的默认值就是oracle SID。 一般跟数据库库名称相同也可以不相同。 服务器参数spfile中没有设置instance_name,所以取默认值oracle_sid,我这里sid 为ORCL 所以instance_nameORCL oracle_sid(数据库实例名) SID的全称为site identifierOracle_SID则为Oracle site identifier. Unix/Linux查看oralce_sid: export $oracle_sid 或者直接查看环境变量文件 more .bash_profile 下面引用Tom(Thomas Kyte)的一段话来解释Oracle_SID If you’re unfamiliar with the term SID or ORACLE_SID, a full definition is called for. The SID is a site identifier. It and ORACLE_HOME (where the Oracle software is installed) are hashed together in UNIX to create a unique key name for attaching an SGA. If your ORACLE_SID or ORACLE_HOME is not set correctly, you’ll get the ORACLE NOT AVAILABLE error, since you can’t attach to a shared memory segment that is identified by this unique key. On Windows, shared memory isn’t used in the same fashion as UNIX, but the SID is still important. You can have more than one database on the same ORACLE_HOME, so you need a way to uniquely identify each one, along with their configuration files. 即在Unix/Linux系统中SID和ORACLE_HOME在一起哈希后得到一个唯一的值作为SGA的key。 当oracle实例启动时在操作系统上的fork进程则根据Oracle_SID来创建相关后台进程。 Oracle 11g 支持Oracle_SID的长度为12位db_name的长度为8位 作用 用于区别同一台主机上不同的Oracle实例 决定实例所启动后台进程的名称。(实例由SGA和后台进程组成) 决定了参数文件的名称。如spfileORACLE_SID.ora,initORACLE_SID.ora 决定后台进程产生的相关跟踪文件、日志文件等。 如alert_ORACLE_SID.log,ORACLE_SID_arc1_spid.trc,SID_ora_SPID.trc 同一主机上不同的$ORACLE_HOME可以创建相同的ORACLE_SID 由此可以得出 ORACLE_HOME相同时可以使用不同的ORACLE_SID ORACLE_HOME不同时可以使用相同的ORACLE_SID [oracleRESOFT~]$ export ORACLE_SIDorcl --设定ORACLE_SID为orcl SQL ho ps -ef | grep oracle --ORACLE_SID参与了后台进程命名 oracle 3272 1 0 09:46 ? 00:00:00 ora_pmon_orcl oracle 3274 1 0 09:46 ? 00:00:00 ora_psp0_orcl oracle 3276 1 1 09:46 ? 00:00:00 ora_mman_orcl oracle 3278 1 0 09:46 ? 00:00:00 ora_dbw0_orcl oracle 3280 1 0 09:46 ? 00:00:00 ora_lgwr_orcl oracle 3282 1 0 09:46 ? 00:00:00 ora_ckpt_orcl oracle 3284 1 0 09:46 ? 00:00:00 ora_smon_orcl ---------............部分结果省略.............. -------------- dbid(数据库id) An internal, uniquely generated number that differentiates databases. Oracle creates this number automatically when you create the database. DBID 可以看成是db_name在数据库内部的表示。 DBID是在创建数据库时用db_name 结合一种算法来创建的。 具体用什么算法不太清楚。它存在与数据文件和控制文件用于表示数据文件的归属。 所以这个DBID 是唯一的。 对于不同的数据库DBID 是不同的但是db_name 有可能相同。 用身份证打个比方 可以有同名的人但是它的省份证号码肯定是不同的。 查看DBID: SQL select dbid from v$database; DBID ---------- 1318255748 global_names (全局数据库名) Property Description Parameter type Boolean Default value false Modifiable ALTER SESSION, ALTER SYSTEM Range of values true | false Basic No GLOBAL_NAMES specifies whether a database link is required to have the same name as the database to which it connect. If the value of GLOBAL_NAMES is false, then no check is performed. If you use or plan to use distributed processing, then Oracle recommends that you set this parameter to true to ensure the use of consistent naming conventions for databases and links in a networked environment. Global_names 是一个布尔值global_names的作用是创建db link时是否强制使用远程数据库的global_name,如果global_namestrue,则db link name必须要求是remote database的global_name,否则创建之后db link 不能连同缺省值是false。多用于分布式系统。 global_name命名规则 db_name[db_domain] 也就是由db_name.db_domain构成。 查看Global_name SQL SELECT * FROM GLOBAL_NAME; GLOBAL_NAME -------------------------------------------------------------------------------- RACDB 我们可以修改GLOBAL_NAME. 如 ALTER DATABASE RENAME GLOBAL_NAME TO sales.us.example.com; db_domain(数据库域名) Property Description Parameter type String Syntax DB_DOMAIN domain_name Default value There is no default value. Modifiable No Range of values Any legal string of name components, separated by periods and up to 128 characters long (including the periods). This value cannot be NULL. Basic Yes Oracle RAC You must set this parameter for every instance, and multiple instances must have the same value. In a distributed database system, DB_DOMAIN specifies the logical location of the database within the network structure. You should set this parameter if this database is or ever will be part of a distributed system. The value consists of the extension components of a global database name, consisting of valid identifiers (any alphanumeric ASCII characters), separated by periods. Oracle recommends that you specify DB_DOMAIN as a unique string for all databases in a domain. This parameter allows one department to create a database without worrying that it might have the same name as a database created by another department. If one sales departments DB_DOMAIN is JAPAN.ACME.COM, then their SALES database (SALES.JAPAN.ACME.COM) is uniquely distinguished from another database with DB_NAME SALES but with DB_DOMAIN US.ACME.COM If you omit the domains from the name of a database link, Oracle expands the name by qualifying the database with the domain of your local database as it currently exists in the data dictionary, and then stores the link name in the data dictionary. The characters valid in a database domain name are: alphanumeric characters, underscore (_), and number sign (#) 通常用于分布式数据库系统中标识一个数据库的逻辑位置。为数据定义一个域该参数作为Global_names的一部分即在不同的域中可以使用相同的数据库名称该参数缺省情况下位空在RAC环境中需要为每一个实例指定该值且多实例具有相同的值 SQL show parameter db_domain NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_domain string service_names(数据库服务名) Property Description Parameter type String Syntax SERVICE_NAMES db_service_name [, db_service_name [ ... ] ] Default value DB_UNIQUE_NAME.DB_DOMAIN if defined Modifiable ALTER SYSTEM Range of values Any ASCII string or comma-separated list of string names Basic No Oracle RAC Do not set the SERVICE_NAMES parameter for Oracle RAC environments. Instead, define services using Oracle Enterprise Manager and manage those services using Server Control (SRVCTL) utility SERVICE_NAMES specifies one or more names by which clients can connect to the instance. The instance registers its service names with the listener. When a client requests a service, the listener determines which instances offer the requested service and routes the client to the appropriate instance. You can specify multiple service names in order to distinguish among different uses of the same database. For example: SERVICE_NAMES sales.acme.com, widgetsales.acme.com You can also use service names to identify a single service that is available from two different databases through the use of replication. If you do not qualify the names in this parameter with a domain, Oracle qualifies them with the value of the DB_DOMAIN parameter. If DB_DOMAIN is not specified, then no domain will be applied to the non-qualified SERVICE_NAMES values. service_names 在tnsnames.ora中用的最多。 service_namesDB_UNIQUE_NAME.DB_DOMAIN 我的环境中db_unique_name,db_domanin都没有设置而db_unique_name在单实例下默认值为db_name ORCL 所以service_namesORCL 这也就解释了刚开始提出的问题没有在spfile中设置instance_name,service_names 但是动态注册时怎么发生的 总结 DB 相关的 DBID, ORCLE_SID PFILE中的参数DB_NAMEDB_DOMAIN, INSTANCE_NAME DB_UNIQUE_NAME,SERVICE_NAMES, GLOBAL_NAMEGLOBAL_NAMES Listener.ora中参数 SID_NAMEGLOBAL_DBNAME Tnsnames.ora中参数 SERVICE_NAMESID 转载于:https://www.cnblogs.com/AlbertCQY/archive/2013/04/05/3000987.html
