ORA-00600: internal error code, arguments: [krbrckhr_fail]

Databas: 10g R2
OS : Windows 2003 R2

While createing database using DBCA, I got below error. I can't able to create database using dbca and manually also

ORA-00600: internal error code, arguments: [krbrckhr_fail]

Solution:

I think it's bug, that's why i installed patch set 10.2.0.3. After installing patch set i can able to create database.

ORA-24324 & ORA-24323

Oralce 10g: 10.2.0.1
OS : Windows 2000 SP4

During database creation time(DBCA) i got the follow error message.

ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-28547: connection to server failed, probable Oracle Net admin error

There is no problem in my database. Finally i change the below parameter after only i can able to create database.

Solution:

SQLNET.AUTHENTICATION_SERVICES = (NONE)

Memory Notification: Library Cache Object loaded into SGA

Memory Notification: Library Cache Object loaded into SGA

In 10g we have a new undocumented parameter that sets the KGL heap size warning threshold. This parameter was not present in 10gR1. Warnings are written if heap size exceeds this threshold.

Set _kgl_large_heap_warning_threshold to a reasonable high value or zero to prevent these warning messages. Value needs to be set in bytes.

If you want to set this to 8192 (8192 * 1024) and are using an spfile:

(logged in as "/ as sysdba")

SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;

SQL> shutdown immediate SQL> startup

SQL> show parameter _kgl_large_heap_warning_threshold
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_kgl_large_heap_warning_threshold integer 8388608

If using an "old-style" init parameter,

Edit the init parameter file and add

_kgl_large_heap_warning_threshold=8388608

NOTE: The default threshold in 10.2.0.1 is 2M. So these messages could show up frequently in some application environments.

In 10.2.0.2, the threshold was increased to 50MB after regression tests, so this should be a reasonable and recommended value. If you continue to see the these warning messages in the alert log after applying 10.2.0.2 or higher, an SR may be in order to investigate if you are encountering a bug in the Shared Pool.

Replication + Logical

Replication –Logical things

I was facing some new thing in replication. I want to share my experience to you.

Actually my master and materialized view site its different oracle version. Master size 10.2.0.3 and materialized view site one is 10.2.0.3 and another one is 9.2.0.8.

One day I dropped one materialized view and again re-created materialized view because I add some column in base table in mater site. But dropped materialized view objects name I’m not removed in replication group. So, no need to re-add materialized view name in replication group.

Once completed everything I try to transfer data. I’m getting the below error message.

Error 2091 trapped in 2PC on transaction 8.10.95082. Cleaning up.

Error stack returned to user:

ORA-02091: transaction rolled back

ORA-02291: integrity constraint (SAMIINVENTORY.FK_GRNMST_SUP) violated - parent key not found

Error 3113 trapped in 2PC on transaction 6.44.93771. Cleaning up.

Tue Jan 01 18:34:36 2008

DISTRIB TRAN KNGDB.SAMIINDIA.6db37e82.6.44.93771

is local tran 6.44.93771 (hex=06.2c.16e4b)

insert pending collecting tran, scn=379132697 (hex=0.16991b19)

Tue Jan 01 18:34:36 2008

Error stack returned to user:

ORA-02050: transaction 6.44.93771 rolled back, some remote DBs may be in-doubt

ORA-03113: end-of-file on communication channel

ORA-23324: error ORA-00604: error occurred at recursive SQL level

ORA-02063: preceding line from SAMIDB, while creating deferror entry at "SAMIDB.SAMIINDIA" with error -604

Tue Jan 01 18:34:41 2008

Actually problem is while transferring data, some transaction roll backing its called 2PC on transaction error. But you can able to check what are pending transaction using DBA_2PC_PENDING and DBA_2PC_NEIGHBORS. 

But in my case NO values return (I know it’s happens). We found two solutions.

• Deferred Constraints

In master site we have lot referable tables (parent and child table). Here no need to set deferrable constraints but the same we are replicated in materialized view site. Here we should be use deferrable constraints in parent and child tables.

Because replication is going on for a refresh group; we have no control over the order in which the tables inside the refresh group gets refreshed. Sometimes, child tables may get refreshed first and then parent tables. So in such cases we need to defer the enforcement of the constraint till commit time. Hence we need to use Deferrable constraints.

• DBMS_MVIEW.I_AM_A_REFRESH

