Oracle Masters

Welcome to Oracle Masters. The portal, as the tagline says, is for anything Oracle. FAQs, Certifications, Resources! In addtion, there will be pages on real life scenarios, Oracle internals, approaching the DBA job at various stages in your career and many other useful resources.Further, if you have a question, you can ask it and we will answer it at the earliest!
So join in. Learn, share and prosper!
Register with us!

Ramesh Menon
Oracle Certified Master - 10g
Oracle Certified RAC Expert - 10g
Oracle Certified Professional - 11g

Are you interested in Weekend/Online Training in Oracle DBA? Do you have business/consulting requirements? Click here Enquiry Form to leave your requirements or mail me at Ramesh Menon

Page 1 of 3
1
2
3
>

Architecture andHA - RAC, Dataguard

Friday, 20 Aug 2010 7:32 AM

How To Check Whether Physical Standby is in Sync with the Primary or Not?

By tdomf_2a078

1 Reply »

Hi all,

The following tips will help you in monitoring sync between primary and standby databases.

How To Check Whether Physical Standby is in Sync with the Primary or Not?

1. Check for GAP on standby
2. Check redo received on standby
3. Check redo applied on standby
Read the rest of this entry »

Popularity: 6% [?]

Architecture andHA - RAC, Dataguard

Saturday, 14 Aug 2010 6:32 AM

11g Physical Standby database creation

By Ramesh Menon

No Replies »

Creating a physical standby database on Oracle 11gR2

This article is a simple example to setup a standby database in Oracle 11g. The standby database environment will be used in later articles for configuring dataguard broker and logical standby database creation

Read the rest of this entry »

Popularity: 2% [?]

Architecture andHA - RAC & Dataguard FAQs

Thursday, 12 Aug 2010 1:02 AM

one of datafile shows recovery in my standby database

By tdomf_2a078

1 Reply »

Hi,

One of my datafile in standby database shows recover mode.
can any one help me how to solve this issue.

I have created one table in one tablespace in production database, today i have applied the archive files in standby database, after that i have opened the standby database in read only mode to check the table. But the table is
corrupted saying block corrupted at block Num 3.

regards

Popularity: 3% [?]

Architecture andPerformance Tuning

Tuesday, 8 Jun 2010 5:35 PM

Query tuning

By niket

9 Replies »

Optimizer is not using index when i am giving hint to the query which contains bind variables

It is generating its own plan

The query is

^?Download text

SELECT b.amount, b.invoice_charge_price, b.charge_id, b.internal_agreement_no,
       b.item_code, b.entity_id, b.agreement_id, b.start_date, b.end_date,
       b.charge_code, b.charge_type, price_inclusive_of_taxes,
       b.pricing_entity_id, b.party_slaccount
  FROM bt_billing_charges b
 WHERE b.entity_id = :b3
   AND NVL (b.invoice_no, 0) = 0
   AND b.paid_status = 'N'
   AND b.cp_code = NVL (:b2, b.cp_code)
   AND b.paymode_trans_no = NVL (:b1, b.paymode_trans_no);

Now execution plan is
============

^?Download text

Plan Table
============
----------------------------------------------------------------+--------------------------------
---+
| Id  | Operation                      | Name                   | Rows  | Bytes | Cost  | Time
   |
----------------------------------------------------------------+--------------------------------
---+
| 0   | SELECT STATEMENT               |                        |       |       |   539 |
   |
| 1   |  CONCATENATION                 |                        |       |       |       |
   |
| 2   |   FILTER                       |                        |       |       |       |
   |
| 3   |    TABLE ACCESS BY INDEX ROWID | BT_BILLING_CHARGES     |     1 |    88 |   269 |  00:00:
04 |
| 4   |     INDEX RANGE SCAN           | IDX_BT_BILLING_ENTITYID|   807 |       |     8 |  00:00:
01 |
| 5   |   FILTER                       |                        |       |       |       |
   |
| 6   |    TABLE ACCESS BY INDEX ROWID | BT_BILLING_CHARGES     |     1 |    88 |   269 |  00:00:
04 |
| 7   |     INDEX RANGE SCAN           | IDX_BT_BILLING_ENTITYID|   807 |       |     8 |  00:00:
01 |
----------------------------------------------------------------+--------------------------------
---+
Predicate Information:
----------------------
2 - filter(:B1 IS NULL)
3 - filter((NVL("B"."INVOICE_NO",0)=0 AND "B"."PAID_STATUS"='N' AND "B"."PAYMODE_TRANS_NO" IS NOT
 NULL AND "B"."CP_CODE"=NVL(:B2,"B"."CP_CODE")))
