Zaskok KIV/ADM ZS 2019, Milan Michajlov
Omlouvam se za text s chybami, bez diakritiky, neprelozene EN copy/pasty a anglicismy.
CSS, MD TRANSLATE, ST3 MLP dillinger.io

KIV/ADM 2019 - Cviceni 10

Nejake zdroje

Praxe s auditem, flashbackem a Data pump

Priprava - potrebujete:

Rychle opakovani auditu

Na tohle pozor!!!

Toto je auditni policy, ktera ve 22 vecer vyrobi 0.75 Mio zaznamu - NEPOUSTET!!!

CREATE AUDIT POLICY SYS_ALL_ACTIONS ACTIONS ALL; AUDIT POLICY SYS_ALL_ACTIONS BY SYS;

Pokud byste nevideli auditni data tam kde by mela byt, je mozne popohnat DB aby je ulozila z SGA bufferu volanim MGMT fce:

exec SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

Ovsem od 12.2 je depreceated, takze by to asi nemelo byt nutne.

Toto cele si nakopirujte do DEVELOPERU nad PDB1 a odpalujte jednotlive prikazy/selecty

--> PDB1 !!! 

-- Kolik toho ted je v auditu v PDB1?
select count (*) from unified_audit_trail;
--> 2800 cca...

-- Muzeme se podivat...
select * from unified_audit_trail;

-- Prehled (105 v 19c) sloupcu unified auditu s komentari
select column_name, 
       comments
  from dba_col_comments
 where owner = 'AUDSYS'
   and table_name = 'UNIFIED_AUDIT_TRAIL'
 order by column_name;

-- Vycistime audit... - v PDB1 uplne...

-- Jak je zadany L.A.T.?
SELECT * FROM dba_audit_mgmt_last_arch_ts; 

-- Zmenime ho na minus hodinu...
BEGIN
    DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
         audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
         last_archive_time => (sysdate - 1/24)
   );
END;

-- Kontrola...
SELECT * FROM dba_audit_mgmt_last_arch_ts; 

-- Zmena podle L.A.T.
BEGIN
    DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
         audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
         USE_LAST_ARCH_TIMESTAMP => TRUE
    );
END;

-- Melo by jich byt mene...
select count (*) from unified_audit_trail;
--> 5 cca...

-- Totalni vymazani - bez L.A.T.
BEGIN
    DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
         audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
         USE_LAST_ARCH_TIMESTAMP => FALSE
    );
END;

-- ukaze vsechny policies, ktere jsou ted v DB
select distinct policy_name
  from AUDIT_UNIFIED_POLICIES
order by policy_name
;

-- ukaze pouze aktivni police
select * from AUDIT_UNIFIED_ENABLED_POLICIES;

-- Prozkoumame podminky ORA_SECURECONFIG
 select POLICY_NAME,
       AUDIT_OPTION
  from AUDIT_UNIFIED_POLICIES
 where policy_name = 'ORA_SECURECONFIG'
order by 2;

select *
  from AUDIT_UNIFIED_POLICIES
 where policy_name = 'ORA_SECURECONFIG'
order by AUDIT_OPTION;

-- Prozkoumame podminky ORA_LOGON_FAILURES
 select POLICY_NAME,
       AUDIT_OPTION
  from AUDIT_UNIFIED_POLICIES
 where policy_name = 'ORA_LOGON_FAILURES'
order by 2;

-- Ted provedeme neco, co se zaaudituje

-- Zlikvidujeme HR a udelame ho znovu...
drop user hr cascade;

--> Konzole, vyrobit HR/HR...
-- SQL> @?/demo/schema/human_resources/hr_main.sql
-- hr / ts_hr / temp / /home/oracle

-- Co se zalogovalo?
select dbusername, 
       system_privilege_used, 
       action_name, 
       object_schema, 
       object_name,
       sql_text
  from unified_audit_trail
 where unified_audit_policies = 'ORA_SECURECONFIG'
 order by event_timestamp
;
--> Podivejte se do polozek SQL_TEXT

