Purging OWB audit records is easy

August 13, 2009

The following is the PL/SQL package that I discussed in this blog: Purge audit records in OWB at ease. Since I could not post the whole program or include it as an attachment there, I am posting it here. Enjoy it.



/* $Header: owb_purge_audit.sql 1.001 2009/03/25 12:10:10 cc$

Copyright (c) 2008 Octagon Research Solutions.  
All Rights Reserved.

PURPOSE
  This procedure calls to wb_rt_api_purge to purge execution and/or deployment
  audit records

NOTES
  1. If it is installed in a none repository account, it requires 
     'select any table' system previlege. Logging as sysdba, then grant:
     
     grant select any table to owb_admin;    
     grant  owb_d_{owb_rep}, owb_o_{owb_rep} to owb_admin;
     
  2. If run this from different schema, you need to set the schema and roles
     (I found out that this is not needed)
     -- create synonym wb_rt_api_purge as {owb_rep}.wb_rt_api_purge; 
     -- alter session set current_schema = {owb_rep};
     -- set role owb_d_{owb_rep}, owb_o_{owb_rep};     

HISTORY   MM/DD/YYYY (developer) 
  03/25/2009 (htu) - initial creation based on purge_audit_template.sql
  03/26/2009 (htu) - added p_keep_days, p_act, and p_lvl
  04/04/2009 (htu) - fixed the l_sel in step 4 did not get p_rep_owner
                     added the v_own to the API call as well.
  04/06/2009 (htu) - added p_remove_days 
*/


rem SYNOPSYS
rem
rem   @purge_audit_template.sql rt_owner {DEPLOYMENT | EXECUTION} {sql_predicate | ALL | DATE_RANGE} [start_date end_date]
rem
rem NAME
rem
rem   purge_audit_template.sql - SQLPlus Purge Audit Data Template
rem
rem USAGE
rem
rem   rt_owner       := e.g. MY_RUNTIME                     - Name of the Runtime Repository Owner
rem
rem   audit_type     :- DEPLOYMENT                          - Deployment Audit Data
rem                  |  EXECUTION                           - Execution Audit Data
rem
rem   sql_predicate  :- e.g. "number_script_run_errors > 0" - Selection Criteria e.g. deployment errors
rem                  |  ALL                                 - Purge all deployment or execution audit data
rem                  |  DATE_RANGE                          - Purge audit data within given Date Range
rem
rem   start_date     :- e.g. TO_DATE('01-Jan-2003')         - Start of Date Range
rem
rem   end_date       :- e.g. SYSDATE                        - End of Date  Range
rem
rem RETURNS
rem
rem   none
rem
rem DESCRIPTION
rem
rem   This SQL*Plus script can be called from the SQL*Plus shell.
rem
rem   This script is design to be run from a Runtime User that has the Admin role, not the Runtime Repository Owner.
rem   The Runtime Repository Owner is nominated in the parameters.
rem
rem   It is designed to purge either Deployment Audit Data or Execution Audit Data.
rem
rem   A selection mode parameter is provided to enable the purge to be selective
rem   using a predicate or a date range.
rem
rem   The SQL predicate parameter, if supplied, must conform to the SQL syntax rules for "condition".
rem   Columns available for Deployment predicate are given by ALL_RT_AUDIT_DEPLOYMENTS view.
rem   Columns available for Execution predicate are given by ALL_RT_AUDIT_EXECUTIONS view.
rem
rem   The start date and end date parameters, if supplied, must conform to the SQL syntax rules for "date".
rem
rem EXAMPLE
rem
rem   sqlplus user/password@tns_name @purge_audit_template.sql MY_RUNTIME DEPLOYMENT ALL null null
rem   sqlplus user/password@tns_name @purge_audit_template.sql MY_RUNTIME EXECUTION "number_task_errors > 0" null null
rem   sqlplus user/password@tns_name @purge_audit_template.sql MY_RUNTIME EXECUTION DATE_RANGE TO_DATE('01-Jan-2004') SYSDATE
rem
--

