Standby Database

create Standby Database

Architecture

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 failover.

Data Guard Broker - Controls the creation and monitoring of Data Guard. It comes with a GUI and command line interface.

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



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.
• If primary and standby databases are placed on the same system, initialization parameters must be adjusted correctly.
• Primary database must be FORCE LOGGING mode.



Preparing Primary Database for Standby Database creation
Ensure the primary database in ARCHIVELOG mode

Ensure the primary database in ARCHIVELOG mode using following command.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:/oracle/ora92/database/archive/PRIM
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11

Enable database in FORCE LOGGING mode
Place primary database in FORCE LOGGING mode using following SQL statement:
SQL> alter database force logging;
Database altered.


Identify the primary database Datafiles
SQL> select name from v$datafile;

NAME
-------------------------------
/U01/ORACLE/PRIM/SYSTEM01.DBF
/U01/ORACLE/PRIM/UNDOTBS01.DBF
/U01/ORACLE/PRIM/CWMLITE01.DBF
/U01/ORACLE/PRIM/DRSYS01.DBF
/U01/ORACLE/PRIM/EXAMPLE01.DBF
/U01/ORACLE/PRIM/INDX01.DBF
/U01/ORACLE/PRIM/ODM01.DBF
/U01/ORACLE/PRIM/TOOLS01.DBF
/U01/ORACLE/PRIM/USERS01.DBF
/U01/ORACLE/PRIM/XDB01.DBF

10 rows selected.

Make Backup of Primary Database
Make a closed backup copy of primary database by performing following steps:

Shutdown the Primary Database

Issue the following statement to shutdown the primary database.
SQL> shutdown immediate;

Copy the Data files to standby location

Copy the redo log files and Data files to standby location.

Note: Primary Database must be shutdown while coping the files.


Restart the Primary Database

Execute following command to restart the Primary Database.
SQL> startup;

Create Control file for Standby Database

Issue the following command on primary database to create control file for the standby database.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS
2 ‘/u02/oracle/oradata/stby/control_sb01.ctl’;

Database altered.

The filename for newly created standby control file must be different of current control file of the primary database. Also control file for standby database must be created after the last timestamp for the backup Datafiles.

Create pfile from for standby database from the primary database
Create pfile from the server parameter file of the primary database; this pfile can be copied to standby location and modified.
SQL> CREATE PFILE=‘/u01/oracle/ora92/database/initstby.ora’ from spfile;

File created.


Set initialization parameters on physical standby database
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.

db_name - Not modified. The same name as the primary database.

compatible - Not modified. The same as the primary database, 9.2.0.0.0.

control_files - Specify the path name and filename for the standby control file.

log_archive_start - Not modified. The same as the setting for the primary database, TRUE

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.

(Refer Annexure for initialization parameter settings for primary and standby database.)
Create a Window service
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 stby -intpwd stby -startmode manual

Configure listeners & tnsnames for standby and primary databases
Configure listeners in listeners.ora as follows
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u02/oracle/ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = Prim)
(ORACLE_HOME = /u02/oracle/ora92)
(SID_NAME = Prim)
)
(SID_DESC =
(GLOBAL_DBNAME = stby)
(ORACLE_HOME = /u02/oracle/ora92)
(SID_NAME = stby)
)
)
Restart the listeners using LSNRCTL utility.
% lsnrctl stop
% lsnrctl start
Also make an entry into tnsnames.ora for standby database.
stby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
)
)


Start Physical standby database
Start up the stand by database using following commands
C:/>set oracle_sid=stby

C:/>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 25 17:13:26 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile=‘/u01/oracle/ora92/database/initstby.ora’ 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.

Enabling archiving to Physical Standby Database
To configure archive logging from the primary database to the standby site the LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n parameters must be defined.

Issue following commands from primary database session:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=‘SERVICE=stby’ SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

System altered.

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.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now

SQL> recover managed standby database cancel;
Media recovery complete.

Now go to primary database prompt

SQL> alter system switch logfile;

Go to stand by database prompt

SQL> alter database open read only;

Database altered.


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
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

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
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

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
Now connect scott/tiger@prim on primary database and create table or insert row in any table.

Now connect as sys on primary 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.




Parameter file for Primary Database

*.aq_tm_processes=1
*.background_dump_dest=‘/u02/oracle/admin/Prim/bdump’
*.compatible=‘9.2.0.0.0’
*.control_files=‘/u01/oracle/prim/CONTROL01.CTL’,
‘/u01/oracle/prim/CONTROL02.CTL’,’/u01/oracle/prim/CONTROL03.CTL’
*.core_dump_dest=‘/u02/oracle/admin/Prim/cdump’
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=‘‘
*.db_file_multiblock_read_count=16
*.db_name=‘Prim’
*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=PrimXDB)’
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name=‘Prim’
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1=‘LOCATION=C:/oracle/ora92/database/archive/PRIM MANDATORY’
*.log_archive_dest_2=‘SERVICE=stby LGWR SYNC AFFIRM’