-- Najdete jeden rozdil! (EXECUTE DBMS_AUDIT_MGMT - modifikace auditu se loguji vzdy!)
select dbusername,
       event_timestamp,
       unified_audit_policies,
       action_name,
       system_privilege_used,
       object_schema,
       object_name,
       sql_text 
  from unified_audit_trail
 order by event_timestamp desc;

-- Zkusime ORA_LOGON_FAILURES
select event_timestamp, 
       audit_type, 
       dbusername, 
       action_name, 
       return_code
  from unified_audit_trail uat
 where unified_audit_policies = 'ORA_LOGON_FAILURES'
   -- and uat.event_timestamp > systimestamp - ((1/24/60) *5) -- happened in the last 5 minutes
 order by event_timestamp desc;
--> NIC...

-- prihlaste se jako HR ale zadejte spatne heslo!

select event_timestamp, 
       audit_type, 
       dbusername, 
       action_name, 
       return_code
  from unified_audit_trail uat
 where unified_audit_policies = 'ORA_LOGON_FAILURES'
   -- and uat.event_timestamp > systimestamp - ((1/24/60) *5) -- happened in the last 5 minutes
 order by event_timestamp desc;
--> NECO
-- RETURN_CODE je cislo chyby...

Ted se podivame trochu na RMAN... a jeho audit

You want to audit Recovery Manager backup, restore and recover operations.

You do not have to create any audit policy for RMAN operations.

RMAN is audited by default.

 select DBUSERNAME, 
        RMAN_OPERATION
   from UNIFIED_AUDIT_TRAIL
  where RMAN_OPERATION is not null;
--> NIC... Protoze jsme jeste s RMANem nic nedelali
--> Ale az budeme, bude to stejne videt v audit trailu v CDB$ROOT!

select sysdate from dual;
--> Poznamenat si cas!?

Vsuvka RMAN - nastaveni a prvni backup

Provedeme kkontrolu nastaveni RMANa a udelame si nas prvni backup...

Nejdriv ale pruzkum obsahu ASM pomoci ASMCMD

Bude tam cca toto:

DATA
    RES2_IAD1B2
        8E03795456181E0CE0536B09F40A961A
            DATAFILE
                DATAFILE   COARSE NOV 26 15:00:00  SYSAUX.266.1025361647
                DATAFILE   COARSE NOV 26 15:00:00  SYSTEM.265.1025361647
                DATAFILE   COARSE NOV 26 15:00:00  UNDOTBS1.267.1025361647
        9841E01C7EF2D5C8E0532500000A7EAD
            TEMPFILE
                TEMPFILE   COARSE NOV 26 15:00:00  TEMP.268.1025361689
        98420A02E0BF7B00E0532500000A600F
            DATAFILE
                DATAFILE   COARSE NOV 26 15:00:00  SYSAUX.272.1025362393
                DATAFILE   COARSE NOV 26 22:00:00  SYSTEM.271.1025362393
                DATAFILE   COARSE NOV 26 15:00:00  UNDOTBS1.270.1025362393
                DATAFILE   COARSE NOV 26 15:00:00  USERS.275.1025362923
            TEMPFILE
                TEMPFILE   COARSE NOV 26 15:00:00  TEMP.273.1025362403            
        DATAFILE
            DATAFILE   COARSE NOV 27 15:00:00  SYSAUX.262.1025361485
            DATAFILE   COARSE NOV 26 22:00:00  SYSTEM.261.1025361441
            DATAFILE   COARSE NOV 26 15:00:00  UNDOTBS1.263.1025361501
            DATAFILE   COARSE NOV 26 15:00:00  USERS.274.1025362923
        PARAMETERFILE
            PARAMETERFILE   COARSE NOV 26 22:00:00  spfile.269.1025362089        
        TEMPFILE
            TEMPFILE   COARSE NOV 26 15:00:00  TEMP.264.1025361609

