oracle手工建库窍门,【手工建库】手工方式建立 ORACLE数据库全程记录

通过dbca方式创建Oracle数据库是最常见的方法，除此之外如果对数据库的创建过程了解的话，亦可一步一步的通过命令行的方式完成数据库的创建。现将在OEL4.8操作系统上手工创建Oracle数据库实例的方法记录在此，供参考。1.手工建库前提条件既然已经到了创建数据库实例的阶段，因此主机上的数据库软件需要事先创建完毕。2.准备.bash_profile配置文件并确认环境变量1)编辑.bash_profile文件[oracle@secDB ~]$ vi .bash_profile# .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then. ~/.bashrcfi# User specific environment and startup programsPATH=$PATH:$HOME/bin:/sbin:/usr/local/binexport PATHunset USERNAMEexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1export ORACLE_SID=secoolerexport PATH=$ORACLE_HOME/bin:$PATH需要设置的环境变量主要就是这三个：ORACLE_BASE、ORACLE_HOME、ORACLE_SID和PATH需要说明的是PATH变量的设置，需要将“$ORACLE_HOME/bin”写在“$PATH”之前，这样设置主要目的是：Oracle命令优先被选择。2)使.bash_profile配置文件生效[oracle@secDB ~]$ . ./.bash_profile或[oracle@secDB ~]$ source ./.bash_profile3)确认ORACLE_SID等环境变量被正确设置[oracle@secDB ~]$ env | grep -i sidORACLE_SID=secooler[oracle@secDB ~]$ env | grep ORACLE_BASEORACLE_BASE=/u01/app/oracle[oracle@secDB ~]$ env | grep ORACLE_HOMEORACLE_HOME=/u01/app/oracle/product/10.2.0/db_13.在$ORACLE_BASE下创建所需的目录1)创建四个转储目录[oracle@secDB ~]$ cd $ORACLE_BASE[oracle@secDB oracle]$ mkdir -p admin/secooler/adump[oracle@secDB oracle]$ mkdir -p admin/secooler/bdump[oracle@secDB oracle]$ mkdir -p admin/secooler/cdump[oracle@secDB oracle]$ mkdir -p admin/secooler/udump2)创建存放数据文件、日志文件和控制文件的目录[oracle@secDB oracle]$ mkdir -p oradata/secooler/dfile[oracle@secDB oracle]$ mkdir -p oradata/secooler/lfile[oracle@secDB oracle]$ mkdir -p oradata/secooler/cfile3)创建备份介质存放的目录[oracle@secDB oracle]$ mkdir -p rmanbak4.生成密码文件[oracle@secDB ~]$ cd $ORACLE_HOME/dbs[oracle@secDB dbs]$ orapwd file=orapwsecooler password=change_on_install entries=105.创建所需的pfile和spfile1)创建pfile文件并调整我们根据Oracle安装软件$ORACLE_HOME/dbs目录下自带的init.ora来定制我们需要的pfile。(1)进入到$ORACLE_HOME/dbs目录[oracle@secDB ~]$ cd $ORACLE_HOME/dbs(2)去掉默认init.ora文件中杂乱的内容，仅保留参数本身信息[oracle@secDB dbs]$ cat init.ora |grep -v ^# |grep -v ^$ > initsecooler.ora(3)调整initsecooler.ora参数内容调整后的内容如下：[oracle@secDB dbs]$ vi initsecooler.oradb_name=secoolerdb_files = 80                       # SMALLdb_file_multiblock_read_count = 8   # SMALL#db_block_buffers = 100             # SMALL#shared_pool_size = 3500000         # SMALLlog_checkpoint_interval = 10000processes = 50                      # SMALLparallel_max_servers = 5            # SMALLlog_buffer = 32768                  # SMALLmax_dump_file_size = 10240    # limit trace file size to 5 Meg eachglobal_names = TRUE#control_files = (ora_control1, ora_control2)control_files = (/u01/app/oracle/oradata/secooler/cfile/ora_control1,/u01/app/oracle/oradata/secooler/cfile/ora_control2,/u01/app/oracle/oradata/secooler/cfile/control3)sga_max_size=300Msga_target=300M~~较之系统自带的参数文件做了如下修改动作：a.修改db_name参数为b.注销掉参数db_block_buffers、shared_pool_sizec.修改controlfile参数c.增加sga_max_size和sga_target参数，大小设置为300M。2)创建spfile并调整(1)使用sqlplus命令登陆创建spfile[oracle@secDB dbs]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 7 22:44:55 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> create spfile from pfile;(2)启动数据库到nomount状态，调整spfile参数a.启动数据库到nomount状态SQL> startup nomount;ORACLE instance started.Total System Global Area  314572800 bytesFixed Size                  1219184 bytesVariable Size              96470416 bytesDatabase Buffers          213909504 bytesRedo Buffers                2973696 bytesb.需要修改的spfile参数如下SQL> alter system set undo_management=auto scope=spfile;SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/secooler/dfile' scope=spfile;SQL> alter system set db_create_online_log_dest_1='/u01/app/oracle/oradata/secooler/lfile' scope=spfile;SQL> alter system set job_queue_processes =5 scope=spfile;SQL> alter system set background_dump_dest='/u01/app/oracle/admin/secooler/bdump' scope=spfile;SQL> alter system set core_dump_dest= '/u01/app/oracle/admin/secooler/cdump' scope=spfile;SQL> alter system set user_dump_dest='/u01/app/oracle/admin/secooler/udump' scope=spfile;SQL> alter system set audit_file_dest='/u01/app/oracle/admin/secooler/adump' scope=spfile;c.停起数据库到nomount状态使spfile调整生效SQL> shutdown immediate;ORA-01507: database not mountedORACLE instance shut down.SQL> startup nomount;ORACLE instance started.Total System Global Area  314572800 bytesFixed Size                  1219184 bytesVariable Size              96470416 bytesDatabase Buffers          213909504 bytesRedo Buffers                2973696 bytes6.创建数据库1)编写create database脚本(1)获取create database的参考脚本在Oracle文档中中有一个供参考的创建语句(1)进入到Oracle官方文档的首页：；(2)点击“Books”；(3)在列出来的参考书目中的找到第5本书“Administrator's Guide”，点击“HTML”进入；(4)搜索关键字“create database statement”，位到“Step 7: Issue the CREATE DATABASE Statement”，点击进入便可以得到创建数据库的参考脚本；2)调整参考脚本中的内容为我所用(1)需要调整的内容数据库名字SYS和SYSTEM密码路径名修改，注意一定要准确全面表空间tbs_1后面给出具体数据文件信息(2)调整后的脚本如下CREATE DATABASE secoolerUSER SYS IDENTIFIED BY change_on_installUSER SYSTEM IDENTIFIED BY managerLOGFILE GROUP 1 ('/u01/app/oracle/oradata/secooler/lfile/redo01.log') SIZE 100M,GROUP 2 ('/u01/app/oracle/oradata/secooler/lfile/redo02.log') SIZE 100M,GROUP 3 ('/u01/app/oracle/oradata/secooler/lfile/redo03.log') SIZE 100MMAXLOGFILES 5MAXLOGMEMBERS 5MAXLOGHISTORY 1MAXDATAFILES 100MAXINSTANCES 1CHARACTER SET US7ASCIINATIONAL CHARACTER SET AL16UTF16DATAFILE '/u01/app/oracle/oradata/secooler/dfile/system01.dbf' SIZE 325M REUSEEXTENT MANAGEMENT LOCALSYSAUX DATAFILE '/u01/app/oracle/oradata/secooler/dfile/sysaux01.dbf' SIZE 325M REUSEDEFAULT TABLESPACE tbs_1 datafile '/u01/app/oracle/oradata/secooler/dfile/tbs_1.dbf' size 50mDEFAULT TEMPORARY TABLESPACE tempts1TEMPFILE '/u01/app/oracle/oradata/secooler/dfile/temp01.dbf'SIZE 20M REUSEUNDO TABLESPACE undotbsDATAFILE '/u01/app/oracle/oradata/secooler/dfile/undotbs01.dbf'SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;3)使用上面的create database脚本创建数据库脚本执行之后，此时数据库状态已经为OPEN状态。SQL> select status from v$instance;STATUS------------OPEN4)设置数据文件和临时文件为自动扩展(1)获得所有数据文件和临时文件信息SQL> col name for a60SQL> select file#,name from v$datafile union select file#,name from v$tempfile;FILE# NAME---------- ------------------------------------------------------------1 /u01/app/oracle/oradata/secooler/dfile/system01.dbf1 /u01/app/oracle/oradata/secooler/dfile/temp01.dbf2 /u01/app/oracle/oradata/secooler/dfile/undotbs01.dbf3 /u01/app/oracle/oradata/secooler/dfile/sysaux01.dbf4 /u01/app/oracle/oradata/secooler/dfile/tbs_1.dbf(2)将其调整为自动扩展SQL> alter database datafile 1 autoextend on;SQL> alter database datafile 2 autoextend on;SQL> alter database datafile 3 autoextend on;SQL> alter database datafile 4 autoextend on;SQL> alter database tempfile 1 autoextend on;7.执行catalog.sql和catproc.sql脚本创建数据字典视图并配置注意这两个脚本需要以SYSDBA身份来执行[oracle@secDB ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 7 22:17:18 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> @?/rdbms/admin/catalog.sql... ...... 大约需要5分钟 ...... ...SQL> @?/rdbms/admin/catproc.sql... ...... 大约需要10分钟 ...... ...8.小结手工创建数据库相比dbca来说繁琐了很多，不过整个安装过程体现的是Oracle的运行原理，对于理解数据库细节来说有很大的裨益。Good luck.secooler10.07.07-- The End --