1. 首页
  2. IT资讯

【手工建库】手工方式建立 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官方文档的首页:http://www.oracle.com/pls/db102/homepage;(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 secooler USER SYS IDENTIFIED BY change_on_install USER SYSTEM IDENTIFIED BY manager LOGFILE 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 100M MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 MAXINSTANCES 1 CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16 DATAFILE ‘/u01/app/oracle/oradata/secooler/dfile/system01.dbf’ SIZE 325M REUSE EXTENT MANAGEMENT LOCAL SYSAUX DATAFILE ‘/u01/app/oracle/oradata/secooler/dfile/sysaux01.dbf’ SIZE 325M REUSE DEFAULT TABLESPACE tbs_1 datafile ‘/u01/app/oracle/oradata/secooler/dfile/tbs_1.dbf’ size 50m DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE ‘/u01/app/oracle/oradata/secooler/dfile/temp01.dbf’ SIZE 20M REUSE UNDO TABLESPACE undotbs DATAFILE ‘/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.dbf 1 /u01/app/oracle/oradata/secooler/dfile/temp01.dbf 2 /u01/app/oracle/oradata/secooler/dfile/undotbs01.dbf 3 /u01/app/oracle/oradata/secooler/dfile/sysaux01.dbf 4 /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 —

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

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/195326.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code