Difference between 10g, 11g and 12c

Difference between 10g, 11g and 12c

Oracle Database 10g New Features:

NID utility has been introduced to change the database name and id.

Automated Storage Management (ASM). ASMB, RBAL, ARBx are the new background processes related to ASM.

New memory structure in SGA i.e. Streams pool (streams_pool_size parameter), useful for datapump activities & streams replication.

From Oracle 10g, the spool command can append to an existing one.
SQL> spool result.log append

Ability to rename tablespaces (except SYSTEM and SYSAUX), whether permanent or temporary, using the following command:

SQL> ALTER TABLESPACE oldname RENAME TO newname;

 

Oracle Database 11g New Features:

Ability to mark a table as read only.
SQL> alter table table-name read only;

Temporary tablespace or it's tempfile can be shrinked, up to specified size.
SQL> alter tablespace temp-tbs shrink space;
SQL> alter tablespace temp-tbs shrink space keep n{K|M|G|T|P|E};

Introduction of ADR, single location of all error and trace data in the database, diagnostic_dump parameter

A new parameter is added in 11g, called MEMORY_TARGET, automatic memory management for both the SGA and PGA.

A system privelege is introduced in 11g called SYSASM, and this is granted to users who need to perform ASM related tasks.


 

Oracle Database 12c New Features:

 

Moving and Renaming datafile is now ONLINE, no need to put datafile in offline.
SQL> alter database move datafile 'path' to 'new_path';

No need to shutdown database for changing archive log mode.

Like sysdba, sysoper & sysasm, we have new privileges, in Oracle 12.1.0.
sysbackup for backup operations
sysdg for Data Guard operations
syskm for key management

Patching:
Centralised patching.
We can retrieve OPatch information using sqlplus query, using DBMS_QOPATCH package

SQL> select dbms_qopatch.get_opatch_lsinventory() from dual;

We can test patches on database copies, rolling patches out centrally once testing is complete.

New Commands
create pluggable database ...
alter pluggable database ...
drop pluggable database ...


New background processes - LREG (Listener Registration), SA (SGA Allocator), RM.

Oracle Database 12c Data Pump will allow turning off redo for the import operation (only).
$ impdp ... transform=disable_archive_logging:y

There are lots of differences!

Some of the big ones are:

- Multitenant/pluggable databases
- In Memory (12.1.0.2
- JSON support (12.1.0.2)
- Adaptive queries
- New histogram types
- SQL enhancements (match_recognize, PL/SQL in the with clause, ...) 

Oracle RAC Background Processes

The Background Processes

SMON - System Monitor process recovers after instance failure and monitors temporary segments and extents.  SMON in a non-failed instance can also perform failed instance recovery for other failed RAC instance.

PMON - Process Monitor process recovers failed process resources. If MTS (also called Shared Server Architecture) is being utilized.  PMON monitors and restarts any failed dispatcher or server processes.  In RAC, PMON’s role as service registration agent is particularly important.

DBWR - Database Writer or Dirty Buffer Writer process is responsible for writing dirty buffers from the database block cache to the database data files.  Generally, DBWR only writes blocks back to the data files on commit, or when the cache is full and space has to be made for more blocks.  The possible multiple DBWR processes in RAC must be coordinated through the locking and global cache processes to ensure efficient processing is accomplished.

LGWR - Log Writer process is responsible for writing the log buffers out to the redo logs. I n RAC, each RAC instance has its own LGWR process that maintains that instance’s thread of redo logs.

ARCH - (Optional) Archive process writes filled redo logs to the archive log location(s). In RAC, the various ARCH processes can be utilized to ensure that copies of the archived redo logs for each instance are available to the other instances in the RAC setup should they be needed for recovery.

CKPT - Checkpoint process writes checkpoint information to control files and data file headers.

Pnnn - (Optional) Parallel Query Slaves are started and stopped as needed to participate in parallel query operations.

CQJ0 - Job queue controller process wakes up periodically and checks the job log.  If a job is due, it spawns Jnnn processes to handle jobs.

Jnnn - (Optional) Job processes used by the Oracle9i job queues to process internal Oracle9i jobs. The CQJ0 process controls it automatically.

QMN - (Optional) Advanced Queuing process is used to control the advanced queuing jobs.

Snnn - (Optional) Pre-spawned shared server processes are used by the multi-threaded server (MTS) process to handle connection requests from users, and act as connection pools for user processes.  These user processes also handle disk reads from database datafiles into the database block buffers.

Dnnn - (Optional) Dispatcher process for shared server (MTS) - It accepts connection requests and portions them out to the pre-spawned server processes.

MMON – This process performs various manageability-related background tasks, for example:

• Issuing alerts whenever a given metrics violates its threshold value
   
• Capturing statistics value for SQL objects which have been recently modified

MMNL - This process performs frequent and lightweight manageability-related tasks, such as session history capture and metrics computation.

MMAN - is used for internal database tasks that manage the automatic shared memory. MMAN serves as the SGA Memory Broker and coordinates the sizing of the memory components.

RBAL - This process coordinates rebalance activity for disk groups in an Automatic Storage Management instance.

ORBn - performs the actual rebalance data extent movements in an Automatic Storage Management instance.  There can be many of these at a time, called ORB0, ORB1, and so forth.

OSMB - is present in a database instance using an Automatic Storage Management disk group.  It communicates with the Automatic Storage Management instance.

FMON - The database communicates with the mapping libraries provided by storage vendors through an external non-Oracle Database process that is spawned by a background process called FMON. FMON is responsible for managing the mapping information.  When you specify the FILE_MAPPING initialization parameter for mapping data files to physical devices on a storage subsystem, then the FMON process is spawned.

Oracle RAC instances are composed of following background processes:

ACMS    — Atomic Control file to Memory Service (ACMS)
GTX0-j  — Global Transaction Process
LMON    — Global Enqueue Service Monitor
LMD     — Global Enqueue Service Daemon
LMS     — Global Cache Service Process
LCK0    — Instance Enqueue Process

DIAG    — Diagnosability Daemon
RMSn    — Oracle RAC Management Processes (RMSn)
RSMN    — Remote Slave Monitor
DBRM    — Database Resource Manager (from 11g R2)

PING    — Response Time Agent (from 11g R2)

The GCS and GES processes, and the GRD collaborate to enable Cache Fusion. The Oracle RAC processes and their identifiers are as follows:

1. ACMS: Atomic Controlfile to Memory Service (ACMS)
In an Oracle RAC environment, the ACMS per-instance process is an agent that contributes to ensuring a distributed SGA memory update is either globally committed on success or globally aborted if a failure occurs.

2. GTX0-j: Global Transaction Process
The GTX0-j process provides transparent support for XA global transactions in an Oracle RAC environment. The database autotunes the number of these processes based on the workload of XA global transactions.

3. RMSn: Oracle RAC Management Processes (RMSn)
The RMSn processes perform manageability tasks for Oracle RAC. Tasks accomplished by an RMSn process include creation of resources related to Oracle RAC when new instances are added to the clusters.

4. RSMN: Remote Slave Monitor manages background slave process creation and communication on remote instances. These background slave processes perform tasks on behalf of a coordinating process running in another instance.

LMON - The Global Enqueue Service Monitor (LMON), monitors the entire cluster to manage the global enqueues and the resources and performs global enqueue recovery operations. LMON manages instance and process failures and the associated recovery for the Global Cache Service (GCS) and Global Enqueue Service (GES). In particular, LMON handles the part of recovery associated with global resources. LMON provided services are also known as cluster group services (CGS). Lock monitor manages global locks and resources. It handles the redistribution of instance locks whenever instances are started or shutdown. Lock monitor also recovers instance lock information prior to the instance recovery process. Lock monitor co-ordinates with the Process Monitor (PMON) to recover dead processes that hold instance locks.

LMDx - The Global Enqueue Service Daemon (LMD) is the lock agent process that manages enqueue manager service requests for Global Cache Service enqueues to control access to global enqueues and resources. This process manages incoming remote resource requests within each instance. The LMD process also handles deadlock detection and remote enqueue requests. Remote resource requests are the requests originating from another instance. LMDn processes manage instance locks that are used to share resources between instances. LMDn processes also handle deadlock detection and remote lock requests.

LMSx - The Global Cache Service Processes (LMSx) are the processes that handle remote Global Cache Service (GCS) messages. Real Application Clusters software provides for up to 10 Global Cache Service Processes.  The number of LMSx varies depending on the amount of messaging traffic among nodes in the cluster.

This process maintains statuses of datafiles and each cached block by recording information in a Global Resource Directory(GRD). This process also controls the flow of messages to remote instances and manages global data block access and transmits block images between the buffer caches of different instances. This processing is a part of cache fusion feature.

The LMSx handles the acquisition interrupt and blocking interrupt requests from the remote instances for Global Cache Service resources. For cross-instance consistent read requests, the LMSx will create a consistent read version of the block and send it to the requesting instance. The LMSx also controls the flow of messages to remote instances.

The LMSn processes handle the blocking interrupts from the remote instance for the Global Cache Service resources by:

• Managing the resource requests and cross-instance call operations for the shared resources.
• Building a list of invalid lock elements and validating the lock elements during recovery. 
• Handling the  global lock deadlock detection and Monitoring for the lock conversion timeouts

RAC Specific Processes

LCKx - This process manages the global enqueue requests and the cross-instance broadcast. Workload is automatically shared and balanced when there are multiple Global Cache Service Processes (LMSx). This process is called as instance enqueue process. This process manages non-cache fusion resource requests such as library and row cache requests. The instance locks that are used to share resources between instances are held by the lock processes.

DIAG: Diagnosability Daemon – Monitors the health of the instance and captures the data for instance process failures.

The following shows typical background processes of the RAC instance named NYDB1.

$ rac-1a:NYDB1:/app/home/oracle >ps -ef | grep ora_
oracle   31136     1  0 08:45 ?        00:00:00 ora_pmon_NYDB1
oracle   31138     1  0 08:45 ?        00:00:00 ora_diag_NYDB1
oracle   31141     1  0 08:45 ?        00:00:00 ora_lmon_NYDB1
oracle   31143     1  0 08:45 ?        00:00:04 ora_lmd0_NYDB1
oracle   31145     1  0 08:45 ?        00:00:03 ora_lms0_NYDB1
oracle   31147     1  0 08:45 ?        00:00:03 ora_lms1_NYDB1
oracle   31149     1  0 08:45 ?        00:00:00 ora_mman_NYDB1
oracle   31151     1  0 08:45 ?        00:00:01 ora_dbw0_NYDB1
oracle   31153     1  0 08:45 ?        00:00:01 ora_lgwr_NYDB1
oracle   31155     1  0 08:45 ?        00:00:05 ora_ckpt_NYDB1
oracle   31157     1  0 08:45 ?        00:00:05 ora_smon_NYDB1
oracle   31159     1  0 08:45 ?        00:00:00 ora_reco_NYDB1
oracle   31161     1  0 08:45 ?        00:00:00 ora_cjq0_NYDB1
oracle   31163     1  0 08:45 ?        00:00:00 ora_d000_NYDB1
oracle   31165     1  0 08:45 ?        00:00:00 ora_s000_NYDB1
oracle   31168     1  0 08:45 ?        00:00:02 ora_lck0_NYDB1
oracle   31190     1  0 08:46 ?        00:00:00 ora_arc0_NYDB1
oracle   31193     1  0 08:46 ?        00:00:02 ora_arc1_NYDB1
oracle   31207     1  0 08:46 ?        00:00:00 ora_qmnc_NYDB1
oracle   31210     1  0 08:46 ?        00:00:07 ora_mmon_NYDB1
oracle   31213     1  0 08:46 ?        00:00:00 ora_mmnl_NYDB1
oracle   31286     1  0 08:46 ?        00:00:00 ora_q000_NYDB1
oracle   31288     1  0 08:46 ?        00:00:00 ora_q001_NYDB1
oracle   31290     1  0 08:46 ?        00:00:00 ora_q002_NYDB1
oracle   18041     1  0 20:41 ?        00:00:06 ora_j000_NYDB1
oracle   25579     1  0 23:19 ?        00:00:00 ora_pz99_NYDB1
oracle   25581     1  0 23:19 ?        00:00:00 ora_pz98_NYDB1
oracle   26703 19731  0 23:23 pts/5    00:00:00 grep ora_

 

Oracle RAC instances use two processes GES(Global Enqueue Service), GCS(Global Cache Service) that enable cache fusion. The GES and GCS maintain records of the statuses of each datafile and each cached block using global resource directory (GRD). This process is referred to as cache fusion and helps in data integrity.

 
Oracle RAC is composed of two or more instances. When a block of data is read from datafile by an instance within the cluster and another instance is in need of the same block, it is easy to get the block image from the instance which has the block in its SGA rather than reading from the disk. To enable inter instance communication Oracle RAC makes use of interconnects. The Global Enqueue Service(GES) monitors and Instance enqueue process manages the cache fusion.