My Environment
I used VirtualBox to test Oracle 12c 12.2.0.1 Data Guard setup of one primary and one standby (using two VMs) – and switchover testing. To do this, all hosts must know about the primary and standby servers / VMs in /etc/hosts or DNS, and the VirtualBox host if that is what you are using.
Prerequisites – to creating the standby
A database exists that will be your primary – preferrably 11gR2 or higher version.
At least a software only installation with preferrably matching DB version numbers on standby target.
If you want to do Dataguard it is best to force logging so on the primary:
ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SWITCH LOGFILE;
Setup the redo logs for Dataguard
If you don’t have standby log files on the primary (they are not created by default so you probably do not), then create them (they will be useful during switch over):
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/GGTARGET/standby_redo01.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/GGTARGET/standby_redo02.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/GGTARGET/standby_redo03.log’) SIZE 50M;
Turn on flashback
ALTER DATABASE FLASHBACK ON; — flashback on is completely optional – not required unless you want to recover a failed primary – not described here
DB Names on the primary and later on the standby
Show db names on the primary:
SQL> show parameter db_name
NAME TYPE VALUE
———————————— ———– ——————————
db_name string GGTARGET
SQL> show parameter db_unique_name
NAME TYPE VALUE
———————————— ———– ——————————
db_unique_name string GGTARGET
When we get to the standby, The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME. In this case, my standby database name will be ‘GGSTBY’.
I’ll set Oracle manage file creation automatically – as I’m creating the standby on another new VM/server – but care should be taken, as the Oracle documentation says:
Setting STANDBY_FILE_MANAGEMENT to AUTO causes Oracle to automatically create files on the standby database and, in some cases, overwrite existing files. Care must be taken when setting STANDBY_FILE_MANAGEMENT and DB_FILE_NAME_CONVERT so that existing standby files will not be accidentally overwritten.
If the standby database is on the same system as the primary database, then ensure that the primary and standby systems do not point to the same files.
And also important, on the primary, point the archives to the standby destination and have them sent/managed automatically.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
log_archive_dest_2=’SERVICE=ggtargetsby ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ggstby’
log_archive_config=’DG_CONFIG=(ggtarget,ggstby)’
If you have not installed the Oracle DB binaries on the standby – do it now (Pick: Install database software only) install to matching mount points (make standby mounts and directories look like primary) if at all possible.
SQLNet Listener Service and Tnsnames.ora configuration
Entries for the primary and standby databases are needed in the “$ORACLE_HOME/network/admin/tnsnames.ora” files on both servers. You can create these using the Network Configuration Utility (netca) or manually. The following entries were used during this setup. Notice the use of the SID, rather than the SERVICE_NAME in the entries. This is important as the broker will need to connect to the databases when they are down, so the services will not be present.
# put the following tnsnames.ora file on both the primary and the standby
$ORACLE_HOME/network/admin/tnsnames.ora
ggtarget =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = o68-122db)(PORT = 1521))
)
(CONNECT_DATA =
(SID = GGTARGET)
)
)
ggstby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol69stby)(PORT = 1521))
)
(CONNECT_DATA =
(SID = GGTARGET)
)
)
The “$ORACLE_HOME/network/admin/listener.ora” file on the primary server contains the following configuration. Note to allow dgmgrl to do automatic restarts at least one sid description / global_dbname should be of the format – db_unique_name_DGMGRL.db_domain. The entry below has a NULL db_domain name.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = GGTARGET_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = GGTARGET)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol68-122db)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
On the Standby server – ol69stby – we’ll need an explicit entry for the database.
The “$ORACLE_HOME/network/admin/listener.ora” is configured as follows.
The broker will need to connect to the database when it’s down, we can’t rely on auto-registration with the listener,
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = GGSTBY_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = GGTARGET)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol69stby)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Once the config files are in place, restart the listener on both servers.
lsnrctl stop
lsnrctl start
Creating the Dataguard Standby
Create a parameter file for the standby database called “/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initggstby.ora” as follows – will be used to startup nomount:
*.db_name=GGTARGET
Make the mount points for auxiliary stuff
mkdir -p /u01/app/oracle/oradata/GGTARGET/pdbseed
mkdir -p /u01/app/oracle/oradata/GGTARGET/pdb1
mkdir -p /u01/app/oracle/fast_recovery_area/GGTARGET
mkdir -p /u01/app/oracle/admin/GGTARGET/adump
Create a password file with pw that matches the primary – actually I scp’d the password file before this would work… and for grins restarted the listener… finicky… that 12.2…
[oracle@o68-122db dbs]$ pwd
/u01/app/oracle/product/12.2.0/dbhome_1/dbs
[oracle@o68-122db dbs]$ scp orapwGGTARGET oracle@ol69stby:/u01/app/oracle/product/12.2.0/dbhome_1/dbs
oracle@ol69stby’s password:
orapwGGTARGET 100% 2048 2.0KB/s 00:00
[oracle@o68-122db dbs]$ lsnrctl stop
But if you are feeling lucky…
orapwd file=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwGGTARGET password=JuNotReal11# force=Y entries=10 format=12
# note: if you do not specify “format=12” and your db version is 12.2 – 12.2 will enforce password complexity by default
Set your SID – I put this in my .bash_profile
[oracle@ol69stby admin]$ echo $ORACLE_SID
GGTARGET
or
export ORACLE_SID=GGTARGET
sqlplus / as sysdba
SQL> STARTUP NOMOUNT PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initGGSTBY.ora
Create the Standby
Now run the create standby out of rman:
rman TARGET sys/JunotReal11#@GGTARGET AUXILIARY sys/JunotReal11#@ggstby
You should see the following message on connect:
Recovery Manager: Release 12.2.0.1.0 – Production on Sat Sep 2 14:37:11 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: GGTARGET (DBID=3607112047)
connected to auxiliary database: GGTARGET (not mounted)
Now – still connected to rman run this:
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name=’GGSTBY’ COMMENT ‘the initial standby’
NOFILENAMECHECK;
At the end of the process you should see;
_09_02/o1_mf_1_93_dtp015s1_.arc thread=1 sequence=93
media recovery complete, elapsed time: 00:00:01
Finished recover at 02-SEP-17
Finished Duplicate Db at 02-SEP-17
Now enable dataguard broker / dgmgrl
# on both primary and standby
ALTER SYSTEM SET dg_broker_start=true;
So we have a working standby database. If – it was grid based ASM (THIS ONE IS NOT SO WE DON’T HAVE TO DO THIS STOP) we would have to register it with Grid Infrastructure next. Something like as follows: in this example don’t do it cuz there is no clusterware/grid/rac or even ASM.
[oracle@server2 ~]$ srvctl add database -db STDBY -oraclehome /u01/app/oracle/product/12.1.0.2/dbhome_1 \ > -role physical_standby -startoption MOUNT -policy automatic -diskgroup data,reco
Create and use the DG Broker configuration file – first on the primary
[oracle@o68-122db ~]$ dgmgrl /
DGMGRL for Linux: Release 12.2.0.1.0 – Production on Sat Sep 2 15:13:52 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type “help” for information.
Connected to “GGTARGET”
Connected as SYSDG.
CREATE CONFIGURATION DGCONFIG AS PRIMARY DATABASE IS GGTARGET CONNECT IDENTIFIER IS GGTARGET;
Configuration “dgconfig” created with primary database “ggtarget”
Now on while still on the primary side – add the standby to the configuration – and then enable the configuration
DGMGRL> ADD DATABASE GGSTBY AS CONNECT IDENTIFIER IS GGSTBY MAINTAINED AS PHYSICAL;
…
DGMGRL> enable configuration;
Enabled.
Yes it is enabled but something is wrong. The enable took forever and came back successful as above and then this:
DGMGRL> show configuration
Configuration – dgconfig
Protection Mode: MaxPerformance
Members:
ggstby – Primary database
ggtarget – Physical standby database
Error: ORA-16664: unable to receive the result from a member
Fast-Start Failover: DISABLED
Configuration Status:
ERROR (status updated 28 seconds ago)
You want to see the data guard log file? Data Guard log is in the same directory as your text based alert log – V$DIAG_INFO will point you to it. Mine is here:
[oracle@ol69stby trace]$ view drcGGTARGET.log
[oracle@ol69stby trace]$ pwd
/u01/app/oracle/diag/rdbms/ggstby/GGTARGET/trace
Modifying the listener.ora on both sides to include SERVICE_NAME – along with listener restart “lsnrctl stop & start” appears to have fixed the issue as follows – the change I made is in bold. For completeness and role transition purposes, I made the change on both sides listener.ora file.
Standby side listener.ora sid list:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = GGSTBY_DGMGRL.localdomain)
(SERVICE_NAME = GGSTBY.localdomain)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = GGTARGET)
)
)
Primary side listener.ora sid list:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = GGTARGET_DGMGRL.localdomain)
(SERVICE_NAME = GGTARGET.localdomain)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = GGTARGET)
)
)
Show the primary / standby configuration
Now that DG Broker seems to be configured properly – lets look at it again / show the config before we switchover to the standby.
DGMGRL> show configuration verbose;
Configuration – dgconfig
Protection Mode: MaxPerformance
Members:
ggtarget – Primary database
ggstby – Physical standby database
Properties:
FastStartFailoverThreshold = ’30’
OperationTimeout = ’30’
TraceLevel = ‘USER’
FastStartFailoverLagLimit = ’30’
CommunicationTimeout = ‘180’
ObserverReconnect = ‘0’
FastStartFailoverAutoReinstate = ‘TRUE’
FastStartFailoverPmyShutdown = ‘TRUE’
BystandersFollowRoleChange = ‘ALL’
ObserverOverride = ‘FALSE’
ExternalDestination1 = ”
ExternalDestination2 = ”
PrimaryLostWriteAction = ‘CONTINUE’
ConfigurationWideServiceName = ‘GGTARGET_CFG’
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
Validate and Switchover – Using dgmgrl – validate your confirm and if you are good – do the switch over
DGMGRL> validate database ‘ggstby’;
Before you switchover – take a VM snapshot of both the VMs ( documented here )- just in case something goes wrong – it’s easy to restore / revert back.
DGMGRL> switchover to ‘ggstby’;
Here is what a switchover looked like – though the log below is of the 2nd switchover which was a switch back to the original primary:
DGMGRL> switchover to ‘ggtarget’;
Performing switchover NOW, please wait…
Operation requires a connection to database “ggtarget”
Connecting …
Connected to “GGTARGET”
Connected as SYSDBA.
New primary database “ggtarget” is opening…
Operation requires start up of instance “GGTARGET” on database “ggstby”
Starting instance “GGTARGET”…
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Database mounted.
Database opened.
Connected to “GGSTBY”
Switchover succeeded, new primary is “ggtarget”
DGMGRL>
Lets look at the status on both sides:
New primary – yes was original too – cuz of two switchovers:
select db_unique_name,db_unique_name,name,open_mode,log_mode,database_role,flashback_on,dataguard_broker,switchover_status,guard_status from v$database;
DB_UNIQUE_NAME NAME OPEN_MODE LOG_MODE
—————————— ——— ——————– ————
DATABASE_ROLE FLASHBACK_ON DATAGUAR SWITCHOVER_STATUS GUARD_S
—————- —————— ——– ——————– ——-
GGTARGET GGTARGET READ WRITE ARCHIVELOGPRIMARY YES ENABLED TO STANDBY NONE
Ok, what mode is the standby in queried from the primary (assume LOG_ARCHIVE_DEST_2 used for archivelog transport)?
SQL> select recovery_mode from v$archive_dest_status where dest_id = 2;
RECOVERY_MODE
———————–
MANAGED REAL TIME APPLY
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_2 string service=”ggstby”, ASYNC NOAFFI RM delay=0 optional compressio n=disable max_failure=0 max_co nnections=1 reopen=300 db_uniq ue_name=”ggstby” net_timeout=3 0, valid_for=(online_logfile,a ll_roles)
New standby – yes was the original standby too – cuz of two switchovers
select db_unique_name,db_unique_name,name,open_mode,log_mode,database_role,flashback_on,dataguard_broker,switchover_status,guard_status from v$database;
DB_UNIQUE_NAME NAME OPEN_MODE LOG_MODE
—————————— ——— ——————– ————
DATABASE_ROLE FLASHBACK_ON DATAGUAR SWITCHOVER_STATUS GUARD_S
—————- —————— ——– ——————– ——-
GGSTBY GGSTBY GGTARGETGGSTBY GGSTBY GGTARGETREAD ONLY WITH APPLY ARCHIVELOG PHYSICAL STANDBY YES ENABLEDNOT ALLOWED NONE
Troubleshooting Data Guard with DG Broker / dgmgrl
Well – troubleshooting issues with dgmgrl can be a pain – the blogs and documentation below – describes in some detail what is going on. Oracle’s dgmgrl documentation is also below.
Creating A DataGuard Broker Configuration Using DGMGRL Blog – Lots of valuable info
Configuring dataguard to manage the standby database
Oracle’s 12c dgmgrl documentation
By the way, Oracle introduced a new property in 11gR2 called “StaticConnectIdentifier” per support Doc ID – 308943.1. This property specifies the connection identifier that the DGMGRL client will use when starting database. In this case the above entry within listener.ora is not required anymore, but the specified Connection Identifier must still be a Static Listener Entry, of course.
Good news is dgmgrl finally works but, I can also use sqlplus for the role transition (though not both a the same time). All that is required to use sqlplus on a functional Data Guard primary / standby pair is explicitly setting LOG_ARCHIVE_DEST_2 for both the primary and standby – keep in mind – the two DEST_2 params are different as DEST_2 typically used to point at standby’s must be set on the standby to point at the primary and on the primary to point at the standby – for multiple switchover / role transitions.
And dgmgrl – sets lots of Oracle parameters for you including log_archive_dest_2 – and dgmgrl will complain – if you have the standby’s LOG_ARCHIVE_DEST_2 set – when trying to create the dgmgrl configuration – I had to clear it…
Moral of the story, for a simple single instance database… once you do get DB Broker setup properly… it automates quite a few steps in the switch over and works like a charm… at least for now…
I documented the use of sqlplus for role transitions here
Various checks on the status and configuration of your Data Guard databases
12c Data Guard Views Oracle Documentation
Check your primary or standby for various things with the following:
select
db_unique_name,
name,
open_mode,
log_mode,
database_role,
flashback_on,
dataguard_broker,
switchover_status,
guard_status
from v$database;
Use the SQL below to determine the status of your redo on either the primary or standby (once the standby is created).
select * from v$log;
select * from v$logfile;
# more views
SQL> select * from V$DATAGUARD_CONFIG;
SQL> select * from V$DG_BROKER_CONFIG;
# valuable sql query to show current mode of dest_2
select recovery_mode from v$archive_dest_status where dest_id=2;
Other resources:
Izzysoft DG page
oracle-base DGMGRL write up