Program Global Area (PGA)

Program Global Area Components

The Program Global Area or Process Global Area (PGA) is a memory region 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.

Content of the PGA

The content of the PGA memory varies, depending on whether the instance is running the shared server option. But generally speaking, the PGA memory can be classified as follows.

Private SQL Area

A private SQL area contains data such as bind information and runtime 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 itself divided into two areas whose lifetimes are different: The persistent area, which contains, for example, bind information. It is freed only when the cursor is closed. The run-time area, which is freed when the execution is terminated

Cursors and SQL Areas

The application developer of an Oracle precompiler program or OCI program can explicitly open cursors, or handles to specific private SQL areas, and use them as a named resource throughout the execution of the program. Recursive cursors that Oracle issues implicitly for some SQL statements also use shared SQL areas. The management of private SQL areas is the responsibility of the user process. The allocation and deallocation of private SQL areas depends largely on which application tool you are using, although the number of private SQL areas that a user process can allocate is always limited by the initialization parameter OPEN_CURSORS. The default value of this parameter is 50

PGA Memory Management for Dedicated Mode

You can automatically and globally manage the size of SQL work areas. The database administrator simply needs to specify the total size dedicated to PGA memory for the Oracle instance by setting the initialization parameter PGA_AGGREGATE_TARGET. The specified number (for example, 2G) is a global target for the Oracle instance, and Oracle tries to ensure that the total amount of PGA memory allocated across all database server processes never exceeds this target.

Related Post :- 

• Introduction to an Oracle Instance 
• System Global Area (SGA)
• Dynamic SGA

Read More

SGA_TARGET Initialization Parameter

SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:

• Buffer cache (DB_CACHE_SIZE)
• Shared pool (SHARED_POOL_SIZE)
• Large pool (LARGE_POOL_SIZE)
• Java pool (JAVA_POOL_SIZE)
• Streams pool (STREAMS_POOL_SIZE)

If these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

The following pools are manually sized components and are not affected by Automatic Shared Memory Management:

• Log buffer
• Other buffer caches, such as KEEP, RECYCLE, and other block sizes
• Fixed SGA and other internal allocations

The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.

Property                         Description

Parameter type                Big integer

Syntax  SGA_TARGET     integer [K | M | G]

