Recovering an Oracle Database from Unexpected Failure
Oracle Database Introduction
I’ve been a user, developer, and administrator of SQL Server databases many times over the years. I’m really comfortable with that product and how to fix things when they break. Oracle Database on the other hand, I’ve only ever really been a consumer of information. Someone who writes queries to the data. As I’ve worked to expand my horizons in my lab, Oracle Databases are one of the things that I found very interesting.
One things about Oracle Databases that I found surprising is that they do not do well in unexpected shutdowns at all. So, to help those that have an Oracle Database server that really don’t know that much (like me!), I’ve compiled a list of things that have helped me get my system back up and running if I have an unexpected shutdown.
Getting Connected in SQL*Plus
First, if you have more than one Oracle SID on your server, you need to set ORACLE_SID to the SID you want to work on. This should be simple, unless you are like me and included a space between the equal sign. So, these are the commands I use to set my SID correctly (again…note that there is NO SPACE):
SET ORACLE_SID=ORCL SET ORACLE_SID=CDBORCL
Once you have set your SID, you can then fire up sqlplus without logging in:
sqlplus /nolog
Now we can go ahead and log in as the SYSDBA:
connect / AS SYSDBA
I suggest verifying that you set your SID correctly after you login. This saved me a lot of time, once I figured out that I was just connected to the wrong SID:
select name from v$database;
If we’ve done everything right, we should get something along these lines:
If you don’t see the write SID, then check to make sure that you don’t have any spaces in your SET command.
Checking The Status
Now that we are sure that we are connected to the correct SID, let’s check to see how things look. We’ll use this query:
select status, database_status from v$instance;
And here’s the result:
OPEN and ACTIVE indicates that your database is good to go. If you see something else, you may need to mount and/or open the database. To mount the database, try this:
alter database mount;
If that works, you can move on to opening the database using this:
alter database open;
If you can’t mount and open the database, there’s a good chance that you will need to recover the database:
RECOVER DATABASE
This has only worked for me a few times. If that fails, I generally go pull a daily backup of my Oracle DB VM and restore it. If you can’t even make it this far, and you haven’t been able to connect because you see something along the lines of Connected to an idle instance. Try first shutting down your database:
shutdown abort
Once that completes, fire it back up:
startup
Now you can go back to the beginning of this section and verify that everything looks good.
What About Container Databases?
Now that Oracle DB support multi-tenant, you have the idea of container databases (CDB) and pluggable databases (PDB). First, you can use everything from the above sections to connect to your CDB and get it back up and running. This means that CDB’s are relatively straightforward.
What About Pluggable Databases?
You may be wondering why I even bother using CDB’s and PDB’s. Jake even makes it a point to suggest using a plain old Oracle Database in his post. Well…I did that and it works great. But then I wanted some sample data, which of course comes in the form of a PDB! Getting that to work was an adventure, but when it stopped working, I happened across a great Oracle resource related to multi-tenant databases:
Performing Basic Tasks in Oracle Multitenant
This works great for me, given how basic I am. Once you get connected to your CDB, you can quickly get a list of the PDB’s that you might want to connect to:
select name, open_mode from v$pdbs;
And now we can actually connect:
connect sys/oracle@10.0.0.216:1521/pdborcl AS SYSDBA
Basically this tells it to connect to the IP of my Oracle DB (10.0.0.216) and to connect to the pdborcl PDB. After connecting, you can perform operations like opening a database that is mounted:
alter pluggable database pdborcl open;
Conclusion
There it is…everything I know (yes…not much) about recovering an Oracle DB from an unexpected failure (like a really long power outage and your UPS not lasting long enough). If this post helps just one Oracle DB newbie like me, it will have been worth it!