Oracle Audit or Review?

Whether they call it a Review or something else, it feels like, looks like - an Audit. Before you fill out any forms or run any scripts on your system at Oracle's request, please seek the assistance of a third-party beforehand. The saying: "What you say can be used against you" holds true here and like having legal counsel to represent you - you will want someone to explain the process to you from the Oracle side and give you the confidence to push back.

A third-party can help you understand areas such as:

• Your licensing inventory
• What is installed where
• Understanding metrics
• Virtualization impacts
• Mismanaged fault tolerance
• Unintentional license misuse 

Here are some providers:

Palisade Compliance:
http://palisadecompliance.com/oracle-license-audit/

Madora Consulting:
http://madora.co.uk/licence-health-check/


I will list others as they become known. I do not endorse any of these so perform the same due diligence you would when selecting legal counsel.

Oracle Audit or Review?

Whether they call it a Review or something else, it feels like, looks like - an Audit. Before you fill out any forms or run any scripts on your system at Oracle's request, please seek the assistance of a third-party beforehand. The saying: "What you say can be used against you" holds true here and like having legal counsel to represent you - you will want someone to explain the process to you from the Oracle side and give you the confidence to push back.

A third-party can help you understand areas such as:

• Your licensing inventory
• What is installed where
• Understanding metrics
• Virtualization impacts
• Mismanaged fault tolerance
• Unintentional license misuse 

Here are some providers:

Palisade Compliance:
http://palisadecompliance.com/oracle-license-audit/

Madora Consulting:
http://madora.co.uk/licence-health-check/


I will list others as they become known. I do not endorse any of these so perform the same due diligence you would when selecting legal counsel.

Cary Millsap: Fail Fast

Cary Millsap: Fail Fast: Among movements like Agile , Lean Startup , and Design Thinking these days, you hear the term fail fast . The principle of failing fast is...


Find your worst bottleneck, and make it your highest priority. If you cannot solve your idea’s worst problem, then get a new idea. You’ll do yourself a favor by killing a bad idea before it kills you. If you solve your worst problem, then find the next one. Iterate. Shorter iterations are better. You’re done when you’ve proven that your idea actually works. In reality. And then, because life keeps moving, you have to keep iterating.

That’s what fail fast means. It’s about shortening your feedback loop. It’s about learning the most you can about the most important things you need to know, as soon as possible.

Notes on Researching ORA-02049 Errors

If disabling trigger with dblink is possisble, then disable it.

If disabling the trigger is not possible, then one should look into the following to troubleshoot the issue:

Is there a bit map used in the involved tables?

Are there any open transactions on these tables? Check for lock, block, and wait on these tables.

Is there materialized view with refresh on commit setting on these tables?

How fast transactions can be posted using dblink?

Check with Oracle Support to see if DISTRIBUTED_LOCK_TIMEOUT can be changed to a new higher value (the default is 60) - 300 seconds is a common value in a distributed environment. 

~~~~

Query the V$LOCK View:

• Any ANALYZE TABLE TABLENAME COMPUTE STATISTICS? (not a factor 10.2+)
• Any SELECT FOR UPDATE on the table?

~~~~

Managing Read Consistency

An important restriction exists in Oracle's implementation of distributed read consistency. The problem arises because each system has its own SCN, which you can view as the database's internal timestamp. The Oracle database server uses the SCN to decide which version of data is returned from a query.

The SCNs in a distributed transaction are synchronized at the end of each remote SQL statement and at the start and end of each transaction. Between two nodes that have heavy traffic and especially distributed updates, the synchronization is frequent. Nevertheless, no practical way exists to keep SCNs in a distributed system absolutely synchronized: a window always exists in which one node may have an SCN that is somewhat in the past with respect to the SCN of another node.

Because of the SCN gap, you can execute a query that uses a slightly old snapshot, so that the most recent changes to the remote database are not seen. In accordance with read consistency, a query can therefore retrieve consistent, but out-of-date data. Note that all data retrieved by the query will be from the old SCN, so that if a locally executed update transaction updates two tables at a remote node, then data selected from both tables in the next remote access contain data prior to the update.

One consequence of the SCN gap is that two consecutive SELECT statements can retrieve different data even though no DML has been executed between the two statements. For example, you can issue an update statement and then commit the update on the remote database. When you issue a SELECT statement on a view based on this remote table, the view does not show the update to the row. The next time that you issue the SELECT statement, the update is present.

