Detailed Description: Database Name, Instance name, oracle_sid, database Domain Name

Source: Internet
Author: User

Http://blog.sina.com.cn/s/blog_5e17f8840100bgl8.html

 

 

Detailed Description: Database Name, Instance name, oracle_sid, database domain name, Global Database Name, service name, and manual script to create an Oracle database

 

 

Database Name, Instance name, database domain name, Global Database Name, service name,
This is a few confusing concepts for many beginners. I believe that many beginners are confused by these concepts on the title just like me. Now let's figure them out.I. Database Name
What is the database name?

The database name is the ID of a database, just like the ID card number of a person. He uses the db_name parameter to indicate that if multiple full databases are installed on a machine, each database has a database name. After the database is installed or created, the parameter db_name is written to the parameter file. The format is as follows:
Db_name = myorcl
...
When creating a database, you should consider the database name, and after the database is created, the database name should not be modified, even if you want to modify it will be very troublesome. Because the database name is also written into the control file and the control file is stored in binary format, you cannot modify the control file content. Assume that you have modified the database name in the parameter file, that is, the value of db_name. But at Oracle startup, because db_name In the parameter file is inconsistent with the database name in the control file, causing database startup failure, a ORA-01103 error is returned.Database Name
The database name must be used to install the database, create a new database, create a database control file, modify the data structure, and back up and restore the database.
Many Oracle installation file directories are related to database names, such:
Winnt: D:/Oracle/product/10.1.0/oradata/db_name /...
UNIX:/home/APP/Oracle/product/10.1.0/oradata/db_name /...
Pfile:
Winnt: D:/Oracle/product/10.1.0/admin/db_name/pfile/INI. ora
UNIX:/home/APP/Oracle/product/10.1.0/admin/db_name/pfile/init $ oracle_sid.ora
Tracking file directory:
Winnt:/home/APP/Oracle/product/10.1.0/admin/db_name/bdump /...
In addition, when creating data, the database name in the careate database command must be consistent with the value of the db_name parameter in the parameter file. Otherwise, an error will occur.
Similarly, the alter database statement for modifying the database structure also specifies the name of the database to be modified.
If the control file is damaged or lost, the database cannot be loaded. In this case, you need to re-create the control file by starting the instance in nomount mode, and then run the create controlfile command to create the control file, of course, this command also refers to db_name.
You also need to use the database name to back up or restore the database.
In short, the database name is very important and it must be understood accurately.Query the current data name
Method 1: Select name from V $ database;
Method 2: Show parameter DB
Method 3: view the parameter file.Modify Database Name
We recommend that you determine the database name when creating a database. The database name should not be modified because it is complicated to modify the database name. Now let's take a look at how to modify the database name after the data has been created. The procedure is as follows:
1. Shut down the database.
2. Modify the value of db_name in the database parameter file to the new database name.
3. Start the instance in nomount mode and build the control file (for the command syntax for creating the control file, refer to the Oracle document)Ii. database instance name
What is the database instance name?
The database instance name is the identifier used to contact the operating system, that is, the interaction between the database and the operating system uses the database instance name. The instance name is also written to the parameter file. The parameter is instance_name. On the WINNT platform, the Instance name is also written to the Registry.
The Database Name and Instance name can be the same or different.
In general, the database name and Instance name are one-to-one, but in the Oracle Parallel Server Architecture (that is, the Oracle Real-time application cluster, the Database Name and Instance name are one-to-multiple relationships. This is illustrated in the first article.Query the name of the current database instance
Method 1: Select instance_name from V $ instance;
Method 2: Show parameter instance
Method 3: query in the parameter file.Database instance name and oracle_sid
Although both tables are Oracle instances, the two are different. Instance_name is an Oracle database parameter. Oracle_sid is the environment variable of the operating system. Oracld_sid is used to interact with the operating system. That is to say, the Instance name accessed from the operating system must be accessed through oracle_sid. There are no winnt instances, and oracle_sid must also exist in the registry.
And oracle_sid must be consistent with the value of instance_name. Otherwise, you will receive an error. On the UNIX platform, it is "Oracle not available". On the WINNT platform, it is "TNS: protocol adapter error ".Database instance name and network connection
In addition to interacting with the operating system, the database instance name is also used as the Oracle server ID for network connection. When configuring an oracle host connection string, you need to specify the Instance name. Of course, for network components later than 8i, the service name SERVICE_NAME is required. This concept is explained later.Iii. Database Domain Name
What is a database domain name?

In the distributed database system, database servers of different versions can be remotely copied between servers, regardless of whether the operating system is Unix or windows, the database domain name is mainly used for replication in the Oracle distributed environment. Example:
The distributed database of the National Transportation Administration System, where:
Fujian node: FJ. jtyz
Xiamen node in Fujian: XM. fj. jtyz
Jiangxi: jx. jtyz
Shangrao, Jiangxi: Sr. jx. jtyz
This is the database domain name.
The database domain name exists in the parameter file. Its parameter is db_domain.Query database domain names
Method 1: Select value from V $ parameter where name = 'db _ Domain ';
Method 2: Show parameter domain
Method 3: query in the parameter file.Global Database Name
Global Database Name = database name + database domain name. For example, the Global Database Name of the preceding Fujian node is oradb. fj. jtyz.Iv. Database Service name
What is the database service name?
A new parameter, that is, the Database Service name, is introduced from Oracle9i. The parameter name is SERVICE_NAME.
If the database has a domain name, the Database Service name is the global database name; otherwise, the Database Service name is the same as the database name.Query database service names
Method 1: Select value from V $ parameter where name = 'service _ name ';
Method 2: Show parameter SERVICE_NAME
Method 3: query in the parameter file.Database Service name and network connection
The Oracle network component opened from Oracle8i. The connection host string between the database and the client uses the Database Service name. Previously, we used oracle_sid, that is, the database instance name to create an Oracle database (taking Oracle10g as an example). There are two ways to create a database: one is to create a database manually by using a command line script; the other is to create a database by using the Database Configuration Wizard provided by Oracle. This document describes how to create an Oracle database using a command line script on UNIX and windows. A complete database system should contain a physical structure, a logical structure, a memory structure, and a process structure. If you want to create a new database, all these structures must be completely created.1. Create a database under UNIX
1.Determine the database name, database instance name, and service name
For details about the database name, database instance name, and service name, I have previously used this article. It is not described here.2.Create a parameter file
The parameter file determines the overall structure of the database. Oracle10g has two parameter files: a text parameter file and a server parameter file. Create a text parameter file when creating a database. After creating a database, you can create a server parameter file by using the file parameter file. The name of the text parameter file is initoracle_sid.ora, where oracle_sid is the name of the database instance. Its name and path are:
/Home/APP/Oracle/product/10.1.0/admin/db_name/pfile/initoracle_sid.ora
Db_name indicates the database name. Therefore, create a directory named after it and store the text parameter files in the preceding directory. Assume that the newly created database is named myoracle, And the SID is consistent with the database name. The preceding directory is actually:
/Home/APP/Oracle/product/10.1.0/admin/myoracle/pfile/initmyoracle. ora
1)Parameter file Introduction
The parameter file determines the overall structure of the database and is used to set nearly 260 system parameters of the database. The following categories describe the role of each parameter. For detailed instructions on the use of parameters, see the Oracle official reference document.
A.Database identifier Parameters
Db_name: database name. This parameter is determined before data is created. When a database is created and modified, a control file must be created.
Db_domain: Database domain name, used to distinguish databases with the same name. The Database Name and domain name constitute the global database name.
Instance_name: database instance name, which can be the same as the database
Service_names: Database Service name. It is the same as the global database name. If there is no domain name, the service name is the database name.
B.Log management parameters
Log_archive_start: whether to start the automatic archive process Arch
Log_archive_dest: directory for storing archived log files
Log_archive_format: default file storage format for archiving log files
Log_archive_duplex_dest: directory for storing archived log files (oracle8 or above)
Log_archive_dest_n: directory for storing archived log files (Oracle8i or above)
Log_archive_dest_state_n: The log_archive_dest_n parameter is invalid or takes effect.
Log_archive_max_processes: set the number of automatic archiving processes
Log_archive_min_succeed_dest: sets the minimum number of successfully archived log storage directories.
Log_checkpoint_interval: Set the checkpoint frequency based on the number of logs.
Log_checkpoint_timeout: Set the checkpoint Frequency Based on the time interval.
C.Memory Management Parameters
Db_block_size: standard data block size
Db_nk_cache_size: non-standard data block data buffer size
Shared_pool_size: Share pool size control parameter, in bytes
Db_cache_size: data buffer size of standard data blocks
Db_block_buffers: data buffer size, which is no longer used after 9i
Log_buffer: log buffer size
Sort_area_size: size of the sorting area
Large_pool_size: large pool size
Java_pool_size: Java pool size
D.Maximum number of licensed users
License_max_sessions: Maximum number of sessions that a database can connect
License_max_users: Maximum number of users supported by the database
License_max_warning: Maximum number of warning sessions in the database (when session data reaches this value, a warning is generated when a new session is generated)
E.System trace information management parameters
User_dump_dest: settings for User tracking file generation
Background_dump_dest: location where the background process tracks file generation
Max_dumpfile_size: maximum size of the trace file
F.System Performance Optimization and dynamic statistical parameters
SQL _trace: sets SQL tracing.
Timed_statics: Set dynamic statistics
Audit_trail: starts the database Audit Function
G.Other System Parameters
Control_files: controls the file name and Path
Undo_managment: Undo space management method
Rollback_segments: the name of the rollback segment allocated to this routine.
Open_cursors: maximum value of a cursor that a user can open at a time
Processes: the maximum number of processes, including backend processes and server processes.
Ifile: name of another parameter file
Db_recovery_file_dest: Automatic Database Backup Directory
Db_recovery_file_size: Database Backup File Size
2) Parameter file Style
Db_name = myoracle
Instance_name = myoracle
Db_domain = fangys.xiya.com
Service_names = myoracle.fangys.xiya.com
Control_files = (/home/APP/Oracle/product/10.1.0/oradata/myoracle/control01.ctl,
/Home/APP/Oracle/product/10.1.0/oradata/myoracle/control02.ctl,
/Home/APP/Oracle/product/10.1.0/oradata/myoracle/control03.ctl)
DB _ block_size = 8192
User_dump_dest =/home/APP/Oracle/product/10.1.0/admin/myoracle/udump
Background_dump_dest =/home/APP/Oracle/product/10.1.0/admin/myoracle/bdump
Core_dump_dest =/home/APP/Oracle/product/10.1.0/admin/myoracle/cdump
Db_recovery_file_dest =/home/APP/Oracle/product/10.1.0/flash_recover_area
Db_recovery_file_size = 100g
...3.Set Operating System Parameters
$ Oracle_sid = myoracle
$ Export oracle_sid4.Start an instance and create a database
Before creating a database, start the database instance with the new database parameters, because the Database Control file has not yet been generated, and the database cannot be mounted or open. When the instance is started, Oracle only allocates the SGA Zone according to the memory parameters to start the system background process.
$ Sqlplus "sys/pass as sysdba" SQL> startup nomount
If the parameter file is not in the specified directory, you can specify the parameter file when starting the instance:
SQL> startup pfile =/export/home/user/initmyoracle. init nomount
After the instance is started, you can use the create database Command to create data. For detailed syntax, see the Oracle official SQL reference document. Here we will introduce the example:
SQL> Create Database myoracle
Maxinstance 1
# Maxloghistory 216
Maxlogfiles 50
Maxlogmembers 5
Datafile '/home1/APP/Oracle/product/10.1.0/oradata/myoracle/system01.dbf' size 500 m
Autoextend on next 100 m maxsize Unlimited
Logfile
Group 1 ('/home1/APP/Oracle/product/10.1.0/oradata/myoracle/log1a. log ',
'/Home1/APP/Oracle/product/10.1.0/oradata/myoracle/log1b. log') Size 10 m,
Group 2 ('/home1/APP/Oracle/product/10.1.0/oradata/myoracle/log2a. log ',
'/Home1/APP/Oracle/product/10.1.0/oradata/myoracle/log2b. log') Size 10 m,
Group 3 ('/home1/APP/Oracle/product/10.1.0/oradata/myoracle/log3a. log ',
'/Home1/APP/Oracle/product/10.1.0/oradata/myoracle/log3b. log') Size 10 m,
Undo tablespace undotbs datafile
'/Home1/APP/Oracle/product/10.1.0/oradata/myoracle/undotbs01.dbf' size 200 m
Autoextend on next 100 m maxsize Unlimited
Default temporary talespace temp tempfile
'/Home1/APP/Oracle/product/10.1.0/oradata/myoracle/temp01.dbf' size 325 m
Autoextend on next 100 m maxsize Unlimited
Default tablespace users datafile
'/Home1/APP/Oracle/product/10.1.0/oradata/myoracle/usertbs01.dbf' size 1000 m
Character Set zhs16gbk;
The following describes the meanings of the keywords in the create database statement:
Datafile: Data File definition of the system tablespace
Logfile: the definition of a log file group.
Undo_tablespace: definition of the redo tablespace
Default temporty tablespace: default definition of temporary tablespace
Default tablespace: the definition of the default data table space.5.Create a data dictionary
After the database is created, the database is automatically in the open state, and all the V $ ×××× data dictionaries can be queried. Other data dictionaries, such as dba_data_files and dba_tablespaces, do not exist. You must create a data dictionary for the system through the following steps.
1) Load Common Data Dictionary packages
SQL> @/home/APP/Oracle/product/10.1.0/db_1/rdbms/catalog
2) load the PL/SQL package
SQL> @/home/APP/Oracle/product/10.1.0/db_1/rdbms/admin/catproc
3) load the supported software packages for Data Replication
SQL> @/home/APP/Oracle/product/10.1.0/db_1/rdbms/admin/catrep
4) load the Java package
SQL> @/home/APP/Oracle/product/10.1.0/db_1/JavaVM/install/initjvm
5) load the System Environment File
SQL> connect system/pass
SQL> @/home/APP/Oracle/product/10.1.0/db_1/sqlplus/admin/pupbld2. Create a database in Windows
Oracle instances are operating system services in windows. In Windows, the method of using command line to create data is different. The difference is that in Windows, you need to create a database service and an instance first.1.Determine the database name, database instance name, and service name and create a directory (the script created by dbca does not contain the create directory command and must be created by yourself)
Create directory command
(The following uses db_name = eygle as an example): mkdir D:/Oracle/ora90/export toollogs/dbca/eygle
Mkdir D:/Oracle/ora90/Database
Mkdir D:/Oracle/admin/eygle/adump
Mkdir D:/Oracle/admin/eygle/bdump
Mkdir D:/Oracle/admin/eygle/cdump
Mkdir D:/Oracle/admin/eygle/dpdump
Mkdir D:/Oracle/admin/eygle/pfile
Mkdir D:/Oracle/admin/eygle/udump
Mkdir D:/Oracle/flash_recovery_area
Mkdir D:/Oracle/oradata
Mkdir D:/Oracle/oradata/eygle2.Create a parameter file
The parameter file name and Path in windows are as follows:
D:/Oracle/product/10.1.0/admin/db_name/pfile/init. ora (Oracle10g)
D:/orant/database/inioracle_sid.ora (oracle7, oracle8)
The content of the parameter data file is consistent with that described above. It is not described here.3.Select database instance
Set the environment variable oracle_sid
C:/> set oracle_sid = database instance name4.Create a database instance
The command for creating a data library in Windows is oradim.exe, which is an executable file and can be run directly under the Operating System symbol. Enter oradim to display the help of this command.
C:/> oradim
The following describes the parameters of the oradim command.
-------------------------------
-New indicates creating an instance.
-Edit: modifies an instance.
-Delete: deletes an instance.
-Sid: Specifies the name of the instance to be started.
-Srvc service: Specifies the name of the service to be started.
-Intpwd password: password used to connect to the database in Internal Mode
-Maxusers count the maximum number of users that can be connected to the instance
-Usrpwd password: Specifies the password of an internal user. This parameter is not required for Windows logon management.
-Pfile: The parameter file name and path used by the instance.
-Starttype srvc | inst | srvc, inst startup option (srvc: Start service only, inst: Start instance, service must start first, srvc, inst: Service and instance start at the same time)
-Shuttype srvc | linst | srvc, inst option (srvc: only shut down the service, the instance must be closed, inst: only shut down the instance, srvc, inst: both the service and instance are closed)
-Startmode A | the mode in which M is used to create an instance (A: automatic, that is, automatic start of M: manual when Windows is started)
-Shutmode A | I | the mode in which M is used to shut down the instance (A: Abort exception mode, I: immediate instant mode, N: Normal Mode)
----------------------------
Example: Create a database instance
C:/> oradim-New-Sid myoracle-startmode M-pfile "D:/fangys/initmyoracle. ora"
Or
C:/> oradim-New-srvc oracleservicemyoracle-startmode M-pfile "D:/fangys/initmyoracle. ora"
Example: modify a data instance
C:/> oradim-edit-Sid myoracle-startmode
Or
C:/> oradim-edit-srvc oracleservicemyoracle-startmode
Example: delete an instance
C:/> oradim-delete-Sid myoracle
Or
C:/> oradim-delete-srvc oracleservicemyoracle
For example, start the service and instance
C:/> oradim-startup-Sid myoracle-starttype srvc, Inst
Start service only
C:/> oradim-startup-Sid myoracle-starttype srvc
Start an instance:
C:/> oradim-startup-Sid myoracle-starttype Inst
For example, disable services and instances.
C:/> oradim-shutdown-Sid myoracle
C:/> oradim-shutdown-Sid myoracle-shuttype srvc, Inst5.Start an instance and create a database
C:/> oradim-New-Sid myoracle-intpwd syspass-startmode A-pfile D:/fangys/initmyoracle. ora
C:/> set oracle_sid = myoracle
C:/> sqlplus sys/syspass as sysdba
SQL> startup-pfile = D:/fangys/initmyoracle. ora nomount
SQL> Create Database myoracle
Logfile group...
...

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: info-contact@alibabacloud.com 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.