RECO
    RES2_IAD1B2
        ARCHIVELOG
            2019_11_26
                ARCHIVELOG   COARSE NOV 26 14:00:00  thread_1_seq_1.260.1025362791
        AUTOBACKUP
            2019_11_26
                AUTOBACKUP   COARSE NOV 26 15:00:00  s_1025363461.262.1025363463
        CONTROLFILE
            CONTROLFILE   FINE NOV 26 15:00:00  Backup.261.1025363461
            CONTROLFILE   FINE NOV 26 15:00:00  Current.256.1025361571
            CONTROLFILE   FINE NOV 26 15:00:00  snapcf_res2_iad1b2.f => +RECO/RES2_IAD1B2/CONTROLFILE/Backup.261.1025363461
        ONLINELOG
            ONLINELOG   COARSE NOV 26 15:00:00  group_1.257.1025361575
            ONLINELOG   COARSE NOV 26 15:00:00  group_2.258.1025361575
            ONLINELOG   COARSE NOV 26 15:00:00  group_3.259.1025361575

RMAN je CLI utilita, takze poustet ze shellu

RMAN target /

SHOW ALL;

RMAN vypise svoji aktualni konfiguraci. Tu si schovava do controlfile DB, pokud jej nepouzivate v rezimu CATALOG (to ma pak vlastni DB Oracle jen na svoje provozni data - nastaveni)

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name RES2_IAD1B2 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2 G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+RECO/RES2_IAD1B2/controlfile/snapcf_res2_iad1b2.f';

Zmente nastaveni BACKUP OPTIMIZATION na ON takto:

CONFIGURE BACKUP OPTIMIZATION ON;

Peclive odpalte tento prikaz (DB nechte bezet, nevypinejte):

backup database plus archivelog delete input;

Toto provede zalohu DB za chodu vcetne vsech redologu, ktere vznikly i behem procesu zalohy. Provede logswitch na zacatku i konci (tim dostane obsah online logu do archivnich redo logu - prepne je) a vsechny zazalohuje. Co by se stalo, pokud by tam nebylo PLUS ARCHIVELOG - viz zde.

Pozorujte vypis...

Ted je mozno prozkoumat opet ASM pomoci ASMCMD. V RECO by melo neco pribyt:

RECO
    RES2_IAD1B2
        9841E01C7EF2D5C8E0532500000A7EAD
            BACKUPSET
                2019_11_27
                    BACKUPSET COARSE NOV 27 17:00:00  nnndf0_TAG20191127T170901_0.265.1025456965
        98420A02E0BF7B00E0532500000A600F
            BACKUPSET
                2019_11_27
                    BACKUPSET COARSE NOV 27 17:00:00  nnndf0_TAG20191127T170901_0.260.1025456957
        AUTOBACKUP
            2019_11_26
                AUTOBACKUP COARSE NOV 26 15:00:00  s_1025363461.262.1025363463
            2019_11_27
                AUTOBACKUP COARSE NOV 27 17:00:00  s_1025456973.266.1025456975                
        BACKUPSET
            2019_11_27
                BACKUPSET COARSE NOV 27 17:00:00  annnf0_TAG20191127T170844_0.264.1025456925
                BACKUPSET COARSE NOV 27 17:00:00  annnf0_TAG20191127T170931_0.267.1025456973
                BACKUPSET COARSE NOV 27 17:00:00  nnndf0_TAG20191127T170901_0.263.1025456941
        CONTROLFILE
            CONTROLFILE FINE   NOV 26 15:00:00  Backup.261.1025363461
            CONTROLFILE FINE   NOV 26 15:00:00  Current.256.1025361571
            CONTROLFILE FINE   NOV 26 15:00:00  snapcf_res2_iad1b2.f => +RECO/RES2_IAD1B2/CONTROLFILE/Backup.261.1025363461
        ONLINELOG
            ONLINELOG COARSE NOV 26 15:00:00  group_1.257.1025361575
            ONLINELOG COARSE NOV 26 15:00:00  group_2.258.1025361575
            ONLINELOG COARSE NOV 26 15:00:00  group_3.259.1025361575

