Linux下面oracle环境的搭建

  1. 云栖社区>
  2. 博客>
  3. 正文

Linux下面oracle环境的搭建

技术小大人 2017-11-23 16:31:00 浏览552
展开阅读全文

标题:Linux下面oracle环境的搭建

主题:

Oracle数据库环境准备


第一部分:安装oracle软件包

1.安装

 VMware Tools


安装vmware-tools工具

步骤1、点击---->虚拟机----->安装Vmware Tools

wKiom1mQaR-yJiXuAAEND3jnwgE257.png-wh_50

步骤2、df -h

[root@server253 ~]# df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/sda2              20G  8.3G   11G  45% /

/dev/sda1              99M   12M   83M  12% /boot

tmpfs                 1.5G     0  1.5G   0% /dev/shm

/dev/scd0             2.8G  2.8G     0 100% /media/Enterprise Linux dvd 20090908


步骤3、挂在VMwareTools镜像

mount /dev/scd0 /mnt/

df -h

[root@server253 ~]# df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/sda2              20G  8.3G   11G  45% /

/dev/sda1              99M   12M   83M  12% /boot

tmpfs                 1.5G     0  1.5G   0% /dev/shm

/dev/scd0             2.8G  2.8G     0 100% /media/Enterprise Linux dvd 20090908

/dev/scd0             2.8G  2.8G     0 100% /mnt


步骤4、cp /mnt/VMwareTools… /etc/opt/

cd /opt/

ls


步骤5、解压VMwareTools包

tar -zxvf VMwareTools…

cd vmware-tools-distrib

ls


步骤6、./vmware-install.pl

一路回车

最后reboot一下


2.配置ip地址

  计算机名

  配置主机

  防火墙设置

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


1,配置IP地址

vim /etc/sysconfig/network-scripts/ifcfg-eth0

DEVICE=eth0

BOOTPROTO=none

HWADDR=00:0C:29:D3:D9:8D

ONBOOT=yes

IPADDR=192.168.1.253

NETMASK=255.255.255.0

 

/etc/init.d/network restart

chkconfig network on

ifconfig eth0

 

2,配置计算机名字

vim /etc/sysconfig/network

NETWORKING=yes

NETWORKING_IPV6=no

HOSTNAME=server253.oracle.com

 

修改临时主机名

hostname server253.oracle.com

 

3,配置主机

vim /etc/hosts

# Do not remove the following line, or    various programs

# that require network functionality    will fail.

127.0.0.1               localhost.localdomain    localhost

::1             localhost6.localdomain6    localhost6

192.168.1.253   server253.oracle.com    server253

 

ping server253.oracle.com

ping server253

 

4,防火墙设置

system-config-securitylevel

wKioL1mQaUTRzwN0AACbFVtqW7A287.png-wh_50

wKiom1mQaVPD6I6EAACfeHFf7s4666.png-wh_50

 

3.配置yum仓   库
  1. 点击虚拟机---->设置----->CD\DVD(SATA)---->使用ISO映像文件(M)---->

设备状态---->勾选上已连接

df -h

mount /dev/scd0 /mnt/

vim /etc/yum.repos.d/server.repo


[base]                                                                   

name=rhel5.4

baseurl=file:///mnt/Server

enabled=1

gpgcheck=0


4.检查软件的   必要性

Checking the Software Requirements

binutils-2.15.92.0.2-13.EL4

compat-db-4.1.25-9   -----

compat-libstdc++-296-2.96-132.7.2

control-center-2.8.0-12

gcc-3.4.3-22.1.EL4

gcc-c++-3.4.3-22.1.EL44

glibc-2.3.4-2.9

glibc-common-2.3.4-2.9

libstdc++-3.4.3-22.1

libstdc++-devel-3.4.3-22.1

make-3.80-5

pdksh-5.2.14-30

sysstat-5.0.5-1

setarch-1.6-1

 

[root@server253 ~]rpm -qa|grep compat-db

[root@server253 ~]# yum -y install  compat-db

 

[root@server253 ~]# rpm -qa|grep pdksh

[root@server253 ~]# yum -y install pdksh

 

[root@server253 ~]# rpm -qa|grep sysstat

[root@server253 ~]# yum -y install  sysstat

5.检查网络步   骤

1.   ifconfig eth0

eth0      Link encap:Ethernet  HWaddr 00:0C:29:D3:D9:8D 

          inet addr:192.168.1.253  Bcast:192.168.1.255  Mask:255.255.255.0

          inet6 addr:    fe80::20c:29ff:fed3:d98d/64 Scope:Link

          UP BROADCAST RUNNING    MULTICAST  MTU:1500  Metric:1

          RX packets:112 errors:0 dropped:0    overruns:0 frame:0

          TX packets:78 errors:0 dropped:0    overruns:0 carrier:0

          collisions:0 txqueuelen:1000

          RX bytes:14414 (14.0 KiB)  TX bytes:16767 (16.3 KiB)

          Base address:0x2000    Memory:fd5c0000-fd5e0000

  1.       vim /etc/sysconfig/network


  2.      system-config-securitylevel

wKioL1mQaW7T0Nh2AAAsEpUv94Q987.png

wKioL1mQaYjj3bcxAAAzyhvd63A965.png

 

6.配置名称解   析

