Friday, February 24, 2012

Compare data in two tables

Using a GROUP BY and some arithmetic it is possible to improve the performance of SQL comparing data of two tables. Even better, in Oracle this can be achieved with the PARTITION BY analytic function.
Below is an example comparing two tables (TAB1, TAB2) with two columns (col1 number, col2 varchar2) using a PARTITION BY analytic function:


create table tab1 (col1 number, col2 varchar2(3));
create table tab2 (col1 number, col2 varchar2(3));
 
insert into tab1 (col1, col2) values (3, 'ccc');
insert into tab1 (col1, col2) values (1, 'aaa');
insert into tab2 (col1, col2) values (1, 'aaa');
insert into tab2 (col1, col2) values (2, 'aaa');
 
select * from 
 -- list all results
 -- change to "select count(*) from" for a count of differences
(
   select rid, -- ROWID
   col1, -- column 1 of table to compare
   col2, -- column 2 of table to compare
   sum(summ) over (partition by col1, col2) summ
 -- must list all columns to compare in the PARTITION BY
   from
   (select 'In TAB1, NOT in TAB2' tab,
     rowid rid, -- need to alias ROWID
 -- list all/some columns of table to compare
     col1, -- column 1 of table to compare
     col2, -- column 2 of table to compare
 ------
     1 summ 
 -- summ is used for sum() operation,
 -- a matched row from other table will sum to 0
   from tab1
     UNION ALL 
 -- change to UNION for unique rows only
   select 'In TAB2, NOT in TAB1' tab,
     rowid rid, -- need to alias ROWID
 -- list all/some columns of table to compare
     col1, -- column 1 of table to compare
     col2, -- column 2 of table to compare
 ------
     -1 summ 
 -- summ used for sum() operation,
 -- a matched row from other table will sum to 0
   from tab2)
)
-- all the rows that didn't have a match have a non-zero sum()
where summ != 0
/   

RID                      COL1 COL       SUMM
------------------ ---------- --- ----------
AAAWK9AAGAAADNHAAB          2 aaa         -1
AAAWK8AAGAAADM/AAA          3 ccc          1

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     4 |   164 |     7  (15)| 00:00:01 |
|*  1 |  VIEW                 |      |     4 |   164 |     7  (15)| 00:00:01 |
|   2 |   WINDOW SORT         |      |     4 |   124 |     7  (15)| 00:00:01 |
|   3 |    VIEW               |      |     4 |   124 |     6   (0)| 00:00:01 |
|   4 |     UNION-ALL         |      |       |       |            |          |
|   5 |      TABLE ACCESS FULL| TAB1 |     2 |    56 |     3   (0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL| TAB2 |     2 |    56 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SUMM"<>0) 


Two full table scans and a SORT. Compare this to what traditionally people have used:

(
 select 'IN TAB1, NOT TAB2', col1, col2
   from tab1
   MINUS
 select 'IN TAB1, NOT TAB2', col1, col2
   from tab2
 )
 UNION ALL
 (
 select 'IN TAB2, NOT TAB1', col1, col2
   from tab2
   MINUS
 select 'IN TAB2, NOT TAB1', col1, col2
   from tab1
 )
 /  

'INTAB1,NOTTAB2'        COL1 COL
----------------- ---------- ---
IN TAB1, NOT TAB2          3 ccc
IN TAB2, NOT TAB1          2 aaa

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     4 |   128 |    12  (75)| 00:00:01 |
|   1 |  UNION-ALL           |      |       |       |            |          |
|   2 |   MINUS              |      |       |       |            |          |
|   3 |    SORT UNIQUE       |      |     2 |    32 |            |          |
|   4 |     TABLE ACCESS FULL| TAB1 |     2 |    32 |     3   (0)| 00:00:01 |
|   5 |    SORT UNIQUE       |      |     2 |    32 |            |          |
|   6 |     TABLE ACCESS FULL| TAB2 |     2 |    32 |     3   (0)| 00:00:01 |
|   7 |   MINUS              |      |       |       |            |          |
|   8 |    SORT UNIQUE       |      |     2 |    32 |            |          |
|   9 |     TABLE ACCESS FULL| TAB2 |     2 |    32 |     3   (0)| 00:00:01 |
|  10 |    SORT UNIQUE       |      |     2 |    32 |            |          |
|  11 |     TABLE ACCESS FULL| TAB1 |     2 |    32 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Four full table scans, four sorts. For a large table the PARTITION BY wins.

Thursday, February 23, 2012

SP2-1503: Unable to initialize Oracle call interface; SP2-0152: ORACLE may not be functioning properly


When running a OS script via a job on Enterprise Manager 11g the following error may occur when calling SQL*plus:

SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

The problem relates to the environment variable settings used by SQL*Plus, specifically the LD_LIBRARY_PATH variables.

There is a metalink note describing this error:
Sp2-1503 And Sp2-0152 When Running SQL Script Job From Grid Control [ID 1226083.1]

For a solution set the LD_LIBRARY_PATH (or LD_LIBRARY_PATH_64) variable after setting up the environment (for example after setting oraenv).

Example:

# setup the Oracle environment without being prompted

export ORACLE_SID=AAA
export ORAENV_ASK=NO
. /usr/local/bin/oraenv

#LD_LIBRARY_PATH
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}

