oracle五部曲深入數(shù)據(jù)庫

1、Oracle 11GR2 install in with 版本OS Oracle 11GR2 install in with 版本OS Oracle ：OracleDatabase11g rise EditionRelease .0- 64bitlinux.x64_11gR2_database_1of2.zip+ Virtualbox總體架構(gòu)目 錄一總體架二運(yùn)行環(huán)三創(chuàng)建虛擬LinuxR5U7Win8 安裝Oracle Linux 操作系四五配置OracleLinux操作系六安裝Oracle數(shù)據(jù)設(shè)置Oracle安裝Oracle Linux 操作系四五配置OracleLinux操作系六安裝Orac

2、le數(shù)據(jù)設(shè)置Oracle和OEM隨機(jī)啟七八設(shè)置Oracle數(shù)據(jù)二運(yùn)行環(huán)OS Oracle ：OracleDatabase11g rise EditionRelease .0- 64bitlinux.x64_11gR2_database_1of2.zip+ Virtualbox1.5G，20GBIP Address eth0: 00 DefaultGatewayeth0: 三 創(chuàng)建虛擬1.Oracle VMVirtual 操作系統(tǒng)：OracleLinux版本：操作系統(tǒng)：OracleLinux版本：Oracle 64bitOracleLinux操作系統(tǒng)OEL 的linuxwindows windo

3、ws 是 64linux64or32windows32位那么linux 32 位哦！ Oracle 便于處理數(shù)據(jù)，如果你的內(nèi)存很霸氣，那么就無須再忍，3G 4G VDI VDI RACC 20G C 20G 備份的位置：virtualbox C備份的位置：virtualbox C 22 CPURACRAC212（啟動(dòng)網(wǎng)絡(luò)連接就可以編輯，Bridged 模式）USB 裝Oracle Linux 操作系33. Virtualbox Ctrl VirtaulboxVirtaulbox CPU 光盤介質(zhì)檢查通過，點(diǎn)擊“光盤介質(zhì)檢查通過，點(diǎn)擊“OKEglsh鍵盤系統(tǒng)，保持默認(rèn)，U.SEnglish鍵盤系

4、統(tǒng)，保持默認(rèn)，U.SEnglishsda 設(shè)備上的分區(qū)表是不可到達(dá)的，創(chuàng)建一個(gè)新分區(qū)表必須初始化但這樣會(huì)格式化所有數(shù)IP 地址，使用哪個(gè)網(wǎng)卡就IP IP 地址，使用哪個(gè)網(wǎng)卡就IP 地址IPAddresseth0:00 IP（RAC VIP 必須使用網(wǎng)關(guān)DefaultGatewayeth0:（RAC VIP 必須使用網(wǎng)關(guān)DefaultGatewayeth0:點(diǎn)擊“EditIPIPv6 IP地址和子網(wǎng)掩碼Primary DNS： SecondaryDNS：（如果是RAC VIP 需要設(shè)置網(wǎng)關(guān)，否則不用設(shè)置PublicDNSDNS話還想DNS的時(shí)區(qū)：亞洲r(nóng)oot 用戶時(shí)區(qū)：亞洲r(nóng)oot 用戶：ora

5、cle6 選擇是否要安裝操作系統(tǒng)組件包，CustomizeCustomize選擇是否要安裝操作系統(tǒng)組件包，CustomizeCustomizenow DatabaseDatabaseCluster Cluster Support Support 10 點(diǎn)擊“NextOracleLinux OracleLinux 剩余時(shí)間標(biāo)注：OracleLinux 剩余時(shí)間標(biāo)注：Remainingtime20minutes60 LinuxSELinuxSELinux oracle oracle linux linux Finish輸入用戶名輸入用戶名Virtualboxlinux Virtualbox 會(huì)彈出

6、一個(gè)光盤圖標(biāo)并打開文件夾點(diǎn)擊“autorun.shVirtualboxVirtualbox會(huì)彈出一個(gè)光盤圖標(biāo)并打開文件夾點(diǎn)擊“autorun.shVirtualboxVirtualboxVirtualbox置OracleLinux操作系前期-rootleonarding1app#df-UsedAvail Use%Mounted028% 25% 0%rootleonarding1app#mount-oremount-osize=1500mrootleonarding1置OracleLinux操作系前期-rootleonarding1app#df-UsedAvail Use%Mounted028%

