Top new features in Oracle 11g

Database Replay

Explore Database Replay, the new tool that captures SQL statements and lets you replay them at will.

Partitioning

Learn about Referential, Internal, and Virtual Column partitioning; new sub-partitioning options; and more.

Edition-Based Redefinition

It's revolutionary: Patch or update your application's data objects while the application remains in uninterrupted use (in Release 2 only).

Schema Management

Add columns with a default value easily and explore invisible indexes, virtual columns, and read only tables.

Patching and Upgrades, RAC One Node, and Clusterware

Learn how to enable a single name for the cluster, enable HA for a single-instance database, place OCR and voting disks on ASM, and more (in Release 2 only).

Data Warehousing and OLAP

Get a tour of new features in these areas, including Cube Organized MVs, a new Analytic Workspace Manager, Query Rewrite extended to subqueries and remote tables, and more.

PL/SQL Performance

Explore in-lining of code, "real" native compilation, PLS timer, use of simple integer, and more.

PL/SQL: Efficient Coding

Triggers that fire several times at different events and ability to force triggers of the same type to follow a sequence are some new gems.

Transaction Management

Get an introduction to Flashback Data Archive and explore Enterprise Manager's LogMiner interface.

Security

Learn about Tablespace Encryption, case-sensitive passwords, data masking, and other features.

SQL Performance Analyzer & Real-Time SQL Monitoring

Learn how to accurately predict changes on the actual SQL statements issued against your database - plus, monitor SQL performance in real time.

Oracle Exadata Simulator

Predict how your statements will behave in Oracle Exadata Database Machine using any Oracle Database 11g Release 2 EE database (using SQL Performance Analyzer).

SQL Plan Management

Use bind variables that pick the right plan every time and ensure a new execution plan is perfect before it's used.

Manageability

Explore automatic memory management, multicolumn statistics, online patching, and more features.

SQL Access Advisor

Get advice about optimal table design based on actual use of the table, not just data.

SQL Operations: Pivot and Unpivot

Present information in a spreadsheet-type crosstab report from any relational table using simple SQL, and store any data from a crosstab table to a relational table.

Backup and Recovery

Explore Data Recovery Advisor, do parallel backup of the same file, create and manage virtual catalogs, and undrop tablespaces.

Resiliency

Explore Automatic Health Monitor, Automatic Diagnostic Repository, and other new resiliency features.

Automatic Storage Management

Learn about new SYSASM role, variable extent sizes, and other ASM improvements.

Compression

Support for data compression is nothing new in Oracle Database, but 11g takes the concept to a new level with Advanced and Hybrid Columnar Compression.

Caching and Pooling

Explore SQL Result Cache, PL/SQL Function Cache, and Database Resident Connection Pooling.

Data Guard

Query the physical standby database in real time without shutting down recovery, just for starters.

SecureFiles

Explore next-generation LOBs: LOB encryption, compression, deduplication, and asynchronicity.

data movement.

COPY command, Export/Imports, Data Pump and SQL*Plus improvements, Scheduler email notification, and more.

CPU Used By This Instance

CPU Used By The Instance

(Tested on an Oracle 10 database.)

The "CPU used by this session" statistic in V$SYSSTAT shows how much CPU time the database has used since it was started. It is recorded in hundredths of a second. First set timed_statistics to false. This stops Oracle incrementing the statistic:

SQL> alter system set timed_statistics = false

  2  /

System altered.

SQL> col value format a10

SQL> l

  1  select value from v$parameter

  2* where name = 'timed_statistics'

SQL> /

VALUE

----------

FALSE

SQL>

Check the starting value:

SQL> l

  1  select value/100 SECONDS from v$sysstat

  2* where name = 'CPU used by this session'

SQL> /

   SECONDS

----------

  14756.13

SQL>

Do some work:

SQL> select count(*) from dba_objects;

  COUNT(*)

----------

     44734

SQL>

Check the statistic again. It will not have changed:

