Latest Post

11g - Security New Features

Written By askMLabs on Wednesday, September 30, 2009 | 12:40 PM

11g - Security New Features :


1) A new view to know the users with default passwords (DBA_USERS_WITH_DEFPWD)
2) Now the passwords are case sensitive
3) Making SYSDBA password case sensitive
orapwd file=orapwPRODB3 password=abc123 entries=10 ignorecase=n
4) Profiles and Password Verify Function
5) Improved Out-of-Box Auditing
6) Transparent Tablespace Encryption
7) Encryption of Data Pump Dumpfiles
8 ) Access Control Lists for UTL_TCP/HTTP/SMTP
9) Data Masking

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

Run the following script to add the 11g security feature "profiles and password verification"
$ORACLE_HOME/rdbms/admin/verify_fnction_11g

The script attaches the function to the profile DEFAULT, which is the default profile for all users.

Profiles and function details :
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION verify_function_11G;

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

Transparent Tablespace Encryption :
In 10g -> Transparent Data Encryption -> DBMS_CRYPTO and DBMS_OBFUSCATION_TOOLKIT ->
In 11g -> Transparent Tablespace Encryption
DBA_TABLESPACES -> column ENCRYPTED

V$ENCRYPTED_TABLESPACES -> what type of encryption is enabled for the tablespace.

Procedure:
===========

1) Create a wallet directory $ORACLE_BASE/admin/wallet.
2) Create the encryption key using
SQL> alter system set encryption key identified by "abcd1234!";
3) Issue the following command to open the wallet for encryption
SQL> alter system set wallet open identified by "abcd1234!"
4) Create a tablespace with encryption option and also create some objects in it

Encryption of Data Pump Dumpfiles:
A new parameter called ENCRYPTION included.

Procedure
=========

1) Create a wallet directory $ORACLE_BASE/admin/wallet.
2) Create the encryption key using
SQL> alter system set encryption key identified by "abcd1234!";
3) Issue the following command to open the wallet for encryption
SQL> alter system set wallet open identified by "abcd1234!"
4) Export the data using the following syntax
$expdp system/xxxx tables=sh.sales dumpfile=sales_bkp.dmp directory=EXP_DIR encryption=data_only encryption_algorithm=aes128
5) Verification by
$ cat /u01/app/oracle/exports/sales_bkp.dmp | grep "region"

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

Data Masking :
Datapump new parameter remap_data

Data masking to mask the sensitive data when we are creating a test instance from production.

Procedure
=========
1) Create a function or procedure which generates a randon key and returns it.
2) Export the data using the above function to mask the sensitive data ..
$expdp system/xxxxx tables=sh.sales dumpfile=sales_bkp.dmp directory=EXP_DIR remap_data=sh.sales:pkg_to_msk.fun_mask
[ remap_data = [<SchemaName>.]<TableName>.<ColumnName>:[<SchemaName>.]<PackageName>.<FunctionName> ]
3) If the data to the target system using
$impdp system/xxxxx tables=sh.sales dumpfile=sales_bkp.dmp directory=EXP_DIR remap_data=sh.sales:pkg_to_msk.fun_mask

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

Users with Default Passwords and Password Case Sensitive :
SQL> conn sh/sh
Connected.
SQL> conn sh/Sh
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn /as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon = false;

System altered.

SQL> conn sh/sh
Connected.
SQL> conn sh/SH
Connected.
SQL> alter system set sec_case_sensitive_logon =true;
alter system set sec_case_sensitive_logon =true
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn /as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon =true;
System altered.

SQL> desc DBA_USERS_WITH_DEFPWD
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                  NOT NULL VARCHAR2(30)

SQL> select count(1) from DBA_USERS_WITH_DEFPWD;

COUNT(1)
----------
24

SQL> select * from DBA_USERS_WITH_DEFPWD where username='SCOTT';

USERNAME
------------------------------
SCOTT

SQL> alter user scott identified by Tiger1;

User altered.

SQL> select * from DBA_USERS_WITH_DEFPWD where username='SCOTT';

no rows selected

SQL> alter user scott identified by tiger;

User altered.

SQL> select * from DBA_USERS_WITH_DEFPWD where username='SCOTT';

USERNAME
------------------------------
SCOTT

SQL> desc dba_users
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                  NOT NULL VARCHAR2(30)
USER_ID                                   NOT NULL NUMBER
PASSWORD                                           VARCHAR2(30)
ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
LOCK_DATE                                          DATE
EXPIRY_DATE                                        DATE
DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
CREATED                                   NOT NULL DATE
PROFILE                                   NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
EXTERNAL_NAME                                      VARCHAR2(4000)
PASSWORD_VERSIONS                                  VARCHAR2(8)
EDITIONS_ENABLED                                   VARCHAR2(1)
AUTHENTICATION_TYPE                                VARCHAR2(8)

SQL> select username,user_id,password, password_versions from dba_users where username in ('SH','SCOTT');

USERNAME        USER_ID PASSWORD   PASSWORD
--------------- ------- ---------- --------
SH                   88            10G 11G
SCOTT                84            10G 11G
Note the column PASSWORD_VERSIONS, which is new in Oracle Database 11g. This column signifies the case
sensitivity of the password. The value "10G 11G" signifies that the user was either created in 10g and migrated to 11g or
created in 11g directly.

SQL> desc user$
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USER#                                     NOT NULL NUMBER
NAME                                      NOT NULL VARCHAR2(30)
TYPE#                                     NOT NULL NUMBER
PASSWORD                                           VARCHAR2(30)
DATATS#                                   NOT NULL NUMBER
TEMPTS#                                   NOT NULL NUMBER
CTIME                                     NOT NULL DATE
PTIME                                              DATE
EXPTIME                                            DATE
LTIME                                              DATE
RESOURCE$                                 NOT NULL NUMBER
AUDIT$                                             VARCHAR2(38)
DEFROLE                                   NOT NULL NUMBER
DEFGRP#                                            NUMBER
DEFGRP_SEQ#                                        NUMBER
ASTATUS                                   NOT NULL NUMBER
LCOUNT                                    NOT NULL NUMBER
DEFSCHCLASS                                        VARCHAR2(30)
EXT_USERNAME                                       VARCHAR2(4000)
SPARE1                                             NUMBER
SPARE2                                             NUMBER
SPARE3                                             NUMBER
SPARE4                                             VARCHAR2(1000)
SPARE5                                             VARCHAR2(1000)
SPARE6                                             DATE

SQL> select user#,password from user$ where user# in (88,84);

USER# PASSWORD
---------- -------------------------
84 F894844C34402B67
88 54B253CBBAAA8C48

imp information

Q) What is  napply option with opatch ?
ANS :Installs n number of patches at a time to several oracle homes.
The command to apply the following patches at a time is ...
8447875  8534394  8537027

-bash-3.00$ pwd
/test/disco/product/disco_1012/PATCHES/8537032
-bash-3.00$ ls
8447875  8534394  8537027  README.html  README.txt  remove_demo.sh
-bash-3.00$ $ORACLE_HOME/OPatch/opatch napply -invPtrLoc $ORACLE_HOME/oraInst.loc
..
..
..

