HOW TO: Analyzing and Interpreting AWR Report

September 30th, 2015 por Jorge Acevedo Flores
gravatar
Compartir este post:
  • Google Reader
  • Google Plus
  • Identi.ca
  • Meneame
  • PDF
  • Print
  • Twitter
  • Facebook
  • Email
  • LinkedIn

Hello

Today I wanted to share with you my personal approach for how to get into the details on AWR Report and where to see and where should be our attention. Keep in mind just two main focuses : the Application and the Database instance

1) Application: Most of the times you should go directly to review how was the SQL statements Performance. That is MANDATORY and the result of your analysis will be the classic TOP 10 SQL with the worst performance. That should be called : Oracle Application’s Performance Tuning .

2) Instance: read once, twice or three times the AWR looking for issues. What kind of issues or metrics ?

For example here I have  a Conclusion and Workaround for one database instance issue regarding performance :

AWR Report – How to get it through EM

October 31st, 2013 por Jorge Acevedo Flores
gravatar
Compartir este post:
  • Google Reader
  • Google Plus
  • Identi.ca
  • Meneame
  • PDF
  • Print
  • Twitter
  • Facebook
  • Email
  • LinkedIn

We need a database user/pwd

….

But we can generate it using SQLPLUS as well :

AWR Snapshot Reports
Oracle provides reports that you can run to analyze the data in the AWR. These reports are much like the statspack reports prior to Oracle Database 10g. There are two reports: awrrpt.sql and awrrpti.sql, which are available in the directory $ORACLE_HOME/ rdbms/ admin.
The output of these reports is essentially the same, except that awrrpti.sql script allows you to define a specific instance to report on. The reports are much like the statspack reports of old, in that you define a beginning and ending snapshot ID, and the output filename of the report. Additionally, you can opt to produce the report in either text format or HTML format.

HOWTO: Checking options installed within the Database/Oracle Home

October 31st, 2013 por Jorge Acevedo Flores
gravatar
Compartir este post:
  • Google Reader
  • Google Plus
  • Identi.ca
  • Meneame
  • PDF
  • Print
  • Twitter
  • Facebook
  • Email
  • LinkedIn

1)From Oracle Universal Installer: In OUI select installed products and click on list and expand, you will see a list of options installed in database.

You have to use OUI to check whether Oracle Secure Enterprise Search is installed or not.

TIP: Start Oracle Universal Installer from your system prompt (UNIX) with the following command:

./runInstaller

2)From v$option:

SQL> set pages 100

SQL> select * from v$option;

PARAMETER                                                        VALUE

—————————————————————- —–

Partitioning                                                     TRUE

Objects                                                          TRUE

Real Application Clusters                                        FALSE

Advanced replication                                             TRUE

Bit-mapped indexes                                               TRUE

Connection multiplexing                                          TRUE

Connection pooling                                               TRUE

Database queuing                                                 TRUE

Incremental backup and recovery                                  TRUE

Instead-of triggers                                              TRUE

Parallel backup and recovery                                     TRUE

Forms in socket mode

October 31st, 2013 por Jorge Acevedo Flores
gravatar
Compartir este post:
  • Google Reader
  • Google Plus
  • Identi.ca
  • Meneame
  • PDF
  • Print
  • Twitter
  • Facebook
  • Email
  • LinkedIn

Algun cliente alguna vez debe haber sufrido de un extraño issue con  los forms y se preguntara si vale la pena cambiarlos a ‘socket’ mode, y él se estará preguntando (con toda razón):

A.      When the forms are changed to socket mode:

1.      Will this affect the EBS application in anyway (will it be slow than now)

2.      Do we need to perform any change on our or the workstation’s side ?

ASSESSMENT AND SOLUTION

Please look at the Note in Metalink 310976.1. It seems that the Java Console in JInitiator tells you at the beginning which mode you are using.

Verifying the Forms Client Connection to the Forms Server

HOWTO : Getting User Responsability on EBS 11i using a Database user (BOLINF or APPS)

October 31st, 2013 por Jorge Acevedo Flores
gravatar
Compartir este post:
  • Google Reader
  • Google Plus
  • Identi.ca
  • Meneame
  • PDF
  • Print
  • Twitter
  • Facebook
  • Email
  • LinkedIn

Find below the SQL statement to generate an extract with active users together with their active responsibilities.

The statement extracts all the active assignments of responsibilities by using the FND_USER_RESP_GROUPS table which combines DIRECT and INDIRECT responsibilities.

If you need only the DIRECT responsibilities than you also have the option to use the seeded view FND_USER_RESP_GROUPS_DIRECT. If you need only the INDIRECT responsibilities (added by roles – like Application Diagnostics and others) than use the seeded view FND_USER_RESP_GROUPS_INDIRECT.

As always adjust the SQL to cover your needs.

SELECT
fuser.USER_NAME USER_NAME
, per.FULL_NAME FULL_NAME
, per.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, frt.RESPONSIBILITY_NAME RESPONSIBILITY
FROM
FND_USER fuser
, PER_PEOPLE_F per
, FND_USER_RESP_GROUPS furg
, FND_RESPONSIBILITY_TL frt
WHERE
fuser.EMPLOYEE_ID = per.PERSON_ID
AND fuser.USER_ID = furg.USER_ID
AND (to_char(fuser.END_DATE) is null
OR fuser.END_DATE > sysdate)
AND frt.RESPONSIBILITY_ID = furg.RESPONSIBILITY_ID
AND (to_char(furg.END_DATE) is null
OR furg.END_DATE > sysdate)
AND frt.LANGUAGE = ‘US’
ORDER BY
, fuser.USER_NAME;

