How to build a PHYSICAL Standby Database (DRS)
1. Preparing Physical Standby Database Server
Preparing a server to for the PHYSICAL Standby Database
You will need a standalone server, preferably a server with similar specification
(CPU,RAM,Storage) with the Primary Database server.
On the Physical Standby Database, install similar version of SAP and Oracle. Once this is
done you can start building/configuring the Physical Standby Database.
2. Convert Primary Database to use SPFILE
Make sure the Primary Database is using SPFILE instead of PFILE this step is crucial as
with SPFILE no time will be required when adjusting/altering Oracle SPFILE.
To verify whether the database is running in SPFILE, please issue the following
command,
SQL>show parameter pfile;
If the database is running in SPFILE, there will be a file path under the VALUE column
showing the location of SPFILE. If there is no file path under the VALUE column, the
database is using PFILE.
To convert primary Database to use SPFILE, issue the following command.
SQL>create spfile from pfile;
If this is a Microsoft Clustered with Oracle Failsafe Database Server, you will need to
modified the init_OFSSID.ora located at ORACLEHOME\database and include the line
Spfile=ORACLEHOME\database\spfileSID.ora
Kindly restart the Oracle Database to take effect.
3. Enable Forced Logging on Primary Database
This is a must for the DRS to function, please issue the following command,
SQL>alter database force logging;
If you see the error message, ORA-12920: database is already in force logging mode.
This is fine as the database is already in Forced Logging Mode.
This statement may take a considerable amount of time to complete, because it waits for
all un-logged direct write I/O operations to finish.
4. Backup the Primary Database to the Standby Database
Use the following script to backup the Database,
SQL>select 'alter tablespace '||tablespace_name||' begin
backup;' from dba_tablespaces
where contents !='TEMPORARY';
This will set all the tablespace except Temporary tablespace to backup mode .
Copy all the oracle datafile, offline redo logs, parameter file and password file to the
Standby Database.
This process may take hours to complete as it depends on the size of the Primary database
and the method of transfer.
Once this is completed, issue the following command,
SQL>select 'alter tablespace '||tablespace_name||' end
backup;' from dba_tablespaces
where contents !='TEMPORARY';
This will end the backup mode of all the tablespace except Temporary tablespace .
5.Creating Standby Controlfile
A standby contrilfile is needed for the STANDBY Database. To create a standby
controlfile, issue the following command,
SQL>alter database create standby controlfile as
‘c:\standby.ctl’;
Copy the standby controlfile to the Standby Database and place it accordingly.
Usually in the following location,
I. OriglogA\cntrl
II. Saparch\cntrl
III.Sapdata1\cntrl
The location of the control file is specifying in the Oracle PFILE. Please rename the file
name accordingly.
6.Modifying TNSNAMES.ORA for both PRIMARY and STANDBY Database
In order for both STANDBY and PRIMARY Database to communicate, we must modify
the TNSNAMES.ORA.
Before modifying the TNSNAMES.ORA, used Windows ping utility and make sure both
servers are able to ping each other. If this is not possible, resolve this issue first.
Open the PRIMARY Database and browse to
ORACLEHOME\network\admin\TNSNAMES.ora
Within the file you will see similar description as below,
SID.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = IP address)(PORT =
1527))
)
(CONNECT_DATA =
(SID = SID)
(GLOBAL_NAME = SID..WORLD)
)
)
Copy the entire description as above and paste it in the STANDBY Database
TNSNAMES.ora
On the STANDBY Database’s TNSNAMES.ora copy the similar description and paste it
in the PRIMARY Database TNSNAMES.ora and modified the first line by including
_DR before .WORLD
SID_DR.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = IP address)(PORT =
1527))
)
(CONNECT_DATA =
(SID = SID)
(GLOBAL_NAME = SID..WORLD)
)
)
When this is done, perform a test from both Primary and Standby Database by using the
following command.
On the Primary Database,
C:\>tnsping SID_dr
TNS Ping Utility for 32-bit Windows: Version 9.2.0.8.0 - Production on 02-JAN-20
08 17:05:25
Copyright (c) 1997, 2006, Oracle Corporation. All rights reserved.
Used parameter files:
D:\oracle\SID\920\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (SDU = 32768) (ADDRESS_LIST = (ADDRESS = (C
OMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = hostname)(PORT = 1527)))
(CONNECT_DAT
A = (SID = SID) (GLOBAL_NAME = SID.WORLD)))
OK (20 msec)
On the Standby Database,
C:\>tnsping SID
TNS Ping Utility for 32-bit Windows: Version 9.2.0.8.0 - Production on 02-JAN-20
08 17:03:19
Copyright (c) 1997, 2006, Oracle Corporation. All rights reserved.
Used parameter files:
E:\oracle\DPF\920\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=TCP)
(Hos
t=Ip address) (Port=1521)) (ADDRESS= (COMMUNITY=SAP.WORLD) (PROTOCOL=TCP)
(Ho
st=Ip address) (Port=1527))) (CONNECT_DATA= (SID=SID) (GLOBAL_NAME=SID.WORLD)
))
OK (20 msec)
Both tnsping results must be OK for the DR to work.
7. Add In Initialization Parameters For Primary and Standby Database.
In the Primary Database, add in the following by logging into SQL PLUS.
SQL>alter system set fal_client=’SID’;
SQL>alter system set fal_server=’SID_DR’;
SQL>alter system set log_archive_dest_2=’SERVICE=SID_DR’;
SQL>alter system set log_arhive_dest_state_2=’ENABLE’;
In the Standby database, add in the following manually by editing the initSID.ora,
Standby_file_management=AUTO
remote_archive_enable=TRUE
fal_client=SID_DR
fal_server=SID
standby_archive_dest=’LOCATION=U:\oracle\SID\oraarch\SIDarch’
8. Convert Standby Database to SPFILE
Logging into SQL PLUS by using sqlplus /nolog followed by connect / as sysdba.
Execute the following command,
SQL>create spfile from pfile;
Followed by
SQL>shutdown immediate;
9. Starting Up and Enabling Log Shipment To Standby Database
Logging into SQL PLUS by using sqlplus /nolog followed by connect / as sysdba.
Execute the following command,
SQL> startup nomount;
SQL> alter database mount standby database;
If there’s no error message, the Standby Database is up.
To apply the offline archive logs backed up previously, issued the following command,
SQL> recover automatic standby database until cancel;
When the recovery is completed, issue the following command to start the log shipment
service.
SQL>alter database recover managed standby database
disconnect from session;
On the primary Database issue the following command from SQL prompt,
SQL>alter system switch logfile;
This will generate new offline archive log on the primary and you can verify whether this
new log file has been shipped to the Standby Database.
10.Monitoring The Log Shipment Service
The following SQL Script is useful in monitoring the Physical Standby Database,
select * from (select sequence#,applied,first_time from v$archived_log
order by first_time desc)
where rownum <10;
It will display out the list of archived log files that has been applied to the Physical
Standby Database.
This SQL Script will provide you the status of the Log Shipment Service.
select process,status,thread#,sequence#,block#,blocks from v$managed_standby;
11. Physical Standby Database Maintenance.
From time to time, offline archived log that has been generated in the Primary Database
will be shipped to the Physical Standby Database. This will result in the physical standby
database running out of disk space to store new incoming offline archive log from
Primary Database.
To provide ample disk space for new incoming offline archive log, we will need to clean
up all the successfully applied offline redo logs.
Follow these steps to clean up the successfully applied archived log file:
- Determine which archived log file have been successfully applied to DRS site: See
the section “Monitoring the log shipment service” above
- Delete the successfully applied archived log file on the physical standby database in
the folder X:\oracle\SID\oraarch (standby_archive_dest parameter on DRS site)
To determine whether an archive gap exist, use the following query,
SQL>select * from v$archive_gap;
If the return results are “no rows selected” that means there’s no archive gap detected.
If a record returns, manual intervention will be required.
Example of a record,
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
-------- -------------- --------------
1 32545 32548
From the record above, we can see that the physical standby database is currently missing
logs from sequence 32545 to sequence 32548. Hence, we will need to manually copy
these files from the primary database to the physical standby database’s archived log
directory.
Issue the following command to stop the log shipment service,
SQL>alter database recover managed standby database cancel;
Start copy the missing offline log files from Primary database to Physical Standby
Database.
Once all the files are copied and placed in the archived log directory, issue the following
command,
SQL> ALTER DATABASE REGISTER LOGFILE
'X:\oracle\SID\oraarch\SIDARCHARC32545.001';
SQL> ALTER DATABASE REGISTER LOGFILE
'X:\oracle\SID\oraarch\SIDARCHARC32546.001';
SQL> ALTER DATABASE REGISTER LOGFILE
'X:\oracle\SID\oraarch\SIDARCHARC32547.001';
SQL> ALTER DATABASE REGISTER LOGFILE
'X:\oracle\SID\oraarch\SIDARCHARC32548.001';
When this is completed, restart the managed recovery operations by issuing the following
command,
SQL> alter database recover managed standby database
disconnect from session;
12. Switchover Operations
A database switchover is a role reversal between the Primary and its Standby databases.
A switchover operation guarantees no data loss when Standby Redo Logs are used. This
is typically done for planned maintenance of the Primary system. During the switchover,
the Primary databases transitions to a Standby role and the Standby database transitions
to the Primary role. The transition occurs without having to recreate either database.
Prerequisite
• Verify that BOTH the Primary and Standby database has the following files
under the directory ‘D:\oracle\DPF\920\database’ to support role transition.
Name Contents
Primary DB log_archive_dest_state_2='ENABLE'
Standby DB log_archive_dest_state_2='DEFER'
• Verify that there is network connectivity between the Primary and Standby
sites.
• Verify that all the Oraarch, MirrlogA,B,Orriglog A,B is in the exact same
drive and folder for both Primary DB and Standby DB. This is important as it
will ensure no data loss during switchover.
• For switchover operations involving a Physical Standby database, the Primary
database instance must be OPEN and the Standby database must be
MOUNTED.
• To verify this, perform the following steps:
On the Primary database, issue the following query:
SQL> SELECT STATUS FROM V$INSTANCE;
STATUS
------
OPEN
1 row selected
On the Standby database, at the SQL> prompt, issue the following
query:
SQL> SELECT STATUS FROM V$INSTANCE;
STATUS
------
MOUNTED
1 row selected
12.1 Perform the Switchover
• Convert the Primary database to the new Standby:
On the Primary database, at the SQL> prompt, issue the following
command:
SQL> alter database commit to switchover to physical
standby with session shutdown;
Database altered.
Shutdown the former Primary database.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
Create SPFILE using the command
SQL> create spfile from pfile=’?\database\initDPFsb.ora’;
File Created
Start the new Physical Standby Database
SQL> startup nomount;
Mount the new Physical Standby Database
SQL>alter database mount standby database;
Database Altered
• Convert the Physical Standby to the new Primary role:
On the Standby database, at the SQL> prompt, issue the following
command:
SQL> alter database commit to switchover to primary with
session shutdown;
Database altered.
Shutdown new Primary database:
SQL> shutdown immediate
ORA-01507: database not mounted.
ORACLE instance shut down.
Create SPFILE for the new Primary database
SQL>Create spfile from pfile=’?\database\initDPFpr.ora’;
Startup the new primary database.
SQL>startup;
• Start managed recovery on the new Standby database:
On the new Standby database, at the SQL> prompt, issue the following
command:
SQL> alter database recover managed standby database
disconnect from session;
Database Altered
12.2 Verify the new Primary and Standby databases are working
Identify the existing archived redo log files on the Standby database.
At the SQL> prompt on the Standby database, issue the following
query:
SQL> select sequence#, first_time, next_time
from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- --------------- ---------
927 24-NOV-05 25-NOV-05
928 25-NOV-05 26-NOV-05
929 26-NOV-05 27-NOV-05
930 27-NOV-05 28-NOV-05
Archive the current log on the New Primary database. At the SQL>
prompt on the Primary database, issue the following command:
SQL> alter system archive log current;
System altered.
Verify that the new archived redo log file was received by the Standby
database. At the SQL> prompt on the Standby database, issue the
following query:
SQL> select sequence#, first_time, next_time
from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- --------------- ---------
927 24-NOV-05 25-NOV-05
928 25-NOV-05 26-NOV-05
929 26-NOV-05 27-NOV-05
930 27-NOV-05 28-NOV-05
931 27-NOV-05 28-NOV-05
Now that we know the archived redo log files are being shipped to the
Standby database successfully, we need to verify that they are being applied to
the Standby database.
Verify that the new archived redo log files are being applied on the
Standby database. At the SQL> prompt on the Standby database, issue
the following query:
SQL> select sequence#, applied from v$archived_log
order by sequence#;
SEQUENCE# APP
---------- ---
927 YES
928 YES
929 YES
930 YES
931 YES
Verify that the Primary database is in “Maximum Availability” mode.
At the SQL> prompt on the Primary database, issue the following
query:
SQL> select protection_level, protection_mode from
v$database;
PROTECTION_LEVEL PROTECTION_MODE
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
current production had below directory structure
D:\ Origlog and Mirrlog
F:\ sapdata1 -5
Now after restore to new directory structure
R:\origlogA and mirrlogB
T:\origligB and mirrlogA
V:\sapdata1-4
we would need to insert the below log_file_name_convert and db_file_name_convert to identify the directory structure.
SQL>alter database create standby controlfile as ‘c:\standby.ctl’;
DB_FILE_NAME_CONVERT=('/u01/app/oracle/oradata/RECOVER2', '/u01/app/oracle/oradata/RECLONE','/u01/oradata/RECOVER2','/u01/oradata/RECLONE')
LOG_FILE_NAME_CONVERT=('/u01/app/oracle/flash_recovery_area/RECOVER2', '/u01/app/oracle/flash_recovery_area/RECLONE')
alter system set DB_FILE_NAME_CONVERT='D:\oracle\ATC\','V:\oracle\ATC\';
*.db_file_name_convert='D:\oracle\ATC\','V:\oracle\ATC\'
*.LOG_FILE_NAME_CONVERT='D:\oracle\ATC\oraarch','U:\oracle\ATC\oraarch'
