[Oracle技能]Oracle 11g手工建库

Oracle版本：11.2.0.4
系统版本：Redhat 6.4 x64
前提条件：Oracle软件已完成安装

 

图1

添加环境变量：

$ pwd
/home/oracle
$ tail -8 .bash_profile
export PATH
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_SID=tank
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib/:/lib64:/usr/lib:/usr/lib64
export PATH=$ORACLE_HOME/bin:$PATH
$ source .bash_profile

修改glogin配置文件，用来在连接实例时显示连接名字：

$ cd $ORACLE_HOME/sqlplus/admin

$ tail -2 glogin.sql 
set sqlprompt '_USER"@"_CONNECT_IDENTIFIER> '
define _editor=vim

$ export ORACLE_SID=tank
$ sqlplus / as sysdba
Connected to an idle instance.
SYS@tank> 

创建密码文件及pfile：

$ cd $ORACLE_HOME/dbs
$ orapwd file=orapwTANK password=oracle entries=20

通过模板创建pfile，并修改内容：

$ cat init.ora |grep -v ^#|grep -v ^$>inittank.ora

修改pfile文件：

$ cat inittank.ora 
db_name='TANK'
memory_target=1G
processes = 150
audit_file_dest='/u01/app/oracle/admin/tank/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=TANKXDB)'
open_cursors=300 
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = ('/data01/tank/control01.ctl','/data02/tank/control02.ctl')
compatible ='11.2.0'
--本文以演示为主，该部分参数未做调整，可结合实际情况进行修改

创建pfile中涉及的路径：

$ grep u01 inittank.ora 
audit_file_dest='/u01/app/oracle/admin/tank/adump'
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
diagnostic_dest='/u01/app/oracle'
$ mkdir -p /u01/app/oracle/admin/tank/adump
$ mkdir -p /u01/app/oracle/flash_recovery_area

创建spfile并将实例启动至nomount阶段：

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 13 03:48:01 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@tank> create spfile from pfile;

File created.

SYS@tank> 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
SYS@tank>

编辑建库脚本：

官方文档参考模板

Administrator's Guide--2 Creating and Configuring an Oracle Database--Creating a Database with the CREATE DATABASE Statement--Step 9: Issue the CREATE DATABASE Statement--Example 1

SYS@tank> ed createdb.sql
CREATE DATABASE tank
   USER SYS IDENTIFIED BY oracle
   USER SYSTEM IDENTIFIED BY oracle
   LOGFILE GROUP 1 ('/data01/tank/redo01a.log','/data02/tank/redo01b.log') SIZE 500M BLOCKSIZE 512,
           GROUP 2 ('/data01/tank/redo02a.log','/data02/tank/redo02b.log') SIZE 500M BLOCKSIZE 512,
           GROUP 3 ('/data01/tank/redo03a.log','/data02/tank/redo03b.log') SIZE 500M BLOCKSIZE 512
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/data01/tank/system01.dbf' SIZE 325M REUSE
   SYSAUX DATAFILE '/data01/tank/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TABLESPACE users
      DATAFILE '/data01/tank/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/data01/tank/temp01.dbf'
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs1
      DATAFILE '/data01/tank/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
--注意undo表空间的名称要与pfile中的undo名称一致

执行建库脚本：

SYS@tank> @createdb

Database created.

执行创建数据字典等脚本：

SYS@tank> ed createdict.sql
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
conn system/oracle
@?/sqlplus/admin/pupbld.sql

SYS@tank> @createdict
--输出内容略，大约需执行5-10分钟。

网络文件配置，listener.ora和tnsnames.ora：

官方文档参考模板

Database Administration--Net Services Reference--6 Local Naming Parameters (tnsnames.ora)--ADDRESS

Database Administration--Net Services Reference--7 Oracle Net Listener Parameters (listener.ora)--ADDRESS

$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tank)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))    
  )

ADR_BASE_LISTENER = /u01/app/oracle

--启动监听器
$ lsnrctl start


$ cat tnsnames.ora 
TEST =
  (DESCRIPTION= 
    (ADDRESS=(PROTOCOL=tcp)(HOST=192.0.2.10)(PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=TANK))
  )

 PS：在复制编辑官方文档tnsnames内容时，注意在末尾处需要添加一个右括号，在线文档中漏掉了这个括号，如图2：

图2

连通性测试：

$ tnsping TEST

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 13-FEB-2017 04:21:39

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=192.0.2.10)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=TANK)))
OK (0 msec)


$ sqlplus system/oracle@TEST

SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 13 04:23:45 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

SYSTEM@TEST> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
tank             OPEN

参考文档：

　　　　Oracle 11g在线文档 https://docs.oracle.com/cd/E11882_01/index.htm

-The End-

Tank

20180130