Default value                     0 (SGA autotuning is disabled

Read More

SGA_MAX_SIZE Initialization Parameter

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:

Parameter                         Description

DB_CACHE_SIZE              The size of the cache of standard blocks.

LOG_BUFFER                    The number of bytes allocated for the redo log buffer.

SHARED_POOL_SIZE      The size in bytes of the area devoted to shared SQL and PL/SQL statements.

LARGE_POOL_SIZE         The size of the large pool; the default is 0.

JAVA_POOL_SIZE            The size of the Java pool.

Related Post :-

• System Global Area (SGA)
• Program Global Area (PGA)
• SGA_TARGET initialization Parameter  

Read More

Dynamic SGA and SGA Granules

A dynamic SGA implements an infrastructure that allows the SGA configuration to change without shutting down the instance. With the dynamic SGA infrastructure, the size of the buffer cache, the shared pool, the large pool, and the process-private memory can be changed without shutting down the instance. Oracle can start instances underconfigured and allow the instance to use more memory by growing the SGA components, up to a maximum of SGA_MAX_SIZE. If SGA_MAX_SIZE specified in the initialization parameter file is less than the sum of all components specified or defaulted at initialization time, then the setting in the initialization parameter file is ignored.

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, because 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 most affect SGA size:

DB_CACHE_SIZE: The size of the cache of standard blocks.

LOG_BUFFER: The number of bytes allocated for the redo log buffer cache.

SHARED_POOL_SIZE: The size in bytes of the area devoted to shared SQL and PL/SQL.

LARGE_POOL_SIZE: The size of the large pool; the default is zero

JAVA_POOL_SIZE  :- The size of the Java pool.

Dynamic SGA Granules

With dynamic SGA, the unit of allocation is called a granule. Components, such as the buffer cache, the shared pool, the java pool, and the large pool, allocate and free SGA space in units of granules. Oracle tracks SGA memory use in integral numbers of granules, by SGA component. All information about a granule is stored in a corresponding granule entry. Oracle maintains the state of each granule in the granule entry and the granule type.

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 128 MB, and it is 16 MB for larger SGAs. There may be some platform dependency, for example, on 32-bit Windows NT, the granule size is 8 MB for SGAs larger than 128 MB.

The granule size that is currently being used for SGA can be viewed in the view V$SGA_DYNAMIC_COMPONENTS. The same granule size is used for all dynamic components in the SGA.

If you specify a size for a component that is not a multiple of granule size, then Oracle rounds the specified size up to the nearest multiple. For example, if the granule size is 4 MB and you specify DB_CACHE_SIZE as 10 MB, you will actually be allocated 12 MB.

Related post:

• System Global Area (SGA)
• Program Global Area (PGA)
• SGA_MAX_SIZE Initialization Parameter
  

Read More

System Global Area (SGA)

 The System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle instance. All Oracle processes use the SGA to hold information. The SGA is used to store incoming data and internal control information that is needed by the database. You control the amount of memory to be allocated to the SGA by setting some of the Oracle  initialization parameters.

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 contains the following data structures:

Database buffer cache

Redo log buffer

Shared pool

Java pool

Large pool

Data dictionary cache

Part of the SGA contains general information about the state of the database and the instance, which the background processes need to access; this is called the fixed SGA. 

Related posts:- 

• Introduction to an Oracle Instance 
• Program Global Area (PGA)
• Dynamic SGA

Read More

Instance Memory Structure

The size of these structures affects the performance of the Oracle database server and is controlled by initialization parameters. These initialization parameters can be categorized as memory parameters. When a database is created with DBCA, the memory parameters are automatically set to optimal values based on your specification of the database workload. However, as your database usage expands, you might find it necessary to alter the settings of the memory parameters. Oracle provides alerts and advisors to identify memory sizing problems and to help you determine appropriate values for memory parameters. Oracle’s memory structure consists of two memory areas known as:

• System Global Area (SGA): Allocated at instance startup, and is a fundamental component of   an Oracle Instance

• Program Global Area (PGA): Allocated when the server process is started

Read More

Setting Initial Database ArchiveLog Mode

You set the initial archiving mode as part of database creation in the CREATE DATABASE statement. Usually, you can use the default of NOARCHIVELOG mode at database creation because there is no need to archive the redo information generated by that process. After creating the database, decide whether to change the initial archiving mode. If you specify ARCHIVELOG mode, you must have initialization parameters set that specify the destinations for the archive log files

Changing the Database Archiving Mode

To change the archiving mode of the database, use the ALTER DATABASE statement with the ARCHIVELOG or NOARCHIVELOG clause. To change the archiving mode, you must be connected to the database with administrator privileges (AS SYSDBA).

The following steps switch the database archiving mode from NOARCHIVELOG to ARCHIVELOG:

1) Shut down the database instance

 SHUTDOWN

 An open database must first be closed and any associated instances shut down before you can switch   the database archiving mode. You cannot change the mode from ARCHIVELOG to NOARCHIVELOG if any datafiles need media recovery.

2) Back up the database.

Before making any major change to a database, always back up the database to protect against any problems. This will be your final backup of the database in NOARCHIVELOG mode and can be used if something goes wrong during the change to ARCHIVELOG mode

3) Edit the initialization parameter file to include the initialization parameters that specify the destinations for the archive log files

4) Start a new instance and mount, but do not open, the database.

STARTUP MOUNT

To enable or disable archiving, the database must be mounted but not open.

5) Change the database archiving mode. Then open the database for normal operations.

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

6) Shut down the database.

SHUTDOWN IMMEDIATE

7) Back up the database.

Changing the database archiving mode updates the control file. After changing the database archiving mode, you must back up all of your database files and control file. Any previous backup is no longer usable because it was taken in NOARCHIVELOG mode.

Read More

ArchiveLog Mode

When you run a database in ARCHIVELOG mode, you enable the archiving of the redo log. The database control file indicates that a group of filled redo log files cannot be reused by LGWR until the group is archived. A filled group becomes available for archiving immediately after a redo log switch occurs.

The archiving of filled groups has these advantages:

•  A database backup, together with online and archived redo log files, guarantees that you can recover all committed transactions in the event of an operating system or disk failure. 
• If you keep an archived log, you can use a backup taken while the database is open and in normal system use.
• You can keep a standby database current with its original database by continuously applying the original archived redo logs to the standby.

You can configure an instance to archive filled redo log files automatically, or you can archive manually. For convenience and efficiency, automatic archiving is usually best. 

If all databases in a distributed database operate in ARCHIVELOG mode, you can perform coordinated distributed database recovery. However, if any database in a distributed database is in NOARCHIVELOG mode, recovery of a global distributed database (to make all databases consistent) is limited by the last full backup of any database operating in NOARCHIVELOG mode

Related Post :- Setting the Initial Database Archiving Mode

Read More

NoArchiveLog Mode

When you run your database in NOARCHIVELOG mode, you disable the archiving of the redo log. The database control file indicates that filled groups are not required to be archived. Therefore, when a filled group becomes inactive after a log switch, the group is available for reuse by LGWR.

NOARCHIVELOG mode protects a database from instance failure but not from media failure. Only the most recent changes made to the database, which are stored in the online redo log groups, are available for instance recovery. If a media failure occurs while the database is in NOARCHIVELOG mode, you can only restore the database to the point of the most recent full database backup. You cannot recover transactions subsequent to that backup.

In NOARCHIVELOG mode you cannot perform online tablespace backups, nor can you use online tablespace backups taken earlier while the database was in ARCHIVELOG mode. To restore a database operating in NOARCHIVELOG mode, you can use only whole database backups taken while the database is closed. Therefore, if you decide to operate a database in NOARCHIVELOG mode, take whole database backups at regular, frequent intervals.

Read More

NoArchiveLog Mode and ArchiveLog Mode

You set the initial archiving mode as part of database creation in the CREATE DATABASE statement. Usually, you can use the default of NOARCHIVELOG mode at database creation because there is no need to archive the redo information generated by that process. After creating the database, decide whether to change the initial archiving mode.

If you specify ARCHIVELOG mode, you must have initialization parameters set that specify the destinations for the archive log files.

This section describes the issues you must consider when choosing to run your database in NOARCHIVELOG or ARCHIVELOG mode, and contains these topics:

• Running a Database in NOARCHIVELOG Mode
• Running a Database in ARCHIVELOG Mode

Read More

Archived Redo Logs Files

What Is the Archived Redo Log?

Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log, or more simply the archive log. The process of turning redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode. You can choose automatic or manual archiving.

An archived redo log file is a copy of one of the filled members of a redo log group. It includes the redo entries and the unique log sequence number of the identical member of the redo log group. For example, if you are multiplexing your redo log, and if group 1 contains identical member files a_log1 and b_log1, then the archiver process (ARCn) will archive one of these member files. Should a_log1 become corrupted, then ARCn can still archive the identical b_log1. The archived redo log contains a copy of every group created since you enabled archiving.

When the database is running in ARCHIVELOG mode, the log writer process (LGWR) cannot reuse and hence overwrite a redo log group until it has been archived. The background process ARCn automates archiving operations when automatic archiving is enabled. The database starts multiple archiver processes as needed to ensure that the archiving of filled redo logs does not fall behind.

You can use archived redo logs to:

Recover a database

Update a standby database

Get information about the history of a database using the LogMiner utility


Related Post: NOARCHIVELOG and ARCHIVELOG Mode

Read More

Logical Storage Structure

Oracle Database allocates logical space for all data in the database. The logical units of database space allocation are data blocks, extents, segments, and tablespaces. At a physical level, the data is stored in data files on disk. The data in the data files is stored in operating system blocks.

The logical parts of the Oracle database are those structures within Oracle that determine where in the physical structures your table and index data reside. Since at the core of every operating system are mechanisms to access the physical data block defined on disk, Oracle must also have a logical structure that will relate down to that data block as your tables are created within the data files and as they grow over time. This is done by three logical structures that are kept for every object created in Oracle. These three structures are data blocks, extents, and segments.


Related Video:- Oracle DBA - Logical Database Structure

Read More

Physical Storage Structure

The physical structure of an Oracle database is determined by the operating system files that provide the actual physical storage for database information.One characteristic of an RDBMS is the independence of logical data structures such as tables, views, and indexes from physical storage structures. Because physical and logical structures are separate, you can manage physical storage of data without affecting access to logical structures. For example, renaming a database file does not rename the tables stored in it. An Oracle database is a set of files that store Oracle data in persistent disk storage.The physical structure of an Oracle database includes only three types of files: control files, data files, and redo log files

Data files

A data file is a physical file on disk that was created by Oracle Database and contains data structures such as tables and indexes. A temp file is a data file that belongs to a temporary tablespace. The data is written to these files in an Oracle proprietary format that cannot be read by other programs.

Control files

A control file is a root file that tracks the physical components of the database.

Online redo log files

The online redo log is a set of files containing records of changes made to data.

Read More

Database Storage Structure

An Oracle database is made up of physical and logical structures. Physical structures are those that can be seen and operated on from the operating system, such as the physical files that store data on a disk. Logical structures are created and recognized by Oracle Database and are not known to the operating system. The primary logical structure in a database, a tablespace, contains physical files. The applications developer or user may be aware of the logical structure, but is not usually aware of this physical structure. The DBA must understand the relationship between the physical and logical structures of a database.

Read More

Oracle Database

Oracle introduced the first commercial Relational Database Management System (RDBMS) almost 30 years ago. Oracle database consists 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.

The general purpose of a database is to store and retrieve related information. An Oracle database has a logical and a physical structure. The physical structure of the database is the set of operating system files in the database. An Oracle database consists of three file types.

• Data files containing the actual data in the database
• Redo logs containing a record of changes made to the database to enable recovery of     the data in case of failures
• Control files containing information necessary to maintain and verify database integrity Other Key File Structures

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

• The parameter file defines the characteristics of an Oracle instance. For example, it contains parameters that size some of the memory structures in the SGA.
• The password file authenticates users privileged to start up and shut down an Oracle instance.
• Archived redo log files are offline copies of the redo log files that may be necessary to recover from media failures.

Related video:- Introduction to Oracle Database Administration - Oracle DBA

Read More

How to Become a Database Administrator

While searching jobs some people go for database administrator but not many people know how to become a database administrator. When we talk about a particular job, it is necessary to know what the job is about and its prospect in future. Before the implantation of computers and laptops in one word electronic gadgets, data of office, schools, industry, factory and other places were recorded in note books. This particular process was quite length and time consuming......

Even it was very difficult when one required to a particular data. The individual has to go through considerable amount of sheets in order to one data. Lots of places were also required to keep them in files. Moreover, the entire process was time consuming and quite boring also. For the particular work quite a number of persons were needed to be appointed.

Read More

Database Adminstrator

In this new era of information technology, data and information is central and a key aspect of any company’s business activities. This data and information must be logged, backed-up, protected but most importantly easily available for staff to use to make crucial business decisions. As a result, there must be people in charge of managing this data and these people are called database administrators.

A database administrator (short form DBA) is a person responsible for the design, implementation, maintenance and repair of an organization's database. They are also known by the titles Database Coordinator or Database Programmer, and is closely related to the Database Analyst, Database Modeller, Programmer Analyst, and Systems Manager. The role includes the development and design of database strategies, monitoring and improving database performance and capacity, and planning for future expansion requirements. They may also plan, co-ordinate and implement security measures to safeguard the database.

Read More

Introduction to an Oracle Instance

Every running Oracle database is associated with an Oracle instance. When a database is started on a database server (regardless of the type of computer), Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle instance. The memory and processes of an instance manage the associated database's data efficiently and serve the one or multiple users of the database.

An instance is the (executed) Oracle software (Oracle processes) and the memory they use (SGA). It is the instance that manipulates the data stored in the database. An instance is identified by using methods specific to each operating system. The instance can open and use only one database at a time.

Related Post:-

• Instance Memory Structure 
• Background Process
• Initialization Parameter Files 

Read More

Database Management System (DBMS)

DBMS means "Database Management System."A DBMS is a set of software programs that controls the system organization, storage, management, and retrieval of data in a database. DBMSs are categorized according to their data structures or types. The DBMS accepts requests for data from an application program and instructs the operating system to transfer the appropriate data. The queries and responses must be submitted and received according to a format that conforms to one or more applicable protocols. When a DBMS is used, information systems can be changed more easily as the organization's information requirements change.A DBMS allows different user application programs to concurrently access the same database. DBMSs may use a variety of database models, such as the relational model or object model, to conveniently describe and support applications.

Well known DBMSs include Oracle, IBM DB2, Microsoft SQL Server, PostgreSQL, MySQL and SQLite. A database is not generally portable across different DBMS, but different DBMSs can inter-operate to some degree by using standards like SQL and ODBC to support together a single application. A DBMS also needs to provide effective run-time execution to properly support (e.g., in terms of performance, availability, and security) as many end-users as needed.

Many DBA's think that RDBMS is a Client Server Database system but thats not the case with RDBMS.

Yes you can say DBMS does not impose any constraints or security with regard to data manipulation it is user or the programmer responsibility to ensure the ACID PROPERTY of the database whereas the rdbms is more with this regard bcz rdbms define the integrity constraint for the purpose of holding ACID PROPERTY.

Read More

What is Database

A database is a collection of information that is organized so that it can easily be accessed, managed, and updated.Databases are designed to offer an organized mechanism for storing, managing and retrieving information. They do so through the use of tables.It allows organizations to conveniently develop databases for various applications by database administrators (DBAs) and other specialists.The term "database" may be narrowed to specify particular aspects of organized collection of data and may refer to the logical database, to physical database as data content in computer data storage or to many other database sub-definitions.The term database is correctly applied to the data and their supporting data structures, and not to the database management system (referred to by the acronym DBMS). The database data collection with DBMS is called a database system.The utilization of databases is now spread to such a wide degree that virtually every technology and product relies on databases and DBMSs for its development and commercialization, or even may have such embedded in it. Also, organizations and companies, from small to large, heavily depend on databases for their operations

Read More