oracle创建数据库 linux/oracle10g

部分转载：http://blog.csdn.net/robinson_0612/article/details/5478362

oracle创建数据库

操作环境： linux suse 10上oracle10g

Oracle提供了DBCA来创建数据库，对于初学者来说使用DBCA创建数据库简化了很多工作和设置，直接在交互界面即可实现所有的功能。然而对于实际的生产数据库来说，使用DBCA来创建不切实际，建议按实际需求规划来创建数据库。Oracle数据库的创建不像SQL server，直接使用CREATE DATABASE DB_NAME(仅作临时，演示用)一条语句即可实现。不管是SQL还是Oracle，对于创建生产型数据库都需要进行需求分析、规划、创建等步骤。



一、规划数据库

1.创建数据库的目的(高可用性、并发性、数据装载)
2.数据库的应用类型(OLAP,OLTP)
3.数据库存储结构的设计
4.数据库的名称、字符集
5.db_block 块的大小
6.数据库容量的初始大小及增幅



二、建库前需要确认的问题(创建后不可修改)

1.数据库字符集(使用ZHS16GBK,支持中文不乱码)
2.数据库的名称(SID)
3.数据块的大小

以下是可调整的设置，建议提前确定

1.SGA大小sga_max_size
2.日志缓冲区大小log_buffer
3.最大允许进程数

其他注意事项

1.文件存储方式(文件系统/RAW/ASM)
2.数据文件、日志文件大小、存储位置
3.表空间的构成



三、建库的几种方法

1.通过OUI安装软件后自动调用DBCA来创建
2.手动调用DBCA创建(图型化界面，跨平台)
3.手动执行命令创建

可以使用dbca配置创建数据库脚本，然后再执行。

四、使用DBCA创建数据库
1.打开Linux中的命令行模
2.在root帐户下执行xhost +
3.切换到oracle 帐户，执行DBCA
4.按提示操作完毕

生成的建库脚本...

mydb.sh  emRepository.sql
CreateDB.sql CreateDBCatalog.sql CreateDBFiles.sql emRepository.log init.ora mydb.sql postDBCreation.sql

vi mydb.sh

#!/bin/sh

mkdir -p /home/oracle/admin/mydb/adump
mkdir -p /home/oracle/admin/mydb/bdump
mkdir -p /home/oracle/admin/mydb/cdump
mkdir -p /home/oracle/admin/mydb/dpdump
mkdir -p /home/oracle/admin/mydb/pfile
mkdir -p /home/oracle/admin/mydb/udump
mkdir -p /home/oracle/flash_recovery_area
mkdir -p /home/oracle/oradata/mydb
mkdir -p /home/oracle/product/10.2/db_1/cfgtoollogs/dbca/mydb
mkdir -p /home/oracle/product/10.2/db_1/dbs
ORACLE_SID=mydb; export ORACLE_SID
echo You should Add this entry in the /etc/oratab: mydb:/home/oracle/product/10.2/db_1:Y
/home/oracle/product/10.2/db_1/bin/sqlplus /nolog @/home/oracle/admin/mydb/scripts/mydb.sql

vi mydb.sql

set verify off
PROMPT specify a password for sys as parameter 1;
DEFINE sysPassword = &1
PROMPT specify a password for system as parameter 2;
DEFINE systemPassword = &2
PROMPT specify a password for sysman as parameter 3;
DEFINE sysmanPassword = &3
PROMPT specify a password for dbsnmp as parameter 4;
DEFINE dbsnmpPassword = &4
host /home/oracle/product/10.2/db_1/bin/orapwd file=/home/oracle/product/10.2/db_1/dbs/orapwmydb password=&&sysPassword force=y
@/home/oracle/admin/mydb/scripts/CreateDB.sql
@/home/oracle/admin/mydb/scripts/CreateDBFiles.sql
@/home/oracle/admin/mydb/scripts/CreateDBCatalog.sql
@/home/oracle/admin/mydb/scripts/emRepository.sql
@/home/oracle/admin/mydb/scripts/postDBCreation.sql

vi CreateDB.sql

connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /home/oracle/admin/mydb/scripts/CreateDB.log
startup nomount pfile="/home/oracle/admin/mydb/scripts/init.ora";
CREATE DATABASE "mydb"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/home/oracle/oradata/mydb/system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/home/oracle/oradata/mydb/sysaux01.dbf' SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/home/oracle/oradata/mydb/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/home/oracle/oradata/mydb/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/home/oracle/oradata/mydb/redo01.log') SIZE 51200K,
GROUP 2 ('/home/oracle/oradata/mydb/redo02.log') SIZE 51200K,
GROUP 3 ('/home/oracle/oradata/mydb/redo03.log') SIZE 51200K
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";
spool off

vi CreateDBFiles.sql

connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /home/oracle/admin/mydb/scripts/CreateDBFiles.log
CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/home/oracle/oradata/mydb/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
ALTER DATABASE DEFAULT TABLESPACE "USERS";
spool off

vi CreateDBCatalog.sql

connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /home/oracle/admin/mydb/scripts/CreateDBCatalog.log
@/home/oracle/product/10.2/db_1/rdbms/admin/catalog.sql;
@/home/oracle/product/10.2/db_1/rdbms/admin/catblock.sql;
@/home/oracle/product/10.2/db_1/rdbms/admin/catproc.sql;
@/home/oracle/product/10.2/db_1/rdbms/admin/catoctk.sql;
@/home/oracle/product/10.2/db_1/rdbms/admin/owminst.plb;
connect "SYSTEM"/"&&systemPassword"
@/home/oracle/product/10.2/db_1/sqlplus/admin/pupbld.sql;
connect "SYSTEM"/"&&systemPassword"
set echo on
spool /home/oracle/admin/mydb/scripts/sqlPlusHelp.log
@/home/oracle/product/10.2/db_1/sqlplus/admin/help/hlpbld.sql helpus.sql;
spool off
spool off

vi emRepository.sql

connect "SYS"/"&&sysPassword" as SYSDBA
set echo off
spool /home/oracle/admin/mydb/scripts/emRepository.log
@/home/oracle/product/10.2/db_1/sysman/admin/emdrep/sql/emreposcre /home/oracle/product/10.2/db_1 SYSMAN &&sysmanPassword TEMP ON;
WHENEVER SQLERROR CONTINUE;
spool off

vi postDBCreation.sql

connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /home/oracle/admin/mydb/scripts/postDBCreation.log
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
create spfile='/home/oracle/product/10.2/db_1/dbs/spfilemydb.ora' FROM pfile='/home/oracle/admin/mydb/scripts/init.ora';
shutdown immediate;
connect "SYS"/"&&sysPassword" as SYSDBA
startup ;
alter user SYSMAN identified by "&&sysmanPassword" account unlock;
alter user DBSNMP identified by "&&dbsnmpPassword" account unlock;
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute utl_recomp.recomp_serial();
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;
host /home/oracle/product/10.2/db_1/bin/emca -config dbcontrol db -silent -DB_UNIQUE_NAME mydb -PORT 1522 -EM_HOME /home/oracle/product/10.2/db_1 -LISTENER LIST2 -SERVICE_NAME mydb -SYS_PWD &&sysPassword -SID mydb -ORACLE_HOME /home/oracle/product/10.2/db_1 -DBSNMP_PWD &&dbsnmpPassword -HOST linux-wc79.site -LISTENER_OH /home/oracle/product/10.2/db_1 -LOG_FILE /home/oracle/admin/mydb/scripts/emConfig.log -SYSMAN_PWD &&sysmanPassword;
spool /home/oracle/admin/mydb/scripts/postDBCreation.log
exit;