Data Guard is easy

By / / 7 minutes of reading

Data Guard is easy!

Every Oracle database with a certain value for the business should be protected with a standby database. How you can set up a standby environment for Standard Edition Two will be covered in a dfiferent blog. Today the goal is to create a Data Guard configuration fast, easy and repeatable.
This blog will not cover the generic usage of Data Guard and the protection modes. There are other blogs available like this one: https://www.carajandb.com/en/blog/2015/dataguard-protection-modes-en.
The configuration that I’m using contains two databases on two servers:
Primary Database:
Server: wagner
Databasename: RICHARD
DB-Unique-Name: RICHARD_W

Standby Database:
Server: strauss
Databasename: RICHARD
DB-Uniquen-Name: RICHARD_S

Database-Version: 12.2.0

This this example I’m using Oracle Managed Files (OMF). Ther’s another blog on the details and restrictions if you are going to use OMF and Multitenant (https://www.carajandb.com/en/blog/2016/multitenant-in-a-dataguard-environment).

Primary Database

To ensure that all directories are correctly set you need to set the parameter [inlinecode]db_unique_name[/inlinecode] before creating the database because otherwise the data files will be in the wrong directory. And for me it’s helpful to have a symbolic link in the admin directory like follows:

cd /u01/app/oracle/admin
mkdir -p RICHARD/config RICHARD/adump RICHARD/dpdump RICHARD/spfile RICHARD/scripts RICHARD/xdb_wallet
ln -s RICHARD RICHARD_W
mkdir -p /u02/oradata/RICHARD/config
mkdir -p /u03/orabackup/RICHARD/config

Now the database will be configured for data guard.

sqlplus / as sysdba
ALTER SYSTEM SET dg_broker_config_file1='/u02/oradata/RICHARD/config/dg_config1.cfg';
ALTER SYSTEM SET dg_broker_config_file2='/u03/orabackup/RICHARD/config/dg_config2.cfg';
ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SET log_archive_format='%t_%s_%r.arc' scope=spfile;
ALTER SYSTEM SET "_query_on_physical"=false SCOPE=spfile;
SHUTDOWN IMMEDIATE
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

CAUTION: The parameter [inlinecode]_query_on_physical[/inlinecode] is not supported and shouldn’t be used due to Oracle’s recommendation. But it will avoid that the standby database will be opened red only while the apply process is still running. And that requires the Active Data Guard License.

Now it’s time to create the Standby redologs. Oracle recommends that they should have the same size as the online redolog and the number should be one more than the online redologs. Most of the time I’m working with three online redologs per thread so I need 4 standby redlogs per thread.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 size 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 size 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 size 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 size 200M;

The primary database is no preparred for data guard.

Oracle Net

The next step will be to create the proper Oracle Net configuration:
The listener needs a static configuration for the database because otherwiwse the restart of the instance will end up in an RMAN disconnect during database duplication. A second entry is needed for the data guard broker.
As I try to script as many steps as possible I’m always using shell with stdin and stdout redirection.

cat < $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
   (SID_LIST =
      (SID_DESC =
         (GLOBAL_DBNAME = RICHARD_W.carajandb.intra)
         (ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
         (SID_NAME = RICHARD))
   (SID_DESC =
      (GLOBAL_DBNAME = RICHARD_W_DGMGRL.carajandb.intra)
      (ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
      (SID_NAME = RICHARD))
   )
EOCAT

lsnrctl stop
lsnrctl start

After hte listener has been configured the TNS-Aliases are going to be defined.
I’m using two aliases for every database: one for the normal operating using a service_name and one for the duplication using a SID.

cat < $ORACLE_HOME/network/admin/tnsnames.ora
cat < $ORACLE_HOME/network/admin/tnsnames.ora
RICHARD_W.carajandb.intra =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = wagner)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = RICHARD_W.carajandb.intra)
    )
  )

RICHARD_S.carajandb.intra =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = strauss)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = RICHARD_S.carajandb.intra)
    )
  )

RICHARD_wagner.carajandb.intra =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = wagner)(PORT = 1521))
    (CONNECT_DATA =
      (SID = RICHARD)
    )
  )

RICHARD_strauss.carajandb.intra =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = strauss)(PORT = 1521))
    (CONNECT_DATA =
      (SID = RICHARD)
    )
  )
EOCAT

Finally sqlnet.ora will be changed. This step is only for lazy admins as I’m going to add the default domain.

echo "NAMES.DEFAULT_DOMAIN=carajandb.intra" > $ORACLE_HOME/network/admin/sqlnet.ora

The tnsnames.ora and sqlnet.ora can be copied to the standby server.

Parameterfile

Very often you see that a parameter file is being created from the spfile of the primary database and than modified to reflect the parameters of the standby database. I found this cumbersome because the correct spfile will be created automatically during the RMAN duplication procedure. That’s why I’m using a dummy init.ora file with some basic configuration just to start the instance for the RMAN duplicate.
One of the advantages of the method is that I can use this file on both servers so it helps if you need to recreate the standby database regardless if there has been a switch in the meantime.

cat < /u01/app/oracle/admin/RICHARD/config/initdup.ora
*.compatible=’12.2.0.1′
*.db_block_size=8192
*.db_domain=’carajandb.intra’
*.db_name=’RICHARD’
*.diagnostic_dest=’/u01/app/oracle’
*.log_archive_format=’%t_%s_%r.arc’
*.pga_aggregate_target=512M
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=2000M
*.undo_tablespace=’UNDOTBS1′
EOCAT

This file can now be copied to the standby server.

RMAN Script

The final script will be for rman duplicate. Be careful because the both servers need different scripts.