Jednotliva nastaveni - probrat pouze pokud bude cas, jinak viz odkazy:

Audit - cviceni

Budeme pokracovat v hrani s auditem

--> Nezapomente, RMAN se neaudituje do trailu PDB1 ale CDB!
-- Pustte toto v CDB:  
 select DBUSERNAME, 
        RMAN_OPERATION,
        RMAN_SESSION_RECID,
        RMAN_SESSION_STAMP,
        RMAN_OBJECT_TYPE,
        RMAN_DEVICE_TYPE
   from UNIFIED_AUDIT_TRAIL
  where RMAN_OPERATION is not null;
-- Melo by to tam byt...

-- Zpet do PDB...  

-- Rovnou si vyrobime auditni politiku pro smirovani datapump (budem provadet expdp / impdp)
-- A budeme si hrat v PDB1...
create audit policy SMIRUJ_DATA_PUMP actions component=datapump all; --( import / export / all)

-- Zapnout (pro vsechny uzivatele, jinak by se do tohoto prikazu pridal vycet pro koho)
audit policy SMIRUJ_DATA_PUMP;

-- Kontrola
select * from AUDIT_UNIFIED_ENABLED_POLICIES;

DataPump - cviceni - EXPDP/IMPDP

Vyrobit adresar a nagrantovat prava HR 

OS oracle>
mkdir /home/oracle/ora_impexp

Vyrobit DIRECTORY v oracle v PDB1

SQLPlus SYS@PDB1>
CREATE OR REPLACE DIRECTORY expimp AS '/home/oracle/ora_impexp';
GRANT READ, WRITE ON DIRECTORY expimp TO hr;

Vyexportovat (jako HR) tabulku HR.employees a cele schema HR

expd/impdp jsou CLI utility - prihlasujete se do nich... (jako hr/hr + kam dp PDB1) Tj: pokud nemate PDB1_direct zkratku, musite jit prez dlouhou SERVICE_NAME (-> listener services)

U exportu TABLES nezapomenout uvest CONTENT=ALL! Jinak vyexportuje jenom DDL tabulky (tabulek).

OS oracle>
expdp hr/hr@PDB1_direct tables=EMPLOYEES CONTENT=ALL directory=expimp dumpfile=hr_table_EMPLOYEES.dmp logfile=hr_table_EMPLOYEES_exp.log

expdp hr/hr@PDB1_direct schemas=HR directory=expimp dumpfile=hr_schema.dmp logfile=hr_schema_exp.log

Vytvorit nejake potrebne objekty a uzivatele v DB na pokusy - tablespace a uzivatele

SQL SYS@PDB1> 
create tablespace ts_kivadm DATAFILE 
  SIZE 100M AUTOEXTEND ON NEXT 100M 
  MAXSIZE UNLIMITED;

/*
Vytvorit uzivatele kivadm/kivadm a flash/flash
- default ts bude ts_kivadm
- nagrantovat prava na adresar expimp
*/

-- drop user kivadm cascade;
CREATE USER kivadm IDENTIFIED BY kivadm
  DEFAULT TABLESPACE ts_kivadm
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  QUOTA UNLIMITED ON ts_kivadm; 
GRANT CREATE SESSION TO kivadm;
-- a nejaka prava na vyrobu objektu... existuje role RESOURCES (deprecated...)
GRANT RESOURCE to kivadm; -- obsahuje tabulky, indexy... atd - viz google
GRANT READ, WRITE ON DIRECTORY expimp TO kivadm;

-- drop user flash cascade;
CREATE USER flash IDENTIFIED BY flash
  DEFAULT TABLESPACE ts_kivadm
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  QUOTA UNLIMITED ON ts_kivadm; 
GRANT CREATE SESSION TO flash;
-- a nejaka prava na vyrobu objektu... existuje role RESOURCES (deprecated...)
GRANT RESOURCE to flash; -- obsahuje tabulky, indexy... atd - viz google
GRANT CREATE VIEW to flash; -- toto potrebuje... v RESOURCE to neni...
GRANT READ, WRITE ON DIRECTORY expimp TO flash;

