Oracle management-create an Oracle database

Create an Oracle database

Create an Oracle database (taking Oracle10g as an example)

There are two ways to create a database: one is to create a database manually using a command line script; the other is to create a database 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) Introduction to parameter files
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 identity 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 tracking 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_SID

4. Start the 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/pupbld

2. 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
(Omitted)

2. 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 a database instance
Set the environment variable ORACLE_SID
C: \> set ORACLE_SID = database instance name

4. 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 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, inst

5. Start the 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...
...

6. Create a data dictionary
SQL> @ d: \ oracle \ product \ 10.1.0 \ db_1 \ rdbms \ admin \ catalog. SQL;
SQL> @ d: \ oracle \ product \ 10.1.0 \ db_1 \ rdbms \ admin \ catproc. SQL;
SQL> @ d: \ oracle \ product \ 10.1.0 \ db_1 \ rdbms \ admin \ catrep. SQL;
SQL> @ d: \ oracle \ product \ 10.1.0 \ db_1 \ javavm \ install \ initjvm. SQL;