Thank you in advance for any help you can provide.
- Oracle 22.214.171.124.0 Single-Instance Database on Windows Server 2003 R2
- Enterprise 64-bit Oracle Active Data Guard for Oracle Database 11g R2
- ArcSDE for Oracle 11g 9.3.1 SP2 using SDELOB
- Oracle Client 126.96.36.199.0 on Windows 7 Enterprise
- ArcGIS Desktop 9.3.1 SP2 on Windows 7 Enterprise
- ArcGIS Desktop 9.3.1 SP2 QIP Patch
- ArcGIS Desktop 9.3.1 SP2 Geodatabase Patch
- ArcGIS Desktop 9.3.1 SP2 Geometric Network Patch
I have successfully created two standby databases from my primary Oracle database; one is a physical standby (with realtime-SQL enabled) and the other is a logical standby. When attempting to connect to either of the standby databases using direct connect in ArcCatalog, I receive the following error:
Error: Operation Failed (-1)
It then occurs to me that both standby databases are running in read-only mode, which is expected when creating standby databases via Oracle Active Data Guard. After some more research, I find that it is possible to modify a standby database such that it can run in read-write mode. After modifying the standby to run in read-write mode, I am then able to make edits to the standby. Afterward, however, I was unable to resume the application of redo logs for the standby database which essentially broke the connection between my standby and my primary. This was evident after receiving a variety of Oracle errors (e.g., ORA-01665 and ORA-01153) when following the Oracle documentation for how to switch from the standby back to the primary. In essence, I wasn't able to re-link the standby and primary databases ever again such that redo logs were applied correctly from the primary to the standby.
The good news is that I can connect to either the logical or physical standby if I perform a Switchover within OEM 12c R3 such that the primary and standby databases swap roles. Therefore, I think it has something to do with the read-only status of the database whereby Data Guard is enabled.
After some discussion with a contact at Esri, part of the solution might reside in the SDE.SERVER_CONFIG table, specifically with the READONLY parameter. Setting the CHAR field to TRUE and the NUM field to 1 would set the geodatabase to read-only mode (Esri's method of read only). However, I can't change this value on the standby because it's in read-only mode according to the Oracle software. I found out that making a geodatabase connection actually performs edits at login. There is an Oracle package that executes a function or two which performs inserts and object creations within the database. Setting the geodatabase to read-only mode per the method above may be the way to change this behavior but I'm still waiting for answers at the moment.
Lastly, I believe there are a list of exclusions that are needed when setting up the Active Data Guard replication for logical standby databases. I'm yet to find those, though.
Has anyone used ArcSDE in conjunction with Oracle Active Data Guard? If so, how am I able to make a geodatabase connection to the standby? Any insight would be greatly appreciated.
I am trying to determine the high-level proper workflow for creating a standby database and subsequently configuring the standby to receive archived redo data from the primary database (using either Redo Apply, Real-Time Apply, or SQL Apply).