Fix : RMAN restore fails with RMAN-06023 error

Fix : RMAN RESTORE FAILS WITH RMAN-06023 ALTHOUGH BACKUPS ARE AVAILABLE 

Issue Description:

I am upgrading one of my production database from 10.2.0.4 to 11.2.0.4. With one iteration I have upgraded successfully. When I try one more iteration then I faced below issues. Restore failed with "RMAN-06023" error. But restored the controlfile sucessfully and In the RMAN catalog I can see that there are available database backups.

Error reported:

Starting restore at 29-OCT-14

released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/29/2014 14:53:16
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 44 found to restore
RMAN-06023: no backup or copy of datafile 43 found to restore
....
....
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

RMAN>


Cause:

1) There may be issue with incarnation. When I verified, I found one database ID with current like below:

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PROD  4100727707       ORPHAN  1          12-MAR-08
2       2       PROD  4100727707       ORPHAN  602821     22-FEB-10
3       3       PROD  4100727707       ORPHAN  10224810000 04-AUG-11
4       4       PROD  4100727707       ORPHAN  10273513355 09-AUG-11
5       5       PROD  4100727707       ORPHAN  16112640652 02-MAR-12
6       6       PROD  4100727707       ORPHAN  49639514687 13-FEB-14
7       7       PROD  4100727707       CURRENT 93574835300 28-SEP-14

RMAN>

So, It it best practice do reset incarnation to 1 like below:

RMAN> reset database to incarnation 1;

database reset to incarnation 1
RMAN>

2) Another problem here is that there are some files in the Flash Recovery Area that belong to different incarnation than the available backups CURRENT incarnation. If we start a RESTORE database with a BACKUP controlfile and Flash Recovery Area is defined, RMAN execute and implicit crosscheck and
catalog of all the objects in the Flash Recovery Area.

RMAN will catalog any objects in the Flash Recovery Area that will not be registered in the controlfile and if any of this files belongs to an incarnation different from CURRENT incarnation in the controlfile then changes controlfile CURRENT incarnation to the one found in the file that is being cataloged.

This prevents database from restoring backups that belong to old CURRENT incarnation.
RMAN considers backup availble for being restored if the backup incarnation and CURRENT incarnation in controlfile are the same.

We can check that there are not backups belonging to CURRENT incarnation with command:

RMAN> LIST BACKUP RECOVERABLE;

This command only will show CURRENT incarnation available backups

Fix / Solution:

1) Reset database incarnation as described above.
2) Temporarily disable use of the flash recovery area for the duration of the restore and recovery commands.

Now restart the restore activity freshly. When I followed the above steps, my database has been restored successfully.


Note: Script used for restore:

-- executed again
RUN
{
allocate channel ch1 type disk;
SET NEWNAME FOR DATAFILE 1 TO '/u01/ORADATA/PROD/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/u01/ORADATA/PROD/undotbs01.dbf';
....
SET NEWNAME FOR DATAFILE 44 TO '/u01/ORADATA/PROD/hr02.dbf';
 RESTORE DATABASE;
 SWITCH DATAFILE ALL;
}

Thanks.
Cheers!!!

Performance Tuning using SQL Access Advisor(SAA) using Oracle11g

Performance Tuning & SQL Access Advisor:

When Oracle Database 10g released, it offers lots of features for performance tuning. One example is SQL Tuning Advisor, which provides recommendations on query tuning. "Advisors" — which help you decide the best course of action.

One scenario: An Index will definitely help a query but the query is executed only once. So even though the query will benefit from it, the cost of creating the index will outweigh that benefit. To analyze the scenario in that manner, you would need to know how often the query is accessed and why?

Another advisor—SQL Access Advisor—performs this type of analysis. In addition to analyzing indexes, materialized views, and so on as it does in Oracle Database 10g, in Oracle Database 11g SQL Access Advisor also analyzes tables and queries to identify possible partitioning strategies—a great help when designing optimal schema. In Oracle Database 11g SQL Access Advisor can now provide recommendations with respect to the entire workload, including considering the cost of creation and maintaining access structures.

Find recommendations on improving schema design by partitioning, indexing, and creating materialized views based on your current workload and type of usage - not using any data.

About SQL Access Advisor:

This is an enterprise feature in Oracle database to get recommendations for performance improvements. Materialized views, partitions, and indexes are essential when tuning a database to achieve optimum performance for complex, data-intensive queries.

SQL Access Advisor helps you achieve your performance goals by recommending the proper set of materialized views, materialized view logs, partitions, and indexes for a given workload. Understanding and using these structures is essential when optimizing SQL as they can result in significant performance improvements in data retrieval. The advantages, however, do not come without a cost. Creation and maintenance of these objects can be time consuming, and space requirements can be significant. In particular, partitioning of an unpartitioned base table is a complex operation that must be planned carefully.

SQL Access Advisor recommend partitioning on an existing unpartitioned base table to improve performance. Furthermore, it may recommend new indexes and materialized views that are themselves partitioned. While creating new partitioned indexes and materialized view is no different from the unpartitioned case, partitioning existing base tables should be executed with care. This is especially true when indexes, views, constraints, or triggers are defined on the table.