7、 25% 0%rootleonarding1app#mount-oremount-osize=1500mrootleonarding1app#df-UsedAvailUse%Mounted0/ swap，而/dev/shm虛擬內(nèi)存文件系統(tǒng)呢1.3G 可以給其他文件使用，如果在/dev/shm200M 0 (1).tmpfs IO 速度超快，因?yàn)檫@個(gè)系統(tǒng)下的文件完全駐留在內(nèi)存中，讀寫文件即可，oracle 會(huì)用它裝一些日志文件 Red Hatrise Linux Serverrelease 5.7 1.hosts hosts 文件呢，hosts ip hosts ip rootleonardin

8、g1etc#vim#Donotremovethefollowing line,orvarious #trequirenetworkfunctionalitywill #Network- leonarding1(00)56(84)bytes of64bytes fromleonarding1(00):icmp_seq=1ttl=64time=0.069ms 64bytes fromleonarding1(00):icmp_seq=2ttl=64time=0.155 - sistics -2packets transmitted,2received,0%packetloss,time leonar

9、ding1(00)56(84)bytes of64bytesfromleonarding1(00):icmp_seq=1ttl=64time=0.152 ms 64bytes fromleonarding1(00):icmp_seq=2ttl=64time=0.071- sistics -2packets transmitted,2- sistics -2packets transmitted,2received,0%packetloss,time leonarding1(00)56(84)bytes of64bytesfromleonarding1(00):icmp_seq=1ttl=64t

10、ime=0.152 ms 64bytes fromleonarding1(00):icmp_seq=2ttl=64time=0.071- sistics -2packets transmitted,2received,0%packetloss,time 這條語句中 leonarding1 ，NTP2345rootleonarding1etc#chkconfig-listdrootleonarding1etc#chkconfig-2345開機(jī)都啟動(dòng)rootleonarding1etc#serviceiptablesrootleonarding1etc#Shuttingdownsm-Shuttin

11、gdown:35 35 rootleonarding1etc#chkconfig-level35iptables rootleonarding1etc#chkconfig-level35rootleonarding1etc#chkconfig-list35 d35 rootleonarding1etc#chkconfig-ntp 服務(wù)ntprootleonarding1app#servicentpdrootleonarding1app#servicentpdrootleonarding1app#chkconfigntpdrootleonarding1etc#groupadd-g501oinst

12、all rootleonarding1 etc# groupadd -g 502 dbarootleonarding1 etc# useradd -m -u 501 -g oinstall -G dba -d /home/oracle -s /bin/bash -OracleSoftwareOwner-UID-指定用戶登錄 ，默認(rèn)為 -oracle rootleonarding1etc#passwdChangingpasswordforuseroracle. New UNIX password:BASSWORD:it-oracle rootleonarding1etc#passwdChangi

13、ngpasswordforuseroracle. New UNIX password:BASSWORD:itisbasedona dictionaryRetypenewUNIX rootleonarding1etc#mkdir-p3 775 n-R oracle:oinstall od-R 775 rootleonarding1/#cdrootleonarding1u01#ll total 4drwxrwxr-x3oracleoinstall4096Nov1318:43app rootleonarding1 u01# cd app rootleonarding1 app# lltotaldrw

14、xrwxr-x2oracleoinstall4096Nov1318:43rootleonarding1etc#vim#Controls umshared segment size, in kernel.shmmax=#Controls kernel.shmallumnumberof sharedmemory segments, inkernel.shmmni=kernel.sem= 250 32000100 fs.file-max =net.ipv4.ip_local_port_range=900065500 net.core.rmem_default = 4194304 net.core.r

15、mem_max = 4194304net.core.wmem_default = 1048576 net.core.wmem_max = 1048576 fs.aio-max-nr = 1048576 rootleonarding1#/sbin/sysctlp net.ipv4.ip_forward = 0net.ipv4.conf.default.rp_filter = 2 kernel.sysrq = 0= s = gmnb= gmax = kernel.shmmax=kernel.shmall = kernel.shmmni=4096kernel.sems = gmnb= gmax =

16、kernel.shmmax=kernel.shmall = kernel.shmmni=4096kernel.sem= 25032000100 fs.file-max =net.ipv4.ip_local_port_range=900065500 net.core.rmem_default = 4194304 net.core.rmem_max = 4194304net.core.wmem_max = 1048576 fs.aio-max-nr = 1048576 rootleonarding1 # ulimit acore filedata seg size filesizepending

17、signals max memory size open filespipeIXmessagequeues real-time prioritystacksize cpu time(blocks, -c)(-e) 0(blocks,-f)(-i) (kbytes, -l) 64 (kbytes,-m)unlimited(-n) (512bytes,-p) (bytes,-q) (-r) (kbytes, -s) 8192 (seconds,-t)unlimited(-u) (kbytes, -v) (-x)maxvirtualfile 6.Oracle 用戶的rootleonarding1#v

