Oracle DataGuardSupport IssuesBrian HitchcockOCP 10g DBASun Microsystemsbrian.hitchcocksun.combrhoraaol.comwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 1 www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 2 Oracle DataGuardFocus on Logical Standby Support IssuesMaintains a standby databaseArchived redo logs on primarySent to standby and appliedSimple ideaMany configuration optionsCan become very complicatedwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 3 DataGuardMust be SYS to make changesSqlplus / as sysdbaChanges to DataGuard standby databaseSome cant be made while apply process runningChange Guard statusSupport IssuesCreate physical standbyConvert to logical standbyAfter logical standby is runningRefresh processwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 4 DataGuard ErrorsDataGuard reports lot of errorsStandby database alert logMany are for normal operationWhy reported as errors?Monitoring of db alert logWill report these errorsHard to filter out normal errorswww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 5 Create Physical StandbyOn Primary databaseEnable Forced LoggingCreate password fileSetup init.ora/spfile parametersCant connect to standbySYS passwordVerify archiving enabledBackup db (hot or cold)Create standby control filewww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 6 Create Physical StandbyOn Standby databaseCopy db backup files from primaryCopy standby control file from primarySetup init.ora/spfile parametersStart physical standby dbTrace fileVerify physical standby workingMay not see redo logs, register themRedo logs not deleted, use RMANwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 7 Convert to Logical StandbyOn Primary databaseBuild LogMiner dictionaryOn Standby databaseStop redo applyErrors, no impactConvert database to logical standbyTwo trace filesRestart dbOpen resetlogsVerify logical standby workingwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 8 Logical Standby is RunningBusiness requirementsStandby frozen most of the dayStandby catches up once per dayAlert log messages while catching upDisk space for archived redo logsOther issuesApply process is slowHow to detect, resolvePrimary versus Standby backupsImpact, resolutionwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 9 Logical Standby is RunningOther IssuesConstraint violationsErrors, resolutionNo data foundErrors, resolutionORA16211Errors, Oracle SupportPrimary db XDB schema issuesFixed on primary, errors on standbywww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 10 Logical Standby is RunningOther issuesORA-07445Refresh cures allRefresh processAfter refreshORA-16211: unsupported record found in the archived redo logCompile invalid objectsImport into standby databasewww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 11 Primary Cant ConnectStandby not availableReported on primary production database“ORACLE not available”Looks like production primary is downYour monitoring may need to be adjustedThu Oct 18 16:59:20 2007 Error 1034 received logging on to the standby Thu Oct 18 16:59:20 2007 Errors in file /shared/orahome01/admin/BRHPROD/bdump/brhprod_arc1_2635.trc: ORA01034: ORACLE not available PINGARC1: Heartbeat failed to connect to standby BRHPRSB. Error is 1034.www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 12 SYS Password IssueMon Oct 8 15:31:36 2007 Error 1017 received logging on to the standby - Check that the primary and standby are using a password file and remote_login_passwordfile is set to SHARED or EXCLUSIVE, and that the SYS password is same in the password files. returning error ORA-16191 - Mon Oct 8 15:31:36 2007 Errors in file /orahome01/admin/BRHBETA/bdump/brhbeta_arc0_2309.trc: ORA-16191: Primary log shipping client not logged on standby PINGARC0: Heartbeat failed to connect to standby BRHBRSB. Error is 16191. Primary tries to connect to standbywww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 13 SYS Password IssueVerify SYS password is the sameOn primary and standbySqlplus sys/Verify password file has same passwordOn primary and standbyCat $ORACLE_HOME/dbs/orapwRefresh password fileAlter user SYS identified by Update password filewww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 14 DataGuard Trace FilePhysical StandbyStart log apply processTrace file createdStops when log apply process stopsSee file contents laterwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 15 Cant See Redo LogsPhysical StandbyCreating or Refreshing standbyPrimary configured, sending redo logsStandby not yet created/runningStandby may not register redo logsOur scripts maintain primary archived redo logsCompress to save disk space, delete after 2 daysManually registerAlter database register logfile ;DataGuard applies redo logwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 16 Cant See Redo LogsBRHBETA select * from v$archive_gap; THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# 1 1959 1976 BRHBETA select sequence#, applied from v$archived_log order by sequence#; SEQUENCE# APP 1956 YES 1957 YES 1958 YES 1977 NO 1978 NO 1979 NO 1980 NO 1981 NO 1982 NO 1983 NO 1984 NO 11 rows selected.1959 thru 1976 on standbywww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 17 Redo Logs Not DeletedPhysical StandbyAfter applied to standbyUnlike logical standbySQL apply process does delete themUse RMANPossible disk space issues on standbyHow long will you need to store redo logs?Not an issue if converting to logical soonwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 18 Stop Physical StandbyLog Apply Process startStarts trace fileWhen physical standby first createdEnds when log apply stopsNormal processingTrace file looks like a problemwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 19 Turn Off Apply ProcessPhysical standbyTurn off apply processRegular maintenanceConverting to Logical StandbyGenerates errorWhy is this an error?Typical of DataGuardEverything seems to be an errorEven when it is perfectly routineMakes support more difficultWhen is an error something to worry about?www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 20 Standby Alert LogTue Oct 9 16:34:34 2007 Physical Standby Database mounted. Completed: ALTER DATABASE MOUNT Tue Oct 9 16:34:36 2007 alter database recover managed standby database disconnect from session Tue Oct 9 16:34:36 2007 Attempt to start background Managed Standby Recovery process (BRHBETA) MRP0 started with pid=11, OS id=13474 Tue Oct 9 16:34:36 2007 MRP0: Background Managed Standby Recovery process started (BRHBETA) Managed Standby Recovery not using Real Time Apply parallel recovery started with 7 processes Log apply process started when physical standby createdwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 21 Standby Alert LogWed Oct 10 10:15:15 2007 alter database recover managed standby database cancel Wed Oct 10 10:15:19 2007 MRP0: Background Media Recovery cancelled with status 16037 Wed Oct 10 10:15:19 2007 Errors in file /orahome01/admin/BRHBETA/bdump/brhbeta_mrp0_13474.trc: ORA-16037: user requested cancel of managed recovery operation Recovery interrupted! Wed Oct 10 10:15:20 2007 Errors in file /orahome01/admin/BRHBETA/bdump/brhbeta_mrp0_13474.trc: ORA-16037: user requested cancel of managed recovery operation Wed Oct 10 10:15:20 2007 MRP0: Background Media Recovery process shutdown (BRHBETA) Wed Oct 10 10:15:21 2007 Managed Standby Recovery Canceled (BRHBETA) Wed Oct 10 10:15:21 2007 Completed: alter database recover managed standby database cancel Log apply process stopped preparing to convert to logical standbywww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 22 Trace File$ more /orahome01/admin/BRHBETA/bdump/brhbeta_mrp0_13474.trc /orahome01/admin/BRHBETA/bdump/brhbeta_mrp0_13474.trc Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /orahome01/product/10.2.0 System name: SunOS Node name: brh-beta1-zone04 Release: 5.10 Version: Generic_118833-36 Machine: sun4u Instance name: BRHBETA Redo thread mounted by this instance: 1 Oracle process number: 11 Unix process pid: 13474, image: oraclebeta1-zone04 (MRP0) * SERVICE NAME:() 2007-10-09 16:34:36.298 * SESSION ID:(394.1) 2007-10-09 16:34:36.298 ARCH: Connecting to console port. * 2007-10-09 16:34:36.299 60639 kcrr.c MRP0: Background Managed Standby Recovery process started Start applying redo logs to physical standbywww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 23 Trace File* 2007-10-09 16:34:41.302 1018 krsm.c Managed Recovery: Initialization posted. * 2007-10-09 16:34:41.303 60639 kcrr.c Managed Standby Recovery not using Real Time Apply Recovery target incarnation = 2, activation ID = 0 Influx buffer limit = 27762 (50% x 55524) Successfully allocated 7 recovery slaves Using 158 overflow buffers per recovery slave Start recovery at thread 1 ckpt scn 8257757517457 logseq 1956 block 5 * 2007-10-09 16:34:42.124 Media Recovery add redo thread 1 * 2007-10-09 16:34:42.124 1018 krsm.c Managed Recovery: Active posted. ORA-00367: checksum error in log file header ORA-00305: log 1 of thread 1 inconsistent; belongs to another database ORA-00312: online log 1 thread 1: /shared/oralogs01/BRHBETA/redo01a.log * 2007-10-09 16:34:42.147 60639 kcrr.c Clearing online redo logfile 1 /shared/oralogs01/BRHBETA/redo01a.log * 2007-10-09 16:36:15.066 * 2007-10-09 16:36:15.066 60639 kcrr.c Clearing online redo logfile 1 complete ORA-00367: checksum error in log file header ORA-00305: log 2 of thread 1 inconsistent; belongs to another database ORA-00312: online log 2 thread 1: /shared/oralogs01/BRHBETA/redo02a.logRecreating redo logswww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 24 Trace File* 2007-10-09 16:36:15.100 60639 kcrr.c Clearing online redo logfile 2 /shared/oralogs01/BRHBETA/redo02a.log * 2007-10-09 16:37:51.473 * 2007-10-09 16:37:51.473 60639 kcrr.c Clearing online redo logfile 2 complete ORA-00367: checksum error in log file header ORA-00305: log 3 of thread 1 inconsistent; belongs to another database ORA-00312: online log 3 thread 1: /shared/oradata02/BRHBETA/redo03b.log * 2007-10-09 16:37:51.479 60639 kcrr.c Clearing online redo logfile 3 /shared/oradata02/BRHBETA/redo03b.log * 2007-10-09 16:39:26.048 * 2007-10-09 16:39:26.048 60639 kcrr.c Clearing online redo logfile 3 complete ORA-00367: checksum error in log file header ORA-00305: log 4 of thread 1 inconsistent; belongs to another database ORA-00312: online log 4 thread 1: /shared/oradata02/BRHBETA/redo04b.log * 2007-10-09 16:39:26.488 60639 kcrr.c Clearing online redo logfile 4 /shared/oradata02/BRHBETA/redo04b.log * 2007-10-09 16:41:00.447 * 2007-10-09 16:41:00.447 60639 kcrr.c Clearing online redo logfile 4 complete * 2007-10-09 16:41:00.469 60639 kcrr.c Media Recovery Waiting for thread 1 sequence 1956 www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 25 Trace File* 2007-10-09 16:41:00.469 60639 kcrr.c Fetching gap sequence in thread 1, gap sequence 1956-1976 * 2007-10-09 16:41:30.782 - Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter is defined to a value that is sufficiently large enough to maintain adequate log switch information to resolve archivelog gaps. - * 2007-10-09 16:54:31.045 * 2007-10-09 16:54:31.045 60639 kcrr.c Fetching gap sequence in thread 1, gap sequence 1956-1956 * 2007-10-09 16:55:01.154 - Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter is defined to a value that is sufficiently large enough to maintain adequate log switch information to resolve archivelog gaps. - * 2007-10-09 16:56:31.179 Media Recovery Log /oraarch01/BRHBETA/LOG_1956_1_629245032.arc * 2007-10-09 16:56:33.431 Media Recovery Log /oraarch01/BRHBETA/LOG_1957_1_629245032.arc * 2007-10-09 16:56:44.495 Applying redo logs to physical standbywww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 26 Trace File* 2007-10-09 16:56:44.495 60639 kcrr.c Media Recovery Waiting for thread 1 sequence 1958 * 2007-10-09 16:56:44.495 60639 kcrr.c Fetching gap sequence in thread 1, gap sequence 1958-1976 * 2007-10-09 16:57:14.647 - Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter is defined to a value that is sufficiently large enough to maintain adequate log switch information to resolve archivelog gaps. - * 2007-10-09 17:05:14.785 Media Recovery Log /oraarch01/BRHBETA/LOG_1958_1_629245032.arc * 2007-10-09 17:05:18.043 60639 kcrr.c Media Recovery Waiting for thread 1 sequence 1959 * 2007-10-09 17:05:18.043 60639 kcrr.c Fetching gap sequence in thread 1, gap sequence 1959-1976 * 2007-10-09 17:05:48.284 - Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter is defined to a value that is sufficiently large enough to maintain adequate log switch information to resolve archivelog gaps. - www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 27 Trace File* 2007-10-09 17:07:18.309 Media Recovery Log /oraarch01/BRHBETA/LOG_1959_1_629245032.arc * 2007-10-09 17:07:21.114 Media Recovery Log /oraarch01/BRHBETA/LOG_1960_1_629245032.arc * 2007-10-09 17:07:22.945 Media Recovery Log /oraarch01/BRHBETA/LOG_1961_1_629245032.arc * 2007-10-09 17:07:27.300 Media Recovery Log /oraarch01/BRHBETA/LOG_1962_1_629245032.arc * 2007-10-09 17:07:29.637 Media Recovery Log /oraarch01/BRHBETA/LOG_1963_1_629245032.arc * 2007-10-09 17:07:29.709 60639 kcrr.c Media Recovery Waiting for thread 1 sequence 1964 * 2007-10-09 17:07:29.709 60639 kcrr.c Fetching gap sequence in thread 1, gap sequence 1964-1976 * 2007-10-09 17:07:59.858 - Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter is defined to a value that is sufficiently large enough to maintain adequate log switch information to resolve archivelog gaps. - * 2007-10-09 17:08:29.866 Media Recovery Log /oraarch01/BRHBETA/LOG_1964_1_629245032.arc www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 28 Trace File* 2007-10-09 17:08:31.924 Media Recovery Log /oraarch01/BRHBETA/LOG_1965_1_629245032.arc * 2007-10-09 17:09:12.510 Media Recovery Log /oraarch01/BRHBETA/LOG_1966_1_629245032.arc * 2007-10-09 17:09:21.050 Media Recovery Log /oraarch01/BRHBETA/LOG_1967_1_629245032.arc * 2007-10-09 17:09:40.234 Media Recovery Log /oraarch01/BRHBETA/LOG_1968_1_629245032.arc * 2007-10-09 17:09:45.055 Media Recovery Log /oraarch01/BRHBETA/LOG_1969_1_629245032.arc * 2007-10-09 17:09:50.572 Media Recovery Log /oraarch01/BRHBETA/LOG_1970_1_629245032.arc * 2007-10-09 17:09:58.968 Media Recovery Log /oraarch01/BRHBETA/LOG_1971_1_629245032.arc * 2007-10-09 17:10:03.922 Media Recovery Log /oraarch01/BRHBETA/LOG_1972_1_629245032.arc * 2007-10-09 17:10:13.196 Media Recovery Log /oraarch01/BRHBETA/LOG_1973_1_629245032.arc * 2007-10-09 17:10:21.927 Media Recovery Log /oraarch01/BRHBETA/LOG_1974_1_629245032.arc * 2007-10-09 17:10:34.064 Media Recovery Log /oraarch01/BRHBETA/LOG_1975_1_629245032.arc * 2007-10-09 17:10:42.420 60639 kcrr.c Media Recovery Waiting for thread 1 sequence 1976 www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 29 Trace File* 2007-10-09 17:10:42.421 60639 kcrr.c Fetching gap sequence in thread 1, gap sequence 1976-1976 * 2007-10-09 17:11:12.538 - Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter is defined to a value that is sufficiently large enough to maintain adequate log switch information to resolve archivelog gaps. - * 2007-10-09 17:12:42.563 Media Recovery Log /oraarch01/BRHBETA/LOG_1976_1_629245032.arc * 2007-10-09 17:12:45.563 Media Recovery Log /oraarch01/BRHBETA/LOG_1977_1_629245032.arc * 2007-10-09 17:12:48.534 Media Recovery Log /oraarch01/BRHBETA/LOG_1978_1_629245032.arc * 2007-10-09 17:13:00.505 Media Recovery Log /oraarch01/BRHBETA/LOG_1979_1_629245032.arc * 2007-10-09 17:13:02.054 Media Recovery Log /oraarch01/BRHBETA/LOG_1980_1_629245032.arc * 2007-10-09 17:13:03.231 Media Recovery Log /oraarch01/BRHBETA/LOG_1981_1_629245032.arc * 2007-10-09 17:13:03.902 Media Recovery Log /oraarch01/BRHBETA/LOG_1982_1_629245032.arc www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 30 Trace File* 2007-10-09 17:13:04.492 Media Recovery Log /oraarch01/BRHBETA/LOG_1983_1_629245032.arc * 2007-10-09 17:13:08.171 Media Recovery Log /oraarch01/BRHBETA/LOG_1984_1_629245032.arc * 2007-10-09 17:13:26.860 * 2007-10-09 17:13:26.860 60639 kcrr.c Media Recovery Waiting for thread 1 sequence 1985 * 2007-10-09 17:16:07.172 Media Recovery Log /oraarch01/BRHBETA/LOG_1985_1_629245032.arc * 2007-10-09 17:16:08.067 Media Recovery Log /oraarch01/BRHBETA/LOG_1986_1_629245032.arc * 2007-10-09 17:16:08.131 Media Recovery Log /oraarch01/BRHBETA/LOG_1987_1_629245032.arc * 2007-10-09 17:16:08.195 60639 kcrr.c Media Recovery Waiting for thread 1 sequence 1988 * 2007-10-09 17:16:13.202 Media Recovery Log /oraarch01/BRHBETA/LOG_1988_1_629245032.arc * 2007-10-09 17:16:13.268 60639 kcrr.c Media Recovery Waiting for thread 1 sequence 1989 * 2007-10-09 21:14:01.119 Media Recovery Log /oraarch01/BRHBETA/LOG_1989_1_629245032.arc * 2007-10-09 21:14:16.922 * 2007-10-09 21:14:16.922 60639 kcrr.c Media Recovery Waiting for thread 1 sequence 1990 www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 31 Trace File* 2007-10-10 09:32:33.399 * 2007-10-10 09:32:33.399 60639 kcrr.c Fetching gap sequence in thread 1, gap sequence 1990-1990 * 2007-10-10 09:33:05.187 Media Recovery Log /oraarch01/BRHBETA/LOG_1990_1_629245032.arc * 2007-10-10 09:33:22.505 Media Recovery Log /oraarch01/BRHBETA/LOG_1991_1_629245032.arc * 2007-10-10 09:33:22.570 Media Recovery Log /oraarch01/BRHBETA/LOG_1992_1_629245032.arc * 2007-10-10 09:33:22.631 Media Recovery Log /oraarch01/BRHBETA/LOG_1993_1_629245032.arc * 2007-10-10 09:33:22.693 Media Recovery Log /oraarch01/BRHBETA/LOG_1994_1_629245032.arc * 2007-10-10 09:33:22.761 Media Recovery Log /oraarch01/BRHBETA/LOG_1995_1_629245032.arc * 2007-10-10 09:33:22.807 Media Recovery Log /oraarch01/BRHBETA/LOG_1996_1_629245032.arc * 2007-10-10 09:33:22.864 Media Recovery Log /oraarch01/BRHBETA/LOG_1997_1_629245032.arc * 2007-10-10 09:33:22.918 Media Recovery Log /oraarch01/BRHBETA/LOG_1998_1_629245032.arc * 2007-10-10 09:33:23.199 Media Recovery Log /oraarch01/BRHBETA/LOG_1999_1_629245032.arc www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 32 Trace File* 2007-10-10 09:33:23.255 60639 kcrr.c Media Recovery Waiting for thread 1 sequence 2000 * 2007-10-10 10:11:07.685 Media Recovery Log /oraarch01/BRHBETA/LOG_2000_1_629245032.arc * 2007-10-10 10:11:08.422 60639 kcrr.c Media Recovery Waiting for thread 1 sequence 2001 * 2007-10-10 10:14:48.843 Media Recovery Log /oraarch01/BRHBETA/LOG_2001_1_629245032.arc * 2007-10-10 10:14:49.013 60639 kcrr.c Media Recovery Waiting for thread 1 sequence 2002 * 2007-10-10 10:15:19.072 * 2007-10-10 10:15:19.072 60639 kcrr.c MRP0: Background Media Recovery cancelled with status 16037 ORA-16037: user requested cancel of managed recovery operation - Redo read statistics for thread 1 - Read rate (ASYNC): 619732Kb in 63640.12s = 0.01 Mb/sec Total physical reads: 619732Kb Longest record: 28Kb, moves: 0/2001133 (0%) Change moves: 779641/4101685 (19%), moved: 141Mb Longest LWN: 1023Kb, moves: 117/175493 (0%), moved: 23Mb Last redo scn: 0x0782.a8f27f37 (8257761607479) - * 2007-10-10 10:15:19.088 Media Recovery drop redo thread 1 Stop Log Apply ProcessReady to convert to Logical Standbywww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 33 Trace File* 2007-10-10 10:15:20.864 1018 krsm.c Managed Recovery: Not Active posted. ORA-16037: user requested cancel of managed recovery operation ARCH: Connecting to console port. * 2007-10-10 10:15:20.871 60639 kcrr.c MRP0: Background Media Recovery process shutdown * 2007-10-10 10:15:20.871 1018 krsm.c oraarch01/BRHBETA $www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 34 Convert to Logical StandbySQL Apply ProcessWhen applying redo logsGenerates 2 trace filesWhat are they?Trace filesOne shows start of kcrrwkxSecond shows end of kcrrwkxWhat are these for?Neither show up in alert logBoth continue as long as SQL apply process runswww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 35 First Trace File/orahome01/admin/BRHBETA/bdump $ more brhbeta_arc0_13168.trc /orahome01/admin/BRHBETA/bdump/brhbeta_arc0_13168.trc Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /orahome01/product/10.2.0 System name: SunOS Node name: brh-beta1-zone04 Release: 5.10 Version: Generic_118833-36 Machine: sun4u Instance name: BRHBETA Redo thread mounted by this instance: 1 Oracle process number: 24 Unix process pid: 13168, image: oraclebeta1-zone04 (ARC0) * SERVICE NAME:() 2007-10-10 10:40:26.358 * SESSION ID:(188.2) 2007-10-10 10:40:26.358 kcrrwkx: nothing to do (start) * 2007-10-10 10:45:26.240 kcrrwkx: nothing to do (start) * 2007-10-10 10:46:35.388 kcrrwkx: nothing to do (end) . . www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 36 Second Trace File/orahome01/admin/BRHBETA/bdump $ more brhbeta_arc1_13170.trc /orahome01/admin/BRHBETA/bdump/brhbeta_arc1_13170.trc Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /orahome01/product/10.2.0 System name: SunOS Node name: brh-beta1-zone04 Release: 5.10 Version: Generic_118833-36 Machine: sun4u Instance name: BRHBETA Redo thread mounted by this instance: 1 Oracle process number: 9 Unix process pid: 13170, image: oraclebeta1-zone04 (ARC1) * SERVICE NAME:() 2007-10-10 10:40:26.358 * SESSION ID:(396.1) 2007-10-10 10:40:26.358 kcrrwkx: nothing to do (start) * 2007-10-10 10:41:26.315 kcrrwkx: nothing to do (end) * 2007-10-10 10:42:26.322 kcrrwkx: nothing to do (end) www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 37 DataGuard Likes to ChatPhysical StandbyWhile applying archived redo logsTrace file documents everything standby doesLogical StandbyOnce converted to logical standbyTwo trace files generatedContain messages for start/stop of each log applyWhy are these generated?Why not have DataGuard alert logs?Trace files tell me that something is wrongwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 38 Normal OperationLogical Standby catching up to PrimaryApply process turned off during the dayCatches up at nightApply process failedCatch up after fix (skip table in the example)Typical alert log messagesRedo log from primary registered with DGRedo logs applied to standbyRedo logs deleted from standbywww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 39 Standby Catching UpTue Oct 16 15:13:22 2007 Completed: ALTER DATABASE STOP LOGICAL STANDBY APPLY Tue Oct 16 15:14:16 2007 Incremental checkpoint up to RBA 0x7.a0aa2.0, current log tail at RBA 0x7.b8e2c.0 Tue Oct 16 15:14:45 2007 ALTER DATABASE START LOGICAL STANDBY APPLY Tue Oct 16 15:14:45 2007 ALTER DATABASE START LOGICAL STANDBY APPLY (BRHBETA) Tue Oct 16 15:14:45 2007 No optional part Attempt to start background Logical Standby process LSP0 started with pid=21, OS id=5041 LOGSTDBY status: ORA-16111: log mining and apply setting up Tue Oct 16 15:14:46 2007 LOGMINER: Parameters summary for session# = 1 LOGMINER: Number of processes = 3, Transaction Chunk Size = 201 LOGMINER: Memory Size = 30M, Checkpoint interval = 150M Tue Oct 16 15:14:46 2007 Completed: ALTER DATABASE START LOGICAL STANDBY APPLY LOGMINER: session# = 1, builder process P001 started with pid=7 OS id=10018 LOGMINER: session# = 1, reader process P000 started with pid=34 OS id=10014 LOGMINER: session# = 1, preparer process P002 started with pid=36 OS id=10020 LSP2 started with pid=23, OS id=5043 Stop SQL Apply processStart SQL Apply process after skipping tablewww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 40 Standby Catching UpTue Oct 16 15:14:48 2007 LOGMINER: Begin mining logfile: /oraarch01/BRHBETA/LOG_2048_1_629245032.arc LOGSTDBY Analyzer process P003 started with pid=13 OS id=10051 Tue Oct 16 15:14:48 2007 LOGMINER: Turning ON Log Auto Delete LOGSTDBY Apply process P004 started with pid=40 OS id=10054 LOGSTDBY Apply process P006 started with pid=42 OS id=10062 LOGSTDBY Apply process P007 started with pid=17 OS id=10064 LOGSTDBY Apply process P005 started with pid=15 OS id=10060 Tue Oct 16 15:22:02 2007 Beginning log switch checkpoint up to RBA 0x8.2.10, SCN: 8295181217591 Thread 1 advanced to log sequence 8 Current log# 4 seq# 8 mem# 0: /shared/oradata02/BRHBETA/redo04b.log Current log# 4 seq# 8 mem# 1: /shared/oralogs01/BRHBETA/redo04a.log Tue Oct 16 15:25:28 2007 Completed checkpoint up to RBA 0x8.2.10, SCN: 8295181217591 Tue Oct 16 15:34:32 2007 Incremental checkpoint up to RBA 0x8.4cbae.0, current log tail at RBA 0x8.65553.0 Tue Oct 16 15:42:40 2007 LOGMINER: End mining logfile: /oraarch01/BRHBETA/LOG_2048_1_629245032.arc Tue Oct 16 15:42:40 2007 LOGMINER: Begin mining logfile: /oraarch01/BRHBETA/LOG_2049_1_629245032.arc . . . Processing redo logswww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 41 Standby Catching UpTue Oct 16 17:20:48 2007 LOGMINER: End mining logfile: /oraarch01/BRHBETA/LOG_2049_1_629245032.arc Tue Oct 16 17:20:48 2007 LOGMINER: Begin mining logfile: /oraarch01/BRHBETA/LOG_2050_1_629245032.arc Tue Oct 16 17:20:54 2007 LOGMINER: End mining logfile: /oraarch01/BRHBETA/LOG_2050_1_629245032.arc . . . Tue Oct 16 18:39:13 2007 LOGMINER: Log Auto Delete - deleting: /oraarch01/BRHBETA/LOG_2048_1_629245032.arc Deleted file /oraarch01/BRHBETA/LOG_2048_1_629245032.arc . . . Tue Oct 16 18:43:40 2007 LOGMINER: Begin mining logfile: /oraarch01/BRHBETA/LOG_2082_1_629245032.arc Tue Oct 16 18:43:59 2007 LOGMINER: End mining logfile: /oraarch01/BRHBETA/LOG_2082_1_629245032.arc Tue Oct 16 18:43:59 2007 LOGMINER: Begin mining logfile: /oraarch01/BRHBETA/LOG_2083_1_629245032.arc Processing redo logsDeleting redo logsProcessing redo logswww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 42 Standby Catching UpTue Oct 16 18:44:01 2007 LOGMINER: Log Auto Delete - deleting: /oraarch01/BRHBETA/LOG_2056_1_629245032.arc Deleted file /oraarch01/BRHBETA/LOG_2056_1_629245032.arc Tue Oct 16 18:44:01 2007 LOGMINER: Log Auto Delete - deleting: /oraarch01/BRHBETA/LOG_2057_1_629245032.arc Deleted file /oraarch01/BRHBETA/LOG_2057_1_629245032.arc Tue Oct 16 18:44:01 2007 LOGMINER: Log Auto Delete - deleting: /oraarch01/BRHBETA/LOG_2058_1_629245032.arc Deleted file /oraarch01/BRHBETA/LOG_2058_1_629245032.arc . Deleting redo logswww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 43 Standby Catching UpTue Oct 16 18:44:15 2007 LOGMINER: Begin mining logfile: /oraarch01/BRHBETA/LOG_2087_1_629245032.arc Tue Oct 16 18:48:37 2007 Completed checkpoint up to RBA 0xa.2.10, SCN: 8295181577382 Tue Oct 16 18:55:18 2007 Incremental checkpoint up to RBA 0xa.12dad.0, current log tail at RBA 0xa.1314b.0 Tue Oct 16 19:01:31 2007 RFS1: No standby redo logfiles created RFS1: Archived Log: /oraarch01/BRHBETA/LOG_2153_1_629245032.arc Tue Oct 16 19:01:32 2007 RFS LogMiner: Registered logfile /oraarch01/BRHBETA/LOG_2153_1_629245032.arc to LogMiner session id 1 Tue Oct 16 19:15:22 2007 Incremental checkpoint up to RBA 0xa.142b2.0, current log tail at RBA 0xa.143fe.0 Tue Oct 16 19:29:01 2007 LSP0: warning - apply server 2, sid 384 waiting on user sid 196 for event (since 0 seconds): Tue Oct 16 19:29:01 2007 LOGMINER: End mining logfile: /oraarch01/BRHBETA/LOG_2087_1_629245032.arc . Primary is at 2153Standby is at 2087www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 44 Standby Catching UpTue Oct 16 19:30:58 2007 LOGSTDBY stmt: CREATE PFILE = /tmp/datatools/BRHBETA.PFILE.19144.1192413665 FROM SPFILE = LOGSTDBY status: ORA-16226: DDL skipped due to lack of support LOGSTDBY id: XID 0x0003.02d.00013e70, hSCN 0x0782.a9c2fdb8, lSCN 0x0782.a9c2fdb8, Thread 1, RBA LOGSTDBY stmt: create pfile=/orahome01/oradba/tmp/ora_adm_sqlbt_bkp.tmp1.17449.BRHBETA from spfile LOGSTDBY status: ORA-16226: DDL skipped due to lack of support LOGSTDBY id: XID 0x000b.001.000126cf, hSCN 0x0782.a9c2fe15, lSCN 0x0782.a9c2fe15, Thread 1, RBA LOGSTDBY stmt: CREATE PFILE = /tmp/datatools/BRHBETA.PFILE.19695.1192413687 FROM SPFILE = LOGSTDBY status: ORA-16226: DDL skipped due to lack of support LOGSTDBY id: XID 0x0003.00c.00013e62, hSCN 0x0782.a9c2fe4a, lSCN 0x0782.a9c2fe4a, Thread 1, RBA LOGSTDBY stmt: ALTER DATABASE BACKUP CONTROLFILE TO /tmp/datatools/dtodump_LOGSTDBY status: ORA-16226: DDL skipped due to lack of support LOGSTDBY id: XID 0x0009.007.00011453, hSCN 0x0782.a9c2feb4, lSCN 0x0782.a9c2feb4, Thread 1, RBA Tue Oct 16 19:30:58 2007 ALTER TABLESPACE SYSTEM BEGIN BACKUP Completed: ALTER TABLESPACE SYSTEM BEGIN BACKUP Tue Oct 16 19:30:58 2007 ALTER TABLESPACE SYSTEM END BACKUP Completed: ALTER TABLESPACE SYSTEM END BACKUP . Unsupported DDL Standby doesnt executewww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 45 Standby Catching UpTue Oct 16 21:29:19 2007 LOGMINER: Begin mining logfile: /oraarch01/BRHBETA/LOG_2157_1_629245032.arc Tue Oct 16 21:30:03 2007 LOGMINER: End mining logfile: /oraarch01/BRHBETA/LOG_2157_1_629245032.arc Tue Oct 16 21:35:52 2007 Incremental checkpoint up to RBA 0xa.f41b7.0, current log tail at RBA 0xa.f41cc.0 Tue Oct 16 21:55:56 2007 Incremental checkpoint up to RBA 0xa.f43b5.0, current log tail at RBA 0xa.f43b5.0 Tue Oct 16 22:11:16 2007 RFS1: No standby redo logfiles created RFS1: Archived Log: /oraarch01/BRHBETA/LOG_2158_1_629245032.arc Tue Oct 16 22:11:16 2007 RFS LogMiner: Registered logfile /oraarch01/BRHBETA/LOG_2158_1_629245032.arc to LogMiner session id 1 Tue Oct 16 22:11:16 2007 LOGMINER: Begin mining logfile: /oraarch01/BRHBETA/LOG_2158_1_629245032.arc Tue Oct 16 22:11:20 2007 LOGMINER: End mining logfile: /oraarch01/BRHBETA/LOG_2158_1_629245032.arc Standby catches up at 2158www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 46 Archived Redo LogsLogical StandbyAfter applied to standbySQL apply process does delete themUnlike physical standbyPossible disk space issues on standbyHow long will you need to store redo logs?If standby frozen all dayWeekends? Holidays?If standby failsHow many days to fix failures?www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 47 Archived Redo LogsHow long are redo logs available on primary?If not on disk when needed for standbyRecover from backupDataguard may not see these redo logsRegister redo logsLogical standbyAlso generates its own archived redo logsNeeded to recover standby dbUnique standby db objects?www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 48 SQL Apply Process SlowDetect longrunning transactionCompute estimate of time to completeIdentify and skip problem tablewww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 49 Long Running TransactionStandby Alert LogSQL apply process applying redo log 2049Doesnt move on within a few minutesCurrent time is Tue Oct 16 08:09:55 2007 Shows start time for this redo logHas been processing for over 24 hoursMon Oct 15 05:52:29 2007 LOGMINER: Begin mining logfile: /oraarch01/BRHBETA/LOG_2049_1_629245032.arc www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 50 Long Running TransactionWhat is apply process doing?Check redo logs waiting to be appliedWhere is processing in current redo log?How long to complete current redo log?www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 51 Long Running Transactionalter session set nls_date_format = DDMonYYYY hh24:mi:ss; column first_change# format 99999999999999999999 column next_change# format 99999999999999999999 column resetlogs_change# format 99999999999999999999 select * from dba_logstdby_log; BRHBETA SELECT TYPE, STATUS, HIGH_SCN FROM V$LOGSTDBY; TYPE STATUS HIGH_SCN COORDINATOR ORA16116: no work available 8257767540953 READER ORA16127: stalled waiting for additional transactions to be applied 8257767541085 BUILDER ORA16127: stalled waiting for additional transactions to be applied 8257767540965 www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 52 Long Running TransactionPREPARER ORA16127: stalled waiting for additional transactions to be applied 8257767540965 ANALYZER ORA16117: processing 8257767540953 APPLIER ORA16116: no work available 8257767539467 APPLIER ORA16116: no work available 8257767512259 APPLIER ORA16113: applying change to table or sequence BRH.XXSUN_BRH_COMPS_INT 8257767539247 APPLIER ORA16116: no work available 8257767512262 9 rows selected. BRHBETA www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 53 Long Running TransactionBRHBETA select * from dba_logstdby_log; THREAD# RESETLOGS_CHANGE# RESETLOGS_ID SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# - - - - - - - - FILE_NAME TIMESTAMP DIC DIC APPLIED 1 8257200902826 629245032 2048 8257767447753 8257767534297 12-Oct-2007 22:41:05 12-Oct-2007 23:18:23 /oraarch01/BRHBETA/LOG_2048_1_629245032.arc 12-Oct-2007 22:19:08 NO NO CURRENT 1 8257200902826 629245032 2049 8257767534297 8257767754044 12-Oct-2007 23:18:23 13-Oct-2007 00:11:05 /oraarch01/BRHBETA/LOG_2049_1_629245032.arc 12-Oct-2007 23:12:18 NO NO CURRENT 1 8257200902826 629245032 2050 8257767754044 8257767922751 13-Oct-2007 00:11:05 13-Oct-2007 01:11:05 /oraarch01/BRHBETA/LOG_2050_1_629245032.arc 13-Oct-2007 00:11:06 NO NO NO . . . 1 8257200902826 629245032 2140 8257781397314 8257781562968 16-Oct-2007 07:41:15 16-Oct-2007 08:41:15 /oraarch01/BRHBETA/LOG_2140_1_629245032.arc 16-Oct-2007 07:41:16 NO NO NO 93 rows selected. www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 54 Long Running Transactionredo log 2049 goes from SCN 8257767534297 to SCN 8257767754044 Check again Tue Oct 16 15:04:29 MST 2007 Compute EstimateTue Oct 16 08:09:55 Tue Oct 16 11:17:39APPLIER has moved from 39247 to 399913 hours roughly 750 SCNs, 250 per hourit still needs to go from 539991 to 754044over 200,000 SCNs at 250 per hour, this would take 800 hours 33 days APPLIER ORA-16113: applying change to table or sequence BRH.XXSUN_BRH_COMPS_INT 8257767540857 www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 55 Long Running TransactionCheck again Tue Oct 16 15:04:29 MST 2007APPLIER has moved39991 to 40857in the last 4 hours, 866 SCNs, roughly in line with 250/hr we computed earlierAPPLIER ORA-16113: applying change to table or sequence BRH.XXSUN_BRH_COMPS_INT 8257767540857www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 56 Long Running TransactionThis is truly awful science!Assumes all SCNs take same amount of timeIf processing takes more than a few minutesCompute estimateConfirm that it will take a long timeCompare with business requirements for standbyMust be in synch once per dayDecide to skip tableIf table required, must wait or full refreshwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 57 Long Running TransactionSkip tableSQL Apply Process restarts with redo 2048Standby catches up quicklyALTER DATABASE STOP LOGICAL STANDBY APPLY; EXECUTE DBMS_LOGSTDBY.SKIP (stmt = DML , schema_name = BRH , object_name = XXSUN_BRH_COMPS_INT, proc_name = null); ALTER DATABASE START LOGICAL STANDBY APPLY; www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 58 Primary/Standby InteractionsLogical standby backup startsTablespaces put into backup modeApply process applies redo logs from primaryContain transactions for primary backupTries to put tablespaces into backup modeApply process failsWait for standby backup to finishRestart apply processDisable standby backups when catching upApply process runs longer than normalwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 59 Unique Constraint ViolationOracle calls thisOscillating updatesOracle docs explain this (I cant)Or primary update really did failAnd was rolled back on primary dbFails and rolls back in standby dbSQL apply process restartsAutomaticallyNo need to do anythingwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 60 Unique Constraint ViolationTue Oct 16 21:23:42 2007 LOGMINER: Begin mining logfile: /oraarch01/BRHBETA/LOG_2147_1_629245032.arc . . . Tue Oct 16 21:24:31 2007 LOGSTDBY stmt: insert into APPLSYS.WF_LOCAL_ROLES values “COL1 = Value1, “COL2 = Value2, “COL3 IS NULL, LOGSTDBY status: ORA-00001: unique constraint (APPLSYS.WF_LOCAL_ROLES_U1) violated LOGSTDBY id: XID 0x0009.016.00011548, hSCN 0x0782.aa32b533, lSCN 0x0782.aa32b533, Thread 1, RBA Tue Oct 16 21:25:20 2007 LOGMINER: End mining logfile: /oraarch01/BRHBETA/LOG_2147_1_629245032.arc SQL Apply Process continues processingwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 61 No Data FoundWhat does it mean?When DataGuard updates standbyBrings update from primaryBrings preupdate data from primaryOn standby, DataGuard comparesPreupdate data from primaryCurrent data on standbyIf they dont agreeDataGuard wont apply the update on standbySQL apply process failswww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 62 No Data FoundWed Sep 19 12:09:23 2007 LOGSTDBY stmt: update PO.PO_LINE_LOCATIONS_ALL . .SQL, values . LOGSTDBY status: ORA01403: no data found LOGSTDBY id: XID 0x0008.01e.0000c437, hSCN 0x0789.eacde6c1, lSCN 0x0789.eacde6c1 LOGSTDBY Apply process P007 pid=29 OS id=3447 stopped Wed Sep 19 12:09:23 2007 Errors in file /shared/orahome01/admin/BRHPRSB/bdump/brhprsb_lsp0_12386.trc: ORA12801: error signaled in parallel query server P004 ORA01403: no data found LOGSTDBY Analyzer process P003 pid=24 OS id=3439 stopped LOGSTDBY Apply process P006 pid=27 OS id=3445 stopped LOGSTDBY Apply process P005 pid=26 OS id=3443 stopped www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 63 No Data FoundWhat happened?For some reasonTable data not the same primary vs standbyHow could this happen?Logical standby is readwriteSYS can change anything at any timeSYS left guard status at NONEOther db users can make changes in standbyHow to fix?Skip tableRefresh logical standbywww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 64 No Data FoundLogical StandbyNo way to find out what happenedNo utility to verify primary, standby in synchDifferences can exist for a long timeWont cause error until table updated on primaryLogical Standby for reporting?Can you depend on this for your reports?How do you know what is in the standby?What has been skipped?www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 65 Primary Schema IssuesPrimary dbXDB schema reinstalledCreate java class (loads java class from filesystem)Standby dbTransactions came through to standbyStandby doesnt have java class filesApply process failsIdentify and skip transaction(s)www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 66 ORA-07445 ErrorsSR openedResultsKnown bug fixed in 11gApply patch on standbyImpactNone, no affect on standbyApply patch?No refresh would wipe out patchDont want to patch primary dbPrimary doesnt have this errorwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 67 ORA-07445 ErrorsTue Oct 16 21:27:50 2007 Errors in file /orahome01/admin/BRHBETA/bdump/brhbeta_p004_6577.trc: ORA-07445: exception encountered: core dump krvsmso()+1212 SIGSEGV Address not mapped to object Tue Oct 16 21:29:06 2007 Errors in file /orahome01/admin/BRHBETA/bdump/brhbeta_lsp0_5041.trc: ORA-12805: parallel query server died unexpectedly Tue Oct 16 21:29:06 2007 TLCR process death detected. Shutting down TLCR logminer process death detected, exiting logical standby LOGSTDBY Analyzer process P003 pid=13 OS id=10051 stopped LOGSTDBY Apply process P005 pid=15 OS id=10060 stopped LOGSTDBY Apply process P006 pid=42 OS id=10062 stopped LOGSTDBY Apply process P007 pid=17 OS id=10064 stopped Tue Oct 16 21:29:06 2007 LOGSTDBY status: ORA-16222: automatic Logical Standby retry of last action LOGSTDBY status: ORA-16111: log mining and apply setting up SQL Apply Process stopsSQL Apply Process automatically restartsLogical Standby is not for the faint of heart!www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 68 ORA-07445 ErrorsTue Oct 16 21:29:07 2007 LOGMINER: Parameters summary for session# = 1 LOGMINER: Number of processes = 3, Transaction Chunk Size = 201 LOGMINER: Memory Size = 30M, Checkpoint interval = 150M LOGMINER: session# = 1, builder process P001 started with pid=7 OS id=10018 LOGMINER: session# = 1, reader process P000 started with pid=34 OS id=10014 LOGMINER: session# = 1, preparer process P002 started with pid=36 OS id=10020 Tue Oct 16 21:29:10 2007 LOGMINER: Begin mining logfile: /oraarch01/BRHBETA/LOG_2147_1_629245032.arc Tue Oct 16 21:29:10 2007 LOGMINER: Turning ON Log Auto Delete LOGSTDBY Analyzer process P003 started with pid=13 OS id=10051 LOGSTDBY Apply process P006 started with pid=42 OS id=10062 LOGSTDBY Apply process P004 started with pid=30 OS id=10219 LOGSTDBY Apply process P005 started with pid=15 OS id=10060 LOGSTDBY Apply process P007 started with pid=17 OS id=10064SQL Apply Process continues processingwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 69 Refresh ProcessExport unique standby db objectsScripts to recreateBackup primary dbCreate standby control fileRecover primary db backup on standbyUse standby control fileCreate physical standbyConvert to logical standbyImport unique standby db objectsRecreate with scriptswww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 70 Unsupported RecordORA16211SQL apply process failsMust skip table or refresh standbyOracle SR tells me to Add all column supplemental log group to tableRebuild standbyOr reinstantiate the tableNeeded for each tableNot an easy processwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 71 Unsupported RecordThu Oct 11 10:11:58 2007 LOGMINER: Log Auto Delete - deleting: /oraarch01/BRHBETA/LOG_2005_1_629245032.arc Deleted file /oraarch01/BRHBETA/LOG_2005_1_629245032.arc Thu Oct 11 10:15:55 2007 * LOGMINER WARNING - Invalidated 4 LCRs * Thu Oct 11 10:20:29 2007 LOGSTDBY stmt: BRH.XXSUN_INV_ITEMS_INT: unsupported LOGSTDBY status: ORA-16211: unsupported record found in the archived redo log ORA-06512: at SYS.DBMS_INTERNAL_LOGSTDBY, line 4717 ORA-06512: at line 1 LOGSTDBY id: XID 0x0009.02e.0001127d, hSCN 0x0782.a9016545, lSCN 0x0782.a9016545, Thread 1 LOGSTDBY Apply process P007 pid=23 OS id=16578 stopped Thu Oct 11 10:20:29 2007 Errors in file /orahome01/admin/BRHBETA/bdump/brhbeta_lsp0_13625.trc: ORA-12801: error signaled in parallel query server P007 ORA-16211: unsupported record found in the archived redo logLOGSTDBY Analyzer process P003 pid=19 OS id=16570 stopped LOGSTDBY Apply process P005 pid=21 OS id=16574 stopped LOGSTDBY Apply process P006 pid=36 OS id=16576 stopped LOGSTDBY Apply process P004 pid=34 OS id=16572 stoppedwww.brianhitchcock.netBrian Hitchcock October 23, 2007Page 72 Unsupported RecordWhat causes this?Metalink 304061.1Possible causesDirect path insert on partitioned tableTable has 500 columnsIs this a standby?At any time this error may happenHow to predict/prevent?www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 73 Compile Invalid ObjectsIn Logical StandbyExecute utlrp.sql2 hours go byNot much changedDisable Guard for sessionAlter session disable guardalter database guard standby;Recompile runs in 2 minutesAlter session enable guardWhen normal things dont workPerhaps guard enabled is the problemGuard level is the problem (all vs standby)www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 74 Import Into Logical StandbyFor recompile we usedAlter session disable guard;Refresh Logical StandbyUnique db objects exported before refreshMust be imported after refreshImport doesnt use SQL*Plus sessionAlter database guard standby;www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 75 ConclusionLogical standbyLots of errorsMany require refreshing standbyLots of DBA support neededFor all of this supportWhat do you have?Do you know what is in the standbyReporting?www.brianhitchcock.netBrian Hitchcock October 23, 2007Page 76 ConclusionPhysical standbyIs solid, dependableNo issuesLogical standbyIs it really a standby?Is it ready for failover?Is it providing complete data for reports?Lots of issuesIs it worth the effort/risk?