vim /etc/hosts

# Do not remove the following line, or    various programs

# that require network functionality    will fail.

127.0.0.1               localhost.localdomain    localhost

::1             localhost6.localdomain6    localhost6

192.168.1.253   server253.oracle.com    server253

ping server253.oracle.com

ping server253

7. 创建安装软    件需要账户    名和组













8. 判断nobody    是否存在

Creating Required Operating System Groups  and Users

创建三个用户

[root@server253 ~]# useradd dba

[root@server253 ~]# useradd oinstall

[root@server253 ~]# useradd oper

 

[root@server253 ~]# id oracle

uid=500(oracle) gid=500(oracle)  groups=500(oracle)

 

将用户加入到组

usermod -g oinstall -G  oinstall,dba,oper,oracle oracle

[root@server253 ~]# id oracle

uid=500(oracle) gid=502(oinstall)  groups=502(oinstall),500(oracle),501(dba),503(oper)

8,Verifying that the User nobody Exists 判断nobody是否存在

原因:外部作业必须存在nobody

 

[root@server253 ~]# id nobody

uid=99(nobody) gid=99(nobody)  groups=99(nobody)

9. 内核参数

Configuring Kernel Parameters

vi /etc/sysctl.conf

kernel.shmall = 2097152

kernel.shmmax = 2147483648

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024    65000

net.core.rmem_default = 1048576

net.core.rmem_max = 1048576

net.core.wmem_default = 262144

net.core.wmem_max = 262144

-shmall :该参数表示系统依次可以使用的共享内存段的总容量(以页为单位)。默认值是2097152,通常不需要修改

-shmmax:该参数定义了单个进程能够使用的共享内存段的最大尺寸(以字节为单位),默认为32MB,对于ORACLE来说,该默认值太低了,通常将其设置为2GB

-shmmin:该内核参数用于设置系统范围内共享内存段的最大个数,该参数的默认值是4096,通常不需要更改

-sem:该参数表示设置的信号量

-file-max: 该参数表示文件句柄的最大数量,文件句柄设置表示在linux系统中可以打开的文件数量

 

[root@server253 ~]# sysctl -p

10. 配置SHELL     限制

配置SHELL限制

a,vim /etc/security/limits.conf

oracle              soft    nproc      2047

oracle              hard    nproc      16384  

oracle              soft    nofile     1024

oracle              hard    nofile     65536

 

b,vi /etc/pam.d/login

 

session    required     /lib/security/pam_limits.so

session    required     pam_limits.so

 

c,Depending on the oracle user's default  shell

vi /etc/profile

if [ $USER = "oracle" ]; then

           if [ $SHELL = "/bin/ksh" ]; then

              ulimit -p 16384

              ulimit -n 65536

           else

              ulimit -u 16384 -n 65536

           fi

fi

 

 

 

 

 

 

11. 创建所需     要的目录

创建所需要的目录

Identifying Required Software Directories

a,Oracle Base Directory--- 根目录  必须手工创建

mkdir -p /u01/app/oracle

 

b,Oracle Inventory Directory

       名字:oracle_base/oraInventory --产品清单列表

/u01/app/oracle/oraInventory  You do not need to create it. 安装自动创建

 

c,Oracle Home Directory  软件所安装的目录

   每安装一个产品都会有一相应的oralce home目录,You do not need to create  this directory.

建议你们创建

/u01/app/oracle/product/10.2.0/db_1

 

d,命令

     # mkdir -p /u01/app/oracle

     # chown -R oracle:oinstall /u01/app/oracle

     # chmod -R 775 /u01/app/oracle

   测试

      ll /u01/app

12.配置oracle    用户环境

Configuring the oracle User's Environment

su - oracle

cd /home/oracle

     vim .bash_profile

 

umask 022

        ORACLE_BASE=/u01/app/oracle 根目录

        ORACLE_SID=orcl

        export ORACLE_BASE ORACLE_SID

[root@server253 ~]# source .bash_profile

 

13.准备安装介    质

a,如何将安装介质上传到linux

b,tool

c,使用root上传,使用解压,查看权限

  unzip  包名

  chown  -R oracle:oinstall /u01

  chmod  -R 775 /u01

14. su - 

    oracle


[oracle@server253 ~]$ su - root

[root@server253 ~]# xhost +

access control disabled, clients can  connect from any host

 

[root@server253 ~]# su - oracle

[oracle@server253 ~]$ xclock

wKiom1mQairiGYO8AAAYX-F3kvk138.png

 

15.

安装你的数据库软件

 

 

 

 

16.安装过程要    做的事情

 

[oracle@server253 ~]$ cd /u01/database/

[oracle@server253 database]$  ./runInstaller

wKioL1mQaj2hf3uIAARqP46YPDA309.png

a,vim /home/oracle/.base_profile

 

umask 022

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

ORACLE_SID=orcl

PATH=$PATH:$HOME/bin

 

export PATH ORACLE_BASE ORACLE_SID    ORACLE_HOME

 

b,不要选默认创建数据库,这里只是安装数据库软件

 

c.最后以root身份执行2个脚本:

    orainstRoot.sh==用来更新最终的产品清单信息

    root.sh ==根据当前主机的信息生成一些使用脚本,如dbca

 

[oracle@server253 ~]$ source  .bash_profile

[oracle@server253 ~]$ cd /u01/database/

[oracle@server253 database]$  ./runInstaller

wKioL1mQanLxvsT9AAShyfHuNcM124.png-wh_50

wKiom1mQbN-D52DAAAShyfHuNcM577.png

wKioL1mQa_Oy1LnLAAQrqY2jIhc097.png

wKioL1mQbArxAPydAANESLP2sxI732.png

wKioL1mQa7CxV7DhAAIlVIEbxH8660.png

wKioL1mQbEDTHweLAAEyJkttKtA140.png

[root@localhost /]#  /u01/app/oracle/oraInventory/orainstRoot.sh

[root@localhost /]#  /u01/app/oracle/product/10.2.0/db_1/root.sh

wKiom1mQbFWSFCkvAAI8fDMKuDs801.png


wKioL1mQbWCQNPlYAAA-aiEQMvY083.png



第二部分:创建侦听




[oracle@server253 ~]$ netca

-bash: netca: command not found

 

查看netca所在的路径

[oracle@server253 bin]$ ls netca

netca

[oracle@server253 bin]$ pwd

/u01/app/oracle/product/10.2.0/db_1/bin

 

[oracle@server253 ~]$ vim .bash_profile

umask 022

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

ORACLE_SID=orcl

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

export PATH ORACLE_BASE ORACLE_SID    ORACLE_HOME                                                      

 

[oracle@server253 ~]$ source .bash_profile

 

[oracle@server253 ~]$ netca

wKioL1mQbZzwWpmoAAIve2-9fFw100.png

wKioL1mQbc6T0DCGAAIvcbU0B4E281.png

wKioL1mQbc_QySohAAIQ0g_jiLQ862.png

wKiom1mQbdDw8ZszAAIsZEEnDKo381.png

wKioL1mQbdGijwfvAAIbWQPDFm4623.png

wKiom1mQbdLRAuoeAAHwYcXBxiQ032.png

wKiom1mQbdPiHdW8AAHrzkJHRSw488.png

wKioL1mQbdSTtX8XAAJBh1REQ3U682.png







[oracle@server253 ~]$ lsnrctl status

[oracle@server253 ~]$ netstat -tunlp  |grep 1521

(Not all processes could be identified,  non-owned process info

 will not be shown, you would have to be root  to see it all.)

tcp         0      0 0.0.0.0:1521                0.0.0.0:*                   LISTEN      14633/tnslsnr      

 

 



第三部分:创建数据库




------------创建数据库-----dbca--

一是采用脚本的方式

二是采用图形界面

[oracle@server253 ~]$ dbca

wKioL1mQbiPyeKOQAAFaiBGkiao406.png

wKioL1mQbmGQoc10AAFn1jS1Wx0071.png

wKiom1mQbmKw_I3hAAE9pYgsYiM311.png


使用DBCA创建第一个数据库:orcl 

全局数据库名:数据库名+域名

oracle sid:  实例名

默认实例名和数据库名一样,也可以不一样

wKiom1mQbmPSRdJBAAGBTPlGKeY974.png


enterprise manager (EM)

EM采用网页形式对数据库进行管理

有2种类型的EM:


一是采用GRID CONTROL-它可以管理多台主机以及多个数据库,必须在主机上安装agent

二是采用Database control 只能管理一个数据库


数据库管理方式

1 grid control 默认不可选,原因:你没有配置grid contol agent 

2 dbconsole 管理

wKioL1mQbmOBPzIUAAGS2XjpaPk931.png

wKioL1mQbmTQ_6vKAAGHrfCIEmQ561.png

wKiom1mQbmWgPObfAAGhwslPmPI450.png

wKiom1mQbmXzfpnmAAF7sb4c5yk294.png

wKioL1mQbmagnmPsAAGABGFqZSE945.png

一定要选择此模板

wKioL1mQbmaBsncBAAFeGHNfLXc623.png

wKiom1mQbmfD7XHiAAGnErt7NeY135.png

wKiom1mQbmjQfCVVAAHCTUoMV1I716.png

wKioL1mQbmnRnBglAAESarJaJCk307.png

wKioL1mQbmnjEmwAAAFqwP2WVoM721.png

wKiom1mQbmqgaT3NAADSTCJWJkc324.png

wKiom1mQbmuitSFwAAGlJWSeeM4437.png

wKioL1mQbmuzuWaMAAFEq-9lUME043.png




查看创建的数据库信息

[oracle@server253 orcl]$ ls

adump   bdump  cdump  dpdump   pfile  udump

[oracle@server253 orcl]$ pwd

/u01/app/oracle/admin/orcl

 


cd admin/实例名/            审计 跟踪 警告日志

[oracle@server253 admin]$ cd orcl/bdump/

[oracle@server253 bdump]$ ls

alert_orcl.log  orcl_lgwr_16683.trc  orcl_lgwr_16778.trc  orcl_lgwr_16845.trc

 


cd /u01/app/oracle/oradata/实例名/    数据库文件


这里十二个文件要写脚本

[oracle@server253 orcl]$ ls

control01.ctl  example01.dbf  redo03.log    temp01.dbf

control02.ctl  redo01.log     sysaux01.dbf  undotbs01.dbf