-bash-3.00$ $ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc | grep 8447875
3) Patch  8447875 applied on Wed Sep 09 19:38:39 CDT 2009
[ Bug fixes: 6490362 8447875 8351021  ]

-bash-3.00$ $ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc | grep 8534394

2) Patch  8534394 applied on Wed Sep 09 19:46:26 CDT 2009
[ Bug fixes: 6350565 6079585 4175906 5901912 6864078 4486132 5896963 6647005 5095815 4519477 5347751 4691191 4754900 5861360 8290534 5382595 5071931 5458543 6607951 5179574 4329444 5029950 5464895 5029952 5029954 5738539 5648727 4402808 5631915 4871035 5091108 5114396 6455161 5584790 4605877 4751932 5751672 5490845 6753516 8534394 3345756 5933477 7592360 5094098 5015557 4679094 5154689 5675556 5222931 5910829 5227879 4152843 4661844 6079603 5637094 7044603 4905112 6016022 8290629 4166537 7120513 5276400 7154097 7154098 5408664 6395024 4146291 6397568 5901877 4768040 5049074 7022400 5960451 5490935 5049077 4593539 4680009 4542188 4555795 5406923 4359124 5258410 5689908 4969005 3962946 3743912 5648102 5057964 5014128 4873311 4439469 4331689 6705965 4597251 4903532 6055387 7576788 5650178 5225797 4047969 4554284 5376215 4874628 5401921 5151518 4458415 4900129 5226235 7375686 5095648 4561867 5239126 4712638 4925103 5354517 4745776 4939157 5998987 6404864 7300525 5501362 4587572 4969029 6270140 5055442 7334756 6639839 4492467 5222032 5151675 5242647 6999528 7137797 6864202 6737308 4587431 5605370 6647068 4335559 6826532 5417371 4671216 5065930 4575854 5355257 6130365 5243019 7173149 4966417 6639553 5884075 4899479 4610820 3837600 5092688 4528572 4449900 4601861 6009358 4226736 6404447 4348230 5233111 5644862 4197970  ]
-bash-3.00$ $ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc | grep 8537027

1) Patch  8537027 applied on Wed Sep 09 19:47:15 CDT 2009
[ Bug fixes: 7379127 7608327 7156655 7156648 8265594 7135493 8537027 7135488 8298232 7379122  ]
-bash-3.00$

Check the link : http://download.oracle.com/docs/cd/B28359_01/em.111/b31207/oui7_opatch.htm#CHDCIGJC

Q) How to check whether  the application MTs are with shared tech stack or not ?
ANS : Following commands should result in the same value

grep s_iASconfig_home $CONTEXT_FILE
grep s_weboh_oh $CONTEXT_FILE

Q)How to install enterprise manager dbconsole in a DHCP enabled system?
Ans : If you want to install an em dbconsole in a local machine which is DHCP inabled , it will fail in pre-requesites check with loopback adapter error. The solution is to install a loopback adaptor before starting em dbconsole installation.

On Linux :
Edit file /etc/hosts and place
127.0.0.1 localhost.localdomain localhost

On Windows XP
=============
Navigation
Control panel -> add hardware -> select "Yes, I have already connected the hardware" -> select "Add a new hardware device" ->
select "Install the hardware that I manually select from a list"  -> select "Network adapters" -> select "Manufacturer: Select Microsoft" and "Network Adapter: Select Microsoft Loopback Adapter" -> Click on Finish

Go to Desktop and click on "My Network Places"
Choose properties -> select the newly created network connection -> Choose "Properties" -> Use the following IP address ->
IP address= 192.168.x.x (x is any value between 0 and 255)
Subnet mask=Enter 255.255.255.0
Click OK

Add a line to the SYSTEM_DRIVE:\WINDOWS\system32\drivers\etc\hosts
<IP address u assigned above>  mycomputer.mydomain.com   mycomputer

Confirmation:
============
Ping <ip address>
Ping <mycomputer>

Q) Solution for "ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes" ?

Sometimes you may get the ORA-20000 error while executing the script with pl/sql code.  Pls set the folloing sqlplus setting to avoid the error .....
SQL> set serveroutput on size 200000
SQL> <Execute the script >

Q) Following error in alert log file ?


ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/askm/redo02.log'
Wed Sep 30 05:04:49 2009
Errors in file /u01/app/oracle/diag/rdbms/askm/askm/trace/askm_ora_2291.trc:
ORA-16038: log 1 sequence# 91 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/askm/redo01.log'
USER (ospid: 2291): terminating the instance due to error 16038
Instance terminated by USER, pid = 2291


Solution :


To verify this run the following query. It will show the size of the recovery area and how full it is:
SQL> startup mount
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             536874576 bytes
Database Buffers          306184192 bytes
Redo Buffers                5132288 bytes
Database mounted.
SQL> set lines 100
col name format a60
select  name
,       floor(space_limit / 1024 / 1024) "Size MB"
,       ceil(space_used  / 1024 / 1024) "Used MB"
from    v$recovery_file_dest
order by nameSQL> SQL>   2    3    4    5  ;

NAME                                                            Size MB    Used MB
------------------------------------------------------------ ---------- ----------
/u01/app/oracle/flash_recovery_area                                3852       3826

SQL> alter system set db_recovery_file_dest_size=6g scope=both;

System altered.

SQL> alter database open;

Database altered.

SQL>

R12 - How to enable logging for Apache,Oc4j and Opmn

Written By askMLabs on Tuesday, September 29, 2009 | 4:51 AM

R12 - How to enable logging for Apache,Oc4j and Opmn:


Apache Logging ( Plain Text )
==============================

1) Edit the file $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/httpd.conf and set
LogLevel warn (s_apache_loglevel in context file)
2) Bounce the apache
3) Try to access the home URL or reproduce the issue.
4) Collect the following logfiles from $LOG_HOME/ora/10.1.3/Apache
access_log.<unique id>
error_log.<unique id>

Values that can be set to LogLevel variable in httpd.conf file
LogLevel = emerg,alert,crit,error,warn,notice,info,debug.

Apache Logging ( ODL Logging)
=============================

1) Set the following parameters in file $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/httpd.conf
OraLogMode [oracle|odl|apache]
OraLogSeverity <message type>:<message level>

Message type: INTERNAL_ERROR, ERROR, WARNING, NOTIFICATION & TRACE
Message level: 1-32 (1 most severe, 32 least)
2) Bounce the apache
3) Try to access the home URL or reproduce the issue.
4) Collect the logfiles from $LOG_HOME/ora/10.1.3/Apache/oracle

OC4J Logging
============

By default Oracle Applications R12 creates 3 OC4J instances:
*  OACore: runs OA Framework-based applications
* Forms: runs Forms-base applications
* OAFM (Oracle Apps Fusion Middleware): runs web services, mapviewer, ascontrol

Loglevel is set in the file
$ORA_CONFIG_HOME/10.1.3/j2ee/<oacore, forms, oafm>/config

