Oracle 11g 手动建库，在虚拟环境中，不使用DBCA工具进行创建数据库
1.Linux环境的基本配置
2.ip 10.11.30.60
3.Oracle 11g安装过程
------------------------------------Linux环境配置---------------------------------------
1.硬件配置
[root@gzxbi01 ~]# free -g total used free shared buffers cached Mem: 3 1 2 0 0 1 -/+ buffers/cache: 0 3 Swap: 17 0 17 [root@gzxbi01 ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 34G 8.2G 24G 26% / tmpfs 1.9G 84K 1.9G 1% /dev/shm /dev/sda1 477M 33M 419M 8% /boot /dev/sda5 15G 37M 14G 1% /u01 /dev/sda6 33G 48M 32G 1% /u01/oradata
[root@gzxbi01 ~]# grep "model name" /proc/cpuinfo
model name : QEMU Virtual CPU version (cpu64-rhel6)
model name : QEMU Virtual CPU version (cpu64-rhel6)
model name : QEMU Virtual CPU version (cpu64-rhel6)
model name : QEMU Virtual CPU version (cpu64-rhel6)
2.软件
[root@gzxbi01 ~]# ll /data/download/ total 2295604 drwxr-xr-x 8 root root 4096 Aug 21 2009 database -rw-rw-r-- 1 eson eson 1239269270 Nov 29 08:55 linux.x64_11gR2_database_1of2.zip -rw-rw-r-- 1 eson eson 1111416131 Nov 29 08:55 linux.x64_11gR2_database_2of2.zip
3.配置过程
[root@gzxbi01 ~]# yum -y install epel-release
[root@gzxbi01 ~]# yum -y install binutils compat-libstdc++ gcc gcc-c++ glibc glibc-devel ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel libXext libXtst libX11 libXau libXi make sysstat compat-libstdc*
[root@gzxbi01 ~]# groupadd dba
[root@gzxbi01 ~]# groupadd oinstall
[root@gzxbi01 ~]# useradd -g oinstall -G dba oracle
[root@gzxbi01 ~]# passwd oracle
[root@gzxbi01 ~]# mkdir -p /u01/app/oracle
[root@gzxbi01 ~]# chown -R oracle:oinstall /u01/app/oracle
[root@gzxbi01 ~]# chmod -R 775 /u01/app/oracle
[root@gzxbi01 ~]# sed -i 's/SELINUX=.*/SELINUX=disabled/' /etc/selinux/config
#在sysctl.conf中添加对应的内核参数
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
net.bridge.bridge-nf-call-ip6tables = 0
net.bridge.bridge-nf-call-iptables = 0
net.bridge.bridge-nf-call-arptables = 0
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmall = 2097152
kernel.shmmni=4096
net.core.rmem_default = 262144
net.core.wmem_default = 4194304
kernel.shmmax = 536870912
kernel.sem = 250 32000 100 128
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
[root@gzxbi01 ~]# sysctl -p
[root@gzxbi01 ~]# vim /etc/security/limits.conf
oracle soft nproc 2047 oracle hard nproc 16384 oracle soft nofile 1024 oracle hard nofile 65536
[root@gzxbi01 ~]# vim /etc/pam.d/login
session required /lib64/security/pam_limits.so
[root@gzxbi01 ~]# vim /etc/profile
if [ $USER = "oracle" ]; then ulimit -u 16384 ulimit -n 65536 else ulimit -u 16384 -n 655536 fi
[root@gzxbi01 ~]# source /etc/profile
[root@gzxbi01 ~]# su - oracle
[oracle@gzxbi01 ~]$ vim .bash_profile
TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME ORACLE_SID=orcl11; export ORACLE_SID ORACLE_TERM=xterm; export ORACLE_TERM PATH=/usr/sbin:$PATH; export PATH PATH=$ORACLE_HOME/bin:$PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH if [ $USER = "oracle" ]; then if [ $SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi fi
[oracle@gzxbi01 ~]$ source .bash_profile
安装过程省略,手工建库过程
[root@gzxbi01 ~]# mkdir -p /u02/app/oracle/flash_recovery_area
[root@gzxbi01 ~]# mkdir -p /u02/app/oracle/arch/
[root@gzxbi01 ~]# chown -R oracle:oinstall /u02
[root@gzxbi01 ~]# chmod -R 775 /u02
[oracle@gzxbi01 oradata]$ cd $ORACLE_HOME/dbs
[oracle@gzxbi01 dbs]$ vim initnina.ora
instance_type='RDBMS'
db_domain='world'
db_name='nina'
compatible ='11.2.0'
statistics_level='typical'
audit_trail='none'
remote_login_passwordfile='none'
sga_target=300M
memory_target=1G
open_cursors=300
db_files=1000
processes=600
db_block_size=8192
cursor_sharing='force'
background_dump_dest='/u01/app/oracle/admin/nina/'
user_dump_dest='/u01/app/oracle/admin/nina/'
core_dump_dest='/u01/app/oracle/admin/nina/'
control_files = ('/u01/oradata/cont1.ctl','/u01/oradata/cont2.ctl')
db_file_multiblock_read_count=16
db_flashback_retention_target=7200
db_recovery_file_dest=('/u02/app/oracle/flash_recovery_area')
db_recovery_file_dest_size=1000M
log_archive_dest_1='LOCATION=/u02/app/oracle/arch/'
log_archive_format='log%t_%s_%r.arc'
optimizer_mode='all_rows'
undo_management='auto'
undo_retention=7200
undo_tablespace='undotbs_01'
连接启动
SQL> startup nomount ORA-01031: insufficient privileges SQL> exit [oracle@gzxbi01 dbs]$ sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 1 14:42:50 2016 Copyright (c) 1982, 2009, Oracle. All rights reserved. SQL> conn sys as sysdba Enter password: Connected to an idle instance. SQL> startup nomount
编写SQL脚本
[root@gzxbi01 oradata]# mkdir -pv /u01/oradata/nina
[root@gzxbi01 oradata]# chown -R oracle:oinstall /u01/oradata
[root@gzxbi01 oradata]# chmod -R 755 /u01/oradata
CREATE DATABASE nina USER SYS IDENTIFIED BY sys_password USER SYSTEM IDENTIFIED BY system_passwordd LOGFILE GROUP 1 ('/u01/oradata/nina/redo01.log') SIZE 100M, GROUP 2 ('/u01/oradata/nina/redo02.log') SIZE 100M, GROUP 3 ('/u01/oradata/nina/redo03.log') SIZE 100M MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 300 CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16 EXTENT MANAGEMENT LOCAL DATAFILE '/u01/oradata/nina/system01.dbf' SIZE 500M REUSE SYSAUX DATAFILE '/u01/oradata/nina/sysaux01.dbf' SIZE 325M REUSE DEFAULT TABLESPACE users DATAFILE '/u01/oradata/nina/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u01/oradata/nina/temp01.dbf' SIZE 200M REUSE UNDO TABLESPACE undotbs_01 DATAFILE '/u01/oradata/nina/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
执行SQL语句
SQL> @/u02/app/oracle/a.sql
可以同时观察对应的预警日志
[oracle@gzxbi01 trace]$ pwd
/u01/app/oracle/diag/rdbms/nina/nina/trace
[oracle@gzxbi01 trace]$ cat alert_nina.log
CREATE DATABASE nina USER SYS IDENTIFIED BY *USER SYSTEM IDENTIFIED BY *MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 300 LOGFILE GROUP 1 ('/u01/oradata/nina/redo01.log') SIZE 100M, GROUP 2 ('/u01/oradata/nina/redo02.log') SIZE 100M, GROUP 3 ('/u01/oradata/nina/redo03.log') SIZE 100M CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16 EXTENT MANAGEMENT LOCAL DATAFILE '/u01/oradata/nina/system01.dbf' SIZE 500M REUSE SYSAUX DATAFILE '/u01/oradata/nina/sysaux01.dbf' SIZE 325M REUSE DEFAULT TABLESPACE users DATAFILE '/u01/oradata/nina/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u01/oradata/nina/temp01.dbf' SIZE 200M REUSE UNDO TABLESPACE undotbs_01 DATAFILE '/u01/oradata/nina/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED Database mounted in Exclusive Mode #表示Oracle已经打开initnina.ora文件中指定的控制文件 Lost write protection disabled Thu Dec 01 17:27:29 2016 Successful mount of redo thread 1, with mount id 1284923831 #指出第一个重做日志已经成功创建，并为恢复的目的打开 Assigning activation ID 1284923831 (0x4c9661b7) Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: /u01/oradata/nina/redo01.log Successful open of redo thread 1 Thu Dec 01 17:27:30 2016 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Thu Dec 01 17:27:30 2016 SMON: enabling cache recovery processing ?/rdbms/admin/dcore.bsq create tablespace SYSTEM datafile '/u01/oradata/nina/system01.dbf' SIZE 500M REUSE EXTENT MANAGEMENT LOCAL online Thu Dec 01 17:27:50 2016 Completed: create tablespace SYSTEM datafile '/u01/oradata/nina/system01.dbf' SIZE 500M REUSE EXTENT MANAGEMENT LOCAL online create rollback segment SYSTEM tablespace SYSTEM storage (initial 50K next 50K) Completed: create rollback segment SYSTEM tablespace SYSTEM storage (initial 50K next 50K) processing ?/rdbms/admin/dsqlddl.bsq processing ?/rdbms/admin/dmanage.bsq CREATE TABLESPACE sysaux DATAFILE '/u01/oradata/nina/sysaux01.dbf' SIZE 325M REUSE #system和sysaux表空间创建成功 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE Thu Dec 01 17:28:06 2016 Completed: CREATE TABLESPACE sysaux DATAFILE '/u01/oradata/nina/sysaux01.dbf' SIZE 325M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE processing ?/rdbms/admin/dplsql.bsq processing ?/rdbms/admin/dtxnspc.bsq CREATE UNDO TABLESPACE UNDOTBS_01 DATAFILE '/u01/oradata/nina/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED Thu Dec 01 17:28:16 2016 Successfully onlined Undo Tablespace 2. Completed: CREATE UNDO TABLESPACE UNDOTBS_01 DATAFILE '/u01/oradata/nina/undotbs01.dbf' #撤销表空间undotbs01创建成功 SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED CREATE TEMPORARY TABLESPACE TEMPTS1 TEMPFILE '/u01/oradata/nina/temp01.dbf' SIZE 200M REUSE Completed: CREATE TEMPORARY TABLESPACE TEMPTS1 TEMPFILE '/u01/oradata/nina/temp01.dbf' #创建临时表空间TEMPPS1 SIZE 200M REUSE ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTS1 #指定TEMPTS1作为数据库的默认永久表空间 Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTS1 CREATE TABLESPACE USERS DATAFILE '/u01/oradata/nina/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED SEGMENT SPACE MANAGEMENT MANUAL Thu Dec 01 17:28:36 2016 Completed: CREATE TABLESPACE USERS DATAFILE '/u01/oradata/nina/users01.dbf' #创建User表空间 SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED SEGMENT SPACE MANAGEMENT MANUAL ALTER DATABASE DEFAULT TABLESPACE USERS #指定USERS表空间为默认永久表空间 Completed: ALTER DATABASE DEFAULT TABLESPACE USERS processing ?/rdbms/admin/dfmap.bsq processing ?/rdbms/admin/denv.bsq processing ?/rdbms/admin/drac.bsq processing ?/rdbms/admin/dsec.bsq processing ?/rdbms/admin/doptim.bsq processing ?/rdbms/admin/dobj.bsq processing ?/rdbms/admin/djava.bsq processing ?/rdbms/admin/dpart.bsq processing ?/rdbms/admin/drep.bsq processing ?/rdbms/admin/daw.bsq processing ?/rdbms/admin/dsummgt.bsq processing ?/rdbms/admin/dtools.bsq processing ?/rdbms/admin/dexttab.bsq processing ?/rdbms/admin/ddm.bsq processing ?/rdbms/admin/dlmnr.bsq processing ?/rdbms/admin/ddst.bsq Thu Dec 01 17:28:39 2016 SMON: enabling tx recovery Starting background process SMCO Thu Dec 01 17:28:39 2016 SMCO started with pid=18, OS id=3206 Thu Dec 01 17:28:50 2016 replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC #启动新后台进程QMNC Thu Dec 01 17:28:50 2016 QMNC started with pid=20, OS id=3211 Completed: CREATE DATABASE nina USER SYS IDENTIFIED BY *USER SYSTEM IDENTIFIED BY *MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 300 LOGFILE GROUP 1 ('/u01/oradata/nina/redo01.log') SIZE 100M, GROUP 2 ('/u01/oradata/nina/redo02.log') SIZE 100M, GROUP 3 ('/u01/oradata/nina/redo03.log') SIZE 100M CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16 EXTENT MANAGEMENT LOCAL DATAFILE '/u01/oradata/nina/system01.dbf' SIZE 500M REUSE SYSAUX DATAFILE '/u01/oradata/nina/sysaux01.dbf' SIZE 325M REUSE DEFAULT TABLESPACE users DATAFILE '/u01/oradata/nina/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u01/oradata/nina/temp01.dbf' SIZE 200M REUSE UNDO TABLESPACE undotbs_01 DATAFILE '/u01/oradata/nina/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED Thu Dec 01 17:28:50 2016 db_recovery_file_dest_size of 1000 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup.
执行Oracle脚本创建数据字典
catalog.sql:数据字典视图。公用同义词及其他对象填充数据库
catproc.sql:创建Oracle提供的程序包及其他支持在数据库中使用PL/SQL代码的对象
SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql .... Grant succeeded. PL/SQL procedure successfully completed. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP CATALOG 2016-12-02 08:47:09
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
....
在执行过程中，观察预警日志
Fri Dec 02 08:46:11 2016 Thread 1 advanced to log sequence 2 (LGWR switch) Current log# 2 seq# 2 mem# 0: /u01/oradata/nina/redo02.log Fri Dec 02 08:49:36 2016 Thread 1 advanced to log sequence 3 (LGWR switch) Current log# 3 seq# 3 mem# 0: /u01/oradata/nina/redo03.log Fri Dec 02 08:50:35 2016 Create Relation IPS_PACKAGE_UNPACK_HISTORY Fri Dec 02 08:51:34 2016 Thread 1 advanced to log sequence 4 (LGWR switch) Current log# 1 seq# 4 mem# 0: /u01/oradata/nina/redo01.log Fri Dec 02 08:53:54 2016 Thread 1 advanced to log sequence 5 (LGWR switch) Current log# 2 seq# 5 mem# 0: /u01/oradata/nina/redo02.log Fri Dec 02 08:56:36 2016 Thread 1 advanced to log sequence 6 (LGWR switch) Current log# 3 seq# 6 mem# 0: /u01/oradata/nina/redo03.log Fri Dec 02 08:57:29 2016 Starting background process CJQ0 Fri Dec 02 08:57:30 2016 CJQ0 started with pid=22, OS id=15118 Fri Dec 02 08:57:55 2016 Thread 1 advanced to log sequence 7 (LGWR switch) Current log# 1 seq# 7 mem# 0: /u01/oradata/nina/redo01.log Fri Dec 02 08:58:21 2016 SERVER COMPONENT id=CATPROC: timestamp=2016-12-02 08:58:21
至此，手动建库完成！