ebs version check

----------------------------------------- 数据库模块列表
set lines 200 pages 50000
col comp_id format a10
col comp_name format a35
col version format a15
col status format a15
select comp_id,comp_name, version, status from dba_registry;

----------------------------------------- nodes check 
spool fnd_nodes
set lines 200 pages 50000
col node_name format a15
col server_id format a8
col server_address format a15
col platform_code format a4
col webhost format a25
col domain format a20
col virtual_ip format a12
select
node_id,
platform_code,
support_db D,
support_cp C,
support_admin A,
support_forms F,
support_web W,
node_name,
server_id,
server_address,
domain,
webhost,
virtual_ip
from
apps.fnd_nodes
order by node_id;

----------------------------------------- version check 
select b.bug_number, b.language, to_char(b.LAST_UPDATE_DATE, 'YYYY-MM-DD HH24:MI:SS') Last_update from apps.ad_bugs b where bug_number='&a'; 

select * from apps.icx_parameters;
登录每台机器执行命令( !date;id;pwd;hostname -f;echo $ORACLE_SID;echo $TWO_TASK):

!date;id;pwd;hostname -f;echo $ORACLE_SID;echo $TWO_TASK
SELECT name,open_mode,dbid FROM v$database;
SELECT node_name,support_cp,support_forms,support_web,support_admin,support_db FROM apps.fnd_nodes;
SELECT release_name,creation_date FROM apps.fnd_product_groups;
SELECT instance_name,status,parallel,archiver,version FROM v$instance;
SELECT nls_language,language_code,installed_flag FROM apps.fnd_languages WHERE installed_flag in ('I','B');

R12 new: SELECT bug_number,language,creation_date FROM apps.ad_bugs where bug_number='&a';
11i old:SELECT bug_number,creation_date FROM apps.ad_bugs where bug_number='&a';
11i old:select release_name from fnd_product_groups;

select patch_level from fnd_product_installations where application_id = 50;

SELECT application_id,status,patch_level FROM apps.fnd_product_installations where PATCH_LEVEL like '%AD%';
SELECT application_id,status,patch_level FROM apps.fnd_product_installations where application_id=2031;

/autofs/upgrade/ohsupg/bin/check_module.sh -module PA

select decode(support_db,'Y','DB: ','MT: ')||lower(host)||'.'||lower(domain) name from apps.fnd_nodes where node_name <> 'AUTHENTICATION' order by node_name;

set line 300
col host for a30
col domain for a30
col webhost for a30
select node_name, host, domain, webhost, SUPPORT_DB, SUPPORT_CP, SUPPORT_FORMS, SUPPORT_WEB, SUPPORT_ADMIN from apps.fnd_nodes where NODE_NAME<>'AUTHENTICATION';

NODE_NAME HOST DOMAIN WEBHOST S S S S S
------------------------------ ------------------------------ ------------------------------ ------------------------------ - - - - -
C1-ZJCWEBS1 c1-zjcwebs1 dehetu.com c1-zjcwebs1.dehetu.com N Y Y Y Y
RAC1 rac1 dehetu.com Y N N N N
RAC2 rac2 dehetu.com Y N N N N
C1-ZJCWEBS2 c1-zjcwebs2 dehetu.com c1-zjcwebs2.dehetu.com N Y Y Y Y

----------------------------------------- E-Business Suite Version
select instance_name, release_name, host_name,
startup_time, version
from applsys.fnd_product_groups, v$instance;

----------------------------------------- Instance Node Details
select substr(node_name, 1, 20) node_name, node_mode, server_address, substr(host, 1, 15) host,
substr(domain, 1, 20) domain, substr(support_cp, 1, 3) cp, substr(support_web, 1, 3) web,
substr(support_admin, 1, 3) ADMIN, substr(support_forms, 1, 3) FORMS,
substr(SUPPORT_DB, 1, 3) db, substr(VIRTUAL_IP, 1, 30) virtual_ip
from apps.fnd_nodes;

----------------------------------------- WF components
set linesize 120
set pagesize 50
column COMPONENT_NAME format a45
column STARTUP_MODE format a15
column COMPONENT_STATUS format a16
select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS from apps.FND_CONCURRENT_QUEUES_VL fcq,apps.fnd_svc_components fsc where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+) order by COMPONENT_STATUS,STARTUP_MODE,COMPONENT_NAME;

----------------------------------------- Check Invalids
--按照用户
break on report
compute sum of INVALIDS on report
SELECT owner,count(object_name) INVALIDS from dba_objects where status ='INVALID' group by owner;

--按照用户及对象类型
break on report
compute sum of INVALIDS on report
SELECT owner,object_type, COUNT(*) INVALIDS from dba_objects where status ='INVALID' group by owner,object_type;