Log file path is specified in the file
$ORA_CONFIG_HOME/10.1.3/j2ee/<oacore, forms, oafm>/application-deployments/<oacore, forms, oafm>/orion-application.xml

1) Open the file to set the following log level $ORA_CONFIG_HOME/10.1.3/j2ee/<oacore, forms, oafm>/config/j2ee-logging.xml
2) Come to the location located as
"<logger name='oracle' level='NOTIFICATION:1‘ .....
3) Set the desired logging using following values
<message type>:<message level>
Message type: INTERNAL_ERROR, ERROR, WARNING, NOTIFICATION & TRACE
Message level: 1-32 (1 most severe, 32 least)
4) Locate the log file path from the file
$ORA_CONFIG_HOME/10.1.3/j2ee/<oacore, forms, oafm>/application-deployments/<oacore,forms,oafm>/orion-application.xml
(Will be identified with tag : <log> <file path=...> </log>)
5) Bounce the OC4J instance and reproduce the issue
6) Collect the log files from the following locations .
Plain text -> $LOG_HOME/ora/10.1.3/j2ee/<oacore, forms, oafm>/<oacore,forms,oafm>_<default_group_1>/application.log
ODL Log -> $LOG_HOME/ora/10.1.3/j2ee/<oacore, forms, oafm>/<oacore,forms,oafm>_<default_group_1>/log.xml

OPMN Logging
============

1) Open the file $ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml to set the logging parameter
2) Logging is enabled per component (internal, ons or pm)
3) Levels that can be set are (component codes) as following:
none, fatal, error, warn, notify          (written to .log)
debug1, debug2, debug3, debug4    (written to .dbg)
Ex :
opmnctl set target=log comp=warn
opmnctl set target=debug comp=debug1
4) Bounce opmn services and reproduce the issue
5) Collect the opmn log files generated in $LOG_HOME/ora/10.1.3/opmn
opmn.log , opmn.dbg and opmn.out

NOTE : Logfiles can be enabled for rotation using parameter s_opmn_log_rotation_size, s_opmn_log_rotation_time in opmn.xml

Various Other Logfiles :
========================

$LOG_HOME/appl/admin/log/






































ServiceLogfile Name
TNS Listener Start/Stop logadalnctl.txt
Fulfillment Server Start/Stop logjtffmctl.txt
Oracle HTTP Server start/stop logadapcctl.txt
Concurrent Managers and ICM start/stop logadcmctl.txt
Forms OC4J start/stop logadformsctl.txt
OACore OC4J start/stop logadoacorectl.txtq
OAFM OC4J start/stop logadoafmctl.txt
OPMN start/stop logadopmnctl.txt

11g Partitioning Features

Written By askMLabs on Monday, September 28, 2009 | 10:59 PM

11g Partitioning Features :


What is Partitioning ?
Oracle Partitioning is the splitting of data sets usually into separate physical files using separate partition tablespaces.

Why is partitioning relevant to tuning ?
Partitioning can be used to break large tables into smaller subsets. Processing of smaller subsets of data separately and in parallel is petentially much faster than serial processing on very large data sets.

Different Partitioning methods :- (till 10g)
Partitions can be created on single or multiple columns of a table. A table can be devided into separate partitions based on three methods ( ranges of values , values in lists and hashing algorithms on columns).
1) Range Partition
2) List Partition
3) Hash Partition
4) Composite Partition (Range - Hash Partition and Range - List Partition)

The Optimizer can access individual partitions when processing SQL code. This process is termed pruning.

===  What is new with 11g ?  =====


Extended Composite Partitions
Range top level
--Range-Hash (available since Oracle 8i)
--Range-List (available since Oracle 9.2)
--Range-Range
List top level
--List-List
--List-Hash
--List-Range
Interval top level
--Interval-Range
--Interval-List
--Interval-Hash

Enhanced Partitioning
1)Virtual Column based partitioning
2)Reference Partitioning
3)Interval Partitioning
4)System Partitioning

Enhanced Manageability
1)Partition Advisor

Virtual Column Based Partitioning :
It is purely virtual , meta-data only. Virtual columns can have statistics and they are also eligible for partitioning key. This enhances the performance and manageability.

Reference Partitioning :
Oracle 11g introduced Reference Partitioning. Child table inherits the partitioning strategy of parent table through PK-FK relationship. This enhances the performance and manageability.

Interval Partitioning :
Extention to range partition and these are created as metadata information only.Partition created when new data is added.
Used Functions:
Numtodsinterval - Convert a number into an interval day to second literal.
Numtoyminterval - Convert a number into an interval year to month literal.
Interval partitioning does not support subpartitions. Thus, you can create an interval partition on the main partition of a composite partitioned table, but the subpartition cannot be interval-partitioned.

System Partitioning:
Application-controlled partitioning and No partitioning keys.You must define which partition the data goes in when doing an insert of data.System partitioning gives you all the advantages partitioning, but leaves the decision of how the data is partitioned to the application layer.

Partitioning Advisor:
Considers entire query workload to improve query performance.

=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================




Interval Partitioning:
======================

