分类 Oracle 下的文章

Oracle修改SGA后无法启动解决方法

Oracle修改SGA超过系统内存后,会造成启动失败,解决方法如下:

1、登录oracle 
sqlplus sys/sys@XXX(网络服务名). as sysdba 
能够登陆 
提示已经连接到空闲例程;

2、找 pfile,在 /.../admin/XXX(服务名)/pfile 下,init.ora.XXXXXXX(数字串) 

3、执行命令 
SQL> startup pfile='pfile 路径' 
通过指定的 pfile 启动数据库实例 

4、执行命令 
SQL> create spfile from pfile='pfile 路径';(一定要有分号!) 
创建成功会提示: 文件已创建。 

5、查看SGA
show parameter sga

已经SGA恢复了调整之前的配置,在此根据系统实际内存修改大小即可。

ORA-27102: out of memory 故障处理

ORA-27102: out of memory一般发生在服务器系统配置变更以后,oracle在启动的时候报错,

SQL> startup
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device

系统实际可用内存还剩几十个GB,这个问题在linux到64位中比较常见,当SGA的值大于共享内存就会报这个错;
解决方法:
--MetaLink上给出了关于这个问题的描述 [ID 301830.1]
--是由于kernel.shmall值设置太小引起的

kernel.shmall
  该参数用于配置系统一次能够使用的最大的共享页面数,该参数的值总是ceil(shmmax/PAGE_SIZE)
Oracle 9i,10g,11g的x86以及x86-64平台推荐以及缺省的大小通常为2097152
在大多数情况下,该参数是够用的。按照上述计算方式页面内存总大小可以达到8GB(2097152*4096 bytes (shmall*PAGE_SIZE))

  通常情况下,PAGE_SIZE 的大小为4096byte,除非使用了Big Pages 或 Huge Pages
对于超出8GB系统内存,如共享内存段(shmmax)的最大大小是16G,则所需要共享内存页数(shmall)为16GB/4KB=16777216KB/4KB=4194304(页)

  共享内存在shmall这个参数中设置,shmall单位为页,换算成内存大小还需要把两个值相乘,如shmall=8388608,那么共享内存的大小就是(8388608*4096)/(1024*1024*1024)=32G
其他依次类推;



---阅读剩余部分---

Oracle查看系统运行情况常用SQL

1、查看系统当前连接会话数

select s.value,s.sid,a.username
from 
v$sesstat S,v$statname N,v$session A
where 
n.statistic#=s.statistic# and
name='session pga memory'
and s.sid=a.sid
order by s.value;

2、等待最多的用户

SELECT s.SID, s.username, SUM (a.wait_time + a.time_waited) total_wait_time
    FROM v$active_session_history a, v$session s
   WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
GROUP BY s.SID, s.username
ORDER BY total_wait_time DESC;

---阅读剩余部分---

ORA-00257报错处理

用户反映NC测试库不能连接,报ORA-00257错误,截图如下:
ORA-00257.png
错误原因是很常见的‘归档日志’写满‘快闪恢复区’(flash recovery area)导致的问题。解决方法如下:
一、删除过期的归档文件;
二、修改快闪恢复区(flash recovery area)的大小;





---阅读剩余部分---

expdp/impdp/rman会话进度查看

oracle11g使用DBA_DATAPUBMP_JOBS和DBA_DATAPUMP_SESSIONS视图来显示数据泵取作业的信息,
查看expdp进度:

select * from DBA_DATAPUBMP_JOBS;
select * from DBA_DATAPUMP_SESSIONS;
select sid,serial# from v$session s,dba_datapump_sessions d where s.saddr=d.saddr;

查看impdp进度:

SELECT   a.tablespace_name,
         ROUND (a.total_size) "total_size(MB)",
         ROUND (a.total_size) - ROUND (b.free_size, 3) "used_size(MB)",
         ROUND (b.free_size, 3) "free_size(MB)",
         ROUND (b.free_size / total_size * 100, 2) || '%' free_rate
  FROM   (  SELECT   tablespace_name, SUM (bytes) / 1024 / 1024 total_size
              FROM   dba_data_files
          GROUP BY   tablespace_name) a,
         (  SELECT   tablespace_name, SUM (bytes) / 1024 / 1024 free_size
              FROM   dba_free_space
          GROUP BY   tablespace_name) b
 WHERE   a.tablespace_name = b.tablespace_name(+);

