All posts by mrculp

Oracle and OCI & JDBC Connection Strings

<b>Sample for tnsnames.ora</b>
=======================
culpdbs =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = your-primary-host-name)(PORT = 49130))
(ADDRESS = (PROTOCOL = TCP)(HOST = your-standby-host-name)(PORT = 49130))
(LOAD_BALANCE = no)
(FAILOVER = yes)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydbs)
(failover_mode =
(type = select)
(method = preconnect)
(retries = 180)
(delay = 10)
)
)
)

(LOAD_BALANCE = no)  ==&gt; You cannot load balance between Dataguard servers

Here is a sample JDBC connect string for failover scenarios:

Jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = primary-scan)(PORT = 49125)) ADDRESS = (PROTOCOL = TCP)(HOST = standby-scan) (PORT = 49125))(LOAD_BALANCE = no)(FAILOVER = yes))(CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = someserv_svc_01.bigbusiness.com)))

Jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = primary-scan)(PORT = 49125)) ADDRESS = (PROTOCOL = TCP)(HOST = standby-scan) (PORT = 49125))(LOAD_BALANCE = no)(FAILOVER = yes))(CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = someserv_svc_01.bigbusiness.com)))

there are a few different parameters here that are good to note what they do:

If TAF is configured there are 2 types which are defined in the CONNECT_DATA portion of the connect string, if you do not have TAF configured these parameters should not be configured as they will cause issues during connection retries.

TYPE

Specifies the type of failover. Three types of Net8 failover functionality are available by default to Oracle Call Interface

(OCI) applications:
SESSION: Fails over the session; that is, if a user’s connection
is lost, a new session is automatically created for the user on
the backup. This type of failover does not attempt to recover
selects.
SELECT: Enables users with open cursors to continue
fetching on them after failure. However, this mode involves
overhead on the client side in normal select operations.

 

Server side TAF settings override client-side counterparts that might be configured in TNS connect descriptors

select name, failover_method, failover_type from dba_services;

SELECT inst_id, sid, serial#, username, failover_type, failover_method, service_name, failed_over FROM gv$session where username = UPPER(‘&your_user_name’);

 

Failover Descriptions

TAF is transparent application failover which will move a session to a backup connection if the session fails.

Note: TAF will not work with JDBC thin.

 

 

 

Here is a good article for jdbc FCF configuration, keep in mind there are a few prereqs to have in place before you can do this.

FCF jdbc config

 

Failover Connect Strings

METALINK Articles

Information Center: Oracle JDBC 11g community

https://support.oracle.com/epmos/faces/SrCreate?_adf.ctrl-state=ghjvpcjk5_4&_afrLoop=30270398455552

 Information Center: Using Oracle JDBC 11g [ID 1371705.2]

Information Center: Installing and Configuring Oracle JDBC 11g [ID 1372203.2]

——————————————————————————————————————————————-

Can the JDBC Thin Driver Do Failover by Specifying FAILOVER_MODE?  Oracle Doc ID 465423.1

The Oracle note is quite specific on this topic

A string formatted with the FAILOVER_MODE is not supported with jdbc thin. FCF is however supported.

jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1vip)(PORT = 1525)) (ADDRESS = (PROTOCOL = TCP)(HOST = rac2vip)(PORT = 1525)) (LOAD_BALANCE = on)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ractest) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 2) (DELAY = 1))))

 

Master Note for Oracle JDBC High Availability Features [ID 1100024.1]

JDBC Connection String to RAC System

The following notes contain information for setting a valid JDBC connection string to a RAC system:

Document 390923.1 Connection-String For Jdbc Thin-Client In A Data guard-Environment

Document 414465.1 How to Set JDBC Thin Connection String to RAC Without Referencing Every Node in the Cluster

Document 391015.1 How to Connect to a RAC Database from a Java Application That Takes a HOST PORT SID Argument

Document 1081179.1 How to Access a RAC Database configured with SCAN via Java Thin Driver