SQL> conn sh/sh
Connected.
SQL> create table daily_sales(
product_id number not null,
customer_id number not null,
sale_dt date not null ,
quantity_sold number(3) not null)
partition by range (sale_dt)interval (numtoyminterval(1,'MONTH'))
(
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')));


Table created.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name = 'DAILY_SALES'order by table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
DAILY_SALES          P1                   TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P2                   TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P3                   TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> INSERT INTO daily_sales VALUES (1, 3423,  TO_DATE('16-OCT-2002', 'DD-MON-YYYY'), 45);

1 row created.

SQL> INSERT INTO daily_sales VALUES (2, 3426,  TO_DATE('31-OCT-2002', 'DD-MON-YYYY'), 97);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats('SH', 'DAILY_SALES');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name = 'DAILY_SALES'order by table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
DAILY_SALES          P1                   TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P2                   TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P3                   TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> INSERT INTO daily_sales VALUES (3, 34863,  TO_DATE('16-NOV-2004', 'DD-MON-YYYY'), 29);

1 row created.

SQL> INSERT INTO daily_sales VALUES (4, 34586,  TO_DATE('30-NOV-2004', 'DD-MON-YYYY'), 94);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats('SH', 'DAILY_SALES');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name = 'DAILY_SALES'order by table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
DAILY_SALES          P1                   TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P2                   TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P3                   TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          SYS_P21              TO_DATE(' 2004-12-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> INSERT INTO daily_sales VALUES (5, 3403,  TO_DATE('16-JAN-2005', 'DD-MON-YYYY'), 29);

1 row created.

SQL> INSERT INTO daily_sales VALUES (6, 3486,  TO_DATE('31-JAN-2005', 'DD-MON-YYYY'), 94);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'DAILY_SALES');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.gather_table_stats('SH', 'DAILY_SALES');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name = 'DAILY_SALES'order by table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
DAILY_SALES          P1                   TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P2                   TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P3                   TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          SYS_P21              TO_DATE(' 2004-12-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          SYS_P22              TO_DATE(' 2005-02-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> alter table daily_sales rename partition SYS_P21 to P4;

Table altered.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name = 'DAILY_SALES'order by table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
DAILY_SALES          P1                   TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P2                   TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P3                   TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P4                   TO_DATE(' 2004-12-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          SYS_P22              TO_DATE(' 2005-02-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> alter table daily_sales merge partitions for(to_date('01-JAN-2002','dd-MON-yyyy')) , for(to_date('01-JAN-2003','dd-MON-yyyy')) into partition P5;

Table altered.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name = 'DAILY_SALES'order by table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
DAILY_SALES          P1                   TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P4                   TO_DATE(' 2004-12-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          P5                   TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

DAILY_SALES          SYS_P22              TO_DATE(' 2005-02-01 00:00:00', 'SYYYY-M          2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


Virtual Column Based Partitioning
=================================

SQL> sho user
USER is "SH"
SQL> CREATE TABLE employees
( employee_id number(6) not null,
emp_name varchar2(30),
first_letter VARCHAR2(1)
GENERATED ALWAYS AS
(
UPPER(SUBSTR(TRIM(emp_name), 1, 1))
) VIRTUAL

)
PARTITION BY LIST (first_letter)
(
PARTITION part_a_g VALUES ('A','B','C','D','E','F','G'),
PARTITION part_h_n VALUES ('H','I','J','K','L','M','N'),
PARTITION part_o_u VALUES ('O','P','Q','R','S','T','U'),
PARTITION part_v_z VALUES ('V','W','X','Y','Z')
);


Table created.

SQL> select column_name, data_default from   user_tab_columns where  table_name = 'EMPLOYEES';

COLUMN_NAME                    DATA_DEFAULT
------------------------------ --------------------------------------------------
EMPLOYEE_ID
EMP_NAME
FIRST_LETTER                   UPPER(SUBSTR(TRIM("EMP_NAME"),1,1))

SQL> INSERT INTO employees (employee_id, emp_name) VALUES (1, 'Andy Pandy');
INSERT INTO employees (employee_id, emp_name) VALUES (1, 'Burty Basset');
INSERT INTO employees (employee_id, emp_name) VALUES (1, 'Harry Hill');
INSERT INTO employees (employee_id, emp_name) VALUES (1, 'Iggy Pop');
INSERT INTO employees (employee_id, emp_name) VALUES (1, 'Oliver Hardy');
INSERT INTO employees (employee_id, emp_name) VALUES (1, 'Peter Pervis');
INSERT INTO employees (employee_id, emp_name) VALUES (1, 'Veruca Salt');
INSERT INTO employees (employee_id, emp_name) VALUES (1, 'Wiley Cyote');
COMMIT;
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats('SH', 'EMPLOYEES');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rows from user_tab_partitions where table_name = 'EMPLOYEES' order by table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
EMPLOYEES                 PART_A_G             'A', 'B', 'C', 'D', 'E', 'F', 'G'                 2
EMPLOYEES                 PART_H_N             'H', 'I', 'J', 'K', 'L', 'M', 'N'                 2
EMPLOYEES                 PART_O_U             'O', 'P', 'Q', 'R', 'S', 'T', 'U'                 2
EMPLOYEES                 PART_V_Z             'V', 'W', 'X', 'Y', 'Z'                           2

SQL>


Reference Partitioning
======================

SQL> sho user
USER is "SH"
SQL> create table parent_tab(
customer_id  number,
order_id number not null,
order_date date not null,
order_mode varchar2(8),
order_status varchar2(1))
partition by range (order_date)
(
partition p_before_jan_2008   values less than(to_date('01-JAN-2009','dd-MON-yyyy')),
partition p_before_jan_2009   values less than(to_date('01-DEC-2010','dd-MON-yyyy'))) parallel;


Table created.

SQL> alter table parent_tab add constraint parent_tab_pkprimary key (order_id);

Table altered.

SQL> INSERT INTO parent_tab(customer_id,order_id,order_date) VALUES (1, 100,  SYSDATE);

1 row created.

SQL> INSERT INTO parent_tab(customer_id,order_id,order_date) VALUES (2, 101,  SYSDATE);

1 row created.

SQL> INSERT INTO parent_tab(customer_id,order_id,order_date) VALUES (3, 102,  ADD_MONTHS(SYSDATE,12));

1 row created.

SQL> create table child_tab(
order_id number not null,
product_id number not null,
quantity number not null,
sales_amount number not null,
constraint child_tab_fk
foreign key (order_id) references parent_tab(order_id) )
partition by reference (child_tab_fk)parallel;


Table created.

SQL> INSERT INTO child_tab(order_id,product_id,quantity,sales_amount) VALUES (101, 1, 4, 500);

1 row created.

SQL> INSERT INTO child_tab(order_id,product_id,quantity,sales_amount) VALUES (101, 3, 97, 450);

1 row created.

SQL> INSERT INTO child_tab(order_id,product_id,quantity,sales_amount) VALUES (102, 3, 47, 350);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats('SH', 'PARENT_TAB');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.gather_table_stats('SH', 'CHILD_TAB');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rowsfrom user_tab_partitions where table_name in ('PARENT_TAB','CHILD_TAB')order by table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
CHILD_TAB                 P_BEFORE_JAN_2008                                                      0
CHILD_TAB                 P_BEFORE_JAN_2009                                                      3
PARENT_TAB                P_BEFORE_JAN_2008    TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-M          0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

PARENT_TAB                P_BEFORE_JAN_2009    TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-M          3
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> select table_name, partitioning_type, ref_ptn_constraint_name from user_part_tables where table_name in ('PARENT_TAB','CHILD_TAB');

TABLE_NAME                PARTITION REF_PTN_CONSTRAINT_NAME
------------------------- --------- ------------------------------
CHILD_TAB                 REFERENCE CHILD_TAB_FK
PARENT_TAB                RANGE

SQL> select table_name, partition_name, high_value from user_tab_partitions where table_name in ('PARENT_TAB','CHILD_TAB') order by partition_position, table_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE
------------------------- -------------------- ----------------------------------------
CHILD_TAB                 P_BEFORE_JAN_2008
PARENT_TAB                P_BEFORE_JAN_2008    TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

CHILD_TAB                 P_BEFORE_JAN_2009
PARENT_TAB                P_BEFORE_JAN_2009    TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> select up.table_name, up.partitioning_type, uc.table_name ref_table from user_part_tables up, (select r.table_name, r.constraint_name from user_constraints uc, user_constraints r where uc.constraint_name=r.constraint_name and uc.owner=r.owner) uc where up.ref_ptn_constraint_name = uc.constraint_name(+) and up.table_name in ('PARENT_TAB','CHILD_TAB');

TABLE_NAME                PARTITION REF_TABLE
------------------------- --------- ------------------------------
CHILD_TAB                 REFERENCE CHILD_TAB
PARENT_TAB                RANGE

SQL> alter table parent_tab add partition p_before_jan_20010 values less than (to_date('01-feb-2011','dd-mon-yyyy'))  ;

Table altered.

SQL> select table_name, partitioning_type, ref_ptn_constraint_name from user_part_tables where table_name in ('PARENT_TAB','CHILD_TAB');

TABLE_NAME                PARTITION REF_PTN_CONSTRAINT_NAME
------------------------- --------- ------------------------------
CHILD_TAB                 REFERENCE CHILD_TAB_FK
PARENT_TAB                RANGE

SQL> select table_name, partition_name, high_value from user_tab_partitions where table_name in ('PARENT_TAB','CHILD_TAB') order by partition_position, table_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE
------------------------- -------------------- ----------------------------------------
CHILD_TAB                 P_BEFORE_JAN_2008
PARENT_TAB                P_BEFORE_JAN_2008    TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

CHILD_TAB                 P_BEFORE_JAN_2009
PARENT_TAB                P_BEFORE_JAN_2009    TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

CHILD_TAB                 P_BEFORE_JAN_20010
PARENT_TAB                P_BEFORE_JAN_20010   TO_DATE(' 2011-02-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

6 rows selected.

SQL> select up.table_name, up.partitioning_type, uc.table_name ref_table from user_part_tables up, (select r.table_name, r.constraint_name from user_constraints uc, user_constraints r where uc.constraint_name=r.constraint_name and uc.owner=r.owner) uc where up.ref_ptn_constraint_name = uc.constraint_name(+) and up.table_name in ('PARENT_TAB','CHILD_TAB');

TABLE_NAME                PARTITION REF_TABLE
------------------------- --------- ------------------------------
CHILD_TAB                 REFERENCE CHILD_TAB
PARENT_TAB                RANGE

SQL>


System Partitioning :
======================

SQL> sho user
USER is "SH"
SQL> CREATE TABLE systab (c1 integer, c2 integer)
PARTITION BY SYSTEM
(
PARTITION p1 TABLESPACE test1,
PARTITION p2 TABLESPACE test2,
PARTITION p3 TABLESPACE test1,
PARTITION p4 TABLESPACE test2
);


Table created.

SQL> INSERT INTO systab VALUES (4,5);
INSERT INTO systab VALUES (4,5)
*
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by
the System method
-- Insert row with partition-extended syntax

SQL> INSERT INTO systab PARTITION (p1) VALUES (4,5);

1 row created.

SQL> INSERT INTO systab PARTITION (p4) VALUES (2,7);

1 row created.

SQL> INSERT INTO systab PARTITION (p2) VALUES (3,5);

1 row created.

SQL> INSERT INTO systab PARTITION (p2) VALUES (1,9);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'SYSTAB');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rows from user_tab_partitions where table_name = 'SYSTAB' order by table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
SYSTAB                    P1                                                                     1
SYSTAB                    P2                                                                     2
SYSTAB                    P3                                                                     0
SYSTAB                    P4                                                                     1

SQL> UPDATE SYSTAB PARTITION (p2) SET c2 = 3 WHERE c1 = 2;

0 rows updated.

SQL> DELETE FROM SYSTAB PARTITION (p1) WHERE c1 = 2;

0 rows deleted.

SQL> select count(1) from systab where c1=2;

COUNT(1)
----------
1

SQL> DELETE FROM SYSTAB PARTITION (p4) WHERE c1 = 2;

1 row deleted.

SQL> select count(1) from systab where c1=2;

COUNT(1)
----------
0

SQL>
-- The PARTITION clause is optional for update and delete statements, but omitting this clause will force
all partitions to be scanned, since there is no way perform automatic partition pruning when the database
has no control over row placement. When the PARTITION clause is used, you must be sure to perform the
operation against the correct partition.


List-Hash
==========

SQL> sho user
USER is "SH"
SQL> CREATE TABLE list_hash_tab (
id           NUMBER,
code         VARCHAR2(10),
description  VARCHAR2(50),
created_date DATE
)
PARTITION BY LIST (code)
SUBPARTITION BY HASH (id)
(
PARTITION part_aa values ('AA')
(
SUBPARTITION part_aa_01,
SUBPARTITION part_aa_02
),
partition part_bb values ('BB')
(
SUBPARTITION part_bb_01,
SUBPARTITION part_bb_02
)
);


Table created.

SQL> DECLARE
l_code  VARCHAR2(10);
BEGIN
FOR i IN 1 .. 40 LOOP
IF MOD(i, 2) = 0 THEN
l_code := 'BB';
ELSE
l_code := 'AA';
END IF;


INSERT INTO list_hash_tab (id, code, description, created_date)
VALUES (i, l_code, 'Description for ' || i || ' ' || l_code, SYSDATE);
END LOOP;
COMMIT;
END;
/


PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'LIST_HASH_TAB', granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rows from user_tab_partitions  where table_name = 'LIST_HASH_TAB' order by table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
LIST_HASH_TAB             PART_AA              'AA'                                             20
LIST_HASH_TAB             PART_BB              'BB'                                             20

SQL>


Single Partition Transportable for Oracle Data Pump :
=====================================================

1) create two tablespaces and assign quota to users
2) Create a partitioned table with some data and gather stats
3) Query the user_tab_partitions to see data
4) Make one tablespace as read only
5) Use expdp to export the partion
6) Drop the table and tablespace ( dont drop the datafiles of tablespace which is transported)
7) use impdp to import
8 ) Check the table data in user_tables

