DRS (Disaster Recovery Server)

DRS (Disaster Recovery Server)

When the primary (production) database is duplicated on another database, this is referred as a standby database configuration. The aim of this configuration is to minimize downtime if the primary database suffers an error, since the standby database can assume the role of the production database in a very short time.

A standby database is a database replica created from a backup of a primary database. By applying archived redo logs from the primary database to the standby database, the two databases can be synchronized.

Two identically configured databases operate on two identically configured hosts.

The primary (production) Database instance is located on the first host (i.e. Production Server), the database is open and fully available for all SAP users.
The standby Database instance on the second host is in a mounted standby state (not opened) and is recovered constantly. This means that the standby instance incorporates all changes to the data of the primary instance either immediately, or with a slight delay. To do this, the offline redo log files created in the primary database system are imported (only the redo entries already archived by ORACLE can be imported). The standby instance therefore ‘follows’ the state of the primary instance.
If it is necessary to recover the primary database system (for example, after a media error), the standby instance can assume the functions of the primary instance in very short time (‘takeover’). The recovery mode of the standby instance is therefore ended, and the standby database opened for online operation.
Since all data files are already located on the standby host, costly reloading of the files is avoided.
Changes to the physical structure of the primary database (creating new files, changes to online redo log and control files) are not automatically incorporated in the standby database in every case. The Administrator/DBA may need to intervene depending on the type of change.
If it is not possible to incorporate the changes automatically, the recovery process is stopped, and the DBA needs to intervene manually to incorporate the structural change in the standby database. After that, the recovery process needs to be started again.

Advantages
A standby database can be a powerful tool for both disaster prevention and supplementary reporting. For example, you can:

Maintain a standby database in a location that is geographically remote from the primary database.
Implement a managed standby configuration, whereby a standby database automatically applies archived redo logs that are automatically shipped to the standby site by a primary database. In this way, changes to a primary database are regularly propagated to a standby database.
Make a standby database the new primary database with minimal loss of time and data if the primary database is completely destroyed.

Disadvantages

An additional resources/server, if you want to maximize disaster prevention by maintaining a standby database on a separate host .
Additional system resources and extra storage space no matter which implementation you choose

Recovery Mode
Standby database runs in

Managed Recovery Mode

it automatically applies redo logs received from the primary database.

Manual Recovery Mode

Redo logs file to be applied Manually.

Preparing Standby Database

Before go for Creating Standby Database, ensure that Primary database is in ARCHIVELOG mode.

In this document DEV is <SID> for primary DB and DRS is <SID> for standby DB.The Database names remain same as DEV for both primary DB and standby DB.

Take the listing of the files to be backed up. Backup must be a cold backup (i.e. Take a backup when Database is shutdown.) For listing of the files to be backup use following commands.

Select substr(name,1,45) "Data File", bytes/1024/1024 "SIZE IN MB" from v$datafile;

select substr(a.member,1,45) "LOG FILE",b.bytes/1024/1024 "SIZE IN MB" from v$logfile a, v$log b where a.group#=b.group#;

Restore the cold Backup taken in above step to DRS at respective path if same path is available.

2) Create the Standby database control file (in Primary DB Server) using following command
svrmgrl
connect internal;
SVRMGR> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/cfile/cntrlDRS.dbf’;
(/cfile is a temporary directory for saving a control file cntrlDRS.dbf)

3) Copy/Transfer the above control file to the respective control file system in DRS .The control files will be located in directory’s sapdata1,saparch,origlogA.

4) Configure the following Network files in network/admin as follows;
Add the following lines in the file tnsnames.ora, in Primary database
This adds new service entry: standby1 give path of files.

standby1.WORLD =
(DESCRIPTION =
(SDU = 32768)
(ADDRESS_LIST =
(ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = prddrs)(PORT = 1527))
)
(CONNECT_DATA =
(SID = DRS)
(GLOBAL_NAME = standby1.WORLD )
)
)

Add the following line in the file listener.ora, in standby database

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU = 32768)
(SID_NAME = DRS)
(ORACLE_HOME = /oracle/DEV/817_64) ) )

5) Start Listener at Standby DB
Lsnrctl start

