环境:
OS : Oracle-R5-U6-Server-x86_64
DB:Oracle11.2.0.4
安装Linux操作系统步骤及安装Oracle软件步骤此处省略,可参考本站相关文章。
手工建库参数目标:
1、数据库名称:OL64;
2、SYS/SYSTEM,口令:oracle;
3、三组日志文件,每组日志一个成员,日志大小20M;
4、2个控制文件;
5、启动快速恢复区,位置:/u01/app/oracle/flash 大小:1G;
6、内存自动管理,800M;
7、字符集:UTF8。
一、确认ORACLE_SID并生成密码文件;
[oracle@OL64 ~]$ echo $ORACLE_SID OL64 [oracle@OL64 ~]$ cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1 export ORACLE_SID=OL64 export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin [oracle@OL64 ~]$ cd $ORACLE_HOME/dbs [oracle@OL64 dbs]$ pwd /u01/app/oracle/product/11.2.0/db_1/dbs [oracle@OL64 dbs]$ ls init.ora [oracle@OL64 dbs]$ orapwd file=orapwOL64 password=oracle [oracle@OL64 dbs]$ ls init.ora orapwOL64 [oracle@OL64 dbs]$
二、生成pfile参数文件;
[oracle@OL64 dbs]$ pwd /u01/app/oracle/product/11.2.0/db_1/dbs [oracle@OL64 dbs]$ ls init.ora orapwOL64 [oracle@OL64 dbs]$ cat init.ora |grep -v ^#|grep -v ^$ > initOL64.ora [oracle@OL64 dbs]$ ls initOL64.ora init.ora orapwOL64 [oracle@OL64 dbs]$ vi initOL64.ora
需修改参数如下:
db_name='OL64'
memory_target=800M
processes = 150
--注意要校验此路径,在下一步创建目录时,要相同
audit_file_dest='/u01/app/oracle/admin/prod/adump'
#audit_trail ='db'
--把审计的参数注释掉,去掉这个功能,DBCA建库,默认情况下是启用的。
db_block_size=8192
db_domain=''
--启用快速恢复区,并给出路径
db_recovery_file_dest='/u01/app/oracle/flash'
--如果需要开启的话,随时可以通过修改参数来完成。
db_recovery_file_dest_size=1G
--注释掉诊断的参数,数据库创建好后,这个参数会自动启用
#diagnostic_dest='<ORACLE_BASE>'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
--undo表空间名称,在创建数据库时名称要匹配上
undo_tablespace='UNDOTBS1'
--修改控制文件的名字及位置
control_files = (/u01/app/oracle/oradata/OL64/ora_contro1.ctl,/u01/app/oracle/oradata/OL64/ora_contro2.ctl)
compatible ='11.2.0'
修改完成参数:
db_name='OL64' memory_target=800M processes = 150 audit_file_dest='/u01/app/oracle/admin/OL64/adump' audit_trail ='db' db_block_size=8192 db_domain='' db_recovery_file_dest='/u01/app/oracle/flash' db_recovery_file_dest_size=1G #diagnostic_dest='<ORACLE_BASE>' dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)' open_cursors=300 remote_login_passwordfile='EXCLUSIVE' undo_tablespace='UNDOTBS1' control_files = (/u01/app/oracle/oradata/OL64/ora_control1,/u01/app/oracle/oradata/OL64/ora_control2) compatible ='11.2.0'
保存参数,并创建相应路径:
[oracle@OL64 ~]$ mkdir -p /u01/app/oracle/admin/OL64/adump [oracle@OL64 ~]$ mkdir -p /u01/app/oracle/flash [oracle@OL64 ~]$ mkdir -p /u01/app/oracle/oradata/OL64/ [oracle@OL64 ~]$
三、通过pfile生成spfile,并成功启动数据库至nomount状态;
[oracle@OL64 dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 11 11:16:01 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile; File created. SQL> startup nomount ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2257840 bytes Variable Size 541068368 bytes Database Buffers 289406976 bytes Redo Buffers 2371584 bytes SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@OL64 dbs]$ ls hc_OL64.dat initOL64.ora init.ora orapwOL64 spfileOL64.ora [oracle@OL64 dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 11 11:16:54 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select status from v$instance; STATUS ------------ STARTED SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0 /db_1/dbs/spfileOL64.ora SQL>
四、编写Oracle创建数据库脚本,创建数据库;
脚本来源ORALCE11G官方文档:
Master Book List-->Administrator’s Guide->2 Creating and Configuring an Oracle Database->Step 9: Issue the CREATE DATABASE Statement
官方给出的创建脚本模板如下:
CREATE DATABASE mynewdb USER SYS IDENTIFIED BY sys_password USER SYSTEM IDENTIFIED BY system_password LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log') SIZE 100M BLOCKSIZE 512, GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log') SIZE 100M BLOCKSIZE 512, GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log') SIZE 100M BLOCKSIZE 512 MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16 EXTENT MANAGEMENT LOCAL DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE DEFAULT TABLESPACE users DATAFILE '/u01/app/oracle/oradata/mynewdb/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01.dbf' SIZE 20M REUSE UNDO TABLESPACE undotbs DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; 按照实际需求修改参数: CREATE DATABASE OL64 USER SYS IDENTIFIED BY oracle USER SYSTEM IDENTIFIED BY oracle LOGFILE GROUP 1 ('/u01/app/oracle/oradata/OL64/redo01a.log') SIZE 20M BLOCKSIZE 512, GROUP 2 ('/u01/app/oracle/oradata/OL64/redo02a.log') SIZE 20M BLOCKSIZE 512, GROUP 3 ('/u01/app/oracle/oradata/OL64/redo03a.log') SIZE 20M BLOCKSIZE 512 MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 CHARACTER SET US7ASCII NATIONAL CHARACTER SET UTF8 EXTENT MANAGEMENT LOCAL DATAFILE '/u01/app/oracle/oradata/OL64/system01.dbf' SIZE 325M REUSE SYSAUX DATAFILE '/u01/app/oracle/oradata/OL64/sysaux01.dbf' SIZE 325M REUSE DEFAULT TABLESPACE users DATAFILE '/u01/app/oracle/oradata/OL64/users01.dbf' SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u01/app/oracle/oradata/OL64/temp01.dbf' SIZE 20M REUSE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/OL64/undotbs01.dbf' SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
[oracle@OL64 ~]$ mkdir -p /u01/app/oracle/oradata/OL64 [oracle@OL64 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 11 13:40:41 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select status from v$instance; STATUS ------------ STARTED SQL> CREATE DATABASE OL64 USER SYS IDENTIFIED BY oracle USER SYSTEM IDENTIFIED BY oracle LOGFILE GROUP 1 ('/u01/app/oracle/oradata/OL64/redo01a.log') SIZE 20M BLOCKSIZE 512, GROUP 2 ('/u01/app/oracle/oradata/OL64/redo02a.log') SIZE 20M BLOCK 2 3 4 5 SIZ E 512, GROUP 3 ('/u01/app/oracle/oradata/OL64/redo03a.log') SIZE 20M BLOCKSIZE 512 MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 CHARACTER SET US7ASCII NATIONAL CHARACTER SET UTF8 EXTENT MANAGEMENT L 6 7 8 9 10 11 12 13 OCAL DATAFILE '/u01/app/oracle/oradata/OL64/system01.dbf' SIZE 325M REUSE SYSAUX DATAFILE '/u01/app/oracle/oradata/OL64/sysaux01.dbf' SIZE 325M REUSE DEFAULT TABLESPACE users DATAFILE '/u01/app/oracle/oradata/OL64/users01.dbf' SIZE 50M 14 15 16 17 18 REUSE AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u01/app/oracle/oradata/OL64/temp01.dbf' SIZE 20M REUSE UNDO TABLESPACE UNDOTBS1 19 20 21 22 DATAFILE '/u01/app/oracle/oradata/OL64/undotbs01.dbf' SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; 23 24 Database created. SQL> select status from v$instance; STATUS ------------ OPEN SQL> show parameter control_files; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /u01/app/oracle/oradata/OL64/o ra_control1, /u01/app/oracle/o radata/OL64/ora_control2 SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0 /db_1/dbs/spfileOL64.ora SQL>
五、创建字典表及工具包
--必执行脚本
SQL> conn / as sysdba
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> conn system/oracle
SQL> @?/sqlplus/admin/pupbld.sql
--可选脚本
SQL> conn / as sysdba
@?/rdbms/admin/catblock.sql
@?/rdbms/admin/catoctk.sql
@?/rdbms/admin/owminst.plb