'1. IT Story/Scripts'에 해당되는 글 75건

set heading off

set feedback off

set pages 1000

set verify off

spool allcompile.sql

SELECT 

'alter '||DECODE(object_type,'PACKAGE BODY',

'PACKAGE',object_type)||' '||owner||'.'||object_name||

' compile'||

DECODE(object_type,'PACKAGE BODY',' body;',';')

FROM dba_objects

WHERE object_type in ('VIEW','FUNCTION','TRIGGER','PROCEDURE','PACKAGE','PACKAGE BODY')

AND status='INVALID'

ORDER BY owner,decode

(object_type,'VIEW','A','FUNCTION','B',

'TRIGGER','C','PROCEDURE','D',

'PACKAGE','E','PACKAGE BODY','F')

,object_name

/

spool off

set lines 200 pages 200

set pagesize 66

set pause on

set verify off

set feed off

clear screen

column sid heading 'sid' format 999

column username heading 'username' format a8

column pgm heading 'program' format a25

column terminal heading 'terminal' format a8

column pga heading 'PGA session memory' format a11

column uga heading 'UGA session memory' format a11

column pga_sum heading 'SUM PGA mem' format a12

column uga_sum heading 'SUM UGA mem' format a12

column pga_max heading 'Max PGA session memory' format a15

column uga_max heading 'Max UGA session memory' format a15

column pga_m_sum heading 'Sum Max PGA session memory' format a11

column uga_m_sum heading 'Sum Max UGA session memory' format a11

spool sess.txt

ttitle '**********< Program Global Area >**********'

ttitle '1. Current pga, uga session memory'

select a.sid, a.username, substr(a.program, 1, 25) as pgm, a.terminal,

max(decode(c.name, 'session pga memory', trunc(value/1000)||'K', 0)) pga,

max(decode(c.name, 'session uga memory', trunc(value/1000)||'K', 0)) uga

from v$session a, v$sesstat b, v$statname c

where a.sid = b.sid

and b.statistic# = c.statistic#

and c.name like 'session%'

group by a.sid, a.username, substr(a.program, 1, 25), a.terminal;

ttitle '2. Sum of current pga, uga session memory'

select 'Current PGA, UGA session memory SUM:' as sum,

sum(decode(c.name, 'session pga memory', trunc(value/1000),0))||'K' pga_sum,

sum(decode(c.name, 'session uga memory', trunc(value/1000),0))||'K' uga_sum

from v$session a, v$sesstat b, v$statname c

where a.sid = b.sid

and b.statistic# = c.statistic#

and c.name like 'session%';

ttitle '3. Max(peak) pga, pga session memory'

select a.sid, a.username, substr(a.program, 1, 25) as pgm, a.terminal,

max(decode(c.name, 'session pga memory max', trunc(value/1000)||'K', 0)) pga_max,

max(decode(c.name, 'session uga memory max', trunc(value/1000)||'K', 0)) uga_max

from v$session a, v$sesstat b, v$statname c

where a.sid = b.sid

and b.statistic# = c.statistic#

and c.name like 'session%'

group by a.sid, a.username, substr(a.program, 1, 25), a.terminal;

ttitle '4. Sum of max(peak) pga, uga session memory'

select 'Max(peak) PGA, UGA session memory SUM:' as sum,

sum(decode(c.name, 'session pga memory max', trunc(value/1000), 0))||'K' pga_m_sum,

sum(decode(c.name, 'session uga memory max', trunc(value/1000), 0))||'K' uga_m_sum

from v$session a, v$sesstat b, v$statname c

where a.sid = b.sid

and b.statistic# = c.statistic#

and c.name like 'session%';

spool off

exit

GRANT EXECUTE ON DBMS_OBFUSCATION_TOOLKIT TO system;

GRANT EXECUTE ON DBMS_CRYPTO TO system;

create or replace function TYS_Encrypt (input_string VARCHAR2) RETURN RAW

IS

   -- select DBMS_CRYPTO.RANDOMBYTES (num_key_bytes) from dual; --> key_bytes_raw

   encrypted_raw      RAW (2000);             -- stores encrypted binary text

   key_bytes_raw      RAW (32) := HEXTORAW('6E266456572A3E4C79D677BAC5144A645DF911CE07F18AEFF57C3E8560AE1672');  --암호화 키

   encryption_type    PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;  --암호화 알고리즘

