DR Setup on Oracle using Data Guard Configuration

1.       Take a backup of the primary DB which will be restored on the standby server.

2.       Create a standby control file before you make any further changes to DB (like adding datafiles, renaming datafiles, etc.) for starting the standby db for recovery.

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oracle/PER/controlfile.dbf';

3.       Restore the standby DB server with the backup taken instep 1.

brrestore -b <backupfile_name>

The parameter changes in the section below, can be performed individually on primary and standby db, or only on primary db and then you can create a pfile and use it in the standby db for generating spfile. In the second approach, you may need to do a few additional changes to the standby system. I had done the changes individually on both systems.

4.       Check db_name and db_unique_name on both primary and standby databases. The db_name of standby should be the same as that of primary, but db_unique_name should be different.

On primary database:

On secondary database:

5.       The db_unique_name (of both primary and standby) should be used in dg_config setting of the parameter log_archive_config of both primary and standby databases.

    ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PER,PER_NRDR)'; 

6.       Create suitable archive destinations. Use db_unique_name in the service to properly reference the standby db.

In primary database:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='service="PER_NRDR", LGWR SYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PER_NRDR DELAY=0'; 

        In secondary database:

        ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='service="PER", LGWR SYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PER DELAY=0';

7.       Check the below parameters and set the values accordingly if not set by default in both primary and secondary databases.

a.       LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf'

b.       LOG_ARCHIVE_MAX_PROCESSES=30

c.       REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

8.       It is recommended to ensure that the primary DB is ready to switch the role of standby. Need to set the below parameters for this.

On primary database:

a.       ALTER SYSTEM SET FAL_SERVER=PER_NDR;

b.       ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

c.       ALTER SYSTEM SET FAL_CLIENT=PER;

        On standby database:

            a.       ALTER SYSTEM SET FAL_SERVER=PER;

            b.       ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

            c.       ALTER SYSTEM SET FAL_CLIENT=PER_NRDR;

9.       After all the changes are done to the parameters, generate fresh pfile from spfile.

CREATE PFILE='\oracle\PER\11204\database\initPER.ora' FROM SPFILE;

10.   In addition to the existing entries, make the entries for standby and standby data guard services in tnsnames.ora file on primary DB ($ORACLE_HOME\NETWORK\admin) in primary database. A sample of the entry would look like something as below:

        PER_NRDR.WORLD=

                        (DESCRIPTION =

                                        (ADDRESS_LIST =

                                        (ADDRESS =

                                        (COMMUNITY = SAP.WORLD)

                                        (PROTOCOL = TCP)

                                        (HOST = <standby_db_host>)

                                        (PORT = 1521)

                                        )

                                        )

                                        (CONNECT_DATA =

                                        (SID = PER)

                                        (GLOBAL_NAME = PER)

                                        (SERVICE_NAME = PER)

                                        )

                                        )

       

        PER_NRDR_DGMGRL.WORLD=

                        (DESCRIPTION =

                                        (ADDRESS_LIST =

                                        (ADDRESS =

                                        (COMMUNITY = SAP.WORLD)

                                        (PROTOCOL = TCP)

                                        (HOST = <standby_db_host>)

                                        (PORT = 1521)

                                        )

                                        )

                        (CONNECT_DATA =

                                        (SID = PER)

                                        (GLOBAL_NAME = PER)

                                        (SERVICE_NAME = PER_DGMGRL)

                                        )

                                        )

11.   Ensure that telnet from primary to standby DB and vice-versa using the ports defined in the file tnsnames.ora is successful.

12. Copy the file pwd<SID>.ora from primary DB to standby DB (Location: $ORACLE_HOME/database).

13.   If all the configurations above have been done properly, at this point, you should be able to see the oraarch directory on standby DB to be in synch with the primary DB.

14.   Copy the standby control file created in step 2 in all the control file locations on the standby db. Keep a copy of the existing control file as a backup. You can find the control file location as below:

15.   Now, you should run the recovery of the DB using backup control file.

16.   Mount the standby database.

            ALTER DATABASE MOUNT STANDBY DATABASE;

17.  Start applying the logs.

            RECOVER STANDBY DATABASE USING BACKUP CONTROLFILE;

18.   After all the logs have been applied in the foreground, enable the log application in the background.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

19.   This is the final step in DR setup. Now you can check if the MRP0 process in standby DB is waiting for logs from primary DB:

        select process, thread#, sequence#, status from v$managed_standby where process='MRP0';

Finding list of all ports used in an Active SAP system

We often come to a situation where there is an overlapping of ports for an application. A single port would be used by an SAP service and any non-SAP application/service on the same server simultaneously. This will cause the SAP service to fail/stop. For avoiding this, we should know the list of all the ports which are being used by a running SAP application, so another service is not configured on any of these ports.

You can find the list of ports using the below steps:

1. Login to the system using <sid>adm and navigate to the profile directory.
2. Find all the instance number of all the instances present in the directory.
3. For every instance number, run the below command.

Command: sapcontrol -nr <instance_nr> -function GetAccessPointList

e.g. sapcontrol -nr 00 -function GetAccessPointList

Output:

Shell script to automate diagnostic agent startup

 

