Shulou(Shulou.com)06/01 Report--

There was a problem yesterday when an OGG replication process was replicating a sequence (Sequence). The Hang did not move, and the process state was always Running but did not copy forward, resulting in a delay of more than 6 hours.

GGSCI (ctm-3) 2 > info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING REPLICAT RUNNING USIM 00:13:39 06:50:22

When operating the replication process, stats and stop show that the operation timed out and only the kill process can be restarted. The problem lasted from the afternoon to more than 7 o'clock in the evening, there is really no way to initialize all the data, it is still not good, but the amount of data is not large. Finally, the problem of assigning dba rights to goldengate users is solved, but the allocation of rights for GOLDENGATE users is still a problem. The following is a record of the process of dealing with the problem, which can be seen by interested friends.

Use view report usim to view the log, which stops on a statement that replicates Sequence

Wildcard MAP resolved (entry USIM.*): map "USIM". "SEQ_PROCESSSTEP", target USIM. "SEQ_PROCESSSTEP"; Resolving target sequence USIM.SEQ_PROCESSSTEP.

Check the ggserr.log and there is no error message.

So check the session in the database.

SYS@ctm > select sid,serial#,LOGON_TIME,MODULE,sql_id,prev_sql_id,event,blocking_session from v$session where MODULE like'% USIM%' SID SERIAL# LOGON_TIME MODULE SQL_ID PREV_SQL_ID EVENT BLOCKING_SESSION -764 8229 20-JAN-17 OGG-USIM-OPEN_DATA_SOURCE awjuqsu6bk5d4 b6zg67dtg1q14 row cache lock SYS@ctm > select sql_text from v$sql where sql_id='awjuqsu6bk5d4' SQL_TEXT- -SELECT "SEQ_PROCESSSTEP" .NEXTVAL FROM DUALSYS@ctm > select sql_text from v$sql where sql_id='b6zg67dtg1q14' SQL_TEXT- -SELECT HIGHWATER FROM SYS.SEQ$ WHERE OBJ#=:B1

Seeing that the current ogg replication process is executing the statement of SELECT "SEQ_PROCESSSTEP" .NEXTVAL FROM DUAL, the process is waiting for row cache lock. What is posted above is the situation when querying in the evening. In fact, when checking this sql in the afternoon, it is a different sequence name, but the actions are all select nextval from dual, but the waiting events are library cache: mutex X, cursor: pin S wait on X, latch free, latch: shared pool.

See these waiting events are big, first go to Baidu to search, see a post that may be BUG, and then go to MOS to search for the combination of waiting events, see a lot of BUG articles, but the description is not consistent with the current situation. Then turned to search goldengate and sequence hang keywords, found a http://m.blog.csdn.net/article/details?id=48544207, found 1331998.1 and 1535322.1 on MOS, but are not consistent with the current situation.

So I had no choice but to look up the official documents and found the following words in Oracle GoldenGate Oracle Installation and Setup Guide:

After seeing the sentence in the red box, it occurred to me that due to the cross-dimension of the system, users are not allowed to have DBA roles, and GOLDENGATE is not allowed, so check the roles of GOLDENGATE users.

SYS@ctm > select granted_role from dba_role_privs where grantee='GOLDENGATE';GRANTED_ROLE--RESOURCECONNECTSELECT_CATALOG_ROLE

Sure enough, there is no DBA role, so is that the problem? so try to give the GOLDENGATE user the DBA role.

Grant dba to goldengate

Kill dropped the session where hang lived, restarted the replication process usim, and the problem was finally solved. The replication process begins to copy down, and the sequence replicates very quickly.

GGSCI (ctrm-r3) 20 > stats usim hourlySending STATS request to REPLICAT USIM... Start of Statistics at 2017-01-20 21:47:22.DDL replication statistics:*** Total statistics since replicat started * Operations 0.00 Mapped operations 0.00 Unmapped operations 0.00 Other operations 0.00 Excluded operations 0.00 Errors 0.00 Retried errors 0.00 Discarded errors 0.00 Ignored errors 0.00Replicating from USIM.SEQ_PROCESSSTEP to USIM.SEQ_PROCESSSTEP:*** Hourly statistics since 2017-01-20 21:46:06 * * Total updates 16.00 Total discards 0.00 Total operations 16.00End of Statistics.

In retrospect, is it really because of the DBA character? So take back the DBA role of the GOLDENGATE user: revoke dba from goldengate

Once again observe the replication process, unexpectedly normal in the replication, not affected. Moreover, from recycling the dba role at 10:00 last night to posting a blog today, the replication process does not have hang residence, and is still replicating normally. This goes back to the above question, is it really caused by the role of DBA? It's not clear yet. If any great god knows the cause of the problem, you can reply in the blog, thank you very much.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.