Virag Sharma

We want restore backup from source. So we took backup from source and copied it to /dba/share/MYDB on target. My collouge sent me email saying  restore failed with following error

rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Sep 18 20:47:38 2013

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area    8551575552 bytes

Fixed Size                     2245480 bytes
Variable Size                503319704 bytes
Database Buffers            8036286464 bytes
Redo Buffers                   9723904 bytes

RMAN> set DBID=2387922???;

executing command: SET DBID

RMAN> restore controlfile from '/dba/share/MYDB/c-2387922???-20130910-00.ctl';

Starting restore at 18-SEP-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=391 device type=DISK

channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/18/2013 20:48:04
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

We searched MOS and forums. Most of the search , pointing it is old backup ( more then 7 day ),  some doc say backup corrupted etc etc. Instead going for whole story that how reached to conclusion, let we tell you the finding.

We found the mount option for /dba/share/MYDB ( where we copied backup from source ) not correct
...that's why getting error. We changed it to

           rw,bg,intr,hard,timeo=600,wsize=32768,rsize=32768,nfsvers=3,tcp,noacl 0 0

After this change we able to restore control file and rest of the backup successfully

-- Create new STS and copy from exiting one
DECLARE
l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
-- Create STS
DBMS_SQLTUNE.create_sqlset(sqlset_name  => 'STS_DBNAME_CMUL',description  => 'new STS SQL tuning set from XYZ DB');
OPEN l_cursor FOR
SELECT VALUE(r)
FROM   TABLE (DBMS_SQLTUNE.select_sqlset (
                'STS_RAG_CMUL'        -- old sqlset_name which need to rename
                                         )         
              ) r;
DBMS_SQLTUNE.load_sqlset (
sqlset_name     => 'STS_DBNAME_CMUL',
populate_cursor => l_cursor);
-- Drop STS
dbms_sqltune.drop_sqlset('STS_RAG_CMUL');
close l_cursor;
END;
/

The All India Oracl Users Group (AIOUG) presented Sangam 2010, a two-day(3-4-Sep-2010) Oracle Users Conference held in Hyderabad. Sangam 2010 hosted multiple sessions by international and Indian experts like Jonathan Lewis , Rittman , Iggy Fernandez etc.

Jonathan Lewis & Virag Sharma

The event was very good and opportunity to learn from experts. Sangam 2010 started with Murali welcome note followed by Presentation, by Roland Slee, Vice-President, Database Product Management, Oracle Asia Pacific & Japan.

First technical session was from Jonathan Lewis on "Writing Optimal SQL". This One day Presentation was divided in to two , 1/2 day sessions. Conference room packed for his sessions, Jonathan Lewis Presentation was main attraction for Sangam 2010. After Lunch , there were 2 Conference room for different sessions and audience can choose session as per there interest.

Sponsors "OSI Consulting" presentation on "Cross Platform migration challenges and time reduction techniques" was pretty good then expected. After "OSI Consulting" presentation, RAC SIG meeting held. In this meeting Satyendra Kumar , explained things about RAC SIG. In the end of "RAC SIG" meeting Satyendra looking leader for various city , for Delhi/NCR, I proposed Aman Sharma’s name and latter learned that Aman recently become Oracle ACE.

Session “Tips and Best Practices for DBA’s” from Francisco , was in soo much demand that Conference room 2 overflowed and finally session held in Conference room 1.

Rittman and Vivek took one session in Sangam 2010 and unfortunately , Not able to attend any of them.In the end of Sangam 2010 Oracle Users Conference, Jonathan Lewis took 1 Hr question and Answer session, that was pretty good.

"In this world nothing can be said to be certain but death and taxes."
--Benjamin Franklin

Today while working on Production performance tuning, We came across one difference in awr report of 11g R1 and 11R2

In oracle 11g R1 AWR report logon trigger modelue name usually come like
sqlplus / perl / Mid Tier@server_name_from_it_login

But in 11g R2 AWR report logon trigger modelue name usually come like oraagent.bin@DB_SERVER_WHERE_IT_RUN

New change looks more logical because DB server running logon trigger code ,
not the user.

# Excerpt from 11g R1 AWR report
# In below given example app678 is server name from where
# user logged in to database using sqlplus

Module: sqlplus@app678utl (TNS V1-V3)
UPDATE scott.MY_AUDIT SET LAST_LOGIN = SYSDATE WHERE XYZ_USERNAME = :B1

#Excerpt from 11g R2 AWR report
#In below given example apps001 is database server

Module: oraagent.bin@apps001 (TNS V1-V3)
UPDATE scott.MY_AUDIT SET LAST_LOGIN = SYSDATE WHERE XYZ_USERNAME = :B1

Every release has some Major changes , which we usually says New Features.Some of these features dominate the version, For example 11g R1 has SPA , DB Replay Active standby etc. Same this Oracle Release ( Oracle Database 11g Release 2 ) has some New features for which this release will be known in feature. These features are "Edition based redefination"


Most likely these features designed to give big support to APPS upgrade ( ie Oracle E-Business suite upgrade). When you upgrade APPS database , it need lots of down time , hope, using these new features APPS upgrade will take less time in future.


This feature will allow application upgrade( AS DBA , i would prefer to say Online Database object upgrade) with Minimum down time or may be zero down time. I consider this feature as one step toward ZERO DOWN time for application upgrade.


In 10g statistics collected on table published immediatly, That usually cause lots of performance issue. In oracle 11g r1 there is feature, for collecting stats on tables and publishing stats , as per need to avoid performance issue due to stats collection.

Taking similar feature to next step , 11g R2 has feature "REDEFINITION" , which upgrades objects , but not published immediately, also database can have multiple Editions of objects definition. Of-course there are, some limitation



Check Default Edition

SQL>
1 SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
2* Where PROPERTY_NAME = 'DEFAULT_EDITION'
SQL> /

PROPERTY_VALUE
--------------------------------------------------------------------------------
ORA$BASE

