Oracle Notes

Sharing Oracle technology knowledge and experiences.

Posts Tagged ‘Architecture’

Protecting Archive Logs in the Event of a Disaster

Posted by sdefilip on May 6, 2011

 Summary of Methods to Protect Archive Log Tapes

 I have a client who is concerned about losing archive logs and being able to recover their database in the event of a disaster.  We take full RMAN backups, including archive logs and control files every night, but the concern is what would happen if their server is destroyed in between these backups. With the knowledge that a full Disaster Recovery Plan is not a simple undertaking and that they would need more than archive logs to recover their database in case of the destruction of their server, they asked me to provide options which I am sharing on this blog.  I am sharing the results of this undertaking. The environment is AIX 6.5 and Oracle 11.2 Standard Edition.  Please feel free to comment and share your suggestions.

 Options to protect archive log tapes (and the full database) are:

 I.  Have RMAN take more frequent backups, including archive logs.

II. Oracle allows specification of an alternate destination for archive logging that is 

     additional to the primary destination. 

      NFS mount a directory on a remote server. Use an alternate “archive_log_dest”    parameter to specify the NFS mounted remote directory as the alternate log destination.

III.  SFTP or rsynch a copy of the archive logs to a remote server through a shell script. The shell script would have to:

  1. check the V$ARCHIVED_LOG view to determine if each archive log file in the archive log directory has completed its archiving process.
  2. use the AIX rsynch command (or SFTP) to synchronize the remote archive log destination with the primary server archive log destination
  3. run this script every nn time intervals, leaving a window for the RMAN backups

IV. Use the pre-RMAN method of database backup. Copy the database user datafiles, control files, archive logs and parameter file to a directory on an NFS mounted remote server directory. This requires that an Oracle instance is installed on the remote server to recover the database from these files. This method has it’s weaknesses and so RMAN was created and offered as an alternative.

V.  Use RMAN to rig a standby database without DataGuard or GoldenGate;

      this involves another Oracle instance running on the remote server, use of 

    RMAN to clone the primary database, shipping primary database RMAN backup

    files to remote server and, finally, running an RMAN recovery of the database on

   the remote server.

VI.  Create an Oracle RAC 2-node cluster with one cluster being on a remote server.

       I believe that this is possible with 11.2 tandard Edition, possibly with a small

       charge per node.

 I recommend option “III” because it does not require another instance of Oracle on the remote server, it will not slow down the log writer Oracle process (LGWR) which would most likely result in a general database slowdown. In addition, the AIX/network resources used, if the script is not run very excessively, should not be sufficient to slowdown the Oracle database or the application.  My suggestion is to determine the average amount of time that the database takes to fully archive a log, decide how many logs you would like copied at the same time and use that average as a guideline to determine how frequently to schedule the script.

 

Posted in Best Practices, Disaster Recovery, Oracle 11g, Oracle 11g Database Administration, Tips | Tagged: , | Leave a Comment »

A Useful Oracle 11g New Feature

Posted by sdefilip on September 18, 2010

Server Result Cache

  • Enables query result to be cached in memory which can be used during future execution of a similar query by bypassing the regular processing thereby returning the results faster.
  • Decreases the wait time for both physical and logical IO by directly fetching the results from the cached memory.
  • Cached result set is completely shareable between the sessions and various statements as long as they share a common execution plan.
  • Server result cache is the new component of SGA that caches results of queries and is managed by automatic memory management.
  • New parameter RESULT_CACHE_MAX_SIZE is used to enable result cache by setting the maximum size of the cache.
  • A new optimizer hint allows use of result cache at the query level.

Query execution without result cache hint.

Query execution without result cache hint - Oracle 11gQuery execution without result cache hint – Oracle 11g

Query execution with result cache hint.

Query execution with result cache hint - Oracle 11gQuery execution with result cache hint – Oracle 11g

Parameters  related to Result Cache

RESULT_CACHE_MAX_RESULT  :  specifies the percentage of RESULT_CACHE_MAX_SIZE that any single result can use
RESULT_CACHE_MAX_SIZE    :  specifies the maximum amount of SGA memory (in bytes) that can be used by the Result Cache.
RESULT_CACHE_REMOTE_EXPIRATION   : specifies the number of minutes that a result using a remote object is allowed to remain valid
RESULT_CACHE_MODE    : specifies when a ResultCache operator is spliced into a query’s execution plan.

How to find result cache information:
V$RESULT_CACHE_STATISTICS
V$RESULT_CACHE_MEMORY
V$RESULT_CACHE_OBJECTS
V$RESULT_CACHE_DEPENDENCY

DBMS_RESULT_CACHE – PL/SQL API for result cache management:
Functions : Status – displays the current status of the result cache.

SELECT DBMS_RESULT_CACHE.status FROM dual;
STATUS
—————————————————–
ENABLED

Flush: remove all objects from the result cache and release memory.

I will cover some more features in next post . Keep reading

Posted in Oracle 11g, Oracle 11g Database Administration, Oracle 11g New Features, Oracle Architecture, Oracle Performance, Tips | Tagged: , , , | Leave a Comment »