Biju Thomas

OneNeck IT Services Corporation

Session #328

Oracle11g: Rest of the

New Features for DBA

http://www.oneneck.com

Introducing OneNeck

The ERP Outsourcing Experts

Provide a comprehensive, flexible suite of outsourcing solutions designed specifically to help mid-market and public sector organizations

• Supporting over 30,000 users at over 850 sites worldwide

• Primary data center/support center operations in Phoenix

and Houston

• Hosting and managing over 1000 databases

• 99% Contract Renewal Rate over 10 years

• 100% US based operations and staff

• 24x7x365 support center handling over 50,000 tickets

annually

• Oracle certified hosting partner

• Ranked #1 ERP Outsourcing Vendor by the Black Book of

Outsourcing two years in a row

• http://www.OneNeck.com

• Stop by booth 1271

About the Speaker

• Senior Database Administrator at OneNeck IT

Services Corporation (www.oneneck.com)

• More than 14 years of Oracle experience

• Co-author of Oracle10g (New Features), Oracle9i

(SQL & Admin) and Oracle8i (SQL & Admin)

certification guides published by Sybex/Wiley

• Published articles in Oracle Magazine, Oracle

Internals and Select Journal

• Oracle 7, 8i, 9i, 10g OCP Administrator

• Oracle Database SQL Certified Expert

Objectives

• Review Oracle11g features that are not top-10

• Discuss the new features that are out-of-the-box in

Oracle11g Enterprise Edition

• Concentrate on the features that are simple and easy

to implement

• Identify the changes in behavior from Oracle10g

Outline

• Security Changes

– Passwords, Auditing, Profiles

• Configuration Changes

– Alert log, trace locations

– Parameter files, Memory configuration

• Administration Enhancements

– Read only tables, Invisible indexes

– Temporary tablespaces

– Lock waits

– Stats collection enhancements

• SQL*Plus error logging

Security

Passwords are Case Sensitive

• New users created in Oracle11g have case sensitive password.

• When upgrading from pre-11 release, case sensitivity takes effect when you change password.

• PASSWORD_VERSIONS and PASSWORD columns in DBA_USERS.

• Disable case sensitivity by setting SEC_CASE_SENSITIVE_LOGON to FALSE.

• ORAPWD has a new parameter – IGNORECASE.

Passwords and DB Links

• Since passwords are case sensitive in

Oracle11g, careful with the DB Links from

pre-11g database to 11g database.

• The account in Oracle11g should have

password in uppercase when linking from

pre-11g database.

To pre-11g To 11g

From Pre-11g Doesn’t Matter Upper Case

From 11g Doesn’t Matter Same Case

Users with default password

• New view to list the users with default password.

• DBA_USERS_WITH_DEFPWD has only one column – USERNAME.

select * from

dba_users_with_defpwd;

USERNAME

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

DIP

MDSYS

WK_TEST

CTXSYS

HR

OUTLN

EXFSYS

SCOTT

MDDATA

ORDPLUGINS

ORDSYS

XDB

SI_INFORMTN_SCHEMA

WMSYS

14 rows selected.

Changes to DEFAULT Profile

• PASSWORD_LIFE_TIME is 180

• PASSWORD_LOCK_TIME is 1

• PASSWORD_GRACE_TIME is 7

• ?/rdbms/admin/utlpwdmg.sql creates

VERIFY_FUNCTION_11G, with enhanced

password complexity checking.

Default Auditing

• AUDIT_TRAIL parameter is set to DB

• ALTER SYTEM, ALTER USER, ALTER

DATABASE, ALTER PROFILE actions are

audited.

• CREATE USER, DROP USER, DROP

PROFILE actions are audited.

• Logins are audited (CREATE SESSION).

• Most “ANY” privilege actions are audited.

• Delete on AUD$ is audited and such records

cannot be deleted.

Actions Audited

ALTER ANY PROCEDURE CREATE ANY JOB DROP ANY TABLE

ALTER ANY TABLE CREATE ANY LIBRARY DROP PROFILE

ALTER DATABASE CREATE ANY PROCEDURE DROP USER

ALTER PROFILE CREATE ANY TABLE EXEMPT ACCESS POLICY

AUDIT ROLE BY ACCESS CREATE EXTERNAL JOB GRANT ANY OBJECT

PRIVILEGE

ALTER SYSTEM CREATE PUBLIC DATABASE

LINK GRANT ANY PRIVILEGE

ALTER USER CREATE SESSION GRANT ANY ROLE

AUDIT SYSTEM CREATE USER

AUDIT SYSTEM BY ACCESS DROP ANY PROCEDURE

DDL in Alert Log

• To log all DDL operations in the alert log file, set the parameter ENABLE_DDL_LOGGING.

• The default value is FALSE, means DDL logging is disabled.