Changing Edition at session or Database level

SQL> ALTER SESSION SET EDITION=ora$base;

Session altered.

SQL> ALTER DATABASE DEFAULT EDITION =ora$base;

Database altered.

Grant create or drop edition to user

SQL> GRANT CREATE ANY EDITION, DROP ANY EDITION to virag;

Grant succeeded.

Enable Edition on schema / User

SQL> ALTER USER virag ENABLE EDITIONS force;

Of-course there are, some limitation......

Will add more ......soon , for How to...


Reference

19 Edition-Based Redefinition

More Post on Oracle RDBMS Database 11g R2 ( Release 2 ) 

Oracle 11g Release 2 (11.2 ) New Features : SCAN - Single Client Access Name
11G R2 New Feature : Purge audit trail records using DBMS_AUDIT_MGMT
Oracle Database 11g Release 2 New Features : Edition based redefination

Once you decided to add/remove node from RAC database/cluster. We need to change tns
entry. Oracle 11g R2 introduced new concept called Single Client Access Name (SCAN).
Which eliminate the need to change tnsnetry when nodes are added to or removed from
the Cluster.

RAC Instances register to SCAN listeners as remote listeners. SCAN is fully qulified name.
Oracle recommends to assign 3 address to SCAN , which create three SCAN listeners.

$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node apps001
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node apps002
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node apps002

Running following command on Node 2 (apps002)

$ ps -aef |grep -i SCAN

oracle 9380 1 0 Aug13 ? 00:01:09 /d01/apps/oracle_crs/11.2/bin/tnslsnr LISTENER_SCAN2 -inherit

oracle 9380 1 0 Aug13 ? 00:01:09 /d01/apps/oracle_crs/11.2/bin/tnslsnr LISTENER_SCAN3 -inherit

oracle 9993 7114 0 09:57 pts/3 00:00:00 grep -i crs

From above output, it is clear that SCAN listener is running from CRS_HOME

$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521


$ srvctl config scan
SCAN name: apps-scan, Network: 1/192.168.182.0/255.255.255.0/
SCAN VIP name: scan1, IP: /apps-scan.us.oracle.com/192.168.182.109
SCAN VIP name: scan2, IP: /apps-scan.us.oracle.com/192.168.182.110
SCAN VIP name: scan3, IP: /apps-scan.us.oracle.com/192.168.182.108

tns entry can use single address ( SCAN Name ) in tnsentry , instead os using entry for all Node


tns entry configured to use VIP addresses for Database will work without any issue. using
SCANs is not Medatory ( May be to support backward compatibility )

#
# TNS ENTRY with SCAN
#

test.world =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST==apps-scan.world)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=R1211.world))
)

#
# TNS Entry without SCAN ( Old way)
#

test.world =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=apps001-vip.world)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=apps002-vip.world)(PORT=1521))
)
(CONNECT_DATA=(SERVICE_NAME=R1211.world)))
)
)

Clients Can connect to a particular instance of the database using SCAN. Entry will looks like

test.world =
(description=
(address=(protocol=tcp)(host=apps-scan.world)(port=1521))
(connect_data=
(service_name=R1211.world)
(instance_name=apps1cl1)))

tns entry configured to use VIP addresses for Database will work without any issue. using

SCANs is not Medatory ( May be to support backward compatibility )

More Post on Oracle RDBMS Database 11g R2 ( Release 2 ) 

Oracle 11g Release 2 (11.2 ) New Features : SCAN - Single Client Access Name
11G R2 New Feature : Purge audit trail records using DBMS_AUDIT_MGMT
Oracle Database 11g Release 2 New Features : Edition based redefination

In earlier version there is no standard way to change AUDIT tables tablespace.
IN Oracle 11g R2 ( Also included in 11.1.0.7 and 10.2.0.5 ( Need to check)) , you can change audit table (SYS.AUD$ and SYS.FGA_LOG$) tablespace using DBMS_AUDIT_MGMT


Not ONLY you can change audits table tablespace , now you can periodically deleting the audit trail records using CLEAN_AUDIT_TRAIL (new in 11.2 ) Procedure.

So Now it is official , that you can change AUD$ table tablespace and purge :-) .

In Below given example , am trying to change tablespace for AUD$

Checking current tablespace from AUD$

SQL> select TABLESPACE_NAME from dba_segments where SEGMENT_NAME='AUD$';

TABLESPACE_NAME
--------------------------------------------------------------------------------
SYSTEM

Changing Tablespace from SYSTEM to SYSAUX for AUD$

SQL>

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_LOCATION_VALUE => 'SYSAUX');
END;
/

PL/SQL procedure successfully completed.

Checking changed Tablespace

SQL> select TABLESPACE_NAME from dba_segments where SEGMENT_NAME='AUD$';

TABLESPACE_NAME
--------------------------------------------------------------------------------
SYSAUX




AUDIT_TRAIL_TYPE: Refers to the database audit trail type. Enter one of the following values:

1. DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$.
2. DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$.
3. DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trail tables.
   

AUDIT_TRAIL_LOCATION_VALUE: Specifies the NEW destination tablespace.

Not ONLY you can change audits table tablespace , now you can periodically deleting the audit trail records/xml/.aud files etc using CLEAN_AUDIT_TRAIL Procedure.

STEPS for Purging AUDIT TRAIL

# Check initialization

BEGIN
IF
NOT DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
THEN
dbms_output.put_line('CLEANUP NOT INITIALIZED' );
ELSE
dbms_output.put_line('CLEANUP INITIALIZED' );
END IF;
END;


# Set initialization

BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
DEFAULT_CLEANUP_INTERVAL => 6 );
END;

# Set Last Audit Time stamp

SQL> desc DBA_AUDIT_MGMT_LAST_ARCH_TS
Name Null? Type
----------------------------------------- -------- ----------------------------
AUDIT_TRAIL VARCHAR2(20)
RAC_INSTANCE NOT NULL NUMBER
LAST_ARCHIVE_TS TIMESTAMP(6) WITH TIME ZONE

