This document contains a guide on setting up standby databases for maximum protection, using command line mode, and avoiding using the GUI. To do this Oracle9i has a feature called Data Guard and the following sections describe the tasks undertaken to set-up primary and standby servers and a couple of WINDOWS servers.
Database PROD is replicated from production Server to Standby Server via DataGuard
Data Guard Operational Prerequisites:
-
Same Oracle software release must be used for both primary and standby databases. The operating system running on primary and standby locations must be same, but operating system release may not need to be same.
-
The Primary Database must run in ARCHIVELOG mode.
-
The hardware and Operating system architecture on primary and standby location must be same.
-
Each primary and standby database must have its own control file.
Architecture:
The Oracle9i Data Guard architecture incorporates the following items:
Primary Database - A production database that is used to create standby databases. The archive logs from the primary database are transferred and applied to standby databases. Each standby can only be associated with a single primary database, but a single primary database can be associated with multiple standby databases.
Standby Database - A replica of the primary database.
Log Transport Services - Control the automatic transfer of archive redo log files from the primary database to one or more standby destinations.
Network Configuration - The primary database is connected to one or more standby databases using Oracle Net.
Log Apply Services - Apply the archived redo logs to the standby database. The Managed Recovery Process (MRP) actually does the work of maintaining and applying the archived redo logs.
Role Management Services - Control the changing of database roles from primary to standby. The services include switchover, switchback and fail over.
The services required on the primary database are:
Log Writer Process (LGWR) - Collects redo information and updates the online redo logs. It can also create local archived redo logs and transmit online redo to standby databases.
Archiver Process (ARCn) - One or more archiver processes make copies of online redo logs either locally or remotely for standby databases.
Fetch Archive Log (FAL) Server - Services requests for archive redo logs from FAL clients running on multiple standby databases. Multiple FAL servers can be run on a primary database, one for each FAL request.
The services required on the standby database are:
Fetch Archive Log (FAL) Client - Pulls archived redo log files from the primary site. Initiates transfer of archived redo logs when it detects a gap sequence.
Remote File Server (RFS) - Receives archived and/or standby redo logs from the primary database.
Archiver (ARCn) Processes - Archives the standby redo logs applied by the managed recovery process (MRP).
Managed Recovery Process (MRP) - Applies archive redo log information to the standby database.
Step-by-Step Stand by Database Configuration:
Step1: Configure Listener in Production Server and Standby Server.
-
TIPS: You should try to Create Listener (Standby) by using Net Configuration Assistant on Standby Server.
-
TIPS: assume Listener already configure with PROD name on Primary Node. If Listener not configured on Primery Node , You Should Create Listener by using Net Configuration Assistant on Primary Server.
Step2: Configure TNSNAMES.ORA in Production Server and Standby Server. following TNSNAMES.ORA entry on Production Database and Standby Database
# Connection string for Primary Instance.
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Production IP)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD)
)
)
# Connecting string for Standby Instance
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY IP)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD)
)
)
Step3: Put your production database in Archive Log mode if your database not running in Archive log mode add following entries in init.ora file in Production Server.
LOG_ARCHIVE_START=TRUE
LOG_ARCHIVE_DEST_1='LOCATION=C:\oracle\database\archive MANDATORY REOPEN=30'
LOG_ARCHIVE_DEST_2='SERVICE=STANDBY REOPEN=300'
LOG_ARCHIVE_DEST_STATE_1=enable
LOG_ARCHIVE_DEST_STATE_2=enable
LOG_ARCHIVE_FORMAT=ARC%S.arc
REMOTE_ARCHIVE_ENABLE=true
STANDBY_FILE_MANAGEMENT=AUTO
STANDBY_ARCHIVE_DEST = 'C:\standby\archive '
# db_file_name_convert: do not need; same directory structure
# log_file_name_convert: do not need; same directory structure
Step4 : After add above syntax in init.ora file, copy init.ora file from production server to Standby server in Oracle_Home\Database\ folder.
Step5 : On the both system, the same directory structure was set-up
Step6 : Place production database in FORCE LOGGING mode by using following statement:
SQL> alter database force logging;
Database altered.
Step7 : Identify the primary database Data files:
SQL> select name from v$datafile;
Step8 : Make a copy of Production data files and redo flog file by performing following steps:
Shutdown the Primary Database
SQL> shutdown immediate and put your primary database in Archive log mode after archive log enable shutdown the database the database.
Copy the Datafiles and redo log files to standby location by using OS Command
Note: Primary Database must be shutdown while coping the files.
Step9 : Restart the Production Database
SQL> startup;
Step10 : Create Control file for Standby Database Issue the following command on production database to create control file for the standby database.
SQL> Alter database create standby controlfile as 'c:\controlfile_standby.ctl';
Database altered.
Note: The filename for newly created standby control file must be different of current control file of the production database. Also control file for standby database must be created after the last timestamp for the backup Datafiles.
Step11 : Create init.ora file for standby database.
Copy init.ora file from Production Server to Stand by Server in Database folder in oracle home directory and add following entries:
LOG_ARCHIVE_START = TRUE
LOG_ARCHIVE_DEST_1 = 'LOCATION=c:\oracle\database\archive MANDATORY'
LOG_ARCHIVE_FORMAT = arch%s.arc
REMOTE_ARCHIVE_ENABLE = true
STANDBY_FILE_MANAGEMENT = AUTO
LOG_ARCHIVE_MIN_SUCCEED_DEST=1
STANDBY_ARCHIVE_DEST = 'C:\standby\archive '
fal_server = FAL
fal_client = STANDBY
# db_file_name_convert: do not need; same directory structure
# log_file_name_convert: do not need; same directory structure
Note: Although most of the initialization parameter settings in the text initialization parameter file that you copied from the primary system are also appropriate for the physical standby database, some modifications need to be made.
Edit created pfile from primary database.
control_files - Specify the path name and filename for the standby control file.
standby_archive_dest - Specify the location of the archived redo logs that will be received from the primary database.
db_file_name_convert - Specify the location of the primary database datafiles followed by the standby location of the datafiles. This parameter will convert the filename of the primary database datafiles to the filename of the standby datafile filenames. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site then this parameter is required.
log_file_name_convert - Specify the location of the primary database logs followed by the standby location of the logs. This parameter will convert the filename of the primary database log to the filenames of the standby log. If the standby database is on the same system as the primary database or if the directory structure where the logs are located on the standby site is different from the primary site then this parameter is required.
log_archive_dest_1 - Specify the location where the redo logs are to be archived on the standby system. (If a switchover occurs and this instance becomes the primary database, then this parameter will specify the location where the online redo logs will be archived.)
standby_file_management - Set to AUTO.
remote_archive_enable - Set to TRUE.
instance_name - If this parameter is defined, specify a different value for the standby database than the primary database when the primary and standby databases reside on the same host.
lock_name_space - Specify the standby database instance name. Use this parameter when you create the physical standby database on the same system as the primary database. Change the INSTANCE_NAME parameter to a value other than its primary database value, and set this LOCK_NAME_SPACE initialization parameter to the same value that you specified for the standby database INSTANCE_NAME initialization parameter.
Also change the values of the parameters background_dump_dest, core_dump_dest and user_dump_dest to specify location of the standby database.
Step12 : Create a Window service in Standby Server
If standby database is running on windows system, then oradim utility is used to create windows service. Issue following command from the command prompt window
C:\>oradim -new -sid PROD -intpwd PROD -startmode a
Step: 13 Start Physical standby database
Start up the stand by database using following commands
C:\>set oracle_sid=PROD
C:\>sqlplus /nolog
SQL> conn sys/prod as sysdba
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;
Database altered.
Step: 14 Initiate Log apply services The example includes the DISCONNECT FROM SESSION option so that log apply services run in a background session.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
Step: 15 Now go to production database prompt
SQL> alter system switch logfile;
Database altered.
Step: 16 Verifying the Standby Database On standby database query the V$ARCHIVED_LOG view to verify that redo log received.
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Result:
SEQUENCE# FIRST_TIME NEXT_TIME
14 25-APR-05 16:50:34 25-APR-02 16:50:42
15 25-APR-05 16:50:42 25-APR-02 16:50:47
16 25-APR-05 16:50:47 25-APR-02 16:51:52
Archive the current log on the primary database using following statement.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
On standby database query the V$ARCHIVED_LOG view
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Result:
SEQUENCE# FIRST_TIME NEXT_TIME
14 25-APR-05 16:50:34 25-APR-02 16:50:42
15 25-APR-05 16:50:42 25-APR-02 16:50:47
16 25-APR-05 16:50:47 25-APR-02 16:51:52
17 25-APR-05 16:51:52 25-APR-02 17:34:00
TIPS: Now connect system/manager on production database and create table or insert row in any table.
Now connect as sys on production database and execute following SQL statement
SQL> alter system switch logfile;
On standby database execute following SQL statements
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;
Database altered. And check whether the changes applied on the standby database or not.
Step: 17 Query V$MANAGED_STANDBY
Query the physical standby database to monitor log apply and log transport services activity at the standby site.
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
Result:
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_LOG 1 4205 0 0
RFS RECEIVING 0 0 0 0
RFS RECEIVING 1 3524 2445 2445
RFS WRITING 1 4205 14947 20480
If we do the same query on the production database
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
ARCH CLOSING 1 4203 2049 124
ARCH CLOSING 1 4204 1 1551
LGWR WRITING 1 4205 14947 1
From the query on the primary database, we see the current sequence being written to in the redo log area is 4205, and on the standby database we also see the current archive log being applied is for sequence 4205. In the directory that receives archive files on the standby database, the file DWH0P01_0000004205.arc will exist and will be the same size as the redo log on the primary database. However the primary database will not have DWH0P01_0000004205.arc as a file in the archive area, as a log switch will not have occurred yet, but both databases are synchronized at the same sequence and block number, 14947.
Step: 18 Log files to check on both systems
On production database in the bdump directory, the alert log and files generated by lgwr and lnsx can be checked for any problems On standby database in the bdump directory, the alert log and files generated by mrpx can be checked for any problems.
