TRAINING (ORA-8) - Different Oracle Authentication Type

Different Oracle Authentication Type
Oracle Authentication can be of following type:

1.) Password:
This is also referred as authentication by database.

2.) External:
This is also referred as authentication by OS. With external authentication, your database relies on underlying OS or network services to restrict access to database accounts. User can connect oracle database without specifying user name and password.

3.) Global:
Allows users to be identified through the use of biometrics, x509 certificates, token devices and Oracle Internet Directory.


The password file: The problem, password file addresses is how to authenticate a user when the database is not open, or indeed before the database has even been created or an instance started. Users are nothing more than rows in a table in the data dictionary. You can see them and their encrypted passwords by querying the data dictionary view DBA_USERS. When you create a user, you are simply inserting rows into the data dictionary.

It is therefore vitally important that Oracle should authenticate you, before letting you connect as such a user. But if the database is not already created and open, how can Oracle query the data dictionary to validate your username and password, and thus work out who you are and what you are allowed to do? To resolve this paradox, Oracle has provided two means of authentication that are not data dictionary based and therefore do not require the database to be open, or even to exist. These are:

1.) Operating System authentication
2.) Password file authentication.

For operating system authentication, Oracle delegates responsibility for identifying a user to the host operating system. At installation time (not database creation time!) you specified an operating system group name that would own the Oracle software, defaulting to dba on Unix, ORA_DBA on Windows. If you are logged on to the computer hosting the Oracle installation as a member of that group, then you will be allowed to connect (using appropriate syntax) to an instance, start it up, and open or create a database without any username/password prompt. Clearly, this relies totally on your operating system being secure, which it should be: that is out of Oracle’s control and relies on decent system administration.
But this mechanism can’t work if you are connecting to the instance remotely, across a network: you will never actually log on to the operating system of the machine hosting the Oracle server, only to the machine where you are working. This is where the password file comes in: it is an operating system file, with usernames and passwords encrypted within it, that exists independently of the database. Using appropriate syntax, you can connect to an instance, respond to a prompt with a username/password combination that exists in the password file, start the instance, and open an existing database or create a new one. If you do not need to start the instance and open the database across a network but you can always log on to the computer hosting the Oracle installation, then a password file is not strictly necessary because you can use operating system authentication instead. However, for practical purposes, you will always have one.


Ordinary users cannot start up or shut down a database. This is because an ordinary user is authenticated against the data dictionary, and it is logically impossible for an ordinary user to start up (or create) a database, since the data dictionary cannot be read until the database is open.

We must therefore connect with some form of external authentication: you must be authenticated either by the operating system, as being a member of the group that owns the Oracle software, or by giving a username/password combination that exists in the external password file. You tell Oracle that you wish to use external authentication by using appropriate syntax in the CONNECT command that you give in your user process. If you are using Database Control, it is easy: in the Connect As combo box you select either SYSOPER or SYSDBA.

These are the possibilities:
connect user/pass[@db]                 :      data dictionary authentication.
connect user/pass[@db] as sysdba    :      external password file
connect user/pass[@db] as sysoper   :      external password file
connect / as sysdba                       :      OS authentication
connect / as sysoper                      :      OS authentication

TRAINING (ORA-7) - Undo Management and Undo Tablespace

Undo Management and Undo Tablespace

Undo data is:
- A copy of original, pre modified data
- Captured for every transaction that changes data
- Retained at least until the transaction is ended
- Used to support:
Rollback operations
Read-consistent and flashback queries
Recovery from failed transactions


Monitoring Undo: Undo usually requires little management. Areas to monitor include:
- Undo tablespace free space
- “Snapshot too old” error


Administration of undo:

- Issue
     > Undo tablespace space errors
- Solution
     > Size the undo tablespace properly
     > Ensure large transactions commit periodically

- Issue
     > “Snapshot too old” errors
- Solution
     > Configure an appropriate undo retention interval
     > Size the undo tablespace properly
     > Consider guaranteeing undo retention

TRAINING (ORA-6) - Delivered Tablespaces in the preconfigured Database

Delivered Tablespaces in the preconfigured Database

SYSTEM
- The SYSTEM tablespace is always created at database creation. Oracle uses it to manage the database.
- It contains the data dictionary, which is the central set of tables and views used as a read-only reference describing a particular database.
- It also contains various tables and views that contain administrative information about the database. These are all contained in the SYS schema, and can only be accessed by user SYS, or other administrative users with the required privilege.
- For system users, the default permanent tablespace remains SYSTEM.

