Problems spanning multiple Oracle RAC instances are encountered very frequently. In such cases, alert log and trace files from across multiple instances might be required for diagnosis. This can be quite difficult if ADR is located on non-shared storage as it requires logging on to each node. By placing ADR base for all instances of an Oracle RAC database on a shared location, aggregated diagnostic data from all instances can be viewed from a single location. This article explores placement of ADR for an Oracle RAC database on Oracle cloud file system so that it is accessible by all the nodes in the cluster and can be exported over Highly Available Network File System (HANFS) to clients outside the cluster as well.
Various Diagnostic files for an oracle database are:
Dumps and Core Files
Health monitor reports
Data repair records
SQL test cases
Incident packages etc.
From Oracle Database 11g onwards, these diagnostic files are stored in the Automatic Diagnostic Repository (ADR)—a file-based repository outside the database. The location of ADR, known as ADR Base, is set by the DIAGNOSTIC_DEST initialization parameter.
In case of a RAC database, each instance has its own alert log and various other diagnostic files which are stored underneath its own home directory within the ADR. Since ADR is outside the database, it can be accessed even when the database is down. However, if ADR Base is set to a non-shared location, one needs to physically log on to the machine hosting an Oracle RAC instance in order to view the instance’s diagnostic files residing on the operating system. If the problems span multiple Oracle RAC instances, in order to diagnose you may need to correlate the alert logs and trace files from across multiple instances. This would involve logging on to each node which besides being inconvenient might not be possible if one or more nodes are down. Things could be much easier if the diagnostic files could somehow be gathered in a central location which is accessible from every node in the cluster. This can be implemented by setting ADR base to a location on shared storage on each node in an Oracle Real Application Clusters (Oracle RAC) environment, so that aggregated diagnostic data from all instances can be viewed from a single location.
From Oracle 12c onwards, ACFS can be leveraged as a highly available network file system (HANFS). In this article, I will demonstrate storage of ADR for all instances of a RAC database onthe same ACFS file system so that ADR can be exported over HANFS. As a result, the log files and trace files of all the instances are always available to the client server(s) outside the cluster as long as at least one node in the cluster is active.
Oracle 126.96.36.199c Flex cluster having two nodes host01 and host02
Oracle 188.8.131.52c policy managed RAC database pmdb having two instances pmdb_1 and pmdb_2