How to use DECODE to create a script for compilation of both packages and package bodies

connect scott/tiger
alter session set nls_language='AMERICAN';
set heading off
set trimspool on
set feedback off
set verify off
set echo off
set pagesize 0
spool recompl.lst
select 'alter '||decode(object_type, 'PACKAGE BODY', 'package', object_type) || ' ' || object_name || ' compile' || decode(object_type, 'PACKAGE BODY', ' body;', ';')
from user_objects
where status = 'INVALID'
order by object_type;
select 'show errors' from dual;
select 'exit' from dual;
spool off
start recompl.lst

How to solve ORA-02180 when specifying COMPRESSION type

You get

ORA-02180: invalid option for CREATE TABLESPACE

when executing a create tablespace statement like this one:

CREATE TABLESPACE test DATAFILE 
  '/u02/oradata/mydb/test.ora' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
COMPRESS FOR OLTP 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

Solution:
Add the DEFAULT keyword to specify the default parameters for the database:

CREATE TABLESPACE test DATAFILE 
  '/u02/oradata/mydb/test.ora' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
DEFAULT
COMPRESS FOR OLTP 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

How cloning from backup became a little more secure in Oracle 11g

From version 11, Oracle supplied another method to use during cloning, namely "Backup-based duplication without a target connection".

From the Oracle 11g Documentation:

RMAN can perform the duplication in any of the following supported modes:

1.       Active duplication
2.       Backup-based duplication without a target connection
3.       Backup-based duplication with a target connection
4.       Backup-based duplication without connection to target and recovery catalog

If you choose method number 2, you need to use the following syntax:

DUPLICATE DATABASE <target database> TO <auxiliary database>;

In the 10g documentation you will need to use the "target" keyword

DUPLICATE TARGET DATABASE TO <auxiliary database>;

Oracle points out:

«This mode is useful when the target database is not available or a connection to it is not desirable». 

In other words, cloning has become more secure since version 11g, where we can totally avoid connecting to the target database, which is often in production, during cloning from backup.

An example of an incident where a connection to the target could potentially jeopardize production, is when you are scripting jobs for RMAN and accidently issue "shutdown", which will bring down your target database and not your auxiliary database, which was what you intended.

For the record, the keyword "target" is an unfortunate choice of syntax during cloning. In IT, a "target" would generally be interpreted as a synonym for "destination", which is where you want to clone *TO*.

The emcli utility

If you're working in a large Enterprise and are using Cloud Control, you will have access to a neat utility called emcli.

emcli is short for "Enterprise Manager Command Line Interface" and is documented here.

In short, emcli can be used to perform scripted jobs on hosts, or on databases running on hosts, that are registered in your Cloud Control 12c.

The jobs you create and execute will show up in Cloud Control under "Enterprise"-->"Jobs"-->"Job Activity"

In this particular case, I wanted to use the

adrci

utility to set new retention policies.
Instead of logging into each and every oracle 11g databaser server, I used emcli to loop through the different groups that our Cloud Control Administrator had created.

To change target from a specific host to a group, exchange the -target to a valid group name, followed by the keyword "group". For example

-targets="prod_db_servers:group"

First, make sure you're logged into emcli:

emcli login -username=sysman -password=mypassword
Login successful

You can query the targets usable by emcli by using "get_targets" like this:

oracle@myomsserver:[OMS]# emcli get_targets |grep -i myserver1

If you have a large enterprise with many targets, you may need to add "-limit_rows" to the "get_targets" command in order to accommodate a larger list, since "get_targets" will automatically limit the rows extracted to 2000:

oracle@myomsserver: [OMS]# emcli get_targets -limit_rows=5000 |grep -i myserver2

The following command will execute a host command of type "file", and the actual file is pointed out by the -input_file flag:

emcli execute_hostcmd -cmd="/bin/sh -s" -osscript="FILE" -input_file="FILE:/u01/app/scripts/change_adrci_policy.sh" -credential_set_name="HostCredsNormal" -targets="myhost.mydomain.no:host"

The file "change_adrci_policy.sh":

# Oric Consulting AS
# Vegard Kasa
# Version 2.0
#!/usr/bin/ksh
# Special coding required for AIX hosts, as there are many different flavours of setting the environment...
#

