Create an Oracle database manually

Note: The Blue font indicates the various commands to be executed during database creation. Red indicates the content and prompts that may need to be modified in practice. The green font is used in the file.

Manual database creation is more troublesome than dbca database creation. However, if we have learned how to manually create a database, we can better understand the architecture of the Oracle database.

Next, I will use the above steps as an experiment.

Oracle installation path: name of the database created by drive D: Sm

(Modify the path and data name in the following article in the experiment)

1. Open the command line tool and create related directories.

C: \> mkdir D: \ oracle \ product \ 10.2.0 \ admin \ Sm

C: \> mkdir D: \ oracle \ product \ 10.2.0 \ admin \ Sm \ bdump

C: \> mkdir D: \ oracle \ product \ 10.2.0 \ admin \ Sm \ udump

C: \> mkdir D: \ oracle \ product \ 10.2.0 \ admin \ Sm \ cdump

C: \> mkdir D: \ oracle \ product \ 10.2.0 \ admin \ Sm \ pfile

C: \> mkdir D: \ oracle \ product \ 10.2.0 \ admin \ Sm \ create

C: \> mkdir D: \ oracle \ product \ 10.2.0 \ oradata \ Sm

You can also create a directory on the GUI of windows. Where:

D: Several subdirectories under the \ oracle \ product \ 10.2.0 \ admin \ SM directory are mainly used to store tracking information during database operation. The two most important sub-directories are the bdump and udump directories. The bdump directory stores the tracking information of various background processes in the database's dynamic process. The alert file is a warning file, the file name is called alert_sm.log. When a database encounters a problem, you can first view the file to find out the cause. You can also find the cause of various problems during manual creation. The udump directory stores trace information related to a specific session.

D: \ oracle \ product \ 10.2.0 \ oradata \ SM directory stores various database files, including control files, data files, and redo log files.

2Create an initialization parameter file

When starting the database system, you must use the initialization parameter file settings to allocate memory and start necessary background processes. Therefore, whether the initialization parameter file is created correctly or whether the parameter settings are correct is related to the "fate" of the entire database ".

You can create an initialization parameter file by copying the current initialization parameter file and modifying it as appropriate, so you do not have to write it out in one sentence manually, because the structure of the initialization parameter file is basically the same. When we install Oracle, the system has installed a database named orcl for us, so we can get an initialization parameter file from it. Open D: \ oracle \ product \ 10.1.0 \ admin \ orcl \ pfile and find init. copy the file starting with Ora to D: \ oracle \ product \ 10.1.0 \ bd_1 \ Databse and change it to initsm. ora.

Note:: It is not copied to another path. When the instance is started, it will go to the database directory to find the initialization file. Databases produced using dbca only generate a navigation init file under the database directory, and the real information is stored in other locations.

Open initsm. ora in notepad and modify the following content:

Db_domain = ""

Db_name = Sm

Control_files = ("D: \ oracle \ product \ 10.2.0 \ oradata \ Sm \ control01.ctl", "d: \ oracle \ product \ 10.2.0 \ oradata \ Sm \ control02.ctl", "D: \ oracle \ product \ 10.2.0 \ oradata \ Sm \ control03.ctl ")

Undo_management = auto

Undo_tablespace = undotbs1

-Note that the "undotbs1" here must correspond to the database creation step.

Background_dump_dest = D: \ oracle \ product \ 10.2.0 \ admin \ Sm \ bdump

Core_dump_dest = D: \ oracle \ product \ 10.2.0 \ admin \ Sm \ cdump

User_dump_dest = D: \ oracle \ product \ 10.2.0 \ admin \ Sm \ udump

3. Open the command line and set the environment variableOracle_sid

C: \> set oracle_sid = Sm

The environment variable is set to SM by default, and the database instance operated in the command line is specified.

4. Create an instance (that is, the background Control Service)

C: \> oradim-New-Sid Sm

Oradim is an instance creation tool.Program-New indicates that the instance is created,-delete indicates that the instance is deleted, and-Sid indicates the Instance name.

5. Create a password file

C: \> orapwd file = D: \ oracle \ product \ 10.1.0 \ db_1 \ database \ pwdsm. ora Password = smstore entries = 2

