• HCC works with direct path loads only that includes: CTAS, running impdp with ACCESS_METHOD=DIRECT or direct path inserts. If you insert data using a normal insert, it will not be HCC compressed.

• It is most suited for tables that aren’t going to be updated once loaded. There are some complications (next point) that arise if some DML is going to be run on HCC compressed data.

• At block level HCC stores data as compression units. A compression unit can be defined as a set of blocks. Now if some rows (stored with HCC) are updated, they need to be decompressed first. Also in that case the database needs to read the compression unit, not a single block. So once you do some update on the data stored in HCC, it will be moved out of HCC compression. To HCC compress it again you will need to do alter table table_name move compress for (Also see Metalink note 1332853.1). So if the tables you are planning to use HCC upon, undergo frequent DML, HCC may not be best suited for that scenario. Not only it will add the additional overhead of running alter table move statement every time some updates happen, it may screw up the storage space calculations as well.

]]> http://amardeepsidhu.com/blog/2011/12/22/dml-and-hcc-exadata/feed/ 0 http://amardeepsidhu.com/blog/2011/12/22/dml-and-hcc-exadata/ http://feedproxy.google.com/~r/OracleAndMore/~3/Q3sR5oRPrvg/ http://amardeepsidhu.com/blog/2011/11/04/ogg-01004-aborted-grouped-transaction-on-database-error-1403/#comments Fri, 04 Nov 2011 11:08:39 +0000 Sidhu http://amardeepsidhu.com/blog/?p=371 The last post was just like that. It was this GoldenGate issue that woke me up from the deep sleep to do a post after a long time .

Well it was a simple schema to schema replication setup using GoldenGate. We were using the SCN method (Metalink Doc ID 1276058.1 & 1347191.1) to do the intial load so that there is no overlvapping of transactions and the replicat runs with minimum issues. Even after following this method, the replicat was hitting

2011-10-31 19:25:17  WARNING OGG-01004  Aborted grouped transaction on 'SCHEMA.TABLE', Database error 1403 ().

2011-10-31 19:25:17  WARNING OGG-01003  Repositioning to rba 3202590 in seqno 1.

2011-10-31 19:25:18  WARNING OGG-01154  SQL error 1403 mapping SCHEMA.TABLE TO SCHEMA.TABLE.

2011-10-31 19:25:18  WARNING OGG-01003  Repositioning to rba 3468713 in seqno 1.

If we managed to bypass this error somehow, it hit:

2011-10-24 19:58:15  WARNING OGG-00869  OCI Error ORA-00001: unique constraint (SCHEMA.UK) violated (status = 1), SQL <INSERT INTO "SCHEMA"."TABLE" (<INSERT HERE>

2011-10-24 19:58:15  WARNING OGG-01004  Aborted grouped transaction on 'SCHEMA.TABLE', Database error 1 (OCI Error ORA-00001: unique constraint (SCHEMA.UK) violated (status = 1), SQL <INSERT HERE>).

2011-10-24 19:58:15  WARNING OGG-01003  Repositioning to rba 1502788 in seqno 3.

2011-10-24 19:58:15  WARNING OGG-01154  SQL error 1 mapping SCHEMA.TABLE to SCHEMA.TABLE OCI Error ORA-00001: unique constraint (SCHEMA.UK) violated (status = 1), SQL <INSERT HERE>.

2011-10-24 19:58:15  WARNING OGG-01003  Repositioning to rba 1502788 in seqno 3.

1403 means that GoldenGate couldn’t find the record it wanted to update.

00001 would mean that the record GoldenGate tried to insert was already there.

In our case, as we used SCN method so none of them was expected. So these weird errors left us totally confused. Some guys suggested that expdp was not taking a consistent image and some transactions were getting overlapped (picked up by both expdp & GG extract trail). We took the database down and repeated the exercise but oops ! it hit almost the same errors again. So it was not about consistency for sure.

Till now we haven’t been examining the contents of discard file very seriously. As the errors were pretty simple so we always suspected that some transactions were getting overlapped. Now it was high time to take some help from discard file as well . We took the before/after image of the record from the discard file and checked it in the target database & values in one or two columns were different (that is why GG couldn’t find that record). The new values were the actual hint towards the solution [It was a table storing the mail requests and their statuses. This update that GG was trying to run was updating the status from NOT-SENT TO SENT but here on the target the status was already set to 'ORA-something......']. We got the clue that something must have run on the target itself that spoiled this record and now GG is not able to find it and abending with 1403. select * from dba_jobs cleared it all. While doing the initial load with expdp/impdp, job also got imported and some of them were in not broken state. They were firing according to their schedule and making changes to data in the target. So before GG came to update/insert record the job had already done its game and the replicat was hitting different errors. We did the initial load again (this time by using flashback_scn in the running database), disabled all the jobs and ran the replicat. It went through without any errors.

So things to take care of, in such cases:

1) Disable all the triggers on the target side (or exclude triggers while running expdp)