echo "Hostname: " `hostname`
echo "Running as user: " `whoami`
export NUMDB=`cat /etc/oratab | egrep -v "\#|\*" | sed  '/^$/d' | wc -l | tr -d ' '`
export OS=`uname -s`

 
echo "There are $NUMDB database(s) on this server."
# Loop through the databases extracted from /etc/oratab
for x in $(cat /etc/oratab | grep -v "#" | grep -v "*" | awk -F":" '{print $1}' | sed  '/^$/d'); do
  case $OS in
     'Linux') echo "This is Linux, sourcing .bash_profile...";
              . ~/.bash_profile;
               # set the ORACLE_SID, and make sure LIBPATH is set
              export ORACLE_SID=$x;
              export LIBPATH=$ORACLE_HOME/lib;
              # PATH must be set explictly, even after sourcing profile, to avoid mixing database home binaries
              # and Oracle Agent home binaries.
              export PATH=$ORACLE_HOME/bin:/usr/bin:/bin:/usr/local/bin:/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/sbin:/sbin:/usr/local/drift:/home/oracle/bin;
              echo "adrci exec=\"spool /home/oracle/get_adr_home.lst;show homes;quit\"" > find_adr_home.sh;
              chmod 755 /home/oracle/find_adr_home.sh;
              /home/oracle/find_adr_home.sh;
              export ADR_HOME=`cat /home/oracle/get_adr_home.lst | grep rdbms | grep $ORACLE_SID`;;
        'AIX') echo "This is AIX...";
               echo "Now checking for oracle11g installations..."
               USEDSHELL=`cat /etc/passwd | grep ora11g | cut -f7 -d":"`
               # The user ora11g is not found
               if [ -z "${USEDSHELL}" ]; then
                echo "Oracle user \"ora11g\" does not exist on server `hostname`"
                break;
               # The user ora11g is indeed found in /etc/passwd
               else
                if [ "${USEDSHELL}" = "/usr/bin/bash" ]; then
                  # We found that ora11g is using bash shell. Source .bash_profile
                  echo "Sourcing .bash_profile in this environment..."
                  . ~/.bash_profile
                 else
                   # We found that ora11g is using a shell <> from bash. Source .profile
                   echo "Sourcing .profile in this environment..."
                   . ~/.profile;
                   IAM=`whoami`
                   # We have sourced .profile and the user turns out to be ora10g
                   if [ "${IAM}" = "ora10g" ]; then
                     echo "Oracle installation under user \"ora10g\" found."
                     echo "Only Oracle11g is applicable for adrci purging. Exiting."
                     break;
                   fi
                fi
               fi
               # set the ORACLE_SID, and make sure LIBPATH is set
               export ORACLE_SID=$x;
               export LIBPATH=$ORACLE_HOME/lib
               # PATH must be set explictly, even after sourcing profile, to avoid mixing database home binaries
               # and Oracle Agent home binaries.
               export PATH=$ORACLE_HOME/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/home/ora11g/bin:/usr/bin/X11:/sbin:/usr/java131/bin:/usr/vac/bin:/usr/local/bin;
               # create an executable file on the target server
               echo "adrci exec=\"spool /home/ora11g/get_adr_home.lst;show homes;quit\"" > find_adr_home.sh;
               chmod 755 /home/ora11g/find_adr_home.sh;
               # execute the file
               /home/ora11g/find_adr_home.sh;
               # grep for the ADR home in the log file produced by the execution above. Look for rdbms home
               # Then set the ADR_HOME to the path reflecting the rdbms instance
               export ADR_HOME=`cat /home/ora11g/get_adr_home.lst | grep rdbms | grep $ORACLE_SID`;;
  esac
  # Show the current settings (for logging purposes)
  echo "ORACLE_SID is " $ORACLE_SID
  echo "ORACLE_HOME is: " $ORACLE_HOME
  echo "PATH: " $PATH
  echo "LIBPATH: " $LIBPATH
  echo "adrci located in " `which adrci`
  echo "ADR HOME selected: " $ADR_HOME
  # finally, execute the set control statements in adrci against the rdbms home
  adrci exec="set home $ADR_HOME;show control;set control \(shortp_policy = 336\);set control\(longp_policy=2160\);show control;quit"
done
exit 0

What is the difference between a BEQUEATH connection and an IPC connection?

A bequeath connection

runs on your local host

bypasses the listener

the protocol creates the server process for you directly

An IPC (Inter-Process Communication) connection

will use the native protocol on each OS, but uses the generic term "IPC" for all of them

can only be used when the Client and Server reside on the same host

can only be used by having the Client connect through the Oracle Listener

the Database Listener must be configured to listen on an IPC endpoint

the listener spawns the server process for you

Example setup:

Listener.ora
 LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     )
   )

tnsnames.ora:

proddb01_ipc =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = proddb01)
     )
   )

Connect to your database locally:

sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0. Production on Thu Jan 22 15:35:44 2015

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

SQL> connect scott/tiger
Connected.

From another window, create another session:

sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0. Production on Thu Jan 22 15:35:44 2015

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

SQL> connect scott/tiger@proddb01_ipc
Connected.

Check the connections and their types:

