oracle 数据库的创建数据库实例,oracle 创建数据库实例

1.       设置环境变量ORACLE_HOME,ORACLE_SID

2.创建初始化参数文件文件名称init+实例名+.ora注意该参数文件中涉及到的目录都需要提交创建否则会报错undo_tablespace参数的值与其后创建数据库语句中的UNDO TABLESPACE后的参数必须相同

示例

db_name='prtl'

memory_target=2G

processes = 150

audit_file_dest='/oracle/admin/prtl/adump'

audit_trail='db'

db_block_size=8192

db_domain=''

db_recovery_file_dest='/oracle/flash_recovery_area'

db_recovery_file_dest_size=2G

diagnostic_dest='/oracle'

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

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

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

# devices

control_files = (/oradata3/prtl/ora_control1, /oradata3/prtl/ora_control2)

compatible ='11.2.0'

3.连数据库

$ sqlplus /nolog SQL> CONNECT SYS AS SYSDBA

如果连不上用ps–ef|grep smon查看一下

如果存在该实例进程杀掉再连一下

4.生成pfile CREATE SPFILE FROM PFILE;该文件由init+实例名+.ora文件生成

5.启动实例STARTUP NOMOUNT

6.创建数据库实例注意字符集设置相关文件路径UNDO TABLESPACE参数示例语句

CREATE DATABASE prtl

USER SYS IDENTIFIED BY password

USER SYSTEM IDENTIFIED BY password

LOGFILE GROUP 1 ('/oradata3/prtl/redo01.log') SIZE 100M,

GROUP 2 ('/oradata3/prtl/redo02.log') SIZE 100M,

GROUP 3 ('/oradata3/prtl/redo03.log') SIZE 100M

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

CHARACTER SET AL32UTF8

NATIONAL CHARACTER SET AL16UTF16

EXTENT MANAGEMENT LOCAL

DATAFILE '/oradata3/prtl/system01.dbf' SIZE 325M REUSE

SYSAUX DATAFILE '/oradata3/prtl/sysaux01.dbf' SIZE 325M REUSE

DEFAULT TABLESPACE users

DATAFILE '/oradata3/prtl/users01.dbf'

SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE '/oradata3/prtl/temp01.dbf'

SIZE 20M REUSE

UNDO TABLESPACE UNDOTBS1

DATAFILE '/oradata3/prtl/undotbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

7.创建应用表空间

CREATE TABLESPACE apps_tbs LOGGING

DATAFILE '/oradata3/prtl/apps01.dbf'

SIZE 500M REUSE AUTOEXTEND ON  NEXT  1280K  MAXSIZE

UNLIMITED

EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE indx_tbs LOGGING

DATAFILE '/oradata3/prtl/indx01.dbf'

SIZE 100M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE

UNLIMITED

EXTENT MANAGEMENT LOCAL;

8.执行创建脚本创建数据字典及视图

@$ORACLE_HOME/rdbms/admin/catalog.sql

@$ORACLE_HOME/rdbms/admin/catproc.sql

@$ORACLE_HOME/sqlplus/admin/pupbld.sql