CIL: Jako kivadm importovat vyexportovanou tabulku HR ale:

Poznamka: Zajimave je, ze pri importu samotne tabulky nemusite udavat CONTENT=ALL... pokud nejaka data v exportu jsou, defaultne je natahne.

Realizace:

Pokus o primy import - v nejjednodussi mozne forme. Jako kivadm/kivadm!

OS oracle>
impdp kivadm/kivadm@PDB1_direct directory=expimp dumpfile=hr_table_EMPLOYEES.dmp logfile=hr_table_EMPLOYEES_imp.log TABLES='HR.EMPLOYEES'

ORA-31655: no data or metadata objects selected for job
ORA-39154: Objects from foreign schemas have been removed from import

Jasne... nechce importovat tabulku vyexportovanou z ciziho schematu. Udelame si takovy pokus: Z minula vime (predvadel jsem), ze pri importu samotne tabulky HR.EMPLOYEES stejne vybouchnou triggery, constrainty a indexy - i do vlastniho schematu. Takze se musi excludovat. Pro zjednoduseni vysledku je tedy excludujeme. Ale pouzijeme take parametr sqlfile, kterym udame nazev souboru, do ktereho impdp vyrobi presne serii SQL prikazu (DDL), kterou hodla provadet - ale samotny import neprovede. Je to presne pro takove situace, kdy musite zjistit, co je treba pri importu prenastavit, nebo si chcete neco z importu rucne vybrat a odpalit (treba).

Ale tenhle pseudo-import "do sql souboru" udelame jako hr/hr

OS oracle>
impdp hr/hr@PDB1_direct directory=expimp dumpfile=hr_table_EMPLOYEES.dmp logfile=hr_table_EMPLOYEES_imp.log TABLES='HR.EMPLOYEES' EXCLUDE=CONSTRAINT EXCLUDE=INDEX EXCLUDE=TRIGGER sqlfile=hr_table_EMPLOYEES_imp_ddl.sql

Podivejte se do souboru hr_table_EMPLOYEES_imp_ddl.sql, zamerte se na retezce CONNECT (nahore) HR a TS_HR

OS oracle>
cat hr_table_EMPLOYEES_imp_ddl.sql

Ted to zkusime naimportovat jako kivadm/kivadm - ale taky do souboru. Ale aby vubec byl ochoten neco natahnout, musime impdp presvedcit, ze chceme tu tabulku ze schematu HR do schematu KIVADM! --> pridame REMAP_SCHEMA

OS oracle>
impdp kivadm/kivadm@PDB1_direct directory=expimp dumpfile=hr_table_EMPLOYEES.dmp logfile=hr_table_EMPLOYEES_imp.log TABLES='HR.EMPLOYEES' REMAP_SCHEMA=hr:kivadm EXCLUDE=CONSTRAINT EXCLUDE=INDEX EXCLUDE=TRIGGER sqlfile=kivadm_table_EMPLOYEES_imp_ddl.sql

Podivejte se do souboru kivadm_table_EMPLOYEES_imp_ddl.sql, zamerte se na retezce CONNECT (nahore) HR a TS_HR

OS oracle>
cat kivadm_table_EMPLOYEES_imp_ddl.sql

Vidite rozdily? Jak to bude s tim TS_HR? Co se stane...

UKAZAT ZVYRAZNENY DIFF u mne...

Zkusime to natahnout jako kivadm naostro (s remapem i excludy ale bez sqlfile)

OS oracle>
impdp kivadm/kivadm@PDB1_direct directory=expimp dumpfile=hr_table_EMPLOYEES.dmp logfile=hr_table_EMPLOYEES_imp.log TABLES='HR.EMPLOYEES' REMAP_SCHEMA=hr:kivadm EXCLUDE=CONSTRAINT EXCLUDE=INDEX EXCLUDE=TRIGGER

