Oracle手工创建数据库方法和步骤

1、设置ORACLE_SID

2、设置ORACL_HOME等环境变量信息

3、创建初始化参数文件

[oracle@test dbs]$ cat initmydb.ora
DB_NAME=mydb
DB_BLOCK_SIZE=8192
CONTROL_FILES=/opt/oradata/mydb/control01.ora,/opt/oradata/mydb/control02.ora
UNDO_TABLESPACE=undotbs
UNDO_MANAGEMENT=AUTO
SGA_TARGET=1G
PGA_AGGREGATE_TARGET=512M
LOG_BUFFER=5242880
DB_RECOVERY_FILE_DEST=/opt/oradata/mydb/recovery
DB_RECOVERY_FILE_DEST_SIZE=2G

初始化参数文件定义的目录若不存在则需要手工进行创建

[oracle@test dbs]$ mkdir -p /opt/oradata/mydb/
[oracle@test dbs]$ mkdir -p /opt/oradata/mydb/recovery
[oracle@test dbs]$

重要说明：若是Windows平台则在启动到NOMOUNT时需要先将实例注册成服务，命令如下

oradim -NEW -SID oracleSID -STARTMODE manual [oracleSID替换成您要注册的实例名称】

4、将数据库启动到NOMOUNT状态

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size            2260088 bytes
Variable Size          285213576 bytes
Database Buffers      775946240 bytes
Redo Buffers            5517312 bytes
SQL>

 

5、跑创建数据库脚本创建数据库

create database mydb
user sys identified by sys
user system identified by system
MAXLOGFILES 10
MAXLOGHISTORY 1
MAXDATAFILES 500
controlfile reuse
logfile
group 1 ('/opt/oradata/mydb/log01a.log') size 50M,
group 2 ('/opt/oradata/mydb/log02a.log') size 50M,
group 3 ('/opt/oradata/mydb/log03a.log') size 50M
datafile '/opt/oradata/mydb/system01.dbf' size 1G AUTOEXTEND ON MAXSIZE 10G
sysaux datafile '/opt/oradata/mydb/sysaux01.dbf' size 1G AUTOEXTEND ON MAXSIZE 10G
Undo tablespace undotbs
Datafile '/opt/oradata/mydb/undotbs01.dbf' size 500M
Default temporary tablespace temp tempfile
'/opt/oradata/mydb/temp01.dbf' size 75M
Extent management local
Character set US7ASCII;

创建完毕之后数据库自动置为读写状态

SQL> select dbid,name,open_mode from v$database;

      DBID NAME      OPEN_MODE
---------- --------- --------------------
2985466427 MYDB      READ WRITE

SQL>

 

6、创建动态参数文件【可选】

Create spfile from pfile;

7、创建其他表空间

create tablespace users datafile '/opt/oradata/mydb/user01.dbf' size 100M;

8、创建数据字典

$@$ORACLE_HOME/rdbms/admin/catalog.sql

$@$ORACLE_HOME/rdbms/admin/catproc.sql

两个脚本的作用

catalog.sql =>Creates dictionary tables and views
catproc.sql =>Creates PL/SQL procedures,functions and packages necessary.

此外还可以创建用户资料信息，需要以system用户登录到数据库，然后执行如下脚本【该步骤为可选操作，查阅相关文档发现为其实主要是针对SQLPLUS而已】

@$ORACLE_HOME/sqlplus/admin/pupbld.sql

 

9、检查是否有无效组件或对象

select comp_id,version,status from dba_registry;

select owner,object_name,object_type from all_objects where status<>'VALID';

若有无效组件则跑utlrp.sql脚本

该脚本位于ORACLE_HOME/rdbms/admin/utlrp.sql

10、备份数据库