DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, December 31, 2013

Minimal downtime rolling database upgrade to 12c Release 1

Minimal downtime rolling database upgrade to 12c Release 1

This note describes the procedure used to perform a rolling database upgrade from 11.2.0.3 to Oracle 12c Release 1 using a Data Guard physical standby database and transient logical standby database.

The average time to perform a database upgrade would be in the region of one to two hours and for many organizations even that amount of downtime would in some cases not be possible or would lead to a significant financial implication because of a database outage.

The rolling upgrade procedure greatly reduces the downtime for an upgrade from hours to a few minutes which is the duration in which a database switchover can be performed.

At a high level these are the steps involved in the  rolling upgrade process

1) Start with the 11.2.0.3 Data Guard physical standby database and convert that to a transient logical standby database. Users are still connected to  primary database.

2) Upgrade the transient logical standby database to 12.1.0.1
The transient logical standby process uses SQL Apply to take redo generated by a database running a lower Oracle version (11.2.0.3) , and apply the redo to a standby database running on a higher Oracle version (12.1.0.1)

3) Perform a switchover so that the original primary database now becomes a physical standby database

4) Use Redo Apply to synchronize (and upgrade) the original primary database with the new upgraded primary database

5) Perform another switchover to revert the databases to their former roles.


Oracle provides a Bourne shell script (physru) which really does automate a lot of the rolling upgrade process and is available for download from MOS via the note – Database Rolling Upgrade Shell Script (Doc ID 949322.1).

The DBA only has a few tasks to perform as the physru script handles the rolling upgrade process.

i. Upgrade the standby database using DBUA or manual upgrade.
ii. Start the upgraded standby database in the new Oracle 12c home
iii. Start the original primary database in the new Oracle 12c home

The physru script accepts six parameters as shown below.

$./physru

We need to provide the SYSDBA password  and can run this from either the primary database server or from the node hosting the standby database as long as SQL*Net connectivity is available from that node to both the databases involved in the rolling upgrade.

We need to execute the script 3 times and let us see what happens at each stage.

1. First execution

Create control file backups for both the primary and the target physical standby database

Creates Guaranteed Restore Points (GRP) on both the primary database and the physical standby database that can be used to flashback to beginning of the process or any other  intermediate steps along the way.

Converts a physical standby into a transient logical standby database.

2. Second execution

Use SQL apply to synchronize the transient logical standby database and make it current with the primary

Performs a switchover to the upgraded 12c transient logical standby and  the standby database becomes the primary

Performs a flashback on the original primary database to the initial Guaranteed Restore Point  and converts the original primary into a physical standby

3. Third execution

Starts Redo Apply on the new physical standby database (the original primary database) to apply all redo that has been generated during the rolling upgrade process, including any SQL statements that have been executed on the transient logical standby as part of the upgrade.

When synchronized, the script offers the option of performing a final switchover to return the databases to their original roles of primary and standby, but now on the new 12c database software version.

Removes all Guaranteed Restore Points

Prerequisites

A) Data Guard primary and physical standby database environment exists

B) Flashback database is enabled on both Primary and Standby database

C) If Data Guard Broker is managing the configuration, then it has to be disabled for the duration of the upgrade process (by setting the initialization parameter DG_BROKER_START=FALSE)

D) Ensure that the log transport (initialization parameter LOG_ARCHIVE_DEST_n) is correctly configured to perform a switchover from the primary database to the target physical standby database and back.

E) Static entries defined in the listener.ora file on both Primary as well as Standby database nodes for the databases directly involved the rolling upgrade process.

F) Oracle 12.1.0.1.0 software has already been installed on both the primary as well as standby database servers.

Friday, September 20, 2013

Oracle E-Business Suite Extensions for Oracle Endeca

          Overview:

           Oracle Endeca Information Discovery (EID) is a sophisticated data discovery platform that searches for and filters data. It is based on a patented hybrid search-analytical database, and gives IT a centralized platform to rapidly deploy interactive analytic applications, and keep pace with changing business requirements while maintaining information governance. Oracle Endeca Information Discovery provides enhanced display capabilities using metrics, graphs and charts. It provides further drill down capabilities using tag cloud, data attributes, and granular dimensions.
