Backup and restore spfile using RMAN

Today we are gonna discuss how to take a backup of your spfile using RMAN and restore the same using RMAN.

Now before we start, I assume you have already configured your RMAN parameters.

Here we will be backing up and restoring the spfile of the target database using catalog database.

I assume your catalog database is up and running as well the listener corresponding to that too.

And in case of the target database, I assume the tns has been configured according to that particular catalog.

Now we are gonna take the backup of the spfile, while the target database is up and running or we can say online. So startup your target database, if it hasn't been.

Now open RMAN on the target environment.

Now follow these steps one after another. Keep that in mind, whatever I am using here are just as examples. rman/rman and catdb, BASE may be different in case of yours. You just have to put you ones. The target database is BASE here. I have mentioned the example names in bold. These bold ones can be different from yours.

RMAN> connect catalog rman/rman@catdb

connected to recovery catalog database

RMAN> connect target/

connected to target database: BASE (DBID=1899780213)

RMAN> backup spfile;

Wait until you see Finished backup.

Then exit from the RMAN prompt.

Now you can check the backup location that you have set up for RMAN. You will find the backup which has just been performed, but of course not in the form of a typical spfile.

Now go to ORACLE_HOME/dbs and delete the spfile corresponding to this target database.

Now shutdown the target database.

And then write startup on the sql prompt and see what happens.

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initbase.ora'

Now time to recover the spfile

Open RMAN prompt. And perform these steps

RMAN> connect catalog rman/rman@catdb

connected to recovery catalog database

RMAN> connect target/

connected to target database (not started)

RMAN> startup force nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initbase.ora'

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area     159383552 bytes

Fixed Size                     2019256 bytes
Variable Size                 67108936 bytes
Database Buffers              88080384 bytes
Redo Buffers                   2174976 bytes

RMAN> restore spfile;

And the spfile is finally restored. Visit ORACLE_HOME/dbs and check, you will find the restored spfile there. 

Now  exit from the RMAN prompt.

Then on sql prompt write

SQL> shut abort
ORACLE instance shut down.

Then again startup the database and see that the database gets opened without any error.

That's all for now. Do not hesitate to correct or include any points or information that I have mentioned wrongly or missed completely. Your feedback is highly appreciated.

Thanks
Subhajit

oraenv and its significance

Ora from Oracle and env from environment makes 'oraenv' together. Simple, isn't it? So by the name it suggests from the beginning that it must have something to do with oracle environment.

Now what is oracle environment? Oracle environment is nothing but a collection of some oracle environment variables. These variables are $ORACLE_HOME, $LD_LIBRARY_PATH, $PATH and $ORACLE_SID.

Now what is oraenv?

oraenv is a shell script provided by oracle for unix only. Remember only for unix, not for windows. It is used to set a user's environment to access an oracle database.

 

Now when is it created?

Do you remember the last step of a fresh oracle installation, where you are prompted to run 2 scripts as root, : root.sh and orainstRoot.sh. Yes, this root.sh copies oraenv to /usr/local/bin location so that it can be executed from any location.

 

How to execute oraenv?

We need to execute oraenv as . oraenv (dot space oraenv). Don't execute as only oraenv, cause if we use only oraenv, it will be not be executed in the current shell environment and so it won't be able to change the environment of the current shell, no matter how many times we put ORACLE_SID or ORACLE_HOME. Once we execute this, it asks for ORACLE_SID first and if we don't put the SID and hit enter it asks for ORACLE_HOME. These two are sufficient to set the rest of the 2. If we put any of this these 2, the rest of the 2 will automatically be set. That's why oraenv doesn't ask for all 4.

Once the environment is set you can all any of the binary files of oracle from any location.

I am including the oraenv script here. Have a look.

case ${ORACLE_TRACE:-""} in

    T)  set -x ;;
esac

#
# Determine how to suppress newline with echo command.
#
N=
C=
if echo "\c" | grep c >/dev/null 2>&1; then
    N='-n'
else
    C='\c'
fi

#
# Set minimum environment variables
#

# ensure that OLDHOME is non-null
if [ ${ORACLE_HOME:-0} = 0 ]; then
    OLDHOME=$PATH
else
    OLDHOME=$ORACLE_HOME
