Oracle Database: AWR Reports Summary - AWR Hatırlatması

Today, we  tried to run AWR reports in our system but we encountered an error so we decided that we could have a brief summary on AWR Reports.

First we will start with ORA-20200 error. We tried to run awrrpt.sql found under $ORACLE_HOME/rdbms/admin and we encountered with that error since we had no AWR reports although it had been months since se installed our Oracle Applications Database(EBS).

Then we come up with the suggested solution below however our conditions were not that critical(Ref-1). We also didnt have those snapshots but we were able to create these snapshots. In that solution, it was suggested that we recreate the awr.

How to drop and recreate AWR?
Note:782974.1 How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository 

Our solution was however is to just recreate our snapshot to solve our ORA-20200.



References:

1- AWR Error - ORA 20200
 http://fayoubi.blogspot.com.tr/2013/09/ora-13516-awr-operation-failed-or-ora.html

How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository ? (Doc ID 782974.1) 2- Awr generation How to Generate an AWR Report and Create Baselines (Doc ID 748642.1)

3- Systemwide Tuning using STATSPACK Reports (Doc ID 228913.1)

4-How to Use AWR Reports to Diagnose Database Performance Issues (Doc ID 1359094.1)

5-Awr reports Reading

http://oracle-base.com/articles/10g/automatic-workload-repository-10g.php

Oracle Database: Compiling Invalid Objects Continued - Invalid Objelerin Compile Edilmesi Devam

During our health check, ıt appeared to us that we were working with invalid objects and we thought that there must be some script Oracle has done for it. We found out that we were right .

Oracle has 2 scripts for it. "Utlprp.sql" and "Utlrp.sql" which are found under $ORACLE_HOME/rdbms/admin.

It asks for just one input value. Its answer must be given accordingly.
0 - The level of parallelism is derived based on the CPU_COUNT parameter.
1 - The recompilation is run serially, one object at a time.
N - The recompilation is run in parallel with "N" number of threads.

Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.
However sometimes we need to some manuel compilation.

For example the following states a compilation of package.

 BEGIN  
  FOR cur_rec IN (SELECT owner,  
              object_name,  
              object_type,  
              DECODE(object_type, 'PACKAGE', 1,  
                        'PACKAGE BODY', 2, 2) AS recompile_order  
          FROM  dba_objects  
          WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')  
          AND  status != 'VALID'  
          ORDER BY 4)  
  LOOP  
   BEGIN  
    IF cur_rec.object_type = 'PACKAGE' THEN  
     EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||   
       ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';  
    ElSE  
     EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||   
       '"."' || cur_rec.object_name || '" COMPILE BODY';  
    END IF;  
   EXCEPTION  
    WHEN OTHERS THEN  
     DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||   
                ' : ' || cur_rec.object_name);  
   END;  
  END LOOP;  
 END;  
 / 

References:

1- Compile Invalid Objects

http://oracle-base.com/articles/misc/recompiling-invalid-schema-objects.php

Oracle Database: Error ORA-01655 - Unable to Extend - Tablespace Sorunu

While we were checking our alert.log for possible error, we noticed that there was an error that was saying that our tablespace was having a lack of space:


----------
Errors in file /u01/install/PROD/11.2.0/admin/TEST_DBMACHINE/diag/rdbms/test/TEST/trace/TEST_smon_1166.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01655: unable to extend cluster SYS.SMON_SCN_TO_TIME_AUX by 128 in tablespace SYSAUX
Wed Mar 11 11:36:24 2015
----------

Solution:

We added a datafile to the system in order to add more space to the tablespace:

The following is the sql:

-----------------
ALTER TABLESPACE SYSAUX
  ADD DATAFILE '/u01/install/PROD/data/sysaux03.dbf'
  SIZE 1024M
  AUTOEXTEND ON
  NEXT 50M
  MAXSIZE UNLIMITED;
---------------

References:

1- Tablespace Clauses:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3002.htm#SQLRF01002

2- Unable To Extend Error 

https://community.oracle.com/thread/950421

Oracle Database: Error ORA-48165 In the Sqlnet.log File

 ORA-48165 this error was noticed while we were checking for sqlnet.ora log file.  The error message was like the following.


--------
NL-08014: Failed to initialize Diagnosability framework, falling back to old network tracing/logging

 NL-08015: Client(OCI) side initialization of Diagnosability framework failed
  ORA-48165: user missing read, write, or exec permission on specified ADR Base directory []
User inputted base directory is invalid [48187] [/u01/E-BIZ/db/tech_st/11.2.0/admin/VISION_slc01ozg]

--------

This message was in fact indicating us that we had misconfiguration in our sqlnet.ora file. Therefore we had to change it.

Solution:

Go to $ORACLE_HOME/network/admin and then change the ADR_BASE to the correct value.

The correct value has to be value of the diagnostic_dest:

SQL> show parameter diag

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

diagnostic_dest                      string      /u01/install/PROD/11.2.0/admin

                                                 /TEST_DBMACHINE

References:

1-http://onlineappsdba.blogspot.com.tr/2007/11/oracle-11g-alert-log-file.html

2- Oracle Documentation About Network Connectivity Issues:

https://docs.oracle.com/cd/A57673_01/DOC/net/doc/NWTR23/apa.htm

Oracle Database: Reading X$DBGALERTEXT Table - Reading Alert Log Via SQL - SQL ile Alert.log'ları okumak

With Oracle Database 11g, we can read the alerts which are critical for us from a table. In the prior editions, we had to parse the table so that we could extract the information that we need but we can access it directly.

How to read from x$dbagalertext?

In order to read from x$dbagalertext we need to be a user who is sys or sys privileged. However we could also access to the x$dbagalertext by creating a  view so that we can query it.(Ref-1)

References:

1- Reading from x$dbagalertext from a use who is not "sys"  privileged.

https://community.oracle.com/thread/2356037

2-  Alert Log Reading

http://www.oradba.ch/2013/07/query-alert-log-from-sqlplus/

3-  Reading alert log

http://blog.tanelpoder.com/2009/03/21/oracle-11g-reading-alert-log-via-sql/

Oracle E-Business Suite: Creating Custom Application Top in R12.2 - Custom Application Top Yaratılması - Custom Top Nasıl Yaratılır?

We will be using doc id 1577707.1 to create a custom application top in R12.2

Procedure:

1-We start by downloading the patch "3636980 "Support Diagnostics (IZU) patch for AD Splice" from My Oracle Support. We will be using its contents to setup the application.

2- Under 3636980/izu/admin there are 3 files that needs to be used. 

izuprod.txt  izuterr.txt  newprods.txt

3- We then rename the following files accordingly, such as prod.txt and 
terr.txt

mv izuprod.txt xxstprod.txt
mv izuterr.txt xxstterr.txt


4- Change the details in the newprods.txt so that all references of "izu" to and all references of "IZU" to (i.e. keep the case sensitivity). 

5- We then create a  tablespace. In our example our tablespace name will be TSXXST

 CREATE TABLESPACE TSXXST DATAFILE   
  '/u01/install/PROD/data/tsxxst01.dbf' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED  
 LOGGING  
 DEFAULT   
 COMPRESS BASIC   
 ONLINE  
 EXTENT MANAGEMENT LOCAL AUTOALLOCATE  
 BLOCKSIZE 8K  
 SEGMENT SPACE MANAGEMENT AUTO  
 FLASHBACK ON;  
   

6- After we create the tablespace, we continue with the user creation. We add the required privileges so that this use can work.

CREATE USER XXST  
  IDENTIFIED BY xxst  
  DEFAULT TABLESPACE TSXXST  
  TEMPORARY TABLESPACE TEMP  
  PROFILE DEFAULT  
  ACCOUNT UNLOCK;  
   
 -- 1 Role for XXST   
  GRANT CONNECT TO XXST;  
  ALTER USER XXST DEFAULT ROLE ALL;  
   
  
 -- 13 System Privileges for XXST   
  GRANT ALTER ANY OUTLINE TO XXST;  
  GRANT ALTER SESSION TO XXST;  
  GRANT ANALYZE ANY TO XXST;  
  GRANT CREATE ANY OUTLINE TO XXST;  
  GRANT CREATE CLUSTER TO XXST;  
  GRANT CREATE MATERIALIZED VIEW TO XXST;  
  GRANT CREATE SEQUENCE TO XXST;  
  GRANT CREATE SESSION TO XXST;  
  GRANT CREATE TABLE TO XXST;  
  GRANT CREATE TRIGGER TO XXST;  
  GRANT CREATE TYPE TO XXST;  
  GRANT DROP ANY OUTLINE TO XXST;  
  GRANT QUERY REWRITE TO XXST;  
  -- 2 Tablespace Quotas for XXST   
  ALTER USER XXST QUOTA UNLIMITED ON APPS_TS_TX_DATA;  
  ALTER USER XXST QUOTA UNLIMITED ON TSXXST;  

7- Then xxstprod.txt will be changed so that  all references of "izu" to and all references of "IZU" to (i.e. keep the case sensitivity).   We also change product id references of 278 to our own number which we decide according the following SQL. Choose one id accordingly, "for example 50001", and replace 278 with it.

 select decode(count ,0, 'Selected number is Available', 'Selected number already in use') Status, &&enter_custom_applID selected_number  
 from  
 (  
 select count(*) as count from  
 (  
 select 'x' from fnd_oracle_userid  
 where oracle_id= &&enter_custom_applID  
 union  
 select 'x' from fnd_application  
 where application_id= &&enter_custom_applID  
 )  
 );  

8- We then edit the last file xxstterr.txt so that all references are for xxst which our new custom top.

9- We then copy the files we have changed to the $APPL_TOP/admin folder.

 cp newprods.txt /u01/install/APPS/fs1/EBSapps/appl/admin/.
 cp xxstprod.txt /u01/install/APPS/fs1/EBSapps/appl/admin/.
 cp xxstterr.txt /u01/install/APPS/fs1/EBSapps/appl/admin/.

10- Then we are finally able to run adsplice under the $APPL_TOP folder so that  we run autoconfig and create our custom application.

>cd $APPL_TOP/admin
>adsplice

11- Remember to check apply the following SQLs so that we might be sure that we have created our application top.

select * from fnd_application where application_short_name = 'XXST';
select * from fnd_product_installations where APPLICATION_ID = 50101;
select * from dba_users where username = 'XXST';

Note: The thing that we have done for a system that is with one application machine and one database machine. If you have more than one application servers that you will have to create application top in your other non shared application.


References:

1-Creating a Custom Application in Oracle E-Business Suite Release 12.2 (Doc ID 1577707.1)

2-http://ermanarslan.blogspot.com.tr/2014/05/ebs-122-add-custom-top.html

Oracle E-Business Suite: General Information - Concepts - Architecture - Mimari - Genel Bilgi ve Konseptler

General Information:

E-Business Suite is formed of 3 tiers These are database, application and client. Database tier holds the database services while Application tier holds the Fusion Middleware and Concurrent Processing part. Client has the java virutal machine which enables it to access and communicate with Application tier. Application tier acts like the middleman between Database and client.

In Fusion Middleware, Forms,Oacore,Oafm servers. The $WEBAPPS_TOP shows us the location of these servers.

Oracle Homes in EBS 12.2:

There are two homes under the run files system. For example under the run file system we have our Oracle home as /u01/install/APPS/fs1/EBSapps/10.1.2 other than that /u01/install/APPS/fs1/FMW_HOME is the other Oracle home for Fusion Middleware. Third home that is seen is $APPL_TOP which has the modules. It is beside the 10.1.2  under /u01/install/APPS/fs1/EBSapps/appl

Web Services:

Web services control the HTTP requests that are coming to the application.

Concurrent Processing Server:

As well as HTML applications and forms , there are also data reporting and data updating programs that need to be run randomly or in scheduled way. They run in the background. The requests that are needed to run are written to a table, then it is read by the concurrent managers and run accordingly.

Concurrent Managers spawn their own processes which is called as target processes.Every target process can run only one concurrent request at a time.

The frequency or the amount of work that is to be done can be arranged by the work shifts