查看RMAN备份进度:

SELECT SID,OPNAME, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK
order by "%_COMPLETE"
desc

ORA-01940: 无法删除当前连接的用户处理

Oracle11gR2删除用户时报错如下:

SQL> drop user HONGSINC CASCADE;
drop user ODI_SRC CASCADE
*
第 1 行出现错误:
ORA-01940: 无法删除当前连接的用户

ORA-01940是因为要删除的用户,还存在连接无法删除,解决方法如下:
查看当前用户的连接:

select username,sid,serial# from v$session where username='HONGSINC';
USERNAME                                                            SID    SERIAL#
------------------------------------------------------------ ---------- ----------
HONGSINC                                                             127       185
HONGSINC                                                             246       195
HONGSINC                                                             366       183

删除用户的sid,和serial:

SQL> alter system kill session'127,185';

系统已更改。

SQL> alter system kill session'246,195';

系统已更改。

SQL> alter system kill session'366,183';

系统已更改。

删除用户:

SQL> drop user HONGSINC CASCADE;
用户已删除。

如果在drop后还提示ORA-01940:无法删除当前已链接的用户,说明还有连接的session,可以通过查看session的状态来确定该session是否被kill了,
用如下语句查看:status为要删除用户的session状态,如果还为inactive,说明没有被kill掉,如果状态为killed,说明已kill。

select saddr,sid,serial#,paddr,username,status from v$session where username ='HONGSINC';

Oracle11g服务器ip地址变更后监听修改

Oracle服务器IP地址发生变更后需要修改下监听,要么就会连不上数据库,操作方法如下:
1、修改Oracle监听 listener.ora

su - oracle
cd $ORACLE_HOME/network/admin/
[oracle@hd_nc_db admin]$ pwd
/data/app/oracle/product/11.2.0/db_1/network/admin
[oracle@hd_nc_db admin]$ cat listener.ora
# listener.ora Network Configuration File: /data/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hd_nc_db)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /data/app/oracle

此处使用的是主机名hd_nc_db,直接修改/etc/hosts,将主机名对应的IP修改为服务器新IP保存即可,如果是IP地址直接修改为新IP地址保存;
2、重启服务器的listener

lsnrctl stop
lsnrctl start

3、修改客户端的tnsnames.ora(打开PL/SQL,工具栏找到 帮助->支持即可定位到tnsnames.ora文件位置),服务器如果配置了RAC,要更改参数文件中的节点地址、心跳地址等;

Oracle11g查询数据量最大的表及存储空间大小

数据量最大的表:

select * from user_tables t where t.NUM_ROWS is not  null  order by t.NUM_ROWS  desc

table.png

存储空间可以用如下语句查:

select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name;

tablespace.png

查询最大表记录:

select count(1) from LBPM_RT_NODEHANDLER_DEFINE;

select_max_row.png
最大记录数量两千三百多万;

Oracle 错误号解释大全

ORA-00001: 违反唯一约束条件 (.)
ORA-00017: 请求会话以设置跟踪事件
ORA-00018: 超出最大会话数
ORA-00019: 超出最大会话许可数
ORA-00020: 超出最大进程数 ()
ORA-00021: 会话附属于其它某些进程;无法转换会话
ORA-00022: 无效的会话 ID;访问被拒绝
ORA-00023: 会话引用进程私用内存;无法分离会话








---阅读剩余部分---

impdp导入还原命令详解

Oracle数据库还原IMPDP命令是相对于EXPDP命令的,方向是反向的。即对于数据库备份进行还原操作。

[oracle@ehrtest108 ~]$ impdp -help

Import: Release 11.2.0.4.0 - Production on чǚ̄ 1Ղ 17 13:48:33 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:

     Format:  impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

USERID must be the first parameter on the command line.

参数说明:

---阅读剩余部分---

impdp或imp导入乱码解决

impdp或imp导入时,显示乱码或问号等乱码,最终导致失败,一般主要是由于编码字符集设置不同导致,导入时先确认导出的编码是什么,然后设置客户端LNS_LANG在导入,

LS_LANG 参数构成
NLS_LANG参数由以下局部构成:NLS_LANG=<Language>_<Territory>.<Clients Characterset>,NLS_LANG各局部含义如下:

LANGUAGE指定:
Oracle消息利用的语言
日期中月份和日揭示
TERRITORY指定:
货币和数字款式
地区和计算礼拜即日期的适应
CHARACTERSET:扼制客户端利用过程利用的字符集。