Overview of Using SQL Access Advisor:

An easy way to use SQL Access Advisor is to invoke its wizard, which is available in Enterprise Manager from the Advisor Central page. If you prefer to use SQL Access Advisor through the DBMS_ADVISOR package, then this section describes the basic components and the sequence in which you must call the procedures.

This section describes the four steps in generating a set of recommendations:

• Create a task
• Define the workload
• Generate the recommendations
• View and implement the recommendations

All the above tasks are easy to handle though Oracle Enterprise Manager rather than Manual.

Here are the steps to complete the task:

Using Oracle OEM (Enterprise Manager) it is easy to set, manage and execute SQL Access advisor. Here are the steps to set task for sql access advisor.

OEM Home Page → Advisor Central → SQL Advisor → Advisor Central → SQL Access Advisor → Choose Verify use of access structures (indexes, materialized views, partitioning, etc) only options → Select Continue → Choose Current and Recent SQL Activity option to choose from current work load → Next → Choose Indexes / Materialized views / Partitioning with scope ( limited) → Next → Schedule your task details → Next → Submit → See the Advisory Central all scheduled windows/ tasks → Choose your task to view recommendation details

Fig_1: Home screen when you logged on to the database ( sys user)

                                                                    Fig_1(Home Page)

Fig_2 : When Advisor central selected from Home screen of after log on.

                                                                   Fig_2 ( Advisor Central Window)