Co se stalo? Musime udelat jeste jeden remap... ze? A kdyz jsme v te praci, tak si jeste navic remapneme i tu tabulku. To jsou 2 dalsi parametry, to uz je fakt dlouhe. Jak se toto dela v praxi: parametry se nacpou do parametrickeho souboru a ten se preda impdp. Pridame jeste jeden remap, ktery nam natahne tabulku znova ale pojmenuje ji jako EMPLOYEES2. Ale pomoci param souboru.

OS oracle>
rm import_tab_EMPLOYEES_as_EMPLOYEES2.par
touch import_tab_EMPLOYEES_as_EMPLOYEES2.par
echo "directory=expimp" >> import_tab_EMPLOYEES_as_EMPLOYEES2.par
echo "dumpfile=hr_table_EMPLOYEES.dmp" >> import_tab_EMPLOYEES_as_EMPLOYEES2.par
echo "logfile=kivadm_table_EMPLOYEES_imp.log" >> import_tab_EMPLOYEES_as_EMPLOYEES2.par
echo "TABLES='HR.EMPLOYEES'" >> import_tab_EMPLOYEES_as_EMPLOYEES2.par
echo "REMAP_TABLESPACE=ts_hr:ts_kivadm" >> import_tab_EMPLOYEES_as_EMPLOYEES2.par
echo "EXCLUDE=CONSTRAINT" >> import_tab_EMPLOYEES_as_EMPLOYEES2.par
echo "EXCLUDE=INDEX" >> import_tab_EMPLOYEES_as_EMPLOYEES2.par
echo "EXCLUDE=TRIGGER" >> import_tab_EMPLOYEES_as_EMPLOYEES2.par
echo "REMAP_SCHEMA=hr:kivadm" >> import_tab_EMPLOYEES_as_EMPLOYEES2.par
echo "REMAP_TABLE=employees:employees2" >> import_tab_EMPLOYEES_as_EMPLOYEES2.par

A pustit...

OS oracle>
impdp kivadm/kivadm@PDB1_direct parfile=import_tab_EMPLOYEES_as_EMPLOYEES2.par

A ted natahneme cele schema HR z puvodniho exportu schematu do pripraveneho (prazdneho) schematu flash, ne jen jedinou tabulku:

OS oracle>
rm import_schema_HR_as_FLASH.par
touch import_schema_HR_as_FLASH.par
echo "directory=expimp" >> import_schema_HR_as_FLASH.par
echo "dumpfile=hr_schema.dmp" >> import_schema_HR_as_FLASH.par
echo "logfile=flash_schema_imp.log" >> import_schema_HR_as_FLASH.par
echo "SCHEMAS='HR'" >> import_schema_HR_as_FLASH.par
echo "REMAP_TABLESPACE=ts_hr:ts_kivadm" >> import_schema_HR_as_FLASH.par
echo "REMAP_SCHEMA=hr:flash" >> import_schema_HR_as_FLASH.par

A pustit...

OS oracle>
impdp flash/flash@PDB1_direct parfile=import_schema_HR_as_FLASH.par

--> Je to tam... se vsim vsudy, ale misto v HR je to ve schematu FLASH

A ted se podivame, jestlipak se nam prace datove pumpy spravne zaauditovala. Na datovou pumpu nejsou zadne specialni sloupce v unified audit trailu, musime si vystacit s temi standardnimi

-- Co se zalogovalo?
-- A kde? Kam jsme se pri importech / exportech prihlasovali?
-- Tam se musime divat potom... to trailu te databaze (pluggable)
select dbusername, 
       system_privilege_used, 
       action_name, 
       object_schema, 
       object_name,
       sql_text
  from unified_audit_trail
 where unified_audit_policies like 'SMIRUJ_DATA_PUMP'
 order by event_timestamp

FlashBack - cviceni

Pokusy budu sice delat jako SYS ale ve schematu KIVADM - z lenosti grantovat prava na v$database kvuli scn ;)

Toto si cele natahnete tedy do DEVELOPERU do SYS@PDB1