/*
set serveroutput on
set verify off

whenever sqlerror exit failure;

--define REPOS_OWNER=&1
--define AUDIT_TYPE=&2
--define SQL_PREDICATE=&3
--define START_DATE=&4
--define END_DATE=&5
define DATE_MASK = "DD-Mon-YYYY HH12-MI-SSAM" (CHAR) -- Date format used in output message

alter session set current_schema = &1;
set role owb_d_&1, owb_o_&1;

alter session set current_schema = owb_rep2
set role owb_d_owb_rep2, owb_o_owb_rep2
*/


CREATE OR REPLACE PROCEDURE owb_purge_audit (
    p_rep_owner		VARCHAR2 DEFAULT USER		-- rep owner
  , p_audit_type	VARCHAR2 DEFAULT 'execution'	
  , p_sql_predicate	VARCHAR2 DEFAULT 'date_range'
  , p_start_dt		DATE	 DEFAULT NULL
  , p_end_dt		DATE  	 DEFAULT NULL
  , p_remove_days	NUMBER	 DEFAULT NULL		-- default 7 days
  , p_keep_days		NUMBER 	 DEFAULT NULL 		-- default 120 days
  , p_act		NUMBER   DEFAULT 0		-- action:0-test;1-exec
  , p_lvl		NUMBER	 DEFAULT 0
) 
  AUTHID CURRENT_USER    -- using invoker's privillege
