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

时间:2022-02-05 08:29:57

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

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

图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:

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

图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