Oracle DBA concepts, DBA tutorials for Beginners , Oracle helps

Migrating Dictionary Managed Tablespace To Locally Managed Tablespace

2020-10-22T00:25:00.003+05:30

Migrate a dictionary managed SYSTEM tablespace to locally managed :

SQL> DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');

Use the below following steps to convert your SYSTEM tablespace from Dictionary Managed to locally Managed :

Make a complete backup of your Database
Ensure that the database has a default temporary tablespace that is not SYSTEM. The temporary tablespace is created using the CREATE TEMPORARY TABLESPACE command.
Eliminate any undo (rollback) segments in disctionary-managed tablespaces.
There should be at least one online undo segment in a locally managed tablespace, or an undo tablespace should be online.
All tablespaces other than the tablespace containing the undo space and the default temporary tablespace should be placed in READ ONLY mode.
Startup the instance in restricted mode.
Migrate the SYSTEM tablespace by using the above command.

Note: Any non-SYSTEM dictionary managed tablespace that is not migrated to locally managed prior to migrating the SYSTEM tablespace, cannot be altered to READ-WRITE. The tablespace will remain READ-ONLY. Only locally managed tablespaces can be altered to READ-WRITE after the migration of the system tablespace.

Thank you !!

Space Management in Tablespace

2020-10-13T00:44:00.004+05:30

Tablespaces allocated space in extents. Tablespaces can be created to use one of the following two different methods of keeping track of free and used space.

Locally Managed tablespace
Dictionary Managed Tablespace

Locally Managed tablespace:

- Free Extents are managed in the tablespace.

- Bitmap is used to records free extents.

- Each bit corressponds to a block or group of blocks.

- Bit Value indicates free or used.

The Extents are managed within the tablespace via bitmaps. Each bit in the bitmap corresponds to a block or a group of blocks. When an extent is allocated or freed for reuse, the oracle server changes the bitmap values to show the new status of the bocks. Locally managed is the default tablespace management it is introduced in Oracle 9i.

Locally managed tablespaces are :

Reduced contention on data dictionary tables.
No undo generated when space allocation or deallocation occurs
No Coalescing required.

SQL Command for Creation of locally managed tablespace for Example:

SQL> CREATE TABLESPACE userdata

DATAFILE '/u01/oradata/userdata01.dbf' size 100M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

The LOCAL option of the EXTENT MANAGEMENT clause specifies that a tablespace is to be locally managed. By default, a tablespace is locally managed.

extent_management_clause :

[EXTENT MANAGEMENT [ DISCTIONARY | LOCAL

[AUTOALLOCATE | UNIFORM [SIZE integer [K/M]]]]

Where :

DICTIONARY: Specifies that the tablespace is managed using dictionary tables.

LOCAL: Specifies that the tablespace is Locally managed via bitmaps. If you specify LOCAL, you cannot specify DEFAULT storage_caluse, MINIMUM EXTENT, or TEMPORARY.

AUTOALLOCATE: Specifies that the tablespace is system managed. Users cannot specify the extent size. This is the default.

UNIFORM: Specifies that the tablespace is managed with uniform extents of size bytes. Use the K or M to specify the extent size in kilobytes or megabytes. The default size is 1 MB.

The EXTENT_MANAGEMENT clause can be used in various CREATE command :

For a permanent tablespace, you can specify EXTENT_MANAGEMENT_LOCAL in the CREATE TABLESPACE command.

Note : Parior to Oracle 9i release 2 , the SYSTEM tablespace was not LOCALLY managed.

For a temporary tablespace, you can specify EXTENT_MANAGEMENT_LOCAL in the CREATE TEMPORARY TABLESPACE command.

Advantages of Locally Managed Tablespaces :

A locally managed tablespace has the following advantage over the Dictionary Managed tablespaces :

Local Management avoids recursive space management operations. This can occur in Dictionary-Managed tablespaces if consuming or releasing space to an extent and in results of this operation another operation that consumes or release space in an Undo segment or Data Dictionary tables.
Because locally managed tablespaces do not record free space in the data dictionary tables, they reduce contention on these tables.
Local management of extents automatically tracks adjacent free space, Eliminating the need to coalesce free extents.
The sizes of the extents that are managed locally can be determined automatically by the system.
Changes to the extent bitmaps do not generate undo information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).

Dictionary- Managed Tablespace: Characteristics :

- Free Extents are managed by the database disctionary.

- Extents are managed in the data dictionary.

- Appropriate tables are updated when extents are allocated or deallocated.

- Each segment stored in the tablespace can have a different storage clause.

- Coalescing is required.

The extents are managed by the data dictionary. The Oracle server updates the appropriate tables in the data dictionary whenever an extent is allocated or deallocated.

Segments in dictionary-managed tablespaces can have customized storage. This storage is more flexible than locally managed tablespaces but much less efficient.

SQL Command for Creation of Dictionary managed tablespace for Example:

SQL> CREATE TABLESPACE userdata

DATAFILE '/u01/oradata/userdata01.dbf' size 100M

EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE

(initial 1M next 1M PCTINCREASE 0);

Thank you !!

Managing Tablespaces and Data Files

2020-09-28T22:49:00.002+05:30

Tablespace and Data Files :

Oracle Stores Data logically in tablespaces and Physically in Data files.

Tablespaces :

- Can belong to only one databses at a time.

- Consist of one or more data files

- Are further devided into logical units of stoarge.

Data Files :

- Can belong to only one tablespace and one databases

- Are a repository for Schema object data.

Databases, Tablespaces, and Data files are closely related, but they have important differences :

An Oracle Database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data.
Each tablespace in an Oracle Database consists of one or more files called data files, which are physical structures that conform with the operating system in which Oracle is running.
A database's data is collectively stored in the data files that constitute each tablespace of the database.

For example, the simplest Oracle Database would have one tablespace and one data file. Another database can have more tablespaces with more data files.

Types of tablespaces :

SYSTEM Tablespace

- Created with the database

- Required in all databases

- Contains the data Dictionary, Including stored program units

- Contains the SYSTEM undo segments

- Should not contain user data, although it is allowed.

Non-SYSTEM tablespace

- Separate Segments

- Separate undo temporary, application data, and application index segments

- Separate Data by backup requirements

- Separate dynamic and static data

- Eases Space adminstration

- Controls amount of space allocation to a user

The DBA creates tablespace for increased control and ease of maintenance.

Creating Tablespaces:

A tablespace is created using the command: CREATE TABLESPACE:

Example:

CREATE TABLEPSACE userdata DATAFILE

'/u01/oradata/userdata01.dbf' size 100m;

Tablespace Creation syntax with more option Details :

Syntax:

CREATE TABLESPACE tablespace name

[DATAFILE clause] [MINIMUM EXTENT integer [K|M]]

[BLOCKSIZE integer [K]] [LOGGING | NOLOGGING]

[DEFAULT storage_clause] [ONLINE | OFFLINE ]

[PERMANENT | TEMPORARY]

[extent_management_clause] [ segment_management_clause]

Where :

TABLESPACE NAME: This is the name of the tablespace to be created.

DATAFILE: This specifies the data file Or Data files name that uses to make up tablespace.

MINIMUM EXTENT: This ensures that every used extent size in the tablespace is a multiple of the integer. Use K or M to specify this size in Kilobytes or Megabytes.

BLOCKSIZE: Specifies a nonstandard block size for the tablespace. In order to specify this clause, you must have the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE parameter set and the integer you specify in this clause must correspond with the setting of one DB_nK_CACHE_SIZE parameter setting.

LOGGING: This specifies that, by default, all tables, indexes, and partition within the tablespace have all changes written to online redo log files. LOGGING is the by default.

NOLOGGING: This specifies that, by default, all tables, indexes, and partition within the tablespace do not have all changes written to the online redo log file. NOLOGGING affects only some DML and DDL commands, For Example, Direct loads.

DEFAULT: DEFAULT clause specifies the default storage parameter for all objects created in the tablespace creation.

OFFLINE: This clause makes the tablespace unavailable immediately after creation.

PERMANENT: This specifies that the tablespace can be used to hold permanent objects.

TEMPORARY: This specifies that the tablespace can be used to hold temporary objects. For example, segments used by implicit sorts caused by an ORDER BY clause. It cannot specify EXTENT MANAGEMENT LOCAL or the BLOCKSIZE clause.

extent_management_clause: This clause specifies how the extents of the tablespace are managed.

segment_management_caluse: This is relevant only for permanent, Locally managed tablespaces. It lets you specify whether Oracle should track the used and free space in the segments in the tablespace using free lists or bitmaps.

Review below syntax:

datafile_clause:= file_name [SIZE integer (K|M)] [REUSE] [autoextend_caluse] [NEXT]

Filename : this is the name of a data file in the tablespace.

SIZE : This specifies the size if the file. Use K or M to specify the size in Kilobytes or Megabytes

REUSE: This allows the Oracle server to reuse and existing file.

autoextend_clause : This Clause enables or disables automatic extention of the data file.

NEXT: This Specifies the size in bytes of the next increment of disk space to be allocated automatically when more extents are required.

Where:

MAXSIZE : this specifies the maximum disk space allowed for automatic extention of the datafile.

UNLIMITED : This specifies the disk space that can be allocated to the data file, or that the tempfile is not limited.

Thank you !!

Archived Redo log File

2020-09-23T20:27:00.006+05:30

Archived Redo Log File :

Filled online redo log files can be archived.
There are two advantages in running the database in ARCIHIVELOG mode and archiving online redo log files :

- Recovery: A Database backup togather with online and archived redo log files can guarantee recovery of all committed transaction.

- Backup: This can be performed while the database is open

By Default, The database is created in NOARCHIVELOG mode.

One of the important decisions that a database administrator has to make is whether the database is configured to operate in ARCHIVELOG mode or in NOARCHIVELOG mode.

NOARCHIVELOG MODE :

In noarchivelog mode, the online redo log files are overwritten each time an online redo log file is filled, and log switches occur. LGWR does not overwrite an online redo log file group until the checkpoint for that group is completed.

ARCHIVELOG MODE :

If the database is configured to run in ARCHIVELOG mode, inactive groups of filled online redo log files must be archived. Because all changes made to the database are recorded in the online redo log files, The Database administrator should take the physical backup of the archived online redo log file. Backup of the archived redo log file is used to recover the database without losing any committed data when Database recovery required.

There are two ways in which online redo log files can be archived :

Manually
Automatically ( recommended method )

The LOG_ARCHIVE_START initialization parameter indicates whether archiving should be automatic or manual when the instance starts up.

TRUE: TRUE indicates that archiving is automatic. ARCn initiates the archiving of the filled log group at every log switch.
FALSE: The default value is FALSE, and it indicates that the DBA archives the filled online redo log files manually. The DBA must manually execute a command each time you want to archive an online redo log file. All or specific online redo log files can be archived manually.

Please review some important facts about Archive redo log files :

Archiving accomplished automatically by ARCn
Archiving accomplished manually through SQL statements
When the online redo log file successfully archived below event occurs :

- An entry in the control file is made.

- Records: archive log name, log sequemce number, and high and low system change number (SCN)

Filled online redo log files cannot be reused until:

- A checkpoint has taken place

- File has been archived by ARCn

Can be multiplexed
Maintained by the DBA

Information about archive logs can be obtained from V$INSTANCE view.

SQL> SELECT archiver FROM V$INSTANCE;

ARCHIVE
-------
STARTED

The archiver parameter having a different status as below.

STOPPED: This status shows that the archiving has stopped working.

STARTED: This status shows that archiving is in progress and archiving is done by the Oracle Background Process ARCn.

FAILED: This status shows that the archiver failed to archive a log last time but will try again within 5 minutes.

Thank you !!

Managing Online Redo Log Files With OMF.

2020-09-23T00:38:00.001+05:30

Managing Online Redo Log File With OMF :

Define the DB_CREATE_ONLINE_LOG_DEST_n parameter :

DB_CREATE_ONLINE_LOG_DEST_1

DB_CREATE_ONLINE_LOG_DEST_2

A group can be added with no file specification.

ALTER DATABASE ADD LOGFILE;

Dropping a Group

ALTER DATABASE DROP LOGFILE GROUP 3;

Define the DB_CREATE_ONLINE_LOG_DEST_n parameter:

To create online redo log files to be managed by OMF, the DB_CREATE_ONLILNE_LOG_DEST_n parameter must be defined. The parameter must be set for each multiplexed copy identified by the n value.

In the example above two groups have been created with 2 members each. The names will be generated automatically (such as "ora_1_wo94n2xi.log") and displayed in the alertSID.log. The default size is 100 MB.

Adding Log File Group:

To create a new group of online redo log files, the DBA uses the ALTER DATABASE ADD LOGFILE command. The command has been modified so that the file specification is not necessary.

In the example above, To add a log file with 2 members, One in the location defined by the

DB_CREATE_ONLINE_LOG_DEST_1 and one in

DB _CREATE_ONLINE_LOG_DEST_2.

Unique filenames for the logfile members have generated automatically and displayed in the alertSID.log. the Default Size is 100MB.

Dropping a Group :

The above example drops the log file Group 3, and its operating system files associated with each OMF log file members in Group 3.

Archived Redo Log Files and OMF :

Archived redo log files cannot be OMF.

Obtaining Group and Member Information:

Information about a group and its members can be obtained by querying the following views.

V$LOG
V$LOGFILE

V$LOG view :

The following query returns information about the online redo logfile from the control file :

SQL> SELECT group#, sequence#, bytes, members, status FROM v$log;

GROUP# SEQUENCE# BYTES MEMBERS STATUS
-------- ---------- ---------- ---------- ----------------
1 403637 209715200 2 CURRENT
2 403634 209715200 2 INACTIVE
3 403635 209715200 2 INACTIVE
4 403636 209715200 2 INACTIVE
5 403632 209715200 2 INACTIVE
6 403633 209715200 2 INACTIVE

6 rows selected.

The following items are the most common values for the Status column :

UNUSED: This status indicates that the online redo log file group has never been written to. This is the state of the online redo log file group that was just added.
CURRENT: This status indicates the current online redo log file group. This implies that the online redo log file group is active.
ACTIVE: This status indicates that the online redo log file group is active but it is not the current online redo log file group. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
CLEARING: This status indicates that the log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE command. After the log is cleared, the status changes the UNUSED.
CLEARING_CURRENT: This status indicates that the current log file is being cleared of a closed thread. The log can stay in this status id there is some failure in the switch, such as an input/output (I/O) error writing the new log header.
INACTIVE: This status indicates that the online redo log file group is no longer needed for instance recovery. It may or may not be archived.

V$LOGFILE View :

To obtain the names of all the members of a group, query the V$LOGFILE view.

SQL> SELECT * FROM V$LOGFILE;

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE

------- ------- ------- ----------------- ---------------
2 ONLINE /u01/oradata/ORCL/log02a.rdo NO
2 ONLINE /u01/oradata/ORCL/log02b.rdo NO
1 ONLINE /u01/oradata/ORCL/log01a.rdo NO
1 ONLINE /u01/oradata/ORCL/log01b.rdo NO

