nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Geodatabase Connection to Oracle Active Data Guard Physical Standby

WilliamCraft

Thank you in advance for any help you can provide.

ENVIRONMENT

Database Server:

Oracle 11.2.0.2.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

Client Machine:

Oracle Client 11.2.0.1.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

BACKGROUND

I have successfully created two standby databases from my primary Oracle database; one is a physical standby 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: "Failed to connect to the specified server. Operation Failed". After some online research, it seemed like there may be an issue with the authorization of the geodatabase from a licensing perspective. This would make sense since the standby databases have a different DB_UNIQUE_NAME than the primary. Therefore, I tried to authorize both standby databases using the ArcSDE for Oracle 11g Post Installation, trying both of the standby database unique names as the NET SERVICE value. However, I end up with a similar error:

ESRI ArcSDE Server Setup Utility Tue Feb 04 13:33:01 2014
----------------------------------------------------------------
Error: Operation Failed (-1).
Error: Error upgrading authorization key.

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 physical standby to run in read-write mode, I am then able to authorize it using the ArcSDE for Oracle 11g Post Installation. 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.

QUESTION

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? I am trying to determine the high-level proper workflow for creating a standby database, authorizing the geodatabase, and subsequently configuring the standby to receive archived redo data from the primary database (using either Redo Apply or Real-Time Apply).

Again, thank you in advance for any help you can provide. If you require more information, please just let me know.

Find more posts tagged with

Accepted answers

All comments

ColleenFarrell

We are in the process of investigating this setup at my company - I don't have any answers just yet.

Some of my concerns are what to do about the keyset_* tables and the sde log pool tables when a user selects more that 100 features.

WilliamCraft

We are in the process of investigating this setup at my company - I don't have any answers just yet.

Some of my concerns are what to do about the keyset_* tables and the sde log pool tables when a user selects more that 100 features.

The keyset tables can be dropped periodically. I think the best bet is to drop them up using a batch file that executes as a scheduled task. As far as the log tables, I think you are referring to the SDE_LOGFILES and SDE_LOGFILE_DATA tables. Yes, those get generated when the user selects 1,000 or more records from an ArcGIS client. We convert them to Global Temporary Tables, which truncate automatically when the user logs out of his or her session. On the next login, their selection set of 1,000 or more will utilize the global temp tables instead of generate a regular set of tables.

I've attached scripts to help you with these topics. The keyset script will perform the drop of any keyset table. The logfile script will generate the SQL to drop the logfile tables and re-create them as global temp tables.

scripts.zip

WilliamCraft

In getting back to the original topic at hand, I found the following thread which discusses ArcSDE and Oracle Active Data Guard in a bit more detail: http://forums.arcgis.com/threads/20100-Moving-an-SDE-Replication-DB-to-a-new-machine

In my particular case, I think the issues I am having are related to creating the standby database on the same machine as the primary. Since this is a test environment, I did not think it would be a show stopper. Of course, in a production environment we would have the standby on a separate machine. Having the standby on a separate machine would allow the DB_NAME to match that of the primary database, which is something I am not able to do by nature of having the primary and standby on the same machine. I think that might be why I am unable to connect to the standby right after creating it; the license for the geodatabase may need to be specific to the DB_NAME. Hence, a different DB_NAME with the same license may not work. The only thing I can't explain is why I still couldn't connect to the standby via ArcCatalog even after breaking replication, putting the standby in read-write mode, and successfully authorizing it with the Post Installation. Moreover, I have not had to re-authorize test databases when importing dump files from a production export. I could be wrong in my thought process here, but I'm just sharing what has occurred to me in the last few minutes.

I'll explore this a bit more and provide more results once I can get a second test server stood up for the standby database.

WilliamCraft

Disregard some of my comments above. It had nothing to do with the DB_NAME or the esri license. I can connect to either standby if I perform a Switchover within OEM such that the primary and standby databases swap roles. It has something to do with the read-only status of the database whereby Data Guard is enabled.

WilliamCraft

After several weeks of testing, I am finally ready to update this thread again. Below are my findings:

Physical Standby databases are, by very nature according to Oracle, read-only databases that are exact copies of their respective primary databases. They receive information via Oracle's Redo Apply (or optionally, Real Time Apply) log shipping mechanism at the block level and are always started in Mount mode. I have found them to be very robust; edits to geometric networks, compress operations, and rec & post operations all make it over to the standby when performed on the primary, as expected. After switching over to the standby, the same behavior occurs the other way just as well. In other words, transactions (all DDL and DML) are shipped the other way since switching over results in the primary and standby swapping roles. I have been able to perform versioned editing without issue on both sides only to find that the standby redo logs are shipped and applied without issue on the opposite side. If you want to verify that the STATES, VERSIONS, and STATE_LINEAGES tables (or any others for that matter) in the standby show the same information as the primary, you can query the standby the standby using a non-Esri application such as SQL*plus, DB Artisan, or SQL Developer. Just make sure that Real-Time SQL (not the same as the aforementioned "Real Time Apply") is enabled on the primary database (offered at 11g and later); it then allows read-only connections. As far as I can see, Physical Standby databases do NOT allow connections from an ArcGIS client because connecting with an ArcGIS client is actually NOT a read-only connection. When an ArcGIS client connects, there are write operations that occur by default... it's just how the software works. To connect to the physical standby, you MUST perform a switchover or failover which will change the role of the standby to become primary; thus starting it up in read-write mode.

Logical Standby databases are inherently different; they use Oracle's SQL Apply log shipping mechanism and are always started in read-write mode. However, there are major issues with these databases when it comes to storing and accessing GEOdatabases. Despite logical standby databases being "editable", they only allow DML changes, not DDL. While it is one thing to replicate changes from primary to standby, it is another thing to replicate complex changes and it is yet another thing to allow ArcGIS client connections to the standby. ArcGIS clients instantiate DDL when connecting with the database, so logical standby databases will yield an error; in other words, they write values to the database and they apply schema changes upon connecting. To get around this, there are 24 exclusions that need to be inputted into the logical standby database before an ArcGIS client can connect. Those are listed below. When setting up a logical standby via Oracle Enterprise Manager (OEM) / Cloud Control, you are notified that certain objects in the database are not supported for replication with logical standby databases. That is why exclusions like these need to be made; otherwise replication won't just "not work for some objects", it will actually break entirely and the standby redo logs won't get applied to the standby whatsoever.

(Note: the exclusions below can be run as the SYS user and have been tested against 10.2.1 geodatabases using the SDELOB spatial data type):

EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL', -
     schema_name => '%', -       
     object_name => 'SDE_LOGFILES'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL', -
     schema_name => '%', -       
     object_name => 'SDE_LOGFILE_DATA'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL', -
     schema_name => '%', -       
     object_name => 'SDE_EXCEPTIONS'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL', -
     schema_name => 'SDE', -     
     object_name => 'PROCESS_INFORMATION'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'DML', -
     schema_name => 'SDE', -     
     object_name => 'PROCESS_INFORMATION'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL', -
     schema_name => 'SDE', -     
     object_name => 'OBJECT_LOCKS'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'DML', -
     schema_name => 'SDE', -     
     object_name => 'OBJECT_LOCKS'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL', -
     schema_name => 'SDE', -     
     object_name => 'STATE_LOCKS'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'DML', -
     schema_name => 'SDE', -     
     object_name => 'STATE_LOCKS'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL', -
     schema_name => 'SDE', -     
     object_name => 'TABLE_LOCKS'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'DML', -
     schema_name => 'SDE', -     
     object_name => 'TABLE_LOCKS'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL', -
     schema_name => 'SDE', -     
     object_name => 'LAYER_LOCKS'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'DML', -
     schema_name => 'SDE', -     
     object_name => 'LAYER_LOCKS'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'DML', -
     schema_name => '%', -       
     object_name => 'SDE_LOGFILE_LID_GEN'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL', -
     schema_name => '%', -       
     object_name => 'SDE_LOGFILE_POOL'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'DML', -
     schema_name => '%', -       
     object_name => 'SDE_LOGFILE_POOL'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL', -
     schema_name => '%', -       
     object_name => 'SDE_LOGFILE_LID_GEN'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL', -
     schema_name => 'SDE', -     
     object_name => 'CONNECTION_ID_GENERATOR'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'DML', -
     schema_name => 'SDE', -     
     object_name => 'CONNECTION_ID_GENE RATOR'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'DML', -
     schema_name => '%', -       
     object_name => 'SDE_LOGFILES'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'DML', -
     schema_name => '%', -       
     object_name => 'SDE_LOGFILE_DATA'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'DML', -
     schema_name => '%', -       
     object_name => 'SDE_EXCEPTIONS'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL', -
     schema_name => '%', -       
     object_name => 'SDE_LOGFILE%'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'DML', -
     schema_name => '%', -       
     object_name => 'SDE_LOGFILE%');