pre
create table apps.invalids_<rfc_num> as (select object_name,owner from dba_objects where status='INVALID');

post
SELECT owner,object_name,object_type from dba_objects where status ='INVALID' and object_name not in(SELECT object_name from apps.invalids_<RFC NUM>); 

-----------------------------------------表大小
--注，仅表数据的大小，不含索引、分区、LOB类型
select sum(bytes)/1024/1024 "表大小(M)" from dba_segments where segment_name='AP_SUPPLIER_SITES_ALL';

select Segment_Name,Sum(bytes)/1024/1024 From dba_Extents Group By Segment_Name having Segment_Name='AP_SUPPLIER_SITES_ALL';

select sum(bytes)/1024/1024/1024 GB from dba_temp_files /*临时文件大小*/
union
select sum(bytes)/1024/1024/1024 GB from dba_data_files /*数据文件大小*/
union
select sum(bytes)/1024/1024/1024 GB from dba_segments; /*对象大小*/

--used_mb存储空间使用大小
column name format a20
column free_mb format 999,999,999
select name,to_char(sysdate,'YYYY-MM-DD HH24:MI') SYSTEM_DATETIME from v$database;
select (select name from v$database) db_name,name diskgroup_name,total_mb,free_mb,(total_mb-free_mb) used_mb,round(free_mb/total_mb*100,1) FREE_Uti_PCT from v$asm_diskgroup order by 4;

-----------------------------------------
shutdown immediate
startup upgrade

@?/rdbms/admin/utlirp     --invalid all the objects 
@?/rdbms/admin/utlprp 16  --compile all the invalids
select count(*) from dba_objects where status = 'INVALID';

----------------------------------------- Compile new invalids
Ans:
alter package <package_name> compile ;
alter package <packae_name> compile body;
alter view <view_name> compile; 

@?/rdbms/admin/utlrp =>compile all the invalids
@?/rdbms/admin/utlprp 8 =>compile all the invalids(并行度8)

exec utl_recomp.recomp_parallel(8);

--exec dbms_utility.compile_schema('APPS'); 太慢

SQL> select owner,object_name,object_type from dba_objects where status ='INVALID' and object_name not in(select object_name from apps.mafify_invalids);

OWNER OBJECT_NAME OBJECT_TYPE
--------------- ------------------------------ -------------------
APPS XLA_BALANCES_CALC_PKG PACKAGE BODY
APPS XLA_BALANCES_PKG PACKAGE BODY

SQL> alter package XLA_BALANCES_PKG compile body;
SQL> alter package XLA_BALANCES_CALC_PKG compile body;

alter procedure <owner>.<procedure_name> compile;
alter function <owner>.<function_name> compile;
alter view <owner>.<view_name> compile;
alter package <owner>.<package_name> compile;
alter package <owner>.<package_name> compile body;
alter materialized view <owner>.<Package_name> compile;

spool compile.sql
set lines 200 pages 50000 
select 'alter PROCEDURE '||OWNER||'.'||OBJECT_NAME||' compile;'from dba_objects where status='INVALID' and OBJECT_TYPE='PROCEDURE'
union
select 'alter FUNCTION '||OWNER||'.'||OBJECT_NAME||' compile;'from dba_objects where status='INVALID' and OBJECT_TYPE='FUNCTION'
union
select 'alter VIEW '||OWNER||'.'||OBJECT_NAME||' compile;'from dba_objects where status='INVALID' and OBJECT_TYPE='VIEW'
union
select 'alter TRIGGER '||OWNER||'.'||OBJECT_NAME||' compile;'from dba_objects where status='INVALID' and OBJECT_TYPE='TRIGGER'
union 
select 'alter package '||OWNER||'.'||OBJECT_NAME||' compile;'from dba_objects where status='INVALID' and OBJECT_TYPE='PACKAGE'
union
select 'alter package '||OWNER||'.'||OBJECT_NAME||' compile body;'from dba_objects where status='INVALID' and OBJECT_TYPE='PACKAGE BODY'
union
select 'alter MATERIALIZED VIEW '||OWNER||'.'||OBJECT_NAME||' compile;'from dba_objects where status='INVALID' and OBJECT_TYPE='MATERIALIZED VIEW'; 
spool off

@?/rdbms/admin/utlprp 16
select count(*) from dba_objects where status = 'INVALID';

alter session set nls_date_format ='yyyy-mm-dd HH24:MI:SS';
set lines 200 pages 50000 
col object_name format a30
col owner format a20
select owner, object_name, object_type,status,last_ddl_time from dba_objects where status = 'INVALID' order by last_ddl_time desc;

@?/rdbms/admin/utlrp
select count(*) from dba_objects where status = 'INVALID';