You can use the following techniques to ensure that the SCNs of the two machines are synchronized just before a query:

• Because SCNs are synchronized at the end of a remote query, precede each remote query with a dummy remote query to the same site, for example, SELECT * FROM DUAL@REMOTE.
• Because SCNs are synchronized at the start of every remote transaction, commit or roll back the current transaction before issuing the remote query.

Table Stats Using Objects in a Materialized View Refresh Group

If you use Refresh Groups for your Materialized Views, it may be helpful to look at some basic table stats in a group.

This query will sum the sizes of the Table objects for each REFGROUP:

SELECT refgroup, SUM (MEG) as meg
  FROM (SELECT a.OWNER, a.SEGMENT_NAME, b.refgroup, a.BYTES / 1024 / 1024 AS MEG
          FROM dba_segments a, all_refresh_children b
         WHERE     a.segment_type = 'TABLE'
         and A.SEGMENT_NAME = B.NAME
               AND SEGMENT_NAME IN (SELECT name
                                      FROM all_refresh_children
                                     ))
   group by refgroup
   order by refgroup;


This query will return Object specifics for a single REFGROUP in question:


  SELECT a.table_name,
         a.num_rows ROW_count_FROM_STATS,
         b.col_count,
         C.MEG
    FROM dba_tables a,
         (  SELECT TABLE_NAME, COUNT (column_name) AS col_count
              FROM dba_tab_cols
             WHERE owner||TABLE_NAME IN (SELECT owner || name
                                   FROM all_refresh_children
                                  WHERE refgroup = 5)
          GROUP BY TABLE_NAME
          ORDER BY TABLE_NAME) b,
         (  SELECT owner, table_name, TRUNC (SUM (bytes) / 1024 / 1024) Meg
              FROM (SELECT segment_name AS table_name, owner, bytes
                      FROM dba_segments
                     WHERE segment_type = 'TABLE' AND owner||SEGMENT_NAME IN (SELECT owner || name
                                   FROM all_refresh_children
                                  WHERE refgroup = 5)
                    UNION ALL
                    SELECT i.table_name, i.owner, s.bytes
                      FROM dba_indexes i, dba_segments s
                     WHERE     s.segment_name = i.index_name
                           AND s.owner = i.owner
                           AND s.segment_type = 'INDEX'
                    UNION ALL
                    SELECT l.table_name, l.owner, s.bytes
                      FROM dba_lobs l, dba_segments s
                     WHERE     s.segment_name = l.segment_name
                           AND s.owner = l.owner
                           AND s.segment_type = 'LOBSEGMENT'
                    UNION ALL
                    SELECT l.table_name, l.owner, s.bytes
                      FROM dba_lobs l, dba_segments s
                     WHERE     s.segment_name = l.index_name
                           AND s.owner = l.owner
                           AND s.segment_type = 'LOBINDEX')
             WHERE owner||TABLE_NAME IN (SELECT owner || name
                                   FROM all_refresh_children
                                  WHERE refgroup = 5)
          GROUP BY table_name, owner
--            HAVING SUM (bytes) / 1024 / 1024 > 5 /* Ignore really small tables */
          ORDER BY SUM (bytes) DESC) C
   WHERE     a.owner||A.TABLE_NAME IN (SELECT owner || name
                                   FROM all_refresh_children
                                  WHERE refgroup = 5)
         AND A.TABLE_NAME = b.table_name(+)
         AND A.TABLE_NAME = C.TABLE_NAME(+)
ORDER BY a.table_name;

Working with Application System Accounts (CREATE LIKE)

If you have an Application on a Oracle database that uses a standard account to access the database (and usually owns all the objects), sometimes you will need to drop that user (cascade constraints) for reasons such as:

 

• Refreshing a schema (often with Test accounts)
• Performing complete restores

 

Since the roles and object privileges are often quite involved on systems like this, it is a good idea to make copies of your user. I used to use this all the time on OEM but now this function exists in SQL*Developer also.

 

Please see this great article from Jeff Smith:

http://www.thatjeffsmith.com/archive/2015/12/trick-getting-all-the-privileges-grants-for-a-user/

 

I would usually name my user "XXXX_shell"  (like: FIN_SHELL) and after their objects were dropped, would perform this "CREATE LIKE" again back to the normal user name (like: FIN_PROD). Now you are ready to IMP your objects.