4 rows selected.

The value of the STATUS column could be one of the following.

INVALID: This status indicates that the file is inaccessible.
STALE: This status indicates that the contents of the file are incomplete.
DELETED: This status indicates that the file is no longer used.
Blank indicates that the file is in use.

Thank You !!

Adding,Dropping, Clearing and Relocating or Renaming Online Redo Log Files and Redo log File Members

2020-09-14T17:32:00.006+05:30

Adding Online Redo Log File Groups:

Generally, we all use the below command to add Redo Log File Groups. Suppose If you have 2 redo log groups in your Environment and Due to the heavy transaction, Database performance is affecting, So in this scenario, you can add an online redo log file groups to run Your database smoothly and Ensure your Database availability and reduce the burden of the oracle process.

Command Used for Online Redo log file Group Creation :

SQL> ALTER DATABSE ADD LOGFILE GROUP 3

('$HOME/ORADATA/u01/log3a.log',

'$HOME/OARADATA/u02/log3b.log') SIZE 1M;

In some cases, you might need to create additional log file groups. For example, Adding groups can solve availability problems. To create a new group of online redo log files, Use the above SQL command and use the path as per your requirement.

Redo Log File Group creation syntax is as below:

SQL> ALTER DATABASE ADD LOGFILE [GROUP integer] filespec

[, [GROUP integer] Filespec ] ... SIZE ];

You Specify the Name and Location of the members with the file specification. The Value of the GROUP parameter can be selected for each online redo log filegroup. If you omit this parameter, the Oracle Server Generates its Value Automatically.

Adding Online Redo Log File Members :

We use below SQL command to add Online Redo Log File Group Members. You can add new members to existing Online Redo Log File Groups using the following Command.

SQL> ALTER DATABASE ADD LOGFILE MEMBER

'$HOME/ORADATA/u04/log1c.log' To GROUP 1,

'$HOME/ORADATA/u04/log2c.log ' To GROUP 2,

'$HOME/ORADATA/u04/log3c.log' To GROUP 3 ;

Use the Fully specified name of the log file members, Otherwise, the files are created in a default directory of the database server.

If the file already exists, it must have the same size, and you must specify the REUSE option. You can Identify the target group either by specifying one or more members of the group or by specifying the group numbers.

Dropping Online Redo Log File Groups :

Generally, We use the Below command to Drop an online redo log file group :

SQL> ALTER DATABASE DROP LOGILE GROUP 3;

To increase or decrease the size of the online redo log file groups, Add a new online redo log file group (With the new size) and then drop the old one.

An entire online redo log file group can be dropped with the following ALTER DATABASE DROP LOGFILE command. Syntax of Command is as below:

