Learning By...Breaking?
I caught
Chen Gwen mocking me the other day on Twitter.
That was in response to
Learning By Breaking.
Come on. Give me a break.
Now if I were a big DBA (not a little dba), I would not be so haphazard in my approach. This is a sandbox after all.
Naturally, I was at it again today.
I performed my very first recovery today. That is if you consider
ALTER DATABASE RECOVER;
a recovery. To make it worse, I guessed at the syntax.
Just a reminder as to what I am doing. I am testing my migration script (a couple of hundred DDL/DML and code changes) in an object only copy of production in my own private idaho...sandbox. I run the script, find the errors (usually order of execution related), fix the script(s), flashback the database and then rerun. To make my life someone easier, I created a script that performs the necessary commands to flashback the database.
ALTER DATABASE CLOSE;
FLASHBACK DATABASE TO RESTORE POINT PRE_DEPLOY;
SHUTDOWN;
STARTUP;
ALTER DATABASE OPEN RESETLOGS;
Then I just run @flashback after disconnecting from my other session.
Somewhere along the way I believe I have managed to destroy my database. How did I do that? Well, I don't know. I do know I have accidentally dropped the restore point (2 times) prior to flashing back. When that happens, I clean up the database with one of 2 methods:
1. Use my handy-dandy
clean up script to remove all the objects
2. DROP USER test CASCADE;
That's followed by a full import (via datapump) of the 2 necessary schemas.
So tonight I went to flashback and...well, something went really wrong. Something about I needed to recover the system datafile (I didn't save that one unfortunately).
ALTER DATABASE RECOVER?
Nope.
ERROR at line 1:
ORA-00279: change 9235581 generated at 09/22/2009 21:24:09 needed for thread 1
ORA-00289: suggestion : FRA\TESTING\ARCHIVELOG\2009_09_22\O1_MF_1_23_%U_.ARC
ORA-00280: change 9235581 for thread 1 is in sequence #23
Sweet...Oracle's giving me a suggestion. What does that mean? (Me guessing again):
ALTER DATABASE RECOVER ARCHIVELOG 'FRA\TESTING\ARCHIVELOG\2009_09_22\O1_MF_1_23_%U_.arc
*
ERROR at line 1:
ORA-00905: missing keyword
ALTER DATABASE RECOVER LOGFILE 'FRA\TESTING\ARCHIVELOG\2009_09_22\O1_MF_1_23_%U_.ARC
'
*
ERROR at line 1:
ORA-00308: cannot open archived log 'FRA\TESTING\ARCHIVELOG\2009_09_22\O1_MF_1_23_%U
_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
That second one looked closer right? Meanwhile, I began a backup of the archived redo logs.
BACKUP ARCHIVELOGS ALL;
I wasn't really sure what that was going to do, but I needed some space.
Meanwhile, back in SQL*Plus I keep trying different commands.
SQL>ALTER DATABASE RECOVER TABLESPACE SYSTEM;
ALTER DATABASE RECOVER TABLESPACE SYSTEM
*
ERROR at line 1:
ORA-00275: media recovery has already been started
SQL>ALTER DATABASE RECOVER TABLESPACE USERS;
ALTER DATABASE RECOVER TABLESPACE USERS
*
ERROR at line 1:
ORA-00275: media recovery has already been started
SQL>ALTER DATABASE RECOVER;
ALTER DATABASE RECOVER
*
ERROR at line 1:
ORA-00275: media recovery has already been started
SQL>ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01156: recovery in progress may need access to files
I'm pretty sure I picked those up in a Google search (in other words, I didn't bother to click through). Out of frustration, I killed the instance (net stop oracleservicetesting) and tried to bring it back up.
STARTUP MOUNT;
SQL>RECOVER DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTING\SYSTEM01.DBF';
ORA-00283: recovery session canceled due to errors
ORA-38798: Cannot perform partial database recovery
ORA-38797: Full database recovery required after a database has been flashed back
For some unknown reason, I went back into RMAN (the session I didn't save to cut and paste) and issued
RECOVER DATABASE;
It's doing something...
Did I get it?
Lo and behold it worked. I was able to get back into my precious sandbox.
What's the moral of the story? I have no idea. Like I said last time, I learned something...I just don't know what it is yet.
Wait, I did learn one thing...Oracle is a pretty incredible piece of software if the likes of me can go in, muck it up, and it still comes back to life. That my friends, is pretty impressive.
Labels: dba, oradb, rman