Fig_3 : When selected SQL Access Advisor from Advisory window:

                                                                Fig_3 (SQL Advisor's Window)

Fig_4 : After Selecting SQL Access Advisor from SQL Access advisor window:

                                                                 Fig_4 ( SQL Access Advisor )

Fig_5 : Starting of SQL Access advisor screen:

                                                                 Fig_5 ( First phase of SQL access advisor  )
Note : 
In this screen, you can schedule a SQL Access Advisor session and specify its options. The advisor must gather some SQL statements to work with. The simplest option is to get them from the shared pool, via Current and Recent SQL Activity. Choosing this option allows you to get all SQL statements cached in the shared pool for analysis.
However, in some cases you may not want all the statements in the shared pool; just a specific set of them. To do so, you need to create a "SQL Tuning Set" on a different screen and then refer to the set name here, in this screen.
Alternatively, perhaps you want to run a synthetic workload based on a theoretical scenario you anticipate to occur. These types of SQL statements will not be present in the shared pool, as they have not executed yet. Rather, you need to create these statements and store them in a special table. In the third option ( Create a Hypothetical Workload...), you need to supply the name of this table along with the schema name.
For the purpose of this article, assume you want to take the SQLs from the shared pool. So, choose the first option as shown in the screen, which is default.

Fig_6 : Second phase of SQL Accees advisor window:

                                                              Fig_6 (Access recommendation window )

Fig : 7 : Third window to set task details:

                            Fig_7 ( Window to set SQL Acccess advisor task details including filter options )


Fig:8 :  Fourth / Submit window to schedule SQL Access Advisor task:

                                                               Fig_8 ( Submit Window)


Fig : 9 : SQL Advisor main window (with status created)

                                              Fig_9 (  SQL Advisor main window to view task details)


Fig:10 : SQL Advisor main window (with status running)

                             Fig_10 (SQL Advisor main window (with status running)

Fig:11: from v$session when Task is executing:

Fig:12 : Task selected see the status

                                       Fig_12 ( SQL advisor status window after run )

This screen says it all! SQL Access Advisor analyzed the SQL statement and found some solutions that can improve query performance tenfold. To see what specific recommendations were made, click on the Recommendations tab.

This screen has a lot of good information, at a slightly higher level. For instance, for the statement with ID = 1 there are two recommended actions, under column Actions. The following column, Action Types, shows the types of actions, indicated by colored squares. From the icon guide just below it, you can see that the actions were on indexes and partitions. Together they improve performance by several orders of magnitude.

To see exactly what SQL statement can be improved, click on the ID, which brings up the screen below. Of course, this analysis had only one statement so only one showed up here. If you had more than one, you would see them all.

The screen offers a very clear description of the solutions. It has two recommendations: to create the table as partitioned and to use an index. Then it finds that the index is already present so it advises to retain the index. If you click on PARTITION TABLE under the column Action, you will see the actual script Oracle will generate to make this a partitioned table. Before you click, however, fill in the tablespace name in the text box. This allows SQL Access Advisor to use that tablespace while building this script:

Rem 
Rem Repartitioning table "SCOTT"."TRANS"
Rem 
SET SERVEROUTPUT ON

1. SET ECHO ON
   
   Rem 
   Rem Creating new partitioned table
   Rem 
   CREATE TABLE "SCOTT"."TRANS1" 
   (    "TRANS_ID" NUMBER, 
       "RES_ID" NUMBER, 
       "TRANS_DATE" DATE, 
       "AMT" NUMBER, 
       "STORE_ID" NUMBER(3,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
   TABLESPACE "USERS" 
   PARTITION BY RANGE ("RES_ID") INTERVAL( 3000) ( PARTITION VALUES LESS THAN (3000)
   );
   
   begin
   dbms_stats.gather_table_stats('"SCOTT"', '"TRANS1"', NULL, dbms_stats.auto_sample_size);
   end;
   /
   
   Rem 
   Rem Copying constraints to new partitioned table
   Rem 
   ALTER TABLE "SCOTT"."TRANS1" MODIFY ("TRANS_ID" NOT NULL ENABLE);
   
   Rem 
   Rem Copying referential constraints to new partitioned table
   Rem 
   ALTER TABLE "SCOTT"."TRANS1" ADD CONSTRAINT "FK_TRANS_011" FOREIGN KEY ("RES_ID")
        REFERENCES "SCOTT"."RES" ("RES_ID") ENABLE;
   
   Rem 
   Rem Populating new partitioned table with data from original table
   Rem 
   INSERT /*+ APPEND */ INTO "SCOTT"."TRANS1"
   SELECT * FROM "SCOTT"."TRANS";
   COMMIT;
   
   Rem 
   Rem Renaming tables to give new partitioned table the original table name
   Rem 
   ALTER TABLE "SCOTT"."TRANS" RENAME TO "TRANS11";
   ALTER TABLE "SCOTT"."TRANS1" RENAME TO "TRANS";

Use Cases:

SQL Access Advisor is useful for tuning schemas, not just queries. As a best practice, you can use this strategy in developing an effective SQL tuning plan:

1. Search for high-cost SQL statements, or better yet, evaluate the entire workload.
2. Put suspect statements into a SQL Tuning Set.
3. Analyze them using both SQL Tuning Advisor and SQL Access Advisor.
4. Get the results of the analysis; note the recommendations.
5. Plug the recommendations into SQL Performance Analyzer (see this installment).
6. Examine the before-and-after changes in SQL Performance Analyzer and arrive at the best solution.
7. Repeat the tasks until you get the optimal schema design.
8. When you have the best schema design, you may want to lock the plan using SQL Plan Management baselines (described in this installment).

User level grants for SQL access advisor:

Assume APP is the user to run SQL access advisor.


SQL> grant all on dbms_advisor to app;
SQL> grant advisor to app;
SQL> grant ADMINISTER SQL TUNING SET to app;


SQL Access Advisor Examples-using DBMS_ADVISOR

SQL Access Advisor Examples are as follows:

Example1: Steps to Implement Advisor recommendations

Step1: Create a sample table:

SQL> create table test_saa as select * from all_objects;

Step2: gather table statistics:

SQL> exec dbms_stats.gather_table_stats  (APP, 'TEST_SAA');

Step3: Create Advisor Task using dbms_advisor.quick_tune:

SQL>
declare
    v_sql varchar2(200) := 'select object_name from test_adv where object_id = 1523';
    v_tuning_task varchar2(200) := 'tune_task_advisor_gourang';
    v_tune_result clob;
begin
    dbms_advisor.quick_tune ( dbms_advisor.sqlaccess_advisor , v_tuning_task, v_sql );
    dbms_output.put_line ('Quick Tune Completed');
end;
/

Step4:  Sql’s to check the status:

SQL> select * from user_advisor_templates;
SQL> select * from user_advisor_journal;
SQL> select * from user_advisor_recommendations
where task_name = 'tune_task_advisor_gouranga';
SQL> select * from user_advisor_actions
where task_name = 'tune_task_advisor_gouranga';
SQL> SELECT description, advisor_name, created, status,
recommendation_count, source, how_created
FROM user_advisor_tasks WHERE task_name = 'tune_task_advisor_gouranga';

Step5: To see the advisor recommendations execute below SQL

SELECT DBMS_ADVISOR.get_task_script
('tune_task_advisor_gouranga') AS script
FROM dual;

Output from above sql statement:
--------------------------------------------------

Rem  SQL Access Advisor: Version 10.2.0.4.0 - Production
Rem 
Rem  Username:        APP
Rem  Task:            tune_task_advisor_gouranga
Rem  Execution date:  27/10/2014 19:09
Rem 

CREATE MATERIALIZED VIEW LOG ON
    "APP"."TEST_ASS"
    WITH ROWID ;

CREATE MATERIALIZED VIEW "APP"."MV$$_88110000"
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT "APP"."TEST_ASS"."OBJECT_NAME" M1
FROM APP.TEST_ASS
WHERE (APP.TEST_ASS.OBJECT_ID  = 2599);

begin
  dbms_stats.gather_table_stats('"APP"','"MV$$_88110000"',NULL,dbms_stats.auto_sample_size);
end;
/

---------------------------------------------------------------------------------------
I hope this document will help to understand SAA ( SQL Access advisor). Post comments.

Fix : OC4J Configuration issue while start OEM in Oracle

Unable to start Oracle Enterprise Manager (OEM) using emctl command:
Found : OC4J Configuration issue while start

-- This document also useful to recreate OEM repository in Cluster database (more than one node RAC)

Fix / Solution:
There are couple of reason for the above issue.

Fix-1:

Crosscheck your hostname configuration details. Don't put any complexity for host name configuration. Chek where your /etc/hosts filename is like beolw or not ( with order):

$ cat /etc/hsosts

127.0.0.1       loopback localhost      # loopback (lo0) name/address
10.99.1.1       prod-db1

OR

$ cat /etc/hsosts

127.0.0.1       loopback localhost      # loopback (lo0) name/address
10.99.1.1       prod-db1    mycompany.com

I found some recommendations like below from oracle.com comunity:

They really create a directory whose name is composed of hostname (or IP) and the SID!
So it breaks whenever you change the hostname. Furthermore this strongly depends on the entries in /etc/hosts which typicall look like so: 10.193.63.76 strongbow.odi.ch strongbow.
Oracle will use the first entry after the IP address (of probably the first ethernet device) as the name.
So whenever IP or hostname changes you need to alter this line.


If you have the FQDN / Hostname problem change the order in your /etc/hosts file

If oracle use the Hostname change from FQDN to Hostname after the IP address
10.193.63.76 strongbow.odi.ch strongbow => 10.193.63.76 strongbow strongbow.odi.ch strongbow

If oracle use the FQDN change from Hostname to FQDN after the IP address
10.193.63.76 strongbow strongbow.odi.ch => 10.193.63.76 strongbow.odi.ch strongbow

Fix:2 : Recreate the OEM repository

Caution: Do it only in fully OFF peak hour / down time only.

Pre-requisites:
1) You should have following user passwords:
SYS, DBSNMP, SYSMAN
2) You schould have ORACLE_HOME address
3) Export ORACLE_SID and ORACLE_UNQNAME
4) If you want to configure mail alerts in future, collect SMTP server IP address and one mail address atleast.

