Corruption in redo log file when implementing Physical Standby

Lately I started implementing Data Guard - Physical Standby - as a DRP environment for our production E-Businsess Suite database and I must share with you one issue I encountered during implementation. I chose one of our test environments as a primary instance and I used a new server, which was prepared to the standby database in production, as the server for the standby database in test. Both are Red-Hat enterprise linux 4. The implementation process went fast with no special issues (at lease I thought so...), everything seems to work fine, archived logs were transmitted from the primary server to the standby server and successfully applied on the standby database. I even executed switchover to the standby server (both database and application tier), and switchover back to the primary server with no problems. The standby database was configured for maximum performance mode, I also created standby redo log files and LGWR was set to asynchronous (ASYNC) network transmission. The exact setting from init.ora file: log_archive_dest_2='SERVICE=[SERVICE_NAME] LGWR ASYNC=20480 OPTIONAL REOPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30' At this stage, when the major part of the implementation had been done, I found some time to deal with some other issues, like interfaces to other systems, scripts, configure rsync for concurrent log files, etc... , and some modifications to the setup document I wrote during implementation. While doing those other issues, I left the physical standby instance active so archive log files are transmitted and applied on the standby instance. After a couple of hours I noticed the following error in the primary database alert log file: ARC3: Log corruption near block 146465 change 8181238407160 time ? Mon Mar  2 13:04:43 2009 Errors in file [ORACLE_HOME]/admin/[CONTEXT_NAME]/bdump/[sid]_arc3_16575.trc: ORA-00354: corrupt redo log block header ORA-00353: log corruption near block 146465 change 8181238407160 time 02/03/2009 11:57:54 ORA-00312: online log 3 thread 1: '[logfile_dir]/redolog3.ora' ARC3: All Archive destinations made inactive due to error 354 Mon Mar  2 13:04:44 2009 ARC3: Closing local archive destination LOG_ARCHIVE_DEST_1: '[archivelog_dir]/arch_[xxxxx].arc' (error 354)([SID]) Committing creation of archivelog '[archivelog_dir]/arch_[xxxxx].arc' (error 354) ARCH: Archival stopped, error occurred. Will continue retrying Mon Mar  2 13:04:45 2009 ORACLE Instance [SID] - Archival Error I don't remember if I've ever had a corruption in redo log file before...  What is wrong?! Is it something with the physical standby instance ?? Actually, if it's something with the standby instance I would have expected for a corruption in the standby redo log files not the primary's.. The primary instance resides on a Netapp volume, so I checked the mount option in /etc/fstab but they were fine. I asked our infrastructure team to check if something went wrong with the network during the time I got the corruption, but they reported that there was no error or something unusual. Ok, I had no choice but to reconstruct the physical standby database, since when an archive log file is missing, the standby database is out of sync'. I set the 'log_archive_dest_state_2' to defer so no further archive log will be transferred to the standby server, cleared the corrupted redo log files (alter database clear unarchived logfile 'logfile.log') and reconstruct the physical standby database. Meanwhile (copy database files takes long...), I checked documentation again, maybe I missed something, maybe I configured something wrong.. I have read a lot and didn't find anything that can shed some light on this issue. At this stage, the standby was up and ready. First, I held up the redo transport service (log_archive_dest_state_2='defer') to see if I'll get a corruption when standby is off.  After one or two days with no corruption I activated the standby. Then I saw the following sentence in Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2): "All members of a Data Guard configuration must run an Oracle image that is built for the same platform. For example, this means a Data Guard configuration with a primary database on a 32-bit Linux on Intel system can have a standby database that is configured on a 32-bit Linux on Intel system" One moment, I thought to myself, the standby server is based on AMD processors and the primary server is based on Intel's..    Is it the problem?! When talking about same platform, is the meaning same processors also? Isn't it sufficient to have same 32 bit OS on x86 machines? Weird but I had to check it... Meanwhile, I got a corruption in redo log file again which assured there is a real problem and it wasn't accidentally. So I used another AMD based server (identical to the standby server) and started all over again – primary and standby instances. After two or three days with no corruption I started to believe the difference in the processors was the problem. But one day later I got a corruption again (Oh no…) I must say that on the one hand I was very frustrated, but on the other hand it was a relief to know it's not the difference in the processors. It was so clear that when I'll find out the problem it will be something stupid.. So it is not the processors, not the OS and not the network.  What else can it be?! And here my familiarity with the "filesystemio_option" initialization parameter begins (thanks to Oracle Support!). I don't know how I missed this note before, but all is written here - Note 437005.1: Redo Log Corruption While Using Netapps Filesystem With Default Setting of Filesystemio_options Parameter. When the redo log files are on a netapp volume, "filesystemio_options" must be set to "directio" (or "setall"). When "filesystemio_options" is set to "none" (like my instance before), read/writes to the redo log files are using the OS buffer cache. Since netapp storage is based on NFS (which is stateless protocol), when performing asynchronous writing over the network, the consistency of writes is not guaranteed. Some writes can be lost. By setting the "filesystemio_options" to "directio", writes bypasses the OS cache layer so no write will be lost. Needless to say that when I set it to "directio" everything was fine and I haven't gotten any corruption again. Aviad