• Can be changed using ALTER SESSION or ALTER SYSTEM

Wed Jan 30 11:21:11 2008

create table bthomas.test_table (nn number)

Wed Jan 30 11:27:34 2008

alter table bthomas.test_table modify (nn varchar2 (10))

Wed Jan 30 11:29:43 2008

drop table bthomas.test_table

Configuration

09:55

Where is the alert log?

• Automatic Diagnostic Repository (ADR) keeps all the

log and trace files.

• DIAGNOSTIC_DEST parameter replaces

BACKGROUND_DUMP_DEST,

USER_DUMP_DEST and CORE_DUMP_DEST

parameters.

• DIAGNOSTIC_DEST defaults to ORACLE_BASE if

set, else ORACLE_HOME/log.

• Alert log is under

DIAGNOSTIC_DEST/diag/rdbms/database/instance/t

race. The XML version is under ../alert.

File Locations

• The USER/BACKGROUND/CORE_DUMP_DEST values are ignored by Oracle11g.

• V$DIAG_INFO gives file locations.

select name, value from v$diag_info;

NAME VALUE

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

Diag Enabled TRUE

ADR Base c:\oracle

ADR Home c:\oracle\diag\rdbms\w11gr1\w11gr1

Diag Trace c:\oracle\diag\rdbms\w11gr1\w11gr1\trace

Diag Alert c:\oracle\diag\rdbms\w11gr1\w11gr1\alert

Diag Incident c:\oracle\diag\rdbms\w11gr1\w11gr1\incident

Diag Cdump c:\oracle\diag\rdbms\w11gr1\w11gr1\cdump

Health Monitor c:\oracle\diag\rdbms\w11gr1\w11gr1\hm

Default Trace File c:\oracle\diag\rdbms\w11gr1\w11gr1\trace\

w11gr1_ora_6036.trc

Active Problem Count 3

Active Incident Count 1

Parameter - Default Value Changes

Parameter 10g 11g

LOG_ARCHIVE_MAX_PROCESSES 2 4

SESSION_CACHED_CURSORS 0 50

JOB_QUEUE_PROCESSES 10 1000

AUDIT_TRAIL NONE DB

UNDO_MANAGEMENT MANUAL AUTO

Memory Configuration

• New parameters – MEMORY_TARGET and MEMORY_MAX_TARGET.

• No need to specify SGA size (SGA_TARGET) and PGA size (PGA_AGGREGATE_TARGET) separately.

• If SGA and PGA parameters are not set, the default initial allocation is 60% SGA and 40% PGA.

• New view – V$MEMORY_TARGET_ADVICE.

Memory Configuration - 2

• The V$MEMORY_DYNAMIC_COMPONENTS view

shows the summarized information on all resize

operations and shows the current sizes of all SGA

components.

• If you set the MEMORY_TARGET to a non-zero

value, Oracle will manage memory automatically. The

MEMORY_MAX_TARGET will default to the value of

MEMORY_TARGET.

• If you have also set SGA_TARGET and

PGA_AGGREGATE_TARGET parameters, those

values will be considered the minimums.

Parameter File from Memory

• In pre-Oracle11g releases, we can create

pfile from spfile or spfile from pfile.

• In Oracle11g, we can now create spfile or

pfile from memory

CREATE

PFILE='/u01/oracle/admin/DB1/pfile/in

itDB1.ora' FROM MEMORY;

CREATE SPFILE FROM MEMORY;

RESET Clause in ALTER SYSTEM

• In pre-Oracle11g releases, the RESET clause

required SID= clause. ALTER SYSTEM RESET UTL_FILE_DIR SID=*

SCOPE=SPFILE;

• In Oracle11g, SID= is optional. ALTER SYSTEM RESET UTL_FILE_DIR;

• In Oracle11g, SCOPE=SPFILE is only valid

option for SCOPE and is the default. In

Oracle10g, BOTH was the default and BOTH,

MEMORY, SPFILE were valid options.

Administration

10:10

Shrink Temporary Tablespaces

• Option to shrink a temporary tablepace

• Option to shrink a temporary tablespace file

• New view DBA_TEMP_FREE_SPACE

alter tablespace temp shrink space keep 18M;

alter tablespace temp shrink space;

alter tablespace temp shrink tempfile

'c:\oracle\oradata\w11gr1\temp01.dbf' keep 20M;

select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

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

TEMP 52494336 2162688 51380224

Read-only Tables

• New ALTER TABLE clause to make a table read-

only.

• If you try to perform insert or update or delete on

read-only table, you get an ORA-12081 error.

• Flashback, Truncate not allowed.

• Drop table, drop indexes, drop constraints allowed.

ALTER TABLE <table_name> READ ONLY;

ALTER TABLE <table_name> READ WRITE;

select owner, table_name, tablespace_name

from dba_tables

