ACL on AIX for a File

There is a requirement for an outside user (not a member of DBA group) to get READ acces to alert.log. Since we are on AIX I will show how it should be done on AIX -

export EDITOR=$(which vi)
acledit /oracle/diag/rdbms/testdb/TESTDB/trace/alert_TESTDB.log

as soon as you hit enter, it will open an vi editor and you may need to change

"/tmp/acledit.2908392/aclet-yo7a" 10 lines, 148 characters
*
* ACL_type AIXC
*
attributes:
base permissions
owner(oracle): rw-
group(dba): r--
others: ---
extended permissions
disabled

Change it to -

"/tmp/acledit.2908392/aclet-yo7a" 10 lines, 148 characters
*
* ACL_type AIXC
*
attributes:
base permissions
owner(oracle): rw-
group(dba): r--
others: ---
extended permissions
enabled
permit r-- u:newuser

Here newuser is the user to which we are permitting to READ (r--) alert_TESTDB.log file.

NOLOGGING and Corruption

I got Block corruption error while gathering the stats. This will show how to find the corrupted segment.

SQL> execute dbms_stats.gather_database_stats;
BEGIN dbms_stats.gather_database_stats; END;
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 16270)
ORA-01110: data file 9: '/oracle/STGDB/data02/int_tab_1m_a_01'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_STATS", line 18569
ORA-06512: at "SYS.DBMS_STATS", line 19054
ORA-06512: at "SYS.DBMS_STATS", line 19211
ORA-06512: at "SYS.DBMS_STATS", line 19170
ORA-06512: at line 1


SELECT SEGMENT_TYPE,OWNER'.'SEGMENT_NAME
FROM DBA_EXTENTS
WHERE FILE_ID = 9 AND 16270 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS -1;
SEGMENT_TYPE
------------------
OWNER'.'SEGMENT_NAME
--------------------------------------------------------------------------------
INDEX
STGDB.AUDITENTRY_IX02

For now, just rebuild the index without nologging. However, that's addressing the symptom, not the cause, and you must learn the root cause of the corruption.

If you did not do a recovery on this index, open an SR IMMEDIATELY and Oracle technical Support. They can use the block editor (BBED) utility to inspect the data blocks and see EXACTLY why they became corrupted. You can also inspect the blocks with BBED yourself.

Table Fragmentation

To find if there is fragmentation in a table run the following script -

select table_name,round((blocks*8/1024),2) "TABLE SIZE (MB)",round((num_rows*avg_row_len/1024/1024),2) "ACTUAL DATA (MB)",
round((blocks*8/1024),2)-round((num_rows*avg_row_len/1024/1024),2) "DIFF (MB)"
from dba_tables
where OWNER ='SCOTT'
ORDER BY 4;

If you find few tables where the colum "DIFF" is too high then you may need to reset the High Water Mark or remove fragmentation. We can achieve by taking one of the following step -

1. alter table ... move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition

Notes

Can Oracle 11g MEMORY_TARGET work with AIX Large Pages?

http://intermediatesql.com/index.php/aix/can-oracle-11g-memory_target-work-with-aix-large-pages/