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 db_unique_name 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 _query_on_physical 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
No comments on “Data Guard is easy”