手工創(chuàng)建數(shù)據(jù)庫(kù)的全過(guò)程.docx

手工創(chuàng)建數(shù)據(jù)庫(kù)的全過(guò)程一，創(chuàng)建數(shù)據(jù)文件存儲(chǔ)使用文件系統(tǒng)1，創(chuàng)建pvpvcreate /dev/sdb2，創(chuàng)建vgvgcreate oravg /dev/sdb3，創(chuàng)建卷lvcreate n lvoradata L 10240 /dev/oravg4，創(chuàng)建文件系統(tǒng)及掛載mkfs.ext3 /dev/oravg/lvoradatamkdir /oradatamount /dev/oravg/lvoradata /oradatavi /etc/fstab/dev/oravg/lvoradata /oradata ext3 defaults 1 2使用祼設(shè)備1，創(chuàng)建pvpvcreate /dev/sdb2，創(chuàng)建vgvgcreate oravg /dev/sdb3，創(chuàng)建卷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，綁定視祼設(shè)備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，改變祼設(shè)備的屬主和訪問權(quán)限chown oracle:dba /dev/raw/raw*chmod 660 /dev/raw/raw*或chmod 664 /dev/raw/raw*6，使祼設(shè)備生效并在啟動(dòng)時(shí)能夠加裁/etc/init.d/rawdevices restart/sbin/chkconfig rawdevices on也可以修改/etc/sysconfig/rawdevices以開機(jī)時(shí)自動(dòng)加載裸設(shè)備/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還可以把這個(gè)命令寫在/etc/rc.local上，使每次啟動(dòng)都執(zhí)行raw命令這種方式也需要加上chown oracle:dba /dev/raw/raw*,或每次重啟動(dòng)手動(dòng)執(zhí)行。這種方式是通過(guò)啟動(dòng)服務(wù)的方式來(lái)綁定裸設(shè)備7，祼設(shè)備的讀寫及格式化不能用cp等命令操作，寫入內(nèi)容用dd命令bs是塊的大小，block sizecount是塊的數(shù)量，這兩者相乘大于裸設(shè)備的容量即可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來(lái)管理設(shè)備手動(dòng)修改/dev/raw/raw1 不能永久生效要想使得權(quán)限持久生效需要修改文件/etc/udev/permissions.d/50-udev.permissions 的第113行raw/*:root:disk:0660改成raw/*:oracle:dba:06608，數(shù)據(jù)庫(kù)使用祼設(shè)備的注意事項(xiàng)：a) 一個(gè)裸設(shè)備只能放置一個(gè)數(shù)據(jù)文件b) 數(shù)據(jù)文件的大小不能超過(guò)裸設(shè)備的大小日志文件，則裸設(shè)備最大可用大小=裸設(shè)備對(duì)應(yīng)分區(qū)大小 - 1 * 512 （保留一個(gè)redo lock）數(shù)據(jù)文件，則裸設(shè)備最大可用大小=裸設(shè)備對(duì)應(yīng)分區(qū)大小 - 2 * db_block_size（保留兩個(gè)block）為了簡(jiǎn)單起見，對(duì)所有的文件設(shè)置稱比裸設(shè)備小1M即可c) 數(shù)據(jù)文件最好不要設(shè)置稱自動(dòng)擴(kuò)展，如果設(shè)置稱自動(dòng)擴(kuò)展，一定要把maxsize設(shè)置設(shè)置為比裸設(shè)備小其它一些關(guān)于祼設(shè)備的命令a) 如何知道當(dāng)前綁定了什么祼設(shè)備raw -qa命令列出當(dāng)前綁定的所有裸設(shè)備raw -q /dev/raw/raw1 查看raw1的情況b) 如何知道某個(gè)裸設(shè)備的大小 如果是整個(gè)磁盤或分區(qū)：fdisk l /dev/hdb1fdisk l /dev/hdb2 如果是邏輯卷：lvscan /dev/vg02/ctl001lv或display /dev/vg02/ctl001lv 計(jì)算：用blockdev命令來(lái)計(jì)算：blockdev -getsize /dev/raw/raw1rootras # blockdev -getsize /dev/raw/raw1614400614400表示有多少OS BLIOCK一般一個(gè)OS BLOCK大小是512字節(jié)，所以614400*512/1024/1024/1024 = 300(m) 就是裸設(shè)備的大小c) 如何取消裸設(shè)備的綁定用raw把major and minor設(shè)成0就可以取消裸設(shè)備的綁定：raw /dev/raw/raw1 0 0這個(gè)命令取消綁定裸設(shè)備的綁定，/dev/raw/raw1會(huì)被刪除d) Linux下單個(gè)磁盤最多可以有多少個(gè)分區(qū)15個(gè)。3個(gè)主分區(qū) + 1個(gè)擴(kuò)展分區(qū) + 11個(gè)邏輯分區(qū)建議的分區(qū)方法是：先分3個(gè)主分區(qū)，第四個(gè)分區(qū)為擴(kuò)展分區(qū)，然后在擴(kuò)展分區(qū)中再分成11個(gè)邏輯分區(qū)注意，裸設(shè)備不要綁定在擴(kuò)展分區(qū)上二，編輯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三，準(zhǔn)備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四，安裝數(shù)據(jù)庫(kù)1，創(chuàng)建密碼文件$ export ORACLE_SID=tst1$ orapwd file=$ORACLE_HOME/dbs/orapw password=tst1234 entries=52，啟動(dòng)實(shí)例$ sqlplus /as sysdbaSQLstartup nomount pfile=/oracle/inittst1.ora或者startup nomount pfile=/oracle/app/oracle/product/9.2.0/dbs/inittst1.ora取決于inittst1.ora的位置3，安裝數(shù)據(jù)庫(kù)手工建庫(kù)start /oracle/CreateDB.sql備注CreateDB.sql的內(nèi)容：文件系統(tǒng)：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;祼設(shè)備：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;建庫(kù)腳本其它參數(shù)：USER SYS IDENTIFIED BY iamwangnc USER SYSTEM IDENTIFIED BY iamwangnc;建庫(kù)還可以考慮如下參數(shù)：SET TIMEZONE = time_zone_regionSET TIMEZONE = +|- hh:mmFORCE LOGGINGOMF建庫(kù)如果采用OMF管理數(shù)據(jù)庫(kù)文件，則通過(guò)以下方式建庫(kù)：SQL create database ORCLDBcontrolfile reuse ARCHIVELOGcharacter set ZHS16GBK national character set AL16UTF16undo tablespace undotbs1default temporary tablespace temp;或者直接最小方式建立(字符集根據(jù)操作系統(tǒng)會(huì)自動(dòng)指定,其它參數(shù)用默認(rèn)值)：SQL create database ORCLDB;4，運(yùn)行創(chuàng)建數(shù)據(jù)字典的腳本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;重新編譯所有失效過(guò)程：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;交互運(yùn)行emca -config dbcontrol dbemca -config dbcontrol db -repos recreate6，創(chuàng)建系統(tǒng)參數(shù)文件create spfile from pfile=/oracle/inittst1.ora或create spfile from pfile=/oracle/app/oracle/product/9.2.0/dbs/inittst1.ora這與前面有沒有拷貝有關(guān)7，將數(shù)據(jù)庫(kù)服務(wù)添加到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，將數(shù)據(jù)庫(kù)添加到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建庫(kù)必需參數(shù)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#以下為一般建庫(kù)需設(shè)置的參數(shù)，不設(shè)置就采用默認(rèn)值audit_file_dest = /u01/app/oracle/admin/WENDING/adump #不設(shè)置默認(rèn)$ORACLE_HOME/rdbms/adumpbackground_dump_dest = /u01/app/oracle/admin/WENDING/bdump #不設(shè)置默認(rèn)$ORACLE_HOME/rdbms/logcore_dump_dest = /u01/app/oracle/admin/WENDING/cdump #不設(shè)置默認(rèn)$ORACLE_HOME/rdbms/dbsuser_dump_dest = /u01/app/oracle/admin/WENDING/udump #不設(shè)置默認(rèn)$ORACLE_HOME/rdbms/logdb_domain = LK #不設(shè)置默認(rèn)為空open_cursors = 1500 #不設(shè)置默認(rèn)50processes = 500 #不設(shè)置默認(rèn)40log_archive_dest_1 = LOCATION=/orahome/arch/WENDING #不設(shè)置默認(rèn)為空，歸檔存儲(chǔ)在$ORACLE_HOME/rdbms/dbs/archlog_archive_format = log_%t_%s_%r.arc #不設(shè)置默認(rèn)為%t_%s_%r.dbfjob_queu