Document 1290193.1 Using SCAN With Oracle JDBC 11g Thin Driver

JDBC Drivers and Failover Mechanisms (FCF, TAF, SCAN) [ID 1080674.1]

The following documentation provides an overview of Fast Connection Failover, how it works and how to use it, and a comparison between Fast Connection Failover and Transparent Application Failover:

Version 11.2 Oracle® Database JDBC Developer’s Guide, 11g Release 2 (11.2)
Chapter 27: Fast Connection Failover
http://download.oracle.com/docs/cd/E11882_01/java.112/e10589/fstconfo.htm
Version 11.1 Oracle® Database JDBC Developer’s Guide and Reference, 11g Release 1 (11.1)
Chapter 26: Fast Connection Failover
http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/fstconfo.htm#CIHJBFFC
Version 10.2 Oracle® Database JDBC Developer’s Guide and Reference 10g Release 2 (10.2)
Chapter 27: Fast Connection Failover
http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/fstconfo.htm
Version 10.1 Oracle® Database JDBC Developer’s Guide and Reference 10g Release 1 (10.1)
Chapter 8: Fast Connection Failover
http://download.oracle.com/docs/cd/B14117_01/java.101/b10979/fastconnfail.htm

 

Fast Connection Failover (FCF) Test Client Using 11g JDBC Driver and 11g RAC Cluster [ID 566573.1]

This is an excellent article describing and testing the FCF functionality within Oracle 11g

How to Implement Connect Failover Using JDBC Thin [ID 213412.1]

How to Use Connect-Time Failover Across Independent, Non-RAC Databases From JDBC [ID 1067811.1]

JDBC TCPS Connections Do Not Failover For SCAN Listeners [ID 1289244.1]

JDBC With FAN Events
The Oracle RAC FAN APIs enable application code to receive and respond to FAN event notifications sent by Oracle RAC. This is achieved by enabling the code to respond to FAN events in the following way:

•Listening for Oracle RAC service down, service up, and node down events
•Listening for load balancing advisory events and responding to them
More information is available here!!

FAN Event Documentation

SCAN

Using SCAN is entirely different that what is described above

SALES=(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=Austin-scan)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=OrderEntry)))
This is a typical SCAN entry this is based on a single RAC cluster in one location. Load balancing is automatically done within the nodes of the cluster, there is no need to set any additional parameters for a SCAN setup locally. If a cluster has additional nodes added or deleted the SCAN string remains the same with no outage or additional work to be done. The client changes nothing.

Initial Script Template

Here is a little header that I like to use for my shell scripts for Oracle code,
feel free to suggest mods


#!/bin/ksh 
############################################################################ 
# Script Name..: <script name>.ksh 
# Description..: 
# Author.......: Michael Culp 
# Date.........: 04/ /2013 
# Version......: 
# Modified By..: 
# Date Modified: 
# Comments.....: 
# Schema owner.: 
#              : alter session set current should be used to change schemas 
# Login User...:
# Run Order....: 
# Dependent on.: 
# Script type..: 
# ############################################################################
sqlplus -s "/ as sysdba" &lt;&lt;EOF 
set lines 150 
set pages 150 
-- spool &lt;some file name&gt;
-- spool off 
EOF

DBCA command line

You can use dbca to create ASM or database instances from the command line:

dbca -silent  -createDatabase  -templatename <template name> -gdbName <global database name>

-silent will prevent the GUI from appearing 
-templatename specifies a template in the $oracle_home/assistants/dbca/template directory

Using the DBCA to Delete a Database

To delete (or configure) a database in UNIX or Linux, you must set ORACLE_SID in the shell from which DBCA is launched. Start the DBCA by entering dbca in a terminal window, and click Next on the Welcome page. to delete the database, perform the following steps:

  1. On the Operations page, select Delete a Database, and click Next.
  2. Select the database that you wanted to delete (in class, hist), and click Finish.
  3. Click Yes to confirm your deletion.

