Data … as usual

All things about data by Laurent Leturgez

Category Archives: Oracle

Managing database audit Trail in Oracle 11gR2

Due to American Security laws, Oracle has changed its audit management rules in Oracle 11g.

Now, AUDIT_TRAIL is configured bydefault to ‘DB’. Many operations are now audited by default and stored in the AUD$ table (which is stored by default in the SYSTEM tablespace).

Audited operations are available in the DBA_PRIV_AUDIT_OPTS view, and if you have a look in it, you will see that “CREATE SESSION” operations are audited.

So, if you create your database with ‘dbca’, if you update your database with ‘dbua’, you leave the default configuration, and your SYSTEM Tablespace datafile is in autoextend mode … you will probably have a huge system datafile soon.

To manage the audit trail, you have different solutions:

– The first one is to disable audit trail: set AUDIT_TRAIL to NONE into your spfile file, shutdown and restart your database.

– The second one is to move your AUD$ to another tablespace :

SQL> select table_name,tablespace_name from dba_tables where table_name='AUD$';

TABLE_NAME      TABLESPACE_NAME
--------------- ---------------
AUD$            SYSTEM
SQL> exec DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'AUD_TS');

PL/SQL procedure successfully completed.
SQL> select table_name,tablespace_name from dba_tables where table_name='AUD$';

TABLE_NAME      TABLESPACE_NAME
--------------- ---------------
AUD$            AUD_TS

– The third one is to manage the properties of the audit trail with the DBMS_AUDIT_MGMT PL/SQL package

If you want to change it, you have to use DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure to configure properties of the audit trail:

SQL> begin
  2  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3                                           audit_trail_property=>DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,
  4                                           audit_trail_property_value=>1000);
  5  end;
  6  /

Then, you have to initialize the cleanup, and to define the oldest record to leave in the table (Here 30 days):

SQL> begin
  2  DBMS_AUDIT_MGMT.INIT_CLEANUP(audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3                               default_cleanup_interval=>720);
  4
  5  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  6                                             last_archive_time => SYSTIMESTAMP-30);
  7  end;
  8  /

Next, we create a job. This job will be executed every 168 hours (7 days).

SQL> BEGIN
  2  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3                                   audit_trail_purge_interval => 168,
  4                                   audit_trail_purge_name=> 'PURGE_AUD_JOB',
  5                                   use_last_arch_timestamp => TRUE);
  6  END;
  7  /
Note : if USE_LAST_ARCH_TIMESTAMP is set to TRUE, the job will purge only the oldest entries that match to the corresponding retention parameter. You can have a look to the DBA_SCHEDULER_JOBS to have more details about this new job.

Here I gave you an example to purge the DB audit structure (AUD$ and FGA_AUD$ tables), if you have a look to the Oracle documentation, you can use the same procedure to purge OS Audit File, XML File.

What’s your Oracle cluster name ?

If, like me, you have installed an oracle cluster few months ago, and now you have to reinstall your database control … you must know your cluster name.

To retrieve it, you can use the “cemutlo” command.

For example, in a Windows Grid Infrastructure installation :

D:\>d:\grid\11.2.0\BIN\cemutlo.exe -n
RAC_PRD

Control bug fixes activation in Oracle

When you upgrade oracle database version, you can be faced to a problem of CBO that changes its behaviour.

To do this, Oracle have introduced an undocumented parameter : “_fix_control” (Since 10.2.0.2). With this parameter, you can unable or disable a bug fixe to see, for example, if the CBO behaviour is controlled by a bug fix or not.

Off course, this kind of tests have to be implemented for testing purposes only !

To see what bug fix is implemented on your database, oracle have implemented two views V$SYSTEM_FIX_CONTROL and V$SESSION_FIX_CONTROL.

In these views, you will find information about the bug number, a short description, and the value of the parameter optimizer_features_enabled in which the bug fix is activated.

SQL> select bugno,value,description,optimizer_feature_enable,is_default from v$system_fix_control;

BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE IS_DEFAULT
---------- ---------- ---------------------------------------------------------------- ------------------------- ----------
3834770 1 Lift restriction on unnest subquery with a view 8.0.0 1
3746511 1 do not combine predicates from LNNVL 8.0.0 1
4519016 1 Pick view card from view qb instead of parent qb 9.2.0 1
3118776 1 Check for obj# for named view estimated card 9.2.0.8 1
4488689 1 ignore IS NOT NULL predicate as an index filter 10.2.0.2 1
2194204 0 disable push predicate driven by func. index into partition view 1
2660592 1 do not trigger bitmap plans if no protential domain index driver 8.1.7 1
2320291 1 push into table with RLS 9.2.0 1
2324795 1 add(remove) cluster index for push view 8.1.7 1
4308414 1 outer query must have more than one table unless lateral view 10.1.0.5 1
3499674 0 enable tiny index improvements: consider small indexes as cachhe 1
4569940 1 Use index heuristic for join pred being pushed 10.1.0.5 1
4631959 1 Refine criteria for additional phase in JPPD 10.2.0.2 1
4519340 1 Generate distinct view in SU if candidate for JPPD 10.2.0.2 1
4550003 1 do not consider no sel predicates in join selectivity sanity 10.1.0 1

.../...

In this example, we can see that the bug fix #4488689 is activated in our database (because of the value parameter set to 1).

We can see too that some og the buf fixes are not activated by default (ex: 2194204)

You can have a session view by querying the V$SESSION_FIX_CONTROL.

Now, if you want to test to desactivate a bug fix, you can do it by using the _fix_control undocumented parameter.

To disable a bug fix, you have to specify the bug number completed by ON or OFF.

For example:

-- To deactivate a single bug fix:
SQL> alter session set "_fix_control"='4488689:OFF';
-- To deactivate many bug fixes:
SQL> alter session set "_fix_control"='4488689:OFF','4631959:OFF','4519340:OFF';
-- To activate it, you have to use ON instead of OFF
SQL> alter session set "_fix_control"='4488689:ON';

If we control the V$SESSION_FIX_CONTROL view on these specific bug fixes, we can view that they are desactivated (value column equals 0).

SQL> select SESSION_ID,BUGNO,VALUE,OPTIMIZER_FEATURE_ENABLE,IS_DEFAULT
 2 from V$SESSION_FIX_CONTROL
 3 where session_id=sys_context('USERENV','SID') and bugno in (4488689,4631959,4519340);
 SESSION_ID BUGNO VALUE OPTIMIZER_FEATURE_ENABLE IS_DEFAULT
 ---------- ---------- ---------- ------------------------- ----------
 22 4488689 0 10.2.0.2 0
 22 4631959 0 10.2.0.2 0
 22 4519340 0 10.2.0.2 0

 

Disassembling V$ views

If you are instesting by Oracle Internals, maybe you have already tried to use the DBMS_METADATA Package to get the definition code of an Oracle static view :

SQL> set long 5000
SQL> set pages 5000
SQL> select dbms_metadata.get_ddl('VIEW','DBA_DATA_FILES') from dual;
DBMS_METADATA.GET_DDL('VIEW','DBA_DATA_FILES')
---------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_DATA_FILES" ("FILE_NAME", "F
ILE_ID", "TABLESPACE_NAME", "BYTES", "BLOCKS", "STATUS", "RELATIVE_FNO
", "AUTOEXTENSIBLE", "MAXBYTES", "MAXBLOCKS", "INCREMENT_BY", "USER_BY
TES", "USER_BLOCKS", "ONLINE_STATUS") AS
select v.name, f.file#, ts.name,
ts.blocksize * f.blocks, f.blocks,
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
ts.blocksize * f.maxextend, f.maxextend, f.inc,
ts.blocksize * (f.blocks - 1), f.blocks - 1,
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
where v.file# = f.file#
and f.spare1 is NULL
and f.ts# = ts.ts#
and fe.fenum = f.file#
union all
select
.../...


But if you try to get the definition code of the “V$” dynamic views, you will obtain a strange output which mentioned a call to V$DATAFILE (at the end)

