Thursday 20 March 2014

Database Upgrade from 11.2.0.3 to 12.1.0.1 using DBUA

I recently tested the upgrade of an Oracle 11.2.0.3 database to 12c Release 1 and here are the screen shots as well as some new things in found in the 12c Database Upgrade Assistant.
  • The DBUA GUI interface has now changed as compared to previous releases. It is like the OUI with split screen layout with tasks listed on the left of the screen and steps, details and progress on the right.
  • The  utlu121s.sql script has been replaced with the preupgrd.sql script. The new Pre-Upgrade Information Tool provides fix-up scripts with the ability to fix and address any issues identified by the pre-upgrade check script
  • The previous upgrade script catupgrd.sql has been replaced with the new catctl.pl Parallel Upgrade Utility script which provides the ability to run upgrade scripts in parallel taking advantage of spare CPU capacity which can potentially reduce upgrade times
  • If Flashback database is enabled, DBUA creates a guaranteed restore point which can be used in case we have a  failure in the upgrade process. We can also restart DBUA to recover from any failures during the upgrade.
  • We can now use an existing RMAN backup which DBUA is aware of in case we need to restore the database to the pre-12c upgrade state
  • Oracle XML DB is now a mandatory component of the Oracle 12c database and is automatically installed as part of the upgrade process if not found
  • If Database Control from earlier release exists, we need to drop the same before upgrading the database. We can do this via DBUA or reduce the upgrade downtime by performing this task in advance by copying a new script emremove.sql from the 12c Oracle Hone and running it against the 11g database.

Posted by at No comments:

UTL_DBWS - Consuming Web Services in Oracle 10g - 11g

Using the UTL_DBWS package is essentially a PL/SQL wrapper over JPublisher. ahmed hazzaf

First, download the latest copy of the dbwsclient.jar file:
Extract the jar file from the zip file into the "$ORACLE_HOME/sqlj/lib" directory.
The jar file can be loaded into the SYS schema for everyone to access, or into an individual schema that needs access to the web client. To make sure you avoid errors during the load, set theJAVA_POOL_SIZE initialization parameter to at least 150M.
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1

# Load into the SYS schema.
export PATH=$ORACLE_HOME/bin:$PATH
cd $ORACLE_HOME/sqlj/lib
# 10gR2
loadjava -u sys/password -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb102.jar
# 11g
loadjava -u sys/password -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb11.jar

# Load into an individual schema.
export PATH=$ORACLE_HOME/bin:$PATH
cd $ORACLE_HOME/sqlj/lib
# 10gR2
loadjava -u scott/tiger -r -v -f -genmissing dbwsclientws.jar dbwsclientdb102.jar
# 11g
loadjava -u scott/tiger -r -v -f -genmissing dbwsclientws.jar dbwsclientdb11.jar
In Oracle 10g the UTL_DBWS package is loaded by default. In Oracle 9i and 11g the package must be loaded using the specification and body provided in the zip file. The execute privilege should be granted on the ULT_DBWS package to any users needing access to the functionality.
$ cd $ORACLE_HOME/sqlj/lib
$ sqlplus / as sysdba

SQL> @utl_dbws_decl
SQL> @utl_dbws_body

