Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

ORA-00328 archived log ends at change 29604569404, need later change 29605086441

Posted by Hendry chinnapparaj on February 21, 2011

ORA-00328 archived log ends at change 29604569404, need later change 29605086441

Problem:-

The Dataguard / standby database has these errors when the MRP0 – Recover session was initiated.

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT from session

Sun Feb 20 14:04:25 2011

Media Recovery Log /u03/oradata/flash_recovery_area/ZOEPRD_JS/archivelog/2011_02_20/o1_mf_1_22383_1FJ-7GqbN_.arc

Errors with log /u03/oradata/flash_recovery_area/ZOEPRD_JS/archivelog/2011_02_20/o1_mf_1_22383_1FJ-7GqbN_.arc

MRP0: Background Media Recovery terminated with error 328

Sun Feb 20 14:04:25 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_mrp0_790780.trc:

ORA-00328: archived log ends at change 29604569404, need later change 29605086441

ORA-00334: archived log: ‘/u03/oradata/flash_recovery_area/ZOEPRD_JS/archivelog/2011_02_20/o1_mf_1_22383_1FJ-7GqbN_.arc’

Recovery interrupted!

Sun Feb 20 14:04:26 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_mrp0_790780.trc:

ORA-00328: archived log ends at change 29604569404, need later change 29605086441

ORA-00334: archived log: ‘/u03/oradata/flash_recovery_area/ZOEPRD_JS/archivelog/2011_02_20/o1_mf_1_22383_1FJ-7GqbN_.arc’

Sun Feb 20 14:04:26 2011

MRP0: Background Media Recovery process shutdown (zoeprd)

Cause:-

The issue can be due to many reasons realting to archived logs and SCN having wrong meta entry / data.

Standby Oracle / control file is looking for a specific Archivelog / specific SCN which it thinks is required for Recovery which  is not available in the archived log. Somewhere in the log shipping / transfer

things got distorted inside the log causing issues / .

Even if you copy the logs from primary and register / replace the on standby and apply them – still the standby controlfile cannot recover them

Even if you recreate standby controlfile and recover, still comes up with same errors

So what do you do finally ? try this one as it worked fo rme

Fix:-

12.7 Using RMAN Incremental Backups to Roll Forward a Physical Standby Database

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/scenarios.htm#CIHIAADC

In cases where a physical standby database is far behind the primary database, an RMAN incremental backup can be used to roll the standby database forward faster than redo log apply. In this procedure, the RMAN BACKUP INCREMENTAL FROM SCN command is used to create an incremental backup on the primary database that starts at the current SCN of the standby and is used to roll forward the standby database.

Note:

The steps in this section can also be used to resolve problems if a physical standby database has lost or corrupted archived redo data or has an unresolveable archive gap.

1.     On the standby database, stop the managed recovery process (MRP):

2.         SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3.     On the standby database, find the SCN which will be used for the incremental backup at the primary database:

4.         SQL> SELECT CURRENT_SCN FROM V$DATABASE;

SQL> column current_scn format 999999999999999
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

     CURRENT_SCN
----------------
     29615710642

5.     In RMAN, connect to the primary database and create an incremental backup from the SCN derived in the previous step:

6.         RMAN> BACKUP INCREMENTAL FROM SCN <SCN from previous step>
7.         DATABASE FORMAT '/u03/oradata/backup/ForStandby_%U' tag 'FORSTANDBY';

Note:

RMAN does not consider the incremental backup as part of a backup strategy at the source database. Hence:

o        The backup is not suitable for use in a normal RECOVER DATABASE operation at the source database

o        The backup is not cataloged at the source database

o        The backup sets produced by this command are written to the /dbs location by default, even if the flash recovery area or some other backup destination is defined as the default for disk backups.

o        You must create this incremental backup on disk for it to be useful. When you move the incremental backup to the standby database, you must catalog it at the standby as described in Oracle Database Backup and Recovery Advanced User’s Guide. Backups on tape cannot be cataloged.

Rman>

Run

{

ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT  ‘/u03/oradata/backup/ForStandby_%U’;

      BACKUP INCREMENTAL FROM SCN 29615710642 DATABASE FORMAT '/u03/oradata/backup/ForStandby_%U' tag 'FORSTANDBY';

}

input datafile fno=00646 name=/u04/oradata/zoeprd/CB151_01.DBF

input datafile fno=00657 name=/u02/oradata/zoeprd/CBIDX156_01.DBF

input datafile fno=00668 name=/u02/oradata/zoeprd/CSH067_01.DBF

channel disk1: starting piece 1 at 20-02-2011 12:44:34

channel disk1: finished piece 1 at 20-02-2011 12:48:59

piece handle=/u03/oradata/backup/ForStandby_58m5501i_1_1 tag=FORSTANDBY comment=NONE

channel disk1: backup set complete, elapsed time: 00:04:25

Finished backup at 20-02-2011 12:48:59

Starting Control File and SPFILE Autobackup at 20-02-2011 12:49:00

piece handle=/u03/oradata/flash_recovery_area/ZOEPRD_FC/autobackup/2011_02_20/o1_mf_s_743604540_1FJwU-jRQ_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 20-02-2011 12:49:02

