11g New Features List

Parameter List :-

MEMORY_TARGET
MEMORY_MAX_TARGET
-V$MEMORY_TARGET_ADVICE
DIAGNOSTIC_DEST
-$ORACLE_BASE/diag/rdbms/$INSTANCE_NAME/$ORACLE_SID
-$ORACLE_HOME/log.
RESULT_CACHE_MODE
RESULT_CACHE_MAX_RESULT
RESULT_CACHE_MAX_SIZE
DDL_LOCK_TIMEOUT
DB_ULTRA_SAFE
SEC_CASE_SENSITIVE_LOGON
SEC_MAX_FAILED_LOGIN_ATTEMPTS

-------------------------------------------------
New Features of 11g
-------------------------------------------------
1)database reply
2)sql performance analyzer (SPA,DBMS_SQLPA)
3)sql repair advisor

4)Data Recovery Advisor with RMAN (DRA)
-RMAN>LIST FAILURE;
-RMAN>list failure 722 detail;
-RMAN>LIST OPEN;
-RMAN>LIST CLOSED;
-RMAN>ADVISE FAILURE;
-RMAN>REPAIR FAILURE PREVIEW;
-RMAN>repair failure;

5)sql test case builder (DBMS_SQLDIAG SQL Test Case Builder is a SQL script that contains the commands
required to recreate all the necessary objects and the environment)

6)Automatic Diagonastic repositroy (ADR)
-adrci tool
-Problems and Incidents
-V$DIAG_INFO
-ips create package
-ips generate package 1 in /u01/myfiles/incidents incremental

6.1)Database Health Monitor (DHM)
-V$HM_CHECK
-V$HM_RUN
-V$HM_FINDING
-V$HM_RECOMMENDATION
-DBMS_HM
-adrci>show hm_run

7)Oracle flashback
LogMinner
Transaction callout
Flashback data archive

8)Database connection resident pooling
9)Comparing and Synchronzing database objects
10)Changing stats preference
Multicolumn statistics
Expression statistics
11)SQL Plan management
12)Access control list
13)Real time sql monitor
14)Pl/SQL Native compliation
15)Adaptive cursor sharing
16)Rman
Block Recovery
Rman substitution variable
Rman configuration parameter
multisection backup
creating archival backup
validate (new command)
Archive log deletion policy
Active database duplication
Importing and moving recovery catalog
virtual private catalog
17)Datapump Utility
encryption enhancement
remap_data
remap_table
reuse_dumpfile
18)ASM
sysasm privilage and osasm group
diskgroup attribute
asmcmd utility
remap
md_backup
md_restore
19)Parition enhancement
-Reference Partitions
-Interval Partitioning
-Extended Composite Partitioning
Composite List-Range
Composite List-Hash
Cmposite List-List
-Virtual Column-Based Partitioning
-System Partitioning
20)Active dataguard
Stapshot standby
------------------------------------------------------
RAC 11g New Features
------------------------------------------------------
1)SCAN(Single Client Access Network) and GNS (Grid Naming Service)
2)CRS file in ASM
3)VD autobackup
4)CTSS (Cluster time synchronisation service)
5)ASM Cluster file system (ACFS)
6)OLR (Oracle Local Registry)
7)Cluster + ASM having only one home called GRID home

How to use Oracle LogMiner for tracing DDL

With the help of Oracle LogMiner we can trace the DDL fire. Please note that this is session dependent.

Below are the steps to be followed.

1)Set the parameter UTL_FILE_DIR (this is static parameter u need to take bounce of db to reflect it)

Sql>alter system set UTL_FILE_DIR=’D:\oracle\oradata\utl’ scope=spfile;

And take the bounce of database.

2)Add the logminer dictionary
Sql>
EXECUTE dbms_logmnr_d.build(Dictionary_FileName=>'Dictionary.ora',Dictionary_Location=>'D:\oracle\oradata\utl',OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

This will create one flat file 'Dictionary.ora' in the give UTL_FILE_DIR location.

3)Add the archive log file to Mine
Now u have to get the time between view u have created (approx ) and from ls –ltr u can see the archive log generated time.

Sql>
EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.new,LogFileName=>'D:\oracle\oradata\archive\1_52_743911224.LOG');
EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.addfile,LogFileName=>'D:\oracle\oradata\archive\1_51_743911224.LOG');
EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.addfile,LogFileName=>'D:\ORACLE\ORADATA\ODSPROD\REDO01.LOG');

Now check whether this archived log register with logminer

Sql>select filename from V$LOGMNR_LOGS;

4) Start the logmining

Sql>
EXECUTE dbms_logmnr.start_logmnr(DictFileName=>'D:\oracle\oradata\utl\Dictionary.ora',OPTIONS =>DBMS_LOGMNR.DDL_DICT_TRACKING);

5) Once its successfully executed now u can see the DDL statement.

Sql> SELECT USERNAME, SQL_REDOFROM V$LOGMNR_CONTENTS WHERE USERNAME = 'TEST' AND OPERATION = 'DDL';

ORA-14450: attempt to access a transactional temp table already in use

Step to resolve this error.

1)Lock the table in exclusive mode

sql>lock table <username>.<temp_table> in exclusive mode;

2)Leave this session as it is and take new session

3) Take another session and execute below command

sql>SELECT * FROM v$lock
    WHERE id1 = (SELECT object_id FROM all_objects WHERE owner =<schema_name> AND object_name =<glb temp table>);

sql>ALTER SYSTEM KILL SESSION '<sid>,<serial#>';

4)After all the session get killed (confirm once)

5)perform the DDL on table;

sql>ALTER TABLE <username>.<temp_table>MODIFY(CONVERSION_FACTOR_VAL NUMBER (20,9));

6)Release the lock which we held in step (1) by below command

sql>rollback;