SQL> CREATE PUBLIC SYNONYM utl_dbws FOR sys.utl_dbws;
SQL> GRANT EXECUTE ON sys.utl_dbws TO test;
The function below uses the UTL_DBWS package to access a web services from PL/SQL. The URL of the WDSL file describing the web service is shown here (http://www.oracle-base.com/webservices/server.php?wsdl). The web service accepts two number parameters and returns the sum of those values.
CREATE OR REPLACE FUNCTION add_numbers (p_int_1 IN NUMBER,
                                        p_int_2 IN NUMBER)
  RETURN NUMBER
AS
  l_service          UTL_DBWS.service;
  l_call             UTL_DBWS.call;
  
  l_wsdl_url         VARCHAR2(32767);
  l_namespace        VARCHAR2(32767);
  l_service_qname    UTL_DBWS.qname;
  l_port_qname       UTL_DBWS.qname;
  l_operation_qname  UTL_DBWS.qname;

  l_xmltype_in       SYS.XMLTYPE;
  l_xmltype_out      SYS.XMLTYPE;
  l_return           NUMBER;
BEGIN
  l_wsdl_url        := 'http://www.oracle-base.com/webservices/server.php?wsdl';
  l_namespace       := 'http://www.oracle-base.com/webservices/';

  l_service_qname   := UTL_DBWS.to_qname(l_namespace, 'Calculator');
  l_port_qname      := UTL_DBWS.to_qname(l_namespace, 'CalculatorPort');
  l_operation_qname := UTL_DBWS.to_qname(l_namespace, 'ws_add');

  l_service := UTL_DBWS.create_service (
    wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),
    service_name           => l_service_qname);

  l_call := UTL_DBWS.create_call (
    service_handle => l_service,
    port_name      => l_port_qname,
    operation_name => l_operation_qname);

  l_xmltype_in := SYS.XMLTYPE('
    
      ' || p_int_1 || '
      ' || p_int_2 || '
    ');
  l_xmltype_out := UTL_DBWS.invoke(call_Handle => l_call,
                                   request     => l_xmltype_in);
  
  UTL_DBWS.release_call (call_handle => l_call);
  UTL_DBWS.release_service (service_handle => l_service);

  l_return := l_xmltype_out.extract('//return/text()').getNumberVal();
  RETURN l_return;
END;
/
The output below shows the function in action.
SELECT add_numbers(1, 5) FROM dual;

ADD_NUMBERS(1,5)
----------------
               6

SQL>

SELECT add_numbers(10, 15) FROM dual;

ADD_NUMBERS(10,15)
------------------
                25

SQL>
Posted by at No comments:

Thursday 13 March 2014

Auditing SQL DML Changes In Oracle

We are often faced with a situation when every DML change (Inserts/Updates/Deletes) made in Oracle/SQL tables must be audited. Banking Softwares and other similar applications have a strict requirement to maintain the audit trail of every single change made to the database.
The DML changes must be audited irrespective of whether it was made from the Front End, during a release, or directly by a production support person while serving a production ticket. Ever wondered how an audit trail of such large numbers tables in your database can be created. Especially when your application is ever-changing with new columns getting added, dropped or modified often.
Triggers in oracle often come handy when fulfilling audit requirements for your database. An audit trigger can be created on the table which will compare the old and new values of all the columns and in case of a difference will log the old record into an audit table. The audit table will have a similar structure to the main table with 3 additional columns AUDIT_BY, AUDIT_AT and AUDIT_ACTION.
Triggers will ensure that the audit trail is maintained irrespective of from where the database change was initiated. However creating such large number of audit tables and triggers manually can be a huge effort. In this article I will demonstrate how easily we can create audit tables and triggers in oracle for database of any size very easily and with very less effort.

Step 1 – Create some tables

Create some sample tables for which you would like to maintain the audit trail.
CREATE TABLE EMPLOYEE
(
   EID     NUMBER,
   ENAME   VARCHAR2 (40)
);
CREATE TABLE DEPARTMENT
(
   DID     NUMBER,
   DNAME   VARCHAR2 (40)
);
CREATE TABLE SALARY
(
   EID      NUMBER,
   SALARY   NUMBER
);

Step 2 – Create an exclude table

There will be always some tables which we would like to exclude from the audit. For example if the table is very huge, contains blob or images, or if the table is rarely modified we might not want to audit it. The exclude table will contain a list of such table which we would like to exclude from the audit.
CREATE TABLE EXAUDIT
(
   TNAME VARCHAR2 (30) NOT NULL
);
In our example let us assume that we want to exclude the department table from the audit. We simply make an entry of this table in our exclude table.
INSERT INTO EXAUDIT (TNAME)
     VALUES ('DEPARTMENT');

Step 3 – Create audit tables

Now comes the interesting part. We want to create audit tables that will hold the audit trail of all the tables in our database. This can be achieved with a simple procedure like below.
CREATE OR REPLACE PROCEDURE create_audit_tables (table_owner VARCHAR2)
IS
   CURSOR c_tables (
      table_owner VARCHAR2)
   IS
SELECT ot.owner AS owner, ot.table_name AS table_name
        FROM all_tables ot
       WHERE     ot.owner = table_owner
             AND ot.table_name NOT LIKE 'AUDIT_%'
             AND ot.table_name <> 'EXAUDIT'
             AND NOT EXISTS
                    (SELECT 1
                       FROM EXAUDIT efa
                      WHERE ot.table_name = efa.tname)
             AND NOT EXISTS
                        (SELECT 1
                           FROM all_tables at
                          WHERE at.table_name = 'AUDIT_'||ot.table_name);
   v_sql     VARCHAR2 (8000);
   v_count   NUMBER := 0;
   v_aud     VARCHAR2 (30);
BEGIN
   FOR r_table IN c_tables (table_owner)
   LOOP
      BEGIN
         v_aud := 'AUDIT_'||r_table.table_name;
         v_sql :=
               'create table '
            || v_aud
            || ' as select * from '
            || r_table.owner
            || '.'
            || r_table.table_name
            || ' where 0 = 1';
         DBMS_OUTPUT.put_line ('Info: ' || v_sql);
         EXECUTE IMMEDIATE v_sql;
         v_sql :=
               'alter table '
            || v_aud
            || ' add ( AUDIT_ACTION char(1), AUDIT_BY varchar2(50), AUDIT_AT TIMESTAMP)';
         EXECUTE IMMEDIATE v_sql;
         v_count := c_tables%ROWCOUNT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
                  'Failed to create table '
               || v_aud
               || ' due to '
               || SQLERRM);
      END;
   END LOOP;
   IF v_count = 0
   THEN
      DBMS_OUTPUT.put_line ('No audit tables created');
   ELSE
      DBMS_OUTPUT.put_line (v_count || ' audit tables created.');
   END IF;
