【Oracle】 手工建库

操作系统:OEL 5.6

数据库版本：Oracle11gR2  11.2.0.4.0

新建数据库名称：lgr

1 生成pfile和口令文件

1)生成pfile文件，在模板文件init.ora中提取

[oracle@lgr ~]$ cd $ORACLE_HOME/dbs

[oracle@lgr dbs]$ ls

init.ora

[oracle@lgr dbs]$ cat init.ora |grep -v ^$|grep -v ^# > initlgr.ora

 

2）修改initlgr.ora文件

[oracle@lgr dbs]$ vi initlgr.ora

----添加如下内容----

db_name='lgr'

memory_target=800M

processes = 150

audit_file_dest='$ORACLE_BASE/admin/lgr/adump'

audit_trail ='db'

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_tablespace='UNDOTBS1'

control_files = '/u01/app/oracle/oradata/lgr/ora_control1.ctl’

compatible ='11.2.0'

 

3）创建initlgr.ora中的目录

[oracle@lgr ~]$ mkdir -p $ORACLE_BASE/admin/lgr/adump

[oracle@lgr lgr]$ mkdir -p /u01/app/oracle/oradata/lgr/

 

4）创建口令文件orapwlgr

[oracle@lgr dbs]$ orapwd file=orapwlgr password=oracle entries=3;

[oracle@lgr dbs]$ ls

initlgr.ora  init.ora  orapwlgr

 

5）SqlPlus中启动数据库到nomount状态，验证口令文件和参数文件

[oracle@lgr dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 5 15:06:45 2016

 

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

 

Connected to an idle instance.

 

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             536874064 bytes

Database Buffers          289406976 bytes

Redo Buffers                6565888 bytes

 

2.通过建库脚本创建数据库

##建库脚本可在官方文档中获取

[oracle@lgr ~]$ cd $ORACLE_HOME/dbs

[oracle@lgr dbs]$ pwd

/u01/app/oracle/product/11.2.0/db_1/dbs

[oracle@lgr dbs]$ vi createdb.sql

----添加如下内容----

CREATE DATABASE lgr

   USER SYS IDENTIFIED BY oracle

   USER SYSTEM IDENTIFIED BY oracle

   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/lgr/redo01a.log') SIZE 100M BLOCKSIZE 512,

           GROUP 2 ('/u01/app/oracle/oradata/lgr/redo02a.log') SIZE 100M BLOCKSIZE 512

   MAXLOGFILES 5

   MAXLOGMEMBERS 5

   MAXLOGHISTORY 1

   MAXDATAFILES 100

   CHARACTER SET AL32UTF8

   NATIONAL CHARACTER SET AL16UTF16

   EXTENT MANAGEMENT LOCAL

   DATAFILE '/u01/app/oracle/oradata/lgr/system01.dbf' SIZE 325M REUSE

   SYSAUX DATAFILE '/u01/app/oracle/oradata/lgr/sysaux01.dbf' SIZE 325M REUSE

   DEFAULT TABLESPACE users

      DATAFILE '/u01/app/oracle/oradata/lgr/users01.dbf'

      SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

   DEFAULT TEMPORARY TABLESPACE tempts1

      TEMPFILE '/u01/app/oracle/oradata/lgr/temp01.dbf'

      SIZE 20M REUSE

   UNDO TABLESPACE undotbs1

      DATAFILE '/u01/app/oracle/oradata/lgr/undotbs01.dbf'

      SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

 

3.SqlPlus中运行createdb.sql脚本

SQL> @/u01/app/oracle/product/11.2.0/db_1/dbs/createdb.sql

 

4.创建数据字典

数据库OPEN状态下运行如下脚本，安装盘中存在这些脚本，或者去官网下载脚本

SQL> @?/rdbms/admin/catalog.sql

SQL> @?/rdbms/admin/catproc.sql

SQL> conn system/oracle

SQL> @?/sqlplus/admin/pupbld.sql

 

5.运行脚本创建必要的Schema及表空间

SQL> @?/rdbms/admin/utlsampl