11g table Partitions Features



SQL Plan Management(SPM) - Theory

Written By askMLabs on Saturday, September 26, 2009 | 9:40 AM

SPM - SQL Plan Management


How are we evolved to 11g SQL plan management (SPM) ?

Outlines -> Stored Outlines are very difficult to manage and also very difficult to swap the execution plans with plan stability.
10g SQL Profiles -> Starting from 10g we used the SQL profile concept ie SQL tuning sets and also we can change the execution plans.
11g SQL Plan management (SPM) -> It is an easy to use tool to lock the best execution plans.

The major difference between Outlines and SPM is that Outlines are fixed and we cant override execution plan, But SPM uses baselines that can be evaluated for better plans and activated in place of original plans.

The difference between SQL Profiles and SPM is that the query plan changes based on the predicate used, But with SQL Plan Baselines,  the plan would be the same regardless of the value in the predicate.
For example queries with "Where cust_id= ", "where book_id= " will have different execution plans in SQL profiles.

What is SQL Baseline ?
Baselines are the latest evolution in Oracle’s efforts to allow a plan to be locked. Baselines stores the plan_hash_value, so they know if they are reproducing the correct plan or not.
-- Baselines will be used by default in 11gR1, if they exist. There is a parameter to control whether they are used or not (OPTIMIZER_USE_SQL_PLAN_BASELINE). It is set to TRUE by default.
-- Baselines will not be created by default in 11gR1. So, much like with the older Outlines or SQL Profiles, you must do something to create them.
-- There is a view called DBA_SQL_PLAN_BASELINES that exposes the Baselines that have been created.
-- Just like Outlines and SQL Profiles, Baselines apply to all instances in a RAC environment (they are not localized to a specific instance).

