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.

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.

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$

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