在Oracle性能分析中,Zamong比ASH或AWR好得多。

Zamong的好点。

使用Zamong时,数据库服务器上没有负载。
Zamong使用使用DMA(direct memory access)方法。
DMA : Zamong提取并存储Oracle数据库状态和活动会话(active session)详细信息,而不使用SQL查询或连接到Oracle。
Zamong是免费的。
安装非常简单,您可以自己安装。

Zamong特有的两大功能:
实时监控和事后分析功能。
实时监控可以快速地定位问题,针对数据进行精密地分析。
支持以最低负荷全面收集数据的实时性能监控。

ORACLE AWR和ASH的弱点。
当Oracle数据库服务器的CPU利用率非常高并且没有sql查询响应时,系统管理员通过重新启动服务器或重新启动数据库来解决此问题。
在这种情况下,您会注意到,如果要分析AWR(Automatic Workload Repository)和ASH(Active Session History)以查找失败原因,您注意到数据未保存。

由于v$-view数据位于内存中,
当您重新启动数据库时,内存中的数据将会丢失。
Oracle会定期将这些数据存储在dba视图中,但是如果数据库突然重新启动,那么此时的信息不会存储在v$-view或dba-view中的任何位置。
在Oracle性能分析中,Zamong比ASH或AWR更好,因为它每秒都会保存每个活动会话信息。

此外,要使用AWR,ASH和ADDM,除了oracle企业许可证oracle enterprise license之外,还必须购买其他许可证。
那是diagnostics pack license或tuning pack license。
但是使用oracle standard edition的客户不能购买diagnostics pack license或tuning pack license。

无论AWR是否启用,ZAMONG都可以收集所有活动会话信息并分析Top query,wait event和详细信息。
Zamong提取并存储Oracle数据库状态和活动会话( active session )详细信息,而不使用SQL查询或连接到Oracle。
请参阅下面的链接。
http://zamongsoft.com/feature.html

zamong使用直接内存访问( dma – Direct Memory Access )方法。
因此,数据库服务器上没有负载。 直接内存访问方法的速度足够快,每秒可以完成超过100次。

由于zamong每秒都会存储每个活动会话的详细信息,因此dba可以使用存储的信息轻松确定Oracle性能下降的原因。

dma_picture

您可以通过仪表板轻松检查Oracle数据库的状态。

dashboard using dma

 

您可以使用Zamong Clues来检查过去活动会话(active session)的状态。
由于zamong每秒存储活动会话信息,因此当出现问题时,您可以在问题发生时检查所有活动会话。
也就是说,可以以1秒为单位移动时间来确认。

 

 

 

ORA-01440 : column to be modified must be empty to decrease precision or scale

oracle number data type format :  number( precision, scale)

Precision 4, scale 2   : 99.99
Precision 10, scale 0 : 9999999999
Precision 8, scale 3   : 99999.999
Precision 5, scale -3  : 99999000

How to modify  column number(10)  to number(10,2)

Column number(10,2) has  2 decimal places of precision.
The 2 digits of precision being added to the column would presumably come at the cost of precision to the left of the decimal place.
Hence, ORA-01440 error  occurs

you have to increase the precision (10)  by 2
” alter table test_table1  modify (  a(12,2)  ) ;  ”

To modify  column number(6,2)  to number( 6,3 )

you have to increase the precision (6)  by 1
” alter table test_table2  modify ( 7,3)  ) ;  “

Zamong is a free tool for real time oracle monitoring and failure analysis

dma_picture

Zamong is a tool for real time oracle monitoring using direct sga memory access method. So zamong does not connect to db for db monitoring. It collects Oracle DB internal status(v$sysstat, v$system_event) and active session details(v$session, sql-text .. ) using direct memory access method without accessing Oracle DB.

The advantage of this software is that there is no load on the server at all. Direct memory access is really fast and light enough to be done more than 100 times per second.

