手工创建数据库的完整步骤-CSDN博客

手工建立数据库具体步骤

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

分别在$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

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.

Synonym created.

Grant succeeded.

View 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.

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.

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作为输入参数的命令就是