splitting table partitions with BLOB in streams

The last hours of the last day of the last year I spent trying to get
capture process of streams working.

Developers executed the script that prepared application tables
for new year splitting and rebuilding indexes on them.

After that capture process was aborted due to
ORA-04031 for streams pool.

The setting for stream_pool_size was 384M only but increase of
the value up to 1, 2 even 5Gb did not help either.

Capture was failing with the errors:

ORA-04031: unable to allocate ... bytes of shared memory ("streams pool","unknown object","streams pool","kol raw")
ORA-04031: unable to allocate ... bytes of shared memory ("streams pool","unknown object","streams pool","sob_kgqmrec")
ORA-04031: unable to allocate ... bytes of shared memory ("streams pool","unknown object","streams pool","kolccst obj")
ORA-04031: unable to allocate ... bytes of shared memory ("streams pool","unknown object","streams pool","sob_kgqmrec")


After research on Metalink one interesting note had been found:

STREAMS CAPTURE ERRORS OUT WITH ORA-4031 ON SPLIT PARTITION COMMAND

which exactly describes the problem that we have.
But they were able to go through all transactions for capture process
only when streams_pool_size was increased up to 16Gb!
Unfortunately there is no chance for now to allocate
so much memory for streams and there is no patch for it yet.

Might be upgrade (from 10.2.0.4) to 11g will help,
might be additional memory will be found
or Metalink will come with some ideas, will see...

Have a good day!

orapwd and standby in 11g

I was trying to build standby for Oracle 11g database to test new
features of Data Guard and stuck with problem that archive logs had
not been transferred to standby side because of the following error:

Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
      returning error ORA-16191
------------------------------------------------------------

The password files have been created on both sides using orapwd:
orapwd file=orapwdb11 password=sys
and remote_login_passwordfile parameters were set to EXCLUSIVE.
And I was able to login as sysdba to remote sites using either TNS or
easy naming methods.

After different attempts I recreated password files with password
in upper-case (thinking about case sensitivity for password in 11g):
orapwd file=orapwdb11 password=SYS force=y
and it worked - primary started to send logs to standby.

Trying to find the reason of such behavior I recognized that there is
the parameter that responsible for password sensitivity in 11g -
SEC_CASE_SENSITIVE_LOGON.
I set it to FALSE, changed passwords to lower-case, bounced databases
and... the same problem as before :(

That meant the parameter does not influence on case sensitivity of
password during sysdba connection.

And only after that case I found out that there is a new parameter for
orapwd utility - ignorecase

Usage: orapwd file= password= entries= force= ignorecase= nosysdba=

  where
    file - name of password file (required),
    password - password for SYS (optional),
    entries - maximum number of distinct DBA (required),
    force - whether to overwrite existing file (optional),
    ignorecase - passwords are case-insensitive (optional),
    nosysdba - whether to shut out the SYSDBA logon (optional Database Vault only).

I changed SEC_CASE_SENSITIVE_LOGON back to TRUE,
recreated password file with lower-case characters and ignorecase=y,
bounced databases and everything went smoothly!

Have a good day!

switching $ORACLE_BASE using oraenv in 11g

I recently installed a couple of Oracle 11g on VM. First one was installed
to /u01 but after testing different things I added one more to /u05.
But it does not really matter, the main thing that 11g raised importance
of $ORACLE_BASE variable added it to different places - oraenv script
(you have noticed sentence about oracle base after execution of the script)
and as parameter to init file.

All that means the $ORACLE_BASE should be dependent on
ORACLE_HOME and be saved somewhere. Moreover the script should
change it! But it was not so. Everytime I ran it $ORACLE_BASE left the
same even they based on the same path as $ORACLE_HOME:

[oracle@oel1 bin]$ . oraenv
ORACLE_SID = [db11106] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u05/app/oracle
[oracle@oel1 bin]$ . oraenv
ORACLE_SID = [db11106] ? db11a
The Oracle base for ORACLE_HOME=/u05/app/oracle/product/11.1.0/db_2 is /u05/app/oracle
[oracle@oel1 bin]$ . oraenv
ORACLE_SID = [db11a] ? db11106
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u05/app/oracle

Checking the script I found the part that responsible for
oracle base change and comments there:


# Set the value of ORACLE_BASE in the environment. Use the orabase
# executable from the corresponding ORACLE_HOME, since the ORACLE_BASE
# of different ORACLE_HOMEs can be different.
# The return value of orabase will be determined based on the following :
#
# 1. Value of ORACLE_BASE in the environment.
# 2. Get the value of ORACLE_BASE from oraclehomeproperties.xml as
# set in the ORACLE_HOME inventory.



Going further I found that oraclehomeproperties.xml is under
$ORACLE_HOME/inventory/ContentsXML and it contains properties
and one of them is:

...
property name="ORACLE_BASE" val="/u05/app/oracle"
...

(/u01/app/oracle was for the other oracle home)

That's the place where $ORACLE_BASE and $ORACLE_HOME variable
are put together. But the script doesn't switch base if it was already set.
And the reason of it - orabase script ($ORACLE_HOME/bin).
If you have $ORACLE_BASE value set - orabase returns this value.
If not - it gets value from oraclehomeproperties.xml file:

[oracle@oel1 bin]$ . oraenv
-- switched to another home and got wrong $ORACLE_BASE
ORACLE_SID = [db11a] ? db11106
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u05/app/oracle
-- wrong value
[oracle@oel1 bin]$ echo $ORACLE_BASE
/u05/app/oracle
-- orabase returns wrong value
[oracle@oel1 bin]$ which orabase
/u01/app/oracle/product/11.1.0/db_1/bin/orabase
[oracle@oel1 bin]$ orabase
/u05/app/oracle
-- unset $ORACLE_BASE
[oracle@oel1 bin]$ export ORACLE_BASE=
[oracle@oel1 bin]$ echo $ORACLE_BASE

-- and now orabase returns right value
[oracle@oel1 bin]$ orabase
/u01/app/oracle

Finally I added the "export ORACLE_BASE=" entry before
"ORABASE_EXEC=$ORACLE_HOME/bin/orabase" to null the value that
orabase could accomodated the real value of $ORACLE_BASE variable.

[oracle@oel1 bin]$ vi /u01/app/oracle/product/11.1.0/db_1/bin/oraenv
[oracle@oel1 bin]$ vi /u05/app/oracle/product/11.1.0/db_2/bin/oraenv
[oracle@oel1 bin]$ . oraenv
ORACLE_SID = [db11106] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u01/app/oracle
[oracle@oel1 bin]$ . oraenv
ORACLE_SID = [db11106] ? db11a
The Oracle base for ORACLE_HOME=/u05/app/oracle/product/11.1.0/db_2 is /u05/app/oracle

Have a good day!

adrci and Data Guard logs

I made an installation of Oracle 11g recently and had started to set up
Data Guard when got some errors during configuration. Since the new
version of databases came with Automatic Diagnostic Repository (ADR)
I thought it might be useful and interesting to check if adrci can
provide some view of not only alert log but Data Guard log files as well.

Unfortunately I did not find such possibility but at least dr...log file
can be added to an incident package if it is generated.

I hope in future releases ADR will integrate Data Guard logs as well.

Have a good day!

"ORA-10388: parallel query server interrupt" for streams process

It is almost a week of battle between streams process and us who support it.
It failed on weekend (as usual) with bunch of errors:

Errors in file /u01/oracle/admin/PROD/bdump/prod01_p001_21536.trc:
ORA-00600: internal error code, arguments: [kgh_heap_sizes:ds], [0x2A9808E358], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kgh_heap_sizes:ds], [0x2A9808E358], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kgh_heap_sizes:ds], [0x2A9808E358], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghGetHpSz1], [0x2A9808E368], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kgh_heap_sizes:ds], [0x2A9808E358], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [kghalf()+561] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFFFFFFFF0] [] []
...
ORA-01438: value larger than specified precision allowed for this column
...
ORA-10388: parallel query server interrupt (failure)


