Explore topic-wise InterviewSolutions in .

Server Process - There is no background process which reads data from datafile or database buffer. Oracle creates server processes to handle requests from connected user processes. A server process communicates with the user process and interacts with Oracle to carry out requests from the associated user process. For EXAMPLE, if a user queries some data not already in the database buffers of the SGA, then the associated server process reads the proper data blocks from the datafiles into the SGA.

Oracle can be configured to VARY the number of user processes for each server process. In a dedicated server configuration, a server process handles requests for a SINGLE user process.A shared server configuration lets many user processes share a small number of server processes, minimizing the number of server processes and maximizing the USE of available system resources.

Server Process - There is no background process which reads data from datafile or database buffer. Oracle creates server processes to handle requests from connected user processes. A server process communicates with the user process and interacts with Oracle to carry out requests from the associated user process. For example, if a user queries some data not already in the database buffers of the SGA, then the associated server process reads the proper data blocks from the datafiles into the SGA.

Oracle can be configured to vary the number of user processes for each server process. In a dedicated server configuration, a server process handles requests for a single user process.A shared server configuration lets many user processes share a small number of server processes, minimizing the number of server processes and maximizing the use of available system resources.

CONNECT , RESOURCE and DBA are three default roles. The DBA_ROLES DATA dictionary view can be used to LIST all roles of a database and the authentication used for each ROLE.

The FOLLOWING QUERY lists all the roles in the database:

CONNECT , RESOURCE and DBA are three default roles. The DBA_ROLES data dictionary view can be used to list all roles of a database and the authentication used for each role.

The following query lists all the roles in the database:

Before Oracle changes data in a datafile it writes these changes to the redo LOG. If something happens to ONE of the datafiles, a backed up datafile can be RESTORED and the redo, that was written since, REPLIED, which brings the datafile to the state it had before it became unavailable.

Before Oracle changes data in a datafile it writes these changes to the redo log. If something happens to one of the datafiles, a backed up datafile can be restored and the redo, that was written since, replied, which brings the datafile to the state it had before it became unavailable.

Regardless of the setting of this paramter, maximum per DATABASE: 65533 (May be less on some operating systems) Maximum number of datafiles per tablespace: OS dependent = USUALLY 1022 Limited also by size of database blocks and by the DB_FILES initialization PARAMETER for a particular instance Bigfile tablespaces can contain only one file, but that file can have up to 4G blocks.

Regardless of the setting of this paramter, maximum per database: 65533 (May be less on some operating systems) Maximum number of datafiles per tablespace: OS dependent = usually 1022 Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance Bigfile tablespaces can contain only one file, but that file can have up to 4G blocks.

The db_files parameter is a "soft LIMIT " parameter that controls the MAXIMUM number of PHYSICAL OS files that can MAP to an Oracle instance. The maxdatafiles parameter is a DIFFERENT - "hard limit" parameter. When issuing a "create database" command, the value specified for maxdatafiles is stored in Oracle control files and default value is 32. The maximum number of database files can be set with the init parameter db_files.

The db_files parameter is a "soft limit " parameter that controls the maximum number of physical OS files that can map to an Oracle instance. The maxdatafiles parameter is a different - "hard limit" parameter. When issuing a "create database" command, the value specified for maxdatafiles is stored in Oracle control files and default value is 32. The maximum number of database files can be set with the init parameter db_files.

While Oracle instance FAILS, Oracle performs an Instance Recovery when the associated database is being re-started.

Instance recovery occurs in two steps:

Cache recovery:
Changes being made to a database are recorded in the database buffer cache.

These changes are also recorded in online redo LOG files simultaneously. When there are enough data in the database buffer cache,they are written to data files.

If an Oracle instance fails before the data in the database buffer cache are written to data files, Oracle uses the data recorded in the online redo log files to recover the lost data when the associated database is re-started.This process is called cache recovery.

TRANSACTION recovery:
When a transaction modifies data in a database, the before image of the modified data is stored in an undo segment.The data stored in the undo segment is used to restore the original values in case a transaction is rolled back.

At the TIME of an instance failure, the database may have uncommitted transactions. It is possible that changes made by these uncommitted transactions have gotten saved in data files.

