Oracle DBA

RHEL3-on hasznaltam, shell scriptekben, ha pl. parallel sql-t szerettem volna futtatni es a parallellizmust a processzorszamhoz szerettem volna igazitani:

echo $((`grep processor /proc/cpuinfo | cut -d":" -f2|sort -n|tail -1|tr -d " "` + 1))

az ugyfel nem kivant tul szofisztikalt DR (disaster recovery) megoldast, tobb verzio felvazolasa utan ugy dontott, hogy a mindenkori sikeres napi mentes allapotara tudjunk visszaallni. Ez ugye legrosszabb esetben 1 napi adatvesztest jelent (jo, feltetelezve, hogy a mentesek sikerulnek ugye...).

termeszetesen volt egy DR site (ami kilometerekre volt az elestol) es egy DR gep (ami parametereiben megegyezett az elessel).
az a dontes szulettett, hogy a napi rman mentest masoljuk at minden esetben a DR gepre de olyan megoldast kerestem ami minimalis karbantartast igenyel a kesobbiekben. itt jott a kepbe az "rsync" okos kis talalmany.
az alabbi linux parancs a kovetkezoket teszi:

a lokalis gepunk /bkp/dat/PRD1/rman directory-janak tartalmat rekurzivan, ssh-n keresztul, tomoritve szinkronizalja a tavoli gepen talalhato /bkp/dat/PRD1/rman konyvtarral.
a szinkronizalas pontosan azt jelenti, hogy a tavoli directory tartalmat pontosan megfelelteti a lokalis konyvtarunkeval, magyarul ha a tavoli gepen vannak olyan allomanyok melyek lokalban nincsenek, azokat a tavoli konyvtarbol letorli, hasonlokeppen ha hianyoznak allomanyok a tavoli directory-ban, azokat atmasolja lokalbol.

teljesen jol mukodik, ugyesen kell beallitani az rman retention policy-t es a tavoli gepen mindig ugyanazt a mentesi koteget fogjuk latni mint az elesen.

 
/usr/bin/rsync -avz --delete -e ssh /bkp/dat/PRD1/rman/ 10.18.112.127:/bkp/dat/PRD1/rman

igy lehet egy after logon triggerrel letiltani az exp hasznalatat azoknak, akiknek nincs dba role-uk:

create or replace trigger restrict_exp after logon on database
declare
v_module varchar2(255);
v_isdba number;
begin
select sys_context('userenv','module')  into v_module from dual;
if v_module LIKE 'exp@%' then
select count(*) into v_isdba from dba_role_privs where granted_role='DBA' and
grantee=SYS_CONTEXT('USERENV','SESSION_USER');
   if v_isdba = 0 then
          raise_application_error (-20001,'exp is allowed only for DBAs',true);
   end if;
end if;
end;
/

az oracle grid control/database control ugyes kis joszag, de vannak olyan szakik (mondjuk en is), akik szeretik a dolgokat sqlplus-bol, neadjisten toad-bol is megvizslatni.

sok problemat es fejtorest okozhat, hogy kideritsuk milyen lock-ok vannak a rendszerben, erre en a kovetkezo "kis" scriptet hasznalom. okos es beszedes:

select
nvl(S.USERNAME,'Internal') username,
        L.SID,
        nvl(S.TERMINAL,'None') terminal,
U1.NAME||'.'||substr(T1.NAME,1,20) tab,
decode(command,
0,'BACKGROUND',
1,'Create Table',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'CREATE SEQUENCE',
14,'ALTER SEQUENCE',
15,'ALTER TABLE',
16,'DROP SEQUENCE',
17,'GRANT',
18,'REVOKE',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK TABLE',
27,'NO OPERATION',
28,'RENAME',
29,'COMMENT',
30,'AUDIT',
31,'NOAUDIT',
32,'CREATE EXTERNAL DATABASE',
33,'DROP EXTERNAL DATABASE',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEGMENT',
37,'ALTER ROLLBACK SEGMENT',
38,'DROP ROLLBACK SEGMENT',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM SWITCH LOG',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
58,'ALTER TRACING',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
69,'DROP PROCEDURE',
70,'ALTER RESOURCE COST',
71,'CREATE SNAPSHOT LOG',
72,'ALTER SNAPSHOT LOG',
73,'DROP SNAPSHOT LOG',
74,'CREATE SNAPSHOT',
75,'ALTER SNAPSHOT',
76,'DROP SNAPSHOT',
79,'ALTER ROLE',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
87,'-',
88,'ALTER VIEW',
89,'-',
90,'-',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PACKAGE BODY',
98,'ALTER PACKAGE BODY',
99,'DROP PACKAGE BODY',
command||' - ???') COMMAND,
        decode(L.LMODE,1,'No Lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive','NONE') lmode,
        decode(L.REQUEST,1,'No Lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive','NONE') request,