When you use "emctl status dbconsole" command, you may have the below issue.

$ emctl status dbconsole

OC4J Configuration issue. /home/oracle/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_prod-db1_FINPROD not found.

-- Recreate the OEM respository. 

When you use "emca -config dbcontrol db -repos recreate" command following output will come.

$ emca -config dbcontrol db -repos recreate

STARTED EMCA at Oct 24, 2014 2:23:59 PM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:
Database SID: FINPROD
Listener port number: 1521
Listener ORACLE_HOME [ /home/oracle/app/oracle/product/11.2.0/dbhome_1 ]: /home/oracle/app/oracle/product/11.2.0/dbhome_1
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /home/oracle/app/oracle/product/11.2.0/dbhome_1

Local hostname ................ prod-db1
Listener ORACLE_HOME ................ /home/oracle/app/oracle/product/11.2.0/dbhome_1
Listener port number ................ 1521
Database SID ................ FINPROD
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
----------------------------------------------------------------------
WARNING : While repository is dropped the database will be put in quiesce mode.
----------------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Oct 24, 2014 2:24:45 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /home/oracle/app/oracle/cfgtoollogs/emca/FINPROD/emca_2014_10_24_14_23_58.log.
Oct 24, 2014 2:24:46 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Oct 24, 2014 2:24:47 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Oct 24, 2014 2:24:47 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Oct 24, 2014 2:41:45 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Oct 24, 2014 2:41:51 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Oct 24, 2014 2:43:52 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Oct 24, 2014 2:44:00 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Oct 24, 2014 2:44:25 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Oct 24, 2014 2:44:25 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Oct 24, 2014 2:45:30 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Oct 24, 2014 2:45:30 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://prod-db1:1158/em <<<<<<<<<<<
Oct 24, 2014 2:45:33 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed

in the file: /home/oracle/app/oracle/product/11.2.0/dbhome_1/prod-db1_FINPROD/sysman/config/emkey.ora. Ensure this file is backed up as the

encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Oct 24, 2014 2:45:33 PM

-- Now recheck the status of dbcolsole

$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://prod-db1:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /home/oracle/app/oracle/product/11.2.0/dbhome_1/prod-db1_FINPROD/sysman/log

OEM Directory structure for Oracle 11g and more:

Use below links for details information:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbcontrol.htm


Deleting and Creating Database Control Objects:
=====================================

For an Oracle RAC database ( more than One node ):

Option 1: Deleting Database Control Configuration Files Using EMCA Scripts

$ $ORACLE_HOME/bin/emca -deconfig dbcontrol db –cluster

Option 2: Deleting Database Control Configuration Files and Repository Objects Using An EMCA Script

$ $ORACLE_HOME/bin/emca -deconfig dbcontrol db -repos drop –cluster

Option 3: Deleting A Database Control Repository Using EMCA Scripts

$ $ORACLE_HOME/bin/emca -repos drop -cluster

Option 4: Deleting Database Control Configuration Files Manually

In addition to using EMCA scripts to delete Database Control objects, you can delete objects manually, as follows:

Remove the following directories from your file system:

$ $ORACLE_HOME/hostname_sid
$ $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_hostname_sid

Option 5: Deleting the Database Control Repository Using RepManager

You can delete the Database Control repository using RepManager. This option is not as complete as the other options. You may find that dropping the repository using the command line options is a better solution. Also note that RepManager cannot be used to create a Database Control Repository.

Run the following command:

$ $ORACLE_HOME/sysman/admin/emdrep/bin/RepManager hostname listener_port sid -action drop


Creating Database Control Objects:
===========================

Option 1: Creating the DB Control Configuration Files

$ $ORACLE_HOME/bin/emca -config dbcontrol db -cluster

Option 2: Creating the Database Control Repository Objects and Configuration Files

$ $ORACLE_HOME/bin/emca -config dbcontrol db -repos create –cluster

Option 3: Creating the Database Control Repository Objects and Configuration Files For a Cloned Database Home

$ $ORACLE_HOME/bin/emca -config dbcontrol db -repos recreate -cluster


Recreating Or Reconfiguring Database Control:
====================================

Option 1: Recreating the Database Control Configuration Files Only (Leaving Repository Intact)

$ $ORACLE_HOME/bin/emca -config dbcontrol db -cluster

Option 2: Recreating the Database Control Configuration Files and Repository

$ $ORACLE_HOME/bin/emca -config dbcontrol db -repos recreate -cluster

Note: Export ORACLE_SID and RDBMS home in your connecting/ oprating node.

Below error message during OEM repository creation:

$ emca -config dbcontrol db -repos recreate
.................
.................
Mar 14, 2016 11:49:08 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/UAT/emca_2016_03_14_11_47_54.log.
Mar 14, 2016 11:49:09 AM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
WARNING: ORA-12541: TNS:no listener

Mar 14, 2016 11:49:09 AM oracle.sysman.emcp.EMConfig perform
SEVERE:

Database connection through listener failed. Fix the error and run EM Configuration Assistant again.

Some of the possible reasons may be:

1) Listener port 1251 provided is incorrect. Provide the correct port.
2) Listener is not up. Start the Listener.
3) Database service UAT is not registered with listener. Register the database service.
4) Listener is up on physical host and ORACLE_HOSTNAME environment variable is set to virtual host. Unset ORACLE_HOSTNAME environment variable.
5) Listener is up on virtual host. Set environment variable ORACLE_HOSTNAME=<virtual host>.
6) /etc/hosts does not have correct entry for hostname.

Refer to the log file at /u01/app/oracle/cfgtoollogs/emca/UAT/emca_2016_03_14_11_47_54.log for more details.
Could not complete the configuration. Refer to the log file at /u01/app/oracle/cfgtoollogs/emca/UAT/emca_2016_03_14_11_47_54.log for more details.

$



Solution:

check the following points

1)-Ensure that the Database is up and running by executing the below commands:
<ORACLE_HOME>/bin/sqlplus "/ as sysdba"
SQL> select status from v$instance;

2)-Ensure that the Listener servicing the Database is up and running by executing the below command:
<ORACLE_HOME>/bin/lsnrctl status <listener name>

3)-SYS should be able to establish connection to the Database both via the listener and also the bequeath adaptor.
<ORACLE_HOME>/bin/sqlplus sys as sysdba
or
<ORACLE_HOME>/bin/sqlplus "sys as sysdba"
and
<ORACLE_HOME>/bin/sqlplus sys/<Password>@<TNS Connect descriptor>

If above connection fails take corrective actions to get the connection working from sqlplus.

4)-Password file should be correctly configured for SYS user connection.

Verify the initialization parameter REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE by executing the below command.
SQL> show parameter REMOTE_LOGIN_PASSWORDFILE;

5)-DBSNMP user must exist in the database and should be able to connect to the database.
<ORACLE_HOME>/bin/sqlplus dbsnmp/<password>


Following this, I fixed the issue. Crosscheck all.

This above simple solution fix my issue. You can try in the same way. If you have other issues, please send mail / post in comment box.
Thank you. Cheers !!!

Buffer busy waits & reverse index - Performance improvement tips & tricks

Buffer busy waits & reverse index - Performance improvement tips & tricks
-- What is Buffer busy waits?
-- How will reduce Buffer busy waits?
-- Reduce Buffer busy waits with implementing reverse index ( one factor)

From mail, I received one query "How can I decrease the buffer busy waits?". This query usually comes from new DBAs. Some cases it is most important question for all DBAs. We discuss how we gain performance using reverse index.

Buffer busy waits is normally due to waiting to get a clean buffer when the block buffer is dirty (and requires DBWR process to clean up) Increasing the size of buffer cache will help, until the cache is full of dirty buffers again. It will fix the problem only if the extra size of the cache, enables free buffers to be found long enough for the DBWR to clean up others.

Make sure there isn't any bad SQL running which is doing too much loading of data. i.e full table scans to update a single row etc.

If db block hit ratio is good and still u see high buffer busy waits , u need to dig little further:


1) Catch sqls at the same time when u see high buffer busy waits.

2) Also see in v$latch_children which of the cache buffer chains are highly used then others
Then try to locate the object/s which is/are highly used
thru x$bh.addr and v$latch_children.addr and then getting dba block address.

3) Also u should check if the number of waits on cache buffer chains is high, u may need to increase their number but generally is not reqd.

4) After u get the object , try to tune that object, by finding if storage parameters are ok or u may want to relocate it on to another datafile .

