Monday 5 November 2012

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 CarefulTM with this.

Sunday 2 September 2012

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

Sunday 26 August 2012

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"

Wednesday 1 August 2012

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;
/

Friday 27 July 2012

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$

Thursday 26 July 2012

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;

Wednesday 25 July 2012

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.

Monday 23 July 2012

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.

Friday 20 July 2012

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$.

Tuesday 10 July 2012

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.

Saturday 23 June 2012

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. 

Wednesday 1 February 2012

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.