AS
  l_valid      	boolean;       	-- true if valid input params, false if invalid
  l_deployment 	boolean;        -- true if DEPLOYMENT, false if EXECUTION
  l_where      	boolean;        -- true if criteria, false if ALL or DATE_RANGE
  l_dates      	boolean;        -- true if DATE_RANGE, false if otherwise
  l_audit_type 	varchar2(12);   -- DEPLOYMENT or EXECUTION
  l_criteria   	varchar2(2000); -- Selection criteria
  l_start      	date;           -- Start of date range
  l_end        	date;           -- End of date range
  l_keep_days	number;		-- number of keep days
  l_remove_days	number;		-- number of remove days
  l_sel     	varchar2(2200); -- Select statement
  l_del     	varchar2(2000); -- Delete statement
  type 		t_cursor is ref cursor;
  l_cursor      t_cursor;       -- Cursor for selected audit data
  l_audit_id    number;         -- Audit Id to be purged
  l_count       number;         -- Number of Audit Details purged
  v_msg		varchar2(2000);	-- message
  v_sql		varchar2(2000); 
  v_dt		date;		-- date variable
  v_dft		varchar2(100)	:= 'yyyymmdd.hh24miss';
  v_src		varchar2(100);	-- audit table name
  v_own		varchar2(100); 	-- repository owner
  v_tab		varchar2(100);  -- table name
  v_api		varchar2(200);	-- wb rt api program
  cr     	CHAR(1) 	:= CHR(10);   	-- new line  
  c1		t_cursor;

  PROCEDURE echo (
    msg clob,
    lvl NUMBER DEFAULT 999
  ) IS
  BEGIN
    IF lvl  l_end THEN
      l_valid := false;
      v_msg := '    '||to_char(l_start, v_dft)||' > '||to_char(l_end,v_dft);
      v_msg := v_msg||': skipped due to start date greater then end date.';
      echo(v_msg,0);
      RETURN;
    END IF;
    -- exit if end date is greater then keep date
    v_dt := sysdate - l_keep_days; 
    IF l_end > v_dt THEN
      l_valid := false;
      v_msg := '    '||to_char(l_end, v_dft)||' > '||to_char(v_dt,v_dft);
      v_msg := v_msg||': skipped due to end date greater then ';
      v_msg := v_msg||to_char(l_keep_days)||' keep days of current dt ';
      v_msg := v_msg||to_char(sysdate,v_dft)||'.';
      echo(v_msg,0);
      RETURN;
    END IF;
  ELSE
    l_where := true;
    l_dates := false;
  END IF;

  -- exit if no valid action
  IF NOT l_valid THEN
    echo('    No valid input: all skipped', 0);
    RETURN;
  END IF;

  IF l_criteria = 'ALL' AND l_keep_days > 0 THEN
    v_msg := '    You can not purge all while you want to keep ';
    v_msg := v_msg||to_char(l_keep_days)||' days of records.'; 
    echo(v_msg, 0);
    RETURN;
  END IF;

  -- 3. get number of records to be purged
  v_msg := ' 3. get record count...';
  echo(v_msg, 1); 
  IF l_dates THEN
  -- Perform Purge for Date Range
    l_sel := '    SELECT count(*) FROM '||v_src||cr;
    l_sel := l_sel||'     WHERE created_on BETWEEN :1 AND :2';
    echo(l_sel, 3); 
    EXECUTE IMMEDIATE l_sel INTO l_count USING IN l_start, IN l_end;
    v_msg := '    '||to_char(l_count)||' '||l_audit_type;
    v_msg := v_msg||' audit details will be purged between ';
    v_msg := v_msg||to_char(l_start,v_dft)||' and '||to_char(l_end,v_dft);
  ELSIF l_where THEN
  -- Initialise SQL Statements for Predicate
    l_sel := '    SELECT count(*) FROM '||v_src||cr;
    l_sel := l_sel||'     WHERE '||l_criteria;
    echo(l_sel, 3);     
    EXECUTE IMMEDIATE l_sel INTO l_count;
    v_msg := '    '||to_char(l_count)||' '||l_audit_type;
    v_msg := v_msg||' audit details will be purged where '||l_criteria;
  ELSE
    l_sel := '    SELECT count(*) FROM '||v_src;
    echo(l_sel, 3);     
    EXECUTE IMMEDIATE l_sel INTO l_count;
    v_msg := '    '||to_char(l_count)||' '||l_audit_type;
    v_msg := v_msg||' audit details will be purged.';
  END IF;
  echo(v_msg, 1);
  
  -- 4. run the purge procedure
  v_msg := ' 4. run the purge procedure...';
  echo(v_msg, 1); 
  IF l_deployment THEN
    v_api := v_own||'.wb_rt_api_purge.purge_deployment'; 
  ELSE
    v_api := v_own||'.wb_rt_api_purge.purge_execution'; 
  END IF;
  echo('    '||v_api, 2); 
  IF l_dates THEN
  -- Perform Purge for Date Range
    l_del := 'CALL '||v_api||'(:1, :2)';
    IF p_act = 1 THEN
      EXECUTE IMMEDIATE l_del USING IN l_start, IN l_end;
    END IF;
  ELSIF l_where THEN
  -- Initialise SQL Statements for Predicate
    l_del := 'CALL '||v_api||'(:1)';
    l_sel := 'SELECT deployment_audit_id FROM '||v_src;
    l_sel := l_sel || ' WHERE ' || l_criteria;
    echo(l_sel, 3);
    IF p_act = 1 THEN
      -- Perform Purge for Predicate
      OPEN l_cursor FOR l_sel;
      l_count := 0;
      LOOP
        FETCH l_cursor INTO l_audit_id;
        EXIT WHEN l_cursor%notfound;
        EXECUTE IMMEDIATE l_del USING IN l_audit_id;
        l_count := l_count + 1;
      END LOOP;
      CLOSE l_cursor;
    END IF;
  ELSE
    -- Perform Purge for all
    l_del := 'CALL '||v_api||'()';
    echo(l_sel, 3);
    IF p_act = 1 THEN 
      EXECUTE IMMEDIATE l_del;
    END IF;
  END IF;
  IF p_act = 1 THEN
    v_msg := '    '||l_audit_type||' AUDIT DETAILS PURGED = '||l_count;
  ELSE
    v_msg := '    '||l_audit_type||' records will be purged = '||l_count;
  END IF;
  echo(v_msg, 1); 
  
  -- 5. commit the result
  v_msg := ' 5. commit the result...';
  echo(v_msg, 1); 
 
  IF p_act = 1 THEN 
    COMMIT; 
    echo('    Commit: done.',2);
  ELSE
    echo('    Commit: skipped.',2);
  END IF;

  -- 6. gather stats for wb_rt_audit tables
  v_msg := ' 6. gather stats for audit tables...';
  echo(v_msg, 1); 

  l_sel :=        'SELECT table_name, num_rows FROM all_tables'||cr;
  l_sel := l_sel||' WHERE owner = :1 '||cr;
  l_sel := l_sel||'   AND table_name LIKE :2 '||cr;
  l_sel := l_sel||' ORDER BY table_name'||cr;
  
  OPEN c1 FOR l_sel USING v_own, 'WB_RT_AUDIT%';	
  LOOP
    FETCH c1 INTO v_tab, l_count;
    EXIT WHEN c1%NOTFOUND;
    v_msg := '    Table '||v_tab||' ('||to_char(l_count)||') ';
    IF p_act = 1 THEN
      DBMS_STATS.GATHER_TABLE_STATS(v_own,v_tab,CASCADE=>TRUE);
      v_msg := v_msg||' has been analyzed.';
    ELSE
      v_msg := v_msg||' would be analyzed.'; 
    END IF; 
    echo(v_msg, 3); 
  END LOOP;	
  CLOSE c1;

