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

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... ...