Oracle定時查詢結果輸出到指定的log檔案

標籤：

      最近有個監控項目需要採集資料庫資訊，原來方案是寫個sql指令碼，每個指令碼放一個查詢語句然後通過作業系統層su到oracle使用者通過sqlpus執行這個.sql，然後加到crontab定時執行。但是這個問題有個弊端，就是建立的串連太多，假如每5分鐘採集一次資料那意味著5分鐘通過sqlplus登陸資料庫再退出。後來想著怎麼在資料庫層每5分鐘執行下查詢而不是作業系統頻繁的通過sqlplus登陸，具體思路寫個預存程序通過預存程序把查詢結果輸出到指定的記錄檔。再通過job定時調度。
1、建立dir授予相應許可權
SQL> create or replace directory monitor as ‘/oracle/monitor‘;
SQL> grant read,write on directory monitor to system;
Grant succeeded.
SQL> grant read,write on directory monitor to sys;   
Grant succeeded.
2、建立procedure
SQL> create or replace procedure pro_inst_status as
  2    v_tmp         varchar2(60);
  3    v_out_file    utl_file.file_type;
  4    v_file_name   varchar2(30);
  5  begin
  6    v_file_name := ‘inst_status.log‘;
  7    v_out_file := utl_file.fopen(‘MONITOR‘,v_file_name,‘w‘);
  8    if (utl_file.is_open(v_out_file)) then  
  9       select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss‘)||‘,  ‘||instance_name||‘,  ‘||status
 10       into v_tmp from v$instance;
 11       utl_file.put_line(v_out_file, v_tmp);
 12    else
 13       raise_application_error(-20001,‘Inst file Open Failure!‘);
 14    end if;
 15    utl_file.fclose(v_out_file);
 16  end pro_inst_status;
 17    /

Procedure created.
3、執行procedure
SQL> exec pro_inst_status;
BEGIN pro_inst_status; END;

*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "SYS.PRO_INST_STATUS", line 7
ORA-06512: at line 1
報錯因為沒有給dir建目錄。開建。。
[[email protected] ~]# cd /oracle/
[[email protected] oracle]# mkdir monitor
[[email protected] oracle]# chown -R oracle:oinstall monitor/

SQL> exec pro_inst_status;

PL/SQL procedure successfully completed.

SQL> exec pro_inst_status;

PL/SQL procedure successfully completed.
4、查看組建記錄檔
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[[email protected] oracle]$ cd monitor/
[[email protected] monitor]$ ls -ltr
total 4
-rw-r--r-- 1 oracle oinstall 33 Aug 31 13:26 inst_status.log
[[email protected] monitor]$ cat inst_status.log
2016-08-31 13:26:04,  idb,  OPEN
[[email protected] monitor]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 31 13:27:17 2016
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> exec pro_inst_status;
PL/SQL procedure successfully completed.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[[email protected] monitor]$ cat inst_status.txt
2016-08-31 13:27:21,  idb,  OPEN
[[email protected] monitor]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 31 13:36:53 2016
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
5、建job做定時作業
SQL> variable monitor_job number;
SQL> begin
  2  dbms_job.submit(:monitor_job,‘pro_inst_status;‘,sysdate,‘sysdate+1/288‘);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> desc dba_jobs
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 JOB                                       NOT NULL NUMBER
 LOG_USER                                  NOT NULL VARCHAR2(30)
 PRIV_USER                                 NOT NULL VARCHAR2(30)
 SCHEMA_USER                               NOT NULL VARCHAR2(30)
 LAST_DATE                                          DATE
 LAST_SEC                                           VARCHAR2(8)
 THIS_DATE                                          DATE
 THIS_SEC                                           VARCHAR2(8)
 NEXT_DATE                                 NOT NULL DATE
 NEXT_SEC                                           VARCHAR2(8)
 TOTAL_TIME                                         NUMBER
 BROKEN                                             VARCHAR2(1)
 INTERVAL                                  NOT NULL VARCHAR2(200)
 FAILURES                                           NUMBER
 WHAT                                               VARCHAR2(4000)
 NLS_ENV                                            VARCHAR2(4000)
 MISC_ENV                                           RAW(32)
 INSTANCE                                           NUMBER

SQL> select job, what from dba_jobs;

       JOB WHAT
---------- ----------------------------------------------------------------------------------------------------
      4001 wwv_flow_cache.purge_sessions(p_purge_sess_older_then_hrs => 24);
      4002 wwv_flow_mail.push_queue(wwv_flow_platform.get_preference(‘SMTP_HOST_ADDRESS‘),wwv_flow_platform.get
           _preference(‘SMTP_HOST_PORT‘));

         7 pro_inst_status;
         6 dbms_refresh.refresh(‘"I6000_SYS"."SYS_V_USERROLEORGRS"‘);

6、運行job
SQL> begin
  2  dbms_job.run(7);
  3  end;
  4   /

PL/SQL procedure successfully completed.

7、查看運行結果
[[email protected] monitor]# tail -f inst_status.log
2016-08-31 13:45:45,  idb,  OPEN
tail: inst_status.log: file truncated
2016-08-31 13:50:45,  idb,  OPEN
tail: inst_status.log: file truncated
2016-08-31 13:55:46,  idb,  OPEN
tail: inst_status.log: file truncated
2016-08-31 14:00:46,  idb,  OPEN
tail: inst_status.log: file truncated
2016-08-31 14:05:46,  idb,  OPEN
tail: inst_status.log: file truncated
2016-08-31 14:10:46,  idb,  OPEN

Oracle定時查詢結果輸出到指定的log檔案