TRẦN VĂN BÌNH MASTER: Quy trình vận hành cơ sở dữ liệu Oracle

Thứ Bảy, 11 tháng 1, 2020

Quy trình vận hành cơ sở dữ liệu Oracle - 2.Hướng dẫn khai báo partition cho DB

Cách sử dụng script này:

1. Mở TOAd hoặc SQL Navigator

2. Copy toàn bộ và thực hiện từng bước một cho đến khi kết thúc
(hoặc có thể đặt job để tự động sinh partition nhé):

/***** 1. Tao tablespace *****/

--select * from dba_tablespaces where tablespace_name like '%2021%' order by tablespace_name;

--select * from dba_data_files where tablespace_name in ('DATA2021','INDX2021');

-- Sau nay read only tablespace này

create tablespace DATA2020 datafile '+DATA' size 1g autoextend on next 100m;

create tablespace INDX2020 datafile '+DATA' size 1g autoextend on next 100m;

-- Sau này read write tablespace 3, 5, 10 năm

create tablespace DATA2020_RW datafile '+DATA' size 1g autoextend on next 100m;

create tablespace INDX2020_RW datafile '+DATA' size 1g autoextend on next 100m;

/**** 2. GEN PARTITION *****/

/* 2.1.GEN PARTITION THEO NĂM: */

select 'alter table '||table_owner||'.'||table_name||' add PARTITION DATA2021 VALUES LESS THAN (TO_DATE(''2021-01-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) LOGGING TABLESPACE DATA2020;'

from dba_tab_partitions

WHERE PARTITION_NAME LIKE '%2019%'

and table_name not like '%$%' and table_name not like 'XXX%' --and table_name not like 'TMP%'

and table_owner not in ('Test')

GROUP BY table_owner, table_name

having length(max(partition_name))<9

order by table_owner,table_name;

--2.1.1.Rebuild index theo năm:

DECLARE

v_nam varchar2(4) := '2020';

v_tablespace varchar2(50):='INDX';

cursor c1 is

select table_owner,table_name,max(partition_name)

from dba_tab_partitions where table_name not like '%$%' and table_name not like 'XXX%' --and table_name not like 'TMP%'

and table_name not in ('Test')

group by table_owner,table_name having max(partition_name) like '%'||2020||'%' and length(max(partition_name))<9 order by table_owner,table_name;

cursor c2 (p_tablename varchar2,p_owner varchar2) is

select * from DBA_PART_INDEXES where table_name = p_tablename and owner=p_owner;

BEGIN

for r1 in c1

LOOP

FOR r2 in c2(r1.table_name,r1.table_owner)

LOOP

DBMS_OUTPUT.put_line ('alter index '|| r1.table_owner||'.'||r2.index_name || ' REBUILD PARTITION DATA'||v_nam||' TABLESPACE '||v_tablespace||v_nam||' nologging noparallel online;');

END LOOP;

end loop;

END;

/* 2.2.GEN PARTITION THEO THÁNG */

DECLARE

v_nam NUMBER (4) := 2020;

v_thang_from NUMBER (2) := 1;

v_thang_to NUMBER (2) := 12;

v_tablespace varchar2(50):='DATA';

CURSOR c1

select table_owner, table_name,max(partition_name)

from dba_tab_partitions

WHERE PARTITION_NAME LIKE '%2019%'

and table_name not like '%$%' and table_name not like '%TEST%' and table_name not like 'XXX%' and table_name not like '%BAK' and table_name not like 'BK%'

GROUP BY table_owner, table_name

having length(max(partition_name))=10 and max(partition_name) like '%201912'

order by table_owner,table_name;

BEGIN

for r1 in c1 loop

FOR i IN v_thang_from .. (v_thang_to-1)

LOOP

DBMS_OUTPUT.put_line ('alter table '||r1.table_owner ||'.'|| r1.table_name || ' add PARTITION DATA'||v_nam||LPAD (i, 2, '0')||' VALUES LESS THAN (TO_DATE('''|| v_nam ||'-'||LPAD (i+1, 2, '0')||'-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) LOGGING TABLESPACE '||v_tablespace||v_nam||';');

END LOOP;

