Oracle EBS救急metalink文档编号

1、GLBALFIX: GL Balances Corruption Datafix – Master Note (文档 ID 1563019.2);

2、Oracle培训视频:740966.1;

3、EBS Concurrent Processing (CP) Analyzer (文档 ID 1411723.1);

4、E-Business Suite Applications Login Analyzer (文档 ID 2319360.1);

5、EBS BI Publisher (BIP) Analyzer (文档 ID 2032715.1);

6、EBS R12.0.x / R12.1.x : How To Find Location Of Install, Autoconfig, Patching , Clone And Other Logs ? (文档 ID 804603.1);

7、Remote Diagnostic Agent (RDA) – Getting Started (Doc ID 314422.1)。

Linux操作系统定时任务crond

Linux操作定时清理固定路径下的文件,可以直接用命令方式。        
1) 执行命令:
     例如:find /u01/DEV/app/fs_ne/inst/DEV_devfin/logs/appl/conc/out/  -mtime +90 -exec rm -f {} \;
     (-mtime +90为删除最后修改时间在90天以前的文件,可自己配置)
2) 在linux上配置定时任务(通过有权限的用户):        
        说明:因为我们使用的是系统的crontab文件,系统的crontab文件路径为:/etc/crontab
        编辑crontab文件: 
         命令:vi /etc/crontab,输入i;进入编辑模式,在文件末尾写入步骤一的命令,配置任务定时时间:
         crontab定时配置说明:  
          *(分)  *(时)   *(天)  *(月)  *(星期)
          crontab中最终写入的命令为: 00 01 * * * root  find /u01/DEV/app/fs_ne/inst/DEV_devfin/logs/appl/conc/out/  -mtime +90 -exec rm -f {} \;(每天凌晨一点开始执行此命令;     
3) 核实定时任务是否执行:
          命令(root): tail /var/spool/mail/appldev       
4) 若定时任务没有执行,输入命令检查服务是否启动service crond status,确保crond状态为 is  running,如果没有则重启service crond restart 即可。

SQL Tuning Health-Check Script (SQLHC) (文档 ID 1366133.1)

Applies to: 
Oracle Database – Personal Edition – Version 10.2.0.1 and later
Oracle Database – Enterprise Edition – Version 10.2.0.1 and later
Oracle Database – Standard Edition – Version 10.2.0.1 and later
Oracle Database Products > Oracle Database Suite > Oracle Database
Information in this document applies to any platform.

Purpose 

Download the SQL Tuning Health-Check Script

SQL Health Check

Download the SQLHC Script Here (.zip archive).

 

What is the SQL Tuning Health-Check Script (SQLHC)?

The SQL Tuning Health-Check Script is a tool developed by the Oracle Server Technologies Center of Expertise. The tool, also known as SQLHC, is used to check the environment in which a single SQL Statement runs, checking Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters and other elements that may influence the performance of the one SQL being analyzed.

Licensing (SQLHC requires no license and is FREE)

As in the title of this section, SQLHC requires no license and is FREE. 
Since SQLHC can use information from AWR reports if the Diagnostic or Tuning pack are installed, it asks if these packs are licensed at your site. For more details refer to the licensing section in the SQLHC FAQ:

Document 1454160.1 FAQ: SQL Health Check (SQLHC) Frequently Asked Questions
 

Overview of the SQL Tuning Health-Check Script

An overview video about SQLHC is available here :

Document 1455583.1 SQL Tuning Health-Check Script (SQLHC) Video


The intention of SQLHC is to allow users to avoid SQL Performance from being affected by avoidable problems by ensuring that the environment that an individual SQL runs in is sound.

It does this while leaving "no database footprint" ensuring it can be run on all systems.

When executed for one SQL_ID, this script generates an HTML report with the results of a set of health-checks around the one SQL statement provided. You can find the SQL_ID of a statement from an AWR or ASH report or you can select it from the database using the V$SQL view. See:

Document 1627387.1 How to Determine the SQL_ID for a SQL Statement

Health-checks are performed over:
•CBO Statistics for schema objects accessed by the one SQL statement being analyzed
•CBO Parameters
•CBO System Statistics
•CBO Data Dictionary Statistics
•CBO Fixed-objects Statistics

NOTE: A webcast has been recorded entitled: "How to Improve SQL Performance with the New Health Check Tool?".

This can be found, along with many other recorded webcasts, here:

Document 740964.1 Advisor Webcast Archived Recordings

A FAQ for the SQL Healthcheck script (SQLHC) can be found here:


Document 1417774.1 FAQ: SQLHC HealthCheck Frequently Asked Questions

Additionally, we welcome any additional health-checks that you may suggest. If any specific health-checks are needed and not covered by this script, then as long as the health-check can be produced with a SQL Command (leaving no database footprint ) then these can be implemented in future versions.

Please add comments to this Document for any desired additions.
 

Best Practices

Pro-Active Problem Avoidance and Diagnostic Collection

Although some problems may be unforeseen, in many cases problems may be avoidable if signs are detected early enough. Additionally, if an issue does occur, it is no use collecting information about that issue after the event. SQLHC is one of the tools that support recommend for collecting such diagnostics. For information on suggested uses, other proactive preparations and diagnostics, see:

Document 1482811.1 Best Practices: Proactively Avoiding Database and Query Performance Issues
Document 1477599.1 Best Practices Around Data Collection For Performance Issues

Ask Questions, Get Help, And Share Your Experiences With This Article

Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?

Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.
Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Tuning.

Requirements

Execute this script from SQL*Plus connecting as SYS, DBA or a user with access to Data Dictionary views.

NOTE: The script ADDS NO OBJECTS TO THE DATABASE. It simply reports and advises on existing objects

Configuring

There is no configuration required.

Instructions
1.Login to the database server and set the environment used by the Database Instance
2.Download the "sqlhc.zip" archive file and extract the contents to a suitable directory/folder
3.Connect into SQL*Plus as SYS, a DBA account, or a user with access to Data Dictionary views and simply execute the "sqlhc.sql" script. It will request to enter two parameters:

i.Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
If site has both Tuning and Diagnostics licenses then specify T (Oracle Tuning pack includes Oracle Diagnostics)


ii.A valid SQL_ID for the SQL to be analyzed.  


For Example:

# sqlplus / as sysdba
SQL> START sqlhc.sql T djkbyr8vkc64h

#SQLHC报告说明

1_health_check.html
    Observations:显示health-checks输出的可能存在问题的项目,确认是否需要更改.例如:对象统计信息过旧
    SQL Text:要检查的sql文本
    Tables Summary:显示相关表的统计信息大概
    Indexes Summary:显示相关索引的统计信息大概
2_diagnostics.html
    SQL Text:要检查的sql文本
    SQL Plan Baselines (DBA_SQL_PLAN_BASELINES):DBA_SQL_PLAN_BASELINES中此sql的plan history
    SQL Profiles (DBA_SQL_PROFILES):DBA_SQL_PROFILES中此sql的profiles
    SQL Patches (DBA_SQL_PATCHES):DBA_SQL_PATCHES中此sql的相关patches
    Cursor Sharing and Reason:GV$SQL_SHARED_CURSOR是此sql的cursor sharing
    Cursor Sharing List:GV$SQL_SHARED_CURSOR中此sql的cursor sharing
    Current Plans Summary (GV$SQL):GV$SQL中此sql的平均消耗
    Current SQL Statistics (GV$SQL):GV$SQL中此sql的消耗
    Historical Plans Summary (DBA_HIST_SQLSTAT):DBA_HIST_SQLSTAT中此sql的历史平均消耗
    Historical SQL Statistics – Delta (DBA_HIST_SQLSTAT):DBA_HIST_SQLSTAT中此sql的历史消耗
    Historical SQL Statistics – Total (DBA_HIST_SQLSTAT):DBA_HIST_SQLSTAT中此sql的历史消耗
    Active Session History by Plan (GV$ACTIVE_SESSION_HISTORY):GV$ACTIVE_SESSION_HISTORY中此sql的session state统计
    Active Session History by Plan Line (GV$ACTIVE_SESSION_HISTORY):GV$ACTIVE_SESSION_HISTORY中此sql的plan line统计
    AWR Active Session History by Plan (DBA_HIST_ACTIVE_SESS_HISTORY):DBA_HIST_ACTIVE_SESS_HISTORY中此sql的session state统计
    AWR Active Session History by Plan Line (DBA_HIST_ACTIVE_SESS_HISTORY):DBA_HIST_ACTIVE_SESS_HISTORY中此sql的plan line统计
    DBMS_STATS System Preferences:DBMS_STATS系统参数
    Tables:表相关的统计信息
    DBMS_STATS Table Preferences:DBMS_STATS表参数
    Table Columns:列相关的统计信息
    Table Partitions:表分区相关的统计信息
    Table Constraints:表上的约束信息
    Tables Statistics Versions:表相关的统计信息
    Indexes:索引相关的统计信息
    Index Columns:索引列相关的统计信息
    Index Partitions:索引分区相关的统计信息
    Indexes Statistics Versions:索引相关的统计信息
    System Parameters with Non-Default or Modified Values:GV$SYSTEM_PARAMETER2中isdefault = 'FALSE' OR ismodified != 'FALSE'的参数
    Instance Parameters:V$SYSTEM_PARAMETER2中的系统参数
    Metadata:表和索引的建立语句