l.id1||'-'||l.id2 Laddr,
l.type||' - '||
decode(l.type,
'BL','Buffer hash table instance lock',
'CF',' Control file schema global enqueue lock',
'CI','Cross-instance function invocation instance lock',
'CS','Control file schema global enqueue lock',
'CU','Cursor bind lock',
'DF','Data file instance lock',
'DL','Direct loader parallel index create',
'DM','Mount/startup db primary/secondary instance lock',
'DR','Distributed recovery process lock',
'DX','Distributed transaction entry lock',
'FI','SGA open-file information lock',
'FS','File set lock',
'HW','Space management operations on a specific segment lock',
'IN','Instance number lock',
'IR','Instance recovery serialization global enqueue lock',
'IS','Instance state lock',
'IV','Library cache invalidation instance lock',
'JQ','Job queue lock',
'KK','Thread kick lock',
'MB','Master buffer hash table instance lock',
'MM','Mount definition gloabal enqueue lock',
'MR','Media recovery lock',
'PF','Password file lock',
'PI','Parallel operation lock',
'PR','Process startup lock',
'PS','Parallel operation lock',
'RE','USE_ROW_ENQUEUE enforcement lock',
'RT','Redo thread global enqueue lock',
'RW','Row wait enqueue lock',
'SC','System commit number instance lock',
'SH','System commit number high water mark enqueue lock',
'SM','SMON lock',
'SN','Sequence number instance lock',
'SQ','Sequence number enqueue lock',
'SS','Sort segment lock',
'ST','Space transaction enqueue lock',
'SV','Sequence number value lock',
'TA','Generic enqueue lock',
'TD','DDL enqueue lock',
'TE','Extend-segment enqueue lock',
'TM','DML enqueue lock',
'TO','Temporary Table Object Enqueue',
'TT','Temporary table enqueue lock',
'TX','Transaction enqueue lock',
'UL','User supplied lock',
'UN','User name lock',
'US','Undo segment DDL lock',
'WL','Being-written redo log instance lock',
'WS','Write-atomic-log-switch global enqueue lock',
'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)',
                    'New block allocation enqueue lock (ID2=1)'),
'LA','Library cache lock instance lock (A=namespace)',
'LB','Library cache lock instance lock (B=namespace)',
'LC','Library cache lock instance lock (C=namespace)',
'LD','Library cache lock instance lock (D=namespace)',
'LE','Library cache lock instance lock (E=namespace)',
'LF','Library cache lock instance lock (F=namespace)',
'LG','Library cache lock instance lock (G=namespace)',
'LH','Library cache lock instance lock (H=namespace)',
'LI','Library cache lock instance lock (I=namespace)',
'LJ','Library cache lock instance lock (J=namespace)',
'LK','Library cache lock instance lock (K=namespace)',
'LL','Library cache lock instance lock (L=namespace)',
'LM','Library cache lock instance lock (M=namespace)',
'LN','Library cache lock instance lock (N=namespace)',
'LO','Library cache lock instance lock (O=namespace)',
'LP','Library cache lock instance lock (P=namespace)',
'LS','Log start/log switch enqueue lock',
'PA','Library cache pin instance lock (A=namespace)',
'PB','Library cache pin instance lock (B=namespace)',
'PC','Library cache pin instance lock (C=namespace)',
'PD','Library cache pin instance lock (D=namespace)',
'PE','Library cache pin instance lock (E=namespace)',
'PF','Library cache pin instance lock (F=namespace)',
'PG','Library cache pin instance lock (G=namespace)',
'PH','Library cache pin instance lock (H=namespace)',
'PI','Library cache pin instance lock (I=namespace)',
'PJ','Library cache pin instance lock (J=namespace)',
'PL','Library cache pin instance lock (K=namespace)',
'PK','Library cache pin instance lock (L=namespace)',
'PM','Library cache pin instance lock (M=namespace)',
'PN','Library cache pin instance lock (N=namespace)',
'PO','Library cache pin instance lock (O=namespace)',
'PP','Library cache pin instance lock (P=namespace)',
'PQ','Library cache pin instance lock (Q=namespace)',
'PR','Library cache pin instance lock (R=namespace)',
'PS','Library cache pin instance lock (S=namespace)',
'PT','Library cache pin instance lock (T=namespace)',
'PU','Library cache pin instance lock (U=namespace)',
'PV','Library cache pin instance lock (V=namespace)',
'PW','Library cache pin instance lock (W=namespace)',
'PX','Library cache pin instance lock (X=namespace)',
'PY','Library cache pin instance lock (Y=namespace)',
'PZ','Library cache pin instance lock (Z=namespace)',
'QA','Row cache instance lock (A=cache)',
'QB','Row cache instance lock (B=cache)',
'QC','Row cache instance lock (C=cache)',
'QD','Row cache instance lock (D=cache)',
'QE','Row cache instance lock (E=cache)',
'QF','Row cache instance lock (F=cache)',
'QG','Row cache instance lock (G=cache)',
'QH','Row cache instance lock (H=cache)',
'QI','Row cache instance lock (I=cache)',
'QJ','Row cache instance lock (J=cache)',
'QL','Row cache instance lock (K=cache)',
'QK','Row cache instance lock (L=cache)',
'QM','Row cache instance lock (M=cache)',
'QN','Row cache instance lock (N=cache)',
'QO','Row cache instance lock (O=cache)',
'QP','Row cache instance lock (P=cache)',
'QQ','Row cache instance lock (Q=cache)',
'QR','Row cache instance lock (R=cache)',
'QS','Row cache instance lock (S=cache)',
'QT','Row cache instance lock (T=cache)',
'QU','Row cache instance lock (U=cache)',
'QV','Row cache instance lock (V=cache)',
'QW','Row cache instance lock (W=cache)',
'QX','Row cache instance lock (X=cache)',
'QY','Row cache instance lock (Y=cache)',
'QZ','Row cache instance lock (Z=cache)','????') Lockt
from    GV$LOCK L, 
        GV$SESSION S,
        SYS.USER$ U1,
        SYS.OBJ$ T1