Oracle Endeca Information Discovery provides the following features:
  • Range Filters
  • Guided Navigation
  • Customized charts to represent data graphically
  • Collection set of results tables
  • Tag Clouds
  • Dashboard metrics

    Oracle Channel Revenue Management (ChRM) Extensions for Oracle Endeca

    Oracle Channel Revenue Management Extensions for Oracle Endeca integrations enable you to search and filter claim and budget related information. You can use the Claims Dashboard to view and analyze Open and Closed Claims using metrics, charts, graphs, and tables. You can use the Budget Dashboard to track funds and related promotional activity transactions to forecast and execute various promotional activities to get maximum return on investments.

    Oracle Cost Management Extensions for Oracle Endeca

    Oracle Cost Management Extensions for Oracle Endeca enables you to quickly search and filter transaction distribution details across organizations and related entities. Use the Period Health Check page to search for uncosted, unprocessed and material with errors. You can search for Inventory, Manufacturing, Receiving, Shipping, Landed Cost Management, and Enterprise Asset Management transactions that are preventing or delaying period close. Use the Transaction Accounting Register page to search and display all material, manufacturing, receiving, and write-off transaction accounting information. You can also use the guided navigation to identify hidden trends.

    Oracle Cost Management Extensions for Oracle Endeca: Landed Cost Management

    Oracle Cost Management Extensions for Oracle Endeca: Landed Cost Management enables you to search, filter and analyze the landed cost data to extract valuable insights for supply and product margin management. You can use the Monitor Daily Operations page to view details of pending activities for Landed Cost shipments. The Landed Cost Analysis page provides an in-depth understanding of the landed cost distribution across inventories, and includes supply information, item information, and purchase order information.
    Note: Landed Cost Management Endeca pages are included in the Cost Management Extensions for Oracle Endeca product.

    Oracle Depot Repair Extensions for Oracle Endeca

    Oracle Depot Repair Extensions for Oracle Endeca enables you to search and filter the most critical service data in a single location and drill down to get more detailed information enabling you to take action using that information. Service managers and service technicians access the Repair Orders Dashboard and claims managers and claims administrators access the Supplier Warranty Claims Dashboard to view, search, and filter the information.

    Oracle Discrete Manufacturing Extensions for Oracle Endeca Work Order Search

    Oracle Discrete Manufacturing Extensions for Oracle Endeca Work Order Search enables you to search and view dashboard metrics related to job status, job delays, component and resource shortage, quantity of scraps, and rejects. You can use the Oracle Endeca Discrete Work Order Search page to view and analyze data using Key Performance Indicators (KPIs), performance evaluation metrics, charts, graphs, and tables.

    Oracle Enterprise Asset Management Extensions for Oracle Endeca

    Oracle Enterprise Asset Management Extensions for Oracle Endeca enables you to search and filter asset, work requests, work orders, and item information. Using the eAM Endeca dashboard pages, you can review and analyze data using key Performance Indicators (KPIs), performance evaluation metrics, charts, graphs, and tables.

    Oracle Field Service Extensions for Oracle Endeca

    Oracle Field Service Extensions for Oracle Endeca enables you to search and filter Field Service Execution data and Spares Supply Chain data. You can use the Oracle Endeca Field Service Tasks Summary and Field Service Parts Summary pages to view and analyze data using Key Performance Indicators (KPIs), performance evaluation metrics, charts, graphs, and tables.

    Oracle Installed Base Extensions for Oracle Endeca

    You can use Oracle Installed Base Extensions for Oracle Endeca to search and filter details such as Up-Sell and Cross-Sell opportunities, search by geographical location of Installed Base, and also search for customer products such as Model/Configuration. Oracle Installed Base Extensions displays the most critical data in a single location and drill down to get more detailed information enabling you to take action using that information.

    Oracle Inventory Management Extensions for Oracle Endeca Item Master Search

    Oracle Inventory Management Extensions for Oracle Endeca Item Master Search enables you to search, filter, and compare item information. You can use the Oracle Endeca Item Search page to view and analyze data using Key Performance Indicators (KPIs), performance evaluation metrics, charts, graphs, and tables.

    Oracle iProcurement Extensions for Oracle Endeca

    Oracle iProcurement Extensions for Oracle Endeca integration enables you to search for and filter catalogs in a particular store, or browse through the hierarchy of categories within the catalog to locate items and services. You can use the Oracle Endeca Shopping Stores Home page to view and analyze recent requisitions and notifications graphically and in a tabular format.

    Oracle iRecruitment Extensions for Oracle Endeca Job Search

    Oracle iRecruitment Extensions for Oracle Endeca Vacancy Search integration enables you to search and filter available job information. You can use the Oracle Endeca iRecruitment Home page to search for jobs using Links, Advanced Search, and Keywords Search. You can use the Oracle Endeca Available Job page to filter and search for jobs based on various attributes. This page contains two tabs - Job List and Compare.

    Oracle Learning Management Extensions for Oracle Endeca

    Oracle Learning Management Extensions for Oracle Endeca enables you to add filters to browse and search for new training in the Course Catalog.

    Oracle Order Management Extensions for Oracle Endeca

    Oracle Order Management Extensions for Oracle Endeca enables you to search and filter Open Orders, Closed Orders, and Returns information on a wide range of search criteria. You can view and analyze order information and order fulfillment delays using Key Performance Indicators (KPIs), performance evaluation metrics, charts, graphs, and tables.

    Oracle Outsourced Manufacturing Extensions for Oracle Endeca

    Oracle Outsourced Manufacturing Extension for Oracle Endeca enables you to search and filter open subcontract orders, delayed subcontract orders, impacted customer orders, open replenishments, delayed replenishments, on-hand and in-transit inventory values, component consumption adjustments, quality rejects, rejected value, uninvoiced assemblies, and components. You can use the Endeca Search Page to quickly assess the state of the outsourcing process by identifying where component shortages occur, identify assemblies and Manufacturing Partners (MP) with the most frequent scraps and production rejects and reasons, and identify the top exception causes and corrective actions to take. Buyers can monitor payments due to the manufacturing partners and take appropriate actions to reduce payment backlog to promote positive supplier relationships. Buyers can also monitor on-hand inventory of manufacturing partners and take appropriate actions for better inventory and working capital management.

    Oracle Process Manufacturing Extensions for Oracle Endeca

    Oracle Process Manufacturing (OPM) Extensions for Oracle Endeca enable you to quickly search and filter batch status, batch delays, ingredient and resource shortage, quantity of scraps, quality sample results, and batch yield information. You can use the Batch Search page to view Endeca content for Oracle Process Manufacturing batch processes. You can search for process batches, materials, steps, activities, resources, sales orders, and view production metrics, charts, and a results table.

    Oracle Projects Extensions for Oracle Endeca

    Oracle Projects Extensions for Oracle Endeca integration enables you to search and filter project information. You can use the Oracle Endeca Projects Search and Overview page to search for Projects in a particular Operating Unit, and create new projects. You can also use the Oracle Endeca Workplan Overview page to view details of the Workplan. Each page enables you to view and analyze the data using metrics, charts, graphs, and tables.

    Oracle Receivables Extensions for Oracle Endeca

    Oracle Receivables Extensions for Oracle Endeca enables internal and external users to quickly identify potential problem areas within their area of responsibility, navigate to specific transactions requiring attention, and take necessary actions. The Endeca Overview page for internal users addresses the requirements of users who are either responsible for identifying potential problem areas in respect to outstanding receivables, or need easy access to individual customer’s receivables transactions to take specific action. The Endeca Overview page for external users addresses the requirements of customers’ personnel who are either responsible for reconciling their account receivables balance with the deploying company or vendor, or need easy access to individual transactions to take specific action. You can use the Disputes page to analyze disputes flow within the organization, identify specific dispute reasons that might require closer attention and further investigation, and identify those customers who are abusing the disputes mechanism to delay payments.

    Oracle Warehouse Management Extensions for Oracle Endeca

    Oracle Warehouse Management Extensions for Oracle Endeca enables you to search and filter outbound, inventory, and labor (productivity) information from the warehouse. The Plan Fulfillment page lets you plan the fulfillment batches and waves. The Track Fulfillment page lets you track the fulfillment process and react in real-time to changes impacting the outbound activities. The Space Utilization page lets you track the fragmentation in the warehouse and suggest material movement to manage fragmentation. The Labor Utilization page lets you plan and manage labor in the warehouse. Using these Warehouse Management Endeca pages, you can review and analyze data using key Performance Indicators (KPIs), performance evaluation metrics, charts, graphs, and tables. In addition to the search and filter capabilities, each WMS page contains action-based analytics where you can diagnose an issue and take specific actions.

Thursday, September 12, 2013

Run SQL Tuning Advisor from SQL Prompt

Get SQL ID
===========
select distinct hash_value, sql_id, sql_text
from v$sql
where sql_text like '%pktable_cat%'
and sql_text not like 'select distinct hash_value, sql_id%';


Create Tuning Task:
===================
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '0j3dqg3a8aj8c',
scope => 'COMPREHENSIVE',
time_limit => 3600, 
task_name => 'my_sql_tuning_task_1',
description => 'Tune query using sqlid');
end;
/

Execute Tuning Task:
===================
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
end;
/


SET LONG 100000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
set pagesize 100

--***************************
--TO GET SUMMARY INFORMATION
--***************************

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL;

--***************************
--TO GET DETAILED INFORMATION
--***************************
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_1','TEXT','ALL','ALL') FROM DUAL;


-- Accept the profile <<< If you get one...
DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'my_sql_tuning_task_1',
name => 'my_sql_profile');
END;
/


###

If necessary you can drop the tuning task.

begin
DBMS_SQLTUNE.DROP_TUNING_TASK('my_sql_tuning_task_1');
end;
/

If necessary (the profile does not help),
you can drop the profile.

BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE ('MY_SQL_TUNING_TASK_1');
END;
/

Tuesday, September 3, 2013