Oracle Transparent Gateway for MS SQL Server - Part II

Following my last post about Oracle Transparent Gateways which was theoretical post, this post will be more practical. In this post I'll show an example of configuring Oracle Transparent Gateway for MS SQL Server. The goal is to make it possible to retrieve data from MS SQL server database by querying from Oracle database. The steps are: install Oracle Transparent Gateway for MS SQL Server (not explained here) Configure the gateway initialization parameter file Configure the listener Configure the Oracle database tnsnames.ora Creating DB link from Oracle database to MS SQL Server In my environment there are 3 servers involved: Oracle database server - name: orclsrv01, with Oracle database installed name: orcldb Microsoft SQL server - name: msqlsrv01, with MS SQL server installed name: msqldb Oracle Gateway server - name: oragwsrv01, with Oracle Transparent Gateway installed. Configure the gateway initialization parameter file After Oracle Transparent Gateway for MS SQL server installed on ORAGWSRV01 server, a new default initialization file is created at: $ORACLE_HOME\tg4msql\admin\inittg4msql.ora. $ORACLE_HOME - is the directory where Transparent Gateway installed. inittg4msql.ora - is the default initialization parameter file, as tg4msql represents the Gateway SID. If you choose to not use the default SID (or when you have more than one MS Sql server to connect to), the initialization parameter file should be named as follow: init[GW_SID].ora Edit the inittg4msql.ora file and specify the MS Sql Server connection details as follow: HS_FDS_CONNECT_INFO=server_name.database_name in our case: HS_FDS_CONNECT_INFO=msqlsrv01.msqldb Alternatively you can set it as follow: HS_FDS_CONNECT_INFO="SERVER=msqlsrv01; DATABASE=msqldb" Configure the listener In order to make the listener work with the gateway we should configure it. The listener have to be on the same server like the Gateway. We should edit the listener.ora within the Oracle home on which the Gateway installed. The listener.ora must have at least 2 entries: The address to listen on the Gateway to start On ORAGWSRV01 server at $ORACLE_HOME\network\admin edit the listener.ora as follow: LISTENER=                (ADDRESS=                    (PROTOCOL=TCP)                    (HOST=host_name)                    (PORT=port_number)) SID_LIST_LISTENER=       (SID_LIST=            (SID_DESC=                  (SID_NAME=gateway_sid)                  (ORACLE_HOME=oracle_home)                  (PROGRAM=tg4msql)            )        ) Where: host_name - is the server name on which the gateway is installed port_number - is the listener port gateway_sid - is the gateway SID (default is tg4msql) oracle_home - is the Oracle directory on which the gateway is installed In our case: LISTENER=               (ADDRESS=                (PROTOCOL=TCP)               (HOST=oragwsrv01)               (PORT=1521)) SID_LIST_LISTENER=       (SID_LIST=           (SID_DESC=                 (SID_NAME=tg4msql)                (ORACLE_HOME=C:\oracle\product\10.2.0)                (PROGRAM=tg4msql)           )       ) After finishing the Gateway configuration, restart the listener (lsnrctl stop; lsnrctl start). Configure the Oracle database tnsnames.ora Now, when the Gateway configured, we need to configure the Oracle database server to communicate with the Gateway via Oracle Net services. On ORCLSRV01 server at $TNS_ADMIN ($ORACLE_HOME/network/admin) edit the tnsnames.ora file and add the Gateway's connect descriptor as follow: connect_descriptor=    (DESCRIPTION=       (ADDRESS=          (PROTOCOL=TCP)          (HOST=host_name)          (PORT=port_number)       )       (CONNECT_DATA=          (SID=gateway_sid))       (HS=OK)) Where: connect_descriptor - you can specify a name which will be used later when we create a db link host_name - is the server on which the Gateway installed port_number - is the port used by the listener that listening for the Gateway gateway_sid - is the gateway SID (default tg4msql) HS=OK - indicates this connect descriptor is using heterogeneous Services In our case: mytg4msql=    (DESCRIPTION=       (ADDRESS=          (PROTOCOL=TCP)          (HOST=oragwsrv01)          (PORT=1521)       )       (CONNECT_DATA=          (SID=tg4msql))       (HS=OK)) Creating DB link from Oracle database to MS SQL Server Now, when all configurations were done, we only need to create a Database Link from Oracle to the Gateway. Connect to orcldb database and create a db link as follow: create database link ORCLDB2MSQLDB   connect to [MSQLUSER] identified by [MSQLPWD]   using 'mytg4msql'; Where MSQLUSER & MSQLPWD are the user name and password to connect to the MS SQL server. Test it... At this stage we can get data from MS SQL Server via Oracle Database as if we query from Oracle database. For example, if we have a RESERVATIONS table in MSQLDB, we can retrieve RESERVATIONS rows from ORCLDB by issuing the following SQL statement: select * from reservations@ORCLDB2MSQLDB; For more information about Transparent Gateway for Microsoft SQL Server: Oracle® Transparent Gateway for Microsoft SQL Server Administrator's Guide 10g Release 2 (10.2) for Microsoft Windows (32-bit) For more information about other Transparent Gateways for other Database systems: Oracle Database Documentation Library 10g release 2 (10.2) - Information Integration You are welcome to leave a comment. Aviad