中文一般用SIMPLIFIED CHINESE_CHINA.ZHS16GBK编码

1、查询导入库字符集

---阅读剩余部分---

impdp导入时ORA-31684报错解决

Oracle中使用impdp导入数据时,如果导入之前已经创建了用户,会提示错误:“ORA-31684:对象类型已经存在”,如:

[oracle@ehrtest108 ~]$ impdp yksoft/123456ABC@ehrtest directory=DATA_PUMP_DIR dumpfile=GQYS_ORACLE11201_20180827.DMP full=y

报错内容:

ORA-31684: 对象类型 USER:"YKSOFT" 已存在

---阅读剩余部分---

ORA-28547:连接服务器失败,可能是Oracle Net管理错误

描述:监听和服务都正常启动了,但是远程连接的时候会有这种错误
ORA-28547:连接服务器失败,可能是Oracle Net管理错误
解决办法:
listener.ora 文件中

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ORCL)
      (ORACLE_HOME = d:\app\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:d:\app\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.219)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = d:\app

(PROGRAM = extproc) 把这句话注释掉重启监听服务就好了。
重启监听:

lsnrctl stop
lsnrctl start

extproc是一个扩展的程序调用接口协议, 连接和调用外部的操作系统程序或进程用时会用到。

Oracle RMAN控制文件备份

进入RMAN界面

rman target /
 
RMAN>
修改控制文件备份方式为自动备份
RMAN > configure controlfile autobackup on;
查看控制文件备份是否为自动备份
RMAN > show controlfile autobackup;
查看控制文件自动备份路径
RMAN> show controlfile autobackup format;
修改控制文件自动备份路径
RMAN > configure controlfile autobackup format for device type disk to '/backup/orcl/%F';
注意%F是DBID+日期,可以用来识别时间及DBID

Oracle在非归档模式下RMAN的备份和恢复

1、Oracle数据库在非归档模式下的备份:

[oracle@dbnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期三 8月 8 21:24:39 2018

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


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

SQL> archive log list;   #查看是否启用归档模式
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     31004
Current log sequence           31006

登录到rman下:

[oracle@dbnode1 ~]$ rman target /   #登录rman

Recovery Manager: Release 11.2.0.3.0 - Production on 星期三 8月 8 21:29:02 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1351645873)
RMAN> shutdown immediate   #停掉数据库

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN>startup mount    #启动并挂载数据库,但是不打开

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area   25120276480 bytes

Fixed Size                     2240016 bytes
Variable Size              19193135600 bytes
Database Buffers            5905580032 bytes
Redo Buffers                  19320832 bytes
开始全量备份
RMAN> run
2> {
3> allocate channel t1 type disk;
4>  backup database format '/bak/rmanbak/20180808/df_%t_%s_%p.bak';
5> }

allocated channel: t1
channel t1: SID=518 device type=DISK

Starting backup at 08-8月 -18
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00005 name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/nnc_data01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00010 name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/nnc_index03.dbf
input datafile file number=00007 name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/nnc_data03.dbf
input datafile file number=00008 name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/nnc_index01.dbf
input datafile file number=00013 name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/nnc_data01b.dbf
input datafile file number=00006 name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/nnc_data02.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00012 name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/nnc_data01a.dbf
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/nnc_index02.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/IUFO.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/ORCL/users01.dbf
channel t1: starting piece 1 at 08-8月 -18
channel t1: finished piece 1 at 08-8月 -18
piece handle=/bak/rmanbak/20180808/df_983655480_2_1.bak tag=TAG20180808T213800 comment=NONE
channel t1: backup set complete, elapsed time: 00:18:16
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel t1: starting piece 1 at 08-8月 -18
channel t1: finished piece 1 at 08-8月 -18
piece handle=/bak/rmanbak/20180808/df_983656576_3_1.bak tag=TAG20180808T213800 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-8月 -18
released channel: t1
RMAN> alter database open;   #打开数据库

database opened

RMAN> 

2、非归档模式数据库恢复

RMAN> shutdown immediate    #停止数据库

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount   #启动挂载数据库

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 535662592 bytes

Fixed Size 1375792 bytes
Variable Size 411042256 bytes
Database Buffers 117440512 bytes
Redo Buffers 5804032 bytes

RMAN> restore database;    #恢复数据库