control03.ctl  redo02.log     system01.dbf  users01.dbf

[oracle@server253 orcl]$ pwd

/u01/app/oracle/oradata/orcl



ps -elf |grep ora


[oracle@server253 dbs]$ ps -elf|grep ora

0 S root      3548  3523  0  78   0 -   494 stext  Aug13 ?        00:00:10 hald-addon-storage: polling /dev/scd0

4 S root      9164  6840  0  77   0 -  1230 wait   Aug13 pts/2    00:00:00 su - oracle

4 S oracle    9165  9164  0  76   0 -  1135 wait   Aug13 pts/2    00:00:00 -bash

4 S root      9261  9216  0  77   0 -  1230 wait   Aug13 pts/2    00:00:00 su - oracle

4 S oracle    9262  9261  0  75   0 -  1135 wait   Aug13 pts/2    00:00:00 -bash

4 S root      9893  9753  0  77   0 -  1230 wait   Aug13 pts/2    00:00:00 su - oracle

4 S oracle    9894  9893  0  75   0 -  1135 wait   Aug13 pts/2    00:00:00 -bash

4 S root     10010  9967  0  77   0 -  1230 wait   Aug13 pts/2    00:00:00 su - oracle

4 S oracle   10011 10010  0  75   0 -  1135 wait   Aug13 pts/2    00:00:00 -bash

4 S root     14109  4694  0  77   0 -  1230 wait   Aug13 pts/1    00:00:00 su - oracle

4 S oracle   14110 14109  0  76   0 -  1135 -      Aug13 pts/1    00:00:00 -bash

0 S oracle   14633     1  0  76   0 - 10567 stext  Aug13 ?        00:00:00 /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit

0 S oracle   16837     1  0  78   0 - 250810 -     Aug13 ?        00:00:00 ora_pmon_orcl

0 S oracle   16839     1  0  78   0 - 250657 -     Aug13 ?        00:00:00 ora_psp0_orcl

0 S oracle   16841     1  0  78   0 - 250657 -     Aug13 ?        00:00:00 ora_mman_orcl

0 S oracle   16843     1  0  78   0 - 251174 -     Aug13 ?        00:00:00 ora_dbw0_orcl

0 S oracle   16845     1  0  76   0 - 254545 -     Aug13 ?        00:00:01 ora_lgwr_orcl

0 S oracle   16847     1  0  78   0 - 250781 -     Aug13 ?        00:00:03 ora_ckpt_orcl

0 S oracle   16849     1  0  77   0 - 251051 -     Aug13 ?        00:00:00 ora_smon_orcl

0 S oracle   16851     1  0  80   0 - 250657 -     Aug13 ?        00:00:00 ora_reco_orcl

0 S oracle   16853     1  0  75   0 - 251056 -     Aug13 ?        00:00:01 ora_cjq0_orcl

0 S oracle   16855     1  0  78   0 - 251353 -     Aug13 ?        00:00:00 ora_mmon_orcl

0 S oracle   16857     1  0  78   0 - 250657 -     Aug13 ?        00:00:00 ora_mmnl_orcl

0 S oracle   16859     1  0  78   0 - 250817 -     Aug13 ?        00:00:00 ora_d000_orcl

0 S oracle   16861     1  0  75   0 - 250809 -     Aug13 ?        00:00:00 ora_s000_orcl

0 S oracle   16866     1  0  79   0 - 250657 -     Aug13 ?        00:00:00 ora_qmnc_orcl

0 S oracle   17090     1  0  78   0 - 251063 -     Aug13 ?        00:00:02 ora_j000_orcl

0 S oracle   17811     1  0  75   0 - 251044 -     Aug13 ?        00:00:00 ora_q000_orcl

0 S oracle   17813     1  0  78   0 - 250656 -     Aug13 ?        00:00:00 ora_q001_orcl

0 S oracle   17856     1  0  75   0 -  2031 -      Aug13 pts/1    00:00:00 /u01/app/oracle/product/10.2.0/db_1/perl/bin/perl /u01/app/oracle/product/10.2.0/db_1/bin/emwd.pl dbconsole /u01/app/oracle/product/10.2.0/db_1/server253.oracle.com_orcl/sysman/log/emdb.nohup

0 S oracle   17881 17856  0  78   0 - 148091 stext Aug13 pts/1    00:00:29 /u01/app/oracle/product/10.2.0/db_1/jdk/bin/java -server -Xmx256M -XX:MaxPermSize=96m -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=40 -DORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 -Doracle.home=/u01/app/oracle/product/10.2.0/db_1/oc4j -Doracle.oc4j.localhome=/u01/app/oracle/product/10.2.0/db_1/server253.oracle.com_orcl/sysman -DEMSTATE=/u01/app/oracle/product/10.2.0/db_1/server253.oracle.com_orcl -Doracle.j2ee.dont.use.memory.archive=true -Djava.protocol.handler.pkgs=HTTPClient -Doracle.security.jazn.config=/u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_server253.oracle.com_orcl/config/jazn.xml -Djava.security.policy=/u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_server253.oracle.com_orcl/config/java2.policy -Djava.security.properties=/u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/home/config/jazn.security.props -DEMDROOT=/u01/app/oracle/product/10.2.0/db_1/server253.oracle.com_orcl -Dsysman.md5password=true -Drepapi.oracle.home=/u01/app/oracle/product/10.2.0/db_1 -Ddisable.checkForUpdate=true -Djava.awt.headless=true -jar /u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/home/oc4j.jar -config /u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_server253.oracle.com_orcl/config/server.xml

