手工创建Oracle数据库详解

阅读导航
数据库版本:
1.设置环境变量和创建目录
2.创建初始化参数文件
3.启动实例到nomount状态创建数据库
4.运行脚本创建字典、视图、包、角色、权限、同义词
5.检查数据库相关信息
6.启用归档日志数据库版本: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 - Production1.设置环境变量和创建目录$ 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/logfile2.创建初始化参数文件$ 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.dbf4.运行脚本创建字典、视图、包、角色、权限、同义词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.sql6.启用归档日志$ 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更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址