fi
case ${ORAENV_ASK:-""} in                       #ORAENV_ASK suppresses prompt when set

    NO)    NEWSID="$ORACLE_SID" ;;
    *)    case "$ORACLE_SID" in
        "")    ORASID=$LOGNAME ;;
        *)    ORASID=$ORACLE_SID ;;
    esac
    echo $N "ORACLE_SID = [$ORASID] ? $C"
    read NEWSID
    case "$NEWSID" in
        "")        ORACLE_SID="$ORASID" ;;
        *)            ORACLE_SID="$NEWSID" ;;       
    esac ;;
esac
export ORACLE_SID

ORAHOME=`dbhome "$ORACLE_SID"`
case $? in
    0)    ORACLE_HOME=$ORAHOME ;;
    *)    echo $N "ORACLE_HOME = [$ORAHOME] ? $C"
    read NEWHOME
    case "$NEWHOME" in
        "")    ORACLE_HOME=$ORAHOME ;;
        *)    ORACLE_HOME=$NEWHOME ;;
    esac ;;
esac

export ORACLE_HOME

#
# Reset LD_LIBRARY_PATH
#
case "$LD_LIBRARY_PATH" in
    *$OLDHOME/lib*)     LD_LIBRARY_PATH=`echo $LD_LIBRARY_PATH | \
                            sed "s;$OLDHOME/lib;$ORACLE_HOME/lib;g"` ;;
    *$ORACLE_HOME/lib*) ;;
    "")                 LD_LIBRARY_PATH=$ORACLE_HOME/lib ;;
    *)                  LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH ;;
esac

export LD_LIBRARY_PATH

#
# Put new ORACLE_HOME in path and remove old one
#

case "$OLDHOME" in
    "")    OLDHOME=$PATH ;;    #This makes it so that null OLDHOME can't match
esac                #anything in next case statement

case "$PATH" in
    *$OLDHOME/bin*)    PATH=`echo $PATH | \
                sed "s;$OLDHOME/bin;$ORACLE_HOME/bin;g"` ;;
    *$ORACLE_HOME/bin*)    ;;
    *:)            PATH=${PATH}$ORACLE_HOME/bin: ;;
    "")            PATH=$ORACLE_HOME/bin ;;
    *)            PATH=$PATH:$ORACLE_HOME/bin ;;
esac

export PATH

#
# Install any "custom" code here
#

# Locate "osh" and exec it if found
ULIMIT=`LANG=C ulimit 2>/dev/null`

if [ $? = 0 -a "$ULIMIT" != "unlimited" ] ; then
  if [ "$ULIMIT" -lt 2113674 ] ; then

    if [ -f $ORACLE_HOME/bin/osh ] ; then
    exec $ORACLE_HOME/bin/osh
    else
    for D in `echo $PATH | tr : " "`
    do
        if [ -f $D/osh ] ; then
        exec $D/osh
        fi
    done
    fi

  fi

fi

That's all for now. Do not hesitate to correct or include any points or information that I have mentioned wrongly or missed completely. Your feedback is highly appreciated.

Thanks

Subhajit

Starting an oracle database. The 3 stages with explanation.

There are 3 stages through which an oracle database passes during start up.

These 3 stages are

1. No Mount
2. Mount
3. Open

Normally when we issue the 'startup' command on the sql prompt, we see the following

SQL> startup
ORACLE instance started.
Total System Global Area  285212672 bytes
Fixed Size                               2020224 bytes
Variable Size                      109055104 bytes
Database Buffers                171966464 bytes
Redo Buffers                          2170880 bytes
Database mounted.
Database opened.

But we don't know the story behind this. Let's find out, what actually happens.

No Mount Stage, instance starts.

As soon as we issue this command,

• The oracle server process reads the parameter file corresponding to the SID (That can be a pfile or spfile initSID.ora or spfileSID.ora).
• The memory areas associated with this instance are allocated and oracle background processes are started. Together we can say the instance starts.
• The database is at no mount state now.

Mount Stage

At this stage, oracle server process reads the control file, which contains important information like the location of the datafiles and redo log groups. (This control file is previously specified in the parameter file). And finally the database is mounted.

Open Stage

At this stage, oracle server process accesses the datafiles and the finally the database is opened.

We can enter these stages individually but with the sequence. We have to issue the following commands one after another.

SQL> startup nomount

SQL> alter database mount;

SQL> alter database open;

That's all for now. Do not hesitate to correct or include any points or information that I have mentioned wrongly or missed completely. Your feedback is highly appreciated.

Thanks

Subhajit