手工建立数据库具体步骤

一建立存放数据库文件的目录和日志相关的文件目录

分别在$ORACLE_BASE\oradata 和 $ORACLE_BASE\admin目录下建立NEWDB目录

使用命令查看目录结构

[oracle@wang NEWDB]$ pwd

/u01/app/admin/NEWDB

[oracle@wang NEWDB]$ pwd

/u01/app/oradata/NEWDB

二 确定ORACLE实例名

[oracle@wang NEWDB]$ export ORACLE_SID=NEWDB

三 创建初始化参数文件

1 创建参数文件

vi $ORACLE_BASE/dbs/initNEWDB.ora

control_files              = (/u01/app/oradata/NEWDB/control01.dbf,

                              /u01/app/oradata/NEWDB/control02.dbf,

                              /u01/app/oradata/NEWDB/control03.dbf)

db_name                    = NEWDB

db_domain                  = NEWDB.oracle.com

log_archive_dest_1         = "LOCATION=/u01/app/admin/NEWDB/arch"

log_archive_dest_state_1   = enable

db_block_size              = 8192

pga_aggregate_target       = 90M

processes                  = 100

sessions                   = 120

open_cursors               = 200

undo_management            = AUTO

undo_tablespace            = undotbs

compatible                 = 10.2.0

sga_target                 = 270M

nls_language               = AMERICAN

nls_territory              = AMERICA

db_recovery_file_dest      = /u01/app/admin/NEWDB/flashback

db_recovery_file_dest_size = 1G

audit_dump_dest            = /u01/app/admin/NEWDB/adump

core_dump_dest             = /u01/app/admin/NEWDB/cdump

background_dump_dest       = /u01/app/admin/NEWDB/bdump

 

2 创建spfile参数文件

[oracle@wang dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 23 14:37:08 2014

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> create spfile from pfile;

 

File created.

 

四 创建数据库

1 startup nomount;

2 编写创建数据库脚本

vi temp

CREATE DATABASE NEWDB

   USER SYS IDENTIFIED BY oracle

   USER SYSTEM IDENTIFIED BY oracle

   LOGFILE GROUP 1 ('/u01/app/oradata/NEWDB/redo01.log') SIZE 50M,

           GROUP 2 ('/u01/app/oradata/NEWDB//redo02.log') SIZE 50M,

           GROUP 3 ('/u01/app/oradata/NEWDB/redo03.log') SIZE 50M

   MAXLOGFILES 5

   MAXLOGMEMBERS 5

   MAXLOGHISTORY 1

   MAXDATAFILES 100

   MAXINSTANCES 1

   CHARACTER SET US7ASCII

   NATIONAL CHARACTER SET AL16UTF16

   DATAFILE '/u01/app/oradata/NEWDB/system01.dbf' SIZE 325M REUSE

   EXTENT MANAGEMENT LOCAL

   SYSAUX DATAFILE '/u01/app/oradata/NEWDB//sysaux01.dbf' SIZE 325M REUSE

   DEFAULT TEMPORARY TABLESPACE tempts1

      TEMPFILE '/u01/app/oradata/NEWDB/temp01.dbf'

      SIZE 20M REUSE

   UNDO TABLESPACE undotbs

      DATAFILE '/u01/app/oradata/NEWDB/undotbs01.dbf'

      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

3执行脚本

SQL> CREATE DATABASE NEWDB

   USER SYS IDENTIFIED BY oracle

   USER SYSTEM IDENTIFIED BY oracle

   LOGFILE GROUP 1 ('/u01/app/oradata/NEWDB/redo01.log') SIZE 50M,

           GROUP 2 ('/u01/app/oradata/NEWDB//redo02.log') SIZE 50M,

           GROUP 3 ('/u01/app/oradata/NEWDB/redo03.log') SIZE 50M

   MAXLOGFILES 5

   MAXLOGMEMBERS 5

   MAXLOGHISTORY 1

   MAXDATAFILES 100

   MAXINSTANCES 1

   CHARACTER SET US7ASCII

   NATIONAL CHARACTER SET AL16UTF16

   DATAFILE '/u01/app/oradata/NEWDB/system01.dbf' SIZE 325M REUSE

   EXTENT MANAGEMENT LOCAL

   SYSAUX DATAFILE '/u01/app/oradata/NEWDB//sysaux01.dbf' SIZE 325M REUSE

   DEFAULT TEMPORARY TABLESPACE tempts1

      TEMPFILE '/u01/app/oradata/NEWDB/temp01.dbf'

      SIZE 20M REUSE

   UNDO TABLESPACE undotbs

      DATAFILE '/u01/app/oradata/NEWDB/undotbs01.dbf'

      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22 

 

Database created.

五 创建数据字典

SQL>@?/rdbms/admin/catalog.sql

…

View created.

 

 

Synonym created.

 

 

Grant succeeded.

 

 

View created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Synonym created.

 

 

Grant succeeded.

 

 

Grant succeeded.

 

 

View created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Synonym created.

 

 

Grant succeeded.

 

 

View created.

 

 

Synonym created.

 

 

Grant succeeded.

 

 

Commit complete.

 

 

View created.

 

 

Synonym created.

 

 

Grant succeeded.

 

 

View created.

 

 

Synonym created.

 

 

Grant succeeded.

 

 

View created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Comment created.

 

 

Synonym created.

 

 

Grant succeeded.

 

 

PL/SQL procedure successfully completed.

 

SQL> @?/rdbms/admin/catproc.sql

…

Synonym created.

 

 

Grant succeeded.

 

 

Library created.

 

 

Package created.

 

 

Package body created.

 

 

Package created.

 

 

Package body created.

 

 

Library created.

 

 

Function created.

 

 

PL/SQL procedure successfully completed.

 

 

PL/SQL procedure successfully completed.

 

 

Package body created.

 

No errors.

 

Package body created.

 

 

PL/SQL procedure successfully completed.

 

 

SQL> @?/rdbms/admin/catblock.sql

……

View created.

 

 

Synonym created.

 

 

Grant succeeded.

 

SQL> connect system/oracle

Connected.

SQL> @?/sqlplus/admin/pupbld.sql

 

……

Synonym created.

 

DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE

                    *

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

 

 

 

Synonym created.

 

建立help表并插入help的数据,非常值得参考

SQL> @?/sqlplus/admin/help/hlpbld.sql helpus.sql

……

View created.

 

 

58 rows created.

 

 

Commit complete.

手动建库的时候执行到最后，需要使用system用户执行SQL脚本  ，但在执行hlpbld.sql的时候要求输入参数，
  @?/sqlplus/admin/help/hlpbld.sql
?代表oracle home
实际就是创建了一张名为help的表，用于支持help命令，而hlpbld.sql本身是没有插入数据的语句的，所以需要传递一个参数，而这个参数实际上就是插入help数据的脚本。将helpus.sql作为输入参数的命令 就是

 @?/sqlplus/admin/help/hlpbld.sql helpus.sql