SQL> select value/100 SECONDS from v$sysstat

  2  where name = 'CPU used by this session';

   SECONDS

----------

  14756.13

SQL>

Now set timed_statistics to true:

  1* alter system set timed_statistics = true

SQL> /

System altered.

SQL>

Check the starting value again. It has already gone up a little:

  1  select value/100 SECONDS from v$sysstat

  2* where name = 'CPU used by this session'

SQL> /

   SECONDS

----------

  14756.35

SQL>

Do some work again:

SQL> select count(*) from dba_objects;

  COUNT(*)

----------

     44734

SQL>

Check the statistic again. This time it will have increased:

SQL> select value/100 SECONDS from v$sysstat

  2  where name = 'CPU used by this session';

   SECONDS

----------

  14756.96

SQL>

Sync standby database with production using RMAN with missing archive log at primary

Sync standby database with production using RMAN with missing archive log at primary

There are scenarios when physical standby database is out of sync and the required archive file is missing from production server. In such cases we can use RMAN to recover standby database to any SCN.  This is one of the way to sync the standby database with production.

Bring standby database in sync with Primary Database using RMAN. Steps as follows

1. Get current SCN of standby database.

select current_scn from v$database;

CURRENT_SCN

———–

5793471

2. Take incremental backup from last sequence change at standby (standby SCN from above query) using RMAN

rman target  sys/pass

RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 5793471 DATABASE FORMAT ‘C:\temp\rmanSCN%U’;

3. create a new standby controlfile from production

sqlplus> alter database create standby crontrolfile ‘c:\stdbyscn.ctl’;

4. cancel managed recovery on standby

sqlplus> recover managed standby database cancel;

5. copy incremental backup  and standby control file to standby server server (path c:\rmanbkp) and register it to standby control file

rman target  sys/syspwd

rman> catalog start with ‘c:\rmanbkp’;

6. once registed with standby control file recover  standby database  from the incremental backup using RMAN with noredo (with noredo option recovery process will recover from incremental backup instead of redolog and archivelog)

rman> recover database noredo;

7. shutdown the standby and replace the controlfile with backed up before  

8. mount the database and put  it in managed recovery mode

sqlplus> startup mount

sqlplus> recover managed standby database disconnect;

9.  Verify whether scn of production and standby is matching  

Oracle Database Editions

Oracle Database Editions

Oracle Database Editions 
Oracle Corporation subdivides its product into varying "editions" - apparently for marketing and license-tracking reasons. In approximate order of decreasing scale, the editions are listed below.


1)Enterprise Edition (EE): Includes more features than the 'Standard Edition', especially in the areas of performance and security. Oracle Corporation licenses this product on the basis of users or of processors, typically for servers running 4 or more CPUs. EE has no memory limits, and can utilize clustering using Oracle RAC software.


2)Standard Edition (SE): Contains base database functionality. Oracle Corporation licenses this product on the basis of users or of processors, typically for servers running from one to four CPUs. If the number of CPUs exceeds 4 CPUs, the user must convert to an Enterprise license. SE has no memory limits, and can utilize clustering with Oracle RAC at no additional charge.

3)Standard Edition One: Introduced with Oracle 10g, has some additional feature-restrictions. Oracle Corporation markets it for use on systems with one or two CPUs. It has no memory limitations.

4)Express Edition: ('Oracle Database XE'), introduced in 2005, offers Oracle 10g free to distribute on Windows and Linux platforms (with a footprint of only 150 MB and restricted to the use of a single CPU, a maximum of 4 GB of user data and 1 GB of memory). Support for this version comes exclusively through on-line forums and not through Oracle support.

5)Oracle Personal Edition: Provides the functionality of the "high end" Enterprise Edition but marketed to (and licensed for) single-user developers working on personal workstations.

6)Oracle Database Lite: Intended to run on mobile devices. The database, partially located on the mobile device, can synchronize with a server-based installation.

Oracle Database Startup and Shutdown Process

Oracle Database Startup / Shutdown process