SQL> select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;

# Check is Last Audit Time stamp set or not

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
LAST_ARCHIVE_TIME => sysdate -30 <------ want to delete aud file older then 30 days
RAC_INSTANCE_NUMBER => 1 );
END;

# For non RAC don't use "RAC_INSTANCE_NUMBER =>"
# If RAC system having 4 node then run above command 4 time
# with RAC_INSTANCE_NUMBER 1 , 2, 3 ,4

# Manual Purge

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
USE_LAST_ARCH_TIMESTAMP => TRUE);
END;

#
# If USE_LAST_ARCH_TIMESTAMP is False , it purge all audit trail
#

# Here we used DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL =>
# All audit trail types. This includes the standard database audit trail
# (SYS.AUD$ and SYS.FGA_LOG$ tables), operating system (OS) audit trail,
# and XML audit trail. More details are given below
#AUDIT_TRAIL_ALL => All audit trail types. This includes the standard database audit trail (SYS.AUD$ and SYS.FGA_LOG$ tables), operating system (OS) audit trail, and XML audit trail.
#AUDIT_TRAIL_AUD_STD => Standard database audit records in the SYS.AUD$ table
#AUDIT_TRAIL_DB_STD => Both standard audit (SYS.AUD$) and FGA audit(SYS.FGA_LOG$) records
#AUDIT_TRAIL_FGA_STD => Standard database fine-grained auditing (FGA) records in the SYS.FGA_LOG$ table
#AUDIT_TRAIL_FILES => Both operating system (OS) and XML audit trails
#AUDIT_TRAIL_OS => Operating system audit trail. This refers to the audit records stored in operating system files.
#AUDIT_TRAIL_XML => XML audit trail. This refers to the audit records stored in XML files.

Refrence

DBMS_AUDIT_MGMT ( Oracle Documentation 11.2 )

More Post on Oracle RDBMS Database 11g R2 ( Release 2 )

Oracle 11g Release 2 (11.2 ) New Features : SCAN - Single Client Access Name
11G R2 New Feature : Purge audit trail records using DBMS_AUDIT_MGMT
Oracle Database 11g Release 2 New Features : Edition based redefination

Last Month , I was there in Nasik ( One Small Town near Mumabi ). We roam around the city and visited many places in town. Our Driver suggested to visit Sual Vineyard. I am not very found of wine ,

But had very good time with friends in Napa Valley, CA, US, So thought let me experience Indian Vineyard.In Vineyard , we learned that owner of Sula Vineyard Mr. Rajeev is graduated in Economics and Industrial Engineering from Stanford and was working with Oracle in the Silicon Valley. Latter he left

Job and started Vineyard in India , today Sula is one of the well know Wine band in India. Story look interesting , so thought, to share with all(Links are given below ).


From Oracle to a Successful Vineyard: Rajeev Samant of Sula Wines
http://www.delhiwineclub.com/Interview/interview_with_rajeev2.asp
http://www.pagalguy.com/plugins/p2_news/printarticle.php?p2_articleid=912

NEW option in ADRCI purge acommand - UTSCDMP
There is new option in ADRCI for purge command - UTSCDMP

adrci> help purge

Usage: PURGE [[-i ]
[-age [-type ALERT INCIDENT TRACE CDUMP HM UTSCDMP]]]:

Purpose: Purge the diagnostic data in the current ADR home. If no
option is specified, the default purging policy will be used.

Options:
[-i id1 id1 id2]: Users can input a single incident ID, or a range of incidents to purge.
[-age ]: Users can specify the purging policy either to all the diagnostic data or the specified type. The data older than ago will be purged

[-type ALERT INCIDENT TRACE CDUMP HM UTSCDMP]:

Users can specify what type of data to be purged.
Examples:

purge
purge -i 123 456
purge -age 60 -type incident

There where some issue that directories cdmp_* in repositories (=$diagnostic_dest/Diag/rdbms/database_name/instance_name/bdump ) are not purging automatically. When you run following command, it remove cdmp_* directories, which is older the 3600 Minutes

adrci> purge -age 3600 -type UTSCDMP

The All India Oracle User Group with a 4 lakh -strong developer base and a community of seven-lakh users in India is organising a second meeting in Bangalore on October 22 to share experiences and provide insights into shape of things to come. It is the second largest technology network of professionals for Oracle after the US. Soon these TechNights will be held in other parts of India, such as Pune, Mumbai and Delhi.

Like several successful Oracle user groups around the world, AIOUG is also a not for profit organization formed by Oracle users for Oracle users. Let’s join hands across the country to make AIOUG a global lead user group.

AIOUG first TechNight held at Hyderabad, Please click here to see TechNight pictures

AIOUG started his first ever Technical session in India at Hyderabad… On … Friday, July 18th 2008, between 5:00 PM to 8:00 PM. Session started with quick introduction about AIOUG, there mission goal by Murali Vallath. Latter Phani Arega took technical session on "Efficient SQL Programming - Some Tricks and Tips" and Vivek Sharma on " Real Time Performance Tuning".

Both the technical session was quite good and they shared lots of real life examples in there presentation. Here is some photos from AIOUG TechNight

DBMS_STATS Enhancements in Oracle 11g Database
Virag Sharma virag123@gmail.com

We know in 10g and 11g there is automatic job that collect stats of database based on certain preferences settings , lets have a look what are these preferences and what are there default values and how DBMS_STATS is different in 11g from 10g

Changing Preferences for Statistics

Preference name Default Values

CASCADE

AUTO

DEGREE

AUTO

ESTIMATE_PERCENT

AUTO

METHOD_OPT

FOR ALL COLUMN SIZE AUTO

NO_INVALIDATE

AUTO

GRANULARITY

AUTO

PUBLISH ( New in 11g)

