Thursday, October 4, 2012

ORA-03135: connection lost contact

A firewall timeout may cause an idle session to receive an "ORA-03135: connection lost contact" when running an SQL.
SQL> select to_char(sysdate,'DD.MON.YYYY HH24:MI:SS') from dual

TO_CHAR(SYSDATE,'DD.MON.YYYYH
-----------------------------
28.SEP.2012 10:52:01

Elapsed: 00:00:00.01
(Delay for 35+ minutes)
SQL> r
  1* select to_char(sysdate,'DD.MON.YYYY HH24:MI:SS') from dual
select to_char(sysdate,'DD.MON.YYYY HH24:MI:SS') from dual
*
ERROR at line 1:
ORA-03135: connection lost contact


Elapsed: 00:05:22.63
Note the elapsed time of 5 mintes 22 seconds.

To debug enable a client side SQL*net trace and reproduce the error. In this case after several tests the error occured after 30 minutes.

Oracle 11.1+ database versions mask the "ORA-03135" error and return a result after the 5 minutes which may lead the DBA down the wrong path believing there is a problem with the database.

For 11g the DIAG_ADR_ENABLED=off must be set otherwise trace files will go to the DIAGNOSTICS destination.

To not interfer with other users create the SQLNET.ORA and TNSNAMES.ORA in your own directory and use them by setting the TNS_ADMIN variable.

SQLNET.ORA


TNSPING.TRACE_LEVEL=admin
TNSPING.TRACE_DIRECTORY=/home/oracle/dba/user1/tns_admin/trace/
TRACE_LEVEL_CLIENT=admin
TRACE_DIRECTORY_CLIENT=/home/oracle/dba/user1/tns_admin/trace/
TRACE_UNIQUE_CLIENT=on
TRACE_TIMESTAMP_CLIENT=on
TRACE_TIMESTAMP_SERVER=true
TRACE_FILE_CLIENT=client_trace
DIAG_ADR_ENABLED=off
Information gathered in the trace file:
[28-SEP-2012 11:29:21:622] nioqsm: send-break: failed to send oob break...
[28-SEP-2012 11:29:21:622] nioqper:  error from send-marker
[28-SEP-2012 11:29:21:622] nioqper:    ns main err code: 12547
[28-SEP-2012 11:29:21:622] nioqper:    ns (2)  err code: 12560
[28-SEP-2012 11:29:21:622] nioqper:    nt main err code: 517
[28-SEP-2012 11:29:21:622] nioqper:    nt (2)  err code: 32
[28-SEP-2012 11:29:21:622] nioqper:    nt OS   err code: 0
[28-SEP-2012 11:29:21:622] nioqsm: exit
[28-SEP-2012 11:29:21:622] nioqer: entry
[28-SEP-2012 11:29:21:622] nioqce: entry
[28-SEP-2012 11:29:21:622] nioqce: exit
[28-SEP-2012 11:29:21:623] nioqer: exit
[28-SEP-2012 11:29:21:623] nioqrs: nioqrs: Couldn't send break. returning 3135
The trace file data above matches what is in MOS note:
  • ORA-3135 with Recovery Catalog Creation Across the Network (Firewall included) [ID 805088.1]
    "where nt[1]=32 is Operating System Dependent(OSD) error code.
    
    OSD error is Err#32 Broken pipe. This OSD error is also defined in errno.h:
    * #define EPIPE 32 /* Broken pipe */"
    
    Resolution was to set an SQLNET.EXPIRE_TIME=15 in the database product $ORACLE_HOME/network/admin/sqlnet.ora (choose 15 minutes because it was less than the 30 minutes the firewall was closing the session).

    Other references

  • Troubleshooting ORA-3135 Connection Lost Contact [ID 787354.1]
  • Friday, September 28, 2012

    Copying files to ASM using ASMCMD

    When manually copying a file from the filesystem to ASM using ASMCMD the file will be placed under a generic ASM directory regardless of specifying the destination. Below example a file is copied to +DATA/mynewdb1/ however file this creates an alias to +DATA/ASM/DATAFILE/
    ASMCMD> cp /app/oracle/backup/mydb1/DF.2105.2105.784545123 +DATA/mynewdb1/datafile/DF.2105
    copying /app/oracle/backup/mydb1/DF.2105.2105.784545123 -> +DATA/mynewdb1/datafile/DF.2105
    ASMCMD> cd +DATA/mynewdb1/datafile/
    ASMCMD> ls -al
    WARNING:option 'a' is deprecated for 'ls'
    please use 'absolutepath'
    Type Redund Striped Time Sys Name
    ...
    N DF.2105 => +DATA/ASM/DATAFILE/DF.2105.401.784554241
    ...
    ASMCMD> pwd
    +DATA/mynewdb1/datafile
    
    According to Oracle this is expected behaviour.

    However files transferred to ASM via the database ONNN background process set the context to which to store the files and are placed in the "correct" location. For example using RMAN connected to the target database:

    RMAN> restore controlfile from '/app/oracle/backup/db1/Current.312.769340147';
    Starting restore at 23-SEP-12
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=242 instance=MYNEWDB1 device type=DISK
    channel ORA_DISK_1: copied control file copy
    output file name=+DATA/mynewdb1/control01.ctl
    output file name=+DATA/mynewdb1/control02.ctl
    Finished restore at 23-SEP-12
    RMAN>
    
    
    To copy a file without it ending up being an alias to the generic location use RMAN which will communicate with ASM via the ONNN process and place it in the appropriate location for the database.

  • 11.2 Background Processes "Onnn, ASM Connection Pool Process, Maintains a connection to the ASM instance for metadata operations, Onnn slave processes are spawned on demand. These processes communicate with the ASM instance."

    On a semi-related note, why did Oracle consider "ls -absolutepath" was better than "ls -al"? "ls -al" had fewer keystrokes.

    ASMCMD> ls -al
    WARNING:option 'a' is deprecated for 'ls'
    please use 'absolutepath'
    
  • Saturday, September 1, 2012

    ORA-27504: IPC error creating OSD context, ORA-27302: failure occurred at: skgxpcini3

    CRS-5017: The resource action "ora.mydb.db start" encountered the following error:
    ORA-27504: IPC error creating OSD context
    ORA-27300: OS system dependent operation:check if failed with status: 0
    ORA-27301: OS failure message: Error 0
    ORA-27302: failure occurred at: skgxpcini3
    ORA-27303: additional information: requested interface vnet5:1 interface not up
     _disable_interface_checking = TRUE to disable this check for single instance cluster. 
    Check output from ifconfig co
    
    Alert log error for instance:
    SKGXP: ospid 10823: network interface with IP address 169.254.20.147 is DOWN
    
    The error message mentions network interface vnet5:1 In this case vnet5 and vnet6 are private interconnects using HAIP hence the 169.254.x.x address (Link Local Address). On SERVER01, vnet5 is UP, no 169. address bound to it:
    vnet5: flags=1000843 mtu 9000 index 6
            inet 10.0.84.194 netmask fffffff0 broadcast 10.0.84.207
    
    On SERVER02, vnet5 not UP, has 169.254.20.147 bound to both vnet5 and vnet6!
    vnet5: flags=1000842 mtu 9000 index 8
            inet 10.0.84.195 netmask fffffff0 broadcast 10.0.84.207
    vnet5:1: flags=1000842 mtu 9000 index 8
            inet 169.254.20.147 netmask ffff8000 broadcast 169.254.127.255
    vnet6: flags=1000843 mtu 9000 index 7
            inet 10.0.84.211 netmask fffffff0 broadcast 10.0.84.223
    vnet6:1: flags=1000843 mtu 9000 index 7
            inet 169.254.164.136 netmask ffff8000 broadcast 169.254.255.255
    vnet6:2: flags=1000843 mtu 9000 index 7
            inet 169.254.20.147 netmask ffff8000 broadcast 169.254.127.255
    
    Fix was to reboot SERVER02 as it turned out the UNIX administrator had performed some work on the blades concerning MAC addresses for vnet5. Somewhere vnet5 and vnet6 got the link-local address mixed up.

    ORA-27102: out of memory

    The below shows an "ORA-27102 out of memory" error on startup for an Oracle RAC database however this error can occur in single instances also.
    $ srvctl start database -d MYDB
    PRCR-1079 : Failed to start resource ora.MYDB.db
    CRS-5017: The resource action "ora.MYDB.db start" encountered the following error:
    ORA-27102: out of memory
    SVR4 Error: 22: Invalid argument
    . For details refer to "(:CLSN00107:)" in 
    "/app/11.2.0/grid/log/myserver01/agent/crsd/oraagent_oracle/oraagent_oracle.log".
    
    In alert log:
    WARNING: The system does not seem to be configured
    optimally. Creating a segment of size 0x000000009b000000
    failed. Please change the shm parameters so that
    a segment can be created for this size. While this is
    not a fatal issue, creating one segment may improve
    performance
    
    9b000000 hex = 2400M = MEMORY_TARGET and MEMORY_MAX_TARGET settings for this particular instance. Check the shared memory max in the project. Databases are being started by the 'grid' user for RAC however single instances will usually be 'oracle':
    > projects -l
    
    user.oracle
    ...
                     project.max-shm-memory=(priv,4294967295,deny)
    user.grid
    ...
                     project.max-shm-memory=(priv,4294967295,deny)
    
    Shared memory maximum allocated to the project is 4G. If the sum of all SGA/PGAs allocated for instances is more than 4G within the project an instance will encounter the error either on startup or even during operation. To fix either increase the maximum shared memory limit (OS resources permitting) or decrease the SGA/PGA in individual instances to under the limit. The following metalink note describes the problem and solutions: Database Startup On Solaris 10 Fails With Ora-27102 Out Of Memory Error [ID 399895.1]

    Friday, August 31, 2012

    “Create ASM Disk Group” no candidate disks to choose from

    A blank list of candidate disks to choose from in "Create ASM Disk Group":
    Check the path of the disks and permissions have been granted to them.
    From the Grid Infrastructure Installation Guide (Solaris 11gR2):
    # chown grid:asmadmin /dev/rdsk/cxtydzs4
    # chmod 660 /dev/rdsk/cxtydzs4
    Verify the setting with:
    # ls -lL /dev/rdsk/cxtydzs4
    
    Correct permissions:
    $ ls -lL /dev/rdsk/c0d[2345]s0
    crw-rw----   1 grid     asmadmin 117, 16 Aug 28 16:32 /dev/rdsk/c0d2s0
    crw-rw----   1 grid     asmadmin 117, 24 Aug 28 16:32 /dev/rdsk/c0d3s0
    crw-rw----   1 grid     asmadmin 117, 32 Aug 28 16:32 /dev/rdsk/c0d4s0
    crw-rw----   1 grid     asmadmin 117, 40 Aug 28 16:32 /dev/rdsk/c0d5s0
    
    Refresh list of candidate disks and they now appear.

    Tuesday, August 14, 2012

    DBA_LOGSTDBY_LOG, APPLIED column is 'CURRENT'

    Why does Logical Standby say an applied log is 'CURRENT' instead of 'YES' when the Standby is up to date? Reason is because there's at least one session on the Primary that hasn't yet committed or rolled back. Until a commit or rollback occurs on the Primary the Standby keeps the logs in a 'CURRENT' state and on the Standby server filesystem as they may still be required to apply transactions.

    (output below formatted to fit)

    column file_name heading 'Archive Log' format a80
    column applied heading 'Applied' format a30
    
    select file_name, sequence#, timestamp,
    decode(applied,'NO',applied||' - Not Applied Logs!','YES',applied||' - OK',applied) applied
    from dba_logstdby_log
    order by sequence#;
    
    Archive Log                                                   SEQUENCE# TIMESTAMP            Applied
    -------------------------------------------------------------------------------- ---------- -------------------- ------------------------------
    /oradata/MYDB/arch/logapplyarch/arch1_1247463_699468119.log    1247463 10.AUG.2012 16:16:33 CURRENT
    /oradata/MYDB/arch/logapplyarch/arch1_1247464_699468119.log    1247464 10.AUG.2012 16:17:04 CURRENT
    ...
    ...
    

    The 10.2 documentation doesn't give many details:

    "APPLIED, Indicates whether each archive log has been applied (YES) or not (NO)"

  • 10.2 DBA_LOGSTDBY_LOG Data Dictionary View
  • The 11.2 documentation has more details:

    "APPLIED, CURRENT - SQL Apply is currently applying changes contained in the foreign archived log"

  • 11.2 DBA_LOGSTDBY_LOG Data Dictionary View
  • "Since transactions can span multiple archived log files, multiple archived log files may show the value CURRENT in the APPLIED column."

  • 11.2 Data Guard Concepts and Administration - DBA_LOGSTDBY_LOG View
  • To resolve: On the Primary look for a session that has a lock requested around the time when the first (oldest) 'CURRENT' archive log appeared (16:16:33 in the above).

    
    set linesize 500
    
    column program format a40
    column event format a30
    column username format a15
    column osuser format a15
    column lock_requested format a30 heading 'Lock Requested'
    column object_name format a30
    
    select
            s.sid,
            s.serial#,
            s.username,
            s.osuser,
            s.program,
            s.event,
            l.type,
            decode(l.lmode,
                    0, 'NONE',
                    1, 'NULL',
                    2, 'ROW SHARE',
                    3, 'ROW EXCLUSIVE',
                    4, 'SHARE',
                    5, 'SHARE ROW EXCLUSIVE',
                    6, 'EXCLUSIVE', '?') "Mode",
            decode(l.request,
                     0, 'NONE',
                     1, 'NULL',
                     2, 'ROW SHARE',
                     3, 'ROW EXCLUSIVE',
                     4, 'SHARE',
                     5, 'SHARE ROW EXCLUSIVE',
                     6, 'EXCLUSIVE', '?') "Request",
            o.object_name,
            to_char(sysdate-l.ctime / 86400,'DD-MON-YYYY HH24:MI:SS') lock_requested
    from
            v$lock l, dba_objects o, v$session s
    where l.id1 = o.object_id (+)
    and l.sid = s.sid
    order by l.sid, l.type
    /
    
           SID     SERIAL# USERNAME     OSUSER  PROGRAM                    EVENT                          TY Mode                Request     OBJECT_NAME      Lock Requested
    --------- ----------------------- ------ ------------------------- ------------------------------ -- ------------------- ------------- ------------------ -----------------------------
    ...
    
           113      34978 SCOTT         scott     prog@tigerserv (TNS V1-V3)    SQL*Net message from client    TM ROW EXCLUSIVE NONE         scott25X         10-AUG-2012 16:16:32
           113      34978 SCOTT         scott     prog@tigerserv (TNS V1-V3)    SQL*Net message from client    TM ROW EXCLUSIVE NONE         scott25J         10-AUG-2012 16:16:32
           113      34978 SCOTT         scott     prog@tigerserv (TNS V1-V3)    SQL*Net message from client    TM ROW EXCLUSIVE NONE         scott25I         10-AUG-2012 16:16:32
           113      34978 SCOTT         scott     prog@tigerserv (TNS V1-V3)    SQL*Net message from client    TX EXCLUSIVE     NONE    
    ...
    ...
    
    
    Above there was a DML lock at 16:16:32 on the Primary. Investigate why the session has uncommitted transactions. There is nothing necessarily wrong but if the Standby has been uncharacteristically building up 'CURRENT' applied logs for hours or the filesystem is starting to fill up with shipped archive logs then maybe there is an SQL performance problem or it could be something as simple as a user has not exited their session.

    Friday, August 10, 2012

    Setting Preferred Credentials in Enterprise Manager "Error: Connection to {hostname} as user {username} failed"

    When setting preferred/default credentials:
    "Error 
    Connection to {hostname} as user {username} failed."
    
    In the agent home on the host: AGENT_HOME/sysman/log/emagent.trc:
    ERROR util.fileops: error: file AGENT_HOME/bin/nmo is not a setuid file
    WARN  Authentication: nmo binary in current oraHome doesn't have setuid privileges !!!
    ERROR Authentication: altNmo binary doesn't exist ... reverting back to nmo
    
    Check that the root.sh was run when the agent product was installed, if not then UNIX admin need to run root.sh to set the proper permissions for the Oracle binaries. This should be under AGENT_HOME/root.sh Try and set preferred/default credetials again in Grid Control.
     
    "Information
    Credentials successfully verified for {hostname}."
    

    Thursday, August 2, 2012

    Using DBMS_RCVMAN to display human readable byte sizes and time

    The DBMS_RCVMAN.num2displaysize procedure can be used to display human readable byte sizes. The DBMS_RCVMAN.Sec2DisplayTime procedure can display a time format given seconds. The drawback is the DBMS_RCVMAN package is only available to SYS.
    
    SQL> select sys.dbms_rcvman.num2displaysize(1024) sz from dual;
    
    SZ
    -------------------------------------------------------------------------------
        1.00K
    
    SQL> select sys.dbms_rcvman.num2displaysize(235236346) sz from dual;
    
    SZ
    -------------------------------------------------------------------------------
      224.34M
    
    SQL> select sys.dbms_rcvman.num2displaysize(1024*1024*1024*1024) sz from dual;
    
    SZ
    -------------------------------------------------------------------------------
        1.00T
    
    
    Given a number of seconds display in time format:
    
    SQL> select dbms_rcvman.Sec2DisplayTime(600) dt from dual;
    
    DT
    ------------------------------
    00:10:00
    
    SQL> select dbms_rcvman.Sec2DisplayTime(90035) dt from dual;
    
    DT
    ------------------------------
    25:00:35
    
    SQL>
    

    Sunday, June 24, 2012

    Assessing Oracle Critical Patch Updates (CPU) for the Database

  • Oracle Database Security Checklist - White Paper (2008) (pre-requisite read for below).

  • The second pre-requisite you should undertake before assessing any security risks is understand your environment!. This should include an understanding of all product installs, networks, firewall, user access and public facing infrastructure.

    Every 3 months Oracle release a Critical Patch Update (CPU) which usually applies to a majority of Oracle products. I haven't seen an instance where there was a Critical Patch Update that didn't apply to the Oracle Database. It is good practice to regularly review these advisories when they are released. The simplest form of assessing the Risk Matrix is to see what version and component of a product is affected, whether that product exists in their environment and then apply the CPU.

    To give you an idea of vulnerabilities and their seriousness there was recently the release of a zero-day attack for the Oracle database known as the TCP Listener Poison attack. To view the most recent advisories see the below link and select "View the most recent Critical Patch Update Advisory":

  • http://www.oracle.com/technetwork/topics/security/

    The April 2012 advisory (latest at time of this writing):

  • http://www.oracle.com/technetwork/topics/security/cpuapr2012-366314.html
  • For each product there is a Risk Matrix. This matrix is the basis for assessing risk to a component associated with a product. I'm only concentrating on the Database product however the same principles in assessing the CVSS (Common Vulnerability Scoring System) applies for other products also.

    I have provided a short summary on some of the key aspects in understanding risk matrix and meaning of the CVSS headings. Hopefully for high risks that gives people imperitive to patch and help present a case to management to undertake a patching exercise.

    Prior to the Risk Matrix Oracle provide a good summary of the vulnerabilities for a product. From the April 2012 Database advisory:

    Oracle Database Server Executive Summary

    "This Critical Patch Update contains 6 new security fixes for the Oracle Database Server. 3 of these vulnerabilities may be remotely exploitable without authentication, i.e., may be exploited over a network without the need for a username and password. 1 of these fixes is applicable to client-only installations, i.e., installations that do not have the Oracle Database Server installed.

    Below is an excerpt of the April 2012 Database Product Risk Matrix:

    Oracle Database Server Risk Matrix


    CVE#ComponentProtocolPackage and/or Privilege RequiredRemote Exploit without Auth.?CVSS VERSION 2.0 RISK (see Risk Matrix Definitions)Supported Versions AffectedNotes
    Base ScoreAccess VectorAccess ComplexityAuthen-
    tication
    Confiden-
    tiality
    IntegrityAvail-
    ability
    CVE-2012-0552Oracle SpatialOracle NETCreate session, create index, alter index, create tableNo9.0NetworkLowSingleCompleteCompleteComplete10.2.0.3, 10.2.0.4, 10.2.0.5, 11.1.0.7, 11.2.0.2, 11.2.0.3See Note 1
    CVE-2012-0519Core RDBMSOracle NETCreate library, create procedureNo7.1NetworkHighSingleCompleteCompleteComplete11.2.0.2See Note 2
    CVE-2012-0510Core RDBMSOracle NetNoneYes6.4NetworkLowNoneNonePartialPartial10.2.0.3, 10.2.0.4, 10.2.0.5, 11.1.0.7 

    source: http://www.oracle.com/technetwork/topics/security/cpuapr2012-366314.html

      Here is a quick guide to assessing the risk matrix before understanding the CVSS completely for your environment:

  • As already stated understand your environment completely! It would be an incomplete assessment if you're going to miss parts of it and a pointless exercise.
  • Assess whether the “Component” exists for any databases, if the package or privilege required exists and the base score’s value (a higher value obviously requires more attention).
  • Identify components and current products listed in the Risk matrix. As stated know your environment.
  • Are there any default usernames/passwords in any databases especially for this component? Vulnerabilities that require single or no authentication and make use of packages/privileges readily available are good candidates for patching. Of course follow the security checklist and change default username passwords and lock these accounts.
  • Base score – a base score of 10 means the product should be patched as fast as reasonably possible.
  • An Access vector of “Network” or “Adjacent Network” means the vulnerability is remotely exploitable. Assess the extent at which access is available to a database/server (number of people, other servers which can connect to the database/server).
  • Search the vulnerability # on the internet and examine the search results for more information. Vulnerabilities where exploits are available off the internet need higher consideration.
  • Even in the event of a component (say “Oracle Spatial”) has a locked MDSYS account and changed default password, there may be access to packages granted to other users or PUBLIC which are still accessible. Having locked a database account does not necessarily mean that vulnerable package is not exploitable.
  • The Risk Matrix only lists latest affected and supported versions. The vulnerability may apply for lower versions so the entire branch needs to be examined. For example, say 11.2.0.3 is listed as affected then assume the 11.2.x branch is affected however the patch is only available for 11.2.0.3 The question might then be raised on why the database is still on a lower patch set.

  • As recommended by the Security Checklist change default usernames/password (and lock if not required), and revoke privileges that are not required (eg, UTL_TCP). An example security vulnerability, a default database username/password that has EXECUTE on UTL_TCP (up to 11.x by default granted to PUBLIC) is a risk to the extent that once this is exploited all hosts accessible on the network are vulnerable to attack.
  • Understanding Risk Matrix columns

    The columns Base Score, Access Vector, Access complexity, Authentication, Confidentiality, Integrity and Availability are taken from the CVSS verion 2.0 open framework. Oracle have added the "Remote Exploit Without Authentication", and also modified the “Partial” definition to include “Partial+”.

    Oracle have also included a few of their own columns in addition to CVSS. These are Component, Protocol, Package and/or Privilege and Remote Exploit without Authentication. These are described here: Risk Matrix Glossary -- terms and definitions for Critical Patch Update risk matrices

    This document describes Oracle's use of the CVSS system: Use of Common Vulnerability Scoring System (CVSS) by Oracle

    Base Score

    “The CVSS base score defines the severity of the vulnerability and ranges between 0.0 and 10.0, where 10.0 represents the highest severity. Each risk matrix is ordered using this value, with the most severe vulnerability at the top of each risk matrix.”

    Access Vector

    The poorest score is "Network".
  • Local (L) - A vulnerability exploitable with only local access requires the attacker to have either physical access to the vulnerable system or a local (shell) account. Examples of locally exploitable vulnerabilities are peripheral attacks such as Firewire/USB DMA attacks, and local privilege escalations (e.g., sudo).
  • Adjacent Network (A) - A vulnerability exploitable with adjacent network access requires the attacker to have access to either the broadcast or collision domain of the vulnerable software.  Examples of local networks include local IP subnet, Bluetooth, IEEE 802.11, and local Ethernet segment.
  • Network (N) - A vulnerability exploitable with network access means the vulnerable software is bound to the network stack and the attacker does not require local network access or local access.  Such a vulnerability is often termed "remotely exploitable".  An example of a network attack is an RPC buffer overflow.

    Access Complexity

    The poorest score is "Low".
  • High (H) - Specialized access conditions exist. For example:
    In most configurations, the attacking party must already have elevated privileges or spoof additional systems in addition to the attacking system (e.g., DNS hijacking).
    The attack depends on social engineering methods that would be easily detected by knowledgeable people. For example, the victim must perform several suspicious or atypical actions.
    The vulnerable configuration is seen very rarely in practice.
    If a race condition exists, the window is very narrow.
  • Medium (M) - The access conditions are somewhat specialized; the following are examples:
    The attacking party is limited to a group of systems or users at some level of authorization, possibly untrusted.
    Some information must be gathered before a successful attack can be launched.
    The affected configuration is non-default, and is not commonly configured (e.g., a vulnerability present when a server performs user account authentication via a specific scheme, but not present for another authentication scheme).
    The attack requires a small amount of social engineering that might occasionally fool cautious users (e.g., phishing attacks that modify a web browser’s status bar to show a false link, having to be on someone’s “buddy” list before sending an IM exploit).
  • Low (L) - Specialized access conditions or extenuating circumstances do not exist. The following are examples:
    The affected product typically requires access to a wide range of systems and users, possibly anonymous and untrusted (e.g., Internet-facing web or mail server).
    The affected configuration is default or ubiquitous.
    The attack can be performed manually and requires little skill or additional information gathering.
    The “race condition” is a lazy one (i.e., it is technically a race but easily winnable).

    Authentication

    The poorest score is "None".
  • Multiple (M) - Exploiting the vulnerability requires that the attacker authenticate two or more times, even if the same credentials are used each time. An example is an attacker authenticating to an operating system in addition to providing credentials to access an application hosted on that system.
  • Single (S) - The vulnerability requires an attacker to be logged into the system (such as at a command line or via a desktop session or web interface).
  • None (N) - Authentication is not required to exploit the vulnerability.

    Confidentiality

    The poorest score is "Complete".
  • None (N) - There is no impact to the confidentiality of the system.
  • Partial (P) - There is considerable informational disclosure. Access to some system files is possible, but the attacker does not have control over what is obtained, or the scope of the loss is constrained. An example is a vulnerability that divulges only certain tables in a database.
  • Complete (C) - There is total information disclosure, resulting in all system files being revealed. The attacker is able to read all of the system's data (memory, files, etc.)
  • Integrity

    The poorest score is "Complete".
  • None (N) - There is no impact to the integrity of the system.
  • Partial (P) - Modification of some system files or information is possible, but the attacker does not have control over what can be modified, or the scope of what the attacker can affect is limited. For example, system or application files may be overwritten or modified, but either the attacker has no control over which files are affected or the attacker can modify files within only a limited context or scope.
  • Complete (C) - There is a total compromise of system integrity. There is a complete loss of system protection, resulting in the entire system being compromised. The attacker is able to modify any files on the target system.
  • Availability

    The poorest score is "Complete".
  • None (N) - There is no impact to the availability of the system.
  • Partial (P) - There is reduced performance or interruptions in resource availability. An example is a network-based flood attack that permits a limited number of successful connections to an Internet service.
  • Complete (C) - There is a total shutdown of the affected resource. The attacker can render the resource completely unavailable.
  • Additional Notes about CVSS terms

  • The difference between Integrity and Confidentiality: confidentiality relates to data, integrity can also relate to access controls and security, for example, an exploit that gives elevated privileges to a set of users compromises the integrity of a system.
  • Availability includes a reduction in performance of a system (denial of service for example).
  • Here is also a link to the Common Vulnerability Scoring System Version 2 Calculator. I've included it so you can see how a score is calculated. This does not include the Oracle created columns (for eg, there is no Partial+) but still worth having a look at.

    Thursday, June 7, 2012

    HTML reports using SQL*Plus

    Using SQL*Plus it is possible to create presentable HTML reports that improve readability over traditional ASCII based reports.

    A Simple report:

    set markup html on 
    set verify off trimspool on trimout on feedback off heading on echo off pages 100 termout off
    
    spool currentTime1.html
    
    select to_char(sysdate,'DD.MON.YYYY HH24:MI:SS') "Current Time" from dual
    /
    
    spool off
    


    Output:

    Current Time
    06.JUN.2012 18:56:29


    Taking a plain report and adding color to it and stylesheet:
    spool currentTime2.html
    
    set markup html on spool on entmap off -
     head 'The Current Time -
     ' -
     body 'text=black bgcolor=fffffff align=left' -
     table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'
    
    select 
    '

    ' || to_char(sysdate,'DD.MON.YYYY HH24:MI:SS') ||'

    ' "Current Time" from dual / spool off


    Output:

    Current Time

    06.JUN.2012 20:56:03



    Color highlighting columns in the output:

    set verify off trimspool on trimout on feedback off heading on echo off pages 100 termout off
    
    set markup html on spool on entmap off -
        head 'USERENV -
        ' -
        body 'text=black bgcolor=fffffff align=left' -
        table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'
    
    define COLOR_BEGIN_GREEN = '';
    define COLOR_END_GREEN = '';
    
    spool userenv.html
    
    select 
     sys_context('USERENV','SESSION_USER') "Session User",
     ''|| sys_context('USERENV','INSTANCE_NAME')|| 
     '' "Instance Name",
     '&COLOR_BEGIN_GREEN' || sys_context('USERENV','SERVER_HOST') ||
     '&COLOR_END_GREEN' "DB Hostname"
    from dual
    /
    
    spool off
    


    Output:

    Session UserInstance NameDB Hostname
    SYSlexusjupiter


    Displaying columns in different colors based on a condition. This can help highlighting an issue in a report:

    set verify off trimspool on trimout on feedback off heading on echo off pages 100 termout off
    
    set markup html on spool on entmap off -
        head 'The Current Time -
        ' -
        body 'text=black bgcolor=fffffff align=left' -
        table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'
    
    define BEGIN_WARNING = '';
    define END_WARNING = '';
    
    define BEGIN_OK = '';
    define END_OK = '';
    
    column server_host new_value server_host
    column instance_name new_value instance_name
    
    select 
     sys_context('USERENV','SERVER_HOST') server_host,
     sys_context('USERENV','INSTANCE_NAME') instance_name
    from dual
    /
    
    spool logMode.html
    
    prompt 

    &server_host..&instance_name - ARCHIVELOG MODE

    select decode(log_mode,'NOARCHIVELOG','&BEGIN_WARNING'|| log_mode||'&END_WARNING', '&BEGIN_OK' || log_mode||'&END_OK') "

    Archive Log Mode

    " from v$database / spool off


    Output:

    jupiter.lexus - ARCHIVELOG MODE


    Archive Log Mode

    NOARCHIVELOG


    Putting it all together, show init.ora prameters and comment, highlight problem areas in red otherwise in green:

    set verify off trimspool on trimout on feedback off heading on echo off pages 100 termout off
    
    define BEGIN_WARNING = '';
    define END_WARNING = '';
    
    define BEGIN_OK = '';
    define END_OK = '';
    
    column server_host new_value server_host
    column instance_name new_value instance_name
    
    select 
     sys_context('USERENV','SERVER_HOST') server_host,
     sys_context('USERENV','INSTANCE_NAME') instance_name
    from dual
    /
    
    set markup html on spool on entmap off -
        head 'INIT.ORA Parameters - &server_host..&instance_name -
        ' -
        body 'text=black bgcolor=fffffff align=left' -
        table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'
    
    spool initParameters.html
    
    prompt 

    &server_host..&instance_name - INIT.ORA Parameters

    select name "Parameter Name", value "Parameter Value", comments "Comments" from ( select name name, decode(value,null,'',value) value, case -- -- control_file_record_keep_time -- when name = 'control_file_record_keep_time' then case when value <=14 then '&BEGIN_WARNING Value of '||value|| ' potentially low for retention of RMAN backup information &END_WARNING' end -- -- destinations *dest*, dump and log destinations -- when name like '%dest%' then case when lower(value) like '%product%' then '&BEGIN_WARNING Destination possibly under the Oracle product directory '||value|| ' &END_WARNING' when lower(value) like '%temp%' or lower(value) like '%tmp%' then '&BEGIN_WARNING Destination possibly under a temporary directory!'||value|| ' &END_WARNING' end -- -- processes -- when name = 'processes' then case when value < 100 then '&BEGIN_WARNING '||name||' value '||value|| ' may be too low, see v$resource_limit max utilisation &END_WARNING' end -- -- skip_unusable_indexes -- when name = 'skip_unusable_indexes' then decode(value,'FALSE',value, '&BEGIN_WARNING '||value||' &END_WARNING') -- -- SPFILE -- when name = 'spfile' then decode(value,'?', '&BEGIN_WARNING No SPFILE set &END_WARNING', '&BEGIN_OK SPFILE in USE &END_OK') -- -- undo management -- when name = 'undo_management' then case when lower(value) != 'auto' then '&BEGIN_WARNING UNDO_MANAGEMENT is not AUTO '|| value || ' &END_WARNING' end end comments from v$parameter, v$instance ) where comments is not null order by name, value / spool off


    jupiter.lexus - INIT.ORA Parameters


    Parameter NameParameter ValueComments
    control_file_record_keep_time7Value of 7 potentially low for retention of RMAN backup info rmation
    skip_unusable_indexesTRUETRUE
    spfile/app/oracle/product/11.2.0/db_1/dbs/spfileABC.oraSPFILE in USE



    References:
    Originally inspired from:
    Creating HTML Reports with SQLPLus


    Wednesday, May 30, 2012

    How to setup IP Multi Pathing (IPMP) public interface on Solaris for Oracle Grid Infrastructure 11.2

    Using two network interfaces, vnet1 and vnet2 it is possible to create a redundant public interface that is used as one by Grid Infrastructure. If one of the interfaces (and depending on network design, links) is unavailable the public interface is still available.

    Current configuration:

    $ srvctl config nodeapps
    
    Network exists: 1/165.156.22.240/255.255.255.240/vnet1, type static
    VIP exists: /myserver01-vip/165.156.22.246/165.156.22.240/255.255.255.240/vnet1, hosting node myserver01
    VIP exists: /myserver02-vip/165.156.22.247/165.156.22.240/255.255.255.240/vnet1, hosting node myserver02
    GSD exists
    ONS exists: Local port 6100, remote port 6200, EM port 2016
    
    $ crsctl check cluster -all
    **************************************************************
    myserver01:
    CRS-4537: Cluster Ready Services is online
    CRS-4529: Cluster Synchronization Services is online
    CRS-4533: Event Manager is online
    **************************************************************
    myserver02:
    CRS-4537: Cluster Ready Services is online
    CRS-4529: Cluster Synchronization Services is online
    CRS-4533: Event Manager is online
    **************************************************************
    
    Below are the network interfaces, you can see the VIP (.246) and SCANs (.248, .250) bound to vnet1. vnet2 has nothing running on it.
    $ /usr/sbin/ifconfig -a
    ....
    vnet1: flags=9040843 mtu 1500 index 3
            inet 165.156.22.251 netmask fffffff0 broadcast 165.156.22.255
            groupname topside
    vnet1:1: flags=1000843 mtu 1500 index 3
            inet 165.156.22.244 netmask fffffff0 broadcast 165.156.22.255
    vnet1:2: flags=1040843 mtu 1500 index 3
            inet 165.156.22.246 netmask fffffff0 broadcast 165.156.22.255
    vnet1:4: flags=1040843 mtu 1500 index 3
            inet 165.156.22.248 netmask fffffff0 broadcast 165.156.22.255
    vnet1:6: flags=1040843 mtu 1500 index 3
            inet 165.156.22.250 netmask fffffff0 broadcast 165.156.22.255
    vnet2: flags=9040843 mtu 1500 index 4
            inet 165.156.22.252 netmask fffffff0 broadcast 165.156.22.255
            groupname topside
    ...
    ...
    

    To perform the change an outage for all PUBLIC traffic is required:

    $ srvctl stop listener
    $ srvctl stop cvu
    $ srvctl stop scan_listener
    $ srvctl stop scan$ srvctl stop listener
    $ srvctl stop cvu
    $ srvctl stop scan_listener
    $ srvctl stop scan
    


    Check that nothing is bound to the interfaces (on all nodes in the cluster):

    $ /usr/sbin/ifconfig -a
    

    As root (SUDO setup in this case, below the SUBNET mask ends in .240, your network may be different!)

    $ /opt/sfw/bin/sudo $ORACLE_HOME/bin/srvctl modify nodeapps\
             -n myserver01 -A 165.156.22.246/255.255.255.240/vnet1\|vnet2
    
    $ srvctl config nodeapps
    Network exists: 1/165.156.22.240/255.255.255.240/vnet1:vnet2, type static
    VIP exists: /myserver01-vip/165.156.22.246/165.156.22.240/255.255.255.240/vnet1:vnet2, hosting node myserver01
    VIP exists: /myserver02-vip/165.156.22.247/165.156.22.240/255.255.255.240/vnet1:vnet2, hosting node myserver02
    GSD exists
    ONS exists: Local port 6100, remote port 6200, EM port 2016
    
    $ srvctl start nodeapps
    

    Check the SCAN listener also:

    $ srvctl config scan
    SCAN name: mycluster-scan, Network: 1/165.156.22.240/255.255.255.240/vnet1:vnet2
    SCAN VIP name: scan1, IP: /mycluster-scan/165.156.22.249
    SCAN VIP name: scan2, IP: /mycluster-scan/165.156.22.250
    SCAN VIP name: scan3, IP: /mycluster-scan/165.156.22.248
    $ srvctl start scan
    $ srvctl start scan_listener
    $ srvctl start cvu 
    

    Sunday, May 27, 2012

    11.1 Grid Agent "Some recommended prerequisites checks are failed"

    Passing the -debug option to runInstaller provides more information on where a pre-requisite could be failing. The 11.1 Grid Agent no longer uses a GUI and is required to be installed command line. The following error message does not provide many clues as to what the pre-requisite fail could be:


    ./runInstaller -silent -responseFile /oracle/product/software/agent/linux/agent/response.rsp
    Starting Oracle Universal Installer...

    Checking Temp space: must be greater than 150 MB.   Actual 1083 MB    Passed
    Checking swap space: must be greater than 150 MB.   Actual 16383 MB    Passed
    Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-05-10_10-36-59AM. 
    Please wait ...
    *** Check for updates ***
    *** Select Installation Type ***
    *** Check Prerequisites ***
    Some recommended prerequisites checks are failed. You might get errors during installation. Please fix those prerequisites and start the installation again.

    Further, the README.TXT contains the following:

    For detailed information on using silent installation method to install the Management agent please refer to .Enterprise Manager Silent Installations. described in the Enterprise Manager Grid Control Basic Installation available at:

    http://www.oracle.com/technology/documentation/oem.html

    The URL contains the documentation for all Oracle products. 

    The documentation is here:

    Oracle® Enterprise Manager
    Grid Control Basic Installation Guide 
    11g Release 1 (11.1.0.1.0) 
    E15838-09


    After reading through the documentation, the -debug flag can be used for the runInstaller which gives us more detailed information as to what the problem can be:

    ./runInstaller -debug -silent -responseFile /oracle/product/software/agent/linux/agent/response.rsp


    In Progress
    Checking if Oracle software certified on the current O/S...  1
    In Progress
    Checking for required packages installed on the system ....  1
    In Progress
    Checking whether the software compatibile for current o/s...  7
    In Progress
    Checking for sufficient diskspace in TEMP location...  1
    In Progress
    Checking for sufficient diskspace in Inventory location...  1
    In Progress
    Checking for the Hostname...  1
    This is a prerequisite condition to test whether the Oracle software is certified on the current O/S or not.Succeeded

    This is a prerequisite condition to test whether the minimum required packages are available on the system.Succeeded

    This is a prerequisite condition to test whether the software is compatible for this o/sFailed

    This check ensures that sufficient diskspace is available in system TEMP location.Succeeded

    This check ensures that sufficient diskspace is available in system Inventory location.Succeeded

    This is a prerequisite condition to test whether the host name where the installation will be done, is correct or not.Succeeded

    Silent Install page at  3 oracle.sysman.install.oneclick.EMGCPrereqDialog,pageTitle=Check Prerequisites,label=Check Prerequisites,selected
    Some recommended prerequisites checks are failed. You might get errors during installation. Please fix those prerequisites and start the installation again.

    Saturday, March 17, 2012

    ORA-03297: file contains used data beyond requested RESIZE value

    At some stage a DBA will encounter the following error when resizing a datafile:

    ORA-03297: file contains used data beyond requested RESIZE value

    What may be more frustrating is that the datafile has plenty of free space.

    So what can be done to solve this error and resize down the datafile? Why does it occur? Some might be tempted to just add more space to the filesystem and be done with it. Not everyone has that option.

    A datafile cannot be resized down below the High Water Mark (HWM) and that's what causes the ORA-03297 error. . In the lifetime of the datafile some extents have been created bumping up the HWM. Others below the HWM have also been DELETED. 

    I will show an example of a datafile encountering this error, what is happening inside the datafile and how to resolve the error. Let's create a tablespace first and the two tables, one with many rows and a second with a few rows.

    CREATE TABLESPACE "MYTS" DATAFILE '/oradata1/MYDB//mytsdatafile01.dbf' SIZE 32M
    AUTOEXTEND ON NEXT 1310720 MAXSIZE 64M 
    LOGGING ONLINE PERMANENT BLOCKSIZE 8192
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
    
    Tablespace created.
    
    SQL> create table myuser.mytab1 tablespace MYTS as
    (select level id,dbms_random.string('a',dbms_random.value(1,4000)) str
    from dual
    connect by level < 20000
    )
    /
    Table created.
    
    SQL> create table myuser.mytab2 tablespace MYTS as
    (select level id,dbms_random.string('a',dbms_random.value(1,4000)) str
    from dual
    connect by level < 10
    )
    /
    
    Table created.
    

    How many MB is now in use by these tables?
    select segment_name, bytes/1024/1024 
    from dba_segments
    where segment_name in ('MYTAB1','MYTAB2')
    and owner = 'MYUSER'
    /
    
    SEGMENT_NAME                    BYTES/1024/1024
    -----------------------------------------------
    MYTAB1                          54
    MYTAB2                          .0625
    
    

    To demonstrate how these two tables fit in the MYTS tablespace I want to firstly show a few things about the datafile.

    DFSIZEMB = the datafile size in MB on disk.
    HWMMB = the location of the HWM in the datafile.
    DFREEMB = the number of FREE MB in the datafile.
    %FREE = the percentage of FREE space in the datafile.
    RESIZEABLE = the amount of MB that can be gained from resizing.

    Use the below SQL to see how much space can be gained from a RESIZE and the location of the HWM in a datafile. For a database with many datafiles this query may take a while.

    QUERY 1 - datafile free space, HWM location, resizeable MB

    column file_name format a50
    column tablespace_name format a20
    select
                tablespace_name,
                file_id,
                file_name,
                dfsizeMB,
                hwmMB,
                dffreeMB,
                trunc((dffreeMB/dfsizeMB)*100,2) "% Free",
                trunc(dfsizeMB-hwmMB,2) "Resizeble"
       from
       (
          select
               df.tablespace_name tablespace_name,
               df.file_id file_id,
               df.file_name file_name,
               df.bytes/1024/1024 dfsizeMB,
               trunc((ex.hwm*(ts.block_size))/1024/1024,2) hwmMB,
               dffreeMB
          from
               dba_data_files df,
               dba_tablespaces ts,
          (
               select file_id, sum(bytes/1024/1024) dffreeMB
               from dba_free_space
               group by file_id
          ) free,
          (
               select file_id, max(block_id+blocks) hwm
               from dba_extents
               group by file_id
          ) ex
          where df.file_id = ex.file_id
          and df.tablespace_name = ts.tablespace_name
          and df.file_id = free.file_id (+)
          order by df.tablespace_name, df.file_id
        ) 
    /
    TABLESPACE_NAME  FILE_ID File Name                            DFSIZEMB     HWMMB   DFFREEMB     % Free  Resizeble
    -------------------- ---------- ------------------------------ ---------- ---------- ---------- ---------- ----------...
     MYTS             14      /oradata1/MYDB/mytsdatafile01.dbf        55.75      55.06      .6875       1.23        .69
    ...
    
    Above, the datafile can only be resized by 0.69MB and the HWM is at the 55MB mark with the datafile size on disk being 55.75MB.

    How do these two tables look like within the datafile? NOTE: This query may take a while for a large datafile with lots of extents.

    QUERY 2 - location of segments within a datafile

    column segment heading 'Segment Name' format a14
    column file_name heading 'File Name' format a40
    column segment_type heading 'Segment Type' format a10
    select
                    file_name,
                    segment_type,
                    owner||'.'||segment_name segment,
                    block_id,
                    blockIdMB
            from
            (
             select
                  ex.owner owner,
                  ex.segment_name segment_name,
                  ex.segment_type segment_type,
                  ex.block_id block_id,
                  df.file_name file_name,
                  trunc((ex.block_id*(ts.block_size))/1024/1024,2) blockIdMB
          from
                  dba_extents ex, dba_data_files df, dba_tablespaces ts
                  where df.file_id = &file_id
                  and df.file_id = ex.file_id
                  and df.tablespace_name = ts.tablespace_name
                  order by ex.block_id desc
          )
          where rownum <= 100
    /
    
    Enter value for file_id: 14
    
    File Name                         Segment Ty Segment Name     BLOCK_ID  BLOCKIDMB
    --------------------------------- ---------- -------------- ---------- ---------- 
    /oradata1/MYDB/mytsdatafile01.dbf   TABLE      MYUSER.MYTAB2        7040        55
    /oradata1/MYDB/mytsdatafile01.dbf   TABLE      MYUSER.MYTAB1        6912        54 
    /oradata1/MYDB/mytsdatafile01.dbf   TABLE      MYUSER.MYTAB1        6784        53
    /oradata1/MYDB/mytsdatafile01.dbf   TABLE      MYUSER.MYTAB1        6656        52
    /oradata1/MYDB/mytsdatafile01.dbf   TABLE      MYUSER.MYTAB1        6528        51
    /oradata1/MYDB/mytsdatafile01.dbf   TABLE      MYUSER.MYTAB1        6400        50
    ...
    ...
    /oradata1/MYDB/mytsdatafile01.dbf   TABLE      MYUSER.MYTAB1         144      1.12
    /oradata1/MYDB/mytsdatafile01.dbf   TABLE      MYUSER.MYTAB1         136      1.06
    /oradata1/MYDB/mytsdatafile01.dbf   TABLE      MYUSER.MYTAB1         128         1
    


    We can see table MYTAB1 ends around the 54MB block in the datafile and MYTAB2 starts around the 55MB block.

    So we now have two tables in the MYTS tablespace created one after the other. Further, how about we create a situation where there is alot of free space in the datafile and cannot be resized resulting in "ORA-03297: file contains used data beyond requested RESIZE value". DELETING all the rows from the MYTAB1 table will not free up any space and will still have it allocated to MYTAB1, so let's TRUNCATE the table MYTAB1 and run the same SQL above again to see the result of the datafile.
    SQL> truncate table myuser.mytab1;
    
    Table truncated.
    TABLESPACE_NAME         FILE_ID File Name                                            DFSIZEMB      HWMMB   DFFREEMB     % Free  Resizeble
    -------------------- ---------- -------------------------------------------------- ---------- ---------- ---------- ---------- ----------
    MYTS                         14 /oradata1/MYDB/mytsdatafile01.dbf                  55.75      55.06     54.625      97.98        .69
    
    The datafile now has 97% and 54MB FREE space. The HWM is still at the 55MB mark. Inspecting the datafile, MYTAB1 is at block 1MB and MYTAB2 is still at of course 55MB.
    File Name                         Segment Ty Segment Name     BLOCK_ID  BLOCKIDMB
    --------------------------------- ---------- -------------- ---------- ----------
    /oradata1/MYDB/mytsdatafile01.dbf   TABLE      MYUSER.MYTAB2        7040        55
    /oradata1/MYDB/mytsdatafile01.dbf   TABLE      MYUSER.MYTAB1         128         1
    

    So seeing we have 54MB FREE space in a 55MB datafile let's try to resize it down to 50MB.

    SQL> alter database datafile '/oradata1/MYDB/mytsdatafile01.dbf' resize 50M;
    alter database datafile '/oradata1/MYDB/mytsdatafile01.dbf' resize 50M
    *
    ERROR at line 1:
    ORA-03297: file contains used data beyond requested RESIZE value
    


    The only way to resize the datafile is to lower the HWM. This is achieved by moving the extents located at the HWM either to another tablespace or the same tablespace. For the same tablespace ensure plenty of free space and run QUERY 2 after each MOVE to see if anything is achieved.

    Of course for a production environment where there are many users accessing the table, index, LOB etc it might not be that easy and this sort of activity would need to be carefully planned, especially with LOB segments. Also, when moving a table the underlying INDEXES are marked UNUSABLE and need to be rebuilt.

    SQL> alter table myuser.mytab2 move tablespace MYTS;
    
    Table altered.
    
    Now the datafile shows that MYTAB2 is at block 1MB.

    File Name                           Segment Ty Segment Name     BLOCK_ID  BLOCKIDMB
    ----------------------------------- ---------- -------------- ---------- ---------- 
    /oradata1/MYDB/mytsdatafile01.dbf   TABLE      MYUSER.MYTAB2         136       1.06 
    /oradata1/MYDB/mytsdatafile01.dbf   TABLE      MYUSER.MYTAB1         128          1
    

    What about the HWM?
    TABLESPACE_NAME   FILE_ID File Name                                            DFSIZEMB      HWMMB   DFFREEMB     % Free  Resizeble
    -------------------- ---------- -------------------------------------------------- ---------- ---------- ---------- ---------- ----------
    
    MYTS              14 /oradata1/MYDB/mytsdatafile01.dbf                  55.75       1.12     54.625      97.98      54.63
    

    Now the HWM is at the 1.12 MB mark and there is 54MB of reclaimable space in the datafile.

    SQL> alter database datafile '/oradata1/MYDB/mytsdatafile01.dbf' resize 2M;
    
    Database altered.
    

    Datafile now resized! In a production environment there may be many different objects extents near the HWM and simply moving one table might not be so common. It's best to see how many different objects are around the HWM and calculate how much space can be reclaimed via the MOVE. Unless there is a benefit in reclaiming a large amount of space, or moving tables, indexes, LOBs etc is something that just has to be done, then if possible get more space added to the filesystem.

    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