2) Look for and disable any scheduled jobs (could be dba_jobs, dba_scheduler_jobs or cron)

Happy GoldenGate’ing !

]]> http://amardeepsidhu.com/blog/2011/11/04/ogg-01004-aborted-grouped-transaction-on-database-error-1403/feed/ 0 http://amardeepsidhu.com/blog/2011/11/04/ogg-01004-aborted-grouped-transaction-on-database-error-1403/ http://feedproxy.google.com/~r/OracleAndMore/~3/tfAFal0TETo/ http://amardeepsidhu.com/blog/2011/11/04/expdp-not-consistent/#comments Fri, 04 Nov 2011 09:40:26 +0000 Sidhu http://amardeepsidhu.com/blog/?p=368 Came across this small oddity that documentation of 10.2 and 11.2 states that expdp by default takes consistent image of the database. But actually it is not so. You need to use flashback_scn/flashback_time for that. Metalink doc 377218.1 explains the scenario.

Buffer cache        =  1048448    4.0g  25%  details with -bufcache

In HP-UX, The memory allocated to (dynamic) buffer cache is controlled by two parameters dbc_min_pct and dbc_max_pct. It can vary between dbc_min_pct and dbc_max_pct percent of the total RAM. They default to 5 and 50 respectively. For a system that is running an Oracle database value of 50 for dbc_max_pct is way too high. That means half of the memory is going to be allocated to OS buffer cache. As Oracle has got its own buffer cache so the OS cache is not of much use. As mentioned in the metalink note 726652.1, the value of dbc_max_pct can be safely lowered without impacting the Oracle database performance. In many of the threads (on HP website) people have suggested the value of 10 for db_max_pct. Not sure if it is more like a thumb rule but in the same metalink note (726652.1) it is mentioned that if %rcache in sar -b is above 90, that means your OS buffer cache is adequately sized.

After setting the value of dbc_max_pct to 15 (It will be changed to 10, finally), around 1.6 GB more memory was freed. Also there was no impact on the database or OS performance. Here are few of the metalink notes and threads on HP-UX website that talk about these parameters in detail:

Oracle Shadow Processes Are Taking Too Much Memory (Doc ID 434535.1)

How OS Buffer Cache Size Affects Db Performance (Doc ID 726652.1)

Commonly Misconfigured HP-UX Kernel Parameters (Doc ID 68105.1)

http://forums11.itrc.hp.com/service/forums/questionanswer.do?admit=109447626+1306231311459+28353475&threadId=1266914

http://forums11.itrc.hp.com/service/forums/questionanswer.do?threadId=727618

http://forums11.itrc.hp.com/service/forums/questionanswer.do?threadId=467288

http://forums11.itrc.hp.com/service/forums/questionanswer.do?threadId=750342

Scenario was: Oracle is installed from “oracle” user and all runs well. There is a new OS user “test1″ that also needs to use sqlplus. So granted the necessary permissions on ORACLE_HOME to test1. Tried to connect sqlplus scott/tiger@DB and yes it works. But while trying sqlplus scott/tiger it throws:

$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.5.0 - Production on Wed May 18 09:32:35 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

ERROR:
ORA-12547: TNS:lost contact

Enter user-name: ^C
$

