Killing a session in Oracle

Sessions in oracle can be killed with the command:

SQL> alter system kill session 'sid,serial#';

The value for sid and serial# can be query'd using the following query:

select * from v$session
or
select sid, serial#, osuser, program
from v$session;

After this command, the indicated session is marked for kill. When it's possible the session will be killed. Sometimes this can take a while (for example when a lot of rollback is needed).
To kill a session faster you can use the keyword IMMEDIATE like this:

SQL> alter system kill session 'sid,serial#' immediate;

DBWR_IO_SLAVES vs DB_WRITER_PROCESSES

DBWR_IO_SLAVES vs DB_WRITER_PROCESSES
Questions about multiple DBWR processes have plagued DBAs since Oracle7. You configured multiple DBWR process in Oracle7 by setting the parameter DB_WRITERS. In Oracle7, multiple DBWR processes were actually slave processes that were unable to perform asynchronous I/O calls on their own. The algorithm used by the Oracle7 DBWR caused it to incur waits when the delay of a single write caused additional writes to queue up until the initial write was complete.
Oracle8 and later release’s DBWR architecture corrects this problem. Oracle’s DBWR now writes continuously without waiting for previous writes to complete. The new design allows DBWR to act as if it were inherently synchronous, regardless of whether the operating system supports asynchronous I/O or not. Administrators are able to configure multiple DBWR process by setting the init.ora parameter db_writer_processes. Multiple database writers became available in Oracle 8.0.4 and allow true multiple database writes. There is no master-slave relationship as in Version 7.

If you implement database writer I/O slaves by setting the dbwr_io_slaves parameter, you configure a single (master) DBWR process that has slave processes that are subservient to perform asynchronous I/O calls. I/O slaves can also be used to simulate asynchronous I/O on platforms that do not support asynchronous I/O or implement it inefficiently.
You can't activate both multiple DBWRs and I/O slaves. If both parameters are set in the parameter file, dbwr_io_slaves will take precedence.
To determine whether to use multiple DBWn processes or database slaves, follow these guidelines:
Use db_writer_processes for most write intensive applications. One per CPU is the recommended setting.
Use db_writer_processes for databases that have a large data buffer cache.
Use dbwr_io_slaves for applications that are not write intensive and run on operating systems that support asynchronous I/O.
Use dbwr_io_slaves on platforms that do no support asynchronous I/O.
Use dbwr_io_slaves on single CPU systems. Multiple DBWR processes are CPU intensive.

Bitmap Join Indexes

Bitmap Join Indexes

Oracle8i supported bitmap indexes on a single table. 9i adds bitmap join indexes, built on two tables, to optimize access for a specific join condition:

CREATE BITMAP INDEX my_bit_join_ix
ON emp_table (e.job)
FROM emp_table e, dept_table d
WHERE e.deptno = d.deptno ;

Restrictions:

• Can not reference an IOT, temporary table, or a self-join
• Only one table can be updated concurrently by different transactions
• Parallel DML is only supported on the fact table, and the columns in the index must all be columns of the dimension tables
• Join must be on primary key or unique-constraint key, and, if the key is composite, the entire key must be used

SPFILE

Online Configuration Parameter Updating

Oracle keeps initialization parameters in its parameter file (PFILE). Now Oracle also has a binary file equivalent to the PFILE called the server parameter file (SPFILE). Unlike the PFILE, you can not edit the SPFILE, because its contents are binary.

To create an SPFILE: CREATE SPFILE FROM PFILE ;

To view settings within the SPFILE, query the dictionary view V$SPPARAMETER.

Change parameter file settings with the ALTER SYSTEM statement. Set the SCOPE for this statement as follows:

• – Change only applies to the current instance
• – Change only applies to the SPFILE (not to the current instance)
• BOTH – Applies the change immediately to the current instance and to the SPFILE as well

The default is MEMORY if the PFILE was used to start the database. The default is BOTH if the SPFILE was used to start the database.

To export the SPFILE to a PFILE, run:

CREATE PFILE=’editsp.ora’ FROM SPFILE;

You can edit the exported file with a text editor, then recreate the SPFILE from it. Remember that since the SPFILE is in binary format you can not directly edit it.

Flashback

Oracle Flashback