SYSAUX
- This is an auxiliary (supplementary or supportive) tablespace to the SYSTEM tablespace. This reduces the load on the SYSTEM tablespace.
- Introduced in Oracle 10g. Components that use this as their default tablespace during installation
- Every Oracle Database 10g or higher level database must have a SYSAUX tablespace.
- SYSAUX tablesaces include Automatic Workload Repository, Oracle Streams, Oracle Text, and Enterprise Manager Repository components and products.
- Some of the components and products mentioned above earlier used to be in SYSTEM tablespace but now use the SYSAUX tablespace.

TEMP
- This tablespace stores temporary data generated when processing SQL statements. It would, for example, be used for sort work space.
- Every database should have a temporary tablespace that is assigned to users as their temporary tablespace.
- In the preconfigured database, the TEMP tablespace is specified as the default temporary tablespace. This means that if no temporary tablespace is specified when the user account is created, then Oracle assigns this tablespace to the user.

UNDOTBS1
- This is the undo tablespace used by the database server to store undo information.
- Every database must have an undo tablespace that can be created at database creation.

USERS
- This tablespace is used to store permanent user objects and data.
- Like the TEMP tablespace, every database should have a tablespace for permanent user data that is assigned to users. Otherwise, their objects will be created in the SYSTEM tablespace, and this is not good practice.
- In the preconfigured database, USERS is assigned the default tablespace, and space for all objects created by non-system users comes from this tablespace.

EXAMPLE
- This tablespace contains the sample schemas that Oracle includes with the database. The sample schemas provide a common platform for examples. Oracle documentation and educational materials contain examples based on the sample schemas.

TRAINING (ORA-5) - Extent Management in Tablespaces

Extent Management in Tablespaces

1.) Locally Managed Tablespace:
- Free extents are managed in the tablespace.
- Bitmap is used to record free extents.
- Each bit corresponds to a block or group of blocks.
- Bit value indicates free or used.

2.) Dictionary Managed Tablespace:
- Free extents are managed by the data dictionary.
- Appropriate tables are updated when extents are allocated or deallocated.

TRAINING (ORA-4) - Database Logical Architecture

Database Logical Architecture

Database objects such as table, index are stored as segments in tablespaces. Oracle uses the term “segment” to describe any structure that contains data. Typical segment is a table, containing rows of data, but there are more than a dozen possible segment types in an Oracle database. Of particular interest are table segments, index segments, and undo segments. Tables contain rows of information; that indexes are a mechanism for giving fast access to any particular row; and that undo segments are data structures used for storing information that might be needed to reverse, or roll back, any transactions that you do not wish to make permanent.

> System administrators see physical datafiles; programmers see logical segments. Oracle abstracts the logical storage from the physical storage by means of the tablespace.
> A tablespace is logically a collection of one or more segments, and physically a collection of one or more datafiles.
> One tablespace can belong to only one database at a time.
> One tablespace consist of one or more data files.
> Tablespaces are further divided into logical units of storage called Segments.
> Segments exist within a tablespace.
> Segments are made of a collection of extents.
> Extents are a collection of data blocks.
> Data blocks are mapped to OS blocks.

TRAINING (ORA-3) - Database Physical Architecture

Database Physical Architecture



Control Files

> The control file is small, but vital.
> Every database has minimum one control file.
> It contains pointers to the rest of the database: the locations of the online redo log files and of the data files.
> It also stores information required to maintain database integrity: various critical sequence numbers and time stamps.
> The control file will usually be no more than a few megabytes big, but you can’t survive without it.
> Control file maintenance is automatic; your only control is how many copies to have, and where to put them.
> Control file are multiplexed to protect against loss.




Redo Log Files

> Record changes to the database.
> Every database has at least two online redo log files.
> The online redo logs store a continuous chain in chronological order of every change applied to the database. This will be the bare minimum of information required to reconstruct, or redo, changes.
> The redo log consists of groups of redo log files, each file being known as a member.
> Oracle requires at least two groups of at least one member each to function.
> Redo Log files are multiplex to protect against loss.