Did a lot of troubleshooting including checking tnsnames.ora, sqlnet.ora, listener.ora and so on. Nothing was hitting my mind so finally raised an SR. And it has to do with the permissions of the $ORACLE_HOME/bin/oracle binary. The permissions of oracle executable should be rwsr-s–x or 6751 but they were not. See below:

$ id
uid=241(test1) gid=202(users) groups=1(staff),13(dba)
$

$ cd $ORACLE_HOME/bin
$ ls -ltr oracle
-rwxr-xr-x    1 oracle   dba       136803483 Mar 16 20:32 oracle
$

$ chmod 6751 oracle
$ ls -ltr oracle
-rwsr-s--x    1 oracle   dba       136803483 Mar 16 20:32 oracle
$

$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.5.0 - Production on Wed May 18 10:23:27 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "SCOTT"
SQL>

SQL> @wait

EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
wait for possible quiesce finish                                          1
Streams AQ: qmn coordinator idle wait                                     1
Streams AQ: qmn slave idle wait                                           1
Streams AQ: waiting for time management or cleanup tasks                  1
SQL*Net message to client                                                 1
smon timer                                                                1
pmon timer                                                                1
jobq slave wait                                                           4
rdbms ipc message                                                        11
SQL*Net message from client                                              27
resmgr:become active                                                    322

11 rows selected.

SQL>

Tanel’s snapper showed something like:

SQL> @snapper ash 5 1 all
Sampling with interval 5 seconds, 1 times...