TRUE

INCREMENTAL new in 11g

FALSE

STALE_PERCENT (New in 11g)

10

#
# In Oracle 10g
#

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 6 19:04:57 2008

SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual;

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE

SQL>

SQL> SELECT dbms_stats.get_param('method_opt') FROM dual;

DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO

SQL> SELECT dbms_stats.get_param('GRANULARITY') FROM dual;

DBMS_STATS.GET_PARAM('GRANULARITY')
--------------------------------------------------------------------------------
AUTO

SQL> SELECT dbms_stats.get_param('NO_INVALIDATE') FROM dual;

DBMS_STATS.GET_PARAM('NO_INVALIDATE')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE

SQL> SELECT dbms_stats.get_param('DEGREE') FROM dual;

DBMS_STATS.GET_PARAM('DEGREE')
--------------------------------------------------------------------------------
NULL

SQL> SELECT dbms_stats.get_param('CASCADE') FROM dual;

DBMS_STATS.GET_PARAM('CASCADE')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_CASCADE

SQL> exec dbms_stats.set_param('ESTIMATE_PERCENT','100');

PL/SQL procedure successfully completed.

SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual;

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
---------------------------------------------------------------------
100

SQL> exec dbms_stats.RESET_PARAM_DEFAULTS();

PL/SQL procedure successfully completed.

SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual;

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
---------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE

->

GET_PARAM , RESET_GLOBAL_PREFS_DEFAULTS and SET_PARAM are obsolete in Oracle 11g.

In place of above procedures need to use following procedures GET_PREFS , RESET_GLOBAL_PREF_DEFAULTS and SET_GLOBAL_PREFS

SQL> Select dbms_stats.GET_PREFS('CASCADE') from dual;

DBMS_STATS.GET_PREFS('CASCADE')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_CASCADE

SQL> Select dbms_stats.GET_PREFS('DEGREE') from dual;

DBMS_STATS.GET_PREFS('DEGREE')
--------------------------------------------------------------------------------
NULL

SQL> Select dbms_stats.GET_PREFS('ESTIMATE_PERCENT') from dual;

DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE

SQL> Select dbms_stats.GET_PREFS('METHOD_OPT') from dual;

DBMS_STATS.GET_PREFS('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO

SQL> Select dbms_stats.GET_PREFS('NO_INVALIDATE') from dual;

DBMS_STATS.GET_PREFS('NO_INVALIDATE')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE

SQL> Select dbms_stats.GET_PREFS('GRANULARITY') from dual;

DBMS_STATS.GET_PREFS('GRANULARITY')
--------------------------------------------------------------------------------
AUTO

SQL> Select dbms_stats.GET_PREFS('PUBLISH') from dual;

DBMS_STATS.GET_PREFS('PUBLISH')
--------------------------------------------------------------------------------
TRUE

SQL> Select dbms_stats.GET_PREFS('INCREMENTAL') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL')
--------------------------------------------------------------------------------
FALSE

SQL> Select dbms_stats.GET_PREFS('STALE_PERCENT') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT')
--------------------------------------------------------------------------------
10

In Oracle 11g you can set preference not only on database level but on global level , schema level , table level as well. It really give more control to DBA for example on one table you want histograms should collected always or want to set METHOD_OPT => ‘FOR ALL COLUMNS SIZE 254’ on some tables

Procedure for setting preference on global , database , schema and table level are given below

1. SET_GLOBAL_PREFS

2. SET_DATABASE_PREFS

3. SET_SCHEMA_PREFS

4. SET_TABLE_PREFS

• Preference set on global apply for new objects or object which no preference available

• Database level preference will be applied on all objects in the database excluding the tables owned by Oracle. These tables can included by passing TRUE for the add_sys parameter of procedure.

• If you set preference value to NULL , it will set to Oracle default value

>

#
#Test case
#

SQL> create table test1(abc number);

Table created.

SQL> create table test2(abc number);

Table created.

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST1') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST1')
--------------------------------------------------------------------------------
10

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2')
--------------------------------------------------------------------------------
10

SQL> execute dbms_stats.set_table_prefs('SYS', 'TEST2', 'STALE_PERCENT', '35');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2')
--------------------------------------------------------------------------------
35

SQL> execute dbms_stats.set_global_prefs('STALE_PERCENT', '20');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST1') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST1')
--------------------------------------------------------------------------------
20

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2')
--------------------------------------------------------------------------------
35

SQL> exec dbms_stats.SET_DATABASE_PREFS('STALE_PERCENT', '15');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST1') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST1')
--------------------------------------------------------------------------------
20

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2')
--------------------------------------------------------------------------------
35

SQL> exec dbms_stats.set_database_prefs('STALE_PERCENT','30');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2')
--------------------------------------------------------------------------------
35

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'BUG', 'BUG_USER')
from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','BUG','BUG_USER')
--------------------------------------------------------------------------------
30

SQL> exec dbms_stats.SET_DATABASE_PREFS('STALE_PERCENT', '15');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2')
--------------------------------------------------------------------------------
35

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'BUG', 'BUG_USER')
from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','BUG','BUG_USER')
--------------------------------------------------------------------------------
15

Published and Pending Statistics

Statistics published by default when system/user/job collect stats. In Oracle 11g we have option to put newly collect stats in pending state and published latter once satisfied by performance testing based on new statistics.

In 10g we have face performance issue when collected stats on OLTP (24 X 7 ) database, probably because stats of one index/ table published before other. So collect stats in pending stat , test it and once satisfied with testing published it.

SQL> col name form a40
SQL> col VALUE form a6
SQL> col ISSES_MODIFIABL VALUE form a6
SQL> col ISSES_MODIFIABL form a6
SQL> col ISSYS_MODIFIABLE form a12
SQL> select name,value,isses_modifiable,issys_modifiable
from v$parameter
where name='optimizer_use_pending_statistics' ;