where read_only = 'YES';

Add NOT NULL column

• When NOT NULL column with DEFAULT

value is added to a table, no update

performed on existing rows.

• Only dictionary is updated

• Same syntax, different behavior

• When you query an existing record from the

table, Oracle optimizer will apply the filter

predicate NVL(<column_name>,

<default_value>) before returning the record

Tablespace for GTT

• In pre-Oracle11g releases, the global temporary table

segments where created in the user’s default

temporary tablespace.

• In Oracle11g, you can specify a tablespace for global

temporary tablespace segments.

create global temporary table customer_stage (

cust_name number (10),

col1 varchar2 (40),

col2 varchar2 (60))

on commit preserve rows

tablespace temp;

Invisible Indexes

• If you want to test the implications of creating

an index without affecting the application, you

can make the index invisible. After testing,

you may drop the index or make it visible.

• To make the optimizer use invisible index, set

the OPTIMIZER_USE_INVISIBLE_INDEXES

to TRUE in the session.

• INVISIBLE/VISIBLE clause is added to

CREATE INDEX and ALTER INDEX

statements.

Invisible Indexes - 2

SQL> create index hr.employee_test on hr.employee (first_name) invisible;

Index created.

SQL> select index_name, visibility from dba_indexes

2 where owner = 'HR' and table_name = 'EMPLOYEE';

INDEX_NAME VISIBILIT

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

PK_EMPLOYEE VISIBLE

EMPLOYEE_TEST INVISIBLE

SQL> select first_name, last_name from hr.employee

2 where first_name = 'John';

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 3 | 78 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMPLOYEE | 3 | 78 | 3 (0)| 00:00:01 |

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

SQL> alter session set optimizer_use_invisible_indexes = true;

Session altered.

SQL> select first_name, last_name from hr.employee

2 where first_name = 'John';

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU

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

