oracle11g new features tl meeting

8/4/2019 Oracle11g New Features TL Meeting

1/34

30-Jun-2011

Rakesh Kumar Verma

Oracle11g New Features


2/34

Active Data Guard RMAN Enhancements ASM Enhancements Automatic Memory Management(AMM)

Security Enhancements Schema Management Flashback Data Archive Partitioning & Storage Related Enhancements

Query Result Cache Q&A

Agenda


3/34

1) Active Data Guard

Prior to Oracle 11g, read access to standby database required Redo

Apply to be stopped.

Queries on read only standby databases could potentially returnstale results as changes occurring on the primary database are not

applied.

This backlog of unapplied redo data could potentially increase

failover times

Before 11g Active Data Guard


4/34

What is Active Data Guard?

Available in the database server Enterprise Edition as an

additional license option

Real time replica of a production database which is open in read

only mode while changes transmitted from the primary database are

being simultaneously applied to it.

Applies to physical and not logical standby database

Can offload reporting, queries and fast incremental backups from

the primary site to the standby

Performs its primary Data Guard objective of preventing data loss

and downtime due to data corruptions, database and site failures,

human error, or natural disaster.


5/34

What can we do? Issue SELECT statements

Issue complex queries such as grouping SET queries and WITH clause

queries

Call stored procedures

Use database links to write to remote databases

Use stored procedures to call remote procedures via dblinks

What we cannot do .

Any DML excluding SELECT

Any DDLSo no additional indexes allowed

AWR tool not supported for Active DG standby

Active Data Guard Operations


6/34

2) RMAN Enhancements


7/34

Duplicating a Database

With network (no backups required)

Including customized SPFILE

Via Enterprise Manager or RMAN command line

Active source database

Destination orAUXILIARY database

TCP/IP


8/34

The RMAN DUPLICATE Command

DUPLICATE TARGET DATABASE

TO dbtest

FROM ACTIVE DATABASE

SPFILE PARAMETER_VALUE_CONVERT '/u01', '/u02'

SET SGA_MAX_SIZE = '200M'

SET SGA_TARGET = '125M'

SET LOG_FILE_NAME_CONVERT = '/u01','/u02'

DB_FILE_NAME_CONVERT = '/u01','/u02';


9/34

Creating a Standby Database with the DUPLICATE Command

DUPLICATE TARGET DATABASE

FOR STANDBY

FROM ACTIVE DATABASE

SPFILE PARAMETER_VALUE_CONVERT '/u01', '/u02'

SET "DB_UNIQUE_NAME"="FOO"

SET SGA_MAX_SIZE = "200M"

SET SGA_TARGET = "125M"

SET LOG_FILE_NAME_CONVERT = '/u01','/u02'

DB_FILE_NAME_CONVERT = '/u01','/u02';


10/34

RMANbasecatalog

Virtual private catalogs (VPC)

Enhancing securityby restricting accessto metadata

Databases registered in RMAN catalog

Creating and Using RMAN Virtual Private Catalogs


11/34

Using RMAN Virtual Private Catalogs

1. Create an RMAN base catalog:

2. Grant RECOVERY_CATALOG_OWNER to VPC owner:

3a. Grant REGISTER to the VPC owner, or:

3b. Grant CATALOGFORDATABASE to the VPC owner:

RMAN> CONNECT CATALOG catowner/oracle@catdb;RMAN> CREATE CATALOG;

SQL> CONNECT SYS/oracle@catdb AS SYSDBASQL> GRANT RECOVERY_CATALOG_OWNER to vpcowner

RMAN> CONNECT CATALOG catowner/oracle@catdb;RMAN> GRANT REGISTER DATABASE TO vpcowner;

RMAN>GRANT CATALOG FOR DATABASE db10g TO vpcowner


12/34

Using RMAN Virtual Private Catalogs

4a. Create a virtual catalog for 11gclients, or:

4b. Create a virtual catalog for pre-11gclients:

5. Register a new database in the catalog:

6. Use the virtual catalog:

RMAN> CONNECT CATALOG vpcowner/oracle@catdb;RMAN> CREATE VIRTUAL CATALOG;

SQL> CONNECT vpcowner/oracle@catdbSQL> exec catowner.dbms_rcvcat.create_virtual_catalog;

RMAN> CONNECT TARGET / CATALOG vpcowner/oracle@catdb;RMAN> REGISTER DATABASE;

RMAN> CONNECT TARGET / CATALOG vpcowner/oracle@catdb;RMAN> BACKUP DATABASE;