where   L.SID = S.SID 
and     T1.OBJ#  = decode(L.ID2,0,L.ID1,1) 
and     U1.USER# = T1.OWNER#
and     S.TYPE != 'BACKGROUND'
order by 1,2,5

az exp/imp utodja 10g-ben a datapump amely teljesen mas technologian alapul mint az elod, aki errol tobbet szeretne tudni olvasgassa el ezt az angol nyelvu ismertetot (igaz ez mar Oracle 11g alatt mutatkozik be, de a lenyeg ugyanaz).

a lenyeg hogy keszitettek hozza command line interface-t, ami nem mas mint az expdp es az impdp programok.
ezeket szepen lehet parameterezni (sok hasonlosag felfedezheto az exp/imp-pel), de mi van akkor ha nem ezt a modszert valaszjuk, hanem a beepitett pl/sql csomagokkal szeretnenk datapump-olni?

itt egy lehetseges megoldas az importra:

impdp system/manager             \
schemas=my_dba                     \
directory=db_dir                        \
dumpfile=my_dba_db12.dmp     \
logfile=my_dba_db12.log           \
table_exist_action=replace         \
remap_schema=my_dba:test     \
remap_tablespace=MY_REFERENCE:TEST_LOGSTDBY

a fenti parancs a kovetkezoket teszi:
- system-kent csatlakozik a db-hez
- a db_dir adatbazis directory-ban talalhato my_dba_db12.dmp-bol fogunk importalni
- a logfile-t a db_dir adatbazis directory-ba irjuk my_dba_db12.log neven
- a my_dba schemat fogjuk feldolgozni a dump-bol
- a my_dba schema objektumokat a test schema ala importaljuk
- ha egy, az importalando tablak kozul mar letezik a test schema alatt, akkor azt kicsereljuk az import dump-bol jovore
- az importalando objektumok kozul, ami eddig a MY_REFERENCE tablaterben volt az az import utan a TEST_LOGSTDBY-be fog kerulni

mindez igy nez ki a pl/sql package-ekkel:

set serveroutput on

DECLARE
 dph NUMBER;
BEGIN
  dph := dbms_datapump.open(operation => 'IMPORT', job_mode =>
  'SCHEMA',job_name => 'MY_IMPORT_01');

  dbms_datapump.add_file(handle => dph,filename =>
  'my_dba_db12.dmp', directory => 'DB_DIR',filetype=>1);

  dbms_datapump.add_file(handle => dph,filename =>
  'my_dba_db12.log',directory => 'DB_DIR',filetype=>3);

  dbms_datapump.set_parameter(handle => dph,name =>
  'TABLE_EXISTS_ACTION', value =>'REPLACE');
 
  DBMS_DATAPUMP.METADATA_REMAP
      (handle => dph,
      name => 'REMAP_SCHEMA',
      old_value => 'MY_DBA',
      value => 'TEST');
   
  DBMS_DATAPUMP.METADATA_REMAP
          (handle => dph,
          name => 'REMAP_TABLESPACE',
          old_value => 'MY_REFERENCE',
          value => 'TEST_LOGSTDBY');

  dbms_datapump.start_job(dph);

  dbms_datapump.detach(dph);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error:' || sqlerrm || ' on Job-ID:' || dph);