NAME VALUE ISSES_MODIFIABL ISSYS_MODIFI
---------------------------------------- ------ --------------- ------------
optimizer_use_pending_statistics FALSE TRUE IMMEDIATE

#
# Test Case
#

# By default Publish is set TRUE
#

SQL> select dbms_stats.get_prefs('PUBLISH') publish from dual
PUBLISH
---------
TRUE

SQL> select dbms_stats.get_prefs('PUBLISH', 'VIRSHARM', 'TEST1') publish
from dual;

PUBLISH
------------
TRUE

#
# Setting Publish false for TEST1 table
#

SQL> exec dbms_stats.set_table_prefs('VIRSHARM', 'TEST1', 'PUBLISH', 'false');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('PUBLISH', 'VIRSHARM', 'TEST1') publish
from dual;

PUBLISH
-------------
FALSE

SQL> execute dbms_stats.gather_table_stats('VIRSHARM', 'TEST1');

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed from user_col_pending_stats
where table_name ='TEST1'

TABLE_NAME LAST_ANALYZED
---------- ---------------
TEST1 30-APR-08

SQL> select table_name, last_analyzed from user_ind_pending_stats
where table_name ='TEST1';

no rows selected

SQL> select table_name, last_analyzed from user_col_pending_stats
where table_name ='TEST1';

TABLE_NAME LAST_ANALYZED
---------- ---------------
TEST1 30-APR-08

#
# Following views will have no record for table TEST1
# because stats not published
#

SQL> select table_name, last_analyzed from user_tables
where table_name = 'TEST1';

TABLE_NAME LAST_ANALYZED
------------------------------ -------------------
TEST1

SQL> select index_name, last_analyzed from user_indexes
where table_name ='TEST1';

no rows selected

SQL> select column_name, last_analyzed from user_tab_columns
where table_name='TEST1' ;

COLUMN_NAME LAST_ANALYZED
------------ ---------------
ABC

>

#
# Testing
#

SQL> ALTER SESSION SET optimizer_use_pending_statistics =TRUE;

Session altered.

#
# OR export / import STATS
#

SQL> exec dbms_stats.create_stat_table('VIRSHARM','STATS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_pending_stats(tabname=>'TEST1',stattab=>'STATS');

PL/SQL procedure successfully completed.

#
# Use expdp /impdp ot exp/imp to import stats in TEST/QA database
#

SQL> exec

dbms_stats.import_table_stats(ownname=>'VIRSHARM',tabname=>'TEST1',
stattab=>'STATS');

select column_name, last_analyzed from user_tab_columns
wheretable_name='TEST1';

#
# Published stats
# Publish the Stats to Data Dictionary for Optimizer Usage
#

SQL> exec dbms_stats.publish_pending_stats(tabname=>'TEST1');

PL/SQL procedure successfully completed.

If Stats published and performance become worse , then you can restore old version of stats. Oracle will manage the historical statistics repository, purging the statistics on a regular basis, by default every 31 days

By default Oracle keep stats for 31 day after that it purge. Retention can be increased useing following

SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION ( 60 );

PL/SQL procedure successfully completed.

11g RAC Database hanged or appears to hanged

Around lunch time 1:00 PM database hanged. When we tried to login as sysdba
our sqlplus session also hanged. we not able to login on either of node of RAC.
Thought to kill all the remote connection , so we will able to login

ps -aef |grep LOCAL=NO |awk '{ print $2}' |xargs kill -9

When your database hanged and you not able to login, we have used following procedure
to create hang analysis dump

sqlplus /nolog

SQL> set _prelim on
SQL> connect / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug -g all hanganalyze 3
SQL> oradebug -g all hanganalyze 3
Cycle 1: (1/1965)--(1/1839)
Hang Analysis in /u04/oraout/abcap/11gdiag/diag/rdbms/abcap/abc2ap/trace/abc2ap_diag_9269.trc

We also generate ASH report for that(=hang) duration.
Here is what , hang analysis show

#
# Session id is in RED colour for further analysis
#

Chains most likely to have caused the hang:
[a] Chain 1 Signature: 'row cache lock'<='row cache lock' (cycle) Chain 1 Signature Hash: 0x75bdd0c

===============================================================================
Cycles:

-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (abcap.abc1ap)
os id: 27244
process id: 343, oracle@abc232
session id: 1839
session serial #: 12
}
is waiting for 'row cache lock' with wait info:
{
p1: 'cache id'=0x7
p2: 'mode'=0x0
p3: 'request'=0x5
time in wait: 0.526566 secs
heur. time in wait: 18.534318 secs
timeout after: 2.473434 secs
wait id: 49
blocking: 2 sessions
current sql:
short stack: <-ksedsts()+285<-ksdxfstk()+22<-ksdxcb()+1599<-sspuser()+102<-semtimedop()+36<-sskgpwwait()+211<-skgpwwait()+104<-ksliwat()+1256<-kslwaitctx()+135<-kqrigt()+1592<-kqrLockAndPinPo()+420<-kqrpre1()+915<-kqrpre()+57<-kziasfc()+235<-kpolnb()+5279<-kpoauth()+582<-opiodr()+991<-ttcpip()+1066<-opitsk()+1275<-opiino()+882<-opiodr()+991<-opidrv()+462<-sou2o()+91<-opimai_real()+109<-ssthrdmain()+142<-main()+116<-__libc_start_main()+211<-_start()+33 wait history: 1. event: 'row cache lock'
wait id: 48 p1: 'cache id'=0x7
time waited: 3.001908 secs p2: 'mode'=0x0
p3: 'request'=0x5
2. event: 'row cache lock'
wait id: 47 p1: 'cache id'=0x7
time waited: 3.001615 secs p2: 'mode'=0x0
wait id: 47 p1: 'cache id'=0x7
time waited: 3.001615 secs p2: 'mode'=0x0
p3: 'request'=0x5
3. event: 'row cache lock'
wait id: 46 p1: 'cache id'=0x7
time waited: 3.000437 secs p2: 'mode'=0x0
p3: 'request'=0x5
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (abcap.abc1ap)
os id: 27941
process id: 125, oracle@abc232
session id: 1965
session serial #: 476
}
which is waiting for 'row cache lock' with wait info:
{
p1: 'cache id'=0x7
p2: 'mode'=0x0
p3: 'request'=0x3
time in wait: 0.462402 secs
heur. time in wait: 18.536906 secs
timeout after: 2.537598 secs
wait id: 30
blocking: 2 sessions
current sql:
short stack: <-ksedsts()+285<-ksdxfstk()+22<-ksdxcb()+1599<-sspuser()+102<-semtimedop()+36<-sskgpwwait()+211<-skgpwwait()+104<-ksliwat()+1256<-kslwaitctx()+135<-kqrigt()+1592<-kqrLockAndPinPo()+420<-kqrpre1()+915<-kqrpre()+57<-kkdlgui()+186<-kziavdb.()+2023<-kziaia.()+220<-kpolnb()+580<-kpoauth()+582<-opiodr()+991<-ttcpip()+1066<-opitsk()+1275<-opiino()+882<-opiodr()+991<-opidrv()+462<-sou2o()+91<-opimai_real()+109<-ssthrdmain()+142<-main()+116<-__libc_start_main()+211<-_start()+33 wait history: 1. event: 'row cache lock' wait id: 29 p1: 'cache id'=0x7 time waited: 3.001776 secs p2: 'mode'=0x0 p3: 'request'=0x3 2. event: 'row cache lock' wait id: 28 p1: 'cache id'=0x7 time waited: 3.001578 secs p2: 'mode'=0x0 p3: 'request'=0x3 3. event: 'row cache lock' wait id: 27 p1: 'cache id'=0x7

When checked above session ( as marked in RED in above windows ) in ASH table.
We found both sessions are belong to “sys” user.

Lets have a look on ASH report, one which we created, when database was hanged

Top User Events

Event

Event Class

% Event

Avg Active Sessions

resmgr:cpu quantum

Scheduler

80.66

55.53

row cache lock

Concurrency

7.37

5.08

db file sequential read

User I/O

3.55

2.45

null event

Other

2.66

1.83

CPU + Wait for CPU

CPU

1.90

1.31

Top Event P1/P2/P3 Values

Event

% Event

P1 Value, P2 Value, P3 Value

% Activity

Parameter 1

Parameter 2

Parameter 3

resmgr:cpu quantum

80.75

"1","0","0"

60.85

location

"2","0","0"

17.81

"3","0","0"

2.09

row cache lock

7.38

"10","0","3"

7.31

cache id

mode

request

db file sequential read

3.58

"59","23006","1"

0.20

file#

block#

blocks

<

So ASH report is showing different story , that mean database was not hanged , it appeared to hanged due to event “resmgr:cpu quantum”

As per

Oracle® Database Reference
11g Release 1 (11.1)
Part Number B28320-01

resmgr: cpu quantum

The session is waiting to be allocated a quantum of cpu. This event occurs when the resource manager is enabled and is throttling CPU consumption. To reduce the occurrence of this wait event, increase the CPU allocation for the sessions's current consumer group.

Wait Time: The time the session waited to acquire a CPU quantum

We have not enabled any resource plan , how it come into picture,In Oracle
11g all predefine maintenance windows use DEFAULT_MAINTENANCE_PLAN resource plan and Automatedmaintenance tasks run under its subplan ORA$AUTOTASK_SUB_PLAN supportEmptyParas]-->which has 25% resource allocation.