18、imoracle soft nproc 2047 oraclehardnproc16384 oracle soft nofile 1024 oraclehardnofile65536rootleonarding1#vim64 位操作系統(tǒng)不需要這行64 rootleonarding1# catauthuser_unknown=ignore system-# pam_selinux.socloseshouldbe pam_selinux.sosystem-#pam_selinux.sorootleonarding1# catauthuser_unknown=ignore system-# pam_

19、selinux.socloseshouldbe pam_selinux.sosystem-#pam_selinux.soopenshouldonlybefollowedbystobe he userpam_selinux.sorootleonarding1#ulimitcore filedata seg size filesizepending signals max memory size open filespipeIXmessagequeues real-time prioritystackcpu(blocks, -c)(-e) 0(blocks,-f)(-i) (kbytes, -l)

20、 64 (kbytes,-m)unlimited(-n) (512bytes,-p) (bytes,-q) (-r) (kbytes, -s) 8192 (seconds,-t)unlimited(-u) (kbytes, -v) (-x)maxvirtualfile 包-binutils-elfutils-libelf-gcc-c+-glibc-glibc-devel-glibc-headers-2.* ksh-2* libaio-0.* libaio-devel-0.* libgcc-libstdc+-4.* libstdc+-devel-7.*unixODBC-2.*unixODBC-d

21、evel-packageibstdc+-33*isnotgcc-4.1.2-glibc-2.5-glibc-devel-2.5-packagelibaio-devel-0.*isnotinstalled libgcc-4.1.2-package-7.*is notgcc-4.1.2-glibc-2.5-glibc-devel-2.5-packagelibaio-devel-0.*isnotinstalled libgcc-4.1.2-package-7.*is notunixODBC-2.2.11-unixODBC-2.2.11-packageunixODBC-devel-2.*is not如

22、果發(fā)現(xiàn)有包沒有安裝，使用-pakgename rootleonarding1Server#rpm-ivhwarning: ibstdc+-33-3.2.3-61.x86_64.rpm: Header V3 DSA signature: NOKEY, key # ibstdc+-33-3.2.3-61.x86_64isalreadyrootleonarding1Server#rpm-ivhlibaio-devel-0.3.106-warning:libaio-devel-0.3.106-5.x86_64.rpm:HeaderV3DSAsignature:NOKEY,keyID# #rootleo

23、narding1Server#rpm-ivh -7.0.2-11.el5.x86_64.rpm:HeaderV3DSAsignature:NOKEY,keyID# rootleonarding1Server#rpm-ixODBC-devel-2.2.11-warning: unixODBC-devel-2.2.11-7.1.x86_64.rpm: Header V3 DSA signature: NOKEY, key 1:unixODBC-# #已經(jīng)把沒有安裝的4 包補(bǔ)齊-elfutils-libelf-gcc-c+-glibc-glibc-devel-glibc-headers-2.*已經(jīng)把

24、沒有安裝的4 包補(bǔ)齊-elfutils-libelf-gcc-c+-glibc-glibc-devel-glibc-headers-2.* ksh-2* libaio-0.* libaio-devel-0.* libgcc-libstdc+-4.* libstdc+-devel-7.*unixODBC-2.*unixODBC-devel-gcc-4.1.2-glibc-2.5-glibc-devel-2.5- unixODBC-2.2.11-unixODBC-2.2.11-裝Oracle 數(shù)據(jù)1.oracle 用戶的.bash_profile rootleonarding1 # suoracl

25、e oracleleonarding1 $ vim .bash_profile# OracleSettingsORACLE ORACLE ORACLEORACLE dumpexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/libalert 日志會(huì)有報(bào)錯(cuò)信息ORACLE 注：我添加的環(huán)境 變量比較全 ，至少有 ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH oracle 了ORACLE dumpexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/

26、libalert 日志會(huì)有報(bào)錯(cuò)信息ORACLE 注：我添加的環(huán)境 變量比較全 ，至少有 ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH oracle 了oracleleonarding1$source8uftp工具上傳的文件，放在/home/oracle oracleleonarding1$-rw-r-r-1oracleoinstall1239269270Nov1420:36-rw-r-r-1oracleoinstall1111416131Nov1420:36oracle od -R 777 3runInstalleroracle數(shù)據(jù)庫 o

27、racleleonarding1 $ cd database/ oracleleonarding1 database$ pwdoracleleonarding1database$ll total 36drwxrwxrwx12oracleoinstall 4096Aug 200920092009200920094oracle oinstall 4096Aug 2oracle oinstall 4096Aug 2oracle oinstall 4096Aug oracle oinstall 3226 Augoracleoinstall 4096Aug drwxrwxrwx14oracleoinst