Oracle Flashback allows you to query the database as of a specific time or System Change Number (SCN) in the past. It can be useful in recovering from a recent logical or user error – just use Flashback to query the data prior to an accidental data deletion, for example.

To use Flashback you must have already:

• Set the initialization parameter UNDO_MANAGEMENT = AUTO

(This enables a new 9i feature called Automatic Undo Management, for Oracle-controlled management of rollback segments.)

• Set the initialization parameter UNDO_RETENTION to how far back (in seconds) Oracle should retain undo information:

Example: ALTER SYSTEM SET UNDO_RETENTION = 1600 ;

• Have the execute privilege on system package DBMS_FLASHBACK

Here’s how to use Flashback:

• Run either DBMS_FLASHBACK.ENABLE_AT_TIME or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER to enable Flashback for the session and indicate the time or SCN to return to
• Perform the query and access missing information by a PL/SQL cursor. You can not use a regular DML query for this purpose!
• Disable Flashback when done by running DBMS_FLASHBACK.DISABLE

A primary limitation in using Flashback is how much extra Undo information you want to store, and how much space you are willing to allocate to this purpose.

Backup and Recovery (RMAN)

Backup and Recovery Enhancements

Recovery Manager (RMAN) Enhancements

Block Media Recovery (BMR)

By default, RMAN’s backup/recovery method operates on the datafile level. 9i now allows you to recover individual data blocks, called block media recovery (BMR). BMR is quicker than full file recovery where you have a small number of corrupt blocks that need to be recovered. Plus you do not have to take either the datafile or the database offline to perform BMR. BMR minimizes Mean Time to Recover (MTTR).

Determine what blocks are bad from views V$BACKUP_CORRUPTION and V$COPY_CORRUPTION. Oracle writes information about block corruption in both the Alert Log and the user trace files.

Here’s the command to recover one or more data blocks:

BLOCKRECOVER DATAFILE filename_1 BLOCK b1 [, b2, b3 ...]

[ DATAFILE filename_2 BLOCK b4, b5 ... ]

Restrictions:

• You can only do BMR from RMAN (not from SQL*Plus)
• You can only do BMR off full backups (not incremental backups)
• You can only recover complete blocks

The CONFIGURE Command

The new 9i RMAN CONFIGURE command lets you permanently configure many aspects of RMAN. Configure the window for the retention policy:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Configure the redundancy policy:

CONFIGURE RENTENTION POLICY TO REDUNDANCY 5;

Configure the default backup device type:

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

Clear the previous command:

CONFIGURE DEFAULT DEVICE TYPE CLEAR;

You can also use the CONFIGURE command to assign attributes to channels. As well, you can configure things so that control files are automatically backed up without having to specify the INCLUDING CONTROL FILE phrase on the BACKUP command.

You can configure to exclude certain tablespaces from backups:

CONFIGURE EXCLUDE FOR TABLESPACE my_ts ;

Append keyword CLEAR to that command to remove the exclusion.

Turn on backup optimization with:

CONFIGURE BACKUP OPTIMIZATION ON;

This skips files that already have a backup on the backup device with the same header status (they don’t need to be backed up, because the same header means the original files have not been changed).

Other RMAN Enhancements

Archivelog Backup – Include any archivelogs not yet backed up with a datafile backup by this command: BACKUP DATAFILE n PLUS ARCHIVELOG ;

Restartable Backup - Back up files that have not been backed up since a particular date and time with the new clause NOT BACKED UP [SINCE TIME...]. Example:

BACKUP DATABASE NOT BACKED UP SINCE TIME ‘sysdate – 5 ;

Smarter Restoration – Oracle only restores files whose headers do not match those of the target. This saves needlessly restoring certain files that are unchanged.

Other Improvements -

• 9i backs up tablespaces of different blocksizes in one command
• Report obsolete backups not needed in the recovery period specified: REPORT OBSOLETE RECOVERY WINDOW 7 DAYS;
• The RECOVERY WINDOW clause was added here too:

REPORT NEED BACKUP RECOVERY WINDOW 7 DAYS:

• Use the CROSSCHECK command to check if backup sets or file copies exist
• Display RMAN session configuration by the SHOW or SHOW ALL commands
• The LIST command has new parameters BY BACKUP and BY FILE
• You can now execute the BACKUP and RESTORE commands directly from the RMAN prompt, instead of only from within a RUN command

Backup and Recovery

