Simulate Server Crash

During some RAC 11.2  testing, I wanted to simulate a server crash, but ensure that the server rebooted afterwards.

The following does just that - you execute it as root:
#echo 1 > /proc/sys/kernel/sysrq
#echo b > /proc/sysrq-trigger

Obviously, Be Careful^TM with this.

ANY, SOME & ALL

I'm studying for my OCP, and have decided to do exam 1ZO-047 "Oracle Database SQL Expert" as one of my OCA exams.

I recently came across a question on the usage of ANY and SOME, which I have rarely, if ever encountered in my job, so I Googled and found this great explanation by Eddie Awad:

http://awads.net/wp/2005/08/09/any-some-and-all-in-oracle

DBMS_METADATA Valid Object Types

DBMS_METADATA takes object type parameters that do not always match the object type you'd find in DBA_OBJECTS. e.g. in the following, which gets DDL for a database link, notice that "DB_LINK", not "DATABASE LINK", is passed as the object type:

SELECT DBMS_METADATA.GET_DDL('DB_LINK','SCOTTS_LINK','SCOTT')
FROM DUAL;

To get the definitive object type list visit http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#i998485 and navigate to "Table 57-18 Object Type Path Names for Heterogeneous Object Types"

SP2-0027: Input Is Too Long

If you are attempting to execute DBMS_TTS.TRANSPORT_SET_CHECK over a long list of tablespaces, then you may encounter "SP2-0027: Input Is Too Long".

The solution is to use PL/SQL, where the VARCHAR2 length limit is 32767 (i.e. 32K - 1).

The following example loops through a list of schemas, and, per schema, creates a comma-separated list of all tablespaces, and then executes DBMS_TTS.TRANSPORT_SET_CHECK per schema:

DECLARE
LONG_VARCHAR2 VARCHAR2(32767);

BEGIN

    FOR X IN
    (
    SELECT DISTINCT TABLESPACE_NAME
      FROM DBA_SEGMENTS
     WHERE OWNER IN ('SCOTT','FRED','MEGA_APP')
    )
    LOOP
        LONG_VARCHAR2 := LONG_VARCHAR2||X.TABLESPACE_NAME||',';
    END LOOP;

-- remove the final comma
LONG_VARCHAR2 := SUBSTR(LONG_VARCHAR2,1,LENGTH(LONG_VARCHAR2)-1);

-- do the work
DBMS_TTS.TRANSPORT_SET_CHECK('' || LONG_VARCHAR2 || '', TRUE);

-- show the list of tablespaces we just worked on
DBMS_OUTPUT.PUT_LINE(LONG_VARCHAR2);
END;
/

Stopping Auditing of Objects

Assume you have set-up auditing of tables in the following 3 schemas:

FINANCE1, DATAUSER2 and REPORTUSER9

The purpose of the auditing is to check whether any of the schemas' tables is no longer being used i.e. not queried or changed in any way.

Any unused tables in these schemas will be dropped as part of housekeeping.

You want to stop AUD$ becoming too large, and so want to stop auditing of a table soon after any rows for it are written to AUD$.

You have set-up auditing of the 3 schemas' table by executing the SQL generated by the following dynamic SQL:

select distinct 'AUDIT SELECT, INSERT, UPDATE, DELETE ON "'||owner||'"."'||table_name||'" BY SESSION;'
from dba_tables
where owner in ('FINANCE1','DATAUSER2','REPORTUSER9');
 

If the following dynamic SQL is executed it will generate a NOAUDIT SQL statement for each table in the 3 schemas that is being audited, and which has has at least 1 row in AUD$:

