Six Reasons Why EA Should NOT be Assigned to the IT Department

6. EA ≠ IT Architecture

5. True EA leads to redistribution of authority, which is beyond CIO jurisdiction.

4. EA value proposition (i.e. standardization vs. innovation) is solely business realizable.

3. The primary goal of EA is to build coherent enterprises, not better IT systems.

2. Synthesis takes precedence over analysis.

1. EA failure is an organization failure, not an IT failure.

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/

How to check rows that got inserted today

I got a reaquest where a user wanted to check all the records created today or between a date range. This was not achievable from the application perspective as it was not having any column like that.

Luckily I got reference to one of the reply from Pelle in a forum regarding this -

If you are running 10g or higher, you can use the ORA_ROWSCN pseudocolumn, which can be translated to a timestamp using the SCN_TO_TIMESTAMP function.

select ID from SCHEMA.TABLE where scn_to_timestamp( ora_rowscn ) - 0 > sysdate - 1;

But there can be a situation where we insert the data imported from another instance. Since it will have an invalid SCNs, it will raise an exception. So, here is a small function that returns NULL whenever there's an imported row with an invalid SCN.

CREATE OR REPLACE function rowdate( rowscn in number )
return date
as
l_rowts timestamp;
bad_scn exception;
pragma exception_init( bad_scn, -8181 );
begin
l_rowts := scn_to_timestamp( rowscn );
return cast( l_rowts as date);
exception when bad_scn then
return null;
end;
/

and then you can use the following SQL to see the records added to the table but be advised that this will give you not only rows inserted today, but also the updated ones.

select ID from SCHEMA.TABLE where rowdate( ora_rowscn ) > trunc( sysdate );

Monitoring Indexes

Databases, over the period of time can have many indexes. Some indexes might be in use but others may not be. Since these indexes eat up al lot of space and adds to a total cost, we should try to check what all indexes are in use and what not so that we can remove the unwanted indexes from the system.

Way to acheive this is that, we can turn on the Monitioring on the indexes for a while like -

ALTER INDEX INDEX_NAME MONITORING USAGE;

and once the information has been gathered we can turn off the monitoring like -

ALTER INDEX INDEX_NAME NOMONITORING USAGE;

This will record all the information under the table OBJECT_USAGE data dictionary view.
Under this view there are following four column (OBJ#,FLAGS,START_MONITORING,
END_MONITORING)


OBJ# is the OBJECT_ID and can be correlated with DBA_OBJECTS. The OBJECT_ID is a unique identifier of the object in the database. Each object is assigned a unique number to recognize it in the database. So, no two object of two different schemas will be same. That is why Oracle might have kept only one master table (X$) and one master view (OBJECT_USAGE) to know which objects were actually get used and which were not.

FLAGS column of OBJECT_USAGE view can have two values, 1 (USED) and 0 (NOT USED).


After turning on/off the monitoring we can run a simple query like the following to get the Objects that are being in use -

SELECT A.OWNER, A.OBJECT_NAME, A.OBJECT_TYPE
FROM DBA_OBJECTS A, OBJECT_USAGE B
WHERE A.OBJECT_ID=B.OBJ#
AND B.FLAGS=1
/

If you want to check the space used by all INDEXS that are NOT in use then you can check it by running this query -

SELECT SUM(BYTES/1024/1024/1024) "TOTAL SPACE (GB)"
FROM DBA_SEGMENTS A, DBA_OBJECTS B, OBJECT_USAGE C
WHERE A.OWNER=B.OWNER
AND A.SEGMENT_NAME=B.OBJECT_NAME
AND B.OBJECT_ID=C.OBJ#
AND B.OBJECT_TYPE='INDEX'
AND C.FLAGS=0
/


This can save us not only space but can enhance the performance of the query also.