Due to some restricitons in uploading the Word document here, i have uploaded the document in Google Drive.
Please refer to my link.
EBS_DB_Upgrade
Please refer to my link.
EBS_DB_Upgrade
Monday, January 9, 2017
Friday, January 6, 2017
How To Create, export, import and Evolve a SQL Plan Baseline
Introduction: The query optimizer normally uses information like object and system statistics, bind values and so on to determine the best plan for a SQL statement. In some cases, defects in either these inputs or the optimizer can lead to a sub-optimal plan. A SQL plan baseline for a SQL statement consists of a set of accepted plans. When the statement is parsed, the optimizer will only select the best plan from among this set. If a different plan is found using the normal cost-based selection process, the optimizer will add it to the plan history but this plan will not be used until it is verified to perform better than the existing accepted plan and is evolved
Parameters
Two parameters allow you to control SPM.The first, optimizer_capture_sql_plan_baselines, which is FALSE by default, allows you to automatically capture plans. SPM will start managing every repeatable SQL statement that is executed and will create a plan history for it. The first plan that is captured will be automatically accepted. Subsequent plans for these statements will not be accepted until they are evolved.
The second parameter, optimizer_use_sql_plan_baselines, is TRUE by default. It allows the SPM aware optimizer to use the SQL plan baseline if available when compiling a SQL statement. If you set this parameter to FALSE, the SPM aware optimizer will be disabled and you will get the regular cost-based optimizer which will select the best plan based on estimated cost.
Note:Setting optimizer_capture_sql_plan_baselines = true permanently will result in a SQL plan baseline being created for every repeatable SQL statement on the system. That includes all of the recursive SQL Oracle executes on your behalf as well as every 'select sysdate from dual;' or 'select * from v$sql;' the DBA may do on the system. This could lead to a very large number of SQL plan baselines being captured and an increased foot print in the SYSAUX tablespace. There are some customers who do have optimizer_capture_sql_plan_baselines = true and they have not encountered any problem other than a large SYSAUX tablespace!
Scenario where a query is working fine in one instance and working bad in another instance. Thus i will exporting/importing the baseline from one instance to another.
SYS@SAM AS SYSDBA> show parameter baseline;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
Identify the problematic sql text using awr, statspack etc.
Get the sql_id for the sql_text- "Select R.Rowid From Fnd_Concur%"
SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text FROM v$sql WHERE sql_text LIKE '%Select R.Rowid From Fnd_Concur%';
5ugwm6cpfpr7b
create a tuning set
SYS@SAM AS SYSDBA> begin
DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME => '5ugwm6cpfpr7b_STS01', DESCRIPTION => 'TEST SQL TUNE SET');
END;
/ 2 3 4
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA>
Populate sql tuning set
SYS@SAM AS SYSDBA> DECLARE
CUR SYS_REFCURSOR;
BEGIN
OPEN CUR FOR
SELECT VALUE(P) FROM TABLE (DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''5ugwm6cpfpr7b''')) p;
DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME=> '5ugwm6cpfpr7b_STS01', POPULATE_CURSOR=>CUR);
CLOSE CUR;
END;
/
2 3 4 5 6 7 8 9
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA> SYS@SAM AS SYSDBA>
List out sql tuning set
SYS@SAM AS SYSDBA>
SELECT plan_hash_value FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(SQLSET_NAME => '5ugwm6cpfpr7b_STS01'));
PLAN_HASH_VALUE
---------------
2836784050
LOAD DESIRED PLAN FROM ‘SQL TUNING SET’ AS SQL PLAN BASELINE
SYS@SAM AS SYSDBA> DECLARE
MY_PLANS PLS_INTEGER;
BEGIN
MY_PLANS := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(SQLSET_NAME=>'5ugwm6cpfpr7b_STS01', BASIC_FILTER=> 'PLAN_HASH_VALUE=
''2836784050''' );
END;
/ 2 3 4 5 6
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA>
VERIFY IF SQL PLAN BASELINE GOT CREATED SUCCESSFULLY
select * from dba_sql_plan_baselines;
select sql_handle from dba_sql_plan_baselines;
SQL_HANDLE
------------------------------
SQL_a00583ac0188a3d6
========================================================================
Now we have to pack this sql baseline.
Create a staging table in system schema. It cannot be under sys schema
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'stage1');
END;
/
pack the sql baselines
DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => 'stage1',
enabled => 'yes',
SQL_HANDLE => 'SQL_a00583ac0188a3d6');
END;
/
Now export the sql baselines
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@SAM AS SYSDBA> CREATE OR REPLACE DIRECTORY test_dir AS '/xxx_out/dump';
Directory created.
SYS@SAM AS SYSDBA> GRANT READ, WRITE ON DIRECTORY test_dir TO system;
Grant succeeded.
SYS@SAM AS SYSDBA> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@SAM2 /xxx_out> expdp system/*** tables=stage1 directory=TEST_DIR dumpfile=stage.dmp logfile=stage.log
compression=all
Export: Release 11.2.0.3.0 - Production on Thu Jan 5 06:00:15 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** tables=stage1 directory=TEST_DIR dumpfile=stage.dmp
logfile=stage.log compression=all
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/PRE_TABLE_ACTION
Processing object type TABLE_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
. . exported "SYSTEM"."STAGE1" 8.164 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/xxx_out/dump/stage.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 06:09:
--------------------------------------------------------------------------------------------
Import the dump to the target database.
check first if there any baselines present or not.
select * from dba_sql_plan_baselines;
Import the sql baseline
oracle@SAM1 /oracle/stage> impdp system/***** tables=stage1 dumpfile=stage.dmp
Import: Release 11.2.0.3.0 - Production on Thu Jan 5 06:17:23 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** tables=stage1 dumpfile=stage.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."STAGE1" 8.164 KB 1 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 06:17:33
oracle@ /oracle/stage>
unpack the sql baseline
sqlplus system/<password>
SET SERVEROUTPUT ON
SYSTEM> DECLARE
l_plans_unpacked PLS_INTEGER;
BEGIN
l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
table_name => 'STAGE1',
table_owner => 'SYSTEM');
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END; 2 3 4 5 6 7 8
9 /
Plans Unpacked: 1
PL/SQL procedure successfully completed.
SYSTEM@SAM1>
check if the baselines are there or not
SYSTEM@SAM1 > select sql_handle from dba_sql_plan_baselines;
SQL_HANDLE
------------------------------
SQL_a00583ac0188a3d6
check if the plan is accepted or not.
SYS@SAM1 AS SYSDBA> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
If the plan is not accepted, then we have to eveolve it, which is discussed later in the post.
========================================================================
Scenario-2 -Force the optimizer to choose a plan. The first query runs against an unindexed column, does a full scan. And then an index have been added but the optimizer still chooses to do a full table scan. By Evolving the baseline, we forced the optimizer to choose the index and at last we removed the original plan which was doing a full table scan.
oracle@SAM2 ~> sqlplus test/welcome123
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 6 02:57:07 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
TEST@SAM >
TEST@SAM > create table t as select * from applsys.FND_OBJECTS;
Table created.
TEST@SAM >
TEST@SAM > exec DBMS_STATS.GATHER_SCHEMA_STATS ('test');
PL/SQL procedure successfully completed.
TEST@SAM > show parameter baselines
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
TEST@SAM > ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
Session altered.
TEST@SAM > select count(*) from t;
COUNT(*)
----------
1307
TEST@SAM > /
COUNT(*)
----------
1307
TEST@SAM >
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;
SYS@SAM AS SYSDBA> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC FIX
-------------------- ------------------------------------------ ------------------------------------------ --- --- ---
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy3fdbb376 select count(*) from t YES YES NO
Lets create an index and create a new baseline
TEST@SAM > create index t_idx on t (OBJECT_ID);
Index created.
TEST@SAM >TEST@SAM > exec dbms_stats.gather_schema_stats ('test');
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA> alter system flush shared_pool;
System altered.
SYS@SAM AS SYSDBA>
TEST@SAM > ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
Session altered.
TEST@SAM >
TEST@SAM > select count(*) from t;
COUNT(*)
----------
1307
TEST@SAM > /
COUNT(*)
----------
1307
TEST@SAM >
TEST@SAM > ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;
Session altered.
TEST@SAM >
TEST@SAM > set autotrace on;
TEST@SAM > select count(*) from t;
COUNT(*)
----------
1307
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1307 | 30 (4)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- SQL plan baseline "SQL_PLAN_7kchmhua5dydy3fdbb376" used for this statement
SYS@SAM AS SYSDBA> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT ENA ACC FIX
-------------------------------------------------------------------------------- --- --- ---
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy3fdbb376
select count(*) from t YES YES NO
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy89596ecb
select count(*) from t YES NO NO
check the execution plan for this baseline.
SYS@SAM AS SYSDBA> select * from table(dbms_xplan.display_sql_plan_baseline('SQL_793213869456f9be'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_793213869456f9be
SQL text: select count(*) from t
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_7kchmhua5dydy3fdbb376 Plan id: 1071362934
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1307 | 30 (4)| 00:00:01 |
-------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_7kchmhua5dydy89596ecb Plan id: 2304339659
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 995313729
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| T_IDX | 1307 | 4 (0)| 00:00:01 |
------------------------------------------------------------------
35 rows selected.
SYS@SAM AS SYSDBA>
Create the evolve report but do not actually change the ACCEPTED flag yet by setting commit=>no
SYS@SAM AS SYSDBA> set serveroutput on
declare evolve_out CLOB;
begin
evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_793213869456f9be', COMMIT => 'NO' );
dbms_output.put_line(evolve_out);
end;
/SYS@SAM AS SYSDBA> 2 3 4 5 6
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------------
Inputs:
----
---
SQL_HANDLE = SQL_793213869456f9be
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY
= YES
COMMIT = NO
Plan: SQL_PLAN_7kchmhua5dydy89596ecb
------------------------------------
Plan was verified: Time used .12 seconds.
Plan passed performance criterion: 60.7 times better
than baseline plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE
COMPLETE
Rows Processed: 1 1
Elapsed Time(ms):
2.817 2.538 1.11
CPU Time(ms): 2.888 2.555
1.13
Buffer Gets: 258 4 64.5
Physical Read
Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0
0
Executions: 1
1
-------------------------------------------------------------------------------
Report
Summary
-------------------------------------------------------------------------------
Number of
plans verified: 1
Number of plans accepted: 0
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA>
Now evolve the plan with commit option as YES.
SYS@SAM AS SYSDBA> set serveroutput on
declare evolve_out CLOB;
begin
evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_793213869456f9be', COMMIT => 'YES' );
dbms_output.put_line(evolve_out);
end;
/SYS@SAM AS SYSDBA> 2 3 4 5 6
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------------
Inputs:
----
---
SQL_HANDLE = SQL_793213869456f9be
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY
= YES
COMMIT = YES
Plan: SQL_PLAN_7kchmhua5dydy89596ecb
------------------------------------
Plan was verified: Time used .07 seconds.
Plan passed performance criterion: 60.7 times better
than baseline plan.
Plan was changed to an accepted plan.
Baseline
Plan Test Plan Stats Ratio
------------- ---------
-----------
Execution Status: COMPLETE COMPLETE
Rows Processed:
1 1
Elapsed Time(ms): 2.804 2.519 1.11
CPU
Time(ms): 2.888 2.555 1.13
Buffer Gets:
258 4 64.5
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0
0
Physical Write Bytes: 0 0
Executions: 1
1
-------------------------------------------------------------------------------
Report
Summary
-------------------------------------------------------------------------------
Number of
plans verified: 1
Number of plans accepted: 1
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA>
SYS@SAM AS SYSDBA> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT ENA ACC FIX
-------------------------------------------------------------------------------- --- --- ---
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy3fdbb376
select count(*) from t YES YES NO
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy89596ecb
select count(*) from t YES YES NO
SQL_a00583ac0188a3d6 SQL_PLAN_a01c3ph0sj8yq562c7608
SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text FROM v$sql WHERE sql_tex YES YES NO
t LIKE '%Select R.Rowid From Fnd_Concur%' order by L
AST_LOAD_TIME asc
TEST@SAM > set autotrace on
TEST@SAM > select count(*) from t;
COUNT(*)
----------
1307
Execution Plan
----------------------------------------------------------
Plan hash value: 995313729
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| T_IDX | 1307 | 4 (0)| 00:00:01 |
------------------------------------------------------------------
Note
-----
- SQL plan baseline "SQL_PLAN_7kchmhua5dydy89596ecb" used for this statement
TEST@SAM >
If we need to drop the original plan, we can do by
declare
drop_result pls_integer;
begin
drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => 'SQL_a00583ac0188a3d6',
plan_name => 'SQL_PLAN_a01c3ph0sj8yq562c7608');
dbms_output.put_line(drop_result);
end;
/
=========================*************************============
Parameters
Two parameters allow you to control SPM.The first, optimizer_capture_sql_plan_baselines, which is FALSE by default, allows you to automatically capture plans. SPM will start managing every repeatable SQL statement that is executed and will create a plan history for it. The first plan that is captured will be automatically accepted. Subsequent plans for these statements will not be accepted until they are evolved.
The second parameter, optimizer_use_sql_plan_baselines, is TRUE by default. It allows the SPM aware optimizer to use the SQL plan baseline if available when compiling a SQL statement. If you set this parameter to FALSE, the SPM aware optimizer will be disabled and you will get the regular cost-based optimizer which will select the best plan based on estimated cost.
Note:Setting optimizer_capture_sql_plan_baselines = true permanently will result in a SQL plan baseline being created for every repeatable SQL statement on the system. That includes all of the recursive SQL Oracle executes on your behalf as well as every 'select sysdate from dual;' or 'select * from v$sql;' the DBA may do on the system. This could lead to a very large number of SQL plan baselines being captured and an increased foot print in the SYSAUX tablespace. There are some customers who do have optimizer_capture_sql_plan_baselines = true and they have not encountered any problem other than a large SYSAUX tablespace!
Scenario where a query is working fine in one instance and working bad in another instance. Thus i will exporting/importing the baseline from one instance to another.
SYS@SAM AS SYSDBA> show parameter baseline;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
Identify the problematic sql text using awr, statspack etc.
Get the sql_id for the sql_text- "Select R.Rowid From Fnd_Concur%"
SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text FROM v$sql WHERE sql_text LIKE '%Select R.Rowid From Fnd_Concur%';
5ugwm6cpfpr7b
create a tuning set
SYS@SAM AS SYSDBA> begin
DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME => '5ugwm6cpfpr7b_STS01', DESCRIPTION => 'TEST SQL TUNE SET');
END;
/ 2 3 4
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA>
Populate sql tuning set
SYS@SAM AS SYSDBA> DECLARE
CUR SYS_REFCURSOR;
BEGIN
OPEN CUR FOR
SELECT VALUE(P) FROM TABLE (DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''5ugwm6cpfpr7b''')) p;
DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME=> '5ugwm6cpfpr7b_STS01', POPULATE_CURSOR=>CUR);
CLOSE CUR;
END;
/
2 3 4 5 6 7 8 9
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA> SYS@SAM AS SYSDBA>
List out sql tuning set
SYS@SAM AS SYSDBA>
SELECT plan_hash_value FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(SQLSET_NAME => '5ugwm6cpfpr7b_STS01'));
PLAN_HASH_VALUE
---------------
2836784050
LOAD DESIRED PLAN FROM ‘SQL TUNING SET’ AS SQL PLAN BASELINE
SYS@SAM AS SYSDBA> DECLARE
MY_PLANS PLS_INTEGER;
BEGIN
MY_PLANS := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(SQLSET_NAME=>'5ugwm6cpfpr7b_STS01', BASIC_FILTER=> 'PLAN_HASH_VALUE=
''2836784050''' );
END;
/ 2 3 4 5 6
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA>
VERIFY IF SQL PLAN BASELINE GOT CREATED SUCCESSFULLY
select * from dba_sql_plan_baselines;
select sql_handle from dba_sql_plan_baselines;
SQL_HANDLE
------------------------------
SQL_a00583ac0188a3d6
========================================================================
Now we have to pack this sql baseline.
Create a staging table in system schema. It cannot be under sys schema
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'stage1');
END;
/
pack the sql baselines
DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => 'stage1',
enabled => 'yes',
SQL_HANDLE => 'SQL_a00583ac0188a3d6');
END;
/
Now export the sql baselines
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@SAM AS SYSDBA> CREATE OR REPLACE DIRECTORY test_dir AS '/xxx_out/dump';
Directory created.
SYS@SAM AS SYSDBA> GRANT READ, WRITE ON DIRECTORY test_dir TO system;
Grant succeeded.
SYS@SAM AS SYSDBA> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@SAM2 /xxx_out> expdp system/*** tables=stage1 directory=TEST_DIR dumpfile=stage.dmp logfile=stage.log
compression=all
Export: Release 11.2.0.3.0 - Production on Thu Jan 5 06:00:15 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** tables=stage1 directory=TEST_DIR dumpfile=stage.dmp
logfile=stage.log compression=all
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/PRE_TABLE_ACTION
Processing object type TABLE_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
. . exported "SYSTEM"."STAGE1" 8.164 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/xxx_out/dump/stage.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 06:09:
--------------------------------------------------------------------------------------------
Import the dump to the target database.
check first if there any baselines present or not.
select * from dba_sql_plan_baselines;
Import the sql baseline
oracle@SAM1 /oracle/stage> impdp system/***** tables=stage1 dumpfile=stage.dmp
Import: Release 11.2.0.3.0 - Production on Thu Jan 5 06:17:23 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** tables=stage1 dumpfile=stage.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."STAGE1" 8.164 KB 1 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 06:17:33
oracle@ /oracle/stage>
unpack the sql baseline
sqlplus system/<password>
SET SERVEROUTPUT ON
SYSTEM> DECLARE
l_plans_unpacked PLS_INTEGER;
BEGIN
l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
table_name => 'STAGE1',
table_owner => 'SYSTEM');
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END; 2 3 4 5 6 7 8
9 /
Plans Unpacked: 1
PL/SQL procedure successfully completed.
SYSTEM@SAM1>
check if the baselines are there or not
SYSTEM@SAM1 > select sql_handle from dba_sql_plan_baselines;
SQL_HANDLE
------------------------------
SQL_a00583ac0188a3d6
check if the plan is accepted or not.
SYS@SAM1 AS SYSDBA> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
If the plan is not accepted, then we have to eveolve it, which is discussed later in the post.
========================================================================
Scenario-2 -Force the optimizer to choose a plan. The first query runs against an unindexed column, does a full scan. And then an index have been added but the optimizer still chooses to do a full table scan. By Evolving the baseline, we forced the optimizer to choose the index and at last we removed the original plan which was doing a full table scan.
oracle@SAM2 ~> sqlplus test/welcome123
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 6 02:57:07 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
TEST@SAM >
TEST@SAM > create table t as select * from applsys.FND_OBJECTS;
Table created.
TEST@SAM >
TEST@SAM > exec DBMS_STATS.GATHER_SCHEMA_STATS ('test');
PL/SQL procedure successfully completed.
TEST@SAM > show parameter baselines
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
TEST@SAM > ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
Session altered.
TEST@SAM > select count(*) from t;
COUNT(*)
----------
1307
TEST@SAM > /
COUNT(*)
----------
1307
TEST@SAM >
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;
SYS@SAM AS SYSDBA> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC FIX
-------------------- ------------------------------------------ ------------------------------------------ --- --- ---
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy3fdbb376 select count(*) from t YES YES NO
Lets create an index and create a new baseline
TEST@SAM > create index t_idx on t (OBJECT_ID);
Index created.
TEST@SAM >TEST@SAM > exec dbms_stats.gather_schema_stats ('test');
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA> alter system flush shared_pool;
System altered.
SYS@SAM AS SYSDBA>
TEST@SAM > ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
Session altered.
TEST@SAM >
TEST@SAM > select count(*) from t;
COUNT(*)
----------
1307
TEST@SAM > /
COUNT(*)
----------
1307
TEST@SAM >
TEST@SAM > ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;
Session altered.
TEST@SAM >
TEST@SAM > set autotrace on;
TEST@SAM > select count(*) from t;
COUNT(*)
----------
1307
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1307 | 30 (4)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- SQL plan baseline "SQL_PLAN_7kchmhua5dydy3fdbb376" used for this statement
SYS@SAM AS SYSDBA> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT ENA ACC FIX
-------------------------------------------------------------------------------- --- --- ---
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy3fdbb376
select count(*) from t YES YES NO
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy89596ecb
select count(*) from t YES NO NO
check the execution plan for this baseline.
SYS@SAM AS SYSDBA> select * from table(dbms_xplan.display_sql_plan_baseline('SQL_793213869456f9be'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_793213869456f9be
SQL text: select count(*) from t
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_7kchmhua5dydy3fdbb376 Plan id: 1071362934
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1307 | 30 (4)| 00:00:01 |
-------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_7kchmhua5dydy89596ecb Plan id: 2304339659
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 995313729
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| T_IDX | 1307 | 4 (0)| 00:00:01 |
------------------------------------------------------------------
35 rows selected.
SYS@SAM AS SYSDBA>
Create the evolve report but do not actually change the ACCEPTED flag yet by setting commit=>no
SYS@SAM AS SYSDBA> set serveroutput on
declare evolve_out CLOB;
begin
evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_793213869456f9be', COMMIT => 'NO' );
dbms_output.put_line(evolve_out);
end;
/SYS@SAM AS SYSDBA> 2 3 4 5 6
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------------
Inputs:
----
---
SQL_HANDLE = SQL_793213869456f9be
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY
= YES
COMMIT = NO
Plan: SQL_PLAN_7kchmhua5dydy89596ecb
------------------------------------
Plan was verified: Time used .12 seconds.
Plan passed performance criterion: 60.7 times better
than baseline plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE
COMPLETE
Rows Processed: 1 1
Elapsed Time(ms):
2.817 2.538 1.11
CPU Time(ms): 2.888 2.555
1.13
Buffer Gets: 258 4 64.5
Physical Read
Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0
0
Executions: 1
1
-------------------------------------------------------------------------------
Report
Summary
-------------------------------------------------------------------------------
Number of
plans verified: 1
Number of plans accepted: 0
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA>
Now evolve the plan with commit option as YES.
SYS@SAM AS SYSDBA> set serveroutput on
declare evolve_out CLOB;
begin
evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_793213869456f9be', COMMIT => 'YES' );
dbms_output.put_line(evolve_out);
end;
/SYS@SAM AS SYSDBA> 2 3 4 5 6
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------------
Inputs:
----
---
SQL_HANDLE = SQL_793213869456f9be
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY
= YES
COMMIT = YES
Plan: SQL_PLAN_7kchmhua5dydy89596ecb
------------------------------------
Plan was verified: Time used .07 seconds.
Plan passed performance criterion: 60.7 times better
than baseline plan.
Plan was changed to an accepted plan.
Baseline
Plan Test Plan Stats Ratio
------------- ---------
-----------
Execution Status: COMPLETE COMPLETE
Rows Processed:
1 1
Elapsed Time(ms): 2.804 2.519 1.11
CPU
Time(ms): 2.888 2.555 1.13
Buffer Gets:
258 4 64.5
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0
0
Physical Write Bytes: 0 0
Executions: 1
1
-------------------------------------------------------------------------------
Report
Summary
-------------------------------------------------------------------------------
Number of
plans verified: 1
Number of plans accepted: 1
PL/SQL procedure successfully completed.
SYS@SAM AS SYSDBA>
SYS@SAM AS SYSDBA> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT ENA ACC FIX
-------------------------------------------------------------------------------- --- --- ---
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy3fdbb376
select count(*) from t YES YES NO
SQL_793213869456f9be SQL_PLAN_7kchmhua5dydy89596ecb
select count(*) from t YES YES NO
SQL_a00583ac0188a3d6 SQL_PLAN_a01c3ph0sj8yq562c7608
SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text FROM v$sql WHERE sql_tex YES YES NO
t LIKE '%Select R.Rowid From Fnd_Concur%' order by L
AST_LOAD_TIME asc
TEST@SAM > set autotrace on
TEST@SAM > select count(*) from t;
COUNT(*)
----------
1307
Execution Plan
----------------------------------------------------------
Plan hash value: 995313729
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| T_IDX | 1307 | 4 (0)| 00:00:01 |
------------------------------------------------------------------
Note
-----
- SQL plan baseline "SQL_PLAN_7kchmhua5dydy89596ecb" used for this statement
TEST@SAM >
If we need to drop the original plan, we can do by
declare
drop_result pls_integer;
begin
drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => 'SQL_a00583ac0188a3d6',
plan_name => 'SQL_PLAN_a01c3ph0sj8yq562c7608');
dbms_output.put_line(drop_result);
end;
/
=========================*************************============
Another method especially in EBS
- 1. Create a Baseline from cursor cache (based on SQL_ID and PHV)
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => 'a29c10n7x9gvt',
plan_hash_value => 749062863);
END;
-- 2. Create TMP table for baseline export
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'STG_SPM_TRANSFER', table_owner => 'APPS');
END;
-- 3. Pack the Baseline into the TMP table
DECLARE
v_sql_handle VARCHAR2(30);
v_packed PLS_INTEGER;
BEGIN
SELECT DISTINCT sql_handle
INTO v_sql_handle
FROM dba_sql_plan_baselines
WHERE signature IN (SELECT exact_matching_signature
FROM v$sql
WHERE sql_id = 'a29c10n7x9gvt');
v_packed := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => 'STG_SPM_TRANSFER',
table_owner => 'APPS',
sql_handle => v_sql_handle
);
DBMS_OUTPUT.PUT_LINE('SQL_HANDLE found: ' || v_sql_handle);
DBMS_OUTPUT.PUT_LINE('Plans packed: ' || v_packed);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error: Baseline not found. Make sure Step 1 (LOAD) was successful.');
WHEN OTHERS THEN
RAISE;
END;
/
-- On DB server
expdp apps/password tables=APPS.STG_SPM_TRANSFER directory=DATA_PUMP_DIR dumpfile=spm_fix.dmp
References:http://www.oracle.com/technetwork/articles/database/create-sql-plan-baseline-2237506.html
https://blogs.oracle.com/optimizer/entry/sql_plan_management_part_1_of_4_creating_sql_plan_baselines
Friday, December 9, 2016
Feedback Loop in EBS 12.2
EBS application layer has
changed significantly in release EBS 12.2. There are two oracle homes here
1. 10.1.2-Oracle AS tools or developer tools
2 Oracle AS 10.1.3 is now replaced by fusion
middleware home
In the previous release of
EBS oacore,oafm, forms and forms-c4ws was deployed in OC4J. Thus one run of
autoconfig used to change all the configurations files. But in EBS 12.2 OC4J is
replaced by weblogic server, thus EBS components are deployed in the managed
servers.
All properties of managed
servers are managed using weblogic tools like admin console. So now to sync the
context file and OHS configuration in weblogic, there is a mechanism introduced
in EBS12.2 . This mechanism is known as feedback loop and it is invoked by 2
scripts.
adRegisterWLSListeners.pl is used to update the XML file listening
to Weblogic parameter changes. This script is invoked automatically on the
primary node every time Weblogic administrator server is started in UNIX
machines.
adSyncContext.pl is for explicitly pulling the values of
Weblogic server and HTTP server parameters to synchronize corresponding context
variable values.
We did some changes by
using Fusion middleware admin console.
These changes have to be
in sync with context file. To do that
You will need to run the following steps on
all application nodes:
Go to cd $AD_TOP/bin/
perl
<AD_TOP>/bin/adSyncContext.pl contextfile=<CONTEXT_FILE>
This will read the WLS
configuration parameter values and synchronize them with the context variables.
Run The Autoconfig on All
App Nodes.
These are some of the
activities which autoconfig cannot manage by itself completely.
Tuesday, December 6, 2016
Abandoned nodes in EBS 12.2
Think of a scenario, where EBS has a multinode
environment(Not Shared application tier or Shared APPL TOP), in such case we
have to patch all the application nodes. One node will be an admin node and
others will fall into secondary node. In a multinode environment, adop command
is invoked only in admin node and internally adop uses SSH connectivity to
execute required patching activities in other secondary nodes. (Before
executing, we should have ssh connectivity in
place-https://www.clearos.com/resources/documentation/clearos/content:en_us:kb_o_setting_up_ssh_trust_between_two_servers)
Tip: If you change the password for the relevant operating
system account on one or more nodes, you must regenerate the ssh credentials
either using the $AD_TOP/patch/115/bin/txkRunSSHSetup.pl script, or your own
native solution if you prefer(https://docs.oracle.com/cd/E26401_01/doc.122/e22954/T202991T531065.htm)
The txkRunSSHSetup.pl script has a -help option that shows
relevant usage options.
For example, a basic command to enable ssh would be:
$ perl $AD_TOP/patch/115/bin/txkRunSSHSetup.pl enablessh
-contextfile=<CONTEXT_FILE> -hosts=h1,h2,h3$
To verify ssh operation:
$ perl $AD_TOP/patch/115/bin/txkRunSSHSetup.pl verifyssh
-contextfile=<CONTEXT_FILE> -hosts=h1,h2,h3 \
-invalidnodefile=<filename to report ssh verification
failures>
To disable ssh:
$ perl $AD_TOP/patch/115/bin/txkRunSSHSetup.pl disablessh \
-contextfile=<CONTEXT_FILE> -hosts=h1,h2,h3 \
-invalidnodefile=<filename to report ssh verification
failures>
Now the question-what happens if adop fails in one
node and complete successfully in another node.
example for this error-
prepare phase failed on node secondary node. if you choose
to proceed with cutover, node will be marked as abandoned.
Do you want adop to continue with other completed nodes
[y/n]
if we press n, it will exit out of this and then we have to
rectify the error first and restart the prepare phase again by
adop phase=prepare allnodes=yes
The same goes for apply phase.
But for cutover phase, the situation is little
different. ADOP will just continue by just skipping the problematic node(the
problematic node has to be secondary) and this problematic node will be marked
as abandoned after cut-over. If the abandoned node is meant for imporant ebs
Services such as conc processing then skipping this will impact the
availability of concurrent processing. So it is not advisable to skip any
errors during patching. And also the admin node cannot have a status abandoned,
so if an error occurs in admin node, it cannot be skipped and has to be
corrected before proceeding with patching. The abandoned nodes has to be removed
and recreated using rapid clone.
Friday, September 16, 2016
EBS 12.2.6 is now available.
Oracle recently announced the new version of EBS which is release 12.2.6.
For the upgrade customers: This is a online patch(Patch 21900901) and customers who are in ebs 12.2.x can move to 12.2.6 using online patching cycle.
Those users who are upgrading from 11i ,12.0 or 12.1 or doing a new install of 12.2 can apply this patch using apply_mode=downtime to take their ebs to 12.2.6. Also the minimum version of DB should be atleast 11.2.0.4
11i customers should first upgrade to 12.2 before applying 12.2.6.
12.0 and 12.1 customers should first upgrade to 12.2 before applying 12.2.6.
12.2 customers can directly apply 12.2.6.
In this, they have concentrated mainly upon three key areas.
Functional innovation
Modern user Experience and mobility
Operational Efficiency
For the upgrade customers: This is a online patch(Patch 21900901) and customers who are in ebs 12.2.x can move to 12.2.6 using online patching cycle.
Those users who are upgrading from 11i ,12.0 or 12.1 or doing a new install of 12.2 can apply this patch using apply_mode=downtime to take their ebs to 12.2.6. Also the minimum version of DB should be atleast 11.2.0.4
11i customers should first upgrade to 12.2 before applying 12.2.6.
12.0 and 12.1 customers should first upgrade to 12.2 before applying 12.2.6.
12.2 customers can directly apply 12.2.6.
In this, they have concentrated mainly upon three key areas.
Functional innovation
Modern user Experience and mobility
Operational Efficiency
Approvals, sign ordering documents becomes
easy and can be done anytime, anywhere and at any time. Intelligent interleaving
of inbound and outbound tasks were introduced to reduce deadheading. There are
many others functional changes which have been done in this new release. More
information can be found:
Oracle E-Business
Suite System Administration Release Notes for Release 12.2.6 (Doc ID 2174164.1)
Oracle E-Business
Suite Release 12.2.6 Readme (Doc ID 2114016.1)
Steven Chan blogs: https://blogs.oracle.com/stevenChan/entry/ebs_1226_now_available
From the system administration point of
view, there are many interesting changes done in EBS 12.2.6
Feature Name
3.1 Secure Configuration Console
3.2 Allowed JSPs Restricted Access Enabled
by Default
3.3 Allowed Redirects Restricted Access
Enabled by Default
3.4 Changes to the Applications SSO Type
(APPS_SSO) Profile
3.5 Proxy Auditing
3.6 Audit Trail Search HTML UI
3.7 Oracle E-Business Suite Forms in
Read-Only Mode on the Responsibility or User Level
3.8 New Schemes for Storing Concurrent
Processing Log and Output Files
3.9 Standard Request Submission
Enhancements
3.10 Flexfields Value Set Security Setup
Wizard
3.11 Flexfields Registration HTML UI
3.12 Improved Language Determination
3.13 Oracle Applications Manager Licensing
for Lightweight MLS
3.14 Translation Synchronization Patches
Manifest File for Full Mode Languages Only
Being an APPS DBA, 3.8 and 3.9 was more of
a hit to me.
3.8- New Schemes for Storing Concurrent
Processing Log and Output Files
This enhancement introduces additional
choices of storage schemes for management of large numbers of concurrent
processing log and output files. For example, these files can be organized by
user name or by date. Customers can specify the scheme that best suits their
particular needs.
The schemes are:
•SCHEME = single
This
is the default scheme. Request log files will go into $APPLCSF/log and log
files will go into $APPLCSF/out.
•SCHEME = product
Request log and out files will go to
$APPLCSF/<product short name>/log and $APPLCSF/<product short
name>/out, respectively.
•SCHEME = user
Requests log and out files will go to
$APPLCSF/<user name>/log and $APPLCSF/<user name>/out,
respectively.
•SCHEME = date
This
scheme takes a string as a required parameter, and files will be organized in
directories by date.
•SCHEME = reqidexp
This
scheme takes an integer value for a parameter. Directories will be created
based on that integer and the request ID number place. For example, using this
formula: result = int(request_id / 10^(parameter)) * 10^(parameter)
If
the argument is 1, request 12345 will be put in 12340
If
the argument is 2, request 12345 will be put in 12300
If
the argument is 3, request 12345 will be put in 12000
•SCHEME = reqidmod where
reqidmod:<integer> (required)
This
scheme takes an integer value for a parameter. Valid values are all positive
integers except for zero. Managers will create specified n number of
directories starting with 0 (zero) and continue with other numbers in
sequential order. Then, each manager process will perform the following
function to determine log and output location: result = request_id mod
<parameter>
Requests log and out files will go to
$APPLCSF/<resulting string>/log and $APPLCSF/<resulting
string>/out, respectively.
•SCHEME = mgrproc
Requests log and out files will go into
$APPLCSF/<manager's process ID>/log and $APPLECSF/<manager's process
ID>/out, respectively. Therefore, each manager's process will have its own
directory.
3.9 Standard
Request Submission Enhancements
The standard request submission (SRS) View
Requests window has the enhancements listed below. These changes simplify the
navigation required to perform these steps and/or reduce the number of
mouse-clicks needed.
•Auto Refresh check box - When this box is
selected, the form will refresh the list of requests after a specified interval
measured in seconds. Use the profile option "Concurrent: Auto-refresh View
Request Timer (secs)" to set this interval.
•Rerun Request button - This new button
will resubmit the selected request with exactly the same parameters, after
confirmation. Note that you cannot use this button to rerun non-SRS requests.
•Copy Single Request and Copy Request Set
buttons - these buttons allow you to copy a single request or request set,
respectively. A list of values is provided to select the request or request
set.
•Submit a New Request and Submit New
Request Set - These buttons allow you to submit a new request or submit a new
request set, respectively. These buttons are also added to the Find Requests
window.
Category
Name
Technical Name
Description
Changed Form Standard Request Submission
(Forms) FNDRSRUN Updated Find Requests and View Requests windows.
Category
Profile Option Name
Feature Area
Description
New Profile Option Concurrent: Auto-refresh
View Request Timer (secs) Concurrent Processing
This profile option sets the interval, in seconds, for refreshing the
View Request window based on a timer, if the Auto Refresh box is checked. The
default value is 300, if no value is given. The value 0 disables Auto Refresh
Tuesday, September 6, 2016
Beware of crontab - l
You have cron jobs running in Prod server and everyone is happy about it. Also you don't have the backups of cron entries. Now, by mistake, one of the junior dba executed the command crontab - l instead of running crontab -l. The cron did hang at this moment of time. To come out of this, he entered ctrl-d and exited out of the session as well as from putty. The next shift started and the other dba thought of checking the crontab entries and thus executed the command crontab -l...Eureka. He could not see any cron entires. Unix team forwarded the log file /var/log/cron and the term "REPLACE" was logged in when the dba executed ctrl-d. So be careful next time.
Saturday, August 27, 2016
Edit /etc/fstab in maintenance mode
Imagine your server crashes (due to some hardisk I/O error, for instance, or removed the hdd by mistake). Then if you reboot your machine, it’ll spit out something like:
Checking filesystems...
e2fsck: Cannot continue, aborting
Type root password for maintenance mode or CTRL+D to continue
Lets suppose the culprit of this is some HDD (for instance, /dev/mapper/Vt31-p1 which should be mounted in /software).
Then if you read the contents of /etc/fstab you will see one line like:
/dev/mapper/Vt31-p1 /t31 ext3 defaults 1 2
If you try to comment this line, your editor will complain and tell “changes cannot be written” or something like that. Why is this? Well, your /etc files have been mounted on a non writable partition (maintenance mode, you remember?). So you will have to remount this partition in RW mode. Just like this:
mount -o remount,rw /
Then edit fstab, save your changes and reboot.
Subscribe to:
Posts (Atom)