블로그 이미지
bedbmsguru

Notice

Recent Comment

Recent Trackback

Archive

calendar

      1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31  
  • 30,768total
  • 11today
  • 12yesterday
2018.08.13 16:52 오라클

--특정테이블 index Size 조회


SELECT 
T1.owner, T1.segment_name, T1.segment_type, T1.bytes/1024/1024 SIZE_MB, T2.PRIMARY_KEY, T2.UNIQUENESS, INDEX_POS1, INDEX_POS2, INDEX_POS3, INDEX_POS4, INDEX_POS5, INDEX_POS6, INDEX_POS7, INDEX_POS8, INDEX_POS9, INDEX_POS10
FROM DBA_SEGMENTS T1
INNER JOIN 
(
SELECT * FROM TABLE(PKG_DBA_REPOSITORY.GET_INDEX_INFO('테이블이름'))
) T2
ON T1.SEGMENT_NAME = T2.INDEX_NAME



--PKG_DBA_REPOSITORY.GET_INDEX_INFO 함수 생성

http://bedbmsguru.tistory.com/101?category=793983

posted by bedbmsguru
2018.07.05 08:31 Zabbix

트리거 Flapping 방지


({TRIGGER.VALUE}=0 and {Template OS Windows2:perf_counter["\Processor Information(_Total)\% Processor Time",60].avg(#3)}>85)
or
({TRIGGER.VALUE}=1 and {Template OS Windows2:perf_counter["\Processor Information(_Total)\% Processor Time",60].avg(#3)}>65)



출처

https://blog.zabbix.com/no-more-flapping-define-triggers-the-smart-way/1488/



posted by bedbmsguru
2018.07.04 13:32 Zabbix

Zabbix Proxy 구성중 "zabbix_proxy - cannot connect [13] permission denied" 에러가 발생할 경우


SElinux  설정으로 발생



아래 코드 실행 

getsebool -a | grep zabbix


아래와 같이 결과가 나오게 될경우 발생하는 문제임


httpd_can_connect_zabbix --> off

zabbix_can_network --> off



아래 command를 실행하여 해결

setsebool -P zabbix_can_network on



https://www.zabbix.com/forum/zabbix-troubleshooting-and-problems/41290-zabbix_proxy-cannot-connect-13-permission-denied

posted by bedbmsguru
2018.06.26 16:57 오라클

인덱스 조회용 쿼리를 필요할때마다 불러와서 실행하기가 불편해서 타이핑이 가능한 수준으로 줄였음 Package와 Function 활용



--실행

SELECT * FROM TABLE(PKG_DBA_REPOSITORY.GET_INDEX_INFO('테이블명'));



ORACLE INDEX 정보조회 FUNCTION 만들기

SELECT * FROM TABLE(PKG_DBA_REPOSITORY.GET_INDEX_INFO('테이블명'));

CREATE OR REPLACE PACKAGE PKG_DBA_REPOSITORY IS
TYPE INDEX_INFO_RECORD IS RECORD
(
INDEX_NAME VARCHAR2(100),
INDEX_TYPE VARCHAR2(100),
PRIMARY_KEY VARCHAR2(100),
UNIQUENESS VARCHAR2(100),
INDEX_POS1 VARCHAR2(100),
INDEX_POS2 VARCHAR2(100),
INDEX_POS3 VARCHAR2(100),
INDEX_POS4 VARCHAR2(100),
INDEX_POS5 VARCHAR2(100),
INDEX_POS6 VARCHAR2(100),
INDEX_POS7 VARCHAR2(100),
INDEX_POS8 VARCHAR2(100),
INDEX_POS9 VARCHAR2(100),
INDEX_POS10 VARCHAR2(100)

);
--Return 하게될 Row type Array
TYPE INDEX_INFO_TABLE IS TABLE OF INDEX_INFO_RECORD;


FUNCTION GET_INDEX_INFO(IN_TABLE_NAME IN VARCHAR2) RETURN INDEX_INFO_TABLE PIPELINED;
END PKG_DBA_REPOSITORY;


CREATE OR REPLACE PACKAGE BODY PKG_DBA_REPOSITORY IS

FUNCTION GET_INDEX_INFO(IN_TABLE_NAME IN VARCHAR2)
RETURN INDEX_INFO_TABLE PIPELINED
IS
v_menu INDEX_INFO_RECORD;
BEGIN
FOR func_cur IN
(
select index_name
,index_type
,decode(constraint_name, null, ' ', 'pk') primary_key
,uniqueness
,nvl(max(a),' ') index_pos1
,nvl(max(b),' ') index_pos2
,nvl(max(c),' ') index_pos3
,nvl(max(d),' ') index_pos4
,nvl(max(e),' ') index_pos5
,nvl(max(f),' ') index_pos6
,nvl(max(g),' ') index_pos7
,nvl(max(h),' ') index_pos8
,nvl(max(i),' ') index_pos9
,nvl(max(j),' ') index_pos10
from
(
select a.index_name
,a.index_type
,a.uniqueness
,c.constraint_name
,DECODE(column_position, 1, column_name, '') a
,DECODE(column_position, 2, column_name, '') b
,DECODE(column_position, 3, column_name, '') c
,DECODE(column_position, 4, column_name, '') d
,DECODE(column_position, 5, column_name, '') e
,DECODE(column_position, 6, column_name, '') f
,DECODE(column_position, 7, column_name, '') g
,DECODE(column_position, 8, column_name, '') h
,DECODE(column_position, 9, column_name, '') i
,DECODE(column_position, 10, column_name, '') j
from all_indexes a, all_ind_columns b, all_constraints c
where a.table_name = IN_TABLE_NAME
and a.table_name = b.table_name
and a.index_name = b.index_name
and a.table_name = c.table_name(+)
and a.index_name = c.constraint_name(+)
and c.constraint_type(+) = 'P'
order by index_name, column_position
)
group by index_name, index_type,constraint_name, uniqueness
order by primary_key desc, index_name
)
LOOP
v_menu.INDEX_NAME := func_cur.index_name;
v_menu.INDEX_TYPE := func_cur.index_type;
v_menu.PRIMARY_KEY := func_cur.primary_key;
v_menu.UNIQUENESS := func_cur.uniqueness;
v_menu.INDEX_POS1 := func_cur.index_pos1;
v_menu.INDEX_POS2 := func_cur.index_pos2;
v_menu.INDEX_POS3 := func_cur.index_pos3;
v_menu.INDEX_POS4 := func_cur.index_pos4;
v_menu.INDEX_POS5 := func_cur.index_pos5;
v_menu.INDEX_POS6 := func_cur.index_pos6;
v_menu.INDEX_POS7 := func_cur.index_pos7;
v_menu.INDEX_POS8 := func_cur.index_pos8;
v_menu.INDEX_POS9 := func_cur.index_pos9;
v_menu.INDEX_POS10 :=func_cur.index_pos10;
PIPE ROW (v_menu);
END LOOP;
END GET_INDEX_INFO;
END PKG_DBA_REPOSITORY;

posted by bedbmsguru
2018.06.22 15:09 Zabbix

IBM 장비에 Suse Linux를 설치하여 사용하는 시스템이 있다...


비싼IBM장비에 왜 Linux를...ㅡㅡ


해당머신에서 소스컴파일 하니 Agent 사용가능하다. Zabbix는 정말 안되는게 없는듯.!!


(1)Zabbix Source Download 

https://www.zabbix.com/download_sources



(2) 아래 메뉴얼 참고하여 컴파일 

https://github.com/linux-on-ibm-z/docs/wiki/Building-Zabbix-agent

posted by bedbmsguru
2018.06.19 11:55 오라클

한글깨짐으로 인해 DB 캐릭터셋을 변경하고 impdb 작업하다가  


ORA-00600 internal error code, arguments:[kokle_lob2lob13:input mismatch] 

ORA-29213 두가지 에러가 발생


아래 두개의 블로그로 해결 가능


http://atin.tistory.com/463


http://www.dba-oracle.com/t_ora_39213_metadata_processing.htm

posted by bedbmsguru
2018.06.15 13:58 오라클

오라클 인덱스 칼럼 정보 조회

select  index_name
    ,index_type
    ,decode(constraint_name, null, ' ', 'pk') primary_key
    ,uniqueness
    ,nvl(max(a),' ') index_pos1
    ,nvl(max(b),' ') index_pos2
    ,nvl(max(c),' ') index_pos3
    ,nvl(max(d),' ') index_pos4
    ,nvl(max(e),' ') index_pos5
    ,nvl(max(f),' ') index_pos6    
    ,nvl(max(g),' ') index_pos7        
    ,nvl(max(h),' ') index_pos8        
    ,nvl(max(i),' ') index_pos9
    ,nvl(max(j),' ') index_pos10
from
(
select  a.index_name
    ,a.index_type
       ,a.uniqueness
       ,c.constraint_name
       ,DECODE(column_position, 1, column_name, '') a
       ,DECODE(column_position, 2, column_name, '') b
       ,DECODE(column_position, 3, column_name, '') c
       ,DECODE(column_position, 4, column_name, '') d
       ,DECODE(column_position, 5, column_name, '') e          
       ,DECODE(column_position, 6, column_name, '') f
       ,DECODE(column_position, 7, column_name, '') g       
       ,DECODE(column_position, 8, column_name, '') h       
       ,DECODE(column_position, 9, column_name, '') i       
       ,DECODE(column_position, 10, column_name, '') j       
from all_indexes a, all_ind_columns b, all_constraints c
where a.table_name = upper('테이블이름')
and a.table_name = b.table_name
and a.index_name = b.index_name
and a.table_name = c.table_name(+)
and a.index_name = c.constraint_name(+)
and c.constraint_type(+) = 'P'
order by index_name, column_position
)
group by index_name, index_type,constraint_name, uniqueness
order by primary_key desc, index_name
;


posted by bedbmsguru
2018.05.25 15:39 Powershell

1. AD  등록된 서버

AD 관리자 계정으로 스크립트 실행


Invoke-Command -ComputerName host1, host2 -ScriptBlock {Invoke-Expression 'C:\script.bat'}



2.AD에 등록되지 않은 서버


(1) 로컬 서버의 "신뢰할 수 있는 호스트 목록" 에 원격 서버 추가하기.
    Set-Item WSMan:\localhost\Client\TrustedHosts -Value "원격서버IP 혹은 호스트네임"


    여러개의 원격 서버를 지정해야할 경우

     Set-Item WSMan:\localhost\Client\TrustedHosts -Value "host1, host2"



(2)원격서버에서 스크립트 실행

$password='p@ssword'|convertto-securestring -asplaintext -force;

$cred=new-object -typename system.management.automation.pscredential('Domain\Username',$password);

$s = New-PSSession -computer "127.0.0.1" -credential $cred;

Invoke-Command -Session $s -ScriptBlock { cmd /c "c:\myfile.bat" };

Remove-PSSession $s;



'Powershell' 카테고리의 다른 글

Powershell 외부서버의 스크립트 실행하기  (0) 2018.05.25
posted by bedbmsguru

티스토리 툴바