END;
/

meg is lepodtem hogy nincs meg ilyen blog, nem baj :) mostmar van.

kezdjuk egy kis "online redefinition"-nel.

"online redefinition"-nel, mint a neve is mondja, menet kozben tudunk tablakat machinalni, peldaul particionalni.

en igy csinaltam, Oracle 10.2.0.1-en, system felhasznaloval (logikai standby-on kellett a tablak strukturajat valtoztatni, ezert van benne 1-2 logikai standby specifikus lepes, ezt majd jelzem. Plusz nem default blocksize-zal rendelkezo tablaterbe tettem a particiokat):

* mielott egy particionalt tablat szeretnel krealni, ahol a particiok nem default blokkmerettel rendelkezo tablaterbe lesznek teve allitsd be a DB_nK_CACHE_SIZE parametert.

·         Keszitsd el a nem default blokkmerettel rendelkezo tablateret:

CREATE TABLESPACE DIFF_CACHE_SIZE DATAFILE 

  '/u05/TST/data/diff_cache_size_01.dbf' SIZE 50M AUTOEXTEND ON NEXT 
10M MAXSIZE 300M

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 16K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

·         Lassuk, hogy a tabla egyaltalan alkalmas-e online redefinitionra 
(kell hogy legyen rajta Primary Key, nem szabad hogy materializalt 
view/materializalt view log legyen rajta):

       EXEC Dbms_Redefinition.Can_Redef_Table('MY_DBA','ACCOUNT');

 ·     Ha az elozo futtatas nem dob vissza hibauzenetet allitsuk le a 
logikai standby SQL Apply-t:

        alter database stop logical standby apply;

 ·         keszitsuk el a particionalt tablankat (ez lesz az "interim table"):

 CREATE TABLE MY_DBA.ACCOUNT_PART

(  ACCOUNT_NO          NUMBER(10)                ,

  ACCOUNT_TYPE        VARCHAR2(3 CHAR)         ,

  NAME_ID             NUMBER(10)              ,

  TIMESTAMP           DATE,

  USERID              VARCHAR2(8 CHAR),

  RECORD_VERSION      NUMBER)

TABLESPACE DIFF_CACHE_SIZE   

PARTITION BY RANGE (TIMESTAMP) 

(PARTITION P1 VALUES LESS THAN (TO_DATE(' 2007-01-01 00:00:00', 
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),  

  PARTITION P2 VALUES LESS THAN (MAXVALUE));

·         kezdjuk a redefinition-t:

 BEGIN

  DBMS_REDEFINITION.start_redef_table(

    uname      => 'MY_DBA',        

    orig_table => 'ACCOUNT',

    int_table  => 'ACCOUNT_PART');

END;

  ·         masoljuk at a source tabla minden kapcsolodo objektumat 
  (indexek, triggerek, grant-ok, constraint-ek):

 DECLARE

num_errors PLS_INTEGER;

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('my_dba', 
'account','account_part', 1, TRUE, TRUE, TRUE, TRUE, num_errors);

END;

·         Ellenorizzuk, hogy az elozo futtatas generalt-e hibat:

select object_name, base_table_name, ddl_txt 

from DBA_REDEFINITION_ERRORS;

	· javasolt, hogy egy "sync"-et futtassunk 
	(kulonosen,ha nagy tablat redefinialtunk:) 

exec  DBMS_REDEFINITION.sync_interim_table(

uname => 'MY_DBA', 

orig_table => 'ACCOUNT',

int_table  => 'ACCOUNT_PART');

·         Fejezzuk be a redefinialasi folyamatot 
(az eredeti es az interim tablak nevei megcserelodnek 
 az adatszotarban):

exec DBMS_REDEFINITION.finish_redef_table(

uname => 'MY_DBA', 

orig_table => 'ACCOUNT',

int_table  => 'ACCOUNT_PART');

  ·         dobjuk el az interim tablat 
(ami ugye a nevcsere utan tulkepp az eredeti (nem particionalt)):

         drop table my_dba.ACCOUNT_PART

 ·         valoszinuleg akarsz majd uj, particionalt indexe(ke)t is hasznalni:

e.g.: CREATE INDEX my_dba.account_trans_date_local 
ON my_dba.account (TRANS_DATE) LOCAL tablespace PART_INDEXES;

 ·         enable row movement - ez azert szukseges, mert ha a particios kulcs 
modosul, lehet hogy particiok kozott kell a sornak maszkalni: 

alter table my_dba.account enable row movement;

·         inditsuk el a logikai standby sql apply-t:

       alter database start logical standby apply immediate;