Starting/shutting down a oracle database seems very simple for new DBA’s, just connect and put “startup” or “shutdown immediate” command, but a thorough understanding of its stages is needed to be able to troubleshoot startup and shutdown related issues. It is mandatory for any Oracle DBA to have proper understanding of startup and shutdown process.

Starting up Oracle Database:

 To start a oracle database you need to login as “sysdba”, on “startup” command found in sql prompt it search for parameter file (spfile) to get configuration of oracle instance. The path of parameter file located by referring environment variable on the system (ORACLE_HOME, ORACLE_SID..). Oracle Database startup process consists of three stages.

Stage 1: NOMOUNT      => startup nomount

Stage 2: MOUNT           => startup mount

Stage 3: OPEN             => startup

When we fire “startup” command once connected as sysdba, in the background above three stages are followed to open the database.

 $sqlplus / as sysdba

 SQL>startup

ORACLE instance started.

Total System Global Area  732352512 bytes

Fixed Size            1339036 bytes

Variable Size          440402276 bytes

Database Buffers      285212672 bytes

Redo Buffers            5398528 bytes

Database mounted.

Database opened.                                                                                                                                                                                          

As you can see it the startup command automatically takes the database in Open mode. However some operations, like restores and recovers require decomposing the startup process. In this case switching up from one mode to the next one is done using an "alter database" command.

Three stages and process of bringing oracle database up is described as follows.

1.     “Nomount” Stage:

This is the first stage in the startup process.  You can start Oracle database in nomount mode using the command

SQL>startup nomount

ORACLE instance started.

Total System Global Area  732352512 bytes

Fixed Size            1339036 bytes

Variable Size          440402276 bytes

Database Buffers      285212672 bytes

Redo Buffers            5398528 bytes

When you execute the above command, an Oracle instance is started. When instance starts it will read the initialisation file (commonly known as parameter file) called init.ora file. From this parameter file the instance will know about the size of SGA, PGA, database buffer size and other configurable parameters. The instance will also start the Oracle background process such as (PMON, SMON, LGWR etc). This stage also opens the alert log and the trace files.

Check the running process

oracle@vmoratest1:/home/oracle/ [DBC] ps -ef |grep DBC

oracle    3999     1  0 09:12 ?        00:00:00 ora_pmon_DBC

oracle    4001     1  0 09:12 ?        00:00:00 ora_vktm_DBC

oracle    4005     1  0 09:12 ?        00:00:00 ora_gen0_DBC

oracle    4007     1  0 09:12 ?        00:00:00 ora_diag_DBC

oracle    4009     1  0 09:12 ?        00:00:00 ora_dbrm_DBC

oracle    4011     1  0 09:12 ?        00:00:00 ora_psp0_DBC

oracle    4013     1  0 09:12 ?        00:00:00 ora_dia0_DBC

oracle    4015     1  1 09:12 ?        00:00:00 ora_mman_DBC

oracle    4017     1  0 09:12 ?        00:00:00 ora_dbw0_DBC

oracle    4019     1  0 09:12 ?        00:00:00 ora_lgwr_DBC

oracle    4021     1  0 09:12 ?        00:00:00 ora_ckpt_DBC

oracle    4023     1  0 09:12 ?        00:00:00 ora_smon_DBC

oracle    4025     1  0 09:12 ?        00:00:00 ora_reco_DBC

oracle    4027     1  0 09:12 ?        00:00:00 ora_mmon_DBC

oracle    4029     1  0 09:12 ?        00:00:00 ora_mmnl_DBC

oracle    4031     1  0 09:12 ?        00:00:00 ora_d000_DBC

oracle    4033     1  0 09:12 ?        00:00:00 ora_s000_DBC

oracle    4106  3356  0 09:12 pts/1    00:00:00 grep DBC

Instance in NOMOUNT mode, it is only possible to access and interact to the spfile information

SQL> show parameter name

NAME                                                         TYPE       VALUE

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

db_file_name_convert                         string

db_name                                                string         ULTHAN

db_unique_name                                 string        ULTHAN_SITE1