Query based DB monitoring method is performed by executing many recursive queries internally in the DB in order to execute queries for db monitoring. These resursive queries can cause latch contention in the shared pool.

Query based DB monitoring method can be used if these indicators are only normal, such as the server’s query response time, cpu usage, active session count, etc.
However, as the db response time increases, the number of active sessions increases, using a query based DB monitoring method is dangerous because it can further increase the latch contention in the shared pool.

Running queries for db monitoring every second will increase the latch, In more serious cases, you can cause a db instance hang.

So DMA method for oracle db monitoring is the best solution.

Database instance statistics are displayed as several real time charts in zamong dashboard and details of real time active sessions are also displayed in the dashboard.
these active sessions are refreshed every 2 seconds and you can see the session details by double clicking a active session in the dashboard.
zamong is also used to analyze the cause of the failure at a point in time.
you specify a time range for analyzing failure, and then you can check details of active sessions on every second in the range.

The DMA method is very powerful. Even if a oracle database is in hang state, zamong can store all information of each session in sga.
So you can use zamong for failure analysis or performance degradation analysis.

 

 

Refer to this link for a use case

 

 

Zamong은 SGA 메모리를 직접 액세스 하는 방법을 사용하는 무료의 실시간 오라클 모니터링 솔루션입니다.

Zamong is free oracle monitoring software with DMA method.

dma_picture

이 솔루션은 쿼리를 사용하지 않고, 모니터링 할 정보를 OS 메모리에서 직접 읽어오기 때문에 오라클 장애 순간에도,
심지어 ORACLE이 HANG 상태에 있어도, 오라클 instance 의 상태와 지표값, 실행되고 있는 sql 쿼리를 비롯하여  SGA에 올라와 있는 각 세션의 모든 정보를 os 메모리에서 읽어 저장할 수 있습니다.

메모리에서 직접 읽는 방법( DMA )은 매우 빠르고 부하가 거의 없는게 특징입니다.
실제로 테스트해보면 1초에 100 회 이상 OS 메모리 읽으면서 오라클 관련 중요 정보를 추출할 수 있을 정도입니다.
하지만 성능분석이나 장애 원인 분석을 위한 데이터 추출은 1초에 1번 추출하는것으로도 충분합니다.

일반적인 쿼리 방식의 모니터링 솔루션은 주기적으로 쿼리를 실행시켜 DB 인스턴스 나 Active Session의 상세 정보를 추출하는 방법을 사용하는데, 이 방법은 매우 위험할 수 있습니다.
쿼리 수도 많고, 또 쿼리를 수행하기위해 DB에서 내부적으로 많은 recursive 쿼리가 실행되고, 이러한 recursive 쿼리는 shared pool에서 latch 경합을 일으킬 수 있습니다. 더구나 cpu 사용률이 높고, 실시간 트랜잭션이 많은 계정계 db나 쇼핑몰 db, PG VAN사의 결제 db 등 민감한 db에서는 이런 쿼리 방식의 모니터링은 매우 위험합니다.
예전 얘기지만 Oracle 9i 때는 v$session를 반복적으로 조회하는 것만으로도 shared pool 내의 latch를 유발시켜 instance hang 까지 발생했었습니다.