| 0 | SELECT STATEMENT | | 3 | 78 | 2 (0

| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 3 | 78 | 2 (0

|* 2 | INDEX RANGE SCAN | EMPLOYEE_TEST | 3 | | 1 (0

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

Wait for DDL Lock

• New parameter to specify the DDL lock wait time – DDL_LOCK_TIMEOUT

• Value specified in seconds, default is 0, max 1,000,000.

• Parameter can be changed using ALTER SYSTEM or ALTER SESSION.

• When the timeout expires, error returned as in previous releases.

ORA-00054: resource busy and

acquire with NOWAIT specified or

timeout expired

LOCK TABLE Enhancement

• The LOCK TABLE statement is enhanced to

include a WAIT clause.

• You can now specify how many seconds you

want the database to wait before returning

the “resource busy” error.

LOCK TABLE <table_name> IN

EXCLUSIVE MODE WAIT 120;

10:20

DBMS_STATS Enhancements

• New procedures to set table, schema, database and

global preferences for statistics collection. This

replaces the SET_PARAM procedure in Oracle10g.

• New preferences

– PUBLISH: Pending Statistics Feature

– STALE_PERCENT: Adjust Staleness Threshold

– INCREMENTAL: For Partitioned Table Statistics

• Extended Statistics

– Multi-column

– Expression column

• Report differences between statistics collected

• Resume statistics gathering if aborted earlier

Stats – Preferences

• Procedures (DST – Substitute DATABASE, SCHEMA or TABLE)

– SET_<DST>_PREFS, SET_GLOBAL_PREFS

– DELETE_<DST>_PREFS

– EXPORT_<DST>_PREFS, IMPORT_<DST>_PREFS

– GET_PREFS, RESET_GLOBAL_PREF_DEFAULTS

• Parameter Names – CASCADE, DEGREE, ESTIMATE_PERCENT, METHOD_OPT,

NO_INVALIDATE, GRANULARITY

– STALE_PERCENT, PUBLISH, INCREMENTAL

• Query – DBA_TAB_STAT_PREFS (USER_, ALL_)

– Function: dbms_stats.get_prefs(

'STALE_PERCENT', 'SH','CUSTOMERS')

Staleness Threshold

• If GATHER_STALE option is specified when

gathering statistics, Oracle looks for 10% of rows

changed in the table to collect new statistics.

• In 11g, using the SET_.._PREFS procedures, this

default can be changed globally or at db, schema,

table level. SQL> exec dbms_stats.set_table_prefs('SH',

'CUSTOMERS', 'STALE_PERCENT', '20');

SQL> select table_name, preference_name,

preference_value from dba_tab_stat_prefs;

TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE

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

CUSTOMERS STALE_PERCENT 20

Pending Statistics

• Test implication of new statistics before making it available to all users

SQL> exec dbms_stats.set_table_prefs('HR', 'EMPLOYEES', 'PUBLISH', 'FALSE');

SQL> exec dbms_stats.gather_table_stats('HR', 'EMPLOYEES');

SQL> select table_name, num_rows, blocks, sample_size

from dba_tab_pending_stats;

TABLE_NAME NUM_ROWS BLOCKS SAMPLE_SIZE

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

EMPLOYEES 107 5 107

SQL> alter session set optimizer_use_pending_statistics = true;

SQL> exec dbms_stats.publish_pending_stats( 'HR', 'EMPLOYEES');

SQL> exec dbms_stats.delete_pending_stats( 'HR', 'EMPLOYEES'); 10:30

Multi-column Statistics

• Tell optimizer relationship between columns • CUSTOMERS table is populated and has about 91000 rows. Statistics collected

on the table with the “FOR ALL ROWS SIZE AUTO” option.

SQL> select column_name, num_distinct, histogram

2 from dba_tab_col_statistics

3 where owner = 'BTHOMAS' and table_name = 'CUSTOMERS';

COLUMN_NAME NUM_DISTINCT HISTOGRAM

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

CUST_COUNTRY 3 FREQUENCY

CUST_STATE 6 FREQUENCY

CUST_NAME 47692 NONE

SQL>

SQL> select * from customers where cust_country = 'India' and cust_state = 'TN';

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

| Id | Operation | Name | Rows | Bytes |

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

| 0 | SELECT STATEMENT | | 1447 | 41963 |

|* 1 | TABLE ACCESS FULL| CUSTOMERS | 1447 | 41963 |

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

Multi-columns Stats - 2

SQL> select dbms_stats.create_extended_stats('BTHOMAS','CUSTOMERS',

2 '(CUST_COUNTRY, CUST_STATE)') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS('BTHOMAS','CUSTOMERS','(CUST

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

SYS_STUZVS6GX30A0GN_5YRYSD2LPM

SQL> exec dbms_stats.gather_table_stats(null, 'customers',

method_opt=>'for all columns size skewonly');

=====================================================================

SQL> exec dbms_stats.gather_table_stats(null, 'customers',

method_opt=>'for all columns size skewonly

for columns (cust_country, cust_state)');

SQL> select extension_name, extension from user_stat_extensions

2 where table_name = 'CUSTOMERS';

EXTENSION_NAME EXTENSION

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

SYS_STUZVS6GX30A0GN_5YRYSD2LPM ("CUST_COUNTRY","CUST_STATE")

Multi-Column Stats - 3

SQL> select column_name, num_distinct, histogram

2 from user_tab_col_statistics

3* where table_name = 'CUSTOMERS‘;

COLUMN_NAME NUM_DISTINCT HISTOGRAM

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

… …

SYS_STUZVS6GX30A0GN_5YRYSD2LPM 8 FREQUENCY

SQL> select * from customers where cust_country = 'India'

and cust_state = 'TN';

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

| Id | Operation | Name | Rows | Bytes |

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

| 0 | SELECT STATEMENT | | 86 | 2580 |

|* 1 | TABLE ACCESS FULL| CUSTOMERS | 86 | 2580 |

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

SQL> exec dbms_stats.drop_extended_stats(null,'CUSTOMERS',

'(CUST_COUNTRY, CUST_STATE)');

SQL*Plus

• On Windows, SQLPLUSW.EXE replaced with SQL Developer, SQLPLUS.EXE still available.

• SQL*Plus error logging to write ORA, PLS and SP2 errors – Default table name is SPERRORLOG.

SET ERRORL[OGGING] {ON|OFF} [TABLE [schema.]tablename]

[TRUNCATE] [IDENTIFIER identifier]

SQL> desc sperrorlog

Name Null? Type

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

USERNAME VARCHAR2(256)

TIMESTAMP TIMESTAMP(6)

SCRIPT VARCHAR2(1024)

IDENTIFIER VARCHAR2(256)

MESSAGE CLOB

STATEMENT CLOB

• CONNECT AS SYSASM, in addition to SYSDBA and SYSOPER

What Else to Try/Read?

• Database Replay

• Interval and Reference

Partitions

• SQL Query Result Cache

• SQL Performance Analyzer

• SQL Plan Baseline

• Automatic SQL Tuning

• Snapshot Standby

• Active Data Guard

• OLTP Table Compression

• AutoTask Infrastructure

• Virtual Columns

• Flashback Data Archive

• Flashback Transaction

• Automatic Diagnostic

Repository & ADRCI

• Data Recovery Advisor

• Active DB Duplication

• Multi-section backups

• Lightweight Jobs in

Scheduler

• Fine grained dependency

tracking

• DBMS_COMPARE

• DBMS_HM

And… Thank You For Your Time

• Please complete the session evaluation form

– Biju Thomas

– Oracle11g: Rest of New Features for DBA

– Session # 328

• Further questions, comments…

– Stop by Booth #1271

– WWW.ONENECK.COM

Questions…???