Oracle - Starting a Database

Dear Readers,

My name is Franz Devantier, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g) Security DBA.  I will be sharing with you the basic duties of an Oracle DBA, and also some of the undocumented, and not so well known tasks. 

I will make a deal with you:  If you refer me to a company that needs database support, from a few hours per week to full time, and I am able to sign a contract with them.

Then I will give you 10% of the monthly contract or deal price every month.  When the contract ends, and we re-sign the contract, I will again give you 10% of the monthly contract price.  This will go on until the company no longer employs or contracts me or my agents to look after their databases.

I can do this, because that 10% is my marketing budget.  When we re-sign the contract, in the future, it may depend on you giving the thumbs up again, and that is worth 10% of the monthly contract price, to be given to you as commission.

Contact: Franz

Starting a database

To start up an Oracle database, you will firstly need to be properly authenticated, and logged in with a DBA user with appropriate privileges.  I will not go into that now, because it can get quite involved authenticating yourself correctly.  We will focus here on actually starting up.

Let’s say you are properly authenticated and logged into SQL*Plus.  This is not the only way to do it, but the preferred method to demonstrate.

SQL> startup

That is all that is needed, Oracle will find the pfile or I hope the spfile in the default location and use that to find the control files, and therefore all the components it needs to start up.  If you are still using the pfile, then we need to talk.

In you look in your alert<sid>.ora file, you will notice that Oracle in fact went through a few different states of the database, before it ended up in the “OPEN” state.

•          Nomount – This is where Oracle reads the spfile or I hope not pfile.  The information in the parameter file is used to start the Oracle Instance.  The instance is the structure that Oracle creates in memory.  This is the instance state that is useful for recreating the control file, and other maintenance tasks.

•          Mount – This is the state where the instance mounts the database.  The database is really the physical database files and the instance is the memory structures.  So this state is where the memory structures are mounted or connected to the physical database files.  The database files are not opened in this state of the database.  This means that even if there are corrupted datafiles, you will still not be aware of that in this state, because you have simply mounted the files, but the files have not been opened.  This is a good state to proceed from for database maintenance activities, like recovery for example.

•          Open – Finally the database reaches the “Open” state.  The data files and the redo files that are mentioned in the control files are opened in this state.  If you do not have redo logs, you will need to issue “resetlogs”, to create them in the location specified in the control files.