To maintain read consistency, Oracle rolls back all uncommitted transactions when the associated database is re-started.Oracle uses the undo data stored in undo SEGMENTS to accomplish this.This process is called transaction recovery.

While Oracle instance fails, Oracle performs an Instance Recovery when the associated database is being re-started.

Instance recovery occurs in two steps:

Cache recovery:
Changes being made to a database are recorded in the database buffer cache.

These changes are also recorded in online redo log files simultaneously. When there are enough data in the database buffer cache,they are written to data files.

If an Oracle instance fails before the data in the database buffer cache are written to data files, Oracle uses the data recorded in the online redo log files to recover the lost data when the associated database is re-started.This process is called cache recovery.

Transaction recovery:
When a transaction modifies data in a database, the before image of the modified data is stored in an undo segment.The data stored in the undo segment is used to restore the original values in case a transaction is rolled back.

At the time of an instance failure, the database may have uncommitted transactions. It is possible that changes made by these uncommitted transactions have gotten saved in data files.

To maintain read consistency, Oracle rolls back all uncommitted transactions when the associated database is re-started.Oracle uses the undo data stored in undo segments to accomplish this.This process is called transaction recovery.

SMON : System Monitor Process - Performs recovery after instance failure, MONITORS temporary segments and EXTENTS; cleans temp segments, coalesces free space (MANDATORY process for DB and starts by default).

PMON : Process Monitor - Recovers failed process resources. In Shared Server architecture, monitors and retarts any failed DISPATCHER or server proceses (mandatory process for DB and starts by default).

SMON : System Monitor Process - Performs recovery after instance failure, monitors temporary segments and extents; cleans temp segments, coalesces free space (mandatory process for DB and starts by default).

PMON : Process Monitor - Recovers failed process resources. In Shared Server architecture, monitors and retarts any failed dispatcher or server proceses (mandatory process for DB and starts by default).

Oracle first opens and READS the initialization parameter FILE (init.ora) [oracle@hostname ~]$ LS -la $ORACLE_HOME/dbs/initDB1_SID.ora -rw-r--r-- 1 oracle oinstall 1023 May 10 19:27 /u01/app/oracle/product/ 11.2.0/dbs/initDB1_SID.ora.

Oracle first opens and reads the initialization parameter file (init.ora) [oracle@hostname ~]$ ls -la $ORACLE_HOME/dbs/initDB1_SID.ora -rw-r--r-- 1 oracle oinstall 1023 May 10 19:27 /u01/app/oracle/product/ 11.2.0/dbs/initDB1_SID.ora.

Oracle DATABASE must have at least one control file.
It's a binary file contains some of the following information:

• The database name and unique ID.
• The timestamp of database creation.
• The names and locations of associated datafiles and REDO log files.
• Tablespace information.
• DATAFILE offline RANGES.
• Archived log information and history.
• Backup set and backup piece information.
• Backup datafile and redo log information.
• Datafile copy information.
• Log records: sequence numbers, SCN range in each log.
• RMAN Catalog.
• Database block CORRUPTION information.

The location of the control files is specified through the control_files init param:

Oracle Database must have at least one control file.
It's a binary file contains some of the following information:

The location of the control files is specified through the control_files init param:

Basically, set up DISKS, KERNEL PARAMETERS, and RUN orainst.

Basically, set up disks, kernel parameters, and run orainst.

SHMMAX & SHMMNI.

SHMMAX & SHMMNI.

Use :%s/&LT;OLD&GT;/<NEW>/G.

Use :%s/<old>/<new>/g.

Iostat: reports on terminal, disk and TAPE I/O activity.

Vmstat: reports on virtual memory statistics for processes, disk, tape and CPU activity.

Netstat: reports on the contents of NETWORK DATA structures.

Iostat: reports on terminal, disk and tape I/O activity.

Vmstat: reports on virtual memory statistics for processes, disk, tape and CPU activity.

Netstat: reports on the contents of network data structures.

DF -LK.

df -lk.

A symbolic (soft) linked FILE and the TARGETED file can be LOCATED on the same or DIFFERENT file system while for a hard link they must be located on the same file system.

