在原有数据库的基础上再建立一个数据库

（先决条件：当当前虚拟内存不够用时，将现有数据库关闭；如果不关库，则需要增加虚拟内存）

建库开始：
1.生成pfile文件：

[oracle@book oracle]$ cd $ORACLE_HOME

[oracle@book dbhome_1]$ cd dbs

oracle@book dbs]$ ls

hc_PROD.dat init.ora initPROD.ora lkPROD orapwPROD spfilePROD.ora

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

[oracle@book dbs]$

[oracle@book dbs]$ ls

hc_PROD.dat initEN.ora init.ora initPROD.ora lkPROD orapwPROD spfilePROD.ora

2.编辑pfile文件：
[oracle@book dbs]$ vi initEN.ora

db_name='EN'

memory_target=900M

processes = 150

audit_file_dest='/u01/app/oracle/admin/EN/adump'

#audit_trail ='db'

db_block_size=8192

db_domain=''

#db_recovery_file_dest='/flash_recovery_area'

#db_recovery_file_dest_size=2G

#diagnostic_dest=''

dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

control_files = (/u01/app/oracle/oradata/EN/control01.ctl, /u01/app/oracle/oradata/EN/control02.ctl)

compatible ='11.2.0'

3.根据pfile创建目录adump目录和控制文件目录：

[oracle@book oracle]$ mkdir -p /u01/app/oracle/admin/EN/adump

[oracle@book oradata]$ cd /u01/app/oracle/admin

[oracle@book admin]$ ls

EN PROD

[oracle@book dbs]$ mkdir -p /u01/app/oracle/oradata/EN/

[oracle@book oradata]$ ls

EN PROD

4.通过pfile创建spfile：

--查看已存在的pfile文件iniEN.ora

[oracle@book dbs]$ ls

hc_PROD.dat initEN.ora init.ora initPROD.ora lkPROD orapwPROD spfilePROD.ora

--验证当前环境变量

[oracle@prod dbs]$ echo $ORACLE_SID

PROD

[oracle@book dbs]$ export ORACLE_SID=EN

[oracle@book dbs]$ echo $ORACLE_SID

EN

--通过pfile创建spfile

[oracle@book dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 14 17:59:42 2016

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> !ls

hc_PROD.dat initEN.ora init.ora initPROD.ora lkPROD orapwPROD spfileEN.ora spfilePROD.ora

——验证，数据库启动到nomount（默认情况下，oracle会使用spfile启动数据库）:

[oracle@book dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 14 18:01:50 2016

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 939495424 bytes

Fixed Size 2258840 bytes

Variable Size 595593320 bytes

Database Buffers 335544320 bytes

Redo Buffers 6098944 bytes

SQL>

SQL> show parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/spfileEN.ora

SQL> select status from v$instance ;

STATUS

------------

STARTED

5.编写创建数据库脚本:

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文件中的名称相同

[oracle@book admin]$ cd $ORACLE_HOME

[oracle@book dbhome_1]$ vi create.sql

create database EN

user sys identified by oracle

user system identified by oracle

logfile group 1 ('/u01/app/oracle/oradata/EN/redo01.log') size 10m blocksize 512,

group 2 ('/u01/app/oracle/oradata/EN/redo02.log') size 10m blocksize 512,

group 3 ('/u01/app/oracle/oradata/EN/redo03.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/EN/system01.dbf' size 325m reuse

sysaux datafile '/u01/app/oracle/oradata/EN/sysaux01.dbf' size 325m reuse

default tablespace users

datafile '/u01/app/oracle/oradata/EN/users01.dbf'

size 50m reuse autoextend on maxsize unlimited

default temporary tablespace tempts1

tempfile '/u01/app/oracle/oradata/EN/temp01.dbf'

size 20m reuse

undo tablespace undotbs1

datafile '/u01/app/oracle/oradata/EN/undotbs01.dbf'

size 50m reuse autoextend on maxsize unlimited;

~

"create.sql" [New] 25L, 2140C written

[oracle@book dbhome_1]$

6.创建数据库（数据库此时的状态应该是nomount，直接在sqlplus下执行创建数据库脚本即可）:

SQL> @?/create.sql或者@?/create（默认找 .sql）,@执行脚本，？指$ORACLE_HOME.

或者直接在sql下执行如下：

SQL>create database EN

user sys identified by oracle

user system identified by oracle

logfile group 1 ('/u01/app/oracle/oradata/EN/redo01.log') size 10m blocksize 512,

group 2 ('/u01/app/oracle/oradata/EN/redo02.log') size 10m blocksize 512,

group 3 ('/u01/app/oracle/oradata/EN/redo03.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/EN/system01.dbf' size 325m reuse

sysaux datafile '/u01/app/oracle/oradata/EN/sysaux01.dbf' size 325m reuse

default tablespace users

datafile '/u01/app/oracle/oradata/EN/users01.dbf'

size 50m reuse autoextend on maxsize unlimited

default temporary tablespace tempts1

tempfile '/u01/app/oracle/oradata/EN/temp01.dbf'

size 20m reuse

undo tablespace undotbs1

datafile '/u01/app/oracle/oradata/EN/undotbs01.dbf'

size 50m reuse autoextend on maxsize unlimited;

Database created.

--验证查看相关参数

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL> show parameter control_files

NAME TYPE VALUE

------------------------------------ -----------

control_files string /u01/app/oracle/oradata/EN/con

trol01.ctl, /u01/app/oracle/or

adata/EN/control02.ctl

SQL> show parameter undo

NAME TYPE VALUE

------------------------------------ -----------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL>

SQL> show parameter spfile

NAME TYPE VALUE

------------------------------------ -----------

spfile string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileEN.ora

7.创建字典表及工具包：

--必执行脚本

SQL> conn / as sysdba

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

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

SQL> conn system/oracle

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

--可选脚本

SQL> conn / as sysdba

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

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

SQL> @?/rdbms/admin/owminst.plb

8.验证数据库可用性：

SQL> select * from v$version;

BANNER

---------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

5 rows selected.

9.创建密码文件：

[oracle@book dbs]$ ls

hc_EN.dat hc_PROD.dat initEN.ora init.ora initPROD.ora lkEN lkPROD orapwPROD spfileEN.ora spfilePROD.ora

——查看显示没有建密码文件，创建密码文件（作用：允许远程用密码方式以sysdba身份登陆数据库，密码文件可有可无）

[oracle@book dbs]$ orapwd file=orapwEN password=oracle

[oracle@book dbs]$ ls

hc_EN.dat hc_PROD.dat initEN.ora init.ora initPROD.ora lkEN lkPROD orapwEN orapwPROD spfileEN.ora spfilePROD.ora

数据库手工建库完成！！！！！！！！！！

——验证查看：

[oracle@book dbs]$ cd /u01/app/oracle/oradata/EN/

[oracle@book EN]$ ls

control01.ctl control02.ctl redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf

[oracle@book EN]$

[oracle@book ~]$ echo $ORACLE_SID

EN

[oracle@book ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 14 18:56:45 2016

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

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL> show parameter name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

cell_offloadgroup_name string

db_file_name_convert string

db_name string EN

db_unique_name string EN

global_names boolean FALSE

instance_name string EN

lock_name_space string

log_file_name_convert string

processor_group_name string

service_names string EN

结束！！！！！！！！！

【手工建库】（二）在原有数据库的基础上再建立一个数据库