Following  shell script  will be helpful to automate the startup of diagnostic  agent in case it is stopped.

 

You can schedule  the script  as a  crontab entry under the OS user that runs diagnostic agent.  The script will perform the status check and  if the diagnostic agent is found stopped it will be automatically started by the script.  You will be able to overcome the manual intervention needed.

 

 

Cron entry for  scheduling the script in every 5 minutes :   */5 * * * * /home/dadadm/check_diagnostic_agent.sh

 

In my case , DAD is the SID of the diagnostic agent. So I  have installed the script under the OS user dadadm.

 

The script is not intended to replace standard SAP process of starting the agent during OS startup.

If you observe that the agent  does not start up  during OS startup, check the following

 

1) whether  sapstartsrv service for the diagnostic agent has been configured  in sapservices file.  sapservices  can be located in /usr/sap directory

 

 

2)sapinit  is placed in /etc/init.d directory (this is for Linux systems)

 

3)Autostart parameter  has been set to 1 in instance profile of diagnostic agent

 

 

#SCRIPT STARTS HERE - COPY FROM THE LINE BELOW

 

#!/bin/sh

SID=""

profile_dir=""

LOG="$HOME/check_diagnostic_agent.log"

#DIR_LIBRARY=/usr/sap/DAD/SYS/exe/run

#=================================================================

startExecution()

{

start_timestamp=`date +"%Y-%m-%d %H:%M:%S"`

echo "[ Execution start timestamp:  ${start_timestamp}" >> "$LOG"

}

 

#=================================================================

exitOnError()

{

error_timestamp=`date +"%Y-%m-%d %H:%M:%S"`

echo "Execution end timestamp:  ${error_timestamp} ]" >> "$LOG"

exit 1

}

 

#=================================================================

endExecution()

{

end_timestamp=`date +"%Y-%m-%d %H:%M:%S"`

echo "Execution end timestamp:  ${end_timestamp} ]" >> "$LOG"

}

 

##################

##Main Function

startExecution

#================================================================

#Export Environment variables

if [ -f $HOME/.profile ]; then

     . $HOME/.profile

   #echo "Environment profile found..." >> "$LOG"

else

   echo "ERROR: No environment profile found!!!" >> "$LOG"

   exitOnError

fi

#================================================================

#Check Environment variable SAPSYSTEMNAME

if [ ! -z $SAPSYSTEMNAME ]

then

 SID="${SAPSYSTEMNAME}"

 profile_dir="/usr/sap/${SAPSYSTEMNAME}/SYS/profile"

 #echo "Diagnostic agent SID id ${SAPSYSTEMNAME}" >> "$LOG"

else

  echo "ERROR: SAPSYSTEMNAME environment variable not found...exiting!!!" >> "$LOG"

  exitOnError

fi

#================================================================

#check whether startsap exists

STARTSAP_DIR=""

for dir in `echo $LD_LIBRARY_PATH | sed 's/:/ /g'`

do

   if [ -x "${dir}/startsap" ]

   then

    STARTSAP_DIR="$dir"  

    break

   fi

done

#echo "STARTSAP_DIR=${STARTSAP_DIR}"

if [ -z "$STARTSAP_DIR" ]

then

 echo "ERROR: startsap executable not found...exiting!!!" >> "$LOG"

 exitOnError

fi

#=================================================================

#check if the SAP System is a diagnostic agent

if [ -d /usr/sap/$SAPSYSTEMNAME/SMDA[0-9][0-9] ]

then

 echo "SAP System ${SAPSYSTEMNAME} is a diagnostic agent..." >> "$LOG"

else

  echo "ERROR: Dignostic agent is not installed..." >> "$LOG"

  exitOnError

fi

#=================================================================

if [ -d "$profile_dir" ]

then

  #echo "File system /usr/sap/${SAPSYSTEMNAME}/SYS/profile exists..." >> "$LOG"

  echo "Checking if diagnostic agent is running...." >> "$LOG"

  if [ `$STARTSAP_DIR/startsap check|grep -c "is running"` -ge 1 ]

  then

      # check whether diagnostic agent processes are running

      ps -ef | grep "/usr/sap/$SAPSYSTEMNAME/SMDA[0-9][0-9]/exe/jstart" > /dev/null 2>&1

      rc1=$?

      ps -ef | grep "/usr/sap/$SAPSYSTEMNAME/SMDA[0-9][0-9]/exe/sapstartsrv" > /dev/null 2>&1

      rc2=$?

      rc=`expr $rc1 + $rc2`

      if [ $rc -eq 0 ]; then

        echo "diagnostic agent is already running..." >> "$LOG"

      else

        echo "RC=$rc...Probably diagnostic agent is not running..Executing startsap...." >> "$LOG"

        $STARTSAP_DIR/startsap

      fi

   else

     echo "Probably diagnostic agent is not running..Executing startsap...." >> "$LOG"

     $STARTSAP_DIR/startsap

  fi

else

  echo "File system does not /usr/sap/${SAPSYSTEMNAME}/SYS/profile exist..." >> "$LOG"

  exitOnError

fi

endExecution

 

# END OF SCRIPT