We are using OEM 13.3 to create standby. Primary and Standby all using ASM, but Standby using a different diskgroup name from primary. (e.g. Primary +DATAC1, Standby +DATAC3).
While the primary DB was in 12.2, to create a Standby for a 10TB Primary would take around 8 hours. However After upgrade to 19c (19.6), create a Standby could takes 2 days then fail at the end.
Alert log keep spooling message about cannot find the ASM DG as Primary:
2020-09-21T16:40:18.875868+08:00
Errors in file /u01/app/oracle/diag/rdbms/c_garnet_x6e3/CGARNET/trace/CGARNET_mz05_61034.trc:
ORA-00312: online log 10 thread 1: '+RECOC1/C_GARNET_X7F4/ONLINELOG/group_10.2443.1021202829'
ORA-17503: ksfdopn:2 Failed to open file +RECOC1/C_GARNET_X7F4/ONLINELOG/group_10.2443.1021202829
ORA-15012: ASM file '+RECOC1/C_GARNET_X7F4/ONLINELOG/group_10.2443.1021202829' does not exist
ORA-00312: online log 10 thread 1: '+DATAC1/C_GARNET_X7F4/ONLINELOG/group_10.2105.1021202821'
ORA-17503: ksfdopn:2 Failed to open file +DATAC1/C_GARNET_X7F4/ONLINELOG/group_10.2105.1021202821
ORA-15001: diskgroup "DATAC1" does not exist or is not mounted
ORA-15001: diskgroup "DATAC1" does not exist or is not mounted
ORA-15064: communication failure with ASM instance
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
2020-09-21T16:09:11.724099+08:00
***********************************************************************
Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(TCP_USER_TIMEOUT=1)(RECV_TIMEOUT=10)(CONNECT_TIMEOUT=60)(EXPIRE_TIME=1)(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.1)(PORT=1526))(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.3)(PORT=1526)))(CONNECT_DATA=(SERVICE_NAME=+ASM_DATAC1)(CID=(PROGRAM=oracle)(HOST=hkx6e3db01.lfinfra.net)(USER=oracle))))
VERSION INFORMATION:
TNS for Linux: Version 19.0.0.0.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
Version 19.6.0.0.0
Time: 21-SEP-2020 16:09:11
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 12560
nt main err code: 524
TNS-00524: Current operation is still in progress
nt secondary err code: 115
nt OS err code: 0
2020-09-21T16:09:11.728083+08:00
WARNING: failed to register AMB1 with ASM instance
WARNING: AMB1 exiting with error
Look into the MZ05 trace file:
Trace file /u01/app/oracle/diag/rdbms/c_garnet_x6e3/CGARNET/trace/CGARNET_mz05_61034.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
Build label: RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
...
dbkea: Clearing error 49205
ORA-49205: DDE async action error [dbkea_exec] [dbgea_exec failed] [] [] [] [] [] []
ORA-49205: DDE async action error [dbgea_exec_] [cancel exec] [0] [34340905] [DB_STRUCTURE_INTEGRITY_CHECK] [] [] []
ORA-49205: DDE async action error [dbkea_exec] [msg too old] [324] [300] [] [] [] []
========= Dump for error ORA 312 (no incident) ========
----- DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
dbkh_reactive_run_check: BEGIN
dbkh_reactive_run_check:; incident_id=0
dbkh_run_check_internal: BEGIN; check_namep=DB Structure Integrity Check, run_namep=<null>
dbkh_run_check_internal: BEGIN; timeout=0
dbkh_run_check_internal: AFTER RUN CREATE; run_id=26301
Check from Standby DB, any session blocking (final_blocking_session is not null): seeing MMON_SLAVE keep waiting on “enq: DD – contention”
set pages 120
set lines 100
col module for a20
col action for a30
col event for a30
SQL> select sid,module,action,event,state,final_blocking_session from v$session where final_blocking_session is not null;
SID MODULE ACTION EVENT STATE FINAL_BLOCKING_SESSION
---------- -------------------- ------------------------------ ------------------------------ ------------------- ----------------------
3838 MMON_SLAVE DDE async action enq: DD - contention WAITING 10276
7262 MMON_SLAVE DDE async action enq: DD - contention WAITING 10276
7811 MMON_SLAVE DDE async action enq: DD - contention WAITING 10276
8359 MMON_SLAVE DDE async action enq: DD - contention WAITING 10276
10277 MMON_SLAVE DDE async action enq: DD - contention WAITING 10276
Solution:
While OEM keep running standby creation, in the mean time, run alter database clear logfile online, to recreate the log files into the new ASM diskgroup.
SQL> select distinct 'alter database clear logfile group '||group#||';' from v$logfile;
alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;
alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 6;
alter database clear logfile group 10;
alter database clear logfile group 11;
alter database clear logfile group 12;
alter database clear logfile group 13;
alter database clear logfile group 14;
alter database clear logfile group 15;
alter database clear logfile group 16;
Later after all logfiles re-created, the alert log will not complaint about log files. It will keep doing “DB_STRUCTURE_INTEGRITY_CHECK” for other datafiles, but during standby creation progressing, those datafiles will be fixed one after another.
After a while, check again blocking sessions, all clear:
SQL> select sid,module,action,event,state,final_blocking_session from v$session where final_blocking_session is not null;
no rows selected
In this way the OEM standby creation speed about to resumed prior 19c upgrade.
TonieLook's Data Blog 