Ramakrishnan & Gehrke Sailors script for Oracle database

October 19th, 2013 por Felipe Manriquez
gravatar
Compartir este post:
  • Google Reader
  • Google Plus
  • Identi.ca
  • Meneame
  • PDF
  • Print
  • Twitter
  • Facebook
  • Email
  • LinkedIn

The chapter 5 of the  book “Database Management Systems” (Ramakrishnan, Raghu & Johannes Gehrke.) introduces the Sailors data model, a set of three tables named Sailors, Boats and Reserves

In this article, the SQL script that implements the creation of these relational structures is developed and adapted  for Oracle Database. This script is based on the script worked by Hjalmtyr Hafsteinsson for PostgreSQL database.

The script does the following tasks

Cleans Sailors Schema and tablespaces of previous script executions

Creates Sailors_data and Sailors_index  tablespaces

Creates Sailors Schema

Grants roles and privileges to Sailors user

Connects to Sailors user

Creates tables, constraints,  in the Sailors Schema

Populates tables

Adds new columns  and new constraints (that are not part of the original data model)

Cambiar logfile del Listener sin tener que detenerlo

September 8th, 2013 por Juan Carlos Alzafiedes
gravatar
Compartir este post:
  • Google Reader
  • Google Plus
  • Identi.ca
  • Meneame
  • PDF
  • Print
  • Twitter
  • Facebook
  • Email
  • LinkedIn

Hay veces que el log del listener crece sin parar y puede llegar a pesar varios GB . Una solucion para esto es detener el listener, truncar el archivo y volver a levantarlo, pero hay veces que las base de datos son 24/7 y esta solucion no sirve .

La solucion que voy a postear es una de las muchas que encontre en este link : http://msutic.blogspot.com/2009/06/truncating-rotating-flushing.html

La solución que implemente fue en un Oracle 10.2.0.5 y consistia en crear un nuevo log para el listener por cada mes del año con el siguiente formato :listener_yyyymm.log , asi cada mes del año iba a tener su propio log.

Estos son los pasos :

Paquete LT_CTX_PKG inválido (9.2.0.8)

August 22nd, 2013 por Juan Carlos Alzafiedes
gravatar
Compartir este post:
  • Google Reader
  • Google Plus
  • Identi.ca
  • Meneame
  • PDF
  • Print
  • Twitter
  • Facebook
  • Email
  • LinkedIn

El otro día revisando los objetos inválidos del SYS de una BD Oracle 9.2.0.8 me encontré que el paquete LT_CTX_PKG estaba inválido por que había un código no identificado dentro del BODY de este.

Buscando bien por internet me encontré con la solución y lo que hay que hacer por si a alguien se le presenta este problema es ejecutar el wrapped package : owmctxb.plb
Un wrapped package es un paquete codificado y no puede ser leído de forma normal como los *.sql.
Este paquete se encuentra en la ruta : $ORACLE_HOME/rdbms/admin
Entonces ejecutamos este paquete como sys  :
sqlplus “/as sysdba” @$ORACLE_HOME/rdbms/admin/owmctxb.plb

Con esto se arreglaría el problema de invalidez del LT_CTX_PKG .
Ademas esto también arreglaría el problema de invalidez de otros paquetes como el LTADM y el LTUTL también pertenecientes al SYS .

Flashback database Oracle 11gR2 after resize datafile

May 5th, 2013 por Felipe Manriquez
gravatar
Compartir este post:
  • Google Reader
  • Google Plus
  • Identi.ca
  • Meneame
  • PDF
  • Print
  • Twitter
  • Facebook
  • Email
  • LinkedIn

It is very important to read the documentation before to use the Oracle facility of Flashback Database. Furthermore, you have to take a full rman database backup to be protected against any contingency (bugs or restrictions) of flashback database features.

If you create restore points and after that you do a datafile resizing, then you have to create again a new restore point, because you cannot flashback database across a datafile resize.  For example:

SQL> CREATE RESTORE POINT before_test0 GUARANTEE FLASHBACK DATABASE;
SQL> ALTER DATABASE DATAFILE 6 RESIZE 2048G;
SQL> CREATE RESTORE POINT before_test1 GUARANTEE FLASHBACK DATABASE;

For more information, please read the oracle documentation.

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm

Oracle RAC One Node (nuevo desde Oracle 11gR2 EE)

May 5th, 2013 por Felipe Manriquez
gravatar
Compartir este post:
  • Google Reader
  • Google Plus
  • Identi.ca
  • Meneame
  • PDF
  • Print
  • Twitter
  • Facebook
  • Email
  • LinkedIn
Oracle Real Application Clusters (RAC) One  node es una nueva opción para  Oracle  Database 11g Release 2 Enterprise Edition. Esta opción proporciona alta disponibilidad mejorada  para las bases de datos de una sola instancia, protegiendo tanto del tiempo de inactividad planificado y no planificado.
Oracle RAC proporciona un nodo:
  • Sistema de alta disponibilidad “always on”  para servicios de base de datos de una sola instancia.
  • Mejor consolidación de servidores de bases de datos
  • Virtualización mejorada
  • Menor costo de desarrollo y plataforma de pruebas para full RAC