oracle五部曲深入数据库

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总体架构目 录一总体架二运行环三创建虚拟LinuxR5U7Win8 安装Oracle Linux 操作系四五配置OracleLinux操作系六安装Oracle数据设置Oracle安装Oracle Linux 操作系四五配置OracleLinux操作系六安装Orac

2、le数据设置Oracle和OEM随机启七八设置Oracle数据二运行环OS Oracle ：OracleDatabase11g rise EditionRelease .0- 64bitlinux.x64_11gR2_database_1of2.zip+ Virtualbox1.5G，20GBIP Address eth0: 00 DefaultGatewayeth0: 三 创建虚拟1.Oracle VMVirtual 操作系统：OracleLinux版本：操作系统：OracleLinux版本：Oracle 64bitOracleLinux操作系统OEL 的linuxwindows windo

3、ws 是 64linux64or32windows32位那么linux 32 位哦！ Oracle 便于处理数据，如果你的内存很霸气，那么就无须再忍，3G 4G VDI VDI RACC 20G C 20G 备份的位置：virtualbox C备份的位置：virtualbox C 22 CPURACRAC212（启动网络连接就可以编辑，Bridged 模式）USB 装Oracle Linux 操作系33. Virtualbox Ctrl VirtaulboxVirtaulbox CPU 光盘介质检查通过，点击“光盘介质检查通过，点击“OKEglsh键盘系统，保持默认，U.SEnglish键盘系

4、统，保持默认，U.SEnglishsda 设备上的分区表是不可到达的，创建一个新分区表必须初始化但这样会格式化所有数IP 地址，使用哪个网卡就IP IP 地址，使用哪个网卡就IP 地址IPAddresseth0:00 IP（RAC VIP 必须使用网关DefaultGatewayeth0:（RAC VIP 必须使用网关DefaultGatewayeth0:点击“EditIPIPv6 IP地址和子网掩码Primary DNS： SecondaryDNS：（如果是RAC VIP 需要设置网关，否则不用设置PublicDNSDNS话还想DNS的时区：亚洲root 用户时区：亚洲root 用户：ora

5、cle6 选择是否要安装操作系统组件包，CustomizeCustomize选择是否要安装操作系统组件包，CustomizeCustomizenow DatabaseDatabaseCluster Cluster Support Support 10 点击“NextOracleLinux OracleLinux 剩余时间标注：OracleLinux 剩余时间标注：Remainingtime20minutes60 LinuxSELinuxSELinux oracle oracle linux linux Finish输入用户名输入用户名Virtualboxlinux Virtualbox 会弹出

6、一个光盘图标并打开文件夹点击“autorun.shVirtualboxVirtualbox会弹出一个光盘图标并打开文件夹点击“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虚拟内存文件系统呢1.3G 可以给其他文件使用，如果在/dev/shm200M 0 (1).tmpfs IO 速度超快，因为这个系统下的文件完全驻留在内存中，读写文件即可，oracle 会用它装一些日志文件 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开机都启动rootleonarding1etc#serviceiptablesrootleonarding1etc#Shuttingdownsm-Shuttin

11、gdown:35 35 rootleonarding1etc#chkconfig-level35iptables rootleonarding1etc#chkconfig-level35rootleonarding1etc#chkconfig-list35 d35 rootleonarding1etc#chkconfig-ntp 服务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-指定用户登录 ，默认为 -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 位操作系统不需要这行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、果发现有包没有安装，使用-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-# #已经把没有安装的4 包补齐-elfutils-libelf-gcc-c+-glibc-glibc-devel-glibc-headers-2.*已经把

24、没有安装的4 包补齐-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 数据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 日志会有报错信息ORACLE 注：我添加的环境 变量比较全 ，至少有 ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH oracle 了ORACLE dumpexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/

26、libalert 日志会有报错信息ORACLE 注：我添加的环境 变量比较全 ，至少有 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数据库 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 环境变量，如果是: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 下先执行xhost启动图形化安装界面，满足这个条su DBCA DBCA ServerServerServer 版本RAC

31、 产品语言选择，EnglishSimplified产品语言选择，EnglishSimplifiedORACLE ORACLE ORACLE patch的patch的oracle 选择安装数据库是OLAP LEO1LEO1ASMM650 45%即可，这是一个经验值，大家可以根据自己的内存大小来配置，点击“Character sets”Simplified Simplified le oracleoracleOEM数据库用户设置oracle 数据库用户设置oracle Oracle 管理组：dba Oracle 安装组：Oracle 管理组：dba Oracle 安装组：oinstallSYSDB

32、A 权限SYSOPER执行先决条件检查（）， 显示有两个包安装失败unixODBC-devel-2.2.11 IgnoreunixODBC-devel-2.2.11 Ignore全局设置 信息目录信息数据库 信息数据库文件-数据库文件-root2 root.shORACLE_HOMEbinOracle：ORACLE_HOMEbinOracle：置Oracle OEM 随机启1. 修改rootleonarding1#vim(N2. 修改置Oracle OEM 随机启1. 修改rootleonarding1#vim(N2. 修改$ORACLE_HOME/bin/dbstart oracleroot

33、oracleleonarding1$vimoracleleonarding1$vimORACLE_HOME_LISTNER ($1 修改root 用户设置，创建一个启动文件 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随机启动服务oracle 启动级别重启一下系统测试是否成功 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，点击“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 数据1.设置 SQLPLUS 命令行提示符样oracleleonarding1 $ vim $ORACL

43、E_HOME/sqlplus/admin/glogin.sqlglogin.sql全局配在该文件 glogin.sql 尾部添加如下内setsqlprompt_user_connect_identifier测试设置效oracleleonarding1$sqlplus /as2.检查 instance 信SYSLEO1selectus fromS实例名：LEO1STOPPED 2.检查 instance 信SYSLEO1selectus fromS实例名：LEO1STOPPED 3.检查是否归档 SYSLEO1archiveloglist Database log mode Automatic

44、archivalArchiveOldestonlinelogsequence Current log sequenceNoArchive 74.检查 database 信SYSLEO1selectname,log_mode,open_modefromREAD数据库名：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.数据文件目录和重做日志目录和控制文件目录和临546.数据文件目录和重做日志目录和控制文件目录和临时文件目录都一文spfile二进制参数文pfile纯文本参数文createpfilefrom$ORACLE_HOME/dbs/告警日 7.添加表空间 leo1 并设置为数据库默认表空SYSLEO1createtablespaceleo1datafile/u01/app/oracle/oradata/LEO1/leo1_01.dbf sizeSYSLEO1 alterdatabasedefaulttablespaceDatabase altered. SYSLEO1setlinesize131SYSLEO1co