ITPub博客

首页 > 数据库 > Oracle > 手工创建数据库的完整步骤

手工创建数据库的完整步骤

Oracle 作者:skelet 时间:2014-03-30 19:44:56 0 删除 编辑


手工建立数据库具体步骤


一建立存放数据库文件的目录和日志相关的文件目录


分别在$ORACLE_BASE\oradata $ORACLE_BASE\admin目录下建立NEWDB目录


使用命令查看目录结构


[oracle@wang NEWDB]$ pwd


/u01/app/admin/NEWDB


[oracle@wang NEWDB]$ pwd


/u01/app/oradata/NEWDB


确定ORACLE实例名


[oracle@wang NEWDB]$ export ORACLE_SID=NEWDB


创建初始化参数文件


1 创建参数文件


vi $ORACLE_BASE/dbs/initNEWDB.ora


control_files              = (/u01/app/oradata/NEWDB/control01.dbf,


                              /u01/app/oradata/NEWDB/control02.dbf,


                              /u01/app/oradata/NEWDB/control03.dbf)


db_name                    = NEWDB


db_domain                  = NEWDB.oracle.com


log_archive_dest_1         = "LOCATION=/u01/app/admin/NEWDB/arch"


log_archive_dest_state_1   = enable


db_block_size              = 8192


pga_aggregate_target       = 90M


processes                  = 100


sessions                   = 120


open_cursors               = 200


undo_management            = AUTO


undo_tablespace            = undotbs


compatible                 = 10.2.0


sga_target                 = 270M


nls_language               = AMERICAN


nls_territory              = AMERICA


db_recovery_file_dest      = /u01/app/admin/NEWDB/flashback


db_recovery_file_dest_size = 1G


audit_dump_dest            = /u01/app/admin/NEWDB/adump


core_dump_dest             = /u01/app/admin/NEWDB/cdump


background_dump_dest       = /u01/app/admin/NEWDB/bdump


 


2 创建spfile参数文件


[oracle@wang dbs]$ sqlplus / as sysdba


 


SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 23 14:37:08 2014


 


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


 


Connected to an idle instance.


 


SQL> create spfile from pfile;


 


File created.


 


创建数据库


1 startup nomount;


2 编写创建数据库脚本


vi temp


CREATE DATABASE NEWDB


   USER SYS IDENTIFIED BY oracle


   USER SYSTEM IDENTIFIED BY oracle


   LOGFILE GROUP 1 ('/u01/app/oradata/NEWDB/redo01.log') SIZE 50M,


           GROUP 2 ('/u01/app/oradata/NEWDB//redo02.log') SIZE 50M,


           GROUP 3 ('/u01/app/oradata/NEWDB/redo03.log') SIZE 50M


   MAXLOGFILES 5


   MAXLOGMEMBERS 5


   MAXLOGHISTORY 1


   MAXDATAFILES 100


   MAXINSTANCES 1


   CHARACTER SET US7ASCII


   NATIONAL CHARACTER SET AL16UTF16


   DATAFILE '/u01/app/oradata/NEWDB/system01.dbf' SIZE 325M REUSE


   EXTENT MANAGEMENT LOCAL


   SYSAUX DATAFILE '/u01/app/oradata/NEWDB//sysaux01.dbf' SIZE 325M REUSE


   DEFAULT TEMPORARY TABLESPACE tempts1


      TEMPFILE '/u01/app/oradata/NEWDB/temp01.dbf'


      SIZE 20M REUSE


   UNDO TABLESPACE undotbs


      DATAFILE '/u01/app/oradata/NEWDB/undotbs01.dbf'


      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;


3执行脚本


SQL> CREATE DATABASE NEWDB


   USER SYS IDENTIFIED BY oracle


   USER SYSTEM IDENTIFIED BY oracle


   LOGFILE GROUP 1 ('/u01/app/oradata/NEWDB/redo01.log') SIZE 50M,


           GROUP 2 ('/u01/app/oradata/NEWDB//redo02.log') SIZE 50M,


           GROUP 3 ('/u01/app/oradata/NEWDB/redo03.log') SIZE 50M


   MAXLOGFILES 5


   MAXLOGMEMBERS 5


   MAXLOGHISTORY 1


   MAXDATAFILES 100


   MAXINSTANCES 1


   CHARACTER SET US7ASCII


   NATIONAL CHARACTER SET AL16UTF16


   DATAFILE '/u01/app/oradata/NEWDB/system01.dbf' SIZE 325M REUSE


   EXTENT MANAGEMENT LOCAL


   SYSAUX DATAFILE '/u01/app/oradata/NEWDB//sysaux01.dbf' SIZE 325M REUSE


   DEFAULT TEMPORARY TABLESPACE tempts1


      TEMPFILE '/u01/app/oradata/NEWDB/temp01.dbf'


      SIZE 20M REUSE


   UNDO TABLESPACE undotbs


      DATAFILE '/u01/app/oradata/NEWDB/undotbs01.dbf'


      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;


  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22 


 


Database created.


创建数据字典


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



View created.


 


 


Synonym created.


 


 


Grant succeeded.


 


 


View created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Synonym created.


 


 


Grant succeeded.


 


 


Grant succeeded.


 


 


View created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Synonym created.


 


 


Grant succeeded.


 


 


View created.


 


 


Synonym created.


 


 


Grant succeeded.


 


 


Commit complete.


 


 


View created.


 


 


Synonym created.


 


 


Grant succeeded.


 


 


View created.


 


 


Synonym created.


 


 


Grant succeeded.


 


 


View created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Comment created.


 


 


Synonym created.


 


 


Grant succeeded.


 


 


PL/SQL procedure successfully completed.


 


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



Synonym created.


 


 


Grant succeeded.


 


 


Library created.


 


 


Package created.


 


 


Package body created.


 


 


Package created.


 


 


Package body created.


 


 


Library created.


 


 


Function created.


 


 


PL/SQL procedure successfully completed.


 


 


PL/SQL procedure successfully completed.


 


 


Package body created.


 


No errors.


 


Package body created.


 


 


PL/SQL procedure successfully completed.


 


 


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


……


View created.


 


 


Synonym created.


 


 


Grant succeeded.


 


SQL> connect system/oracle


Connected.


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


 


……


Synonym created.


 


DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE


                    *


ERROR at line 1:


ORA-01432: public synonym to be dropped does not exist


 


 


 


Synonym created.


 


建立help表并插入help的数据,非常值得参考


SQL> @?/sqlplus/admin/help/hlpbld.sql helpus.sql


……


View created.


 


 


58 rows created.


 


 


Commit complete.


手动建库的时候执行到最后,需要使用system用户执行SQL脚本  ,但在执行hlpbld.sql的时候要求输入参数,
  @?/sqlplus/admin/help/hlpbld.sql
?
代表oracle home
实际就是创建了一张名为help的表,用于支持help命令,而hlpbld.sql本身是没有插入数据的语句的,所以需要传递一个参数,而这个参数实际上就是插入help数据的脚本。将helpus.sql作为输入参数的命令 就是

 @?/sqlplus/admin/help/hlpbld.sql helpus.sql 
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29563200/viewspace-1132779/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-03-24

  • 博文量
    4
  • 访问量
    8166