Application related reasons, we don’t want the trigger (especially those which update new table (this table it’s not replicated)) to execute while data comes from other materialized view site. So to see whether the replication operation is going on or not, we are using the above mentioned function. Do note that our triggers are NOT replicated triggers, we are creating triggers locally. Also our stock table is a local table, NOT a replicated table.

Critical Patch Installation

Critical Patch Installation in 10.2.0.3

1. Patch Set Overview

Patch release 10.2.0.3. Before installing this patch set you must be need 10.2.0.3 version.

2. Requirements

Oracle DB : Oracle 10.2.0.3
Operating System: Windows 2000 (Service Pack 2 or higher)


1. Stop all services

C:\>set oracle_sid=samidb

C:\>net stop OracleDBConsoleSamidb
The OracleDBConsolesamidb service is stopping...........
The OracleDBConsolesamidb service was stopped successfully.


C:\>net stop OracleServiceSamidb
The OracleServicesamidb service is stopping.
The OracleServicesamidb service was stopped successfully.


C:\>net stop OracleOraDB10g_Home1iSQL*Plus...
The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.


C:\>net stop OracleOraDb10g_home1TNSListener
The OracleOraDb10g_home1TNSListener service is stopping.
The OracleOraDb10g_home1TNSListener service was stopped successfully.


Note: while patch installation time we need to stop Distributed Transaction Coordinator. It’s not oracle related but you should be stop this service.

C:\>net stop msdtc
The Distributed Transaction Coordinator service is stopping.
The Distributed Transaction Coordinator service was stopped successfully.

3. How to check previous patch

C:\>set oracle_sid=samidb

C:\>set oracle_home=d:\oracle\product\10.2.0\db_1

C:\>d:

D:\>cd d:\oracle\product\10.2.0\db_1

D:\oracle\product\10.2.0\db_1>cd opatch

D:\oracle\product\10.2.0\db_1\OPatch>opatch lsinventory
….
….
….
4. Take complete backup.

Before installing patches we should take complete database cold backup. And take backup for OraInventory folder (It’s available in operating system drive).
5. How to install patch
C:\>set oracle_sid=samidb

C:\>set oracle_home=d:\oracle\product\10.2.0\db_1

C:\>d:

D:\>cd d:\oracle\product\10.2.0\db_1

D:\oracle\product\10.2.0\db_1>cd opatch

D:\oracle\product\10.2.0\db_1\OPatch>opatch apply E:\ 6637237
……
…..
…..

6. Start all Oracle Services including MSDTC

Once patch installation successfully completed. You need to start all oracle services including Microsoft Distribution co-ordinate (MSDTC)

7. Post installation
For each database instance running out of the ORACLE_HOME being patched, connect to the database using SQL*Plus as SYSDBA and run catcpu.sql as follows:
> cd %ORACLE_HOME%\CPU\CPUJan2008
> sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catcpu.sql
SQL> SHUTDOWN
SQL> QUIT

5. Compile Invalid Objects

If catcpu.sql reports any Invalid Objects, compile the invalid objects as follows.
(For large numbers of objects, this compilation step can take some time. If you are applying this patch through the Oracle Enterprise Manager console, you will be prompted to run this compilation script.)
> cd %ORACLE_HOME%\rdbms\admin
> sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlprp.sql 0

Configuring Conflict Resolution

Conflict Resolution in Advanced Replication:

In replication, the most common data conflict occurs when the same row at two or more different sites are updated at nearly the same time, or before the deferred transaction from one site was successfully propagated to the other sites.

One method to avoid update conflicts is to implement a synchronous replication environment, though this solution requires large network resource.

The other solution is to use the Oracle conflict resolution methods to deal with update conflicts that can occur when the same row receives two or more updates.

Now we need to avoid this error in replication. Next question is; How to avoid.

There are several methods to avoid this issue in replication but before we need to know how to replication data transfer working? If you understand this concept you never face any error in replication.

How working data transfer between two or more site:

Once you stat data transfer using dbms_refresh.refresh. First of all checking old values in my object.

For example:

I have one table like “EMP” it’s replicated (updatable). Once start data transfer what’s the old values in EMP objects? Those values available in both site or not. If available means next that data checking current values I mean new values. Suppose both end old records not available sense you should be face “ORA-12034: materialized view log younger than last refresh”. If you face this error you need complete refresh other than no never solve this issue.

