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 :
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.
|
