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