Dropping a database involves removing its data files, redo log files, control files, and initialization parameter files. The DROP DATABASE statement deletes all control files and all other database files listed in the control file. To use the DROP DATABASE statement successfully, all the following conditions must apply:

  • The database must be mounted and closed.
  • The database must be mounted exclusively – not in shared mode.
  • The database must be mounted as RESTRICTED.

An example of this statement is:

DROP DATABASE;

The DROP DATABASE statement has no effect on archieved log files nor does it have any effect on copies or backups of the database. It is best to use Recovery Manager (RMAN) to delete such files. If the database is on raw disks, then the actual raw disk special files are not deleted.

 

DBCA Stuff

DBCA 12.2

Compression Check Script

Compression Check Script

#!/bin/ksh
############################################################################
# Script Name..: comp_chk.ksh
# Description..: Estimate the compression for a table
# Author.......: Michael Culp
# Date.........:
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
############################################################################
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set serveroutput on
-- spool <some file name>
declare
   lv_cmp_ratio number;
   lv_comptype_str varchar2(300);
   lv_BLKCNT_CMP number;
   lv_BLKCNT_UNCMP number;
   lv_ROW_CMP number;
   lv_ROW_UNCMP number;
begin
   dbms_compression.GET_COMPRESSION_RATIO(SCRATCHTBSNAME=>'USERS',
                                                 OWNNAME=>'CFGADM',
                                                 TABNAME=>'EQUIPMENT',
                                                PARTNAME=>null,
                                                COMPTYPE=>2, ---2 means OLTP
                                             BLKCNT_CMP =>lv_BLKCNT_CMP,
                                           BLKCNT_UNCMP =>lv_BLKCNT_UNCMP,
                                                ROW_CMP =>lv_ROW_CMP,
                                              ROW_UNCMP =>lv_ROW_UNCMP,
                                               CMP_RATIO=>lv_cmp_ratio,
                                            COMPTYPE_STR=>lv_COMPTYPE_STR);
dbms_output.put_line('====================================================');
dbms_output.put_line('1. Compression Ratio.: '||lv_cmp_ratio);
dbms_output.put_line('2. Block Count.......: '||lv_blkcnt_cmp);
dbms_output.put_line('3. Compression Type.......: '||lv_comptype_str);
dbms_output.put_line('4. Blk Count Compressed...: '||lv_BLKCNT_CMP);
dbms_output.put_line('5. Blk Count Un-compressed: '||lv_BLKCNT_UNCMP);
dbms_output.put_line('6. Row Count Compressed : '||lv_row_cmp);
dbms_output.put_line('4. Row Count Un-Compressed: '||lv_row_uncmp);
dbms_output.put_line('====================================================');
end;
/
-- spool off
EOF

Compression Example

Support for ASMLib on Red Hat

Oracle will no longer release ASMLib kernel drivers for RHEL 6 kernels. See this note on support.oracle.com

Oracle ASMLib Software Update Policy for Red Hat Enterprise Linux Supported by Red Hat (Doc ID 1089399.1)

Going to Oracle EL 6 is an option, and may save money in the long run, one vendor to support OS and database. Oracle is much closer to the drivers that are used for database purposes exclusively, and support may actually be less expensive.

Show waiting sessions and the event they wait on

 

#!/bin/ksh

sqlplus -s "/ as sysdba" <<EOF

select  sw.sid     sid,
        p.spid     spid,
        s.username username,
        s.osuser   osuser,
        sw.event   event,
        s.machine  machine,
        s.program  program,
        decode(sw.event,'db file sequential read', sw.p3, 'db file scattered read', sw.p3, null) blocks
   from v\$session_wait sw,
        v\$session  s, 
        v\$process p
  where s.paddr = p.addr
    and event not in ('pipe get','client message')
    and sw.sid  = s.sid

EOF

 

Linux Quick Finds

Finding Large Directories

look for large files with this command:

find . -type f -size +100000k -exec ls -lh {} \; | awk ‘{ print $9 “: ” $5 }’

This looks for files over 100MB, but other than datafiles there were very few files found. I knew I needed to look for large directories. Directories that had a large amount of small files. This command worked nicely:

du -h / | grep ^[1-9][0-9][0-9.]*G | sort -rn

From there I was able to find the directories that I needed to clear out.

Find and zip files newer than X minutes

find . -mmin -1620 -type f -name \*_lm[d,s]\*trc -print -exec zip /tmp/`hostname`_lm_files.zip {} \;

Find Locked Sessions

set lines 100 pages 999
col username format a20
col sess_id format a10
col object format a25
col mode_held format a10
select oracle_username || ' (' || s.osuser || ')' username,
       s.sid || ',' || s.serial# sess_id,
       owner || '.' || object_name object,
       object_type,
       decode( l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') status,
       decode(v.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X SX)'
 , 4, 'Share'
 , 5, 'S/Row-X (SSX)'
 , 6, 'Exclusive', TO_CHAR(lmode)) mode_held
    from v\$locked_object v,
         dba_objects d,
         v\$lock l,
         v\$session s
   where v.object_id = d.object_id and
         v.object_id = l.id1 and
         v.session_id = s.sid
   order by oracle_username, session_id;

Start Session Trace Function


/**************************************************************************
 * Description:
 *      SQL*Plus script to start the SQL Trace for another session.  This
 *      script displays the trace file name and location.  This script will
 *      display the user sessions and prompt for information it needs.
 *
 **************************************************************************/
SET TERMOUT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET ECHO OFF

column in_db_namet new_value out_db_namet noprint
column in_spidt new_value out_spidt noprint
column in_usert new_value out_usert noprint
column in_patht new_value out_patht noprint

select value in_db_namet,
       user in_usert
  from v\$parameter
 where name = 'db_name';

-- Find the path of the trace file directory
select value in_patht
  from v\$parameter
 where name = 'user_dump_dest';

-- This will put identifiable characters into the trace file and make it easier to find
alter session set tracefile_identifier = &out_usert;

SET TERMOUT ON
column program format a20

select username,
       sid,
       serial#,
       program
  from v\$session
 where program NOT LIKE 'ORACLE%';

ACCEPT in_user PROMPT 'Enter User ID from list above : '
ACCEPT in_sid PROMPT 'Enter SID from list above : '
ACCEPT in_serial PROMPT 'Enter SERIAL from list above : '

PROMPT
PROMPT 'Write the SID and SERIAL down...will need them to stop the trace'
PROMPT
SET TERMOUT OFF

select p.spid in_spidt
  from v\$process p, 
       v\$session s
 where p.addr = s.paddr 
   and s.username = upper('&in_user') 
   and s.sid = &in_sid;

SET TERMOUT ON
PROMPT
PROMPT Started SQL Trace in session
SELECT '&out_path' "Trace File Path" from dual;
SELECT '&out_db_namet' || '_ora_' || '&out_spidt' || '.trc' "Trace File Name"
  from dual;

PROMPT
rem exec dbms_system.set_ev(&in_sid, &in_serial, 10046, 12, '');
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => &in_sid,
                                          serial_num => &in_serial,
                                               waits => TRUE,
                                               binds => TRUE);
SET FEEDBACK ON
SET VERIFY ON
SET ECHO ON

This will start a trace in another session
exec dbms_system.set_sql_trace_in_session(3,5023,true);
exec dbms_system.set_sql_trace_in_session(3,5023,false);

Oracle11gR2 Linux install

This should be found in linux_common.ksh

Preinstall requirements:

Minimum RAM requirement: 1.5 GB for GRID Infrastructure and 2.5GB for GI plus RAC

you can verify easily by:

# grep memTotal /proc/meminfo

For Swap space checking

# grep SwapTotal /proc/meminfo

For disk temp space

# df -h /tmp