0 S oracle   18060     1  0  77   0 - 253384 -     Aug13 ?        00:00:03 oracleorcl (LOCAL=NO)

0 S oracle   18062     1  0  75   0 - 251086 -     Aug13 ?        00:00:01 oracleorcl (LOCAL=NO)

0 S oracle   18064     1  0  75   0 - 251348 -     Aug13 ?        00:00:05 oracleorcl (LOCAL=NO)

0 S oracle   19902 17856  0  77   0 - 14604 stext  Aug13 pts/1    00:00:03 /u01/app/oracle/product/10.2.0/db_1/bin/emagent

0 S oracle   19945     1  0  76   0 - 251627 -     Aug13 ?        00:00:02 oracleorcl (LOCAL=NO)

0 S oracle   19956     1  0  75   0 - 251093 -     Aug13 ?        00:00:01 oracleorcl (LOCAL=NO)

0 S oracle   24484     1  0  76   0 - 251073 -     Aug13 ?        00:00:00 oracleorcl (LOCAL=NO)

0 S oracle   24953     1  0  79   0 - 251081 -     00:00 ?        00:00:00 oracleorcl (LOCAL=NO)

0 S oracle   24957     1  0  78   0 - 251084 -     00:00 ?        00:00:00 oracleorcl (LOCAL=NO)

0 R oracle   25086 10011  0  77   0 -  1065 -      00:02 pts/2    00:00:00 ps -elf

0 R oracle   25087 10011  0  78   0 -   980 -      00:02 pts/2    00:00:00 grep ora




还多了一个文件夹

[oracle@server253 ~]$ cd $ORACLE_HOME 

[oracle@server253 db_1]$ ls

assistants   has               log      oraInst.loc  server253.oracle.com_orcl

bin          hs                md       ord          slax

cdata        install           mesg     oui          sqlj

cfgtoollogs  install.platform  mgw      owm          sqlplus

clone        inventory         network  perl         srvm

config       javavm            nls      plsql        sysman

crs          jdbc              oc4j     precomp      uix

css          jdk               odbc     racg         wwg

ctx          jlib              olap     rdbms        xdk

dbs          jre               OPatch   relnotes

demo         ldap              opmn     root.sh

diagnostics  lib               oracore  root.sh.old

[oracle@server253 db_1]$ pwd

/u01/app/oracle/product/10.2.0/db_1

这里的实例在启动的时候,第一个读到的是spfileorcl.ora文件,一旦此文件丢了,你的实例就崩啦,以后排错,第一个要修复的文件就是这个文件

[oracle@server253 db_1]$ cd dbs

[oracle@server253 dbs]$ ls

hc_orcl.dat  initdw.ora  init.ora  lkORCL  orapworcl  spfileorcl.ora

[oracle@server253 dbs]$ 


如何证明创建数据库成功

[oracle@server253 ~]$ sqlplus sys/oracle as sysdba

-bash: sqlplus: command not found


[oracle@server253 ~]$ vim .bash_profile 

umask 022

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

ORACLE_SID=orcl

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin


export ORACLE_BASE ORACLE_SID ORACLE_HOME PATH


[oracle@server253 ~]$ source .bash_profile 



[oracle@server253 ~]$ sqlplus sys/oracle as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 14 00:15:00 2017


Copyright (c) 1982, 2005, Oracle.  All rights reserved.



Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options


SQL> 


方法一:

SQL> select * from tab;

3643 rows selected.


方法二:

SQL> shutdown abort

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options



[oracle@server253 ~]$ sqlplus  sys/oracle as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 14 00:22:38 2017


Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to an idle instance.


SQL> 


启动过程中有三个过程

  1. no mount

  2. mount

  3. open

如果三个过程都OK,那么数据库启动成功

SQL> startup

ORACLE instance started.


Total System Global Area  926941184 bytes

Fixed Size                  1222672 bytes

Variable Size             243271664 bytes

Database Buffers          679477248 bytes

Redo Buffers                2969600 bytes

Database mounted.

Database opened.

SQL> 




第四部分:oracle登录模式


1.数据库的连   接方式介绍

oracle登录模式

连接基本使用

  1. 连接--

1、EM-企业化管理器(图形管理)

2、isqlplus--(JAVA)

3、sqlplus

2.sys账号登录

sqlplus账号/密码  as sysdba


[oracle@server253 ~]$ sqlplus sys/oracle as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 14 06:27:25 2017


Copyright (c) 1982, 2005, Oracle.  All rights reserved.



Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options


SQL> 

 


3.sys账户采   用的是系统   身份验证

例如:

[oracle@server253 ~]$ sqlplus xiaoming/oracle as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 14 06:29:53 2017


Copyright (c) 1982, 2005, Oracle.  All rights reserved.



Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options


SQL> show user

USER is "SYS"

SQL> 



所以系统账号还可以这样登录

[oracle@server253 ~]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 14 06:31:08 2017


Copyright (c) 1982, 2005, Oracle.  All rights reserved.



Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options


SQL> 

