【Oracle XE系列之三】使用OMF方式手工创建Oracle XE数据库

环境：win10_X64_Pro

1.创建数据库实例，实例名为PF

C:oraclexeapporacleproduct11.2.0serverin>oradim -new -sid PF

实例已创建。

错误：dim-00014:无法打开 Windows NT 服务控制管理器。OS-Error:(OS 5) 拒绝访问。

处理办法：以管理身份运行。

2.为了sys用户可以连入数据库执行操作，先创建数据库的密码文件，密码文件的名称必须为PWDsid.ora

C:oraclexeapporacleproduct11.2.0serverin>orapwd password=oracle file=C:oraclexeapporacleproduct11.2.0serverdatabasePWDPF.ora

3.创建下列目录，以存放数据文件和dump信息

#数据文件目录

C:oraclexeapporacleoradataPF

#background dump目录

C:oraclexeapporacleoradataPFdump

#core dump目录

C:oraclexeapporacleoradataPFcdump

#user dump目录

C:oraclexeapporacleoradataPFudump

4.修改listener.ora为下列信息，把此数据库注册在监听器中（可选）在C:oraclexeapporacleproduct11.2.0server etworkADMIN下

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:oraclexeapporacleproduct11.2.0server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:oraclexeapporacleproduct11.2.0server)
      (PROGRAM = extproc)
    )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = wanglifu)(PORT = 1521))
    )
)

DEFAULT_SERVICE_LISTENER = (PF)

根据文件添加

5.为了可以访问数据库，修改tnsnames.ora为下列信息

在C:oraclexeapporacleproduct11.2.0server etworkADMIN下

PF =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PF)
    )
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
)

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
)

6.建立pfile文件C:oraclexeapporacleproduct11.2.0serverdatabaseinitPF.ora，文件的内容如下：(注意文件名)

background_dump_dest='C:oraclexeapporacleoradataPFdump'

compatible='11.2.0.1.0'

control_files='C:oraclexeapporacleoradataPFcontrol01.ctl',C:oraclexeapporacleoradataPFcontrol02.ctl'

core_dump_dest='C:oraclexeapporacleoradataPF/cdump'

db_block_size=8192

db_name='PF'

db_recovery_file_dest='C:oraclexeapporaclefast_recovery_area'

db_recovery_file_dest_size=2147483648

pga_aggregate_target=41943040

processes=150

remote_login_passwordfile='EXCLUSIVE'

sga_max_size=167772160

sga_target=125829120

undo_management='AUTO'

undo_tablespace='undotbs1'

user_dump_dest='C:oraclexeapporacleoradataPF/udump'

db_create_file_dest='C:oraclexeapporacleoradataPF'

db_create_online_log_dest_1='C:oraclexeapporacleoradataPF'

7.连入数据库，并创建spfile文件

C:oraclexeapporacleproduct11.2.0serverin>set ORACLE_SID=PF

C:oraclexeapporacleproduct11.2.0serverin>sqlplus.exe / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on 星期五 12月 18 11:17:05 2015

已连接到空闲例程。

SQL>create spfile from pfile;

文件已创建。

8.启动数据库到nomount

SQL>startup nomount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-12853: insufficient memory for PX buffers: current 0K, max needed 0K
ORA-04031: unable to allocate 65560 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")

SQL>

9.创建并执行creata database文件C:oraclexeapporacleadminPFCreatecreatedb.sql脚本，具体内容如下：

create database PF

user sys identified by oracle

user system identified by oracle

default temporary tablespace temp

undo tablespace undotbs1

character set utf8

national character set al16utf16;

SQL>@C:oraclexeapporacleadminPFCreatecreatedb.sql

10.执行catalog.sql，创建数据库的数据字典视图

SQL>spool C:oraclexecatalog.log

SQL>@C:oraclexeapporacleproduct11.2.0serverRDBMSADMINcatalog.sql

SQL>spool off

11.执行catproc.sql，创建执行PL/SQL程序所需的所有包

SQL>spool C:oraclexecatproc.log

SQL>@C:oraclexeapporacleproduct11.2.0serverRDBMSADMINcatproc.sql

SQL>spool off

到此数据库PF创建完毕，可以正常使用了。

需要注意的地方：
（1）在11g中，如果system01.dbf这个文件用extent management local的话，必须创建一个temporary表空间。
（2）11g必须创建sysaux01.dbf这个文件
（3）如果以上步骤出现错误，而错误提示为乱码的话，可以退出SQLPLUS，然后设置环境变量set nls_lang=american_america.zhs16gbk（windows），然后再登录数据库。

总结：创建过程中出现了很多的问题和报错，出现报错后查看告警日志alert_pf，本例中路径是C:oraclexeapporacleoradataPFdump，根据错误号可以google一下，能解决很多问题，具体出现的一些问题我会在日志中记录下来。