LOG 4

Session 4
4.1Multiplexage
1)describe v$logfile;
     nom                  taille                     emplacement
     redo01.log        52429312             /oracle/app/oracle/oradata/orcl/redo01.log
     redo02.log        52429312             /oracle/app/oracle/oradata/orcl/redo02.log
     redo03.log        52429312             /oracle/app/oracle/oradata/orcl/redo03.log
2)alter database
  add logfile group 5 ('/oracle/app/oracle/oradata/orcl/mon_log.log') size 4 m;
3)alter database add logfile member '/oracle/app/oracle/oradata/orcl/mon_log2 .log' to group 5;
4)create table ma_table
( num number(5) primary key,
 job varchar2(15) not null);
5)alter database drop logfile member '/oracle/app/oracle/oradata/orcl/mon_log2.log';
6)alter database drop logfile group 5;

4.2)Surveillance
1) select table_name from dictionary where table_name like '%AUDIT%';

TABLE_NAME
------------------------------
ALL_DEF_AUDIT_OPTS
USER_OBJ_AUDIT_OPTS
DBA_OBJ_AUDIT_OPTS
DBA_STMT_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_AUDIT_TRAIL
USER_AUDIT_TRAIL
DBA_AUDIT_SESSION
USER_AUDIT_SESSION
DBA_AUDIT_STATEMENT
USER_AUDIT_STATEMENT

TABLE_NAME
------------------------------
DBA_AUDIT_OBJECT
USER_AUDIT_OBJECT
DBA_AUDIT_EXISTS
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
ALL_AUDIT_POLICIES
ALL_AUDIT_POLICY_COLUMNS
USER_AUDIT_POLICIES
USER_AUDIT_POLICY_COLUMNS
DBA_FGA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL

TABLE_NAME
------------------------------
DBA_REPAUDIT_ATTRIBUTE
ALL_REPAUDIT_ATTRIBUTE
USER_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
ALL_REPAUDIT_COLUMN
USER_REPAUDIT_COLUMN
AUDIT_ACTIONS
V$XML_AUDIT_TRAIL
GV$XML_AUDIT_TRAIL

31 rows selected.

2)ok
3)select * from all_def_audit_opts;
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA
--- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
-/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-
4) audit select table, update table by IL_U1;
select * from dba_stmt_audit_opts;

USER_NAME                      PROXY_NAME
------------------------------ ------------------------------
AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
IL_U1
SELECT TABLE                             BY SESSION BY SESSION

IL_U1
UPDATE TABLE                             BY SESSION BY SESSION
5)ok
6)pas besoin
7)audit select on ma_table whenever not successful;
create user IL_U2 identified by azerty;
audit select table, update table by IL_U1, IL_U2;
audit role whenever successful;
audit all privileges by IL_U1;
audit insert, update, delete on sys.aud$ by access;
noaudit select table by IL_U1,IL_U2;

verification: select * from dba_stmt_audit_opts;
8)pareil

4.3) Archive des journaux

http://oracle.developpez.com/guide/sauvegarde/generalites/#L4.2.a

1. SELECT name, log_mode from v$database;

=> Mode : NOARCHIVELOG

DESC v$archived_log;

3.
décommenter des lignes dans le fichier de conf
dans SQL plus :
shutdown abort;
startup mount;
alter databse archivelog;