Tam kde ja mam poznamenany cas nebo SCN si ho poznamenejte a pak dosazujte podle akt. stavu / doby kdy si hrajete

select CURRENT_SCN from v$database;
--> zaznamenat si nekam: 2459083

SELECT TO_CHAR(SYSDATE, 'DD.MM.YYYY HH:MI:SS') as start_cas FROM dual;
--> zaznamenat si nekam: 28.11.2019 12:51:02

-- pruzkum
select *
  from KIVADM.employees2
 where 1=1;

-- pruzkum
select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL 
  from KIVADM.employees2
 where employee_id in (193, 207);

-- Insert...
Insert into KIVADM.EMPLOYEES2 (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID) Values
   (207, 'Quentin', 'Tarantino', 'Q_TAR', '516.124.8191', sysdate, 'AC_ACCOUNT', 12300, 205, 110);
COMMIT;

-- 1 update
update KIVADM.employees2 
   set last_name = 'Vohnoutová',
       email='VHNOUT3'
 where employee_id = 193;  
COMMIT;

-- Kontrola
select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL 
  from KIVADM.employees2
 where employee_id in (193, 207);

select CURRENT_SCN from v$database;
--> zaznamenat si nekam: 2459296

-- VERZE
select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, 
       versions_startscn, 
       versions_starttime, 
       versions_endscn, 
       versions_endtime,
       versions_xid, 
       versions_operation
  from KIVADM.employees2
       VERSIONS BETWEEN SCN 2459083 AND 2459296
  where employee_id in (193, 207);

-- Simulujeme zakernika...
update KIVADM.employees2 
   set email='SHIT!_HACKED!'
 where employee_id > 0;  
COMMIT;

SELECT TO_CHAR(SYSDATE, 'DD.MM.YYYY HH:MI:SS') as start_cas FROM dual;
--> zaznamenat si nekam: 28.11.2019 12:56:02

-- VERZE
select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, 
       versions_startscn, 
       versions_starttime, 
       versions_endscn, 
       versions_endtime,
       versions_xid, 
       versions_operation
  from KIVADM.employees2
       VERSIONS BETWEEN TIMESTAMP 
                TO_TIMESTAMP('28.11.2019 12:51:02', 'DD.MM.YYYY HH:MI:SS')
                AND
                TO_TIMESTAMP('28.11.2019 12:56:02', 'DD.MM.YYYY HH:MI:SS')       
 WHERE 1=1;  

-- Udelam si kopii cele tabulky:
create table KIVADM.employees2_pre_hacked as select * from KIVADM.employees2
    as of timestamp TO_TIMESTAMP('28.11.2019 12:51:02', 'DD.MM.YYYY HH:MI:SS');

-- Udelam si kopii cele tabulky - ale trochu prepalim datum...
create table KIVADM.employees2_pre_hacked_II as select * from KIVADM.employees2
    as of timestamp TO_TIMESTAMP('28.10.2019 12:51:02', 'DD.MM.YYYY HH:MI:SS');

-- Zachrana tabulky - do puv stavu
FLASHBACK TABLE KIVADM.employees2 TO SCN 2459083;
-- WTF?

-- Nutno nastavit...
alter table KIVADM.employees2 enable row movement;

-- Znova --> OK
FLASHBACK TABLE KIVADM.employees2 TO SCN 2459083;

select * from KIVADM.employees2;

-- Sakra, nekdo to smazal!
drop table KIVADM.employees2; 

select * from KIVADM.employees2;

-- Nepanikarit... vratit zpet...
FLASHBACK TABLE KIVADM.employees2 TO before drop;

select * from KIVADM.employees2;

A jeste jednou RMAN na zaver...

Point in time recovery - Z VYPNUTE DB (pokud stihnem...)

Ale musi se udelat pomoci skriptu...

OS oracle>
rman target /
RUN {
  SET UNTIL TIME "TO_DATE('28.11.2019 XX:XX:00', 'DD.MM.YYYY hh24:mi:ss')";
  startup nomount;
  restore controlfile from autobackup;
  alter database mount;
  restore database;
  recover database;
  shutdown immediate;
  startup mount;
  # udela novou inkarnaci, po nekompl. point in time recovery se to MUSI UDELAT aby se utla alternativni budoucnost...   
  alter database open resetlogs;
}