The LGWR SYNC AFFIRM keywords indicate that the Log writer should synchronously write updates to the online redo logs to this location and wait for confirmation of the write before proceeding. The remote site will process and archive these standby redo logs to keep the databases synchronized. This whole process can impact performance greatly but provides maximum data security.

*.log_archive_dest_state_2=‘ENABLE’
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled=‘FALSE’
*.remote_login_passwordfile=‘EXCLUSIVE’
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled=‘FALSE’
*.timed_statistics=TRUE
*.undo_management=‘AUTO’
*.undo_retention=10800
*.undo_tablespace=‘UNDOTBS1’
*.user_dump_dest=‘/u02/oracle/admin/Prim/udump’

Parameter file for Standby Database

*.aq_tm_processes=1
*.background_dump_dest=‘/u02/oracle/admin/stby/bdump’
*.compatible=‘9.2.0.0.0’
*.control_files=‘/u02/oracle/oradata/stby/CONTROL_SB01.CTL’
*.core_dump_dest=‘/u02/oracle/admin/stby/cdump’
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=‘‘
*.db_file_multiblock_read_count=16
*.db_name=‘Prim’
*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=PrimXDB)’
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name=‘stby’
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1=‘LOCATION=/u02/oracle/admin/stby/archive’
*.log_archive_dest_state_1=enable
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled=‘FALSE’
*.remote_login_passwordfile=‘NONE’
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled=‘FALSE’
*.timed_statistics=TRUE
*.undo_management=‘AUTO’
*.undo_retention=10800
*.undo_tablespace=‘UNDOTBS1’
*.user_dump_dest=‘/u02/oracle/admin/stby/udump’
*.standby_archive_dest=‘/u01/oracle/ora92/database/archive/PRIM’
*.db_file_name_convert=‘/u01/oracle/prim’,’/u02/oracle/oradata/stby’
*.log_file_name_convert=‘/u01/oracle/prim’,’/u02/oracle/oradata/stby’
*.standby_file_management=AUTO
*.remote_archive_enable=TRUE
*.lock_name_space=stby


Protect Primary Database
Now that Data Guard is configured and running the primary database can be prevented from applying updates unless the update has been sent to at least one standby location. Connect to the primary database and execute:

ALTER DATABASE SET STANDBY DATABASE PROTECTED;

Cancel Managed Standby Recovery
To stop managed standby recovery:

SQL> -- Cancel protected mode on primary
SQL> CONNECT sys/password@prim AS SYSDBA
SQL> ALTER DATABASE SET STANDBY DATABASE UNPROTECTED;
SQL>
SQL> -- Cancel recovery if necessary
SQL> CONNECT sys/password@stby1 AS SYSDBA
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;
The database can subsequently be switched back to recovery mode as follows:

SQL> -- Startup managed recovery
SQL> CONNECT sys/password@stby1 AS SYSDBA
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT PFILE==‘/u01/oracle/ora92/database/initstby.ora’
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

SQL> -- Protect primary database
SQL> CONNECT sys/password@prim AS SYSDBA
SQL> ALTER DATABASE SET STANDBY DATABASE PROTECTED;


Activating A Standby Database
If the primary database is not available the standby database can be activated as a primary database using the following statements:

SQL> -- Cancel recovery if necessary
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Since the standby database is now the primary database it should be backed up immediately. The previous primary database can then be configured as a standby.

Backup Standby Database
Backups of the standby database can only be performed if the database is shut down or in read only mode. Read only mode is best for managed recovery systems as archive logs will still be transfered during the backup process, thus preventing gap sequences. Once the server is in the desired mode simply copy the appropriate database files.

Database Switchover
A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements:

-- Convert primary database to standby
CONNECT sys/change_on_install@prim1 AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

-- Shutdown primary database
SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database
STARTUP NOMOUNT PFILE==‘/u01/oracle/ora92/database/initstby.ora’
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;


-- Convert standby database to primary
CONNECT sys/change_on_install@stby1 AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database
SHUTDOWN IMMEDIATE;

-- Open old standby database as primary
STARTUP PFILE==‘/u01/oracle/ora92/database/initstby.ora’
This process has no affect on alternative standby locations. The process of converting the instances back to their original roles is known as a Switchback. The switchback is accomplished by performing another switchover.

Database Failover
Graceful Database Failover occurs when database failover causes a standby database to be converted to a primary database:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
This process will recovery all or some of the application data using the standby redo logs, therefore avoiding reinstantiation of other standby databases. If completed successfully, only the primary database will need to be reinstatiated as a standby database.

Forced Database Failover changes one standby database to a primary database. Application data may be lost neccessitating the reinstantiation of the primary and all standby databases.