A symbolic (soft) linked file and the targeted file can be located on the same or different file system while for a hard link they must be located on the same file system.

Read ALLOWS you to see and list the DIRECTORY contents.

Write allows you to create, edit and delete FILES and SUBDIRECTORIES in the directory.

Execute gives you the previous read/write PERMISSIONS plus allows you to change into the directory and execute programs or shells from the directory.

Read allows you to see and list the directory contents.

Write allows you to create, edit and delete files and subdirectories in the directory.

Execute gives you the previous read/write permissions plus allows you to change into the directory and execute programs or shells from the directory.

Umask.

Umask.

USE the "&AMP;".

Use the "&".

LS -ltra.

ls -ltra.

It disables the logging of changes to the redologs. It does not DISABLE ALL LOGGING, however as Oracle continues to use a base of changes, for recovery if you pull the PLUG on the box, for instance. However it will cause problems if you are using STANDBY database. Use it to speed up operations, like an index REBUILD, or PARTITION maintenance operations.

It disables the logging of changes to the redologs. It does not disable ALL LOGGING, however as Oracle continues to use a base of changes, for recovery if you pull the plug on the box, for instance. However it will cause problems if you are using standby database. Use it to speed up operations, like an index rebuild, or partition maintenance operations.

Fast refresh maintains a change log table, which records change vectors, not unlike how the redo logs work. There is overhead to this, as with a table that has a LOT of indexes on it, and inserts and updates will be slower. However if you are performing refreshes often, LIKE every few minutes, you want to do fast refresh so you don't have to full-table-SCAN the SOURCE table. Complete refresh is good if you're GOING to refresh once a day. Does a full table scan on the source table, and recreats the snapshot/mview. Also inserts/updates on the source table are NOT impacted on tables where complete refresh SNAPSHOTS have been created.

Fast refresh maintains a change log table, which records change vectors, not unlike how the redo logs work. There is overhead to this, as with a table that has a LOT of indexes on it, and inserts and updates will be slower. However if you are performing refreshes often, like every few minutes, you want to do fast refresh so you don't have to full-table-scan the source table. Complete refresh is good if you're going to refresh once a day. Does a full table scan on the source table, and recreats the snapshot/mview. Also inserts/updates on the source table are NOT impacted on tables where complete refresh snapshots have been created.

TRUNCATE is nearly instantaenous, cannot be rolled back, and is FAST because Oracle simply RESETS the HWM. When a full table scan is performed on a table, such as for a sort operation, Oracle reads to the HWM. So if you delete every single solitary ROW in 10 million row table so it is now empty, sorting on that table of 0 rows would still be extremely SLOW.

Truncate is nearly instantaenous, cannot be rolled back, and is fast because Oracle simply resets the HWM. When a full table scan is performed on a table, such as for a sort operation, Oracle reads to the HWM. So if you delete every single solitary row in 10 million row table so it is now empty, sorting on that table of 0 rows would still be extremely slow.

• CREATE INDEX.
• DISTINCT.
• GROUP BY.
• ORDER BY.
• INTERSECT.
• MINUS.
• UNION.
• UNINDEXED TABLE JOIN.

• EXISTS can be BETTER than IN under various CONDITIONS.
• UNION ALL is FASTER than UNION (not sorting).

SET the WORKAREA_SIZE_POLICY PARAMETER to AUTO and set PGA_AGGREGATE_TARGET.

Set the WORKAREA_SIZE_POLICY parameter to AUTO and set PGA_AGGREGATE_TARGET.

Be sure to SET NLS_LANG for EXAMPLE to "AMERCIAN_AMERICA.WE8ISO8859P1". If your SOURCE DATABASE is US7ASCII, beware of 8-bit characters. Also be wary of multi-byte characters sets as those may require extra attention. Also watch export/import for messages about any "character set conversions" which may occur.

Be sure to set NLS_LANG for example to "AMERCIAN_AMERICA.WE8ISO8859P1". If your source database is US7ASCII, beware of 8-bit characters. Also be wary of multi-byte characters sets as those may require extra attention. Also watch export/import for messages about any "character set conversions" which may occur.

RAID 5 is striping with an extra disk for parity. If we lose a disk we can reconstruct from that parity disk.