13/34

3) ASM Enhancements

ASM Fast Mirror Resync


14/34

Without ASM Fast Mirror Resync

Disk access failure

Disk automatically dropped:All dropped extents re-created

ASM redundancy used

Disk added back:Extents rebalanced

1 2

34

Oracle Database 10gand 11g


15/34

ASM Fast Mirror Resync: Overview

Disk access failure

Failure time < DISK_REPAIR_TIME

ASM redundancy used

Disk again accessible;need only to resync modified extents

1 2

34

Oracle Database 11g


16/34

Setting Up ASM Fast Mirror Resync

ALTER DISKGROUP dgroupA SET ATTRIBUTE 'DISK_REPAIR_TIME'='3H';

ALTER DISKGROUP dgroupA

OFFLINE DISKS IN FAILGROUP contrl2 DROP AFTER 5H;

ALTER DISKGROUP dgroupA

ONLINE DISKS IN FAILGROUP contrler2 POWER 2 WAIT;

ALTER DISKGROUP dgroupA DROP DISKS IN FAILGROUP contrl2 FORCE;


17/34

ASM Preferred Mirror Read

Site BSite A

P S

Site BSite A

P S

P: Primary AUS: Secondary AU


18/34

Setup

ASM Preferred Mirror Read: Setup

ASM_PREFERRED_READ_FAILURE_GROUPS=DATA.SITEA

ASM_PREFERRED_READ_FAILURE_GROUPS=DATA.SITEB

On first instance

On second instance

SELECT preferred_read FROM v$asm_disk;

SELECT * FROM v$asm_disk_iostat;

Monitor


19/34

SYSASMRole

Using the SYSASM role to manage ASM instances avoids

overlap between DBAs and storage administrators.

SYSDBA will be deprecated:

Oracle Database 11g, Release 1 behaves as in10g.

In future releases, SYSDBA privileges will be

restrictedin ASM instances.

SQL> CONNECT / AS SYSASM

SQL> CREATE USER username IDENTIFIED by passwd;

SQL> GRANT SYSASM TO username;

SQL> DROP USER username;

SQL> CONNECT username/passwd AS SYSASM;


20/34

4)Automatic memory management(AMM)

In 10g,the total available memory is divided into two components. One is SGA and

the other is PGA. Oracle offered SGA_TARGET to automate the individual

components inside the SGA. In 11g oracle takes this tradition a little forward to

completely automate the available memory.

Along with SGA_TARGET, PGA_AGGREGATE_TARGET oracle offersMEMORY_TARGET. If MEMORY_TAGET is set oracle takes care of SGA and PGA

automatically. This parameter is a static initialization parameter.

Oracle also provides four new dynamic performance views :

V$MEMORY_CURRENT_RESIZE_OPS

V$MEMORY_DYNAMIC_COMPONENTS

V$MEMORY_RESIZE_OPS

V$MEMORY_TARGET_ADVICE
http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_2057.htmhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_2057.htmhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_2058.htmhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_2058.htmhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_2059.htmhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_2059.htmhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_2060.htmhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_2060.htmhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_2060.htmhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_2060.htmhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_2059.htmhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_2059.htmhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_2058.htmhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_2058.htmhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_2057.htmhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_2057.htm


21/34

5) Security Enhancements

Case sensitive passwords

Oracle offers case sensitive passwords and also allows you to revert back if

you do not like it using the parameter SEC_CASE_SENSITIVE_LOGON.One

can set the parameter to false to disable this.

When you upgrade an existing Oracle 10g database to 11g, you can migrate

your passwords to the new standard. You can check the status of the

password by querying the DBA_USERS view, especially the new column

PASSWORD_VERSIONS.


22/34

6) Schema Management Enhancements

DDL Wait Option

The ORA-00054 : resource busy and acquire with NOWAIT specified or timeout expired

The ORA is generally observed during busy system time,oracle 11g does not throw a error

instead tries it for 10secs until it is successful.

ALTER SYSTEM SET DDL_LOCK_TIMEOUT; can also be used to make this default to all sessions

Fine Grained Dependency Tracking

Consider a table is altered by adding a new column and in 10g a view derived from this object

will be invalid causing the objects like packages using this view to be invalid.

In 11g Oracle takes care of changes like these and will not mark the view as invalid thereby

improving the availability of the application.


23/34

7) Flashback Data Archive: Oracle Total Recall

Transparently tracks historical changes to all Oracle data in a highly secure and

efficient manner Secure

No possibility to modify historical data

