Written By askMLabs on Wednesday, September 30, 2009 | 3: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
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 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
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);
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
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
Written By askMLabs on Tuesday, September 29, 2009 | 7: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
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/
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
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.
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;
SQL> SELECT table_name, partition_name, high_value, num_rows from user_tab_partitions where table_name = 'EMPLOYEES' order by table_name, partition_name;
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);
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;
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;
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');
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;
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');
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);
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; /
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;
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
Written By askMLabs on Saturday, September 26, 2009 | 12:40 PM
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
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 :
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.
Written By askMLabs on Thursday, September 24, 2009 | 12:36 AM
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.1to 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';
Written By askMLabs on Monday, September 21, 2009 | 8: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 ;
How to Check the Product Installation Status (Installed Modules)
Run the script $AD_TOP/adutconf.sql.
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
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;
Written By askMLabs on Sunday, September 20, 2009 | 4: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 ......
Using profile option
Using appsweb.cfg
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 .....
Posts