Orapwd is the name of the worker program used to create the password file. The file parameter specifies the Directory and file name of the password file, and the password parameter specifies the password of the Sys user, the entries parameter specifies the number of users with DBA permissions in the database. Of course, there is also a force parameter. I believe you do not mean it, so I will not detail it here.

Note that the command here requires a line of input, and line breaks are not allowed in the middle; otherwise, unnecessary errors may occur.

The password file is used to store the password of a sys user. Because a sys user is responsible for creating a database, starting a database, and shutting down the database, the password file is separately stored in the password file as a sys user, in this way, password verification can also be performed when the database is opened at the end.

6, Start the databaseNomount (Instance)Status

C: \> sqlplus/nolog

SQL> connect sys/smstore as sysdba

--- Use sys to connect to the database.

Connected to idle routine

SQL> startup nomount

The Oracle routine has been started.

Total system global area 319888364 bytes

Fixed size 453612 bytes

Variable Size 209715200 bytes

Database buffers 109051904 bytes

Redo buffers 667648 bytes

SQL>

7Execute the database creation script

Use NotePad to edit the following content and save it as an SQL script with the file name and the suffix (*. SQL). Save it to the root directory of the E disk and the file name is SM. SQL.

Create Database Sm

Datafile 'd: \ oracle \ product \ 10.1.0 \ oradata \ Sm \ system01.dbf 'size 300 m reuse autoextend on next 10240 K maxsize Unlimited

Extent management local

Sysaux datafile 'd: \ oracle \ product \ 10.1.0 \ oradata \ Sm \ sysaux01.dbf'

Size 120 m reuse autoextend on next 10240 K maxsize Unlimited

Default temporary tablespace temp

Tempfile 'd: \ oracle \ product \ 10.1.0 \ oradata \ Sm \ temp01.dbf 'size 20 m reuse autoextend on next 640 K maxsize Unlimited

Undo tablespace undotbs1

Datafile 'd: \ oracle \ product \ 10.1.0 \ oradata \ Sm \ undotbs01.dbf 'size 200 m reuse autoextend on next 5120 K maxsize Unlimited

Logfile

Group 1 ('d: \ oracle \ product \ 10.1.0 \ oradata \ Sm \ redo01.log) size 10240 K,

Group 2 ('d: \ oracle \ product \ 10.1.0 \ oradata \ Sm \ redo02.log ') size 10240 K,

Group 3 ('d: \ oracle \ product \ 10.1.0 \ oradata \ Sm \ redo03.log') size 10240 K

Then execute the newly created database creation script:

SQL> Start E: \ SM. SQL or SQL> run e: \ SM. SQL

8, ExecutionCatalogCreate data dictionary using scripts

SQL> Start c: \ oracle \ product \ 10.2.0 \ db_1 \ RDBMS \ admin \ catalog. SQL

9, ExecutionCatprocCreatePackagePackage

SQL> Start D: \ oracle \ product \ 10.1.0 \ db_1 \ RDBMS \ admin \ catproc. SQL

10, ExecutionPupbld

Pupbld. SQL is not executed. It serves SQL * Plus. When using SQL * Plus to connect to and use a database as a common user, the tool itself needs to use the table and view of the product profile. We need to log on to the system user to execute this SQL statement.

Before executing pupbld, you must convert the current user (sys) to system, that is, connect to the database using the system account. Because the database is just created, the system password is the default password of the system, that is, the manager. You can reset the account password after the database is created.

SQL> connect system/Manager

SQL> Start D: \ oracle \ product \ 10.1.0 \ db_1 \ sqlplus \ admin \ pupbld. SQL

11, Created by the initialization parameter fileSpfileFile

SQL> Create spfile from pfile = 'C: \ oracle \ product \ 10.2.0 \ db_1 \ database \ initsm. ora ';

12, ExecutionScottScript CreationScottMode

SQL> Start D: \ oracle \ product \ 10.1.0 \ db_1 \ RDBMS \ admin \ Scott. SQL

13,Run the following command to test whether the database creation is complete:

SQL> select * from Scott. EMP;

If the employee information is correctly displayed, it indicates that the database has been established.

14, ConfigurationOEM enables you to manage and control databases through a browser

Cd c: \ oracle \ product \ 10.2.0 \ db_1 \ bin

Set oracle_sid = book

Emca-repos recreate

Emca-config dbcontrol DB