Create a relationship between the Oracle database, the database name, and the instance name and SID (illustrated)

Source: Internet
Author: User
Tags dba


    • Directory
    • Software Environment
    • Objective
    • Installing the Oracle Listener
      • Start Stop Listener
    • Create a database
      • Database name Db_name
      • DB Instance Name instance_name
      • The relationship between the database name and the instance name
      • Operating system environment variable Oracle_sid

Software Environment
    • Operating system
      • RHEL 6.1
    • Software Environment
      • Oracle10gr2

In the previous RHEL6.1 installation Oracle10gr2 (graphics, parsing) documented how to successfully install ORACLE10GR2 in RHEL6.1, but did not create a database during installation, this article mainly records if you create a database and a listener.

Installing the Oracle Listener

Oracle Net Services configuration: NETCA, which is also the name of the directive that initiated its configuration program. The main functions are configuring listeners, naming method configuration, local net service configuration, and directory usage configuration. That is, you can configure a listener and service name so that Oracle client can connect to the database for related operations.
The monitored files are stored in the following locations: $ORACLE_HOME\NETWORK\ADMIN\listener.ora
The location of the file where the service name is stored is:$ORACLE_HOME\NETWORK\ADMIN\tnsnames.ora

Log in as an Oracle user and execute the NETCA command to start the configuration program:

[oracle@jmilk database]$ netca

Select Configure Listener

Add a listener

Set the name of a listener

Select the protocol for the Listener

Select the port of the listener

Do I need to create another listener?

Create complete

Click Finsh

Output after configuration is complete

Configuring Listener:LISTENERDefaultconfigurationconfigurationconfigurationexitis0

Test :

[root@jmilk bin]# pwd/u01/oracle/bin[root@jmilk bin]# ./lsnrctl status
Start and stop the listener
lsnrctl start   #启动 stop    #停止 lsnrctl status  #查看状态 

NETCA's execution file :

[root@jmilk bin]# pwd/u01/oracle/bin[root@jmilk bin]# vim netca
Create a database

DBCA programs can perform database options such as creating databases, deleting databases, managing database templates, and configuring existing databases
Configuring the Database Configuration Assistant

[oracle@jmilk database]$ dbca

Create DATABASE Welcome page

Choosing to create a database

Choose to create a common database

Enter the name of the database you want to create

Configure Enterprise Manager
You can configure an Enterprise Manager warning to warn that when you use database control to configure a listener in the current Oracle home directory, you must run NETCA to configure and start the listener before continuing. Alternatively, you can choose to continue, but do not use the database control configuration.

Enter the global password for the system user, or you can individually edit a different password for each system user.
All Account:system, SYS, SYSDBA

Choose how to store the database, which is stored in the OS's file system

Select the location where you created the database file, select Create from Database template

Specify a fast recovery area for a database

Select the database character set as UTF8, better support for Chinese

Create a database, you can choose a production database creation script

DBCA times wrong hint : ora-27125:unable to create shared memory segment
Workaround :

[oracle@jmilk database]$ $ORACLE_HOME/bin[oracle@jmilk bin]$ mv oracle oracle.bin

Vim Oracle

#!/bin/bashexport DISABLE_HUGETLBFS=1exec$ORACLE_HOME[email protected]EOF
[oracle @jmilk  Bin]$  chmod a+x oracle[oracle @jmilk  Bin]$  ID oracleuid=501  (Oracle) Gid= 502  (Oinstall) groups=502  (Oinstall), 503  (DBA) [Oracle @test  bin] $  more/proc/sys/vm/hugetlb_shm_group0  [ Oracle @test  bin]$  su-rootpassword  :  [Root @test  ~ ]# echo "503" >/proc/sys/vm/hugetlb_shm_group  # Write the DBA's GID to hugetlb_shm_group   

Wait for database completion to be created

Click Password Management can be configured for system users, you can unlock, lock a user, or you can set a password for the user.
The Scott user is one of the founders of Oracle, and in general we use this user to learn, and his default password tiger is the name of a cat in Scott's home.

Click Exit to complete the creation of the database

Test : Enter username/password to successfully log in indicates that the database was created successfully.