ORA-01741: illegal zero-length identifier

Scenario:
========

While adding multiple schemas to a single workspace, we get the following error in Apex version 4.1 which is identified as a Bug in the version and it is fixed in version 4.2 

Error Faced :
===========

 "ORA-01741: illegal zero-length identifier"

Solution:
========

Run the following in the command prompt to add the schema

SYNTAX:
======

EXEC apex_instance_admin.add_schema( 'WORKSPACE_NAME', 'SCHEMA_TO_BE_ADDED' );

or 

conn as sysdba and Run the following 

BEGIN
    APEX_INSTANCE_ADMIN.ADD_SCHEMA( 'WORKSPACE_NAME', 'SCHEMA_TO_BE_ADDED');
END;
/


Example :
========

EXEC apex_instance_admin.add_schema( 'TEST', 'APEX_USER' );

PL/SQL procedure successfully completed.


Bug Reference :
=============

http://www.inside-oracle-apex.com/

Tuesday, August 20, 2013

Error "ORA-0000: normal, successful completion" during Gather Schema Statistics for AP Schema



ISSUE:
---------

When attempting to run the concurrent program Gather Schema Statistics for AP schema the following error occurs.


ERROR
----------
ORA-0000: normal, successful completion
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
In GATHER_SCHEMA_STATS , schema_name= AP percent= 10 degree = 8 internal_flag= NOBACKUP
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=AP.JE_FR_DAS_010***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=AP.JE_FR_DAS_010_NEW***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=AP.JG_ZZ_SYS_FORMATS_ALL_B***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+


SOLUTION
---------------
Perform the below action plan and re-run the Gather Stats and verify the results

Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them.
Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

– identify duplicate rows
select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1;

– Use above results on the following SQL to delete duplicates
delete from FND_HISTOGRAM_COLS where table_name = ‘&TABLE_NAME’ and column_name = ‘&COLUMN_NAME’ and rownum=1;

– Use following SQL to delete obsoleted rows
delete from FND_HISTOGRAM_COLS
where (table_name, column_name) in
(
select hc.table_name, hc.column_name
from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
where hc.table_name =’&TABLE_NAME’
and hc.table_name= tc.table_name (+)
and hc.column_name = tc.column_name (+)
and tc.column_name is null
);

Monday, August 19, 2013

Adpreclone.pl fails with RC-50007: Could not create cloning stage area

Issue :
adpreclone.pl fails with RC-50007: Could not create cloning stage area.

Can see the entire log below.