Possible Work around

Disable resource plan

Last week Oracle University arrange Seminar by Performance Management guru Craig Shallahamer in India. Craig brings his unique experiences to many as a keynote speaker, a sought after teacher, a researcher and publisher for improving Oracle performance management, and also the architect of Horizone, OraPub's service level management solution.<?xml:namespace prefix = o />

His way of teaching is very very good , also contents of seminar is different and very effective. He focused on accurate performance diagnosis of even the most complex Oracle systems and how oracle interact with OS and application’s. I have been reading Craig’s papers/articles for long time , but never got full/big picture. After attending seminar his papers/articles look easy to understand.

Focus point of seminar was how to pin point issue area and different method to resolve it. He also cover some area of Mutex, IMU ( In memory Undo ) and performance forecasting etc. The way he teach latch Internals, anybody can understand it, even small kids J, he Create an honest and understandable story explaining the problem, the solutions, and how to best implement your solutions in a complex and highly available Oracle environment.

Second day of training , we had little tough time , as next to our conference hall in Oberoi Hotel, New Delhi , Shahrukh Khan( One of India Famous actor) was coming to inugrate/launch "Kya Aap Paanchvi Paas Se Tez Hain" new serial on Star Plus channel. But Craig enjoy music and continue with training

What CPU Jan 2008 Patch will not tell you

File system space :- opatch suppose to check files system space as a part of Running prerequisite checks... and start apply patch when enough space is there.

Patching component oracle.rdbms, 11.1.0.6.0...

Updating archive file "/u01/app/oracle/product/11.1.0.6/lib/libserver11.a" with "lib/libserver11.a/kzsr.o"
Updating archive file "/u01/app/oracle/product/11.1.0.6/lib/libserver11.a" with "lib/libserver11.a/kzu.o"

The following actions have failed:

Archive not applied /home/oracle/patch/CPU2008/6646866/6650135/files/lib/libserver11.a/kzsr.o to /u01/app/oracle/product/11.1.0.6/lib/libserver11.a... ''

Archive not applied /home/oracle/patch/CPU2008/6646866/6650135/files/lib/libserver11.a/kzu.o to /u01/app/oracle/product/11.1.0.6/lib/libserver11.a... ''

Do you want to proceed? [yn]

Well I came out with N option. When we started opatch again , it gave error some thing like spacecheck fail.

