Parrallel execution (external link)

Understanding Parallel Execution - parts 1 and 2

LONG columns and scripts

There are many ways to read a LONG column -  in general one have to use the PL/SQL code. The summary of those ways is provided here (http://www.oracle-developer.net/display.php?id=430). 
I focused only on one of those methods i.e. the one leveraging the XML processing features of the Oracle RDBMS. The main reason here is the convenience while trying to produce a kind of administration script (which a basic task is usually to generate another script, which in turn makes the final effect). Here an example - the script, which produces the script, which may recreate constraints from a chosen schema:

set pages 0 lin 300 trimspool on trimout on
define CONDITIONS='OWNER=''VPD_BPSS'''

-- check
SELECT 'alter table '
  || xs.owner
  ||'.'
  || xs.table_name
  ||' add constraint '
  || xs.constraint_name
  ||' '
  || ' check ('
  || xs.search_condition
  ||')'
  ||
  CASE xs.status
    WHEN 'ENABLED'
    THEN ' enable '
    WHEN 'DISABLED '
    THEN ' disable '
  END
  || ';' SQL1
FROM XMLTABLE('/ROWSET/ROW' PASSING
  (SELECT dbms_xmlgen.getxmltype(
    q'[SELECT * FROM dba_constraints c WHERE c.constraint_type in ('C') and &&CO
NDITIONS]' )
  FROM dual
  ) COLUMNS
  owner VARCHAR2(32) PATH 'OWNER',
  constraint_name VARCHAR2(32) path 'CONSTRAINT_NAME',
  constraint_type VARCHAR2(1) path 'CONSTRAINT_TYPE',
  table_name VARCHAR2(30) path 'TABLE_NAME',
  search_condition VARCHAR2(4000) path 'SEARCH_CONDITION',
  status VARCHAR2(30) path 'STATUS' ) xs
UNION ALL
-- pk and uniq
SELECT 'alter table '
  ||c.owner
  ||'.'
  ||c.table_name
  ||' add constraint '
  ||c.constraint_name
  ||
  CASE c.constraint_type
    WHEN 'P'
    THEN ' primary key'
    WHEN 'U'
    THEN ' unique'
  END
  || ' ('
  ||
  (SELECT LISTAGG(i.column_name, ',') WITHIN GROUP (
  ORDER BY i.position)
  FROM dba_cons_columns i
  WHERE c.owner         = i.owner
  AND c.constraint_name = i.constraint_name
  )
  ||') using index '
  ||c.index_owner
  ||'.'
  ||c.index_name
  ||' '
  || c.deferrable
  ||' '
  || c.deferred
  ||' '
  ||';' DDL1
FROM DBA_CONSTRAINTS C
WHERE c.constraint_type IN ('P','U')
AND
  &&CONDITIONS
UNION ALL
-- fk
SELECT 'alter table '
  ||c.owner
  ||'.'
  ||c.table_name
  ||' add constraint '
  ||c.constraint_name
  ||' foreign key('
  ||
  (SELECT LISTAGG(f.column_name, ',') WITHIN GROUP (
  ORDER BY f.position)
  FROM dba_cons_columns f
  WHERE c.owner        =f.owner
  AND c.constraint_name=f.constraint_name
  )
  ||') '
  ||' references '
  ||
  (SELECT p.owner
    ||'.'
    ||p.table_name
    ||'('
    || LISTAGG(p.column_name, ',') WITHIN GROUP (
  ORDER BY p.position)
    || ')'
  FROM dba_cons_columns p
  WHERE c.r_OWNER        =p.owner
  AND c.r_constraint_name = p.constraint_name
  GROUP BY p.owner,
    p.table_name
  )
  ||' '
  ||
  CASE
    WHEN c.delete_rule IS NOT NULL
    AND c.delete_rule!  ='NO ACTION'
    THEN 'ON DELETE '
      || c.delete_rule
      ||' '
  END
  || c.deferrable
  ||' '
  || c.deferred
  ||' '
  ||
  CASE status
    WHEN 'ENABLED'
    THEN 'ENABLE novalidate '
    WHEN 'DISABLED '
    THEN 'DISABLE '
  END
  ||';' ddl1
FROM DBA_CONSTRAINTS C
WHERE C.CONSTRAINT_TYPE IN ('R')
AND
  &&CONDITIONS ;

My own commandline FU

Shell

# changes the ownership for a particular user
# I use it mainly in cases the uid/gid differs between machines while it was intended
# to be the same 

find /oracle -uid 500 -exec chown oracle {} \;
find /oracle -gid 500 -exec chgrp oracle {} \;

OT: Commandline Fu (external link)

This is not exactly bound with the Oracle RDBMS, but I am impressed by cleverness and flexibility of those oneliners - http://www.commandlinefu.com/commands/browse

The ORA-00001 while resyncing the catalog

Lately I switched over a database to the standby. All went well until I faced the RMAN configuration. Usually there are problems with archived logs, which were backed up on the previous primary node and I usually deal with that calling CROSSCHECK ARCHIVELOG ALL;
This time however I have got the following:

RMAN> resync catalog;

starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 10/24/2013 22:57:29
ORA-00001: unique constraint (RMAN.TS_U3) violated

I registered an SR on the MOS, and they stated I probably hit the BUG:12597985 - RMAN RESYNC CATALOG GOT ORA-1 and may install some patch or use workaround in the form:

unregister database;
register database;

I went on with the provided workaround and hit the following:

RMAN> unregister database;

database name is "YYYYYY" and DBID is 688900000

Do you really want to unregister the database (enter YES or NO)? YES
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: 
ORA-02292: integrity constraint (RMAN.TSATT_F2) violated - child record found

Now I was informed this time it is a Bug 9799518 RMAN-6004 / ORA-2292 trying to unregister a database from an RMAN catalog and again I can install a patch. I would like to avoid the patch installation, so I did something else. Below the solution:

RMAN> unregister database;

database name is "YYYYYY" and DBID is 688900000

Do you really want to unregister the database (enter YES or NO)? YES
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: 
ORA-02292: integrity constraint (RMAN.TSATT_F2) violated - child record found

# here from sqlplus I disabled RMAN.TSATT_F2
# select table_name from dba_constraints where constraint_name='TSATT_F2';
# alter table TSATT modify constraint TSATT_F2 disable;

RMAN> unregister database;

database name is "YYYYYY" and DBID is 688900000

Do you really want to unregister the database (enter YES or NO)? YES
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: 
ORA-02292: integrity constraint (RMAN.TSATT_F3) violated - child record found

# here from sqlplus I disabled RMAN.TSATT_F3
# alter table rman.TSATT modify constraint TSATT_F3 disable;

RMAN> unregister database;

database name is "YYYYYY" and DBID is 688900000

Do you really want to unregister the database (enter YES or NO)? YES
database unregistered from the recovery catalog

# here I found the orphaned rows in the TSATT table and delete them - according to the expectations 
# these were the rows making both the constraints unable to be enabled
# after the deletion I enabled the constraints disabled previously
# delete from RMAN.TSATT T where not exists (select 1 from RMAN.CKP C where C.CKP_KEY=T.START_CKP_KEY) ;
# delete from RMAN.TSATT T where not exists (select 1 from RMAN.CKP C where C.C3626503034KP_KEY=T.END_CKP_KEY) ;
# most possibly both queries above would touch the same rows

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

About patterns' recognition (external link)

A tutorial on the pattern recognition in the data series
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/misc/sql_pattern/sql_pattern.html

DB upgrade shortly

This is a very generic and concise description of the upgrade operation an Oracle RDBMS environment to higher version.

1. run the Pre-Upgrade Tool - one may think this is an optional step, yet it seems it is not. Once I tried to run the whole operation without an earlier call to it and here You may look at what may go wrong and how to deal with it.

   sh > sqlplus / as sysdba
   -- the Pre-Upgrade Tool
   -- one have to call it with the ORACLE_HOME set to current binaries 
   -- (in contrast to the new binaries i.e. after upgrade)
   -- there is no need to copy it to the current home, yet one have to provide a full path
   -- of course one may expect a change in the name according to the version 
   -- for example utlu121i for the version 12c
   sql> SPOOL preupg.log
   sql> @/rdbms/admin/utlu112i
   sql> SPOOL OFF
   

2. change the ORACLE_HOME to the new home (as it is really inadvisable to install the new binaries in-place) and run the catalog upgrade

   -- usually the docs say one should go to ORACLE_HOME/rdbms/admin
   -- before running the catalog upgrade and this is then specified without a path
   -- I used to run it as shown below
   sh > sqlplus / as sysdba
   sql> STARTUP UPGRADE
   sql> SPOOL db_upgrade.log
   sql> @?/rdbms/admin/catupgrd
   -- these steps below are actually not necessary or even impossible due to 
   -- the shutdown included in catupgrd.sql
   sql> SPOOL OFF
   sql> SHUTDOWN IMMEDIATE
   

3. now a normal start, some additional script and check if all seems to go well

   sh > sqlplus / as sysdba
   sql> STARTUP 
   sql> SPOOL postupg.log
   -- check if the upgrade went well
   sql> @?/rdbms/admin/utlu112s
   -- upgrade actions, which do not need UPGRADE mode
   sql> @?/rdbms/admin/catuppst.sql
   sql> SPOOL OFF
   -- compilation
   sql> @?/rdbms/admin/utlrp
   

That's all assuming everything went well ;-).