oracle手动 建库_[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-2glogin.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= 150audit_file_dest='/u01/app/oracle/admin/tank/adump'audit_trail='db'db_block_size=8192db_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=300remote_login_passwordfile='EXCLUSIVE'undo_tablespace='UNDOTBS1'control_files= ('/data01/tank/control01.ctl','/data02/tank/control02.ctl')

compatible='11.2.0'

--本文以演示为主，该部分参数未做调整，可结合实际情况进行修改

创建pfile中涉及的路径：

$ grep u01 inittank.oraaudit_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 2017Copyright (c)1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SYS@tank> create spfile frompfile;

File created.

SYS@tank>startup nomount;

ORACLE instance started.

Total System Global Area1068937216bytes

Fixed Size2260088bytes

Variable Size671089544bytes

Database Buffers390070272bytes

Redo Buffers5517312bytes

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 GROUP1 ('/data01/tank/redo01a.log','/data02/tank/redo01b.log') SIZE 500M BLOCKSIZE 512,

GROUP2 ('/data01/tank/redo02a.log','/data02/tank/redo02b.log') SIZE 500M BLOCKSIZE 512,

GROUP3 ('/data01/tank/redo03a.log','/data02/tank/redo03b.log') SIZE 500M BLOCKSIZE 512MAXLOGFILES5MAXLOGMEMBERS5MAXLOGHISTORY1MAXDATAFILES100CHARACTER 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 UtilityforLinux: Version 11.2.0.4.0 - Production on 13-FEB-2017 04:21:39Copyright (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

参考文档：

-The End-

Tank

20180130