BEGIN

     encrypted_raw := DBMS_CRYPTO.ENCRYPT

      (src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),

       typ => encryption_type,

       key => key_bytes_raw);

   return encrypted_raw;

END;

/

[PACKAGE]

CREATE OR REPLACE PACKAGE SCOTT.CRYPTO_AES256

IS

/******************************************************************************

  암호화

 ******************************************************************************/

 FUNCTION ENC_AES ( input_string IN VARCHAR2

 ) RETURN VARCHAR2;

/******************************************************************************

  복호화

 ******************************************************************************/

 FUNCTION DEC_AES (  encrypted_raw IN VARCHAR2

 ) RETURN VARCHAR2;                       

END CRYPTO_AES256;

/

CREATE OR REPLACE PACKAGE BODY SCOTT.CRYPTO_AES256

IS

/******************************************************************************

  암호화

 ******************************************************************************/

 FUNCTION ENC_AES ( input_string IN VARCHAR2

 ) RETURN VARCHAR2

 IS

   encrypted_raw      RAW (2000);             -- 암호화된 RAW타입 데이터

   key_bytes_raw      RAW (32);               -- 암호화 KEY (32RAW => 32Byte => 256bit)

   encryption_type    PLS_INTEGER :=          -- 암호화 알고리즘 선언

                            DBMS_CRYPTO.ENCRYPT_AES256

                          + DBMS_CRYPTO.CHAIN_CBC

                          + DBMS_CRYPTO.PAD_PKCS5;

    BEGIN

        key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678901234567890123456789012', 'AL32UTF8');

        encrypted_raw := DBMS_CRYPTO.ENCRYPT

            (

                src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),

                typ => encryption_type,

                key => key_bytes_raw

            );

        -- 에러 방지를 위해 base64_encode로 인코딩 처리..

        -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error

        RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(encrypted_raw));

    END ENC_AES;

/******************************************************************************

  복호화

 ******************************************************************************/

 FUNCTION DEC_AES (  encrypted_raw IN VARCHAR2

 ) RETURN VARCHAR2

IS

   output_string      VARCHAR2 (200);         -- 복호화된 문자열

   decrypted_raw      RAW (2000);             -- 복호화된 raw타입 데이터

   key_bytes_raw      RAW (32);               -- 256bit 암호화 key

   encryption_type    PLS_INTEGER :=          -- 복호화 알고리즘 선언

                            DBMS_CRYPTO.ENCRYPT_AES256

                          + DBMS_CRYPTO.CHAIN_CBC

                          + DBMS_CRYPTO.PAD_PKCS5;

    BEGIN

        key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678901234567890123456789012', 'AL32UTF8');

        decrypted_raw := DBMS_CRYPTO.DECRYPT

            (

                -- 에러 방지를 위해 base64_decode로 인코딩 처리..

                -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error

                src => UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(encrypted_raw)),

                typ => encryption_type,

                key => key_bytes_raw

            );

       output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');

       RETURN output_string;

    END DEC_AES;                       

END CRYPTO_AES256;

/

[Funtion]

create FUNCTION ENC_AES ( input_string IN VARCHAR2

 ) RETURN VARCHAR2

 IS

 /******************************************************************************

  암호화

 ******************************************************************************/

   encrypted_raw      RAW (2000);             -- 암호화된 RAW타입 데이터

   key_bytes_raw      RAW (32);               -- 암호화 KEY (32RAW => 32Byte => 256bit)

   encryption_type    PLS_INTEGER :=          -- 암호화 알고리즘 선언

                            DBMS_CRYPTO.ENCRYPT_AES256

                          + DBMS_CRYPTO.CHAIN_CBC

                          + DBMS_CRYPTO.PAD_PKCS5;

    BEGIN

        key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678901234567890123456789012', 'AL32UTF8');

        encrypted_raw := DBMS_CRYPTO.ENCRYPT

            (

                src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),

                typ => encryption_type,

                key => key_bytes_raw

            );

        -- 에러 방지를 위해 base64_encode로 인코딩 처리..

        -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error

        RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(encrypted_raw));

    END ENC_AES;

 /

 create FUNCTION DEC_AES (  encrypted_raw IN VARCHAR2

 ) RETURN VARCHAR2

IS

/******************************************************************************

  복호화

 ******************************************************************************/

   output_string      VARCHAR2 (200);         -- 복호화된 문자열

   decrypted_raw      RAW (2000);             -- 복호화된 raw타입 데이터

   key_bytes_raw      RAW (32);               -- 256bit 암호화 key

   encryption_type    PLS_INTEGER :=          -- 복호화 알고리즘 선언

                            DBMS_CRYPTO.ENCRYPT_AES256

                          + DBMS_CRYPTO.CHAIN_CBC

                          + DBMS_CRYPTO.PAD_PKCS5;

    BEGIN

        key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678901234567890123456789012', 'AL32UTF8');

        decrypted_raw := DBMS_CRYPTO.DECRYPT

            (

                -- 에러 방지를 위해 base64_decode로 인코딩 처리..

                -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error

                src => UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(encrypted_raw)),

                typ => encryption_type,

                key => key_bytes_raw

            );

       output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');

       RETURN output_string;

    END DEC_AES;                       

/

export EDITOR=vi

echo "======================================="

echo "===Please Select the Instance Number==="

echo "======================================="

echo "1) TESTDB"

echo "2) STARTDB"

echo "3) ABCDB"

echo "What number do you want?"

read number

case $number in

        "1")

                export ORACLE_SID=TESTDB

                echo "Thank you for selecting TESTDB"

                ;;

        "2")

                export ORACLE_SID=STARTDB

                echo "Thank you for selecting STARTDB"

                ;;

        "3")

                export ORACLE_SID=ABCDB

                echo "Thank you for selecting ABCDB"

                ;;

        *)

                exit;;

esac

define LOWTHRESHOLD=10

define MIDTHRESHOLD=62

define VERBOSE=FALSE

set veri off;

set feedback off;

set serverout on

DECLARE

 verbose boolean:=&&VERBOSE;

BEGIN

 For C in (

  select 

   version, 

   date_time,

   dbms_flashback.get_system_change_number current_scn,

   indicator

  from

  (

   select

   version,

   to_char(SYSDATE,'YYYY/MM/DD HH24:MI:SS') DATE_TIME,

   ((((

    ((to_number(to_char(sysdate,'YYYY'))-1988)*12*31*24*60*60) +

    ((to_number(to_char(sysdate,'MM'))-1)*31*24*60*60) +

    (((to_number(to_char(sysdate,'DD'))-1))*24*60*60) +

    (to_number(to_char(sysdate,'HH24'))*60*60) +

    (to_number(to_char(sysdate,'MI'))*60) +

    (to_number(to_char(sysdate,'SS')))

    ) * (16*1024)) - dbms_flashback.get_system_change_number)

   / (16*1024*60*60*24)

   ) indicator

   from v$instance

  ) 

 ) LOOP

  dbms_output.put_line( '-----------------------------------------------------'

                        || '---------' );

  dbms_output.put_line( 'ScnHealthCheck' );

  dbms_output.put_line( '-----------------------------------------------------'

                        || '---------' );

  dbms_output.put_line( 'Current Date: '||C.date_time );

  dbms_output.put_line( 'Current SCN:  '||C.current_scn );

  if (verbose) then

    dbms_output.put_line( 'SCN Headroom: '||round(C.indicator,2) );

  end if;

  dbms_output.put_line( 'Version:      '||C.version );

  dbms_output.put_line( '-----------------------------------------------------'

                        || '---------' );

  IF C.version > '10.2.0.5.0' and 

     C.version NOT LIKE '9.2%' THEN

    IF C.indicator>&MIDTHRESHOLD THEN 

      dbms_output.put_line('Result: A - SCN Headroom is good');

      dbms_output.put_line('Apply the latest recommended patches');

      dbms_output.put_line('based on your maintenance schedule');

      IF (C.version < '11.2.0.2') THEN

        dbms_output.put_line('AND set _external_scn_rejection_threshold_hours='

                             || '24 after apply.');

      END IF;

    ELSIF C.indicator<=&LOWTHRESHOLD THEN

      dbms_output.put_line('Result: C - SCN Headroom is low');

      dbms_output.put_line('If you have not already done so apply' );

      dbms_output.put_line('the latest recommended patches right now' );

      IF (C.version < '11.2.0.2') THEN

        dbms_output.put_line('set _external_scn_rejection_threshold_hours=24 '

                             || 'after apply');

      END IF;

      dbms_output.put_line('AND contact Oracle support immediately.' );

    ELSE

      dbms_output.put_line('Result: B - SCN Headroom is low');

      dbms_output.put_line('If you have not already done so apply' );

      dbms_output.put_line('the latest recommended patches right now');

      IF (C.version < '11.2.0.2') THEN

        dbms_output.put_line('AND set _external_scn_rejection_threshold_hours='

                             ||'24 after apply.');

      END IF;

    END IF;

  ELSE

    IF C.indicator<=&MIDTHRESHOLD THEN

      dbms_output.put_line('Result: C - SCN Headroom is low');

      dbms_output.put_line('If you have not already done so apply' );

      dbms_output.put_line('the latest recommended patches right now' );

      IF (C.version >= '10.1.0.5.0' and 

          C.version <= '10.2.0.5.0' and 

          C.version NOT LIKE '9.2%') THEN

        dbms_output.put_line(', set _external_scn_rejection_threshold_hours=24'

                             || ' after apply');

      END IF;

      dbms_output.put_line('AND contact Oracle support immediately.' );

    ELSE

      dbms_output.put_line('Result: A - SCN Headroom is good');

      dbms_output.put_line('Apply the latest recommended patches');

      dbms_output.put_line('based on your maintenance schedule ');

      IF (C.version >= '10.1.0.5.0' and

          C.version <= '10.2.0.5.0' and

          C.version NOT LIKE '9.2%') THEN

       dbms_output.put_line('AND set _external_scn_rejection_threshold_hours=24'

                             || ' after apply.');

      END IF;

    END IF;

  END IF;

  dbms_output.put_line(

    'For further information review MOS document id 1393363.1');

  dbms_output.put_line( '-----------------------------------------------------'

                        || '---------' );

 END LOOP;

