All posts by mrculp

spfile script




SELECT DECODE(value, 
               NULL, 
              'PFILE', 
              'SPFILE') "Init File Type"
  FROM sys.v_$parameter 
 WHERE name = 'spfile';

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';

set linesize 200 
col host_name for a45 
select INST_ID, 
       INSTANCE_NAME, 
       HOST_NAME,
       STARTUP_TIME DATABASE_STATUS, 
       status 
  from gv\$instance;

Database size script

Here is a nice little script I like to use for showing the database size 
for various components.
#!/bin/ksh ############################################################################ 
# Script Name..: db_overall_size.ksh 
# Description..: 
# Author.......:
# Date.........: 04/10/2013 
# Version......: 
# Modified By..: 
# Date Modified: 
# Comments.....: 
# Schema owner.: 
# Login User...: 
# Run Order....: 
# Dependent on.: 
# Script type..: 
# ############################################################################
sqlplus -s "/ as sysdba" <<EOF 
set lines 200 
set pages 200
spool db_size.txt set feed off 
column systot   format 99,990.99 heading "System |Alloc|GB" 
column sysused  format 99,990.99 heading "System |Used|GB" 
column systm    format 99,990.99 heading "System Tbsp|MB" 
column sysax    format 99,990.99 heading "Sys Aux|GB" 
column systmusd format 99,990.99 
column sysused  format 99,990.99 
column undotb   format 99,990.99 heading "Undo|GB" 
column datatot  format 99,990.99 heading "Data|Alloc|GB" 
column dataused format 99,990.99 heading "Data|Used|GB" 
column tmptot   format 99,990.99 heading "TEMP|Alloc|GB" 
column redotot  format 99,990.99 heading "Online|Redo|MB" 
column ctltot   format 99,990.99 heading "Ctrl File|MB" 
column systall  format 99,990.99 column systused format 99,990.99 
column fratot   format 99,990.99 column fraused  format 99,990.99 
column total_gb format 99,990.99 column free_gb  format 99,990.99 

select name from v\$database;
select s.system_size            SYSTOT,        
       s.system_size-f1.free_mb SYSUSED,        
       st.system_size           SYSTM,        
       sa.system_size           SYSAX,        
       ud.system_size           UNDOTB,        
       d.data_size              DATATOT,        
       d.data_size-f2.free_mb   DATAUSED,        
       t.temp_size              TMPTOT,        
       r.redo_size              REDOTOT,        
       c.controlfile_size       CTLTOT,        
       s.system_size+t.temp_size+r.redo_size+c.controlfile_size            SYSTALL,
       s.system_size-f1.free_mb+t.temp_size+r.redo_size+c.controlfile_size SYSTUSED,        
       fra.fratot               FRATOT,        
       fra.fraused              FRAUSED   
       from       (select sum(bytes)/1024/1024/1024 system_size               
                     from dba_data_files         
                    where tablespace_name like 'SYSTEM' or               
                          tablespace_name like 'SYSAUX' or               
                          tablespace_name like 'UNDO%'  or               
                          tablespace_name like 'TOOLS'  or                
                          tablespace_name like 'USERS') s,       
                  (select sum(bytes)/1024/1024/1024 data_size          
                     from dba_data_files         
                    where tablespace_name not like 'SYSTEM' and               
                          tablespace_name not like 'SYSAUX' and               
                          tablespace_name not like 'UNDO%'  and               
                          tablespace_name not like 'TOOLS'  and               
                          tablespace_name not like 'USERS') d,       
                  (select sum(bytes)/1024/1024 system_size          
                     from dba_data_files         
                    where tablespace_name like 'SYSTEM' ) st,       
                  (select sum(bytes)/1024/1024/1024 system_size          
                     from dba_data_files         
                    where tablespace_name like 'SYSAUX' ) sa,       
                  (select sum(bytes)/1024/1024/1024 system_size          
                    from dba_data_files         
                   where tablespace_name like 'UNDO%' ) ud,       
                  (select nvl(sum(bytes),0)/1024/1024/1024 temp_size          
                     from dba_temp_files) t,       
                  (select sum(bytes)/1024/1024 redo_size          
                     from sys.v\$log) r,       
                  (select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size        
                     from v\$controlfile) c,       
                  (select sum(bytes)/1024/1024/1024 FREE_MB          
                     from dba_free_space         
                    where tablespace_name like 'SYSTEM' or               
                          tablespace_name like 'SYSAUX' and               
                          tablespace_name like 'UNDO%'  and               
                          tablespace_name like 'TOOLS'  and               
                          tablespace_name like 'USERS') f1,       
                  (select sum(bytes)/1024/1024/1024 FREE_MB          
                     from dba_free_space         
                    where tablespace_name not like 'SYSTEM' and              
                          tablespace_name not like 'SYSAUX' and              
                          tablespace_name not like 'UNDO%'  and              
                          tablespace_name not like 'TOOLS'  and               
                          tablespace_name not like 'USERS') f2,       
                  (select name, (SPACE_LIMIT)/1024/1024/1024 FRATOT, (space_used)/1024/1024/1024 FRAUSED          
                     from V\$RECOVERY_FILE_DEST) fra;
spool off 
EOF

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.