SQL> select dbms_metadata.get_ddl('VIEW','V_$DATAFILE') from dual;
DBMS_METADATA.GET_DDL('VIEW','V_$DATAFILE')
----------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."V_$DATAFILE" ("FILE#", "CREATION
_CHANGE#", "CREATION_TIME", "TS#", "RFILE#", "STATUS", "ENABLED", "CHE
CKPOINT_CHANGE#", "CHECKPOINT_TIME", "UNRECOVERABLE_CHANGE#", "UNRECOV
ERABLE_TIME", "LAST_CHANGE#", "LAST_TIME", "OFFLINE_CHANGE#", "ONLINE_
CHANGE#", "ONLINE_TIME", "BYTES", "BLOCKS", "CREATE_BYTES", "BLOCK_SIZ
E", "NAME", "PLUGGED_IN", "BLOCK1_OFFSET", "AUX_NAME", "FIRST_NONLOGGE
D_SCN", "FIRST_NONLOGGED_TIME", "FOREIGN_DBID", "FOREIGN_CREATION_CHAN
GE#", "FOREIGN_CREATION_TIME", "PLUGGED_READONLY", "PLUGIN_CHANGE#", "
PLUGIN_RESETLOGS_CHANGE#", "PLUGIN_RESETLOGS_TIME") AS
select "FILE#","CREATION_CHANGE#","CREATION_TIME","TS#","RFILE#","ST
ATUS","ENABLED","CHECKPOINT_CHANGE#","CHECKPOINT_TIME","UNRECOVERABLE_
CHANGE#","UNRECOVERABLE_TIME","LAST_CHANGE#","LAST_TIME","OFFLINE_CHAN
GE#","ONLINE_CHANGE#","ONLINE_TIME","BYTES","BLOCKS","CREATE_BYTES","B
LOCK_SIZE","NAME","PLUGGED_IN","BLOCK1_OFFSET","AUX_NAME","FIRST_NONLO
GGED_SCN","FIRST_NONLOGGED_TIME","FOREIGN_DBID","FOREIGN_CREATION_CHAN
GE#","FOREIGN_CREATION_TIME","PLUGGED_READONLY","PLUGIN_CHANGE#","PLUG
IN_RESETLOGS_CHANGE#","PLUGIN_RESETLOGS_TIME" from v$datafile

In fact, dynamic performance view definition are stored in the V$FIXED_VIEW_DEFINITION.
As in 10g and 11g, the V$ views are results of GV$ view, if you want to get the definition view of the V$DATAFILE, a better way is to query this view with the GV$DATAFILE definition.

SELECT view_definition FROM
2 v$fixed_view_definition
3 WHERE view_name='V$DATAFILE'
4 /
VIEW_DEFINITION
----------------------------------------------------------------------
select FILE# , CREATION_CHANGE# , CREATION_TIME , TS# , RFILE# , STAT
US , ENABLED , CHECKPOINT_CHANGE# , CHECKPOINT_TIME, UNRECOVERABLE_CHA
NGE#, UNRECOVERABLE_TIME, LAST_CHANGE# , LAST_TIME , OFFLINE_CHANGE# ,
ONLINE_CHANGE# , ONLINE_TIME , BYTES , BLOCKS , CREATE_BYTES , BLOCK_
SIZE , NAME, PLUGGED_IN, BLOCK1_OFFSET , AUX_NAME , FIRST_NONLOGGED_SC
N, FIRST_NONLOGGED_TIME, FOREIGN_DBID, FOREIGN_CREATION_CHANGE#, FOREI
GN_CREATION_TIME, PLUGGED_READONLY, PLUGIN_CHANGE#, PLUGIN_RESETLOGS_C
HANGE#, PLUGIN_RESETLOGS_TIME from GV$DATAFILE where inst_id = USERENV
('Instance')
SELECT view_definition FROM
2 v$fixed_view_definition
3 WHERE view_name='GV$DATAFILE'
4 /
VIEW_DEFINITION
----------------------------------------------------------------------
select fe.inst_id,fe.fenum,to_number(fe.fecrc_scn), to_date(fe.fecrc_t
im,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), fe.fetsn,fe.ferfn,
decode(fe.fetsn,0,decode(bitand(fe.festa,2),0,'SYSOFF','SYSTEM'), d
ecode(bitand(fe.festa,18),0,'OFFLINE',2,'ONLINE','RECOVER')), decode(f
e.fedor,2,'READ ONLY', decode(bitand(fe.festa, 12), 0,'DISABLED'
,4,'READ ONLY',12,'READ WRITE','UNKNOWN')), to_number(fe.fecps), to_da
te(fe.fecpt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), to_number
(fe.feurs), to_date(fe.feurt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Grego
rian'), to_number(fe.fests), decode(fe.fests,NULL,to_date(NULL), to_d
ate(fe.festt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian')), to_numb
er(fe.feofs),to_number(fe.feonc_scn), to_date(fe.feonc_tim,'MM/DD/RR H
H24:MI:SS','NLS_CALENDAR=Gregorian'), fh.fhfsz*fe.febsz,fh.fhfsz,fe.fe
csz*fe.febsz,fe.febsz,fn.fnnam, fe.fefdb, fn.fnbof, decode(fe.fepax,
0, 'UNKNOWN', 65535, 'NONE', fnaux.fnnam), to_number(fh.fhfirstunr
ecscn), to_date(fh.fhfirstunrectime,'
MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), fe.fepdi, fe.fefcrs, f
e.fefcrt, decode(fe.fefdb, 1, 'YES', 'NO'), fe.feplus, fe.feprls, fe.
feprlt from x$kccfe fe, x$kccfn fn, x$kccfn fnaux, x$kcvfh fh where
((fe.fe
pax!=65535 and fe.fepax!=0 and fe.fepax=fnaux.fnnum) or
((fe.fepax=65535 or fe.fepax=0) and fe.fenum=fnaux.fnfno
and fnaux.fntyp=4 and fnaux.fnnam is not null and
bitand(fnaux.fnflg, 4) != 4
and fe.fefnh=fnaux.fnnum))
and fn.fnfno=fe.fenum and fn.fnfno=fh.hxfil an
d fe.fefnh=fn.fnnum and fe.fedup!=0 and fn.fntyp=4 and fn.f
nnam is not null and bitand(fn.fnflg, 4) != 4 order by fe.fenum

Now you know that GV$DATAFILE view is using x$kccfe, x$kccfn, x$kcvfh fixed tables.

Oracle 11g md_restore and compatible.rdbms

I have recently made a restore test of my diskgroup metadata with the new version of oracle server : 11.2.0.1.

Initially, I have created a TEST_DG diskgroup with the new asmca interface.
As I had a 10g database on my laptop, I have set the compatibility.rdbms parameter on 10.2.0.0.
After a rapid md_backup command to save my diskgroup metadatas, I have tried to restore it with the md_restore command.

What a surprise when I saw this error message:

ASMCMD [+] > md_restore md_backup.sav -G TEST_DG
Current Diskgroup metadata being restored: TEST_DG
ASMCMD-09352: CREATE DISKGROUP failed
ORA-15018: diskgroup cannot be created
ORA-15283: ASM operation requires compatible.rdbms of 11.1.0.7.0 or higher (DBD ERROR: OCIStmtExecute)

After a short research, I realize that the CREATE DISKGROUP command is generated like this:

create diskgroup TEST_DG EXTERNAL redundancy disk '/dev/oracleasm/disks/ASM10'name TEST_DG_0001 size 100M disk '/dev/oracleasm/disks/ASM05' name
TEST_DG_0000 size 100M attribute 'compatible.asm' = '11.2.0.0.0' , 'compatible.rdbms' = '10.2.0.0.0' , 'au_size' = '1048576', 'sector_size' = '512
', 'cell.smart_scan_capable' = 'FALSE';

And this command uses properties which have been introduced in Oracle 11g release (for example: sector_size). Next command works fine:

create diskgroup TEST_DG EXTERNAL redundancy disk '/dev/oracleasm/disks/ASM10' name TEST_DG_0001 size 100M disk '/dev/oracleasm/disks/ASM05' name
TEST_DG_0000 size 100M attribute 'compatible.asm' = '11.2.0.0.0' , 'compatible.rdbms' = '10.2.0.0.0' ;

So, if like me, you have some Oracle 10g databases that run on an ASM 11g release, you have to restore your diskgroup metadata in an SQL file (by using -S option of the md_restore command) and adapt the DDLs for your needs.

Not really easy ! 😉