28、all 4096Aug -1oracle oinstall 5402Aug oracleleonarding1database$StartingOracleUniversalChecking Tempspace:mustbeCheckingswapspace:mustbe n120 n150 Actual 6546Actual 2943Checking monitor:mustbeconfiguredtoeast256 Couldnotexecute autocheckfordisplaycolors d/usr/bin/xdpyinfo.ChecktheDISPLAYvariableisFa

29、iledoracle Couldnotexecute autocheckfordisplaycolors d/usr/bin/xdpyinfo.ChecktheDISPLAYvariableisFailedoracleDISPLAY 3.export檢查一下是否修改，必須為:0.0 ip4. echorootdisplay0.0rootleonarding1#xhost echo $DISPLAY 環(huán)境變量，如果是:0.0那么只需滿s controldisabled,ntscanconnectfromanyoracleleonarding1database$StartingOracleUniv

30、ersalChecking Tempspace:mustbeCheckingswapspace:mustbe n120 n150 Actual 6546Actual 2943Checking monitor: must be configured to east 256 Actual Preparing tolaunch OracleUniversal Installerfrom/tmp/OraInstall2012-11-14_09-07-32PM. wait root 下先執(zhí)行xhost啟動(dòng)圖形化安裝界面，滿足這個(gè)條su DBCA DBCA ServerServerServer 版本RAC

31、 產(chǎn)品語言選擇，EnglishSimplified產(chǎn)品語言選擇，EnglishSimplifiedORACLE ORACLE ORACLE patch的patch的oracle 選擇安裝數(shù)據(jù)庫是OLAP LEO1LEO1ASMM650 45%即可，這是一個(gè)經(jīng)驗(yàn)值，大家可以根據(jù)自己的內(nèi)存大小來配置，點(diǎn)擊“Character sets”Simplified Simplified le oracleoracleOEM數(shù)據(jù)庫用戶設(shè)置oracle 數(shù)據(jù)庫用戶設(shè)置oracle Oracle 管理組：dba Oracle 安裝組：Oracle 管理組：dba Oracle 安裝組：oinstallSYSDB

32、A 權(quán)限SYSOPER執(zhí)行先決條件檢查（）， 顯示有兩個(gè)包安裝失敗unixODBC-devel-2.2.11 IgnoreunixODBC-devel-2.2.11 Ignore全局設(shè)置 信息目錄信息數(shù)據(jù)庫 信息數(shù)據(jù)庫文件-數(shù)據(jù)庫文件-root2 root.shORACLE_HOMEbinOracle：ORACLE_HOMEbinOracle：置Oracle OEM 隨機(jī)啟1. 修改rootleonarding1#vim(N2. 修改置Oracle OEM 隨機(jī)啟1. 修改rootleonarding1#vim(N2. 修改$ORACLE_HOME/bin/dbstart oracleroot

