Basically, my insanity is from my experience as Senior Tax Advisor and investment background.
Planning and organization will simplify processes in the long run.
I will share my thoughts for implementing Data Guard having two standby databases.
Data Guard configurations will be for the following hosts: LAX, PHX, SLC
- ORACLE_SID and db_name will be the same value.
- db_unique_name and service_names will be the same value.
- Listener will be LISTENER.
- TNS entries (alias) will be created for service_names and listener.
- Data Guard Broker is being used with Oracle Restart
Hostname: LAX (PRIMARY) : OEL6
Option #1: Using alphabet for configurations
ORACLE_SID(db_name)/db_unique_name(service_names): HAWK/HAWKA
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_HAWKA
dg_config=(HAWKA,HAWKB,HAWKC)
fal_server='HAWKB,HAWKC'
Option #2: Using hostname for configurations
ORACLE_SID(db_name)/db_unique_name: HAWK/LAX
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_LAX
dg_config=(LAX,PHX,SLC)
fal_server='PHX,SLC'
Hostname: PHX (CURRENT STANDBY) : OEL6
Option #1: Using alphabet for configurations
ORACLE_SID(db_name)/db_unique_name: HAWK/HAWKB
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_HAWKB
dg_config=(HAWKA,HAWKB,HAWKC)
fal_server='HAWKA,HAWKC'
Option #2: Using hostname for configurations
ORACLE_SID(db_name)/db_unique_name: HAWK/PHX
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_PHX
dg_config=(LAX,PHX,SLC)
fal_server='LAX,SLC'
Hostname: SLC (NEW STANDBY) : OEL7
Option #1: Using alphabet for configurations
ORACLE_SID(db_name)/db_unique_name: HAWK/HAWKC
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_HAWKC
dg_config=(HAWKA,HAWKB,HAWKC)
fal_server='HAWKB,HAWKC'
Option #2: Using hostname for configurations
ORACLE_SID(db_name)/db_unique_name: HAWK/SLC
Listener/port/local_listener(tnsnames): LISTENER/1521/LISTENER_SLC
dg_config=(LAX,PHX,SLC)
fal_server='LAX,PHX'
I know what you are thinking. Why did I name local_listener when I specifically blogged Why Name Listener?!
I did so for consistency and to avoid having to make too many changes.
As stated from blog post, local_listener=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)) would be better since the only change required would be tns entry for listener alias.
Semi off topic: I still remembered from my martial art teacher, “Expected the unexpected.”
How does this relate to Data Guard and creating standby databases?
Always create database having db_name different from db_unique_name.
One day when there is a requirement to create standby database, environment will be configured with good naming convention. “Expect the unexpected.”
The demonstration will be using Option #2.
One downside, what happens when there is a new host added for the same location?
Use PHX2?
Me being as anal as I am, that’s going to be disturbing.
May be suffix all locations with numeric values?
You decide.
CONFIGURE ARCHIVELOG DELETION TO NONE
There are 1059 datafiles and 5 tempfiles for database duplication and don’t want archivelog to be deleted before second standby is duplicated.
Recovery Manager: Release 12.1.0.2.0 – Production on Mon Apr 13 19:06:26 2020
Finished Duplicate Db at 2020-APR-14 03:42:51
LAX: CONFIGURE ARCHIVELOG DELETION AND DB_UNIQUE_NAME
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
RMAN> CONFIGURE DB_UNIQUE_NAME 'SLC' CONNECT IDENTIFIER 'SLC';
PHX: CONFIGURE ARCHIVELOG DELETION
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
SLC : BUG 19504946 – FLASH CACHE DOESN’T WORK IN OEL7
Apply patch as required.
SLC : Create pfile and startup nomount database
Since Database Smart Flash Cache was configured at PHX (CURRENT STANDBY) and not at SLC (NEW STANDBY),
need to disable db_flash_cache_size and created dummy db_flash_cache_file for the time being until configured.
$ cat initHAWK.ora
*.db_name='HAWK'
*.db_flash_cache_size='0'
*.db_flash_cache_file='+DATA/flashfile.dat'
FOR ALL HOSTS : Verify TNS entries using tnsping from DB_HOME
tnsping LAX; tnsping PHX; tnsping SLC
tnsping LISTENER_LAX; tnsping LISTENER_PHX; tnsping LISTENER_SLC
FOR ALL HOSTS : Verify connect using TNS as SYSDBA
sqlplus sys@LAX as sysdba
sqlplus sys@PHX as sysdba
sqlplus sys@SLC as sysdba
PHX : Perform RMAN Active DB Duplication
$ rman checksyntax @ duplicate.rman
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Apr 13 19:06:15 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
RMAN> set echo on
2> connect target *
3> connect auxiliary *
4> run {
5> allocate channel c1 type disk maxopenfiles 1;
6> allocate channel c2 type disk maxopenfiles 1;
7> allocate channel c3 type disk maxopenfiles 1;
8> allocate auxiliary channel a1 type disk;
9> allocate auxiliary channel a2 type disk;
10> allocate auxiliary channel a3 type disk;
11> duplicate target database for standby from active database
12> spfile
13> parameter_value_convert 'phx','slc','PHX','SLC'
14> set db_unique_name='SLC'
15> set db_flash_cache_size='0'
16> set db_flash_cache_file='+DATA/flashfile.dat'
17> set pga_aggregate_limit='24159191040'
18> set pga_aggregate_target='11596411699'
19> set sga_max_size='32G'
20> set fal_server='LAX,PHX'
21> set local_listener='LISTENER_SLC'
22> nofilenamecheck;
23> }
24> exit
The cmdfile has no syntax errors
Recovery Manager complete.
$ nohup rman @ duplicate.rman > /tmp/duplicate_rman.log 2>&1 &
PHX : Add 2nd Standby (SLC) to DG Broker Configuration
Better not to use dgmgrl / as it may be problematic.
$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@PHX
DGMGRL> add database SLC as connect identifier is SLC;
DGMGRL> enable configuration
DGMGRL> show configuration
DGMGRL> show database SLC
DGMGRL> exit
SLC : Move spfile to ASM
$ echo "+DATA/spfile$ORACLE_SID.ora" > init$ORACLE_SID.ora
SQL> create pfile='/tmp/init@.ora' from spfile;
File created.
SQL> ! ls -l /tmp/init*
-rw-r--r-- 1 oracle oinstall 9511 Apr 14 17:33 /tmp/init$ORACLE_SID.ora
SQL> create spfile='+DATA/spfile$ORACLE_SID.ora' from pfile='/tmp/init$ORACLE_SID.ora';
File created.
SQL>
SLC : Add database to Oracle Oracle Restart
$ export ORACLE_SID=HAWK
$ export ORACLE_UNQNAME=SLC
$ srvctl add database -dbname $ORACLE_SID -db $ORACLE_UNQNAME -instance $ORACLE_SID -oraclehome $ORACLE_HOME \
-spfile +DATA/spfile$ORACLE_SID.ora -pwfile $ORACLE_HOME/dbs/orapw$ORACLE_SID \
-role PHYSICAL_STANDBY -diskgroup "FRA,DATA" -startoption MOUNT -stopoption IMMEDIATE
$ srvctl config database -d $ORACLE_UNQNAME
$ srvctl start database -d $ORACLE_UNQNAME
$ srvctl status database -d $ORACLE_UNQNAME -v
$ ps -ef|grep [p]mon
PHX/SLC : Review resource
crsctl stat res -t -w "TYPE = ora.database.type"
crsctl stat res -t -w "TYPE = ora.service.type"
LAX: CONFIGURE ARCHIVELOG DELETION
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;
PHX/SLC: CONFIGURE ARCHIVELOG DELETION
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
REFERENCES:
What’s New in Oracle Data Guard?
New 11.2 Features Common to Redo Apply and SQL Apply
The FAL_CLIENT database initialization parameter is no longer required.
Data Guard Broker 12c Release 1 (12.1.0.2)
The following database initialization parameters are controlled by broker configurable properties.
Therefore, you should not set these parameters manually:
ARCHIVE_LAG_TARGET
DB_FILE_NAME_CONVERT
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_STATE_n
LOG_ARCHIVE_FORMAT
LOG_ARCHIVE_MAX_PROCESSES
LOG_ARCHIVE_MIN_SUCCEED_DEST
LOG_ARCHIVE_TRACE
LOG_FILE_NAME_CONVERT
STANDBY_FILE_MANAGEMENT
Configuring RMAN in a Data Guard Environment
CONFIGURE DB_UNIQUE_NAME defines a connection to a physical standby database and implicitly registers the new database.
New standby databases are also automatically registered when RMAN connects as TARGET to a standby database for the first time.
CONFIGURE FOR DB_UNIQUE_NAME configures settings for a database in the Data Guard environment.
UPDATES:
The only consistency with Oracle is that it’s inconsistent.
*** Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)
Note: Static “_DGMGRL” entries are no longer needed as of Oracle Database 12.1.0.2 in Oracle Data Guard Broker configurations
that are managed by Oracle Restart, RAC On Node or RAC as the Broker will use the clusterware to restart an instance.
*** How to Configure Static Listener Registration for Standalone,RAC and Data Guard setup (Doc ID 2312510.1)
*** Connection to Auxiliary using connect string failed with ORA-12528 (Doc ID 419440.1)
If “Duplicate from Active Database” is used , you must connect using ‘TNS alias’ to both, Target and AUXiliary Instance.
Therefore, the AUXiliary instance should have a static listener configured like:
$ rman target sys/@ auxiliary sys/@
To test, sqlplus (sysdba user), and rman must be able to connect to AUXiliary Instance, even if AUX instance is down/nomount.
For this recommendation is to create a ‘Static Listener’ entry for the AUX instance.
The cause of the problem is that the Auxiliary Instance is started in NOMOUNT status, so it’s not registered with listener.
PMON process registers the instances with the listener when they are in MOUNT status, while instances in NOMOUNT status appear in listener as BLOCKED.
In addition, RMAN will shutdown the auxiliary instance several times during the execution.
Therefore, the connection may succeed initially, then fail with the error later in the execution.
*** Performing RMAN duplicate from standby to create a new clone (Doc ID 1665784.1)
*** Bug 11715084 : ACTIVE DUPLICATE SHOULD WORK WHEN CONNECTED TO STANDBY AS SOURCE DB
Updated : 25-Feb-2020. Fixed in Product Version 12.1
*** Data Guard Physical Standby Changing the SYS password when a broker configuration exists (Doc ID 1199943.1)
Q.E.D.