END;
/
After the above procedure is created execute it by passing the schema name (owner) of the schema where your main tables were created.
execute create_audit_tables('SCHEMANAME');
This will create audit tables corresponding to all main tables and with the additional columns like audit_on,audit_by and audit_action. The tables in the exclude table will be excluded.

Step 4 – Create audit triggers

I will first create a small helper function that will give me a comma separated list of columns of a given table (with a prefix if required)
create or replace FUNCTION get_columns_for_table (
     table_owner   VARCHAR2,
     t_name   VARCHAR2,
     prefix  VARCHAR2
  ) RETURN  CLOB
  IS
     v_text CLOB;
  BEGIN
     FOR getrec IN (SELECT column_name
                      FROM all_tab_columns
                     WHERE table_name = t_name
        AND owner = table_owner
        AND data_type<>'BLOB')
     LOOP
       v_text := v_text
          || ','
          || prefix
          || getrec.column_name
          || CHR (10)
          || '                             ';
     END LOOP;
     RETURN ltrim(v_text,',');
  END;
Next create a helper function that will give us a comparison between the columns in case of table updates
create or replace function get_column_comparasion (
     table_owner   VARCHAR2,
     t_name   VARCHAR2
  ) RETURN CLOB
  IS
    v_text CLOB;
  BEGIN
    FOR getrec IN (SELECT column_name
                     FROM all_tab_columns
                    WHERE table_name = t_name
                      AND owner = table_owner
                      AND data_type<>'BLOB')
   LOOP
      v_text := v_text
         || ' or( (:old.'
         || getrec.column_name
         || ' <> :new.'
         || getrec.column_name
         || ') or (:old.'
         || getrec.column_name
         || ' IS NULL and  :new.'
         || getrec.column_name
         || ' IS NOT NULL)  or (:old.'
         || getrec.column_name
         || ' IS NOT NULL and  :new.'
         || getrec.column_name
         || ' IS NULL))'
         || CHR (10)
         || '                ';
   END LOOP;
   v_text := LTRIM (v_text, ' or');
   RETURN v_text;
  END;
Next create the procedure that will create our audit triggers
CREATE OR REPLACE PROCEDURE create_audit_triggers (table_owner VARCHAR2)
IS
   CURSOR c_tab_inc (
      table_owner VARCHAR2)
   IS
      SELECT ot.owner AS owner, ot.table_name AS table_name
        FROM all_tables ot
       WHERE     ot.owner = table_owner
             AND ot.table_name NOT LIKE 'AUDIT_%'
             AND ot.table_name <> 'EXAUDIT'
             AND ot.table_name NOT IN (SELECT tname FROM EXAUDIT);
   v_query   VARCHAR2 (32767);
   v_count   NUMBER := 0;