Zamong 을 사용하면 대시보드에서 DB instance의 중요 통계(stat) 항목을 실시간으로 차트로 보여주고, 또 Active 세션들의 상세 정보도 2초 단위로 refresh 해줍니다. 이렇게 실시간적으로 주요 statistics 변화 추이를 차트로 보여주기 때문에 db에 어떤 성능 지연이 나타나면 바로 인지할 수 있고, 하단에 보여주는 active session 정보까지 같이 확인하면, 어떤 세션들로 인해 db에 부하가 가고 있는지 직관적으로 확인 할 수 있습니다. 무엇보다도 1초 간격으로 모니터링 된 정보들이 수집서버로 전송되고 저장되고 있어, 과거 장애 난 시점의 각 엑티브 세션들의 Wait Event와 쿼리, 프로그램 등 상세정보들을 분석 뷰에서 1초 간격으로 시간을 이동시키면서 분석할 수 있습니다. 그래서 세션들의 전/후 움직임을 직접 확인 할 수 있습니다. 간단하게 설명드리면 어느 서버의 어떤 프로그램에서 db에 접속했고 어떤 쿼리를 실행시켰고 그 쿼리가 어떤 wait event를 발생시키고 있고, 어느 데이터 파일의 어느 블럭을 엑세스 하고 있는지 체크가 가능하며, 그 시점의 백그라운드 프로세스의 엑티브한 wait event까지 확인이 가능합니다.
바로 빠른 장애 원인 분석과 성능 저하 분석을 위한 툴입니다.

이 툴의 최고 장점은 응답지연이나 장애 원인의 명확한 증거를 찾아낼 때 입니다.
지금까지는 과거 시점에 장애 원인이나 성능저하를 분석하기위해서 고가의 모니터링 솔루션을 이용하거나, 그런 모니터링 솔루션이 없다면 ASH( active session history) 뷰를 조회하는 방법을 사용하곤 했습니다.
ASH 뷰 하나면 믿고 DB를 운영하는 DBA는 없을 겁니다. 정작 중요시점의 정보는 조회해도 없을 때가 많다는 것을 대부분의 DBA는 알고 있기 때문입니다.
그리고 ASH( active session history) 기능을 사용하려면 Enterprise edition 라이선스 외에 Diagnostics pack 이라는 추가적인 라이선스가 필요합니다.  이 부분은 DBA에게 매우 중요한 포인트인데,  만일 Standard edition일 경우에 ASH( active session history) 기능을 enable 해서 사용할 경우,  요즘 핫하게 이슈되고 있는 LMS의 타겟이 될 수 있습니다. 이 경우는 diagnostics pack 뿐만이 아니라 enterprise edition 라이선스 위반이 되기 때문에 standard edition 의 구매 금액대와는 차원이 다르게 심각해질 수 있게 됩니다.   이 부분은 여기를 참조해보세요 https://docs.oracle.com/database/121/DBLIC/options.htm#DBLIC165

간단한 사용 방법 링크.

how to access oracle sga directly with c code

Zamong is free  oracle monitoring software with DMA method.

dma_picture

What is Direct oracle sga Memory Access ?

This example is from an AIX environment.
On linux, address value is differ from aix.

SQL>  select * from x$ksmmem  where rownum < 10 ;

ADDR                   INDX    INST_ID KSMMMVAL
---------------- ---------- ---------- ----------------
0700000000000000          0          1 00
0700000000000008          1          1 0700000514334020
0700000000000010          2          1 00
0700000000000018          3          1 00
0700000000000020          4          1 00
0700000000000028          5          1 00
0700000000000030          6          1 00
0700000000000038          7          1 00
0700000000000040          8          1 00

9 rows selected.

“0700000000000000” address is sga start address.

oracle@PaCG:/oracle> ipcs -mba
IPC status from /dev/mem as of Thu 
T        ID     KEY        MODE       OWNER    GROUP  CREATOR   CGROUP NATTCH     SEGSZ  CPID  LPID   ATIME    DTIME    CTIME 
Shared Memory:
m   1048576   00000000 --rw-r-----     grid      dba     grid      dba     32 318767104 4456886 58524018 21:41:51 21:41:51  5:38:14
m   5242882   00000000 --rw-r-----     grid      dba     grid      dba     32   3678208 4456886 58524018 21:41:51 21:41:51  5:38:14
m         3 0x8895c820 --rw-r-----     grid      dba     grid      dba     32      8192 4456886 58524018 21:41:51 21:41:51  5:38:14
m   1048581 0x3bea2a94 --rw-r-----   oracle      dba   oracle      dba   2404     16384 9371670 23593476 21:55:27 21:55:27  6:00:24
m   1048582   00000000 --rw-r-----   oracle      dba   oracle      dba   2404 21340618752 9371670 23593476 21:55:27 21:55:27  6:00:24
m   3145735   00000000 --rw-r-----   oracle      dba   oracle      dba   2404 134217728 9371670 23593476 21:55:27 21:55:27  6:00:24

