TEMP_UNDO_ENABLED: Increase performance and decrease undo segment usage

Oracle 12c has a new parameter called TEMP_UNDO_ENABLED.

This parameter can be set at the SESSION or SYSTEM level. When it is set to TRUE, all undo information for temporary tables is written to the temp tables themselves and not to the database undo tablespace.

Background
Undo space can be a problem for the database, especially if the application code is written in such a way that it holds transactions open for a long time (generally a bad practice) or there are long-running queries in the database. If the undo tablespace runs out of space, the database will generate ORA-01650: Unable to extend rollback segment for attempts to update the database and ORA-01555: Snapshot too old for long-running queries. Each of these will cause application errors, resulting in unhappy users.

Benefits

• Enabling temp undo helps with both of these problems by removing all undo for temporary tables from the database undo tablespace, giving more space for DML operations.
• Undo retention will more accurately reflect the database needs and not be skewed by temporary table usage. This may help meet retention targets for FLASHBACK operations.
• Less redo is generated against the database as well. Temporary table DML does not generate redo so rollbacks against temporary tables do not generate redo either.
• Operations that use undo (database recovery, Log Miner, etc.) will complete faster since the temp undo is not included.

Notes

• This setting affects operations against temporary tables only. Other uses of the temporary tablespaces (sorts, index creations, select distinct, etc.) will not be affected.
• The first time a session uses temporary tables, the current value of TEMP_UNDO_ENABLED will be used for the rest of the session and cannot be changed.

Problem starting Cluster Services when installing 11gr2 on RHEL 7

Recently I installed Oracle RAC 11gr2 (11.2.0.4) on Red Hat Enterprise Linux 7 and ran into a problem running the root.sh script towards the end of the installation.

According to Oracle support, Red Hat Enterprise Linux 7 is supported by Oracle for 11gr2 (11.2.0.4). However, I found out the hard way that it will not successfully install without manual intervention. Here is what I did to solve the problem:

The Oracle installation went well until the point of running the root.sh script. At that point it was unable to start the Oracle Clusterware Stack:

How to turn off all scheduled jobs for a database

Sometimes it is useful to disable all scheduled jobs for a database (for example if you are running a performance benchmark.) You can disable each job manually and then enable them again but it is easier to set the allowed number of job processes to zero.

alter system set JOB_QUEUE_PROCESSES=0 scope=both;

Now check the setting:

show parameter JOB_QUEUE_PROCESSES
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0

Now no scheduled jobs will be run.

How to use udev rules instead of ASMLIB to configure LUNs for Oracle 11g ASM on Red Hat Enterprise Linux 6.4

Oracle ASM depends upon the names of each LUN device to determine which LUN belongs in a disk group. The problem with Linux is that these LUN device names may change upon reboot. In the past I have used ASMLIB from Oracle to help with this but I found that udev rules are actually easier to use and do not rely on third-party software to get the job done.

I will use the following LUNs for this example:

Purpose	Size	Device Name
DATA	50 GB	/dev/sddlmab
FRA	30 GB	/dev/sddlmac
TEMP	20 GB	/dev/sddlmad
REDO	10 GB	/dev/sddlmae

Udev rules can be used to give consistent names to devices when the server boots. In order to do this you need a unique identifier for each LUN that does not change on boot. This identifier is the SCSI ID. You can get this using the scsi_id command against each LUN device name:

Easier Passwordless SSH with ssh-copy-id

You may be familiar with the many steps involved in setting up Passwordless SSH for an Oracle cluster but there is a utility that simplifies the process considerably: ssh-copy-id.

First generate the public and private keys using ssh-keygen as usual:

oracle@dbserver-1$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/home/oracle/.ssh/id_rsa):[Enter key]
Enter passphrase (empty for no passphrase): [Press enter key]
Enter same passphrase again: [Press enter key]
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
33:b3:fe:af:95:95:18:11:31:d5:de:96:2f:f2:35:f9 oracle@dbserver-1

After that you can append the key values and set the proper permissions easily using ssh-copy-id:

oracle@dbserver-1$ ssh-copy-id -i ~/.ssh/id_rsa.pub dbserver-2
oracle@dbserver-2's password:
Now try logging into the machine, with "ssh 'dbserver-2'", and check in:

.ssh/authorized_keys

to make sure we haven't added extra keys that you weren't expecting.

Now you are able to ssh into the remote server without a password:

oracle@dbserver-1$ ssh dbserver-2
Last login: Fri Aug 16 14:27:56 PDT 2013 from 192.168.1.2

oracle@dbserver-2$

How to make Ethernet settings on Linux persist after a reboot

Ethtool is a Linux utility that displays and changes Ethernet card settings. For example, to turn on flow control for Ethernet device 5 you can issue the following command:

ethtool –A eth5 tx on rx on

The problem is that these settings are lost on reboot of a server. While there is no way to make these settings persistent using ethtool by itself, you can make them persistent with a script. I found that the script /etc/sysconfig/network-scripts/ifup-ethpost checks for a script called /sbin/ifup-local on boot. If it exists then it is run once for each Ethernet device, and the device name is passed in as an argument. In my particular case I needed to turn on flow control for Ethernet devices 4 and 5 so I created the script and put the following piece of code into it:

#!/bin/bash
case "$1" in
 eth[45])
  /sbin/ethtool -A $1 rx on tx on
  ;;
esac
exit 0

You must also make sure that the script is executable or it will not run:

chmod +x /sbin/ifup-local

I have also determined that the calling script is named /etc/sysconfig/network-scripts/ifup-post on RHEL 5.6.

Quickly and Simply Reconfiguring a UNIX Server using sys-unconfig

Sys-unconfig is a useful utility for Solaris and Linux that allows you to reconfigure your networking. It is easier than doing it manually, which involves editing some or all of the following files depending upon the operating system and version:

• /etc/defaultrouter
• /etc/dladm/*
• /etc/dumpadm.conf
• /etc/hostname
• /etc/inet/hosts
• /etc/nodename
• /etc/nsswitch.conf
• /etc/resolv.conf

Sys-unconfig is used for restoring a server's configuration back to its original state, before it was configured. It "unconfigures" the following:

• Host Name
• Domain Name
• Time Zone
• IP Address
• IP Subnet Mask
• Root Password

Moving an Existing Oracle 11g Database to a New Server

Sometimes you have a database installed on external storage and you need to move it to a new server. If your Oracle binaries are installed locally then you can't just install a new set of binaries, mount the volumes containing the database files and start the database. This is because Oracle creates directory structures and files under the Oracle binary directories. This article is meant to help you make these changes to your binary installation so it will recognize the new database. It is assumed that the operating systems of the source and target servers are the same and that the version of Oracle on both servers is the same also.

Create Users and Groups
Create any Oracle users and groups on the new server and make sure that the user IDs and group IDs are the same as they are on the source server. You can verify this on UNIX by checking the /etc/passwd file for user IDs and /etc/group for group IDs.

Install Oracle Binaries
The first step is to install the Oracle binaries as normal. Take note of the location of $ORACLE_BASE and $ORACLE_HOME directories and the $ORACLE_SID and substitute them where needed below.

How to configure native multipathing for Solaris 10

To configure Solaris 10's native multipathing you should first check to see if it is enabled. Look at the Fibre Channel Port Driver Configuration file located at /kernel/drv/fp.conf. By default multipathing is disabled on SPARC-based systems and enabled on x86-based systems.

Find the entry for mpxio-disable and set it to "no" like so:

mpxio-disable="no";

How to run a script on reboot on Red Hat Linux

This technique can be useful for running a service on reboot. It has been tested and known to work on Red Hat Linux 5.5 but will probably work on most versions of Linux since they mostly use a similar system of starting services at reboot.

You will need to put a wrapper script for your service under /etc/rc.d that must be owned by root. By convention it does not need an extension. In this example my wrapper script is called "myscript". The header is required to have a certain format (see below for an example) and the operating system will check for certain tokens in the comments. You can create different functions in the script corresponding to different conditions such as start, stop, status, restart, or reload. To keep it simple I will just deal with start and stop here. The following script is an example of a wrapper script for the script that you want to run. The actual script that you run will be called from this script. In this example I have called the script to be run "myservice.sh".

Operating system configuration settings for Oracle 11g and Solaris 10

Oracle's Health Check utility recommends that you set certain parameters when installing on Solaris 10. Here is a summary of some common parameters and how to set them:



Host Name
RDA Rule A00320: /etc/hosts Format (Missing host.domain)
This rule checks that the host name is configured the way that Oracle wants to see it. To fix this, edit /etc/hosts to append the domain name to the existing host name. Also include the tab-separated bare host name or the server may not boot:

123.45.678.90   myhost.domain.local        myhost      

Kernel Parameters
Rule A00330: Kernel Parameters OK? (NOEXEC_USER_STACKUndef)
There is a lot of confusion about how to set kernel parameters since things have changed with Solaris 10. This particular parameter can be set in /etc/system under the "set" section:

set noexec_user_stack=1

There is an additional problem with Solaris when you are doing a preinstallation check with RDA. Some modules are only loaded into memory the first time they are accessed by an application. Therefore, when you run the RDA check before Oracle is installed they will not show correctly, even if they are set correctly. To force the modules to load at boot time you can add these lines to /etc/system under the "forceload" section:

forceload: sys/shmsys
forceload: sys/semsys

Reboot after making these changes.

How to fix problem with ASM not recognizing multipathed disks after a reboot

After installing ASM 11g on Red Hat 5.5 using the Linux multipath software the disk permissions are not set properly by ASMLib when the system is rebooted. This results in the following error in /var/log/oracleasm:

oracleasm-read-label: Unable to open device "/dev/dm-0": No such file or directory

ASM will not mount the disks and of course the database will not start.

I tried the following:
- I checked that all the correct versions of the ASM RPMs were installed.
- I performed the oracleasm configure, which is supposed to properly set the permissions on reboot.
- I tried setting the ORACLEASM_SCANORDER per the documentation about setting up ASM on Linux Multipath disks [Document Id 602952.1].

None of these things worked.

Finally I added these lines to the top of the script /etc/init.d/oracleasm and the problem is solved:

chown grid:oinstall /dev/dm*
chmod 775 /dev/dm*

Ownership and permissions for ASM devices

When preparing devices for ASM set the following ownership and permissions:

chown <asm owner>:<asmdba group> <device>
chmod 660 <device>

Example:

chown grid:oinstall c5t60060E8006CFCC000000CFCC00000204d0s0
chmod 660 c5t60060E8006CFCC000000CFCC00000204d0s0

Mount an ISO image on Solaris

lofiadm -a /image-dir/image-name.iso
/dev/lofi/1

mount -F hsfs -o ro /dev/lofi/1 /mnt

Clean the header of an ASM volume so it can be reused

As root run the dd command on the device, which will write zeroes to the first 40 MB, wiping the header.

Example:

dd if=/dev/zero of=c9t50060E8006CFCC11d9s0 bs=1048576 count=40

124861-15 patch prerequisite can be safely ignored when installing Oracle 11g on Solaris 10

When installing 11gr2 db on Solaris 10 the following error comes up:

OS Patch:124861-15 
- This is a prerequisite condition to test whether the patch "124861-15" is available on the system.
Expected Value
 : Patch 124861-15
Actual Value
 : Patch missing
 List of errors: 
 - 
PRVF-9809 : Proper OS Patch is not found on node "&ltNode Name>" 
[Expected = "124861-15" ; Found = "Patch missing"]  - Cause:  Required OS Patch is not applied.  
- Action:  Apply the required OS Patch. 

This can be safely ignored according to MOS doc id 969497.1:

Cause
OUI should only check for this optional patch while installing the Pro*C precompiler.

If Pro*C is not being installed, OUI should not check for this patch.

Support opened Bug 9214210 for this issue

Deinstall ASM on standalone

First deconfigure clusterware
Run as root

cd <GRID_HOME>/crs/install
perl rootcrs.pl -deconfig -force

Now Deinstall
Run as ASM owner

cd <GRID_HOME>/deinstall

./deinstall

If you want to reuse the ASM volumes under a different user name you can wipe the headers like this.

Script to create Standard Groups, Users, and Paths on Solaris 10

#!/bin/ksh
# Example of Creating Standard Groups, Users, and Paths
# From Oracle Grid Infrastructure Installation Guide 11g Release 2 (11.2) for Oracle Solaris E24616-01

groupadd -g 1000 oinstall
groupadd -g 1020 asmadmin
groupadd -g 1021 asmdba
groupadd -g 1031 dba
useradd -m -d /export/home/grid -s /bin/bash -u 1100 -g oinstall -G dba,asmadmin grid
passwd grid
useradd -m -d /export/home/oracle/ -s /bin/bash -u 1101 -g oinstall -G dba,asmdba oracle
passwd oracle
mkdir -p /u01/app/11.2.0/grid
mkdir -p /u01/app/grid
chown -R grid:oinstall /u01
mkdir /u01/app/oracle
chown oracle:oinstall /u01/app/oracle
chmod -R 775 /u01/

mkdir /opt/oracle
mkdir /opt/oraInventory
chown oracle:oinstall /opt/oracle
chown oracle:oinstall /opt/oraInventory
chmod 755 /opt/oracle
chmod 755 /opt/oraInventory

Add swap space to Solaris 10 zfs

# zfs create -V 16gb rpool/newswap
# swap -a /dev/zvol/dsk/rpool/newswap

Configure Yum on Linux to read from a local repository

This has been tested on Red Hat Linux 5.4.
This example assumes you have an ISO image stored on an NFS volume.

Mount the NFS volume

mount -t nfs <Host Name or IP Address>:/<Path to Directory with ISO> <Local Mount Point>
Example:
mount -t nfs MyVolHost:/isodir /mnt

cd /mnt

Mount the ISO image

mount -o loop image-name.iso /mnt2

Create a local repository file and point it to the ISO image

vi /etc/yum.repos.d/local.repo

[local]
name=Red Hat Local Repository
baseurl=file:///mnt2/Server
enabled=1
gpgcheck=0 

From here you should be able to use Yum normally with your local repository.

Show session information for a pending transaction

select s.sid,
       s.serial#,
       s.username,
       s.machine,
       s.status,
       s.lockwait,
       t.used_ublk,
       t.used_urec,
       t.start_time
       from v$transaction t 
       inner join v$session s on t.addr = s.taddr;

Example:

       SID    SERIAL# USERNAME   MACHINE   STATUS   LOCKWAIT          USED_UBLK  USED_UREC START_TIME
---------- ---------- --------   --------- -------- ---------------- ---------- ---------- --------------------
        29       7789 SYS        thehost   ACTIVE                             1          2 04/07/11 21:26:29

Generate AWR reports for all snapshots

This script will go through your AWR repository and generate an AWR report for each snapshot. Depending on the size of your repository, this may take a while to run. For example, a repository of 1 week will generate (7 * 24) 168 reports. The script sleeps 10 seconds between generating each report so it doesn't put a strain on the database.

set serveroutput on;
set echo off;
set feedback off;
set timing off;
set verify off;

define tempfile = _tmp.run.all.awr.sql

spool &tempfile;

DECLARE  
     db_name v$database.name%TYPE;
     dbid v$database.dbid%TYPE;
     inst_num v$instance.instance_number%TYPE;
     inst_name v$instance.instance_name%TYPE;
     begin_snap NUMBER;
     end_snap NUMBER;
     high_snap NUMBER;
     low_snap NUMBER;
     num_days NUMBER;
     report_type VARCHAR2(4);
     report_name VARCHAR2(100);
BEGIN

     report_type := 'html';
     num_days := 0;

     select instance_name, instance_number 
       into inst_name, inst_num from v$instance;

     select dbid, name 
       into dbid, db_name from v$database;

     select min(snap_id) into low_snap from dba_hist_snapshot
       where dbid = dbid
         and instance_number = inst_num
       order by snap_id;

     select max(snap_id) into high_snap from dba_hist_snapshot
       where dbid = dbid
         and instance_number = inst_num
       order by snap_id;

     begin_snap := low_snap;

     while begin_snap < high_snap loop

         end_snap := begin_snap + 1;

         select 'awrrpt.' || db_name || '.' || inst_num || '.' || begin_snap || '-' -
|| end_snap || '.' || report_type into report_name from dual;

         dbms_output.put_line('exec dbms_lock.sleep(10);');
         dbms_output.put_line('define inst_num = ' || inst_num || ';');
         dbms_output.put_line('define num_days = ' || num_days || ';');
         dbms_output.put_line('define inst_name = ' || inst_name || ';');
         dbms_output.put_line('define db_name = ' || db_name || ';');
         dbms_output.put_line('define dbid = ' || dbid || ';');
         dbms_output.put_line('define begin_snap = ' || begin_snap || ';');
         dbms_output.put_line('define end_snap = ' || end_snap || ';');
         dbms_output.put_line('define report_type = ' || report_type || ';');
         dbms_output.put_line('define report_name = ' || report_name || ';');
         dbms_output.put_line('@@?/rdbms/admin/awrrpt.sql;');

         begin_snap := begin_snap + 1;

     end loop;

END;
/

spool off;

@&tempfile

exit

Run AWR Report Daily

This script can be run from cron. It will generate an AWR report for the last 24 hours.

set serveroutput on;
set echo off;
set feedback off;
set timing off;
set verify off;

define tempfile = _tmp.run.awr.sql

spool &tempfile;

DECLARE
     db_name v$database.name%TYPE;
     dbid v$database.dbid%TYPE;
     inst_num v$instance.instance_number%TYPE;
     inst_name v$instance.instance_name%TYPE;
     begin_snap NUMBER;
     end_snap NUMBER;
     num_days NUMBER;
     report_type VARCHAR2(4);
     report_name VARCHAR2(100);
BEGIN

     report_type := 'html';
     num_days := 0;

     select instance_name, instance_number
       into inst_name, inst_num from v$instance;

     select dbid, name
       into dbid, db_name from v$database;

     select max(snap_id)
       into begin_snap
       from dba_hist_snapshot
       where begin_interval_time <= (select sysdate - 1 from dual)
         and dbid = dbid
         and instance_number = inst_num
       order by snap_id;

     select max(snap_id) into end_snap from dba_hist_snapshot order by snap_id;

     select 'awrrpt.' || db_name || '.' || inst_num || '.' || begin_snap || '-' || -
end_snap || '.' || report_type into report_name from dual;

     dbms_output.put_line('define inst_num = ' || inst_num || ';');
     dbms_output.put_line('define num_days = ' || num_days || ';');
     dbms_output.put_line('define inst_name = ' || inst_name || ';');
     dbms_output.put_line('define db_name = ' || db_name || ';');
     dbms_output.put_line('define dbid = ' || dbid || ';');
     dbms_output.put_line('define begin_snap = ' || begin_snap || ';');
     dbms_output.put_line('define end_snap = ' || end_snap || ';');
     dbms_output.put_line('define report_type = ' || report_type || ';');
     dbms_output.put_line('define report_name = ' || report_name || ';');
     dbms_output.put_line('@@?/rdbms/admin/awrrpt.sql;');

END;
/

spool off;

@&tempfile

exit

Streams Troubleshooting

Check status of Capture process

SELECT STATUS FROM DBA_CAPTURE WHERE CAPTURE_NAME = 'CAPTURE';

STATUS
--------
DISABLED

Determine what caused capture process to abort

COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A10
COLUMN STATUS_CHANGE_TIME HEADING 'Abort Time'
COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 99999999
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A40

SELECT CAPTURE_NAME, STATUS_CHANGE_TIME, ERROR_NUMBER, ERROR_MESSAGE
  FROM DBA_CAPTURE WHERE STATUS='ABORTED';

Check status of Apply process

SELECT STATUS FROM DBA_APPLY WHERE APPLY_NAME = 'APPLY';

STATUS
--------
DISABLED

Determine what caused apply process to abort

COLUMN APPLY_NAME HEADING 'APPLY|Process|Name' FORMAT A10
COLUMN STATUS_CHANGE_TIME HEADING 'Abort Time'
COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 99999999
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A40

SELECT APPLY_NAME, STATUS_CHANGE_TIME, ERROR_NUMBER, ERROR_MESSAGE
  FROM DBA_APPLY WHERE STATUS='ABORTED';

From "Oracle® Streams Concepts and Administration 10g Release 2 (10.2)"

Check free space in ASM

1. Set SID to ASM instance.

2. asmcmd

3. lsdg

Example output:

ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
MOUNTED  EXTERN  N         512   4096  1048576    103575      678                0             678              0  PRE1_VOLNAME_000/
MOUNTED  EXTERN  N         512   4096  1048576     34525    24821                0           24821              0  PRE2_VOLNAME_000/

Disable/Enable all foreign keys pointing to a table

If you want to truncate a table that is referenced by foreign key constraints in other tables, oracle will generate ORA-02266: unique/primary keys in table referenced by enabled foreign keys.

This script accepts the table name and then generates scripts that will disable and enable the foreign keys so you can truncate the table:

set linesize 300;
set pagesize 9999;
set echo off;
set feedback off;
set heading off;
set verify off;

accept tablename prompt "Enter Table Name: ";

spool _disable.fk.constraints.sql

select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' DISABLE CONSTRAINTS ' || CONSTRAINT_NAME || ';'
       from dba_constraints
       where r_constraint_name = (select constraint_name from dba_constraints
                                         where table_name = UPPER('&tablename')
                                         and constraint_type = 'P')
       and constraint_type = 'R' ;

spool off


spool _enable.fk.constraints.sql

select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' ENABLE CONSTRAINTS ' || CONSTRAINT_NAME || ';'
       from dba_constraints
       where r_constraint_name = (select constraint_name from dba_constraints
                                         where table_name = UPPER('&tablename')
                                         and constraint_type = 'P')
       and constraint_type = 'R' ;

spool off

Execute _disable.fk.constraints.sql, truncate the table and then execute _enable.fk.constraints.sql.

Configure vi so it doesn't clear screen on Linux

When using Linux I noticed that it clears the screen after quitting out of the file you were editing. If you do not like this behavior here is how to avoid it.

Apparently on most distros of Linux vi is an alias of vim.

Create or edit a file named .vimrc in your home directory and add the following line:

set t_ti= t_te=

Now vi will behave the same on Linux as it does on other flavors of UNIX.

This has been tested on Red Hat 5.

Restart Data Guard log shipping

From standby database:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;

Check alert log to see that logs are being applied:

Thu Sep 16 09:51:09 2010
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session
Thu Sep 16 09:51:09 2010
Media Recovery Log +FLASH/{database}/archivelog/2010_09_13/thread_2_seq_12120.1251.729645939
Media Recovery Log +FLASH/{database}/archivelog/2010_09_13/thread_1_seq_12752.7696.729642661
Media Recovery Log +FLASH/{database}/archivelog/2010_09_14/thread_1_seq_12753.5454.729657061
Media Recovery Log +FLASH/{database}/archivelog/2010_09_14/thread_2_seq_12121.6595.729649857
Thu Sep 16 09:51:21 2010
Media Recovery Log +FLASH/{database}/archivelog/2010_09_14/thread_2_seq_12122.1235.729657063
Media Recovery Log +FLASH/{database}/archivelog/2010_09_14/thread_2_seq_12123.9205.729664261

If this doesn't work then try the following:

Stop/start the standby database.

Disable/Enable log shipping on primary, which may restart log shipping automatically.

Script to complete crosscheck and remove expired archivelogs

#!/bin/ksh

export RMAN_LOGFILE=crosscheck.and.rm.arc.logs.log

(
echo "connect target /"
echo "delete noprompt archivelog until time 'sysdate -1' ;"
echo "crosscheck archivelog all ;"
echo "exit ;"
) |
$ORACLE_HOME/bin/rman LOG $RMAN_LOGFILE

# rman returns nonzero result for any errors encountered in command set
export ERRORCODE=$?

if [ "$ERRORCODE" = "0" ]
then
  echo "RMAN SUCCESSFUL completion of crosscheck"
else
  echo "RMAN FAILED completing crosscheck"
fi

courtesy of Gerald Bowers

Spool to a file with the database name

    column dbn new_value dbname noprint
    select global_name dbn from global_name;

    spool &dbname..txt

Run AWR, ASH, ADDM reports

--AWR Reports
$ORACLE_HOME/rdbms/admin/awrrpt.sql
--AWR Reports for multiple nodes
$ORACLE_HOME/rdbms/admin/awrrpti.sql

--ASH Reports
$ORACLE_HOME/rdbms/admin/ashrpt.sql
--ASH Reports for multiple nodes
$ORACLE_HOME/rdbms/admin/ashrpti.sql

--ADDM Reports
$ORACLE_HOME/rdbms/admin/addmrpt.sql
--ADDM Reports for multiple nodes
$ORACLE_HOME/rdbms/admin/addmrpti.sql

Useful commands for temp table spaces

Show usage:

select TABLESPACE_NAME, BYTES_USED/1024/1024 "MB Used", BYTES_FREE/1024/1024 "MB Free" from V$TEMP_SPACE_HEADER;

List tempfiles:

select file_name, tablespace_name, bytes/1024/1024, autoextensible from dba_temp_files;

Add a tempfile:

ALTER TABLESPACE TempTablespaceName ADD TEMPFILE 'TempfilePathAndName' SIZE 1G;

Modify a tempfile:

alter database tempfile 'TempfilePathAndName' autoextend on next 1G maxsize 31G;


Clean up archive logs using rman

Removes archive logs from archive log destination.

rman
connect target /
delete noprompt archivelog until time 'sysdate -1' ;
crosscheck archivelog all ;
-- Will clean up logs deleted from command line
delete noprompt expired archivelog all ;

Export user details including grants

This writes to a script called create.<userName>.sql which can then be executed to create the user in another db.

Note that it may throw an error if the dba_java_policy view does not exist. Simply edit the output file if that happens.

set echo off;
set linesize 80;
set pagesize 9999;
set feedback off;
set heading off;
set verify off;
set timing off;

accept user prompt "Enter User Name: ";

spool create.&user..sql;

select 'create user '||username||' identified by values '||chr(39)||password||chr(39)||'
       default tablespace '||default_tablespace||' temporary tablespace '||temporary_tablespace||' profile '||profile||';'
       from sys.dba_users
       where username = upper('&user');


select 'grant '||privilege||' to '||grantee||' with admin option;'
       from sys.dba_sys_privs
       where grantee = upper('&user')
         and admin_option = 'YES';

select 'grant '||privilege||' to '||grantee||' ;'
       from sys.dba_sys_privs
       where grantee = upper('&user')
         and admin_option = 'NO';


select 'grant '||granted_role||' to '||grantee||' with admin option;'
       from sys.dba_role_privs
       where grantee = upper('&user')
         and admin_option = 'YES';

select 'grant '||granted_role||' to '||grantee||';'
       from sys.dba_role_privs
       where grantee = upper('&user')
         and admin_option = 'NO';

select 'alter user '||'&user'||' quota '||decode(max_bytes, -1, 'unlimited', max_bytes)||' on '||tablespace_name||';'
       from sys.dba_ts_quotas
       where username = upper('&user');

select 'alter user &user default role all ; ' from dual ;

select 'grant '||privilege||' on '||owner||'."'||table_name||'" to '||grantee||' with grant option ;' 
       from dba_tab_privs 
       where grantee = upper('&user')
         and grantable = 'YES';

select 'grant '||privilege||' on '||owner||'."'||table_name||'" to '||grantee||' ;' 
       from dba_tab_privs 
       where grantee = upper('&user')
         and grantable = 'NO';

select kind||' '||type_schema||' '||type_name||' '||name||' '||action||' 'enabled from dba_java_policy where grantee = upper('&user');

spool off;

Delete all objects from a Schema

Useful when doing an Import so you don't have to recreate the user, grants, db links, etc.

set echo off;
set feedback off;
set heading off;
set verify off;
set linesize 300;

accept schemaName prompt "Enter Schema Name: ";

select to_char(sysdate, 'MM-DD-YYYY HH:MM:SS') from dual;

prompt Disabling Constraints. Logged to disable.constraints.log...;

set termout off;

spool _disable.constraints.sql;

select 'spool disable.constraints.log append;' from dual;

select 'alter table ' || '&schemaName' || '.' || c.table_name || 
' disable constraint ' ||  constraint_name || ' cascade;'
   from dba_constraints c, dba_tables t
   where c.table_name = t.table_name
     and t.owner = upper('&schemaName')
    order by t.table_name,
             constraint_name;

select 'spool off;' from dual;

spool off;

@_disable.constraints.sql;

set termout on;

select to_char(sysdate, 'MM-DD-YYYY HH:MM:SS') from dual;

prompt Constraints Disabled.;
prompt Dropping all objects for Schema &schemaName.. Logged to drop.schema.objects.log...;

set termout off;

spool _drop.schema.objects.sql;

select 'spool drop.schema.objects.log append;' from dual;

select 'drop table ' || '&schemaName' || '.' || table_name || 
' cascade constraints;' 
    from dba_tables where owner = upper('&schemaName')
    order by table_name;

select 'drop ' || object_type || ' ' || '&schemaName' || '.' || 
object_name || ';'
    from dba_objects
    where owner = upper('&schemaName')
      and object_type NOT IN ('PACKAGE BODY', 'UNKNOWN', 'DATABASE LINK', 
'TABLE', 'INDEX', 'TRIGGER', 'JOB', 'LOB')
    order by object_type,
             object_name;

select 'spool off;' from dual;

spool off

@_drop.schema.objects.sql

set termout on;

select to_char(sysdate, 'MM-DD-YYYY HH:MM:SS') from dual;

prompt Objects dropped. Check drop.schema.objects.log for errors.;

select to_char(sysdate, 'MM-DD-YYYY HH:MM:SS') from dual;

prompt Purging Recycle Bin...

purge dba_recyclebin;

select to_char(sysdate, 'MM-DD-YYYY HH:MM:SS') from dual;

prompt Script Complete.

Check available space on ASM disk groups

column name format a24;

select name, free_mb, total_mb from v$asm_diskgroup;

Example:

NAME                        FREE_MB   TOTAL_MB
------------------------ ---------- ----------
DATA_EPBS02P                 726569     908162
DATA_IATM01P                  26768      34987
DONOTUSE                     664413     664753
FLASH_EPBS02P                229632     244909
FLASH_IATM01P                 33208      34987

Show free blocks in temp tablespace

select tablespace_name, segment_file, total_blocks, free_blocks, free_blocks / total_blocks * 100 "% Free" 
    from V$SORT_SEGMENT;

Example:

TABLESPACE_NAME                                      SEGMENT_FILE TOTAL_BLOCKS FREE_BLOCKS     % Free
---------------------------------------------------- ------------ ------------ ----------- ----------
TEMP                                                            0        84352       82176 97.4203338

Run UNIX shell script from Stored Procedure via DBMS_SCHEDULER

When you try to run a UNIX shell script directly from DBMS_SCHEDULER it runs as the UNIX owner nobody:nobody, which means it will likely have permissions problems and give the error ORA-27369. It is possible to configure DBMS_SCHEDULER so it will run as the oracle user (Metalink Doc ID 389685.1) but if you can't or don't want to do that you can do this:

First you have to give Java permission to access the script:

begin
dbms_java.grant_permission
('SYS',
 'java.io.FilePermission',
 '[path]/test.sh',
 'execute');

dbms_java.grant_permission
('SYS',
 'java.lang.RuntimePermission',
 '*',
 'writeFileDescriptor' );
end;

After that create the Java procedure itself:

create or replace and compile
java source named "Util"
as
import java.io.*;
import java.lang.*;

public class Util extends Object
{
public static int RunThis(String args)
{
Runtime rt = Runtime.getRuntime();
int        rc = -1;

try
{
   Process p = rt.exec(args);

   int bufSize = 4096;
   BufferedInputStream bis =
    new BufferedInputStream(p.getInputStream(), bufSize);
   int len;
   byte buffer[] = new byte[bufSize];

   // Echo back what the program spit out
   while ((len = bis.read(buffer, 0, bufSize)) != -1)
      System.out.write(buffer, 0, len);

   rc = p.waitFor();
}
catch (Exception e)
{
   e.printStackTrace();
   rc = -1;
}
finally
{
   return rc;
}
}
}
/

Then a function that calls the java procedure:

create or replace
function RUN_CMD(p_cmd in varchar2) return number
as
language java
name 'Util.RunThis(java.lang.String) return integer';
/

And finally the stored procedure:

create or replace procedure RC(p_cmd in varchar2)
as
x number;
begin
x := run_cmd(p_cmd);
end;
/

After that it can be run from DBMS_SCHEDULER like this:

SQL>BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'MyJob',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN  rc(''[path]/test.sh''); END;',
    start_date      => SYSTIMESTAMP,
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'UNIX shell script run from stored procedure');
  END;
  /

PL/SQL procedure successfully completed.

Of course it can also be set up with a schedule, etc. like any other stored procedure.

Generate DDL for all Indexes in a Schema

set pagesize 0
set linesize 1000
set long 500000
set head off
set trimspool on
set feedback off
set echo off
set verify off

column theClob format a400

accept schemaName prompt "Enter Schema Name: "
accept outputFile prompt "Enter Output File: "

spool '&outputFile'

EXEC dbms_metadata.SET_TRANSFORM_PARAM (dbms_metadata.SESSION_TRANSFORM, 'PRETTY', true);

SELECT DBMS_METADATA.GET_DDL('INDEX', dba_indexes.index_name, '&schemaName') as theClob from dba_indexes where owner = '&schemaName';

spool off

Generate DDL for all Tables in a Schema


set pagesize 0
set linesize 1000
set long 500000
set head off
set trimspool on
set feedback off
set echo off
set verify off

column theClob format a400

accept schemaName prompt "Enter Schema Name: "
accept outputFile prompt "Enter Output File: "

spool '&outputFile'

EXEC dbms_metadata.SET_TRANSFORM_PARAM (dbms_metadata.SESSION_TRANSFORM, 'PRETTY', true);

SELECT DBMS_METADATA.GET_DDL('TABLE', dba_tables.table_name, '&schemaName') as theClob from dba_tables where owner = '&schemaName';

spool off



List constraints on a table

set verify off
set echo off
set pagesize 200
set linesize 150

column type format a10 heading "Type"
column cons_name format a30 heading "Name"
column status heading "Status"
column last_change heading "Last Chg"

accept tableName prompt "Table: "

select  decode(constraint_type,
                'C', 'Check',
                'O', 'R/O View',
                'P', 'Primary',
                'R', 'Foreign',
                'U', 'Unique',
                'V', 'Check view') type,
        constraint_name cons_name,
        status,
        last_change
from dba_constraints
where table_name = upper('&tableName')
order by 1
/

List indexes on a table

set verify off
set echo off
set pagesize 9999
set linesize 300

column index_name format a32  heading "Index"
column column_name format a32 heading "Column"
column column_position format 999 heading "Pos"
column descend heading "Asc?"

accept tableName prompt "Table: "

select index_name,
       column_position,
       column_name,
       descend
from   dba_ind_columns
       where table_name = upper('&tableName')
       order by index_name,
                column_position;

Explain plan within Sqlplus

On:
set autotrace trace explain
Off:
set autotrace off

Determine run mode of database instance

SQL> select status from v$instance;


STATUS
------------------------------------
OPEN

May be OPEN, UPGRADE, OPEN MIGRATE, etc.

Determine pool sizes

SQL> select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;

POOL             MBYTES
------------ ----------
java pool             4
large pool            4
shared pool         148

Find location of online redo logs

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/te11/u02/ORACLE/te11/te11.redo.g01.m01.rdo
/te11/u03/ORACLE/te11/te11.redo.g01.m02.rdo
/te11/u02/ORACLE/te11/te11.redo.g02.m01.rdo
/te11/u03/ORACLE/te11/te11.redo.g02.m02.rdo
/te11/u02/ORACLE/te11/te11.redo.g03.m01.rdo
/te11/u03/ORACLE/te11/te11.redo.g03.m02.rdo
/te11/u02/ORACLE/te11/te11.redo.g04.m01.rdo
/te11/u03/ORACLE/te11/te11.redo.g04.m02.rdo
/te11/u02/ORACLE/te11/te11.redo.g05.m01.rdo
/te11/u03/ORACLE/te11/te11.redo.g05.m02.rdo

10 rows selected.

Oracle install ships with unzip

If you need unzip on a system where it is not installed, check $ORACLE_HOME/bin.

You can also use jar -xvf.

Determine data cache hit ratio

Hit Ratio since database start

column physicalReads        format 999,999,999  heading 'Physical Reads'
column gets                 format 999,999,999  heading 'DB Block Gets'
column consistentGets       format 999,999,999  heading 'Consistent Gets'
column hitRatio             format 999.99       heading 'Hit Ratio'

select  sum(decode(name, 'physical reads', value, 0)) physicalReads,
        sum(decode(name, 'db block gets', value, 0)) gets,
        sum(decode(name, 'consistent gets', value, 0)) consistentGets,
        (1 - 
        (sum(decode(name, 'physical reads', value, 0)) /
        (sum(decode(name, 'db block gets', value, 0)) +
        sum(decode(name, 'consistent gets', value, 0))))) *
        100 hitRatio
from    v$sysstat;

Example:

Physical Reads DB Block Gets Consistent Gets Hit Ratio
-------------- ------------- --------------- ---------
        27,056       156,566       1,844,389     98.65

---

Another method that gives the Hit Ratio for the last couple of minutes (10g+)

column beginTime                    heading 'From'
column endTime                      heading 'To'
column hitRatio     format 999.999  heading 'Hit Ratio'

alter session set nls_date_format = 'MM/DD/YYYY HH24:MI:SS';

select  value       hitRatio,
        begin_time  beginTime,
        end_time    endTime
from    v$sysmetric
where   metric_name = 'Buffer Cache Hit Ratio';

Example:

Hit Ratio From                To
--------- ------------------- -------------------
   96.389 09/27/2009 13:35:54 09/27/2009 13:36:54
   99.421 09/27/2009 13:36:54 09/27/2009 13:37:09

List user sessions

Skips background processes with null user name

set pagesize 40
set linesize 100
column program  format a40
column username format a20

select  username,
        logon_time,
        sid,
        serial#,
        program
        from v$session
        where username is not null
        order by logon_time
        ;

Show disk space utilization for ASM

column name     heading 'Disk|Group Name'
column type     heading 'Type'
column totalGB  heading 'Total(GB)'
column freeGB   heading 'Free(GB)'
column usableGB heading 'Usable(GB)'

select  name, 
        type, 
        total_mb / 1024         totalGB, 
        free_mb / 1024          freeGB, 
        usable_file_mb / 1024   usableGB
from    v$asm_diskgroup;

Example:

Disk
Group Name     Type   Total(GB)     Free(GB)     Usable(GB)
-------------- ------ ------------- ------------ -------------------
PRDARCG        EXTERN    649.905273        299.5               299.5
PRDDATG        EXTERN    3099.55273   384.307617          384.307617
PRDREDG        EXTERN    29.9238281   24.8271484          24.8271484

Retrofit SYSOPER privilege to an Oracle installation where it was not installed originally

1. Create a UNIX group for the system operators. By default on UNIX it is the 'oper' group.

2. Remake the oracle executable so the group is included.
- cd $ORACLE_HOME/rdbms/lib
- vi config.s
- (Solaris) change the second occurrence of .ascii "dba\0" in the file to be .ascii "new_grp\0"
- mv config.o config.o.orig
- make -f ins_rdbms.mk ioracle

3. Run the following script to allow the oper group to have access to the Oracle directories and executables:
$ORACLE_HOME/install/changePerm.sh

Migrate to new rman catalog

[rmancat@milrmnvip01] /oracle>: sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 4 11:17:37 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop user rman cascade;

User dropped.

SQL> drop user rmancat cascade;

User dropped.

SQL> drop user rcvcat cascade;

User dropped.

SQL> create user RMANCAT IDENTIFIED BY rmancatpassword DEFAULT TABLESPACE rman QUOTA UNLIMITED ON rman;

User created.

SQL> grant connect,resource to rmancat;

Grant succeeded.

SQL> grant recovery_catalog_owner to rmancat;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64 bit Production

[rmancat@milrmnvip01] /oracle>: impdp system directory=DATAPUMPDIR DUMPFILE=rmanschema.exp LOGFILE=rmanschema.imp.log TABLE_EXISTS_ACTION=replace SCHEMAS=rmanca t

Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 04 June, 2009 11:26:47

Copyright (c) 2003, 2007, Oracle. All rights reserved.
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=DATAPUMPDIR DUMPFILE=rmanschema.exp LOGFILE=rmanschema.imp.log TABLE_EXISTS_ACTION=replace SCHEMAS=rmancat
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"RMANCAT" already exists
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "RMANCAT"."ROUT" 14.24 MB 150132 rows
. . imported "RMANCAT"."BRL" 5.440 MB 66707 rows
. . imported "RMANCAT"."RLH" 4.393 MB 78403 rows
. . imported "RMANCAT"."BDF" 3.815 MB 36827 rows
. . imported "RMANCAT"."BP" 2.359 MB 11405 rows
. . imported "RMANCAT"."BS" 857.8 KB 11407 rows
. . imported "RMANCAT"."RSR" 380.5 KB 2631 rows
. . imported "RMANCAT"."AL" 80.5 KB 349 rows
. . imported "RMANCAT"."CCF" 48.06 KB 267 rows
. . imported "RMANCAT"."DFATT" 53.77 KB 571 rows
. . imported "RMANCAT"."BCF" 27.37 KB 263 rows
. . imported "RMANCAT"."CKP" 18.28 KB 160 rows
. . imported "RMANCAT"."CONF" 6.593 KB 3 rows
. . imported "RMANCAT"."CONFIG" 5.234 KB 1 rows
. . imported "RMANCAT"."DB" 6.476 KB 1 rows
. . imported "RMANCAT"."DBINC" 14.62 KB 3 rows
. . imported "RMANCAT"."DF" 22.06 KB 265 rows
. . imported "RMANCAT"."NODE" 6.828 KB 1 rows
. . imported "RMANCAT"."OFFR" 15.34 KB 120 rows
. . imported "RMANCAT"."ORL" 6.835 KB 16 rows
. . imported "RMANCAT"."RCVER" 4.937 KB 2 rows
. . imported "RMANCAT"."RT" 7.187 KB 4 rows
. . imported "RMANCAT"."TF" 9.429 KB 39 rows
. . imported "RMANCAT"."TFATT" 10.77 KB 39 rows
. . imported "RMANCAT"."TS" 10.67 KB 58 rows
. . imported "RMANCAT"."TSATT" 8.664 KB 87 rows
. . imported "RMANCAT"."BCB" 0 KB 0 rows
. . imported "RMANCAT"."BSF" 0 KB 0 rows
. . imported "RMANCAT"."CCB" 0 KB 0 rows
. . imported "RMANCAT"."CDF" 0 KB 0 rows
. . imported "RMANCAT"."FB" 0 KB 0 rows
. . imported "RMANCAT"."GRSP" 0 KB 0 rows
. . imported "RMANCAT"."RR" 0 KB 0 rows
. . imported "RMANCAT"."SCR" 0 KB 0 rows
. . imported "RMANCAT"."SCRL" 0 KB 0 rows
. . imported "RMANCAT"."XAL" 0 KB 0 rows
. . imported "RMANCAT"."XCF" 0 KB 0 rows
. . imported "RMANCAT"."XDF" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 11:31:17

[rmancat@milrmnvip01] /oracle>: sqlplus rmancat/rmancatpassword

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 4 11:32:31 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from rc_database;

DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE#
---------- ---------- ---------- -------- -----------------
RESETLOGS_TIME
--------------------------
72579 72580 1722235645 BIPRDDB 500966
27-JUN-2006 20:28:50

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


[rmancat@milrmnvip01] /oracle>: rman catalog rmancat/rmancatpassword

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Jun 4 11:33:22 2009

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

connected to recovery catalog database

RMAN> upgrade catalog;

recovery catalog owner is RMANCAT
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;


recovery catalog upgraded to version 10.02.00.04
DBMS_RCVMAN package upgraded to version 10.02.00.04
DBMS_RCVCAT package upgraded to version 10.02.00.04

RMAN>

Set DBID so views work in sqlplus

CALL DBMS_RCVMAN.SETDATABASE(null,
                             null,
                             null,
                             2283997583,
                             null);

The fourth parameter must be the DBID of a database registered in the recovery catalog. The other parameters must all be NULL.

Calculate the progress of an RMAN job

To calculate the progress of an RMAN job, run the following query in SQL*Plus while the RMAN job is executing:

SELECT  SID, 
        SERIAL#, 
        CONTEXT, 
        SOFAR, 
        TOTALWORK,
        ROUND(SOFAR/TOTALWORK*100,2) "% COMPLETE"
FROM    V$SESSION_LONGOPS 
        WHERE OPNAME LIKE 'RMAN%' 
          AND OPNAME NOT LIKE '%aggregate%' 
          AND TOTALWORK != 0 
          AND SOFAR <> TOTALWORK;

Terminate an RMAN Session

Terminating an RMAN Command

There are several ways to terminate an RMAN command in the middle of execution:

*

The preferred method is to press CTRL+C (or the equivalent "attention" key combination for your system) in the RMAN interface. This will also terminates allocated channels, unless they are hung in the media management code, as happens when, for example, when they are waiting for a tape to be mounted.
*

You can kill the server session corresponding to the RMAN channel by running the SQL ALTER SYSTEM KILL SESSION statement.
*

You can terminate the server session corresponding to the RMAN channel on the operating system.

Terminating the Session with ALTER SYSTEM KILL SESSION

You can identify the Oracle session ID for an RMAN channel by looking in the RMAN log for messages with the format shown in the following example:

channel ch1: sid=15 devtype=SBT_TAPE


The sid and devtype are displayed for each allocated channel. Note that the Oracle sid is different from the operating system process ID. You can kill the session using a SQL ALTER SYSTEM KILL SESSION statement.

ALTER SYSTEM KILL SESSION takes two arguments, the sid printed in the RMAN message and a serial number, both of which can be obtained by querying V$SESSION. For example, run the following statement, where sid_in_rman_output is the number from the RMAN message:

SELECT SERIAL# FROM V$SESSION WHERE SID=sid_in_rman_output;


Then, run the following statement, substituting the sid_in_rman_output and serial number obtained from the query:

ALTER SYSTEM KILL SESSION 'sid_in_rman_output,serial#';


Note that this will not unhang the session if the session is hung in media manager code..
Terminating the Session at the Operating System Level

Finding and killing the processes that are associated with the server sessions is operating system specific. On some platforms the server sessions are not associated with any processes at all. Refer to your operating system specific documentation for more information.
Terminating an RMAN Session That Is Hung in the Media Manager

You may sometimes need to kill an RMAN job that is hung in the media manager. The best way to terminate RMAN when the channel connections are hung in the media manager is to kill the session in the media manager. If this action does not solve the problem, then on some platforms, such as Unix, you may be able to kill the Oracle processes of the connections. (Note that killing the Oracle processes may cause problems from the media manager. See your media manager documentation for details.)

Determine what database components are installed

set pagesize    40

column componentName    format a40  heading 'Component Name'
column version          format a15  heading 'Version'
column status           format a8   heading 'Status'

select  COMP_NAME   componentName, 
        VERSION     version, 
        STATUS      status
from    dba_registry;

Example:

Component Name                           Version         Status
---------------------------------------- --------------- --------
OWB                                      11.1.0.6.0      VALID
Oracle Application Express               3.0.1.00.08     VALID
Oracle Enterprise Manager                11.1.0.6.0      VALID
Oracle Ultra Search                      11.1.0.6.0      VALID
OLAP Catalog                             11.1.0.6.0      VALID
Spatial                                  11.1.0.6.0      VALID
Oracle Multimedia                        11.1.0.6.0      VALID
Oracle XML Database                      11.1.0.6.0      VALID
Oracle Text                              11.1.0.6.0      VALID
Oracle Expression Filter                 11.1.0.6.0      VALID
Oracle Rules Manager                     11.1.0.6.0      VALID
Oracle Workspace Manager                 11.1.0.6.0      VALID
Oracle Database Catalog Views            11.1.0.6.0      VALID
Oracle Database Packages and Types       11.1.0.6.0      VALID
JServer JAVA Virtual Machine             11.1.0.6.0      VALID
Oracle XDK                               11.1.0.6.0      VALID
Oracle Database Java Packages            11.1.0.6.0      VALID
OLAP Analytic Workspace                  11.1.0.6.0      VALID
Oracle OLAP API                          11.1.0.6.0      VALID

Determine if database is in ARCHIVELOG mode

select log_mode from v$database;

-OR-

archive log list

Recompile invalid schema objects

spool /tmp/invalid.objects.sql

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT   owner,
                         object_type,
                         object_name,
                         status
                FROM     dba_objects
                WHERE    status = 'INVALID'
                ORDER BY object_name, 
                         owner, 
                         object_type
               )
    LOOP
        BEGIN 
            DBMS_OUTPUT.put_line('alter ' || cur_rec.object_type || ' ' || cur_rec.object_name || ' compile; ');
        END;
    END LOOP;
END;
/

spool off;

@/tmp/invalid.objects.sql

---

Or:

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT  owner,
                        object_name,
                        object_type,
                        DECODE(object_type, 
                               'PACKAGE', 
                               1,
                               'PACKAGE BODY', 
                               2, 
                               2) 
                        AS recompile_order
                FROM    dba_objects
                WHERE   object_type IN ('PACKAGE', 
                                        'PACKAGE BODY')
                  AND   status != 'VALID'
                  ORDER BY 4
               )
    LOOP
        BEGIN
        IF cur_rec.object_type = 'PACKAGE' THEN
            EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || 
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
        ELSE
            EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner || 
            '"."' || cur_rec.object_name || '" COMPILE BODY';
        END IF;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner || 
                             ' : ' || cur_rec.object_name);
        END;
    END LOOP;
END;
/

Disconnect Oracle user

select SID, SERIAL#, username, osuser, machine from v$session;

username = database username
osuser = client operating system user that is connecting to the database
machine = client machine IP.

Remember the SID and SERIAL# of the user(s) that you with to kill/disconnect. Then issue a kill command.

Alter system kill session ‘SID,SERIAL#’;

Delete Oracle Database

Since 10g, It is possible to drop a database in Oracle 10g with the new SQL statement drop database.
The command gets rid of datafiles online redo log files, controlfiles and spfile.

shutdown abort;
startup mount exclusive restrict; 
drop database;
exit

Requirements

* database must be closed,
* exclusively mounted,
* restricted


-------------------------------------------------------------------------
To delete a database Pre 10g

First:

select  "rm -f "||name from v$controlfile
        union
        select "rm -f "||name from v4datafile
        union
        select "rm -f "||member from v$logfile;

Second:
Clean up above output & make it executable

Third:
If installation follows OFA standard:
remove directory $ORACLE_BASE/admin/$ORACLE_SID

Fourth:
Delete archive log directory (if archive mode)

or)

Depending on if OFA was followed in installation you can use the Oracle Database Configuration Assistant(DBCA). You can create and delete a database with this tool. Delete the database and then remove the files as noted in the previous response.

Enable trace in Oracle

1. Enable trace at instance level

Put the following line in init.ora. It will enable trace for all sessions and the background
processes

sql_trace = TRUE

to disable trace:

sql_trace = FALSE

- or -

to enable tracing without restarting database run the following command in sqlplus

SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;

to stop trace run:

SQLPLUS> ALTER SYSTEM SET trace_enabled = FALSE;

2. Enable trace at session level

to start trace:

ALTER SESSION SET sql_trace = TRUE;

to stop trace:

ALTER SESSION SET sql_trace = FALSE;

- or -

EXECUTE dbms_session.set_sql_trace (TRUE);
EXECUTE dbms_session.set_sql_trace (FALSE);

- or -

EXECUTE dbms_support.start_trace;
EXECUTE dbms_support.stop_trace;

3. Enable trace in another session

Find out SID and SERIAL# from v$session. For example:

SELECT * FROM v$session WHERE osuser = OSUSER;

to start trace:

EXECUTE dbms_support.start_trace_in_session (SID, SERIAL#);

to stop trace:

EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);

- or -

EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, TRUE);
EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, FALSE);

Display the number of rows in each table

Count rows in each table:

set heading off
set feedback off
set pagesize 0
set termout off
set trimout on
set trimspool on
set recsep off
set linesize 100

column d noprint new_value date_
column u noprint new_value user_

spool tmp

select   'select '''||table_name||' : ''||count(*) from '||table_name||';',
         to_char(sysdate, 'YYYYMMDDHH24MISS') d, 
         user u
from     user_tables
order by table_name
/

Spool off
Spool count_&user_._&date_
@tmp.lst
Spool off

Count as of last analyze (faster but less accurate):

COLUMN DUMMY NOPRINT
  COMPUTE SUM OF NUM_ROWS ON DUMMY
  BREAK ON DUMMY
  select
     NULL DUMMY,
     TABLE_NAME,
     NUM_ROWS
  FROM
     ALL_TABLES;

Note that it shows num_rows as blank if you try to add a WHERE clause on owner...

List all ORACLE_SIDs and their home directories on a server (UNIX)

Look in oratab, typically located in /var/opt/oracle or the /etc directories.

$ more /var/opt/oracle/oratab
#
ora9:/oracle/product/10.2.0/db_1:N
EIC:/oracle/product/10.2.0/db_1:N
cmstdev:/oracle/product/10.2.0/db_1:N

Show information for all sessions

set feedback off
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
set feedback on

column username     format a15      heading 'User Name'
column osuser       format a12      heading 'OS User'
column sid          format 99999    heading 'Session|ID'
column serial#                      heading 'Serial #'
column spid         format 99999    heading 'SPID'
column status                       heading 'Status'
column pgaKB                        heading 'PGA(KB)'
column module       format a20      heading 'Module'
column machine      format a12      heading 'Machine'
column program      format a20      heading 'Program'
column logon_Time   format a12      heading 'Log On Time'

set linesize 500
set pagesize 1000

select   a.username,
         a.osuser,
         a.sid,
         a.serial#,
         d.spid,
         a.status,
         trunc(b.value/1024)    pgaKB,
         a.module,
         a.machine,
         a.program,
         a.logon_Time
from     v$session a,
         v$sesstat b,
         v$statname c,
         v$process d,
         v$statname f
where    a.paddr      = d.addr
  and    a.sid        = b.sid
  and    b.statistic# = c.statistic#
  and    c.name       = 'session pga memory'
  and    f.name       = 'session uga memory'
order by 1,2
;

List Tablespace Usage

set pagesize 9999
set linesize 160 

column tablespaceName       heading 'Tablespace'
column kBytesUsed           heading 'Used (KB)'
column kBytesFree           heading 'Free (KB)'
column largest              heading 'Largest'
column percentUsed          heading '% Used'

select      a.TABLESPACE_NAME           tablespaceName,
            a.BYTES  / 1024             kBytesUsed,
            b.BYTES / 1024              kBytesFree,
            b.largest / 1024            largest,
            round(((a.BYTES - b.BYTES)
                    / a.BYTES)
                    *100
                    ,2) percentUsed
from        (
            select     TABLESPACE_NAME,
                       sum(BYTES) BYTES
            from       dba_data_files
            group by   TABLESPACE_NAME
            ) a,
            (
            select      TABLESPACE_NAME,
                        sum(BYTES) BYTES ,
                        max(BYTES) largest
            from        dba_free_space
            group by    TABLESPACE_NAME
            ) b
where       a.TABLESPACE_NAME = b.TABLESPACE_NAME
order by    ((a.BYTES-b.BYTES)/a.BYTES) desc
;

Example:

Tablespace                      Used (KB)  Free (KB)    Largest     % Used
------------------------------ ---------- ---------- ---------- ----------
SYSTEM                             716800       7808       7104      98.91
SYSAUX                             601024      29120      29120      95.15
EXAMPLE                            102400      22848      20416      77.69
USERS                                5120       1920       1920       62.5
UNDOTBS1                            81920      60416      45888      26.25

5 rows selected.

Backup and disable crontab

#!/bin/ksh
export CRONTABFILE=crontab.`date +%m%d%y-%H%M%S`

echo Exporting crontab to $CRONTABFILE

crontab -l > $CRONTABFILE

crontab -r

Set prompt to show current directory (ksh)

PS1="[$LOGNAME@`'hostname'`]"' $PWD>: '
export PS1

Find location of data files

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/emprddb/disk1/system01.dbf
/oradata/emprddb/disk1/undotbs01.dbf
/oradata/emprddb/disk1/sysaux01.dbf
/oradata/emprddb/disk1/users01.dbf
/oradata/emprddb/disk1/mgmt.dbf
/oradata/emprddb/disk1/mgmt_ecm_depot1.dbf
/oradata/emprddb/disk1/rman_01.dbf

Locate the Oracle Inventory Directory

Solaris

$ more /var/opt/oracle/oraInst.loc
inventory_loc=/oracle/oraInventory
inst_group=dba

On Linux the file will be under /etc

Move data files to new location

1-Shutdown the DB
2-copy all .DBF .LOG to the new location. Do not copy the .CTL files at this step.
3-startup mount
3-alter data~base rename file 'old location' to 'new location' ; ... for all .DBF and .LOG files
Oracle finds them in the new location
4-alter system set control_file=[list of CTL files in new location] scope=spfile
5-alter system set core_dump_dest=[new empty location for cdump] scope=spfile
6-alter system set user_dump_dest=[new empty location for udump] scope=spfile
7-alter system set background_dump_dest=[new empty location for bdump] scope=spfile
(CTL are still in old location but contain information about the new location)
8-shutdown
9-copy all .CTL files to the new location
10-startup

Set display for X emulation for installation

DISPLAY=[machine-name]:0.0; export DISPLAY

This may vary by server. A good way to determine the correct value is to connect to the server via Xstart and then to check the value of the DISPLAY variable.

(Telnet connection via Xstart)

Sun Microsystems Inc.   SunOS 5.10      Generic January 2005
$ echo $DISPLAY
10.177.10.137:1.0

Write Custom Messages into the alert log

The undocumented procedure kdswrt in dbms_system writes custom messages to the alert log.

Syntax:

sys.dbms_system.ksdwrt(destination, message);

Valid Destination values are

1 Write to a trace file

2 Write to the alert log

3 Write to both trace and alert log

Example:

begin
sys.dbms_system.ksdwrt(2, 'Custom Message');
end;
/

Troubleshoot client connection problems

1. Check whether you can ping the server from the client machine.
2. If pinging then check whether service is started on server.
3. If service started then check the listener.
4. If listening then check your tnsnames.ora file.

Determine background dump dest (bdump.) Location of alert Log

select value from v$parameter where name = 'background_dump_dest';

--OR--

show parameter dump;

Show columns in all indexes given the table name

column indexName    format a30  heading 'Index Name'
column columnName   format a30  heading 'Column Name'
column indexType    format a12  heading 'Index|Type'

set linesize 80
set verify off

accept tableName prompt "Enter Table Name: "

select  DBA_IND_COLUMNS.index_name  indexName,
        DBA_IND_COLUMNS.column_name columnName,
        DBA_INDEXES.index_type      indexType
from    DBA_IND_COLUMNS,
        DBA_INDEXES
where   DBA_INDEXES.table_name = '&tableName'
  and   DBA_INDEXES.table_name = DBA_IND_COLUMNS.table_name;

Example:

                                                              Index
Index Name                     Column Name                    Type
------------------------------ ------------------------------ ------------
PRODUCTS_PROD_STATUS_BIX       PROD_STATUS                    NORMAL
PRODUCTS_PK                    PROD_ID                        NORMAL
PRODUCTS_PROD_SUBCAT_IX        PROD_SUBCATEGORY               NORMAL
PRODUCTS_PROD_CAT_IX           PROD_CATEGORY                  NORMAL
PRODUCTS_PROD_STATUS_BIX       PROD_STATUS                    NORMAL
PRODUCTS_PK                    PROD_ID                        NORMAL
PRODUCTS_PROD_SUBCAT_IX        PROD_SUBCATEGORY               NORMAL
PRODUCTS_PROD_CAT_IX           PROD_CATEGORY                  NORMAL
PRODUCTS_PROD_STATUS_BIX       PROD_STATUS                    NORMAL
PRODUCTS_PK                    PROD_ID                        NORMAL
PRODUCTS_PROD_SUBCAT_IX        PROD_SUBCATEGORY               NORMAL
PRODUCTS_PROD_CAT_IX           PROD_CATEGORY                  NORMAL
PRODUCTS_PROD_STATUS_BIX       PROD_STATUS                    BITMAP
PRODUCTS_PK                    PROD_ID                        BITMAP
PRODUCTS_PROD_SUBCAT_IX        PROD_SUBCATEGORY               BITMAP
PRODUCTS_PROD_CAT_IX           PROD_CATEGORY                  BITMAP

Kill Oracle session using Session ID

SQL> select sid,serial#,username,status,sql_hash_value from v$session where sid=153;

SID    SERIAL# USERNAME   STATUS   SQL_HASH_VALUE
---- --------- ---------- -------- --------------
153       1885 THEUSER    ACTIVE   3021386057

SQL> alter system kill session '153,1885';

System altered.