注:只要你的oracle账号可以登录到你的系统上面来,那么你的SQL就可以登录

4.查看当前登   录的账号

SQL> show user

USER is "SYS"

5.退出登录

SQL> exit 

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

[oracle@server253 ~]$ 

6.普通账号的   登录

oracle数据库在创建的时候,提供三个账号:

sys

scott

hr


scott账号默认登录的时候,密码为tiger,但是登录时候发现账号被锁定了

[oracle@server253 ~]$ sqlplus scott/tiger


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 14 06:44:42 2017


Copyright (c) 1982, 2005, Oracle.  All rights reserved.


ERROR:

ORA-28000: the account is locked



Enter user-name: 


7.解锁普通账   号

[oracle@server253 ~]$ sqlplus / as sysdba


SQL> show user

USER is "SYS"

SQL> alter user scott account unlock ;


User altered.


[oracle@server253 ~]$ sqlplus scott/tiger


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 14 06:49:14 2017


Copyright (c) 1982, 2005, Oracle.  All rights reserved.


ERROR:

ORA-28001: the password has expired



Changing password for scott

New password: 

Retype new password: 

Password changed


Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options


SQL> show user

USER is "SCOTT"

SQL> 


8.修改普通账   号密码

SQL> show user

USER is "SCOTT"

SQL> alter user scott identified by redhat;


User altered.


注意:注意:用户自己本身也可以修改自己的密码,一般情况是不被允许的


9.同时解锁和   修改密码

SQL> alter user scott account unlock identified by oracle;

alter user scott account unlock identified by oracle

                                              *

ERROR at line 1:

ORA-01031: insufficient privileges


SQL> show user;

USER is "SYS"

SQL> alter user scott account unlock identified by oracle;


User altered.


SQL> 


10.解锁hr账 号

sqlplus / as sysdba;

alter user hr account unlock identified by redhat;

exit

sqlplus hr/redhat;

show user;

11.用户之间    的切换

----conn hr/redhat---- 切换到hr账号上面


----conn / as sysdba-- 切换到sys账号上面


---conn sys/oracle as sysdba---切换到sys账号上面


SQL> show user

USER is "SYS"

SQL> alter user hr account unlock identified by redhat;


User altered.


SQL> show user;

USER is "SYS"

SQL> conn scott/oracle

Connected.

SQL> show user;

USER is "SCOTT"

SQL> 

12.帮助文件

--------------------------帮助-----------------------------------


---help index ---帮助索引


---?shutdown---- 查找shutdown的使用功能


---?set-----查找set的使用功能


SQL> help index


Enter Help [topic] for help.


 @             COPY         PAUSE                    SHUTDOWN

 @@            DEFINE       PRINT                    SPOOL

 /             DEL          PROMPT                   SQLPLUS

 ACCEPT        DESCRIBE     QUIT                     START

 APPEND        DISCONNECT   RECOVER                  STARTUP

 ARCHIVE LOG   EDIT         REMARK                   STORE

 ATTRIBUTE     EXECUTE      REPFOOTER                TIMING

 BREAK         EXIT         REPHEADER                TTITLE

 BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE

 CHANGE        HELP         RESERVED WORDS (PL/SQL)  VARIABLE

 CLEAR         HOST         RUN                      WHENEVER OSERROR

 COLUMN        INPUT        SAVE                     WHENEVER SQLERROR

 COMPUTE       LIST         SET

 CONNECT       PASSWORD     SHOW



SQL> ? shutdown


 SHUTDOWN

 --------


 Shuts down a currently running Oracle Database instance, optionally

 closing and dismounting a database.


 SHUTDOWN [ABORT|IMMEDIATE|NORMAL|TRANSACTIONAL [LOCAL]]



