Monday, May 11, 2015

ACE Series: My IOUG Collaborate15 Paper

Case Studies Moving ASM Files
Anthony D. Noriega, ADN Research
NJIT Alumnus, Montclair State University Alumnus
When moving ASM database files in any scenario, it is important to analyze the convenience of taking such an action before proceeding. The Database Administrators should first ensure that this is the last option, and the most convenient in the resolution of the scenario. For a successful move, it is significantly important to look at the array of methods and techniques available in order to accomplish this task successfully and appropriately in each scenario. including tools, utilities, e.g., RMAN, and the SQL and PL/SQL API, as well as the task manageability via Oracle Enterprise Manager Cloud Control. Each options offers a constrained domain and scope; and, therefore, this task should be carefully planned and executed with a required regression testing.
Target Audience
This paper is addressed primarily to Oracle DBAs and Architects and IT Managers and Storage Administrators (e.g., SAN Administrators) who wish to be up to date with ASM storage management technology, and participate hands-on in these tasks, the planning, and its step-by-step execution.
Executive Summary
Upon completion of this study, the reader should be able to differentiate among strategies to accomplish ASM file move through the basic inspection of file type, disk capacity, disk group redundancy, and available space, granularity, and various other factors concerning storage networking and future capacity planning. Therefore, the following become specific target objectives, namely:

  1. Attaining a thorough of understanding in possible scenarios and options to move or rename ASM files of any kind.
  2. Establishing a set of best practices in order to determine the best applicable method to use, and the best approach to its execution.
  3. Handling errors and recovering from disaster while performing an ASM file move.