Some time in both site updating same row. This time you should be face “ORA-23402: refresh was aborted because of conflicts caused by deferred txns” If you face this error you need to implement conflict resolution.

For example:

SQL> show user;

USER is "BABU@WASIM" – Master Site

SQL> select * from master;

FIRSTNAME LASTNAME ID

-------------------- -------------------- ----------

BABU SRIKANT 101

WASIM SRI 102

SQL> update master set firstname='WASIM LATIF' where FIRSTNAME='WASIM';

1 row updated.

SQL> commit;

Commit complete.

SQL> CONNECT BABU@MV - Materialized view site.

Enter password:

Connected.

SQL> SELECT * FROM MASTER;

FIRSTNAME LASTNAME ID

-------------------- -------------------- ----------

BABU SRIKANT 101

WASIM SRI 102

SQL> update master set firstname='WASIM LATIF' where FIRSTNAME='WASIM';

1 row updated.

SQL> commit;

Data Transfer between master and materialized view database:

MviewAdmin@mv.

SQL> exec dbms_refresh.refresh('mviewadmin.tst_rep');

BEGIN dbms_refresh.refresh('mviewadmin.tst_rep'); END;

*
ERROR at line 1:
ORA-23402: refresh was aborted because of conflicts caused by deferred txns
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195

Configure conflict resolution:

CONNECT repadmin/repadmin@wasim.samiinda

BEGIN

DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (

gname => 'BABU');

END;

/

/*Create Column group for target table*/

BEGIN

DBMS_REPCAT.MAKE_COLUMN_GROUP (

sname => 'BABU',

oname => 'MASTER',

column_group => 'BABU',

list_of_column_names => 'firstname,lastname'); //this is the column available

for both end

END;

/

/*Define conflict resolution method*/

BEGIN

DBMS_REPCAT.ADD_UPDATE_RESOLUTION (

sname => 'BABU',

oname => 'MASTER',

column_group => 'BABU',

sequence_no => 1,

method => 'DISCARD',

parameter_column_name => 'firstname,lastname');

END;

/

/*regenerate replication support for conflict resolution method*/

BEGIN

DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (

sname => 'BABU',

oname => 'MASTER',

type => 'TABLE',

min_communication => TRUE);

END;

/

/*start replication activity*/

BEGIN

DBMS_REPCAT.RESUME_MASTER_ACTIVITY (

gname => 'BABU');

END;

/

Commit;

Conflict Resolution Methods:

There are several method to configure conflict resolutions. The above example I explained “DISCARD” methods.

1. Discard – Already explained.

2. Minimum and Maximum :

When Advanced Replication detects a conflict with a column group and calls either the minimum or maximum value conflict resolution methods, it compares the new value from the originating site with the current value from the destination site for a designated column in the column group. You must designate this column when you define your conflict resolution method.

If the new value of the designated column is less than or greater than (depending on the method used) the current value, then the column group values from the originating site are applied at the destination site, assuming that all other errors were successfully resolved for the row. Otherwise the rows remain unchanged.

3. Timestamp Conflict resolution method.

The earliest timestamp and latest timestamp methods are variations on the minimum and maximum value methods. To use the timestamp method, you must designate a column in the replicated table of type DATE. When an application updates any column in a column group, the application must also update the value of the designated timestamp column with the local SYSDATE. For a change applied from another site, the timestamp value should be set to the timestamp value from the originating site.

Two elements are needed to make timestamp conflict resolution work well:

· Synchronized time settings between computers

· Timestamp field and trigger to automatically record timestamp

REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY
3 (
4 GNAME => 'BABU'
5 );
6 END;
7 /

PL/SQL procedure successfully completed.

REPADMIN@wasim.samiindia> COMMIT;

/* Altering my based using alter_master_repobject procedure */

REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.ALTER_MASTER_REPOBJECT
3 (
4 SNAME => 'BABU',
5 ONAME => 'MENUMASTER',
6 TYPE => 'TABLE',
7 DDL_TEXT => 'ALTER TABLE BABU.MENUMASTER ADD (MODIFYED_ON DATE )'
8 );
9* END;
REPADMIN@wasim.samiindia> /

PL/SQL procedure successfully completed.

