Certain things in this world

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

Module name for logon trigger in 11g R2 AWR report

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

Oracle Database 11g Release 2 New Features : Edition based redefination

Oracle Database 11g Release 2 New Features : Edition based redefination
Virag Sharma

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

Oracle 11g Release 2 (11.2 ) New Features : SCAN - Single Client Access Name

Oracle 11g R2 (11.2 ) New Features : SCAN - Single Client Access Name
Virag Sharma

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

11G R2 New Feature : Purge audit trail records using DBMS_AUDIT_MGMT

11G R2 New Feature : Purge audit trail records using DBMS_AUDIT_MGMT
Virag Sharma

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