Data Guard 支持问题( 76 ).ppt

1、Oracle DataGuardSupport Issues,Brian HitchcockOCP 10g DBA Sun Microsystems ,Brian Hitchcock October 23, 2007,Page 1,Oracle DataGuard,Focus on Logical Standby Support Issues Maintains a standby database Archived redo logs on primary Sent to standby and applied Simple idea Many configuration options C

2、an become very complicated,DataGuard,Must be SYS to make changes Sqlplus / as sysdba Changes to DataGuard standby database Some cant be made while apply process running Change Guard status Support Issues Create physical standby Convert to logical standby After logical standby is running Refresh proc

3、ess,DataGuard Errors,DataGuard reports lot of errors Standby database alert log Many are for normal operation Why reported as errors? Monitoring of db alert log Will report these errors Hard to filter out normal errors,Create Physical Standby,On Primary database Enable Forced Logging Create password

4、 file Setup init.ora/spfile parameters Cant connect to standby SYS password Verify archiving enabled Backup db (hot or cold) Create standby control file,Create Physical Standby,On Standby database Copy db backup files from primary Copy standby control file from primary Setup init.ora/spfile paramete

5、rs Start physical standby db Trace file Verify physical standby working May not see redo logs, register them Redo logs not deleted, use RMAN,Convert to Logical Standby,On Primary database Build LogMiner dictionary On Standby database Stop redo apply Errors, no impact Convert database to logical stan

6、dby Two trace files Restart db Open resetlogs Verify logical standby working,Logical Standby is Running,Business requirements Standby frozen most of the day Standby catches up once per day Alert log messages while catching up Disk space for archived redo logs Other issues Apply process is slow How t

7、o detect, resolve Primary versus Standby backups Impact, resolution,Logical Standby is Running,Other Issues Constraint violations Errors, resolution No data found Errors, resolution ORA-16211 Errors, Oracle Support Primary db XDB schema issues Fixed on primary, errors on standby,Logical Standby is R

8、unning,Other issues ORA-07445 Refresh cures all Refresh process After refresh ORA-16211: unsupported record found in the archived redo log Compile invalid objects Import into standby database,Primary Cant Connect,Standby not available Reported on primary production database “ORACLE not available” Lo

9、oks like production primary is down Your monitoring may need to be adjusted,Thu 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: ORA-01034: ORACLE not available PINGARC1: Heartbeat

10、failed to connect to standby BRHPRSB. Error is 1034.,SYS Password Issue,Mon 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

11、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 conn

12、ect to standby,SYS Password Issue,Verify SYS password is the same On primary and standby Sqlplus sys/ Verify password file has same password On primary and standby Cat $ORACLE_HOME/dbs/orapw Refresh password file Alter user SYS identified by Update password file,DataGuard Trace File,Physical Standby

13、 Start log apply process Trace file created Stops when log apply process stops See file contents later,Cant See Redo Logs,Physical Standby Creating or Refreshing standby Primary configured, sending redo logs Standby not yet created/running Standby may not register redo logs Our scripts maintain prim

14、ary archived redo logs Compress to save disk space, delete after 2 days Manually register Alter database register logfile ; DataGuard applies redo log,Cant See Redo Logs,BRHBETA select * from v$archive_gap; THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# - - - 1 1959 1976 BRHBETA select sequence#, applied from

15、 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 standby,Redo Logs Not Deleted,Physical Standby After applied to standby Unlike logical standby SQL apply process does d

16、elete them Use RMAN Possible disk space issues on standby How long will you need to store redo logs? Not an issue if converting to logical soon,Stop Physical Standby,Log Apply Process start Starts trace file When physical standby first created Ends when log apply stops Normal processing Trace file l

17、ooks like a problem,Turn Off Apply Process,Physical standby Turn off apply process Regular maintenance Converting to Logical Standby Generates error Why is this an error? Typical of DataGuard Everything seems to be an error Even when it is perfectly routine Makes support more difficult When is an er

18、ror something to worry about?,Standby Alert Log,Tue 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 Stand

19、by 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 standb

20、y created,Standby Alert Log,Wed 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: u

21、ser 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 shut

22、down (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 standby,Trace File,$ more /orahome01/admin/BRHBETA/bdump/brhbeta_mr

23、p0_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 Ver

24、sion: 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

25、 port. * 2007-10-09 16:34:36.299 60639 kcrr.c MRP0: Background Managed Standby Recovery process started,Start applying redo logs to physical standby,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 Recove

26、ry 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

27、.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 * 2

28、007-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; be

29、longs to another database ORA-00312: online log 2 thread 1: /shared/oralogs01/BRHBETA/redo02a.log,Recreating redo logs,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

30、.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

31、 /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: /share

32、d/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 W

33、aiting for thread 1 sequence 1956,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 m

34、aintain 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 define

35、d 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 *

36、 2007-10-09 16:56:44.495,Applying redo logs to physical standby,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

37、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.

38、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 sufficie

39、ntly large enough to maintain adequate log switch information to resolve archivelog gaps. -,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:

40、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 Recover

41、y 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

42、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,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 /ora

43、arch01/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_6292450

44、32.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

45、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 Waiti

46、ng for thread 1 sequence 1976,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 maint

47、ain 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/

48、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

49、* 2007-10-09 17:13:03.902 Media Recovery Log /oraarch01/BRHBETA/LOG_1982_1_629245032.arc,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.

50、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 Med

51、ia 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

52、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,Trace File,* 2007-10-10 09:32:33.399 * 2007-10-10 09:32:33.399 60639

53、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/B

54、RHBETA/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 *

55、 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 Re

56、covery Log /oraarch01/BRHBETA/LOG_1999_1_629245032.arc,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 t