#LD_LIBRARY_PATH_64
export LD_LIBRARY_PATH_64=${LD_LIBRARY_PATH}
If the above don't work also try LD_LIBRARY_PATH64.

On 32-bit Linux the lib directory under the ORACLE_HOME is $ORACLE_HOME/lib32 so set:
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib32:${LD_LIBRARY_PATH}

Monday, February 20, 2012

11g Password Expiry

Since 11gR1 Oracle have implemented good security practice by enforcing regular updating of passwords through a default password expiry of 180 days.  

11gR1 Parameters Used to Secure User Accounts 

11gR2 Parameters Used to Secure User Accounts 


As good as this sounds may spell a time bomb (sleeper) for some applications with hard coded passwords. For example Application Servers with no procedures in place for regular password changes. Some may not even deal with the grace period message warning of an impending lock out.

When logging into an account that has entered a GRACE period the following message is displayed

SQL> connect username/password
ERROR:
ORA-28002: the password will expire within 3 days

When logging into an account that has passed the EXPIRED period the following message is displayed

SQL> connect username
Enter password:
ERROR:
ORA-28001: the password has expired


Changing password for username
New password:
Retype new password:
Password changed
Connected.
SQL>

Finding user accounts that have expired or grace passwords:

select username, account_status, expiry_date from dba_users

USERNAME        ACCOUNT_STATUS                   EXPIRY_DATE
------------------------------ --------------------------------
USER1           EXPIRED                          11-JAN-12
USER2           EXPIRED(GRACE)                   01-FEB-12
USER3           EXPIRED(GRACE)                   02-FEB-12

The defaults are defined in the user's PROFILE. See these default LIMIT column values:

select * from dba_profiles where resource_name in ('PASSWORD_LIFE_TIME','PASSWORD_GRACE_TIME');

PROFILE       RESOURCE_NAME                    RESOURCE LIMIT
------------- -------------------------------- --------------
DEFAULT       PASSWORD_LIFE_TIME               PASSWORD 180
DEFAULT       PASSWORD_GRACE_TIME              PASSWORD 7

To change the default profile settings (not recommended):

alter profile default limit password_life_time unlimited;
alter profile default limit password_grace_time unlimited;

To un-expire an already expired where the password is not known in 11g+ use the following:

alter user identified by values ‘FD8C3D14F6B60015’;

As of 11g the DBA_USERS table no longer contains encrypted passwords. Use the SYS.USER$ table and the NAME, PASSWORD columns. 

select name, password, ltime from user$ order by name, exptime;

Verify the account status in the DBA_USERS table:

select username, account_status, expiry_date from dba_users where expiry_date is null;

USERNAME            ACCOUNT_STATUS                   EXPIRY_DATE
------------------- -------------------------------- -----------
USER1               OPEN
USER2               OPEN
USER3               OPEN

Friday, February 17, 2012

Faster access to DUAL

Since Oracle 10g there is a quicker way to access the DUAL table. Many applications use this table such as a "ping" from an application server. Below demonstrates how to SELECT from it over the "old" DUAL table. The difference is 0 consistent gets over 3 consistent gets using the old method. This may not sound like much but adds up for an application executing thousands of these requests in a short span of time.

In order to use FAST DUAL avoid using "SELECT * FROM DUAL", if possible use "SELECT 1 FROM DUAL".

Example:

SQL> set autotrace traceonly timing on
SQL> select * from dual;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
407 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select 1 from dual;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

----------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
----------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
404 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select * from dual;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
407 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select 1 from dual;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

----------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
----------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
404 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


Some more information on DUAL:

http://www.orafaq.com/wiki/Dual

Oracle Clusterware Log Files (11.2)

Investigating a RAC error can be a minefield when it comes to log files. Below are a list of Clusterware log files and their associated process that can be used to diagnose/resolve errors.

A good MOS (My Oracle Support) note for diagnosing Grid Infrastructure (Clusterware) issues on startup:
Troubleshoot Grid Infrastructure Startup Issues [ID 1050908.1]

For the below log file names server hostname is "myserver01" and Grid Infrastructure is installed under /app/11.2.0/grid/ (UNIX).

Summary of all Clusterware events on this host
$GI_HOME/log/myserver01/alertmyserver01.log

Oracle High Availability Services Daemon OUT
$GI_HOME/log/myserver01/ohasd/ohasdOUT.log

Oracle High Availability Services Daemon
$GI_HOME/log/myserver01/ohasd/ohasd.log

Cluster Ready Services OUT
$GI_HOME/log/myserver01/crsd/crsdOUT.log

Cluster Ready Services Daemon
$GI_HOME/log/myserver01/crsd/crsd.log

Cluster Synchronisation Service OUT
$GI_HOME/log/myserver01/cssd/cssdOUT.log

Cluster Synchronisation Service Daemon
$GI_HOME/log/myserver01/cssd/ocssd.log

Alert log for ASM (ADR - Automatic Diagnostic Repository = /app/oracle/diag/)
/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log

Cluster Ready Services Monitor Agent
$GI_HOME/log/myserver01/agent/crsd/oraagent_grid/oraagent_grid.log

Cluster Ready Services Root Agent
$GI_HOME/log/myserver01/agent/crsd/orarootagent_root/orarootagent_root.log

Oracle High Availability Services Monitor Agent
$GI_HOME/log/myserver01/agent/ohasd/oraagent_grid/oraagent_grid.log

Oracle High Availability Services Root Agent
$GI_HOME/log/myserver01/agent/ohasd/orarootagent_root/orarootagent_root.log

Cluster Synchronisation Service Root Agent (under OHAS)
$GI_HOME/log/myserver01/agent/ohasd/oracssdagent_root/oracssdagent_root.log

Cluster Synchronisation Service Monitor Agent (under OHAS)
$GI_HOME/log/myserver01/agent/ohasd/oracssdmonitor_root/oracssdmonitor_root.log

In addition to the above I recently found this page in the documentation:

  • Clusterware Log Files and the Unified Log Directory Structure

  • Tuesday, February 7, 2012

    "ORA-12502: TNS:listener received no CONNECT_DATA" when connecting to a SCAN address.



    To resolve this error when a SCAN is in use ensure that all servers in the cluster are resolvable by all clients. These include the host VIPs and real IP of all the servers in the RAC configuration. For example, if you have a two node cluster and add a third node, all clients must be able to resolve the third node's VIP addresses and real IP not only the SCAN. The resolution should be in DNS from the client machine, if for some reason that cannot be achieved then try using the HOSTS file. Of course a SCAN cannot work using a HOSTS file.

    Example:

    It appears the SCAN is setup OK:

    >tnsping mydb_cluster

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mydb-scan.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYDB_CLUSTER)))
    OK (20 msec)


    When connecting via SQL*plus:

    >sqlplus someuser@mydb_cluster

    SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 7 10:59:24 2012

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

    Enter password:
    ERROR:
    ORA-12502: TNS:listener received no CONNECT_DATA from client


    To verify this is the issue eliminate the SCAN and try connect to the database using the VIP address:

    >tnsping mydb

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mydbhost01-vip)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = mydbhost02-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYDB)))
    OK (30 msec)



    >sqlplus someuser@mydb

    ERROR:
    ORA-12545: Connect failed because target host or object does not exist


    In this case to resolve ensure the VIP and HOST addresses (host01-vip, host02-vip, host01 and host02) are resolvable via DNS on the client machine.

    Even if we're using the SCAN and add a new host to the cluster, as true as it may be the TNSNAMES.ORA doesn't need updating when using a SCAN the new hosts must still be resolvable by all clients!