Backup and Recovery Enhancements

Trial Recovery

The new 9i Trial Recovery feature does not write changes to files, just to the data buffers. Errors are written to the Alert Log. Trial Recovery allows you to test out recovery and find any corrupt blocks without failing at a real recovery and leaving files in an inconsistent state. Use the keyword TEST for a trial recovery:

RECOVER DATABASE . . . TEST ;

Also new in 9i is the ability to continue even past n corrupt blocks:

RECOVER DATABASE ALLOW n CORRUPTION;

LogMiner

LogMiner Enhancements

9i improves LogMiner in a variety of ways. The purpose of LogMiner is to allow you to view entries in the redo log (the log that records database changes).

LogMiner now has a GUI called the LogMiner Viewer. Use this new GUI in viewing the contents of the redo log files via the view V$LOGMNR_CONTENTS. LogMiner Viewer has Display Options that allow you to specify what information you want to view from the logs. Two of the key fields are SQL_REDO and SQL_UNDO. The former contains the redo log statement, while the latter has its “inverse” – a statement that would reverse its effects (or “undo” it).

9i now records DDL statements in the redo logs. (You no longer have to reverse-engineer them from the complex DML Oracle runs against its internal tables, as you had to when using LogMiner under 8i). You can track (but not) undo DDL commands. That is, you can still not recover from a table DROP or TRUNCATE using this feature.

Remember that LogMiner requires access to the data dictionary in order to translate object identifiers into their names and data according to proper data types. Without dictionary access, LogMiner returns object identifiers and hex data representations – not too useful. You can now extract the data dictionary to either a flat file or to the redo log files by using the procedure DBMS_LOGMNR_D.BUILD. Specify the OPTIONS parameter in this procedure as either STORE_IN_FLAT_FILE or STORE_IN_REDO_LOGS.

There is also a third OPTION – DICT_FROM_ONLINE_CATALOG. This uses the online data dictionary, which could possibly be inaccurate if the database changed since the logs were generated.

If you extract the dictionary to either a flat file or the redo logs, you can specify the additional option DDL_DICT_TRACKING. This prompts LogMiner to keep its extracted dictionary in sync with any changes to the real data dictionary by applying DDL it encounters in the redo logs. LogMiner detects that its dictionary export is stale (obsolete) through its use of object version numbers.

LogMiner stops whenever it encounters a corruption in the redo log it is viewing. Use the OPTION called SKIP CORRUPTIONS to force it to continue beyond corruptions.

Oracle DataGuard

Data Guard

Oracle’s Standby Database feature has been upgraded and renamed to Data Guard in 9i. Data Guard allows you to set up a standby database (locally or at a remote site) and keep it either completely or approximately “in sync” with the primary database by automatically shipping and applying redo logs to the standby database. This provides for quick disaster and/or off-site recovery should the primary database be lost or destroyed. Data Guard components are:

• Data Guard Manager – The new administrative Data Guard GUI (part of OEM)
• Data Guard Command Line Interface (CLI) – For issuing commands
• Data Guard Monitor (DMON) – A monitor process that supports the Data Guard administration

Update the standby database by one of four Data Guard modes:

• Guaranteed – Keeps primary and standby totally in sync at all times. Primary transactions are not committed until verification that the change has been applied to the standby
• Instant – Ensures changes are shipped to the standby but does not require they immediately be applied and confirmed
• Rapid – Log Writer (LGWR) on the primary sends changes to the standby as soon as it can
• Delayed – the Archiver Process (ARCH) process on the primary send changes to the standby. You can specify a time lag prior to propagation (this can help avoid propagating errors)

Guaranteed mode is the most conservative and keeps the standby totally synchronized with the primary, but impacts primary performance the most. The other modes are progressively “looser” in the coupling between the primary and standby but cause less performance impact on the primary. You configure the mode (and other Data Guard attributes) by the initialization parameters LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n.

Role Management Service (RMS) helps you to perform database transitions (switching the roles of the primary and standby databases). A switchover is a planned transition whereby you purposefully make the primary the standby, and vice versa. Among the commands you use during a switchover are these key ones:

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

ALTER DATABASE MOUNT STANDBY DATABASE;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL PRIMARY;

A switchback is the reverse of the switchover operation. It puts the two databases back to their original roles as primary and standby.