Automaticke mazani TRC/TRM logu procesu GRC1/GRC2 v trace CRONEM

Kdo si poctive provedl vsechny kroky z minulych cviceni, ma pravdepodobne DB v nastrelenem stavu a produkuje mu cca kazdou sekundu 1 TRM a 1 TRC log soubor od procesu GRC1 nebo GRC2. Nebyl zatim cas a vule to zkoumat nebo resit s helpdeskem Oracle. Workaround je tento: Nastavit CRON aby kazdou chvili (treba minutu) smazal prislusne soubory. Kdo neumi s CRONem, alespon se to nauci. Kdo umi, necht omluvi ten popis a udela si to po svem.

Jako uzivatel oracle odpalite nasledujici prikazy z prikazove radky:

V prvnim kroku si vytvorite v home adresari shellovsky skript clean_trace.sh. Aby ho shell dovedl interpretovat, musi byt na prvnim radku ve specialnim komentari uvedena cesta k interpreteru a musi byt spustitelny (chmod na konci).

Echo vam provede editacni ukony se soubory, takze nebudete muset skrabat nic ve vi. Proto jsou ale samotne prikazy omotane uvozovkami a aby echo odradkovalo, vola se s parametrem -e.

Pokud byste se pokouseli smazat soubory pomoci prikazu rm, tak pokud jich bude opravdu hodne, muze rozvinuti masky nazvu souboru do seznamu souboru udelat tak dlouhy argument, ze to padne na argument list too long. Takze se musi pouzit find s argumentem -delete.

Nezapomente si spravne prepsat adresare v ceste k trace diru, urcite nebudete mit: dbx319c_iad1wf/DBX319C

OS oracle>

cd ~
echo -e '#!/bin/sh\n' >> clean_trace.sh
echo "find /u01/app/oracle/diag/rdbms/dbx319c_iad1wf/DBX319C/trace -maxdepth 1 -name \"*gcr*.tr*\" -delete" >> clean_trace.sh
chmod a+x ~/clean_trace.sh

Opakuji: Nezapomente si spravne prepsat adresare v ceste k trace diru, urcite nebudete mit: dbx319c_iad1wf/DBX319C

Soubor by mel tedy uvnitr vypadat takto:

#!/bin/sh

find /u01/app/oracle/diag/rdbms/dbx319c_iad1wf/DBX319C/trace -maxdepth 1 -name "gcr.tr*" -delete

Pokud ted ten skript /home/oracle/clean_trace.sh spustite, odmaze vam trctrm bordel z trace diru. Ale Vy ho nechcete poustet jen rucne, chcete si nastavit CRON aby to kazdou minutu udelal za vas.

V druhem kroku si zalozite soubor crontabu s aktualnim nastavenim cronu (soubor se bude jmenuovat mycron) a pridate do nej pravidlo, ktere pusti kazdou minutu vyse uvedeny clean_trace.sh. Jelikoz pravdepodobne vas uzivatel oracle zadny crontab zatim mit nebude, ten soubor mycron bude na zacatku prazdny. Po pridani pravidla ho nainstalujete a pak smazete. V podstate berlicka se souborem je tu opet kvuli tomu, usetrit vas spouprace s oblibenym vi. Normalne by se cron nastavil editaci crontabu takto:

crontab -l

Ale vy muzete misto toho odpalit nasledujici prikazy z prikazove radky a vysledek by mel byt stejny.

crontab -l > mycron
echo "*/1 * * * * /home/oracle/clean_trace.sh" >> mycron
crontab mycron
rm mycron

Viz dalsi popis zde...

Kontrola: prikaz crontab -l (l jako LIST) by mel ted vypsat toto a kazdou minutu uklidit svincik

crontab -l
*/1 * * * * /home/oracle/clean_trace.sh