手工创建数据库的全过程.docx

手工创建数据库的全过程一，创建数据文件存储使用文件系统1，创建pvpvcreate /dev/sdb2，创建vgvgcreate oravg /dev/sdb3，创建卷lvcreate n lvoradata L 10240 /dev/oravg4，创建文件系统及挂载mkfs.ext3 /dev/oravg/lvoradatamkdir /oradatamount /dev/oravg/lvoradata /oradatavi /etc/fstab/dev/oravg/lvoradata /oradata ext3 defaults 1 2使用祼设备1，创建pvpvcreate /dev/sdb2，创建vgvgcreate oravg /dev/sdb3，创建卷lvcreate n ctl001lv L +300m vg01lvcreate n ctl002lv L +300m vg01lvcreate n ctl003lv L +300m vg01lvcreate n system01lv L +300m vg01lvcreate n temp01lv L +300m vg01lvcreate n redo01lv L +300m vg01lvcreate n redo02lv L +300m vg01lvcreate n redo03lv L +300m vg01.4，绑定视祼设备a) cd /devb) mkdir /dev/raw如果/dev/下没有 /raw/ 目录，可以自己手工建立c) raw /dev/raw/raw1 /dev/vg01/clt001lvraw /dev/raw/raw2 /dev/vg01/clt002lvraw /dev/raw/raw3 /dev/vg01/clt003lvraw /dev/raw/raw4 /dev/vg01/system01lvraw /dev/raw/raw5 /dev/vg01/temp01lvraw /dev/raw/raw6 /dev/vg01/undo01lvraw /dev/raw/raw7 /dev/vg01/redo01lvraw /dev/raw/raw8 /dev/vg01/redo02lv5，改变祼设备的属主和访问权限chown oracle:dba /dev/raw/raw*chmod 660 /dev/raw/raw*或chmod 664 /dev/raw/raw*6，使祼设备生效并在启动时能够加裁/etc/init.d/rawdevices restart/sbin/chkconfig rawdevices on也可以修改/etc/sysconfig/rawdevices以开机时自动加载裸设备/dev/raw/raw1 /dev/vg02/ctl001lv /dev/raw/raw2 /dev/vg02/ctl002lv /dev/raw/raw3 /dev/vg02/ctl003lv /dev/raw/raw4 /dev/vg02/syst01lv /dev/raw/raw5 /dev/vg02/temp01lv /dev/raw/raw6 /dev/vg02/undo01lv clt/dev/raw/raw7 /dev/vg02/redo01lv /dev/raw/raw8 /dev/vg02/redo02lv /dev/raw/raw9 /dev/vg02/redo03lv /dev/raw/raw10 /dev/vg02/data01lv/dev/raw/raw11 /dev/vg02/data02lv/dev/raw/raw12 /dev/vg02/data03lv/dev/raw/raw13 /dev/vg02/data04lv/dev/raw/raw14 /dev/vg02/data05lv/dev/raw/raw15 /dev/vg02/data06lv/dev/raw/raw16 /dev/vg02/data07lv/dev/raw/raw17 /dev/vg02/data08lv还可以把这个命令写在/etc/rc.local上，使每次启动都执行raw命令这种方式也需要加上chown oracle:dba /dev/raw/raw*,或每次重启动手动执行。这种方式是通过启动服务的方式来绑定裸设备7，祼设备的读写及格式化不能用cp等命令操作，写入内容用dd命令bs是块的大小，block sizecount是块的数量，这两者相乘大于裸设备的容量即可dd if=/dev/zero of=/dev/raw/raw1 bs=8192 count=12800dd if=/dev/zero of=/dev/raw/raw2 bs=8192 count=12800另外：rhel4使用udev来管理设备手动修改/dev/raw/raw1 不能永久生效要想使得权限持久生效需要修改文件/etc/udev/permissions.d/50-udev.permissions 的第113行raw/*:root:disk:0660改成raw/*:oracle:dba:06608，数据库使用祼设备的注意事项：a) 一个裸设备只能放置一个数据文件b) 数据文件的大小不能超过裸设备的大小日志文件，则裸设备最大可用大小=裸设备对应分区大小 - 1 * 512 （保留一个redo lock）数据文件，则裸设备最大可用大小=裸设备对应分区大小 - 2 * db_block_size（保留两个block）为了简单起见，对所有的文件设置称比裸设备小1M即可c) 数据文件最好不要设置称自动扩展，如果设置称自动扩展，一定要把maxsize设置设置为比裸设备小其它一些关于祼设备的命令a) 如何知道当前绑定了什么祼设备raw -qa命令列出当前绑定的所有裸设备raw -q /dev/raw/raw1 查看raw1的情况b) 如何知道某个裸设备的大小 如果是整个磁盘或分区：fdisk l /dev/hdb1fdisk l /dev/hdb2 如果是逻辑卷：lvscan /dev/vg02/ctl001lv或display /dev/vg02/ctl001lv 计算：用blockdev命令来计算：blockdev -getsize /dev/raw/raw1rootras # blockdev -getsize /dev/raw/raw1614400614400表示有多少OS BLIOCK一般一个OS BLOCK大小是512字节，所以614400*512/1024/1024/1024 = 300(m) 就是裸设备的大小c) 如何取消裸设备的绑定用raw把major and minor设成0就可以取消裸设备的绑定：raw /dev/raw/raw1 0 0这个命令取消绑定裸设备的绑定，/dev/raw/raw1会被删除d) Linux下单个磁盘最多可以有多少个分区15个。3个主分区 + 1个扩展分区 + 11个逻辑分区建议的分区方法是：先分3个主分区，第四个分区为扩展分区，然后在扩展分区中再分成11个逻辑分区注意，裸设备不要绑定在扩展分区上二，编辑init文件$vi /oracle/inittst1.ora# Copyright (c) 1991, 2001, 2002 by Oracle Corporation# # Cache and I/O#db_block_size=8192db_cache_size=33554432db_file_multiblock_read_count=16 # Cursors and Library Cache#open_cursors=300 # Database Identification#db_domain=lindb_name=tst1 # Diagnostics and Statistics#background_dump_dest=/oracle/app/oracle/admin/tst1/bdumpcore_dump_dest=/oracle/app/oracle/admin/tst1/cdumptimed_statistics=TRUEuser_dump_dest=/oracle/app/oracle/admin/tst1/udump # File Configuration#control_files=(/dev/raw/raw1, /dev/raw/raw2, /dev/raw/raw3) # Instance Identification#instance_name=tst1 # Job Queues#job_queue_processes=10 # MTS#dispatchers=(PROTOCOL=TCP) (SERVICE=tst1XDB) # Miscellaneous#aq_tm_processes=1compatible=9.2.0.0.0 # Optimizer#hash_join_enabled=TRUEquery_rewrite_enabled=FALSEstar_transformation_enabled=FALSE # Pools#java_pool_size=13886080large_pool_size=16777216shared_pool_size=83886080 # Processes and Sessions#processes=150 # Redo Log and Recovery#fast_start_mttr_target=300 # Security and Auditing#remote_login_passwordfile=EXCLUSIVE # Sort, Hash Joins, Bitmap Indexes#pga_aggregate_target=25165824sort_area_size=524288 # System Managed Undo and Rollback Segments#undo_management=AUTOundo_retention=10800undo_tablespace=UNDOTBS1三，准备inittst1.ora文件中的目录mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adumpmkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdumpmkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdumpmkdir -p $ORACLE_BASE/admin/$ORACLE_SID/dpdumpmkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udumpmkdir -p $ORACLE_BASE/admin/$ORACLE_SID/pfile四，安装数据库1，创建密码文件$ export ORACLE_SID=tst1$ orapwd file=$ORACLE_HOME/dbs/orapw password=tst1234 entries=52，启动实例$ sqlplus /as sysdbaSQLstartup nomount pfile=/oracle/inittst1.ora或者startup nomount pfile=/oracle/app/oracle/product/9.2.0/dbs/inittst1.ora取决于inittst1.ora的位置3，安装数据库手工建库start /oracle/CreateDB.sql备注CreateDB.sql的内容：文件系统：CREATE DATABASE tst1MAXINSTANCES 1MAXLOGHISTORY 1MAXLOGFILES 5MAXLOGMEMBERS 3MAXDATAFILES 200DATAFILE /oradata/tst1/system01.dbf SIZE 500M REUSE AUTOEXTEND ON NEXT 500K MAXSIZE UNLIMITEDEXTENT MANAGEMENT LOCALDEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE /oradata/tst1/temp01.dbf SIZE 500m REUSE AUTOEXTEND ON NEXT 100K MAXSIZE UNLIMITEDUNDO TABLESPACE UNDOTBS1 DATAFILE /oradata/tst1/undotbs01.dbf SIZE 520M REUSE AUTOEXTEND ON NEXT 100K MAXSIZE UNLIMITEDsysaux datafile /oradata/tst1/sysaux01.dbf size 500m reuse autoextend on next 100k maxsize unlimitedCHARACTER SET ZHS16GBKNATIONAL CHARACTER SET AL16UTF16LOGFILE GROUP 1 (/oradata/tst1/redo01.log) SIZE 512000K,GROUP 2 (/oradata/tst1/redo02.log) SIZE 512000K,GROUP 3 (/oradata/tst1/redo03.log) SIZE 512000K;祼设备：CREATE DATABASE tst1MAXINSTANCES 8MAXLOGHISTORY 100MAXLOGFILES 48MAXLOGMEMBERS 3MAXDATAFILES 4000DATAFILE /dev/raw/raw4 SIZE 290M REUSE AUTOEXTEND OFFEXTENT MANAGEMENT LOCALDEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE /dev/raw/raw5 SIZE 390M REUSE AUTOEXTEND OFFUNDO TABLESPACE UNDOTBS1 DATAFILE /dev/raw/raw6 SIZE 390M REUSE AUTOEXTEND OFFCHARACTER SET ZHS16GBKNATIONAL CHARACTER SET AL16UTF16LOGFILE GROUP 1 (/dev/raw/raw7) SIZE 290M,GROUP 2 (/dev/raw/raw8) SIZE 290M,GROUP 3 (/dev/raw/raw9) SIZE 290M;建库脚本其它参数：USER SYS IDENTIFIED BY iamwangnc USER SYSTEM IDENTIFIED BY iamwangnc;建库还可以考虑如下参数：SET TIMEZONE = time_zone_regionSET TIMEZONE = +|- hh:mmFORCE LOGGINGOMF建库如果采用OMF管理数据库文件，则通过以下方式建库：SQL create database ORCLDBcontrolfile reuse ARCHIVELOGcharacter set ZHS16GBK national character set AL16UTF16undo tablespace undotbs1default temporary tablespace temp;或者直接最小方式建立(字符集根据操作系统会自动指定,其它参数用默认值)：SQL create database ORCLDB;4，运行创建数据字典的脚本catalog和catproc是必需的，其它可选SQLstart /oracle/app/oracle/product/9.2.0/rdbms/admin/catalog.sql;SQLstart /oracle/app/oracle/product/9.2.0/rdbms/admin/catblock.sql;SQLstart /oracle/app/oracle/product/9.2.0/rdbms/admin/catproc.sql;SQLstart /oracle/app/oracle/product/9.2.0/rdbms/admin/catoctk.sql;SQLstart /oracle/app/oracle/product/9.2.0/rdbms/admin/owminst.plb;新建sqlplus属性和帮助、USERS表空间：password system 更改system用户的缺省密码为tst1234SQLconnect system/tst1234SQLstart /oracle/app/oracle/product/9.2.0/sqlplus/admin/pupbld.sqlSQLstart /oracle/app/oracle/product/9.2.0/sqlplus/admin/help/hlpbld.sql helpus.sql;SQL connect /as sysdbaSQL CREATE TABLESPACE USERS LOGGING DATAFILE /orahome/oradata/WENDING/users01.dbf SIZE 100M REUSE AUTOEXTEND ON NEXT 10m MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;SQL ALTER DATABASE DEFAULT TABLESPACE USERS;修改归档模式（可选）并重启SQL shutdown immediate;SQL connect /as sysdbaSQL startup mountSQL alter database archivelog;SQL alter database open;重新编译所有失效过程：SQL execute utl_recomp.recomp_serial();5，建立和配置EM（可选）：SQL?/sysman/admin/emdrep/sql/emreposcre /u01/app/oracle/product/10.2.0/db_1 SYSMAN iamwangnc TEMP ON;SQL alter user SYSMAN identified by iamwangnc account unlock;SQL alter user DBSNMP identified by iamwangnc account unlock;SQL host emca -config dbcontrol db -silent -DB_UNIQUE_NAME WENDING -PORT 1521 -EM_HOME /u01/app/oracle/product/10.2.0/db_1 -LISTENER LISTENER -SERVICE_NAME WENDING.LK -SYS_PWD iamwangnc -SID WENDING -ORACLE_HOME /u01/app/oracle/product/10.2.0/db_1 -DBSNMP_PWD iamwangnc -HOST vmone -LISTENER_OH /u01/app/oracle/product/10.2.0/db_1 -LOG_FILE /orahome/emConfig.log -SYSMAN_PWD iamwangnc;交互运行emca -config dbcontrol dbemca -config dbcontrol db -repos recreate6，创建系统参数文件create spfile from pfile=/oracle/inittst1.ora或create spfile from pfile=/oracle/app/oracle/product/9.2.0/dbs/inittst1.ora这与前面有没有拷贝有关7，将数据库服务添加到listener# LISTENER.ORA Network Configuration File: /oracle/app/oracle/product/9.2.0/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ras1)(PORT = 1521) ) ) )SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /oracle/app/oracle/product/9.2.0) (PROGRAM = extproc) ) )8，将数据库添加到tnsnames.ora# TNSNAMES.ORA Network Configuration File: /oracle/app/oracle/product/9.2.0/network/admin/tnsnames.ora# Generated by Oracle configuration tools.TST1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ras1)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = tst1) ) )五，init建库必需参数control_files = (/orahome/oradata/control1.ctl,/orahome/oradata/control2.ctl,/orahome/oradata/control3.ctl)undo_management = AUTOundo_tablespace = UNDOTBS1db_name = ORCLDBdb_unique_name = WENDINGdb_block_size = 8192sga_max_size = 320Msga_target = 320M#以下为一般建库需设置的参数，不设置就采用默认值audit_file_dest = /u01/app/oracle/admin/WENDING/adump #不设置默认$ORACLE_HOME/rdbms/adumpbackground_dump_dest = /u01/app/oracle/admin/WENDING/bdump #不设置默认$ORACLE_HOME/rdbms/logcore_dump_dest = /u01/app/oracle/admin/WENDING/cdump #不设置默认$ORACLE_HOME/rdbms/dbsuser_dump_dest = /u01/app/oracle/admin/WENDING/udump #不设置默认$ORACLE_HOME/rdbms/logdb_domain = LK #不设置默认为空open_cursors = 1500 #不设置默认50processes = 500 #不设置默认40log_archive_dest_1 = LOCATION=/orahome/arch/WENDING #不设置默认为空，归档存储在$ORACLE_HOME/rdbms/dbs/archlog_archive_format = log_%t_%s_%r.arc #不设置默认为%t_%s_%r.dbfjob_queu