Oracle Transparent Gateways - General Description - Part I

A lot of companies have several applications based on more than one database system (e.g DB2, SQL Server, Sybase, etc). Each database system store its own data and naturally there's a need to share data among the various heterogeneous database systems. Oracle, starting with Oracle Database 9i, offers the "Oracle Transparent Gateways" (Oracle Database Gateways) to allow integration of Oracle database with non-Oracle databases. Unlike "Oracle Generic Connectivity" that provide a generic solution to connect any ODBC/OLEDB compliant non-Oracle system using ODBC and OLEDB standards, the "Oracle Transparent Gateways" are solutions specifically tailored for each target non-Oracle database system. The "Oracle Transparent Gateways" communicates using the target database native interface, it's make it possible to access to non-Oracle systems as if they were Oracle databases. The Transparent Gateway solution composed of two parts: Heterogeneous Services (HS) - this is a general integrated component that make it possible to connect to non-Oracle systems from Oracle database Oracle Database Gateways (agent) - these are specific tailored agents for non-Oracle systems that make it possible to interacts with the target non-Oracle system. Heterogeneous Services (HS) This is a generic component for connecting to non-Oracle systems. It's an integrated component of the database that "extends the Oracle SQL engine to recognize the SQL and procedural capabilities of the remote non-Oracle system and the mappings required to obtain necessary data dictionary information" (Oracle Doc'). The following services are provided by the Heterogeneous Services (HS): Transaction service Responsible for establishing authenticated connection when the non-Oracle system is accessed and close the connection when session end. Also responsible for global data integrity using two phase commit protocol, even for non-Oracle systems that do not support two phase commit natively. SQL Service Provide the translation capabilities: SQL & data dictionary translations. The SQL services uses an information arrived from the Gateway to translate Oracle SQL to the appropriate SQL dialect of the non-Oracle system. Also, references to data dictionary tables in a query will be rewrite by the SQL Service and result with a result set as from Oracle database. ** Data type translation performed by the Gateway. Procedural Service An interface for executing stored procedures on non-Oracle system. Pass through SQL A mechanism for issuing a SQL statement against the non-Oracle system. It is useful when the statement/function/procedure are not supported by the Gateway. Oracle Database Gateways (agents) This component responsible for the interface to the remote non-Oracle system. It's also responsible for SQL mappings and data type conversions. The Gateway interacts with Heterogeneous Services to make it possible to transparently connect from an Oracle Database to a non-Oracle System. In contrast to the HS (Heterogeneous Services) which is a generic component, the Gateways are tailored specifically for each target non-Oracle system. There are Gateways for many systems such as: DB2, Sybase, Informix, SQL Server, IMS, VSAM, Adabas, Ingres, Teradata, to name a few. The Gateway can be installed on the same server like the non-Oracle system or on the same server like the Oracle system or on a separate server. Next post I'll show an example of connecting and retrieving data from a SQL Server database to an Oracle database using Oracle Transparent Gateway for Microsoft SQL Server including all configuration required for Transparent Gateway and the source Oracle System. Related Documents for more information: - Oracle® Transparent Gateway for Microsoft SQL Server Administrator's Guide 10g Release 2 (10.2) for Microsoft Windows (32-bit) - Database Gateways Technical Whitepaper You are more than welcome to leave a comment. Aviad