A graceful failover is performed when the primary becomes unavailable but you have its redo logs to apply to the standby. Apply any and all possible logs to the standby:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

then issue:

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL PRIMARY;

A forced failover occurs when the primary fails and redo logs are not all available. Some redo may be lost as you activate the standby. Key commands here include:

ALTER DATABASE SET STANDBY DATABASE UNPROTECTED;

ALTER DATABASE ACTIVATE STANDBY DATABASE;

An archive log gap occurs whenever a primary database archives the online redo log, but it is not also archived to the standby database. 9i automatically recovers archive log gaps for you. Set this up by initialization parameters that assign service names to the primary and standby databases:

FAL_CLIENT = ‘standby_name’

FAL_SERVER = ‘primary_name’

9i includes a Managed Recovery Process (MRP), a process that automatically applies archived redo logs on the standby database server to the standby database. Put a standby database into MRP mode by running:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

Resumable Space Allocation

Resumable Space Allocation

Resumable space allocation suspends a long-running operation in event of a space allocation error so that you can fix the problem. Then Oracle automatically resumes the long-running operation. Operations that are resumable are:

• Queries that run out of temporary sort space
• DML – INSERT, UPDATE, and DELETE statements
• DDL – CREATE TABLE AS SELECT, ALTER TABLE, CREATE INDEX, ALTER INDEX, and statements that create materialized views or materialized view logs
• Import/Export – SQL*Loader operations

The space problems resumable space allocation addresses are:

• Out of space
• Maximum extents reached
• Space quota exceeded

To use resumable space allocation, set your session as resumable, and ensure you have the RESUMABLE system privilege:

ALTER SESSION ENABLE RESUMABLE ;

GRANT RESUMABLE TO my_id ;

If an operation is suspended, Oracle writes an error to its Alert log. Views USER_RESUMABLE and DBA_RESUMABLE contain info on the error, or you can run DBMS_RESUMABLE.SPACE_ERROR_INFO for error details. Fix the error, then Oracle will automatically resume the suspended statement.

Oracle also provides the AFTER SUSPEND system event to handle resumable space errors. You could use this, for example, in a trigger with custom code:

CREATE OR REPLACE TRIGGER my_space_handler

AFTER SUSPEND ON DATABASE...

Temp Tablespace

Default Temporary Tablespaces

If you CREATE USER and forget to include a TEMPORARY TABLESPACE clause, Oracle uses the SYSTEM tablespace for that user’s sorts. This hurts performance. 9i addresses this by allowing you to specify a system-wide default temporary tablespace. Specify the DEFAULT TEMPORARY TABLESPACE on the CREATE DATABASE statement. Or, define the new temporary tablespace by the CREATE TEMPORARY TABLESPACE statement, and make it the default by running:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE default_temp_ts ;

Automated UNDO Management

Automated Undo Management (AUM)

9i’s new feature Automated Undo Management (AUM) relieves you of the traditional, labor-intensive task of sizing and managing rollbacks. To use AUM, create a tablespace that will be used for rollbacks (the UNDO tablespace). Then start the instance with these two new 9i initialization parameters set to:

UNDO_MANAGEMENT = AUTO

UNDO_TABLESPACE = undo_tablespace_name

Once an instance is started with AUM, you can not and do not create or manage rollback segments manually. You can switch to another UNDO tablespace whenever you want, but you can not drop an UNDO tablespace while it has active transactions.

Multiple Block Sizes

Multiple Block Sizes

9i supports multiple blocksizes within one database. The original block size specified is called the default blocksize or the standard blocksize. An Oracle database can have up to four additional blocksizes. Oracle’s allowable blocksizes are 2, 4, 8, 16, and 32 Kilobytes.

The SYSTEM and temporary tablespaces must be of the standard blocksize, and all partitions of a partitioned table must be of the same blocksize.

For each nonstandard blocksize you intend to use, you must set one initialization parameter to provide its cache. Here’s an example for an 8K blocksize:

DB_8K_CACHE_SIZE = 8M

Now you can create tablespace(s) with the new blocksize simply by appending the keywords BLOCKSIZE 8K to the CREATE TABLESPACE statement. The dictionary view DBA_TABLESPACES has the new column BLOCK_SIZE to track this parameter.

My Database Has an Archiver Error Whatever Shall I Do?

