CREATING STANDBY (DISASTER RECOVERY) DATABASE WITHOUT USING DATAGUARD – by Ghulam Abbasi
This article is focussed to provide basic understanding on how to create an standby (Disaster Recovery) database. In this article we are not using the dataguard therefore we have to go through few additional steps. The advantage of using dataguard is that you can create standby databases for primary databases already configured to use FRA (Flash Recovery Area). Also dataguard provide a great feature to automate your synchronization process and manage primary and standby databases with more control and flexibility.
My next article will cover the issue of how to synchronize archive log files automatically using some database tables and unix scripts.
The Standby database is always created in read only mode. Though you can switch it back to normal database at any time, bear in mind that standby database is always managed with statement ‘read only’. Suppose open the standby database we use following statement:
SQL> alter database open read only;
If you issued following statement instead, the database will switch to normal (read / write) mode and you cannot switch it back to standby mode again.
SQL> alter database open;
1. Configuring init.ora file (orcl primary database)
1.1 Remove FRA feature to allow recognised archive log file name (while not using dataguard).
Note: You don’t need this step if using dataguard for synchronizing archive log files
[oracle@orcl_primary]$ sqlplus / as sysdba
SQL> alter system reset db_recovery_file_dest_size scope=SPFILE sid='*';
System altered.
SQL> alter system reset db_recovery_file_dest scope=SPFILE sid='*';
System altered.
SQL> exit
[oracle@orcl_primary]$
1.2 Now set a new path where archive logs should be created by database.
- Create a new directory for archive logs
[oracle@orcl_primary]$ cd $ORACLE_HOME/db_1/
[oracle@orcl_primary]$ mkdir archivelogs_orcl
[oracle@orcl_primary]$ chmod 775 archivelogs_orcl
- Add/ Change Set the parameters log_archive_dest and log_archive_format in init.ora file.
[oracle@orcl_primary]$ sqlplus / as sysdba
SQL> ALTER SYSTEM SET log_archive_dest = '/app/oracle/product/10.2.0/db_1/archivelogs_orcl' scope=SPFILE sid='*' ;
SQL> ALTER SYSTEM SET log_archive_format='%t_%s_%r.arc' ;
SQL> ALTER SYSTEM SET
db_file_name_convert='/oradata/ORCL/','/oradata/ORCL_STDBY/';
- Now restart primary database to let new settings in init.ora take effect.
SQL> shutdown immediate;
SQL> startup;
SQL> create pfile from spfile;
File created.
NOTE: The new SPFILE / PFILE are normally created at following path in unix environment (10g only).
Please note that :
- spfile is created by default as ‘spfile’+’instance name’+.’ora’.
- pfile is created by default as ‘init’+’instance name’+.’ora’.
Remember, we need to move initORCL.ora form following location to standby server later in section 3.
$ORACLE_HOME/db_1/dbs/
2. Create backup for Standby Database (orcl primary database)
2.1 Create a separate directory to store standby backup. This backup set will be
moved to standby database.
- Create a new directory to store backup for standby database.
[oracle@orcl_primary]$ cd $ORACLE_HOME/db_1/
[oracle@orcl_primary]$ mkdir backup_standby
[oracle@orcl_primary]$ chmod 775 backup_standby
2.2 Take an standby backup of the primary database now.
- Logon to primary database target using RMAN and run following script to backup in standby mode.
[oracle@orcl_primary]$ rman
RMAN> connect target /
RMAN> sql 'alter system archive log current';
run {
allocate channel d01 device type disk format
'/app/oracle/product/10.2.0/db_1/backup_standby/%d_%t_%s_%U.dbf';
backup as compressed backupset database include current controlfile for
standby plus archivelog;
}
3. Move Required files to Standby Database
Please note that this article is tested on two databases (primary and standby) where:
- Same software version was installed on both servers (i.e. 10g).
- Similiar oracle home and directory structure was used on both servers.
- The Primary database was running in archive log mode.
- Primary host name is set to orcl_primary
- Standby host name is set to orcl_standby
- Instance name is used as ‘orcl’ for both the primary and standby databases.
3.1 Move init.ora (initORCL.ora) file from primary database to standby database.
Copy initORCL.ora from ORACLE_HOME/db_1/dbs/ directory on primary server to the same directory on sandby database (i-e. ORACLE_HOME/db_1/dbs/).
3.2 Move standby backup set from primary database to standby database.
Note: You need to create directory backup_standby under $ORACLE_HOME/dbs/ on standby server before copying backup files.
Copy all files from $ORACLE_HOME/db_1/backup_standby/ directory on primary server to the same directory on sandby server (i-e. $ORACLE_HOME/db_1/backup_standby/).
4. Create TNSNAMES entry on standby database pointing to primary server.
TNSNAMES.ORA file normally exists at $ORACLE_HOME/db_1/network/admin/
Edit this file and add following lines at the end of this file.
Note: Host name should be same as your primary host name. In this case the primary hostname is set as orcl_primary. Also you must be able to ping the primary database from standby database. You may use following command to make sure you primary database is accessible.
$ ping orcl_primary
[oracle@orcl_standby]$ vi ORACLE_HOME/db_1/network/admin/tnsname.ora
ORCL_PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl_primary)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
:wq
[oracle@orcl_standby]$
5. Restore standby database
Before restoring the standby database we need to delete any existing control files and datafiles from standby database locations.
5.1 Remove all datafiles and control files from all locations of standby database.
[oracle@orcl_standby]$ cd /oradata/datafile/
[oracle@orcl_standby]$ rm *
[oracle@orcl_standby]$ cd /oradata/controlfile/
[oracle@orcl_standby]$ rm *
5.2 Shutdown and start database with new spfile in nomount mode.
[oracle@orcl_standby]$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup nomount pfile='/datadr/app/oracle/product/10.2.0/db_1/dbs/initORCL.ora';
SQL> create spfile from pfile='/datadr/app/oracle/product/10.2.0/Db_1/dbs/initORCL.ora';
File created.
SQL> exit
5.3 Shutdown and start database with new spfile in nomount mode.
[oracle@orcl_standby]$ rman
RMAN>
connect target sys/password@ORCL;
connect auxiliary /;
run {
allocate channel d01 device type disk;
allocate auxiliary channel aux01 device type disk;
duplicate target database for standby
dorecover;
}
Note: There are three types of connection RMAN normally deals with. The database connection, the Catalogue DB and the Standby database connection. The RMAN statement ‘connect auxiliary /;’ is used to connect with standby database. This connection uses TNS connection entry (ORCL_PRIMARY) we just configured in step 4 of this article. This connectivity validates to control file sequence number, the log file sequence number and some other necessary checks.
RMAN> exit
[oracle@orcl_standby]$ sqlplus / as sysdba
SQL> shutdown;
SQL> Startup nomount;
SQL> alter database mount standby database;
SQL> alter database open read only;
SQL> alter system register;
SQL> exit
The database is now restored on DR (standby) server.
6. Applying archive logs to the standby database
6.1 Create new archive log files on primary database.
Here we will force the primary database to create some new archive logs.
Connect to primary database first:
[oracle@orcl_primary]$ sqlplus / as sysdba
SQL>alter system archive log current;
Note: Repeat this statement for few times to create more log files. As a result some new archive log files with be created at following location on primary database.
/app/oracle/product/10.2.0/db_1/archivelogs_orcl/
6.2 Move new archive log files to DR (Standby) database.
Copy the new arc files created as result of step 6.1 to the same location of DR (standby database).
/app/oracle/product/10.2.0/db_1/archivelogs_orcl/
6.3 Apply new archive log files to the DR (Standby) Database.
SQL> set autorecovery on;
SQL> recover standby database;
SQL> alter database open read only;
SQL> exit
$
Above recovery will fail if any active sessions are made to DR database. I would write following script to automate recovery. The script can be set in crontab to run every night.
#!/bin/sh
ORACLE_SID='orcl'
export ORACLE_SID
sqlplus / as sysdba << EOF
shutdown immediate;
startup nomount;
alter database mount standby database;
alter database open read only;
set autorecovery on;
recover standby database;
alter database open read only;
exit;
EOF
exit 0
Now both the databases are synchronised upto this point of time. Use following statement to check current log file sequence number on both databases. Both databases must show you the same last sequence number in following query.
[oracle@orcl_primary]$ sqlplus / as sysdba
SQL> select * from V_$LOGHIST order by SEQUENCE#;
[oracle@orcl_standby]$ sqlplus / as sysdba
SQL> select * from V_$LOGHIST order by SEQUENCE#;
GMA