global_names                                        boolean    FALSE

instance_name                                    string          DBC

lock_name_space                                 string

log_file_name_convert                        string

service_names                                      string         DBC.it.dhu-domain.com

SQL> show parameter spfile;

NAME                       TYPE       VALUE

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

spfile                         string     /u00/app/oracle/admin/DBC/pfile/spfileDBC.ora

To access some instance views

SQL> select INSTANCE_NAME,HOST_NAME,STATUS from v$instance;

INSTANCE_NAME      HOST_NAME                         STATUS

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

DBC                        vmoratest1.it.dhu-domain.com      STARTED

2.      “Mount” Stage:

The next stage after NOMOUNT is called MOUNT. You can manually start an Oracle database in MOUNT stage using the command

SQL>startup mount

Or when database is already in nomount stage then you can change the stage by running the command

SQL>alter database mount;

When database goes into mount stage, it will read the control files specified in the parameter file. The control files contain the information about the physical structure of the database. So the control file will have the names and locations of all the datafiles and online redo log files. At this stage these datafiles and log files are not opened.

SQL> show parameter control_files

NAME                TYPE          VALUE
------------------- ------------- ----------------------------------------------------
control_files       string        /u01/oradata/DBC/control01DBC.dbf, /u02/oradata
                                              /DBC, /control02DBC.dbf,/u03/oradata/DBC  
                                             /control03DBC.dbf

Some DBA activities and maintenance can only be performed when the Oracle database is MOUNT stage. For example Oracle full database recovery can be done only when the database is in mount stage. If you want to rename a datafile you may need to take the database to mount stage unless the tablespace of the datafile is already offline.

SQL> select open_mode from v$database;

OPEN_MODE

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

MOUNTED

SQL> select name from v$datafile;

NAME

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

/u01/oradata/DBC/system01DBC.dbf

/u01/oradata/DBC/sysaux01DBC.dbf

/u01/oradata/DBC/undotbs01DBC.dbf

/u01/oradata/DBC/users01DBC.dbf

SQL>select member from v$logfile;

MEMBER

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

/u01/oradata/DBC/redog1m1DBC.dbf

/u02/oradata/DBC/redog1m2DBC.dbf

/u01/oradata/DBC/redog2m1DBC.dbf

/u02/oradata/DBC/redog2m2DBC.dbf

/u01/oradata/DBC/redog3m1DBC.dbf

/u02/oradata/DBC/redog3m2DBC.dbf

6 rows selected.

3.     “Open” Stage:

The final stage in the Oracle startup process. When the database is open then only normal database operations can takes place. Which means users and applications can login and start reading/writing data.

Running the command below will start the Oracle database and put into OPEN stage.  

SQL>startup

And if the database is already in MOUNT stage then you can open the database using the command

SQL> alter database open;

Database altered.

SQL> select status,open_mode from v$instance,v$database;

STATUS         OPEN_MODE
-------------- --------------------
OPEN           READ WRITE

You can see above that database is opened in READ WRITE Mode , Database can also be opened in some special mode for specific purpose by DBA, namely Restricted, readonly.

SQL> startup restrict

ORACLE instance started.

Total System Global Area  732352512 bytes

Fixed Size            1339036 bytes

Variable Size          440402276 bytes

Database Buffers      285212672 bytes

Redo Buffers            5398528 bytes

Database mounted.

Database opened.

&

SQL> alter database open read only;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ ONLY

Shutting down Oracle Database:

There are four methods available to shutdown the oracle database:

• Normal Shutdown
• Shutdown transactional
• Shutdown Immediate
• Shutdown Abort

1. Normal Shutdown

During normal shutdown no new user connection will be allowed, before the oracle database is shut down, oracle will wait for all active users to finish its work, use this option to shutdown the database under normal conditions. This mode of shutdown may take minutes or hours.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

2. Shutdown Transactional