33、oracleleonarding1$vimoracleleonarding1$vimORACLE_HOME_LISTNER ($1 修改root 用戶設(shè)置，創(chuàng)建一個(gè)啟動(dòng)文件 rootleonarding1vimetc/rc.d/init.d/oracle # chkconfig:2345 99 #description:StartupScriptfororacleDatabase # /etc/rc.d/init.d/oracleexport ORACLE_SID=LEO1case $1 inecho-startup suoracle -c oracle suoracle-c$ORACLE_H

34、OME/bin/emctlstartechostartuporacleechoecho-shutdown suoracle -c oracle suoracle-c$ORACLE_HOME/bin/emctlstoprm-f echoshutdownoracleechoechoUsage:basename $0exit exit od7555.添加oraclerootleonarding1init.d#chkconfig-addoracle rootleonarding1 init.d# exit exit od7555.添加oraclerootleonarding1init.d#chkcon

35、fig-addoracle rootleonarding1 init.d# chkconfig oracle on rootleonarding1 init.d# chkconfig -list oracleoracle隨機(jī)啟動(dòng)服務(wù)oracle 啟動(dòng)級(jí)別重啟一下系統(tǒng)測試是否成功 oracleleonarding1$cat -startuporaclesful-oracleleonarding1$ ps -ef|grepDB 00:00:0000:00:0100:00:0000:00:0000:00:0000:00:0000:00:0000:00:0000:00:0000:00:0000:00:

36、0000:00:0000:00:0000:00:0100:00:0000:00:0000:00:0000:00:0000:00:0000:00:0000:00:0000:00:00grep111111111111111111111015:25015:25015:25015:25015:25015:25015:25015:25015:25015:25015:25015:25015:25015:25015:25015:25015:25015:25015:25015:25015:25015:30oracleleonarding1$lsnrctlLSNRCTLforLinux:.0-Productio

37、non17-NOV-2012Copyright(c) 1991,2009,s.ConnectingtoSUSoftheStart Date TNSLSNR for Linux: Ver.0-Production 17-NOV-2012 15:25:150days 0hr.6min.40sec ON:LocalOSCopyright(c) 1991,2009,s.ConnectingtoSUSoftheStart Date TNSLSNR for Linux: Ver.0-Production 17-NOV-2012 15:25:150days 0hr.6min.40sec ON:LocalOS

38、Authentication ListenerLog ListeningService LEO1has 1InstanceLEO1,usREADY,has1handler(s)forthisService LEO1XDBhas1 InstanceLEO1,usREADY,has1handler(s)forthisLEO1到dcompleted，點(diǎn)擊“LoginOEMGC oracleleonarding1$sqlplus /asSQL*Plus:Release.0ProductiononSatNov1715:53:47Copyright(c) 1982,GC oracleleonarding1

39、$sqlplus /asSQL*Plus:Release.0ProductiononSatNov1715:53:47Copyright(c) 1982,2009,.OracleDatabase 11g rise EditionRelease .0- 64bitWiththePartitioning,OLAP,DataMining andRealApplicationTesting SQL select* from;OracleDatabase 11g rise EditionRelease .0- 64bitPL/SQL Release.0- .0- TNSforLinux:NLSRTL .0

40、- oracle11g SQL select* fromRealApplicationAdvancedBit-mapped indexes Connection pooling Database queuingInstead-of triggersParallel backupandParallel Parallel AdvancedBit-mapped indexes Connection pooling Database queuingInstead-of triggersParallel backupandParallel Parallel -ime tablespace Fine-gr

41、ained sChange DPlan StabilityOnlineIndexCoalesce Index MaterializedviewMaterializedviewwarehouseDatabaseresourcemanager AutomaticStorageExport transportable tablespa Fast-Start Fault RecoverySle DuplexedOLAPWindowBlockMedia Application Rolerise User OracleData OracleLabel Join indexData MiningOnline

42、 OracleLabel Join indexData MiningOnline File MapBlockChangeTracking Flashback TableFlashback Database Backup EncryptionOracleDatabase Vault Result CacheSQL Plan SecureFiles Encryption Flashback Data ArchiveActive DataServerFlash Cache 65 -置Oracle 數(shù)據(jù)1.設(shè)置 SQLPLUS 命令行提示符樣oracleleonarding1 $ vim $ORACL

43、E_HOME/sqlplus/admin/glogin.sqlglogin.sql全局配在該文件 glogin.sql 尾部添加如下內(nèi)setsqlprompt_user_connect_identifier測試設(shè)置效oracleleonarding1$sqlplus /as2.檢查 instance 信SYSLEO1selectus fromS實(shí)例名：LEO1STOPPED 2.檢查 instance 信SYSLEO1selectus fromS實(shí)例名：LEO1STOPPED 3.檢查是否歸檔 SYSLEO1archiveloglist Database log mode Automatic

44、archivalArchiveOldestonlinelogsequence Current log sequenceNoArchive 74.檢查 database 信SYSLEO1selectname,log_mode,open_modefromREAD數(shù)據(jù)庫名：LEO15.表空間使用 表 空 間 名 ,totalspace 總空 間 M,freespace M,round(1-freespace/totalspace)*100,2) 使用率% 余空間(select tablespace_name,round(sum(bytes)/1024/1024) totalspace from db

45、a_data_files group tablespace_name) df,(select tablespace_name,round(sum(bytes)/1024/1024) freespace from dba_free_space group tablespace_name) fswheredf.tablespace_name=fs.tablespace_nameorderbydf.tablespace_name表空間使用使用率546.數(shù)據(jù)文件目錄和重做日志目錄和控制文件目錄和臨546.數(shù)據(jù)文件目錄和重做日志目錄和控制文件目錄和臨時(shí)文件目錄都一文spfile二進(jìn)制參數(shù)文pfile純文本參數(shù)文createpfilefrom$ORACLE_HOME/dbs/告警日 7.添加表空間 leo1 并設(shè)置為數(shù)據(jù)庫默認(rèn)表空SYSLEO1createtablespaceleo1datafile/u01/app/oracle/oradata/LEO1/leo1_01.dbf sizeSYSLEO1 alterdatabasedefaulttablespaceDatabase altered. SYSLEO1setlinesize131SYSLEO1co