Create a physical standby database

To create a dataguard config, it can be done in two step, preparing the primary database, and then preparing the secondary database.

To create this standby database for oracle Dataguard, I will follow what was indicated on this webpage, it is our reference guide, but there was some unclear point on this webpage.

All the following step needs to be done on the primary database, I will let you known when the work is done on primary database, and we needs some works on the secondary database.

Primary database

Enable force logging

This is a simple step, simply execute this query in sqlplus on our primary database

SQL> alter database force logging;

Put the database in archive log

To get your database in archive log, you can follow the steps on this webpage. I show you how to do that ! Very simple !

Setting parameter for pfile

We now have to set some parameter, this can be set in a file that oracle will read up at startup time.
We have first to create the file that we have to edit, in sqlplus enter this command :

SQL> create pfile='/home/oracle/pfile.txt' from spfile;

We now have a new file created by oracle :

/home/oracle/pfile.txt

This file is created with the setting already set in your spfile, it contains some settings, but there is still some missing parameter for us!
Almost all settings that needs to be set, will be in this file.
If you want more information about this pfile and spfile, let's take a look at external links section of this webpage.

Setting a unique identifier for the database

As there is two or more database running in dataguard, there must be a way to identify each database, this will be done with this parameter :

DB_UNIQUE_NAME

In my case, I will use this name :

ora_db_1

and hence I will add this line in the pfile.txt :

DB_UNIQUE_NAME=ora_db_1

Really important is that DB_UNIQUE_NAME is different on each database running the dataguard.

Archive settings

By default, oracle put each archive file generated in flash recovery area. We will tell to oracle to put these arch files in an other directory. Once again, this following parameter needs to be put in the pfile.txt created before, so let's now add theses settings :

LOG_ARCHIVE_DEST_1=
 'LOCATION=/directory/where/we/want/to/put/arc/files/ 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=ora_db_1'

You only have to change the directory where you want to put the arch files. This is represented by the parameter that you probably guess :

LOCATION

So, don't forget to first create the directory where you want to place your arch file !
This is for a local directory, each arch file created by the database, will be put in this directory.
But we have also to transmit these arch files to the other server (secondary database), like that the database will be synchronize.
The code to insert inside the file is :

LOG_ARCHIVE_DEST_2=
 'SERVICE=standby_db_connector LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=ora_db_2'

The parameter :

SERVICE

has to be set to the connect descriptor that are within the file "tnsname.ora", see this section to configure it properly !
In this example :

ora_db_2

is the DB_UNIQUE_NAME of the remote database, and hence here the secondary database. So, as you might guess, I will give the name :

ora_db_2

as DB_UNIQUE_NAME for my secondary database when we will prepare settings for secondary database.

Enabling archiving

After setting the parameter for the arch files, we have to simply enable them for the next start up of database. So, add simply these two line in the pfile.txt :

LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE

So, as you might guess :

LOG_ARCHIVE_DEST_STATE_1=ENABLE

will enable archiving of log file in our local directory, and :

LOG_ARCHIVE_DEST_STATE_2=ENABLE

will enable trasnmitting arch file to the other server where the secondary database is.

Creating standby controlfile

As the secondary database is a special one for dataguard, you have to create a special controlfile for the standby database, this can be done in sqlplus with this command :

SQL> alter database create standby controlfile as '/home/oracle/std-controlfile.ctl';

During execution of this last command, you will see something like that in your alert.log file :

alter database create standby controlfile as '/home/oracle/std-controlfile.ctl'
Wed Sep 24 18:02:28 2008
Clearing standby activation ID 2236495638 (0x854e3716)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Completed: alter database create standby controlfile as '/home/oracle/std-controlfile.ctl'
Wed Sep 24 18:03:17 2008

As you might guess, you will have to execute these 4 command :
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;

in your primary database (in case of the primary will be become a secondary database after a switch over ) and your secondary database.
So, at this point, only execute these command on our primary database :
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;

I'll remember you that it needs to be done when we will configure the secondary database.

Formatting arch file

Oracle will create each arch file with a default unix name for each file created. We can change that to meet our expectation.
So, let's add this line in pfile.txt :

LOG_ARCHIVE_FORMAT='arch_log_%t_%s_%r.arc'

Each arch file created will have the name formatted with this parameter.
And don't forget that the arch file created and formatted with this parameter will be put in the directory specified by parameter LOCATION of LOG_ARCHIVE_DEST_1 of this section.
For more information about this settings, let's go to external links section, and choose log_archive_format, it will explain you what are the following caracter means :

%t
%s
%r

Fal settings

There is two important settings that needs to be done, to be able to contact the remote database, transmit the arch file and apply them to the remote database.
As we are configurating the primary database settings, we have to set :

fal_server

with the connect descriptor to reach the secondary database. So, in my case, I will add this line in the pfile.txt :

fal_server=standby_db_connector

As some of you perhaps notice it, I used the same connect descriptor as I said in section archive setting for parameter service of log_archive_dest_2 settings, read back section archive settings if you have any trouble understanding what I means, and it will be crystal clear after that ! I hope so…
If you still don't set any connect descriptor, please follow instruction of this section.

Note
the fal_client is to be set to the actual db , primary db, but needs to be test before, with a switch-over by instance.

Convertion parameter

DB Unique name

There is still some parameter that needs to be set before using dataguard.
As each database as a unique name, before applying any arch file, we have to tell to oracle, that he needs to change the name of unique name to the one where he apply the arch file. As you might guess, this settings will only be valid on the database that will receive the arch files, so on secondary database, but he also needs to be set on the primary database, in case a switch occur, and hence the primary database will become a secondary database!
So, on our primary database, the settings in pfile.txt will be :

DB_FILE_NAME_CONVERT='ora_db_1','ora_db_2'

will convert the db unique name from the primary db to the secondary db. This example is taken from the primary db.
So, on the secondary db, you will have :

DB_FILE_NAME_CONVERT='ora_db_2','ora_db_1'

Arch location

When the database will transmit the arch file, oracle have to known where to place these arch file on the other database. The parameter used for that is :

log_file_name_convert

By instance, on my primary database, my arch file are in this directory :

/u01/app/oracle/oradata/arch/

and on the secondary database, the arch file are in this directory :

/u01/app/oracle/oradata/arch/

Yes, it is the same ! So, you simply have to add one line in pfile.txt :

log_file_name_convert='/u01/app/oracle/oradata/arch/','/u01/app/oracle/oradata/arch/'

Note that if on the secondary database, the arch file are, by instance in an other directory, let's say :

/u01/app/oracle/secondary_database/archfile/

The line to add in pfile would be this one :

log_file_name_convert='/u01/app/oracle/oradata/arch/','/u01/app/oracle/secondary_database/archfile/'

Re-starting database

We now have to re-start the database to take into account the settings that we did !
So, shutdown your db, and start it up again with the new pfile created !
1. shutdown your db :

SQL> shutdown

2. Create the spfile from the pfile you edited before, because by default, the db startup with the spfile, if you don't specify a pfile :

SQL> create spfile from pfile='/home/oracle/pfile.txt';

3. and startup your db :

SQL> startup

Then, create your oracle password file authentification, see external links on how to do that !

Check that the listener on each database is running with the following command :

# lsnrctl status

If you see something like that :

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 17-SEP-2008 09:48:14

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dell-poweredge-debian4.kotnet.org)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused

means that you listener is not started, so start it by issuing this command :

# lsnrctl start

and you have to see something like that :

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 17-SEP-2008 09:49:20

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dell-poweredge-debian4.kotnet.org)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                17-SEP-2008 09:49:20
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dell-poweredge-debian4.kotnet.org)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Normally, you already enabled the connect descriptor to connect to a remote database, you can now still test it by issuing this command :

# tnsping name_of_connect_descriptor

Reachability issues

As primary database have to transmit something to the standby database, each database must be able to reach the other !
This can be done by creating aliases in the file :

tnsnames.ora

The file is probably located in this directory :

$ORACLE_HOME/network/admin/

and you have to replace the shell variable :

$ORACLE_HOME

by the value given by your shell, you can see the value of the variable with this command :

# echo $ORACLE_HOME

and you will get probably something like that :

/u01/app/oracle/product/10.2.0/db_1

so, normally the :

tnsnames.ora

is here in my case in the directory :

/u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

In this file, you have to tell to oracle, how he can reach the other database.
I will provide you an example, in my case, the file at the beginning contained these things :

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORAASZ =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dell-poweredge-debian4.kotnet.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oraasz)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

In this file, there is two connect descriptor, the one that is interresting for us is this one :

ORAASZ

This is to be able to connect to our local database, because this file is on the server named :

dell-poweredge-debian4.kotnet.org

Before doing a test, start up your listener :

# lsnrctl startup

Depending if it was up or not, you could obtain two output of this command :

You will probably get something like that :

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 13-NOV-2008 10:54:13

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dell-poweredge-debian4.kotnet.org)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                13-NOV-2008 10:54:13
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dell-poweredge-debian4.kotnet.org)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

As you can see on the last line, the command completed successfully and so your listener is running properly !

You can test it now by executing this command in a bash session of oracle user :

# tnsping oraasz

and you will see probably something like that :

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 15-SEP-2008 10:57:27

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dell-poweredge-debian4.kotnet.org)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oraasz)))
OK (10 msec)

The last line :

OK (10 msec)

indicated us that we can reach the database with the connect descriptor that we provided.
So, let's edit this file to be able to get an answer from an other server where our database is.
In this case, we will say that the ip address of the server where is the remote database is :

192.168.0.151

, the identifier of the remote database is ( we use DB_UNIQUE_NAME to identify a database with dataguard ) :

ora_db_2

and we will choose for the connect descriptor, this name :

standby_db_connector

so, we can add a new connect descriptor with these new informations :

standby_db_connector =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.151 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora_db_2 )
    )
  )

Before doing any test of remote connection, don't forget to enable the listener on the remote database ( and in this case on the server where the ip address is : 192.168.0.151 ) with this command :

lsnrctl start

As stated before you can test the connection with the remote database like this :

tnsping standby_db_connector

and you will see +/- the same ouput as to test the connection on the local database.

Voilà, if all the settings are properly set, the configuration on our primary database is now done ! You only have to set it now for secondary database, and if you already understand what you did on primary database, it will be really simple, as it is almost the same with some modification !

We can see wich connect is on, on the remote db, by doing :
lsnrctl services, and we have to see the db_unique_name that correspond to the service name of the other host.

Starting up the standby database

Now, that all is set up properly, you can start up the standby database to be able to fetch the arch log from the primary database, if it is up also of course ! This can be done with the following command :

SQL> alter database recover managed standby database disconnect from session;

You have to do that command when your database is in mount mode and not in open or shutdown mode.

Errors

If you get an message like this one in the aler_ora.log file :

Fri Sep 19 10:23:18 2008
RFS[2]: Archived Log: '/u01/app/oracle/oradata/arch1/ora_1_281_660158745_.arc'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: No standby redo logfiles created

This means that you need to re-create your redo logfile on the database where you get the message! Don't forget that these redo-logfile have to be the same as on the other database !

To add standby redolog to a standby database ( you can add also to the primary database, because it will need it when there will be a fail-over or switch-over ) :

SQL> alter database ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/oraasz/std-redolog03.log') size 51M;

Check that the size is the same as the one on the other database, check against the redo log !
Create as much group as there are redo log group on the other database !

I still got this error :

FAL[server]: Fail to queue the whole FAL gap

and this one too :
FAL[client]: Failed to request gap sequence 
 GAP - thread 1 sequence 2-101
 DBID 2236488982 branch 665833900
FAL[client]: All defined FAL servers have been attempted.

but I can transfer the archiver, this can be seen with this command, all the arch are transfered to other site :

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

But they are never applied, this can be related to a backup with rman, see this web page :
http://dba.ipbhost.com/index.php?showtopic=9069

oracle bug :
Note: 185076.1 : Standby Redo Logs are not Created when Creating a 9i Data Guard DB with RMAN
Note: 352879.1 : ORA-19527 reported in 10gR2 Standby database
Bug 4724888 - Dataguard Error In 10.2

I had once a problem to transfer deleted archivelog of my primary database. I delete these archive log with OS commands, and the db still was thinking that there was there, and wanted to transfer it to the standby database, but of course it never works ! So, you have to tell to the db that these archive log doesn't exist anymore, uncatalog them from the db. You can do it with rman :

delete archivelog all;

you can also use the crosscheck command to check if there are still present :

crosscheck archivelog all;

and also :

change archivelog all uncatalog;

will be probably the most simple ! like that you won't have to transfer the files.
So, we will try to copy all the database with simple copy !

Starting up a standby database

If you try to startup a db that got a standb controlfile, you will probably get this error :

SQL> startup
ORACLE instance started.

Total System Global Area  926941184 bytes
Fixed Size                  1222672 bytes
Variable Size             239077360 bytes
Database Buffers          683671552 bytes
Redo Buffers                2969600 bytes
Database mounted.
ORA-16004: backup database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/oraasz/system01.dbf'

List to do for standby database

Transfer the pfile originating from the primary db, adapt it to the secondary db, with a new db unique name, mirror effect on some settings…
create the standby datatafile, move the std-controlfile on the old control file !
Adata the tnsname to reach the other db.

adapt the pfile on secondary db with this text :

DB_UNIQUE_NAME=ora_db_2
LOG_ARCHIVE_DEST_1=
 'LOCATION=/u01/app/oracle/oradata/arch/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=ora_db_2'
LOG_ARCHIVE_DEST_2=
 'SERVICE=primary_db_connector LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=ora_db_1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT='arch_log_%t_%s_%r.arc'
fal_server=primary_db_connector
DB_FILE_NAME_CONVERT='ora_db_2','ora_db_1'
log_file_name_convert='/u01/app/oracle/oradata/arch/','/u01/app/oracle/oradata/arch/'

instead of this one from the primary db :
DB_UNIQUE_NAME=ora_db_1
LOG_ARCHIVE_DEST_1=
 'LOCATION=/u01/app/oracle/oradata/arch/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=ora_db_1'
LOG_ARCHIVE_DEST_2=
 'SERVICE=standby_db_connector LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=ora_db_2'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT='arch_log_%t_%s_%r.arc'
fal_server=standby_db_connector
DB_FILE_NAME_CONVERT='ora_db_1','ora_db_2'
log_file_name_convert='/u01/app/oracle/oradata/arch/','/u01/app/oracle/oradata/arch/'

adapt the tnsnamem on std database
insert this code in tnsname of std database :

primary_db_connector =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.180 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora_db_1 )
    )
  )

instead of this one from primary database :
standby_db_connector =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.181 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora_db_2 )
    )
  )

copy the std control file from primary db over the old one !
create the spfile from the pfile adapted !
create the password file on the primary and standby database.
set this param for password authentification :

REMOTE_LOGIN_PASSWORDFILE=shared

create the standby redo log file :

ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;

startup the db in mount mode :

SQL> startup mount

start up the standby database in physical mode, and so starting applying the arch log re-apply from the primary database :

SQL> alter database recover managed standby database disconnect from session;

And then you can finally check if dataguard is properly running with this link.

For any question, send an e-mail or post a thread in the forum !

External links

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License