Archive

Archive for the ‘Oracle’ Category

Oracle – Find consuming sql from process id

September 5, 2011 5 comments

If you find an oracle process is consuming a high amount of CPU, you can find the sql_text that is related to that process id using the following code:

SELECT SQL_TEXT
FROM v$sql
WHERE sql_id = (SELECT sql_id FROM v$session WHERE paddr = (sELECT addr
FROM v$process
WHERE spid = '&process_id'));

You will need to find the process id using either top (linux), topas -P (aix), or task manager (windows). Equally you can use Enterprise Manager to find this information, by looking under the performance monitor and any alerts regarding tuning / high CPU consumption.

Advertisements

Oracle – Disable dbms gather_stats_job or optimizer stats


To disable gather_stats_job in 10g:

BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/

To disable gather_stats_job (also known as optimizer stats) in 11g:

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/

Oracle – RMAN Restore Database


If you have a current controlfile, spfile, but all datafiles have been lost or damaged, then you must restore and recover the entire database.

RMAN> STARTUP MOUNT

You can use the show all command to check which channels are configured for access to backup devices. If none are configured then you will need to manually allocate one or more channels.

RMAN> SHOW ALL

Restore the database using the restore database command:

RMAN> RESTORE DATABASE;

If you have any read-only tablespaces you will need to force RMAN to restore any missing datafiles belonging to them. This is because RMAN typically skips read-only tablespaces on a restore.

To do this you can issue the following instead of the restore command above:

RMAN> RESTORE DATABASE CHECK READONLY;

Recover the database using the recover database command:

RMAN> RECOVER DATABASE;

NB. You can also recover the database appending delete archivelog maxsize <number> this will delete archivelogs after they have been applied and will prevent them from being bigger than the maxsize indicated. It should be noted that if the maxsize of the archivelogs is greater than the size given here, then the statement will give an error and you will need to increase the maxsize in the statement.

Once you have successfully recovered the database, you can open it from RMAN:

RMAN> ALTER DATABASE OPEN;

Further Reading

Basic Database Restore and Recovery Scenarios – Oracle Documentation 10.1

Oracle – Find View Source Code

August 21, 2011 1 comment

If you wish to find the source code for a view, you can look in the user_views table:

SQL> SELECT view_name, text FROM user_views;

You can check the v$fixed_view_definition table to check the definition of fixed views (views starting with v$).

SQL> SELECT view_name, view_definition FROM v$fixed_view_definition;

Further Reading

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1109.htm

Oracle – Backup Controlfile

August 20, 2011 1 comment

It is necessary, from time to time, to backup the controlfile. This can be done when the database is open or mounted.

Backup controlfile using SQL

If you wish to backup the controlfile using SQLPLUS then you can issue the following to backup the controlfile to a binary file:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';

This create a duplicate of the current controlfile.

However, if you wish to produce SQL statements that can later be used to re-create the controlfile, you can issue the following:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Backup controlfile using RMAN

RMAN will automatically backup the controlfile and server parameter file after every backup and after structural database changes, if CONFIGURE CONTROLFILE AUTOBACKUP is set to ON. It is set to OFF by default.

The controlfile autobackup contains metadata about the previous backup, which is essential for database recovery.

Manually backup controlfile

RMAN> BACKUP CURRENT CONTROLFILE;

It should be noted that a manual backup of the controlfile cannot be automatically restored. This is because it only contains RMAN repository data for backups within the current RMAN session. However, if controlfile autobackup is set to on, RMAN will manually backup the controlfile then it will create an autobackup of the controlfile and server parameter file after the manual backup has completed so it can record the latest backup information.

If you wish to include the current controlfile in a backup then you can perform something like the following:

RMAN> BACKUP DEVICE TYPE sbt TABLESPACE emp INCLUDE CURRENT CONTROLFILE;

If autobackup is enabled then RMAN performs and autobackup of the controlfile after the backup, so the controlfile backup contains details of the latest backup taken.

Backup controlfile as copy

You can also backup the controlfile as a copy. You can use either of the below commands for creating a backup controlfile copy:

RMAN> BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/tmp/controlfile_copy.ctl';

RMAN> BACKUP DEVICE TYPE sbt CONTROLFILECOPY  '/tmp/controlfile_copy.ctl';

Futher Reading

Managing Control Files – Oracle Documentation

Backing Up Database Files and Archived Logs With RMAN – Oracle Documentation

Oracle – PL/SQL decode function

August 19, 2011 1 comment

The decode function acts like IF…THEN…ELSE and can be used within PL/SQL code and SQL code much the same.

The syntax is as follows:

decode(expression, search, result [, search , result]... [, default] )

Example:

SELECT employee_name
DECODE(employee_id, 1, 'Alvin'
2, 'Simon'
Theodore') result
FROM employees;

The above code acts like the following:

IF employee_id = 1 THEN
result:= 'Alvin'
ELSIF employee_id = 2 THEN
result:= 'Simon'
ELSE
result:= 'Theodore'
END IF;

The decode will compare each employee_id one by one.

Oracle – RMAN backup example


RMAN can be used to backup a database. You can perform a hot backup if the database is in archivelog mode, or a cold backup if it is not. You can either perform a full backup or an incremental backup. RMAN is particularly useful because it only backs up used space.

An RMAN backup will backup the datafiles, controlfile, server parameter file, and archivelogs (where applicable).

To backup a database using RMAN with a level 0 backup you can perform the following:

$ ORACLE_SID = <oracle_sid>
$ORACLE_HOME = <oracle_home>
$ rman
RMAN> CONNECT TARGET /
RMAN> BACKUP DATABASE; # Cold backup without archivelogs
RMAN> BACKUP DATABASE PLUS ARCHIVELOG; # Cold backup with archivelogs

Incremental backups save time, bandwidth and reduce backup sizes. Incremental backups abck up all the blocks changed since the last level 0 incremental backup. If no level 0 backup has been run when you try to run a level 1 backup, then RMAN automatically makes a level 0 incremental backup.

To backup a database using RMAN with a level 1 incremental backup, and skipping inaccessible files, you can perform the following:

RMAN> BACKUP
INCREMENTAL LEVEL 1 CUMULATIVE
SKIP INACCESSIBLE
DATABASE;

Further Reading

http://download.oracle.com/docs/cd/B28359_01/backup.111/b28273/rcmsynta007.htm#RCMRF107