Create a relationship between an Oracle database, database name, and instance name and sid (picture and text)

Source: Internet
Author: User
Tags data structures create database

Introduction to Directory Software Environment install Oracle Listener start stop listener CREATE database database name db_name database instance name instance_name database name and instance name the relational operating system environment variable Oracle_sid

Software Environment operating system
RHEL 6.1 Software Environment
ORACLE10GR2 Preface

In the previous RHEL6.1 installation ORACLE10GR2 (text, parsing) recorded how successfully installed ORACLE10GR2 in RHEL6.1, but did not create a database during the installation process, this article is mainly recorded if the database and monitor the creation. Install Oracle Listener

Oracle Net Services Configuration: Short NETCA is also the name of the directive that starts its configuration program. The main role is to configure the listener, named method configuration, local Net service configuration, 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 stored location of the listening file is: $ORACLE _home\network\admin\listener.ora
File location of service name in: $ORACLE _home\network\admin\tnsnames.ora

Log on with an Oracle user and execute the NETCA directive 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 listener's port

Do you need to create another listener?

Create complete

Click Finsh

Output after configuration is complete

Configuring Listener:listener
Default Local naming configuration complete.
Listener configuration complete.
Oracle Net Listener Startup:
Running Listener control:
/u01/oracle/bin/lsnrctl start Listener
Listener Control complete.
Listener started successfully.
Oracle Net Services Configuration successful. The exit code is 0

Test :

[Root@jmilk bin]# pwd
[Root@jmilk bin]#./lsnrctl status
start, Stop listener
Lsnrctl start   #启动 
lsnrctl stop    #停止 
lsnrctl Status  

NETCA's execution file :

[Root@jmilk bin]# pwd
[root@jmilk bin]# vim NETCA
Creating a database

DBCA programs can execute database options for creating databases, deleting databases, managing database templates, and configuring existing databases
Configure the database Configuration Assistant

[Oracle@jmilk database]$ DBCA

Create a database Welcome page

Choose to create a database

Choose to create a common database

Enter the name of the database you want to create

Configuring Enterprise Manager
You can configure the Enterprise Manager warning to configure the listener in the current Oracle home directory by using database control, and you must run NETCA to configure and start the listener before continuing. Or you can choose to continue, but do not use the database control configuration.

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

Choose how you store the database, where you choose to store it in the OS file system

Choose where to create the database file, where you choose to create from the database template

Specify a fast recovery area for a database

Select the database's character set for UTF8, better support Chinese

Create a database, you can select a production database to create a script

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

[Oracle@jmilk database]$ cd $ORACLE _home/bin
[oracle@jmilk bin]$ mv Oracle Oracle.bin

Vim Oracle

export Disable_hugetlbfs=1
exec $ORACLE _home/bin/oracle.bin $@
[Oracle@jmilk bin]$ chmod a+x Oracle
[oracle@jmilk bin]$ ID Oracle
uid=501 (Oracle) gid=502 (Oinstall) groups=502 (Oinstall), 503 (DBA)
[oracle@test bin]$ more/proc/sys/vm/hugetlb_shm_group
[oracle@test bin]$ Su- Root
[Root@test ~]# echo "503" >/proc/sys/vm/hugetlb_shm_group
#将dba的gid写入hugetlb_shm_ Group

Waiting to create DATABASE complete

Click Password Management can configure the system user, can unlock, lock a user, also can set a password for the user.
The Scott user is one of the founders of Oracle Corporation, which is typically used by the user to learn, and his default password is Tiger, which is the name of a cat in Scott's house.

Click Exit to complete the creation of the database

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

[Oracle@jmilk ~]$ sqlplus system/fanguiju

sql*plus:release on Thu June 2 18:10:04 2016

Cop Yright (c) 1982, +, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition release Production
with the partitioning, OLAP and Data Mining options

Database name Db_name

Database names are internal identities that are used to differentiate between different databases, just like people's ID numbers.
He uses parameter db_name to indicate that if multiple databases are created on an Oracle server, each database will have a unique database name. After the database installation or creation is complete, the parameter db_name is written to the parameter file/u01/admin/demo/pfile/init.ora.425201695953.
You should consider the database name when you create the database, and after you create the database, the database name should not be modified, even if you want to modify it. 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, modifying the db_name value. However, at Oracle startup, a ORA-01103 error is returned because the db_name in the parameter file is inconsistent with the database name in the control file, causing the database to fail to start.

Database parameter file:

[Oracle@jmilk pfile]$ pwd

the role of database name
Database names are used when you install a database, create a new database, create database control files, modify data structures, and back up and restore databases.
There are many Oracle installation file directories that are related to the database name

[Oracle@jmilk demo]$ pwd

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

database instance name instance_name

The database instance name is used for the connection between Oracle and the OS and for use with external connections for Oracle server, and it can be the same as the database name or not. This parameter is instance_name, and 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 database instance name, only that the database name is not used, unlike the database name, the instance name can be modified after the database is installed or the database is created. After the database installation completes, the instance name is written to the database parameter file **pfile , in the following format:

  Db_name= "Demo"  # generally not allowed to modify   
  db_domain= ""   
  Instance_name=demo # can be modified, can be the same as the db_name can also be different      
relationship between database name and instance name

In general, the database name and instance name are one-to-one relationships, but the database name and instance name are One-to-many relationships in the Oracle Parallel server architecture, which is the Oracle Live application cluster. Database name and instance name together to determine a database, a database can have more than one instance, running each other unaffected.

Querying the current database instance name
Method One:

Select instance_name from V$instance;

Method Two:

Show Parameter instance

Method Three: Query the operating system environment variable in the parameter file Oracle_sid

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

  (os<---------------->  ORACLE  database<--------(instance_name (instance name))  

Although the two parameters listed here are database instance names, the following:
The instance_name parameter is an Oracle database parameter that can be queried in the parameter file.
The ORACLE_SID parameter is the operating system environment variable.

The operating system environment variable ORACLE_SID is used to interact with the operating system, that is, to access an instance from the operating system's perspective, through Oracle_sid. Oracle_sid must be consistent with the value of instance_name, otherwise you will receive an error in Linuxos is ORACLE not available, which is TNS in Winntos : Protocol Adapter error . This parameter is the same as Oracle_base, Oracle_home, and so on. After the database is installed, ORACLE_SID is used to define the name of the database parameter file.

$ORACLE _base/admin/db_name/pfile/init$oracle_sid.ora

To define a method:

Export   Oracle_sid=demo
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.