BEGIN
   FOR r_tab_inc IN c_tab_inc (table_owner)
   LOOP
      BEGIN
         v_query :=
               'CREATE OR REPLACE TRIGGER TRIGGER_'
            || r_tab_inc.table_name
            || ' AFTER INSERT OR UPDATE OR DELETE ON '
            || r_tab_inc.owner
            || '.'
            || r_tab_inc.table_name
            || ' FOR EACH ROW'
            || CHR (10)
            || 'DECLARE '
            || CHR (10)
            || ' v_user varchar2(30):=null;'
            || CHR (10)
            || ' v_action varchar2(15);'
            || CHR (10)
            || 'BEGIN'
            || CHR (10)
            || '   SELECT SYS_CONTEXT (''USERENV'', ''session_user'') session_user'
            || CHR (10)
            || '   INTO v_user'
            || CHR (10)
            || '   FROM DUAL;'
            || CHR (10)
            || ' if inserting then '
            || CHR (10)
            || ' v_action:=''INSERT'';'
            || CHR (10)
            || '      insert into AUDIT_'
            || r_tab_inc.table_name
            || '('
            || get_columns_for_table (r_tab_inc.owner,
                                      r_tab_inc.table_name,
                                      NULL)
            || '      ,AUDIT_ACTION,AUDIT_BY,AUDIT_AT)'
            || CHR (10)
            || '      values ('
            || get_columns_for_table (r_tab_inc.owner,
                                      r_tab_inc.table_name,
                                      ':new.')
            || '      ,''I'',v_user,SYSDATE);'
            || CHR (10)
            || ' elsif updating then '
            || CHR (10)
            || ' v_action:=''UPDATE'';'
            || CHR (10)
            || '   if '
            || get_column_comparasion (r_tab_inc.owner, r_tab_inc.table_name)
            || ' then '
            || CHR (10)
            || '      insert into AUDIT_'
            || r_tab_inc.table_name
            || '('
            || get_columns_for_table (r_tab_inc.owner,
                                      r_tab_inc.table_name,
                                      NULL)
            || '      ,AUDIT_ACTION,AUDIT_BY,AUDIT_AT)'
            || CHR (10)
            || '      values ('
            || get_columns_for_table (r_tab_inc.owner,
                                      r_tab_inc.table_name,
                                      ':new.')
            || '      ,''U'',v_user,SYSDATE);'
            || CHR (10)
            || '   end if;'
            || ' elsif deleting then'
            || CHR (10)
            || ' v_action:=''DELETING'';'
            || CHR (10)
            || '      insert into AUDIT_'
            || r_tab_inc.table_name
            || '('
            || get_columns_for_table (r_tab_inc.owner,
                                      r_tab_inc.table_name,
                                      NULL)
            || '      ,AUDIT_ACTION,AUDIT_BY,AUDIT_AT)'
            || CHR (10)
            || '      values ('
            || get_columns_for_table (r_tab_inc.owner,
                                      r_tab_inc.table_name,
                                      ':old.')
            || '      ,''D'',v_user,SYSDATE);'
            || CHR (10)
            || '   end if;'
            || CHR (10)
            || 'END;';
         DBMS_OUTPUT.put_line (
               'CREATE TRIGGER '
            || REPLACE (r_tab_inc.table_name, 'TABLE_', 'TRIGGER_'));
         EXECUTE IMMEDIATE v_query;
         DBMS_OUTPUT.put_line (
               'Audit trigger '
            || REPLACE (r_tab_inc.table_name, 'TABLE_', 'TRIGGER_')
            || ' created.');
         v_count := c_tab_inc%ROWCOUNT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
                  'Failed to create audit trigger for '
               || r_tab_inc.owner
               || '.'
               || r_tab_inc.table_name
               || ' due to '
               || SQLERRM);
      END;
   END LOOP;
   IF v_count = 0
   THEN
      DBMS_OUTPUT.put_line ('No audit triggers created');
   END IF;
END;
Finally execute the procedure. This will create all the audit triggers.
EXECUTE CREATE_AUDIT_TRIGGERS('SCHEMANAME');

Step 5 – Test the auditing

Now execute a few DML scripts and notice that all changes made to our main tables get audited with appropriate action in the audit tables. Changes to department table will not be audited as we have excluded it.
insert into employee values(1,'John');
insert into employee values(2,'Smith');
insert into department values(1,'Sales');
insert into department values(2,'Purchase');
insert into salary values(1,5000);
insert into salary values(2,10000);
delete from employee where eid = 1;
update employee set ename = 'Raj' where eid = 2;
All tables will have a primary key which never changes. Using the primary key we can query our audit tables and get the entire audit trail when required. Instead of session user we can also set the user from the middle tier in the SYS_CONTEXT.
Here I demonstrated how with few simple procedures you can fulfil the audit requirement of your application. The concepts and scripts here are very small but quite powerful and can be used to create audit trail for any number of tables in your database. ahmed hazzaf
Posted by at No comments:
Subscribe to: Posts (Atom)