released channel: disk1

8.     Transfer all backup sets created on the primary system to the standby system (note that there may be more than one backup file created). For example:

9.         SCP /tmp/ForStandby_* standby:/tmp
 
ftp drpu009n1
/u03/oradata/backup /u03/oradata/backup

10. Connect to the standby database as the RMAN target, and catalog all incremental backup pieces:

11.    RMAN> CATALOG START WITH '/tmp/ForStandby';

RMAN> catalog start with '/u03/oradata/backup';

searching for all files that match the pattern /u03/oradata/backup

List of Files Unknown to the Database
=====================================
File Name: /u03/oradata/backup/ForStandby_4sm54tbs_1_1
File Name: /u03/oradata/backup/ForStandby_4tm54tnb_1_1
File Name: /u03/oradata/backup/ForStandby_4um54u0u_1_1
File Name: /u03/oradata/backup/ForStandby_51m54ub5_1_1
File Name: /u03/oradata/backup/ForStandby_52m54ugm_1_1
File Name: /u03/oradata/backup/ForStandby_53m54uod_1_1
File Name: /u03/oradata/backup/ForStandby_54m54v05_1_1
File Name: /u03/oradata/backup/ForStandby_55m54v86_1_1
File Name: /u03/oradata/backup/ForStandby_56m54vft_1_1
File Name: /u03/oradata/backup/ForStandby_57m54vnu_1_1
File Name: /u03/oradata/backup/ForStandby_58m5501i_1_1

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u03/oradata/backup/ForStandby_4sm54tbs_1_1
File Name: /u03/oradata/backup/ForStandby_4tm54tnb_1_1
File Name: /u03/oradata/backup/ForStandby_4um54u0u_1_1
File Name: /u03/oradata/backup/ForStandby_51m54ub5_1_1
File Name: /u03/oradata/backup/ForStandby_52m54ugm_1_1
File Name: /u03/oradata/backup/ForStandby_53m54uod_1_1
File Name: /u03/oradata/backup/ForStandby_54m54v05_1_1
File Name: /u03/oradata/backup/ForStandby_55m54v86_1_1
File Name: /u03/oradata/backup/ForStandby_56m54vft_1_1
File Name: /u03/oradata/backup/ForStandby_57m54vnu_1_1
File Name: /u03/oradata/backup/ForStandby_58m5501i_1_1

12. Recover the standby database with the cataloged incremental backup:

13.    RMAN> RECOVER DATABASE NOREDO;

Generic Note

------------------------

Use RMAN> catalog backup piece and try the recovery. And when you do the recovery allocate the disk in RMAN as you have copied / ftp’ed these files to the disk

You might encounter these errors because the primary and standby archived logGs location are different. Either create the same directories as in primary or catalog these files

destination for restore of datafile 00647: /u02/oradata/zoeprd/CBIDX151_01.DBF
destination for restore of datafile 00658: /u04/oradata/zoeprd/CB157_01.DBF
channel ORA_DISK_2: reading from backup piece /u03/oradata/flash_recovery_area/ZOEPRD_FC/backupset/2011_02_20/o1_mf_nnnd1_TAG20110219T233049_1FJG22WSN_.bkp
ORA-19870: error reading backup piece /u03/oradata/flash_recovery_area/ZOEPRD_FC/backupset/2011_02_20/o1_mf_nnnd1_TAG20110219T233049_1FJG22WSN_.bkp
ORA-19505: failed to identify file "/u03/oradata/flash_recovery_area/ZOEPRD_FC/backupset/2011_02_20/o1_mf_nnnd1_TAG20110219T233049_1FJG22WSN_.bkp"
ORA-17503: ksfdopn:4 Failed to open file /u03/oradata/flash_recovery_area/ZOEPRD_FC/backupset/2011_02_20/o1_mf_nnnd1_TAG20110219T233049_1FJG22WSN_.bk
failover to copy on device type SBT_TAPE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/20/2011 13:20:20
ORA-19870: error reading backup piece 3pm53j82_1_2
ORA-19507: failed to retrieve sequential file, handle="3pm53j82_1_2", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
   ANS1302E (RC2)    No objects on server match query

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 20-02-2011 13:25:01

using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_DISK_1
using channel ORA_DISK_2
datafile 269 not processed because file is read-only
datafile 270 not processed because file is read-only
datafile 271 not processed because file is read-only
…
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/20/2011 13:26:29
ORA-27191: sbtinfo2 returned error
Additional information: 2

The above error shows – recover is not done and skips the read only datafiles as the SCN are still the same

14. In RMAN, connect to the primary database and create a standby control file backup:

15.    RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';

Run
{

ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT ‘/u03/oradata/backup/sttbyctl.bck’;

backup current controlfile for standby format '/u03/oradata/backup/stbyctl.bck';
}

allocated channel: disk1
channel disk1: sid=634 devtype=DISK