How to create Base lines ?
-- Baselines are created automatically by setting the parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
-- From the cursor cache using dbms_spm.load_plans_from_cursor_cache
-- From the tuning sets using dbms_spm.load_plans_from_sqlset

These created baselines are applied to any SQL statement where the normalized text matches ( means same execution plan ) ie the baseline created for one statement can be attached to a different statement. You dont need to do extra , but call the procedure directly and it’s done. Of course the optimizer will have to verify that the plan will work for the statement you attach it to. If it fails this validation step, then the optimizer will ignore it and go on about it’s normal business.

Well ... we discussed about baselines and how they can be created  .....But how are these baselines related to SQL Plan Management (SPM). What is the relationship between them ? how can they choose the best execution plan ?
When a SQL statement is hard parsed, the cost based optimizer produces several execution plans and selects the one with the lowest cost. If a SQL plan baseline is present, the optimizer tries to find a matching plan in the SQL plan baseline that is flagged as accepted. If a matching plan is found, the optimizer will uses the plan. If the SQL plan baseline doesn't contain a matching plan, the optimizer evaluates the accepted plans in the SQL plan baseline and uses the one with the lowest cost. If the execution plan originally produced by the optimizer has a lower cost than those present in the SQL plan baseline, it is added to the baseline as a not-accepted plan, so it is not used until it is verified not to cause a reduction in performance. If a system change affects all existing accepted plans, the optimizer will use the original plan with the lowest cost and mark this as accepted.
The final conclusion steps are  load , evolve , test and accept.

Are the baselines fully controlled by oracle internally or can we do some maintenance tasks with SQL Plan Baselines ?
1) We can alter the attributes of SQL Plan Baselines with ALTER_SQL_PLAN_BASELINE function
--enabled (YES/NO) : If YES, the plan is available for the optimizer if it is also marked as accepted.
--fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans.
--autopurge (YES/NO) : If YES, the SQL plan baseline is purged automatically if it is not used for a period of time.
--plan_name : Used to amend the SQL plan name, up to a maximum of 30 character.
--description : Used to amend the SQL plan description, up to a maximum of 30 character.
2) Diplay the baseline information using
DBA_SQL_PLAN_BASELINES
DBMS_XPLAN.display_sql_plan_baseline
3) Transferring SQL Plan Baselines - DBMS_SPM.pack_stgtab_baseline
4) Dropping SQL Plan Baselines

References :
SQL Plan Management(SPM) - Theory



R12 - Concurrent Program Tracing

Written By askMLabs on Friday, September 25, 2009 | 4:46 AM

Concurrent Program Tracing :

Reference Note : 296559.1

CASE 1 : Concurrent Program Tracing without bind variables
1) Follow the following navigation to enable logging for conc prog
Goto Sysadmin > Concurrent > Program > Define
Query the concurrent program
Check the trace box to enable trace
2) Execute the concurrent program using the following navigation and note down the request id
3) Collect the trace file using the script provided here

CASE 2: Concurrent Program Tracing with bind variables and waits
1) Note down the following values
SELECT value FROM v$parameter WHERE name = 'max_dump_file_size';
SELECT value FROM v$parameter WHERE name = 'timed_statistics';

2) Execute the following commands as sysdba
ALTER SYSTEM SET max_dump_file_size = unlimited;
ALTER SYSTEM SET timed_statistics = true;
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 12';

3) Execute the concurrent program using the following navigation and note down the request id
4) Collect the trace file using the script provided here
5) Turn off tracing the reset the values
ALTER SYSTEM SET EVENTS '10046 trace name context off';
ALTER SYSTEM SET max_dump_file_size = <value from step 1>;
ALTER SYSTEM SET timed_statistics = <value from step 1>;

CASE 3:
Enabling the trace for a concurrent request for which you donot have privileges to run the concurrent Request.

1) Ask the person who is privileged to run the concurrent program and get the request id 'xxxxx'
2) Get the oracle_process_id for that concurrent request.
SQL>select request_id,oracle_process_id from fnd_concurrent_requests where request_id in ('xxxxxxx');

3) Now get the session details ( SID and Serial ) using value obtained from step 2
col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, s.status
, p.spid "OS PID"
, s.inst_id
, s.module
from sys.gv_$session s
, sys.gv_$process p
Where s.paddr = p.addr
and s.inst_id = p.inst_id
and p.spid=&value_from_step2
order by to_number(p.spid);

4) Execute the following command to enable the trace :
SQL> EXECUTE dbms_support.start_trace_in_session (&SID,&SERIAL,binds=>true,waits=>true);

NOTE : You need to run this command on the corresponding rac node, inst_id from step3)
5) Collect the trace from udump location and investigate the issue.

R12 - Changing APPS password

Written By askMLabs on Wednesday, September 23, 2009 | 9:36 PM

Changing APPS password


There are some situations where you may need to change the apps password. Some times you may or may not know the apps password. And some times the password may be reset if it is corrupted.

Action Steps ( when you know the old apps password )
1) Shutdown all the MT services
2) Change apps password using FNDCPASS
FNDCPASS apps/[oldpasswd] 0 Y system/pwd SYSTEM APPLSYS <new_pwd>
3) Run autoconfig
4) Startup all the MT services

Action Steps ( when you forgot/dont know the apps password)
Follow metalink Note 160337.1 to change the apps password and then run autoconfig.

Encrypted Passwords details :
SQL> select oracle_username,encrypted_oracle_password from fnd_oracle_userid where oracle_username IN ('APPS', 'APPLSYS','APPLSYSPUB');
SQL> select encrypted_foundation_password, encrypted_user_password from fnd_user where user_name = 'SYSADMIN';

Scripts

Written By askMLabs on Monday, September 21, 2009 | 5:34 PM

Find trace file for a Concurrent Request :
SQL>

prompt
accept request prompt 'Please enter the concurrent request id for the appropriate concurrent program:'
prompt

column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off

SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;

Concurrent Request Status Query :
SQL>
set linesize 300;
set head on;
col sid_serial for a13;
col db_pid for a6;
col CMGR_Program for a65;
col user_name for a13;
col phase for a10;
col status for a10;
col start_time for a11;

break on USER_NAME

