手工创建Oracle数据库

数据库版本:

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

1.设置环境变量和创建目录

$ export ORACLE_HOME=/u01/app/product/11.2.0/db_1

$ export ORACLE_SID=mandb

$ mkdir -p /disk2/oradata/mandb/datafile

$ mkdir -p /disk2/oradata/mandb/logfile

2.创建初始化参数文件

$ vim /disk2/oradata/mandb/datafile/mandb.ora

db_name='mandb'  #database name

instance_name='mandb' 

control_files='/disk2/oradata/mandb/datafile/mandb_control01.ctl'

memory_target=500m

db_block_size=32768 

db_create_file_dest='/disk2/oradata/mandb/datafile'

db_block_size 指定创建的数据库默认块大小,如果不指定,默认为:8192,注:创建数据库后不能修改默认数据块大小
db_create_file_dest 在未指定数据文件路径时,数据文件默认创建位置

3.启动实例到nomount状态创建数据库

$ sqlplus / as sysdba

SQL> create spfile from pfile='/disk2/oradata/mandb/datafile/mandb.ora';

SQL> startup nomount

SQL> create database mandb

  2  user sys identified by oracle

  3  user system identified by oracle

  4   logfile group 1 '/disk2/oradata/mandb/logfile/redo01.log' size 50m blocksize 512,

  5            group 2 '/disk2/oradata/mandb/logfile/redo02.log' size 50m blocksize 512,

  6            group 3 '/disk2/oradata/mandb/logfile/redo03.log' size 50m blocksize 512

  7   maxlogfiles 10

  8   maxlogmembers 5

  9   maxloghistory 5

 10   maxdatafiles 200

 11   character set al32UTF8

 12   National character set al16UTF16

 13   extent management local

 14   datafile  '/disk2/oradata/mandb/datafile/system01.dbf' size 500m reuse

 15  sysaux 

 16    datafile '/disk2/oradata/mandb/datafile/sysaux01.dbf' size 500m reuse

 17   default tablespace users 

 18      datafile '/disk2/oradata/mandb/datafile/user01.dbf' size 100m reuse

 19   default temporary tablespace tempfile 

 20    tempfile '/disk2/oradata/mandb/datafile/temp01.dbf' size 20m autoextend on

 21   undo tablespace undotbs01 

 22   datafile '/disk2/oradata/mandb/datafile/undotbs01.dbf' size 20m autoextend on 

 23    maxsize unlimited; 

日志文件的块大小为:512bytes,可以设置1024bytes、4096bytes,控制文件的块大小为:16k;
注:如果中途出错或意外停止,要删除非指定reuse或不能指定reuse 的文件(如:控制文件、tempfile、undofile),然后在运行:

rm /disk2/oradata/mandb/datafile/mandb.ora

rm  /disk2/oradata/mandb/datafile/undotbs01.dbf

rm /disk2/oradata/mandb/datafile/undotbs01.dbf

4.运行脚本创建字典、视图、包、角色、权限、同义词

SQL>@?/rdbms/admin/catalog.sql  ####字典、视图、公共同义词、角色、权限

SQL>@?/rdbms/admin/catproc.sql  ####系统所需的pl/sql包

官方文档说还要登陆到system用户执行@?/sqlplus/admin/pupbld.sql脚本,但在安装的11.2.0.1.0版本上并未找到。至此数据库创建完毕;

5.检查数据库相关信息

SQL> column name format a10

SQL> select  DBID,name,DB_UNIQUE_NAME,CURRENT_SCN,open_mode from v$database; --数据库信息

      DBID NAME       DB_UNIQUE_NAME                 CURRENT_SCN OPEN_MODE

---------- ---------- ------------------------------ ----------- --------------------

3530583721 MANDB                                          385901 READ WRITE

SQL> column name format a50

SQL> select file#,name,block_size from v$datafile; --数据文件信息

     FILE# NAME                                               BLOCK_SIZE

---------- -------------------------------------------------- ----------

         1 /disk2/oradata/mandb/datafile/system01.dbf              32768

         2 /disk2/oradata/mandb/datafile/sysaux01.dbf              32768

         3 /disk2/oradata/mandb/datafile/undotbs01.dbf             32768

         4 /disk2/oradata/mandb/datafile/user01.dbf                32768

SQL> column member format a40

SQL> select * from v$Logfile; --日志文件信息

    GROUP# STATUS  TYPE    MEMBER                                   IS_

---------- ------- ------- ---------------------------------------- ---

         1         ONLINE  /disk2/oradata/mandb/logfile/redo01.log  NO

         2         ONLINE  /disk2/oradata/mandb/logfile/redo02.log  NO

         3         ONLINE  /disk2/oradata/mandb/logfile/redo03.log  NO

SQL> column name format a60

SQL> select * from v$controlfile;  --控制文件信息

STATUS  NAME                                                         IS_ BLOCK_SIZE FILE_SIZE_BLKS

------- ------------------------------------------------------------ --- ---------- --------------

        /disk2/oradata/mandb/datafile/mandb_control01.ctl            NO       16384            668

SQL> column window_name format a17

SQL>select WINDOW_NAME,WINDOW_NEXT_TIME,WINDOW_ACTIVE,AUTOTASK_STATUS from DBA_AUTOTASK_WINDOW_CLIENTS;  --窗口信息

WINDOW_NAME       WINDOW_NEXT_TIME                                                            WINDO AUTOTASK

----------------- --------------------------------------------------------------------------- ----- --------

WEDNESDAY_WINDOW  08-APR-15 10.00.00.000000 PM PRC                                            FALSE ENABLED

FRIDAY_WINDOW     10-APR-15 10.00.00.000000 PM PRC                                            FALSE ENABLED

TUESDAY_WINDOW    07-APR-15 10.00.00.000000 PM PRC                                            FALSE ENABLED

SATURDAY_WINDOW   11-APR-15 06.00.00.000000 AM PRC                                            FALSE ENABLED

SUNDAY_WINDOW     05-APR-15 06.00.00.000000 AM PRC                                            FALSE ENABLED

MONDAY_WINDOW     06-APR-15 10.00.00.000000 PM PRC                                            FALSE ENABLED

THURSDAY_WINDOW   09-APR-15 10.00.00.000000 PM PRC                                            FALSE ENABLED

检查是否有对象错误:

SQL>  select COUNT(*) "OBJECTS WITH ERRORS" from obj$ where status = 3;

如果有包、java code错误,可以通过oracle提供的utlrp.sql重新编译

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

6.启用归档日志

$ sqlplus / as sysdba <<EOF

alter system checkpoint;

alter system checkpoint;

alter system checkpoint;

shutdown immediate;

startup mount

alter database archivelog;

alter database open;

EOF

--The end