ALTER DATABSE DROP LOGFILE {GROUP interger | ('filename',filename')};

Restrictions

An instance requires at least two groups of online redo log files.
An active or Current group Can not be dropped.
When an online redo log file Group is dropped, the operating system files are not deleted.

Dropping Online Redo Log File Members :

We use the below command to drop an online redo log file members:

SQL>ALTER DATABASE DROP LOGFILE MEMBER

'$HOME/ORADATA/u04/log3c.log';

You may want to drop an online redo log file member because it is invalid. Use the following ALTER DATABASE DROP LOGFILE MEMBER command if you want to drip one or more specific online redo log file members.

Command Syntax :

ALTER DATABASE DROP LOGFILE MEMBER 'filename' ;

Restrictions:

If the member you want to drop is the last valid member of the group, you can not drop that member.
If the group is current, you must force a log switch before you can drop the member.
If the Database is running in ARCHIVELOG mode and the log file group to which the member belongs is not archived, then the member can not be dropped.
When an online redo log file member is dropped, the operating system file is not deleted if you not using the OMF feature.

Relocating or Renaming the Online Redo Log Files :

Relocate or Rename online redo log files by one of the two following ways :

ALTER DATABASE RENAME FILE command

- Shut Down the Database.

- Copy the online redo log files to the new Location.

- Place the database in MOUNT mode.

- Execute the Command .

SQL> ALTER DATABSE RENAME FILE

'$HOME/ORADATA/u01/log2a.log' To '$HOME/ORADATA/u02/log1c.log' ;

- Open Databae for normal operation.

Add new members and drop old members.

The Location of Online redo log files can be changed by renaming the online redo log files. Before renaming the online redo log files, Ensure that the new online redo log file exists. The Oracle server changes only the pointers in the control files but does not physically rename or create any operating system files.

Steps for Relocating or Renaming Online Redo Log Members:

1. Shut down the database

SQL> SHUTDOWN IMMEDIATE

2. Copy the online redo log files to the new location.

3. Startup the database in mount mode, but do not open it.

SQL> CONNECT / AS SYSDBA

SQL> STARTUP MOUNT

4. Rename the online redo log members using ALTER DATABASE RENAME FILE command.

SQL> ALTER DATABASE RENAME FILE

'$HOME/ORADATA/u01/log2a.log' To '$HOME/ORADATA/u02/log1c.log' ;

5. Open the Database for normal operation.

SQL> ALTER DATABASE OPEN;

Clearing Online Redo Log Files:

For Clearing the online redo log files by using the following ways :

ALTER DATABASE CLEAR LOGFILE command can be used to reinitialize an online redo log file.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2 ;

Use the UNARCHIVED keyword to avoid archiving the corrupted online redo log file.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2 ;

An online redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this Situation, the ALTER DATABASE CLEAR LOGFILE command can be used to reinitialize the online redo log file without shutting down the database.

This command can overcome two situations where dropping online redo log files is not possible :

If there are only two log groups.
The corrupt online redo log file belongs to the current group

If the corrupt online redo log file has not been archived, use the UNARCHIVED keyword in the command to clear the corrupted online redo log files and avoids archiving them. The cleared online redo log files are available for use even though they were not archived.

If you clear a log file that is needed for recovery of a backup, then you can no longer recover from that backup. Oracle Writes a message in the alert log describing the backups from which you can not recover. So we should have to perform a fresh backup of the Database if this kind of situation comes.

Online Redo Log File Configuration:

To determine the appropriate number of online redo log files for a database instance, you must test different configurations.

In some cases, a database instance may require only two groups. In another situation, a database instance may require additional groups to guarantee that the groups are always available to LGWR.

For example: if the messages in the LGWR trace file or in the alert log file indicate the LGWR frequently must wait for a group because a checkpoint has not completed or a group has not been archived, you should add groups.

Location of Online Redo Log File:

When you multiplex the online redo log files, Place members of a group on different disks. By doing this, even if one member is not available but other members are available, the instance does not shut down.

Separate archive log files and online redo log files on different disks to reduce contention between the ARCn and LGWR background processes.

Datafiles and online redo log files should be placed on different disks to reduce LGWR and DBWn contention and reduce the risk of losing both data files and online redo log files in the event of media failure.

Sizing Online Redo Log Files :

The minimum size of an online redo log file is 50 KB, and the maximum size is specific to the operating system. Members of the different groups can have different size, however, there is no benefit in having different-sized groups.

Different Sized groups should be required as a temporary result only if you want to change the size of the members of the online redo log file groups. In this case, you should create a new online redo log file groups with different sizes, and then remove the old groups

The following situations might influence the configuration of the online redo log files:

Number of log switches and checkpoints
Number and amount of redo entries
Amount of space on the storage medium. for example on tape if archiving is enabled.

Thank you !!

Online Redo Log Files

2020-09-08T21:40:00.007+05:30

Online Redo Log Files:

Online Redo log files have the following characteristics :

Record all changes made to data
Provide a Recovery mechanism
Can be organized into groups
At least two groups required

Online redo log files provide the way to redo transactions in the event of a database failure. Every transaction is written synchronously to the Redo Log Buffer, then gets flushed to the online redo log files in order to provide a recovery mechanism in case of media failure. (With Exceptions such as direct load inserts in objects with the NOLOGGING clause enabled.)

This includes transactions that have not yet been committed, Undo segment information, and schema and object management statements. Online redo log files are used in a situation such as an instance failure to recover committed data that has not been written to the data files. The online redo log files are used only for recovery.

Structure of the Online Redo Log Files:

Online Redo Log File Structure

The DBA can set up the oracle database to maintain copies of online redo log files to avoid losing database information due to a single point of failure.

Online Redo Log File Groups :

A set of identical copies of online redo log files is called an online redo log file group.
The LGWR background process concurrently writes the same information to all online redo log files in a group.
The Oracle Server needs a minimum of two online redo log file groups for the normal operation of a database.

Online Redo Log File Members :

Each online redo log file in a group is called a member.
Each member in a group has identical sequence numbers and are of the same size. The log sequence number is assigned each time that the Oracle Server writes to a log group to uniquely identify each online redo log file.
The current log sequence number is stored in the control file and in the header of all the data files.

Creating Initial Online Redo Log Files :

The Initial set of online redo log file groups and members are created during the database creation.

The following parameters limit the number of the online redo log file :

The MAXLOGFILES parameter in the CREATE DATABASE command specifies the absolute maximum of online redo log file groups.
The maximum and the default value for MAXLOGFILES are dependent on your operating system.
The MAXLOGMEMBERS parameter used in the CREATE DATABASE command determines the maximum number of members per group. The Maximum and default value for MAXLOGMEMBERS is dependent on your operating system.

How Online Redo Log Files Work:

Online Redo Log Files are used in a cyclic fashion.
When an online redo log file is full, LGWR (background Process ) will move to the next log group.

- Called a log Switch

- Checkpoint operations also occure

- Information Written to the control file

The Oracle Server sequentially records all changes made to the database in the Redo Log Buffer. The redo entries are written from the redo log buffer to the current online redo log file group by the LGWR process. LGWR writes under the following situations.

When a transaction commits
When the redo log buffers one-third full.
When there is more than a megabyte of changed records in the Redo Log Buffer.
Before the DBWn writes modified blocks in the database buffer cache to the Data Files.

Online redo log files are used in a cyclic fashion. Each Online redo log file group is identified by a log sequence number that is overwritten each time the log is reused.

Log Switches :

LGWR writes to the online redo log files sequentially. When the current online redo log file group is filled, LGWR begins writing to the next group. This is called a log switch.

When the last available online redo log file is filled. LGWR returns to the first online redo log file group and starts writing again.

Checkpoints :

During the checkpoint below activity happens and can say characteristics of Checkpoint :

DBWn writes a number of dirty database buffers, which are covered by the log that is being checkpointed, to the data file.
The checkpoint backup ground process CKPT updates the control file to reflect that is it has completed a checkpoint successfully. If the checkpoint is caused by a log switch, CKPT also updates the headers of the data file.

Checkpoints can occur for all data files in the database or only for the specific data file.

In the following situation, checkpoints occur, for Example :

At every log switch
When an instance has been shut down with the normal, transactional, or Immediate option.
When forced by setting the FAST_START_MTTR_TRAGET initialization parameter.
When manually requested by the database administrator
When the ALTER TABLESPACE [OFFLINE NORMAL | READ ONLY | BEGIN BACKUP] command causes checkpoint on specific data files.

Information about each checkpoint is recorded in the alert_SID.log file if the LOG_CHECKPOINTS_TO_ALERT initialization parameter is set to TRUE. The default value of FALSE for this parameter does not log checkpoint.

Forcing Log Switches and Checkpoints :

Forcing a log Switch by using Command :

SQL> ALTER SYSTEM SWITCH LOGFILE;

Checkpoint Can be Forced by :

- Setting FAST_START_MTTR_TARGET parameter.

FAST_START_MTTR_TARGET = 600;

ALTER SYSTEM CHECKPOINT command.

SQL> ALTER SYSTEM CHECKPOINT;i

Log Switches and checkpoints are automatically performed at certain points in the operation of the database as identified previously. However, a DBA can force a log switch or a checkpoint to occur.

Forcing Checkpoint :

FAST_START_MTTR_TARGET Parameter replaces the deprecated parameters:

FAST_START_IO_TARGET
LOG_CHECKPOINT_TIMEOUT

These deprecated parameters must not be uses if the parameter FAST_START_MTTR_TARGET is used.

In the above example, the FAST_START_MTTR_TARGET parameter has been set 600 seconds, so that instance recovery should not take more than 600 seconds. The Database will adjust the other parameters to this goal.

Thank you !!

Control File, Multiplexing of Control File

2020-09-04T00:26:00.003+05:30

Hey Folks,

We shall deep dive to know more about the Control file and its function and Why the control file is a very important component and what is the use of it. Here we shall know about the Control File Multiplexing method and steps.

Control File :

Control file Characteristics is as below :

A small binary file
Defines the current state of the physical database
Maintains the integrity of the database
Required :

- At MOUNT state during database startup

- To operate the Database

Linked to a single database
Loss May require recovery
Sized initially by CREATE DATABASE

The control file is a small binary file, it is necessary for the database to start and operate successfully. Each control file is associated with only one Oracle Database. Before a database is opened, the control file is read to determine whether the database is in a valid state to use.

A control file is updated continuously by Oracle Server during Database use, So it must be available for writing whenever the database is open. The information in the control file can be modified only by the Oracle Server, no database administrator or end-user can edit the control file.

If for some reason the control file is not accessible, the database does not function properly. If all copies of a Database's control file are lost, the database must be recovered before it can be opened.

Control File Contents :

A control file contains the Fallowing entries :

Database name and identifier
Time stamp of the Database Creation
Tablespace names
Names and locations of data files and online redo log files
Current Online redo log file sequence number
Checkpoint information
Begin and End of UNDO segments
Redo log Archive information
Backup Information

The information in the Control file includes the following OR when the control file entries are updated:

- The database name is taken from either the name specified by the initialization parameter DB_NAME or the name used in the CREATE DATABASE statement.

- A database identifier is recorded when the database is created.

- Timestamp of database creation is also recorded at the time of database creation.

- Name and location of Associated data files and online redo log files are updated when a data file or online redo log file is added to, renamed in, or dropped from the database.

- Tablespace information is updated as tablespaces are added or dropped.

- Online redo log file history is recorded during log switches.

- Location and status of archive logs are recorded when archiving occurs.

- Location and status for backups are recorded by the Recovery Manager (RMAN) utility.

- Current log sequence number is recorded when log switches occur.

- Checkpoint Information is recorded as checkpoints are made.

The Control file consists of two types of sections :

Reusable
Not reusable

Reusable sections store RMAN information, Such as backup data file names and backup online redo log file names. They are used in a circular manner and can be reused by only RMAN.

Multiplexing the Control File:

To Safeguard against a single point failure of the control file, It is strongly recommended that the control file be Multiplexed, Storing each copy in a different physical disks. If a control file is lost, a multiplexed copy of the control file can be used to restart the instance without database recovery.

Control files can be multiplexed up to Eight times by :

Creating multiple control files when the database is created by including the control file names and full path in the initialization Parameter file :

CONTROL_FILES = $HOME/ORADATA/u01/ctrl01.ctl,

$HOME/ORADATA/u02/ctrl02.ctl

Adding a control file after the database is created.

Backing Up the Control File :

Because the control file records the physical structure of the database, you should immediately make a backup of your control file after making changes to the physical structure of the Database.

Multiplexing the Control File When Using SPFILE :

For Multiplexing the control file using SPFILE, Below are the steps have to follow :

Alter the SPFILE :

Alter the SPFILE using the ALTER SYSTEM SET command, alter the SPFILE to include a list of all control files to be used: Main Control file and multiplexed copies. Below is the Command:

SQL> ALTER SYSTEM SET control files =

'$HOME/ORADATA/u01/ctrl01.ctl','$HOME/ORADATA/u02/ctrl02.ctl' SCOPE=SPFILE;

2. Shut Down the Database :

Shutdown the database in order to create the additional control files on the operating system.

SQL> shutdown immediate

3. Create Additional control files :

Create additional control files using the operating system copy command, Create the additional control files as required and verify that the files have been created in the appropriate directories.

cp $HOME/ORADATA/u01/ctrl01.ctl $HOME/ORADATA/u02/ctrl02.ctl

4. Start The Database:

When the database is started the SPFILE will be read and the Oracle Server will maintain all the control files listed in the CONTROL_FILES parameter.

SQL> startup

Multiplexing the Control File When Using PFILE:

Shut Down the Database:

Shut Down the Database in order to create additional control files on the operating system.

SQL> shutdown immediate

2. Create Additional control files:

Create additional control file using the operating system copy command, Create the additional control files as required and verify that the files have been created in the appropriate directories.

cp $HOME/ORADATA/u01/ctrl01.ctl $HOME/ORADATA/u02/ctrl02.ctl

3. Add control file names to PFILE :

Alter the PFILE to Add names of all the control files.

CONTROL_FILES = $HOME/ORADATA/u01/ctrl01.ctl,

$HOME/ORADATA/u02/ctrl02.ctl

4. Start the database :

When the database is started the PFILE will be read and the Oracle server will maintain all the control files listed in the CONTROL_FILES parameter.

SQL> startup

Managing Control File with OMF :

OMF (Oracle Managed File ) is created if the CONTROL_FILES parameter is not specified.
Locations are defined by DB_CREATE_ONLINE_LOG_DEST_n.
Names are uniquely generated and displayed in the alertSID.log.

Control files are created as OMF automatically during database creation time if the CONTROL_FILES parameter is not specified in the initialization parameter file.

If using an init.ora file, the CONTROL_FILES parameter must be set to the OMF generated Names, Which can be found by selecting from V$CONTROLFILE or from the alertSID.log. If an SPFILE is used, the CONTROL_FILES parameter is automatically set and saved when the database is created.

The locations of the control files are determined by the DB_CREATE_ONLINE_LOG_DEST_n Parameter. If this parameter is not set the control files location will be defined by the DB_CREATE_FILE_DEST Parameter. If neither of these parameter sets, then the control file will not be of OMF. If the control files are not of OMF then the CONTROL_FILES parameter must be set in the initialization parameter file or An error will be received.

The control names are uniquely generated (ora_cmr7t30p.ctl) and displayed in the alertSID.log when the files are created

Obtaining Control File Information :

Information About Control File Status and locations can be retrieved by querying the following views.

V$CONTROLFILE: Lists the name and status of all the control files associated with the instance.
V$PARAMETER: Lists status and location of all parameters.
V$CONTROLFILE_RECORD_SECTION: Provides information about the control file record sections
SHOW PARAMETER CONTROL_FILES: Lists the Name, Status, and Location of the control files.

See below a few Examples to Obtaining Control file Information :

To Obtain the location and names of the control files, Query the V$CONTROLFILE view,

SQL> SELECT NAME FROM V$CONTROLFILE;

NAME

----------------------------------------

/u02/oraprod/proddata/cntrl01.dbf

/u02/oraprod/proddata/cntrl02.dbf

/u02/oraprod/proddata/cntrl03.dbf

You can also query the V$PARAMETER view.

SQL> SELECT name, value from V$PARAMETER where name =

'control_files';

NAME VALUE

--------------- --------------------------------------

control_files /u02/oraprod/proddata/cntrl01.dbf,

/u02/oraprod/proddata/cntrl02.dbf,

/u02/oraprod/proddata/cntrl03.dbf

To Obtain Information about the different sections of the control files, query the V$CONTROLFILE_RECORD_SECTION view.

SQL> SELECT type, record_size, records_total, records_used FROM v$controlfile_record_section WHERE TYPE='DATAFILE';

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED

----------------- ----------- ------------- ------------

DATAFILE 520 512 62

The RCORDS_TOTAL column specifies the number of records allocated for a special section.

To SHOW PARAMETER command can also be used to find the location of the control files.

SQL> SHOW PARAMETER control_file;

NAME TYPE VALUE

-------------------------- ----------- ------------------------

control_file_record_keep_time integer 7

control_files string /u02/oraprod/proddata/cntrl01.dbf,

/u02/oraprod/proddata/cntrl02.dbf, /u02/oraprod/proddata/cntrl03.dbf

Information In Several dynamic performance views is obtained from the control files. See below List is for examples :

V$BACKUP
V$DATAFILE
V$TEMPFILE
V$TABLESPACE
V$ARCHIVE
V$LOG
V$LOGFILE
V$LOGHIST
V$ARCHIVED_LOG
V$DATABASE

Thank you !!

Data Dictionary, Built-in Database Objects

2020-08-25T07:01:00.004+05:30

Hey Folks,

We will see here some interesting facts like If we create/Install a Database and then what all database objects are created by default. So please go through this post

Built-In Database objects :

When we Create/Install a Database, In Addition to creating the database files, Several other structures are created. Below are the objects that are created with the database :

Data Dictionary
Performance Tables
PL/SQL Packages
Database event triggers

Data Dictionary: Data Dictionary contains descriptions of the objects in the Database.

Dynamic performance tables: Dynamic performance tables Contains information used by the database administrator (DBA) to Monitor and tune the database and instance.

PL/SQL packages: PL/SQL packages program units adding the functionality of the database. These packages are created when the catproc.sql script is run after the CREATE DATABASE command.

Database Event Triggers: Triggers are procedures that execute implicitly whenever a table or view is modified, or when some user actions or database system actions occur.

Data Dictionary: Characteristics of Data Dictionary :

Central to every Oracle Database
Describes the database and its objects
Contains read-only tables and views
Stored in the SYSTEM tablespace
Owned by the user SYS
Maintained by the Oracle Server
Accessed with SELECT statement

One of the most important parts of an Oracle database is its data dictionary, which is a read-only set of tables and views that provides information about its associated database.

The Data dictionary is updated by the Oracle Server whenever a data definition language (DDL) command is executed. Also, Data manipulation language (DML) commands, Such as one that the cause of the table to extend, Can update the data dictionary.

The Data dictionary is not only the central part of every Oracle database, but it is an important source of information for all users, From end-user to Application Designers and Database Administrator.

SQL statements are used to access the data dictionary. You can issue queries only against the tables and views of the data dictionary.

Base Tables and Data Dictionary Views :

The Data Dictionary contains two Parts :

Base Tables

Stores description of the database
Created with CREATE DATABASE

Data Dictionary Views

Used to simplify the base table information
Accessed through public synonyms
Created with the catalog.sql script

The Data Dictionary contains descriptions of the objects in the database. It includes two types of objects :

Base Tables :

Base tables are underlying tables, Which store information about the database. The base tables are the first objects created in any Oracle Database. They are automatically created when the Oracle server runs the sql.bsq script at the time the Database is created. Only the Oracle Server should write to these tables. Users rarely access them directly, because most of the data is stored in cryptic format. Never use DML commands to Update the base table directly, except for the AUD$ tables.

An example of a base table is the IND$ table, which contains information about the indexes in the Database.

Data Dictionary Views :

The Data dictionary views are base table summaries, which provide for a more useful display of base table information.

For Example, In the data dictionary views, object names are used instead of only objects numbers.

The Data dictionary views are created using the catalog.sql script which is run after the CREATE DATABASE command.

Creating Data Dictionary Views :

Below Scripts are responsible for Creating Data Dictionary Views :

catalog.sql: Creates commonly used data dictionary views and synonyms

catproc.sql: Runs Scripts required for server-side PL/SQL

The base tables of the data dictionary are automatically created when the database is created. When the database is created using the Oracle Universal Installer (OUI), the scripts to create a data dictionary and dynamic performance views and scripts for your Oracle Server options are run automatically.

You must run these scripts manually when creating a new database manually. In addition, you may need to run them again when upgrading to a new release of the Oracle Server. These Scripts must be run as the user SYS with the SYSDBA privilege.

The scripts are located in the following Directories :

UNIX : $ORACLE_HOME/rdbms/admin

NT : %ORACLLE_HOME%\rdbms\admin

Data Dictionary Contents

The data dictionary provides information about:

Logical and physical database structures
Definitions and space allocations of objects
Integrity constraints
Users
Roles
Privileges
Auditing

The Data Dictionary contains :

The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
How much space has been allocated for, and is currently used by, the schema objects
Default values for columns
Integrity constraint information
The names of the Oracle Users
Privileges and roles each user has been granted
Auditing Information, Such as who has accessed or updated various schema objects

How the Data Dictionary is Used:

Primary Uses :

Oracle Server uses it to find information about

Users
Schema Objects
Storage Structures

Oracle Server modifies it when as DDL statement is executed.
Users and DBAs use it as a read-only reference for information about the database.

How the Oracle server Uses the Data Dictionary

Data is the base tables of the data dictionary that are necessary for the Oracle Server to function. Therefore, Only the Oracle server should write or change Data Dictionary information. During Database operation, the Oracle Server read the data dictionary to ascertain that schema objects exist and that users have proper access to them. The Oracle Server also updates the Data Dictionary continuously to reflect changes in Database Structures.

How Users and DBAs Use the Data Dictionary :

The views of the Data Dictionary serve as a reference for all database users. Some Views are accessible to all Oracle Users. Others are intended for Database Administrator Only.

Data Dictionary View Categories :

Three Sets of Static views
Distinguished by their Scope

- DBA: What is in all the Schema

- ALL: What the users can access

- USER: What is in the User's Schema

Data Dictionary Views with DBA Prefixes :

Views with the DBA prefix show a global view of the entire Database. They are meant to be queried only by the database administrators. Any User granted the system privilege SELECT ANY TABLE can query the DBA prefixed views of the data dictionary.

To Query on all objects in the database, the DBA cab issue the statement like :

SQL> SELECT owner, object_name, object_type from dba_objects;

Data Dictionary Views with ALL Prefixes :

Views with the ALL prefix refer to the user's overall perspective of the database. These views return information about schema objects to which the user has accessed by way of public or explicit grants of privileges and role, in addition to schema objects that the user owns.

For example, The Following query returns information about all the objects to which a user has access :

SQL> SELECT owner, object_name, object_type FROM all_objects;

Data Dictionary Views with USERS Prefixes :

The views most likely to be of interest to typical database users are those with USER prefix. These Views :

Refer to the user's environment in the database
Generally, refer to the objects owned by the current users
Have column identical to the other views, Except that the column OWNER is implied to be a current user
Return a subset of the information in the ALL views
Can have short/compact public synonyms for convenience

For Example, The following query returns all the objects contained in the user's schema :

SQL> SELECT owner, object_name, object_type, from users_objects;

Data Dictionary Views :

Data Dictionary views are Static views that answer to a question such as :

Was the object ever created?
What is the object a part of?
Who owns the object?
What Privileges do users have?
What restrictions are on the objects?

Data Dictionary Examples:

General Overview: DICTIONARY, DICT_COLUMNS
Schema Objects: DBA_TABLES, DBA_INDEXES, DBS_TAB_COLUMNS, DBA_CONSTRAINTS
Space Allocation: DBA_SEGMENTS, DBA_EXTENTS
Database Structures: DBA_TABLESPACES, DBA_DATA_FILES

To get an overview of the data dictionary views, the DICTIONARY view or its synonym DICT can be quired. For example :

SQL> SELECT * FROM dictionary;

Include the where clause to make the responses narrow or short output as required. For example :

SQL> SELECT * FROM dictionary

WHERE table_ name LIKE 'DBA_SEG';

To get a list of columns within a view, use the DESCRIBE keyword. For example :

SQL> DESCRIBE dbs_users;

To get an overview of the columns in the data dictionary views, you can query the DISCT_COLUMNS view.

To view the content of a data dictionary view, use the SELECT command.

SQL> SELECT * from dba_users;

Dynamic Performance Tables: Characteristics :

Virtual Tables
Record current database activity
Continually updated while the database is operational
Information is accessed from memory and control file
Used to monitor and tune the database
Owned by SYS user
Synonyms begin with V$
Listed in V$FIXED_TABLE

Throughout its operation, the Oracle Server records current database activity in a set of virtual tables called Dynamic Performance Views. These virtual tables exist in memory only when the database is running, to reflect real-time conditions of the database operation. They point to actual sources of information in memory and the control file.

These tables are not true tables, and are not to be accessed by most users; however, DBA can query, grant the SELECT privilege, and create views on the views. These views are sometimes called fixed views because they cannot be altered or removed by the DBA.

The Dynamic performance tables are owned by SYS, and their names all begin with V_$. Views are created on these tables, and then public synonyms are created for the views. The Synonyms names begin with V$. For example, the V$DATAFILE view contains information about the database's data files, and the V$FIXED_TABLES view contain information about all of the dynamic performance tables and views in the database.

The dynamic performance tables provide information such as :

Is the object online and available?
Is the object open?
What locks are being held?
Is the session active?

Dynamic Performance Examples: Below is the few important Dynamic performance views :

V$CONTROLFILE : Lists the names of the control files

V$DATABASE : Contains Database information from the Control file.

V$DATAFILE : Contains Data file information from the control file.

V$INSTANCE : Displays the state of the current instance.

V$PARAMETER : Lists Parameters and values currently in effect for the session.

V$SESSION : Lists session information for each current session.

V$SGA : Contains Summary information in the System Global Area (SGA).

V$SPPARAMETER : Lists the contents of the SPFILE.

V$TABLESPACE : Displays tablespace information from the control file.

V$THREAD : Contains thread information from the control file

V$VERSION : Version Numbers of the core library components in the Oracle Server.

The V$FIXED_TABLE view can also be queried to get a listing of the dynamic performance views:

SQL> SELECT * FROM V$FIXED_TABLE;

To get a list of columns within a view, use the DESCRIBE keyword:

SQL> DESCRIBE V$INSTANCE;

To view the contents of the view, use the SELECT command.

SQL> SELECT * FROM V$INSTANCE;

Administrative Script Naming Conventions :

The administrative scripts can be separated into categories by their filenames:

cat*.sql :

This script creates the data dictionary views. In addition to the catalog.sql and catproc.sql scripts. these are scripts that create information for Oracle Utilities. For example, The catadt.sql script creates data dictionary views for showing metadata information for types and other objects features in the Oracle RDBMS. The catnoadt.sql script drops these tables and views.

dbms.sql and prvt.plb :

These scripts creates objects for predefined Oracle packages that extend the Oracle Server functionality. These Programs simplify the task of administering the database. Most SQL scripts are run during the execution of the catproc.sql script. A few additional scripts must be executed by the Database Administrator. An example is a dbmspool.sql script, which enables you to the sizes of objects in the shared pool and marks them to be kept or removed in the SGA in order to reduce shared pool fragmentation.

utl*.sql

These scripts must be run when the database needs additional views and tables. For example, the utlxplan.sql script creates a table used to view the execution plan of a SQL statement.

Note: Most of these scripts must be executed under the user SYS with SYSDBA privileges. The DBA should examine the scripts to determine which user account must be used to run the scripts

Instance Monitoring Using Diagnostic Files, Alert Log File, Background Trace File, User Trace File, Enabling/Disabling User Tracing

2020-08-11T03:37:00.004+05:30

Hey Folks

We will see here some basic stuff about instance monitoring, that how to monitor the database using Diagnostic files, Alert log file, Background trace file User Trace File, Enabling/ Disabling user tracing.

Monitoring an Instance using Diagnostic files :

Diagnostic Files

Contains Information about significant events encountered
Used to resolve problems
Used to better managed the database on a day-to-day basis

Several type of diagnostic file exists:

alertSID.log file
Background trace files
User trace files.

Diagnostic files means to capture information about the database's activities. They are also useful tools for you when you are managing an instance. Several type of files exists. The type of diagnostic file created depends on the problem that occurred or the information that need to be communicated.

Alert Log File (alertSID.log) : Information for day-to-day operation of the database.
Background trace files : It Records important Information when background processes, such as SMON, PMON, DBWn, and others fail.
User Trace File : It is as text file and It records important information for fatal user errors or user forced to traced files.

Alert Log Files :

alertSID.log files :

Records the commands
Records results of major events
Used for day-to-day operational information
Used for diagnostic database errors

Each Entry has a time stamp associated with it
Must be managed by DBA
Location defined by BACKGROUND_DUMP_DEST

Each Oracle instance has an alert log file. If not already created , it is created during instance start up. The alert log File must be managed by DBA. It continues to grow while the database continues to work. The alert log file should be the first place you look when diagnosing day-to-day operations or errors. The alert log file also contains pointers to trace files for more details information.

The alert log file keeps a records of the fallowing information :

When the database was started or shut down
A list of all non-default initialization parameters
The startup of background processes
The thread being used by the instance
The log Sequence number LGWR writing to
Information regarding a log switch
Creation of tablespaces and undo segments
Alter statements that have been issued
Information regarding error message such as ORA-600 and other ORA- errors and extent errors.

The alertSID.log file location is defined by the BACKGROUND_DUMP_DEST initialization parameter.

Background Trace Files :

Background trace files

Log Errors detected by any background process
Are used to diagnose and troubleshoot errors.

Created when a background process encounters an error
Location defined by BACKGROUND_DUMP_DEST

Background trace files are used to log errors that have been encountered by a background process, such are SMON, PMON, DBWn and other background processes. This files exist only when an error requires writing to the trace files. You use these files to diagnose and troubleshoot problems. Initially when a background trace file is created it contains header information indicating the version number of the data server and the operating system.

Naming convention for user trace file : sid_processname_PID.trc ( orcl_lgwr_12345.trc).

Its Location is defined by the BACKGROUND_DUMP_DEST initialization parameter.

User Trace Files :

User trace files :

Produced By the user process
Can be generated by a server process
Contain Statistics for traced SQL statements
Contain user error messages

Created when a user encounters user session errors
Location is defined by USER_DUMP_DEST
Size defined by MAX_SUMP_FILE_SIZE

User trace file contains statistics for traced SQL statements, Which are useful for SQL tuning. In Addition, user trace files contains user error messages.

Naming convention for user trace file : sid_ora_PID.trc (orcl_ora_12345.trc)

Its Location is defined by the USER_DUMP_DEST initialization parameter.

Enabling or Disabling User Tracing :

Session Level :

Using the alter session command :

ALTER SESSION SET SQL_TRACE = TRUE

Executing DBMS Procedure :

dbms_system.SET_SQL_TRACE_IN_SESSION

Instance Level :

Setting the initialization parameter :

SQL_TRACE = TRUE

Setting SQL_TRACE = TRUE at the instance level will produce a significant volume of trace data. This option should be used with care.

Thank You !!

Oracle Database Shutdown Stages Or Mode

2020-08-10T20:05:00.006+05:30

Hey Folks,

We will see here some basic stuff about Oracle Database shutdown stages or modes.

Shutting Down the database :

Shut Down the database to make operating system offline backups of all physical structures and to have modified static initialization parameter take effect when restarted.

To shut down an instance, you must connect as SYSOPER or SYSDBA and use the fallowing command :

SHUTDOWN [NORMAL | TRANSNATIONAL | IMMEDIATE | ABORT ]

SHUTDOWN OPTIONS:

During : SHUTDOWN NORMAL or SHUTDOWN TRANSNATIONAL or SHUTDOWN IMMEDIATE

If you use these shutdown modes options, The Database state is consistent. During the startup Process not any instance recovery required. Consistent Database is also called Clean Database. Below things happens when you use these shutdown options.

Database buffer cache written to the data files
Uncommitted changes rolled back
Resources released
On the way to startup No any instance recovery required.

SHUTDOWN NORMAL :

Normal is the default shutdown mode. Normal database shut down proceeds with the fallowing conditions :

No new connections can be made.
The Oracle server waits for all users to disconnect before completing the shutdown.
Database and redo buffers are written to disk.
Background processes are terminated, and the SGA is removed from memory.
Oracle Closes and dismounts the database before shutting down the instance.
The next startup does not require an instance recovery.

SHUTDOWN TRANSNATIONAL :

A transnational shutdown prevents clients from losing work. A transnational database shutdown proceeds with the fallowing conditions :

No Client can start a new transaction on this particular instance
A client is disconnected when the client ends the transaction that is in progress.
When all transactions have finished, a shut down occurs immediately.
The next start up does not require an instance recovery.

SHUTDOWN IMMEDIATE :

Immediate database shutdown proceeds with the fallowing conditions :

Current SQL statements being processed by Oracle are not completed.
The Oracle server does not wait for the users, who are currently connected to the database, to disconnect.
Oracle rolls back active transactions and disconnects all connected users.
Oracle closes and dismounts the database before shutting down the instance.
The next start up does not require an instance recovery.

During : SHUTDOWN ABORT or Instance Failure or STARTUP FORCE :

If you use these option during shutdown, the database state is inconsistent and instance recovery is required. Inconsistent database is also called Dirty Database. Below things happens when you use these options :

On the way during shut down :

Modified buffers are not written to data files
Uncommitted changes are not rolled back

On the way to startup :

Online redo log files used to re-apply changes
Undo segments used to roll back uncommitted changes
Resources released

SHUTDOWN ABORT :

If the NORMAL and IMMEDIATE shut down option do not work, you can abort the current database instance, Aborting an instance proceeds with the fallowing conditions :

Current SQL statements being processed by the Oracle server are immediately terminated.
Oracle does not wait for users currently connected to the database to disconnect.
Database and Redo buffers are not written to disk.
Uncommitted transactions are not rolled back.
The instance is terminated without closing the files.
The database is not closed or dismounted.
The next start up requires instance recovery, Which occurs immediately.

Note : It is not advisable to backup a database that is in an inconsistent state.

Thank You !!

Oracle Database Startup Stages Or Modes

2020-08-09T11:50:00.006+05:30

Hey Folks,

Here we will see some basics stuff about Oracle Database Startup and shutdown stages like Open database is in restrict, read-only, open and what happens if we open database in these modes and why these are requires and what basics command we use to do that. Let's start.

ALTER DATABASE command :

Change the state of the database from nomount to Mount

ALTER DATABASE ORCL MOUNT;

Open the database as a read-only/open database

ALTER DATABASE ORCL OPEN READ ONLY;

To move the Database from the NOMOUNT to MOUNT stage or from the MOUNT to OPEN stage, Use the ALTER Database command.

ALTER DATABSE {MOUNT | OPEN };

To Prevent data from being modified by user transaction, the database can be opened in read-only mode.

To Start up an Instance, Use the Fallowing Command :

ALTER DATABSE OPEN { READ WRITE | READ ONLY };

Where :

READ WRITE : Opens the database in read/write mode, so that users can generate online redo log file.
READ ONLY : Restricts Users to read-only transactions, preventing them from generating online redo log file information.

Note : Read/Write is the default mode of operation.

Opening a Database in Restricted Mode :

Use the STARTUP command to restrict access to a database

STARTUP RESTRICT

Use the ALTER SYSTEM command to place an instance in restricted mode.

ALTER SYSTEM ENABLE RESTRICTED SESSION;

A restricted Session is useful, For example, When you perform structure maintenance or a database export and import. The database can be started in restricted mode so that it is available only to users with the RESTRICTION SESSION Privilege.

The Database can also be put in restricted mode ALTER SYSTEM SQL command :

ALTER SYSTEM [ { ENABLE/DISABLE } RESTRICTED SESSION ]

Where :

ENABLE RESTRICTED SESSION : Enables future logins only for users who have the RESTRICTED SESSION privilege

DISABLE RESTRICTED SESSION : Disables RESTRICTED SESSION so that the Users who do not have the privilege can log on also.

Terminate Session :

After placing an instance in restricted mode, you may want to kill all current user sessions before performing administrative tasks. this can be done by the fallowing :

ALTER SYSTEM KILL SESSION 'Integer1', Integer2';

Where :

Integer1 : Value of the SID column in the V$SESSION view

Integer2 : Value of the SERIAL# column in the V$SESSION view

Note : The session ID and Serial number are used to uniquely identify a session. This Guarantees that the ALTER SYSTEM KILL SESSION command is applied to be the correct session even if the user logs off and a new session uses the same session ID.

Effect of terminating Session :

The ALTER SYSTEM KILL SESSION command causes the background process PMON to perform the fallowing steps upon executions :

Roll back the user's current transactions
Release all currently help table or row locks
Free all resources currently reserved by the users session.

Opening a Database in Read-Only Mode :

Opening a database in read-only mode enables you to query like an open database But it Protects any potential online data content changes. While opening a database in read-only mode guarantees that datafile and redo log files are not written to, it does not restrict database recovery or operations that change the state of the database without generating redo. For example, you can take datafiles offline or bring them online since these operations do not affect data content.

Opening a database in Read-Only mode :

STARTUP MOUNT

ALTER DATABASE OPEN READ ONLY;

Can be used to

Execute queries
Execute disk sorts using locally managed tablespaces
Take data files offline and online, but not tablespaces
Perform recovery of offline data files and tablespaces

A database can be opened as read-only, as long as it is not already open in read/write mode. The feature is especially useful for a standby database to offload query processing from the production database.

If a query needs to use a temporary tablespace, for example , to do a sort operation, the current user must have a locally managed tablespace assigned as the default temporary tablespace, otherwise the query fails.

Read-Only mode does not restrict database recovery or operations that change the database state without generating redo data. For Example, in read-only mode :

Data files can be taken offline and online
Recovery of offline data files and tablespaces can be performed

Disk writes to other files, such as control files, operating system audit trail, trace files, and alert log files, can continue in Read-Only mode.

Thank You !!

Processing SQL Statements, SQL query Process Mechanism

2020-08-05T12:01:00.014+05:30

Hey Folks,

We will take a quick review here about SQL query Processing phases which is used by ORACLE internally when we execute any SQL statement in Oracle Database.

Processing SQL Statements :

Connect to an Instance using :

User Process
Server Process

The Oracle Server components that are used depend on the type of SQL statement :

Queries Return Rows
DML statement log changes
Commit ensures transaction recovery

Some Oracle Server components do not participate in SQL statement Processing.

Processing a query : Below is the different Phases comes in Picture for Query Execution :

Parse :

Search for identical statement
Check syntax, Object names, and Privileges
Lock Objects used during parse
Create and Store Executions Plan

Bind :

Obtain values for variables

Execute :

Process SQL statement

Fetch:

Return Rows to user process

Processing a DML Statement :

Parse : Same as the parse phase used in processing a Query.
Bind : Same as the Bind Phase used for Processing a query
Execute : Here In Execution phase some internal operations performed by an Oracle :

If the data and undo blocks are not already in the database buffer cache, the Server Process reads them from the data files into the database buffer cache.
The Server process places locks on the rows that are to be modified. The Undo blocks is used to store the before image of the data, So that the DML statements can be rolled back if necessary.
The Data blocks records the new values of the data.
The Server process records the before image to the undo block and updates the data block. Both of these changes are made in database buffer cache. Any changed block in the Database Buffer Cache are marked as Dirty Buffers. That is, Buffer that are not the same as the corresponding blocks on the disk.
The Processing of a DELETE or INSERT command uses similar steps. The before image for a DELETE contains the column value in deleted row, and the before image of an INSERT contains the row Location information.

Processing a DDL statement :

The Execution of DDL (Data Definition Language) statements differs from the execution of DML ( Data manipulation Language ) statements and queries , because the success of a DDL statement requires write access to the data dictionary. For these statements Parsing actually includes parsing, data dictionary lookup, and execution. Transaction management, Session management , and System management SQL statements are processed using the parse and execute stages. To re-execute them, simply perform another execute.

Thank You !!

Startup Command Behavior, Startup Event Sequence, What happens at every stage during startup, Oracle database Internal Processing of Sequence.

2020-08-04T17:48:00.004+05:30

Hey Folks,

We will review here some basic stuff that is Database Startup command behavior and What happens internally in Oracle when we fire the command startup. Let's Start :

Startup Command Behavior :

Order of Providences :

SpfileSID.ora
Default Spfile
InitSID.ora
Default Pfile

Specified PFILE can Override the precedence

STARTUP PFILE = $ORACLE_HOME/dbs/initORCL.ora

PFILE Can Indicate to use SPFILE

SPFILE = $ORACLE_HOME/dbs/initORCL.ora

Order Precedence Details :

When the STARTUP command is used, Then the spfileSID.ora file on the server side is used to startup the instance.
If the spfileSID.ora file is not found ,then the default SPFILE on the server side is used to start the instance
If the default SPFILE not found , then the initSID.ora file on the server side will be used to start the instance.

A Specified PFILE can override the use of the default SPFILE to start instance.

A PFILE can optionally contain a definition to indicate use of an SPFILE. This is the only way to start the instance with an SPFILE in a non default Location.

To start the Database with an SPFILE not in default location :

SPFILE = < Full Path and filename > must be place on PFILE.

Example : In PFILE we can mention as below (For start the instance using SPFILE available at non Default Location.

SPFILE = /U01/oradata/PFILE/spfileSID.ora

Parameters that Should be Specified in the Initialization Parameter File :

Parameter	Description
BACKGROUND_DUMP_DEST	Location Where Background Process Trace files are written (LGWR,DBWn, and so on ). Also the location for the alert log file.
COMPATIBLE	Version of the server with which this instance should be compatible
CONTROL_FILES	Name of the control files
DB_CACHE_SIZE	Specifies the size of the cache for standard block size buffers
DB_NAME	Database identifier of Eight 8 Characters or fewer. This is the only Parameter that is required when a new database is created
SHARED_POOL_SIZE	Size in the bytes of the Shared Pool
USER_DUMP_DEST	Location Where user debugging trace file are created on behalf of user process

Note : The Default Values depend on the version of the Oracle Server.

Commonly Modified Parameters :

Parameter	Description
IFILE	Name of another parameter file to be embedded within the current parameter file. Up to three levels of nesting is possible.
LOG_BUFFER	Number of bytes allocated to the Redo Log Buffer in the SGA
MAX_DUMP_FILE_SIZE	Maximum Size of trace files, Specified as number of Operating System Blocks
PROCESSES	Maximum Number of operation system processed that can connect Simultaneously
SQL_TRACE	Enables or Disables the SQL trace facility for every USER session.
TIMED_STATISTICS	Enables Or Disables timings in trace files and in monitor screens

Database Startup Stages and What Action event Occur at every Stage :

Starting Up a Database : When Starting the Database, You select the state in which it starts. The fallowing scenarios describe different stages of starting up an instance

NOMOUNT :

An Instance would be started in the NOMOUNT stage only during Database creation or the re-creation of control files.

Starting an instance includes the fallowing tasks :

Reading the initialization parameter file from " $ORACLE_HOME/dbs " in the fallowing order.

First spfileSID.ora
If not found then, spfile.ora
If not found then, initSID.ora

Specifying the PFILE parameter with STARTUP overrides the default behavior.

Allocating the SGA
Starting the background processes
Opening The alertSID.log and the trace files

The database must be named with DB_NAME parameter either in the initialization parameter file or in the STARTUP command.

Command Used : SQL > startup nomount ;

2. MOUNT :

Mounting the Database to perform Specific maintenance operations, You start an instance and mount the database but do not open the database.

For Example : The Database must be mounted but not open during the Fallowing Tasks :

Renaming Data files
Enabling and Disabling online redo log file archiving options
Performing full Database recovery

Mounting a database includes the fallowing tasks :

Associating a database with previously started instance
Locating and opening the control files specified in the parameter file
Reading the control file to obtain the names and status of the data files and online redo log files. However No checks performed to verify the existence of the data files and online redo log files at this time.

Command Used : SQL > startup mount or

SQL > startup nomount; and Then SQL > alter database mount;

3. OPEN :

Normal Database Operation means that an instance is started and the database is mounted and open. With normal database operation Any valid user can connect to the database and perform typical data access operations.

Opening the database includes the fallowing tasks :

Opening the online data files
Opening the online redo log files

If any of the data files or online redo log files are not present when you attempt to open the database, The Oracle server returns an error.

During The final stage, the Oracle server verifies that all the data files and online redo log files can be opened and checks the consistency of the database. If necessary, The SMON background Process initiates instance recovery.

Command : SQL> startup ;

STARTUP command :

To startup an instance and open the database by using below commands :

SQL> startup;

SQL> startup pfile = $ORACLE_HOME/dbs/initORCL.ora

To Start up an instance, use the fallowing option with STARTUP command when it requires:

STARTUP [FORCE] [ RESTRICT] [PFILE=file Path and Name]

[OPEN [Recover] [ Database]

|MOUNT

|NOMOUNT

Note : This Is the not a complete syntax

Where :

OPEN : Enables users to access the database.

MOUNT : Mounts the Database for certain DBA activities but does not provide user access to the database.

NOMOUNT : Creates the SGA and starts up the background processes but does not provide access to the database

PFILE-Parfile : Enables a non-default initialization parameter file to be used to configure the instance.

FORCE : Shuts Down the current Oracle Database instance (if it is running) with SHUTDOWN mode ABORT, before restarting it.

RESTRICT : Enables only users with RESTRICTED SESSION privilege to access the database.

RECOVER : Begins media recovery when the database starts.

Automating Database Startup :

ON Unix : Automating Database start up and shut down can be controlled by the entries in a special operating system file. For example, oratab in the /var/opt/oracle directory.

Trouble Shooting :

If any errors are occurs while issuing the STARTUP command then First Issue the SHUTDOWN command and then again try to issue STARTUP command.

Note : STARTUP and SHUTDOWN commands are SQL*plus commands, and not SQL commands.

Thank You !!

Initialization Parameter File (Oracle Database) PFILE, SPFILE

2020-07-29T03:36:00.013+05:30

Initialization Parameter File :

In order to start an instance and open the database, you must connect as SYSDBA and enter the STARTUP command. The Oracle Server will then read the Initialization Parameter file and prepare the instance according to the Initialization Parameter contained within the file.

Some Specific Important Facts about Initialization Parameter file :

Entries are specific to the Instance being Started
Two types of Parameters :

Explicit : Having an entry in the Initialization parameter file
Implicit : NO Entry within the file, But Assuming the Oracle Default Value.

Multiple Initialization parameter file can exists
Changes to entries in the file take effect based on the type of initialization parameter file used :

Static Parameter File, PFILE
Persistent Server Parameter File, SPFILE

To Start an Instance the Oracle server reads the Initialization Parameter File. Two Types of Initialization Parameter files exists :

Staitic Parameter File, PFILE, Commonly referred to as initSID.ora.
Persistent Server Parameter File , SPFILE , commonly Referred to as spfileSID.ora

Initialization Parameter contents :

A list of Instance Parameters
The name of the Database the instance is associated with
Allocation for memory structures of the System Global Area (SGA)
What to do with Filled online redo Log Files.
The names and location of control files
Information About UNDO segments.

PFILE : initSID.ora

The PFILE is a text file that can be Maintained using a standard operating system editor. The PFILE is read only during instance startup. If the file is modified , the instance must be shut down and restarted in order to make the new parameter values effective.

Characteristics of PFILE :

Text File
Modified With an operating system editor
Modification made manually
Changes take Effect on the next startup
Only opened during Instance Start UP
Default Location is $ORACLE_HOME/dbs

By default, The PFILE located in the $ORACLE_HOME/dbs directory and name initSID.ora.

Creating a PFILE :

How to Create PFILE ?

PFILE created from a sample init.ora file

Sample Installed by the Oracle Universal Installer
Copy Sample using operating system copy command
Uniquely Identified by Database SID

cp init.ora $ORACLE_HOME/dbs/initdba01.ora

Modify the initSID.ora

Edit the Parameters
Specific to Database needs

A sample init.ora file is created by the Universal Installer during installation. This Sample init.ora file can be used to create an instance specific initSID.ora. A text editor can be used to modify the parameter within the initSID.ora file.

PFILE Example : Here we will review the example of init.ora file that how it looks like and what parameter are added by default. Below is the my Test system default init.ora file.

cat init.ora

# Example INIT.ORA file
.
db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='<ORACLE_BASE>'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'

Now Let's see if Need to add a parameter manually in PFILE then We need to review below set of rules that we have to fallow it.

Specify the values in the fallowing format like Keyword = value .
The server has a default value for each parameter. This Value may be operating system Dependent, depending on the parameter.
Parameter can be specified in any order, although there are some exceptions
Comment line begin with the # Symbol.
Enclose parameters in double quotation marks to include character literals.
Additional file can be included with the keyword IFILE.
If case is the significant for the operating system , then it is also significant in filenames.
Multiple values are enclosed in parentheses and separated by commas.

Note : The Best Practice is ,For Addition of Parameter in PFILE , First List the Parameter alphabetically or Group them by functionality and add it in PFILE. The PFILE varies from Instance to Instance and does not necessarily look like the example given above.

SPFILE : spfileSID.ora :

An SPFILE (" Introduce in Oracle 9i ") is a binary File. This file is not meant to be modified manually and must always reside on the server side. After the file is created it is maintained by the oracle server. If Modified manually, This makes SPFILE useless.

The SPFILE provides the ability to make changes to database persistent across Shutdown and Startup. It also provide the ability to self-tune parameter values, which are recorded in the file.

RMAN Support for backing UP the Initialization Parameter file is possible because the SPFILE resides on server side. By Default, the files is located in " $ORACLE_HOME/dbs " and has a default name in the format of spfileSID.ora.

Characteristics of SPFILE :

Binary File
Maintained by the Oracle Server
Always resides on the server side
Ability to make changes across shut down and start up
Can Self-Tune parameter value
Can have Recovery manager Support backing Up to the initialization parameter file.

Creating an SPFILE :

Created from a PFILE file.

CREATE SPFILE = '$ORACLE_HOME/dbs/spfileORCL.ora' FROM PFILE = '$ORACLE_HOME/dbs/initORCL.ora ';

Where

- SPFILE-NAME : SPFILE to be created

- PFILE- NAME : PFILE creating the SPFILE

Can be executed before OR after instance start up.

An SPFILE is created from PFILE using " CREATE SPFILE " Command. This Command requires the SYSDBA previlege to execute. This command can be executed before OR after instance start UP.

Syntax :

CREATE SPFILE = ' SPFILE - NAME ' FROM PFILE = 'PFILE-NAME';

Where

- SPFILE-NAME : Name of the SPFILE to be created

- PFILE-NAME :Name of the PFILE being used to create the SPFILE. The PFILE Must be available on the server side

If the SPFILE-NAME and PFILE-NAME are not included in the syntax, Oracle will use the default PFILE to generate an SPFILE with a system generated name.

SQL> CREATE SPFILE FROM PFILE;

The contents of an SPFILE can be exported into a PFILE.

SQL> CREATE PFILE FROM SPFILE;

The PFILE is created as a text file on the server side. This command can be exeuted either before or after instance start up. This Provides an easy way to view the SPFILE and make modifications by :

Exporting the SPFILE to a PFILE
Editing the PFILE
Re-creating the SPFILE form edited PFILE

Exporting an SPFILE to a PFILE can also serve as another alternative to creating a backup of the server parameter file.

Note : With Oracle9i Onwards Relase of oracle , RMAN can also back up server parameter file.

v$SPPARAMETER :

By Using this VIEW, There are several options for Viewing the parameter settings within the SPFILE. V$SPPARAMETER is another source for presenting and viewing Contents of the SPFILE.

Modifying Parameters In SPFILE :

Changing Parameter values

ALTER SYSTEM SET undo_tablespace = UNDO2;

Specifying temporary or persistent cahnges

ALTER SYSTEM SET undo_tablespace = UNDO2 SCOPE=BOTH

Deleting or Resetting values

ALTER SYSTEM RESET undo_suppress_errors SCOPE=BOTH SID='*';

The ALTER SYSTEM SET Command used to change the value of instance Parameters.

ALTER SYSTEM SET parameter_name = parameter_value [Comment 'text'] [SCOPE = MEMORY | SPFILE | BOTH] [SID = 'sid'|'*']

Where ,

Parameter_name : Name of the Parameter to be changed

Parameter_value : Value the parameter is being changed to

COMMENT : A comment to be added into the SPFILE next to the parameter being altered.

SCOPE : Determines if change should be made in memory, SPFILE , OR in both areas

MEMORY : Changes the parameter value only in currently running instance

SPFILE : Cahnges the parameter value in SPFILE only

BOTH : Changes the parameter value in the currently running Instance and the SPFILE

SID: Identifies the ORACLE_SID for the SPFILE being used

'sid' : Specific SID to be used in altering the SPFILE

'*' : Uses the Default SPFILE

The ALTER SYSTEM RESET Command is used to delete or Revert to the default value.

ALTER SYSTEM REST parameter_value [SCOPE = MEMORY | SPFILE | BOTH] [SID = 'sid' | '*']

Example :

SQL> ALTER SYSTEM RESET undo_suppress_errors SCOPE = BOTH SID = 'ORCL';

There are several ways to remove a parameter from SPFILE :

Set the parameter back to its default value to simulate deleting using ALTER SYSTEM SET .
Re-Create the SPFILE using CREATE SPFILE FROM PFILE.
Use ALTER SYSTEM RESET to delete the parameter from the SPFILE.

Thank You !!

Oracle Database Logical Structure

2020-07-27T01:24:00.006+05:30

Logical Structure :

In Oracle Database Logical structure gives the mandate or direction like, how the physical space of database is used. Oracle Database Allocates logical space for all data in the database.

The Logical units of Database Space allocation are Data Blocks, Extents, Segments, Tablespaces.

A Logical Structure hierarchy exists as fallows :

An Database contains at least one tablespace.
A tablespace contains one or more segments.
A Segment is made up of extents.
An Extent is made up of logical Blocks.
A Block is the smallest Unit for read and Write Operations.

The oracle database architecture includes logical and physical structures that make up the database.

The Physical structure includes the control files, online redo log files, and Data files that make up the database.
The Logical Structure includes Tablespaces, Segments, Extents, and Data Blocks.

The Oracle Server enables fine- grained control of disk space use through tablespace and logical storage structures, including segments, Extents and Data blocks.

Tablespaces:

The data is an Oracle Database is stored in tablespaces.

An Oracle database can be logically grouped into smaller logical areas of space known as tablespaces.
A tablespace can belong to only one database at a time.
Each tablespace consists of one or more operating system files, which are called data files.
A tablespace may contain one or more segments.
Tableapaces can be brought online while the database is running.
Except for the SYSTEM tablesoace Or a tablespace with an active UNDO Segments, tablespace can be taken offline, Leaving the database running.
Tablespaces can be switched between Read/Write and Read- Only Status.

Data Files (Not a Logical Structure ) :

Each tablespace in an Oracle Database consist of one or more files called data files. These are Physical structures that Conforms with the operating system on which the Oracle Server is running.
A datafile can belongs to only one tablespace.
An Oracle Server Creates a datafile for a tablespace by allocating the specified amount of disk space plus a small amount of overhead.
The Database Administrator can change the size of a datafile after its creation or can specify that a datafile should dynamically grow as objects in the tablespace Grow.

Segments :

A segment is space allocated for a specific Logical storage structure within a tablespace.
A tablespace may consist of one or more segments
A segment cannot span tablespaces, However, a segment can span multiple datafiles that belongs to the same tablepsace.
Each segment is made up of one or more Extents

Extents :

Space is Allocated to a segment by Extents.

One or more Extents make up a segments.

When a segment is created, it Consist of at least One Extent.
As the segment Grows, Extent are added to the segment.
The DBA can manually add extents to a segment

An Extent is a set of Contiguous Oracle Blocks.
An Extent cannot Span Data files, and therefore, It Must exists in one Datafils.

Data Blocks :

The Oracle Server manages the storage space in the oracle datafiles in units called Oracle Blocks Or Data Blocks.

At the finest level of granularity, The data in an Oracle Database is stored in data Blocks.
Oracle Data Blocks are the smallest unit of storage that the Oracle Server can Allocates, read, Or Writes.
One Data block Corresponds to one or more operating system blocks from an existing Data file.
The Standard Data Block size for an Oracle Database is specified by the DB_BLOCK_SIZE initialization Parameter when database is created.
The Data Block Size should be a multiple of the operating system block size to avoid Unnecessary I/O.
The Maximum Data Block Size is dependent on the Operating System.

Thank you !!

Process Structure (Oracle) and Oracle Mandatory Background Processes

2020-07-15T21:11:00.008+05:30

Process Structure (Oracle) :

Oracle takes an advantage of various types of process :

User Process : User Process started at the time when a database user requests to make a connection to the Oracle Server
Server Process : Server Process connects to the Oracle Instance and It is started when a user establishes a session.
Background Processes : Background Processes started when an Oracle Instance is started

User Process : User process working workflow is as below.

A program that requests interaction with the Oracle Server
Must first Establish a connection
Does not interact Directly with the Oracle Server

A Database user who has need some Information from the database, then it is must to make a connection request to establish a connection with the oracle server. The connection is requested using a database interface tool, such as SQL*Plus, and beginning the user process. The user process does not interact directly with the Oracle Server. Rather it Generates calls through the user program interface (UPI) Which creates a session and starts a server process.

Server Process : User process working workflow is as below.

A program that directly interacts with the Oracle Server
Fulfills calls generated and returns result.
Can be Dedicated or Shared Server

Once a user has established a connection, a server process is started to handle the user processes request. A Server Process Can be either a dedicated server process or a shared server process.

In Dedicated server environment, The Server process handles the request of a single user process. Once a user process disconnects, the server process is terminated.

In a Shared Server environment, the server process handles the request of several User Processes. The server Process communicates with the oracle server using the Oracle Program Interface (OPI).

Background Process : Background Process are Maintains and enforces relationship between Physical structure and Memory structure.

The Oracle architecture has five mandatory background process. In addition to the mandatory background process Oracle has many optional background process that are started when their option is being used.

Mandatory Background Processes

DBWn ( Database Writer)
LGWR (Log Writer )
PMON (Process Monitor )
SMON (Syatem Monitor )
CKPT (Checkpoint)

Optional Background Processes: Optional background Process are many. Below are the few optional Background processes are as below :

ARCn (Archiver Process)
CJQ0 (Coordinator Job Queue background Process)
Dnnn (Dispatcher)
LCKn ( RAC lock manager - Instance Locks)
LMDn (RAC DLM monitor - Remote Locks)
LMON ( RAC DLM monitor - Global Locks)
LMS (RAC global cache Service )
Pnnn ( Parallel Query Slaves )
QMNn (Advance Queuing)
RECO ( Recoverer )
Snnn (Shared Server)

Now we will look here working Capabilities of the mandatory background Processes including one very important optional background Process ARCn (Archiver Process)

Mandatory Background Process:

DBWn (Database Writer) : DBWn is a mandatory background process which is part of the SGA. It is Started when instance starts. When the server process records any changes to undo and data blocks in the Database Buffer Cache, DBWn Writes the dirty buffers from the database buffer cache to the data files. It Ensures that a sufficient number of free buffers are available in the Database Buffer Cache. Database performance is improved because server processes make changes only in the Database Buffer Cache.

Free Buffer : Free Buffers are buffer that can be overwritten when server process need to read in blocks form the data files.

DBWn Process writes the dirty buffers to the Data files when the fallowing below Events are Occurs :

- Incremental or Normal Checkpoint

- The number of Dirty Buffers reaches to threshold Value

- A process scans a specified number of Blocks, When Scanning Free Buffers and can not find any

- Timeout Occurs

- A ping request in Real Application Cluster (RAC) environment

- Placing a Normal and temporary tablespace offline

- Placing a tablespace in read-only mode

- Dropping Or Truncating a table

- ALTER TABLESPACE tablespace name BEGIN BACKUP

LGWR (Log Writer) : LGWR is the mandatory background Process and part of the SGA. It is started when instance starts. LGWR performs sequential writes from the Redo Log Buffer to the online redo log file under the fallowing situations.

- When a transaction commits

- When the Redo Log Buffer is one-third full

- When there is more than 1 MB of Changes recorded in the Redo Log Buffer

- Before DBWn writes modified blocks in the Database Buffer Cache to the Data Files

- Every Three Seconds

Because the redo is needed for recovery, LGWR confirms the commit operation only after the redo is written to disk. LGWR can also call on DBWn to write to the data file.

SMON (System Monitor ) : SMON is the mandatory background process and part of the SGA. It is started when instance starts. SMON Process has fallowing responsibilities :

Instance Recovery

Rolls Forward changes in online redo log files
Opens database for user access
Rolls back uncommitted transactions

Coalesces Free Space
Deallocates temporary segments

If the Oracle Instance Fails, Any Information in the SGA that has not been written to disk is lost. For Example, The failure of the operating system causes an instance failure. After the loss of the instance the background process SMON automatically performs instance recovery when the database is reopened. Instance Recovery consists of the fallowing Steps :

Rolling Forward to Recover data that has not been recorded in the data files but that has been recorded in the Online Redo Log file. This Data has not been written to disk because of the loss of the SGA during Instance failure. During this process, SMON reads the Online Redo Log Files and applies the changes recorded in the Online Redo Log Files to the data blocks, Because All committed transactions have been written to the Online Redo Log Files. This process Completely recovers these transactions.
Opening the database so that user can log on. Any data that is not locked by unrecoverable transactions is immediately available,
Rolling back uncommitted transactions. They are rolled back by SMON or by the individual server process as they access locked data.

SMON also performs some space maintenance functions :

- It combines , Or Coalesces , Adjacent areas of free space in the data files.

- It Deallocates temporary segments to return them as free space in data files.

PMON (Process Monitor) : PMON is the mandatory background process and part of the SGA. It is started when instance starts. PMON process has fallowing Responsibility :

- The background Process PMON Performs Clean up after failed Process By fallowing as below:

- Rolling Back the user's current transactions

- Releasing all currently held tables or Row Locks

- Freeing other resources currently reserves by the users

- Restarts Dead Dispatchers

CKPT (Checkpoint) : CKPT is the mandatory background process and part of the SGA. It is started when instance starts. It is Responsible for fallowing responsibilities :

- Signaling DBWn at checkpoints

- Updating Data files headers with checkpoint information

- Updating control files with checkpoint Information

Every three Seconds the CKPT process stores data in the control file to identify that place in the online redo log file from where recovery is to begin, which is called a checkpoint.

The purpose of a checkpoint is to ensure that all of the buffers in the Database Buffer Cache that were modified prior to a point in time have been written to the data files. This point in time (Called the checkpoint position) is where database recovery is to begin in the event of an instance failure. DBWn will already have written all of the buffers in the Database Buffer Cache that were modified prior to that point in time.

Prior to Oracle 9i, This was done at the end of the Online Redo Log File. In the Event of a log switch CKPT also writes this checkpoint information's to the headers of the data files.

Checkpoints are initiated for the fallowing Reasons :

- To Ensure that modified data block in memory are written to disk regularly so that data is not lost in case of a system or Database failure.

- To reduce the time required for instance recovery. Only the online redo log file entries following the last checkpoint need to be processed for recovery occurs.

- To Ensure that all committed data has been written to the data files during shut down.

Checkpoint information written by CKPT includes checkpoint Position, System Change Number (SCN), Location in the online redo log file to begin recovery, Information About logs and So on.

ARCn (Achiever ) Process : ARCn process is an optional background Process but it is very Important process whose play a crucial role to recovering a database after a loss of disk or Disk failures. ARCn process functionalities as below:

Automatically archives Online Redo Log Files when ARCHIVE LOG mode is enabled.
Preserves the record of all changes made to the database

ARCn is an optional background process , however, it is crucial to recovering a database after the loss of a disk. As Online Redo Log files get filled, the oracle server begins writing to the next online redo log file. The Process of switching from one Online Redo Log file to another is called a Log Switch.

The ARCn process initiates backing up, or archiving, of the filled log group at every log switch. It automatically archives the online redo log file before the log can be reused, So all of the changes made to the database are preserved. This Enables recovery of the database to the point of failure even if a disk drive is damaged.

Archiving Online Redo Log Files :

One of the important decisions that a Database Admin (DBA) has to make is whether to configure the database to operate in ARCHIVE LOG or in NO ARCHIVE LOG mode.

NO ARCHIVE LOG Mode : In NO ARCHIVE LOG mode, the online redo log files are overwritten each time when a log switch occurs. LGWR does not overwrite an Online Redo Log File group until the checkpoint for the group is complete. This Ensures that the committed data can be recovered if there is an instance crash. During the instance crash, Only SGA is lost. There is no Loss of disks, Only memory. For Example, An operation system crash causes Instance Crash.

ARCHIVE LOG Mode : If the Database configured to run in ARCHIVE LOG mode, Inactive groups of filled Online Redo Log Files must be archives before they can be used again. Because changes made to the database are recorded in the online redo log files, The DBA can use the physical backup of the data files and the archived redo log files to recover the database without losing any committed data because of nay single point of failure, Including the loss of a disk. Usually, a production database is configured to run in ARCHIVE LOG mode.

Thank You !!

Program Global Area OR Process Global area (PGA)

2020-07-09T14:42:00.006+05:30

Program Global Area OR Process Global Area (PGA):-

The Program Global Area OR Process Global Area (PGA) is a memory reason that contains data and control information for a single server process or a single background process. The PGA is allocated when a process is created and deallocated when the process is terminated. In Contrast to the SGA, Which is shared by several processes, the PGA is an area that is used by only one process.

PGA has Below Functionality :

Memory reserved for each user process connection to an Oracle Database
Memory Allocated when a process is created
Memory deallocated when the process is terminated
Each Server Process having on its own PGA and used by only one process.

Contents of PGA :

The contents of the PGA memory varies, Depending on whether the Instance is running in a dedicated server Or Shared Server configuration. Generally, the PGA memory includes these below components :

Private SQL Area : It contains data such as bind information and run-time memory structures. Each session that issues a SQL statement has a private SQL area. Each user that submits the same SQL statement has his or her own Private SQL Area that uses a single shared SQL area. Thus many private SQL areas can be associated with the same Shared SQL Area.

The Private SQL area of a Cursor is divided into two areas :

Persistent Area : Persistent Area contains bind Information, and it is freed only when the cursor is closed.
Run-time Area : Run-time Area Created as the first step of an execute request. For INSERT, UPDATE, and DELETE commands, This area is freed after the statement has been executed. For queries, This area is freed only after all rows are fetched OR the query is canceled.

The location of the Private SQL area depends on the type of connections established for the session. In a dedicated server environment, the Private SQL Area is located in the PGA of their Server Process. In a shared server environment, the Private SQL Areas are located in SGA.

The management of the private SQL area is the responsibility of the user process. The number of Private SQL Areas that a user process can allocate is always limited by the OPEN_CURSORS initialization parameter. The default value of this parameter is 50.

Session Memory: Session Memory consists of memory allocated to hold a session's variables and other information related to the session. For a shared server environment, the session memory is shared and not private.
SQL Work Area: SQL Work Areas Used for memory-intensive operations such as Sort, Hash-Join, Bitmap Merge, Bitmap Create. The Size of a work area can be controlled and tuned. Beginning from Oracle 9i, the size of the work area can be automatically and globally managed. This is enabled by Setting the WORKAREA_SIZE_POLICY Parameter to Auto, Which is the default value, and the PGA_AGGREGATE_TARGET initialization Parameter. The PGA_AGGREGATE_TARGET parameter is set to specify the target aggregate amount of PGA memory available to the instance. This Parameter is only a target and can be dynamically modified at the instance level. It will accept a number of bytes, kilobytes, megabytes, or gigabytes. When these parameters are set sizing of work areas becomes automatic and all *_AREA_SIZE.

Prior to oracle 9i, The DBA controlled the maximum size of SQL work area by setting the following parameters:

SORT_AREA_SIZE ,

HASH_AREA_SIZE,

BITMAP_MERGE_AREA_SIZE, and

CREATE_BITMAP_AREA_SIZE

Setting these parameters can be difficult because the maximum work area size is ideally selected on the basis of the data input size and the total number of the work area active in the system. These two factors vary considerably from one work area to another and from one time to another.

Differences in Memory Allocation Between Dedicated Server and Shared Server :

The content of the PGA memory varies, Depending on whether the instance is running in a dedicated server or shared server configuration. Generally, the PGA memory includes Fallowing components.

Memory Area	Dedicated Server	Shared Server
Nature of Session Memory	Private	Shared
Location Of Persistent Area	PGA	SGA
Location Of Run-time Area (SELECT)	PGA	SGA
Location Of Run-time Area (DML/DDL)	PGA	PGA

Thank You !!

Large Pool And JAVA Pool

2020-07-04T20:04:00.011+05:30

Large Pool :

Large Pool is an optional area in SGA. It relieves the burden of the Shared pool also.

It provides an area of memory from which large allocations can be made. Oracle's backup and restore utilities typically allocate buffers that are hundreds of kilobytes in size. These will be allocated in the large pool if the large pool memory allocation is present.

Large Pool Reduces the overhead of the shared pool, in case of shared server connection mode.

A large Pool is used for the following :

Session memory for the shared server and the Oracle XA Interface when distributed transactions are involved
I/O server processes
Backup and restore operations for RMAN
Parallel execution message buffers

PARALLEL_AUTOMATIC_TUNING set to TRUE

Large Pool does not use an LRU list. It is sized by LARGE_POOL_SIZE and can be resized dynamically.

For shared server or parallel query buffers, Oracle uses the shared pool primarily for caching shared SQL statements. For a shared server or parallel query buffers, Oracle Uses the large pool memory when we set below Parameter value to TRUE.

PARALLEL_AUTOMATIC_TUNING=TRUE

Allocating session memory from the large pool for a shared server or parallel query buffers, Thus relieving the burden on the area within the shared pool.

By allocating session memory from the large pool for shared SQL, the database avoids performance overhead caused by increasing and shrinking the shared SQL cache in shared pool memory.

By allocating memory in large buffers for RMAN operations, I/O server processes, and parallel buffers, the large pool can satisfy large memory requests better than the shared pool.

By allocating memory form the large pool, for caching SQL parse tree in favor of shared server session information, I/O, and backup and recovery processes, The shared pool does not have to give up own memory for these processes. So the Performance gain happens from the reduction of overhead from increasing and shrinkage of the shared SQL cache in the shared pool.

The multi-threaded server will also take advantage of the large pool, allocating session memory there instead of in the shared pool, thus leaving more of the shared pool open for SQL statements and execution plans.

Below is t use Cases of Large Pool:

Backup And Restore :

Recovery Manager (RMAN) uses the large pool when the parameter set as below.

BACKUP_DISK_IO = n
BACKUP_TAPE_IO_SLAVE = TRUE

If the large pool is configured but is not large enough, the allocation of memory from the large pool fails. RMAN writes an Error message to the alert log file and does not use I/O slaves for backup and restore.

Parallel Execution :

Large Pool is used for Parallel execution when the below parameter is set to TRUE, Otherwise, these buffers are allocated and use the shared pool memory.

PARALLEL_AUTOMATIC_TUNING=TRUE

Sizing The LARGE POOL :

A large pool area will only be available if the parameter has set in the initialization parameter. For example, to allocate a large pool of 10 megabytes, you would add the following line to your database's initialization parameter file.

LARGE_POOL_SIZE = 10M

The large pool is sized in bytes defined by the LARGE_POOL_SIZE Parameter. This Parameter can be dynamically resized using the ALTER SYSTEM SET command.

ALTER SYSTEM SET LARGE_POOL_SIZE=50M;

Large Pool and LRU List :

The large Pool does not have an LRU list. It is different from reserved space in the shared pool, which uses LRU List.

JAVA POOL :

The java pool is a RAM region within the Oracle SGA and the java pool RAM is used to provide:

Services Parsing of Java code and scripts.
Installation tasks related to Java applications with Oracle versions.
Java stored procedure code parsing.
Sized by JAVA_POOL_SIZE Parameter

The JAVA pool is an optional setting but is required if you are installing and using JAVA. Its Size is set in Bytes using the JAVA_POOL_SIZE. The Default Size of the JAVA POOL is 24MB (Oracle 9i)

Thank You !!

Redo Log buffer

2020-06-29T13:26:00.005+05:30

What is Redo Log ?

Redo Log plays the most important role in Recovery operations. It consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.

Redo Log Buffer : Redo Log Buffer provide the fallowing functions.

Records all changes made to the database data blocks
Primary Purpose is recovery
Changes recorded within are called redo entries
Redo entries contain information to reconstruct or redo changes
Size defined by Log_buffer Initialization Parameter

The redo log buffer is an area of memory within the SGA and it is a circular buffer that holds the changes made to the datafile blocks. This Information is called redo entries. Redo Entries contains the information necessary to re-create the data prior to the made by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations.

Server process generates the redo data (redo entries ) into the redo log buffer as any changes occurs to the data blocks using DMLs in the Database buffers. LGWR (log Writer) database background process writes the redo log buffers (redo entries) to the Active online Redo Log File on the disk.

Oracle will eventually flush the redo log buffer by transferring the redo entries to Active inline redo log Files on disk. This can happen in a number of special cases.

Every commit transaction occurs.
When redo Log buffer Fills even though some redo records may not be committed.
Until Unless All redo entries are transferred to the active online redo log files on disk.

Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file, and assigns a system change number (SCN) to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.

Redo records can also be written to a redo log file before the corresponding transaction is committed. If the redo log buffer fills, or another transaction commits, LGWR flushes all of the redo log entries in the redo log buffer to a active online redo log files on the disk , even though some redo records may not be committed. If necessary, the database can roll back these changes.

Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.

Sizing the Redo Log buffer:

The size of the redo log buffer is defines by the LOG_BUFFER initialization parameter.

The default value of this size is four times the DB_BLOCK_SIZE, but it is often desirable to set this value higher, particularly if there are many or long transactions generating high rates of redo generation.

Thank You !!

Database Buffer Cache

2020-06-27T10:34:00.009+05:30

Database Buffer Cache

Oracle database buffer cache is typically the largest portion of the SGA. It has data that comes from the files on disk. Because accessing data from disk is slower than from memory, The main purpose of the database buffer cache is to cache the data in memory for quicker access.

Database Buffer cache:

Stores copies of data blocks that have been retrieved from the datafiles
Enables great performance gains when you obtain and update data
Managed through an LRU algorithm.
DB_BLOCK_SIZE determines primary Block size.

When a query is processed, the oracle server process looks in the Database buffer cache for any blocks if needs. If the block is not found in the database buffer cache, the server process reads the block from the data file and places a copy in the database buffer cache. Because subsequent requests for the same block may find the block in memory, then the requests may not require physical reads. The Oracle Server uses an LRU algorithm to age out buffers that have not been accessed recently to make some room for new blocks in the Database Buffer Cache.

The database buffer cache consists of independent sub caches.

DB_CACHE_SIZE
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE

These Independent sub cache can be resized dynamically as below,

ALTER SYSTEM SET DB_CACHE_SIZE = 100M;

DB_CACHE_ADVICE set to gather statistics for predicting different cache size behavior.

Statistics displayed by V$DB_CACHE_ADVICE

Sizing of the Database Buffer Cache:

The size of each buffer in the Database Buffer Cache is equal to the size of an oracle block, and it is specified by the DB_BLOCK_SIZE parameter. The Database buffer cache consists of independent sub caches for buffer pools and for multiple block sizes. The Parameter DB_BLOCK_SIZE determines the primary block size, which is used for the SYSTEM tablespace.

Three parameters define the sizes of the Database Buffer Caches:

DB_CACHE_SIZE: Using this parameter, Sizes the default buffer cache buffers only with the primary block size (The Block size defined by DB_BLOCK_SIZE parameter) and it always exists and can not be set Zero.
DB_KEEP_CACHE_SIZE: Using this Parameter sizes keep buffer cache, which is used to retain blocks in the memory that are likely to be reused.
DB_RECYCLE_CACHE_SIZE: By using this parameter, Sizes the recycle buffer cache, which is used to eliminate blocks from memory that have little chance of being reused.

Buffer Cache Advisory

The buffer cache advisory feature enables and disables statistics gathering for predicting behavior with different cache sizes. The Information provided by these statistics can help you to determine the size of Database Buffer Cache optimally for a given workload.

The Buffer Cache Advisory information is collected and displayed through the V$DB_CACHE_ADVISE View.

The Buffer Cache Advisory is enabled via the DB_CACHE_ADVICE initialization parameter. It is a dynamic parameter that can be altered using ALTER SYSTEM command. Three Values (OFF, ON, READY) is available.

DB_CACHE_ADVICE parameter values:

OFF: Advisory is turned off and the memory for the advisory is not allocated.

ON: Advisory is turned on and both CPU and memory overhead is incurred.

Note: Attempting to set the parameter to the ON state when it is in the OFF state may lead to the Fallowing Error:

ORA-4031 Inability to allocate memory from the Shared Pool when the parameter switched to turned ON.

If The parameter is in a READY state it can be set to ON without error because the memory is already allocated.

READY: Advisory is turned off but the memory for the advisory remains allocated. Allocating the memory before the advisory is actually turned ON and it will avoid the risk of ORA - 4031. If the Parameter is Switched to this state from OFF, It Is Possible that an ORA-4031 will be raised.

Thank you !!

Shared Pool

2020-06-17T00:30:00.089+05:30

Hey Folks,

Here we shall review about Shared Pool :

Shared Pool : Shared pool is a part of SGA and it is used to store :

Most recently executed SQL statement
Most recently used data definitions

It consist of three key performance - related memory structures :

Library Cache
Data Discretionary cache
Result cache

Shared pool sized by the Parameter " SHARED_POOL_SIZE "

ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;

The shared pool environment contains both fixed and variable structure. The fixed structures remains relatively the same size whereas the variable structure grows and shrink based on user and program requirements. the actual sizing for the fixed and variable structure is based on an initialization parameter and the work of an Oracle Internal algorithms.

The size of shared pool as total size determined by the initialization parameter SHARED_POOL_SIZE. The Default value of the parameter is 8 M on 32 - bit platforms and 64 M on 64 - bit platforms. Increasing the value of this parameter is increases the amount of memory reserved for the shared pool.

In the shared pool have many caches including the Library Cache and the Data Dictionary Cache automatically increase and decrease in size as needed. Old entries are aged out to accommodate new entries when shared pool runs out of space.

A Cache Miss on the library cache or Data Dictionary Cache is more more expensive than a miss on database buffer cache. For this reason the shared pool should be sized to ensure that frequently used data is cached.

Large memory allocation required in shared pool for several features, such as shared server, Parallel Query OR Recovery Manager. Oracle Recommends to use a separate memory area, that is called "large pool" to protect the System Global Area (SGA) memory used by these features.

Sometime make memory allocation out of the shared pool for JAVA, PL/SQL, Or SQL cursors larger than 5 KB. To Enable these allocations to occur more efficiently Oracle Database preserve a small amount of shared pool memory and that preserved memory is called Reserved Pool. It is Used if the shared pool Runs out of Space.

Sizing the Shared Pool :

Shared Pool consist of three important memory structure:

Library Cache : Library Cache is part of shared pool in the SGA and it Plays the important role and special functionality as below

Stores information about the most recently used SQL and PL/SQL Statements
Enables the sharing of commonly used statements
It is managed by least Recently used (LRU) algorithm
Consist of two structures

--> Shared SQL area

--> Shared PL/SQL area

Size determined by shared pool sizing

The Library cache stores executable forms of SQL cursors, PL/SQL programs, and JAVA Classes, Which are collectively referred to as the application code.

When the application code is executed and parsed or program unit is called the memory is allocated. If the size is too small, Statements are continually reloaded into the library cache and which affect performance. The library cache is managed by an LRU algorithm.

Oracle database attempts to reuse existing code if it has been executed previously and can be shared. If the parsed representation of the SQL statement exists in library cache and it can be shared, then the database reuses the existing code. This is Known as a Soft Parse, Or a Library Cache Hit.

If Oracle Database cannot use the existing code, Then database must build a new executable version of the application code. This is known as Hard Parse or, a Library Cache Miss.

In Order to Perform a Hard Parse Oracle Database uses more resources than during a Soft Parse. Resources used for a Soft Parse include CPU and Library Cache Latch Gets. Resources required for Hard Parse Include additional CPU, Library Cache Latch Gets and the Shared Pool Latch Gets. A Hard Parse may occur on either the parse step or the execute step When processing a SQL Statement.

When the Library Cache fills then the Less/least used execution paths and Parse tress are removed from the Library Cache to make rooms for the new Entries. If the SQL or PL/SQL statements are not reused then they are eventually aged out, Then Oracle Database implicitly re-parses the statement to create a new Shared SQL area for it, and execute the statement. This also results in a Hard Parse. To reduce Library Cache Misses on execution calls, allocate more memory to the Library Cache.

When an application makes a parse call for a SQL statement, if the parsed representation of the statement does not exist in the Library Cache, then Oracle Database Parse the statement and stores the Parsed form in the Shared Pool. To Reduce the Library Cache Misses on Parse calls, ensure that all the shareable SQL statements are stored in the Shared Pool whenever Possible.

The Library Cache consist of two structures:

Shared SQL : The shared SQL area stores and shares the Execution Plan and Parse Tree for SQL Statements runs against the Database. If the Second time, Same SQL statements executes, then it is able to take advantage of the Parse information available in the shared SQL area to faster its executions. To make an assurance that the SQL statements use a shared SQL area whenever possible and for that The Text, Schema and bind variables must be exactly the same.
Shared PL/SQL : The Shared PL/SQL area stores the shares the most recently executed PL/SQL Statements. Parsed and Complied Program Unit and Procedures ( Functions, Packages, Triggers) are stored in this area.

2. Data Dictionary Cache :

The Second major Cache area within the Shared Pool is the Data Dictionary Cache. The Data Dictionary Cache holds the internal data Structure of the Oracle Database.

The Data Dictionary is accessed very often by various Oracle Processes. Dictionary Cache is the memory area that is designed to hold the Database Data dictionary data. It is also known as the ROW Cache Because it holds the data in ROWs format instead of buffers.

When an Oracle Database is first started, No data exists in Data Dictionary Cache. The Data Dictionary Cache started to holds the dictionary data after the Database Startup and it is up and running for a period of time. It is used for storing the most recently used data definition in the Oracle Database. These Data Definitions may include information about, Data Files, tables, Indexes, Privileges, Users etc, and any DML operation performed.

Caching these information in memory improves the performance especially for queries and updates using DML. During the parsing phase, the server process scans this memory structure to resolve the database object names and validate access.

Data dictionary Cache hold the information about Oracle Database is as below:

A Collection of the most recently used definitions in the database
Include information about database files, tables, indexes, columns, users, privileges, and other database objects
During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access
Caching data dictionary information into memory improves response time on queries on DML
Size determined by the Shared Pool Sizing

The data dictionary cache is also referred to as the dictionary cache or Row cache. Information about the database (User Account Data, Data File names, Segment names, Extent Locations, Table descriptions and user privileges) is stored in the data dictionary tables. When this Information is needed by the server, the data dictionary tables are read, and the data that is returned is stored in the data dictionary cache.

We can reduce the contention of Data dictionary cache by using locally managed tablespace (LMT) instead of using dictionary managed tablespace (DMT).

The data dictionary cache misses is the ratio of the number of data dictionary request resulting in cache misses (Requested Data dictionaries information that's are not found in data dictionary cache). The data dictionary cache holds the data dictionary data that is used and heavily referenced during SQL pre-execution processing and it improves the SQL query performance.

If you are having difficulties regarding the data dictionary cache misses, note:

"If the Miss Percentage above is greater than 10-15, you may need to tune your library cache usage by adding additional memory to the data dictionary cache. Increasing memory is done by increasing the SHARED_POOL_SIZE parameter in the INITsid.ora file.

Sizing of the data Dictionary :

The overall size is dependent on the size of the shared pool size and is Managed internally by the database. If the Data Dictionary Cache is too Small, then the database has to query the data dictionary tables repeatedly for information is needed by the server. These queries are called recursive calls and are slower than the direct query on the data dictionary cache because direct queries do not use SQL.

3. Result Cache :

This Features is introduced and included in Shared pool of an SGA from oracle 11G Release. The result cache has received an attention due the capability to cached SQL query and PL/SQL functions results in memory.

When a query is executed for the very first time, the user’s process searches for the data in the database buffer cache. If data is there (because someone else had retrieved this data before), it uses it; otherwise, it performs an I/O operation to retrieve data from the datafile on disk into the buffer cache, and from this data, the final result set is built.

Subsequently, if another query requires the same data set, the process uses the data from the buffer cache to build the result set required by the user. Well, here the result cache could be called a cache area, in this case, the shared pool. So, the Result Cache is an area in the shared pool and contains the end results of a query execution.

E.g. : Suppose one user executed a query and get the final result of 300 rows. If another user executed the same query and then The user session will have to traverse through all these rows in the buffer cache once again to get the final result of 300 rows. So the result cache is introduced and using this result cache to get directly to those 300 rows the second time and every time going forward.

Now Question Comes that how the result cache will use when same query executed again and again. So for that use of result cache and get the faster result of the same query, We have to use OR pass RESULT_CACHE hint in the query.

SELECT /*+ RESULT CACHE */ SUM(Sal) FROM HR.employees WHERE DEPTNO=45;

When a query with RESULT_CACHE hint is run. Oracle Will see if the Results of the query have already been Executed, Computed, and Cached and If so, Retrieve the data from the cache instead of querying the data blocks and computing the results again. Take the fallowing important points into consideration.

The result Cache feature is useful only for SQL queries that are executed over and over again frequently.
the underlying data doesn't change very often. When The data changes, the result set is removed from the cache

If you are executing the same queries over and over, using the RESULT_CACHE hint often makes subsequent queries run faster.

By default, the result_cache_max_size parameter is about 0.25% of the memory_target parameter or 1% of the shared_pool parameter.

This parameter can be modified to control how much is stored in the Result Cache. The result_cache_max_result parameter specifies what percentage of result_cache_max_size a single Result Cache can use. The default value is 5%.

Each result set is identified in the cache using a CACHE_ID, which is a 90-character long string. The CACHE_ID for a query does not match the SQL_ID used to identify the query in the library cache and contained in V$SQL. Unlike the SQL_ID, which is generated for every SQL query executed against an Oracle database, the CACHE_ID is for an area or bucket in the Result Cache section of the shared pool that stores the end result of the query.

Thank You!!

Memory Architecture

2020-06-15T19:08:00.009+05:30

Hey Folks,

Here we shall know very little and important Facts about memory architecture.

When we talk about memory architecture of an ORACLE then it comes in our mind that the memory architecture is SGA (Shared Global Area) and PGA (Program Global Area) and it comes Collectively in Memory architecture of an ORACLE.

Here we shall know about the what kind of information hold by the memory architecture in an ORACLE.

Oracle uses memory to store information at run time and such is fallowing.

Program Code
Information about a connected session even if it is not currently active
Information About the program during the program Execution. For e.g., the current state of a query from which rows are bring fetched.
Information that is shared and communicated among oracle processes. For e.g. Locking Information.
Cached data that is also permanently stored on peripheral memory . For e.g. Data Block and redo Log Entries.

The Basic memory structures associated with oracle is :

System Global Area (SGA) : Which is Shared by all server and background processes.
Program Global Area (PGA) : Which is private to each server and background Process and there is one PGA for an each processes.

System Global Area (SGA) :

A system global area (SGA) is a group of shared memory architecture that contain data and control information for one Oracle Database Instance.

If Multiple Users are concurrently connected to the same instance's SGA is shared among the users.

Memory allocations happens to the SGA from the RAM memory of the systems or Servers. When instance starts the SGA reserves memory from the RAM. When Instance Shut down then SGA allocated memory released automatically.

An SGA and oracle process collectively make up an oracle instance. Oracle Automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shutdown the instance. Each instance has its own SGA.

The SGA is Read/Write. All users connected to a multiple process, database instance can read Information contained within the instance's SGA, and Several processes write to the SGA during execution of an Oracle.

Program Global Area (PGA) :

A program Global area (PGA) is a memory region that contain data and control information for a server process.

When a user makes a request to connect with an instance then the server process start to communicate to instance on behalf of user process and for that server process PGA memory allocated. PGA allocates for each and every server process separately. PGA memory also allocates from the system/Servers RAM memory.

When Session is started then memory reserves for that session from the RAM and when the session is closed then memory is released itself for that particular session.

It is non shared memory created by Oracle when server process is started. Only a Server process can access the PGA. Memory Allocated for PGA that is memory allocated from outside the Oracle Instance Memory.

Oracle Database Reads and Writes information in the PGA on the behalf of the server process. One PGA exists for each server processes and each background processes. It Stores data and control information for a single server Process or a Single background process.

Thank You !!.

Please keep visiting this blog for more informative posts.

SGA (System Global Area)

2020-06-06T17:46:00.006+05:30

Hey Folks,

This post is the continuation of my previous post " Oracle database architecture". In This post We will know few facts about System Global Area (SGA).

SGA (SYSTEM GLOBAL AREA):

System Global Area is also known as Shared Global Area.

A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance's SGA is shared among the users. Therefore, the SGA is sometimes called the shared global area.

An SGA and Oracle processes constitute an Oracle instance. Oracle automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.

The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance's SGA, and several processes write to the SGA during execution of Oracle.

The SGA consist of several memory structure :

Shared pool
Database buffer cache
Redo Log buffer
Other Structures (For example, Lock and latch management , Statistical data)

There are three additional memory structures that can configured within the SGA.

Large Pool (Optional)

Java Pool

Stream Pool

The SGA is also called the shared global area. It is used to store database information that is shared by database processes. it contains data and control information for the oracle server and is allocated in the virtual memory of the computer where oracle resides.

The fallowing statement can be used to view SGA memory allocation. We use below this command to show SGA configuration of the oracle instances.

SQL> show sga

Total System Global Area 1.0689E+10 bytes

Fixed Size 2235904 bytes

Variable Size 4664066560 bytes

Database Buffers 6006243328 bytes

Redo Buffers 16928768 bytes

Dynamic SGA :

Beginning with Oracle 9i, A Dynamic SGA implements an infrastructure that allows the SGA configuration to change without shutting down the instance. This then allows the size of the Database Buffer Cache, Shared Pool, and Large Pool to be changed without shutting down the instance.

So the Database Buffer Cache and Shared Pool could be initially under configured and would grow and shrink depending upon their respective work loads, up to a maximum if SGA_MAX_SIZE.

The SGA_MAX_SIZE Initialization Parameter:

The SGA includes a number of memory components, which are pools of memory used to ensure and fulfill the memory allocation requests. Examples of memory components include the shared pool (used to allocate memory for SQL and PL/SQL execution), the java pool (used for java objects and other java execution memory), and the buffer cache (used for caching disk blocks). All SGA components allocate and deallocate space in units of granules. Oracle Database tracks SGA memory use in internal numbers of granules for each SGA component.

Granule size is determined by total SGA size. On most platforms, the size of a granule is 4 MB if the total SGA size is less than 1 GB, and granule size is 16MB for larger SGAs. Some platform dependencies arise. For example, on 32-bit Windows, the granule size is 8 M for SGAs larger than 1 GB.

Oracle Database can set limits on how much virtual memory the database uses for the SGA. It can start instances with minimal memory and allow the instance to use more memory by expanding the memory allocated for SGA components, up to a maximum determined by the SGA_MAX_SIZE initialization parameter. If the value for SGA_MAX_SIZE in the initialization parameter file or server parameter file (SPFILE) is less than the sum the memory allocated for all components, either explicitly in the parameter file or by default, at the time the instance is initialized, then the database ignores the setting for SGA_MAX_SIZE.

For optimal performance in most systems, the entire SGA should fit in real memory. If it does not, and if virtual memory is used to store parts of it, then overall database system performance can decrease dramatically. The reason for this is that portions of the SGA are paged (written to and read from disk) by the operating system. The amount of memory dedicated to all shared areas in the SGA also has performance impact.

The size of the SGA is determined by several initialization parameters. The following parameters have the greatest effect on SGA size:

Sizing the SGA:

The Size of the SGA is determined by several initialization parameters. The Parameters that most affect SGA size are :

DB_CACHE_SIZE : The size of the cache of standard blocks. The default is 48 MB on UNIX and 52 MB on NT (Windows).

LOG_BUFFER : The number of bytes allocated for the Redo Log Buffers that Oracle uses when buffering redo entries to a redo log file. Redo log entries contain a record of the changes that have been made to the database block buffers. The LGWR process writes redo log entries from the log buffer to a redo log file.

The log buffer size depends on the number of redo strands in the system. One redo strand is allocated for every 16 CPUs and has a default size of 2 MB. Oracle allocates a minimum of 2 redo strands per instance. When the log buffer size is not specified, any remaining memory in the redo granules is given to the log buffer.

SHARED_POOL_SIZE : SHARED_POOL_SIZE specifies (in bytes) the size of the shared pool The shared pool contains shared cursors, stored procedures, control structures, and other structures.

If you set PARALLEL_AUTOMATIC_TUNING to false, then Oracle also allocates parallel execution message buffers from the shared pool. Larger values improve performance in multi user systems. Smaller values use less memory.

LARGE_POOL_SIZE : The large pool size allocation is used in shared server systems for session memory, by parallel execution for message buffers, and by backup processes for disk I/O buffers.

Parallel execution allocates buffers out of the large pool only when SGA_TARGET is set.

The Default value is Zero (Unless the the init.ora parameter PARALLEL_AUTOMATIC_TUNING is set to TRUE, then the default is Automatically Calculated.

JAVA_POOL_SIZE : JAVA_POOL_SIZE specifies (in bytes) the size of the Java pool, from which the Java memory manager allocates most Java state during run-time execution.

STREAM_POOL_SIZE : Oracle's Automatic Shared Memory Management feature manages the size of the Streams pool when the SGA_TARGET initialization parameter is set to a nonzero value.

If the STREAMS_POOL_SIZE initialization parameter also is set to a nonzero value, then Automatic Shared Memory Management uses this value as a minimum for the Streams pool.

If the STREAMS_POOL_SIZE initialization parameter is set to a nonzero value, and the SGA_TARGET parameter is set to 0 (zero), then the Streams pool size is the value specified by the STREAMS_POOL_SIZE parameter.

If both the STREAMS_POOL_SIZE and the SGA_TARGET initialization parameters are set to 0 (zero), then, by default, the first use of Streams in a database transfers an amount of memory equal to 10% of the shared pool from the buffer cache to the Streams pool.

Therefore, the size of the SGA can not be exceed SGA_MAX_SIZE minus the value for DATABASE_BUFFER_CACHE,LOG_BUFFER,SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE and STREAM_POOL_SIZE

Hey Folks, If this post is informative then please fallow, like and Subscribe.

Thank You !!.

Oracle database Architecture

2020-06-04T07:17:00.023+05:30

Hey Folks,

Here we will learn about oracle database architecture and its Function.

Oracle Server: An Oracle server is a Database management system that provides an OPEN, comprehensive, integrated approach to information management.

In General, an oracle server must reliably manage a large amount of data in a multi-user environment so that many users can concurrently access the same Data.

There are several files, Processes, and memory structure in an Oracle Server, However, not all of them are used when processing a SQL statement. Some are used to improve the performance of the database, to ensure that the database can be recovered in the event of a software or hardware error, or to perform other tasks necessary to maintain the Database.

An Oracle server must also prevent unauthorized access and provide efficient solutions for failure recovery.

Oracle Server Consists of an Oracle Instance and Oracle Database.

Oracle Instance: An Oracle Instance is the combination of the background Process and the Memory structure. The Instance must be started to access the data in the database.

Every Time an Instance is started, a System Global Area (SGA) is allocated and Oracle Background Process is started. Background Process performs functions on behalf of the invoking process. The Background Process Perform input/output (I/O) and monitor other Oracle Processes to provide increased parallelism for better performance and Reliability.

Oracle Database: An Oracle Database consist of Operating System Files, Also Known As Database Files, that provide the actual physical storage for database information. The database files are used to ensure that the data is kept consistent and can be recovered in the event of a Failure of the instance.

An Oracle Database has a Logical and a Physical Structure. The physical structure of the database for the database is the set of Operation System files in the database.

An Oracle Database :

Is a collection of data that is treated as a Unit.
Consist if Three File Types.

Data Files: Data Files Containing the actual data in the database.

Online Redo Log files: Online Redo Log Files containing a record of changes made to the database to enable recovery of data in case failures.

Control Files: Control Files containing Information necessary to Maintain and Verify database

Other Key File Structure :

The Oracle server also uses other files that are not part of the Database :

The Parameter File: The Parameter File defines the characteristics of an Oracle Instance. For Example, It contains parameters that size, Some of the memory structure in the SGA.

The Password File: The password file authenticates users privileged to startup and shut down an Oracle Instance.

Archive redo log files: Archive redo log files are offline copies of the online redo log files that may be recover from Media Failure.

ORACLE DATABASE ARCHITECTURE DIAGRAM

Now here we see standard oracle database architecture via Diagram. Further, we will see the one by one all the Oracle architecture components in Details in the coming few subsequent posts.

Here we see the only overview:

Virtual Structure OR memory Structure: Virtual Structure is volatile in nature as when your database instance is up, memory structure will exist and when your database instance is down, memory structure does not exist.

Oracle's memory structure consists of two memory is known as,

System Global Area (SGA): Allocated at instance startup, and it is the fundamental component of an oracle instance.
Program Global Area (PGA): Allocated when the server process is started.

SGA consists of many components OR divided in-memory structures components and as we can see in the above Architecture diagram. Now Let see here the little description of all memory components. I will share descriptive details for all each and every component in a few next posts.

Shared Pool: The Shared pool is an area that is Exists on RAM heap of the system when the Oracle Instance starts Up. This is one of the most important storage structure in an Oracle Database system. The Oracle shared pool contains Oracle library cache, Data dictionary cache and In 11g Onward It contains the Result Cache also.

Database Buffer cache: The oracle database buffer cache is also known as buffer cache. The database buffer cache is typically the largest area if SGA holds the copy of data blocks read from the data files. The data block copies are stored in the buffer and these buffers are equals in the data block size. The database buffer cache is shared among all the sessions that are connected to the instance.

The goal is to keep frequently used data blocks in the buffer cache in order to improve I/O by having fewer physical reads or writes. When a dirty buffer is not used anymore, it is written to the disk by the background process database writer (DBWR).

This area of the SGA contains only the buffers themselves and not their control structures. For each buffer, there is a corresponding buffer header in the variable area of the SGA.

Redo Log Buffer: The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. Redo entries contain the information necessary to reconstruct, or Redo, the changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations.

Redo entries are primarily used for database recovery as necessary.

Large Pool: The large pool is an optional memory area in the SGA. It relieves the burden placed on the shared pool by allocating session memory from the large pool for a shared server or parallel query buffers. Oracle can use the shared pool primarily for caching shared SQL and avoid the performance caused by shrinking the shared SQL cache in Shared Pool.

Large pool provides large memory allocation for :

Session memory for the shared server and It is also used where transaction interact with more than one database
I/O server process
Oracle Backup and restore operations
Parallel execution message buffer if the initialization parameter PARALLEL_AUTOMATIC_TUNING is set to TRUE. (Otherwise, these buffers are allocated to the shared pool)

In addition, the memory for Oracle backup and restore operations, for I/O server processes, and for parallel buffers are allocated in buffers of a few hundred kilobytes. The large pool is better able to satisfy such large memory requests than the shared pool.

JAVA Pool: The java pool is a Memory region within the Oracle SGA and the java pool Memory is used to provide:

Parsing if the JAVA code and Scripts
Installation Tasks related to JAVA applications with Oracle

The JAVA pool is an optional Setting but it is required if you are installing and using JAVA. its size is set in bytes, using the JAVA_POOL_SIZE parameter. Default Size of the JAVA Pool is 24 MB (Oracle 9i)

Streams Pool: The Streams pool stores buffered queue messages and provides memory for Oracle Streams capture processes and apply processes. The Streams pool is used exclusively by Oracle Streams.

Unless you specifically configure it, the size of the Streams pool starts at zero. The pool size grows dynamically as required by Oracle Streams.

Oracle Background Processes: Oracle Mandatory background processes are started when oracle instance is started. These Processes are maintaining and enforces the relationships between Physical and Memory Structures.

We will see here Only ORACLE Mandatory background process.

Mandatory background Processes is:

DBWn (Database Writer Process)
LGWR (Log Writer Process)
PMON (Process monitor process)
SMON (System Monitor process)
CKPT ( Checkpoint Process)

Physical Structure: Physical Structure is consists of oracle physical files.

Data file: Data files Containing the actual data in the database.

Online redo log files: Online Redo Log Files containing a record of changes made to the database to enable recovery of data in case failures.

Control Files containing Information necessary to Maintain and Verify database

Parameter file

Password File

Archive redo log files.

Thank you !!.

See you in the next post with some more Architecture details.