Notes for advanced oracle Replication

Some notes about advanced replication! Advanced Replication is an advanced function of oracle databases. It is more complex to maintain than normal databases. The following are some problems and solutions in the maintenance of existing Advanced Replication databases. 1. Stable physical link: number of servers connected to each other due to network disconnection due to faults

Some notes about advanced replication! Advanced Replication is an advanced function of oracle databases. It is more complex to maintain than normal databases. The following are some problems and solutions in the maintenance of existing Advanced Replication databases. 1. Stable physical link: number of servers connected to each other due to network disconnection due to faults

Some notes about advanced replication!

　　
Advanced Replication is an advanced function of oracle databases. It is more complex to maintain than normal databases. The following are some problems and solutions in the maintenance of existing Advanced Replication databases.
　　
1. Stable physical link: The network connecting to the server is disconnected due to a fault. The data between the servers cannot be accessed and cannot be transmitted. If the network is unstable, data will be lost and incomplete.
　　
2. high transmission speed requirements: otherwise, it is not as good as a single-level version.
　　
3. The participating servers cannot be started for a long time. This will result in a large backlog of delayed tasks and data cannot be released.
　　
4. The table involved in replication cannot directly execute any DDL Statement on it: Because ORACLE automatically creates an internal TRIGGER and PACKAGE that supports replication on the table involved in replication, executing any DDL Statement on it will destroy the replication support. SUSPEND should first modify the replication group of the table, execute DDL statements in repication manager or call the repcat api, and then GENERATE the replication support for the table again, finally, the replication group is restored to NORMAL. Note: In the DDL statement for modifying the table structure, the table name must be prefixed with the owner without a semicolon. If the DDL statement is executed accidentally, the table should be removed from the replication environment, deleted, re-created, or copied from other nodes.
　　
5. modify a TABLE and add a field, and set the default value, such as alter table owner. TABLE_NAME ADD (FIELD_NAME VARCHAR2 (20) DEFAULT 'aaa'); before 9i, it cannot be executed once. It must be executed in two parts:
1. alter table owner. TABLE_NAME ADD FIELD_NAME
2. alter table owner. TABLE_NAME MODIFY FIELD_NAME DEFAULT 'aaa'
This is because the SUSPEND replication group is required to execute any DDL statements. In this case, the replication table can only be queried and other DML statements cannot be executed. If a field is added directly and the default value is assigned, a field is added and the default value is assigned to the field immediately. The subsequent DML statements cannot be executed, therefore, an error is reported. The statement is divided into two parts for execution. The first part adds a field, and the second part modifies the definition of the field. The default DML operation is not inserted, so it can be executed.
　　
6. before executing any admin request (the management command for the replication environment), make sure there are no accumulated deferred trans.
　　
7. Any admin request must be executed step by step. Because of the working principle of admin request, the admin request statement is saved in a REPCATLOG table, and one disappears after the execution, and the subsequent execution continues. If the previous one is not completed, the admin request cannot be executed. If the REPCATLOG table is not empty, the replication group cannot be restored to NORMAL. Therefore, after an admin request is sent, check the REPCATLOG table. Only when the REPCATLOG table on all nodes is empty can the next command be issued.
　　
8. If the REPCATLOG table contains commands that cannot be executed, you can re-APPLY or PURGE them and re-issue the command for execution. If there are only legacy commands on a node, you can APPLY them several times on the node.
　　
9. If no response is received after the command is issued, and the admin request cannot be purged, the deadlock is similar. You can try to drop the JOB corresponding to the admin request and refresh the command again. If the command can continue to be executed, the JOB status will be restored. If not, check the deadlock in V $ SESSION and V $ LOCK and use alter system kill session to KILL the deadlock process. The distributed deadlock of the type 'rq' needs to be identified, and the corresponding background PROCESS is identified from V $ PROCESS and V $ BGPROCESS with SID, and unlocked by killing the background PROCESS at the operating system level, you may need to restart the database. Finally, the JOB status and other related replication environment statuses are restored.
　　
10. You can use replication manager or related system tables to query the REPLICATION environment and log in as a REPADMIN User:
View the replication group and objects:
Select gname, status from dba_repgroup;
-- Gname indicates the name of the replication group, and status indicates the status.
Select gname, onAme, status from dba_repobject where type = 'table' and onAme =
'Table _ name ';
-- To query the copy group of a table, replace table_name with the table name. Note that the table name must be in uppercase.

View the distributed status:
Select job, what, next_sec, this_sec, last_sec, failures, broken from dba_jobs;
Failures is less than 16. If the broken is N, the distributed State is normal.
　　
　　View database connections
Select * from dba_db_links;
　　
　　Resume a propagation copy task
When the network of the database in the replication environment fails, the replication task may be stopped. Symptom
The update operation of the local database is not released to the remote database. When this happens, see
Check whether the replication task is normal by checking the distributed status. If failures is greater than or equal to 16,
If the broken is Y, the distributed State is abnormal and the replication task needs to be resumed.
Exec dbms_job.run (jobno );
-- Jobno is a job in which the ailures in the dba_jobs table is greater than or equal to 16 and the broken is Y.
　　
　　View the transaction with an error
When the distributed database is abnormal, execute the following statements and solve the problem based on the queried error_msg.
Problem.
Select distinct origin_tran_db, destination, error_msg from deferror;
　　
　　An error occurred while executing the transaction.
After the network connection fails and other problems are solved based on the queried error_msg, execute the following statements and copy the generated
The combined exec statement is executed on the client.
Select 'exec dbms_defer_sys.execute_error (''' | DEFERRED_TRAN_ID | ''', ''' |
DESTINATION | ''') 'from deferror;
　　
　　Delete the transaction with an error
If you attempt to execute an incorrect transaction, the transaction still fails to be executed. If
When the error_msg in the deferror table is "NO DATa found "error, you can consider deleting the ticket
A transaction with a row error. Note that you must confirm that the cause of the error has been resolved and
You can delete the transaction with another error only after you try to execute the wrong transaction. Otherwise, the distributed database will
.
Select 'exec dbms_defer_sys.delete_error (''' | DEFERRED_TRAN_ID | ''', ''' |
DESTINATION | ''') 'from deferror;