Replication Troubleshooting

While transferring data from materialized view site end. I was faced the below error message.

ORA-12012: error on auto execute of job 2
ORA-23402: refresh was aborted because of conflicts caused by deferred txns
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

Oracle suggest to fix this error automatically using “refresh_after_errors= true”

MviewAdmin@Materialized view site.

BEGIN

DBMS_REFRESH.CHANGE(
name => '"MVIEWADMIN"."MIS_REFG"',
next_date => to_date('01-01-2020 06:06:08','DD-MM-YYYY HH24:MI:SS'),
interval => 'SYSDATE + 1 + (1/24)',
implicit_destroy => FALSE,
rollback_seg => 'NULL',
push_deferred_rpc => TRUE,
refresh_after_errors => TRUE,
purge_option => 1,
parallelism => 0,
heap_size => 0);

END;

By logically it’s not good. You should be find why data’s not transfer? Any problem in your database?

Sys@MasterSite

Here you can find out those problems using the following data dictionary views,

SQL> select deferred_tran_id, destination, error_number, error_msg from deferror;

Using deferred transaction id, you can find out which transaction not updated in materialized view site. Fix those errors. After that only transfer your data’s.

Please post your comments.

AWR

The Oracle 10g database introduces a new framework for managing many tuning tasks automatically, for producing real-time information about the database's health, and for extending advisories to improve performance.

The new manageability infrastructure mainly focuses on four areas. They are as follows:

- Automatic Workload Repository - The ability to automatically collect and store database information at regular intervals is crucial. This information should be persistent and accurate. Oracle introduces a new internal data store called Automatic Workload Repository (AWR) to collect and store data. AWR is central to the whole framework of self and automatic management. It works with internal Oracle database components to process, maintain, and access performance statistics for problem detection and self-tuning.

- Automatic Database Diagnostic Monitor - The second key component is the advisory framework that provides expert recommendations to improve performance. The Automatic Database Diagnostic Monitor (ADDM) is a server-based performance expert in a box. It can perform real time root cause analysis of performance issues. It relies on the current statistics within the SGA and on the contents of the AWR. In addition, there are various advisory tools to help make tuning decisions.

- Next, are the Automatic Routine Administration tasks. By using the newly introduced Scheduler, you can delegate to the Oracle database some of the repetitive tasks that need to be performed to keep the database up-to-date.

- Server Generated Alerts - Oracle Database 10g is capable of automatically detecting many database alarm situations.

Use AWR instead of statspack report

You can disable the statspack capture as AWR will capture all the information.
To enable most of the new statistical gathering and advisors, ensure that the parameter.

STATISTICS_LEVEL is set to TYPICAL (recommended).

Running the awrrpt.sql Report

To generate an HTML or text report for a range of snapshot Ids, run the awrrpt.sql script at the SQL prompt:

Oracle provides two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

@$ORACLE_HOME/rdbms/admin/awrrpti.sql

The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly

SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql

First, you need to specify whether you want an HTML or a text report.

Enter value for report_type: text

Specify the number of days for which you want to list snapshot Ids.

Enter value for num_days: 2


After the list displays, you are prompted for the beginning and ending snapshot Id for the
workload repository report.

Enter value for begin_snap: 150
Enter value for end_snap: 160


Next, accept the default report name or enter a report name. The default name is accepted in the
following example:

Enter value for report_name:
Using the report name awrrpt_1_150_160

...

...

….

The workload repository report is generated.