Restart of apply processes did not help neither bouncing a database.

After creation of Metalink SR and during substitution of tables in streams
with materialized views two patches were applied
(10.2.0.4 on Linux 64-bit):
7272297
"ORA-00600 [17114] OR ORA-600[17125] - INSTANCE TERMINATION"
and 5868257
"MALFORMED RCI MARKER REDO DUE TO ORA-1551 IN UPDATE DRIVER"
but only the last one helped to avoid the issue and recovered streams
process as it was before.

Streams process failed on updates not for all tables but for some of them
which have many columns with names up to maximum length allowed.

So if you have such tables and plan to add them to streams I would
recommend to check the availability of the patch 5868257 for your database.

Have a good day!

How to exclude ORA- errors from alerts in Grid Control

There is a Note: 330996.1 on Metalink how to filter
certain alert.log errors from appearing in alerts.

There is example there how to ignore certain errors
from coming to alerts of database.

Unfortunately proposed expression doesn't work - might be some
characters hidden in html code of the page or might be simply missed.
Since it is based on regular expressions there must be clause
to ignore certain patterns using symbol ^.

After some testing I came with the expression like this
.*ORA-0[^(3333|2222|1111)].*
that allows to ignore ORA-01111, ORA-02222, ORA-03333 messages.

Also I removed "Generic Alert Log Error Status" metric to elude messages
about ignored errors.

The pattern was tested on EM GC Rel.4 on 10.1.0.4 on Linux.


Have a good day!

ORA-1873 accessing the SQL_RESPONSE_COLLECT metric

So you upgraded your databases to 10.2.0.4 and also recreated hc...dat file not
to hang Grid Control agent, enabled health check, started agent -
everything to fix consequences of bug https://metalink.oracle.com/metalink/plsql/showdoc?db=Bug&id=5872000 (see post http://dbadailytelegraph.blogspot.com/2008/08/killing-health-check.html)
and executed tail -f emagent.trc to see if some errors coming - 1, 2, 3 minutes,
everything looks to be alright.

Don't hurry up! :(

The errors are coming! And they are from anonymous PL/SQL block that runs
to measure "SQL Response Time (%)" metric and it scheduled to gather
information every 5 minutes by default:

ORA-06512: at line 7
2008-09-15 22:12:17,815 Thread-4064263072 ERROR engine: [oracle_database,test,sql_response_collect] : nmeegd_GetMetricData failed : ORA-01873: the leading precision of the interval is too small
ORA-06512: at "DBSNMP.MGMT_RESPONSE", line 1013
ORA-06512: at "DBSNMP.MGMT_RESPONSE", line 923
ORA-06512: at line 7

2008-09-15 22:12:17,816 Thread-4064263072 WARN collector: Error exit. Error message: ORA-01873: the leading precision of the interval is too small
ORA-06512: at "DBSNMP.MGMT_RESPONSE", line 1013
ORA-06512: at "DBSNMP.MGMT_RESPONSE", line 923
ORA-06512: at line 7


The search on Metalink leads to https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=6737060.8
and affected (confirmed) versions of databases are 10.2.0.3 and 10.2.0.4
and it will be fixed in 11.1.0.7 and 11.2

Disabling the metric will help to avoid errors but it can be important metric at some stage.


Have a good day!