One thing to note is that the SQL Apply mechanism of logical standby databases does not work with ST_Geometry. Even if you are using another data type like SDELOB, you should know that the SDE.GDB_ITEMS table at 10.2.1 (actually, probably at 10.1 and later) has a SHAPE field which stores the spatial envelope of all spatial data in the geodatabase. This SHAPE field is of ST_Geometry type, which as I mentioned does not work with logical standby replication. It cannot be changed; I asked Esri's PSSG this question. What I think this implies is that 10.1 and later geodatabases will not work whatsoever with logical standby technology from Oracle. At 9.3.1 (and possibly 10.0), I believe it could work as far as updating values and geometries (just not schema changes); but because of the ST_Geometry data type introduced for the GDB_ITEMS SHAPE field, I have found that logical standby databases won't work if you have any sort of schema change because those changes touch the GDB_ITEMS table. You'll still be able to make a connection, but DDL and DML replication will fail.

On a side note, I have found that Oracle Streams uses much of the same underlying technology as logical standby databases. Although I did not perform exhaustive testing with Streams, I was not able to successfully get replication to behave correctly. Instead, I saw similar results to what I saw with logical standby databases.

While Oracle replication has been announced by Esri as "officially supported", I did not hear from Esri that Oracle replication using physical standby database is unsupported either. It is my impression that physical standby databases are more robust and behave much better with Esri technology than logical standby databases. Several Esri clients are using this configuration and have been successful with physical standby databases from a DR perspective. Just remember that you cannot connect to the physical standby even with Real-Time SQL enabled because Oracle sets it to read-only. To do so would mean that you would need to perform a failover or switchover. Connecting to the physical standby would be great for reporting and query offloading purposes, and this is possible with non-Esri clients. But for ArcGIS connections, it is not possible.

I hope this is helpful to others who have been interested in using Oracle's Active Data Guard Technology. If and when I learn more, I will update this thread.

EmadAl-Mousa

Hi,

i have experienced the physical stand-by database architecture, and as you have mentioned the database is in (read-only mode)....this is why many application servers on Top of the Oracle DBMS will not work with standby databases because their connections are not pure "read" transactions.....one of the application servers i have tested is ArcSDE.

i have a question regarding st_geometry, if you configure external procedure on both (PRIMARY/STANDBY) is this going to resolve the problem you have mentioned ?
http://resources.arcgis.com/en/help/main/10.1/index.html#//002n00000091000000

WilliamCraft

Thanks for your reply and suggestion, Emad. I went ahead and configured my EXTPROC file for 11g, making sure to point to the correct 10.2.1 st_shapelib.dll file on the database server. When attempting to create a new logical standby, I am still seeing the same notifications from Oracle about the unsupported objects, fields, and data types:

[ATTACH=CONFIG]31869[/ATTACH]

I will continue with creating the logical standby to see if, after adding the exclusions, I am able to make DML and DDL changes without breaking the replication and then report back when further testing is complete.

On another note, in reference to my physical standby testing above, most of my work was with using direct connect. I was seeing good results and behavior with the ArcSDE application service too, although I would recommend direct connect in just about any situation.

logical_standby.png

WilliamCraft

I've completed the testing discussed above and one thing I need to correct / mention / clarify is that ArcGIS connections to the logical can only be made if ALTER DATABASE guard none; is issued by the SYS user to the logical standby. Otherwise, the following error is returned:

[ATTACH=CONFIG]31874[/ATTACH]

Doing this turns off data guard altogether, which is not favorable. I apologize for not indicating this earlier; I know this requirement kind of defeats the purpose of data guard altogether. So what this means is that even while log shipping is still enabled and redo logs are still configured to apply to the standby, edits that I've made (changing geometries and attributes as well as adding a feature class and adding a field) do not actually ever get applied to the standby. The logs get shipped but never applied, despite the logical standby showing a Normal status. Re-enabling Data Guard does not help, either. The logical standby will be several logs behind the primary, forever. Making the change to the EXTPROC file did not change any of the behavior, unfortunately.

pinfo.png

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.