Starting restore at 03-NOV-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00004 to D:\ORACLE\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to D:\ORACLE\ORADATA\ORCL\TEST01.DBF channel ORA_DISK_1: restoring datafile 00006 to D:\ORACLE\ORADATA\ORCL\UNDOTBS02.DBF
channel ORA_DISK_1: reading from backup piece E:\RMAN_BAK\20111103\DF_766235030_5_1.BAK
channel ORA_DISK_1: piece handle=E:\RMAN_BAK\20111103\DF_766235030_5_1.BAK tag=TAG20111103T110349
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:35
Finished restore at 03-NOV-11

RMAN> recover database;   #恢复数据库

Starting recover at 03-NOV-18
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 3246 is already on disk as file D:\ORACLE\ORADATA\ORCL\REDO03.LOG archived log for thread 1 with sequence 3247 is already on disk as file D:\ORACLE\ORADATA\ORCL\REDO01.LOG
archived log file name=D:\ORACLE\ORADATA\ORCL\REDO03.LOG thread=1 sequence=3246
archived log file name=D:\ORACLE\ORADATA\ORCL\REDO01.LOG thread=1 sequence=3247
media recovery complete, elapsed time: 00:00:09
Finished recover at 03-NOV-18
打开数据库进行读/写操作时,会立即完成刚刚执行的还原。因此,先先在READ ONLY模式下打开数据库并查看某些数据,检查恢复操作是否符合预期结果。
RMAN> SQL 'ALTER DATABASE OPEN READ ONLY';
#如对恢复结果感到满意,则使用RESETLOGS选项打开数据库
RMAN> alter database open resetlogs;   

database opened

VIA: https://blog.csdn.net/rlhua/article/details/12346829

Oracle给用户新增表空间

什么是表空间?
表空间是数据库的逻辑划分,一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中。但主要存放的是表, 所以称作表空间。
创建用户的时候需要指定默认表空间和temp表空间,主要是为了当该用户创建对象不指定表空间时使用默认表空间来存储对象,
而temp表空间是用户执行查询、排序、分组等操作时使用temp表空间来存储临时数据块。

具体过程:
查询所有表空间使用情况 使用DBA权限登陆,执行:

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;

为表空间增加数据文件,因为接下来要导入很多很多数据进去,一个数据文件最多只能放32g数据。
增加CUPS_BS_DATA的表空间数据:

ALTER TABLESPACE "NNC_DATA01" ADD DATAFILE '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/nnc_data01b.dbf' size 15G autoextend on NEXT 1G MAXSIZE UNLIMITED;
--xxxx可以根据业务多加几个,保证够用

查看所有表空间的数据文件及其使用情况:

select
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;

sqlplus登录方式记录

1.本地登录

sqlplus / as sysdba

2.账号密码登录

sqlplus user/passwd

3.选择实例登录

sqlplus user/passwd@实例名   例如 sqlplus user/passwd@orcl

4.连接远程数据库实例

sqlplus user/passwd@IP:端口/实例名 例如:sqlplus user/passwd@192.168.1.2:1521/orcl 

5.远程登录dba实例

sqlplus user/passwd@IP:端口/实例名 as sysdba  例如:sqlplus user/passwd@192.168.1.2:1521/orcl as sysdba

exp导出报错EXP-00091: Exporting questionable statistics问题解决

使用如下命令执行导出操作:

exp user/pass@orcl file=/bak/20180803.dmp log=/bak/20180803.log owner=username buffer=111149600 grants=y 

输出如下信息,其中包含一些EXP-00091的错误提示:

EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                ARAP_BILLCONFER          0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                   ARAP_BILLMAP     336693 rows exported

导出来的dump文件可用于导入。

看下官方对EXP-00091的介绍:

EXP-00091: Exporting questionable statistics. 
Cause: Export was able export statistics, but the statistics may not be usuable. The statistics are questionable because one or more of the following happened during export: a row error occurred, client character set or NCHARSET does not match with the server, a query clause was specified on export, only certain partitions or subpartitions were exported, or a fatal error occurred while processing a table. 
Action: To export non-questionable statistics, change the client character set or NCHARSET to match the server, export with no query clause, export complete tables. If desired, import parameters can be supplied so that only non-questionable statistics will be imported, and all questionable statistics will be recalculated.

导出过程中,由于客户端字符集或NCHARSET参数和服务器端不一致,导致一些统计信息不可用。解决方案是可以导出不存在问题的统计信息,或者改变客户端字符集或NCHARSET参数。

