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

How to make a cold backup for database running on ASM ( using RMAN )

Many a times, you will come in a situation… Or they may ask you in Interview that how to take a cold backup using RMAN.

You heard Right, Cold backup using RMAN…

Using RMAN Utility, we can perform the backup in MOUNT mode.

Lets see…

Follow the below steps to take cold back of database running on ASM.

1.Start the instances in all the nodes in mount stage.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1.7504E+10 bytes

Fixed Size                  2235448 bytes

Variable Size            4429185992 bytes

Database Buffers         1.3019E+10 bytes

Redo Buffers               53477376 bytes

Database mounted.

2.Connect RMAN from any node and take the backup as below.

[oracle@backupServer ]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon May 2 05:53:43 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SPSHIDMP (DBID=465850406, not open)

RMAN> backup device type disk format '/backupServer/test/rman_backup_43/backup/%U' database plus archivelog;

Starting backup at 02-MAY-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=4566 instance=spshidmp1 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=2 sequence=55 RECID=107 STAMP=783110838

input archived log thread=1 sequence=58 RECID=106 STAMP=783107220

input archived log thread=1 sequence=59 RECID=108 STAMP=783139625

input archived log thread=2 sequence=56 RECID=109 STAMP=783155353

input archived log thread=1 sequence=60 RECID=112 STAMP=783164816

3.Once done, start the instances on all the nodes.

SQL> alter database open;

Database altered

We can take a database Backup using RMAN in Mount, this will be said as COLD-BACKUP

What is "Buffer Busy Wait " - - Tuning

“When several concurrent sessions will read the same blocks/same table or same index block.”

Definition : When two or more session issue the same query/related query (that access the same database blocks), the few sessions will read the data from database buffer cache (based in inittrans and maxtrans in block level) while other sessions are in wait. In that case , Buffer Busy Wait – Event occurs.

How to find Buffer Busy Waits ?

Using the AWR  report top-5 wait-event shows the read by other session or Buffer busy wait event.

Or Using v$segment_statistics or v$system_event also, we can see the buffer busy wait event.

What are Hot Objects/Blocks ?

When Number of concurrent session’s access single block in an object then they are called as hot object.

How to find these Hot Objects ?

Using AWR report “Segment statistics” section shows the HOT objects list.

Or also using below query, we find the hot objects. :

SELECT relative_fno, owner, segment_name, segment_type

FROM dba_extents

WHERE file_id = &file

AND &block BETWEEN block_id AND block_id + blocks - 1;

How we reduce the buffer busy waits?

Method 1:

Increasing INITRANS value method:

First we should know how concurrent sessions accessing a single block in an object?

Each database-block having 3 layers.

1. Cache layer

2. Transaction layer

3. Data layer

Transaction layer is playing vital role for block contention.

Each block will have ITL (INTERESTED TRANSACTION LIST) slots. This ITL slots is required for any sessions that’s need to modify a block in an object.

INITRANS value for table having segment 1 & INITRANS for index segment having 2.

MAXTRANS value default is 255.

If there is no free ITL slot in a blocks, then transaction will waiting for serially for a free ITL slot.

By increasing INITRANS value to avoid the serial transaction waiting. Concurrently number of session will perform the DML operation in single block.

Each ITL requires approximately 23 bytes in the block header.

Method 2 : Increasing PCTFREE method:

Suppose a single 8 KB block contains 1000 rows. We reducing the rows in a block can easily reduce the buffer busy wait.

PCTFREE space is used for future updates only. We have an 8 KB data block. Default PCTFREE value is 10%. If we increased the PCTFREE value is 20% automatically number of rows inserted in a block is automatically reduced.

Method 3:   Reducing database block size method:

It’s similar to PCTFREE method. Suppose a single 8 KB block contains 1000 rows. Using db multiblock size future we used 4 KB data block. Now 1000 rows should be stored two 4 KB blocks.

Our goal is to reduce the number of records stored in a block.

Tune the inefficient queries:

Reduce the number of blocks accessing for an objects in buffer cache. By tuning the query to minimize the number of blocks reads from disk to database buffer cache.

Example: I have a one huge table & it contains 10000 blocks. There is no index for this table.If we doing any operation against this table, it’s going full table scan & accessing all the blocks in a table (server process reads the 10000 blocks from disk to database buffer cache). We can put proper index for this table & avoid the full table scan.

See Also : 

How to Prepare for DBA Interview

General Tips to Prepare for an Oracle DBA Job Interview

For more updates,  Like Us Our Facebook Page Here