SQL> ? set


 SET

 ---


 Sets a system variable to alter the SQL*Plus environment settings

 for your current session. For example, to:

     -   set the display width for data

     -   customize HTML formatting

     -   enable or disable printing of column headings

     -   set the number of lines per page

 In iSQL*Plus, you can also use the Preferences screen to set

 system variables.


 SET system_variable value


 where system_variable and value represent one of the following clauses:


   APPI[NFO]{OFF|ON|text}                   NUM[WIDTH] {10|n}

   ARRAY[SIZE] {15|n}                       PAGES[IZE] {14|n}

   AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}      PAU[SE] {OFF|ON|text}

   AUTOP[RINT] {OFF|ON}                     RECSEP {WR[APPED]|EA[CH]|OFF}

   AUTORECOVERY {OFF|ON}                    RECSEPCHAR {_|c}

   AUTOT[RACE] {OFF|ON|TRACE[ONLY]}         SERVEROUT[PUT] {ON|OFF}

     [EXP[LAIN]] [STAT[ISTICS]]               [SIZE {n | UNLIMITED}] [FOR[MAT]

   BLO[CKTERMINATOR] {.|c|ON|OFF}             {WRA[PPED] |

   CMDS[EP] {;|c|OFF|ON}                       WOR[D_WRAPPED] |

   COLSEP {_|text}                             TRU[NCATED]}]

   CON[CAT] {.|c|ON|OFF}                   *SHIFT[INOUT] {VIS[IBLE] |

   COPYC[OMMIT] {0|n}                         INV[ISIBLE]}

   COPYTYPECHECK {ON|OFF}                  *SHOW[MODE] {OFF|ON}

   DEF[INE] {&|c|ON|OFF}                   *SQLBL[ANKLINES] {OFF|ON}

   DESCRIBE [DEPTH {1|n|ALL}]               SQLC[ASE] {MIX[ED] |

     [LINENUM {OFF|ON}] [INDENT {OFF|ON}]     LO[WER] | UP[PER]}

   ECHO {OFF|ON}                           *SQLCO[NTINUE] {> | text}

  *EDITF[ILE] file_name[.ext]              *SQLN[UMBER] {ON|OFF}

   EMB[EDDED] {OFF|ON}                      SQLPLUSCOMPAT[IBILITY] {x.y[.z]}

   ESC[APE] {\|c|OFF|ON}                   *SQLPRE[FIX] {#|c}

   FEED[BACK] {6|n|ON|OFF}                 *SQLP[ROMPT] {SQL>|text}

   FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL}  SQLT[ERMINATOR] {;|c|ON|OFF}

  *FLU[SH] {ON|OFF}                        *SUF[FIX] {SQL|text}

   HEA[DING] {ON|OFF}                      *TAB {ON|OFF}

   HEADS[EP] {||c|ON|OFF}                  *TERM[OUT] {ON|OFF}

   INSTANCE [instance_path|LOCAL]          *TI[ME] {OFF|ON}

   LIN[ESIZE] {80|n} ({150|n} iSQL*Plus)    TIMI[NG] {OFF|ON}

   LOBOF[FSET] {1|n}                       *TRIM[OUT] {ON|OFF}

   LOGSOURCE [pathname]                    *TRIMS[POOL] {OFF|ON}

   LONG {80|n}                              UND[ERLINE] {-|c|ON|OFF}

   LONGC[HUNKSIZE] {80|n}                   VER[IFY] {ON|OFF}

   MARK[UP] HTML [OFF|ON]                   WRA[P] {ON|OFF}

     [HEAD text] [BODY text] [TABLE text]   XQUERY {BASEURI text|

     [ENTMAP {ON|OFF}]                        ORDERING{UNORDERED|

     [SPOOL {OFF|ON}]                                  ORDERED|DEFAULT}|

     [PRE[FORMAT] {OFF|ON}]                   NODE{BYVALUE|BYREFERENCE|

   NEWP[AGE] {1|n|NONE}                            DEFAULT}|

   NULL text                                  CONTEXT text}

   NUMF[ORMAT] format



 An asterisk (*) indicates the SET option is not supported in iSQL*Plus.

13.启用历史    记录功能

---需要安装rlwrap包----

因为是源码包,所以有点麻烦


.gz结尾的包

所以gunzip rlwrap-0.37.tar.gz


rlwrap-0.37.tar

tar -xvf  rlwrap-0.37.tar



源代码安装其实是最简单的,不用搭建yum仓库


第一种方式:分两步解开

只要一步就可以解开带.gz的压缩包

tar -zxvf rlwrap-0.37.tar.gz


vim REDEAME   查看安装说明


搜索


:/INSTALL

/INSTALL

源码包安装方式,需要两步,其实下面就合成了一步

./configure; make install


需要root权限安装rlwrap-0.37.tar

./configure; make install


如果安装不成功需要检查这两个软件包是否有安装

ls |grep readline

readline-5.1-3.el5.i386.rpm

readline-devel-5.1-3.el5.i386.rpm


rpm -qa|grep readline



ls |grep libter

libtermcap-2.0.8-46.1.i386.rpm

libtermcap-devel-2.0.8-46.1.i386.rpm


rpm -qa|grep libter





---------检验------------

su - oracle

sqlplus / as sysdba;

selecct * from tab;

exit

发现报错,使用不了


因为rlwrap是安装在linux系统上面的,所以每次使用的时候都必须告诉oracle系统


rlwrap sqlplus / as sysdba;


现在发现是可以上翻看,下查看的


可以通过取别名来实现

alias sqlplus='rlwrap sqlplus'     不过这个是临时的



要想要永久的生效需要在oracle家目录下面配置.bash_profile文件

ls -a

vim .bash_profile

alias sqlplus='rlwrap sqlplus'      添加这一项即可


保存退出后source .bash_profile



第五部分:oracle下面的文本编辑器



为解决这个问题


SQL> select ename,sal,hiredata

  2  from emp

  3  where sal=800

  4  ;

select ename,sal,hiredata

                 *

ERROR at line 1:

ORA-00904: "HIREDATA": invalid identifier



------需要修改编辑器------



su - oracle

vim .bash_profile

EDITOR=vim

export   EDITOR

source .bash_profile 




SQL> conn scott/oracle

Connected.

SQL> show user

USER is "SCOTT"


SQL> select ename,sal,hiredata

  2  from emp

  3  where sal=800

  4  ;

select ename,sal,hiredata

                 *

ERROR at line 1:

ORA-00904: "HIREDATA": invalid identifier


敲一个ed,进行纠正编辑

SQL> ed

Wrote file afiedt.buf


  1  select ename,sal,hiredate

  2  from emp

  3* where sal=800

  4  ;



或者

SQL> ed

Wrote file afiedt.buf


  1  select ename,sal,hiredate

  2  from emp

  3* where sal=800

  4  /