SELECT fu.user_name user_name,
TO_CHAR(NVL(cr.actual_start_date, cr.requested_start_date), 'DD.MM HH24:MI') start_time,
cr.request_id request_id,
decode(cr.parent_request_id, -1, 0, cr.parent_request_id) par_req_id,
DECODE(cr.phase_code, 'C', 'Completed', 'I', 'Inactive', 'P', 'Pending', 'R', 'Running', cr.phase_code) phase,
DECODE(cr.status_code, 'A', 'Waiting', 'B', 'Resuming', 'C', 'Normal', 'D', 'Cancelled', 'E', 'Errored', 'F', 'Scheduled',
'G', 'Warning', 'H', 'On Hold', 'I', 'Normal', 'M', 'No Manager', 'Q', 'Standby', 'R', 'Normal', 'S', 'Suspended', 'T',
'Terminating', 'U', 'Disabled', 'W', 'Paused', 'X', 'Terminated', 'Z', 'Waiting', cr.status_code) status,
cr.oracle_process_id db_pid,
vs.SID || ',' || vs.serial# sid_serial,
(SELECT SUBSTR(cp.concurrent_program_name || '-' || cpl.user_concurrent_program_name, 1, 65)
FROM APPS.fnd_concurrent_programs cp,
APPS.fnd_concurrent_programs_tl cpl
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cpl.application_id = cr.program_application_id
AND cpl.concurrent_program_id = cr.concurrent_program_id
AND cpl.LANGUAGE = USERENV('LANG')
) CMGR_Program
FROM APPS.fnd_concurrent_requests cr,
v$process vp,
v$session vs,
APPS.fnd_user fu
WHERE
cr.phase_code <> 'I'
AND (cr.phase_code < 'C' OR cr.phase_code > 'C')
AND cr.status_code NOT IN ('U', 'X', 'D', 'E', 'C', 'I')
AND cr.oracle_process_id = vp.spid (+)
AND cr.oracle_session_id = vs.audsid (+)
AND fu.user_id = cr.requested_by
ORDER BY sid_serial,
phase_code desc,
STATUS_CODE,
2 DESC,
1
;

Sample output

USER_NAME     START_TIME  REQUEST_ID PAR_REQ_ID PHASE      STATUS     DB_PID SID_SERIAL    CMGR_PROGRAM
------------- ----------- ---------- ---------- ---------- ---------- ------ ------------- -----------------------------------------------------------------
501282944     26.09 21:00   13737882   12462882 Pending    Standby           ,             FNDGSCST-Gather Schema Statistics
SYSADMIN      22.09 01:30   13892885   13694882 Pending    Standby           ,             FNDCPPUR-Purge Concurrent Request and/or Manager Data
21.09 18:39   13921062   13921061 Pending    Standby           ,             FNDOAMCOL-OAM Applications Dashboard Collection

How to Check the Product Installation Status (Installed Modules)


  1. Run the script $AD_TOP/adutconf.sql.

  2. This script will generate a file called adutconf.lst. Find this section in the file "Product Installation Status and other product information."



Script to extract the information about the nodes
set serveroutput on
set echo on
set timing on
set feedback on
set long 10000
set pagesize 132
set linesize 80
col PLATFORM_CODE form a5
col HOST form a20
col DOMAIN form a30
col WEBHOST form a30
col VIRTUAL_IP form a20
col status form a20
col ConcMgr form a8
col Forms form a8
col WebServer form a8
col Admin form a8
col Database form a8
col last_monitored form a40
--
select
NODE_NAME,
to_char(CREATION_DATE, 'DD-MON-RR HH24:MI') creation_date,
PLATFORM_CODE,
decode(STATUS,'Y','ACTIVE','INACTIVE') Status,
decode(SUPPORT_CP,'Y', 'ConcMgr','No') ConcMgr,
decode(SUPPORT_FORMS,'Y','Forms', 'No') Forms,
decode(SUPPORT_WEB,'Y','Web', 'No') WebServer,
decode(SUPPORT_ADMIN, 'Y','Admin', 'No') Admin,
decode(SUPPORT_DB, 'Y','Rdbms', 'No') Database,
to_char(LAST_MONITORED_TIME, 'DD-MON-RR HH24:MI:SS') last_monitored,
NODE_MODE,
SERVER_ADDRESS,
HOST,
DOMAIN,
WEBHOST,
VIRTUAL_IP,
SERVER_ID
from fnd_nodes
where node_name != 'AUTHENTICATION;

Which FND_USER is locking that table
SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins      fl
,fnd_user        fu
,v$locked_object vlocked
,v$process       vp
,v$session       vs
,dba_objects     c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status
,'XX') != 'KILLED';

To determine what request groups and concurrent program is assigned to
select request_group_name from FND_REQUEST_GROUPS where REQUEST_GROUP_ID =(SELECT request_group_id FROM FND_REQUEST_GROUP_UNITS WHERE REQUEST_UNIT_ID=(select CONCURRENT_PROGRAM_ID from
FND_CONCURRENT_PROGRAMS where CONCURRENT_PROGRAM_NAME ='<report short name>'));

To Check The Profile Value At All Levels
clear col
clear breaks

set pages 9000
set lines 132
set verify off

col pov    format a45 word_wrapped  heading "Profile Value"
col lo     format a5                heading "Level"
col lov    format a40               heading "Value"
col pon    noprint    new_value n_pon
col upon   noprint    new_value n_upon
col sda    noprint    new_value n_sda
col eda    noprint    new_value n_eda
col cd     noprint    new_value n_cd
col cb     noprint    new_value n_cb
col d      format a78 word_wrapped noprint    new_value n_d

break on pon skip page

ttitle -
"Creation Date:   "      n_cd    "    Created By: "          n_cb   -
skip 1 -
"Date Active From:"      n_sda   "    To:"     n_eda  -
skip 1 -
"Profile Option Name: "  n_pon   -
skip 1 -
"User Profile Name:   "  n_upon  -
skip 1 -
"Profile Description: "          -
skip 1 -
n_d                              -

select        lpad(fpo.profile_option_name,55)         pon
,             lpad(fpot.user_profile_option_name,55)   upon
,             fpot.description                          d
,             lpad(fpo.start_date_active,15)           sda
,             lpad(fpo.end_date_active,15)             eda
,             lpad(fpo.creation_date,15)               cd
,             lpad(fu.user_name,20)                    cb
,             'Site'                                   lo
,             'SITE'                                   lov
,             fpov.profile_option_value                pov
from       FND_PROFILE_OPTIONS_TL      fpot
,          FND_PROFILE_OPTIONS         fpo
,          FND_PROFILE_OPTION_VALUES   fpov
,          FND_USER                    fu
where    fpot.user_profile_option_name like '&&profile_like'
and      fpot.profile_option_name = fpo.profile_option_name
and      fpo.application_id       = fpov.application_id
and      fpo.profile_option_id    = fpov.profile_option_id
and      fpo.created_by           = fu.user_id
and      fpot.language            = Userenv('Lang')
and      fpov.level_id            = 10001                  /* Site Level */
union all
select        lpad(fpo.profile_option_name,55)         pon
,             lpad(fpot.user_profile_option_name,55)   upon
,             fpot.description                          d
,             lpad(fpo.start_date_active,15)           sda
,             lpad(fpo.end_date_active,15)             eda
,             lpad(fpo.creation_date,15)               cd
,             lpad(fu.user_name,20)                    cb
,             'Apps'                                   lo
,             fa.application_name                      lov
,             fpov.profile_option_value                pov
from      FND_PROFILE_OPTIONS_TL      fpot
,         FND_PROFILE_OPTIONS         fpo
,         FND_PROFILE_OPTION_VALUES   fpov
,         FND_USER                    fu
,         FND_APPLICATION_TL          fa
where    fpot.user_profile_option_name like '&&profile_like'
and      fpot.profile_option_name = fpo.profile_option_name
and      fpo.profile_option_id    = fpov.profile_option_id
and      fpo.created_by           = fu.user_id
and      fpot.language            = Userenv('Lang')
and      fpov.level_id            = 10002                  /* Application Level */
and      fpov.level_value         = fa.application_id
union all
select        lpad(fpo.profile_option_name,55)         pon
,             lpad(fpot.user_profile_option_name,55)   upon
,             fpot.description                          d
,             lpad(fpo.start_date_active,15)           sda
,             lpad(fpo.end_date_active,15)             eda
,             lpad(fpo.creation_date,15)               cd
,             lpad(fu.user_name,20)                    cb
,             'Resp'                                   lo
,             frt.responsibility_name                   lov
,             fpov.profile_option_value                pov
from     FND_PROFILE_OPTIONS_TL      fpot
,        FND_PROFILE_OPTIONS         fpo
,        FND_PROFILE_OPTION_VALUES   fpov
,        FND_USER                    fu
,        FND_RESPONSIBILITY_TL          frt
where    fpot.user_profile_option_name like '&&profile_like'
and      fpot.profile_option_name        = fpo.profile_option_name
and      fpo.profile_option_id           = fpov.profile_option_id
and      fpo.created_by                  = fu.user_id
and      frt.language                    = Userenv('Lang')
and      fpot.language                   = Userenv('Lang')
and      fpov.level_id                   = 10003                  /* Responsibility Level */
and      fpov.level_value                = frt.responsibility_id
and      fpov.level_value_application_id = frt.application_id
union all
select        lpad(fpo.profile_option_name,55)         pon
,             lpad(fpot.user_profile_option_name,55)   upon
,             fpot.description                          d
,             lpad(fpo.start_date_active,15)           sda
,             lpad(fpo.end_date_active,15)             eda
,             lpad(fpo.creation_date,15)               cd
,             lpad(fu.user_name,20)                    cb
,             'User'                                   lo
,             fu2.user_name                            lov
,             fpov.profile_option_value                pov
from     FND_PROFILE_OPTIONS_TL      fpot
,        FND_PROFILE_OPTIONS         fpo
,        FND_PROFILE_OPTION_VALUES   fpov
,        FND_USER                    fu
,        FND_USER                    fu2
where    fpot.user_profile_option_name like '&&profile_like'
and      fpot.profile_option_name = fpo.profile_option_name
and      fpo.profile_option_id    = fpov.profile_option_id
and      fpo.created_by           = fu.user_id
and      fpov.level_id            = 10004                  /* User Level */
and      fpov.level_value         = fu2.user_id
and      fpot.language            = Userenv('Lang')
order by upon, lo, lov;

FRD - Forms Runtime Diagnostics

Written By askMLabs on Sunday, September 20, 2009 | 1:19 PM

How To Collect And Use Forms Trace (FRD) in Oracle Applications Release 12:

Forms Runtime Diagnostics (FRD) :


It is a method used to capture all the events that occur in a form session. It is a combination of external user-application interactions and internal Forms processing events.

FRD can be activated in 3 ways ......

  1. Using profile option

  2. Using appsweb.cfg

  3. In an ADHOC way

Action steps for "Using Profile Option" :
1) Login to application as sysadmin user
2) Note the profile value for "ICX: Forms Launcher" at site level
3) Change the profile value for "ICX: Forms Launcher" at user level
 http://hostname.domain:port/forms/frmservlet?record=collect+log=frdtrace.log  --> In servlet mode

 http://hostname.domain:port/OA_HTML/frmservlet?record=collect+log=frdtrace.log --> In socket mode

4) Bounce the apache services
5) Login into Oracle Applications and launch forms via self-service
6) Collect the log file from location $FORMS_TRACE_DIR
NOTE : You can also use the profile "Forms Runtime parameters" to pass the run time parameters.

Action steps for "Using appsweb.cfg" :

1) Edit the appsweb.cfg file pointed by environment variable "FORMS_WEB_CONFIG_FILE"
 record=collect

 log=formstrace.log

Edit in  section with heading "ENVIRONMENT SPECIFIC PARAMETERS "
2) Bounce the apache services
3) Login into Oracle Applications and launch forms via self-service
4) Collect the log file from location $FORMS_TRACE_DIR

Action steps for "In an ADHOC way":

1) Access forms using following urls directly and collect the log files in $FORMS_TRACE_DIR

http://hostname.domain:port/forms/frmservlet?record=collect+log=frdtrace.log  --> In servlet mode
http://hostname.domain:port/OA_HTML/frmservlet?record=collect+log=frdtrace.log --> In socket mode

2) Collect the log files from $FORMS_TRACE_DIR

Forms Trace Diagnostics Utility :


Forms Trace allows you to record information about a precisely defined part of forms functionality or a class of user actions. It provides detailed data collection and other features to assist the user in diagnosing and investigating forms runtime problems. Additional to old FRD logging, forms trace promises to have more structured logging with additional event which can be traced.

Action Steps
============

1) Edit the appsweb.cfg file pointed by environment variable "FORMS_WEB_CONFIG_FILE"
 record=collect

 tracegroup=medium

Edit in  section with heading "ENVIRONMENT SPECIFIC PARAMETERS "

2) Bounce the apache services

3) Login into Oracle Applications and launch forms via self-service

4) Collect the trace file from location $FORMS_TRACE_DIR and convert it either to xml or html file

With ADHOC
==========


1) Access forms using following urls directly and collect the log files in $FORMS_TRACE_DIR
 http://hostname.domain:port/forms/frmservlet?record=forms tracegroup=full  --> In servlet mode

 http://hostname.domain:port/OA_HTML/frmservlet?record=forms tracegroup=full --> In socket mode

2) Collect the trace file from location $FORMS_TRACE_DIR and convert it either to xml or html file

Converting the forms trace file to xml or html format  .....

To convert the binary file into XML format:

<JDK_HOME>/bin/java -cp <10.1.2 ORACLE_HOME>/forms/java/frmxlate.jar
oracle.forms.diagnostics.Xlate datafile=$1.trc outputfile=$1.xml outputclass=WriteOut

To convert the binary file into HTML format:

<JDK_HOME>/bin/java -cp <10.1.2 ORACLE_HOME>/forms/java/frmxlate.jar
oracle.forms.diagnostics.Xlate datafile=$1.trc outputfile=$1.html outputclass=WriteOutHTML

Ref : Note 373548.1

Servlet Logging
===============

1) Edit the appsweb.cfg file pointed by environment variable "FORMS_WEB_CONFIG_FILE"
 serverURL=/forms/lservlet/<parameter>

  Where parameter = /session or /sessionperf or /perf or /debug

2) Collect the log file :
$LOG_HOME/ora/10.1.3/j2ee/forms/forms_default_group_1/application.log
 
Support :
Copyright © 2013. askMLabs - All Rights Reserved
Proudly powered by Blogger