Oracle 10 Gb manual database creation using command line

Oracle 10 Gb manual database creation using command line

Reprinted: http://tech.ccidnet.com/art/1105/20080807/1536965_1.html

There are two methods to create a database in Oracle. The first is to use ORACLE database builder and dbca, which is a graphical interface and easy to understand because of its friendly and beautiful interface and complete prompts. In Windows, this tool can be used in OracleProgramOpen ("START"-"program"-"oracle-oradb10g_home1"-"configuration and migration tools"-"database configurationassistant") in the group, orCommand Line("START"-"run"-"cmd") directly input dbca to open the tool. Another method is to create a database manually. This is what we will talk about below.

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. Manual database creation requires several steps, each of which is critical. It includes:

1. create necessary Directories

2. Create an initialization parameter file

3. Set the environment variable oracle_sid.

4. Create an instance

5. Create a password file

6. Start the database to the nomount (Instance) Status

7. Execute the database creation script

8. Execute catalog to create a data dictionary.

9. Run catproc to create a package.

10. Run pupbld.

11. Create a spfile file from the initialization parameter file

12. Execute the Scott script to create the Scott mode.

After completing the above steps, you can use "SQL> alter database open;" to open the normal use of the database. Next, I will use the above steps as an experiment.

Experimental System Platform: Windows Server 2000 Database System Version: Oracle Database 10 GB

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

1. OpenCommand LineTools to create related directories

C: \> mkdir D: \ oracle \ product \ 10.1.0 \ admin \ book

C: \> mkdir D: \ oracle \ product \ 10.1.0 \ admin \ book \ bdump

C: \> mkdir D: \ oracle \ product \ 10.1.0 \ admin \ book \ udump

C: \> mkdir D: \ oracle \ product \ 10.1.0 \ admin \ book \ cdump

C: \> mkdir D: \ oracle \ product \ 10.1.0 \ admin \ book \ pfile

C: \> mkdir D: \ oracle \ product \ 10.1.0 \ admin \ book \ create

C: \> mkdir D: \ oracle \ product \ 10.1.0 \ oradata \ book

You can also create a directory on the GUI of windows. The subdirectories in the D: \ oracle \ product \ 10.1.0 \ admin \ book 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_book.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.1.0 \ oradata \ book directory stores various database files, including control files, data files, and redo log files.

2. Create 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. ora file, copy it to D: \ oracle \ product \ 10.1.0 \ bd_1 \ Databse, and rename it initbook. ora. Open initbook. ora in notepad and modify the following content:

Db_domain = ""

Db_name = book

Control_files = ("D: \ oracle \ product \ 10.1.0 \ oradata \ book \ control01.ctl", "d: \ oracle \ product \ 10.1.0 \ oradata \ book \ control02.ctl", "D: \ oracle \ product \ 10.1.0 \ oradata \ book \ 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.1.0 \ admin \ book \ bdump

Core_dump_dest = D: \ oracle \ product \ 10.1.0 \ admin \ book \ cdump

User_dump_dest = D: \ oracle \ product \ 10.1.0 \ admin \ book \ udump

3. OpenCommand Line, Set the environment variable oracle_sid

C: \> set oracle_sid = book

By defaultCommand LineInDatabase instanceIt is book.

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

C: \> oradim-New-Sid book

Oradim is the name of the tool program used to create an instance.-New indicates that the new instance is executed,-delete indicates that the instance is deleted, and-Sid indicates the name of the victim instance.

5. Create a password file

C: \> orapwd file = D: \ oracle \ product \ 10.1.0 \ db_1 \ database \ pwdbook. ora Password = bookstore 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 database to the nomount (Instance) Status

C: \> sqlplus/nolog

SQL * Plus: Release 10.1.0.2.0-production on Wednesday June 29 23:09:35 2005

Copyright 1982,2004, Oracle. All rights reserved.

SQL> connect sys/bookstore 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>

7. Execute the database creation script

To execute the database creation script, you must first have the database creation script. (Where can I find the database creation script? I have no !) Don't worry, please proceed.

There are two methods to obtain a database creation script that meets your requirements. One is to use dbca to create a database on your computer and follow the prompts to perform the script step by step, when completing step 1, select "generate database creation script". After that, you can find the script in the corresponding directory and fix it as needed. Another method is to manually write a database creation script. This is also the method to be used here. Use notepad to edit the following content, and save it as a file name with the suffix (*. SQL) SQL script, saved here to the root directory of the E disk, and the file name is called book. SQL.

Create Database book

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

Extent management local

Sysaux datafile 'd: \ oracle \ product \ 10.1.0 \ oradata \ book \ 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 \ book \ temp01.dbf 'size 20 m reuse autoextend on next 640 K maxsize Unlimited

Undo tablespace "undotbs1" -- note that the Undo tablespace corresponds to the parameter file.

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

Logfile

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

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

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

Then execute the newly created database creation script:

SQL> Start E: \ book. SQL

8. Execute catalog to create a data dictionary.

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

9. Run catproc to create a package.

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

10. Run pupbld.

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. Create a spfile file from the initialization parameter file

SQL> Create spfile from pfile;

12. Execute the Scott script to create the Scott mode.

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

13. Open the database to a normal state

SQL> alter database open;

14. Connect to the database with Scott (password: Tiger) and test whether the new database can run normally.

So far, the entire database has been created. Then you can create your own account, table space, and database objects on this database, so we will not describe it more here.