ENAME             SAL HIREDATE

---------- ---------- ---------

SMITH             800 17-DEC-80


SQL> 




SQL> /


ENAME             SAL HIREDATE

---------- ---------- ---------

SMITH             800 17-DEC-80


SQL> 



!也可以退出oracle数据库


l列出oracle数据里面的缓存信息



scott模式下面所有的对象

scott这人账号里面一共有四个对象


这里的对象叫表



SQL> /


TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

DEPT                           TABLE

EMP                            TABLE

BONUS                          TABLE

SALGRADE                       TABLE




得到表里面产生的信息


SQL> select * from EMP

  2  ;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO

----------

      7369 SMITH      CLERK           7902 17-DEC-80        800

        20


      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300

        30


      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500

        30



     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO

----------

      7566 JONES      MANAGER         7839 02-APR-81       2975

        20


      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400

        30


      7698 BLAKE      MANAGER         7839 01-MAY-81       2850

        30



     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO

----------

      7782 CLARK      MANAGER         7839 09-JUN-81       2450

        10


      7788 SCOTT      ANALYST         7566 19-APR-87       3000

        20


      7839 KING       PRESIDENT            17-NOV-81       5000

        10



     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO

----------

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0

        30


      7876 ADAMS      CLERK           7788 23-MAY-87       1100

        20


      7900 JAMES      CLERK           7698 03-DEC-81        950

        30



     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO

----------

      7902 FORD       ANALYST         7566 03-DEC-81       3000

        20


      7934 MILLER     CLERK           7782 23-JAN-82       1300

        10



14 rows selected.



我现在想要知道EMP里面有哪些列?


从emp表里面得到了8个列

SQL> desc emp

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 EMPNO                                     NOT NULL NUMBER(4)

 ENAME                                              VARCHAR2(10)

 JOB                                                VARCHAR2(9)

 MGR                                                NUMBER(4)

 HIREDATE                                           DATE

 SAL                                                NUMBER(7,2)

 COMM                                               NUMBER(7,2)

 DEPTNO                                             NUMBER(2)


SQL> 



SQL> select empno,ename,sal from emp;


/    通过/可以再次执行一遍






-------------如何永久的保存--------


SQL> select ename,sal from emp;


SQL> save /u01/a.sql

Created file /u01/a.sql




注意保存一定要保存绝对路径




------------如何调用保存的脚步----------


第一种方法:@/u01/a.sql     就可以直接的调出来


第二钟方法:start /u01/a.sql




-------将保存的命令调到缓存但是不执行-------


SQL> get /u01/b.sql

  1  select ename,sal

  2  from emp

  3* where sal=3000


get调出来,但是不执行



----------------我想要临时的切换到linux环境下面怎么办-----------------------

就可以到linux环境

exit 从linux环境又切换到oracle环境




----------------直接退出oracle环境--------------

exit   在oracle环境下面直接的输入exit就可以直接的退出oracle环境





host和!功能一样




---------在不退出oracle的环境下删除u01下面的a.sql--------


SQL> rm -rf /u01/a.sql

SP2-0734: unknown command beginning "rm -rf /u0..." - rest of line ignored.

SQL> 



---在前面添加一个感叹号即可完成-----

SQL> ! rm -rf /u01/a.sql


SQL> ! ls /u01

10201_database_linux32.zip  app    database            rlwrap-0.37.tar.gz.bak

afiedt.buf                  b.sql  rlwrap-0.37.tar.gz


SQL> 


SQL> ! mkdir /u01/aa


SQL> ! ls -l /u01

total 654232

-rwxr-xr-x 1 oracle oinstall 668734007 Nov  6  2009 10201_database_linux32.zip

drwxr-xr-x 2 oracle oinstall      4096 Aug 13 00:48 aa

-rw-r--r-- 1 oracle oinstall        43 Aug 13 00:33 afiedt.buf

drwxr-xr-x 3 oracle oinstall      4096 Aug  8 22:38 app

-rw-r--r-- 1 oracle oinstall        43 Aug 13 00:32 b.sql

drwxr-xr-x 6 oracle oinstall      4096 Jul  3  2005 database

-rwxrw-rw- 1 root   root        251438 Nov 19  2011 rlwrap-0.37.tar.gz

-rwxr--r-- 1 root   root        251438 Aug 12 21:55 rlwrap-0.37.tar.gz.bak


SQL> 



---------------要求是把查询使用的命令加上查询的结果全部都保存下来----------------


SQL> spool /u01/a.txt

SQL> select ename from emp;


ENAME

----------

SMITH

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS


ENAME

----------

JAMES

FORD

MILLER


14 rows selected.


SQL> spool off       结果大功告成


SQL> !

[oracle@server253 u01]$ ls

10201_database_linux32.zip  app    database

aa                          a.txt  rlwrap-0.37.tar.gz

afiedt.buf                  b.sql  rlwrap-0.37.tar.gz.bak

[oracle@server253 u01]$ vim a.txt

[oracle@server253 u01]$ 

vim a.txt




本文转自  vbers 博客,原文链接:

http://blog.51cto.com/vbers/1955997

        如需转载请自行联系原作者



网友评论

登录后评论
0/500
评论
技术小大人
+ 关注