Background: Some A Priori Considerations
When moving ASM files from one location to another, the DBA must carefully consider the key reason to move the file. In general, planning storage and disk capacity accordingly should lead to avoiding file moves. However, many reasons such as requirements for file replication or muiti-plexing, low ASM storage redundancy, a physical device being full, previous diskgroup or volume mismanagement, and the general inability to increase the storage space can lead to a file move, usually a data file.
The type of file to move and its role via its logical container (.e.g, a tablespace can also derive unforeseen constraints, which could jeopardize the consistency and integrity of the database and clusterware storage involved directly or indirectly.
Besides, the following are important considerations are important: Oracle ASM metadata resides within the disk group and contains information that Oracle ASM uses to control a disk group, including:
  • The disks that belong to a disk group
  • The filenames of the files in a disk group
  • The location of disk group data file extents
  • The amount of space that is available in a disk group
  • A redo log recording information about atomically changing metadata blocks
  • Oracle ADVM volume information.

Moving Considerations by File Type

Control Files
When copying or moving control files, ensure that each properly renamed copy of the control file is consistent with all other ASM files, and that the target disk utilized has the same properties, such as, for instance, being created using the same ASM template or at least preserves the appropriate type of striping. Likewise, the copied file should be reachable by the instance, and therefore the disk should not be seen as foreign to the containing database and the supporting ASM instances. The DBA can use any of ASMCMD, PL/SQL supplied packages, such as DBMS_FILE_TRANSFER, preferably in order to accomplish this task. Indeed moving a control file will justify a reconfiguration of the initialization parameter files, i.e., both the server parameter file, namely, spfile, and its file system text copy init{SID}.ora.

Log Files
Usually, a DBA would copy or move a log file in order to multiplex such files, but in some ASM-relevant scenarios, this may be required because certain disk groups may be low in redundancy and there is no easy path to accomplish an expansion. For congruency, it may important to set up files in specific disk groups, or there could be a recovery or migration scenario to fulfill this drill. In any circumstances, log files moving and copying are subject to the same rules as on a conventional file system database as it is for an ASM database. Moving a log file in ASM requires that the log group is not currently in used, but also that the file is temporarily off line. Likewise, the appropriate renaming of the file with the ALTER DATABASE command is required. The DBA is also required to BACKUP the control file to TRACE, to maintain a consistent record of the database, and also BACKUP the physical image of the control file by explicitly using the RMAN backup controlfile command, unless RMAN is using the CONFIGURE CONTROFILE AUTOBACKUP ON setting. Although this task can be accomplished with other methods, the only highly recommended method is to use Recovery Manager, since each other method does not provide a natural way for the database to easy become aware of the change, while RMAN provides a native capability not only to consistently catalog the change in its recovery catalog recovery, but also maintain some data dictionary views, which make the instance aware of the new file location.

Data Files
ASM Data files may be subject to a forceful move in some basic scenarios, namely:

  • Fast growing tablespace, data file or table in a disk group with low redundancy
  • Lack of storage symmetry among disk groups available to optimize storage from both the physical and logic perspective
  • The file may reside in a disk, where Oracle Enterprise Manager Cloud Control has reported an existing or potential media failure. For example, an Exadata Machine has become aware of the failure and has reported this issue in a timely fashion to prevent a production downtime.
  • A major storage reorganization or database migration, which implies a physical move to new storage devices, involving in particular newly created ASM disk groups.
  • Other business reasons, such as data security or privacy or even disaster recovery prevention can lead to cloning and moving database files accordingly prior to any such an event taking place.

While, in general, it is possible to use any available method to move an ASM database file, it is recommended to RMAN in the following scenarios:

  • When performing upgrades, migration or data recovery operations.
  • When performing tablespace point-in-time recovery (TSPITR) or (DBPITR) and possibly using an auxiliary container for this purpose.
  • When moving an ASM file as part of a transportable tablespace operations among high-availability instance within native ASM or cross-wide clusterware in a FlexASM environment.

While RMAN remains the recommended method, PL/SQL packages, ASMCMD are useful mechanisms to approach an ASM file move.

In the scenarios depicted for transportable tablespaces or TSPITR, the same principles of self-containment apply to such objects, and therefore this ASM operation may be constrained either logically or physically by the factors involved in such operation, depending on each scenario presented. This is not a speculation, and it can be proven with a straightforward test.

When using ASMCMD, it is important to understand that the connection made to the data file is associated with the ASM instance and not necessarily with a specific database instance. Therefore, the DBA must be aware of this and so the database metadata. So, when performing an ASM file move directly from the database instance, beyond RMAN, PL/SQL file packages, such as, remain the next choice, as the moment to control this event is right at hand to keep the database consistent. This is essentially valid for both standalone ASM databases usually running on a relatively simple clusterware environment or more complex ASM or FlexASM RAC clustered databases.

Some Simple Rules to Follow as a Guideline

RMAN Can Perform Most Moves
In moving scenarios, RMAN can make complex situations due to physical distance, a disparity of domains, Endian in use, disk striping, volume striping and width, file granularity.
Domain Used
If you plan to move a datafile which resides in a containing domain such as ADVM, consider alternative actions or simply work together with your system administrator in relation to using ACFS. While RMAN is a good option, a comprehensive custom coding is normally required for this action. Disparity of domain or namespaces could lead to more complex operations, database reorganization, and substantial metadata work.
ASM Compatibility Issues
This is normally a constraint for many ASM operations. Usually setting the compatible.asm and compatibility.advm parameters to an early supported version, e.g,, 11.2 for Oracle 12c, is useful if the file moved has to be transported as part of a tablespace move to a more current version. However, this is not a panacea, and in a real world scenario, it is possible to encounter scenarios where the expected compatibility behavior does not correspond to the actual execution of the transport. If ADVM and ACFS is in place, the ability to use ASMCMD, SQL and PL/SQL, and access to ACFS via tools such as asmtool or acfsutil, shell scripting, or Oracle Enterprise Manager Cloud Control should be picture to a comprehensive understanding of each domain space and its compatibility accordingly.
Programmable Strategies vs. Tools and Utilities Strategies
Based on the nature and circumstances of the file move, including the file type, the domain space, the storage features, the storage networking resources and capabilities, and the propagation delay among key factors, administrators must decide in the best strategy to be applied based primarily on process reliability, expected performance, and time available to complete this task considering the expected successful outcome: attaining a live working file in a different database or container within the same database.
Local Files vs. Clusterwide Files
ACFS file system, if used, can provide mount points that involve only local mounts (i.e., in one node, versus clusterwide mounts, which maybe mounted thoroughout the cluster file system, e.g., Oracle HANFS.

The Invariant ASM Moving Process

Whether the Oracle DBA team is using programmatic or tools-utilities strategies to move ASM files, the process of moving an ASM file is relatively an invariant one.
Before moving an ASM file, the administrators team should be aware of the domains and namespaces where the move will occur and taking into considerations any pre-conditions and existing or potential constraints. The following are the ASM file move general steps, namely:

  • First, perform a full database consistent backup.
  • Put the file to be move offline
  • Use a tool, utility, RMAN, SQL or PL/SQL command to move the file accordingly to the desired destination.
  • Rename the data file in the data dictionary accordingly, using the ALTER DATABASE RENAME FILE SQL command.
  • Switch the [data] file to copy (use target file name).
  • Recover the [data] file using the target file name.
  • Place the newly moved file online using the ALTER DATABASE command
  • Drop the old file using the ALTER DISKGROUP DROP FILE command
  • Backup the database using RMAN and the controlfile to trace using SQL.

The following is an array of examples moving ASM files. The picture sequence includes some of the most important steps in the process in each scenario presented, namely:
   Exhibit 1. Moving an ASM File between Disk Groups (The most typical case): The Copy Process
   Exhibit 2. Switching the ASM Data File to Copy
   Exhibit 3. Recovering an ASM File Before Placing it back online
   Exhibit 4. Placing the ASM Data File Back Online After as Successful Move Operation.

Some of the steps presented here with RMAN, could have also been accomplished with ASMCMD. When the file is a clusterwide visible file, ASMCMD can work quite well in that domain or namespace. However, in most cases a similar move operation can be accomplished with RMAN and PL/SQL provided that any constrains are properly overriden with the appropriate strategy.
Exhibit 5. Other Move Methods: Copying an ASM File Using PL/SQL or ASMCMD as Part of the Move Process.1

1The actual parameters for source and destination directory objects are the names of existing Oracle Directories with properly granted READ/WRITE privileges (to the AUTHID, when in a stored program) rather than the matching file system paths.

Exhibit 6. DBMS_FILE_TRANSFER Supplied PL/SQL Package Description and Capabilities. .
Summary of ASM File Move Strategies
The following exhibit summarizes the options the DBA team has in order to accomplish the task to move an ASM file in any scenario.
Exhibit 7. Summary of Strategies to Copy an ASM File.
Some Constraints

The following constraints might affect an ASM File move, as follows:

General Constraints
In some scenarios, a copy of an Oracle OMF-named data file may fail in Oracle 12c, but there are several workarounds to resolve the issue. Attempting to first renaming the file is the first consideration and them retrying the copy/move process.

Other constrains involve:
  • In a few scenarios, RMAN Configuration might be inappropriate to accomplish certain PDB's ASM file move in Oracle12c, and therefore RMAN reconfiguration may be required.
  • Inappropriate settings or connections to reach a clusterwide file from a local environment or viceversa (the opposite direction scenario).

When Using ADVM and ACFS
  • The link() and rename() system calls may fail if an attempt is made to link or rename a file in the Oracle ACFS file system and a file in any associated read-write snapshot, or vice versa. Likewise, any tools which use the link() and rename() system calls, such as ln and mv, also fail in similar scenarios.

  • Oracle ACFS snapshots have not been optimized for use with ASM database data files.

  • Exhibit 8. DBMS_FILE_TRANSFER PL/SQL Supplied Package Description and Capabilities.

    Concluding Remarks

    Within the limitations and constraints to demonstrate this case studies whether further work is greatly required, the author has arrived to the following fundamental conclusions, namely:
    • There is a nearly invariant process to copy and move any ASM file by type, intra and inter domain or namespace, and in any diverse scenarios.
    • While there are some constraints, it is in generally possible to overcome any single constraint and get and ASM file moved successfully.
    • Key constraints involve forward and backward compatibility issues, namespace or domain alignment, file granularity and media constraints, including vendor-driven constraints.
    • In environments, such as in Exadata machines, the process described here may be subject to slight changes based on machine architecture, command line interface, and ASM layer within Exadata storage stack, and storage networking levels.

Slides from my IOUG Collaborate15 Presentation