官方文档:
Administrator’s Guide 2 Creating and Configuring an Oracle Database
Creating a Database with the CREATE DATABASE Statement
1 设置修改环境变量
手工建库前,可先将当前系统中已有PROD数据库关闭,减少资源占用。
su - oracle
sqlplus / as sysdba
shutdown immediate;
修改.bash_profile中ORACLE_SID将原有ORACLEL_SID注释掉,添加一行ORACLE_SID=EMREP
vi ~/.bash_profile
export ORACLE_SID=EMREP
source ~/.bash_profile
echo $ORACLE_SID
2 创建目录,赋予权限
模拟控制文件、日志文件多元化(多路复用)
database可以使用目录/u01/app/oracle/oradata/EMREP/disk{1,2,3,4,5}
审计目录/u01/app/oracle/admin/EMREP/adump
cd /u01/app/oracle
mkdir -p oradata/EMREP/disk{1,2,3,4,5}
mkdir -p admin/EMREP/adump
3 创建密码文件
cd $ORACLE_HOME/dbs
orapwd file=orapwEMREP password=oracle entries=5 -----sys用户密码oracle
4 编写初始化参数文件
pwd
vi initEMREP.ora
db_name='EMREP'memory_target=500M
audit_file_dest='/u01/app/oracle/admin/EMREP/adump'
diagnostic_dest='/u01/app/oracle'
undo_tablespace='UNDOTBS1'
control_files = ('/u01/app/oracle/oradata/EMREP/disk1/control01.ctl',
'/u01/app/oracle/oradata/EMREP/disk2/control01.ctl',
'/u01/app/oracle/oradata/EMREP/disk3/control01.ctl')
5 创建spifle,把库启到nomount状态
sqlplus / as sysdba
startup nomount pfile=initEMREP.ora
create spfile from pfile;
startup force nomount;
参数核对
select status from v$instance;
show parameter name;
show parameter control_files;
show parameter dump;
show parameter diag;
show parameter spfile;
show parameter undo;
6 编写建库脚本,执行建库语句
sys/system用户密码oracle,字符集可以使用zhs16gbk注意控制文件、日志文件多元化,注意控制文件名、undo表空间名与初始化参数匹配undotbs1
建议创建特定目录存放相应脚本
pwd
mkdir script
cd script
vi cr_db.sql
按顺序修改:数据库名、sys/system密码、日志文件路径、
字符集、数据文件路径、临时表空间名(可选)、undo表空间名(必须一致)
CREATE DATABASE EMREP USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/EMREP/disk1/redo01.log',
'/u01/app/oracle/oradata/EMREP/disk2/redo01.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/u01/app/oracle/oradata/EMREP/disk1/redo02.log',
'/u01/app/oracle/oradata/EMREP/disk2/redo02.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/u01/app/oracle/oradata/EMREP/disk1/redo03.log',
'/u01/app/oracle/oradata/EMREP/disk2/redo03.log') SIZE 100M BLOCKSIZE 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/EMREP/disk3/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/EMREP/disk3/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/EMREP/disk4/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/EMREP/disk4/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/EMREP/disk5/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
日志文件路径快捷替
:%s+/u01/logs/my+/u01/app/oracle/oradata/EMREP/disk1
:%s+/u02/logs/my+/u01/app/oracle/oradata/EMREP/disk2
换行对齐便于检查,abc字母也可以删除,
数据文件路径快捷替换
:%s+mynewdb+EMREP/disk3
7 执行建库脚本
可以新开session查看alert日志
[oracle@oracle ~]$tail -f /u01/app/oracle/diag/rdbms/emrep/EMREP/trace/alert_EMREP.log
nomount状态下执行建库脚本
@/home/oracle/script/cr_db.sql
select status from v$instance;
8 执行3个脚本
创建必要的数据字典视图
@?/rdbms/admin/catalog.sql
创建必要的PL/SQL包、存储过程
@?/rdbms/admin/catproc.sql
创建sqlplus需要的表
第三个脚本要使用system用户执行!!
conn system/oracle
@?/sqlplus/admin/pupbld.sql
可以创建scott用户方案测试
conn / as sysdba
@?/rdbms/admin/utlsampl.sql