Oracle 11g单实例安装部署

root
233
文章
0
评论
更多
 2020年3月11日00:52:57 评论 25438字阅读84分47秒

Oracle 11g单实例安装部署

Oracle安装部署比较繁琐，一部出错就会导致安装失败，我这里把安装部署分为三部分，一类是服务器自身系统参数的设置，一部分是Oracle服务的部署，还有一类是Oracle数据库安装

配置linux系统参数

1.检查硬件环境

  • 查看内存大小，free -m

2.查看系统架构

  • uname -m

3.检查磁盘空间

  • df -h
[root@oracle ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3              12G  4.2G  6.9G  38% /
tmpfs                 1.9G  100K  1.9G   1% /dev/shm
/dev/sda1             194M   32M  153M  18% /boot
/dev/sdb1              20G  172M   19G   1% /data

增加共享内存，/dev/shm(etc/fstab,改个和物理内存一样大的size=4g）

UUID=2b05276a-8780-406f-8f71-43ece7175381 /                       ext4    defaults        1 1
UUID=d5e4fb1f-5f51-4699-ac09-6d08007c9f77 /boot             r      ext4    defaults        1 2
UUID=b17d859a-d30a-417e-a7b3-916cd169bcc1 swap                    swap    defaults        0 0
tmpfs                   /dev/shm                tmpfs   defaults,size=4g        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
/dev/sdb1               /data                   ext4    defaults        0 0

进行重新挂载查看一下

[root@oracle ~]# mount -o remount /dev/shm
[root@oracle ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3              12G  4.2G  6.9G  38% /
tmpfs                 4.0G  100K  4.0G   1% /dev/shm
/dev/sda1             194M   32M  153M  18% /boot
/dev/sdb1              20G  172M   19G   1% /data

4.检查软件环境

查看系统版本

  • uname -r
[root@oracle ~]# uname -r
2.6.32-279.el6.x86_64

 

5.创建oracle用户组于oracle用户

创建两个组，oinstall和dab。使oracle用户属于oinstall组，附属于dba组

[root@oracle ~]# useradd -g oinstall -G dba oracle;echo "123"|passwd --stdin oracle
Changing password for user oracle.
passwd: all authentication tokens updated successfully.
[root@oracle ~]# id oracle
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba)

6.配置系统内核参数

vim /etc/sysctl.conf

[root@oracle ~]# egrep -v "^#|^$" /etc/sysctl.conf 
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
net.bridge.bridge-nf-call-ip6tables = 0
net.bridge.bridge-nf-call-iptables = 0
net.bridge.bridge-nf-call-arptables = 0
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296

在/etc/sysctl.conf里添加下面参数

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = ‬943,718.4                物理内存的90%/4096
kernel.shmmax =3,865,470,566.4    物理内存的90%
kernel.shmmni = 4096                   段页
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
如果系统内的参数值比如下给出的值大,那么无需修改对应的参数
保存并使之生效： /sbin/sysctl -p

‭4 × 1024 × 1024 × 1024 × 0.9 =‬ 3,865,470,566.4

‭4 × 1024 × 1024 × 1024 × 0.9 ÷ 4096 =‬943,718.4

[root@oracle ~]# /sbin/sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
net.bridge.bridge-nf-call-ip6tables = 0
net.bridge.bridge-nf-call-iptables = 0
net.bridge.bridge-nf-call-arptables = 0
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 943718
kernel.shmmax = 3865470566
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

7.对Oracle账户进行资源限制

vi /etc/security/limits.conf

最下方添加：

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240

[root@oracle ~]# vim /etc/security/limits.conf 
oracle     soft    nproc   2047
oracle     hard    nproc   16384
oracle     soft    nofile  1024
oracle     hard    nofile  65536
oracle     soft    stack   10240

 

8.创建oracle软件安装目录

[root@oracle ~]# mkdir -p /data/oracle/product/11.2.0.4/db_1

[root@oracle ~]# chown -R oracle.oinstall /data
[root@oracle ~]# ll /data
total 20
drwx------ 2 root   root     16384 Mar 10 17:30 lost+found
drwxr-xr-x 2 oracle oinstall  4096 Mar 10 19:05 oracle

 

9.设置oracle用户环境变量

su - oracle

vi .bash_profile
export ORACLE_BASE=/data/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1
export ORACLE_SID=prod
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

[oracle@oracle ~]$ source .bash_profile 
[oracle@oracle ~]$ echo $ORACLE_HOME
/data/oracle/product/11.2.0.4/db_1

10.修改hosts文件

[root@oracle ~]# vim /etc/hosts

192.168.5.60 oracle

11.关闭防火墙，selinux

[root@oracle ~]# chkconfig iptables off

[root@oracle ~]# chkconfig ip6tables off

[root@oracle ~]# chkconfig libvirtd

[root@oracle ~]# chkconfig --list iptables
iptables 0:off 1:off 2:off 3:off 4:off 5:off 6:off

[root@oracle ~]# chkconfig --list libvirtd
libvirtd 0:off 1:off 2:off 3:off 4:off 5:off 6:off

chkconfig libvirtd off(如果是redhat6/centos6上面,还需关闭此服务,否则iptables还会自动启动)

vi /etc/selinux/config
SELINUX=disabled

12.关闭一些不必要的服务

为了使系统启动更快一些,可以关闭一些不必要的服务
chkconfig sendmail off
chkconfig cups off

做完上面重启一下系统

配置VNC远程安装数据库

使用VNC的好处，再windows上进行操作的时候网络断开，也不会影响到数据库的配置

安装步骤：

检查是否安装VNC，没有进行下载tigervnc-server服务端

[root@oracle ~]$ rpm -qa|grep vnc

[root@oracle ~]$ yum -y install tigervnc-server

现在切换Oracle用户配置vnc密码

[root@oracle ~]# su - oracle

输入vnc后两下tab建
[oracle@oracle ~]$ vnc
vncconfig  vncpasswd  vncserver  

[oracle@oracle ~]$ vncpasswd
Password:
Verify:

[oracle@oracle ~]$ cd .vnc
[oracle@oracle .vnc]$ ll
total 4
-rw------- 1 oracle oinstall 8 Mar 11 08:56 passwd

开启VNC一个590X端口

vncserver :1   5901

vncserver :2   5902

出现报错：

[oracle@oracle ~]$ vncserver :1

WARNING: The first attempt to start Xvnc failed, possibly because the font
catalog is not properly configured.  Attempting to determine an appropriate
font path for this system and restart Xvnc using that font path ...
Could not start Xvnc.


Xvnc TigerVNC 1.1.0 - built Mar 22 2017 18:06:28
Copyright (C) 1999-2011 TigerVNC Team and many others (see README.txt)
See http://www.tigervnc.org for information on TigerVNC.
Underlying X server release 11704000, The X.Org Foundation


Wed Mar 11 09:58:56 2020
 vncext:      VNC extension running!
 vncext:      Listening for VNC connections on all interface(s), port 5901
 vncext:      created VNC server for screen 0
(EE) 
Fatal server error:
(EE) could not open default font 'fixed'(EE) 

Xvnc TigerVNC 1.1.0 - built Mar 22 2017 18:06:28
Copyright (C) 1999-2011 TigerVNC Team and many others (see README.txt)
See http://www.tigervnc.org for information on TigerVNC.
Underlying X server release 11704000, The X.Org Foundation


Wed Mar 11 09:58:59 2020
 vncext:      VNC extension running!
 vncext:      Listening for VNC connections on all interface(s), port 5901
 vncext:      created VNC server for screen 0
(EE) 
Fatal server error:
(EE) could not open default font 'fixed'(EE)

解决方法：

主要是缺少相关字体,通过yum安装就可以了

  1. yum install libXfont    
  2. yum install xorg-x11-xdm  
  3. yum install xorg-x11-fonts* 

再次进行开启5901端口

[oracle@oracle ~]$ rm -rf .vnc/*
[oracle@oracle ~]$ vncpasswd 
Password:
Verify:
[oracle@oracle ~]$ vncserver :1

New 'oracle:1 (oracle)' desktop is oracle:1

Creating default startup script /home/oracle/.vnc/xstartup
Starting applications specified in /home/oracle/.vnc/xstartup
Log file is /home/oracle/.vnc/oracle:1.log

[oracle@oracle ~]$ netstat -lntup|grep 5901
(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:5901                0.0.0.0:*                   LISTEN      3255/Xvnc

开启端口后，会在.vnc/目录里生成一个xstartup文件，进行编辑，将最后一行的“twm &”修改为“gnome-session &”，修改配置文件要把vnc进程杀掉，再启动，才生效

[oracle@oracle ~]$ ll .vnc/
total 16
-rw-r--r-- 1 oracle oinstall 1966 Mar 11 10:02 oracle:1.log
-rw-r--r-- 1 oracle oinstall    5 Mar 11 10:02 oracle:1.pid
-rw------- 1 oracle oinstall    8 Mar 11 10:02 passwd
-rwxr-xr-x 1 oracle oinstall  654 Mar 11 10:02 xstartup

[oracle@oracle ~]$ vim .vnc/xstartup 
最后一行修改为
gnome-session &

关闭vnc进程命令：

  • vncserver -kill :1
[oracle@oracle ~]$ vncserver -kill :1
Killing Xvnc process ID 3255

打开vnc进程命令：

  • vncserver :1
[oracle@oracle ~]$ vncserver :1

New 'oracle:1 (oracle)' desktop is oracle:1

Starting applications specified in /home/oracle/.vnc/xstartup
Log file is /home/oracle/.vnc/oracle:1.log

再windows上用VNC，Viewer端进行连接端口

强大的功能

 

Oracle软件安装

下载安装软件

https://www.oracle.com/database/technologies/oracle-database-software-downloads.html#19c

其它版本去：http://suppot.oracle.com/

[root@oracle data]# ll
total 2487228
drwx------ 2 root   root          16384 Mar 10 17:30 lost+found
drwxr-xr-x 3 oracle oinstall       4096 Mar 10 20:03 oracle
-rw-r--r-- 1 root   root     1395582860 Mar 10 21:33 p13390677_112040_Linux-x86-64_1of7.zip
-rw-r--r-- 1 root   root     1151304589 Mar 10 21:34 p13390677_112040_Linux-x86-64_2of7.zip

unzip 进行解压

[root@oracle data]# chown -R oracle.oinstall database/

[root@oracle data]# ll
total 2487232
drwxr-xr-x 7 oracle oinstall       4096 Aug 27  2013 database
drwx------ 2 root   root          16384 Mar 10 17:30 lost+found
drwxr-xr-x 3 oracle oinstall       4096 Mar 10 20:03 oracle
-rw-r--r-- 1 root   root     1395582860 Mar 10 21:33 p13390677_112040_Linux-x86-64_1of7.zip
-rw-r--r-- 1 root   root     1151304589 Mar 10 21:34 p13390677_112040_Linux-x86-64_2of7.zip

切换oracel用户然后cd 进入目录database进行运行runInstaller 脚本

[root@oracle data]# su - oracle
[oracle@oracle ~]$ cd /data/database/
[oracle@oracle database]$ ll
total 60
drwxr-xr-x  4 oracle oinstall  4096 Aug 27  2013 install
-rw-r--r--  1 oracle oinstall 30016 Aug 27  2013 readme.html
drwxr-xr-x  2 oracle oinstall  4096 Aug 27  2013 response
drwxr-xr-x  2 oracle oinstall  4096 Aug 27  2013 rpm
-rwxr-xr-x  1 oracle oinstall  3267 Aug 27  2013 runInstaller
drwxr-xr-x  2 oracle oinstall  4096 Aug 27  2013 sshsetup
drwxr-xr-x 14 oracle oinstall  4096 Aug 27  2013 stage
-rw-r--r--  1 oracle oinstall   500 Aug 27  2013 welcome.html
[oracle@oracle database]$ ./runInstaller

运行脚本报错：

>>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<

解决方法：

方法一：在root用户下执行xhost+，然后切换oracle用户进行操作

方法二：在解压好后，权限配置好，重启服务器用oracle直接登陆，不要用su -  oracle由root用户切换

再次执行脚本

[oracle@oracle database]$ ./runInstaller 
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 6623 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 8191 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-03-10_11-10-15PM. Please wait ..

我用的是Xmanager

对于内核参数的问题：修改内核参数，更具要求修改对应的期望值

[root@oracle ~]# vim /etc/sysctl.conf
kernel.shmall = 2097152

[root@oracle ~]# /sbin/sysctl -p

修改好进行验证

对于缺少依赖包的问题：

打开进行文件进入--Packages目录进行寻找----》对应的包-----》找不到的包去网上下载

libaio-devel-0.3.107-10.el6.x86_64

compat-libstdc++-33-3.2.3-69.el6.x86_64

pdksh-5.2.14-30.x86_64

将找到的这三个包上传到linux

[oracle@oracle data]$ ll
total 2487640
-rw-r--r-- 1 oracle oinstall     187516 Mar 11 01:29 compat-libstdc++-33-3.2.3-69.el6.x86_64.rpm
drwxr-xr-x 7 oracle oinstall       4096 Aug 27  2013 database
-rw-r--r-- 1 oracle oinstall      13516 Mar 11 01:29 libaio-devel-0.3.107-10.el6.x86_64.rpm
drwx------ 2 oracle oinstall      16384 Mar 10 17:30 lost+found
drwxr-xr-x 3 oracle oinstall       4096 Mar 11 01:09 oracle
drwxr-xr-x 2 oracle oinstall       4096 Mar 11 00:09 oraInventory
-rw-r--r-- 1 oracle oinstall 1395582860 Mar 10 21:33 p13390677_112040_Linux-x86-64_1of7.zip
-rw-r--r-- 1 oracle oinstall 1151304589 Mar 10 21:34 p13390677_112040_Linux-x86-64_2of7.zip
-rw-r--r-- 1 oracle oinstall     207399 Mar 11 01:29 pdksh-5.2.14-30.x86_64.rpm

[root@oracle data]# rpm -ivh *.rpm
warning: compat-libstdc++-33-3.2.3-69.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID fd431d51: NOKEY
warning: pdksh-5.2.14-30.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 73307de6: NOKEY
Preparing...                ########################################### [100%]
   1:pdksh                  ########################################### [ 33%]
   2:libaio-devel           ########################################### [ 67%]
   3:compat-libstdc++-33    ########################################### [100%]

再次验证缺少依赖包问题是否解决

确保每一个都是succeeded

[root@oracle data]# /data/oraInventory/orainstRoot.sh
Changing permissions of /data/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /data/oraInventory to oinstall.
The execution of the script is complete.
[root@oracle data]# /data/oracle/product/11.2.0.4/db_1/root.sh
Performing root user operation for Oracle 11g 

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /data/oracle/product/11.2.0.4/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

Oracle数据库安装

终于该安装数据库啦

 

 

监听配置

netca脚本来完成

netca脚本位置：

[oracle@oracle ~]$ cat .bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
	. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
export ORACLE_BASE=/data/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1
export ORACLE_SID=prod
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

所配置的Oracle用户环境变量的export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1

进入到/bin/目录里面，netca脚本就在这里

[oracle@oracle db_1]$ cd $ORACLE_BASE/product/11.2.0.4/db_1/bin

[oracle@oracle bin]$ which netca
/data/oracle/product/11.2.0.4/db_1/bin/netca

因为做了Oracle用户环境变量，使用Oracle用户在什么位置都可以执行netca

[oracle@oracle bin]$ cd 
[oracle@oracle ~]$ netca

进行配置监听

监听配置完成

查看监听状态：

lsnrctl  status

[oracle@oracle ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-MAR-2020 03:46:54

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                11-MAR-2020 03:42:21
Uptime                    0 days 0 hr. 4 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /data/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

 

安装数据库

命令dbca

[oracle@oracle ~]$ dbca

进行查看原先的，Oracle用户环境变量找到，我想要的答案

export ORACLE_SID=prod

[oracle@oracle ~]$ cat .bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
	. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
export ORACLE_BASE=/data/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1
export ORACLE_SID=prod
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

Oracle密码设置格式示例：

  • Oracle123

然后等待安装，大约需要30分钟

安装完成

EM的使用

em web页面管理

查看企业管理器的状态：

  • emctl status dbconsole

 

提示11g is not running.就是没有运行

[oracle@oracle ~]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://oracle:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is not running. 
------------------------------------------------------------------
Logs are generated in directory /data/oracle/product/11.2.0.4/db_1/oracle_prod/sysman/log

 

启动em企业管理器命令：

  • emctl start dbconsole
[oracle@oracle ~]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://oracle:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ..... started. 
------------------------------------------------------------------
Logs are generated in directory /data/oracle/product/11.2.0.4/db_1/oracle_prod/sysman/log

再次查看em的状态，提示11g is running. 代表em开启

[oracle@oracle ~]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://oracle:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running. 
------------------------------------------------------------------
Logs are generated in directory /data/oracle/product/11.2.0.4/db_1/oracle_prod/sysman/log

访问地址给了https://oracle:1158/em/，修改为https://192.168.5.60（IP）:1158/em/

这里监听和实例都打开才能访问到

 

实例及监听启动关闭

实例instance：

通过操作系统验证，就算服务是关闭的也能登陆进去

  • sqlplus / as sysdab

 

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 11 13:11:18 2020

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

Connected to an idle instance.

SQL>

启动实例：

  • startup
SQL> startup
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size		    2253664 bytes
Variable Size		 1476398240 bytes
Database Buffers	  117440512 bytes
Redo Buffers		    7319552 bytes
Database mounted.
Database opened.
SQL>

这个过程就是分配内存，数据库挂载，数据库打开

查看这个instance实例的结构

  • desc v$instance;
SQL> desc v$instance;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 INSTANCE_NUMBER				    NUMBER
 INSTANCE_NAME					    VARCHAR2(16)
 HOST_NAME					    VARCHAR2(64)
 VERSION					    VARCHAR2(17)
 STARTUP_TIME					    DATE
 STATUS 					    VARCHAR2(12)
 PARALLEL					    VARCHAR2(3)
 THREAD#					    NUMBER
 ARCHIVER					    VARCHAR2(7)
 LOG_SWITCH_WAIT				    VARCHAR2(15)
 LOGINS 					    VARCHAR2(10)
 SHUTDOWN_PENDING				    VARCHAR2(3)
 DATABASE_STATUS				    VARCHAR2(17)
 INSTANCE_ROLE					    VARCHAR2(18)
 ACTIVE_STATE					    VARCHAR2(9)
 BLOCKED					    VARCHAR2(3)

SQL>

instance的表中有个STATUS状态连，进行查看OPEN状态就是打开

  • select status from v$instance;
SQL> select status from v$instance;

STATUS
------------
OPEN

关闭实例：

  • shutdown instance

 

监听

查看监听状态：现在还没有启动

  • lsnrctl status
[oracle@oracle ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-MAR-2020 14:22:11

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused

启动监听状态：

  • lsnrctl start
[oracle@oracle ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-MAR-2020 15:01:13

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /data/oracle/product/11.2.0.4/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /data/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Log messages written to /data/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                11-MAR-2020 15:01:14
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /data/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

提示no services，就是说现在还没有服务，要等待个大约50秒才能看到服务，再次查看监听、可以看到no services的提示没有了

[oracle@oracle ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-MAR-2020 15:09:30

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                11-MAR-2020 15:01:14
Uptime                    0 days 0 hr. 8 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /data/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "prod" has 1 instance(s).
  Instance "prod", status READY, has 1 handler(s) for this service...
Service "prodXDB" has 1 instance(s).
  Instance "prod", status READY, has 1 handler(s) for this service...
The command completed successfully

要想快速注册，要手工注册

进行系统验证，输入alter system register;

这里监听和注册有两种情况

  • 监听先启动，实例后启动

实例启动的时候就会去注册

  • 实例先启动，监听后启动

就会等待实例服务注册到监听里或者手工改去注册

 

关闭监听状态：

  • lsnrctl stop

 

默认密码180天过期进行修改

sqlplus / as sysdba

select * from dba_profiles;

SQL> select * from dba_profiles;

PROFILE 		       RESOURCE_NAME			RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT 		       COMPOSITE_LIMIT			KERNEL
UNLIMITED

DEFAULT 		       SESSIONS_PER_USER		KERNEL
UNLIMITED

DEFAULT 		       CPU_PER_SESSION			KERNEL
UNLIMITED


PROFILE 		       RESOURCE_NAME			RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT 		       CPU_PER_CALL			KERNEL
UNLIMITED

DEFAULT 		       LOGICAL_READS_PER_SESSION	KERNEL
UNLIMITED

DEFAULT 		       LOGICAL_READS_PER_CALL		KERNEL
UNLIMITED


PROFILE 		       RESOURCE_NAME			RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT 		       IDLE_TIME			KERNEL
UNLIMITED

DEFAULT 		       CONNECT_TIME			KERNEL
UNLIMITED

DEFAULT 		       PRIVATE_SGA			KERNEL
UNLIMITED


PROFILE 		       RESOURCE_NAME			RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT 		       FAILED_LOGIN_ATTEMPTS		PASSWORD
10

DEFAULT 		       PASSWORD_LIFE_TIME		PASSWORD
180

DEFAULT 		       PASSWORD_REUSE_TIME		PASSWORD
UNLIMITED


PROFILE 		       RESOURCE_NAME			RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT 		       PASSWORD_REUSE_MAX		PASSWORD
UNLIMITED

DEFAULT 		       PASSWORD_VERIFY_FUNCTION 	PASSWORD
NULL

DEFAULT 		       PASSWORD_LOCK_TIME		PASSWORD
1


PROFILE 		       RESOURCE_NAME			RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT 		       PASSWORD_GRACE_TIME		PASSWORD
7


16 rows selected.

其中有个 PASSWORD_LIFE_TIME PASSWORD 180，需要修改为永久

alter profile default limit password_life_time unlimited;

SQL> alter profile default limit password_life_time unlimited;

Profile altered.

进行查看DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED已经变为unlimited

SQL> select * from dba_profiles;

PROFILE 		       RESOURCE_NAME	RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT 		       COMPOSITE_LIMIT	KERNEL
UNLIMITED

DEFAULT 		       SESSIONS_PER_USERKERNEL
UNLIMITED

DEFAULT 		       CPU_PER_SESSION	KERNEL
UNLIMITED


PROFILE 		       RESOURCE_NAME	RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT 		       CPU_PER_CALL	KERNEL
UNLIMITED

DEFAULT 		       LOGICAL_READS_PER_SESSION	KERNEL
UNLIMITED

DEFAULT 		       LOGICAL_READS_PER_CALL		KERNEL
UNLIMITED


PROFILE 		       RESOURCE_NAME	RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT 		       IDLE_TIME	KERNEL
UNLIMITED

DEFAULT 		       CONNECT_TIME	KERNEL
UNLIMITED

DEFAULT 		       PRIVATE_SGA	KERNEL
UNLIMITED


PROFILE 		       RESOURCE_NAME	RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT 		       FAILED_LOGIN_ATTEMPTS		PASSWORD
10

DEFAULT 		       PASSWORD_LIFE_TIMPASSWORD
UNLIMITED

DEFAULT 		       PASSWORD_REUSE_TIME		PASSWORD
UNLIMITED


PROFILE 		       RESOURCE_NAME	RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT 		       PASSWORD_REUSE_MAPASSWORD
UNLIMITED

DEFAULT 		       PASSWORD_VERIFY_FUNCTION 	PASSWORD
NULL

DEFAULT 		       PASSWORD_LOCK_TIMPASSWORD
1


PROFILE 		       RESOURCE_NAME	RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT 		       PASSWORD_GRACE_TIME		PASSWORD
7


16 rows selected.

 

默认密码输错10次用户锁定进行修改

alter profile default limit failed_login_attempts unlimited; 

SQL> alter profile default limit failed_login_attempts unlimited;

Profile altered.

 

取消段延迟特性

deferred segment_creation

alter system set deferred_segment_creation=false;

SQL> alter system set deferred_segment_creation=false;

System altered.

 

忽略密码大小写

alter system set sec_case_sensitive_logon=false;

SQL> alter system set sec_case_sensitive_logon=false;

System altered.

 

实现Oracle开机自启动

实例的自启动

  • 修改配置文件/etc/oratab，将最下面的N改为Y，实现开机自启动实例
[oracle@oracle ~]$ cat /etc/oratab 
#



# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
prod:/data/oracle/product/11.2.0.4/db_1:N
  • 修改dbstart文件，位置$ORACLE_HOME/bin下，搜索ORACLE_HOME_LISTNER

找到文件中的ORACLE_HOME_LISTNER=$1，把他注释掉，在他下面添加一行ORACLE_HOME_LISTNER=/data/oracle/product/11.2.0.4/db_1，这个值就等于export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1的全路径

# First argument is used to bring up Oracle Net Listener
# ORACLE_HOME_LISTNER=$1
ORACLE_HOME_LISTENR=/data/oracle/product/11.2.0.4/db_1

监听自启

前面两个可以用oracle用户在操作，下面这个要用root用户操作,进行添加

  • /etc/rc.d/rc.local
su - oracle -c 'lsnrctl start'
su - oracle -c 'dbstart start'

 

进行重启验证

查看监听：

[oracle@oracle ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-MAR-2020 16:36:03

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                11-MAR-2020 16:31:21
Uptime                    0 days 0 hr. 4 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /data/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "prod" has 1 instance(s).
  Instance "prod", status READY, has 1 handler(s) for this service...
Service "prodXDB" has 1 instance(s).
  Instance "prod", status READY, has 1 handler(s) for this service...
The command completed successfully

查看实例：

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 11 16:32:10 2020

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc v$instance;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 INSTANCE_NUMBER				    NUMBER
 INSTANCE_NAME					    VARCHAR2(16)
 HOST_NAME					    VARCHAR2(64)
 VERSION					    VARCHAR2(17)
 STARTUP_TIME					    DATE
 STATUS 					    VARCHAR2(12)
 PARALLEL					    VARCHAR2(3)
 THREAD#					    NUMBER
 ARCHIVER					    VARCHAR2(7)
 LOG_SWITCH_WAIT				    VARCHAR2(15)
 LOGINS 					    VARCHAR2(10)
 SHUTDOWN_PENDING				    VARCHAR2(3)
 DATABASE_STATUS				    VARCHAR2(17)
 INSTANCE_ROLE					    VARCHAR2(18)
 ACTIVE_STATE					    VARCHAR2(9)
 BLOCKED					    VARCHAR2(3)

SQL> select status from v$instance;

STATUS
------------
OPEN

 

 

继续阅读
weinxin
我的微信
这是我的微信扫一扫
  • 文本由 发表于 2020年3月11日00:52:57
  • 除非特殊声明，本站文章均为原创，转载请务必保留本文链接
基本的SQL结构化查询语言 Oracle

基本的SQL结构化查询语言

基本的SQL结构化语言 包含了四类语言： DML 数据操作语言-----select，insert，updata，delete（增删改查） DDL 数据定义语言------create，alter，d...
03月13日 评论
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: