手动创建oracle监听服务器,手动创建数据库的整体流程 Oracle 11g

注释：文中的所有spdbface均要改成实际的SID，包括三个附带的文档中的spdbface

1 进入终端

2切换到oracle用户

-bash-4.1$ su - oracle

3查看当前oracle变量和sqlplus运行情况

-bash-4.1$ env |grep ORA

ORACLE_SID=CPMSDB

ORACLE_BASE=/oracle/app/oracle

ORACLE_HOME=/oracle/app/oracle/product/11.2

-bash-4.1$ sqlplus /nolog。

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 20 13:31:57 2015

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

SQL> exit

4设置新的实例

-bash-4.1$ export ORACLE_SID=spdbface

5新实例创建密码文件

-bash-4.1$ orapwd file=$ORACLE_HOME/dbs/orapwspdbfacepassword=oracle entries=10

6 创建pfile文件

-bash-4.1$ cd $ORACLE_HOME/dbs

-bash-4.1$ vi initspdbface.ora

initspdbface.ora中需要添加的内容如下

db_name='spdbface'

memory_target=1G

processes = 150

audit_file_dest='/oracle/app/oracle/admin/spdbface/adump'

audit_trail ='db'

db_block_size=8192

db_domain=''

db_recovery_file_dest='/oradata/spdbface/flash_recovery_area'

db_recovery_file_dest_size=2G

diagnostic_dest='/oracle/app/oracle'

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

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS2'

# You may want to ensure that control files are created on separate physical

# devices

control_files=(/oradata/spdbface/ora_control1,/oradata/spdbface/ora_control2)

compatible ='11.2.0'

undo_management=AUTO

7创建实例相关的目录

-bash-4.1$ mkdir -p $ORACLE_BASE/admin/spdbface/adump

-bash-4.1$ mkdir -p $ORACLE_BASE/admin/spdbface/bdump

-bash-4.1$ mkdir -p $ORACLE_BASE/admin/spdbface/cdump

-bash-4.1$ mkdir -p $ORACLE_BASE/admin/spdbface/udump

-bash-4.1$ mkdir -p $ORACLE_BASE/admin/spdbface/pfile

8创建一个数据库执行脚本

-bash-4.1$ cd $ORACLE_HOME/dbs

-bash-4.1$ touchspdbface.sql

-bash-4.1$ vispdbface.sql

mkdir /oradata/spdbface

mkdir/oracle/app/oracle/flash_recovery_area

spdbface.sql中需要添加的内容如下

spool dbcreate.log;

create DATABASE "spdbface"

MAXDATAFILES 500

MAXINSTANCES  8

MAXLOGFILES   32

CHARACTER SET "UTF8"

NATIONAL CHARACTER SET AL16UTF16

ARCHIVELOG

DATAFILE

'/oradata/spdbface/system01.dbf' SIZE 300M

SYSAUX DATAFILE

'/oradata/spdbface/sysaux01.dbf' SIZE 120M

EXTENT MANAGEMENT LOCAL

DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/oradata/spdbface/tempts01.dbf' SIZE 100M EXTENT

MANAGEMENT LOCAL

UNDO TABLESPACE "UNDOTBS2"

DATAFILE '/oradata/spdbface/undotbs01.dbf' SIZE 200M

LOGFILE

GROUP 1(

'/oradata/spdbface/redo01a.rdo',

'/oradata/spdbface/redo01b.rdo'

) SIZE 100M,

GROUP 2(

'/oradata/spdbface/redo02a.rdo',

'/oradata/spdbface/redo02b.rdo'

)SIZE 100M,

GROUP 3(

'/oradata/spdbface/redo03a.rdo',

'/oradata/spdbface/redo03b.rdo'

)SIZE 100M

;

spool off

9创建spfile并将并将数据库启动到nomount状态

-bash-4.1$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 20 13:59:16 2015

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

SQL> connect /as sysdba

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> 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

SQL>

10执行数据库脚本(执行此步骤大概需要5分钟)

SQL> @$ORACLE_HOME/dbs/spdbface.sql

Database created.

11创建表空间

SQL>create tablespace spdbface_dbdatafile'/oradata/spdbface/spdbface_db.dbf' size1000M autoextend on next 1000M maxsize unlimited extent management local segment space management auto;

Tablespace created.

12创建数据字典(大概需要30分钟)

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

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

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

13创建用户

SQL> create user spdbfaceapp identified by 123456;

User created.

SQL> alter user spdbfaceapp default tablespace spdbface_db;

User altered.

SQL> grant create session,create table,create view ,create sequence,unlimited tablespace to spdbfaceapp;

Grant succeeded.

14创建监听器

SQL> exit

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

-bash-4.1$ cd $ORACLE_HOME/network/admin

-bash-4.1$ touch listerer.ora

-bash-4.1$ vi listerer.ora

listerer.ora中需要添加的内容如下

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = spdbface)

(ORACLE_HOME = /oracle/app/oracle/product/11.2.0)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL DBNAME = orcl)

(SID_NAME = spdbface)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

)

)

15启动监听器

-bash-4.1$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-OCT-2015 15:06:55

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

16用sqldeveloper测试