解决方法:
查询服务器端字符集:

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

设置客户端字符集:

export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"

并将这句写入到/home/oracle/.bash_profile文件的最末;

然后重新执行导出,问题解决;

或者在exp导出的时候增加参数(不推荐)

statistics=none   #统计信息字段statistics设置为none

不再报错,但这种方式相当于未导出那些因字符集不一致错误的统计信息,导入自然也不会有这部分统计信息,如果需要,那么只能再重新收集统计信息。

Zabbix3.4.11使用Orabbix插件监控Oracle 11g R2

Orabbix简介
Orabbix是设计用来为zabbix监控Oracle的数据库的插件,它提供多层次的监控,包括可用性和服务器性能的指标。
ORABBIX.png
Orabbix监控的内容:

1. 数据库版本

2. 归档文件

3. 等待的事件(如文件的I/O,单块的读,多块的读,直接读,SQLNet消息,控制文件I/O,日志写等)

4. HitRatio(监控Hit Ratio的触发,表/存储过程,SQLArea,Body)

5. 逻辑I/O(当前读操作,持续的读操作,块的更改)

6. PGA

7. SGA(固定的缓冲,JAVApool,Large Pool,Log Buffer,Shared Pool,Buffer Cache)

8. 物理I/O(重写操作,数据文件写操作,数据文件读操作)

9. SharePool(PoolDictionary Cache, Pool Free Memory, Library Cache,Sql Area ,Misc.)

10. Pin Hit Ratio(monitor Hit Ratio on Trigger, Tables/Procedures, SQLArea,Body)

11. Session/Processes(monitor Sessions and processes)

12. Session (ActiveSession, Inactive Sessions, System Session)

安装步骤如下:
1、JAVA环境配置
若是没有java 需要安装JDK环境:
jdk官方下载地址 http://download.oracle.com/otn/java/jdk/7u80-b15/jdk-7u80-linux-x64.tar.gz

mkdir /usr/lib/jvm/
tar -xvf jdk-7u80-linux-x64.tar.gz -C /usr/lib/jvm/
echo "export JAVA_HOME=/usr/lib/jvm/jdk1.7.0_80" >>  /etc/profile
source /etc/profile
echo "export JRE_HOME=${JAVA_HOME}/jre" >>  /etc/profile
source /etc/profile
echo "export CLASSPATH=.:${JAVA_HOME}/lib:${JRE_HOME}/lib" >>  /etc/profile
echo "export PATH=${JAVA_HOME}/bin:$PATH" >>  /etc/profile
source /etc/profile
sudo update-alternatives --install /usr/bin/java java /usr/lib/jvm/jdk1.7.0_80/bin/java 300
sudo update-alternatives --install /usr/bin/javac javac /usr/lib/jvm/jdk1.7.0_80/bin/javac 300

安装完毕查看版本:

[root@dbnode1 opt]# java -version
java version "1.7.0_80"
Java(TM) SE Runtime Environment (build 1.7.0_80-b15)
Java HotSpot(TM) 64-Bit Server VM (build 24.80-b11, mixed mode)

2、Orabbix插件的下载安装
http://www.smartmarmot.com/product/orabbix/download/
也可以直接搜索下载,目前最新版本为1.2.3

创建一个目录作为orabbix的源地址:
mkdir -p /usr/local/orabbix
解压缩插件:
unzip -n orabbix-1.2.3.zip -d /usr/local/orabbix/

3、更改配置文件:

cd /usr/local/orabbix/
cp conf/config.props.sample conf/config.props
添加系统启动脚本:
cp init.d/orabbix /etc/init.d/orabbix
并修改路径:
vim /etc/init.d/orabbix
orabbix=/usr/local/orabbix

给启动脚本加执行权限

chmod +x /usr/local/orabbix/run.sh
chmod +x /etc/init.d/orabbix

修改配置文件内如如下

cat /usr/local/orabbix/conf/config.props | grep -v "^$"|grep -v "^#"

ZabbixServerList=ZabbixServer1

ZabbixServer1.Address=192.168.3.209
ZabbixServer1.Port=10051


OrabbixDaemon.PidFile=./logs/orabbix.pid
OrabbixDaemon.Sleep=300
OrabbixDaemon.MaxThreadNumber=100

DatabaseList=dbnode1