So make sure you have enough space on file system.

In case of RAC database, CPU patch readme.txt will not tell you that one need to set cluster_database = false , before starting database in upgrade mode(“startup upgrade”). For compiling views and objects.

So make sure, in RAC environment you have set cluster_database = false before starting database in upgrade mode

<?xml:namespace prefix = o />

Small change make difference.

How small , small things make difference , here is one live example
One of my friend want to learn 11g, so she downloaded 11g and started installing on

Linux Box and created Database manually. Next time when she logged in, she did not know where she installed 11g , since she created database manually , so there was no entry in “oratab”

I remember command “pwdx” on Unix Solaris , which give current working directory of processes

$ uname -a

SunOS mysun 5.8 Generic_xyz sun4u sparc SUNW,Ultra-Enterprise

MYSUN:oracle> (10.1.0.4) /usr/proc/bin

$ ps -aef grep pmon

oracle 2424 1 0 Jan 18 ? 13:39 ora_pmon_test
oracle 8337 13002 0 05:31:47 pts/7 0:00 grep pmon

mysun:oracle> (10.1.0.4) /usr/proc/bin

$ pwdx 2424
2424: /u01/app/oracle/product/10.1.0.4/dbs

<

But on Linux(RHEL 4 ) there is no command like “pwdx” . In linux you can check current working directory of processes ( ORACLE_HOME/dbs i.e lock file location ) in /proc/

<?xml:namespace prefix = o /?>

[root@apps001 proc]# ps -aef grep pmon

oracle 2826 1 0 20:55 ? 00:00:00 xe_pmon_XE
oracle 8268 1 0 21:21 ? 00:00:00 ora_pmon_orcl11g
root 23180 13728 0 23:19 pts/2 00:00:00 grep pmon

[root@apps001 proc]# ls -l /proc/8268

total 0
dr-xr-xr-x 2 oracle oinstall 0 Jan 27 23:20 attr
-r-------- 1 oracle oinstall 0 Jan 27 23:20 auxv
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:19 cmdline
lrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 cwd -> /u01/app/oracle/11.1.0/dbs
-r-------- 1 oracle oinstall 0 Jan 27 23:20 environ
lrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 exe -> /u01/app/oracle/11.1.0/bin/oracle
dr-x------ 2 oracle oinstall 0 Jan 27 23:20 fd
-rw-r--r-- 1 oracle oinstall 0 Jan 27 23:20 loginuid
-r-------- 1 oracle oinstall 0 Jan 27 23:20 maps
-rw------- 1 oracle oinstall 0 Jan 27 23:20 mem
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:20 mounts
lrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 root -> /
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:18 stat
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:20 statm
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:16 status
dr-xr-xr-x 3 oracle oinstall 0 Jan 27 23:20 task
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:20 wchan

>

<

[oracle@apps001 oracle]$ cd /u01/app/oracle/11.1.0/dbs

[oracle@apps001 dbs]$ ls
hc_orcl11g.dat initdw.ora init.ora lkORCL11G orapworcl11g spfileorcl11g.ora

[oracle@apps001 dbs]$ /sbin/fuser *

hc_orcl11g.dat: 8268 8268m 8270 8270m 8274 8274m 8276 8276m 8278 8278m 8282 8282m 8284 8284m 8286 8286m 8288 8288m 8290 8290m 8292 8292m 8294 8294m 8296 8296m 8298 8298m 8300 8302 8312 8312m 8314 8314m 8316 8316m 8320 8322 8394 8394m 22755 25372

lkORCL11G: 8268 8276 8278 8284 8286 8288 8290 8292 8294 8296 8298 8312 8314 8316 8320 8322 8394 22755 25372

I asked her to create one small shell script to source oracle 11g environment variable ,
she wrote following script

ORACLE_HOME=/u01/app/oracle/11.1.0/
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
ORACLE_SID=orcl11g
export ORACLE_SID

~

"11g.env" 7L, 136C written

[oracle@apps001 ~]$chmod 755 11g.env
[oracle@apps001 ~]$ . ./11g.env

She called me after some time and said , after sourcing 11g environment variable , when she try to connect oracle 11g as sysdba , it says “connect to ideal instance”

[oracle@apps001 ~]$ . ./11g.env

[oracle@apps001 ~]$ sqlplus “/ as sysdba”
SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jan 27 23:45:42 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.

Ctl-d

[oracle@apps001 ~]$ ps -aef grep pmon
oracle 2826 1 0 20:55 ? 00:00:00 xe_pmon_XE
oracle 8268 1 0 21:21 ? 00:00:00 ora_pmon_orcl11g
oracle 24791 24446 0 23:46 pts/4 00:00:00 grep pmon

[oracle@apps001 8268]$ cd /u01/app/oracle/11.1.0/dbs

[oracle@apps001 dbs]$ /sbin/fuser *

hc_orcl11g.dat: 8268 8268m 8270 8270m 8274 8274m 8276 8276m 8278 8278m 8282 8282m 8284 8284m 8286 8286m 8288 8288m 8290 8290m 8292 8292m 8294 8294m 8296 8296m 8298 8298m 8300 8302 8312 8312m 8314 8314m 8316 8316m 8320 8322 8394 8394m 22755 25372

lkORCL11G: 8268 8276 8278 8284 8286 8288 8290 8292 8294 8296 8298 8312 8314 8316 8320 8322 8394 22755 25372

# It means database is up (may be nomount , mount or open mode )
#

When ran “fuser” on lock file oracle BG processes are connected to lock file, It means database is up (may be nomount , mount or open mode )

Checked alert.log , to make sure things are fine. It shows database is OPEN

[oracle@apps001 oracle]$ adrci
ADRCI: Release 11.1.0.6.0 - Beta on Sun Jan 27 23:52:16 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

ADR base = "/u01/app/oracle"
adrci> set editor vi
adrci> show alert

