Because my DBs are on a disconnected network, I cannot copy and paste large amounts. Everything here is typed. Please be patient.
Primary DB is EE 11.2.0.3.0 on RHEL 5.1 and running on file-system (Non-ASM).
Standby DB is EE 11.2.0.4.0 on OEL 6.4 and running on ASM.
I perform RMAN ACTIVE DUPLICATE with no errors. I run the following SQL queries on the Standby:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
select database_role,protection_mode from v$database;
DATABASE_ROLE - PROTECTION_MODE
PHYSICAL STANDBY - MAXIMUM PERFORMANCE
SELECT sequence#, first_time, next_time, applied
FROM v$archived_log ORDER BY sequence#;
I compare v$archived_log against the primary. Everything has transferred and applied. Life is good. I'm amazed I haven't encountered any issues yet! Maybe I have really become a good DBA now! Now all I have to do is turn on Data Guard.
I configure my Standby listener. Notable entries are below.
...
(SID_DESC =
(SDU = 32767)
(GLOBAL_DBNAME = acme2_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_home1)
(ENVS="TNS_ADMIN=/u01/app/oracle/product/11.2.0/grid/network/admin")
(SERVER = DEDICATED)
(SID_NAME = acme2)
)
(SID_DESC =
(SDU = 32767)
(GLOBAL_DBNAME = acme2_DGB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_home1)
(ENVS="TNS_ADMIN=/u01/app/oracle/product/11.2.0/grid/network/admin")
(SERVER = DEDICATED)
(SID_NAME = acme2)
)
DYNAMIC_REGISTRATION_LISTENER_ACME2=OFF
Because dynamic registration is disabled, I have statically generated the _DGB entry above. I understand that ENVS is not strictly necessary, however I have included it to rule out errors. I insure some more settings are correctly configured.
On Primary:
show parameter log_archive
log_archive_config: 'dg_config=(acme,acme2)'
log_archive_dest_1: LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=acme
log_archive_dest_2: SERVICE=acme2 SYNC AFFIRM reopen=15 NET_TIMEOUT=15 DB_UNIQUE_NAME=acme2 VALID_FOR=(online_logfiles,primary_role)
On Standby:
show parameter log_archive
log_archive_config: 'dg_config=(acme2,acme)'
log_archive_dest_1: LOCATION=USE_DB_RECOVERY_FILE_DEST
log_archive_dest_2: SERVICE=acme AFFIRM SYNC VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=acme
I check on the Primary:
show parameter dg_broker
NAME - VALUE
dg_broker_config_file1 - /u02/app/oracle/fra/dr1acme.dat
dg_broker_config_file2 - /u02/app/oracle/fra/dr2acme.dat
dg_broker_true - TRUE
I set and check the Standby parameters:
show parameter dg_broker
NAME - VALUE
dg_broker_config_file1 - +DATA/acme2/broker/dr1acme.dat
dg_broker_config_file2 - +FRA/acme2/broker/dr2acme.dat
dg_broker_true - TRUE
I go to the Standby ASMCMD and make the directories above.
On Primary:
dgmgrl
connect sys/mypassword
Connected.
add database acme2 as connect identifier is acme2 maintained as physical;
It adds fine. It's part of the configuration, but disabled. In the Standby listener log I see that the Primary database made a successful connection to the "acme2" service.
show database verbose acme2 (I only typed relevant properties...ask for more if need be)
DGConnectIdentifier = 'acme2'
LogXptMode = 'SYNC'
Standbyfilemanagement = 'AUTO'
DbFileNameConvert = '/u02/app/oracle, +DATA, /u03/app/oracle, +DATA'
LogFileNameConvert = '/u02/app/oracle, +FRA, /u03/app/oracle, +FRA'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(host=mystandbyhost)(port=1521))(CONNECT_DATA=(SERVICE_NAME=acme2_DGMGRL)(INSTANCE_NAME=acme)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
Database Status:
DISABLED
Just to double-check, I use my Standby DGMGRL to try to connect to the Primary DGMGRL with "sys/mypassword". Success! I can view the Primary DGMGRL configuration from the Standby. I then copy and paste the above "StaticConnectIdentifier" into my Primary tnsnames.ora file and create a temporary TNS entry. I then try to tnsping that temp entry. All is well. I remove the temp entry and return to DGMGRL. Everything has gone perfectly. Lastly, I check the DG log file on the Standby. The last line is "Configuration does not exist, Data Guard broker ready", which is exactly what I would expect to see at this point. Can this day get any better?
On Primary:
dgmgrl
connect sys/mypassword
Connected.
enable database acme2;
...and thus my long nightmare begins. The Standby data guard fails to work.
On the Standby DG log files, I see these errors for a moment, but then they go away:
drcx: cannot open configuration file "+DATA/acme2/broker/dr1acme.dat"
Which makes sense as they haven't been copied yet. After a minute, I check the +DATA and +FRA diskgroups on the Standby and both DG config files have transferred and are where they should be. OK, good. I check the Standby database log file. Nothing but the standard archived log entries (files are still being transferred from the Primary outside of Data Guard). I check ADRCI on the Standby. There are no problems or incidents.
On the Primary, there are no problems or incidents in ADRCI and the only errors to be found are in the DG log file. They are:
Broker Initiated ENABLE of site acme2 has been scheduled
Data Guard Broker Status Summary:
Type -- Name -- Severity -- Status
Configuration DG_ACME_CONFIG warning ora-16607
Primary DB acme success ora-00000
Physical Standby DB acme2 error ora-16532
ENABLE DATABASE acme2
Metadata Resync failed. Status = ora-16577
Command ENABLE DATABASE acme2 completed
That error repeats continuously as the Primary tries to communicate with the Standby DG broker. I check the Standby listener log file. I see that the Primary is successfully connecting to local service "acme2_DGB" about once every a minute, right on cue. It is reaching the TNS service, but the TNS service does not seem to be talking to the local broker.
On the Standby db I run
SQL> select process,client_process,status from v$managed_standby;
| PROCESS | CLIENT_PROCESS | STATUS |
|---|
| ARCH | ARCH | CLOSING |
| ARCH | ARCH | CLOSING |
| ARCH | ARCH | CONNECTED |
| ARCH | ARCH | CLOSING |
| RFS | ARCH | IDLE |
| RFS | UNKNOWN | IDLE |
| RFS | LGWR | IDLE |
SELECT MESSAGE FROM V$DATAGUARD_STATUS; shows nothing helpful (just standard archive transfers).
I have also completely removed the DG configuration on all boxes, deleted the DG config files, and completely recreated the configuration several times. In fact, I have completely rebuilt the Standby DG database twice. Additionally, Fast-Start Failover is disabled on the DG configuration.
I've tried everything I can think of and find online. I would greatly appreciate any insight into this matter that the community could provide.