[10g] 오라클 서버 용량 수집 자동화 내역 - ver.dbrang

/********************************************************************************************
-- Title : [10g] 오라클 서버 용량 수집 자동화 내역 - ver.dbrang
-- Reference : dbrang.tistory.com
-- Key word : automation collection
********************************************************************************************/

/**********************************
-- directory 확인
**********************************/
-- directory 확인
select * from dba_directories;

-- directory 생성
drop directory new_directory;
CREATE OR REPLACE DIRECTORY new_directory as '/home/oracle';

 

/***************************************
-- CREATE_JOB 등록(Shell Script 사용)
***************************************/
$ vi /home/oracle/exec_df_m.sh
#!/bin/sh

rm -rf /home/oracle/*.log
rm -rf /home/oracle/df_m_date.txt
rm -rf /home/oracle/df_m_result.txt

dd=`date '+%Y%m%d'`

echo "$dd||This command [df-m] has been executed. " > /home/oracle/df_m_date.txt
df -m > /home/oracle/df_m_result.txt 

/********************************************
-- txt에서 table로 저장(Shell Script 사용)
********************************************/
$ vi /oracle/home/ins_from_txt_to_tbl.sh
#!/bin/sh

df_m_date="/home/oracle/df_m_date.txt"
df_m_result="/home/oracle/df_m_result.txt"

if [ ! -f $df_m_date ] || [ ! -f $df_m_result ]; then
    exit
fi

sqlplus /nolog <<EOF
conn /as sysdba;
    insert into new_User.JOB_disk_usage_log
    select substr(cntnt, 1, instr(cntnt, '||', 1, 1) - 1) as grt_dt
         , substr(cntnt, instr(cntnt, '||', 1, 1) + 2, length(cntnt)) as cntnt
    from new_User.JOB_df_m_date;

    insert into new_User.JOB_disk_usage
    select u.grt_dt, row_number() over(order by u.mounted) as seq
         , u.filesystem, u.blocks, u.used, u.available, u.use_pct, u.mounted
    from
    (   select to_char(sysdate, 'yyyymmdd') as grt_dt
             , d.filesystem, to_number(d.blocks) as blocks, to_number(d.used) as used
             , to_number(d.available) as available
             , substr(d.cntnt, 1, instr(d.cntnt, ' ', 1, 1) - 1) as use_pct
             , substr(d.cntnt, instr(d.cntnt, ' ', 1, 1), length(d.cntnt)) as mounted
        from
        (   select c.filesystem, c.blocks, c.used
                 , substr(c.cntnt, 1, instr(c.cntnt, ' ', 1, 1) - 1) as available
                 , ltrim(substr(c.cntnt, instr(c.cntnt, ' ', 1, 1), length(c.cntnt))) as cntnt
            from
            (   select b.filesystem, b.blocks
                     , substr(b.cntnt, 1, instr(b.cntnt, ' ', 1, 1) - 1) as used
                     , ltrim(substr(b.cntnt, instr(b.cntnt, ' ', 1, 1), length(b.cntnt))) as cntnt
                from
                (   select a.filesystem
                         , substr(a.cntnt, 1, instr(a.cntnt, ' ', 1, 1) - 1) as blocks
                         , ltrim(substr(a.cntnt, instr(a.cntnt, ' ', 1, 1), length(a.cntnt))) as cntnt
                    from
                    (   SELECT substr(cntnt, 1, instr(cntnt, ' ', 1, 1) - 1) as filesystem
                             , ltrim(substr(cntnt, instr(cntnt, ' ', 1, 1), length(cntnt))) as cntnt
                        FROM new_User.JOB_df_m_result
                        where cntnt not like 'Filesystem%'
                    ) a
                ) b
            ) c
        ) d
        where d.blocks is not null
    ) u;
        
    commit;
disconnect;
exit;
EOF


/******************************************************
-- 환경 변수 추가(Shell Script 사용)
-- 오라클 유저의 환경 변수 추가 필요(오라클 명령 실행시)
******************************************************/
$ cat $HOME/.bash_profile
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=ORCL
export ORA_NLS10=$ORACLE_HOME/nls/data
export TNS_ADMIN=$ORACLE_HOME/network/admin
export NLS_LANG=American_America.KO16MSWIN949
export PATH=$ORACLE_HOME/bin:.:$PATH
...

$ vi /oracle/home/ins_from_txt_to_tbl.sh
#!/bin/sh
# .bash_profile에 있는 환경 변수 추가
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=ORCL
export ORA_NLS10=$ORACLE_HOME/nls/data
export TNS_ADMIN=$ORACLE_HOME/network/admin
export NLS_LANG=American_America.KO16MSWIN949
export PATH=$ORACLE_HOME/bin:.:$PATH
...


/**********************************
-- 실행권한 부여 및 스크립트 수행
**********************************/
$ chmod 700 *.sh

$ ./exec_df_m.sh


/**********************************
-- 외부테이블 생성
**********************************/
--결과 테이블 생성
DROP TABLE new_User.JOB_df_m_result;
CREATE TABLE new_User.JOB_df_m_result
(
   cntnt VARCHAR2(1000)
)
ORGANIZATION EXTERNAL
(
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY new_directory
    ACCESS PARAMETERS
    (
        RECORDS DELIMITED BY NEWLINE
        FIELDS
        REJECT ROWS WITH ALL NULL FIELDS
    )
    LOCATION ('df_m_result.txt')
)
REJECT LIMIT UNLIMITED;

--수행 일자 테이블생성
DROP TABLE new_User.JOB_df_m_date;
CREATE TABLE new_User.JOB_df_m_date
(
   cntnt VARCHAR2(1000)
)
ORGANIZATION EXTERNAL
(
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY new_directory
    ACCESS PARAMETERS
    (
        RECORDS DELIMITED BY NEWLINE
        FIELDS
        REJECT ROWS WITH ALL NULL FIELDS
    )
    LOCATION ('df_m_date.txt')
)
REJECT LIMIT UNLIMITED;

/**********************************
-- 관리 테이블 생성
**********************************/
drop table new_User.JOB_disk_usage;
create table new_User.JOB_disk_usage
( grt_dt char(8) not null
, seq number(1) not null
, filesystem varchar2(50) null
, blocks number null
, used number null
, available number not null
, used_pct varchar2(5) null
, mounted varchar2(50) not null
, constraint pk_job_disk_usage primary key(grt_dt, seq)
) tablespace USERS;

drop table new_User.JOB_disk_usage_log;
create table new_User.JOB_disk_usage_log
( grt_dt char(8) not null
, cntnt varchar(1000) null
, constraint pk_job_disk_usage_log primary key(grt_dt)
) tablespace USERS;

 

/**********************************
-- 스크립트 수행
**********************************/
$ ./exec_df_m.sh
$ ./ins_from_txt_to_tbl.sh

  
/**********************************
-- 데이터 생성 확인
**********************************/
select * from new_User.JOB_df_m_result;
select * from new_User.JOB_df_m_date;

select * from new_User.JOB_disk_usage; 

select * from new_User.JOB_disk_usage_log;

  
/**********************************
-- CRONTAB 등록
**********************************/
$ crontab -e
0 4 * * * /home/oracle/exec_df_m.sh &
5 4 * * * /home/oracle/ins_from_txt_to_tbl.sh &