EXCEPTION
  WHEN OTHERS THEN
  BEGIN
    close l_cursor;
    EXCEPTION
      WHEN OTHERS THEN
        null;
  END;
  raise;

END;
/

show error

How to solve ORA-12631 (Username retrieval failed)

July 20, 2009

We need to provide access to Oracle database and start Oracle Warehouse Builder (OWB) from our internal network and outside world (the Internet) so we have created a private network called mynet.private. The OS is Window Server 2003 SP2 and Oracle DB 10g (10.2.0.3).  Oracle installation went very smoothly. The DB control worked fine.  Everything worked as expected only until I started configuring backups.

Since my backup needs to access remote servers so need to:

1. use a domain account (OracleServicesAdmin@mynet.private) to start services so that it can backup to remote servers

2. enable archive log mode

So I added OracleServicesAdmin@mynet.private to local admin group;  enabled the OracleServicesAdmin@mynet.private to have “Log on as a batch job” and “Log on as a service” user rights.

Once I enabled the Oracle server to start with OracleServicesAdmin@mynet.private, I found out that my database did not start up completely.  The window service for starting up database very fast, and the alert log shows :

starting up 1 shared server(s) …

Then do not go any further. The instance show the status is at “STARTED”.

idle> select status, database_status from v$instance;

STATUS       DATABASE_STATUS
———— —————–
STARTED         ACTIVE

I had to use SQLplus to manually start the database into mount and open:

alter database mount;

alter database open;

The database is up and running but I could not conduct backup.  The RMAN gives the following error:

C:\Oracle\sqls>rman target sys@owb1

Recovery Manager: Release 10.2.0.3.0 – Production on Mon Jul 20 14:12:15 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

target database Password:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12631: Username retrieval failed

I have the exactly environment in our production network, the backup works. The only difference is now that I have it in the mynet.private domain. After talking to our network administrators, they think that there is something to do with active directory and might be Oracle does not like the “.private” domain.  Later, I found out that it has to do with the format of domain account in starting Oracle services.

The solution is to change  “OracleServicesAdmin@mynet.private” into “mynet.private\OracleServicesAdmin” in the window services for starting listner and database.  After I did this, the service was able to start the database completely and I was able to log into RMAN to conduct backups.

Hello world!

February 19, 2009

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!


Follow

Get every new post delivered to your Inbox.