# Calling StageAppsTechStack...
Evaluated internal cloning stage: /application_r12/apps/apps_st/comn/clone
Creating Staging Area
Creating bin directory
UNZIP used: UnZip 5.50 of 17 February 2002, by Info-ZIP.  Maintained by C. Spieler.  Send
Copying /application_r12/apps/apps_st/appl/ad/12.0.0/admin/template/adxmlctx.tmp to /application_r12/apps/apps_st/comn/clone//context/apps/adxmlctx.tmp
Creating html directory
Creating jre directory
Directory /application_r12/apps/apps_st/comn/clone/jre already exists.
Attempting to delete: /application_r12/apps/apps_st/comn/clone/jre
Failed to copy JRE from /application_r12/apps/tech_st/10.1.3/appsutil/jdk/jre to /application_r12/apps/apps_st/comn/clone/jre
Cloning Stage Area already exists, updating existing area.
Creating jlib directory
Copied /application_r12/apps/tech_st/10.1.3/lib/xmlparserv2.jar to /application_r12/apps/apps_st/comn/clone/jlib/xmlparserv2.jar
Copied /application_r12/apps/apps_st/comn/java/classes/oracle/apps/ad to /application_r12/apps/apps_st/comn/clone/jlib/java/oracle/apps
Copied /application_r12/apps/tech_st/10.1.3/jdbc/lib/ojdbc14.jar to /application_r12/apps/apps_st/comn/clone/jlib/ojdbc14.jar
Copying file from /application_r12/apps/apps_st/appl/ad/12.0.0/bin to /application_r12/apps/apps_st/comn/clone/bin
Copy OUI from /application_r12/apps/tech_st/10.1.3/oui/jlib to /application_r12/apps/apps_st/comn/clone//jlib/oui/
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/xmlparserv2.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/srvm.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/OraInstaller.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/OraInstallerNet.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/share.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/oneclick.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/ewt3.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/ewt3-nls.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/jewt4.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/jewt4-nls.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/help4.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/help4-nls.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/InstHelp.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/InstImages.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/oracle_ice.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/swingaccess.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/ewt3-swingaccess.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/classes12.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/prereq.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/ouica.jar
copying /application_r12/apps/apps_st/comn/clone//jlib/oui//jlib/deinstall.jar
Copy OUI from /application_r12/apps/tech_st/10.1.3/oui/lib to /application_r12/apps/apps_st/comn/clone//oui/
Could not generate new Clone stage base area


StackTrace -
java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at oracle.apps.ad.clone.util.CloneProcessor.run(CloneProcessor.java:67)
        at java.lang.Thread.run(Thread.java:662)

Caused by: java.lang.Exception: RC-50007: Could not create cloning stage area at /application_r12/apps/apps_st/comn/clone/


Cause :
Improper permission of jre under $IAS_ORACLE_HOME

Solution:
1. change the permission to 755 for the folder $IAS_ORACLE_HOME/appsutil/jdk/jre
Take a backup of $IAS_ORACLE_HOME/appsutil/jdk/jre
change the permission
chmod -R 755 $IAS_ORACLE_HOME/appsutil/jdk/jre
2. Take a backup and remove the clone folder from $COMMON_TOP

3. Run adpreclone.pl and retest the issue.


Sunday, August 18, 2013

Points to look for while writing SQL Code

Avoiding the Pitfalls of Database Programming - this is a very good material which every SQL developer should read. I'm sure when each developer reads this they can connect with all the mistakes they have done till now while coding

http://edu.ercess.co.in/ebooks/SQL/SQL-Antipatterns-Avoiding-the-Pitfalls-of-Database-Programming.pdf




Friday, August 16, 2013

Paper Presentation in AIOUG - Sangam'13




  




We , From Doyensys , are going to present paper in AIOUG - Sangam'13.

Below are the presenter and paper to be presented.

Presenter: Somu Chockalingam (Director , Doyensys).
Session :36309
Session Title: New Features of Oracle EBS Rel 12.2 from DB and Techstack perspective


Presenter: Beena Thomas (Principal Consultant , Doyensys).
Session :36362
Session Title: New Features of PL/SQL in Oracle 11g

 

Thursday, August 1, 2013

ORA-10873: file 1 needs to be either taken out of backup mode or media recovered

Scenario :

Database went down due to power fluctuation and during that time hot backup was running.So once we started the database we face the following issue.

Error:


ORA-10873: file 1 needs to be either taken out of backup mode or media recovered
ORA-01110: data file 1: '/system/PROD/system01.dbf'

Cause:

Database in backup mode while it went down.

Solution:

Step 1 : Sqlplus  ‘/as sysdba’
Step 2 : startup mount
Step 3 : Check backup file using below command.
            select * from V$BACKUP
Step 4 : The following command can be used to take all of the data files out of hot backup mode:
            ALTER DATABASE END BACKUP;
Step 5 : Alter database open

Tuesday, July 30, 2013

Oracle OS Watcher Black Box Analyzer OSWBBA

OSWatcher Black Box Analyzer (oswbba) is a graphing and analysis utility which comes bundled with oswbb v4.0.0 and higher.  oswbba allows the user to graphically display data collected, generate reports containing these graphs and provides a built in analyzer to analyze the data and provide details on any performance problems it detects. The ability to graph and analyze this information relieves the user of manually inspecting all the files.

NOTE: oswbba replaces the utility OSWg. This was done to eliminate the confusion caused by having multiple tools in support named OSWatcher. oswbba is only supported for data collected by oswbb and no other tool.

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. oswbb 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

Overview

oswbba is written in java and requires as a minimum java version 1.4.2 or higher. oswbba can run on any Unix X Windows or PC Windows platform. An X Windows environment is required because oswbba uses Oracle Chartbuilder which requires it.

oswbba parses all the oswbb vmstat, iostat ,netstat and top utility log files contained in an archive directory.  Once the data is parsed, the user is presented with a command line menu which has options for both displaying graphs, creating binary gif files of these graphs, generating an html report containing all the graphs with narrative on what to look for, and new in this release, the ability to self-analyze the files oswbb creates.

Back to Contents

oswbba is certified to run on the following platforms:
  • AIX
  • Solaris
  • HP-UX
  • Linux
  • Windows XP
Installing oswbba

oswbba requires no installation. It comes shipped as a standalone java jar file with oswbb v4.0.0 and higher.

Starting oswbba

Before starting the oswbba utility you must have java version 1.4.2 or higher installed on your system. Java can be downloaded for free from http://www.java.com. Also if you are running Oracle you have already a version of java installed. To verify you have the correct version of java installed on your system issue the following command...

$ java -version

This should be version 1.4.2 or higher. If not, contact your system administrator to get the current version of java installed. Alternatively, you can use the version of java that comes shipped with Oracle. Here is an example of using the version of java that comes shipped with the database...(depending upon the version of the database, the jre may be in a different location)

//Note: the location of the jre is in $ORACLE_HOME/jre/1.4.2/bin
//now put this location in the UNIX PATH:
$ export PATH=$ORACLE_HOME/jre/1.4.2/bin:$PATH

Once the correct version of java has been verified, you can start oswbba. oswbba requires an input directory to run. To specify the input directory you must use the -i option. The input directory is the fully qualified path name of an archive directory containing oswbb logs. The archive directory must be the same directory structure as the archive directory for oswbb. It must contain the respective subdirectories--oswvmstat, oswiostat, oswps, oswtop, oswnetstat, etc. It is very important to note the program requires an archive directory not an individual log directory.

$java -jar oswbba.jar -i /u02/home/osw/archive
Starting OSWbba V4.0.0
OSWatcher Black Box Analyzer Written by Oracle Center of Expertise
Copyright (c)  2012 by Oracle Corporation
Parsing Data. Please Wait...
Enter 1 to Display CPU Process Queue Graphs
Enter 2 to Display CPU Utilization Graphs
Enter 3 to Display CPU Other Graphs
Enter 4 to Display Memory Graphs
Enter 5 to Display Disk IO Graphs
Enter 6 to Generate All CPU Gif Files
Enter 7 to Generate All Memory Gif Files
Enter 8 to Generate All Disk Gif Files
Enter L to Specify Alternate Location of Gif Directory
Enter T to Specify Different Time Scale
Enter D to Return to Default Time scale
Enter R to Remove Currently Displayed Graphs
Enter P to Generate A Profile
Enter A to Analyze Data
Enter Q to Quit Program
Please Select an Option:

oswbba parses all the archive files in memory prior to generating graphs or performing an analysis. If you have a large amount of files to parse you may need to allocate more memory in the java heap. If you experience any error messages regarding out of memory such as java.lang.OutOfMemoryError, you may have to increase the size of the java heap. To increase the size of the java heap use the -Xmx flag.

$java -jar -Xmx512M oswbba.jar -i /u02/home/oswbb/archive
Starting oswbba V4.0.0
OSWatcher Black Box Analyzer Written by Oracle Center of Expertise
Copyright (c)  2012 by Oracle Corporation
Parsing Data. Please Wait...

Stopping oswbba

To stop the oswbba utility select option "Q" from the menu.

Using oswbba

oswbba has multiple user interface options. If oswbba is started as above, the user will be able to choose from a list of options on a menu. In all cases oswbba must be supplied the archive directory location with the -i flag. Not all of the Disk I/O options will be available to all users. These options are only available for solaris, linux and aix, and only if iostat is collected with the extended disk statistics option. These options will also not be available if oswbba encountered any parsing issues while parsing iostat archive files.

Previously if a user wanted to analyze the archive all files in the archive would be analyzed automatically. In release 6.0, the analysis can be limited to a subset of files. This can be controlled by using the command line options -START and -STOP. These options are available if you run from the command line or if you want to use the menu. In either case, you must use the -START and -STOP option.

To use this option you must specify the starting filename in the oswvmstat directory. oswbba only analyzes file in chronological order. To specify the starting filename locate a filename in the oswvmstat directory and specify it using the -START option. The -STOP option specifies the last file in the oswvmstat directory that you want to analyze. If you specify either parameter without specifying the other parameter the following default values will be assumed:
If -START is specified and no -STOP is specified then the analysis continues through the last file in the oswvmstat directory.

If -STOP is specified and no -START is specified then the analysis starts at the oldest file in the oswvmstat directory and continues through the file specified in the -STOP option.
oswbba uses the times associated with these oswvmstat files for the other directories so that all iostat,top and netstat files will be analyzed during this time frame.

Example. In this example your archive contains 17 hours worth of data in the oswvmstat directory. You want to only analyze files starting at.13.01.09.1300 through 13.01.09.2000

$ls
coesrv40.us.oracle.com_vmstat_13.01.09.1300.dat
coesrv40.us.oracle.com_vmstat_13.01.09.1400.dat
coesrv40.us.oracle.com_vmstat_13.01.09.1500.dat
coesrv40.us.oracle.com_vmstat_13.01.09.1600.dat
coesrv40.us.oracle.com_vmstat_13.01.09.1700.dat
coesrv40.us.oracle.com_vmstat_13.01.09.1800.dat
coesrv40.us.oracle.com_vmstat_13.01.09.1900.dat
coesrv40.us.oracle.com_vmstat_13.01.09.2000.dat
coesrv40.us.oracle.com_vmstat_13.01.09.2100.dat
coesrv40.us.oracle.com_vmstat_13.01.09.2200.dat
coesrv40.us.oracle.com_vmstat_13.01.09.2300.dat
coesrv40.us.oracle.com_vmstat_13.01.10.0000.dat
coesrv40.us.oracle.com_vmstat_13.01.10.0100.dat
coesrv40.us.oracle.com_vmstat_13.01.10.0200.dat
coesrv40.us.oracle.com_vmstat_13.01.10.0300.dat
coesrv40.us.oracle.com_vmstat_13.01.10.0400.dat
coesrv40.us.oracle.com_vmstat_13.01.10.0500.dat

java -jar oswbba.jar -i archive -START coesrv40.us.oracle.com_vmstat_13.01.09.1300.dat -STOP coesrv40.us.oracle.com_vmstat_13.01.09.2000.dat


oswbba can be run with a menu driven user interface. This option gives the user the most flexibility and allows graphs to be displayed real-time. To start oswbba with the menu option issue the following on the command line...

java -jar oswbba.jar -i

After starting oswbba, a set of options will display.  Enter an option value and hit return.

OPTIONS
The following options are supported:
1..3
These options display graphs of specific CPU components of vmstat. Option 1 displays the process run, wait and block queues. Option 2 displays CPU utilization graphs for system, user and idle. Option 3 displays graphs for context switches and interrupts.
4
This option displays memory graphs for free memory and available swap..
5
This option uses the extended disk statistics option of iostat to display a list of all devices for solaris, aix and linux platforms only. The device name along with the average service time of each device is then listed. The user then selects one of the devices out of the list of devices. Graphs are available for reads/second, writes/second, service time and percent busy.
6..8
These options generate images of the graph to a file without displaying them to the screen. All graphs for that specific category, CPU, memory or IO will be generated and placed in the directory where oswbba is located. To override this directory and name an alternative location to place these files use option L discussed below.
L
User specified location of an existing directory to place any gif files generated by oswbba. This overrides the oswbba automatic convention for placing all gif files in the /gif directory. This directory must pre-exist!
A
This option analyzes the files in the archive and produces a report
P
Generates an html profile.
T
By default OSWbba parses all the OSWbb log files contained in the input directory and the default graphs are based on the entire time span of all the logs. By default, OSWbb keeps the last 48 hours of logs in the archive. This means the default graph will graph all  48 hours of of data. This option allows the user to specify a different subset of times within the entire collection . An example would be only to graph a 2 hour period out of the entire 48 hour collection.
D
This option resets the graphing timescale back to the time encompassing the entire log collection.
R
This option removes all previously displayed graphs from the screen.
Q
Exits the program.

Using oswbba: Command Line Option

All graphing, profile and analysis options are available to be passed into oswbba from the command line. Only the -i option is required. Use the table below to add additional options. Please note if the -F option is used all other options are ignored as the -F option requires input to be read in from a text file.

Please note all options are case sensitive.

java -jar oswbba.jar -i -P -L -6 -7 -8 -B

OPTIONS
The following options are supported:
-i
Required. This is the input archive directory location.
-6
Same as option 6 from the menu. Will generate all cpu gif files.
-7
Same as option 7 from the menu. Will generate all memory gif files.
-8
Same as option 8 from the menu. Will generate all disk gif files.
-L
User specified location of an existing directory to place any gif files generated by OSWbba. This overrides the OSWbba automatic convention for placing all gif files in the /gif directory. This directory must pre-exist!
-A
Same as option A above. This option analyzes the files in the archive and produces a report
-START
Used with the analysis option to specify the first file located in the oswvmstat directory to analyze.
-STOP
Used with the analysis option to specify the last file located in the oswvmstat directory to analyze.
-P
User specified name of the html profile generated by OSWbba. This overrides the oswbba automatic naming convention for html profiles. All profiles whether user specified named or auto generated named will be located in the /profile directory.
-B
Same as option T from the menu. The start time will allow the user to select a start time from within the archive of files to graph/profile. This overrides the default start time which is the earliest time entry in the archive directory.  The format of the start time is  Mon DD HH:MM:SS YYYY. (Example :Jul 25 11:58:01 2007). An end time is required if selecting this option.
-E
Same as option T from the menu. The end time will allow the user to select an end time from within the archive of files to graph/profile. This overrides the default end time which is the latest time entry in the archive directory.  The format of the end time is  Mon DD HH:MM:SS YYYY. (Example :Jul 25 11:58:01 2007). A start time is required if selecting this option.
-F
Filename of a text file containing a list of options. The user can script as many options as desired by using this option. If the -F option is entered on the command line all other options are ignored and commands are only allowed through the file interface. See a sample file named oswbba_input.txt in the src directory.

Using oswbba: Input File Option

All graphing, profile and analysis options are available to be read from a user specified text file. The format of the file is individual lines containing command line options (see above). An example file exists in the src directory (oswbba_input.txt). To specify the input file option the user must specify the -F option when running oswbba.

java -jar oswbba.jar -i -F

Example:

java -jar oswbba.jar -i archive -F src/oswbba_input.txt

Sample Profile

Generating an htmp profile is a useful feature of oswbba. This html document contains all the graphs along with some text directing the user on what to look for.

To add database metrics consider running LTOM. To see what an LTOM profile looks like click here.