When the database is complaining that the disk is full due to an archiver error, and there is no-one to get to do the work for you then you can follow the following steps to clear the problem down.

1. df -h
This shows you the mount points and their sizes and usage.

2. rman target /
This connects you to the rman (Recovery MANager) repository - in this case the database controlfile.

3. For a quick win try....
- crosscheck backup;
- crosscheck archivelog all;
- delete noprompt expired;
- delete noprompt obsolete recovery window of 1 days;

4. list archivelog all;
This shows you a list of all the remaining archivelogs on the system. If this list continues beyond a couple of screenfuls or the dates are for more that two days ago then you have candidates for deletion.

5. delete noprompt archivelog until time 'sysdate-2';
Removes archivelogs older than two days.

6. delete noprompt expired;
Removes any exired objects;

7. exit;
Leaves rman.

In enterprise manager the same commands are available in point and click in the maintenance tab under the maintain current backups entry.

Hot or Cold Backup?

Explain the difference between a hot backup and a cold backup and the benefits associated with each.

A hot backup means taking a backup of the database while it is still up and running where the database is in archive log mode. A cold backup means taking a backup of the database while it is shut down and does not require that it is in archive log mode.

The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time.

The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

To View Sort Area Information

Using the following query we can get some information about the sorting happening on a particular database.

SELECT *
  FROM v$sysstat
 WHERE NAME LIKE '%sorts%'

STATISTIC# NAME         CLASS VALUE         STAT_ID
341         sorts (memory) 64 27568047 2091983730
342         sorts (disk) 64 158         2533123502
343         sorts (rows) 64 9867427817 3757672740

sorts (memory) - If the number of disk writes is non-zero for a given sort operation, then this statistic is incremented. Sorts that require I/O to disk are quite resource intensive. Try increasing the initialization parameter SORT_AREA_SIZE.

sorts (disk) - If the number of disk writes is zero, then the sort was performed completely in memory and this statistic is incremented. This is more an indication of sorting activity in the application workload. You can't do much better than memory sorts, except maybe no sorts at all. Sorting is usually caused by selection criteria specifications within table join SQL operations.

The sorting algorithms and resources used have improved with every release of oracle and I see future releases improving further in this regard.

To View SGA Information

The (SGA) System Global Area is shared memory structures that are created at instance startup. They hold information about

the instance, and control its behavior. The following query gives a window into the various memory pools available in the

SGA.

SELECT NAME, VALUE
  FROM v$parameter
 WHERE NAME IN
          ('shared_pool_size', 'java_pool_size', 'streams_pool_size',
           'log_buffer', 'db_cache_size', 'db_2k_cache_size',
           'db_4k_cache_size', 'db_8k_cache_size', 'db_16k_cache_size',
           'db_32k_cache_size', 'db_keep_cache_size', 'db_recycle_cache_size',
           'large_pool_size');

and the sizes of the various pools in use.

SELECT NAME, pool, ROUND (BYTES / 1024 / 1024, 2) free_mb
  FROM v$sgastat
 WHERE NAME IN
          ('%free memory%', 'parameters', 'memory in use', 'db_block_buffers',
           'log_buffer', 'dictionary_cache,', 'sql area', 'library cache');

Finding the Global Database Name

The full name of the database which uniquely identifies it from any other database. The global database name is of the

form "database_name.database_domain," for example, sales.us.acme.com.

The database name portion, sales, is a simple name you wish to call your database. The database domain portion,

us.acme.com, specifies the database domain in which the database is located, making the global database name unique. When

possible, Oracle Corporation recommends that your database domain mirror the network domain.

The global database name is the default service name of the database, as specified by the SERVICE_NAMES parameter in the

initialization parameter file.

SELECT NAME, value$
  FROM SYS.props$
 WHERE NAME = 'GLOBAL_DB_NAME';

or

SELECT *
  FROM GLOBAL_NAME;

How do I find my SID?

The SID is the Oracle System ID. It is used to uniquely identify a database. In RAC, all instances belonging to the same database must have unique SID's.



In Windows:


set ORACLE_SID=orcl



Unix/ Linux:


export ORACLE_SID=orcl

SELECT enabled, open_time, status, INSTANCE
  FROM v$thread;

Oracle Version Number?

You may need to find what version of database you are running. Not often I admit, but it can happen.

SELECT banner FROM V$VERSION;

Not difficult at all.