SELECT  DISTINCT 'NOAUDIT INSERT, UPDATE, DELETE, SELECT ON '||OBJ$CREATOR||'.'||OBJ$NAME||';'
FROM SYSTEM.AUD$
WHERE TRUNC(NTIMESTAMP#) = TRUNC(SYSDATE)
AND OBJ$CREATOR||'.'||OBJ$NAME IN (SELECT OWNER||'.'||OBJECT_NAME FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER IN ('FINANCE1','DATAUSER2','REPORTUSER9') AND OBJECT_TYPE='TABLE');

Note:
If your AUD$ is large, then you may want to index AUD$

What Is Being Audited?

If you want to know which objects are currently being audited, then run this: 

SELECT * FROM DBA_OBJ_AUDIT_OPTS ORDER BY OWNER,OBJECT_NAME;

INS-32025

The problem:

You can't install the 11gR2 Client on Windows, and are seeing an error like this:

Preparing to launch Oracle Universal Installer from C:\DOCUME~1\orauser\LOCALS~1\Temp\1\OraInstall2012-07-24_12-47-55PM.
Please wait ... [FATAL] [INS-32025] The chosen installation conflicts with software already installed in the given Oracle home.
CAUSE: The chosen installation conflicted with software already installed in the given Oracle home.
ACTION: Install into a different Oracle home.

Please press Enter to exit...

Basically, the installer thinks there's already Oracle software installed in the Oracle Home.
Assuming that you used - correctly - the deinstall tool, then be aware that the tool does not do a 100% job. It can fail to remove previous 11g entries in the inventory.xml file, located in (in my case) c:\Program Files\Oracle\Inventory\ContentsXML

So, manually remove the line pertaining to the Oracle Home that no longer exists, having backed-up the file first, just in case.

Indexes and the Recyclebin

I found out recently that if a table is dropped (without PURGE) and the table has indexes, then, if the table is "undropped", using:

FLASHBACK TABLE my_table TO BEFORE DROP;

the table's indexes are also restored but with their recyclebin names. You can rename such indexes later to their original names.

This is still the behaviour in 11.2.0.3.

So, keep a record of index names before performing any table drops, if you want to avoid problems finding out their original names.

Indexing AUD$

AUD$ can become very large. It depends, of course, on how you set-up auditing in your database.

It can be a bit of a pain querying a monolithic AUD$, especially as it has no index.

So, what to do? Well, the only real option is to create an index on AUD$.

Caveat: Oracle does not support additional indexes on AUD$ (see MOS Note ID 1329731.1), but it's not a show-stopper.

A good approach to creating an index on AUD$ is to use a Function-Based Index (FBI), on the trunc of NTIMESTAMP#. This means each index leaf points to just a day's worth of AUD$ rows.

The SQL:
CREATE INDEX AUD_NTIMESTAMP
ON AUD$ (TRUNC(NTIMESTAMP#))
ONLINE
TABLESPACE MY_NEW_TABLESPACE;

To use the new index:

Example 1:
SELECT *
FROM SYSTEM.AUD$
WHERE TRUNC(NTIMESTAMP#) = TRUNC(SYSDATE);

Example 2:
SELECT *
FROM SYSTEM.AUD$
WHERE TRUNC(NTIMESTAMP#) =
TO_DATE('01-JUL-2011 00:00:00','DD-MON-YYYY HH24:MI:SS');

I found that the FBI index was about 4% of the size of AUD$.

Query Results in Comma-Separated String

Another hidden gem.

Ever wanted to get the results of a query in a single string, with values separated by commas? e.g. you want all tables for a user to be exported using Data Pump.

Here's an easy way to get that list of tables in Data Pump-friendly format:

select wm_concat('SCOTT.'||table_name)

from dba_tables 

where owner = 'SCOTT';

Results:
SCOTT.TABLE_01,SCOTT.TABLE_02,SCOTT.TABLE_03, ...

wm_concat is an Oracle-supplied function and may change at some point in the future, etc., etc.

Object Dependencies

I am currently reacquainting myself with the Oracle documentation, starting with the Concepts Manual, now on Database Administration, and on to a load more. It's surprising what one can forget, or think one knows, but doesn't.

Anyway, I was surprised to find that there is a nice little tool - written 11 years ago - called DEPTREE. 

The doco is here.

NOTE: the objects created by utldtree.sql are created in the schema you are current logged in to. 

Replication: Archive Destination in Error

Replication, even with Data Guard Broker, can be a challenge sometimes, especially if replication stops, and everything looks OK, leading to the Big Question: What's wrong with the #@!% thing?!

If all else fails, it may be worth taking a look at Archive Dest status on the Primary instances.

Unhelpfully, logging in to SQL*Plus and issuing "SHOW PARAMETER ARCHIVE_DEST" will not, necessarily, show in-error archive dest.

Instead, you have to use the following:
col DEST_NAME form A25

col DESTINATION form A20

col STATUS form A20

--

select DEST_NAME,DESTINATION,STATUS,ERROR
from V$ARCHIVE_DEST_STATUS
order by STATUS,DEST_NAME;

If you see "ERROR" in the STATUS column, then re-enable the archive dest.