5) Also see any other waits which could relate to DBWR ,db block buffers like write complete waits, or DBWR dirty buffer inspected which may point to some I/O issue or less number of DBWRs .

Find following query outputs and analyse them:

SQL>
select * from v$waitstat where class ='data block';

CLASS                   COUNT       TIME
------------------ ---------- ----------
data block            1991536    2307343

SQL>
select substr(event,1,25)"Event",Total_waits, Total_timeouts,Time_waited,Average_wait from v$system_event where event='buffer busy waits';

Event                   TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
----------------------------------------------------------------------------
buffer busy waits       1745        0             2355597       1349.91

Using reverse key indexes to solve buffer busy wait problems:

Buffer busy wait and related events can cripple performance of concurrent inserts. Bad in a single instance database, far worse in a RAC (think "gc buffer busy"). Often the problem is because of a primary key populated from a sequence. Reversing the index can fix this problem.

Contention for index blocks when inserting grows can cause an application to hang up completely. This is because with a b-tree index on a monotonically increasing key, even though there will never be row lock all the inserted keys are going onto the same block at the edge of the index. A reverse key index will fix this. If you index, for example, 19, 20 and 21 as themselves, all three keys will probably be in the same block of the index. Instead, you would index them as 91, 02, and 12. So consecutive values will not be adjacent in the index: they will be distributed across the whole width of the index. You could do this programmatically, but Oracle provides the reverse key index for exactly this purpose. Here's an example:

create table t1 (c1 number);
create index idx_normal on t1(c1);

create table t2 (c1 number);
create index idx_reverse on t2(c1) reverse;

Do some inserts with loop and check the following query and you can see the difference.

SQL>
select object_name,value 
from v$segment_statistics
where owner='APP' and object_type='INDEX' 
and statistic_name='buffer busy waits';


See sample output. ( I have tested in my database)

OBJECT_NAME                         VALUE
------------------------------ ----------
IDX_NORMAL                         161347
IDX_REVERSE                          8983

The reverse key index has reduced the buffer busy waits by around 95%. Impressed? I hope you are. This is even more significant in a RAC environment, where buffer busy wait is globalized.
I am not saying that all indexes should be reversed. You do need to understand your data and how it is being accessed. For example, a non-equality predicate on the key cannot use the index. But when would you use a non-equality predicate on a primary key? Probably, never. It is hard to find a reason for not reversing all your monotonically increasing keys.

Hope this will help !!!

Managing XML data in Oracle database - using SQL and PL/SQL

Managing XML data in Oracle database

1) Insert data into table from XML
2) Generating XML from SQL and PL/SQL 

The datatype to hold XML in PL/SQL or in the database is XMLTYPE. So you can use the generated XML in PL/SQL, store it in table (XMLTYPE column), transform it to a CLOB (using the XMLTYPE getClobVal member function which I use in the examples) and write it to a file.

XML Conversion methods:

-- Create below tables to test scenarios

create table LOCATIONS
(
  location_id   NUMBER not null,
  location_name VARCHAR2(30)
);
alter table LOCATIONS add primary key (LOCATION_ID) using index ;

-- create the parent table DEPT

create table dept
(deptno number(3),
dname varchar2(20) not null,
location_id NUMBER,
constraint pk_deptno_dept primary key(deptno)
);

-- create the child table EMP

create table emp
(empno number(10), ename varchar2(50) not null,
sal number(10), deptno number(3),
constraint pk_empno_emp primary key(empno),
constraint fk_deptno_emp foreign key(deptno) references dept(deptno)
);

-- Insert records sample records
insert into LOCATIONS values(101,'Mumbai');
insert into LOCATIONS values(102,'Chennai');
commit;

insert into dept values(10,'Finance',101);
insert into dept values(20,'HR',104);
insert into dept values(30,'IT',106);
insert into dept values(40,'Audit',102);
commit;

insert into emp values (1001,'Gouranga',1000,30);
insert into emp values (1002,'Chowdari',9000,10);
insert into emp values (1003,'Ashok',9999,30);
insert into emp values (1004,'Chandra',8888,10);
insert into emp values (1005,'Gouranga',2000,10);
insert into emp values (1006,'Dinesh',3000,20);
insert into emp values (1007,'Somaiha',1000,20);
commit;


-- Create procedure to post all employees info to cursor

create or replace procedure p_show_employee
(oCursor_getemployee OUT sys_refcursor)
AS

BEGIN
  open oCursor_getemployee for
  select empno,ename,sal from emp;
END;
/


-- Create procedure to post data into cursor with parameterised. Procedure should optional conditions


create or replace procedure p_get_employee
(in_empno            IN emp.empno%TYPE,
in_deptno           IN emp.deptno%TYPE,
oCursor_getemployee OUT sys_refcursor) 
AS

BEGIN
  open oCursor_getemployee for
    select empno, ename, sal
      from emp e
     where e.empno = in_empno
       and (in_deptno IS NULL OR in_deptno = e.deptno);
END;
/


-- Sample XML format

