11g New Features

This is the most frequently asked interview Question in DBA Interviews that What are the 11g  New  Features ? 

Below are the 11g New Features :

·                    Automatic Memory Tuning

            -Automatic PGA tuning was introduced in Oracle 9i.

            -Automatic SGA tuning was introduced in Oracle 10g.

            -In 11g, all memory can be tuned automatically by setting one parameter. You literally tell Oracle how much memory it has and it determines how much to use for PGA, SGA and OS Processes. Maximum and minimum thresholds can be set.

·         Database Capture/replay database workloads

               Allows the total database workload to be captured, transferred to a test database created from a backup or standby database then replayed to test the affects of an upgrade or system change.

·         Index can be rebuild ‘online’

 Online index build with NO pause to DML.

·         RMAN By-Pass UNDO

Rman backup can bypass undo. Undo tablespaces are getting huge, but contain lots of useless information. So, RMAN can bypass those types of tablespace. Beneficial for exporting a tablespace from backup.:-  Source: Laurent

·         Tables can be made read only table :-    

E.g ;

SQL> alter table <table_name> read only

SQL>  alter table <table_name> read write

·         Case sensitive password

Init.ora parameter

sec_case_sensitive_logon = (TRUE FALSE)

To Find users who have case sensitive or case insensitive passwords, Use below queries

SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS

Implementation in Password file

$ orapwd file=orclpwd password=manager ignorecase=y

·         Passwords are expected to also become case sensitive

            In addition to limiting the number of failed login attempts to 10 (the default configuration in 10gR2), Oracle 11g beta’s planned default settings provide a feature to expire passwords every 180 days, and limit to seven the number of times a user can login with an expired password before disabling access.

·         ADR ( Automatic Diagnostic Repository)

            Oracle 11g introduce new tool/utility called ADRCI known as ADR command line tool. This tool allow user to interact with ADR , check alert log, check health monitor(HM) status. It is just a file based repository of diagnostic data. ADR gives lot flexibility to maintain / handle diagnostic data.

             

            When critical errors(i.e Problem) are detected, they automatically create an “incident” ( one occurrence of problem is Incident i.e relation between problem and incident is one to many).

            Information(trace ,dump ) related to the incident is automatically captured in file based repository known as ADR ( Automatic Diagnostic repository), all the incident related files are tagged with Incident Number and certain health checks are run automatically. This information can be packaged to be sent to Oracle support

$ adrci

adrci> show alert ( it will open alert in vi editor )

adrci> show alert -tail ( Similar to Unix tail command )

adrci> show alert -tail 200 ( Similar to Unix Command tail -200 )

adrci> show alert -tail -f ( Similar to Unix command tail -f )

 

RMAN New Features (11g)

·         Active Database Duplication

            We can create a duplicate or standby database over the network without taking backup or using old backup.

·         Configuring backup compression (Compression Algorithm)

            In 11g  can use CONFIGURE command to choose between the BZIP2 and ZLIB compression algorithms for RMAN backups.

·         Block Change Tracking (BCT)  Faster incremental backups on Physical Standby DB’s :

            In 11g we can enable block change tracking on a physical standby database (ALTER DATABASE ENABLE/DISABLE BLOCK CHANGE TRACKING).This new 11g  feature enables faster Incremental backups on a physical standby database than in previous releases. Because RMAN identify  the changed blocks since the last incremental backup.

·         Parallel backup and restore for Huge size files

            RMAN Backups of large sized data files now use multiple parallel server processes to efficiently distribute the workload for each file.

            This features improves the performance of backups. 

Data Guard Enhancements , "Active Data Guard"

1) Data Protection

    * Advanced Compression

    * Lost-write protection

    * Fast-Start Failover

2) Increase ROI

    * Active Data Guard

    * Snapshot Standby

3) High Availability

    * Faster Redo Apply

    * Faster failover & switchover

    * Automatic Failover using ASYNC