Choose the alert log from the following homes to view:

1: diag/tnslsnr/apps001/listener
2: diag/rdbms/orcl2/orcl2
3: diag/rdbms/stdorcl2/stdorcl2
4: diag/rdbms/orcl11g/orcl11g
Q: to quit

Please select option:4

space available in the underlying filesystem or ASM diskgroup.
2008-01-27 21:22:07.366000 +05:30
Completed: ALTER DATABASE OPEN
2008-01-27 21:22:26.499000 +05:30
Starting background process CJQ0
CJQ0 started with pid=26, OS id=8394
2008-01-27 21:23:24.231000 +05:3
Thread 1 advanced to log sequence 3
Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/orcl11g/redo03.log
2008-01-27 21:29:03.616000 +05:30

Finally decided to check shell script , which source 11g environment variable. There suppose to be no issue at script because we able to run “adrci” and able to see alert.log

[oracle@apps001 ~]$vi 11g.env
ORACLE_HOME=/u01/app/oracle/11.1.0/
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
ORACLE_SID=orcl11g
export ORACLE_SID

~
~
"11g.env" 7L, 136C written

#
# I made little change in script i.e. removed last “/” from ORACLE_HOME
#

vi 11g.env
ORACLE_HOME=/u01/app/oracle/11.1.0
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
ORACLE_SID=orcl11g
export ORACLE_SID

~
~
"11g.env" 7L, 136C written

After I removed last “/” from ORACLE_HOME , she able to connect to database J

[oracle@apps001 ~]$ . ./11g.env

[oracle@apps001 ~]$ sys

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 28 00:00:54 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

So small change make difference.

Oracle 11g Security Enhancements Part – 1

New parameters have been added to the Oracle Database 11g to enhance the default security of the database.

• SEC_RETURN_SERVER_RELEASE<?xml:namespace prefix = o />
• SEC_PROTOCOL_ERROR_FURTHER_ACTION
• SEC_PROTOCOL_ERROR_TRACE_ACTION
• SEC_MAX_FAILED_FAILED_LOGIN_ATTEMPTS
• SEC_DISABLE_OLDER_ORACLE_RPCS*

These parameters are system wide and static.

• Release of server information restriction

You can restrict the display of the database version banner to unauthenticated clients by setting the SEC_RETURN_SERVER_RELEASE_BANNER initialization parameter in the initsid.ora initialization parameter file to either YES or NO. By default, SEC_RETURN_SERVER_RELEASE_BANNER is set to FALSE.

SQL> show parameter SEC_RETURN_SERVER_RELEASE_BANNER

NAME TYPE VALUE

----------------------------- -------------------- ---------------------
sec_return_server_release_banner boolean FALSE

When set to true the full banner is displayed. When the value is set to FALSE, a limited generic banner is displayed.

• Protect against denial of Service (DoS) attacks

The two parameters shown specify the actions to be taken when the database receives bad packets from a client. The assumption is that the bad packets are from a possible malicious client. The SEC_PROTOCOL_ERROR_FURTHER_ACTION parameter specifies what action is to be taken with the client connection: Continue, drop the connection, or delay accepting requests.

# Continue connection after 10 bad packet

SEC_PROTOCOL_ERROR_FURTHER_ACTION = Continue

#Delay 4 Sec before sever accept next connection

SEC_PROTOCOL_ERROR_FURTHER_ACTION = Delay,4

# Drop connection after 10 bad packet

SEC_PROTOCOL_ERROR_FURTHER_ACTION = Drop,10

The other parameter SEC_PROTOCOL_ERROR_TRACE_ACTION specifies a monitoring action: NONE, TRACE(Default) , LOG, or ALERT.

# DEFAULT Creates the trace files, but it is useful for debugging purposes,

SEC_PROTOCOL_ERROR_TRACE_ACTION = Trace

# Writes a short, one-line error message to the server trace file and alert log.

SEC_PROTOCOL_ERROR_TRACE_ACTION = Alert

# Writes a short, one-line message to the server trace file.

SEC_PROTOCOL_ERROR_TRACE_ACTION = Log

# Configures the server to ignore the bad packets and does not generate any trace files or

SEC_PROTOCOL_ERROR_TRACE_ACTION = None

>

SQL> show parameter SEC_PROTOCOL_ERROR_FURTHER_ACTION

NAME TYPE VALUE
------------------------------- ------------------- ------------------------
sec_protocol_error_further_action string CONTINUE

SQL> show parameter SEC_PROTOCOL_ERROR_TRACE_ACTION

NAME TYPE VALUE
---------------------- ------------------------- -----------------------
sec_protocol_error_trace_action string TRACE

• Protect against intruder

If profile is not enabled then intruder can try unlimited number of authenticated requests with different user names and passwords in an attempt to gain access to the database.

A new initialization parameter SEC_MAX_FAILED_LOGIN_ATTEMPTS that has a default setting of 10 causes a connection to be automatically dropped after the specified number of attempts. This parameter is enforced even when the password profile is not enabled.

SQL> show parameter SEC_MAX_FAILED_LOGIN_ATTEMPTS

NAME TYPE VALUE
-------------------------- --------------------------------------------------
sec_max_failed_login_attempts integer 10

>

The sqlnet.ora INBOUND_CONNECT_TIMEOUT parameter and the FAILED_LOGIN_ATTEMPTS initialization parameter also restrict failed logins, but the difference is that these two parameters only apply to valid user accounts.

>

Recently come across new parameter SEC_DISABLE_OLDER_ORACLE_RPCS on Database Error Messages 11g Release 1

>
ORA-03139: This OCI call has been disabled by the DBA
Cause: The SEC_DISABLE_OLDER_ORACLE_RPCS initialization parameter was enabled.
Action: Contact the Database Administrator

But when searched oracle documentation not found any detail about it , also not found any detail about it on Database

SQL> show parameter SEC_DISABLE_OLDER_ORACLE_RPCS

SQL>