DBMS_OUTPUT.put_line ('alter table '|| r1.table_owner ||'.'|| r1.table_name || ' add PARTITION DATA'||v_nam||LPAD (to_char(v_thang_to), 2, '0')||' VALUES LESS THAN (TO_DATE('''|| (v_nam +1) ||'-01-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) LOGGING TABLESPACE '||v_tablespace||v_nam||';');

end loop;

END;

-- 2.2.1.Rebuild INDEX theo thang:

DECLARE

v_nam varchar2(4) := '2020';

v_thang_from NUMBER (2) := 01;

v_thang_to NUMBER (2) := 12;

v_tablespace varchar2(50):='INDX';

cursor c1 is

select table_owner,table_name,max(partition_name)

from dba_tab_partitions where table_name not like '%$%' and table_name not like 'XXX%'

group by table_owner,table_name having max(partition_name) like '%'||2020||'%' and length(max(partition_name))=10 order by table_owner,table_name;

cursor c2 (p_tablename varchar2,p_owner varchar2) is

select * from DBA_PART_INDEXES where table_name = p_tablename and owner=p_owner;

BEGIN

for r1 in c1

LOOP

FOR r2 in c2(r1.table_name,r1.table_owner)

LOOP

FOR i IN v_thang_from .. (v_thang_to)

LOOP

DBMS_OUTPUT.put_line ('alter index '|| r1.table_owner||'.'||r2.index_name || ' REBUILD PARTITION DATA'||v_nam||LPAD (to_char(i), 2, '0')||' TABLESPACE '||v_tablespace||v_nam||' nologging parallel 8 online;');

END LOOP; END LOOP;

end loop;

END;

/* 2.3.GEN PARTITION THEO NGAY */

--2.3.1.Gen data partition theo ngay_MAIN_OWNER

-- Chay vao ban dem > 22h, lock ~200, active session ~ 1000

-- Có thể gặp Event: library cache lock, cursor: pin S wait on X

DECLARE

v_tablename VARCHAR2 (50);

v_date_from date;

v_date_to date := to_date('31/12/2020','dd/mm/yyyy');

v_numday number;

v_tablespace varchar2(50):='DATA';

cursor c1 is

select table_owner,table_name,MAX(PARTITION_NAME) par_name from dba_tab_partitions where (table_owner, table_name, partition_name) in (

select table_owner,table_name,MAX(PARTITION_NAME) par_name

from dba_tab_partitions

where LENGTH(PARTITION_NAME)>=11

and table_name not like '%$%' and table_name not like 'XXX%'

and table_owner= 'MAIN_OWNER'

group by table_owner, table_name

)

and partition_name like 'DATA20191231%'

GROUP BY table_owner,table_name order by table_owner,table_name;

BEGIN

for r1 in c1 loop

v_tablename:=r1.table_owner||'.'||r1.table_name;

v_date_from:=to_date(substr(r1.par_name,5,8),'YYYYMMDD')+1;

v_numday:=v_date_to-v_date_from;

FOR i IN 0 .. v_numday

LOOP