DatabaseList.MaxActive=10
DatabaseList.MaxWait=100
DatabaseList.MaxIdle=1

dbnode1.Url=jdbc:oracle:thin:@192.168.2.110:1521:ORCL
dbnode1.User=zabbix
dbnode1.Password=xxxxxxxx
dbnode1.MaxActive=10
dbnode1.MaxWait=100
dbnode1.MaxIdle=1
dbnode1.QueryListFile=./conf/query.props

4、使用DBA账号登陆oracle控制台,创建zabbix用户并赋权

CREATE USER ZABBIX IDENTIFIED BY ZABBIX2018 DEFAULT TABLESPACE SYSTEM TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;

GRANT ALTER SESSION TO ZABBIX;
GRANT CREATE SESSION TO ZABBIX;
GRANT CONNECT TO ZABBIX;
GRANT RESOURCE TO ZABBIX;
ALTER USER ZABBIX DEFAULT ROLE ALL;
GRANT SELECT ON V_$INSTANCE TO ZABBIX;
GRANT SELECT ON DBA_USERS TO ZABBIX;
GRANT SELECT ON V_$LOG_HISTORY TO ZABBIX;
GRANT SELECT ON V_$PARAMETER TO ZABBIX;
GRANT SELECT ON SYS.DBA_AUDIT_SESSION TO ZABBIX;
GRANT SELECT ON V_$LOCK TO ZABBIX;
GRANT SELECT ON DBA_REGISTRY TO ZABBIX;
GRANT SELECT ON V_$LIBRARYCACHE TO ZABBIX;
GRANT SELECT ON V_$SYSSTAT TO ZABBIX;
GRANT SELECT ON V_$LATCH TO ZABBIX;
GRANT SELECT ON V_$PGASTAT TO ZABBIX;
GRANT SELECT ON V_$SGASTAT TO ZABBIX;
GRANT SELECT ON V_$LIBRARYCACHE TO ZABBIX;
GRANT SELECT ON V_$PROCESS TO ZABBIX;
GRANT SELECT ON DBA_DATA_FILES TO ZABBIX;
GRANT SELECT ON DBA_TEMP_FILES TO ZABBIX;
GRANT SELECT ON DBA_FREE_SPACE TO ZABBIX;
GRANT SELECT ON V_$SYSTEM_EVENT TO ZABBIX;
GRANT SELECT ANY TABLE TO ZABBIX;
GRANT SELECT ANY DICTIONARY TO ZABBIX;
GRANT UNLIMITED TABLESPACE TO ZABBIX;

以下授权需要使用sysdba账号登陆
GRANT SELECT ON V_$LOG_HISTORY TO ZABBIX;
GRANT SELECT ON SYS.DBA_AUDIT_SESSION TO ZABBIX;
GRANT SELECT ON V_$LIBRARYCACHE TO ZABBIX;
GRANT SELECT ON DBA_DATA_FILES TO ZABBIX;
GRANT SELECT ON DBA_TEMP_FILES TO ZABBIX;
GRANT SELECT ON DBA_FREE_SPACE TO ZABBIX;
GRANT SELECT ON V_$SYSTEM_EVENT TO ZABBIX;
GRANT SELECT ON V_$SESSION TO ZABBIX; 
exec dbms_network_acl_admin.create_acl(acl => 'resolve.xml',description => 'resolve acl', principal =>'ZABBIX', is_grant => true, privilege => 'resolve');
exec dbms_network_acl_admin.assign_acl(acl => 'resolve.xml', host =>'*');
COMMIT;

5、启动orabbix服务、加入开机启动:

/etc/init.d/orabbix start
chkconfig orabbix on

6、查看日志

tail -f /usr/local/orabbix/logs 
 2018-07-17 11:36:36,662 [pool-1-thread-1] ERROR Orabbix - Error on DBEnquiry on query=archive on database=dbnode1 Error returned is java.lang.IllegalArgumentException: null value for key 'archive'
 2018-07-17 11:36:36,664 [pool-1-thread-1] WARN  Orabbix - Error while executing ->audit- on database ->dbnode1- Exception received ORA-00942: table or view does not exist

 2018-07-17 11:36:36,665 [pool-1-thread-1] WARN  Orabbix - Error while executing ->dbblockgets- on database ->dbnode1- Exception received ORA-00942: table or view does not exist

 2018-07-17 11:36:36,665 [pool-1-thread-1] ERROR Orabbix - Error on DBEnquiry on query=dbblockgets on database=dbnode1 Error returned is java.lang.IllegalArgumentException: null value for key 'dbblockgets