Starting backup at 20-02-2011 13:42:19
channel disk1: starting compressed full datafile backupset
channel disk1: specifying datafile(s) in backupset
including standby control file in backupset
channel disk1: starting piece 1 at 20-02-2011 13:42:20
channel disk1: finished piece 1 at 20-02-2011 13:42:23
piece handle=/u03/oradata/backup/stbyctl.bck tag=TAG20110220T134219 comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:03
Finished backup at 20-02-2011 13:42:23

Starting Control File and SPFILE Autobackup at 20-02-2011 13:42:24
piece handle=/u03/oradata/flash_recovery_area/ZOEPRD_FC/autobackup/2011_02_20/o1_mf_s_743607761_1FJzUyGbV_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-02-2011 13:42:43
released channel: disk1

16. Copy the standby control file backup to the standby system. For example:

17.    SCP /tmp/ForStandbyCTRL.bck standby:/tmp

:: No such file or directory
ftp> lcd /u03/oradata/backup
Local directory now /u03/oradata/backup
ftp> cd /u03/oradata/backup
250 CWD command successful.
ftp> binary
200 Type set to I.
ftp> put stbyctl.bck
200 PORT command successful.
150 Opening data connection for stbyctl.bck.
226 Transfer complete.
3260416 bytes sent in 0.144 seconds (2.211e+04 Kbytes/s)
local: stbyctl.bck remote: stbyctl.bck

18. Shut down the standby database and startup nomount:

19.    RMAN> SHUTDOWN;
20.    RMAN> STARTUP NOMOUNT;

21. In RMAN, connect to standby database and restore the standby control file:

22.    RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';

connected to target database: zoeprd (not mounted)
connected to recovery catalog database

RMAN> run
2> {
3> ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/u03/oradata/backup/sttbyctl.bck';
4> RESTORE STANDBY CONTROLFILE FROM '/u03/oradata/backup/stbyctl.bck';
5> }

allocated channel: disk1
channel disk1: sid=816 devtype=DISK

Starting restore at 20-02-2011 13:48:13

channel disk1: restoring control file
channel disk1: restore complete, elapsed time: 00:00:04
output filename=/u02/oradata/zoeprd/control01.ctl
output filename=/u03/oradata/zoeprd/control02.ctl
output filename=/u04/oradata/zoeprd/control03.ctl
Finished restore at 20-02-2011 13:48:22
released channel: disk1

23. Shut down the standby database and startup mount:

24.    RMAN> SHUTDOWN;
25.    RMAN> STARTUP MOUNT;

26. If the primary and standby database data file directories are identical, skip to step 13. If the primary and standby database data file directories are different, then in RMAN, connect to the standby database, catalog the standby data files, and switch the standby database to use the just-cataloged data files. For example:

27.    RMAN> CATALOG START WITH '+DATA_1/CHICAGO/DATAFILE/'; 
28.    RMAN> SWITCH DATABASE TO COPY;

29. If the primary and standby database redo log directories are identical, skip to step 14. Otherwise, on the standby database, use an OS utility or the asmcmd utility (if it is an ASM-managed database) to remove all online and standby redo logs from the standby directories and ensure that the LOG_FILE_NAME_CONVERT parameter is properly defined to translate log directory paths. For example, LOG_FILE_NAME_CONVERT='/BOSTON/','/CHICAGO/'.

30. On the standby database, clear all standby redo log groups (there may be more than 3):

31.    SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
32.    SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
33.    SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

SQL> column member format a50
SQL> l
  1* select * from v$logfile
SQL> /

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         9         STANDBY /u03/oradata/zoeprd/stdby_redo_09.log              NO
        10         STANDBY /u03/oradata/zoeprd/stdby_redo_10.log              NO
        11         STANDBY /u03/oradata/zoeprd/stdby_redo_11.log              NO
         4         ONLINE  /u02/oradata/zoeprd/redo_04a.log                   NO
         4         ONLINE  /u03/oradata/zoeprd/redo_04b.log                   NO
         5         ONLINE  /u02/oradata/zoeprd/redo_05a.log                   NO
         5         ONLINE  /u03/oradata/zoeprd/redo_05b.log                   NO
        12         STANDBY /u03/oradata/zoeprd/stdby_redo_12.log              NO
        13         STANDBY /u03/oradata/zoeprd/stdby_redo_13.log              NO
         6         ONLINE  /u02/oradata/zoeprd/redo_06a.log                   NO
         6         ONLINE  /u03/oradata/zoeprd/redo_06b.log                   NO
         7         ONLINE  /u02/oradata/zoeprd/redo_07a.log                   NO
         7         ONLINE  /u03/oradata/zoeprd/redo_07b.log                   NO

13 rows selected.

SQL> alter database clear logfile group 9;

Database altered.

SQL> alter database clear logfile group 10;

Database altered.

SQL> alter database clear logfile group 11;

Database altered.

SQL> alter database clear logfile group 12;

Database altered.

SQL> alter database clear logfile group 13;

Database altered.

34. On the standby database, restart Flashback Database:

35.    SQL> ALTER DATABASE FLASHBACK OFF; 
36.    SQL> ALTER DATABASE FLASHBACK ON;

37. On the standby database, restart MRP:

38.    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT from session;

Also recreate the block_change_tracking file if it’s used as it may be disabled.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: