Create a database in Oracle10g in Windows (1)

There are two methods to create a database in Oracle.

1. Using dbca of Oracle is a graphical interface that is easy to use and easy to understand, because of its friendly and beautiful interface and complete prompts. (PS: You can enter dbca from the Start Menu or press dbca in cmd to start dbca)

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

The difference between using dbca and manual database creation is like the difference between using a dummies camera and a SLR camera. As a senior Oracle DBA, you must learn to manually create a database to better adapt to the needs of your business.

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 (Windows Service)

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. Execute the pupbld script to set sqlplus

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.

Here we will create a database with the SID of mydb. The specific directory is determined based on your own Oracle installation:

1. create necessary Directories

First, create mydb under the Admin directory and subfolders under mydb.

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

Second, create a database file storage directory under oradata

C:/Oracle/product/10.1.0/oradata/mydb 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 C:/Oracle/product/10.1.0/admin/orcl/pfile and find init. ora file, copy it to C:/Oracle/product/10.1.0/bd_1/Databse and C:/Oracle/product/10.1.0/bd_1/DBS, and rename it initmydb. ora. Then, use NotePad to open initmydb. ora and modify the content as needed. For example, you must modify db_name = mydb.

NOTE: If init. if the ora file path is messy (both the slash and the backslash exist), copy and copy the file from C:/Oracle/product/10.1.0/bd_1/DBS. Or you can create and back up your own data.

3. Open the command line and set the environment variable oracle_sid.

Command: Set oracle_sid = mydb

4. Create an instance service (that is, control Windows Services in the background)

Command: oradim-New-Sid mydb

Oradim is the tool program name for creating an instance.-New indicates that the new instance is executed,-delete indicates that the instance is deleted, and-Sid indicates the Instance name.

After the command is executed, you can verify whether the service is successfully created.

5. Create a password file

Command: orapwd file = C:/Oracle/product/10.1.0/db_1/database/pwdmydb. ora Password = Oracle entries = 2

Orapwd is the program name 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. 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

:

 

7. Execute the database creation script

To execute the database creation script, you must first have the database creation script. How can I find the script? There are two methods to obtain a database creation script that meets your requirements. One is to use orcl to create the database creation script generated by dbca and refer to the Oracle document. Another method is to manually write a database creation script and save it as an SQL script with the name (*. SQL) and the name mydbcreate. SQL.

Create Database mydb

User SYS identified by Oracle

User system identified by Oracle

Logfile group 1 ('C:/Oracle/product/10.2.0/oradata/mydb/redo01.log') size 100 m,

Group 2 ('C:/Oracle/product/10.2.0/oradata/mydb/redo02.log ') size 100 m,

Group 3 ('C:/Oracle/product/10.2.0/oradata/mydb/redo03.log') size 100 m

Maxinstances 8

Maxloghistory 1

Maxlogfiles 16

Maxlogmembers 3

Maxdatafiles 100

Character Set al32utf8

National Character Set al16utf16

Datafile 'C:/Oracle/product/10.2.0/oradata/mydb/system01.dbf' size 325 m Reuse

Extent management local

Sysaux datafile 'C:/Oracle/product/10.2.0/oradata/mydb/sysaux01.dbf' size 325 m Reuse

Default tablespace tbs_1

Default temporary tablespace temp

Tempfile 'C:/Oracle/product/10.2.0/oradata/mydb/temp01.dbf' size 20480 K reuse autoextend on next 640 K maxsize Unlimited

Undo tablespace undotbs1

Datafile 'C:/Oracle/product/10.2.0/oradata/mydb/undotbs01.dbf' size 200 m reuse autoextend on maxsize unlimited;

 

There are two methods to execute the code above. One is to copy and paste the Code directly into sqlplus for execution, and the other is to execute the code using the @ command. After completing step 7, the database file has been successfully created, but no data dictionary has been created.

(Before step 1, run the SQL. bsq script, which is in the D:/Oracle/product/10.2.0/db_1/rdbms/admin folder to create the base table of the Oracle Data dictionary.
Automatically called during Database Operation)

8. Execute catalog to create a data dictionary.

SQL> @ C:/Oracle/product/10.1.0/db_1/rdbms/admin/CATALOG. SQL

9. Run catproc to create a package.

SQL> @ C:/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 Oracle (participate in the database creation code ).

SQL> connect system/Oracle

SQL> @ C:/Oracle/product/10.1.0/db_1/sqlplus/admin/pupbld. SQL

11. Create a spfile by pfile

SQL> Create spfile from pfile

12. Execute the Scott script to create the Scott mode (if you need to practice the environment)

SQL> @ C:/Oracle/product/10.1.0/db_1/rdbms/admin/Scott. SQL

13. Open the database to a normal state

SQL> alter database open;

OK. The database is created manually! Learn more about the Oracle creation process and understand the architecture of oracle.

ORA-1092 signalled during: Create Database mydb

Reprinted from:

Http://blog.csdn.net/akrem513/article/details/6527882