手工创建数据库DATABASE

参考联机文档：

Administrator's Guide(HEML)->2 Creating an Oracle Database->

Manually Creating an Oracle Database- >Planning for Database Creation

总体步骤：
Step 1: Decide on Your Instance Identifier (SID)

Step 2: Establish the Database Administrator Authentication Method

Step 3: Create the Initialization Parameter File

Step 4: Connect to the Instance

Step 5: Create a Server Parameter File (Recommended)

Step 6: Start the Instance

Step 7: Issue the CREATE DATABASE Statement

Step 8: Create Additional Tablespaces

Step 9: Run Scripts to Build Data Dictionary Views

Step 10: Run Scripts to Install Additional Options (Optional)

Step 11: Back Up the Database.

 

准备工作：
1、Initialization Parameter File
2、File for Seting OS environment
3、CREATE DATABASE Statement
4、Password file
5、Create Additional Tablespaces Statement
6、Scripts to Build Data Dictionary Views

 

实例演示：
1、创建初始化参数文件

[oracle@localhost ~]$ cd /u01/oracle/dbs/
[oracle@localhost dbs]$ strings spfiledenver.ora > initzhmg.ora
或者create pfile=/u01/oracle/dbs/initzhmg.ora from /u01/oracle/dbs/spfiledenver.ora
其中：spfiledenver.ora为已有的spfile，可以从其他地方得到。

 

下面是按照初始化参数文件中的要求创建相应的目录
[oracle@localhost u01]$ cd oradata/
[oracle@localhost oradata]$ mkdir zhmg
[oracle@localhost oradata]$ cd zhmg/
[oracle@localhost zhmg]$ mkdir archive
[oracle@localhost admin]$ cd zhmg/
[oracle@localhost zhmg]$ mkdir bdump cdump create pfile udump
[oracle@localhost zhmg]$ ll
total 20
drwxr-xr-x 2 oracle oinstall 4096 Jun 19 23:00 bdump
drwxr-xr-x 2 oracle oinstall 4096 Jun 19 23:00 cdump
drwxr-xr-x 2 oracle oinstall 4096 Jun 19 23:00 create
drwxr-xr-x 2 oracle oinstall 4096 Jun 19 23:00 pfile
drwxr-xr-x 2 oracle oinstall 4096 Jun 19 23:00 udump

 

2、创建设置环境变量的文件
[oracle@localhost zhmg]$ cd
[oracle@localhost ~]$ cd /u01/oracle/dbs/
[oracle@localhost dbs]$ mkdir utils
[oracle@localhost dbs]$ cd utils/
[oracle@localhost utils]$
[oracle@localhost utils]$
[oracle@localhost utils]$ vi setOS.env

RACLE_BASE=/u01
ORALCE_HOME=$ORACLE_BASE/oracle
ORACLE_SID=zhmg
ORACLE_NLS33=ORACLE_HOME$/ocommon/admin/data
PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

export ORACLE_BASE ORACLE_HOME ORACLE_SID ORACLE_NLS33 PATH LD_LIBRARY_PATH

 

3、创建数据的sql语句
[oracle@localhost utils]$ vi createdbsql.sql
spool createdb.log;

CREATE DATABASE "zhmg"
MAXDATAFILES 500
MAXINSTANCES 8
MAXLOGFILES 32
CHARACTER SET "UTF8"
NATIONAL CHARACTER SET AL16UTF16
ARCHIVELOG
DATAFILE '/u01/oradata/zhmg/system01.dbf' size 300M

EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE

'/u01/oradata/zhmg/tempts01.dbf' size 100M EXTENT MANAGEMENT LOCAL
UNDO TABLESPACE "UNDOTBS1"
DATAFILE '/u01/oradata/zhmg/undotbs01.dbf' SIZE 200M
LOGFILE
GROUP 1 (
'/u01/oradata/zhmg/redo01a.rdo',
'/u01/oradata/zhmg/redo01b.rdo'
) SIZE 100M,
GROUP 2 (
'/u01/oradata/zhmg/redo02a.rdo',
'/u01/oradata/zhmg/redo02b.rdo'
) SIZE 100M,
GROUP 3 (
'/u01/oradata/zhmg/redo03a.rdo',
'/u01/oradata/zhmg/redo03b.rdo'
) SIZE 100M
;
spool off;

 

4、创建口令文件
[oracle@localhost dbs]$ orapwd file=orapwzhmg password=123456 entries=10

 

5、执行设置环境变量文件
[oracle@localhost utils]$ . ./setOS.env
[oracle@localhost utils]$ env |grep ORA
ORACLE_NLS33=ORACLE_HOME$/ocommon/admin/data
ORACLE_SID=zhmg
ORACLE_BASE=/u01
ORACLE_HOME=/u01/oracle

 

6、以nomount方式启动实例
[oracle@localhost dbs]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Jun 19 23:09:54 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes

 

7、执行创建数据的sql语句

SQL> @/u01/oracle/dbs/utils/createdbsql.txt;

Database created.

SQL> select * from dual;

D
-
X

SQL> create table zhmg(id integer,name char(10));

Table created.

SQL> insert into zhmg values(1,'zhmg');

1 row created.

SQL> select * from zhmg;

ID NAME
---------- ----------
1 zhmg

 

8、创建表空间
SQL> create tablespace zhmgtablespace datafile

'/u01/oradata/zhmgtbs.dbf' size 100M extent management local;

Tablespace created.

 

9、建立数据字典
SQL> spool testlog1.log
SQL> @?/rdbms/admin/catalog.sql;
             ............
PL/SQL procedure successfully completed.

SQL> spool testlog2.log
SQL> @?/rdbms/admin/catproc.sql;

             ............

PL/SQL procedure successfully completed.
SQL> spool testlog3.log
SQL> @?/sqlplus/admin/pupbld.sql;

             ............
SQL> desc v$parameter;
Name Null? Type
----------------------------------------- -------- ---------------
NUM NUMBER
NAME VARCHAR2(64)
TYPE NUMBER
VALUE VARCHAR2(512)
ISDEFAULT VARCHAR2(9)
ISSES_MODIFIABLE VARCHAR2(5)
ISSYS_MODIFIABLE VARCHAR2(9)
ISMODIFIED VARCHAR2(10)
ISADJUSTED VARCHAR2(5)
DESCRIPTION VARCHAR2(64)
UPDATE_COMMENT VARCHAR2(255)

SQL> show parameter spfile;

NAME                                 TYPE         VALUE
------------------------------ ----------- --------------------
spfile                                  string