During Shutdown Transactional no new user connections are permitted and existing sessions that are not involved in active transactions will be terminated. However sessions currently involved in a transaction are allowed to complete the transaction and will then be terminated. Once all sessions are terminated, the database will shutdown.

3. Shutdown Immediate

During immediate shutdown, before the oracle database is shut down, oracle will rollback active transaction and disconnect all active users. Use this option when there is a problem with your database and you don’t have enough time to request users to log-off.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

4. Shutdown Abort

shutdown abort is simply oracle instance crash, before the oracle database is shutdown, all user sessions will be terminated immediately. Uncomitted transactions will not be rolled back. Use this option only during emergency situations when the “shutdown” and “shutdown immediate” doesn’t work.

After a shutdown abort, the SMON process will have to perform an instance recovery at next startup.

$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 18 11:11:33 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> shutdown abort

ORACLE instance shut down.

Oracle background processes.

An Oracle instance consists of many background processes; not all the processes running all the time; only mandatory process is running continuously and other processes may spawn on demand or the configuration of Oracle instance. With each release oracle had some new background process added with some new features.
You can view the background processes running on your system by issuing the following SQL query

SELECT * FROM V$BGPROCESS
WHERE PADDR != '00'
ORDER BY NAME;

The common background processes in an Oracle instance include the following:

Mandatory
· Database Writer (DBWR)
· Log Writer (LGWR)
· Checkpoint (CKPT)
· System Monitor (SMON)
· Process Monitor (PMON)
Optional
· Archiver (ARCn)
· Recoverer (RECO)
· Queue Time Manager Process (QMNn)
· Dispatcher (Dnnn) (Present when MST/Shared Server configuration is used.)
· shared server processe (Snnn)
· Coordinator Job Queue Process (CJQ)
· Parallel Query Slaves(Pnnn)
· Wakeup Monitor process(WMON)
· File Mapping Process (FMON) (FMON is a background process started by Oracle file_mapping is set to TRUE)
· Trace writer (TRWR)
· Input/output Slaves(Innn)
· Job queue process (Jnnn)
· event monitor coordinator(EMNC)
· Block server Process (BSPn)

Oracle10g introduced new background processes.
· Manageability Monitor (MMON also called as Memory Monitor).
· Manageability Monitor Light (MMNL)
· Memory Manager (MMAN)
· Recovery Writer process (RVWR)
· Change Tracking Writer (CTWR)
· MMON background slave (m000) processes.

Data guard/stream related Background processes
· Data Guard Broker process ( DMON).
· Managed recovery process(MRP)
· Remote File Server process(RFS)
· Queue Monitor Process (QMNC) (monitors message queue and used by oracle stream advance queueing)
· Logical standby process (LSP)
· DR Resource Manager Process(RSMn)
· DR Server NetSlave or dataguard Net server Process(NSVn)
· DR Partner Process (DRCn) (Partner process of NSV process at standby)
· The snapshot process (SNP) (obsolete in 10g and above).
· communicates with the Automatic Storage Management instance (OSMB).
· data extent rebalance in an Automatic Storage Management instance (ORBn).

Oracle ASM related background process.
· Re-balancer (RBAL)
· Re-balancer child (ARBx)
· ASM Bridge process (ASMB)

Oracle RAC related background process
· Diagnosability Daemon (DIAG)
· process manages the global enqueue requests and the cross-instance broadcast (LCKx)
· Global Enqueue Service Monitor (LMON)
· Global Enqueue Service Daemon (LMDx)
· Global Cache Service Processes (LMSx)
· Global transaction processes(GTX0-j) (provide transparent support for XA global transactions in an Oracle RAC environment)
· atomic control file to memory service(ACMS)

Oracle11g introduced new background processes.
Oracle 11g introduced about 56 bg processes, some mandatory processes are
· DB resource manager(DBRM)
· Virtual Timekeeper (VKTM)
· Flashback data archiver process(FBDA)
· Diagnosability process (DIA0)
· Space Management Co-ordination process (w000)
· Space Management coordinator process (SMCO)

Next we will discuss the working of these processes…..