Schemathings

One of the databases I support is an Oracle 10G instance on Windows 2003 Server running in a physical standby configuration. One thing I still need to tweak is how archive logs get deleted on the standby machine. I can’t see how to get RMAN to address the archivelogs on the other machine. Well, the other day the archivelog destination drive filled up on the standby, so the primary stopped shipping logfiles. The sysadmin noticed this and cleared out some older logfiles, but we had an archivelog gap – the archivelogs that were older than the retention period on the primary were no longer online in the flashback recovery area.

Fortunately we keep disk-based RMAN backups online as long as possible so I knew they were still embedded in the backupset somewhere.

Here’s the secret recipe for getting them back out:
Look at your OEM and figure out which logfile sequence numbers are in the gap.
Login to your RMAN and do this:
RMAN> list backupset;

— snip snip —

List of Archived Logs in backup set 40885
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——— ———- ———
1 12870 132214214 31-OCT-05 132240966 31-OCT-05
1 12871 132240966 31-OCT-05 132291702 01-NOV-05
1 12872 132291702 01-NOV-05 132400078 01-NOV-05
1 12873 132400078 01-NOV-05 132504581 01-NOV-05
1 12874 132504581 01-NOV-05 132594119 01-NOV-05
1 12875 132594119 01-NOV-05 132683505 01-NOV-05

— snip snip —

I am just showing a few of the Sequence numbers – the output is quite long.
Let’s say I needed 12872-1874.

RMAN> run {
2> ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
3> restore archivelog sequence 12872;
4> restore archivelog sequence 12873;
5> restore archivelog sequence 12874;
}

You’ll see something like this:
allocated channel: c1
channel c1: sid=2054 devtype=DISK

Starting restore at 04-NOV-05

channel c1: starting archive log restore to default destination
channel c1: restoring archive log
archive log thread=1 sequence=12872
channel c1: restored backup piece 1
piece handle=G:\BACKUP\ARCHIVE\ARC_DORAMYSID_T573243355_S1283_P1 tag=TAG20051101T181555
channel c1: restore complete
Finished restore at 04-NOV-05

Repeated for each recovered sequence …

What’s really slick is the second they are recovered, the primary will ship the archivelogs and the standby will apply them. Recovery from being 3-4 days behind on a very busy instance only took about 10 minutes at which point the standby was fully in sync again.

If they weren’t in the online backups, I would have had to look in RMAN to figure out which tapeset they were in, restore the backups, and run a CROSSCHECK to let Oracle know they were back online.

Someday I’ll configure RMAN to use the tape farm directly.