Configuring Oracle Standby Database on Ubuntu Linux

In this post I will write the steps I took to configure a Physical Standby Database on two Ubuntu Hosts versions Breezy Badger or Dapper Drake and running Oracle 10g R2. For all this to work


following assumptions must hold true:

Both hosta and hostb must have the same operating system and version.

The directory structure for datafiles on both hosts must be the same.

1. Create the Primary Database.

I have created a small database on hosta called testdb1 to use as the Primary Database using dbca. Then I took a cold backup of this database.

2. Move backups of Primary Database to Standby Database on hostb.

Next I moved the backup datafiles to hostb in their corresponding same locations with scp like this:

scp /u10/oradata/testdb1/* oracle@hostb:/u10/oradata/testdb1/

3. Create the Standby Database control file.

Then I went back to hosta and started up testdb1 Primary Database, and created a special control file. It is important to create the controlfile after you shutdown the Primary Database cleanly and take the backup and not before. This controlfile will be used on hostb to startup the Standby Database.
Log in to Primary Database on hosta as SYSDBA and run the command:

SQL> alter database create standby controlfile as ‘/tmp/sbycontrol.ctl’

NOTE: This file is binary. Use a different filename than the controlfiles of the Primary Database and watch out not to overwrite a current Primary Database control files.

Then move the created controlfile to hostb. You can choose any location on hostb. Later, you will manually edit the initialization parameter file of the Standby Database to indicate the chosen location.


SQL> !scp /tmp/sbycontrol.ctl oracle@hostb:/u10/oradata/testdb1/

4. Create the initialization parameter file for the Standby Database.

Create a pfile from the spfile of the Primary Database to be used later as the intialization parameter file to startup the Standby Database. The initialization parameter file you will create from the Primary Database will be manually modified to introduce Standby Database parameters later.
Log in to Primary Database on hosta as SYSDBA and run the command:

SQL> create pfile=’/tmp/inittestdb1.ora’ from spfile

Then copy the file to $ORACLE_HOME/dbs on hostb, to the default location. You will edit the file later on hostb.

SQL> !scp /tmp/inittestdb1.ora oracle@hostb:$ORACLE_HOME/dbs

5. Set up networking configuration between Primary and Standby Databases.

You have to make sure that both databases will be able to communicate using their own service names pointing to each other. Create service names on both hosta and hostb which will connect to each other and ship archived logs.
Edit the tnsnames.ora file on hosta and hostb and add the service name TO_STANDBY to both.

in tnsnames.ora on hosta add:

TO_STANDBY =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = hosta)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = testdb1)))

in tnsnames.ora on hostb add:

TO_STANDBY =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = hostb)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = testdb1)))


You must also create the password file on standby as well as primary database to enable logins from each other. The password for these must be the same.
on hosta and hostb do

$orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=yourpassword

6. Edit the pfile created for Standby Database

In step 4 above an initialization parameter file was created from the Primary Database. Now is time to make that file the initialization parameter of the Standby Database and add the parameters necessary to startup the Standby Database in Standby mode.

The file created earlier should be in $ORACLE_HOME/dbs directory like:
/usr/local/oracle/product/10.2.0.1/dbs/inittestdb1.ora

According to the method of synchronization (redo transport) you choose between the Primary Database and the Standby Database certain parameters will be set in this file.

For my installation I used Standby Logs on the Standby Database and Archived Log Transmission.

I edited the Standby Database initialization parameter file as below, where bold is what I added.

…
*.compatible=’10.2.0.1.0′
*.control_files=’/u10/oradata/testdb1/sbycontrol.ctl’…
*.log_archive_dest_1=’LOCATION=/u01/arch’
*.log_archive_dest_2=’LOCATION=/u11/arch’
*.standby_archive_dest=’LOCATION=/u00/sbylogs’
*.standby_file_management=’AUTO’
*.remote_archive_enable=’TRUE’
*.log_archive_format=’testdb1_%t_%s_%r.arc”
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/usr/local/oracle/admin/testdb1/udump’
...

Once you edited the Standby Database initialization parameter file next is to make an spfile out of it and startup the Standby Database.

7. Create spfile for Standby Database and start it up

Log in to Standby Database on hostb as SYSDBA and run the command:

SQL> create spfile from pfile=’/usr/local/oracle/product/10.2.0.1/dbs/inittestdb1.ora’;

Next startup the Standby Database on hostb with NOMOUNT

SQL> startup nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes

And then MOUNT the Standby Database as standby!

SQL> alter database mount standby database;
Database altered.

Now the Standby Database has started, it has got a control-file, but hasn’t got standby logs where redo data will be applied as redo comes from the Primary Database and is applied to the standby database via its archivelogs. Next is to add these standby logs on hostb to the standby database testdb1.
NOTE: If you want to configure fail-over between the two host where each can assume the role of Primary Database / Standby Database then is a good idea to add these standby logs to the Primary Database as well. I don’t explain how to do that here. In this post I have only explained how to configure “A physical standby database for a primary database” Is best if you read the Oracle documentation for that. There is a good explanation of that here:
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm

8. Add Standby Logs to your Standby Database

I added 11 logs using the following statement.
Log in to Standby Database on hostb as SYSDBA and run the command:

SQL> alter database add standby logfile ‘/u00/sbylogs/sbylog07.log’ size 5M;
…
SQL> alter database add standby logfile ‘/u00/sbylogs/sbylog011.log’ size 5M;

It is important and there is a good argument to chose twice the number of primary database redo logs as standby logs on the standby database, for reasons of archiving contention. That is during log shipping you don’t want the system to get bottlenecked, due to redo log switches and archiving not being able to cope with them.

9. On hosta on the Primary Database start shipping logs

Log in to Primary Database on hosta as SYSDBA and run the command:
alter system set log_archive_dest_3=’SERVICE=TO_STANDBY ARCH’;
alter system set log_archive_dest_state_3=enable scope=both;
This will start the process of shipping the archivelogs to the standby database from a brand new log archive destination which is pointing to the standby database, check the service name used TO_STANDBY
NOTE: At this point is best to have windows open to both alert.log logs of standby and primary and see what is hapenning and to spot errors

10. Put the Standby Database in recovery mode (Last Step)

Last step, is to put the Standby database in recovery mode. That is the standby database in the “Physical” configuration stays mounted in recovery mode. Is not open for querying.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

That is it! You have a standby database on hostb!

If you want to test and see your system working, for examble by changing something in the primary database on hosta and seeing the change being applied to the standby database on hostb you will need to open the standby database. Steps on how to stop the standby database from receiving Archivelogs from the primary database, and how to put it back to the recovery mode, are below.
Switch from log shipping mode to read only standby database
Cancel Redo Apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Open the database for read-only access:

SQL> ALTER DATABASE OPEN;

Now you can query the standby database and see the changes you made in primary database. You can NOT do DML on the standby database! That is this is a one way synch from primary to standby.

Change the standby database from being open for read-only access to performing Redo Apply: Terminate all active user sessions on the standby database, you my need to kill the sessions except yours.
Restart Redo Apply. To start Redo Apply, issue the following statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Now the standby database is in recovery mode/ standby mode and is receiving Archivelogs from the primary database.

In future post I will write on how to create a physical standby database using RMAN and how to configure Oracle Data Guard Broker for automatic switchover/failover operations and High Availability solutions.



References:

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm
http://www.adp-gmbh.ch/ora/data_guard/create_physical_standby_db.html

Using Data Guard to do fail-over and other cool features here:
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14230/cli.htm#i1005573