Background Processes in Oracle

Background Process:

• Database writer (DBWR): Writes modified blocks from database buffer cache to the datafile.
• Log Writer (LGWR): Writes redo log entries to disk.
• Check Point: At specific times all modified databases buffers in the SGA are written to the data files by DBWR. This event is called Checkpoint.
• System Monitor (SMON): Performs instance recovery at instance startup
• Process Monitor (PMON): Performs process recovery when user process fails
• Archiver (ARCH): Copies on line redo log files to archival storage when they are full
• Dispatcher: For multi threaded server for each request one dispatcher process
• Lock: For parallel server mode for inter instance locking

Hot & Cold Backup

A cold backup is a physical backup. During a cold backup the database is closed and not available to users.  All files of the database are copied (image copy).  The datafiles do not change during the copy so the database is in sync upon restore.
 

Used when:

Service level allows for some down time for backup

A hot backup is a physical backup.  In a hot backup the database remains open and available to users. All files of the database are copied (image copy).  There may be changes to the database as the copy is made and so all log files of changes being made during the backup must be saved too.  Upon a restore, the changes in the log files are reapplied to bring the database in sync.

Used when:

A full backup of a database is needed
Service level allows no down time for the backup

A logical backup is an extract of the database.  All SQL statements to create the objects and all SQL statements to populate the objects are included in the extract.  Oracle provides a utility export, to create the extract.   A partner utility, import, is used to bring the data back into the database.

A logical backup can be done at the table, schema (or proxy owner), or database level.  That is, we can extract only a list of specified tables, a list of specified schemas or the full database.

Used to:

Move or archive a database
Move or archive a table(s)
Move or archive a schema(s)
Verify the structures in the database

Early Binding/Late Binding

When you generate RPCs (remote procedure calls) using SQL*Module, you have a choice of early binding or late binding. Your choice of early or late binding is controlled by the BINDING option.

When you choose early binding, SQL*Module generates a call to the procedure stored in the database, and also uses a time stamp that is associated with the call. The time stamp records the date and time (to the nearest second) that the stored procedure was last compiled. The time stamp is created by the Oracle database. If a host application calls the stored procedure through the interface procedure, and the time stamp recorded with the interface procedure is earlier than the time stamp on the stored procedure recorded in the database, an error is returned to the host application in the SQLCODE and/or SQLSTATE status parameter. The SQLCODE error is 4062 "time stamp of name has been changed".

The late binding option, on the other hand, does not use a time stamp. If your application calls a stored procedure that has been recompiled since SQL*Module generated the interface procedure, no error is returned to the application.

The advantages of late binding are

• Greater flexibility
• Changes in the stored procedure(s) are transparent to the user
• Gives behavior similar to interactive SQL (for example, SQL*PLus)

The disadvantages of late binding are

• There might be additional performance overhead at runtime, due to the necessity of compiling the PL/SQL anonymous block.
• It is difficult to detect runtime PL/SQL compilation errors in the host application. For example, if the anonymous block that calls the late-bound procedure fails at runtime, there is no convenient way for the host application to determine the cause of the error.
• The lack of time-stamp capability means that changes, perhaps radical changes, in the stored procedure could be made after the host application was built, and the application would have no way of detecting this. 

Purity Levels

Prior to Oracle8i Release 8.1, it was necessary to assert the purity level of a packaged procedure or function when using it directly or indirectly in a SQL statement. Beginning with Oracle8i Release 8.1, the PL/SQL runtime engine determines a program's purity level automatically if no assertion exists.

The RESTRICT_REFERENCES pragma asserts a purity level. The syntax for the RESTRICT_REFERENCES pragma is:

PRAGMA RESTRICT_REFERENCES (program_name |

   DEFAULT, purity_level);

The keyword DEFAULT applies to all methods of an object type or all programs in a package.

There can be from one to five purity levels, in any order, in a comma-delimited list. The purity level describes to what extent the program or method is free of side effects. Side effects are listed in the following table with the purity levels they address.

Purity Level	Description	Restriction
WNDS	Write No Database State	Executes no INSERT, UPDATE, or DELETE statements.
RNDS	Read No Database State	Executes no SELECT statements.
WNPS	Write No Package State	Does not modify any package variables.
RNPS	Read No Package State	Does not read any package variables.
TRUST (Oracle8i)		Does not enforce the restrictions declared but allows the compiler to trust they are true.

The purity level requirements for packaged functions are different depending on where in the SQL statement the stored functions are used:

To be called from SQL, all stored functions must assert WNDS.

All functions not used in a SELECT, VALUES, or SET clause must assert WNPS.

To be executed remotely, the function must assert WNPS and RNPS.

To be executed in parallel, the function must assert all four purity levels or, in Oracle8i, use PARALLEL_ENABLED in the declaration.

These functions must not call any other program that does not also assert the minimum purity level.

If a package has an initialization section, it too must assert purity in Oracle7.

If a function is overloaded, each overloading must assert its own purity level, and the levels don't have to be the same. To do this, place the pragma immediately after each overloaded declaration.

Many of the built-in packages, including DBMS_OUTPUT, DBMS_PIPE, and DBMS_SQL, do not assert WNPS or RNPS, so their use in SQL stored functions is necessarily limited. 

Procedure/Function (Difference)

Procedure

A procedure is a subprogram that performs a specific action

Procedure Does and Does not return the Value.

Procedure we can use (In, Out, InOut Parameter)

You cannot use the procedure in select Statement.

Execute as a PL/SQL statement

No RETURN clause in the header

Can return none, one, or many values

Function

A function is a subprogram that computes a value

Invoke as part of an expression

Must contain a RETURN clause in the header

Must return a single value

Must contain at least one RETURN statement

Always return the Value.

Function you cannot use the (In, Out, InOut Parameter)

You can use the Function the in select Statement.