cat < /u01/app/oracle/admin/RICHARD/config/duplicate.rcv
connect target sys/manager@RICHARD_S
connect auxiliary sys/manager@RICHARD_wagner
run{
allocate channel prmy1 type disk;
allocate auxiliary channel stby1 type disk;
DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
  PARAMETER_VALUE_CONVERT 'RICHARD_S','RICHARD_W','strauss','wagner'
  SET "db_unique_name"="RICHARD_W"
  SET "instance_number"="1"
  NOFILENAMECHECK;
}
EOCAT

We won’t use this file yet because it only works if RICHAR_W is the standby database.

Standby Database

I assume that there is no directory structure for any database yet on the standby server but the software is already installed.
So first we need to create some directoriers:

mkdir -p /u01/app/oracle/admin
cd /u01/app/oracle/admin

mkdir -p RICHARD/adump RICHARD/dpdump RICHARD/pfile RICHARD/scripts RICHARD/xdbwallet RICHARD/config /u02/oradata/RICHARD/config /u03/orabackup/RICHARD/config
ln -s RICHARD RICHARD_S

Now the environment variables for the database can be set a usual.

echo "RICHARD:/u01/app/oracle/product/12.2.0/dbhome_1:N" >> /etc/oratab

ORACLE_SID=RICHARD
ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK

Oracle Net

Both tnsnames.ora and sqlnet.ora should already exists because we copied them from the primary server. But the listener.ora needs to be created.

cat < $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
   (SID_LIST =
      (SID_DESC =
         (GLOBAL_DBNAME = RICHARD_S.carajandb.intra)
         (ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
         (SID_NAME = RICHARD))
      (SID_DESC =
         (GLOBAL_DBNAME = RICHARD_S_DGMGRL.carajandb.intra)
         (ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
         (SID_NAME = RICHARD))
)
EOCAT
lsnrctl stop
lsnrctl start

RMAN Scipt

Last but not least we need to create the rman duplicate script as the init.ora has already be copied from the primary server.

cat < /u01/app/oracle/admin/RICHARD/config/duplicate.rcv
connect target sys/manager@RICHARD_W
connect auxiliary sys/manager@RICHARD_strauss
run{
allocate channel prmy1 type disk;
allocate auxiliary channel stby1 type disk;
DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
  PARAMETER_VALUE_CONVERT 'RICHARD_W','RICHARD_S','wagner','strauss'
  SET "db_unique_name"="RICHARD_S"
  SET "instance_number"="1"
  NOFILENAMECHECK;
}
EOCAT

So we are done with the preparation. This whole configuration needs to be created only once and you can start with the next step, whenever you need to recreate the standby database.

RMAN Duplicate

We can now start the standby instance with the dummy init.ora and run the rman duplicate.

sqlplus / as sysdba
startup nomount pfile='/u01/app/oracle/admin/RICHARD/config/initdup.ora'
exit

rman @/u01/app/oracle/admin/RICHARD/config/duplicate.rcv

HINT: Before you start the rman duplicate you need to ensure that the connects to the target and auxiliary (standby) works from both servers.

Data Guard Broker

Now it’s to,e to configure the data guard broker. The broker process must be startet first and in this step we need to active flashback database as that feature will be disabled after the rman duplication.
Standby Database:

sqlplus / as sysdba
ALTER DATABASE FLASHBACK ON;
ALTER SYSTEM SET dg_broker_start=true;

Now we swith over to the primary server start the broker process and create the configuration:

sqlplus / as sysdba
ALTER DATABASE FLASHBACK ON;
EXIT;

dgmgrl /
CREATE CONFIGURATION 'RICHARD_DG' AS
PRIMARY DATABASE IS 'RICHARD_W'
CONNECT IDENTIFIER IS 'RICHARD_W.carajandb.intra';

ADD DATABASE 'RICHARD_S' AS
CONNECT IDENTIFIER IS 'RICHARD_S.carajandb.intra'
MAINTAINED AS PHYSICAL;

EDIT DATABASE 'RICHARD_W' SET PROPERTY StandbyFileManagement='AUTO';
EDIT DATABASE 'RICHARD_S' SET PROPERTY StandbyFileManagement='AUTO';
EDIT DATABASE 'RICHARD_W' SET PROPERTY 'NetTimeout'=60;
EDIT DATABASE 'RICHARD_S' SET PROPERTY 'NetTimeout'=60;
EDIT DATABASE 'RICHARD_W' SET PROPERTY 'LogXptMode'='SYNC';
EDIT DATABASE 'RICHARD_S' SET PROPERTY 'LogXptMode'='SYNC';
EDIT DATABASE 'RICHARD_W' SET PROPERTY dbDisplayName = 'RICHARD_W.carajandb.intra';
EDIT DATABASE 'RICHARD_S' SET PROPERTY dbDisplayName = 'RICHARD_S.carajandb.intra';
EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

ENABLE CONFIGURATION

Done!
Tip: At this point you should take a walk and grap a cup of coffee!
Why? Because it can take some minutes before the synchronisazion between the databases has finished. And you should wait with the command “SHOW CONFIGURATION” to not get worried in the meantime.
After you finished your coffee the configuration should look like this:

DGMGRL> show configuration

Configuration - RICHARD_DG

Protection Mode: MaxAvailability
Members:
RICHARD_W - Primary database
RICHARD_S - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 28 seconds ago)

Conclusion

Using this scripts I’m always able to recreate the standby databas with minimum efford regardless where the primary database is currently running.
Just one last hint: please avoid the use of a db_unique_name which has a kind of qualification, like DBNAME_STANDBY or similar. You should better use the name of the data center (e.g. SF / LA) or a number (S1 / S2) to identify the locations.

Good Luck

Weitere interessante Artikel entdecken

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top