SELECT S.SID, S.OSUSER,S.PROGRAM,S.USERNAME,S.MACHINE, SCI.NETWORK_SERVICE_BANNER,S.LOGON_TIME,S.STATUS
 FROM V$SESSION S INNER JOIN V$SESSION_CONNECT_INFO SCI
 ON S.SID = SCI.SID
 WHERE S.USERNAME = UPPER('scott')
 AND SCI.NETWORK_SERVICE_BANNER LIKE '%IPC%'
 OR  SCI.NETWORK_SERVICE_BANNER LIKE INITCAP('%BEQ%')
 AND S.TYPE <> 'BACKGROUND'
 ORDER BY LOGON_TIME;

And here is the output. Notice how the first session (.. / as sysdba) results in a Bequeath session, while the other one ( ...@proddb_ipc) results in a session using IPC:

SID	OSUSER	PROGRAM	USERNAME	MACHINE	NETWORK_SERVICE_BANNER	LOGON_TIME	STATUS
9	oracle	sqlplus@myserver.mydomain.com (TNS V1-V3)	SCOTT	myserver.mydomain.com	Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production	22.01.2015 15:35:49	INACTIVE
1160	oracle	sqlplus@myserver.mydomain.com (TNS V1-V3)	SCOTT	myserver.mydomain.com	Unix Domain Socket IPC NT Protocol Adaptor for Linux: Version 11.2.0.4.0 - Production	22.01.2015 15:40:44	INACTIVE

Gathering system, data dictionary and fixed object statistics – updated for Oracle 19c

The following script can be used for gathering data dictionary statistics, fixed object statistics and, when specifically required, system statistics.

Update for Oracle Database 19c: The recommendations regarding system statistics have changed somewhat since this post was originally written.

For Oracle 19c, Oracle recommends using the default system statistics in most cases. System statistics affect the costing of every SQL statement in the database, and manually gathered workload statistics can therefore result in execution plan changes across the database.

Consequently, I would not routinely gather workload system statistics on an Oracle 19c database. The system statistics part of the script below is kept as an example, but should only be used when there is a specific reason for doing so and the gathering interval represents the normal workload.

The data dictionary and fixed object statistics are separate from system statistics. Fixed object statistics are still important, but should be gathered when the database has a representative workload, particularly after an upgrade or significant database configuration changes.

alter session set nls_language='AMERICAN';

set timing on
set serveroutput on
set lines 200

col pname format a20
col sname format a20


PROMPT Current system statistics

SELECT sname, pname, pval1, pval2
FROM sys.aux_stats$;


PROMPT
PROMPT ------------------------------------------------------------
PROMPT System statistics
PROMPT ------------------------------------------------------------
PROMPT
PROMPT Oracle 19c: Do NOT gather workload system statistics routinely.
PROMPT Oracle recommends using the default system statistics in most cases.
PROMPT The following is therefore an example only.
PROMPT

/*
PROMPT Gather workload system stats, sample for 1 hour

BEGIN
   DBMS_STATS.GATHER_SYSTEM_STATS (
      gathering_mode => 'INTERVAL',
      interval       => 60,
      statid         => 'DAYTIME');
END;
/

SELECT pname, pval1
FROM sys.aux_stats$;
*/


PROMPT
PROMPT ------------------------------------------------------------
PROMPT Gather data dictionary statistics
PROMPT ------------------------------------------------------------

BEGIN
   DBMS_STATS.GATHER_DICTIONARY_STATS (
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
      method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
      degree           => NULL,
      cascade          => DBMS_STATS.AUTO_CASCADE,
      granularity      => 'AUTO',
      no_invalidate    => DBMS_STATS.AUTO_INVALIDATE);
END;
/


PROMPT
PROMPT ------------------------------------------------------------
PROMPT Gather fixed object statistics
PROMPT ------------------------------------------------------------
PROMPT Run this while the database has a representative workload.
PROMPT

BEGIN
   DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/


exit

Oracle 19c note: If SREADTIM, MREADTIM, CPUSPEED and MBRC are NULL in SYS.AUX_STATS$, while values such as CPUSPEEDNW, IOSEEKTIM and IOTFRSPEED are populated, the database is using the normal noworkload/default system statistics. There is normally no reason to replace these simply because they are old.

Workload system statistics gathered with GATHER_SYSTEM_STATS('INTERVAL') or GATHER_SYSTEM_STATS('START')/('STOP') should be treated as a deliberate optimizer tuning change rather than normal statistics maintenance.

How to relocate the block change tracking file

To relocate the block change tracking file you have two options:

1) shutdown database, mount database, update control file, open database

sqlplus / as sysdba
shutdown immediate
exit
 -- Move the block change tracking file to the new location using the appropriate os utility. --
sqlplus / as sysdba
startup mount
ALTER DATABASE RENAME FILE 'ora_home/dbs/change_trk.f' TO '/new_disk/change_trk.f'; 
ALTER DATABASE OPEN;

OR

2) disable and re-enable block change tracking, and point to the new location when re-enabling.

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'new_location';

See Section "4.4.4.3 Moving the Change Tracking File" in the Oracle Documentation regarding this feature.