Oracle11g手工建库
测试环境：
操作系统：64位 OEL5.6
数据库：Oracle11.2.0.4
1：配置环境变量（以下是本实验环境的配置）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
[oracle@prod ~]$ 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/dbhome_1 export ORACLE_SID=PROD export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib [oracle@prod ~]$ --使环境变量生效，当然，如果重新登陆或者做过切换等操作，此步骤可以省略 [oracle@prod ~]$ . .bash_profile [oracle@prod ~]$ |
2：创建密码文件（作用：允许远程用密码方式以sysdba身份登陆数据库，密码文件可有可无）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
--切换到存放密码文件的目录 [oracle@prod ~]$ cd $ORACLE_HOME/dbs [oracle@prod dbs]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/dbs [oracle@prod dbs]$ ls init.ora [oracle@prod dbs]$ --生成密码文件的命令，帮助信息 [oracle@prod dbs]$ orapwd Usage: orapwd file=<fname> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba=<y/n> where file - name of password file (required), password - password for SYS will be prompted if not specified at command line, entries - maximum number of distinct DBA (optional), force - whether to overwrite existing file (optional), ignorecase - passwords are case-insensitive (optional), nosysdba - whether to shut out the SYSDBA logon (optional Database Vault only). There must be no spaces around the equal-to (=) character. [oracle@prod dbs]$ --生成密码文件，密码文件的格式：orapw+SID [oracle@prod dbs]$ orapwd file=orapwPROD password=oracle [oracle@prod dbs]$ ls init.ora orapwPROD [oracle@prod dbs]$ |
3：生成pfile文件
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
[oracle@prod dbs]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/dbs [oracle@prod dbs]$ ls init.ora orapwPROD [oracle@prod dbs]$ cat init.ora |grep -v ^#|grep -v ^$ > initPROD.ora [oracle@prod dbs]$ ls init.ora initPROD.ora orapwPROD [oracle@prod dbs]$ [oracle@prod dbs]$ vi initPROD.ora db_name='PROD' memory_target=1G 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='<ORACLE_BASE>/flash_recovery_area' --关闭快速恢复区，如果需要开启的话，随时可以通过修改参数来完成。 #db_recovery_file_dest_size=2G --注释掉诊断的参数，数据库创建好后，这个参数会自动启用 #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/PROD/ora_control1.ctl,/u01/app/oracle/oradata/PROD/ora_control2.ctl) compatible ='11.2.0' |
4：根据pfile创建目录adump目录和控制文件目录
|
1 2 3 |
[oracle@prod ~]$ mkdir -p $ORACLE_BASE/admin/prod/adump [oracle@prod ~]$ mkdir -p $ORACLE_BASE/oradata/PROD/ [oracle@prod ~]$ |
5：通过pfile创建spfile
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
--查看已存在的pfile文件initPROD.ora [oracle@prod ~]$ cd $ORACLE_HOME/dbs [oracle@prod dbs]$ ls init.ora initPROD.ora orapwPROD [oracle@prod dbs]$ --验证当前环境变量 [oracle@prod dbs]$ echo $ORACLE_SID PROD [oracle@prod dbs]$ --通过pfile创建spfile [oracle@prod dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 28 16:34:35 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile; File created. SQL> !ls init.ora initPROD.ora orapwPROD spfilePROD.ora SQL> |
6：数据库启动到nomount（默认情况下，oracle会使用spfile启动数据库）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
[oracle@prod dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 28 17:20:28 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area 1068937216 bytes Fixed Size 2260088 bytes Variable Size 671089544 bytes Database Buffers 390070272 bytes Redo Buffers 5517312 bytes SQL> SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ---------------------------------------------------------- spfile string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilePROD.ora SQL> SQL> select status from v$instance; STATUS ------------ STARTED SQL> |
7：编写创建数据库脚本
Oracle11g官方文档：
Administrator’s Guide->2 Creating and Configuring an Oracle Database->Step 9: Issue the CREATE DATABASE Statement
1)修改数据库名称及相关口令
2)由于是测试环境，所以，可以将日志文件改为1个成员，把成员大小改为10M
3)修改users表空间大小及undo表空间大小
4)修改undo表空间的名称，要与pfile文件中的名称相同
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE DATABASE PROD USER SYS IDENTIFIED BY oracle USER SYSTEM IDENTIFIED BY oracle LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/redo01a.log') SIZE 10M BLOCKSIZE 512, GROUP 2 ('/u01/app/oracle/oradata/PROD/redo02a.log') SIZE 10M BLOCKSIZE 512, GROUP 3 ('/u01/app/oracle/oradata/PROD/redo03a.log') SIZE 10M 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/PROD/system01.dbf' SIZE 325M REUSE SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD/sysaux01.dbf' SIZE 325M REUSE DEFAULT TABLESPACE users DATAFILE '/u01/app/oracle/oradata/PROD/users01.dbf' SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u01/app/oracle/oradata/PROD/temp01.dbf' SIZE 20M REUSE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/PROD/undotbs01.dbf' SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; |
8：创建数据库（数据库此时的状态应该是nomount，直接在sqlplus下执行创建数据库脚本即可）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
SQL> CREATE DATABASE PROD 2 USER SYS IDENTIFIED BY oracle 3 USER SYSTEM IDENTIFIED BY oracle 4 LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/redo01a.log') SIZE 10M BLOCKSIZE 512, 5 GROUP 2 ('/u01/app/oracle/oradata/PROD/redo02a.log') SIZE 10M BLOCKSIZE 512, 6 GROUP 3 ('/u01/app/oracle/oradata/PROD/redo03a.log') SIZE 10M BLOCKSIZE 512 7 MAXLOGFILES 5 8 MAXLOGMEMBERS 5 9 MAXLOGHISTORY 1 10 MAXDATAFILES 100 11 CHARACTER SET US7ASCII 12 NATIONAL CHARACTER SET AL16UTF16 13 EXTENT MANAGEMENT LOCAL 14 DATAFILE '/u01/app/oracle/oradata/PROD/system01.dbf' SIZE 325M REUSE 15 SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD/sysaux01.dbf' SIZE 325M REUSE 16 DEFAULT TABLESPACE users 17 DATAFILE '/u01/app/oracle/oradata/PROD/users01.dbf' 18 SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED 19 DEFAULT TEMPORARY TABLESPACE tempts1 20 TEMPFILE '/u01/app/oracle/oradata/PROD/temp01.dbf' 21 SIZE 20M REUSE 22 UNDO TABLESPACE UNDOTBS1 23 DATAFILE '/u01/app/oracle/oradata/PROD/undotbs01.dbf' 24 SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; Database created. SQL> select status from v$instance; STATUS ------------ OPEN SQL> --查看相关参数 SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /u01/app/oracle/oradata/PROD/o ra_control1.ctl, /u01/app/orac le/oradata/PROD/ora_control2.c tl 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 /dbhome_1/dbs/spfilePROD.ora SQL> |
9：创建字典表及工具包
|
1 2 3 4 5 6 7 8 9 10 11 |
--必执行脚本 SQL> conn / as sysdba SQL> @?/rdbms/admin/catalog.sql SQL> @?/rdbms/admin/catproc.sql SQL> conn system/passwd SQL> @?/sqlplus/admin/pupbld.sql --可选脚本 SQL> conn / as sysdba SQL> @?/rdbms/admin/catblock.sql SQL> @?/rdbms/admin/catoctk.sql SQL> @?/rdbms/admin/owminst.plb |