The purpose of this blog entry is to show you how to create manually a physical standby database.
The first will be a basic primary and standby database setup on 2 different machines. In this case, I used VMWARE.
If we afford to lose few minutes of data then this is very good cheap solution for disaster recovery solution.
The Standby Database
• Oracle supplied disaster recovery (DR) solution.
• Operates in 2 modes –manual and automatic.
• No GUI.
• Available since 7.3.4.
• Available with Std Edition one
Step by step document to create manual Standby Database
1 - Prerequisites
• Same Oracle software version 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.
• Each primary and standby database must have its own control file.
• If primary and standby database
• s are placed on the same system, initialization parameters must be adjusted correctly.
Preparing Primary Database for Standby Database creation
Configuration in Primary Database
2 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\ORCLC
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
3 - Identify the primary database Datafiles
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\ORCLC\SYSTEM01.DBF
C:\ORACLE\ORADATA\ORCLC\UNDOTBS01.DBF
C:\ORACLE\ORADATA\ORCLC\CWMLITE01.DBF
C:\ORACLE\ORADATA\ORCLC\DRSYS01.DBF
C:\ORACLE\ORADATA\ORCLC\EXAMPLE01.DBF
C:\ORACLE\ORADATA\ORCLC\INDX01.DBF
C:\ORACLE\ORADATA\ORCLC\ODM01.DBF
C:\ORACLE\ORADATA\ORCLC\TOOLS01.DBF
C:\ORACLE\ORADATA\ORCLC\USERS01.DBF
C:\ORACLE\ORADATA\ORCLC\XDB01.DBF
10 rows selected.
4 - Make a cold backup copy of Primary Database
Make a cold backup copy of primary database by performing following steps:
4.1 Shutdown the Primary Database
Issue the following statement to shutdown the primary database.
SQL> shutdown immediate;
4.2 Copy the Datafiles to standby location
Copy the redo log files and Datafiles identified in section 3 to standby location.
Note: Primary Database must be shutdown while copying the files.
5 - Restart the Primary Database
Execute following command to restart the Primary Database.
SQL> startup;
6 - 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 'D:\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 cold backup of Datafiles and redo logs. Copy this standby controlfile to the standby server.
Configuration in Standby Database
7 - Create pfile for standby database from the primary database
Copy the pfile from the primary database to the standby server
8 - 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,
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
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.
instance_name - If both primary and standby databases are to reside in the same server, specify a different value for the standby database than the primary database. If standby database will be in a different server, then ignore this.
lock_name_space - - If both primary and standby databases are to reside in the same server, 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.)
9 - 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 ssss -intpwd yyyy -startmode manual
10 - Configure tnsnames for standby database in the standby server
11 - Start Physical standby database in standby mode
Start up the stand by database using following commands
C:\>set oracle_sid=ssss
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='C:\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.
12. Now you have to transfer archivelogs from Primary database and apply them to standby database
12.1 create a script in primary server to run under windows task at desired intervals (15 mins or 30 mins etc.), to transfer archivelogs from primary to standby:-
move _arch.cmd
==================
pushd C:\oracle\admin\scripts
set ORACLE_SID=xxx
set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
%ORACLE_HOME%\bin\sqlplus dbo/dbo @move_archivelog.sql
robocopy D:\oracle\backup\arch\roc8 \\172.30.36.13\oracle\backup\arch\roc8 /MIR /COPY:DT /FFT /log:robocopystatus.log
The sql below is to force a new archivelog from primary database:
Apply_archivelog.sql
==================
ALTER SYSTEM SWITCH LOGFILE;
exit;
**** Windows robocopy utility is used above to send archivelogs from arch folder in primary server to arch folder in standby server ****
12.2 apply the archivelogs arriving from primary server to the standby database every 15 mins or 30 mins under a windows task in standby server as follows:-
apply_arch.cmd
==============
pushd C:\oracle\admin\scripts
set ORACLE_SID=ssss
set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
%ORACLE_HOME%\bin\sqlplus /nolog @ Apply_archivelog.sql
Apply_archivelog.sql
===============
connect sys/oracle as sysdba;
set timing on
set echo on
spool apply_archp.log
recover standby database;
AUTO
spool off
exit
License requirements by Oracle:
Standby database doesn’t need Oracle license for 10 days in calendar year.
Failover – In this type of recovery, nodes are arranged in a cluster and share
one disk array. A Failover cluster is a group of systems, bound together into a
common resource pool. In this type of recovery method, the Production node
acts as the primary node. When the primary node fails, one of the surviving
nodes in the cluster acts as the primary node. Solutions like Oracle Failsafe
(included with Oracle Database EE or SE, SE1), or third party vendor solutions
(e.g. Veritas, HP Service Guard, HACMP, Linux HA - Heartbeat) are used to
manage Failover environments. In this type of environment, Oracle permits
licensed Oracle customers to run some Technology Programs on an
unlicensed spare computer for up to a total of ten separate days in any given
calendar year. Once the primary node is repaired, you must switch back to the
primary node. Once the failover period has exceeded ten days, the failover
node must be licensed.
Source: Oracle SOFTWARE INVESTMENT GUIDE
Testing the environment
Once primary and Standby database is configured and transportation log is configured to transfer we need to test it thoroughly.
We can execute some DML and DDL in primary database. After archive logs are applied in Standby database we should able to see those DLL and DML changes in standby database.
To check it Open the database on read only mode.
SQL> alter database open read only;
After the test re-start the database in mount mode. If you OPEN the database you have to recreate the standby database again.
1. SQL> shutdown immediate;
2. SQL>startup mount;
Checking log file in Standby database:
If you check log file you might see error and warning like below.
ORCL: SQL> recover standby database;
ORA-00279: change 239868 generated at 10/20/2010 12:50:02 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_1
00_%U_.ARC
ORA-00280: change 239868 for thread 1 is in sequence #100
Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 240264 generated at 10/20/2010 13:05:02 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_1
01_%U_.ARC
ORA-00280: change 240264 for thread 1 is in sequence #101
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_
100_6CY89YTC_.ARC' no longer needed for this recovery
ORA-00308: cannot open archived log
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_
101_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
TOM: SQL> spool off
If you see error like ORA-00308: cannot open archived log.