If you want to startup from a specific pfile, or startup using a pfile, when your database is using a spfile, you can use this syntax. SQL> startup pfile=’<fully qualified path/>initsid.ora’ Then you want to create your spfile from the pfile. SQL> create spfile from pfile; Now your database has started up from a pfile, so you will need to restart in the normal way to allow your database to use the spfile. Before doing this you would have done something like this to make sure that you did not lose any existing parameters in your spfile. SQL> create pfile from spfile; Then you would have edited this pfile in $ORACLE_HOME/dbs or $ORACLE_HOME/database depending on whether you are in windows or unix/linux. You can also manually control how the database opens, by specifying whether you want to startup in nomount, mount or open state. SQL> startup nomount SQL> startup mount SQL> startup open or SQL> startup The default state of open is “READ WRITE”, but you can also open the database in read only mode.  In this state no database changes such as inserts, deletes or updates can be executed. SQL> startup mount SQL> alter database open read only; Then to change it to READ WRITE mode you can do a shutdown and a startup. Then you can open the database in restricted mode as well.  This will only allow users with the restricted session privilege to connect to the database.  Typically only certain DBA’s will be granted this privilege.  This is useful during maintenance, to prevent other users from accessing the database during maintenance operations. SQL> startup restrict Another useful mode is the Quiesce mode.  In this mode only the sys and system users can query the database.  However users that are already active on the system can still access the database, until there session becomes inactive.  Very useful, but its application needs to be carefully implemented. SQL>  alter system queisce restrict;  SQL> alter system unqueisce; Of course no discussion of startup options would be complete without mentioning the startup force option.  This option is reserved for those situations, in which you have a reliable backup, and for some reason the database is stuck half-way between being shutdown and open.  This option is a last resort, and so the alert log has been studied, and any other measures must have been taken, before using this option.  Startup force will do a “Shutdown abort”, followed by a normal startup. SQL> startup force Now we get to the really interesting startup option, the “resetlogs”.  If you have performed an incomplete recovery, and the database is shutdown you can do this. SQL>  startup open resetlogs More typically you will be in mounted mode, and have performed an incomplete recovery. SQL> alter database open resetlogs; Now it is important to be aware of what “open resetlogs” does in the background.  It goes to check if it has been invoked after an incomplete recovery.  If the database has been shutdown in normal mode without issues, then the resetlogs will not execute. The above SQL statement will in fact return an error something like this: ORA-01139: Resetlogs option only valid after an incomplete database recovery. Now let’s say that we specifically want a new incarnation of the database, so that our RMAN backup will never look for archive log files, beyond the last resetlogs.  How do we achieve this? Ok, so now we will have to simulate an incomplete recovery of the database without actually performing an incomplete recovery of the database.  Let’s imagine that this database is a production database, then we can’t take any chances with getting this process wrong. More correctly we are going to create a scenario to enable “resetlogs”, to validly create a new incarnation of the database, without actually having or performing an incomplete recovery of the database.  Quite obviously this is not documented in the Oracle documentation.   The obvious route to follow would incorrectly be: SQL> recovery database; Doing the above from the mounted mode will not work, because firstly it does not refer to incomplete recovery, and secondly there is nothing to recover, if the database has shutdown normally. You could expect an error message something like this: “no recovery required” Also if you were to try the “alter database open resetlogs” again, you would get the same error as described before. You need to do something that will get past the “resetlogs mechanism” without actually doing any recovery.  For example, from a mounted state database: SQL> recover database until cancel; This will almost immediately return a message something like this: “Media recovery complete” It comes back so quickly, because in fact there was nothing to do.  However from the “resetlogs” mechanism or point of view, an incomplete recovery has now been performed. Now you can execute a resetlogs. SQL> alter database open resetlog; This should return after a while “Database altered.” So now we have successfully executed a resetlogs, without actually doing a recovery.

Oracle did not need to apply any redo in this operation.  The incarnation of the database was changed and the log sequence was reset to 1.  In other words this is a new incarnation of the database, and therefore we can keep backups of it from this point onwards.  More specifically backups or copies of the archived log files.  Oracle will now never ask us to apply an archived log file during recovery, that is older than the point in time of the new incarnation of the database.

From a backup and recovery perspective, this makes a lot of sense.  If the database was a few years old, the archived logs from a few years ago are long gone.  If during a recovery process, because of corrupted backups or whatever reason, we were asked for an archive log from the beginning, we would now only have to go back as far as the last incarnation of the datbase.  So our backup and recovery strategy has become more robust and reliable with this action.

No Redo actually needed to be applied yet, Oracle did do an OPEN RESETLOGS and change the activation ID and reset the Log Sequence Number to 1.

Here we can see that this was the 4th incarnation of this database :

SQL> select dbid, resetlogs_time, recovery_target_incarnation#, last_open_incarnation#

        2  from v$database;

      DBID RESETLOGS RECOVERY_TARGET_INCARNATION# LAST_OPEN_INCARNATION#

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

4279484577 10-JUN-12                            4                      4

SQL> select incarnation#, resetlogs_change#, resetlogs_time, status, resetlogs_id

  2  from v$database_incarnation

  3  where incarnation#=4;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS STATUS  RESETLOGS_ID

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

           4        5.9702E+12 10-JUN-12 CURRENT    785611115

We have successfully caused the “alter database open resetlogs” process to believe that we were sitting with an incomplete recovery, even although the last shutdown was a normal shutdown.  Also this process has not lost any transactions.

A bit of caution though.  Because this is undocumented by Oracle, it should only be attempted very carefully and after testing on a development or test system.

I have by no means exhausted the possibilities of starting up an Oracle database in this article, but hopefully, the undocumented feature will prove to be useful or interesting.  Hopefully in version 12 Oracle would have addressed this issue.

Franz Devantier,

Need a database health check?

devantierf@gmail.com

Income stabilizer (win-win opportunity)

Join the carefully selected and tested cash-flow generating program below to potentially create a long-term residual or annuity type income enhancer for yourself.

Traffic Wave - Free Report:  The report will give you all the information you need to start making a nice long-term residual income stream for yourself.