Retained according to your specifications

Automatically purged based on your retention policy

Efficient

Special kernel optimizations to minimize performance overhead of

capturing historical data

Stored in compressed form in tablespaces to minimize storage

requirements

Completely transparent to applications

Easy to set up


24/34

Flashback Data Archive Comparison

Flashback Data Archive Flashback Database

Main benefit Access to data at any pointin time without changingthe current data

Physically moves entiredatabase back in time

Operation Online operation, trackingenabled, minimal resourceusage

Offline operation, requirespreconfiguration andresources

Granularity Table Database

Access point-in-time Any number per table One per database


25/34

Flashback Data Archive: Overview

Originaldata in

Undo data

DML operations

buffer cache

Flashback data archivesstored in tablespaces

1 year

2 years

5 years

Example: Three flashback dataarchives with retention of:

For long-retention requirements exceeding undo

FBDA


26/34

Using Flashback Data Archive

Basic workflow to access historical data:

1. Create the flashback data archive:

2. Enable history tracking for a table in the FLA1 archive:

3. View the historical data:

CREATE FLASHBACK ARCHIVE fla1

TABLESPACE tbs1 QUOTA 10G RETENTION 5 YEAR;

ALTER TABLE inventory FLASHBACK ARCHIVE fla1;

SELECT product_number, product_name, count

FROM inventory AS OF TIMESTAMP TO_TIMESTAMP

('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');


27/34

Maintaining Flashback Data Archives

1. Adding space:

2. Changing retention time:

3. Purging data:

4. Dropping a flashback data archive:

ALTER FLASHBACK ARCHIVE fla1

ADD TABLESPACE tbs3 QUOTA 5G;

ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE

TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' day);

ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR;

DROP FLASHBACK ARCHIVE fla1;


28/34

8) Interval Partitioning

Interval partitioning is an extension of range partitioning.

Partitions of a specified interval are created when inserted data

exceeds all of the range partitions.

At least one range partition must be created.

Interval partitioning automates the creation of range partitions.


29/34

Interval Partitioning: Example

CREATE TABLE SH.SALES_INTERVALPARTITION BY RANGE (time_id)

INTERVAL(NUMTOYMINTERVAL(1,'month')) STORE IN(tbs1,tbs2,tbs3,tbs4)

(

PARTITION P1 values less than (TO_DATE('1-1-2002','dd-mm-yyyy')),

PARTITION P2 values less than (TO_DATE('1-1-2003','dd-mm-yyyy')),

PARTITION P3 values less than (TO_DATE('1-1-2004','dd-mm-yyyy')))

AS

SELECT *

FROM SH.SALES

WHERE TIME_ID < TO_DATE('1-1-2004','dd-mm-yyyy');

P1 P2 P3 Pi1 Pin

Range section Interval section

Transition point

Automatically createdwhen you insert data


30/34

Table Compression: Overview

Oracle Database 11gextends compression for OLTP data.

Support for conventional DML operations

(INSERT, UPDATE, DELETE)

New algorithm significantly reduces write overhead.

Batched compression ensures no impact for most OLTP

transactions.

No impact on reads

Reads may actually see improved performance due to

fewer I/Os and enhanced memory efficiency.


31/34

Table Compression Concepts

HeaderUncompressed

data

Compresseddata

Freespace

Inserts areuncompressed.

PCTFREE reached

triggers compression.

Inserts are againuncompressed.

PCTFREE reached

triggers compression.

Data block

PCTFREElimit


32/34

Using Table Compression

Requires database compatibility level at 11.1 or greater

New syntax extends the COMPRESS keyword:

COMPRESS [FOR {ALL | DIRECT_LOAD} OPERATIONS]

FOR DIRECT_LOAD is the default: Refers to bulk load operations

from prior releases FOR ALL OPERATIONS: OLTP + direct loads

Enable compression for new tables:

Enable compression on existing table:

Does not trigger compression on existing rows

CREATE TABLE t1 COMPRESS FOR ALL OPERATIONS;

ALTER TABLE t2 COMPRESS FOR ALL OPERATIONS;

9) SQL Q R l C h


33/34

9) SQL Query Result Cache

Cache the result of a query or query block for future reuse.

Cache is used across statements and sessions unless it is stale.

Benefits:

Scalability

Reduction of memory usage

Good candidate statements:

Access many rows

Return few rows

SELECT

SQL

Query ResultCache

SELECT

Session 1 Session 2

1

2 3


34/34

Q&A

oracle11g new features tl meeting

Documents