RAID 10 is mirroring pairs of disks, and then striping across those sets.

RAID 5 was created when disks were expensive. Its purpose was to provide RAID on the cheap. If a disk fails, the IO subsystem will PERFORM VERY slowly during the rebuild process. What's more your liklihood of failure increases DRAMATICALLY during this period, with all the added weight of the rebuild.

Even when it is operating normally RAID 5 is slow for everything but reading. Given that and knowing databases (especially Oracle's redo logs) continue to experience WRITE ACTIVITY all the time, we should avoid RAID5 in all but the rare database that is MOSTLY read activity. Don't put redologs on RAID5.

RAID10 is just all around goodness. If you lose one disk in a set of 10 for example, you could lose any one of eight other disks and have no troubles. What's more rebuilding does not impact performance at all since you're simply making a mirror copy. Lastly RAID10 perform exceedingly well in all types of databases.

RAID 5 is striping with an extra disk for parity. If we lose a disk we can reconstruct from that parity disk.

RAID 10 is mirroring pairs of disks, and then striping across those sets.

RAID 5 was created when disks were expensive. Its purpose was to provide RAID on the cheap. If a disk fails, the IO subsystem will perform VERY slowly during the rebuild process. What's more your liklihood of failure increases dramatically during this period, with all the added weight of the rebuild.

Even when it is operating normally RAID 5 is slow for everything but reading. Given that and knowing databases (especially Oracle's redo logs) continue to experience write activity all the time, we should avoid RAID5 in all but the rare database that is MOSTLY read activity. Don't put redologs on RAID5.

RAID10 is just all around goodness. If you lose one disk in a set of 10 for example, you could lose any one of eight other disks and have no troubles. What's more rebuilding does not impact performance at all since you're simply making a mirror copy. Lastly RAID10 perform exceedingly well in all types of databases.

Fewer INDEXES on a table mean FASTER inserts/updates. More indexes mean faster, more specific WHERE clauses possibly WITHOUT index MERGES.

Fewer indexes on a table mean faster inserts/updates. More indexes mean faster, more specific WHERE clauses possibly without index merges.

SQL*LOADER with direct PATH option can load data ABOVE the high water MARK of a table, and DIRECTLY into the datafiles, without going through the SQL ENGINE at all. This AVOIDS all the locking, latching, and so on, and doesn't impact the db (except possibly the I/O subsystem) at all.

SQL*Loader with direct path option can load data ABOVE the high water mark of a table, and DIRECTLY into the datafiles, without going through the SQL engine at all. This avoids all the locking, latching, and so on, and doesn't impact the db (except possibly the I/O subsystem) at all.

ORACLE's SQL and PL/SQL engines are separate parts of the kernel which require context switching, like between unix processes. This is slow, and uses up resources. If we loop on an SQL STATEMENT, we are implicitely FLIPPING between these two engines. We can minimize this by loading our DATA into an array, and using PL/SQL bulk binding operation to do it all in one GO.

Oracle's SQL and PL/SQL engines are separate parts of the kernel which require context switching, like between unix processes. This is slow, and uses up resources. If we loop on an SQL statement, we are implicitely flipping between these two engines. We can minimize this by loading our data into an array, and using PL/SQL bulk binding operation to do it all in one go.

With bind variables in SQL, Oracle can cache related queries a single time in the SQL cache (area). This avoids a hard parse each time, which saves on various locking and LATCHING resources we use to check OBJECTS existence and so on. BONUS: For rarely run queries, especially BATCH queries, we explicitely DO NOT WANT to use bind variables, as they hide information from the Cost Based Opitmizer.

With bind variables in SQL, Oracle can cache related queries a single time in the SQL cache (area). This avoids a hard parse each time, which saves on various locking and latching resources we use to check objects existence and so on. BONUS: For rarely run queries, especially BATCH queries, we explicitely DO NOT want to use bind variables, as they hide information from the Cost Based Opitmizer.

RMAN is FASTER, can do incremental (changes only) BACKUPS, and does not place tablespaces into hotbackup mode.

RMAN is faster, can do incremental (changes only) backups, and does not place tablespaces into hotbackup mode.