Types of Data Loss
Many users come to RAID with the expectation that using it will prevent data loss. This is expecting too much: RAID can help avoid data loss, but it can't prevent it. To understand why, and to be able to plan a better data protection strategy, it is useful to understand the different types of failures, and the way they can cause data loss.
Accidental or Intentional Erasure
A leading cause of data loss is the accidental or intentional erasure of files by you or another (human) user. This includes files that were erased by hackers who broke into your system, files that were erased by disgruntled employees, and files erased by you, thinking that they weren't needed any more, or due to a sense of discovery, to find out what old-timers mean when they say they fixed it for good by using the wizardly command su - root; cd /; rm -r *. RAID will not help you recover data lost in this way; to mitigate these kinds of losses, you need to perform regular backups (to archive media that aren't easily lost in a fire, stolen, or accidentally erased).
Total Disk Drive Failure
One possible disk drive failure mode is "complete and total disk failure". This can happen when a computer is dropped or kicked, although it can also happen due to old age (of the drive). Typically, the read head crashes into the disk platter, thereby trashing the head, and keeping any/everything on that platter from being readable. If the disk drive has only one platter, this means everything. Failure of the drive electronics (e.g. due to electrostatic discharge, or moisture buildup in capacitors) can result in the same symptoms. This is the pre-eminent failure mode that RAID protects against. By splattering data in a redundant way across many disks, the total failure of any one disk will not cause any actual data loss.
Power Loss and Ensuing Data Corruption
Many beginners think that they can test RAID by starting a disk-access intensive job, and then unplugging the power while it is running. This is usually guaranteed to cause some kind of data corruption, and RAID does nothing to prevent it or to recover the resulting lost data. This kind of data corruption/loss can be avoided by using a journaling file system, and/or a journaling database server (to avoid data loss in a running SQL server when the system goes down). In discussions of journaling, there are typically two types of protection that can be offered: journaled meta-data, and journaled (user's) data. The term "meta-data" refers to the file name, the file owner, creation date, permissions, etc., whereas "data" is that actual contents of the file. By journaling the meta-data, a journaling file system can guarantee fast system boot times, by avoiding long integrity checks during boot. However, journaling the meta-data does not prevent the contents of the file from getting scrambled. Note that most journaling file systems journal only the meta-data, and not the data. (Ext3fs can be made to journal data, but at a tremendous performance loss). Note that databases have their own unique ways of guaranteeing data integrity in the face of power loss or system crash.
Bad Blocks on Disk Drive
The most common form of disk drive failure is a slow but steady loss of 'blocks' on the disk drive. Blocks can go bad in a number of ways: the thin film of magnetic media can separate or slide on its underlying disk platter; the film of magnetic media can span a pit or gouge in the underlying platter, and eventually, like a soap bubble, it can pop. Although disk drives have filters that prevent dust from entering, the filters will not keep out humidity, and slow corrosion can set in. Mechanical abrasion can occur in several ways: the disk head can smash into the disk; alternately, a piece of broken-off media can temporarily jam under the head, or can skitter across the disk patter. Disk head crashes can be caused by kicking or hitting the CPU cabinet; they can also be caused by vibration induced by cooling fans, construction work in the room, etc. There are many other mechanical causes leading to (permanent) bad blocks. In addition, there are also "soft" or corrupted blocks: in modern hard drives, the size of one bit is so small that ordinary thermal noise (Boltzmann noise) is sufficient to occasionally flip a bit. This occurs so frequently that it is normally handled by the disk firmware: modern disk drives store ECC bits to detect and correct such errors. The number of ECC-corrected errors on a disk can be monitored with smartmon tools. Although on-disk ECC correction is sufficient to correct most soft errors, a tiny fraction will remain uncorrectable. Such soft errors damage the data, but do not render the block permanently (physically) unusable. Other soft errors are described in the next section below.
Over time, bad blocks can accumulate, and, from personal experience, do so as fast as one a day. Once a block is bad, data cannot be (reliably) read from it. Bad blocks are not uncommon: all brand new disk drives leave the factory with hundreds (if not thousands) of bad blocks on them. The hard drive electronics can detect a bad block, and automatically reassign in its place a new, good block from elsewhere on the disk. All subsequent accesses to that block by the operating system are automatically and transparently handled by the disk drive. This feature is both good, and bad. As blocks slowly fail on the drive, they are automatically handled until one day the bad-block lookup table on the hard drive is full. At this point, bad blocks become painfully visible to the operating system: Linux will grind to a near halt, while spewing dma_intr: status=0x51 { DriveReady SeekComplete UnrecoverableError } messages.
Using RAID can mitigate the effect of bad blocks. A Linux md-based software RAID array can be forced to run a check/repair sequence by writing the appropriate command to /sys/block/mdX/md/sync_action (see RAID Administration commands, and also below, for details). During repairs, if a disk drive reports a read error, the RAID array will attempt to obtain a good copy of the data from another disk, and then write the good copy onto the failing disk. Assuming the disk has spare blocks for bad-block relocation, this should trigger the bad-block relocation mechanism of the disk. If the disk no longer has spare blocks, then syslog error messages should provide adequate warning that a hard drive needs to be replaced. In short, RAID can protect against bad blocks, provided that the disk drive firmware is correctly detecting and reporting bad blocks. For the case of general data corruption, discussed below, this need not be the case.
General System Corruption
All computer systems are subject to a low-level but persistent corruption of data. Very rarely, a bit in system RAM will flip, due to any one of a large number of reasons, including Boltzmann thermal energy, ground bounce in signal paths, a low level of natural radioactivity in the silicon, and cosmic rays! Similar remarks apply to the CPU itself, as well as all on-chip busses. Off-chip busses (cabling), including EDI, SATA and SCSI cabling, is subject to ground-bounce, clock-skew, electrical interference, kinks in the wires, bad termination, etc. The size of individual bits in modern disk drives are now so small that even ordinary thermal noise (Boltzmann noise) will occasionally flip a bit. Not to be ignored is that obscure software or system bugs can also lead to corrupted data. Some parts may include systems to minimize such errors. For example, mid-range/high-end RAM chips include parity or ECC bits, or employ chipkill-based technology. Serial lines, including Ethernet and SATA, commonly using parity and checksumming to avoid errors. High-end disk drives store parity bits on the disk itself; unfortunately, such high-end drives typically cost about seven times more than consumer-grade disk drives (and/or offer one-seventh the capacity).
If some random "soft" error created in RAM is written to disk, it becomes enshrined and persistent, unless some step is taken to repair it. Random bit-flips on-disk are, by definition, persistent. As these random errors accumulate, they can render a system unusable, and permanently spoil data. Unfortunately, regular data backups do little to avoid this kind of corruption: most likely, one is backing up corrupted data.
Despite this being a common disk failure mode, there is very little (almost nothing) that can be done about it, at least on current Linux with current open source tools. RAID, even in theory, does not address this problem, nor does file system journaling. At this point, I am aware of only a small number of options:
Periodically run badblocks. This solution, in the form of 'e2fsck -f -cc' is terrible: it can only be run on an unmounted file system that was built on a raw disk partition, and its painfully slow -- typically a day or more for partitions the size of the disk itself. Furthermore, it works only on a raw disk partition: if the file system sits on top of a RAID md device, or an LVM logical volume, the exercise is pointless. There is currently no practical way of running a bad-blocks check on a mounted filesystem.
Use smartmontools (SMART == Self-Monitoring Analysis and Reporting Technology System) to carefully monitor the disk for signs of impending failure. This solution has several drawbacks: a) it won't help if you've already lost data, b) it is not well-integrated into desktops or low-end servers (see below for what it means to be "well-integrated"), and c) it does nothing at all if the data is corrupt, but the disk drive electronics doesn't know it.
Buy expensive server-class hard drives. Such hard drives have been engineered for added durability and reliability: using everything from thicker magnetic films and vibration-resistant parts, to on-disk ECC blocks to reconstruct lost data if any one sector goes bad. Unfortunately, server-class disk drives are much more expensive, and hold much less data -- about a factor of seven seems to be a historically typical.
If running md-based software RAID, use the RAID Administration commands echo check > /sys/block/md?/md/sync_action to compare the distinct images of a raid set to one-another; comparison results are posted to syslog; the mismatched block count can be viewed by cat /sys/block/md?/md/mismatch_cnt. The command echo repair > /sys/block/md?/md/sync_action will bring them back into sync. Unfortunately, if there is a block mis-compare, the repair action is not very clever. The following actions are taken (as of kernel 2.6.26 in July 2008):
For a RAID-1 config with two disks, if the data mis-compare was due to one block being unreadable, then the copy will be made from the remaining, readable block, which should hold good data. If both blocks were readable (i.e. were read from the disk, without the disk indicating any sort of error condition), but there was a mis-compare, then the data from the highest-numbered disk is copied to the other disk. This results in a 50-50 chance that good data was over-written by bad. Furthermore, this is done silently: no syslog messages indicate either a mis-compare, or that a repair action was taken.
For a three-disk RAID-1 system, in principle, one could be more clever, and let the multiple disks "vote" about the correct data. This is not done. In other words, RAID-1 will do "the right thing" only if the disk drive itself reported a read error. If the disk drive is silently returning bad data, its luck-of-the-draw as to whether the bad data will be propagated.
For raid-10 ... the behavior should be the same as for raid-1, but I have not personally checked.
For raid 5 and 6 ... the behavior is presumably similar to raid-1. This is somewhat unfortunate, as RAID-6 potentially contains enough information to "vote" for good blocks over bad blocks. A skim of the kernel code seems to indicate that that such voting is not considered when performing an admin-requested resync.
Use tripwire, with manually-designed scripts to hunt for data corruption. Tripwire is normally used to hunt for hacker break-ins and viruses, by recording and verifying file checksums. In principle, it could be used to find data corruption. Unfortunately, it fails to distinguish changes due to explicit file modifications (by system users), which should be considered to be "acceptable", from silent corruption. At this time, there is no simple, easy-to-use setup for using tripwire to hunt for data corruption.
Create scripts to periodically read files, compute their checksums, copy those files, and again compute checksums, looking for mis-compares. This has numerous draw-backs over a kernel-based method: a) it is difficult to check file meta-data and look for inode corruption. b) Files that are being actively edited can lead to false positives.
Woe is I! Over the last 15 years, I've retired over 25 hard drives with bad blocks, while managing a small stable of four or five servers and home computers. This works out to a failure rate of less than one every three years, but, multiplied by the number of machines, this adds up. Most recently, I installed a brand new WDC SATA drive, only to discover weeks later that it was silently corrupting my data, and that at a rather incredibly phenomenal rate: dozens of files a day. It took weeks of crazy system instability before I realized what was going on: the drive was really, really cheap! Defective from the factory! The silent part of the corruption was particularly disturbing: at no point did any Linux system component tell me quite what was really happening. Yet, this could have been avoided. Woe is I.
This lack of data error detection and data error correction options for Linux prompts the following wish-list:
Better smartmon integration
On ordinary, consumer-grade PC's, the smartmon tools should be integrated into the desktop panel: much like a low-battery alert on a laptop, there should be a badblocks/impending failure alert. Similarly, DriveReady/SeekComplete errors appearing in the syslog should also be promptly reflected in panel/dock status applets.
ECC integration into RAID
With only "minor" changes, software ECC could be incorporated into Linux raid. Currently, Linux RAID provides no bad data protection at all. In RAID-1, blocks are read from both disks: you have a 50-50 chance of getting any given block from one or the other disks. Data integrity could be improved by reading both disks, and comparing the results: this would immediately indicate a problem. However, if there is a mis-compare, it would not tell you which block is bad, and which one is good. The echo check > /sys/block/mdX/md/sync_action command, discussed above, performs such a compare. However, the recover action has only a 50-50 chance of picking the right block (when the drive itself doesn't signal a read error).
Similarly, RAID-5 stores parity bits, but does not actually use them for data integrity checks. RAID-6 stores a second set of parity bits, but does not use these in an ECC-like fashion. A "simple" modification of RAID-6 could, in principle, store ECC bits, and then use these for recovering from a bad block.
File system integrity monitors
Long-term archival data can slowly accumulate both soft and hard errors, even if the data is never explicitly accessed. It would be good to have an automatic archive monitoring tool to scan the disk for errors, and report them as they occur. Such scanning can be achieved by computing file checksums, and then periodically copying the files, and verifying that the old and new file checksums match. Mismatches indicate that either the old file, or the new copy, have damage due to soft or hard errors.
Currently, all file system integrity tools, such as tripwire, AIDE or FCheck are aimed at intrusion detection, and not at data decay. This means that all file changes are assumed to be malicious, even if they were initiated by the user. This makes them impractical for day-to-day operations on a normal file system, where user-driven actions cause many files to be added, modified and removed regularly. It is also inappropriate for triggering bad block replacement mechanisms, since unchanged files are never physically moved about the disk. (Physically writing a disk block will normally trigger bad block replacement algorithms in the disk firmware in most drives. Simply reading a block will not (in most drives)).
A core assumption of such a file-system integrity checker is that on-disk data corruption is far more frequent than data corruption due to spontaneous bit flips is RAM or other system components. If corruption in other system components was common, then the likelihood of false positives increases: that good on-disk data was mis-identified as bad.
See also:
Bad Sector Blues: The Dreaded "error=0x40 { UncorrectableError }" which walks through the problem, the options, and the recovery procedures.
Enhancing File System Integrity Through Checksums, Gopalan Sivathanu, Charles P. Wright, and Erez Zadok, Stony Brook University, 2004. Describes the NCryptfs file system, which includes measures to detect file system corruption.
Disk controller failure
Disk controller failure does not normally lead to data loss, but it can lead to system downtime. High-availability systems typically use multiple controllers, each with its own cabling to a disk drive, to minimize the impact of disk controller failure. The Linux multi-path driver supports such systems.
Linux RAID Solutions
There are three types of RAID solution options available to Linux users: software RAID, outboard DASD boxes, and RAID disk controllers.
Software RAID
Pure software RAID implements the various RAID levels in the kernel disk (block device) code. Pure-software RAID offers the cheapest possible solution: not only are expensive disk controller cards or hot-swap chassis not required, but software RAID works with cheaper IDE disks as well as SCSI disks. With today's fast CPU's, software RAID performance can hold its own against hardware RAID in all but the most heavily loaded and largest systems. The current Linux Software RAID is becoming increasingly fast, feature-rich and reliable, making many of the lower-end hardware solutions uninteresting. Expensive, high-end hardware may still offer advantages, but the nature of those advantages are not entirely clear.
The basic Linux Software RAID implementation is provided by the md (multi-disk) driver, which has been around since the late 1990's. Features of the md driver include:
RAID-0 (striping), 1(mirroring), 4 and 5(parity) support.
Automatic Hot Reconstruction (if the array is inconsistent due to a power outage or a replaced disk, it will be rebuilt in the background while the system is running).
Hot Spare (a standby disk will get used if another disk fails).
Hot Swap (disks can be changed in a running array).
MD does not handle bad block relocation.
Outboard DASD Solutions
DASD (Direct Access Storage Device, an old IBM mainframe term) are separate boxes that come with their own power supply, provide a cabinet/chassis for holding the hard drives, and appear to Linux as just another SCSI device. In many ways, these offer the most robust RAID solution. Most boxes provide hot-swap disk bays, where failing disk drives can be removed and replaced without turning off power. Outboard solutions usually offer the greatest choice of RAID levels: RAID 0,1,3,4,and 5 are common, as well as combinations of these levels. Some boxes offer redundant power supplies, so that a failure of a power supply will not disable the box. Finally, with Y-scsi cables, such boxes can be attached to several computers, allowing high-availability to be implemented, so that if one computer fails, another can take over operations.
Because these boxes appear as a single drive to the host operating system, yet are composed of multiple SCSI disks, they are sometimes known as SCSI-to-SCSI boxes. Outboard boxes are usually the most reliable RAID solutions, although they are usually the most expensive (e.g. some of the cheaper offerings from IBM are in the twenty-thousand dollar ballpark). The high-end of this technology is frequently called 'SAN' for 'Storage Area Network', and features cable lengths that stretch to kilometers, and the ability for a large number of host CPU's to access one array.
Inboard DASD Solutions
Similar in concept to outboard solutions, there are now a number of bus-to-bus RAID converters that will fit inside a PC case. These in several varieties. One style is a small disk-like box, that fits into a standard 3.5 inch drive bay, and draws power from the power supply in the same way that a disk would. Another style will plug into a PCI slot, and use that slot only for electrical power (and the space it provides).
Both SCSI-to-SCSI and EIDE-to-EIDE converters are available. Because these are converters, they appear as ordinary hard-drives to the operating system, and do not require any special drivers. Most such converters seem to support only RAID 0 (stripping) and 1 (mirroring), apparently due to size and cabling restrictions.
The principal advantages of inboard converters are price, reliability, ease-of-use, and in some cases, performance. Disadvantages are usually the lack of RAID-5 support, lack of hot-plug capabilities, and the lack of dual-ended operation.
RAID Disk Controllers
Disk Controllers are adapter cards that plug into the PCI bus. Just like regular disk controller cards, a cable attaches them to the disk drives. Unlike regular disk controllers, the RAID controllers will implement RAID on the card itself, performing all necessary operations to provide various RAID levels. Just like outboard boxes, the Linux kernel does not know (or need to know) that RAID is being used. However, just like ordinary disk controllers, these cards must have a corresponding device driver in the Linux kernel to be usable.
If the RAID disk controller has a modern, high-speed DSP/controller on board, and a sufficient amount of cache memory, it can outperform software RAID, especially on a heavily loaded system. However, using and old controller on a modern, fast 2-way or 4-way SMP machine may easily prove to be a performance bottle-neck as compared to a pure software-RAID solution. Some of the performance figures below provide additional insight into this claim.
Related Data Storage Protection Technologies
There are several related storage technologies that can provide various amounts of data redundancy, fault tolerance and high-availability features. These are typically used in conjunction with RAID, as a part of the overall system data protection design strategy.
SAN and NAS
There are a variety of high-end storage solutions available for large installations. These go typically under the acronyms 'NAS' and 'SAN'. NAS abbreviates 'Network Area Storage', and refers to NFS and Samba servers that Unix and Windows clients can mount. SAN abbreviates 'Storage Area Network', and refers to schemes that are the conceptual equivalent of thousand-foot-long disk-drive ribbon cables. Although the cables themselves may be fiber-optic (Fibre-Channel) or Ethernet (e.g. iSCSI), the attached devices appear to be 'ordinary disk drives' from the point of view of the host computer. These systems can be quite sophisticated: for example, this white-paper describes a SAN-like system that has built-in RAID and LVM features.
The Linux-iSCSI Project provides an iSCSI device driver that allows the local box to access iSCSI devices on the network.
Journaling
Journaling refers to the concept of having a file system write a 'diary' of information to the disk in such a way as to allow the file system to be quickly restored to a consistent state after a power failure or other unanticipated hardware/software failure. A journaled file system can be brought back on-line quickly after a system reboot, and, as such, is a vital element of building a reliable, available storage solution.
There are a number of journaled file systems available for Linux. These include:
ReiserFS (which is now a part of the default kernel),
ext3fs (see also here (which is backwards-compatible with ext2fs),
JFS (Developed by IBM, based on JFS for AIX),
XFS (Developed by SGI, based on XFS for Irix)
These different systems have different performance profiles and differ significantly in features and functions. There are many articles on the web which compare these. Note that some of these articles may be out-of-date with respect to features, performance or reputed bugs.
Journaling File Systems For Linux, Moshe Bar, May 2000, a more technical review of the theory and operation.
Journal File Systems in Linux, Ricardo Galli, January 2002, reviews some of the more technical aspects of the Linux kernel, such as the buffer cache, page cache, etc.
Journaling Filesystem Fragmentation Project, Constantin Loizide, 2001 Master's thesis investigating fragmentation effects on long-term performance.
Benchmarks: -- BULMA, May 2001 -- BULMA FAT32, June 2001 -- BULMA Mongo, May 2001 --
LVM
Several volume management systems are available for Linux; the best-known of these is LVM, the Logical Volume Manager. LVM implements a set of features and functions that resemble those that would be found in traditional LVM systems on other Unix's. The Linux LVM (like all traditional Unix volume management systems) provides an abstraction of the physical disks that makes it easier to administer large file systems and disk arrays. It does this by grouping sets of disks (physical volumes) into a pool (volume group). The volume group can be in turn be carved up into virtual partitions (logical volumes) that behave just like the ordinary disk block devices, except that (unlike disk partitions) they can be dynamically grown, shrunk and moved about without rebooting the system or entering into maintenance/standalone mode. A file system (or a swap space, or a raw block device) sits on top of a logical volume. In short, LVM adds an abstraction layer between the file system mount points (/, /usr, /opt, etc) and the hard drive devices (/dev/hda, /dev/sdb2, etc.)
The benefit of LVM is that you can add and remove hard drives, and move data from one hard drive to another without disrupting the system or other users. Thus, LVM is ideal for administering servers to which disks are constantly being added, removed or simply moved around to accommodate new users, new applications or just provide more space for the data. If you have only one or two disks, the effort to learn LVM may outweigh any administrative benefits that you gain.
Linux LVM and Linux Software RAID can be used together, although neither layer knows about the other, and some of the advantages of LVM seem to be lost as a result. The usual way of using RAID with LVM is as follows:
Use fdisk (or cfdisk, etc.) to create a set of equal-sized disk partitions.
Create a RAID-5 (or other RAID level array) across these partitions.
Use LVM to create a physical volume on the RAID device. For instance, if the RAID array was /dev/md0, then pvcreate /dev/md0.
Finish setting up LVM as normal.
In this scenario, although LVM can still be used to dynamically resize logical volumes, one does loose the benefit adding and removing hard drives willy-nilly. Linux RAID devices cannot be dynamically resized, nor is it easy to move a RAID array from one set of drives to another. One must still do space planning in order to have RAID arrays of the appropriate size. This may change: note that LVM is in the process of acquiring mirroring capabilities, although RAID-5 for LVM is still not envisioned.
Another serious drawback of this RAID+LVM combo is that neither Linux Software RAID (MD) nor LVM have any sort of bad-block replacement mechanisms. If (or rather, when) disks start manifesting bad blocks, one is up a creak without a paddle.
Veritas
The Veritas Foundation Suite is a storage management software product that includes an LVM-like system. The following very old press release announces this system: VERITAS Software Unveils Linux Strategy and Roadmap (January 2000) It seems that it is now available for IBM mainframes running Linux (August 2003)!
Diagnostic and Monitoring Tools
Sooner or later, you will feel the need for tools to diagnose hardware problems, or simply monitor the hardware health. Alternately, some rescue operations require low-level configuration tools. In this case, you might find the following useful:
smartmontools
The smartmontools package provides a set of utilities for working with the Self-Monitoring, Analysis and Reporting Technology (SMART) system built into modern IDE/ATA/PATA, SATA and SCSI-3 disks. These tools can report a variety of disk drive health statistics, and the smartd daemon can run continuously to log events into the syslog.
scsirastools
"This project includes changes that enhance the Reliability, Availability and Serviceability (RAS) of the drivers that are commonly used in a Linux software RAID-1 configuration. Other efforts have been made to enable various common hardware RAID adapters and their drivers on Linux." The project is slightly misnamed: the Linux scsi layer handles all modern SATA drives, as well as FCP, SAS and USB drives, and thus is applicable to most all modern hardware.
The package contains low level utilities including sgdskfl to load disk firmware, sgmode to get and set mode pages, sgdefects to read defect lists, and sgdiag to perform format and other test functions.
sg3_utils
The sg3_utils package provides a set of utilities for use with the Linux SCSI Generic (sg) device driver. This driver supports modern SATA and USB-connected disks, as well as SCSI, FCP, SAS disks. The utilities include sg variants for the traditional dd command, tools for scanning and mapping the SCSI bus, tools for issuing low-level SCSI commands, tools for timing and testing, and some example source & miscellany.
This web page is remarkable because it also provides a nice cross-reference to other diagnostic and monitoring tools
Wednesday, April 21, 2010
RAID Primer - What is RAID?
Server Products
RAID Primer - What is RAID?
Contents:
What is RAID?
RAID Levels - How the drives are organized
How to determine your RAID level
RAID 0 - Disk Striping
RAID 1 - Disk Mirroring
RAID 4 - Disk Striping with Parity on a Dedicated Drive
RAID 5 - Disk Striping with Distributed Parity
RAID 10 - Combination of RAID 0 & RAID 1
RAID (Redundant Array of Independent Disks) refers to multiple independent hard drives (the yellow pots in the picture) combined to form one large logical array (dashed pot). Data is stored on this array of disks with additional redundancy information. The redundancy information may be either the data itself (mirroring), or parity information calculated out of several data blocks (RAID 4, or RAID 5). With RAID in place, the operating system (Windows*, NetWare*, or Unix) no longer deals with individual drives, but instead with the entire disk array as one logical drive.
The major objectives of RAID are to improve data availability and security. RAID prevents downtime in the event of a hard disk failure, however it cannot recover data that has been deleted by the user or destroyed by a major event such as theft or a fire. Because of this, it is imperative to routinely back up your data to secure your system from these problems after a RAID system is installed.
There are two ways to implement a RAID solution. A hardware RAID controller is intelligent and processes all RAID information itself. With this kind of system installed, all control of the RAID array is offloaded from the host computer and is controlled entirely by the RAID controller. An alternative is to implement RAID with a simple host adapter and RAID driver. In this type of system, the driver is integrated into the operating system, i.e. Windows* NT. In this case, the performance of the RAID system is completely dependent on the processing load placed on the host CPU, which can potentially become a problem during the array reconstruction phase following a disk failure.
Some things to look for in a hardware RAID controller are: ease of installation and maintenance, the capabilities of the management software and the manufacturer's experience in developing RAID components. A RAID controller should support the most important RAID Levels (0, 1, 4, 5 and 10), and should be capable of simultaneously handling multiple arrays with different RAID levels across multiple channels.
RAID Levels - How the drives are organized
Each level of RAID spreads the data across the drives of the array in a different way and is optimized for specific situations. For our purposes, we are going to concentrate on the most common RAID levels used today.
How to determine your RAID level
RAID 0
This RAID level combines two or more hard drives in a way that the data (ABCD...in the yellow pots) coming from the user is cut into manageable blocks. These blocks are striped across the different drives of the RAID 0 array. By doing this, two or more hard drives are combined and the read/write performance, especially for sequential access, can be improved. However, no redundancy information is stored in a RAID 0 array, which means that if one hard drive fails, all data is lost. This lack of redundancy is also stated in the number 0, which indicates no redundancy. RAID 0 is thus usually not used in servers where security is a concern.
Advantage: Highest transfer rates
Disadvantage: No redundancy, i.e. if one disk fails all data will be lost
Application: Typically used in workstations for temporary data and high I/O rate
RAID 1
In a RAID 1 system, identical data is stored on two hard disks (100 percent redundancy). When one disk drive fails, all data is immediately available on the other without any impact on the performance or data integrity. We refer to "Disk Mirroring" when two disk drives are mirrored on one SCSI channel. If each disk drive is connected to a separate SCSI channel, we refer to this as "Disk Duplexing" (additional security). RAID 1 represents an easy and highly efficient solution for data security and system availability.
Advantage: High availability, one disk may fail, but the Logical Drive with the data is still available
Disadvantage: Requires 2 disks but only uses storage of one
Application: Typically used for smaller systems where capacity of 1 disk is sufficient and for boot disks
RAID 4
RAID 4 is very similar to RAID 0. The data is striped across the disk drives. Additionally, the RAID controller also calculates redundancy (parity information) which is stored on a separate disk drive (P1, P2, ...). Even when one disk drive fails, all data is still fully available. The missing data is accessed by calculating it from the data that remains available and from the parity information. Unlike RAID 1, only the capacity of one disk drive is needed for the redundancy. If we consider, for example, a RAID 4 disk array with 5 disk drives, 80 percent of the installed disk drive capacity is available as user capacity, only 20 percent is used for redundancy. In situations with many small data blocks, the parity disk drive becomes a throughput bottleneck. With large data blocks, RAID 4 shows significantly improved performance.
Advantage: High availability, one disk may fail, but the Logical Drive with the data is still available
Advantage: Has a very good use of disk capacity (array of n disks, n-1 is used for data storage)
Disadvantage: Has to calculate redundancy information, which limits write performance
Application: Typically used for larger systems for data storage due to efficient ratio of installed capacity to actual available capacity
RAID 5
Unlike RAID 4, the parity data in a RAID 5 disk array are striped across all disk drives. The RAID 5 disk array delivers a more balanced throughput. Even with small data blocks, which are very common in multitasking and multi-user environments, the response time is very good. RAID 5 offers the same level of security as in RAID 4: when one disk drive fails, all data is still fully available. The missing data is recalculated from the data that remains available and from the parity information.
Advantage: High availability, one disk may fail, but the Logical Drive with the data is still available
Advantage: Has a very good use of disk capacity (array of n disks, n-1 is used for data storage)
Disadvantage: Has to calculate redundancy information, which limits write performance
Application: Typically used for larger systems for data storage due to efficient ratio of installed capacity to actual available capacity
RAID 10
RAID 10 is a combination of RAID 0 (Performance) and RAID 1 (Data Security). Unlike RAID 4 and RAID 5, there is no need to calculate parity information. RAID 10 disk arrays offer good performance and data security. Similar to RAID 0, optimum performance is achieved in highly sequential load situations. Like RAID 1, 50 percent of the installed capacity is lost for redundancy.
Advantage: High availability, one disk may fail, but the Logical Drive with the data is still available
Advantage: Has good write performance
Disadvantage: Requires an even number of disks minimum 4, only half of the disk capacity is used
Application: Typically used for situations where high sequential write performance is required
RAID Primer - What is RAID?
Contents:
What is RAID?
RAID Levels - How the drives are organized
How to determine your RAID level
RAID 0 - Disk Striping
RAID 1 - Disk Mirroring
RAID 4 - Disk Striping with Parity on a Dedicated Drive
RAID 5 - Disk Striping with Distributed Parity
RAID 10 - Combination of RAID 0 & RAID 1
RAID (Redundant Array of Independent Disks) refers to multiple independent hard drives (the yellow pots in the picture) combined to form one large logical array (dashed pot). Data is stored on this array of disks with additional redundancy information. The redundancy information may be either the data itself (mirroring), or parity information calculated out of several data blocks (RAID 4, or RAID 5). With RAID in place, the operating system (Windows*, NetWare*, or Unix) no longer deals with individual drives, but instead with the entire disk array as one logical drive.
The major objectives of RAID are to improve data availability and security. RAID prevents downtime in the event of a hard disk failure, however it cannot recover data that has been deleted by the user or destroyed by a major event such as theft or a fire. Because of this, it is imperative to routinely back up your data to secure your system from these problems after a RAID system is installed.
There are two ways to implement a RAID solution. A hardware RAID controller is intelligent and processes all RAID information itself. With this kind of system installed, all control of the RAID array is offloaded from the host computer and is controlled entirely by the RAID controller. An alternative is to implement RAID with a simple host adapter and RAID driver. In this type of system, the driver is integrated into the operating system, i.e. Windows* NT. In this case, the performance of the RAID system is completely dependent on the processing load placed on the host CPU, which can potentially become a problem during the array reconstruction phase following a disk failure.
Some things to look for in a hardware RAID controller are: ease of installation and maintenance, the capabilities of the management software and the manufacturer's experience in developing RAID components. A RAID controller should support the most important RAID Levels (0, 1, 4, 5 and 10), and should be capable of simultaneously handling multiple arrays with different RAID levels across multiple channels.
RAID Levels - How the drives are organized
Each level of RAID spreads the data across the drives of the array in a different way and is optimized for specific situations. For our purposes, we are going to concentrate on the most common RAID levels used today.
How to determine your RAID level
RAID 0
This RAID level combines two or more hard drives in a way that the data (ABCD...in the yellow pots) coming from the user is cut into manageable blocks. These blocks are striped across the different drives of the RAID 0 array. By doing this, two or more hard drives are combined and the read/write performance, especially for sequential access, can be improved. However, no redundancy information is stored in a RAID 0 array, which means that if one hard drive fails, all data is lost. This lack of redundancy is also stated in the number 0, which indicates no redundancy. RAID 0 is thus usually not used in servers where security is a concern.
Advantage: Highest transfer rates
Disadvantage: No redundancy, i.e. if one disk fails all data will be lost
Application: Typically used in workstations for temporary data and high I/O rate
RAID 1
In a RAID 1 system, identical data is stored on two hard disks (100 percent redundancy). When one disk drive fails, all data is immediately available on the other without any impact on the performance or data integrity. We refer to "Disk Mirroring" when two disk drives are mirrored on one SCSI channel. If each disk drive is connected to a separate SCSI channel, we refer to this as "Disk Duplexing" (additional security). RAID 1 represents an easy and highly efficient solution for data security and system availability.
Advantage: High availability, one disk may fail, but the Logical Drive with the data is still available
Disadvantage: Requires 2 disks but only uses storage of one
Application: Typically used for smaller systems where capacity of 1 disk is sufficient and for boot disks
RAID 4
RAID 4 is very similar to RAID 0. The data is striped across the disk drives. Additionally, the RAID controller also calculates redundancy (parity information) which is stored on a separate disk drive (P1, P2, ...). Even when one disk drive fails, all data is still fully available. The missing data is accessed by calculating it from the data that remains available and from the parity information. Unlike RAID 1, only the capacity of one disk drive is needed for the redundancy. If we consider, for example, a RAID 4 disk array with 5 disk drives, 80 percent of the installed disk drive capacity is available as user capacity, only 20 percent is used for redundancy. In situations with many small data blocks, the parity disk drive becomes a throughput bottleneck. With large data blocks, RAID 4 shows significantly improved performance.
Advantage: High availability, one disk may fail, but the Logical Drive with the data is still available
Advantage: Has a very good use of disk capacity (array of n disks, n-1 is used for data storage)
Disadvantage: Has to calculate redundancy information, which limits write performance
Application: Typically used for larger systems for data storage due to efficient ratio of installed capacity to actual available capacity
RAID 5
Unlike RAID 4, the parity data in a RAID 5 disk array are striped across all disk drives. The RAID 5 disk array delivers a more balanced throughput. Even with small data blocks, which are very common in multitasking and multi-user environments, the response time is very good. RAID 5 offers the same level of security as in RAID 4: when one disk drive fails, all data is still fully available. The missing data is recalculated from the data that remains available and from the parity information.
Advantage: High availability, one disk may fail, but the Logical Drive with the data is still available
Advantage: Has a very good use of disk capacity (array of n disks, n-1 is used for data storage)
Disadvantage: Has to calculate redundancy information, which limits write performance
Application: Typically used for larger systems for data storage due to efficient ratio of installed capacity to actual available capacity
RAID 10
RAID 10 is a combination of RAID 0 (Performance) and RAID 1 (Data Security). Unlike RAID 4 and RAID 5, there is no need to calculate parity information. RAID 10 disk arrays offer good performance and data security. Similar to RAID 0, optimum performance is achieved in highly sequential load situations. Like RAID 1, 50 percent of the installed capacity is lost for redundancy.
Advantage: High availability, one disk may fail, but the Logical Drive with the data is still available
Advantage: Has good write performance
Disadvantage: Requires an even number of disks minimum 4, only half of the disk capacity is used
Application: Typically used for situations where high sequential write performance is required
Friday, December 4, 2009
Concurrent Manager Questions and Answers Relating to Generic Platform [ID
Concurrent Manager Questions and Answers Relating to Generic Platform [ID 105133.1]
--------------------------------------------------------------------------------
Modified 21-OCT-2008 Type FAQ Status PUBLISHED
PURPOSE
-------
To deliver a set of frequently asked questions against AOL FND Concurrent
Manager.
SCOPE & APPLICATION
-------------------
This reference would be most useful for one who is seeking additional
information regarding AOL FND Concurrent Manager issues.
FND Concurrent Manager FAQ's
-----------------------------
When would one be required to bounce (stop and restart) the Concurrent Manager?
When you modify the Printer Driver you have to restart the Manager which
runs the request which is attached to that Printer Driver, however,if you do
not know which manager then you have to restart the Internal manager because
the printer driver can be used by multiple managers and multiple requests.
If only a concurrent program definition is modified, running a verify on the
Internal Manager will pick up the changes without the need for bouncing the
manager.
Does the Internal manager schedule requests to be run or does it put requests
into queues to be run by other managers?
This is a very common misconception. The ICM really does not have any
such scheduling responsibilities. It has NOTHING to do with scheduling
requests, or deciding which manager will run a particular request.
Its function is only to run 'queue control' requests, which are
requests to startup or shutdown other managers. It is responsible for
startup and shutdown of the whole concurrent processing facility, and
it also monitors the other managers periodically, and restarts them if
they should go down. It can also take over the Conflict Resolution
manager's job, and resolve incompatibilities.
If the ICM itself should go down, requests will continue to run
normally, except for 'queue control' requests. You can restart it with
'startmgr', you do not need to kill the other managers first.
How can I check to see if a concurrent manager is running?
One way to see if a manager is running is to use the 'Administer
Concurrent Managers' form. Navigate to Concurrent->Managers->Administer.
You will see two columns labeled 'Actual' and 'Target'. The Target column
lists the number of processes that should be running for each manager
for this particular workshift. The Actual column lists the number of
processes that are actually running. If the Actual column is zero, there
are no processes running for this manager. If the Target column is zero,
then either a workshift has not been assigned to this manager, or the current
workshift does not specify any target processes. If the target column
is not zero, then the manager processes have either failed to start up,
or gone down. You should check the manager's logfile and the ICM
logfile. You can also search for OS processes using the 'ps' command.
It is possible for the form to be inaccurate, i.e. it may show actual
processes even though they are not really running. When in doubt, check for
processes at the OS level. On NT, you can check to see if the Concurrent Manager
service is running using the Services control panel.
Where do concurrent request or manager logfiles and output files go?
The concurrent manager first looks for the environment variable
$APPLCSF. If this is set, it creates a path using two other
environment variables: $APPLLOG and $APPLOUT
It places log files in $APPLCSF/$APPLLOG, output files go in
$APPLCSF/$APPLOUT
So for example, if you have this environment set:
$APPLCSF = /u01/appl/common
$APPLLOG = log
$APPLOUT = out
The concurrent manager will place log files in /u01/appl/common/log,
and output files in /u01/appl/common/out
Note that $APPLCSF must be a full, absolute path, and the other two
are directory names.
If $APPLCSF is not set, it places the files under the product top of
the application associated with the request. For example, a PO report
would go under $PO_TOP/$APPLLOG and $PO_TOP/$APPLOUT
Logfiles go to: /u01/appl/po/9.0/log
Output files to: /u01/appl/po/9.0/out
All these directories must exist and have the correct permissions.
Note that all concurrent requests produce a log file, but not necessarily
an output file.
Concurrent manager logfiles follow the same convention, and will be
found in the $APPLLOG directory
What are the logfile and output file naming conventions?
Request logfiles: l.req
Output files: If $APPCPNAM is not set:.
If $APPCPNAM = REQID: o.out
If $APPCPNAM = USER:.out
Where: = The request id of the concurrent request
And: = The id of the user that submitted the request
Manager logfiles:
ICM logfile: Default is std.mgr, can be changed with the mgrname
startup parameter
Concurrent manager log: w.mgr
Transaction manager log: t.mgr
Conflict Resolution manager log: c.mgr
Where: is the concurrent process id of the manager
Can I delete a concurrent manager?
You can disable the manager by checking the 'Enabled' checkbox, or
you can simply Terminate the manager and it will not run again unless
you reactivate it.
If it is really necessary, you can query the manager in the
'Define Manager' form, and delete the row. (It is recommended that you
DO NOT do this)
What is the function of the 'Conflict Resolution Manager'?
Concurrent managers read requests to start concurrent programs running. The
Conflict Resolution Manager checks concurrent program definitions for
incompatibility rules.
If a program is identified as Run Alone, then the Conflict Resolution Manager
prevents the concurrent managers from starting other programs in the same
conflict domain.
When a program lists other programs as being incompatible with it, the
Conflict Resolution Manager prevents the program from starting until any
incompatible programs in the same domain have completed running.
What is the 'Internal Scheduler/Prereleaser' manager?
The short name for this manager is FNDSCH. It is also known as the
Advanced Scheduler/Prereleaser Manager. This manager is intended
to implement Advanced Schedules. Its job is to determine when a
scheduled request is ready to run. Advanced Schedules were not fully
implemented in Release 11.0, they are implemented in Release 11.5,
but are not widely used by the various Apps products. General Ledger
uses FNDSCH for financial schedules based on different calendars and
period types. It is then possible to schedule AutoAllocation sets,
Recurring Journals, MassAllocations, Budget Formulas, and MassBudgets
to run according to the General Ledger schedules that have been
defined.
If financial schedules in GL are not being used then it is not a
problem to deactivate this manager.
What is the 'Internal Monitor' manager/service?
This manager/service is used to implement Distributed Concurrent Processing.
It monitors whether the ICM is still running, and if the ICM crashes,
it will restart it on another node.
You do not need to run this manager/service unless you are using Distributed
Concurrent Processing.
See the Installation manual and Sysadmin Guide for more info on DCP.
How do I check/set the PMON method?
To check the PMON method:
1) cd $FND_TOP/sql
2) sqlplus apps/apps @afimchk.sql
This will tell whether the internal manager is running, what the PMON
method is, and where the log file is.
To set the PMON method:
1) first shut the concurrent managers down
2) cd $FND_TOP/sql
3) sqlplus apps/apps @afimpmon.sql LOCK (or RDBMS)
How do I enable/disable the Conflict Resolution Manager?
Use the system profile option 'Concurrent: Use ICM'.
Setting this to 'No' (which is the default) allows the CRM to be started.
Setting it to 'Yes' causes the CRM to be shutdown and the Internal
Manager (ICM) will take over the conflict resolution duties. If the CRM will
not start (it is started automatically by the ICM), check this profile option.
Note that using the ICM to resolve conflicts is not recommended.
The CRM's sole purpose is to resolve conflicts, while the ICM has
other functions to perform as well. Only set this option to 'YES'
if you have a good reason to do so.
How do I clean out the Concurrent Manager tables?
Cleaning out the tables is a useful method of making sure that there
are no invalid statuses that can prevent the managers from starting.
Previously, this has been done by truncating fnd_concurrent_processes
and/or fnd_concurrent_requests. Truncation of the tables is a little
drastic, and can cause problems later when trying to purge requests,
not to mention losing all of the request information.
Run the script, cmclean.sql, article note 134007.1 CMCLEAN.SQL - Non
Destructive Script to Clean Concurrent Manager Tables
It will make sure the relevant status codes are valid without
deleting any information.
How do I tell concurrent manager processes apart at the OS level?
Use: pf -ef | grep FNDLIBR
This will produce output like:
vd11 13703 13660 0 May 11 ? 0:01 FNDLIBR FND Concurrent_Processor
MANAGE OLOGIN="APPS/94A491A1000000000000000000
n1070161 24936 24927 0 Apr 29 ? 0:05 FNDLIBR FND Concurrent_Processor
MANAGE OLOGIN="APPS_APPDEMO/94C4B1C10000000000
n1070161 24938 24927 0 Apr 29 ? 0:06 FNDLIBR FND Concurrent_Processor
MANAGE OLOGIN="APPS_APPDEMO/94C4B1C10000000000
n1070161 24927 24922 0 Apr 29 ? 2:03 FNDLIBR FND CPMGR FNDCPMBR sysmgr
="" sleep=60 pmon=20 diag=N logfile=/u16/app
The last process, #24927, shows 'FNDLIBR FND CPMGR', this one is the
Internal Manager (ICM). Notice that it gives some of the parameters it
was started with, the other processes showing 'Concurrent_Processor'
are Standard manager processes. Notice that the ICM process is the
parent process of the Standard managers. (processes 24936 and 24938)
Other managers will have the name of the executable, like ARLIBR or
INVLIBR:
$ ps -ef | grep ARLIBR
vd11 13683 13660 0 May 11 ? 0:20 ARLIBR APPS/82A2A4940000000000000
000000000000000000000000000000000000000 AR ART
The Conflict Resolution manager will look like:
$ ps -ef | grep FNDCRM
n1070161 24941 24927 0 Apr 29 ? 1:17 FNDCRM APPS_APPDEMO/84BFBEB900000
0000000000000000000000000000000000000000000000
What is the syntax for controlling the concurrent manager using startmgr and
concsub in NT?
On NT, the concurrent manager is run as an NT service, you start and
stop the managers using the Services control panel.
See the Applications Installation manual for NT, Appendix A for
details. See pg. 5-9 of this manual for instructions on creating the
concurrent manager service.
Why am I seeing pinging entries like this in the ICM logfile?
PING (0.0.0.0): 56 data bytes
64 bytes from 192.75.91.2: icmp_seq=0 ttl=255 time=0.705 ms
64 bytes from 192.75.91.2: icmp_seq=1 ttl=255 time=1.120 ms
Process monitor session ended : 29-FEB-2000 10:38:43
64 bytes from 192.75.91.2: icmp_seq=2 ttl=255 time=0.985 ms
64 bytes from 192.75.91.2: icmp_seq=3 ttl=255 time=1.006 ms
Pinging other machines is used in Distributed Concurrent Processing.
This means you have DCP turned on, using the environment variable
APPLDCP. Set APPLDCP to OFF and restart the managers.
I hit the Restart button to start the Standard manager, but it still did not
start?
Telling a manager to restart just sets the status to Restart. The ICM
will start it the next process monitor session or the next time the
ICM starts. Use Activate to start a manager immediately.
When a manager is deactivated manually, the ICM will not restart
it, you will need to set it to Restart, or activate it manually.
How many rows are in FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROCESSES
tables?
Depending on the specification of the system it has been seen that when
tables reach above 3000-4000 rows, the performance begins to diminish, however,
there could be 30000-40000 rows in the tale before the performance begins
to degrade.
You may want to run the Purge Concurrent Request and/or Manager Data on
a regular basis, dependant on the amount of requests being run.
The Purge Concurrent Requests job can be used to purge:
Requests, Mgr logs, and All requests depending on what is chosen.
Use the following options: Enter = All, Mode = AGE, Mode Value = 15
The std.mgr log continuously grows where it may good to
archive it regularly.
Any processes pending in Internal or Conflict Resolution Manager?
Best course of action before starting the Concurrent Managers is to cancel
any "Deactivate" or "Verify" jobs pending in the Internal Manager and place
any other pending jobs on hold.
How do I turn on transaction manager diagnostics?
Set the profile option 'Concurrent:Debug Flags' to 'TCTM1' at the site
level. This will cause transactions to make debug entries in the
FND_CONCURRENT_DEBUG_INFO table. Truncate this table before running a
tranasction, then select the entries from the table.
Starting the managers with diag=Y will also produce more information
in the transaction manager logfile.
How do transaction managers work?
Briefly:
(See the server documentation for details on the DBMS_PIPE package)
1) A tranasction manager is started on the concurrent processing
server, and periodically reads the pipe for incoming transactions.
2) A client program (usually a form) calls the
FND_TRANSACTION.SYNCHRONOUS function.
3) This function writes a message into the pipe containing the program
to be run and its parameters.
4) FND_TRANSACTION.SYNCHRONOUS begins reading a return pipe for the
return status.
5) The manager sees the message in the pipe, retrieves the program id
and parameters.
6) The manager runs the program with the specified parameters. The
program will be of type 'Immediate', so there will not be a
separate concurrent request run.
7) The program completes, and the manager packs its return status into
the return pipe.
8) FND_TRANSACTION.SYNCHRONOUS reads the return value and passes it
back to its caller.
Note that these events take place essentially simultaneously on the
client and server. This is a synchronous transaction because the
client waits for the server to return, or times out waiting for it.
Problem....
When you try to submit a request like Active users or
Active responsibilities, request gets submitted.
When we view the help requests, you find that it is
inactive / nomanager.
Within 12 to 15 seconds, you refresh-it gets completed.
Initially, you could find only inactive and we look at
the diagnostic- the concurrent manager assigned is not
picking up.
There is no specialization rules in any managers except
the include program this source.
Solution....
Most often when this occurs where a request goes
"inactive/no manager" and is then processed a short time
later, the solution is to either increase the cache size
for your Standard manger, or increase the actual number of
Standard manager processes.
Cache Size is set on the CONCURRENT/MANAGER/DEFINE form. Basically,
this regulates how many requests a manager will pick up for each
sleep cycle.
How do I process more concurrent requests concurrently?
The Concurrent Manager parameters, (Query the concurrent manager by
Login as Sysadmin, navigate -> Concurrent -> Manager -> Define and Query for
the relevant concurrent manager), should be modified to handle more
concurrent requests concurrently, this can be done in two steps:
(i) Increase the Number of Target processes for the manager
(ii) Change the cache size of the concurrent manager as this determines
how many requests will be evaluated by a manager at a time and should match the target (process) value as set above.
RELATED DOCUMENTS
-----------------
Note 1050938.6 What to Set $APPCPNAM for the Report Output File Naming Convention Format
Note 149600.1 FND_CONC_PP_ACTIONS and FND_RUN_REQ_PP_ACTIONS Growing Exponentially
Note 134007.1 CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables
Related
--------------------------------------------------------------------------------
Products
--------------------------------------------------------------------------------
Oracle E-Business Suite > Applications Technology > Application Object Library > Oracle Application Object Library
--------------------------------------------------------------------------------
Modified 21-OCT-2008 Type FAQ Status PUBLISHED
PURPOSE
-------
To deliver a set of frequently asked questions against AOL FND Concurrent
Manager.
SCOPE & APPLICATION
-------------------
This reference would be most useful for one who is seeking additional
information regarding AOL FND Concurrent Manager issues.
FND Concurrent Manager FAQ's
-----------------------------
When would one be required to bounce (stop and restart) the Concurrent Manager?
When you modify the Printer Driver you have to restart the Manager which
runs the request which is attached to that Printer Driver, however,if you do
not know which manager then you have to restart the Internal manager because
the printer driver can be used by multiple managers and multiple requests.
If only a concurrent program definition is modified, running a verify on the
Internal Manager will pick up the changes without the need for bouncing the
manager.
Does the Internal manager schedule requests to be run or does it put requests
into queues to be run by other managers?
This is a very common misconception. The ICM really does not have any
such scheduling responsibilities. It has NOTHING to do with scheduling
requests, or deciding which manager will run a particular request.
Its function is only to run 'queue control' requests, which are
requests to startup or shutdown other managers. It is responsible for
startup and shutdown of the whole concurrent processing facility, and
it also monitors the other managers periodically, and restarts them if
they should go down. It can also take over the Conflict Resolution
manager's job, and resolve incompatibilities.
If the ICM itself should go down, requests will continue to run
normally, except for 'queue control' requests. You can restart it with
'startmgr', you do not need to kill the other managers first.
How can I check to see if a concurrent manager is running?
One way to see if a manager is running is to use the 'Administer
Concurrent Managers' form. Navigate to Concurrent->Managers->Administer.
You will see two columns labeled 'Actual' and 'Target'. The Target column
lists the number of processes that should be running for each manager
for this particular workshift. The Actual column lists the number of
processes that are actually running. If the Actual column is zero, there
are no processes running for this manager. If the Target column is zero,
then either a workshift has not been assigned to this manager, or the current
workshift does not specify any target processes. If the target column
is not zero, then the manager processes have either failed to start up,
or gone down. You should check the manager's logfile and the ICM
logfile. You can also search for OS processes using the 'ps' command.
It is possible for the form to be inaccurate, i.e. it may show actual
processes even though they are not really running. When in doubt, check for
processes at the OS level. On NT, you can check to see if the Concurrent Manager
service is running using the Services control panel.
Where do concurrent request or manager logfiles and output files go?
The concurrent manager first looks for the environment variable
$APPLCSF. If this is set, it creates a path using two other
environment variables: $APPLLOG and $APPLOUT
It places log files in $APPLCSF/$APPLLOG, output files go in
$APPLCSF/$APPLOUT
So for example, if you have this environment set:
$APPLCSF = /u01/appl/common
$APPLLOG = log
$APPLOUT = out
The concurrent manager will place log files in /u01/appl/common/log,
and output files in /u01/appl/common/out
Note that $APPLCSF must be a full, absolute path, and the other two
are directory names.
If $APPLCSF is not set, it places the files under the product top of
the application associated with the request. For example, a PO report
would go under $PO_TOP/$APPLLOG and $PO_TOP/$APPLOUT
Logfiles go to: /u01/appl/po/9.0/log
Output files to: /u01/appl/po/9.0/out
All these directories must exist and have the correct permissions.
Note that all concurrent requests produce a log file, but not necessarily
an output file.
Concurrent manager logfiles follow the same convention, and will be
found in the $APPLLOG directory
What are the logfile and output file naming conventions?
Request logfiles: l
Output files: If $APPCPNAM is not set:
If $APPCPNAM = REQID: o
If $APPCPNAM = USER:
Where:
And:
Manager logfiles:
ICM logfile: Default is std.mgr, can be changed with the mgrname
startup parameter
Concurrent manager log: w
Transaction manager log: t
Conflict Resolution manager log: c
Where:
Can I delete a concurrent manager?
You can disable the manager by checking the 'Enabled' checkbox, or
you can simply Terminate the manager and it will not run again unless
you reactivate it.
If it is really necessary, you can query the manager in the
'Define Manager' form, and delete the row. (It is recommended that you
DO NOT do this)
What is the function of the 'Conflict Resolution Manager'?
Concurrent managers read requests to start concurrent programs running. The
Conflict Resolution Manager checks concurrent program definitions for
incompatibility rules.
If a program is identified as Run Alone, then the Conflict Resolution Manager
prevents the concurrent managers from starting other programs in the same
conflict domain.
When a program lists other programs as being incompatible with it, the
Conflict Resolution Manager prevents the program from starting until any
incompatible programs in the same domain have completed running.
What is the 'Internal Scheduler/Prereleaser' manager?
The short name for this manager is FNDSCH. It is also known as the
Advanced Scheduler/Prereleaser Manager. This manager is intended
to implement Advanced Schedules. Its job is to determine when a
scheduled request is ready to run. Advanced Schedules were not fully
implemented in Release 11.0, they are implemented in Release 11.5,
but are not widely used by the various Apps products. General Ledger
uses FNDSCH for financial schedules based on different calendars and
period types. It is then possible to schedule AutoAllocation sets,
Recurring Journals, MassAllocations, Budget Formulas, and MassBudgets
to run according to the General Ledger schedules that have been
defined.
If financial schedules in GL are not being used then it is not a
problem to deactivate this manager.
What is the 'Internal Monitor' manager/service?
This manager/service is used to implement Distributed Concurrent Processing.
It monitors whether the ICM is still running, and if the ICM crashes,
it will restart it on another node.
You do not need to run this manager/service unless you are using Distributed
Concurrent Processing.
See the Installation manual and Sysadmin Guide for more info on DCP.
How do I check/set the PMON method?
To check the PMON method:
1) cd $FND_TOP/sql
2) sqlplus apps/apps @afimchk.sql
This will tell whether the internal manager is running, what the PMON
method is, and where the log file is.
To set the PMON method:
1) first shut the concurrent managers down
2) cd $FND_TOP/sql
3) sqlplus apps/apps @afimpmon.sql LOCK (or RDBMS)
How do I enable/disable the Conflict Resolution Manager?
Use the system profile option 'Concurrent: Use ICM'.
Setting this to 'No' (which is the default) allows the CRM to be started.
Setting it to 'Yes' causes the CRM to be shutdown and the Internal
Manager (ICM) will take over the conflict resolution duties. If the CRM will
not start (it is started automatically by the ICM), check this profile option.
Note that using the ICM to resolve conflicts is not recommended.
The CRM's sole purpose is to resolve conflicts, while the ICM has
other functions to perform as well. Only set this option to 'YES'
if you have a good reason to do so.
How do I clean out the Concurrent Manager tables?
Cleaning out the tables is a useful method of making sure that there
are no invalid statuses that can prevent the managers from starting.
Previously, this has been done by truncating fnd_concurrent_processes
and/or fnd_concurrent_requests. Truncation of the tables is a little
drastic, and can cause problems later when trying to purge requests,
not to mention losing all of the request information.
Run the script, cmclean.sql, article note 134007.1 CMCLEAN.SQL - Non
Destructive Script to Clean Concurrent Manager Tables
It will make sure the relevant status codes are valid without
deleting any information.
How do I tell concurrent manager processes apart at the OS level?
Use: pf -ef | grep FNDLIBR
This will produce output like:
vd11 13703 13660 0 May 11 ? 0:01 FNDLIBR FND Concurrent_Processor
MANAGE OLOGIN="APPS/94A491A1000000000000000000
n1070161 24936 24927 0 Apr 29 ? 0:05 FNDLIBR FND Concurrent_Processor
MANAGE OLOGIN="APPS_APPDEMO/94C4B1C10000000000
n1070161 24938 24927 0 Apr 29 ? 0:06 FNDLIBR FND Concurrent_Processor
MANAGE OLOGIN="APPS_APPDEMO/94C4B1C10000000000
n1070161 24927 24922 0 Apr 29 ? 2:03 FNDLIBR FND CPMGR FNDCPMBR sysmgr
="" sleep=60 pmon=20 diag=N logfile=/u16/app
The last process, #24927, shows 'FNDLIBR FND CPMGR', this one is the
Internal Manager (ICM). Notice that it gives some of the parameters it
was started with, the other processes showing 'Concurrent_Processor'
are Standard manager processes. Notice that the ICM process is the
parent process of the Standard managers. (processes 24936 and 24938)
Other managers will have the name of the executable, like ARLIBR or
INVLIBR:
$ ps -ef | grep ARLIBR
vd11 13683 13660 0 May 11 ? 0:20 ARLIBR APPS/82A2A4940000000000000
000000000000000000000000000000000000000 AR ART
The Conflict Resolution manager will look like:
$ ps -ef | grep FNDCRM
n1070161 24941 24927 0 Apr 29 ? 1:17 FNDCRM APPS_APPDEMO/84BFBEB900000
0000000000000000000000000000000000000000000000
What is the syntax for controlling the concurrent manager using startmgr and
concsub in NT?
On NT, the concurrent manager is run as an NT service, you start and
stop the managers using the Services control panel.
See the Applications Installation manual for NT, Appendix A for
details. See pg. 5-9 of this manual for instructions on creating the
concurrent manager service.
Why am I seeing pinging entries like this in the ICM logfile?
PING (0.0.0.0): 56 data bytes
64 bytes from 192.75.91.2: icmp_seq=0 ttl=255 time=0.705 ms
64 bytes from 192.75.91.2: icmp_seq=1 ttl=255 time=1.120 ms
Process monitor session ended : 29-FEB-2000 10:38:43
64 bytes from 192.75.91.2: icmp_seq=2 ttl=255 time=0.985 ms
64 bytes from 192.75.91.2: icmp_seq=3 ttl=255 time=1.006 ms
Pinging other machines is used in Distributed Concurrent Processing.
This means you have DCP turned on, using the environment variable
APPLDCP. Set APPLDCP to OFF and restart the managers.
I hit the Restart button to start the Standard manager, but it still did not
start?
Telling a manager to restart just sets the status to Restart. The ICM
will start it the next process monitor session or the next time the
ICM starts. Use Activate to start a manager immediately.
When a manager is deactivated manually, the ICM will not restart
it, you will need to set it to Restart, or activate it manually.
How many rows are in FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROCESSES
tables?
Depending on the specification of the system it has been seen that when
tables reach above 3000-4000 rows, the performance begins to diminish, however,
there could be 30000-40000 rows in the tale before the performance begins
to degrade.
You may want to run the Purge Concurrent Request and/or Manager Data on
a regular basis, dependant on the amount of requests being run.
The Purge Concurrent Requests job can be used to purge:
Requests, Mgr logs, and All requests depending on what is chosen.
Use the following options: Enter = All, Mode = AGE, Mode Value = 15
The std.mgr log continuously grows where it may good to
archive it regularly.
Any processes pending in Internal or Conflict Resolution Manager?
Best course of action before starting the Concurrent Managers is to cancel
any "Deactivate" or "Verify" jobs pending in the Internal Manager and place
any other pending jobs on hold.
How do I turn on transaction manager diagnostics?
Set the profile option 'Concurrent:Debug Flags' to 'TCTM1' at the site
level. This will cause transactions to make debug entries in the
FND_CONCURRENT_DEBUG_INFO table. Truncate this table before running a
tranasction, then select the entries from the table.
Starting the managers with diag=Y will also produce more information
in the transaction manager logfile.
How do transaction managers work?
Briefly:
(See the server documentation for details on the DBMS_PIPE package)
1) A tranasction manager is started on the concurrent processing
server, and periodically reads the pipe for incoming transactions.
2) A client program (usually a form) calls the
FND_TRANSACTION.SYNCHRONOUS function.
3) This function writes a message into the pipe containing the program
to be run and its parameters.
4) FND_TRANSACTION.SYNCHRONOUS begins reading a return pipe for the
return status.
5) The manager sees the message in the pipe, retrieves the program id
and parameters.
6) The manager runs the program with the specified parameters. The
program will be of type 'Immediate', so there will not be a
separate concurrent request run.
7) The program completes, and the manager packs its return status into
the return pipe.
8) FND_TRANSACTION.SYNCHRONOUS reads the return value and passes it
back to its caller.
Note that these events take place essentially simultaneously on the
client and server. This is a synchronous transaction because the
client waits for the server to return, or times out waiting for it.
Problem....
When you try to submit a request like Active users or
Active responsibilities, request gets submitted.
When we view the help requests, you find that it is
inactive / nomanager.
Within 12 to 15 seconds, you refresh-it gets completed.
Initially, you could find only inactive and we look at
the diagnostic- the concurrent manager assigned is not
picking up.
There is no specialization rules in any managers except
the include program this source.
Solution....
Most often when this occurs where a request goes
"inactive/no manager" and is then processed a short time
later, the solution is to either increase the cache size
for your Standard manger, or increase the actual number of
Standard manager processes.
Cache Size is set on the CONCURRENT/MANAGER/DEFINE form. Basically,
this regulates how many requests a manager will pick up for each
sleep cycle.
How do I process more concurrent requests concurrently?
The Concurrent Manager parameters, (Query the concurrent manager by
Login as Sysadmin, navigate -> Concurrent -> Manager -> Define and Query for
the relevant concurrent manager), should be modified to handle more
concurrent requests concurrently, this can be done in two steps:
(i) Increase the Number of Target processes for the manager
(ii) Change the cache size of the concurrent manager as this determines
how many requests will be evaluated by a manager at a time and should match the target (process) value as set above.
RELATED DOCUMENTS
-----------------
Note 1050938.6 What to Set $APPCPNAM for the Report Output File Naming Convention Format
Note 149600.1 FND_CONC_PP_ACTIONS and FND_RUN_REQ_PP_ACTIONS Growing Exponentially
Note 134007.1 CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables
Related
--------------------------------------------------------------------------------
Products
--------------------------------------------------------------------------------
Oracle E-Business Suite > Applications Technology > Application Object Library > Oracle Application Object Library
Thursday, December 3, 2009
BACKUP PROCEDURES :
BACKUP PROCEDURES :
It is a very important aspect of any database which should be planned carefully as recovery depends upon the back up strategy which are being followed. Backup strategy depends upon the mode of database. Different methods are adopted for the database running in archivelog mode or database running in no-archivelog mode .
Criteria :
[1] When database is running in archive-log mode.
Cold backup :
In init.ora, search for parameter control_files to find the name of control
file for that database. Query the v$datafiles and v$logfiles views to find the names
of datafiles and redo logfiles associated with the database. Use the operating system
command to take the backup of these files. *
In init.ora, search for parameter log_archive_dest to find the location of archived files.
Use the operating system command to take the backup of these files.
Frequency: This backup has to be taken weekly.
Only Oracle database related file should be backedup. It will solve two problems
[a] Downtime of database will be less.
[b] Retrieval from cartridge will take less time.
[c] Less numbers of cartridges will be required.
File System Backup : Generally its frequency should be low. It will act as a backup for all the files (Oracle+O.S.+Other). It will be needed if all the disks crash. If you are creating important files on server then its frequency should be increased as decided by the site incharge.
Hot backup:
In init.ora, search for parameter control_files to find the name of control file for that database. Query the v$datafiles views to find the names of datafiles associated with the database.
Use the operating system command to take the backup of these files.*
In init.ora, search for parameter log_archive_dest to find the location of archived files.
Use the operating system command to take the backup of these files. *
Frequency: This backup has to be taken daily.
Logical Backup:
[1] Ideally Complete database export should be taken daily.
It is also called base backup.
[2] Take incremental export daily except on weekends.
On weekends, cumulative database export should be taken. When cumulative export is taken, one should remove incremental export to save space on disk.
On month end, take complete database export and remove previously stored cumulative export logical backups.
[3] Take important user level export daily.
Either of the above option can be implemented at the site but order of prefrence should be first try [1], if not then use [2] else last option should be [3]
Cartridges Strategy :
If you are taking complete database export then use three different sets of cartridges ( Grand father , Father and Son concepts .) on three different days . And rotate these cartridges again .
For Incremental Backups use six different sets of cartridges on six different days . And rotate these cartridges again after successfully completion of Cummulative database export backup .
For Cummulative Backups use different cartridges in every week and rotate those cartridges in the next month after successfully completion of Complete database export backup.
Recovery :
In day to day operation the most common type of failure is table drop or partial data loss in any table or instance failure. Using export backed up dump file (expdat.dmp) one can recover first two type of problems.
For instance failure, simply restart the database, oracle will automatically recover the database (Instance recovery).
For more complicated type of problems like media crash (data file loss etc.), please refer to annexure-1.
[2] When database is running in no archive-log mode.
Cold backup :
In init.ora, search for parameter control_files to find the name of control
file for that database. Query the v$datafiles and v$logfiles views to find the names
of datafiles and redo logfiles associated with the database. Use the operating system
command to take the backup of these files. Ideally this backup should be taken daily.
Logical Backup:
[1] Ideally Complete database export should be taken daily.
It is also called base backup.
[2] Take incremental export daily except on weekends.
On weekends, cumulative database export should be taken. When cumulative export is taken, one should remove incremental export to save space on disk.
On month end, take complete database export and remove previously stored cumulative export logical backups.
[3] Take important user level export daily.
Either of the above option can be implemented at the site but order of prefrence should be first try [1], if not then use [2] else last option should be [3]
Cartridges Strategy :
If you are taking complete database export then use three different sets of cartridges ( Grand father , Father and Son concepts .) on three different days . And rotate these cartridges again .
For Incremental Backups use six different sets of cartridges on six different days . And rotate these cartridges again after successfully completion of Cummulative database export backup .
For Cummulative Backups use different cartridges in every week and rotate those cartridges in the next month after successfully completion of Complete database export backup.
Recovery :
It is a very important process and it should be done very carefully. In day to day operation the most common type of failure is table drop or partial data loss in any table or instance failure. Using export backed up dump file (expdat.dmp) one can recover first two type of problems. For instance failure, simply restart the database, oracle will automatically recover the database (Instance recovery). For more complicated type of problems like media crash (data file loss etc.), please refer to annexure-1.
* Commands to be used in copying file(s) to backup device :
In Unix :
[a] cpio -ocBv < [name of file] > [/dev/rmt0.1|/dev/rmt0]
or
find / -name [pattern] -depth -print|cpio -ocBv > [/dev/rmt0.1|/dev/rmt0]
[b] tar -cvf [name of file] > [/dev/rmt0.1|/dev/rmt0]
or
tar -cvf /
Complete file system backup :
In Unix :
[a] find / -name -depth -print|cpio -ocBv > [/dev/rmt0.1|/dev/rmt0]
or
[b] To copy all files of unix to backup device
tar -cvf /
In Window NT :
Use backup utility to copy the necessary files.
Commands to be used in restoring file(s) from backup device :
In Unix :
[a] cpio -icBv < [/dev/rmt0.1|/dev/rmt0]
or
cpio -icBv “[pattern]” < [/dev/rmt0.1|/dev/rmt0]
[b] tar -xvf < [/dev/rmt0.1|/dev/rmt0]
In Window NT :
Use restore utility to copy the necessary files from backup device.
Annexure – 1
1. LOSS OF NON-ESSENTIAL DATAFILE WHEN DATABASE IS DOWN
( DATABASE CAN BE IN ARCHIVELOG MODE OR NO ARCHIVELOG
MODE )
SCENARIO
[1] Database startup fails with errors :
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing.
REQUIREMENT
[1] The script which will recreate the objects in the datafile like script which will create indexex.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN (5 MIN+ TIME TAKEN TO CREATE INDEXES)
NON-ESSENTIAL DATAFILES
DATAFILE OF INDEX TABLESPACE, TEMPORARY TABLESPACE.
SOLUTION
Shutdown the database.(shutdown immediate).
Take complete backup of current database.
Startup mount
Query the v$recover_file view along with v$datafile with a join on file# and note down the name of file
say it is /prodebs/test/ind.dbf.
Alter database datafile ‘/prodebs/test/ind.dbf’ offline;
(if database is in noarchivwlog mode command will be
Alter database datafile ‘/prodebs/test/ind.dbf’ offline drop; )
Alter database open;
Drop tablespace user_index including contents;
Create tablespace user_index
datafile ‘/prodebs/test/ind.dbf’ size 1M;
Run the script which will built indexes*.
Shutdown the database and take backup if necessary.
Startup.
* NB : For temporary tablespace skip this step.
———————————————————————————————–
2. MISSING MIRRORED ONLINE REDO LOG FILES (DATABASE IS UP/DOWN)
SCENARIO
Database opens neatly but in alert log two error messages are logged with errors :
(error from lgwr. Error is also written in lgwr trace file)
ora 313 open failed for members ..
ora 312 name of redo log memeber missing
ora 7360 OS error
ora 321 Can not update logfile header
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN 5 MIN.
SOLUTION
Shutdown the database.(shutdown).
Startup mount
Query v$logfile view and find which member has become invalid.
Query v$log view and find which group is current and size of group members (say it is b).
If the member of current log group (say it is 1) is corrupted issue the following commands :
Alter system switch logfile
If you can add one more member to corrupted log group ie maximum log member is not reached add one more
member to that group
Alter database add logfile member ‘filespec’ to group 1;
Shutdown the database
Startup the database
If you can not add one more member to corrupted log group
create one more log group with equal members and size of non corrupted log group.
Alter database group 3 (‘filespec’,'filespec’) size b;
Drop corrupted log group.
alter database drop logfile group 1;
Manually remove other members of this corrupted log group (ie rm in unix)
Shut down the database
Startup the database
———————————————————————————————–
3. RECOVER A LOST DATAFILE WITH NO BACKUP AND ALL ARCHIVED
LOG FILES
SCENARIO
Database startup fails with errors :
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing.
REQUIREMENT
For full recovery, database should be in archivelog mode.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 10 mins.
SOLUTION
Shutdown the database.(shutdown).
Startup mount;
Query v$recover_file
Query v$datafile and find the name of datafile which is missing.(say it is ‘/prodebs/test/user_odc.dbf’)
Now issue the following commands in the given order :
alter database datafile ‘/prodebs/test/user_odc.dbf’ offline;
alter database create datafile ‘/prodebs/test/user_odc.dbf’ as ‘/prodebs/test/user_odc1.dbf’;
(removed file) (new file)
alter database datafile ‘/prodebs/test/user_odc1.dbf’ online;
alter database recover datafile ‘/prodebs/test/user_odc1.dbf’; or recover database
It will generally recover the database if you have all the archived file with you
alter database open;
Shutdown the database and take necessary backup if required.
Start the database.
———————————————————————————————–
4. RECOVER A LOST DATAFILE WITH BACKUP AND ALL ARCHIVED
LOG FILES
SCENARIO
Database startup fails with errors :
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing.
REQUIREMENT
For full recovery, database should be in archivelog mode.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN 5 mins.
SOLUTION
Shutdown the database.(shutdown).
Startup mount;
Query v$recover_file
Query v$datafile and find the name of datafile which is missing.(say it is ‘/prodebs/test/user_odc.dbf’)
Copy the archived datafile (old one that is in backup) and give following commands :
recover database;
alter database open;
Shutdown the database and take necessary backup if required.
Start the database.
———————————————————————————————–
5. RECOVER A LOST DATAFILE WITH BACKUP AND MISSING ARCHIVED
LOG FILES.
SCENARIO
Database startup fails with errors :
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing.
REQUIREMENT
For recovery, database should be in archivelog mode.
CONDITION
Recovery will be incomplete.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 10 mins.
SOLUTION
Shutdown the database.(shutdown).
Copy all your datafiles from backup except control file.
Startup mount;
Copy the archived datafile (old one that is in backup) and give following commands :
recover database until cancel
alter database open resetlogs;
Shutdown the database and take necessary backup if required.
Start the database.
———————————————————————————————–
6. LOSS OF DATAFILE WHEN THE DATABASE IS IN NOARCHIVELOG MODE WITH NO LOGICAL BACKUP AND RECOVERY.
SCENARIO
Database startup fails with errors : (a) on monday morning (b) on thursday
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
There is no export and import backup (Logical backup).
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Problem has occured on (a) on monday morning (b) on thursday.
Here data file (s) associated with user tablespace is (are) lost.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION when Problem has occured on (a) on monday morning
Shutdown the database.(shutdown).
Take complete backup of current database.
Delete all the database files, redo log files and control files.
Copy the all the database files , redo log files and control files from the backup.
Start the database. There is no data loss.
SOLUTION when Problem has occured on (a) on thursday morning. Here data loss will occur.
Shutdown the database.(shutdown).
Take complete backup of current database.
Delete all the database files, redo log files and control files.
Copy the all the database files , redo log files and control files from the backup.
Start the database. There is data loss for monday, tuesday and wednessday.
Ask user to reenter the data.
7. LOSS OF DATAFILE WHEN THE DATABASE IS IN NOARCHIVELOG MODE WITH LOGICAL BACKUP AND RECOVERY.
SCENARIO
Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
There is export and import backup. (Logical backup).
Strategy : So along with coldback up a complete database backup is also taken.
(i) After this on everyday, an incremental backup is also taken.
(ii) After this on everyday, complete database backup is also taken.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Here data file (s) associated with user tablespace is (are) lost.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
[i] SOLUTION when Problem has occured on thursday morning and incremental backup is taken.
Shutdown the database.(shutdown).
Take complete backup of current database.
Delete all the database files, redo log files and control files.
Copy the all the database files , redo log files and control files from the backup.
Start the database.
Apply incremental export using import file starting from monday to wednesday.There is no data loss.
[ii] SOLUTION when Problem has occured on thursday morning and daily complete database logical
backup is taken.
Shutdown the database.(shutdown).
Take complete backup of current database.
Delete all the database files, redo log files and control files.
Copy the all the database files , redo log files and control files from the backup.
Start the database.
Apply wednesday complete export backup. There is no data loss.
8. LOSS OF SYSTEM DATAFILE WHEN THE DATABASE IS IN ARCHIVE
LOG MODE AND RECOVERY.
SCENARIO
Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Here data file (s) associated with system tablespace is (are) lost.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the missing system database file(s).
Startup mount exclusive;
recover database;
alter database open;
Database is ready for use.
9. LOSS OF NON SYSTEM DATAFILE WITHOUT ROLLBACK SEGMENTS
WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.
SCENARIO
Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Here data file (s) associated with user tablespace is (are) lost.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION -1 (DATABASE RECOVERY)
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the missing database file(s).
Startup mount exclusive;
recover database;
alter database open;
Database is ready for use.
SOLUTION -2 (DATAFILE RECOVERY)*
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the missing database file(s).
Startup mount exclusive;
Alter database datafile ” offline;
Alter database open;
recover datafile ”
Alter database datafile ” online;
Database is ready for use.
* If multiple datafiles are lost use parallel recovery method (from muliple terminal use the same method for different files).
SOLUTION -3 (TABLESPACE RECOVERY)
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the missing database file(s).
Startup mount exclusive;
Alter database datafile ” offline;
Alter database open;
Alter tablespace offline temporary;
recover tablespace ;
Alter tablespace online ;
Database is ready for use.
10. LOSS OF NON SYSTEM DATAFILE WITH ROLLBACK SEGMENTS WHEN
THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.
SCENARIO
Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Datafile(s) associated with rollback segment tablespace is (are) lost.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION –
Shutdown the database.(shutdown).
Take complete backup of current database.
Comment the ROLLBACK_SEGMENT parameter or assign ROLLBACK_SEGMENT=(SYSTEM)
in init.ora file before startup.
Copy the missing rollback segment database file(s).
Startup mount exclusive;
alter database datafile ” offline;
alter database open;
Alter tablespace offline temporary;
recover tablespace ;
Alter tablespace online;
Query dba_rollback_segs (column name segment_name, status) and note down the name of segment
name having status recovery (say they are r01, r02,r03).
alter rollback segment r01 online;
alter rollback segment r02 online;
alter rollback segment r03 online;
shutdown the database.
Remove comment from rollback_segment parameter or remove system value and give the name of rollback
segments which you want to be online when database starts.
Start the database.
Database is ready for use.
11. LOSS OF UNARCHIVED ONLINE LOG FILES WHEN THEY ARE NOT
MIRRORED WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND
RECOVERY.
SCENARIO
Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
All the online redo log files are lost.
All the data files and current control files are intact.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION –
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the all the database files from latest offline or online backup.
Startup mount exclusive;
recover database until cancel;
alter database open resetlogs;
shutdown the database.
Take cold backup. It is strongely advised.
Start the database.
Database is ready for use.
12. DATABASE CRASH DURING HOT BACKUP WHEN THE DATABASE IS IN
ARCHIVE LOG MODE AND RECOVERY.
SCENARIO
While taking hot backup, database crashes.
(a) When Oracle Version is 7.2 or more.
(b) When Oracle Version is 7.1.
TIME TAKEN IN RECOVERY
[a] DATABASE WILL BE READY FOR USE IN MIN 2 mins.
[b] DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION – (Oracle Version is 7.2 or more)
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
Query the view v$backup and get file # having status active. Now from v$datafile get the name of
file which is active in v$backup
alter database datafile ” end backup;
alter database open ;
shutdown the database.
Start the database.
Database is ready for use.
SOLUTION – (Oracle Version is 7.1 )
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
recover database ;
( This may take significant amount of time if a large number of archived logs are to be applied)
alter database open ;
shutdown the database.
Start the database.
Database is ready for use.
13. LOSS OF CONTROL FILE AND WHEN THE DATABASE IS IN ARCHIVE
LOG MODE AND RECOVERY.
SCENARIO
[a] Loss of control file when it is mirrored.
[b] Loss of control file when there is a backup and it is not mirrored but not before last reset log option.
[c] Loss of control file when there is no backup and it is not mirrored (total loss).
NB : Loss of control file when there is a backup and it is not mirrored but it is before last reset log option.
(suppose database is open on day x with alter database startup resetlogs.So your control file should
be before xth day )
startup/recover database using backup controlfile
Oracle error no is ora 1190 :control file or data file 1 is from before the last RESETLOGS
ora 1110 :name of system datafile.
[b] Loss of control file when there is a backup and it is not mirrored but not before last reset log option.
(suppose database is open on day x with alter database startup resetlogs.So your control file should
be after xth day )
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: ‘/prodebs/test/sys_odc.dbf’
ORA-01207: file is more recent than control file – old control file
TIME TAKEN IN RECOVERY
[a] DATABASE WILL BE READY FOR USE IN MIN 2 mins.
[b] DATABASE WILL BE READY FOR USE IN MIN 30 mins.
[c] DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION – (Loss of control file when it is mirrored)
Shutdown the database.(shutdown).
Copy the second control file to this disk.
Rename this control file to the lost one.
Start the database.
Database is ready for use.
SOLUTION – (Loss of all the control file(s) )
Shutdown the database.(shutdown).
run backup of controlfile which you might have taken using the following command :
alter database backup controlfile to trace;
It creates a script. After editing that script it looks like this :
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “” NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 100
LOGFILE
GROUP 1 (
‘/prodebs/test/redo_odc11.dbf’,
‘/prodebs/test/redo_odc12.dbf’
) SIZE 50K,
GROUP 2 (
‘/prodebs/test/redo_odc21.dbf’,
‘/prodebs/test/redo_odc22.dbf’
) SIZE 50K
DATAFILE
‘/prodebs/test/sys_odc.dbf’,
‘/prodebs/test/sys_odc1.dbf’,
‘/prodebs/test/user_odc.dbf’,
‘/prodebs/test/temp_odc.dbf’,
‘/prodebs/test/rbs_odc.dbf’,
‘/prodebs/test/ind.dbf’
;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
shutdown the database.
Start the database.
Database is ready for use.
SOLUTION – (Loss of control file when there is a backup and it is not mirrored)
Shutdown the database.(shutdown).
Copy the old control file to this disk.
startup mount exclusive;
If you have any tablespace which is read only, take all the datafile offiline related to this tablespace.
recover database using backup controlfile;
Offline datafile should bring to online status. (alter database datafile ‘< name of datafile>’ online;)
(for read only tablespace)
alter database open resetlogs;
Shutdown the database.
Take cold backup. It is strongely advised.
Start the database.
Database is ready for use.
14. DATABASE SPACE MANAGEMENT WHEN THE DATABASE IS IN
ARCHIVE LOG MODE AND RECOVERY (RESIZING DATAFILE).
SCENARIO
Space management when :
(a) Oracle Version is 7.2 or more.
(b) Oracle Version is 7.1.
Oracle Error is : ora 00376 file # can not be read at this time.
ora 01110 name of datafile.
TIME TAKEN IN RECOVERY
[a] DATABASE WILL BE READY FOR USE IN MIN 5 mins.
[b] DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION – (Oracle Version is 7.2 or more)
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup open;
Query the view v$datafile get the name of file which you want to resize.
alter database datafile ” resize [m/k];
shutdown the database.
Take backup if necessary.
Start the database.
Database is ready for use.
SOLUTION – (Oracle Version is 7.1 )
[a] Restore the datafile and apply recovery. Resizing is not possible.
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the deleted datafile
Startup mount exclusive;
recover database ;
( This may take significant amount of time if a large number of archived logs are to be applied)
alter database open ;
shutdown the database.
Start the database.
Database is ready for use.
[b] If deleted datafile is not available in backup .
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
alter database add datafile ” as ”;
recover database ;
( This may take significant amount of time if a large number of archived logs are to be applied)
alter database open ;
shutdown the database.
Start the database.
Database is ready for use.
[c] Rebuild tablespace
Requirement : Logical backup is there.
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
alter database datafile ” offline;
alter database open;
alter tablespace offline;
drop tablespace ;
create tablespace datafile ” size [m/k];
alter tablespace online;
use export / import method to recover the loss data.
shutdown the database.
Take backup if necessary.
Start the database.
Database is ready for use.
15. RECOVERY THROUGH RESETLOGS .
CONDITIONS :
[A] When online redo logs files are deleted.
[B] Loss of all control files.
[C] When recovery is done through old control files.
Events
[1]
a- Cold backup is taken.
b- Loss of redo log file.and media recovery. At this moment, a backup is taken.
c- Loss of data file
[2]
a- Cold backup is taken.
b- Loss of redo log file.and media recovery. At this moment, a backup is not taken.
c- Loss of data file .
SOLUTION – [1]
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the most recent cold backup of datafiles.
Startup mount exclusive;
Recover database;
alter database open;
shutdown the database.
Take a cold backup.
Start the database.
Database is ready for use.
Advantage : All the data will be recovered.
SOLUTION – [2]-i
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
alter database datafile ” offline;
alter database open;
Export all the data from the tablespace (all objects in missing file will be inaccessible),
Drop and recreate the tablespace.
Import all the data taken from tablespace.
recover datafile ” ;
alter database open;
shutdown the database.
Take a cold backup.
Start the database.
Database is ready for use.
Disadvantage : All the data will be lost that was entered in datafile 5;
SOLUTION – [2]-ii
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the most recent cold backup of datafile and control file only. Do not copy redo log file.
Startup mount exclusive;
Recover database;
alter database open resetlogs;
shutdown the database.
Take a cold backup.
Start the database.
Database is ready for use.
Disadvantage : All the data will be lost that after event b.
16. LOSS OF DATA FILE WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.
SCENARIO
Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Datafile(s) associated with user tablespace is (are) permanently lost.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION –
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
alter database create datafile ” ;
recover datafile ” ;
alter database open;
shutdown the database.
Start the database.
Database is ready for use.
17. SYSTEM CLOCK CHANGE AND POINT-IN-TIME RECOVERY.
SCENARIO
Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Datafile(s) associated with user tablespace is (are) permanently lost.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION –
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
recover database until time ” ;
alter database open resetlogs;
shutdown the database.
Take a cold backup of database;
Start the database.
Database is ready for use.
18. OFFLINE TABLESPACES AND RECOVERY.
SCENARIO
Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Datafile(s) associated with user tablespace is (are) permanently lost.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION – 1
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
recover database ;
alter database open ;
recover tablespace ;
alter tablespace online;
Shutdown the database.
Start the database.
Database is ready for use.
SOLUTION – 2 (better)
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
Query the view v$datafile and note which datafile is offline say it is .
alter database datafile <’a'> online;
recover database;
alter database open ;
shutdown the database
Start the database.
Database is ready for use.
It is a very important aspect of any database which should be planned carefully as recovery depends upon the back up strategy which are being followed. Backup strategy depends upon the mode of database. Different methods are adopted for the database running in archivelog mode or database running in no-archivelog mode .
Criteria :
[1] When database is running in archive-log mode.
Cold backup :
In init.ora, search for parameter control_files to find the name of control
file for that database. Query the v$datafiles and v$logfiles views to find the names
of datafiles and redo logfiles associated with the database. Use the operating system
command to take the backup of these files. *
In init.ora, search for parameter log_archive_dest to find the location of archived files.
Use the operating system command to take the backup of these files.
Frequency: This backup has to be taken weekly.
Only Oracle database related file should be backedup. It will solve two problems
[a] Downtime of database will be less.
[b] Retrieval from cartridge will take less time.
[c] Less numbers of cartridges will be required.
File System Backup : Generally its frequency should be low. It will act as a backup for all the files (Oracle+O.S.+Other). It will be needed if all the disks crash. If you are creating important files on server then its frequency should be increased as decided by the site incharge.
Hot backup:
In init.ora, search for parameter control_files to find the name of control file for that database. Query the v$datafiles views to find the names of datafiles associated with the database.
Use the operating system command to take the backup of these files.*
In init.ora, search for parameter log_archive_dest to find the location of archived files.
Use the operating system command to take the backup of these files. *
Frequency: This backup has to be taken daily.
Logical Backup:
[1] Ideally Complete database export should be taken daily.
It is also called base backup.
[2] Take incremental export daily except on weekends.
On weekends, cumulative database export should be taken. When cumulative export is taken, one should remove incremental export to save space on disk.
On month end, take complete database export and remove previously stored cumulative export logical backups.
[3] Take important user level export daily.
Either of the above option can be implemented at the site but order of prefrence should be first try [1], if not then use [2] else last option should be [3]
Cartridges Strategy :
If you are taking complete database export then use three different sets of cartridges ( Grand father , Father and Son concepts .) on three different days . And rotate these cartridges again .
For Incremental Backups use six different sets of cartridges on six different days . And rotate these cartridges again after successfully completion of Cummulative database export backup .
For Cummulative Backups use different cartridges in every week and rotate those cartridges in the next month after successfully completion of Complete database export backup.
Recovery :
In day to day operation the most common type of failure is table drop or partial data loss in any table or instance failure. Using export backed up dump file (expdat.dmp) one can recover first two type of problems.
For instance failure, simply restart the database, oracle will automatically recover the database (Instance recovery).
For more complicated type of problems like media crash (data file loss etc.), please refer to annexure-1.
[2] When database is running in no archive-log mode.
Cold backup :
In init.ora, search for parameter control_files to find the name of control
file for that database. Query the v$datafiles and v$logfiles views to find the names
of datafiles and redo logfiles associated with the database. Use the operating system
command to take the backup of these files. Ideally this backup should be taken daily.
Logical Backup:
[1] Ideally Complete database export should be taken daily.
It is also called base backup.
[2] Take incremental export daily except on weekends.
On weekends, cumulative database export should be taken. When cumulative export is taken, one should remove incremental export to save space on disk.
On month end, take complete database export and remove previously stored cumulative export logical backups.
[3] Take important user level export daily.
Either of the above option can be implemented at the site but order of prefrence should be first try [1], if not then use [2] else last option should be [3]
Cartridges Strategy :
If you are taking complete database export then use three different sets of cartridges ( Grand father , Father and Son concepts .) on three different days . And rotate these cartridges again .
For Incremental Backups use six different sets of cartridges on six different days . And rotate these cartridges again after successfully completion of Cummulative database export backup .
For Cummulative Backups use different cartridges in every week and rotate those cartridges in the next month after successfully completion of Complete database export backup.
Recovery :
It is a very important process and it should be done very carefully. In day to day operation the most common type of failure is table drop or partial data loss in any table or instance failure. Using export backed up dump file (expdat.dmp) one can recover first two type of problems. For instance failure, simply restart the database, oracle will automatically recover the database (Instance recovery). For more complicated type of problems like media crash (data file loss etc.), please refer to annexure-1.
* Commands to be used in copying file(s) to backup device :
In Unix :
[a] cpio -ocBv < [name of file] > [/dev/rmt0.1|/dev/rmt0]
or
find / -name [pattern] -depth -print|cpio -ocBv > [/dev/rmt0.1|/dev/rmt0]
[b] tar -cvf [name of file] > [/dev/rmt0.1|/dev/rmt0]
or
tar -cvf /
Complete file system backup :
In Unix :
[a] find / -name -depth -print|cpio -ocBv > [/dev/rmt0.1|/dev/rmt0]
or
[b] To copy all files of unix to backup device
tar -cvf /
In Window NT :
Use backup utility to copy the necessary files.
Commands to be used in restoring file(s) from backup device :
In Unix :
[a] cpio -icBv < [/dev/rmt0.1|/dev/rmt0]
or
cpio -icBv “[pattern]” < [/dev/rmt0.1|/dev/rmt0]
[b] tar -xvf < [/dev/rmt0.1|/dev/rmt0]
In Window NT :
Use restore utility to copy the necessary files from backup device.
Annexure – 1
1. LOSS OF NON-ESSENTIAL DATAFILE WHEN DATABASE IS DOWN
( DATABASE CAN BE IN ARCHIVELOG MODE OR NO ARCHIVELOG
MODE )
SCENARIO
[1] Database startup fails with errors :
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing.
REQUIREMENT
[1] The script which will recreate the objects in the datafile like script which will create indexex.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN (5 MIN+ TIME TAKEN TO CREATE INDEXES)
NON-ESSENTIAL DATAFILES
DATAFILE OF INDEX TABLESPACE, TEMPORARY TABLESPACE.
SOLUTION
Shutdown the database.(shutdown immediate).
Take complete backup of current database.
Startup mount
Query the v$recover_file view along with v$datafile with a join on file# and note down the name of file
say it is /prodebs/test/ind.dbf.
Alter database datafile ‘/prodebs/test/ind.dbf’ offline;
(if database is in noarchivwlog mode command will be
Alter database datafile ‘/prodebs/test/ind.dbf’ offline drop; )
Alter database open;
Drop tablespace user_index including contents;
Create tablespace user_index
datafile ‘/prodebs/test/ind.dbf’ size 1M;
Run the script which will built indexes*.
Shutdown the database and take backup if necessary.
Startup.
* NB : For temporary tablespace skip this step.
———————————————————————————————–
2. MISSING MIRRORED ONLINE REDO LOG FILES (DATABASE IS UP/DOWN)
SCENARIO
Database opens neatly but in alert log two error messages are logged with errors :
(error from lgwr. Error is also written in lgwr trace file)
ora 313 open failed for members ..
ora 312 name of redo log memeber missing
ora 7360 OS error
ora 321 Can not update logfile header
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN 5 MIN.
SOLUTION
Shutdown the database.(shutdown).
Startup mount
Query v$logfile view and find which member has become invalid.
Query v$log view and find which group is current and size of group members (say it is b).
If the member of current log group (say it is 1) is corrupted issue the following commands :
Alter system switch logfile
If you can add one more member to corrupted log group ie maximum log member is not reached add one more
member to that group
Alter database add logfile member ‘filespec’ to group 1;
Shutdown the database
Startup the database
If you can not add one more member to corrupted log group
create one more log group with equal members and size of non corrupted log group.
Alter database group 3 (‘filespec’,'filespec’) size b;
Drop corrupted log group.
alter database drop logfile group 1;
Manually remove other members of this corrupted log group (ie rm in unix)
Shut down the database
Startup the database
———————————————————————————————–
3. RECOVER A LOST DATAFILE WITH NO BACKUP AND ALL ARCHIVED
LOG FILES
SCENARIO
Database startup fails with errors :
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing.
REQUIREMENT
For full recovery, database should be in archivelog mode.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 10 mins.
SOLUTION
Shutdown the database.(shutdown).
Startup mount;
Query v$recover_file
Query v$datafile and find the name of datafile which is missing.(say it is ‘/prodebs/test/user_odc.dbf’)
Now issue the following commands in the given order :
alter database datafile ‘/prodebs/test/user_odc.dbf’ offline;
alter database create datafile ‘/prodebs/test/user_odc.dbf’ as ‘/prodebs/test/user_odc1.dbf’;
(removed file) (new file)
alter database datafile ‘/prodebs/test/user_odc1.dbf’ online;
alter database recover datafile ‘/prodebs/test/user_odc1.dbf’; or recover database
It will generally recover the database if you have all the archived file with you
alter database open;
Shutdown the database and take necessary backup if required.
Start the database.
———————————————————————————————–
4. RECOVER A LOST DATAFILE WITH BACKUP AND ALL ARCHIVED
LOG FILES
SCENARIO
Database startup fails with errors :
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing.
REQUIREMENT
For full recovery, database should be in archivelog mode.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN 5 mins.
SOLUTION
Shutdown the database.(shutdown).
Startup mount;
Query v$recover_file
Query v$datafile and find the name of datafile which is missing.(say it is ‘/prodebs/test/user_odc.dbf’)
Copy the archived datafile (old one that is in backup) and give following commands :
recover database;
alter database open;
Shutdown the database and take necessary backup if required.
Start the database.
———————————————————————————————–
5. RECOVER A LOST DATAFILE WITH BACKUP AND MISSING ARCHIVED
LOG FILES.
SCENARIO
Database startup fails with errors :
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing.
REQUIREMENT
For recovery, database should be in archivelog mode.
CONDITION
Recovery will be incomplete.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 10 mins.
SOLUTION
Shutdown the database.(shutdown).
Copy all your datafiles from backup except control file.
Startup mount;
Copy the archived datafile (old one that is in backup) and give following commands :
recover database until cancel
alter database open resetlogs;
Shutdown the database and take necessary backup if required.
Start the database.
———————————————————————————————–
6. LOSS OF DATAFILE WHEN THE DATABASE IS IN NOARCHIVELOG MODE WITH NO LOGICAL BACKUP AND RECOVERY.
SCENARIO
Database startup fails with errors : (a) on monday morning (b) on thursday
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
There is no export and import backup (Logical backup).
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Problem has occured on (a) on monday morning (b) on thursday.
Here data file (s) associated with user tablespace is (are) lost.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION when Problem has occured on (a) on monday morning
Shutdown the database.(shutdown).
Take complete backup of current database.
Delete all the database files, redo log files and control files.
Copy the all the database files , redo log files and control files from the backup.
Start the database. There is no data loss.
SOLUTION when Problem has occured on (a) on thursday morning. Here data loss will occur.
Shutdown the database.(shutdown).
Take complete backup of current database.
Delete all the database files, redo log files and control files.
Copy the all the database files , redo log files and control files from the backup.
Start the database. There is data loss for monday, tuesday and wednessday.
Ask user to reenter the data.
7. LOSS OF DATAFILE WHEN THE DATABASE IS IN NOARCHIVELOG MODE WITH LOGICAL BACKUP AND RECOVERY.
SCENARIO
Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
There is export and import backup. (Logical backup).
Strategy : So along with coldback up a complete database backup is also taken.
(i) After this on everyday, an incremental backup is also taken.
(ii) After this on everyday, complete database backup is also taken.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Here data file (s) associated with user tablespace is (are) lost.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
[i] SOLUTION when Problem has occured on thursday morning and incremental backup is taken.
Shutdown the database.(shutdown).
Take complete backup of current database.
Delete all the database files, redo log files and control files.
Copy the all the database files , redo log files and control files from the backup.
Start the database.
Apply incremental export using import file starting from monday to wednesday.There is no data loss.
[ii] SOLUTION when Problem has occured on thursday morning and daily complete database logical
backup is taken.
Shutdown the database.(shutdown).
Take complete backup of current database.
Delete all the database files, redo log files and control files.
Copy the all the database files , redo log files and control files from the backup.
Start the database.
Apply wednesday complete export backup. There is no data loss.
8. LOSS OF SYSTEM DATAFILE WHEN THE DATABASE IS IN ARCHIVE
LOG MODE AND RECOVERY.
SCENARIO
Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Here data file (s) associated with system tablespace is (are) lost.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the missing system database file(s).
Startup mount exclusive;
recover database;
alter database open;
Database is ready for use.
9. LOSS OF NON SYSTEM DATAFILE WITHOUT ROLLBACK SEGMENTS
WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.
SCENARIO
Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Here data file (s) associated with user tablespace is (are) lost.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION -1 (DATABASE RECOVERY)
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the missing database file(s).
Startup mount exclusive;
recover database;
alter database open;
Database is ready for use.
SOLUTION -2 (DATAFILE RECOVERY)*
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the missing database file(s).
Startup mount exclusive;
Alter database datafile ” offline;
Alter database open;
recover datafile ”
Alter database datafile ” online;
Database is ready for use.
* If multiple datafiles are lost use parallel recovery method (from muliple terminal use the same method for different files).
SOLUTION -3 (TABLESPACE RECOVERY)
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the missing database file(s).
Startup mount exclusive;
Alter database datafile ” offline;
Alter database open;
Alter tablespace offline temporary;
recover tablespace ;
Alter tablespace online ;
Database is ready for use.
10. LOSS OF NON SYSTEM DATAFILE WITH ROLLBACK SEGMENTS WHEN
THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.
SCENARIO
Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Datafile(s) associated with rollback segment tablespace is (are) lost.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION –
Shutdown the database.(shutdown).
Take complete backup of current database.
Comment the ROLLBACK_SEGMENT parameter or assign ROLLBACK_SEGMENT=(SYSTEM)
in init.ora file before startup.
Copy the missing rollback segment database file(s).
Startup mount exclusive;
alter database datafile ” offline;
alter database open;
Alter tablespace offline temporary;
recover tablespace ;
Alter tablespace online;
Query dba_rollback_segs (column name segment_name, status) and note down the name of segment
name having status recovery (say they are r01, r02,r03).
alter rollback segment r01 online;
alter rollback segment r02 online;
alter rollback segment r03 online;
shutdown the database.
Remove comment from rollback_segment parameter or remove system value and give the name of rollback
segments which you want to be online when database starts.
Start the database.
Database is ready for use.
11. LOSS OF UNARCHIVED ONLINE LOG FILES WHEN THEY ARE NOT
MIRRORED WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND
RECOVERY.
SCENARIO
Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
All the online redo log files are lost.
All the data files and current control files are intact.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION –
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the all the database files from latest offline or online backup.
Startup mount exclusive;
recover database until cancel;
alter database open resetlogs;
shutdown the database.
Take cold backup. It is strongely advised.
Start the database.
Database is ready for use.
12. DATABASE CRASH DURING HOT BACKUP WHEN THE DATABASE IS IN
ARCHIVE LOG MODE AND RECOVERY.
SCENARIO
While taking hot backup, database crashes.
(a) When Oracle Version is 7.2 or more.
(b) When Oracle Version is 7.1.
TIME TAKEN IN RECOVERY
[a] DATABASE WILL BE READY FOR USE IN MIN 2 mins.
[b] DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION – (Oracle Version is 7.2 or more)
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
Query the view v$backup and get file # having status active. Now from v$datafile get the name of
file which is active in v$backup
alter database datafile ” end backup;
alter database open ;
shutdown the database.
Start the database.
Database is ready for use.
SOLUTION – (Oracle Version is 7.1 )
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
recover database ;
( This may take significant amount of time if a large number of archived logs are to be applied)
alter database open ;
shutdown the database.
Start the database.
Database is ready for use.
13. LOSS OF CONTROL FILE AND WHEN THE DATABASE IS IN ARCHIVE
LOG MODE AND RECOVERY.
SCENARIO
[a] Loss of control file when it is mirrored.
[b] Loss of control file when there is a backup and it is not mirrored but not before last reset log option.
[c] Loss of control file when there is no backup and it is not mirrored (total loss).
NB : Loss of control file when there is a backup and it is not mirrored but it is before last reset log option.
(suppose database is open on day x with alter database startup resetlogs.So your control file should
be before xth day )
startup/recover database using backup controlfile
Oracle error no is ora 1190 :control file or data file 1 is from before the last RESETLOGS
ora 1110 :name of system datafile.
[b] Loss of control file when there is a backup and it is not mirrored but not before last reset log option.
(suppose database is open on day x with alter database startup resetlogs.So your control file should
be after xth day )
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: ‘/prodebs/test/sys_odc.dbf’
ORA-01207: file is more recent than control file – old control file
TIME TAKEN IN RECOVERY
[a] DATABASE WILL BE READY FOR USE IN MIN 2 mins.
[b] DATABASE WILL BE READY FOR USE IN MIN 30 mins.
[c] DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION – (Loss of control file when it is mirrored)
Shutdown the database.(shutdown).
Copy the second control file to this disk.
Rename this control file to the lost one.
Start the database.
Database is ready for use.
SOLUTION – (Loss of all the control file(s) )
Shutdown the database.(shutdown).
run backup of controlfile which you might have taken using the following command :
alter database backup controlfile to trace;
It creates a script. After editing that script it looks like this :
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “” NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 100
LOGFILE
GROUP 1 (
‘/prodebs/test/redo_odc11.dbf’,
‘/prodebs/test/redo_odc12.dbf’
) SIZE 50K,
GROUP 2 (
‘/prodebs/test/redo_odc21.dbf’,
‘/prodebs/test/redo_odc22.dbf’
) SIZE 50K
DATAFILE
‘/prodebs/test/sys_odc.dbf’,
‘/prodebs/test/sys_odc1.dbf’,
‘/prodebs/test/user_odc.dbf’,
‘/prodebs/test/temp_odc.dbf’,
‘/prodebs/test/rbs_odc.dbf’,
‘/prodebs/test/ind.dbf’
;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
shutdown the database.
Start the database.
Database is ready for use.
SOLUTION – (Loss of control file when there is a backup and it is not mirrored)
Shutdown the database.(shutdown).
Copy the old control file to this disk.
startup mount exclusive;
If you have any tablespace which is read only, take all the datafile offiline related to this tablespace.
recover database using backup controlfile;
Offline datafile should bring to online status. (alter database datafile ‘< name of datafile>’ online;)
(for read only tablespace)
alter database open resetlogs;
Shutdown the database.
Take cold backup. It is strongely advised.
Start the database.
Database is ready for use.
14. DATABASE SPACE MANAGEMENT WHEN THE DATABASE IS IN
ARCHIVE LOG MODE AND RECOVERY (RESIZING DATAFILE).
SCENARIO
Space management when :
(a) Oracle Version is 7.2 or more.
(b) Oracle Version is 7.1.
Oracle Error is : ora 00376 file # can not be read at this time.
ora 01110 name of datafile.
TIME TAKEN IN RECOVERY
[a] DATABASE WILL BE READY FOR USE IN MIN 5 mins.
[b] DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION – (Oracle Version is 7.2 or more)
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup open;
Query the view v$datafile get the name of file which you want to resize.
alter database datafile ” resize [m/k];
shutdown the database.
Take backup if necessary.
Start the database.
Database is ready for use.
SOLUTION – (Oracle Version is 7.1 )
[a] Restore the datafile and apply recovery. Resizing is not possible.
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the deleted datafile
Startup mount exclusive;
recover database ;
( This may take significant amount of time if a large number of archived logs are to be applied)
alter database open ;
shutdown the database.
Start the database.
Database is ready for use.
[b] If deleted datafile is not available in backup .
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
alter database add datafile ” as ”;
recover database ;
( This may take significant amount of time if a large number of archived logs are to be applied)
alter database open ;
shutdown the database.
Start the database.
Database is ready for use.
[c] Rebuild tablespace
Requirement : Logical backup is there.
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
alter database datafile ” offline;
alter database open;
alter tablespace offline;
drop tablespace ;
create tablespace datafile ” size [m/k];
alter tablespace online;
use export / import method to recover the loss data.
shutdown the database.
Take backup if necessary.
Start the database.
Database is ready for use.
15. RECOVERY THROUGH RESETLOGS .
CONDITIONS :
[A] When online redo logs files are deleted.
[B] Loss of all control files.
[C] When recovery is done through old control files.
Events
[1]
a- Cold backup is taken.
b- Loss of redo log file.and media recovery. At this moment, a backup is taken.
c- Loss of data file
[2]
a- Cold backup is taken.
b- Loss of redo log file.and media recovery. At this moment, a backup is not taken.
c- Loss of data file .
SOLUTION – [1]
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the most recent cold backup of datafiles.
Startup mount exclusive;
Recover database;
alter database open;
shutdown the database.
Take a cold backup.
Start the database.
Database is ready for use.
Advantage : All the data will be recovered.
SOLUTION – [2]-i
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
alter database datafile ” offline;
alter database open;
Export all the data from the tablespace (all objects in missing file will be inaccessible),
Drop and recreate the tablespace.
Import all the data taken from tablespace.
recover datafile ” ;
alter database open;
shutdown the database.
Take a cold backup.
Start the database.
Database is ready for use.
Disadvantage : All the data will be lost that was entered in datafile 5;
SOLUTION – [2]-ii
Shutdown the database.(shutdown).
Take complete backup of current database.
Copy the most recent cold backup of datafile and control file only. Do not copy redo log file.
Startup mount exclusive;
Recover database;
alter database open resetlogs;
shutdown the database.
Take a cold backup.
Start the database.
Database is ready for use.
Disadvantage : All the data will be lost that after event b.
16. LOSS OF DATA FILE WHEN THE DATABASE IS IN ARCHIVE LOG MODE AND RECOVERY.
SCENARIO
Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Datafile(s) associated with user tablespace is (are) permanently lost.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION –
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
alter database create datafile ” ;
recover datafile ” ;
alter database open;
shutdown the database.
Start the database.
Database is ready for use.
17. SYSTEM CLOCK CHANGE AND POINT-IN-TIME RECOVERY.
SCENARIO
Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Datafile(s) associated with user tablespace is (are) permanently lost.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION –
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
recover database until time ” ;
alter database open resetlogs;
shutdown the database.
Take a cold backup of database;
Start the database.
Database is ready for use.
18. OFFLINE TABLESPACES AND RECOVERY.
SCENARIO
Database startup fails with errors on thursday morning.
ora 1157 can not identify datafile
ora 1110 give the name of datafile which is missing and found to be user data file.
Cold backup is taken once in every week . Here backup is taken on every sunday and this is the last activity
on sunday.
Datafile(s) associated with user tablespace is (are) permanently lost.
TIME TAKEN IN RECOVERY
DATABASE WILL BE READY FOR USE IN MIN 30 mins.
SOLUTION – 1
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
recover database ;
alter database open ;
recover tablespace ;
alter tablespace online;
Shutdown the database.
Start the database.
Database is ready for use.
SOLUTION – 2 (better)
Shutdown the database.(shutdown).
Take complete backup of current database.
Startup mount exclusive;
Query the view v$datafile and note which datafile is offline say it is .
alter database datafile <’a'> online;
recover database;
alter database open ;
shutdown the database
Start the database.
Database is ready for use.
APPS DBA FAQS..
Q. What is difference between fresh database and vision database install types ?
Fresh Database – Database is installed with Apps but with no data
Vision Database – Database installed with Apps with dummy data
Q. Whatis US directory in $AD_TOP or under various product TOP’s .
US directory is default language directory in Oracle Applications. If you have multiple language Installed in your Applications then you will see other language directories beside US, that directory will contain reports, fmx and other code in that respective directory like FR for France, AR for arabic, simplifies chinese or spanish.
Q. Whats main concurrent Manager types.
ICM – Internal Concurrent Manager which manage concurrent Managers
Standard Managers – Which Manage processing of requests.
CRM – Conflict Resolution Managers , resolve conflicts in case of incompatibility.
Q. Where is Concurrent Manager log file location ?
By default standard location is $APPLCSF/$APPLLOG , in some cases it can go to $FND_TOP/log as well.
. List some ad utilities and their functions?
adadmin, adctrl, adpatch, adlicmgr, admrgpch, adsplice and adworker
Q. List out the modules related to oracle Apps DBA ?
FND – Application Object Library, AU – Application Utilities, AD – Application DBA
Q. How many concurrent mangers can be used ? what is the limit ?
There is no such documented limit on number of concurrent managers, as long as you have enough operating system resource (memory).
Q. How would you check if the tablespace is in backup mode ?
- Check in v$backup
SQL> SELECT d.tablespace_name, b.status FROM dba_data_files d, v$backup b
WHERE d.file_id = b.FILE# and b.STATUS = ‘ACTIVE’ ;
Q. What is “Custom TOP” in apps ?
- Custom top is similar to other tops under APPL_TOP but containing custom (client) specific functionality. Apps DBA need to define custom TOP and register all custom code under CUSTOM TOP
Q. if you find that under $FND_TOP/secure directory, there are many .dbc files, how would you know which is the correct one ?
– System picks up dbc file based on profile option value “Applications Database ID“
Q Where is applications start/stop scripts stored ?
Ans: $COMMON_TOP/admin/scripts/$CONTEXT_NAME
How to confirm if Report Server is Up & Running ?
Ans: Report Server is started by executable rwmts60 on concurrent manager Node & this file is under $ORACLE_HOME/bin .execute command on your server like
ps -ef | grep rwmts60
You should get output like
applmgr ……. rwmts60 name=REP60_VISION
where VISION is your Instance name.
Else you can submit a request like “Active Users” with display set to PDF, check output & log file to see if report server can display PDF files
How you will start Discoverer in Oracle Apps 11i ?
Ans: In order to start dicoverer you can use script addisctl.sh under $OAD_TOP/admin/scripts/$CONTEXT_NAME
or startall.sh under $ORACLE_HOME/discwb4/util (under Middle/Application Tier)
How to check number of forms users at any time ?
Ans: Forms Connections initiate f60webmx connections so you can use
ps -ef | grep f60webmx | wc –l
What is 0 & Y in FNDCPASS, FNDLOAD or WFLOAD ?
Ans: 0 & Y are flags for FND Executable like FNDCPASS & FNDLOAD where
0 is request id (request ID 0 is assigned to request ID’s which are not submitted via Submit Concurrent Request Form.
‘Y’ indicates the method of invocation. i.e. it is directly invoked from the command-line not from the Submit Request Form.
Q. What are .ldt & .lct files which you see in apps patch or with FNDLOAD ?
Ans: .ldt & .lct stands for Loader datafile & Loader configuration files, used frequently in migrating customization, profile options, configuration data, etc.. across Instances.
Q. What is dev60cgi & f60cgi ?
Ans: cgi stands for Common Gateway Interface and these are Script Alias in Oracle Apps used to access forms server . Usually Form Server access directly via http://hostname:port/dev60cgi/f60cgi
Q. How to compile an Oracle Reports file ?
Ans: Utility adrepgen is used to compile Reports. Synatx is given below
adrepgen userid=apps\ source = $PRODUCT_TOP\srw\filename.rdf dest=$PRODUCT_TOP\srw\filename.rdf stype=rdffile dtype=rdffile logfile=x.log overwrite=yes batch=yes dunit=character
Fresh Database – Database is installed with Apps but with no data
Vision Database – Database installed with Apps with dummy data
Q. Whatis US directory in $AD_TOP or under various product TOP’s .
US directory is default language directory in Oracle Applications. If you have multiple language Installed in your Applications then you will see other language directories beside US, that directory will contain reports, fmx and other code in that respective directory like FR for France, AR for arabic, simplifies chinese or spanish.
Q. Whats main concurrent Manager types.
ICM – Internal Concurrent Manager which manage concurrent Managers
Standard Managers – Which Manage processing of requests.
CRM – Conflict Resolution Managers , resolve conflicts in case of incompatibility.
Q. Where is Concurrent Manager log file location ?
By default standard location is $APPLCSF/$APPLLOG , in some cases it can go to $FND_TOP/log as well.
. List some ad utilities and their functions?
adadmin, adctrl, adpatch, adlicmgr, admrgpch, adsplice and adworker
Q. List out the modules related to oracle Apps DBA ?
FND – Application Object Library, AU – Application Utilities, AD – Application DBA
Q. How many concurrent mangers can be used ? what is the limit ?
There is no such documented limit on number of concurrent managers, as long as you have enough operating system resource (memory).
Q. How would you check if the tablespace is in backup mode ?
- Check in v$backup
SQL> SELECT d.tablespace_name, b.status FROM dba_data_files d, v$backup b
WHERE d.file_id = b.FILE# and b.STATUS = ‘ACTIVE’ ;
Q. What is “Custom TOP” in apps ?
- Custom top is similar to other tops under APPL_TOP but containing custom (client) specific functionality. Apps DBA need to define custom TOP and register all custom code under CUSTOM TOP
Q. if you find that under $FND_TOP/secure directory, there are many .dbc files, how would you know which is the correct one ?
– System picks up dbc file based on profile option value “Applications Database ID“
Q Where is applications start/stop scripts stored ?
Ans: $COMMON_TOP/admin/scripts/$CONTEXT_NAME
How to confirm if Report Server is Up & Running ?
Ans: Report Server is started by executable rwmts60 on concurrent manager Node & this file is under $ORACLE_HOME/bin .execute command on your server like
ps -ef | grep rwmts60
You should get output like
applmgr ……. rwmts60 name=REP60_VISION
where VISION is your Instance name.
Else you can submit a request like “Active Users” with display set to PDF, check output & log file to see if report server can display PDF files
How you will start Discoverer in Oracle Apps 11i ?
Ans: In order to start dicoverer you can use script addisctl.sh under $OAD_TOP/admin/scripts/$CONTEXT_NAME
or startall.sh under $ORACLE_HOME/discwb4/util (under Middle/Application Tier)
How to check number of forms users at any time ?
Ans: Forms Connections initiate f60webmx connections so you can use
ps -ef | grep f60webmx | wc –l
What is 0 & Y in FNDCPASS, FNDLOAD or WFLOAD ?
Ans: 0 & Y are flags for FND Executable like FNDCPASS & FNDLOAD where
0 is request id (request ID 0 is assigned to request ID’s which are not submitted via Submit Concurrent Request Form.
‘Y’ indicates the method of invocation. i.e. it is directly invoked from the command-line not from the Submit Request Form.
Q. What are .ldt & .lct files which you see in apps patch or with FNDLOAD ?
Ans: .ldt & .lct stands for Loader datafile & Loader configuration files, used frequently in migrating customization, profile options, configuration data, etc.. across Instances.
Q. What is dev60cgi & f60cgi ?
Ans: cgi stands for Common Gateway Interface and these are Script Alias in Oracle Apps used to access forms server . Usually Form Server access directly via http://hostname:port/dev60cgi/f60cgi
Q. How to compile an Oracle Reports file ?
Ans: Utility adrepgen is used to compile Reports. Synatx is given below
adrepgen userid=apps\ source = $PRODUCT_TOP\srw\filename.rdf dest=$PRODUCT_TOP\srw\filename.rdf stype=rdffile dtype=rdffile logfile=x.log overwrite=yes batch=yes dunit=character
ORACLE APPS DBA FAQS
1.How do u apply a patch in non-interactive mode ?
2.What are the various kinds of patches you have applied ?
3.What is the methodology you use for promoting the patches ?
4.Did u ever run into a situation where a u encountered a error during Maintenance Pack or Mini-pack ?
5.What are the various errors you encountered in oracle applications patching ? List atleast 3 scenarios ?
6.How do you skip a job in the patch ?
7.How do you restart a failed job ?
8.How do u regenerate a form using adadmin ?
9.What are the steps which needs to be done using adadmin when we add a new langugae ?
10.How do u re-create grants and synonyms using adadmin ?
11. Did u ever regenerated the jar files, if so how do u regenerate those ?
12. How do you create the dbc file, if it is corrupted ?
13. How do you re-create the identitydb.obj file ?
14. What is oajinit.exe ? How d you recompile/rebuild this ?
15.What is the ad command you use to vadlidate a oracle apps table definition ?
16.How do you add a new product which is not part of current release ?
17.How do relink a specific executable FNDLIBR ?
18.What is the location of ad utilities log files ?
19.Give the detail steps for cloning oracle applications using rapidclone ?
20.Give the detail steps for running the autoconfig ?
21.How do u rebuild an autoconfig XML file if it is corrupted ?
22.Which utilities/scripts are needed for merging APPL_TOPS during shared appl_top implementation ?
23. Did u ever used the autoinstall ad utility ? When did u you used it for what purpose ?
24. Give me the steps for upgrading apps database from 9.2.0.7 to 10.2.0.2 ?
25. Give me the steps for upgrading apps from 11.5.8 to 11.5.10.2 ?
26. Give me the high level steps for upgrading apps from 11.5.9 to R 12 ?
27. What change management tools you have used before ?
28. What are the advantages with third party tools such as ringmaster or kintana ?
29.What is maintenance mode ? How do u disable/enable this ?
30.How do u identify/copy missing files in oracle applications ?
UNIX
1. Give me the list of TOP 20 UNIX commands which you use every day?
2. Where do you find logfiles, outfiles, manager log files location.
3. Where do you find concurrent manager (ICM) startup script location.
4. What is environment setting $APPLCSF used for ?
5. Verify the location of the APPSORA.env
6. Verify the location of the APPS$SID_$HOSTNAME.xml file
7. Verify the startup/shutdown scripts location
8. Describe the 3 different methods which can be used for identifying the no of nodes/servers used ?
9. Where do u find startup/shutdown scripts for Concurrent, Forms, Web server ?
10. Where do u find db & listener startup scripts ?
11. Give the detail steps for starting and shutting down oracle applications ?
12. Give the various UNIX commands u use to perform the health-check of Apache, Forms, Concurrent ?
13. How do you check the version of a file from UNIX server ?
14. When the developer develops the below code objects where do you save those ? PLL,FMB,RDF,SQL,PKS,PKB,JSP,JAVA CLASS FILE,HTML FILE.
15. What is the difference between FMB and FMX , PLL AND PLX ?
16. What are the TOP 5 important directories in oracle apps environment ?
17. Where do u find the concurrent manager log file ?
18. What troubleshooting steps you use when you get a page not found error ?
19. Where do u find the XML configuration file ? What information it will contain ?
20. How do you identify all the important port numbers used in an oracle apps environment ?
21. What is RRA ? Why do u need it ? Tell me about your understanding of this process ?
22. What are the important environment files in Oracle Applications ?
23 Give the sequence of steps to start or shutdown the ebusiness suite?
24. How do you change the naming convention for conc req log and out files?
25. Where does the concurrent req log files and out files would be created ?
26. What is RRA listener? When does u need this listener?
27. How do you identify the conc mgr details of a conc request?
28. How do you generate a PLL file?
29. Provide me the syntax to generate the FMB file in oracle applications?
30. What is the location of the directory in which we can find CUSTOM.pll?
31. Where do I find all the FMB files in the server?
32. What the steps for changing the APPS Schema password?
33. What the steps for changing the GL Schema password?
34. How do you enable debug mode for apache server?
35. How do you enable debug mode for Jserv engine?
36. What is aoljtest.jsp? How does it helpful? When do you use it?
37. How do you identify if a patch is applied using SQL statement?
38. How do you troubleshoot the invalid objects issue?
39. How do you identify the version of the package FND_FILE?
40. How do you identify the Jserv port which is being used?
41. How do you check from client pc if you can communicate with port 80 ?
42. What is the syntax to verify if the port (for ex 8010) is in use?
43. What is the syntax to check if the forms server is up?
44. What is the syntax to check if the jserv engine is up?
45. How do you verify if the concurrent manager is up?
46. How do you verify if the RRA listener is up?
47. What is SID_hostname.xml ? Why does u need it?
48. What is SID_hostname.env? Where does u find this file?
49. How do you verify if autoconfig is enabled in APPS environment?
50. What the location of the jserv log files?
51. What is the location of the apache log files?
52. How do you ensure that customizations are not over written?
53. How do you run autoconfig? What is the syntax?
54. How do you compile a jsp page?
55. How do you compile a .java package to create .class files?
56. What is jar file?
57. What is AF_CLASSPATH? Why do you need this?
58. Where does oracle keep all the jar files and class files?
59. How do you identify the jinitiator class id and jinitiator version?
60. How do you identify the Perl version?
61. How do you check the forms patch set level and version?
62. How do you identify all the nodes of a multi-node environment?
63. What is adovars.env? adconfig.txt? topfile.txt?
64. What is the location of the dbc file?
67. Where do you track of the customization information so that patches will not overwrite customizations ?
68. Which method you use to preserve custom environment setups so autoconfig will not overwrite those ?
69. What is the syntax to generate a FMB and PLL file in UNIX enviroment ?
70. What is the syntax to generate a RDF file in UNIX enviroment ?
71. How do you load the workflow configuration information to the database ?
72. What is the syntax for FNDLOAD to transfer responsibilities from one instance to another instance ?
73. What is the syntax for FNDLOAD to transfer custom concurrent programs from one env to other ?
74. What is the syntax for FNDLOAD to transfer menus from one instance to another instance ?
75. What is the syntax for changing the FRONT-END USER password using FNDCPASS ?
sql
1. Develop a sql statement from apps schema verify if u can identify all scheduled concurrent request
2. Develope a sql statement error, terminated requests from both front-end and back-end sqlplus.
3. Develope a sql statement to identify the concurrent requests which took more than 30 minutes time.
4. Develope a sql statement to Identify the session id and os process id for a running concurrent request
5. Develope a sql statement to Identify how many concurrent requests are being run/per day.
6. Develope a sql statement to identify all the installed modules and their mini-pack level.
7. Develope a sql statement to list all the active users using "SQL Statement"
8. Develope a sql statement to list all custom responsibilities using "SQL Statement"
9. Develope a sql statement to list all the custom functions, forms, menus using "SQL statements"
10. Write a SQL to identify all the running concurrent requests?
11. Write a SQL to identify all the reqs which took more than 30 minutes?
12. Write a SQL to identify all the schedule jobs and cancel those?
13. Write a SQL to identify all the error concurrent requests?
14. Write a SQL to identify all the running concurrent requests?
15. Write a SQL to verify all the profile option values set for SYSADMIN user?
16. Write a SQL to identify all the customizations in oracle applications?
17. Write a SQL Query to check the status of the concurrent manager?
18. Write a SQL Identify the source code of a pl/sql package FND_FILE?
19. Run adutconf.sql and verify the output
20. What is cmclean.sql? When does u need to run it ? What precautions u need take when you run it?
21. What is the script you use to identify which modules are installed? and their mini-pack level ?
22. Tell me about the back-end database tables FND_USER, FND_CONCURRENT_QUEUES,FND_CONCURRENT_REQUESTS,FND_ORACLE_USERID,FND_APPLICATION_VL,FND_PRODUCT_INSTALLATIONS.
23. Write a SQL to identify the biggest FND table in the database?
24. Give me the list of TOP 20 database dynamic v$ views?
25. Give me the list of TOP 10 init database parameters?
26. What kind of database objects will get stored in base schema?
27. What kind of database objects will get stored in APPS schema?
28. Describe the importance of APPLSYSPUB,APPS,APPLSYS schemas?
29 Write a SQL to identify the node names for the forms, concurrent, db, web tiers"?
30. Write a SQL to identify all the oracle patch jobs which were skipped?
31. Write a SQL to identify all the pending workflow notifications?
32. Write a SQL to identify the active forms sessions ?
33. Write a SQL to list the ORCLE APPLICATIONS FRONT-END USER NAME based on the database session ID ?
34. Write a SQL to list the ORCLE APPLICATIONS FRONT-END USER NAME based on the OS process id ?
35. Write a SQL to list the ORCLE APPLICATIONS FRONT-END USER NAME based on the concurrent request?
35. Write a SQL to list the standard manager concurrent request queue hourly workload for the last one week ?
36. Write a SQL to identify all the inactive users in the system ?
34. Write a SQL to identify all the active users who didnt log into oracle applications for the last 30 days ?
35. Write a SQL to identify all the patches applied in the last 30 days ?
SYSADMIN
1. What is the typical WEB url for Oracle Applications Front-End Access ?
2. What are the different kinds of user interfaces exist for Oracle Applications ?
3. Explain me about the login process for Oracle Applications ?
4. Describe Java Applet, JRE, Oracle JRE, Jinitator, Jar Files , ?
5. How do u enable the java console on client jinitiator ?
6. What is Oracle Application Profiles ? Tell me about Key SYSADMIN profiles which you are aware of ?
7. What are the various levels in which we can set the profile ?
8. Describe the difference between Key-Flexfield an Descriptive Flexfield ?
9. Describe the LIST-ITEM, LOV, Multi-Record Block, Single Record Block ?
10. How do you query the existing records from front-end Form ?
11. What are the famous key board function keys which you widely used in your experience ?
12. How do you check the record history ? Who created the record who updated the record ?
13. Create New User "TEST123" with Password "WELCOME123"
14. This user account should be active for 180 days only.
15. Password Expiration should be set for every 90 days
16. Assign the responsibility "Payables Manager".
17. Assign the responsibility "Workflow Web Administrator".
18. Login as user "TEST123".
19. Query the user "TEST 123.
20. Assign the responsibility "Receivables Manager"
21. Query the responsibility "Receivables Manager"
22. Using function security, disable access to Bank Accounts Form in the receivables manager
23. Using menu security, disable access to "Setup Menu"
24. Create a custom menu "XXAP Clerk" which should be a copy of "AP_INVOICES_GUI12"
25. Add function "AP Accrual Write-Offs GUI" to the above menu.
26. Create a request group "XXAP_CUSTOM_REQGRP"
27. Add " Active Users" concurrent programs to request group "XXAP_CUSTOM_REQGRP"
28. Add a request set to the request group "XXAP_CUSTOM_REQGRP"
29. Add all payables modules reports to request group "XXAP_CUSTOM_REQGRP"
30.Add "Gather Schema Stats" concurrent programs to request group "XXAP_CUSTOM_REQGRP"
31. Grant "Order Management" Reports access to the above request group"
32. Create a new responsibility "XXAP Clerk"
33. Assign "XXAP Clerk" Menu, "XXAP_CUSTOM_REGGRP", Standard Datagroup
34. Assign this responsibility to the "TEST123" user
35. Login as TEST123 user and navigate to above responsibility
36. Verify the "Active Users" Report by running it.Check the output and logfile.
37. Schedule "Purge Concurrent Request" program to run every day at 11pm to purge requests ?
38. Verify the Notify feature to see if u can get an email after the concurrent request completes.
39. Query all the concurrent request which are submitted by user "VIJAY" using System Administrator ?
40. Using Find Request window, verify all scheduled jobs
41. Run a concurrent program "Gather Schema Stats" for all schemas with 30% estimate.
42. Enable auditing for "AP_INVOICES_ALL.INVOICE_AMOUNT" using "AUDIT TRAIL" screens.
43. Using syadmin Identify all the users who are using "System Administrator" Responsibility.
44. Develop a sql script as concurrent program and tests this as sysadmin from "Payables Manager"
45. Develop a custom form test this as sysadmin from "Payables Manager" Responsibility
46. Query the purchase order "1111" and check the back-end table.
47. Verify the cache size of the "Standard Manager"
48. Downsize the "Standard Manager" Processes from seeded value to "2"
49. Create and Assign a new work shift during which 40 processes should be activated for standard manager.
50. Create specialization rules to exclude conc prog submitted by vijay from "STANDARD MANAGER"
51. Create a custom "Batch Standard Manager" and setup a rule to include only programs run by VIJAY.
52. Login to "System Administrator". Identify APPL_TOP location
53. Login to "OAM" identify the node names for the forms, concurrent, db, web tiers"
54. Verify if workflow background process request is scheduled.
55. Using OAM Verify the workflow mailer IMAP & SMTP settings. Try to start the workflow Mailer.
56. How do you check workflow status for all errored out purchase orders & Retry them
57. Using Workflow Administrator Responsibility Change the workflow administrator role
58. Using OAM Verify the module install status for module "Advanced Pricing". Install it using "OAM"
59. Verify if a patch has been applied using "OAM"
60. How do you enable the trace for a front-end forms session?
61. How do you enable the trace for a concurrent program?
62. How do you enable the trace for a running concurrent program
63. How do you troubleshoot a long running request?
64. How do you identify all the attached PLLS for a FMB file?
65. Submit a request ? Check the output ? Get the output file from server to your laptop using ftp command
66. Schedule a concurrent request to run every day at 1AM ?
67. Turn on the 11046 level 12 trace for a specific user session
68. Try to enable the trace from front-end for "Define Users Form"
69. Identify the back-end database session for a front-end apps-session and enable the trace.
70. Enable the trace and run a concurrent program in trace mode.
71. Identify the server side os process for the standard manager concurrent manager using front-end app.
72. Shutdown standard manager using sysadmin resp. Try to start it using SYSADMIN responsibility.
73. How do u identify the OS process id for a Given concurrent manager ?
74. How do u identify the OS process id for a Given concurrent request ?
75. Tell about the profile option names which will have values about forms server, discoverer, jsp, help ?
76. Tell be about various phases and statuses of a concurrent request ?
77. Tell me about ICM,CRM,STANDARD & Transaction managers ?
78. What is workshift ? How does it help tunning the concurrent manager workload ?
79. How do you setup the PCP in oracle applications ?
80. Which profile can be used to enable the trace at session level ?
Features/Release Content Documents
Note 210326.1, Oracle Applications DBA 11i+ Features Matrix
Note 163400.1, Release Content/New Features Documents for Patch Sets, Family Packs & New Releases
Note 289972.1 , 11.5.10 Release Information
Note 294932.1, Recommendations & Release Notes to Install Oracle Applications 11i
Cloning/Autoconfig/Shared APPL_TOP
Note 165195.1, Using AutoConfig with Oracle Applications 11i
Note 218089.1, Frequently Asked Questions About Using AutoConfig With Oracle Applications Release 11i
Note 217368.1, Advanced Configurations and Topologies for Enterprise Deployments of E-Business Suite 11i
Note 230672.1, Cloning Oracle Applications Release 11i with Rapid Clone
Note 216664.1, Frequently Asked Questions About Cloning Oracle Applications 11i
Note 18070.1, How to Make a Copy of a Database on the Same UNIX Machine
Note 233428.1, Sharing the Application Tier File System in Oracle E-Business Suite11i
Patching
Note 181665.1, Release 11i Adpatch Basics
Note 457566.1, Oracle Applications Patching FAQ for Release 11i
Note 60766.1, Patch Installation Frequently Asked Questions
Note 224875.1, Installation, Patching & Upgrade Frequently Asked Questions (FAQ's)
Note 308916.1, How To Install Interim Patches with Opatch
Note 242480.1, Using a Staged Applications System to Reduce Patching Downtime
Note 225165.1, Oracle Applications 11i Patching Best Practices and Reducing Downtime
Note 259484.1, Release 11.5.10 Maintenance Pack Installation Instructions
Installations & Upgrades
Note 240744.1, Technology Validation Utility for Oracle Applications Release 11.5.9
Note 403311.1, Daylight Saving Time (DST) Compliance for Oracle E-Business Suite
Note 458452.1, Complying with DST and Time Zone Rule Changes in E-Business Suite 11i
Note 465915.1, Using Oracle VM with Oracle E-Business Suite Release 11i or Release 12
Note 216550.1, Interoperability Notes Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
Note 282038.1, Interoperability Notes Oracle Applications Release 11i with Oracle Database10.1.0
Note 362202.1, Interoperability Notes Oracle Applications Release 11i with Oracle Database 10g (10.1.0.4)
Note 362203.1, Interoperability Notes Oracle Applications Release 11i with Oracle 10g Release 2 10.2.0)
Note 452783.1, Interoperability Notes - Oracle E-Business Suite Release 11i with Oracle Database 11.1.0
Note 125767.1, Upgrading Developer 6i with Oracle Applications 11i
Note 164317.1, Upgrading Oracle JDBC Drivers with Oracle E-Business Suite 11i
Note 124606.1, Upgrading Oracle JInitiator with Oracle Applications 11i
Note 201340.1, Using Forms Listener Servlet with Oracle Applications 11i
Note 181244.1, Configuring an X Display Server for Applications on Unix Platforms
Note 146468.1, Installing Oracle9i Application Server with Oracle Applications 11i
Troubleshooting Oracle Applications
Note 295606.1, 10gAS with Oracle E-Business Suite Release 11i Troubleshooting Guide
Note 342332.1, Troubleshooting Login Problems in Oracle Applications 11i (11.5.2 – 11.5.10+)
Note 169935.1, Troubleshooting Oracle Applications Performance Issues
Note 380487.1, Oracle Application Server 10g with Release 12 Troubleshooting Guide
Note 226265.1, Troubleshooting Guide for AutoAccounting
Note 365529.1, Troubleshooting FRM-92XXX Errors in Oracle Applications
Note 139863.1, Troubleshooting Self Service Framework with Oracle Applications 11i
Note 216208.1, Oracle9i Application Server (9iAS) with Oracle E-Business Suite Release 11i Troubleshooting
Application Server/Java
Note 300482.1, Overview of Using Java with Oracle E-Business Suite Release 11i
Note 285218.1, Recommended Browsers for Oracle Applications 11i
Note 290807.1, Deploying Sun JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite 11i
Database and High Availability/RAC & ASM
Note 362135.1, Configuring Oracle E-Business Suite Release 11i with 10g Release2 RAC and ASM
Note 312731.1, Configuring Oracle Applications Release 11i with 10g Real Application Clusters and 10g ASM
Note 279956.1, Oracle E-Business Suite Release 11i with 9i RAC
Note 351646.1, Configuring Release 11i with 10g Database on Raw Devices
Note 341437.1, Business Continuity for Oracle Applications Release 11i Using RAC & Data Guard
Note 294652.1, Configuration Oracle E-Business Suite 11i RAC Environments (Load Balancing )
Application Server/Portal/Web Cache
Note 186981.1, Oracle Application Server with Oracle E-Business Suite Release 11i FAQ
Note 223927.1, Oracle Application Server Integration with Oracle E-Business Suite: Statement of Direction
Note 254618.1, HTTP Server Patches Certified to be used with E-Business Suite 11i External Integrations
Note 233436.1, Installing Oracle Application Server 10g with Oracle E-Business Suite Release 11i
Note 305918.1, Using Oracle Portal 10g with Oracle E-Business Suite 11i
Note 306653.1, Installing and Configuring 10gAS Web Cache with Oracle E-Business Suite 11i
Note 146469.1, Configuring Oracle Applications with Oracle Portal
Security/Encryption/SSL/OID/DMZ/SSO
Note 189367.1, Best Practices for Securing Oracle E-Business Suite
Note 287176.1, Oracle E-Business Suite 11i Configuration in a DMZ
Note 123718.1, A Guide to Understanding and Implementing SSL with Oracle Applications 11i
Note 340178.1, Enabling SSL with Oracle Application Server 10g and the E-Business Suite
Note 234599.1, Enabling Oracle Label Security in Oracle E-Business Suite
Note 391248.1, Encrypting EBS 11i Network Traffic using Advanced Security Option / Advanced Networking
Note 403294.1, Using Transparent Data Encryption with the E-Business Suite
Note 428503.1, Integrating Oracle E-Business Suite Release 11i with Oracle Database Vault 10.2.0.
Note 150832.1, Implementing Oracle Applications Synchronization with LDAP
Note 261914.1, Integrating Oracle E-Business Suite Release 11i with OID and Oracle Single Sign-On
Database
Note 285267.1, Oracle E-Business Suite Release 11i and Database FAQ
Note 216205.1, Database Initialization Parameters and Configuration for Oracle Applications 11i
Note 343917.1, Frequently Asked Questions: Using Oracle Applications with an x86-64 Database Server",
Note 311717.1, Frequently Asked Questions: Using Oracle Applications with an Itanium Database Server
Note 332977.1, Using Oracle Applications with an HP OpenVMS Alpha Database Server
Export & Import
Note 204015.1, Export/Import Process for Release 11i Database Instances Using Oracle8i Enterprise Edition
Note 230627.1, Export/Import Process for Release 11i Database Instances Using Oracle9i Enterprise Edition
Note 331221.1, Export/Import Process for Release 11i Database Instances Using Oracle10g Enterprise Edition
Note 362205.1, Export/Import Process for Release 11i Database Instances Using Oracle Database 10g R2
Split Configurations
Note 304489.1, Using Oracle Applications with a Split Configuration Database Tier on Oracle9i Release 2
Note 356839.1, Using Oracle Applications with a Split Configuration Database Tier on Oracle 10g Release 1
Note 369693.1, Using Oracle Applications with a Split Configuration Database Tier on Oracle 10g Release 2
Discoverer
Note 257798.1, Using Discoverer 10g (9.0.4) with Oracle Applications 11i,
Note 139516.1, Discoverer 4i with Oracle Applications 11i,
Note 313418.1, Using Discoverer 10g with Oracle Applications 11i
2.What are the various kinds of patches you have applied ?
3.What is the methodology you use for promoting the patches ?
4.Did u ever run into a situation where a u encountered a error during Maintenance Pack or Mini-pack ?
5.What are the various errors you encountered in oracle applications patching ? List atleast 3 scenarios ?
6.How do you skip a job in the patch ?
7.How do you restart a failed job ?
8.How do u regenerate a form using adadmin ?
9.What are the steps which needs to be done using adadmin when we add a new langugae ?
10.How do u re-create grants and synonyms using adadmin ?
11. Did u ever regenerated the jar files, if so how do u regenerate those ?
12. How do you create the dbc file, if it is corrupted ?
13. How do you re-create the identitydb.obj file ?
14. What is oajinit.exe ? How d you recompile/rebuild this ?
15.What is the ad command you use to vadlidate a oracle apps table definition ?
16.How do you add a new product which is not part of current release ?
17.How do relink a specific executable FNDLIBR ?
18.What is the location of ad utilities log files ?
19.Give the detail steps for cloning oracle applications using rapidclone ?
20.Give the detail steps for running the autoconfig ?
21.How do u rebuild an autoconfig XML file if it is corrupted ?
22.Which utilities/scripts are needed for merging APPL_TOPS during shared appl_top implementation ?
23. Did u ever used the autoinstall ad utility ? When did u you used it for what purpose ?
24. Give me the steps for upgrading apps database from 9.2.0.7 to 10.2.0.2 ?
25. Give me the steps for upgrading apps from 11.5.8 to 11.5.10.2 ?
26. Give me the high level steps for upgrading apps from 11.5.9 to R 12 ?
27. What change management tools you have used before ?
28. What are the advantages with third party tools such as ringmaster or kintana ?
29.What is maintenance mode ? How do u disable/enable this ?
30.How do u identify/copy missing files in oracle applications ?
UNIX
1. Give me the list of TOP 20 UNIX commands which you use every day?
2. Where do you find logfiles, outfiles, manager log files location.
3. Where do you find concurrent manager (ICM) startup script location.
4. What is environment setting $APPLCSF used for ?
5. Verify the location of the APPSORA.env
6. Verify the location of the APPS$SID_$HOSTNAME.xml file
7. Verify the startup/shutdown scripts location
8. Describe the 3 different methods which can be used for identifying the no of nodes/servers used ?
9. Where do u find startup/shutdown scripts for Concurrent, Forms, Web server ?
10. Where do u find db & listener startup scripts ?
11. Give the detail steps for starting and shutting down oracle applications ?
12. Give the various UNIX commands u use to perform the health-check of Apache, Forms, Concurrent ?
13. How do you check the version of a file from UNIX server ?
14. When the developer develops the below code objects where do you save those ? PLL,FMB,RDF,SQL,PKS,PKB,JSP,JAVA CLASS FILE,HTML FILE.
15. What is the difference between FMB and FMX , PLL AND PLX ?
16. What are the TOP 5 important directories in oracle apps environment ?
17. Where do u find the concurrent manager log file ?
18. What troubleshooting steps you use when you get a page not found error ?
19. Where do u find the XML configuration file ? What information it will contain ?
20. How do you identify all the important port numbers used in an oracle apps environment ?
21. What is RRA ? Why do u need it ? Tell me about your understanding of this process ?
22. What are the important environment files in Oracle Applications ?
23 Give the sequence of steps to start or shutdown the ebusiness suite?
24. How do you change the naming convention for conc req log and out files?
25. Where does the concurrent req log files and out files would be created ?
26. What is RRA listener? When does u need this listener?
27. How do you identify the conc mgr details of a conc request?
28. How do you generate a PLL file?
29. Provide me the syntax to generate the FMB file in oracle applications?
30. What is the location of the directory in which we can find CUSTOM.pll?
31. Where do I find all the FMB files in the server?
32. What the steps for changing the APPS Schema password?
33. What the steps for changing the GL Schema password?
34. How do you enable debug mode for apache server?
35. How do you enable debug mode for Jserv engine?
36. What is aoljtest.jsp? How does it helpful? When do you use it?
37. How do you identify if a patch is applied using SQL statement?
38. How do you troubleshoot the invalid objects issue?
39. How do you identify the version of the package FND_FILE?
40. How do you identify the Jserv port which is being used?
41. How do you check from client pc if you can communicate with port 80 ?
42. What is the syntax to verify if the port (for ex 8010) is in use?
43. What is the syntax to check if the forms server is up?
44. What is the syntax to check if the jserv engine is up?
45. How do you verify if the concurrent manager is up?
46. How do you verify if the RRA listener is up?
47. What is SID_hostname.xml ? Why does u need it?
48. What is SID_hostname.env? Where does u find this file?
49. How do you verify if autoconfig is enabled in APPS environment?
50. What the location of the jserv log files?
51. What is the location of the apache log files?
52. How do you ensure that customizations are not over written?
53. How do you run autoconfig? What is the syntax?
54. How do you compile a jsp page?
55. How do you compile a .java package to create .class files?
56. What is jar file?
57. What is AF_CLASSPATH? Why do you need this?
58. Where does oracle keep all the jar files and class files?
59. How do you identify the jinitiator class id and jinitiator version?
60. How do you identify the Perl version?
61. How do you check the forms patch set level and version?
62. How do you identify all the nodes of a multi-node environment?
63. What is adovars.env? adconfig.txt? topfile.txt?
64. What is the location of the dbc file?
67. Where do you track of the customization information so that patches will not overwrite customizations ?
68. Which method you use to preserve custom environment setups so autoconfig will not overwrite those ?
69. What is the syntax to generate a FMB and PLL file in UNIX enviroment ?
70. What is the syntax to generate a RDF file in UNIX enviroment ?
71. How do you load the workflow configuration information to the database ?
72. What is the syntax for FNDLOAD to transfer responsibilities from one instance to another instance ?
73. What is the syntax for FNDLOAD to transfer custom concurrent programs from one env to other ?
74. What is the syntax for FNDLOAD to transfer menus from one instance to another instance ?
75. What is the syntax for changing the FRONT-END USER password using FNDCPASS ?
sql
1. Develop a sql statement from apps schema verify if u can identify all scheduled concurrent request
2. Develope a sql statement error, terminated requests from both front-end and back-end sqlplus.
3. Develope a sql statement to identify the concurrent requests which took more than 30 minutes time.
4. Develope a sql statement to Identify the session id and os process id for a running concurrent request
5. Develope a sql statement to Identify how many concurrent requests are being run/per day.
6. Develope a sql statement to identify all the installed modules and their mini-pack level.
7. Develope a sql statement to list all the active users using "SQL Statement"
8. Develope a sql statement to list all custom responsibilities using "SQL Statement"
9. Develope a sql statement to list all the custom functions, forms, menus using "SQL statements"
10. Write a SQL to identify all the running concurrent requests?
11. Write a SQL to identify all the reqs which took more than 30 minutes?
12. Write a SQL to identify all the schedule jobs and cancel those?
13. Write a SQL to identify all the error concurrent requests?
14. Write a SQL to identify all the running concurrent requests?
15. Write a SQL to verify all the profile option values set for SYSADMIN user?
16. Write a SQL to identify all the customizations in oracle applications?
17. Write a SQL Query to check the status of the concurrent manager?
18. Write a SQL Identify the source code of a pl/sql package FND_FILE?
19. Run adutconf.sql and verify the output
20. What is cmclean.sql? When does u need to run it ? What precautions u need take when you run it?
21. What is the script you use to identify which modules are installed? and their mini-pack level ?
22. Tell me about the back-end database tables FND_USER, FND_CONCURRENT_QUEUES,FND_CONCURRENT_REQUESTS,FND_ORACLE_USERID,FND_APPLICATION_VL,FND_PRODUCT_INSTALLATIONS.
23. Write a SQL to identify the biggest FND table in the database?
24. Give me the list of TOP 20 database dynamic v$ views?
25. Give me the list of TOP 10 init database parameters?
26. What kind of database objects will get stored in base schema?
27. What kind of database objects will get stored in APPS schema?
28. Describe the importance of APPLSYSPUB,APPS,APPLSYS schemas?
29 Write a SQL to identify the node names for the forms, concurrent, db, web tiers"?
30. Write a SQL to identify all the oracle patch jobs which were skipped?
31. Write a SQL to identify all the pending workflow notifications?
32. Write a SQL to identify the active forms sessions ?
33. Write a SQL to list the ORCLE APPLICATIONS FRONT-END USER NAME based on the database session ID ?
34. Write a SQL to list the ORCLE APPLICATIONS FRONT-END USER NAME based on the OS process id ?
35. Write a SQL to list the ORCLE APPLICATIONS FRONT-END USER NAME based on the concurrent request?
35. Write a SQL to list the standard manager concurrent request queue hourly workload for the last one week ?
36. Write a SQL to identify all the inactive users in the system ?
34. Write a SQL to identify all the active users who didnt log into oracle applications for the last 30 days ?
35. Write a SQL to identify all the patches applied in the last 30 days ?
SYSADMIN
1. What is the typical WEB url for Oracle Applications Front-End Access ?
2. What are the different kinds of user interfaces exist for Oracle Applications ?
3. Explain me about the login process for Oracle Applications ?
4. Describe Java Applet, JRE, Oracle JRE, Jinitator, Jar Files , ?
5. How do u enable the java console on client jinitiator ?
6. What is Oracle Application Profiles ? Tell me about Key SYSADMIN profiles which you are aware of ?
7. What are the various levels in which we can set the profile ?
8. Describe the difference between Key-Flexfield an Descriptive Flexfield ?
9. Describe the LIST-ITEM, LOV, Multi-Record Block, Single Record Block ?
10. How do you query the existing records from front-end Form ?
11. What are the famous key board function keys which you widely used in your experience ?
12. How do you check the record history ? Who created the record who updated the record ?
13. Create New User "TEST123" with Password "WELCOME123"
14. This user account should be active for 180 days only.
15. Password Expiration should be set for every 90 days
16. Assign the responsibility "Payables Manager".
17. Assign the responsibility "Workflow Web Administrator".
18. Login as user "TEST123".
19. Query the user "TEST 123.
20. Assign the responsibility "Receivables Manager"
21. Query the responsibility "Receivables Manager"
22. Using function security, disable access to Bank Accounts Form in the receivables manager
23. Using menu security, disable access to "Setup Menu"
24. Create a custom menu "XXAP Clerk" which should be a copy of "AP_INVOICES_GUI12"
25. Add function "AP Accrual Write-Offs GUI" to the above menu.
26. Create a request group "XXAP_CUSTOM_REQGRP"
27. Add " Active Users" concurrent programs to request group "XXAP_CUSTOM_REQGRP"
28. Add a request set to the request group "XXAP_CUSTOM_REQGRP"
29. Add all payables modules reports to request group "XXAP_CUSTOM_REQGRP"
30.Add "Gather Schema Stats" concurrent programs to request group "XXAP_CUSTOM_REQGRP"
31. Grant "Order Management" Reports access to the above request group"
32. Create a new responsibility "XXAP Clerk"
33. Assign "XXAP Clerk" Menu, "XXAP_CUSTOM_REGGRP", Standard Datagroup
34. Assign this responsibility to the "TEST123" user
35. Login as TEST123 user and navigate to above responsibility
36. Verify the "Active Users" Report by running it.Check the output and logfile.
37. Schedule "Purge Concurrent Request" program to run every day at 11pm to purge requests ?
38. Verify the Notify feature to see if u can get an email after the concurrent request completes.
39. Query all the concurrent request which are submitted by user "VIJAY" using System Administrator ?
40. Using Find Request window, verify all scheduled jobs
41. Run a concurrent program "Gather Schema Stats" for all schemas with 30% estimate.
42. Enable auditing for "AP_INVOICES_ALL.INVOICE_AMOUNT" using "AUDIT TRAIL" screens.
43. Using syadmin Identify all the users who are using "System Administrator" Responsibility.
44. Develop a sql script as concurrent program and tests this as sysadmin from "Payables Manager"
45. Develop a custom form test this as sysadmin from "Payables Manager" Responsibility
46. Query the purchase order "1111" and check the back-end table.
47. Verify the cache size of the "Standard Manager"
48. Downsize the "Standard Manager" Processes from seeded value to "2"
49. Create and Assign a new work shift during which 40 processes should be activated for standard manager.
50. Create specialization rules to exclude conc prog submitted by vijay from "STANDARD MANAGER"
51. Create a custom "Batch Standard Manager" and setup a rule to include only programs run by VIJAY.
52. Login to "System Administrator". Identify APPL_TOP location
53. Login to "OAM" identify the node names for the forms, concurrent, db, web tiers"
54. Verify if workflow background process request is scheduled.
55. Using OAM Verify the workflow mailer IMAP & SMTP settings. Try to start the workflow Mailer.
56. How do you check workflow status for all errored out purchase orders & Retry them
57. Using Workflow Administrator Responsibility Change the workflow administrator role
58. Using OAM Verify the module install status for module "Advanced Pricing". Install it using "OAM"
59. Verify if a patch has been applied using "OAM"
60. How do you enable the trace for a front-end forms session?
61. How do you enable the trace for a concurrent program?
62. How do you enable the trace for a running concurrent program
63. How do you troubleshoot a long running request?
64. How do you identify all the attached PLLS for a FMB file?
65. Submit a request ? Check the output ? Get the output file from server to your laptop using ftp command
66. Schedule a concurrent request to run every day at 1AM ?
67. Turn on the 11046 level 12 trace for a specific user session
68. Try to enable the trace from front-end for "Define Users Form"
69. Identify the back-end database session for a front-end apps-session and enable the trace.
70. Enable the trace and run a concurrent program in trace mode.
71. Identify the server side os process for the standard manager concurrent manager using front-end app.
72. Shutdown standard manager using sysadmin resp. Try to start it using SYSADMIN responsibility.
73. How do u identify the OS process id for a Given concurrent manager ?
74. How do u identify the OS process id for a Given concurrent request ?
75. Tell about the profile option names which will have values about forms server, discoverer, jsp, help ?
76. Tell be about various phases and statuses of a concurrent request ?
77. Tell me about ICM,CRM,STANDARD & Transaction managers ?
78. What is workshift ? How does it help tunning the concurrent manager workload ?
79. How do you setup the PCP in oracle applications ?
80. Which profile can be used to enable the trace at session level ?
Features/Release Content Documents
Note 210326.1, Oracle Applications DBA 11i+ Features Matrix
Note 163400.1, Release Content/New Features Documents for Patch Sets, Family Packs & New Releases
Note 289972.1 , 11.5.10 Release Information
Note 294932.1, Recommendations & Release Notes to Install Oracle Applications 11i
Cloning/Autoconfig/Shared APPL_TOP
Note 165195.1, Using AutoConfig with Oracle Applications 11i
Note 218089.1, Frequently Asked Questions About Using AutoConfig With Oracle Applications Release 11i
Note 217368.1, Advanced Configurations and Topologies for Enterprise Deployments of E-Business Suite 11i
Note 230672.1, Cloning Oracle Applications Release 11i with Rapid Clone
Note 216664.1, Frequently Asked Questions About Cloning Oracle Applications 11i
Note 18070.1, How to Make a Copy of a Database on the Same UNIX Machine
Note 233428.1, Sharing the Application Tier File System in Oracle E-Business Suite11i
Patching
Note 181665.1, Release 11i Adpatch Basics
Note 457566.1, Oracle Applications Patching FAQ for Release 11i
Note 60766.1, Patch Installation Frequently Asked Questions
Note 224875.1, Installation, Patching & Upgrade Frequently Asked Questions (FAQ's)
Note 308916.1, How To Install Interim Patches with Opatch
Note 242480.1, Using a Staged Applications System to Reduce Patching Downtime
Note 225165.1, Oracle Applications 11i Patching Best Practices and Reducing Downtime
Note 259484.1, Release 11.5.10 Maintenance Pack Installation Instructions
Installations & Upgrades
Note 240744.1, Technology Validation Utility for Oracle Applications Release 11.5.9
Note 403311.1, Daylight Saving Time (DST) Compliance for Oracle E-Business Suite
Note 458452.1, Complying with DST and Time Zone Rule Changes in E-Business Suite 11i
Note 465915.1, Using Oracle VM with Oracle E-Business Suite Release 11i or Release 12
Note 216550.1, Interoperability Notes Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
Note 282038.1, Interoperability Notes Oracle Applications Release 11i with Oracle Database10.1.0
Note 362202.1, Interoperability Notes Oracle Applications Release 11i with Oracle Database 10g (10.1.0.4)
Note 362203.1, Interoperability Notes Oracle Applications Release 11i with Oracle 10g Release 2 10.2.0)
Note 452783.1, Interoperability Notes - Oracle E-Business Suite Release 11i with Oracle Database 11.1.0
Note 125767.1, Upgrading Developer 6i with Oracle Applications 11i
Note 164317.1, Upgrading Oracle JDBC Drivers with Oracle E-Business Suite 11i
Note 124606.1, Upgrading Oracle JInitiator with Oracle Applications 11i
Note 201340.1, Using Forms Listener Servlet with Oracle Applications 11i
Note 181244.1, Configuring an X Display Server for Applications on Unix Platforms
Note 146468.1, Installing Oracle9i Application Server with Oracle Applications 11i
Troubleshooting Oracle Applications
Note 295606.1, 10gAS with Oracle E-Business Suite Release 11i Troubleshooting Guide
Note 342332.1, Troubleshooting Login Problems in Oracle Applications 11i (11.5.2 – 11.5.10+)
Note 169935.1, Troubleshooting Oracle Applications Performance Issues
Note 380487.1, Oracle Application Server 10g with Release 12 Troubleshooting Guide
Note 226265.1, Troubleshooting Guide for AutoAccounting
Note 365529.1, Troubleshooting FRM-92XXX Errors in Oracle Applications
Note 139863.1, Troubleshooting Self Service Framework with Oracle Applications 11i
Note 216208.1, Oracle9i Application Server (9iAS) with Oracle E-Business Suite Release 11i Troubleshooting
Application Server/Java
Note 300482.1, Overview of Using Java with Oracle E-Business Suite Release 11i
Note 285218.1, Recommended Browsers for Oracle Applications 11i
Note 290807.1, Deploying Sun JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite 11i
Database and High Availability/RAC & ASM
Note 362135.1, Configuring Oracle E-Business Suite Release 11i with 10g Release2 RAC and ASM
Note 312731.1, Configuring Oracle Applications Release 11i with 10g Real Application Clusters and 10g ASM
Note 279956.1, Oracle E-Business Suite Release 11i with 9i RAC
Note 351646.1, Configuring Release 11i with 10g Database on Raw Devices
Note 341437.1, Business Continuity for Oracle Applications Release 11i Using RAC & Data Guard
Note 294652.1, Configuration Oracle E-Business Suite 11i RAC Environments (Load Balancing )
Application Server/Portal/Web Cache
Note 186981.1, Oracle Application Server with Oracle E-Business Suite Release 11i FAQ
Note 223927.1, Oracle Application Server Integration with Oracle E-Business Suite: Statement of Direction
Note 254618.1, HTTP Server Patches Certified to be used with E-Business Suite 11i External Integrations
Note 233436.1, Installing Oracle Application Server 10g with Oracle E-Business Suite Release 11i
Note 305918.1, Using Oracle Portal 10g with Oracle E-Business Suite 11i
Note 306653.1, Installing and Configuring 10gAS Web Cache with Oracle E-Business Suite 11i
Note 146469.1, Configuring Oracle Applications with Oracle Portal
Security/Encryption/SSL/OID/DMZ/SSO
Note 189367.1, Best Practices for Securing Oracle E-Business Suite
Note 287176.1, Oracle E-Business Suite 11i Configuration in a DMZ
Note 123718.1, A Guide to Understanding and Implementing SSL with Oracle Applications 11i
Note 340178.1, Enabling SSL with Oracle Application Server 10g and the E-Business Suite
Note 234599.1, Enabling Oracle Label Security in Oracle E-Business Suite
Note 391248.1, Encrypting EBS 11i Network Traffic using Advanced Security Option / Advanced Networking
Note 403294.1, Using Transparent Data Encryption with the E-Business Suite
Note 428503.1, Integrating Oracle E-Business Suite Release 11i with Oracle Database Vault 10.2.0.
Note 150832.1, Implementing Oracle Applications Synchronization with LDAP
Note 261914.1, Integrating Oracle E-Business Suite Release 11i with OID and Oracle Single Sign-On
Database
Note 285267.1, Oracle E-Business Suite Release 11i and Database FAQ
Note 216205.1, Database Initialization Parameters and Configuration for Oracle Applications 11i
Note 343917.1, Frequently Asked Questions: Using Oracle Applications with an x86-64 Database Server",
Note 311717.1, Frequently Asked Questions: Using Oracle Applications with an Itanium Database Server
Note 332977.1, Using Oracle Applications with an HP OpenVMS Alpha Database Server
Export & Import
Note 204015.1, Export/Import Process for Release 11i Database Instances Using Oracle8i Enterprise Edition
Note 230627.1, Export/Import Process for Release 11i Database Instances Using Oracle9i Enterprise Edition
Note 331221.1, Export/Import Process for Release 11i Database Instances Using Oracle10g Enterprise Edition
Note 362205.1, Export/Import Process for Release 11i Database Instances Using Oracle Database 10g R2
Split Configurations
Note 304489.1, Using Oracle Applications with a Split Configuration Database Tier on Oracle9i Release 2
Note 356839.1, Using Oracle Applications with a Split Configuration Database Tier on Oracle 10g Release 1
Note 369693.1, Using Oracle Applications with a Split Configuration Database Tier on Oracle 10g Release 2
Discoverer
Note 257798.1, Using Discoverer 10g (9.0.4) with Oracle Applications 11i,
Note 139516.1, Discoverer 4i with Oracle Applications 11i,
Note 313418.1, Using Discoverer 10g with Oracle Applications 11i
Subscribe to:
Posts (Atom)