DBMS_OUTPUT.put_line ('alter table '|| v_tablename || ' add PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' VALUES LESS THAN (TO_DATE('''|| to_char(v_date_from+i+1,'YYYY-MM-DD')||' 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) LOGGING TABLESPACE DATA2020;');

END LOOP;

end loop;

END;

-- 2.3.1.1.Rebuild Index theo ngay MAIN_OWNER

DECLARE

v_date_from date := to_date('01/01/2020','dd/mm/yyyy');

v_date_to date := to_date('31/12/2020','dd/mm/yyyy');

v_numday number;

v_tablespace varchar2(50):='INDX';

cursor c1 is

select a.* from DBA_PART_INDEXES a, DBA_TAB_PARTITIONS b where a.owner=B.TABLE_OWNER and a.table_name=B.TABLE_NAME

and a.owner='CUS_OWNER' and a.index_name not like '%$%' and b.partition_name like '%20191231' order by a.owner,a.index_name;

BEGIN

v_numday:=v_date_to-v_date_from;

FOR i1 in c1

LOOP

FOR i IN 0 .. v_numday

LOOP

DBMS_OUTPUT.put_line ('alter index '||i1.owner||'.'||i1.index_name || ' REBUILD PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' TABLESPACE '||v_tablespace||to_char(v_date_from+i,'YYYY')||' nologging parallel 8 online;');

END LOOP;

END;

--2.3.2.Gen data partition theo ngay_User khac

DECLARE

v_tablename VARCHAR2 (50);

v_date_from date;

v_date_to date := to_date('31/12/2020','dd/mm/yyyy');

v_numday number;

v_tablespace varchar2(50):='DUMP_DATA';

cursor c1 is

select table_owner,table_name,MAX(PARTITION_NAME) par_name

from dba_tab_partitions

WHERE (LENGTH(PARTITION_NAME)>=12 AND PARTITION_NAME LIKE '%20201231'

and table_name not like '%$%' and table_name not like 'XXX%' and table_name not like '%TEST%' and table_name not like '%DAILY_20%'

and table_owner not in ('MAIN_OWNER')

GROUP BY table_owner,table_name order by table_owner,table_name;

BEGIN

for r1 in c1 loop

v_tablename:=r1.table_owner||'.'||r1.table_name;

v_date_from:=to_date(substr(r1.par_name,5,8),'YYYYMMDD')+1;

v_numday:=v_date_to-v_date_from;

if r1.table_name in ('REQUEST_LOG','RESPOND_LOG') then

FOR i IN 0 .. v_numday

LOOP

END LOOP;

elsif r1.table_name in ('AUDIT_LOG') then

FOR i IN 0 .. v_numday

LOOP

END LOOP;

else

LOOP

END LOOP;

end if;

end loop;

END;

-- 2.3.2.1.Rebuild Index theo ngày user khac

-- Lưu tại DUMP_INDX

DECLARE

v_date_from date := to_date('01/01/2020','dd/mm/yyyy');

v_date_to date := to_date('31/12/2020','dd/mm/yyyy');

v_numday number;

v_tablespace varchar2(50):='DUMP_INDX';

cursor c1 is

select a.*,b.partition_name from DBA_PART_INDEXES a, DBA_TAB_PARTITIONS b where a.owner=B.TABLE_OWNER and a.table_name=B.TABLE_NAME

and a.owner!='CUS_OWNER' and a.index_name not like '%$%' and b.partition_name like '%20201231'

order by a.owner,a.index_name;

BEGIN

v_numday:=v_date_to-v_date_from;

FOR i1 in c1

LOOP

if i1.owner='TEST_GATEWAY' then

FOR i IN 0 .. v_numday

LOOP

DBMS_OUTPUT.put_line ('alter index '||i1.owner||'.'||i1.index_name || ' REBUILD PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' TABLESPACE LOB nologging parallel 8 online;');

END LOOP;

DBMS_OUTPUT.put_line ('alter index '||i1.owner||'.'||i1.index_name || ' noparallel;');

else

FOR j IN 0 .. v_numday

LOOP

DBMS_OUTPUT.put_line ('alter index '||i1.owner||'.'||i1.index_name || ' REBUILD PARTITION DATA'||to_char(v_date_from+j,'YYYYMMDD')||' TABLESPACE INDX' || substr(i1.partition_name,5,4) ||' nologging parallel 8 online;');

END LOOP;

DBMS_OUTPUT.put_line ('alter index '||i1.owner||'.'||i1.index_name || ' noparallel;');

end if;

END LOOP;

END;

-- 2.3.4.Set noparallel index

DECLARE

cursor c1 is

select distinct a.index_owner, a.index_name from DBA_ind_partitions a where a.index_owner='CUS_OWNER' and a.index_name not like '%$%' and a.partition_name like '%20201231'

order by a.index_owner,a.index_name;

BEGIN

FOR i1 in c1

LOOP

DBMS_OUTPUT.put_line ('alter index '||i1.index_owner||'.'||i1.index_name || ' noparallel;');

END LOOP;

END;

-- Một số câu lệnh khác hỗ trợ trong quá trình thực hiện

--Optional: Rebuild INDX PARTITION theo ngay, 1 bang

DECLARE

v_tablename VARCHAR2 (50) := 'REQUEST';

v_owner VARCHAR2 (50) := 'TEST_OWNER';

v_date_from date := '1/11/2011';

v_date_to date := '31/12/2011';

v_numday number(2);

v_tablespace varchar2(50):='INDX';

cursor c1 is

select * from DBA_PART_INDEXES where table_name = v_tablename and owner=v_owner;

BEGIN

v_numday:=v_date_to-v_date_from;

FOR i1 in c1

LOOP

FOR i IN 0 .. v_numday

LOOP

DBMS_OUTPUT.put_line ('alter index '||v_owner||'.'||i1.index_name || ' REBUILD PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' TABLESPACE '||v_tablespace||to_char(v_date_from+i,'YYYYMM')||';');

END LOOP;

END;

-- MONITORING: Neu active session > 600, lock > 50 thi kill tien trinh

-- Trong quá trình thêm partition cần MONITOR chặt chẽ tránh tình trạng lock, cao tải gây timeout ứng dụng, nếu gặp lock, cao tải cần dừng lại và chọn thời điểm THẤP TẢI khác để chạy (ví dụ 1h,2h,...)

-- Active session

SELECT distinct s.inst_id i#, s.username, s.SID SID, s.osuser, s.machine,DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') ACTION,

s.sql_id, SUBSTR(DECODE(SS.SQL_TEXT, NULL, AA.NAME, SS.SQL_TEXT), 1, 1000) SQLTEXT,s.logon_time,s.p1text, S.P1, s.p2text, S.P2, s.p3text, S.P3

FROM GV$SESSION S, GV$SQLSTATS SS, AUDIT_ACTIONS AA

WHERE S.STATUS = 'ACTIVE' AND S.SQL_ID = SS.SQL_ID (+) AND AA.ACTION = S.COMMAND and s.type != 'BACKGROUND' AND S.TYPE = 'USER'

--and s.username not in ('SYS','SYSMAN')

--AND username LIKE 'SYS'

--and s.sid=1234

--and s.machine like '%app-2%'

--and DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') like '%cell single block physical read%'

--and s.sql_id ='cb66zngs8xz5j'

--and lower(ss.sql_text) like lower('%test_customer%')

ORDER BY i#,username,sql_id,machine,S.SID;

/************************************************** LOCK SESSION, WAITING *********************************************************/

select status, count(*) from gv$session group by status order by status;

Select /*blocking_session*/ inst_id,blocking_session, sid, serial#, sql_id, wait_class, seconds_in_wait, username,STATUS,SCHEMANAME,OSUSER,MACHINE,PROGRAM,TYPE,LOGON_TIME

From gv$session where blocking_session is not NULL and type not like 'BACKGROUND' order by inst_id --order by blocking_session;

SELECT /*blocking_session*/ inst_id, sid, DECODE (request, 0, 'Holder: ', 'Waiter: ') || sid sess, id1,id2,lmode, request,TYPE

FROM GV$LOCK WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE FROM GV$LOCK WHERE request > 0) ORDER BY id1, request;

/************************************************** SESSIONS *********************************************************/

select count(*) from gv$session ;

select /* count , status*/ username,status, count(*) from gv$session group by username,status order by count(*) desc;

select USERNAME,count(*) from gv$session group by USERNAME order by count(*) desc;

select machine,count(*) from v$session group by machine order by count(*) desc;

select inst_id, count(*) from gv$session group by inst_id;

select /*Thong ke theo status*/ username,status,count(*) from gv$session where username like 'TEST_OWNER%' group by username,status order by count(*) desc;

select /*Thong ke theo inst_id*/ inst_id,count(*), username from gv$session where username like 'TEST_OWNER%' group by inst_id, username order by username;

select /* user theo machine */ machine,count(*), username from gv$session where username like 'TEST_OWNER%' group by machine, username order by username;

/************************************************** KILLER *********************************************************/

SELECT /*username*/ 'kill -9 ' || SPID A ,a.INST_ID,A.SID,A.SQL_ID, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,BACKGROUND, A.EVENT

FROM gv$session a, gv$process b

WHERE b.ADDR = a.paddr

AND a.inst_id=b.inst_id

--AND B.inst_id = 1

--and a.status='INACTIVE'

--and A.USERNAME LIKE 'TEST%'

--AND A.USERNAME not in ('SYS','SYSTEM','SYSMAN','BINHTV','DBSNMP','GGATE')

--and a.username not in ('SYS','SYSTEM','SYSMAN','BINHTV','DBSNMP','GGATE','APP1','APP2','APP3')

AND a.program not LIKE '%Toad.exe%'

--AND sql_id in ('44t0dk94q3xqg','6uac7nnud2gfa')

and machine like '%TCTK-BINHTV%'

--and a.event in ('library cache lock','brary cache load lock','cursor: pin S wait on X','library cache pin','gc buffer busy acquire','enq: TS - contention','enq: TX - row lock contention','db file parallel read')

and type='USER'

order by a.inst_id;

SELECT /*SID*/ 'kill -9 ' || spid a, a.INST_ID,A.SQL_ID,A.SID, A.SERIAL#, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,BACKGROUND

FROM gv$session a, gv$process b

WHERE b.addr = a.paddr

AND a.inst_id=b.inst_id

--and b.inst_id=2

AND a.sid in (

5812

)

and type='USER'

order by inst_id;

/***** 3.CHECK PARTITION *****/

-- Check partition theo NAM, max partition 2020 la OK

select table_owner,table_name,partition_name from dba_tab_partitions

where (table_owner,table_name,partition_position) in

(select table_owner,table_name,max(partition_position) from dba_tab_partitions

where table_name not like '%$%'

group by table_name,table_owner)

and length(partition_name)<9

order by 1,2,3;

---- Check partition theo THANG, max partiton 202012 la OK

select table_owner,table_name,partition_name from dba_tab_partitions where (table_owner,table_name,partition_position) in

(select table_owner,table_name,max(partition_position) from dba_tab_partitions

where table_name not like '%$%'

group by table_name,table_owner)

and length(partition_name)>=9

--and partition_name like 'DATA201912'

and length(partition_name)<11

order by table_owner, table_name, partition_name;

---- Check partition theo NGAY, max partiton 20201231 la OK

select table_owner,table_name,partition_name from dba_tab_partitions where (table_owner,table_name,partition_position) in

(select table_owner,table_name,max(partition_position) from dba_tab_partitions

where table_name not like '%$%'

group by table_name,table_owner)

and length(partition_name)>=11

and partition_name like 'DATA20191231'

order by table_owner, table_name, partition_name;

-- Check ALL (nam, thang, ngay)

-- Dam bao nam 2019, thang 201912, ngay 20201231

select table_owner,table_name,partition_name from dba_tab_partitions where (table_owner,table_name,partition_position) in

(select table_owner,table_name,max(partition_position) from dba_tab_partitions

where table_name not like '%$%'

group by table_name,table_owner)

order by table_owner, table_name, partition_name;

--Test lai sau khi gen partitioin, null la OK

SELECT table_owner, table_name

FROM ( SELECT table_owner, table_name, MAX (PARTITION_NAME) FROM dba_tab_partitions

WHERE PARTITION_NAME LIKE '%2019%' and table_name not like '%$%'

GROUP BY table_owner, table_name)

MINUS

SELECT table_owner, table_name

FROM ( SELECT table_owner, table_name, MAX (PARTITION_NAME) FROM dba_tab_partitions

WHERE PARTITION_NAME LIKE '%2020%' and table_name not like '%$%'

GROUP BY table_owner, table_name);

SELECT table_owner, table_name

FROM ( SELECT table_owner, table_name, MAX (PARTITION_NAME) FROM dba_tab_partitions

WHERE PARTITION_NAME LIKE '%2020%' and table_name not like '%$%'

GROUP BY table_owner, table_name)

MINUS

SELECT table_owner, table_name

FROM ( SELECT table_owner, table_name, MAX (PARTITION_NAME) FROM dba_tab_partitions

WHERE PARTITION_NAME LIKE '%2019%' and table_name not like '%$%'

GROUP BY table_owner, table_name);

@ Trần Văn Bình - Founder of Oracle DBA AZ #học oracle #oracle database #khóa học oracle online #khóa học oca #học oca ở đâu #oca là gì #oca oracle #BossData #OraAz #OracleDBAAz #OracleTutorial #Quản_trị_cơ_sở_dữ_liệu_Oracle #OracleDBA #OracleDatabaseAdministration

Thứ Bảy, 11 tháng 1, 2020

Quy trình vận hành cơ sở dữ liệu Oracle - 2.Hướng dẫn khai báo partition cho DB

ĐỌC NHIỀU