4 - access("B"."ENTITY_ID"=TO_NUMBER(:B3))
5 - filter(:B1 IS NOT NULL)
6 - filter((NVL("B"."INVOICE_NO",0)=0 AND "B"."PAID_STATUS"='N' AND "B"."CP_CODE"=NVL(:B2,"B"."CP
_CODE") AND "B"."PAYMODE_TRANS_NO"=:B1))
7 - access("B"."ENTITY_ID"=TO_NUMBER(:B3))

Now i am passing hint to the query

^?Download text

SELECT /*+ INDEX (b BT_BILLING_CHARGES_I4) */ b.amount, b.invoice_charge_price, b.charge_id, b.internal_agreement_no,
       b.item_code, b.entity_id, b.agreement_id, b.start_date, b.end_date,
       b.charge_code, b.charge_type, price_inclusive_of_taxes,
       b.pricing_entity_id, b.party_slaccount
  FROM bt_billing_charges b
 WHERE b.entity_id = :b3
   AND NVL (b.invoice_no, 0) = 0
   AND b.paid_status = 'N'
   AND b.cp_code = NVL (:b2, b.cp_code)
   AND b.paymode_trans_no = NVL (:b1, b.paymode_trans_no)

But still it is using same plan.

Now when i removed bind variables with values then it is using index

^?Download text

SELECT /*+ INDEX (b BT_BILLING_CHARGES_I4) */ B.AMOUNT,B.INVOICE_CHARGE_PRICE,B.CHARGE_ID,B.INTER
NAL_AGREEMENT_NO, B.ITEM_CODE,B.ENTITY_ID,B.AGREEMENT_ID,B.START_DATE,B.END_DATE, B.CHARGE_CODE,B
.CHARGE_TYPE,PRICE_INCLUSIVE_OF_TAXES,B.PRICING_ENTITY_ID,B.PARTY_SLACCOUNT FROM IMCL420.BT_BILLI
NG_CHARGES B WHERE B.ENTITY_ID=12345263 AND NVL(B.INVOICE_NO,0)=0 AND B.PAID_STATUS='N' AND B.CP_
CODE=NVL(0,B.CP_CODE) AND B.PAYMODE_TRANS_NO= NVL(0,B.PAYMODE_TRANS_NO)

and exec plan is

^?Download text

Plan Table
============
-------------------------------------------------+-----------------------------------+
| Id  | Operation         | Name                 | Rows  | Bytes | Cost  | Time      |
-------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT  |                      |       |       |     5 |           |
| 1   |  INDEX RANGE SCAN | BT_BILLING_CHARGES_I4|     1 |    88 |     5 |  00:00:01 |
-------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("B"."ENTITY_ID"=12345263 AND NVL("INVOICE_NO",0)=0 AND "B"."PAID_STATUS"='N')
1 - filter((NVL("INVOICE_NO",0)=0 AND "B"."PAID_STATUS"='N' AND TO_NUMBER("B"."CP_CODE")=NVL(0,TO
_NUMBER("B"."CP_CODE")) AND "B"."PAYMODE_TRANS_NO"=NVL(0,"B"."PAYMODE_TRANS_NO")))

so my question is how to force query containing bind variables to use index

Kindly guide me

Thanks

Regards

Niket

Popularity: 16% [?]

Architecture

Thursday, 3 Jun 2010 12:27 AM

10g rac

By tdomf_2a078

1 Reply »

hi ramesh

Can u explain about master and slave nodes. I have 4 node rac, if master is down , which node will take over as master out of 3 alive nodes.

2. can I mannually switch master and slave manually.

Popularity: 5% [?]

Architecture andDBA Posts

Thursday, 27 May 2010 7:52 AM

Oracle 9i binary installation.

By srini

No Replies »

We faced this error during the oracle 9I binary installation during (silent installation) on solaris 5.10 (While applying the patch set (9.2.0.6.0))

erver: /tmp/dumps/ship.patch.drop7/Disk1 $ ./runInstaller -silent -responseFile /tmp/dumps/ship.patch.drop7/Disk1/response/patchset.rsp
Starting Oracle Universal Installer…

Checking installer requirements…

Checking operating system version: must be 5.8 or 5.9. Actual 5.10
Failed <<<<

Exiting Oracle Universal Installer, log for this session can be found at /ora9i/oracle/oraInventory/logs/installActions2010-05-27_03-09-17PM.log

This error we rectified by editing the file
oraparamsilent.ini
oraparam.ini
By default these files were present on Disk1/install folder of the patch set dumps.

We added solaris 5.10 entry in to the above mentioned files under column

[Certified Versions]
Solaris=5.8,5.9,5.10

We gone through the installation.

Regards

Srinivas

Popularity: 2% [?]

Architecture andBackup/Recovery

Thursday, 1 Oct 2009 8:50 AM