shared memory id : 3145735

This is simple example of attaching oracle shared memory.

What is Direct oracle sga Memory Access ?

DMA method for oracle monitoring collects Oracle DB internal status(v$sysstat, v$system_event) and active session details(v$session, sql-text .. ) without accessing Oracle DB.
Direct memory access is really fast and light enough to be done more than 100 times per second.
how to access oracle sga directly with c code

Query based DB monitoring method is performed by executing many recursive queries internally in the DB in order to execute queries for db monitoring. These recursive queries can cause latch contention in the shared pool.

Query-based DB monitoring solution might be a good idea if these indicators are normal, such as the server’s query response time, cpu usage, active session count, etc.
However, as the db response time increases, the number of active sessions increases, using a query-based DB monitoring method is dangerous because it can further increase the latch contention in the shared pool.

Zamong is free  oracle monitoring software with DMA method.

How to give non-DBA user kill session privileges ?

Sometimes developers want to have ability to kill their own sessions.
Apparently it is not safe to let a non-DBA user to have a right to execute the “ALTER SYSTEM” command.

connect scott/XXXXX

select 
     sid,  
     serial#,  
     username,  
     machine,  
     program ,  
     (select sql_text  from v$sql b where b.sql_id=a.sql_id and rownum <= 1 ) current_sql_str,
     (select sql_text  from v$sql b where b.sql_id=a.prev_sql_id and rownum <= 1 ) previous_sql_str
from v$session  a
--   where 
--       username = 'SCOTT'
---  and event like '%enq%'
--   and machine like '%WORKGROUP%'
order by username, machine, sid  ;


SID     SERIAL#    USERNAME     MACHINE              PROGRAM               CURRENT_SQL_STR                          PREVIOUS_SQL_STR
4241    64469       SCOTT       DESKTOP-MONITOR      SQL Developer         select * from TB_SALE_ACCEPT_HIS        select count(*) from   TB_SALE_ACCEPT_HIS 
2573    42393       SALEAP      IK_APP_SER           JDBC Thin Client                                              select value$ from props$ where name = 'GLOBAL_DB_NAME'


  

begin
sys.sp_kill_dev_session( 4241 , 64469  )  ;
end ;
/

How to safely grant ALTER SYSTEM KILL SESSION privileg to non-DBA users

SQL> connect /as sysdba
Connected.



CREATE OR REPLACE PROCEDURE sys.sp_kill_dev_session(p_sid NUMBER, p_serial NUMBER)
AS
    v_user VARCHAR2(30);
