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

Advertisements

Oracle – Recover database to point in time


Recovering an Oracle database involves applying redo logs to roll it forward. You can roll forward to a specific point-in-time, or until the latest transaction.

Point-in-time recovery is only available when the database is in ARCHIVELOG mode.

You can use either SQLPLUS or RMAN when recovering a database:

$ sys '/as sysdba'
SQL> RECOVER DATABASE UNTIL TIME '2011-08-08:16:16:16' USING BACKUP CONTROLFILE;

or

RMAN> run {
SET UNTIL TIME to_date('08-Aug-2011 16:16:16', 'DD-MON-YYYY HH24:MI:SS');
restore database;
recover database;
}

Oracle – SQL projection


In Oracle projection limits the columns returned by a query. It is nothing more than a named list of columns which can be made use of in an application. Projections are purely for use by the user and have no affect on business rules.

To return all columns you use * after the SELECT.

Example:

SQL> SELECT name
, age
FROM emp;

Oracle – SQL functions


Numeric results

A single-row function may accept multiple input parameters but always returns one value per row.

Single-row numeric functions always return numeric results. Numeric functions are the only category of functions that always return numeric results.

Character and date functions like LENGTH and MONTHS_BETWEEN return numeric results.

Character results

If the input argument is char or varchar2, then the returned value is varchar2.

For functions that return  char or varchar2, if the length of the return value exceeds the maximum length limit of the datatype, then Oracle truncates it and returns the result without an error message.

For functions that return clob values, if the length of the return value exceeds the maximum length limit of the datatype, then Oracle raises and error and no result is returned.

Character Functions Returning Number Values

Character functions that return number values can take any character datatype as their argument.

Datetime Functions

Datetime functions operate on date, timestamp, and interval.

Some of the datetime functions were designed specifically for the Oracle date datatype. If you provide a timestamp value as their argument, then Oracle internally converts this to a date value and returns a date value. However, the months_between function returns a number, and the round and trunc functions do not accept timestamp or interval at all.

Conversion Functions

Conversion functions convert a value from one datatype to another. You normally do this by datatype1 to datatype2 where datatype1 is the current datatype, and datatype2 is the new datatype.

Aggregate Functions

Distinct causes an aggregate function to consider only unique values of the argument expression.

All causes and aggregate function to consider all values, including duplicates.

All aggregate functions except count(*) and grouping ignore nulls.

You can nest aggregate functions. For example you could find the average maximum salary for an employee in the employees table.

Analytic Functions

Analytic functions compute a value based on a group of rows. They are different from aggregate functions, in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. The window determines the range of rows used to perform the calculations of the current row.

Analytic functions are the last set of operations performed in a query except for the final order by clause. Analytic functions can appear only in the select list or order by clause.

Further Reading

SQL Functions Oracle Documentation

Oracle – PL/SQL instr command


The instr command can be used in PL/SQL to return the location of a substring in a string.

Syntax:

instr( string1, string2 [, start_position [, nth_appearance ] ] )

string1 – string to search

string2 – string to find

start_position – position to start searching from

nth_appearance – appearance of string to search for

Examples:

instr(‘she_sells_sea_shells_on_the_sea_shore’,’s’,4,3) – returns 14, the 3rd appearance of s after the 4th letter.

Further Reading:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions068.htm

Oracle – Cartesian join


A cartesian join is a join of every row in one table with every row of another.

For example if you have one table with 5 rows, and another with 10 rows and you join them without specifying a join criteria, 50 rows will be returned.

To prevent this we use the where clause to specify a common ground between the two tables.

Examples of cartesian join:

SQL> SELECT * FROM emp, dept;

SQL> SELECT * FROM emp CROSS JOIN dept;

SQL> SELECT e.name
FROM emp e
, dept d
WHERE e.name !='GARY'
AND d.dept_id !=7;

Categories: Oracle, SQL Tags: ,

Oracle – Intersect


Intersect is a SQL command that will return all rows that are in both the first query and the second query.

For example:

SQL> SELECT * FROM emp;
NAME AGE DEPT_ID
---- --- -------
Joe   27       5
Mark  29       2

SQL> SELECT * FROM dept;
DEPT_ID NAME
------- -----------
1       IT
2       Accounts
3       HR
4       DBA
5       Development

Find a list of all department id’s that are being used.

SQL> SELECT dept_id
FROM emp
INTERSECT
SELECT dept_id
FROM dept;
DEPT_ID
--------
2
5

Find a list of all the names of the deparment id’s currently being used.

SQL> SELECT name
FROM dept
WHERE dept_id IN (SELECT e.dept_id
FROM emp e
INTERSECT
SELECT d.dept_id
FROM dept d);
NAME
-----------
Accounts
Development

The reason for using intersect is to filter out the duplicates. If we did a simple select name where dept_id’s are the same we get the following:

SQL> SELECT d.name
FROM dept d
, emp e
WHERE d.dept_id = e.dept_id;
NAME
-----------
Development
Development
Accounts

You could change this to have distinct at the beginning to get the same result as before.

SQL> SELECT DISTINCT d.name
FROM dept d
, emp e
WHERE d.dept_id = e.dept_id
NAME
-----------
Development
Accounts

However, what is important to note is that the intersect performs both queries then does a comparison on them, which in turn has a better performance time than the latter.