3_execution_plans.html
    SQL Text:要检查的sql文本
    Current Execution Plans (last execution):按child cursor显示此sql的内存中的执行计划
    Current Execution Plans (all executions):按child cursor显示此sql的内存中的执行计划
    Historical Execution Plans:按child cursor显示此sql的awr的执行计划
4_sql_detail.html
    图形化显示sql的运行统计
5_sql_monitor.zip
    图形化显示sql的监控信息
6_10053_trace_from_cursor.trc
    显示sql的10053跟踪文件
8_sqldx.zip
    sql health check数据来源
9_log.zip
    sql health check生成日志

对于SQLHC(SQL Health Check,SQL性能健康检查脚本)工具,Mos文档1626277.1有非常详细的介绍。SQLHC是Oracle Server Technologies Center of Expertise开发的一个工具。SQLHC用于检查单条SQL语句运行的环境,包括基于成本的优化器(CBO)的统计数据,用户对象的元数据定义,配置参数和其它可能影响到目标SQL性能的因素。SQLHC和SQLT工具一样,本身都是免费的,不需要任何许可证(License)。当对某一个SQL_ID运行SQLHC后,该脚本会生成一系列针对该SQL语句健康检查的一份HTML报告。SQLHC会检查的内容包括:① 待分析的单条SQL涉及的用户对象的CBO统计信息;② CBO参数;③ CBO系统统计信息;④ CBO数据字典统计信息;⑤ CBO固定对象(Fixed-Objects)统计信息。

SQLHC运行时不会在数据库中创建任何对象(“数据库中不留足迹”),它只是对已有的对象提供报告和建议,可以确保它在所有系统上运行。SQLHC的脚本需要以SYS、DBA或者能访问数据字典视图的用户通过SQL*Plus连接运行。SQLHC一共包含3个脚本,分别为sqlhc.sql、sqldx.sql和sqlhcxec.sql,其中sqlhc.sql里边会调用sqldx.sql脚本。sqlhcxec.sql是单独执行的,不过该脚本需要输入一个脚本文件作为入参,而且该脚本文件可以包含绑定变量。

本地测试:http://blog.itpub.net/28539951/viewspace-2100920/

SQL 性能健康检查脚本 (SQLHC) (文档 ID 1626277.1)

表收集信息(防止过旧信息):

begin
  fnd_stats.GATHER_TABLE_STATS('SYS', 'TAB$');
end;

EBS系统数据库统计信息收集总结- gather_schema_stats [final]_ITPUB博客

分析一个SCEHMA (EBS系统) :
exec  fnd_stats.GATHER_TABLE_STATS('PA');

分析一个SCEHMA (一般系统) :
exec dbms_stats.gather_schema_stats(
  ownname          => 'HRM',
  options          => 'GATHER AUTO',
  estimate_percent => dbms_stats.auto_sample_size,
  method_opt       => 'for all columns size repeat', — 稳定后推荐repeat
      cascade          =>TRUE,
  degree           => 12
    )

–一天内执行的sql

select a.*,b.*
  from v$sqltext a, v$session b
 where a.sql_id = b.sql_id
   and b.logon_time between sysdate – 1 and sysdate;

 

select * from dba_hist_active_sess_history t;

扩展:

All About the SQLT Diagnostic Tool (文档 ID 215187.1)

oracle EBS R12和11i的主要区别(转)

简单的说
1、架构变了,原来的SOB现在叫Ledger
2、客户、供应商、税提升了一个级别
3、客户、供应商、银行改为web界面的
4、AR、AP可以支持在一个职责下跨OU查询和录入。
5、引入SLA架构,数据逻辑更清晰。另外从子模块到总账的数据形式和生成的摘要都可编辑。简化了开发的工作量。