-- Session Snapper v3.11 by Tanel Poder @ E2SN ( http://tech.e2sn.com )

-----------------------------------------------------------------------
Active% | SQL_ID          | EVENT                     | WAIT_CLASS
-----------------------------------------------------------------------
 26322% | 4ffu7nb93c2c9   | resmgr:become active      | Scheduler
  1900% | 2wn958z7gzh57   | resmgr:become active      | Scheduler
  1400% | 9d9bg2r538nd2   | resmgr:become active      | Scheduler
   600% | 4d3k70q6y344k   | resmgr:become active      | Scheduler
   500% | d6vwqbw6r2ffk   | resmgr:become active      | Scheduler
   500% | 4tsrz92mmshbw   | resmgr:become active      | Scheduler
   200% | 37td1bbvc1a69   | resmgr:become active      | Scheduler
   100% | ftdjfxws0s8q9   | resmgr:become active      | Scheduler
   100% | 41apc1bjqrfbv   | resmgr:become active      | Scheduler
   100% | af9d8aqtkvn02   | resmgr:become active      | Scheduler

--  End of ASH snap 1, end=2011-02-10 11:06:40, seconds=5, samples_taken=23

PL/SQL procedure successfully completed.

SQL>

If we check the description of the wait event, it says:

The session is waiting for a resource manager active session slot. This event occurs when the resource manager is enabled and the number of active sessions in the session’s current consumer group exceeds the current resource plan’s active session limit for the consumer group. To reduce the occurrence of this wait event, increase the active session limit for the session’s current consumer group.

But if we check the resource_limit settings:

SQL> show parameter resource

NAME_COL_PLUS_SHOW_PARAM   TYPE		VALUE_COL_PLUS_SHOW_PARAM
----------------------- -----------     --------------------------
resource_limit            boolean		FALSE
resource_manager_plan     string

SQL>

What ? Resource manager is not enabled. But why all the sessions are waiting for resmgr:become active and nobody is able to login ?

A bit of googling lead me to this page from where I got the clue.

Generally, this wait situation occurs when you execute certain EMCA operations such as the operation for creating the EM repository. As a result of these operations, the systems implicity switches to QUIESCE mode. Therefore, all database connections (except users SYS and SYSTEM) must wait for “resmgr:become active”. In this case, refer to Note 1044758 and execute the following command if necessary:

ALTER SYSTEM UNQUIESCE;

I asked around in the DBA team and one of the guys was trying to configure EM for the database due to which system switched tto QUIESCE mode and all the sessions were waiting on resmgr:become active.

After canceling the operation, the wait event was gone and everything was working normally.

• Few users in the database were assigned the CREATE SESSION privilege through a password protected role (That role was the default role for that user). 10.2.0.5 onwards, password protected roles can’t be set as default roles. The alternate is to either disable the password for the role or assign CREATE SESSION directly to the user, not through a role.
• After the upgrade, few procedures became invalid and while compiling started giving ORA-00918: COLUMN AMBIGUOUSLY DEFINED. The issue was bug 2846640 which is fixed in 10.2. Actually, in few of the queries using ANSI syntax, the developer didn’t qualify the column names with table names. It worked fine in 9i but due to the bug getting fixed in 10g, it started giving ORA-00918. The simple solution is to prefix the column name with the table name.
• Few of the application schema owner users complained that they were not able to modify the procedures/packages in their own schemas. The schemas were not assigned CREATE PROCEDURE privilege but as per documentation, they should be able to modify the existing procedures/packages owned by them. This again is a documentation bug. It worked fine in 9i but in 10g onwards you need to have either a CREATE PROCEDURE or ALTER ANY PROCEDURE privilege (a risky one) to be able to edit the PL/SQL units in your own schema.

These were few of the issues encountered, rest of the upgrade was super smooth !

Happy upgrading !

While running impdp import in 11g, you hit:

ORA-39083: Object type INDEX failed to create with error:
ORA-14102: only one LOGGING or NOLOGGING clause may be specified

It is related to bug 9015411 where DBMS_METADATA.GET_DDL creates incorrect create index statement by dumping both LOGGING and NO LOGGING clauses. Due to this the CREATE INDEX statement, while running impdp fails with the above error. It applies to 11.2.0.1 (Metalink doc id 1066635.1)

Fix is to install the patch, if it is available for your platform. Another workaround is given in this OTN thread i.e. strip the create index statement of storage related information by using TRANSFORM=SEGMENT_ATTRIBUTES:N:INDEX & TRANSFORM=SEGMENT_ATTRIBUTES:N:CONSTRAINT

exp system/manager@DB owner=ABC file=ABC.dmp log =ABC.log

Export: Release 8.1.7.1.0 - Production on Fri Nov 12 04:21:05 2010

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

EXP-00056: ORACLE error 28002 encountered
ORA-28002: the password will expire within 11 days
EXP-00056: ORACLE error 24309 encountered
ORA-24309: already connected to a server
EXP-00000: Export terminated unsuccessfully

I googled and metalink’ed (oops…is there anything like that ? ) a bit and found that it was bug 1654141 where user accounts in grace period cannot perform export. It is fixed in Oracle 9i (version 9.0.0.0 as per metalink). The obvious work around is to change the password and then try again. Thought about posting it here so that Google can give little better results if someone in trouble comes searching for it .

Jonathan’s presentations were simply amazing. His knowledge about how things work (and why they work this way not that) is simply awesome.  He is an inspiration for newbies like us and there was so much to learn from him. Few of the quick tips that i picked up from him:

1. Don’t believe what you read or hear. Make small test cases to test and confirm how things work & how they don’t. He said that he has around 2000 test cases on his laptop. Some of them ready to be fired on Oracle database 12g .
2. Always document your findings. At a later date you only won’t be able to remember that something that you already faced and solved something you are stuck in. If you document things properly, you would always remember a bit of it and you can search it in a minute.

Also i got to meet & attend presentation of good friend Francisco Munoz Alvarez. I have been in touch with him since more than 2 years but this was for the first time i was meeting him in person. Also his presentation on how to become a good DBA was really awesome. Enjoyed every bit of it.

Two of my colleagues Vivek Sharma and Rahul Dutta were also presenting, so got a chance to see their presentations too. Vivek talked about developing scalable applications and Rahul’s presentation was about developing a EBS reporting solution using Oracle streams.

I attended some part of Mark Rittman‘s session also. I am not much into data warehousing but Mark is such a respected name so wanted to be present in his session .

I also met and attended one of the presentation of Iggy Fernandez. He talked about 52 weeks in the life of a database. I couldn’t attend his other presentation on reading execution plans as Vivek was presnting in the same time slot.

Overall, it was an amazing experience and i am already looking forward to attending Sangam (or whatever it would be called ) 11 !

Read Aman’s post about Sangam 10.