REPADMIN@wasim.samiindia> COMMIT;

Commit complete.


REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT
3 (
4 SNAME => 'BABU',
5 ONAME => 'MENUMASTER',
6 TYPE => 'TABLE',
7 MIN_COMMUNICATION => TRUE,
8 generate_80_compatible => FALSE
9 );
10* END;
REPADMIN@wasim.samiindia> /

PL/SQL procedure successfully completed.

REPADMIN@wasim.samiindia> COMMIT;

Commit complete.

/* This is called creating new column group */

REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.MAKE_COLUMN_GROUP
3 (
4 SNAME => 'BABU',
5 ONAME => 'MENUMASTER',
6 COLUMN_GROUP => 'QC',
7 LIST_OF_COLUMN_NAMES => '*'
8 );
9 END;
10 /

PL/SQL procedure successfully completed.

REPADMIN@wasim.samiindia> COMMIT;

Commit complete.

/* This is latest timestamp method */

REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.ADD_UPDATE_RESOLUTION
3 (
4 SNAME => 'BABU',
5 ONAME => 'MENUMASTER',
6 COLUMN_GROUP => 'QC',
7 SEQUENCE_NO => 1,
8 METHOD => 'LATEST TIMESTAMP',
9 PARAMETER_COLUMN_NAME => 'MODIFYED_ON'
10 );
11* END;
REPADMIN@wasim.samiindia> /

PL/SQL procedure successfully completed.

REPADMIN@wasim.samiindia> COMMIT;

Commit complete.

REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT
3 (
4 SNAME => 'BABU',
5 ONAME => 'MENUMASTER',
6 TYPE => 'TABLE',
7 MIN_COMMUNICATION => TRUE,
8 generate_80_compatible => FALSE
9 );
10* END;
REPADMIN@wasim.samiindia> /

PL/SQL procedure successfully completed.

REPADMIN@wasim.samiindia> COMMIT;

Commit complete.

REPADMIN@wasim.samiindia> BEGIN
2 DBMS_REPCAT.RESUME_MASTER_ACTIVITY
3 (
4 GNAME => 'BABU'
5 );
6 END;
7 /

PL/SQL procedure successfully completed.

REPADMIN@wasim.samiindia> COMMIT;

4. Addtive and Average conflict methods.

The additive and average methods work with column groups consisting of a single numeric column only. Instead of "accepting" one value over another, this conflict resolution method either adds the two compared values together or takes an average of the two compared values.

Complete the following steps to create an additive or average conflict resolution method. This example averages the commission percentage for an employee in the event of a conflict.

5. Priority Groups Conflict Resolution Methods:

Priority groups allow you to assign a priority level to each possible value of a particular column. If Oracle detects a conflict, then Oracle updates the table whose "priority" column has a lower value using the data from the table with the higher priority value.

6. Site Priority Conflict Resolution Methods

Site priority is a specialized form of a priority group. Therefore, many of the procedures associated with site priority behave similarly to the procedures associated with priority groups. Instead of resolving a conflict based on the priority of a field's value, the conflict is resolved based on the priority of the sites involved.

Monitoring Conflict Resolution:

· DBA_REPCONFLICT describes the conflict resolution method for all the tables in the database on which a conflict resolution method has been defined.

· USER_REPCONFLICT describes the conflict resolution method for all the tables owned by the current user on which a conflict resolution method has been defined.DBA_REPPARAMETER_COLUMN - Using this data dictionary we can able to find what are they column groups, which method using and what are they parameter colunm used those infomarion we can find using this data dictionary table.

DBA_REPRESOLUTION - Using this data dictionary we can find conflict resolution method type and table name

DBA_REPRESOLUTION_METHOD - Using this data dictionary we can find method name.

DBA_REPRESOLUTION_STATISTICS - View lists information about successfully resolved update, uniqueness, and delete conflicts for all replicated tables in the database.

DBA_REPSITES view lists the members of each replication group in the database.

DBA_REPCOLUMN - List of column name using group

DBA_REPCOLUMN_GROUP - List of group name using per column;

DBA_REPDDL -contains the DDL for each replication object in the database. Its columns are the same as those in ALL_REPDDL.

DBA_REPCONFLICT - view displays the name of each table in the database on which a conflict resolution method has been defined and the type of conflict that the method is used to resolve.