Shulou(Shulou.com)06/01 Report--

Oracle has a lot of parameters related to names, sometimes confusing, what does this parameter do, and what is the difference between them?

Let's first take a look at what parameters are related to the name

The parameter file contains db_name,instance_name,service_names,db_unique_name

oracle_sid in environment variables

sid_name,global_dbname in listener.ora (in case of static registration)

service_name,sid in tnsname.ora

What do they mean? Let's introduce them one by one:

db_name:

Unique identification of a database (Oracle database), sufficient for a single database but not for distribution

DB_domian is to solve this problem, so the identity of the database is determined by the two parameters db_name and db_domain. Similar to machine name management on the Internet, db_name.db_domain is used to represent a database, and the name of the database is called global_name. db_name can only consist of letters/numbers,'_','#','$' and has a maximum of 8 characters.

DB_UNIQUE_NAME：

Different from db_name, it is used to specify the global unique name of the database. For example, the physical backup database in DG has the same db_name and db_domain as the main database. DB_unique_name is used to distinguish between them. 30 characters can be set, case-insensitive, consisting of letters/numbers,'_','#','$'. DB_UNQUIE_NAME affects Service_names and also service_name when listening dynamically.

instance_name:

Instance unique identifier, such as RAC environment. In case of multiple instances, use instance_name to distinguish each instance. This is used to set the instance_name registered in dynamic monitoring. If SID connection is used in tnsnames.ora, it needs to correspond to it.

Before modifying the instance parameter, as shown in the following figure

After modifying the instance_name parameter:

A comparison of the two figures shows that instance_name plays the role of specifying the name when registering for listening.

In addition, some dynamic performance views that contain an instance_name or inst_name column are inferred from the environment variable ORACLE_SID rather than this parameter.

service_names:

Used to set the service name registered in the listening. If you use SERVICE_NAME to connect, you need to correspond to it.

Before modifying service_names, service_names are consistent with service in the monitor:

After modifying service_names, there are more service names set in the monitoring:

You can also connect to the database using the newly set service name:

oracle_sid:

Instance identifier, which exists in the form of environment variable and is used for corresponding shared memory segment.Oracle instance is composed of SGA and a group of background processes. A parameter file is required for instance creation and startup, and the name of the parameter file is determined by ORACLE_SID. The default file name is init.ora for init files and spfile.ora for spfile files. By setting different ORACLE_SID values, you can start different database instances with different parameter files by default.

That is to say, ORACLE_SID determines the instance name of the database instance started locally. It can be started as long as there is corresponding init.ora or spfile.ora, or you can specify pfile startup in startup. It is OK as long as other parameters set in the parameter file can start a database.

Under normal circumstances, ORACLE_SID=test, start the database as shown in the following figure, and the generated process is identified by test.

Close the database, modify ORACLE_SID=mydb, you can also start the database, but you need to manually specify the parameter file, and the generated process is identified by mydb.

Even if the processes are related to mydb, the database name and listening configuration are still test.

The instance_name and inst_name in the corresponding dynamic performance view have also changed:

service_name and sid in tnsname.ora:

The service_name and sid in tnsnames.ora correspond to the service and instance registered in the listener, respectively, of the remote database to which you want to connect.

test = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = test) ) ) test = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test) ) )

sid_name and global_dbname in listener.ora:

These two parameters are used to identify which instance is statically registered when listening with static registration.

SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=orcl) (ORACLE_HOME=/u02/app/oracle/product/11.2.4/db1) (SID_NAME=orcl)))

The above contents are my own collation and summary. If there is something wrong, please ask the Great God for guidance.

Reference: blog.sina.com.cn/s/blog_616b428f0100iwro.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.