Specialized Concurrent Managers:

Specailized concurrent managers dont exactly run concurrent requests but manages other stuff.

ICM(Internal Concurrent Manager) controls all the other concurrent managers. It administers the startup and shutdown of managers. ICM is important because it has lots of configuration settings that should not be modified but can be modified manually. ICM must be run before any other concurrent manager in order to start other concurrent managers. It creates log files (std.mgr and wnnn.mgr) in $APPLCSF/$APPLLOG.

These are sleep time (number of seconds the ICM waits between checking for new concurrent requests), PMON (process monitor) cycle time (number of sleep cycles the ICM waits between checking for failed workers), and queue size (duration between checks for number of active workers, measured in PMON cycles).

Standard Manager is the default concurrent manager that comes with the installation. However special concurrent managers can be created in order to form special environments for programs.

Concurrent Manager Architecture and Related Tables:

FNDLIBR is the Oracle application object library program which has the concurrent programs that can be called by a concurrent manager.

FND_CONCURRENT_REQUESTS :Details of user requests, including status, start date, and completion date

FND_CONCURRENT_PROGRAMS: Details of concurrent programs, including execution method, whether the program is constrained, and whether it must be run alone.

FND_CONCURRENT_PROCESSES: Cross-references between concurrent requests and queues, and a history of concurrent manager processes

FND_CONCURRENT_QUEUES: Information about each of the concurrent manager queues

How to see the Concurrent Manager Processes?

In order to see the processes we need to be searching for processes with the name of FNDLIBR

ps -ef | grep FNDLIBR


FNDLIBR runs for every concurrent manger and Internal Concurrent Manager.

applmgr  17100 17095  0 Feb19 ?        00:09:37 FNDLIBR               -- ICM                                                                                                                                            
applmgr  18326 18252  0 Feb19 ?        00:00:11 FNDLIBR                                                                                                                                                                                                                                          
applmgr  18329 18252  0 Feb19 ?        00:00:11 FNDLIBR                                                                                                                                                                                                                                          
applmgr  18330 18252  0 Feb19 ?        00:00:29 FNDLIBR                                                                                                                                                                                                                                          
applmgr  18331 18252  0 Feb19 ?        00:00:27 FNDLIBR                                                                                                                                                                                                                                          
applmgr  18332 18252  0 Feb19 ?        00:00:29 FNDLIBR                                                                                                                                                                                                                                          
applmgr  18333 18252  0 Feb19 ?        00:00:14 FNDLIBR                                                                                                      
In fact, ICM starts up a Service Manager that instructs the concurrent manager to startup. Therefore if we look at the mother process of all, we can search for

ps -ef  | grep FNDSM

For example FNDSM process is 18252

Managing Concurrent Processing:

To start ICM, we can start adcmctl.sh which is found under $ADMIN_SCRIPT_HOME to find the status of ICM or to either startup or shutdown the ICM in order to run it again so that other concurrent managers could start.

adcmctl.sh status| shutdown | startup


The EBS Technology Layers:

We have a number of utiliy that runs in between the application and the modules.

• Oracle Applications DBA (AD)
• Oracle Application Object Library (FND)
• Oracle Applications Utilities (AU)
• Oracle Common Modules (AK)
• Oracle Workflow (WF)
• Oracle Web Applications Desktop Integrator (BNE)
• Oracle Alert (ALR)
• Oracle Application Framework (FWK)
• Oracle BI Publisher (XDO)

Use application diagnostics to capture a problem that might exist.


Oracle Alert (ALR):

Oracle Alert allows us to email system notification when an exception occurs. The reason could be any event or database related.

File Structures

The APPL Directory:

Appl_top which is under the EBSapps/appl has the main context files, products and modules.

Admin directory under the common_top has the concurrent manager logs and output directiories. However the correct location is under non editioned file system which is
/u01/install/APPS/fs_ne/inst/SID_HOSTNAME/logs/appl/conc/log/

File Systems of EBS:

There are 2 file systems in EBS 12.2 in additon to non

References:

1-

Architecture and Common Applications
Oracle E-Business Suite Concepts	E22949-09