一、在原来的应收/应付/项目/CST和GL之间增加了一个层次SLA(SUBLEDGER ACCOUNT 子分类帐),通过这个层次来进一步支持全球财务解决方案,如银行、税务引擎、公司间业务等;即银行帐号的定义是法人共享,而不是OU共享。
二、取消帐套的定义,改为以法人单位为核心,定义多套帐的模式。如一个法人可对应PRIMARY LEDGER;SECOND LEDGER,REPORT CURRENCY LEDGER 等
三、权限设置模式改变,12使用角色连接OU,用户指定角色的方式,处理多OU的业务不需要频繁切换。
四、LEDGER的定义要素为4C,分别为原来的3C+ACCOUNTING CONVENTION(会计方法)。
五、新推出LEDGER SET的概念,当COA和Calendar一致的时候可以将多个LEDGER定义为一个LEDGER SET,作用是可以跨LEDGER出报表。可以统一进行会计期的管理及COA的维护。
六、会计方法:ORACLE仍然保留了权责发生制和收付实现制,同时提供了会计方法定制的界面,即AMB(ACCOUNTING MOTHODS BUILDER),如果不使用AMB自己定义会计方法,而选择标准的权责发生制,则应收/应付的设置同11I。
七、增加了全球税务模块,税务规则统一制定(未使用)
八、将应付分为了应付会计和付款会计,一是为了符合萨班斯法案;二是解决统一支付的需求。
九、应收增加了创建会计科目的按钮,收款科目简化了。
十、固定资产增加了创建会计科目的按钮,运行折旧与关闭期间动作分开了,一个月中可以出现多张折旧凭证。
十一、在会计科目段中增加了属性-“第三方控制帐户”,将科目设置为第三方控制帐户后,在SLA中就可以以科目+第三方出余额表,在SLA中的应收应付导入的凭证,是符合用户习惯的凭证,今后可以考虑凭证打印从此处实现。
十二、在会计科目组合界面增加了替代帐户的概念,当原科目失效后如果还有业务发生,系统会使用替代帐户代替。
十三、GL序列产生的时点由原来的一个变为四个。
十四、 R12 多了诊断功能,可以在查询某个记录涉及的所有表。对于处理问题有很好的帮助。

转自:https://www.cnblogs.com/fanofyu/p/4497867.html

参考:https://www.cnblogs.com/quanweiru/p/5021066.html

Oracle EBS 应用用户审计日志记录

Oracle EBS已经存在表fnd_logins,可以通过设置配置文件对Oracle EBS用户登录行为进行记录,但默认没有打开。

默认情况下该表已经有大量的记录了,类型为Concurrent,猜测为用户提交并发请求的日志记录,Concurrent实际并没有什么作用:

可以通过配置文件设置,对用户登录进行记录:

配置文件:登录:审计层

建议值:用户

作用:记录用户登录日志

配置文件值含义
1) NONE – no audit enabled (Default value)

配置文件的默认值,不对用户登录进行记录


2) USER – audit user login to system, the logon time and the logoff time.

用户,记录用户的登录行为


3) RESPONSIBILITY – audit all the above + which responsibilities the user chose and how long he stayed in each responsibility.

RESPONSIBILITY,记录用户登录+用户什么时候进入某一责任,在每个责任停留时间


4) FORM – audit all the above + which forms the user used and how long he stayed in each form.

表单,记录用户登录+责任+用户什么时候打开哪个Forms界面


不同的日志,记录在不同的表里面:

1) USER – populates the FND_LOGINS table only.
2) RESPONSIBILITY – populates FND_LOGINS and FND_LOGIN_RESPONSIBILITIES tables.
3) FORM – populates FND_LOGINS, FND_LOGIN_RESPONSIBILITIES and FND_LOGIN_RESP_FORMS tables.

FND_LOGINS – holds information about users login to system, when and how long.
This table holds one row for each login.

FND_LOGIN_RESPONSIBILITIES – holds information about changes of responsibilities, when and how long being at each responsibility.
For each change this table holds one row with values that identify the user's login session, the user's current responsibility, and when the user is in the responsibility

FND_LOGIN_RESP_FORMS – holds information about using forms, when and how long.
This table holds one row for each form used in the same session with values that identify the user's login session, current responsibility, when and how long using each form.

注:此时可以通过 系统管理员-安全性-用户-监控,监控当前登录系统的用户

用户登录行为的一些报表

1.登录审计未成功注册

作用:用来打印出登录失败用户的时间以及用户

2.登录审计用户

作用:用户登录的历史记录

3.清除登录审计数据

作用:清除fnd_logins开头的表的历史记录,报表有一参数“审计时间”,小于该值的历史记录会被清除。


转载自:https://blog.csdn.net/sunansheng/article/details/52238499 
 

Oracle ERP笔记(小小的实施记录博客)