<EMPLOYEE>
<EMP EMPNO="901" ENAME="XML_ONE" SAL="9999" DEPTNO="10"/>
<EMP EMPNO="902" ENAME="XML_TWO" SAL="8888" DEPTNO="20"/>
<EMP EMPNO="903" ENAME="XML_THREE" SAL="7777" DEPTNO="20"/>
</EMPLOYEE>

-- date sample

tDATE := to_date(lx_HostelDetails.extract('HostelDetails/@EndDate').getstringval(),'mm-dd-yyyy H24:MI:SS')

Note: XML types are case sensitive


-- show table value in XML format using SQL query

select xmlelement("Employee",
xmlattributes(e.empno AS "EMPID",e.ename As "EMPNAME",
e.sal as "SALARY")) As Result
from app.emp e 
where e.deptno=10;

XMLTYPE :

The easiest way to create an XML document, is using the constructor of XMLTYPE. This constructor can have several datatypes as input, like a CLOB and VARCHAR2, but as we’re going to base our XML on table data, we’re using a REF CURSOR.

You can create a ref cursor and pass on this ref cursor to the XMLTYPE constructor like this:

1) Insert data into table from XML 
-- Create a procedure to post data from XML input:

create or replace procedure p_post_employee(iClob_emp IN CLOB) 
IS
  v_xml_emp xmltype;
BEGIN
  v_xml_emp := xmltype(iClob_emp);
  insert into emp
    (empno, ename, sal, deptno)
  values
    (extractvalue(v_xml_emp, '/EMPLOYEE/EMP/@EMPNO'),
     extractvalue(v_xml_emp, '/EMPLOYEE/EMP/@ENAME'),
     extractvalue(v_xml_emp, '/EMPLOYEE/EMP/@SAL'),
     extractvalue(v_xml_emp, '/EMPLOYEE/EMP/@DEPTNO'));

END;
/

OR

create or replace procedure p_post_employee
(iClob_emp IN CLOB)
IS
 v_xml_emp xmltype;
BEGIN
  v_xml_emp:=xmltype(iClob_emp);
 insert into emp(empno,ename,sal,deptno)
 values
 (
 extract(v_xml_emp,'EMPLOYEE/EMP/@EMPNO').getnumberval(),
 extract(v_xml_emp,'EMPLOYEE/EMP/@ENAME').getstringval(),
 extract(v_xml_emp,'EMPLOYEE/EMP/@SAL').getnumberval(),
 extract(v_xml_emp,'EMPLOYEE/EMP/@DEPTNO').getnumberval()
 );
end;
/


During test input the below values:

<EMPLOYEE>
<EMP EMPNO="901" ENAME="XML_ONE" SAL="9999" DEPTNO="10"/>
</EMPLOYEE>

2) Generating XML from SQL and PL/SQL 

XMLTYPE:

The easiest way to create an XML document, is using the constructor of XMLTYPE. This constructor can have several datatypes as input, like a CLOB and VARCHAR2, but as we’re going to base our XML on table data, we’re using a REF CURSOR.

You can create a ref cursor and pass on this ref cursor to the XMLTYPE constructor like this:

create or replace procedure p_get_empdetails_xml
(o_refcursor OUT SYS_REFCURSOR)
IS
   l_xmltype XMLTYPE;
BEGIN
   OPEN o_refcursor 
   FOR  select deptno,dname FROM dept WHERE deptno IN(10,20);

   l_xmltype := XMLTYPE(o_refcursor);
   dbms_output.put_line(l_xmltype.getClobVal);
END;
/

output:

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>Finance</DNAME>
 </ROW>
 <ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>HR</DNAME>
 </ROW>
</ROWSET>


DBMS_XMLGEN:

The DBMS_XMLGEN built-in is similar to the XMLTYPE constructor, but accepts a query directly:

-- Direct display

create or replace procedure p_get_empdetails_xml
IS
  l_xmltype XMLTYPE;
BEGIN
  l_xmltype := dbms_xmlgen.getxmltype('SELECT deptno,dname FROM dept WHERE deptno IN(10,20)');

  dbms_output.put_line(l_xmltype.getClobVal);
END;
/

Output:

<ROWSET>
 <ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>Finance</DNAME>
 </ROW>
 <ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>HR</DNAME>
 </ROW>
</ROWSET>

But it also provides procedures to change the ROWSET and ROW tags.

create or replace procedure p_get_empdetails_xml
IS
   l_xmltype XMLTYPE;
   l_ctx dbms_xmlgen.ctxhandle;
BEGIN
   l_ctx := dbms_xmlgen.newcontext('SELECT deptno,dname FROM dept WHERE deptno IN(10,20)');

   dbms_xmlgen.setrowsettag(l_ctx, 'Departments'); 
   dbms_xmlgen.setrowtag(l_ctx, 'Dept');

   l_xmltype := dbms_xmlgen.getXmlType(l_ctx) ;
   dbms_xmlgen.closeContext(l_ctx);

   dbms_output.put_line(l_xmltype.getClobVal);
End;
/

output:

<Departments>
 <Dept>
  <DEPTNO>10</DEPTNO>
  <DNAME>Finance</DNAME>
 </Dept>
 <Dept>
  <DEPTNO>20</DEPTNO>
  <DNAME>HR</DNAME>
 </Dept>