Data Files
> Oracle stores data physically in data files and logically in tablespaces.
> Data files can belong to only one tablespace and one database.
> Data files are a repository for schema object data.
> Every Database must have two data files, to be created at database creation time.
> With previous releases of Oracle, you could create a database with only one datafile—10g requires two.
> One or more data files could create a single tablespace.

TRAINING (ORA-2) - Different Stages of Buffer in the Buffer Cache

Different Stages of Buffer in the Buffer Cache

Oracle uses least recently used algorithm to age out buffers that have not been accessed recently to make room to new blocks in database buffer cache. Buffer in the Buffer cache can be in any of the four stages:

Pinned: Multiple sessions are kept from writing to the same block at the same time. Other sessions wait to access the block.

Clean: Buffer is now unpinned and is a candidate for immediate aging out, if the current contents (data blocks) are not referenced again. Either the contents are in sync with the block stored on the disk or the buffer contains a consistent read (CR) snapshot of a block.

Free or Unused: The buffer is empty because the instance has just started. This state is very similar to clean state except that the buffer has not been used.

Dirty: Buffer is no longer pinned but the content (data blocks) have changed and must be flushed to the disk by DBWn before it can be aged out.

TRAINING (ORA-1) - Oracle Technical Architecture

Instance: Memory Structures and Background Processes




Memory Structures:

1.) Shared Pool Shared Pool is further subdivided into a number of other structures.

i.) The library Cache: 
The library cache is a memory area for storing recently executed code, in its parsed form. Parsing is the conversion of code written by programmers into something executable, and it is a slow process that Oracle does on demand. By caching parsed code in the shared pool so that it can be reused without reparsing, performance can be greatly improved.
ii.) Data Dictionary Cache
The data dictionary cache stores recently used object definitions: descriptions of tables, indexes, users, and other metadata definitions. Keeping such definitions in memory, rather than having to read them repeatedly from the data dictionary on disk, enhances performance.

2.) Database Buffer Cache
Database Buffer Cache is Oracle’s work area for executing SQL. Users don’t ever update data on disk. They copy data into the database buffer cache and update it there, in memory. Ideally, all the data that is frequently accessed will be in the database buffer cache, therefore minimizing the need for disk I/O.

3.) Log Buffer
Log Buffer is a very small memory structure used as a short-term staging area for all changes that are applied to data in the database buffer cache.

4.) Large Pool 
Large Pool is an optional area that, if created, will be used automatically by various processes that would otherwise take memory from the shared pool.

5.) Java Pool 
Java Pool is required only if your application is going to run Java stored procedures within the database: it is used for the heap space needed to instantiate the Java objects. However, a number of Oracle options are written in Java, so the Java pool is considered standard nowadays.
Streams Pool is used by Oracle streams, an advanced tool that is beyond the scope of this session.


Background Processes:

SMON
SMON’s major function is opening the database: enabling the connection between the instance and a database. During normal running, it carries out a number of monitoring and tidying-up operations.

PMON
PMON looks after user sessions, taking appropriate action if a session gets into trouble. For instance, if a user’s PC reboots while the user is logged on to the database, PMON will detect this and tidy up whatever work the user had in progress.

DBWn 
DBWn process or processes (by default, an instance will have one database writer per eight CPUs) is responsible for all writing to datafiles. Remember no sessions ever update data on disk; they update only data in the database buffer cache: all updates are then funneled through the DBWn to disk. In general, DBWn writes as little and as rarely as possible. The assumption is that disk I/O is bad for performance, so Oracle keeps it to a minimum.

LGWR 
LGWR propagates all changes applied to data in the database buffer cache to the online redo log files on disk. In contrast with DBWn, this disk write activity is done as near as possible in real time—and when you issue the COMMIT statement, it really is done in real time: it immediately flushes the changes from their small and temporary staging area, the log buffer in the SGA, to the online redo log files on disk. This is to ensure that all users’ work is saved so that, in the event of damage to the database’s datafiles, the changes can be applied to a restored backup. In this manner Oracle can guarantee that data will never be lost.

CKPT
CKPT process is responsible for ensuring that, from time to time, the instance is synchronized with the database. In principle, the database is always out of date: there will be changes that have been applied in memory that have not yet been written to the datafiles by DBWn (though the changes themselves will have been streamed out to the online redo log files by LGWR as they happen). There are occasions when it is necessary to force a write of all changed data from the database buffer cache to the datafiles, to bring the database right up-to-date. The CKPT process controls the frequency of this.