[Spacewalk-list] Spacewalk backups, restores, OS Updates, Oracle, and its bloody archivelogs (LONG)

[Ian Forde]

Sorry if any part of the subject offends, but I've just spent the last 4
days putting together a workable solution for what had appeared to be a
Spacewalk database crapping out.

Ingredients:
	Spacewalk server: CentOS 5.5
	Channels: CentOS 5 x86_64 OS and Updates, plus other misc channels

Problem: I wanted to upgrade my channels from CentOS 5.5 to 5.6.

The original plan: I thought I would empty the 5.5 base (OS) channel and
updates channel.  Then repopulate the channels with content from a local
mrepo server.

What went wrong: Some time ago, I setup backups on this box, which
requires archivelogs on the Oracle database to be enabled.  With such a
large number of updates, the contents
of /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/archivelog/ grew
to over 4GB, leading to ORA-00257 errors.

Thoughts:  I thought I'd crapped out the whole thing.  And I'm not a
DBA.  So I wanted to do a restore.  Imagine my surprise when, after
having setup backups, I didn't see anything about restore to a specific
day without having a DBA.

What I did wrong: Don't do what I did.  Turn off the archive logs first.

How I fixed it (bear in mind that I have mrepo on a nearby server):
1. Update mrepo content for OS and updates
2. Update the /var/distro-trees/ iso on the spacewalk server
3. Stop Spacewalk, disable my backup script, disable Spacewalk sync
channel updates, and disable Oracle archive logs
	spacewalk-service stop
	<other stuff to disable backup script and spacewalk sync goes here>
	su - oracle
		. /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh
		sqlplus /nolog
			connect / as sysdba
			shutdown immediate
			startup mount
			alter database noarchivelog;
			alter database open;
			select log_mode from sys.v$database;
			quit
		exit
	spacewalk-service start
4. Import new OS and update packages, knowing that you won't, at this
point, blow up the archive log.
5. Stop Spacewalk (spacewalk-service stop)
5. Remove old archivelogs, then reenable them
	su - oracle
		. /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh
		rman target sys/sys
			delete archivelog all;
			quit
		sqlplus /nolog
			connect / as sysdba
			shutdown immediate
			startup mount
			alter database archivelog;
			alter database open;
			select log_mode from sys.v$database;
			quit
		exit
6. Put things back to normal and make an IMMEDIATE DATABASE BACKUP
	spacewalk-service start
	<steps to enable backup script and spacewalk sync)
	MAKE SPACEWALK DATABASE BACKUP (I used the procedure from the wiki)

Moral of the story: Have a DBA nearby.  I did, but not one that knows
Oracle.  Note that this procedure worked for me.  It may cause others'
computers to spontaneously turn into pixie dust, or offer guided aerial
porcine tours over the frozen wastes of hades.  NO WARRANTIES EXPRESSED
OR IMPLIED.

What I'd like to know is if I went about it in a roundabout way or if
this is what one does when one doesn't have an Oracle backup scheme to
integrate into...

	-Ian