</Departments>

dbms_xmldom:

With the XMLTYPE constructor and DBMS_XMLGEN package, you can create simple XML documents, fast and easy. When you need to create more advanced XML documents or want to have more control on how your XML document looks like, DBMS_XMLDOM can be used. The DBMS_XMLDOM package is a bit more complicated as you’ll have to create the entire document by calling functions and procedures of the package.

The following example creates an XML document with the department information retrieved from the query. In short, this is how it works: create new elements and add them as a (child) node.

create or replace procedure p_get_empdetails_xml
IS
   l_xmltype XMLTYPE;

   l_domdoc dbms_xmldom.DOMDocument;

   l_root_node dbms_xmldom.DOMNode;

   l_department_element dbms_xmldom.DOMElement;
   l_departments_node dbms_xmldom.DOMNode;

   l_dept_element dbms_xmldom.DOMElement;
   l_dept_node dbms_xmldom.DOMNode;

   l_name_element dbms_xmldom.DOMElement;
   l_name_node dbms_xmldom.DOMNode;
   l_name_text dbms_xmldom.DOMText;
   l_name_textnode dbms_xmldom.DOMNode;

   l_location_element dbms_xmldom.DOMElement;
   l_location_node dbms_xmldom.DOMNode;
   l_location_text dbms_xmldom.DOMText;
   l_location_textnode dbms_xmldom.DOMNode;

BEGIN
   -- Create an empty XML document
   l_domdoc := dbms_xmldom.newDomDocument;

   -- Create a root node
   l_root_node := dbms_xmldom.makeNode(l_domdoc);

   -- Create a new node Departments and add it to the root node
   l_department_element := dbms_xmldom.createElement(l_domdoc, 'Deptartments' );
   l_departments_node := dbms_xmldom.appendChild(l_root_node,dbms_xmldom.makeNode(l_department_element));

   FOR r_dept IN (SELECT dept.deptno
                       , dept.dname, loc.location_name
                    FROM dept dept
                    JOIN locations loc
                      ON loc.location_id = dept.location_id
                   WHERE dept.deptno IN (10,20)
                 )
   LOOP
      -- For each record, create a new Dept element with the Department ID as attribute.
      -- and add this new Dept element to the Departments node
      l_dept_element := dbms_xmldom.createElement(l_domdoc, 'Dept' );
      dbms_xmldom.setAttribute(l_dept_element, 'DeptID', r_dept.deptno);
      l_dept_node := dbms_xmldom.appendChild(l_departments_node,dbms_xmldom.makeNode(l_dept_element));

      -- Each Dept node will get a Name node which contains the department name as text
      l_name_element := dbms_xmldom.createElement(l_domdoc, 'Name' );
      l_name_node := dbms_xmldom.appendChild(l_dept_node,dbms_xmldom.makeNode(l_name_element));
      l_name_text := dbms_xmldom.createTextNode(l_domdoc, r_dept.dname );
      l_name_textnode := dbms_xmldom.appendChild(l_name_node,dbms_xmldom.makeNode(l_name_text));

      -- Each Dept node will aslo get a Location node which contains the location(city) as text
      l_location_element := dbms_xmldom.createElement(l_domdoc, 'Location' );
      l_location_node := dbms_xmldom.appendChild(l_dept_node,dbms_xmldom.makeNode(l_location_element));
      l_location_text := dbms_xmldom.createTextNode(l_domdoc, r_dept.location_name );
      l_location_textnode := dbms_xmldom.appendChild(l_location_node,dbms_xmldom.makeNode(l_location_text));
   END LOOP;

   l_xmltype := dbms_xmldom.getXmlType(l_domdoc);
   dbms_xmldom.freeDocument(l_domdoc);

   dbms_output.put_line(l_xmltype.getClobVal);
END;
/

output:

<Deptartments>
  <Dept DeptID="10">
    <Name>Finance</Name>
    <Location>Mumbai</Location>
  </Dept>
</Deptartments>

As you can see DBMS_XMLDOM is the most advanced way to create XML documents from table data, but it can also be used to create a document from different sources (query, CLOB, …), to search in XML documents, and to change documents (e.g. add nodes to an existing document).

More information see below documents on topic like:

•Oracle Documentation on XMLTYPE
•Oracle Documentation on DBMS_XMLGEN
•Oracle Documentation on DBMS_XMLDOM

Be careful on following:

• getClobVal is a deprecated operator and should be avoided in favor or XMLSerialize

• DBMS_XMLGEN is not really maintained anymore by the XMLDB dev team and, IMHO, is only useful for some encoding/decoding functionality it still has and sometimes still would be a valid reason to use it. If not only debugging statements fed into DBMS_XMLGEN can be a xxxx in the xxx and if unlucky can not be optimized by the CBO because its treated as a string and not XML/SQL and/or...

• DBMS_XMLDOM is still a valid way of doing stuff in the PL/SQL realm of things although the XMLDB Dev team strongly advices to use the XMLELEMENT, XMLFOREST, etc functions