'
 2018-07-17 11:36:36,666 [pool-1-thread-1] WARN  Orabbix - Error while executing ->dbconsistentgets- on database ->dbnode1- Exception received ORA-00942: table or view does not exist

 2018-07-17 11:36:36,666 [pool-1-thread-1] ERROR Orabbix - Error on DBEnquiry on query=dbconsistentgets on database=dbnode1 Error returned is java.lang.IllegalArgumentException: null value for key 'dbcons
istentgets'
 2018-07-17 11:36:36,667 [pool-1-thread-1] WARN  Orabbix - Error while executing ->dbhitratio- on database ->dbnode1- Exception received ORA-00942: table or view does not exist

 2018-07-17 11:36:36,667 [pool-1-thread-1] ERROR Orabbix - Error on DBEnquiry on query=dbhitratio on database=dbnode1 Error returned is java.lang.IllegalArgumentException: null value for key 'dbhitratio'
 2018-07-17 11:36:36,668 [pool-1-thread-1] WARN  Orabbix - Error while executing ->dbphysicalread- on database ->dbnode1- Exception received ORA-00942: table or view does not exist

 2018-07-17 11:36:36,668 [pool-1-thread-1] ERROR Orabbix - Error on DBEnquiry on query=dbphysicalread on database=dbnode1 Error returned is java.lang.IllegalArgumentException: null value for key 'dbphysic
alread'
 2018-07-17 11:36:36,670 [pool-1-thread-1] WARN  Orabbix - Error while executing ->dbversion- on database ->dbnode1- Exception received ORA-00942: table or view does not exist

如果有以上类似的报错,基本都是zabbix用户权限不够导致,参考赋权;
正常log如下:

 2018-07-20 17:06:16,915 [pool-1-thread-47] INFO  Orabbix - Done with dbJob on database dbnode1 QueryList elapsed time 18537 ms
 2018-07-20 17:06:58,428 [pool-1-thread-48] INFO  Orabbix - Done with dbJob on database dbnode1 QueryList elapsed time 48 ms
 2018-07-20 17:08:16,967 [pool-1-thread-49] INFO  Orabbix - Done with dbJob on database dbnode1 QueryList elapsed time 18586 ms
 2018-07-20 17:08:58,418 [pool-1-thread-50] INFO  Orabbix - Done with dbJob on database dbnode1 QueryList elapsed time 35 ms
 2018-07-20 17:10:17,033 [pool-1-thread-51] INFO  Orabbix - Done with dbJob on database dbnode1 QueryList elapsed time 18648 ms
 2018-07-20 17:10:58,418 [pool-1-thread-52] INFO  Orabbix - Done with dbJob on database dbnode1 QueryList elapsed time 31 ms
 2018-07-20 17:12:17,225 [pool-1-thread-53] INFO  Orabbix - Done with dbJob on database dbnode1 QueryList elapsed time 18836 ms
 2018-07-20 17:12:58,426 [pool-1-thread-54] INFO  Orabbix - Done with dbJob on database dbnode1 QueryList elapsed time 35 ms
 2018-07-20 17:14:17,013 [pool-1-thread-55] INFO  Orabbix - Done with dbJob on database dbnode1 QueryList elapsed time 18621 ms
 2018-07-20 17:14:58,429 [pool-1-thread-56] INFO  Orabbix - Done with dbJob on database dbnode1 QueryList elapsed time 35 ms
 2018-07-20 17:16:17,087 [pool-1-thread-57] INFO  Orabbix - Done with dbJob on database dbnode1 QueryList elapsed time 18691 ms

/usr/local/orabbix下面有个template目录,里面是zabbix模板,将这些模板copy到本地机器上,然后登陆zabbix,把模板导入进去,就可以配置机器了,zabbix是3.x以上版本的话直接导入不进去,只能先导入2.x版本,然后再导出,然后导入3.x
或者直接用修改后Zabbix3.x可以直接使用的模板
zabbix_oracle_orabbix.zip
导入,并添加对应模板即可,完成以后效果如下:
1.png
2.png
3.png
4.png
5.png
10.png
VIA: http://www.smartmarmot.com/wiki/index.php?title=Orabbix
https://blog.csdn.net/tianhua79658788/article/details/77969426

最新

分类

归档

评论

其它