[Email protected] ~]$ Sqlplus system/fanguijusql*plus:Release 10.2. 0. 1. 0-Production onThu June2  -:Ten:Geneva  .Copyright (c)1982,2005, Oracle. AllRights reserved. Connected to: Oracle DatabaseTenG Enterprise EditionRelease 10.2. 0. 1. 0- -bitProduction withThe partitioning, OLAP andData Mining optionssql>
Database name Db_name

A database name is an internal identity used to differentiate between different databases, just like a person's ID number.
He uses parameters DB_NAME to indicate that if multiple databases are created on a single Oracle server, each database will have a unique database name. After the database installation or creation is complete, the parameters DB_NAME are written to the parameter file /u01/admin/demo/pfile/init.ora.425201695953 .
When you create a database, you should consider the name of the database, and after you create the database, the database name should not be modified, even if you want to modify it will be cumbersome. Because the database name is also written to the control file, the control file is stored in binary mode, and the user cannot modify the contents of the control file. Suppose the user modifies the database name in the parameter file, which is DB_NAME the modified value. However, when Oracle starts, it returns a ORA-01103 error because the database name in the parameter file is DB_NAME inconsistent with the database names in the control file, resulting in a failure to start.

Database parameter file:

[oracle@jmilk pfile]$ pwd/u01/admin/demo/pfile[oracle@jmilk pfile]$ vim init.ora.425201695953

the role of database names
The database name is required to install the database, create a new database, create a database control file, modify the data structure, and back up and restore the database.
There are many Oracle installation file directories that are related to the database name

[oracle@jmilk demo]$ pwd/u01/oradata/demo

querying the current data name
Method One:

select name from v$database;

Method Two:

show parameter db

Method Three: View the parameter file.
VI init.ora.425201695953

DB Instance Name instance_name

The database instance name is used for the connection between Oracle and the OS and for use with external connections to Oracle server, and can be the same as the database name. The parameter is instance_name that if the OS is to interact with the database, the database instance name must be used.
example:**client to connect to an Oracle server, you must know its DB instance name, only the database name is useless, and unlike the database name, the instance name can be modified after the database is installed or the database is created. After the database installation is complete, the instance name is written to the database parameter file **pfile , in the following format:

  db_name="demo"  # 一般不允许修改     db_domain=""     # 可以修改,可以与db_name相同也可不同      
The relationship between the database name and the instance name

In general, database names and instance names are a one-to-many relationship, but if you are in an Oracle Parallel server architecture (that is, Oracle real-time application clusters), the database name and instance name are a one-to-numerous relationship. The database name and instance name together determine a single database in which multiple instances can be run without affecting each other.

Querying the current DB instance name
Method One:

select instance_name from v$instance;

Method Two:


Method Three: Query in the parameter file

Operating system environment variable Oracle_sid

In practice, the description of the database instance name sometimes uses the instance name instance_name parameter, sometimes using ORACLE_SID parameters.
These are both database instance names, what's the difference?
The relationship between instance name instance_name, Oracle_sid, Database, OS :

  (OS<---------------->  ORACLE  Database<--------(Instance_name(实例名))  

Although the two parameters listed here are database instance names, the following:
instance_nameParameters are parameters of the Oracle database, which can be queried in the parameter file.
ORACLE_SIDThe parameter is the operating system environment variable.

Operating system environment variables are ORACLE_SID used to interact with the operating system, that is, accessing an instance from an operating system perspective must pass ORACLE_SID . ORACLE_SIDmust be instance_name consistent with the value, otherwise you will receive an error in Linuxos that is ORACLE not available, which is the TNS: Protocol adapter error in Winntos. This parameter is ORACLE_BASE the ORACLE_HOME same as, and so on. After the database is installed, it ORACLE_SID is used to define the name of the database parameter file.

$ORACLE_BASE/admin/DB_NAME/pfile/init$ORACLE_SID.ora#或  /u01/admin/demo/pfile/init.ora.425201695953

Define the method:

export   ORACLE_SID=demo

Create a relationship between the Oracle database, the database name, and the instance name and SID (illustrated)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.