6) Add/Modify as follows initDRS.ora, at Standby DB

DB_name = DEV # should be Primary DB SID
control_files = (oracle/DEV/sapdata1/cntrl/cntrlDRS.dbf, /oracle/DEV/origlogA/cntrl/cntrlDRS.dbf,/oracle/DEV/saparch/cntrl/cntrlDRS.dbf )

log_archive_dest_1 = 'LOCATION=/oracle/DEV/saparch/'
log_archive_format = DEVarch%t_%s.dbf
standby_archive_dest = /oracle/DEV/saparch/

log_archive_start = false

7) Add/Modify as follows initDEV.ora, at Primary DB

log_archive_dest_1 = "LOCATION=/oracle/DEV/saparch"

log_archive_dest_state_1 = enable
log_archive_dest_2 = "SERVICE=standby1 REOPEN=60"
log_archive_dest_state_2 = enable
log_archive_format = DEVarch%t_%s.dbf

8) After adding, test the availability of service using command;
tnsping standby1 (on primary database server)

Oracle environment variables file .dbenv_prddrs.csh to be modified for SID DRS. (above file exists at /oracle/DEV, if SID is DEV) at standby Database. Now Standby Database instance can be started as follows;

Mounting Standby Database

Step 1 : Login as a ora<sid>…………….on DRS hostmachine
Step 2 : Run a command : svrmgrl
(Now command prompt shown as -> SVRMGRL>>
Step 3 : connect internal;
Step 4 : startup nomount;
Step 5 : alter database mount standby database;

Archive the Current redo log file in Primary Database

Ensure that Primary database is open & following log file can be issued;

Placing the Standby Database in Manual Recovery Mode

Ensure that Standby Database is in Mount stage (above Step 1 to 5 can be followed), issue a following command;

svrmgrl> RECOVER STANDBY DATABASE;

Now, standby database is getting updated by applying archive redo log files, which are copied from Primary Database.

Canceling Manual Recovery Mode in Standby Database

Open a new Window
svrmgrl
connect internal;
svrmgrl> CANCEL;
svrmgrl>EXIT
Now svrmgrl session will get closed

Placing the Standby Database in Managed Recovery Mode

Ensure that no Gap Sequence is exists between Standby Database & Primary Database. i.e. Database is at Primary and Standby should be synchronized. For checking Gap Sequence, please refer the details under “Check for Gap Sequence”.

Also ensure that Standby database is in mount stage (Step 1 to 5 can be followed under Mounting Standby Database), following command can be issued;

svrmgrl> RECOVER MANAGED STANDBY DATABASE;

Now, standby database is getting updated automatically (by applying archive redo log files, which are copied from Primary Database).

Check for Gap Sequence

Before placing the Standby Database in Managed Recovery Mode, ensure that there is no Gap Sequence is exists between Primary and Standby databases.

A Gap Sequence can occur whenever the primary database archives log but the log is not transferred to the standby database. Missing log prevents managed recovery from applying subsequent logs, because standby database requires the sequential application of redo logs.

The following contents can be saved as a file “archgap.sql“ & can be placed under ora<sid> ‘s home folder (e.g. if oradev is user then home folder for the same is /oracle/DEV) .

SELECT high.thread#, "LowGap#", "HighGap#"
FROM
(     SELECT thread#, MIN(sequence#)-1 "HighGap#"
FROM
(     SELECT a.thread#, a.sequence#
FROM
(     SELECT *
FROM v$archived_log
) a,
(     SELECT thread#, MAX(sequence#)gap1
FROM v$log_history
GROUP BY thread#
) b
WHERE a.thread# = b.thread#
AND a.sequence# > gap1
)
GROUP BY thread#
) high,
(     SELECT thread#, MIN(sequence#) "LowGap#"
FROM
(     SELECT thread#, sequence#
FROM v$log_history, v$datafile
WHERE checkpoint_change# <= next_change#
AND checkpoint_change# >= first_change#
)

      GROUP BY thread#

) low
WHERE low.thread# = high.thread#;

The sql file can be executed as follows;

Login as a ora<SID>
svrmgrl à connect internal à @archgap
The output will shows as follows, if standby DB is synchronized with Primary DB.

THREAD# LowGap# HighGap#
---------- ---------- ----------

The output will shows as follows, if standby DB is not synchronized with Primary DB.

   THREAD#    LowGap#    HighGap#
----------   ----------    ----------
1            250           252
If there is any gap sequence, ensure that you copy the missing Offline Redolog files from Primary DB to standby DB. (above case, 3 offline Redolog files, i.e. 250, 251 & 252) and apply them in Standby DB , as per Steps described under Placing the Standby Database in Manual Recovery Mode.

Canceling Managed Recovery Mode in Standby Database

Open a new Window
svrmgrl
connect internal;
svrmgrl> RECOVER MANAGED STANDBY DATABASE CANCEL;
svrmgrl>EXIT
Now svrmgrl session will get closed

Monitoring/Maintaining Standby Database

Although a managed standby environment is mostly automated, it is not completely automated. In various situations, Administrator/DBA may need to perform status checks or maintenance on the standby database. The most common maintenance operations are as follows;

Checking the Status of Archived Redo Logs (Gap Sequence)

Refer the procedure under “Check for Gap Sequence”

Responding to Physical Changes in the Primary Database

Adding Data file to the Primary Database

Data file can be added thro. SAPDBA, in Primary Database, e.g. adding a data file for tablespace for PSAPPROTD;

The same datafile is also to be added in the standby database, for synchronizing both the database. Otherwise Archive redo log file, which are created after adding the datafile in the Primary database, will not get applied/recovered in the standby database.

Login as a ora<sid> and start sapdba from unix prompt>> sapdba
From SAPDBA Menu, select option c à Tablespace Administration à a - Tablespace <give the Tablespace name as PSAPSTABI> f à Alter Tablespace add data file à c – new size <u can give the Size of the data file as 20 M > à s <Start>.

Added datafile stabi.data6, will be stored in '/oracle/DEV/sapdata5/stabi_6’. The following sql command is used by sapdba for adding datafile to the primary database.
ALTER TABLESPACE "PSAPSTABI" ADD DATAFILE '/oracle/DEV/sapdata5/stabi_6/ stabi.data6' SIZE 20472K

2. Adding Data file to the Standby Database

The following points are to be noted, before adding datafile to the standby database;

Ensure that standby database is in Mount stage.
Folder “Stabi_6” is existing in /oracle/DEV/sapdata5 (Because in primary database, added datafile is stored in '/oracle/DEV/sapdata5/stabi_6)

By issuing following sql command, datafile can be added to the standby database.
svrmgrl>> ALTER DATABASE CREATE DATAFILE ‘/oracle/DEV/sapdata5/stabi_6/ stabi.data6' SIZE 20472K ;
After adding datafile to the standby database, remaining Archive Log can be applied manual or Managed recover mode (Refer the respective procedure for the same)

Opening a Standby database in Read-Only Mode

While the standby database is in read-only mode, the site can still receive archived redo logs from the primary site. Nevertheless, Oracle does not apply these logs automatically, as in managed recovery. Consequently, a read-only standby database is not synchronized with the primary database at the archive level.

The following sql command can be issued for Opening a Standby database in Read-Only Mode (if the standby database is shutdown);
Login as a ora<sid>,
Svrmgrl
Give following sql command;
svrmgr>>STARTUP NOMOUNT;
for mounting the database,
svrmgr>>ALTER DATABASE MOUNT STANDBY DATABASE;
svrmgr>> ALTER DATABASE OPEN READ ONLY;
If standby database in recovery mode then follow the steps given below to open standby database in read-only mode.

If manual recovery
Open a new Window

svrmgrl
connect internal;
svrmgr> CANCEL;………….exit out of the window where manual recovery was going on.
svrmgr>shutdown immediate;
svrmgr>startup nomount;
svrmgr>alter database mount standby database;
svrmgr>> ALTER DATABASE OPEN READ ONLY;

If managed Recovery
Open a new Window

svrmgrl
svrmgr>Connect internal;
svrmgr> RECOVER MANAGED STANDBY DATABASE CANCEL; ………….exit out of
the window where manual recovery was going on.
svrmgr>shutdown immediate;
svrmgr>startup nomount;
svrmgr>alter database mount standby database;
svrmgr>> ALTER DATABASE OPEN READ ONLY;

Activating a Standby Database

You should not activate or perform a failover to the standby database except in an emergency. After it is activated, the standby database becomes a normal production database and loses its standby functionality. You cannot undo the activation and return the database to standby mode.

You should not activate a standby database to test whether it is being updated correctly. Open it in read-only mode instead.

Depending on the nature of the emergency, you may not have access to your primary database files. If you do have access, then you should attempt to archive the current online redo log on the primary database manually, and then transfer and apply all available archived redo logs to the standby database.

To attempt to salvage the primary database redo logs:

If possible, archive the current online redo log on the primary database:

SVRMGR> ALTER SYSTEM ARCHIVE LOG CURRENT;

If you do not maintain a managed standby environment, manually transfer to the standby site all available archived logs that have not yet been applied to the standby database.
If the standby database is currently in managed recovery mode, cancel recovery:

SQL> CANCEL

4. If the standby database is not currently in managed recovery mode, put it in a mounted state. For example, if the standby instance is not started,

SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

5. Perform manual recovery on the standby database before activating it, For example, enter following statements:

SQL> RECOVER STANDBY DATABASE;

Following this procedure rolls forward the standby database to the time immediately before the failure of the primary database.

Activating the standby database automatically resets the online redo logs. Note that the redo logs from the standby database and primary database are now incompatible. You cannot apply archived redo logs from the original primary database to the activated standby database or vice versa. Also, the standby database is not mounted when activated; therefore, the tables and views do not contain useful information immediately after activation.

To activate a standby database:

Complete the recovery and after canceling the recovery shutdown the database and then mount the same.
Ensure that your standby database is mounted in EXCLUSIVE mode by executing the following query:

SQL> SELECT name,value FROM v$parameter WHERE name='parallel_server';

NAME                               VALUE

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

parallel_server                FALSE

1 row selected.

If the value is TRUE, then the database is not mounted exclusively; if the value is FALSE, then the database is mounted exclusively.

Activate the standby database; (Note that this command resets the online redo logs)

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Shut down the standby instances:

SQL> SHUTDOWN IMMEDIATE

As soon as possible, back up your new production database. At this point, the former standby database is now your production database. This task, while not required, is a recommended safety measure because you cannot recover changes made after activation without a backup.

Start the new production instance in read/write or read-only mode:

SQL> STARTUP

Note:
After you activate the standby database, you lose all transactions from unarchived logs at your original production database. Because the standby redo logs are reset at activation, you cannot apply logs archived before activation.

Starting SAP at DRS

Ensure that Primary Database Down and/or is not available & Standby database is open/running
status; Followings are the instances of Primary & Stanby Databases;

	SID of Primary DB	SID of Standby DB
Database Name	DEV	DEV
Database Instance	DEV	DRS
SAP Instance	DEV	DEV

Modifying Environment/Parameter file

Before start SAP instance, following parameter file are to be modified;

.dbenv_prddrs.csh,
.sapenv_prddrs.csh for user sid<adm> & ora<sid>
initDRS.ora
START_DVEBMGS00_prddrs
DEV_DEVBMGS00_prddrs
DEFAULT.PFL
startsap_prddrs_00
stopsap_prddrs_00

.dbenv_prddrs.csh

DBSID = DEV to be replaced by DBSID = DRS

.sapenv_prddrs.csh

SAPSYSTEMNAME DEV to be replaced by SAPSYSTEMNAME DRS

following parameters to be confirmed in initDRS.ora

log_archive_start = true
log_archive_dest = /oracle/DEV/saparch
log_archive_format = DEVarch%t_%s

START_DVEBMGS00_prddrs

Host name should be replaced by standby database’s hostname
SAPSYSTEMNAME = DEV

DEV_DEVBMGS00_prddrs
DEFAULT.PFL

SAPSYSTEMNAME = DEV
Host name should be replaced by standby database’s hostname

startsap_prddrs_00/ stopsap_prddrs_00

Host name should be replaced by standby database’s hostname & ensure that following value is exists

DB_SID=DRS

****