end;

/

1. OS 점검

free -g

df -h

ps awxuf |grep mysqld |grep -v grep |grep -v safe |awk '{print $4}'

ps ax |grep mysqld

du -sh /mysql_data/log/mysql.err 

cat /etc/my.cnf

2. Mysql DB 점검

/usr/local/mysql/bin/mysql -uroot -p

\s

show processlist;

show global variables;

show global status;

show global status like "select%";

select sum(index_length+data_length)/1024/1024/1024 from information_schema.tables;

show databases;

show engine innodb status\G;

show slave status\G;

1. create_program 

BEGIN

  -- Shell Script.

  DBMS_SCHEDULER.create_program (

    program_name        => 'hotbackup_executable_prog',

    program_type        => 'EXECUTABLE',

    program_action      => '/backup/hotbackup/test.sql',

    number_of_arguments => 0,

    enabled             => TRUE,

    comments            => 'Program to gather SCOTT''s statistics us a shell script.');

        END;

/

2. create_schedule 

BEGIN

  DBMS_SCHEDULER.create_schedule (

    schedule_name   => 'test_min_schedule',

    start_date      => SYSTIMESTAMP,

    repeat_interval => 'freq=MINUTELY; INTERVAL=30',

    end_date        => NULL,

    comments        => 'Repeats hourly, on the hour, for ever.');

END;

/

3. create_job 

BEGIN

  DBMS_SCHEDULER.create_job (

    job_name      => 'test_prog_sched_job_definition',

    program_name  => 'hotbackup_executable_prog',

    schedule_name => 'test_min_schedule',

    enabled       => TRUE,

    comments      => 'Job defined by an existing program and schedule.');

            END;

/

SELECT    'alter tablespace '

         || t.tablespace_name

         || ' begin backup;'

         || CHR (10)

         || '!cp -p '

         || d.file_name

         || ' /oradata_backup/HOT/ '

         || CHR (10)

         || 'alter tablespace '

         || t.tablespace_name

         || ' end backup;'

         || CHR (10)

    FROM dba_tablespaces t, dba_data_files d

   WHERE t.TABLESPACE_NAME = d.TABLESPACE_NAME

ORDER BY t.tablespace_name;