BEGIN
    SELECT MAX(username)
    INTO v_user
    FROM v$session
    WHERE sid = p_sid
      AND serial# = p_serial;

    IF v_user IN ('SCOTT') THEN --the list can be extended
         EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_sid || ',' || p_serial || '''';
    ELSIF v_user IS NULL THEN
         RAISE_APPLICATION_ERROR(-20001,'Session has Expired or Invalid sid/serial Arguments Passed');
    ELSE
         RAISE_APPLICATION_ERROR(-20002,'Unauthorized Attempt to Kill a Non-Dev Session has been Blocked.');
    END IF;
END sp_kill_dev_session;
/

Procedure created.


SQL> grant execute on sys.sp_kill_dev_session  to scott;


SQL> grant select on sys.v_$session to scott ;
 

Source of PL/SQL procedure : https://sqlpatterns.wordpress.com/2015/03/02/how-to-safely-grant-alter-system-kill-session-privilege-to-non-dba-users/

How to kill pid of oracle session processes immediately

How to create immediately “kill -9 pid “command that can be used when server is in high cpu usage.

select  'kill -9 '|| b.spid from v$session a, v$process b
where a.paddr=b.addr
--and a.program  = 'oracle@SALESDB (TNS V1-V3)'
--and a.machine  = 'pamsaser01'
--and a.sqlid    = 'g6u556kt6pvb3'
--and a.username = 'SCOTT'
and a.status ='ACTIVE'
;
 
 
kill -9 14987
kill -9 14989
kill -9 14991
kill -9 14995
kill -9 14997
kill -9 14999
kill -9 15001
kill -9 15003
kill -9 15005
kill -9 15007
 

aix oracle 12c Installation Fails, rtld: 0712-001 Symbol CreateIoCompletionPort was referenced

when you create database with dbca

UnsatisfiedLinkError exception loading native library: njni12
java.lang.UnsatisfiedLinkError: njni12 (rtld: 0712-001 Symbol CreateIoCompletionPort was referenced
from module /u01/products/rdbms_12102/lib/libnjni12.so(), but a runtime definition
of the symbol was not found.
rtld: 0712-001 Symbol GetMultipleCompletionStatus was referenced
from module /u01/products/rdbms_12102/lib/libnjni12.so(), but a runtime definition
of the symbol was not found.
rtld: 0712-002 fatal error: exiting.)
Exception in thread "main" java.lang.UnsatisfiedLinkError: oracle/net/common/NetGetEnv.get(Ljava/lang/String;)Ljava/lang/String;

The installation logs shows the following relink error:

exec(): 0509-036 Cannot load program sqlplus because of the following errors:
rtld: 0712-001 Symbol CreateIoCompletionPort was referenced
from module /appl/oracle/product/12.1.0/dbhome_1/lib/libttsh12.so(), but a runtime definition
of the symbol was not found.
rtld: 0712-001 Symbol GetMultipleCompletionStatus was referenced
from module /appl/oracle/product/12.1.0/dbhome_1/lib/libttsh12.so(), but a runtime definition
of the symbol was not found.
rtld: 0712-002 fatal error: exiting.

sqlplus , lsnrctl command fails with below error

exec(): 0509-036 Cannot load program sqlplus because of the following errors:
rtld: 0712-001 Symbol CreateIoCompletionPort was referenced
from module /appl/oracle/product/12.1.0/dbhome_1/lib/libttsh12.so(), but a runtime definition
of the symbol was not found.
rtld: 0712-001 Symbol GetMultipleCompletionStatus was referenced
from module /appl/oracle/product/12.1.0/dbhome_1/lib/libttsh12.so(), but a runtime definition
of the symbol was not found.
rtld: 0712-002 fatal error: exiting.


IOCP module should be enabled on the server.

$ lsdev | grep iocp
  iocp0       Defined  I/O Completion Ports

   By default, IOCP is set to Defined.

Enable the “iocp” option and relink the oracle binaries.
To enable IOCP, set IOCP to Available using the following procedure:

Log in as root and run the following command:

# smitty iocp
Select Change / Show Characteristics of I/O Completion Ports.

Change configured state at system restart from Defined to Available.

Run the lsdev command to confirm the IOCP status is set to Available:

$ lsdev | grep iocp
iocp0 Available I/O Completion Ports

Perform a system restart to make the changes permanent.

Relink the binaries using " relink all " and then root.sh as root user.

There is no impact on Existing Oracle Databases installed on server if IOCP status is set to Available.

Zamong is free  oracle monitoring software with DMA method.

dma_picture

How to send mail using PL/SQL

First, You must have mail server and port 25 should be open.
You can check if the port is open with the telnet command as follows:
telnet 192.10.1.160 25
Second, You need to create access control list (ACL) in oracle database 11g.
In 10g, you do not have to do that.
Oracle Database 12c has deprecated many of the procedures and functions in the DBMS_NETWORK_ACL_ADMIN package.
Although deprecated, the old functionality is retained for backwards compatibility.
You can use access control entry (ACE) in 12c.

CREATE OR REPLACE PROCEDURE SYSTEM.TEST_SEND_MAIL
IS

  time_str       varchar2(20) ;
  c             utl_smtp.connection;
  v_line        varchar2(4000);
  v_status      number ;
  n             number := 1;
  err_msg       varchar2(4000);

BEGIN

   dbms_output.enable(1000000);
   dbms_output.put_line('');
   dbms_output.put_line('-----------------start-----------------------');



   select to_char(sysdate,'yyyymmdd hh:mi:ss') into time_str from dual ;
   dbms_output.put_line( time_str) ;

   dbms_output.put_line('--------------------end---------------------');
 
   c := utl_smtp.open_connection('192.10.1.160');  -- mail server ip

   utl_smtp.mail(c, 'jeniffer@abc.com');
   utl_smtp.rcpt(c, 'scott@abc.com');
   utl_smtp.rcpt(c, 'stella@abc.com');

  utl_smtp.open_data(c);
  utl_smtp.write_raw_data(c, UTL_RAW.CAST_TO_RAW('From: jeniffer@abc.com'||chr(13)||chr(10)));
  utl_smtp.write_raw_data(c, UTL_RAW.CAST_TO_RAW('To: scott@abc.com'||chr(13)||chr(10)));
  utl_smtp.write_raw_data(c, UTL_RAW.CAST_TO_RAW('Subject: this is a test mail'||chr(13)||chr(10)));

  loop

    dbms_output.get_line(v_line, v_status);
    if v_status = 0
      then
        utl_smtp.write_raw_data(c, UTL_RAW.CAST_TO_RAW(v_line||chr(13)||chr(10)));
        n := n + 1;
      else
      exit;
    end if;

  end loop;

  utl_smtp.close_data(c);
  utl_smtp.quit(c);

END ;
/




sql> execute SYSTEM.TEST_SEND_MAIL ;
PL/SQL procedure successfully completed.
  
  

 

In 11g, If you get error messages like the following
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.UTL_TCP”, line 17

you must create acl and assign connect privilege

1. create your custom procedure

connect / as sysdba

set serveroutput on

create or replace procedure test_mailserver_acl(
 aacl varchar2,
 acomment varchar2,
 aprincipal varchar2,
 aisgrant boolean,
 aprivilege varchar2,
 aserver varchar2,
 aport number)
is

begin

 begin
    DBMS_NETWORK_ACL_ADMIN.DROP_ACL(aacl);
    dbms_output.put_line('ACL dropped.....');

    exception
    when others then
    dbms_output.put_line('Error dropping ACL: '||aacl);
    dbms_output.put_line(sqlerrm);
 end;

 begin
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(aacl,acomment,aprincipal,aisgrant,aprivilege);
    dbms_output.put_line('ACL created.....');

    exception
    when others then
    dbms_output.put_line('Error creating ACL: '||aacl);
    dbms_output.put_line(sqlerrm);
 end;

 begin
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(aacl,aserver,aport);
    dbms_output.put_line('ACL assigned.....');

    exception
    when others then
    dbms_output.put_line('Error assigning ACL: '||aacl);
    dbms_output.put_line(sqlerrm);
 end;

 commit;
 dbms_output.put_line('ACL commited.....');
end;
/

2. create acl and assign privilege on ip 192.10.1.160 port 25 with your custom procedure.

begin
 test_mailserver_acl(
 'mailserver_acl.xml',
 'ACL for used Email Server to connect',
 'SCOTT',
  TRUE,
 'connect',
 '192.10.1.160',
 25);
end;
/

 

Zamong is free  oracle monitoring software with DMA method.

dma_picture