Veritas InfoScale™ 8.0.2 Storage and Availability Management for Oracle Databases - AIX, Linux, Solaris

Scheduling the relocation of archive and Flashback logs

As are the primary mechanism for recovering from data corruption, database logs are normally kept on premium storage, both for I/O performance and data reliability reasons. Even after they have been archived, logs are normally kept online for fast recovery, but the likelihood of referring to an archived log decreases significantly as its age increases. This suggests that archived database logs might be relocated to lower-cost volumes after a certain period of inactivity.

Similarly, Storage Foundation Flashback technology creates logs that can be used for quick recovery from database corruption by restoring a database to its state at a previous time. Flashback logs are normally kept for a shorter period than archived database logs, if used at all, they are typically used within a few hours of creation. Two or three days are a typical Flashback log lifetime.

The rapidly decaying probability of use for archive and Flashback logs suggests that regular enforcement of a placement policy that relocates them to lower-cost storage after a period of inactivity can reduce an enterprise's average cost of online storage.

For example procedures, a customer could be using a large OLTP Oracle database with thousands of active sessions, which must be up and running 24 hours a day and seven days a week with uptime of over 99%. The database uses Flashback technology to correct any accidental errors quickly and generates a large number of archive logs per day. If the database goes down for any reason, there is a business requirement to bring the database back online and get it functional with in 15 minutes. To prevent Oracle log switch delays during transactions, the archive logs need to be created in a fast EMC array. Archive logs older than a week can be moved to a mid-range Clarion array. Archive logs older than 15 days can be moved to slow JBOD disks. Archive logs are purged after 30 days. Current Flashback logs are created manually by the database administrator on fast EMC storage and can be moved to Clarion storage after two days. The database administrator then deletes the Flashback logs after a week. To set up a system like this, see the following example. Assume that archive logs and Flashback logs are created on the same file system, /oralog. On the file system, /oralog/archive1 contains archive logs and /oralog/flashback contains Flashback logs.

Figure: Database storage configuration suitable for automatic relocation of archive and Flashback logs illustrates a three-tier volume configuration that is suitable for automatic relocation and deletion of archive logs and Flashback logs.

Figure: Database storage configuration suitable for automatic relocation of archive and Flashback logs

The file system used by the production database in this example originally resides on the single volume oralog, which must be prepared by adding volumes and placement classes assigned to the volumes.

Once the volumes are configured, an administrator can define file placement policy rules that specify access age-based relocation of selected files and assign them to the database's file system.

SmartTier for Oracle translates these commands into SmartTier access age-based policy rules, merges them with the file system's placement policy, and assigns the resulting policy to the file system. By default, SmartTier for Oracle enforces the active policy daily. During enforcement, the new rules relocate qualifying files to the destination storage tiers specified in the dbdst_file_move commands used to create the policies.