ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle ASM 系列 小结

Oracle ASM 系列 小结

原创 Linux操作系统 作者:roominess 时间:2012-03-31 10:40:13 0 删除 编辑

metalink上看到一篇有关ASM 总结的文章,贴出来,共同学习。

 

.  Automatic Storage Management (ASM) Alerts:

       Alert: Querying v$asm_file Gives ORA-15196 After ASM Was Upgraded From 10gR2 To 11gR2 with an AU size > 1M [ID 1145365.1]

        

 

.  Automatic Storage Management (ASM) Concepts and Overview:

 

1ASM Concepts Quick Overview [ID 1086199.1]

ASM Concepts Quick Overview [ID 1086199.1]


 

Modified 26-AUG-2010     Type BULLETIN     Status PUBLISHED

 

In this Document
  Purpose
  Scope and Application
  ASM Concepts Quick Overview
  References


Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.2.0.2 - Release: 10.1 to 11.2
Information in this document applies to any platform.

Purpose

This note provides a quick overview of Oracle Automatic Storage Management (ASM) concepts and operations.  This document can help clarify some key concepts that are not well understood by users - without requiring lengthy research.

Scope and Application

This note is intended for architects, DBAs, and system administrators at an intermediate level of Oracle database knowledge.

ASM Concepts Quick Overview

The following points describe key concepts of ASM; for more information refer to the resources at the end of this note.

  • ASM exists to manage file storage for the RDBMS
    • ASM does NOT perform. I/O on behalf of the RDBMS
    • I/O is performed by the RDBMS processes as it does with other storage types
    • Thus, ASM is not an intermediary for I/O (would be a bottleneck)
    • I/O can occur synchronously or asynchronously depending on the value of the DISK_ASYNCH_IO parameter
    • Disks are RAW devices to ASM
    • Files that can be stored in ASM: typical database data files, control files, redologs, archivelogs, flashback logs, spfiles,
      RMAN backups and incremental tracking bitmaps, datapump dumpsets.
    • In 11gR2, ASM has been extended to allow storing any kind of file using Oracle ACFS capability (it appears as another filesystem to clients). Note that database files are not supported within ACFS

 

  • ASM Basics
    • The smallest unit of storage written to disk is called an "allocation unit" (AU) and is usually 1MB (4MB recommended for Exadata)
    • Very simply, ASM is organized around storing files
    • Files are divided into pieces called "extents"
    • Extent sizes are typically equal to 1 AU, except in 11g where it will use variable extent sizes that can be 1, 8, or 64 AUs
    • File extent locations are maintained by ASM using file extent maps.
    • ASM maintains file metadata in headers on the disks rather than in a data dictionary
    • The file extent maps are cached in the RDBMS shared pool; these are consulted when an RDBMS process does I/O
    • ASM is very crash resilient since it uses instance / crash recovery similar to a normal RDBMS (similar to using undo and redo logging)
  • Storage is organized into "diskgroups" (DGs)
    • A DG has a name like "DATA" in ASM which is visible to the RDBMS as a file begining with  "+DATA"; when tablespaces are created, they refer to a DG for storage such as "+DATA/.../..."
    • Beneath a diskgroup are one or more failure groups (FGs)
    • FGs are defined over a set of "disks"
    • "Disks" can be based on raw physical volumes, a disk partition, a LUN presenting a disk array, or even an LVM or NAS device
    • FGs should have disks defined that have a common failure component, otherwise ASM redundancy will not be effective
  • High availability
    • ASM can perform. mirroring to recover from device failures
    • You have a choice of EXTERNAL, NORMAL, OR HIGH redundancy mirroring
      EXTERNAL means allow the underlying physical disk array do the mirroring 

      NORMAL
       means ASM will create one additional copy of an extent for redundancy    
      HIGH means ASM will create two additional copies of an extent for redundancy
    • Mirroring is implemented via "failure groups" and extent partnering; ASM can tolerate the complete loss of all disks in a failure group when NORMAL or HIGH redundancy is implemented

 

  • FG mirroring implementation
    • Mirroring is not implemented like RAID 1 arrays (where a disk is partnered with another disk)
    • Mirroring occurs at the file extent level and these extents are distributed among several disks known as "partners"
    • Partner disks will reside in one or more separate failure groups (otherwise mirror copies would be vulnerable)
    • ASM automatically choses partners and limits the number of them to less than 10 (varies by RDBMS version) in order to contain the overall impact of multiple disk failures
    • If a disk fails, then ASM updates its extent mapping such that reads will now occur on the surviving partners
      • This is one example when ASM and the RDBMS communicate with each other
      • The failed disk is offlined
    • In 11g, while the disk is offline, any changes to files are tracked so that those changes can be reapplied if the disk is brought online within a period of time (3.6 hours by default value of DISK_REPAIR_TIME). This could happen in cases of a bad controller or similar problem rather than the failure of the disk itself
      • The tracking occurs via a bitmap of changed file extents; the bitmaps tell ASM which extents need to be copied back to the repaired disk from the partner
      • This is called "fast mirror resync"
    • In 10g, the disk is offlined and dropped - there is no repair time grace period before dropping.
    • If the disk cannot be onlined, it must be dropped. A new disk will be installed and ASM will copy the data back via a "rebalancing" operation.  This happens automatically in the background
  • Rebalancing
    • "Rebalancing" is the process of moving file extents onto or off of disks for the purpose of evenly distributing the I/O load of the diskgroup
    • It occurs asynchronously in the background and can be monitored
    • In a clustered environment, rebalancing for a disk group is done within a single ASM instance only and cannot be distributed across multiple cluster node to speed it up
    • ASM will automatically rebalance data on disks when disks are added or removed
    • The speed and effort placed on rebalancing can be controlled via a POWER LIMIT setting
    • POWER LIMIT controls the number of background processes involved in the rebalancing effort and is limited to 11.  Level 0 means no rebalancing will occur
    • I/O performance is impacted during rebalancing, but the amount of impact varies on which disks are being rebalanced and how much they are part of the I/O workload.  The default power limit was chosen so as not to impact application performance
  • Performance
    • ASM will maximize the available bandwidth of disks by striping file extents across all disks in a DG
    • Two stripe widths are available: coarse which has a stripe size of 1 AU, and fine with stripe size of 128K
    • Fine striping still uses normally-sized file extents, but the striping occurs in small pieces across these extents in a round-robin fashion
    • ASM does not read from alternating mirror copies since disks contain primary and mirror extents and I/O is already balanced
    • By default the RDBMS will read from a primary extent; in 11.1 this can be changed via the PREFERRED_READ_FAILURE_GROUP parameter setting for cases where reading extents from a local node results in lower latency.  Note: This is a special case applicable to "stretch clusters" and not applicable in the general usage of ASM
  • Miscellaneous
    • ASM can work for RAC and non-RAC databases
    • One ASM instance on a node will service any number of instances on that node
    • If using ASM for RAC, ASM must also be clustered to allow instances to update each other when file mapping changes occur
    • In 11.2 onwards, ASM is installed in a grid home along with the clusterware as opposed to an RDBMS home in prior versions.

 

References

NOTE:751463.1 - ASM Inherently Performs Asynchronous I/O Regardless of filesystemio_options Parameter
Oracle Press book, Oracle Automatic Storage Management, by Nitin Vengurlekar, Murali Valleth, and Rich Long.
Documentation: Oracle Database Storage Administrator's Guide
NOTE:265633.1 - ASM Technical Best Practices


2New Feature on ASM (Automatic Storage Manager). [ID 249992.1]

New Feature on ASM (Automatic Storage Manager). [ID 249992.1]


 

Modified 03-DEC-2009     Type BULLETIN     Status ARCHIVED

 

 

PURPOSE

-------

               Automatic Storage Management is a file system and volume manager built into the database kernel that allows the practical management of thousands of disk drives with 24x7 availability. It provides management across multiple nodes of a cluster for Oracle Real Application Clusters (RAC) support as well as single SMP machines.

               It automatically does load balancing in parallel across all available disk drives to prevent hot spots and maximize performance, even with rapidly changing data usage patterns.

               It prevents fragmentation so that there is never a need to relocate data to reclaim space.  Data is well balanced and striped over all disks. It does automatic online disk space reorganization for the incremental addition or removal of storage capacity.

               It can maintain redundant copies of data to provide fault tolerance, or it can be built on top of vendor supplied reliable storage mechanisms. Data management is done  by selecting the desired reliability and performance characteristics for classes of data rather than with human interaction on a per file basis.

 

               ASM solves many of the practical management problems of large Oracle databases.As the size of a database server increases towards thousands of disk drives, or tens of nodes in a cluster, the traditional techniques for management stop working. They do not scale efficiently, they  become too prone to human error, and they require independent effort on every node of a cluster. Other tasks, such as manual load balancing, become so complex as to prohibit their application.

 

               These problems must be solved for the reliable management of databases in the tens or hundreds of terabytes. Oracle is uniquely positioned to solve these problems as a result of our existing  Real Application Cluster technology. Oracle’s control of the solution ensures it is reliable and integrated with Oracle products.

 

               This document is intended to give some insight into the internal workings of ASM.  It is not a detailed design document. It should be useful for people that need to support ASM.

               Automatic Storage Management is part of the database kernel. It is linked into $ORACLE_HOME/bin/oracle so that its code may be executed by all database processes.

               One portion of the ASM code allows for the start-up of a special instance called an ASM Instance. ASM Instances do not mount databases, but instead manage the metadata needed to make ASM files available to ordinary database instances. Both ASM Instances and database instances have access to some common set of disks.

 

               ASM Instances manage the metadata describing the layout of the ASM files. Database instances access the contents of ASM files directly,  communicating with an ASM instance only to get information about the layout of these files.  This requires that a second portion of the ASM code run in the database instance, in the I/O path.

 

Note:

1. One and only one ASM instance required per node. So you might have multiple databases,  but they will share the same single ASM.

 

2. ASM is for DATAFILE, CONTROLFILE, REDOLOG, ARCHIVELOG and SPFILE. So you can use  CFS for common oracle binary in RAC.

 

3. ASM can provide mirroring for files in a disk group.

 

4. In external redundancy disk groups, ASM does not mirroring.  For normal redundancy,   ASM 2-way mirrors files by default, but can also leave files unprotected.   [Unprotected  files  are  not recommended].  For high redundancy disk groups,   ASM 3-way mirrors files.

 

5. Unless a user specifies an ASM alias filename during file creation, the file is

   OMF.  OMF files are deleted automatically when the higher level object (eg tablespace)   is dropped, whereas non-OMF files must be manually deleted. Oracle is recommending  to use OMF.

 

HOW TO USE ?

------------

 

Use DBCA to configure your ASM.

 

DBCA eases the configuring and creation of your database while EM provides an integrated approach for managing both your ASM instance and database instance.

   

Automatic Storage Management is always installed by the Oracle Universal Installer when you install your database software. The Database Configuration Assistant (DBCA) determines if an ASM instance already exists, and if not, then you are given the option of creating and configuring an ASM instance as part of database creation and configuration. If an ASM instance already exists, then it is used instead.

   

DBCA also configures your instance parameter file and password file.

 

 

Steps in DBCA:

1. Choose ASM disk.

2. Create diskgroup by choosing available disk.

3. While creating ASM you have choice of mirroring for files in a disk group and    the options are like below

   HIGH, NORMAL or EXTERNAL.

   High     -> ASM 3-way mirrors

   Normal   -> ASM 2-way mirrors

   External -> If you have already mirror disk in H/W label like EMC or another                third party

4. dbca will create a separate instance called "+ASM" which will be in nomount stage    to control your ASM.

5. Choose your all datafile, controlfile, redolog and spfile to your ASM volume.

 

Preinstall:

Here DBA will create the ASM volume, so Sysadmin should give the ownership or

proper privs to DBA.

 

Example in LINUX

----------------

 

You have two disk say "/dev/sdf" and "/dev/sdg"

 

Determine what those devices are bound as raw:

$ /usr/bin/raw -qa

If not:

Include devices in diskgroups by editing /etc/sysconfig/rawdevices :

      eg   /dev/raw/raw1 /dev/sdf

           /dev/raw/raw2 /dev/sdg

 

Set owner, group and permission on device file for each raw device:

$ chown oracle:dba /dev/raw/rawn, chmod 660 /dev/raw/rawn

 

Bind disk devices to raw devices:

$ service rawdevices restart

 

So from DBCA you can see the device "raw1" and "raw2".

 

After finishing of ASM volume creation, when you create a database on an ASM volume, you should see the file details using Enterprise Manager (EM). Or you can use V$ or DBA view to check the datafile name. Oracle recommended not to specify the datafile name while adding datafile or creating new tablespace, because ASM will automatically generate OMF file.

 

Note: If DBA's by mistake or intentionally choose the datafile name, dropping of  tablespace, will not drop the datafile from ASM volume.

 

 

IMPORTANT VIEW in ASM

=====================

V$ASM_DISKGROUP

V$ASM_CLIENT

V$ASM_DISK

V$ASM_TEMPLATE

V$ASM_ALIAS

V$ASM_OPERATI

 

311g ASM New Feature [ID 429098.1]

11g ASM New Feature [ID 429098.1]


 

Modified 23-AUG-2007     Type BULLETIN     Status PUBLISHED

 

In this Document
  Purpose
  
Scope and Application
  
11g ASM New Feature
     
1.New SYSASM Role for Automatic Storage Management Administration 
     
2.The Disk Group Compatibility feature
     
3.ASM Fast Rebalance 
     
4.ASM Fast Mirror Resync
     
5.ASMCMD New Commands
     
6.Preferred Mirror Read 
     
7.ASM Variable size extents,scalability and performance enhancements 
     
8.Automatic Storage Management Rolling Migration
  
References


Applies to:

Oracle Server - Enterprise Edition - Version: 11.1 to 11.1
Linux x86

Purpose

This document is intended to introduce various new ASM features introduced in Oracle Database 11g.

 Please find below list of the features 

1) New SYSASM Role for Automatic Storage Management Administration
2) Disk Group Compatibility attributes
3) ASM Fast Rebalance
4) ASM Fast Mirror Resync
5) New ASMCMD commands
6) Automatic Storage Management Preferred Mirror Read
7) ASM Variable size extents,scalability and performance enhancements
8) Automatic Storage Management Rolling Migration

Scope and Application

All the DBAs and users concerned with Database Administration activities.

11g ASM New Feature

1.New SYSASM Role for Automatic Storage Management Administration


The SYSASM privilege enables the separation of the database operating system credentials from the ASM credentials.

Use the SYSASM privilege instead of the SYSDBA privilege to connect to and administer an ASM instance. If you use the SYSDBA privilege to connect to an ASM instance, then Oracle writes warnings to the alert log file because commands that you run using the SYSDBA privilege on an ASM instance will eventually be deprecated.

Oracle writes alerts to the alert log files if you issue CREATE, ALTER, or DROP DISKGOUP statements that should be performed by SYSASM.

Alert entry 

WARNING: Deprecated privilege SYSDBA for command 'STARTUP' 
WARNING: Deprecated privilege SYSDBA for command 'SHUTDOWN' 

SQL> create diskgroup dgext external redundancy disk '/dev/raw/raw7' 
WARNING: Deprecated privilege SYSDBA for command 'CREATE DISKGROUP'

2.The Disk Group Compatibility feature

Two new Disk Group compatibility attributes are introduced in the Oracle 11g ASM.

The new compatible.asm and compatible.rdbms disk group compatibility attributes 
determine the minimum version of ASM and database instances that can connect to an ASM disk group 

Advancing the Disk group Oracle Database and ASM compatibility settings enables you to use the new ASM features that are available in latest release. 

Advancing compatible.asm/compatible.rdbms attributes from 10.1 to 11.1 will enable the following key features: 

- Preferred mirror read 
- Variable size extents 
- Fast mirror resync 

The software version of ASM determines the default compatibility of newly created disk groups. You can override the disk group compatibility default setting when you create disk groups with the CREATE DISKGROUP SQL statement. 

The ALTER DISKGROUP SQL statement can update the compatibility settings for existing disk groups. 

The compatibility settings for a disk group can only be advanced, you cannot revert to a lower compatibility setting.

Example to create diskgroup with compatible attribute :  

SQL> create diskgroup dgext11gasm external redundancy disk '/dev/raw/raw7'   
attribute 'compatible.asm'='11.1';   
Diskgroup created.   

SQL> create diskgroup dgext11grdbms external redundancy disk '/dev/raw/raw8'   
attribute 'compatible.rdbms'='11.1','compatible.asm'='11.1';   

select name,compatibility,database_compatibility from v$asm_diskgroup;   

NAME COMPATIBILITY DATABASE_COMPATIBILITY   
--------- -------------------- ------------  
DGEXT11GASM 11.1.0.0.0 10.1.0.0.0   
DGEXT11GRDBMS 11.1.0.0.0 11.1.0.0.0   
 

 

 

Refer below note for Diskgroup creation error's in 11g 

Note.433710.1 - ORA-15234 target RDBMS compatibility (11.1.0.0.0) exceeds ASM compatibility

 

3.ASM Fast Rebalance

The RESTRICTED mode enables you to perform. all maintenance tasks on a disk group in the ASM Instance without any external interaction.Rebalance operations performed while the diskgroup is in restricted mode eliminate the lock/unlock extent map messaging between ASM instances in Oracle RAC environment,thus improving overall rebalance throughput. 

Refer below note for more information about the feature 

Note 445037.1 - ASM Fast Rebalance

4.ASM Fast Mirror Resync

ASM fast resync keeps track of pending changes to extents on an OFFLINE disk during an outage. The extents are resynced when the disk is brought back online or replaced.

By default, ASM drops a disk shortly after it is taken offline. You can set the DISK_REPAIR_TIME attribute to prevent this operation by specifying a time interval to repair the disk and bring it back online. 

To use this feature, the disk group compatibility attributes must be set to 11.1 or higher 

For more information refer below Note

Note.443835.1 - ASM Fast Mirror Resync - Example to simulate transient disk failure and restore disk

5.ASMCMD New Commands


ASMCMD has the following four new commands: lsdsk,md_backup,md_restore and remap

The following describes the four new ASM commands:

cp - Enables you to copy files between ASM disk groups on local instances and remote instances.

 

lsdsk -ASM can list disk information with or without a running ASM instance. This is a useful tool for system or storage administrators who want to obtain lists of disks that an ASM instance uses.

 

md_backup and md_restore - These commands enable you to re-create a pre-xisting ASM disk group with the same disk path, disk name, failure groups, attributes,templates and alias directory structure. You can use md_backup to back up the disk group environment and use md_restore to re-create the disk group before loading from a database backup.

 

remap - You can remap and recover bad blocks on an ASM disk in normal or high redundancy that have been reported by storage management tools such as disk scrubbers. ASM reads from the good copy of an ASM mirror and rewrites these blocks to an alternate location on disk.

 

6.Preferred Mirror Read


When ASM is managing redundancy,you can configure an ASM instance on a node to read from a preferred mirror copy(ie a preferred real failure group). The default behaviour is to always read from the primary copy.

The feature is beneficial when you have an extended Oracle RAC cluster,where the nodes and the failure groups are seperated by a long distance to enable disaster recovery. In this case, the Oracle RAC nodes on each site can be configured to read from their local storage mirror copies instead of going through a newtwork with potentially high latencies.

The ASM_PREFERRED_READ_FAILURE_GROUPS initialization parameter to specify a list of failure group names as preferred read disks.This parameter is a multi-valued parameter and should contain a string with a list of seperated by a comma.

7.ASM Variable size extents,scalability and performance enhancements

The variable size extent feature in Oracle Database 11g enables support for much larger ASM files,reduces SGA memory requirements for very large databases,and improves performance for file create and open functions.

An ASM file can begin with 1MB extents and as the file size increases,the extent size also increases to 8MB and 64MB at a predefined number of extents.Therefore, the size of extent map defining a file can be smaller by a factor of 8 and 64 depending on the size of the file.The initial extent size is equal to the allocation unit size and it increases by the 8 and 64 factor at predefined thresholds.

This feature is completely automatic for newly created files once compatible.asm and compatible.rdbms have been advance to 11.1

8.Automatic Storage Management Rolling Migration


ASM rolling upgrades enable you to independently upgrade or patch clustered ASM nodes without affecting database availability, thus providing greater uptime. Rolling upgrade means that all of the features of a clustered ASM environment function when one or more of the nodes in the cluster uses different software versions.

To perform. a rolling upgrade, your environment must be prepared.If you are using Oracle Clusterware, then your Oracle Clusterware must be fully upgraded to the next patch or release version before you start the ASM rolling upgrade.In addition, you should prepare your Oracle Clusterware in a rolling upgrade manner to ensure high availability and maximum uptime.

Before you patch or upgrade the ASM software on a node,you must place the ASM cluster into rolling upgrade mode. This enables you to begin an upgrade and operate your environment in multiversion software mode. Do this by issuing the following SQL statement where number includes the version number, release number, update number, port release number, and port update number. Enter these values for number in a decimal-separated string, (e.g 11.2.0.0.0)

To perform. the upgrade as in the following example


ALTER SYSTEM START ROLLING MIGRATION TO 11.2.0.0.0;

 

4Oracle Database 11g Automatic Storage Management New Features Overview

       这个是pdf 格式,下载地址:http://download.csdn.net/source/3191090

 

5ASM - Scalability and Limits [ID 370921.1]
       

ASM - Scalability and Limits [ID 370921.1]

 

ASM imposes the following limits:

163 disk groups in a storage system
2
10,000 ASM disks in a storage system
3
2 terabyte maximum storage for each ASM disk (the Bug 6453944 allowed larger sizes, but that led to problems, see Note 736891.1"ORA-15196 WITH ASM DISKS LARGER THAN 2TB")
440 exabyte maximum storage for each storage system
5
1 million files for each disk group
62.4 terabyte maximum storage for each file

 

6ASM 11g New Features - How ASM Disk Resync Works. [ID 466326.1]

       . ASM Fast Disk Resync Overview

    1) When we take a disk offline in case the disk is corrupted or database is not able to read or write from the disk. In case of Oracle database 10g, oracle engine use to balance the other disks with the content of offline disk. This process was a relatively costly operation, and could take hours to complete, even if the disk failure was only a transient failure. 

    2) Oracle Database 11g introduces the ASM Fast Mirror Resync feature that significantly reduces the time required to resynchronize a transient failure of a disk. When a disk goes off line oracle engine doesn’t balance other disk, instead ASM tracks the allocation units that are modified during the outage. The content present in the failed disk is tracked by other disks and any modification that is made to the content of failed disk is actually made in other available disks. Once we get the disk back and attach it, the data belonging to this disk and which got modified during that time will get resynchronized back again. This avoids the heavy re-balancing activity. 

    3) ASM fast disk resync significantly reduces the time required to resynchronize a transient failure of a disk. When a disk goes offline following a transient failure, ASM tracks the extents that are modified during the outage. When the transient failure is repaired, ASM can quickly resynchronize only the ASM disk extents that have been affected during the outage. 

    4) This feature assumes that the content of the affected ASM disks has not been damaged or modified. 

    5) When an ASM disk path fails, the ASM disk is taken offline but not dropped if you have set the DISK_REPAIR_TIME attribute for the corresponding disk group. The setting for this attribute determines the duration of a disk outage that ASM tolerates while still being able to resynchronize after you complete the repair. 

    Note: The tracking mechanism uses one bit for each modified allocation unit. This ensures that the tracking mechanism very efficient. 

    

     
 

 

 

.      ASM 11g New Features - How ASM Disk Resync Works.

 Requirements: 

1) This feature requires that the redundancy level for the disk should be set to NORMAL or HIGH. 

2) compatible.asm & compatible.rdbms = 11.1.0.0.0 or higher 

3) You need to set DISK_REPAIR_TIME parameter, which gives the time it takes for the disk to get repaired. The default time for this is set to 3.6 hours. 

Examples:

SQL> ALTER DISKGROUP dgroupA SET ATTRIBUTE 'DISK_REPAIR_TIME'='3H';



4) The disk has to be offline (automatically due to the hardware failure or manually for maintenance operations) and should not be dropped. 

To take the disk offline use:

SQL> ALTER DISKGROUP … OFFLINE DISKS command.



Example:

ALTER DISKGROUP dgroupA OFFLINE DISKS IN FAILGROUP controller2 DROP AFTER 5H;


Repair time for the disk is associated with diskgroup. You can override the repair time of diskgroup using following command:

SQL> ALTER DISKGROUP dgroupA SET ATTRIBUTE ‘DISK_REPAIR_TIME’='3H’;



Additional Manual Offline Disk Operations Examples:

SQL>ALTER DISKGROUP DG1 OFFLINE DISK DG1_0003 ; 
SQL>ALTER DISKGROUP DG1 OFFLINE DISK DG1_0003 DROP AFTER 1H; 
SQL>ALTER DISKGROUP DG1 OFFLINE DISKS IN FAILGROUP FG1; 
SQL> ALTER DISKGROUP dgroupA OFFLINE DISKS IN FAILGROUP controller2 DROP AFTER 5H;

 

5) After the transient failure was corrected on the affected disks, you will need to explicitly online the disks. 

Examples:

SQL>ALTER DISKGROUP DG1 ONLINE DISK DG1_0003; 

SQL>ALTER DISKGROUP DG1 ONLINE DISKS IN FAILGROUP FG1 POWER 8 WAIT;

 

6) If you cannot repair a failure group that is in the offline state, you can use the ALTER DISKGROUP DROP DISKS IN FAILGROUP command with the FORCE option. This ensures that data originally stored on these disks is reconstructed from redundant copies of the data and stored on other disks in the same diskgroup.

Example:

 

SQL> ALTER DISKGROUP dgroupA DROP DISKS IN FAILGROUP controller2;

 

7How does ASM work with RAID where striping and mirroring are already built-in [ID 330398.1]

       There is really no single answer to this question since it depends on business requirement and budget (cost, availability, performance & utilization). The followings are some trade offs that needs to be considered to compare hardware mirroring-stripping and ASM.

 

(1).      ASM & RAID striping are complimentary to each other. When a SAN or disk array provides striping, that can be used in a manner which is complementary to ASM.

(2).      Oracle ideally suggest that the RAID stripe size at the SAN layer should match ASM stripe size (1MB by default). However, if the above is not possible (1MB stripe at storage level),then a stripe size of 256K/128K/512k should be ok. As long ASM 1MB stripe size is a multiple of hardware stripe size, I/O is aligned at hardware level. Otherwise, a single I/O can be split into multiple disks and cause multiple read writes and excessive i/o operations.

(3).      ASM mirroring has a small overhead on the server (specially on write performance) where external hardware mirroring performs the function on the storage controller.

(4).      With external mirroring, you need to reserve disks as hot spares.  With ASM, hot spares are not necessary and therefore, more efficient use of the storage capacity.

(5).      ASM reduces the chance of mis-configuation and human error because of failure groups. With external RAID, you have to carefully plan your redundant controllers and paths which requires higher admin overhead.

 

8Can External Tables Be Placed inside ASM diskgroups [ID 470989.1]

       External Tables cannot be created inside ASM diskgroup.

 

       1 ) ASM cannot store external tables in ASM diskgroups since external tables are flat ASCII files which are not a file type supported in ASM, therefore external tables must be allocated on regular filesystem files. 

       2)  The supported ASM files are described in the next manual: 
       
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/storeman.htm#i1021337 

==> 12 Using Automatic Storage Management

 

Table 12-8 Oracle File Types and Automatic Storage Management File Type Tags

 

 

Automatic Storage Management file_type

Description

Automatic Storage Management file_type_tag

Comments

CONTROLFILE

Control files and backup control files

Current

Backup

--

DATAFILE

Datafiles and datafile copies

tsname

Tablespace into which the file is added

ONLINELOG

Online logs

group_group#

--

ARCHIVELOG

Archive logs

thread_thread#_seq_sequence#

--

TEMPFILE

Tempfiles

tsname

Tablespace into which the file is added

BACKUPSET

Datafile and archive log backup pieces; datafile incremental backup pieces

hasspfile_timestamp

hasspfile can take one of two values: s indicates that the backup set includes the spfile; n indicates that the backup set does not include the spfile.

PARAMETERFILE

Persistent parameter files

spfile

 

DAATAGUARDCONFIG

Data Guard configuration file

db_unique_name

Data Guard tries to use the service provider name if it is set. Otherwise the tag defaults to DRCname.

FLASHBACK

Flashback logs

log_log#

--

CHANGETRACKING

Block change tracking data

ctf

Used during incremental backups

DUMPSET

Data Pump dumpset

user_obj#_file#

Dump set files encode the user name, the job number that created the dump set, and the file number as part of the tag.

XTRANSPORT

Datafile convert

tsname

--

AUTOBACKUP

Automatic backup files

hasspfile_timestamp

hasspfile can take one of two values: s indicates that the backup set includes the spfile; n indicates that the backup set does not include the spfile.


 

9Oracle ASM and Multi-Pathing Technologies

       下载地址:http://download.csdn.net/source/3206079

 

 

 Automatic Storage Management (ASM) Installation:

 

这里只看下Linux 平台下的安装说明:

 

 Oracle Database Installation Guide 10g Release 2 (10.2) for Linux x86-64: 1.5.1.2 Automatic Storage Management
 Oracle Database Installation Guide 11g Release 1 (11.1) for Linux: 3.6 Installing Automatic Storage Management
 Oracle Database Installation Guide 11g Release 2 (11.2) for Linux: 3.5 Automatic Storage Management Installation Considerations

 

其他文档:

 

1ASM 11gR2 Grid Infrastructure Deinstallation(CSS+OHAS+ASM)

       http://download.csdn.net/source/3207040

 

2ASM 11gR1 Installation & Configuration

       http://download.csdn.net/source/3207095

 

3ASM 11gR2 Installation & Configuration

       http://download.csdn.net/source/3207158

 

4Upgrade ASM to 11.2

       http://download.csdn.net/source/3207238

 

5Oracle 11g create groups and users for Grid and DB

       http://download.csdn.net/source/3207253

 

6ASM_Install_LINUX_Doc

       http://download.csdn.net/source/3208327

 

7How to cleanup ASM installation (RAC and Non-RAC) [ID 311350.1]

       The outline of the steps involved are :

       a)  Backup all the ASM client database files stored on the diskgroups.
       b)  Dropping all the diskgroups.
       c)  Removing ASM resource from CRS (* RAC specific)
       d)  Removing ASM disk signature (In case of asmlib)
       e)  Remove the ASM pfile/spfile.
       f)  Removing ASM entry in the file oratab
       g)  Wipe out the disks header using dd

 

Following are the steps to be followed:
       1)  Log into the ASM instance and do 'select * from v$asm_client;'
       2)  For each instance listed above, stop the respective databases.
       3)  Backup all the datafiles, logfiles, controlfiles, archive logs, etc. that are currently using ASM storage, to tape or to filesystem (using RMAN). This needs to be done for every database (ASM client) using ASM.

       ** NOTE: Please make sure you have the data secure before continuing to the next step.

       4)  Find all the diskgroups: 'select * from v$asm_diskgroup'
       5)  For each diskgroup listed above:
              ' drop diskgroup including contents'
       6)  Shutdown all(RAC nodes) ASM instances.

       7)  On RAC install verify that all asm instances are stopped
              $ORA_CRS_HOME/bin/crst_stat |more <- look for ASM resources and make sure the target=offline

       8)  For single instance install run the following script.:
              $ORACLE_HOME/bin/localconfig  delete

              * This cleans up the CSSD configuration.

       9)  Invoke OUI, and now de-install the ASM Oracle home.

       10)  For RAC install, remove the asm related resource.
              srvctl remove asm -n <- Peform. for all nodes of a RAC cluster
              crs_stat |more <- make sure no asm resources exists

       11)  If using asmlib (on Linux only), then
              a. oracleasm listdisks
              b. oracleasm deletedisks (do this for every disk listed above)
              c. oracleasm listdisks (to verify they have been deleted)
              d. on other RAC nodes: oracleasm listdisks (to verify they have been deleted too)
              e. On all nodes(RAC) : 

       As root run:
              # /etc/init.d/oracleasm stop
              # /etc/init.d/oracleasm disable

       12)  delete the ASM pfile or spfile

       13)  in the file oratab, remove the line relative to the ASM instance

       14)  clean out the disks headers using the dd command:

              for example: dd if=/dev/zero f=/dev/ bs=1024k count=50

       15)  Finally clean out the possible remaining socket files as explained in the following note:

Listener Fails To Start With IPC Permission Errors (TNS-12546 TNS-12555 TNS-00516 TNS-00525) (Doc ID 434062.1)

 

 

.  Automatic Storage Management (ASM) Configuration:

 

1How To Create An ASM Diskgroup Using XML code Thru ASMCMD [ID 1290550.1]

Oracle Server - Enterprise Edition Version: 11.2.0.1 to 11.2.0.2 - Release: 11.2 to 11.2

 

步骤如下:

 

1)  Create a XML file (e.g. diskgroup.xml) with the next code:













2) Then execute the XML script. thru ASMCMD as follow:

[grid@dbaasm ~]$ asmcmd mkdg diskgroup.xml



3) Validate the diskgroup was created:

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 1 17:59:37 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option

SQL> select name, state, type from v$asm_diskgroup;

NAME STATE TYPE
------------------------------ ----------- ------
ACFSDG MOUNTED EXTERN
DATA MOUNTED NORMAL
DATADG MOUNTED NORMAL  <(= Here
RECO MOUNTED NORMAL

 

2ASM Using OS Files Instead of Real Raw Devices On Windows. [ID 602620.1]

       Windows 平台下,使用OS files 代替raw devices,步骤如下:

 

1)    If the ASM instance was not created, then create an ASM instance thru the DBCA .

2)   Update the initialization parameter file with the next hidden parameter:

Connected to: 
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production 
With the Partitioning, Oracle Label Security, OLAP and Data Mining options 

INSTANCE_NAME 
---------------- 
+asm 

SQL> alter system set "_asm_allow_only_raw_disks"=false scope=spfile; 
System altered.

 

3)    If you have real raw devices you will see them after create the ASM instance:

SQL> select path from v$asm_disk; 

PATH 
-------------------------------------------------------------------------------- 
//./ORCLDISKDG0 
//./ORCLDISKDG1 
//./ORCLDISKDATA0 
//./ORCLDISKDATA1 
//./ORCLDISKDATA2 
//./ORCLDISKDATA3 
//./ORCLDISKFRA0 
//./ORCLDISKFRA1 

8 rows selected.

4)  Create the new directory to allocate the fake files/disks:

SQL> host mkdir d:/asmfake

 

5)  Update the ASM instance with the new path associated with the fake directory:

SQL> alter system set asm_diskstring='//./ORCLDISK*','d:/asmfake/*' scope=both; 
System altered.

 

6)  Shutdown and startup the ASM instance to apply the changes:

SQL> shutdown 
ASM diskgroups dismounted 
ASM instance shutdown 

SQL> startup 
ASM instance started 

Total System Global Area   83886080 bytes 
Fixed Size                  1289028 bytes 
Variable Size              57431228 bytes 
ASM Cache                  25165824 bytes 
ASM diskgroups mounted

SQL> show parameter asm 

NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
_asm_allow_only_raw_disks            boolean     FALSE 
asm_diskgroups                       string      DATADG, FRADG 
asm_diskstring                       string      //./ORCLDISK*, d:/asmfake/* 
asm_power_limit                      integer     1

 

7)   In order to create fake files to be used as ASM disks you will need to create them thru the dd command, therefore please install the dd command for windows, you can download it from:

http://www.chrysocome.net/dd

 

8)  Then create the desired fake disks/files on the fake directory:

SQL> host dd if=/dev/zero of=d:/asmfake/fakeasm1 bs=1k count=1000000 

1000000+0 records in 
1000000+0 records out 

SQL> host dd if=/dev/zero of=d:/asmfake/fakeasm2 bs=1k count=1000000 

1000000+0 records in 
1000000+0 records out 

SQL> host dd if=/dev/zero of=d:/asmfake/fakeasm3 bs=1k count=1000000 

1000000+0 records in 
1000000+0 records out 

SQL> host dd if=/dev/zero of=d:/asmfake/fakeasm4 bs=1k count=1000000 

1000000+0 records in 
1000000+0 records out 


SQL> host dir d:/asmfake/* 
 Volume in drive D is New Volume 
 Volume Serial Number is 94B6-1FC7 

 Directory of d:/asmfake 

06/11/2008  03:00 PM    

          . 
06/11/2008  03:00 PM    
          .. 
06/11/2008  02:58 PM     1,024,000,000 fakeasm1 
06/11/2008  02:59 PM     1,024,000,000 fakeasm2 
06/11/2008  03:00 PM     1,024,000,000 fakeasm3 
06/11/2008  03:01 PM     1,024,000,000 fakeasm4 
               4 File(s)  4,096,000,000 bytes 
               2 Dir(s)  19,148,734,464 bytes free

9)  Connect to the ASM instance and make sure the fake disks are visible:

SQL> select path from v$asm_disk; 

PATH 
---------------------------------------------------------- 
D:/ASMFAKE/FAKEASM1 
D:/ASMFAKE/FAKEASM2 
D:/ASMFAKE/FAKEASM3 
D:/ASMFAKE/FAKEASM4 
//./ORCLDISKDG0 
//./ORCLDISKDG1 
//./ORCLDISKDATA0 
//./ORCLDISKDATA1 
//./ORCLDISKDATA2 
//./ORCLDISKDATA3 
//./ORCLDISKFRA0 
//./ORCLDISKFRA1 

12 rows selected.

 

10)  Then create diskgroup(s) using the fake disks:

SQL> create diskgroup TEST external redundancy disk 'D:/ASMFAKE/FAKEASM1', 
  2  'D:/ASMFAKE/FAKEASM2', 
  3  'D:/ASMFAKE/FAKEASM3', 
  4  'D:/ASMFAKE/FAKEASM4'; 

Diskgroup created.

SQL> select name, state from v$asm_diskgroup; 

NAME                           STATE 
------------------------------ ----------- 
DATADG                         MOUNTED 
FRADG                          MOUNTED 
TEST                           MOUNTED

SQL> select name, path from v$asm_disk where group_number =3; 

NAME                           PATH 
------------------------------ ---------------------------------- 
TEST_0000                      D:/ASMFAKE/FAKEASM1 
TEST_0001                      D:/ASMFAKE/FAKEASM2 
TEST_0002                      D:/ASMFAKE/FAKEASM3 
TEST_0003                      D:/ASMFAKE/FAKEASM4

 

11)  Now your new diskgroup was created using OS files.

 

3DBCA in Silent mode with ASM [ID 744237.1]

       To use  DBCA in silent mode and you want the database stored in the ASM +DGDATA diskgroup follow the next Correct Format for Examples:


       If I already have an ASM instance/diskgroup then the following creates a single database on that diskgroup:

 

       $ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName ORCL -sid ORCL -SysPassword change_on_install -SystemPassword manager -emConfiguration NONE -storageType ASM -asmSysPassword change_on_install -diskGroupName DGDATA -characterSet WE8ISO8859P15 -memoryPercentage 40

 

4Deployment of very large databases (10TB to PB range) with Automatic Storage Management (ASM) [ID 368055.1]

       PURPOSE

-------

Support efficient deployment of very large databases (10TB to PB range) with Automatic Storage Management (ASM) in Oracle Database 10g.

 

 

SCOPE & APPLICATION

-------------------

This procedure applies to ASM configurations on all platforms including RAC and single instance environments. This procedure is suitable for the creation of large databases that are expected to grow to over ~10TB in capacity.

 

Challenge

---------

ASM metadata storage requirements for databases greater than 10TB can be very high introducing inefficiencies in opening ASM filesas well as increasing the memory used to store ASM metadata. This is a common problem for all file systems in general. However, ASM can be configured to address this issue effectively through ASM parameters discussed later in this note.

 

Solution

--------

       The ASM feature in Oracle Database 10g defines an Allocation Unit (AU) as the fundamental unit of allocation within a diskgroup. By default, the ASM Allocation Unit size is set to 1MB.

 

       ASM Data Extents are the raw storage used to hold the content of an ASM file. In Oracle Database 10g, each data extent is a single Allocation Unit. Because of this one to one mapping of an extent size to an AU, an ASM file extent map can grow to gigabytes in very large databases creating inefficiencies in memory usage and file open performance.

 

       ASM provides two hidden initialization parameter that allow you to create a disk group with an AU size of 16 megabytes and FINE grain striping at 1MB (instead of 128KB). This reduces the extent map size by a factor of 16. The AU parameter can only be used at disk group creation time. You cannot change the AU size of an existing disk group after it’s been created.

 

       You must reset the following ASM hidden parameters in order to change the AU

size (and therefore Data Extents) from 1MB to 16MB, and FINE grain stripe size to 1MB. The ASM instance must be shut down, _asm_ausize and _asm_stripesize  parameters must be reset in the ASM instance init.ora parameter file and ASM

instance restarted again to inherit the new values.

 

       _asm_ausize=16777216

       _asm_stripesize=1048576

 

       You must also modify the ASM file templates and set the stripe type to ‘FINE’ for all file types to achieve 1MB striping. This must be done after the disk group is created.

 

       The ASM instance must be re-started for the new parameters to take effect. After setting the underscore parameters and restarting the ASM instance, any disk group that gets created will have the new AU size and FINE stripe size. To create a disk group with the normal 1MB AU size and 128KB FINE stripe size, the underscore parameters need to be removed and ASM instance restarted again.

 

Example:

       The following example creates a diskgroup with 16MB Allocation Unit (Data Extents) and allows for 1MB FINE striping for all data files:

 

1. Shut down the ASM instance

 

2. Edit the ASM init.ora parameter file and add:

   _asm_ausize=16777216

   _asm_stripesize=1048576

 

3. Restart the ASM instance

 

4. Create a disk group

   CREATE DISKGROUP diskgroup_name disk '/devices/diska1','/devices/diska2';

 

5. Change all ASM file templates to FINE grained:

   ALTER DISKGROUP diskgroup_name ALTER TEMPLATE ATTRIBUTES (FINE);

   Repeat this command for all ASM file types. Attributes types are listed      below:

   CONTROLFILE, DATAFILE, ONLINELOG, ARCHIVELOG, TEMPFILE, BACKUPSET,PARAMETERFILE, DATAGUARDCONFIG, FLASHBACK, CHANGETRACKING, DUMPSET,  XTRANSPORT, AUTOBACKUP

 

       The 16MB AU setting with 1MB FINE grain striping continues to provide efficient 1MB striping of data as well as much lower ASM file extent overhead. This

configuration provides faster ASM file opens and supports 10TB to PB capacity range ASM databases more effectively.

 

 

 

Note:

Apply the fix for Bug 5100163 in 10.2.0.4

 

       This patch should be applied if you have created ASM Diskgroups with the AU size greater than 1MB and have fewer than 3585 user files.

 

The following command must be ran on each Diskgroup after applying the patch:

       ALTER DISKGROUP CHECK ALL REPAIR

 

REFERENCES

----------

N/A

 

5How do you setup / remove / modify Cluster Synchronization Services Daemon (CSSD) on a non-RAC system (init.cssd)?

       How do you setup / remove / modify Cluster Synchronization Services Daemon (CSSD) on a non-RAC system (init.cssd)?



Each of the LOCALCONFIG operations need to be run as ROOT
$ORACLE_HOME refers to the home used by ASM

 


SETUP / ADD 
===========

       If the CSS is missing either due to specifically excluding it at install time ... or it was later removed 

    $ORACLE_HOME/bin/localconfig add (for Unix) 
        or 
    :/bin/localconfig add (for Windows)

 

 

REMOVE / DELETE 
===============
If you wish to remove the CSS 

1)  Shutdown all ASM / RDBMS instances using ASM 

2)  $ORACLE_HOME/bin/localconfig delete (for Unix) 
       or 
     :/bin/localconfig delete (for Windows)

*** Please note ... this will disable any Automatic Storage Mangement (ASM) instances / databases ... and will prevent you from using ASM in the future



MODIFY / RESET 
=============

       If you wish the CSS to use a new Oracle Home ... for example if you have now installed a separate Oracle Home for ASM 

1)   Shutdown all ASM / RDBMS instances using ASM 

2)   $ORACLE_HOME/bin/localconfig reset (for Unix) 
       or 
    :/bin/localconfig reset (for Windows) 

 

6How To Automate ASM Startup and Shutdown on AIX 5L [ID 580133.1]

        Oracle recommends that you configure your system to automatically to start Oracle ASM instances and Database Instances when the system starts up, and to automatically shut them down when the system shuts down. Automating ASM/Database startup and shutdown guards against incorrect database shutdown. 


       The present document described how to Automate ASM Startup and Shutdown on AIX 5L.

 

 

步骤如下:

The first step is to create the dbora shell script. and place it in the /etc/init.d directory: 

1)  Log in as the root user. 

2)  Edit the oratab file: 

# vi /etc/oratab 

3)  Update the oratab with the next entry:

+ASM::Y

Example:

+ASM:/u02/app/oracle/11g:Y

 

4)  Change to the /etc directory for AIX:

# cd /etc

 

5)  Create a file called dbora:

# touch /etc/dbora

 

6)  Copy the following lines into this file:

# Note: Change the value of the ORACLE_HOME environment variable to your ASM  
# Oracle home directory for the installation. Change the value of the ORACLE  
# environment variable to the user name of the owner of the database installed in the  
# Oracle home directory (typically, oracle). 


#! /bin/sh  -x 

# Change the value of ORACLE_HOME to specify the correct Oracle home 
# directory for your installation. 

ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 

# Change the value of ORACLE to the login name of the 
# oracle owner at your site. 

ORACLE=oracle 

PATH=${PATH}:$ORACLE_HOME/bin 
HOST=`hostname` 
PLATFORM=`uname` 
export ORACLE_HOME PATH 

if [ ! "$2" = "ORA_DB" ] ; then 
   if [ "$PLATFORM" = "HP-UX" ] ; then 
      remsh $HOST -l $ORACLE -n "$0 $1 ORA_DB" 
      exit 
   else 
      rsh $HOST -l $ORACLE  $0 $1 ORA_DB 
      exit 
   fi 
fi 

case $1 in 
'start') 
        $ORACLE_HOME/bin/dbstart $ORACLE_HOME & 
        ;; 
'stop') 
        $ORACLE_HOME/bin/dbshut $ORACLE_HOME & 
        ;; 
*) 
        echo "usage: $0 {start|stop}" 
        exit 
        ;; 
esac 

exit

 

7)  Change the group of the dbora file to the OSDBA group (typically dba), and set the permissions to 750:

# chgrp dba dbora 
# chmod 750 dbora

 

8)  For ASM instances, the /etc/dbora script. cannot be used inside an rc*.d directory and it needs to be invoked from rc.local only. Otherwise, the CSS service may not be available yet, and this script. will block init from completing the boot cycle. Therefore please perform. the next steps to create the /etc/rc.local file:

# mkitab -i rcnfs "rclocal:2:wait:/etc/rc.local >/dev/console 2>&1" 
# touch /etc/rc.local 
# chmod 700 /etc/rc.local

 

9)  Then update the /etc/rc.local file with the next line:

/etc/dbora &

 

7How to Change ASM Home on a Node in RAC

       How to Change ASM Home on a Node in RAC

 

步骤如下:

1) Install a new Home to be used by ASM

 

2 Stop the listener, database, asm on the node

 

3) Listener is recommended to be run from asm home. We can reset the new home using the following command:

       $ srvctl modify listener -n -l -o

 

4Reset the ASM home with new value

       $ srvctl modify asm -n node1 -i -o

 

5  If TNS_ADMIN variable was used to point to ASM Home to find the listener, then it needs to be changed to the new ASM Home in OCR for listener and nodeapps.        Refer MOS DocID 420977.1 and 360575.1). Updating nodeapps via setenv must be done as root starting from 10.2.0.4, refer MOS DocID 779907.1.

 

6 We need to manually:

(1).      move the (s)pfile and password file into the new home

(2).      copy listener.ora/tnsnames.ora from old home into new home

(3).      update /etc/oratab or /var/opt/oracle/oratab with the new home

(4).      If dbcontrol/emcontrol is there for ASM then needs to be REconfigured. As at many places in the dbcontrol/emcontrol configuration files we hardcode the Oracle home

 

7 If you are on Windows then you need to delete the old ASM instance using oradim and then create the new one:

(1).      Stop the Windows Service, 
e.g OracleASMService+ASM1

(2).      From command prompt 
oradim -delete -asmsid +ASM1

(3).      Set the New Oracle home/bin in the PATH so that you are running 'oradim' from the new home.
From command prompt 
oradim -new -asmsid +ASM1 -intpwd xxx -m -pfile xxxx

 

8 This can be done on other nodes if required

 

8How to configure dbconsole to display information from the ASM DISKGROUPS [ID 329581.1]

       Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.4

 

       This article will show how to display in dbconsole, information for the ASM instance used by the database.  This is particularly usefull if:

(1).      The database and ASM instance were manually created

(2).      The database was originally  created through dbca and it was configured to be managed thorugh dbconsole and the files were stored on filesystem.  In a subsequent step, the files were stored in an ASM diskgroup.

 

 

相关命令如下:

1In 10G Release 1

       emca -r -a

 

2In 10G Release 2

       emca -config all db -repos recreate

 

       This is an example of the execution of the previous command.  The config all option is required for an existant database that is using ASM.  This option will delete the current information stored into the repository.

 

Enter the following information:
Database SID: P10R2      
Central agent home: /oradata2/102b       ## This is the path for the ORACLE_HOME

# The following message is returned only if there is a configuration created for some
# database.  

The database P10R2 is already being monitored by central agent(s)
Database Control is already configured for the database P10R2
You have chosen the central agent /oradata2/102b for monitoring the database P10
R2
You have chosen to configure Database Control for managing the database P10R2
Do you wish to continue? [yes(Y)/no(N)]: Y
Listener port number: 1521               ## Listener port #
Password for SYS user:  r
Password for DBSNMP user:  a
Password for SYSMAN user:  r
Email address for notifications (optional): 
Outgoing Mail (SMTP) server for notifications (optional): 
ASM ORACLE_HOME [ /oradata2/102b ]: 
ASM SID [ +ASM ]: 
ASM port [ 1521 ]: 
ASM user role [ SYSDBA ]: 
ASM username [ SYS ]: 
ASM user password:  a

 

9How to configure local_listener parameter with ASMCA [ID 1112993.1]

       Oracle Server - Enterprise Edition - Version: 11.2.0.0 and later   [Release: 11.2 and later ]

 

       Oracle ASM Configuration Assistant (ASMCA) can be used in silent command-line mode. 
       ASMCA supports only alias-based (net service name based) configuration for local_listener parameter. 
       This article demonstrates to configure local_listener parameter with ASMCA in silent mode.

 

步骤如下:

       ASMCA supports only alias-based (net service name based) configuration for local_listener parameter. 
       If you configure local_listener parameter with the network address, ASMCA fails as follows. 


   $ asmca -silent -configureParameter -param LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=port number))' 

   Invalid value passed for -param option: Expected format 
   = 



1. open tnsnames.ora with text editor, and prepare net service names to be used for local_listener parameter. 

   net_service_name = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = port number)) 


2. run ASMCA as follows 

   $ asmca -silent -configureParameter -param LOCAL_LISTENER=net_service_name 

   Parameter LOCAL_LISTENER configured successfully.

 

 

参考:

BUG:9714623 - ASMCA DOES NOT UNDERSTAND THE VALUE '(ADDRESS=(PROTOCOL...)' FOR LOCAL_LISTENER

 

10How to configure XDB for using ftp and http protocols with ASM [ID 357714.1]

       Configuration of XDBXML Database to be able to use ftp and http protocols to browse and manage ASM directories    

 

       Xdb configuration enable the possibility to use FTP from an ftp session on unix or through a browser on Windows.

       Files can be easily moved in/out from ASM this wayIt also provides an http interface to easily browse through ASM directories in a graphic environment.

 

之前的测试文档:

       设置 FTP / HTTP 访问 RAC ASM 磁盘组

       http://blog.csdn.net/tianlesoftware/archive/2010/12/06/6057706.aspx

 

 

步骤如下:

1)  As root check that ftp service is running:

       # netstat -a | grep ftp
       tcp        0      0 *:ftp  *:*  LISTEN

 

       If no output is returned, start ftp:

       # service vsftpd start
       Starting vsftpd for vsftpd:        [  OK  ]

 

      Also configure ftp to start automatically

       # chkconfig vsftpd on

 

2)  Configure the FTP and HTTP ports of XDB using:

       connect / as sysdba
       execute dbms_xdb.sethttpport(8080);
       execute dbms_xdb.setftpport(2100);
       commit;

 

       to check use:
       select dbms_xdb.GETFTPPORT() from dual;
       select dbms_xdb.GETHTTPPORT() from dual;

 

3)  Check the dispatchers configuration for xdb, if it is not set set it up,

 

for single instance:  

       ALTER SYSTEM SET dispatchers = =(PROTOCOL=TCP) (SERVICE=XDB)" SCOPE=BOTH

 

  For RAC instances:

       ALTER SYSTEM SET siebprod1.dispatchers = "(PROTOCOL=TCP) (SERVICE=XDB)" SCOPE=BOTH

       ALTER SYSTEM SET siebprod2.dispatchers = "(PROTOCOL=TCP) (SERVICE=XDB)" SCOPE=BOTH         

   

   If you are not using the default Listener ensure you have set LOCAL_LISTENER in the (init.ora/spfile) as prescribed for RAC/NON-RAC instances or the end points will not register.

 

4) Restart the listener:       

       lsnrctl stop
       lsnrctl start

 

5)  Check that the following lines are returned when executing lsnrctl, if they are not you may need to restart your database.

       (DESCRIPTION =(ADDRESS = (PROTOCOL = tcp)(HOST = )(PORT = 2100))(Presentation = FTP)(Session = RAW))

       (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = )(PORT = 8080))(Presentation = HTTP)(Session = RAW))

 

6)  Connect to the ftp as follows :

       OS> ftp -n
       open 2100
       user system
       cd sys
       cd asm
       ...
       ...

 

7)  From browser : follow the below

       for FTP type the url :--- ftp://:2100/
       for HTTP type the url :-- http://:8080/

       Enter the user and password as SYSTEM and

 

8) Troubleshooting:

       If you do not succeed to connect to ftp then  :

 

       1. Check for any invalid XDB owned objects:

        SQL> select count(*) from dba_objects

        where wner='XDB' and status='INVALID';

        COUNT(*)

        ----------

         0

 

2.  Check DBA_REGISTRY for XDB status:

        SQL>  select comp_name, status, version from DBA_REGISTRY where comp_name='Oracle XML Database'

          The results should indicate the correct version and patch in a valid status.

 

3.  Restart database and listener to enable Oracle XML DB protocol access.


11How To Create ASM Diskgroups using NFS/NAS Files

        ASM is completely supported on NAS (e.g. NFS filesystem on NetApps).

 

步骤如下:
1)  As Oracle OS user, create the disk devices on your NFS directory:

oracle>dd if=/dev/zero f=/oracle/asm/disks/nfsdisk2 bs=8192 count=100000 
100000+0 records in 
100000+0 records out 


oracle>dd if=/dev/zero f=/oracle/asm/disks/nfsdisk1 bs=8192 count=100000 
100000+0 records in 
100000+0 records out 


oracle>ls -l 
total 3201600 
-rw-r--r-- 1 oracle dba 819200000 Aug 11 14:10 nfsdisk1 
-rw-r--r-- 1 oracle dba 819200000 Aug 11 14:09 nfsdisk2



2)  Connect to your ASM instance and set the NFS directory in the discovery path: 

SQL> alter system set asm_diskstring = '/oracle/asm/disks/*' scope=both; 

System altered. 

SQL> show parameter asm 

NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 
asm_diskgroups string 
asm_diskstring string /oracle/asm/disks/* 
asm_power_limit integer 11


Note: If spfile is not being used then you will need to update the pfile (init+ASM.ora) with the next values to preserve the changes during the shutdown/startup: 

asm_diskstring = '/oracle/asm/disks/*' 

Where: “/oracle/asm/disks/*” is the NFS directory associated with the NAS disk devices.


3)  Verify the NFS disks are now discovered thru the v$asm_disk view:

SQL> select path from v$asm_disk; 

PATH 
---------------------------------------------------- 
/oracle/asm/disks/nfsdisk1 
/oracle/asm/disks/nfsdisk2



4)  Finally, create the diskgroup on the NFS disks:

SQL> create diskgroup test external redundancy disk 
'/oracle/asm/disks/nfsdisk1', 
'/oracle/asm/disks/nfsdisk2'; 

Diskgroup created.


12How To Determinate If An EMCPOWER Partition Is Valid For ASMLIB

       If you are trying to create an ASMLIB disk using an emcpower# partition and you get the next error:

       ASM disk: asmtool: Device "/dev/emcpowera11" is not a partition [FAILED]

 

       Then you will need to determinate if the emcpower# partition is valid.

 

 

解决步骤如下:

In order to confirm the emcpower# partition is valid: 

1)  Check if you can create a tablespace directly on the block partition:

 

[root@dbaasm ~]# cd /dev 
[root@dbaasm dev]# ls -l emcpowerd1 
brw-rw----  1 root disk 3, 19 Apr 29 16:00 emcpowerd1 
[root@dbaasm dev]# chmod 777 emcpowerd1 
[root@dbaasm dev]# ls -l emcpowerd1 
brwxrwxrwx  1 root disk 3, 19 Apr 29 16:00 emcpowerd1 
[root@dbaasm dev]# exit

 

dbaasm.us.oracle.com:/u01/app/oracle/11.1/db:TEST:oracle>sqlplus “/as  
sysdba” 

SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 1 09:09:02 2008 

Copyright (c) 1982, 2007, Oracle.  All rights reserved. 


Connected to: 
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options 

SQL> create tablespace test datafile '/dev/emcpowerd1' size 100M; 

Tablespace created.

 

2)  If so, then drop the previous tablespace.

 

3)  Then check if a diskgroup can be created directly on the block partition:

Connect as root and execute: 

# dd if=/dev/zero of='/dev/emcpowerb1' bs=8192 count=12800

Then connect as oracle, and then connect to the ASM instances and execute:


SQL> alter system set asm_diskstring = '/dev/emcpowerb1' scope=memory; 

SQL> create diskgroup TEST external redundancy disk  '/dev/emcpowerb1';
Diskgroup created. 

 

4)  If the tablespace and diskgroup can be create directly on the block partition then this could be a problem with ASMLIB configuration. Therefore you will need to check if the correct ASMLIB rpms were installed.

5) Alternatively, you can check if the problem is reproducible on another emcpower# partition or ask the SA to recreate the original one.

 

13How to Prepare Storage for ASM

       This document describes how to prepare your storage sub-system before you configure Automatic Storage Management (ASM). When preparing your storage to use ASM, first determine the storage option for your system and then prepare the disk storage for the specific operating system environment.

 

A) You can create an ASM diskgroup using one of the following storage resources:
1) Raw disk partition—A raw partition can be the entire disk drive or a section of a disk drive.    However, the ASM disk cannot be in a partition that includes the partition table because the partition table can be overwritten.

2) Logical unit numbers (LUNs)—Using hardware RAID functionality to create LUNs is a recommended approach. Storage hardware RAID 0+1 or RAID5, and other RAID configurations, can be provided to ASM as ASM disks.

3) Raw logical volumes (LVM)—LVMs are supported in less complicated configurations where an LVM is mapped to a LUN, or an LVM uses disks or raw partitions. LVM configurations are not recommended by Oracle because they create a duplication of functionality. Oracle also does not recommended using LVMs for mirroring because ASM already provides mirroring.

4)  NFS files—NFS files are suitable for testing, but are not a recommended configuration for production environments. Using NFS files with ASM duplicates ASM functionality. Though, NetApp as an NFS vendor certifies its product with ASM. So there are customers using NFS and ASM together.

 

B) The procedures for preparing storage resources for ASM are:

1) Identify or create the storage devices for ASM by identifying all of the storage resource device names that you can use to create an ASM disk group. For example, on Linux systems, device names are typically presented from the /dev directory with the /dev/device_name_identifier name syntax.

2) Change the ownership and the permissions on storage device resources. For example, the following steps are required on Linux systems:

2.1) Change the user and group ownership of devices to oracle:dba
2.2) Change the device permissions to read/write
2.3) On older Linux versions, you must configure raw device binding

After you have configured ASM, ensure that disk discovery has been configured correctly by setting the ASM_DISKSTRING initialization parameter.

Note:
 Setting the ownership to oracle:dba is just one example that corresponds to the default settings. A non-default installation may require different settings. In general, the owner of the disk devices should be the same as the owner of the Oracle binary. The group ownership should be OSDBA of the ASM instance, which is defined at installation.


C) Recommendations for Storage Preparation. The following are guidelines for preparing storage for use with ASM:

1) Configure two disk groups, one for the datafile and the other for the Flash Recovery Area. For availability purposes, one is used as a backup for the other.

2) Ensure that LUNs, which are disk drives of partitions, that ASM disk groups use have similar storage performance and availability characteristics. In storage configurations with mixed speed drives, such as 10K and 15K RPM, I/O distribution is constrained by the slowest speed drive.

3) Be aware that ASM data distribution policy is capacity-based. LUNs provided to ASM have the same capacity for each disk group to avoid an imbalance.

4) Use the storage array hardware RAID 1 mirroring protection when possible to reduce the mirroring overhead on the server. Use ASM mirroring redundancy in the absence of a hardware RAID, or when you need host-based volume management functionality, such as mirroring across storage systems. You can use ASM mirroring in configurations when mirroring between geographically-separated sites over a storage interface.

Hardware RAID 1 in some lower-cost storage products is inefficient and degrades the performance of the array. ASM redundancy delivers improved performance in lower-cost storage products.

5) Maximize the number of disks in a disk group for maximum data distribution and higher I/O bandwidth.

6) Create LUNs using the outside half of disk drives for higher performance. If possible, use small disks with the highest RPM.

7) Create large LUNs to reduce LUN management overhead.

8) Minimize I/O contention between ASM disks and other applications by dedicating disks to ASM disk groups for those disks that are not shared with other applications.

 

9) Choose a hardware RAID stripe size that is a power of 2 and less than or equal to the size of the ASM allocation unit.

10) Avoid using a Logical Volume Manager (LVM) because an LVM would be redundant. However, thereare situations where certain multipathing or third party cluster solutions require an LVM. In these situations, use the LVM to represent a single LUN without striping or mirroring to minimize the performance impact.

11) For Linux, when possible, use the Oracle ASMLIB feature to address device naming and permission persistency.

12) ASMLIB provides an alternative interface for the ASM-enabled kernel to discover and access block devices. ASMLIB provides storage and operating system vendors the opportunity to supply extended storage-related features. These features provide benefits such as improved performance and greater data integrity.


14How to Re-configure Asm DiskGroup

       One ASM DISK GROUP contains all of the available disks of the server. Need to  re-configure the current DISK GROUP to break it down into three different ASM disk groups.

 

注意事项:

1). A DISK GROUP with single disk is possible only if the redundancy of diskgroup is EXTERNAL.
2). We can n't change the redundancy of a diskgroup once it is created
3). By using external redundancy we are depending on external mechanisms (like RAID) for fault tolerance.



操作步骤:
1)   Check the redundancy of diskgroup and Identify the group number.
2)   Identify the physical disk names.
3)    Drop the identified disks from existing diskgroup.
4)   Create new  diskgroups.

 

具体操作如下:

1)  Check the redundancy of diskgroup and Identify the group number.

ASM:SQL>SELECT GROUP_NUMBER,TYPE FROM V$ASM_DISKGROUP WHERE NAME='DATA';

GROUP_NUMBER   TYPE
------------                -------
1                                EXTERN

(Here EXTERN indicates the Diskgroup DATA is created with redundancy external).


2)  Identify the DISK Name and Path.

ASM:SQL > SELECT NAME,PATH FROM V$ASM_DISK WHERE GROUP_NUMBER=1

NAME                        PATH
-----------                ----------
DATA_0001            /dev/raw/raw1
DATA_0002            /dev/raw/raw2
DATA_0003            /dev/raw/raw3


3)  Drop the identified disks from existing diskgroup.

ASM:SQL> ALTER DISKGROUP DATA DROP DISK DATA_0002
ASM:SQL> ALTER DISKGROUP DATA DROP DISK DATA_0003

4)  Create new two diskgroups.

create diskgroup DATA_A external redundancy disk '/dev/raw/raw1'
create diskgroup DATA_B external redundancy disk '/dev/raw/raw2'

 

       Note: The steps provided here assume that there is a disk group DATA with  external redundancy  contains three disks. This need to be break down to three seperate diskgroups. We depend on external redundancy when the defined disks have some external means  for fault tolerence(eg RAID),ie Oracle will not do any mirroring for these diskgroups


 

.  Automatic Storage Management (ASM) Administration:

 Oracle Database Administrator's Guide 10g Release 2 (10.2) : 12 Using Automatic Storage Management
 Oracle Database Storage Administrator's Guide 11g Release 1 (11.1)
 Oracle Database Storage Administrator's Guide 11g Release 2 (11.2) 


1How v$recovery_file_dest.space_used is calculated from v$asm_file.bytes in ASM

       Oracle Server - Enterprise Edition - Version: 11.1.0.7 and later   [Release: 11.1 and later ]

 

       The purpose of this note is to provide insight on how space allocation ( v$asm_file.space column ) in ASM can be calculated depending on Allocation Unit size, redundancy and striping scheme.


       There will be big space gap for Flashback logs in recovery destination diskgrop as the default redundancy and striping method for Flashback logs are "Mirror" and "High" and the way of how v$recovery_file_dest.space_used is calculated in ASM. As a result of this space gap, recovery destination clean up mechanism sometimes won't kick in even db_recovery_file_dest_size is used up.


       db_recovery_file_dest_size should be calculated carefully by taking into redundancy, striping and AU boundary as it can cause database hung in a situation when archivelog can not be generated. 


This note will be useful - 
1. Calculate the exact size of a file in ASM environment. 
2. Calculate v$recovery_file_dest.space_used for flashback logs and archived log files.

 

注意事项:
       This script. is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it. 

       Proofread this script. before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script. may not be in an executable state when you first receive it. Check over the script. to ensure that errors of this type are corrected. 



ASM space allocation consideration 

       1.    The space of each ASM file is allocated in Allocation Unit boundary - default 1M but can be specified at the creation of a diskgroup with an attribute. 

      2. Each file on ASM is created with a corresponding template either explictly or implictly depending on file type. 
- Check v$asm_template 
- Attribute can be altered. 
SQL> alter diskgroup alter template attributes ( coarse ) ; 


3.  v$asm_file.bytes is calculated from v$asm_file.blocks * v$asm_file.block_size . 

4.  v$asm_file.block_size varies to the file type - online redo - 512 bytes, control file - 16K etc . 

5. v$asm_file.space is calculated by taking into account of AU boundary, redundancy and striping width. See Useful SQL statement - SQL1. 
-  For FINE striping scheme with 128K, striping width is 8 AU (Allocation Units. ) 
- 1st 128K needs 1AU space allocation and 2nd 128K needs another 1AU, subsequently 1M with 128K striping width needs 8AU space allocation. 

6. Big space gap between v$asm_file.bytes and v$asm_file.space can be seen if an ASM file is created with a combination of FINE striping and normal or high redundancy. 

7. In Oracle instance side, v$recovery_file_dest.space_used only takes into account of AU boundary of v$asm_file.bytes, doesn't take into striping and redundancy. 
- See Useful SQL satement - SQL2. 
- This is mainly because v$asm_file.space does not mean all space in these extent is full. 
- v$recovery_file_dest.space_used = sum(ceil (( v$asm_file.bytes + 1) / AU ) * AU ) 

8. But recovery destination cleanup mechanism will kick in by the usage of based on v$asm_file.space. 



Useful SQL statement 

SQL1
 How v$asm_file.space is calculated from v$asm_file.bytes depending on striping and redundancy scheme of a diskgroup.


- Please note that space allocation on ASM diskgroup is based on v$asm_file.space, not from v$asm_file.bytes.


------------------------- 
set linesize 140 

col g_n format 999 
col f_n format 9999 
col bytes format 999,999,999,999 
col space format 999,999,999,999 
col a_i format 999 
col blocks format 999,999 
col block_size format 999,999 
col ftype format a16 
col dir format a3 
col s_created format a10 
col full_alias_path format a40 
col striped format a6 
col rdun format a6 
col au format 99999999 
col calculated format 999,999,999,999 

select x.gnum g_n, 
x.filnum f_n, 
substr(x.full_alias_path,1, 40) full_alias_path, 
f.striped, 
f.redundancy rdun, 
f.bytes, 
f.space, 
case when calculated / x.au > 60 then calculated + 3 * x.au -- Over 60 direct extents consideration 
else calculated 
end calculated 
from ( SELECT gnum,filnum,au, concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path 
from ( SELECT g.name gname, 
g.allocation_unit_size au, 
a.parent_index pindex, 
a.name aname, 
a.reference_index rindex, 
a.group_number gnum, 
a.file_number filnum 
FROM v$asm_alias a, v$asm_diskgroup g 
WHERE a.group_number = g.group_number) 
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex) x, 
(select f.group_number gnum, 
f.file_number filnum, 
f.type ftype , 
f.bytes, 
f.space, 
f.blocks, 
f.block_size, 
f.striped, 
f.redundancy, 
case f.striped when 'FINE' 
then ceil(((f.blocks * f.block_size + 1) / g.allocation_unit_size) / 8) 
* decode(f.redundancy, 'HIGH', 3 , 'MIRROR', 2, 'UNPROT', 1 ) * 8 * g.allocation_unit_size 
else ceil((f.blocks * f.block_size + 1) / g.allocation_unit_size) 
* decode(f.redundancy, 'HIGH', 3 , 'MIRROR', 2, 'UNPROT', 1 ) * g.allocation_unit_size 
end calculated 
from v$asm_file f , v$asm_diskgroup g 
where f.group_number = g.group_number 
order by f.group_number,file_number) f 
where x.filnum != 4294967295 
and x.gnum=f.gnum and x.filnum=f.filnum 

------------------------- 

SQL2. How v$recovery_file_dest.space_used is calculated from v$asm_file.bytes. 

------------------------- 
set linesize 140 

col g_n format 999 
col f_n format 9999 
col bytes format 999,999,999,999 
col space format 999,999,999,999 
col a_i format 999 
col blocks format 999,999 
col block_size format 999,999 
col ftype format a16 
col dir format a3 
col s_created format a10 
col full_alias_path format a40 
col striped format a6 
col redundancy format a10 
col au format 99999999 
col size_au format 999,999,999,999 

break on report 
compute sum of size_au on report 

select x.gnum g_n, 
x.filnum f_n, 
substr(x.full_alias_path,1, 40) full_alias_path, 
f.ftype, 
f.bytes, 
ceil((f.bytes + 1 ) / x.au ) * x.au size_au, -- take into account only AU boundary of actual bytes 
f.space, 
f.striped, 
f.redundancy 
from (select gname, gnum,filnum, au, concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path 
from ( select g.name gname, 
g.allocation_unit_size au, 
a.parent_index pindex, 
a.name aname, 
a.reference_index rindex, 
a.group_number gnum, 
a.file_number filnum 
from v$asm_alias a, v$asm_diskgroup g 
where a.group_number = g.group_number) 
start with (mod(pindex, power(2, 24))) = 0 connect by prior rindex = pindex 
) x, 
(select f.group_number gnum, 
f.file_number filnum, 
f.type ftype , 
f.bytes, 
f.space, 
f.redundancy, 
f.striped 
from v$asm_file f 
order by f.group_number,file_number 
) f 
where x.filnum != 4294967295 
and x.gname = '&DB_RECOVERY_FILE_DEST' -- Put the diskgroup name seen in db_recovery_file_dest 
and x.gnum=f.gnum 
and x.filnum=f.filnum 

--------------------------------- 

SQL3. v$asm_template - striping and redundancy information for ASM files. 

--------------------------- 
set linesize 140 
col g_n format 99 
col e_n format 999 
col name format a20 

select group_number g_n, 
entry_number e_n, 
name, 
redundancy, 
stripe, 
system 
from v$asm_template 
order by g_n, e_n 

--------------------------- 

SQL4. SQL for querying v$recovery_file_dest 
--------------------------------- 
set linesize 140 

col name format a20 
col space_limit format 999,999,999,999 
col space_used format 999,999,999,999 
col space_reclaimable format 999,999,999,999 
col number_of_files format 999,999,999,999 

select name, 
space_limit, 
space_used, 
space_reclaimable, 
number_of_files 
from v$recovery_file_dest 
/

 

2ASM Fast Mirror Resync - Example To Simulate Transient Disk Failure And Restore Disk

       Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.1.0 - Release: to 11.2

 

       This note discusses the New 11g ASM feature called ASM Fast Mirror Resync . Also an example is taken to show how this works. We will simulate the transient disk failure and recover the disk before disk repair time.

 

ASM Fast Mirror Resync - Example To Simulate Transient Disk Failure And Restore Disk

 

 

ASM Fast Mirror Resync

       ASM fast resync keeps track of pending changes to extents on an OFFLINE disk during an outage. The extents are resynced when the disk is brought back online or replaced.


       By default, ASM drops a disk shortly after it is taken offline. You can set the DISK_REPAIR_TIME attribute to prevent this operation by specifying a time interval to repair the disk and bring it back online. The default DISK_REPAIR_TIME attribute value of 3.6h should be adequate for most environments.

--  这段话需要注意:默认情况下,当disk take offline 之后, ASM 会很快删除这些disk DISK_REPAIR_TIME 参数可以设定保护时间, 该值默认是3.6小时,即在这个时间内,即使设置为offline,也不会被删除。

 


       The elapsed time (since the disk was set to OFFLINE mode) is incremented only when the disk group containing the offline disks is mounted. The REPAIR_TIMER column of V$ASM_DISK shows the amount of time left (in seconds) before an offline disk is dropped. After the specified time has elapsed, ASM drops the disk.

       You can override this attribute with an ALTER DISKGROUP DISK OFFLINE statement and the DROP AFTER clause.

       If an ALTER DISKGROUP SET ATTRIBUTE DISK_REPAIR_TIME is issued on a disk group that has disks that are currently offline, the new attribute value applies only to those disks that are not currently in OFFLINE mode.

       A disk that is in OFFLINE mode cannot be dropped with an ALTER DISKGROUP DROP DISK statement; an error is returned if attempted. If for some reason the disk needs to be dropped (such as the disk cannot be repaired) before the repair time has expired,a disk can be dropped immediately by issuing a second OFFLINE statement with a DROP AFTER clause specifying 0h or 0m.

       You can use ALTER DISKGROUP to set the DISK_REPAIR_TIME attribute to a specified hour or minute value, such as 4.5 hours or 270 minutes. For example:

alter diskgroup dg set attribute 'disk_repair_time' = '4.5h'
alter diskgroup dg set attribute 'disk_repair_time' = '270m'



       After you repair the disk, run the SQL statement ALTER DISKGROUP DISK ONLINE. This statement brings a repaired disk group back online to enable writes so that no new writes are missed. This statement also starts a procedure to copy of all of the extents that are marked as stale on their redundant copies.

       If a disk goes offline when the ASM instance is in rolling upgrade mode, the disk remains offline until the rolling upgrade has ended and the timer for dropping the disk is stopped until the ASM cluster is out of rolling upgrade mode. See "ASM Rolling Upgrade".

Note: To use this feature, the disk group compatibility attributes must be set to 11.1 or higher.


       Please find below example in which we will simulate the transient disk failure and recover the disk before disk repair time

 

SQL> create diskgroup dgnm11gasm disk '/dev/raw/raw1','/dev/raw/raw2'
attribute 'compatible.rdbms'='11.1','compatible.asm'='11.1';
Diskgroup created.

SQL> select group_number,name from v$asm_diskgroup where group_number=1;

GROUP_NUMBER NAME
------------ --------------------
1 DGNM11GASM

SQL>select name,value from v$asm_attribute where group_number=1;
NAME VALUE
-------------------- --------------------
disk_repair_time 3.6h
au_size 1048576
compatible.asm 11.1.0.0.0
compatible.rdbms 11.1.0.0.0

Default disk repair time is 3.6 hours

Connect to DB Instance

SQL> create tablespace test datafile '+DGNM11GASM' size 20m;
Tablespace created.

Shutdown the DB Instance
Dismount the ASM Diskgroup

SQL> alter diskgroup DGNM11GASM dismount;
Diskgroup altered.

Change the permission of /dev/raw/raw1 to simulate the disk loss

[root@11g ~]# chown root.root /dev/raw/raw1
[root@11g ~]# ls -ltr /dev/raw/raw1
crw-rw---- 1 root root 162, 1 Jul 8 01:47 /dev/raw/raw1

SQL> alter diskgroup dgnm11gasm mount;
alter diskgroup dgnm11gasm mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "0" is missing


       With Oracle Database 11g, ASM will fail to mount a diskgroup if there are any missing disks or failgroups during mount.You need to mount the diskgroup with FORCE option.

       Disk groups mounted with the FORCE option will have one or more disks offline if they were not available at the time of the mount.

 

SQL> alter diskgroup dgnm11gasm mount force;
Diskgroup altered.

SQL>select path,name,repair_timer from v$asm_disk where group_number=1;
PATH NAME REPAIR_TIMER
--------------- -------------------- ------------
DGNM11GASM_0000 12960
/dev/raw/raw2 DGNM11GASM_0001 0


       Disk groups mounted with the FORCE option will have one or more disks offline if they are not available at time of the mount.You must take corrective actions before DISK_REPAIR_TIME expires to restore those devices

Connect to DB Instance and add new datafile to the tablespace.

SQL> alter tablespace test add datafile '+DGNM11GASM' size 20m;
Tablespace altered.


       As there is only one disk available in the diskgroup (Normal redundancy), there will not be any mirror copy until the lost disk is accessible from oracle user and it is onlined using alter diskgroup online/new disk is added to diskgroup

 

chown oracle.dba /dev/raw/raw1
SQL> alter diskgroup dgnm11gasm online disk DGNM11GASM_0000;
Diskgroup altered.

SQL> select group_number,operation,state from v$asm_operation;
GROUP_NUMBER OPERA STAT POWER
---------- ----------------------------
1 ONLIN RUN 1


       ASM fast resync keeps track of pending changes to extents on an OFFLINE disk during an outage. The extents are resynced when the disk is brought back online or replaced.

 

SQL> select path,header_status,mount_status from v$asm_disk where group_number=1; 

PATH HEADER_STATU MOUNT_S 
--------------- ------------ ------- 
/dev/raw/raw2 MEMBER CACHED 
/dev/raw/raw1 MEMBER CACHED

 

       简单的说一下,就是对于有个冗余的disk,当一个disk 损坏之后,2个磁盘会不一致,当这个磁盘被修复之后,oracle ASM Fast Mirror Resync 会自动同步这2个磁盘的数据。

 

 3ASM Fast Rebalance

       This article talks about the 11g new feature - ASM Fast rebalance and how to implement in 11g ASM Instance

 

 

       In 11g, you can use the STARTUP RESTRICT command to control access to an ASM instance while you perform. maintenance. This startup option is used to implement ASM Fast Rebalance feature. Rebalance operations that occur while a disk group is in RESTRICTED mode eliminate the lock and unlock extent map messaging between ASM instances in Oracle RAC environments.

       When an ASM instance is started with 'STARTUP RESTRICT' option and is active in this mode, all of the disk groups are mounted in RESTRICTED mode which prevents databases from connecting to the ASM instance. In addition, the restricted clause of the ALTER SYSTEM statement is disabled for the ASM instance.

Given below is an example to show ASM Fast Rebalance Feature.


Startup ASM instance in Restricted mode

SQL> startup restrict;
ASM instance started
Total System Global Area 92065792 bytes
Fixed Size 1297984 bytes
Variable Size 65601984 bytes
ASM Cache 25165824 bytes


Now in case you wish to mount the Disk group in normal mode , you get following errors

SQL> alter diskgroup dgext11gasm mount;
alter diskgroup dgext11gasm mount
*
ERROR at line 1:
ORA-15251: only restricted mount is allowed in the ASM instance


You need to use restricted clause along with mount option.

 

SQL> alter diskgroup dgext11gasm mount restricted;
Diskgroup altered.

SQL> alter diskgroup dgext11gasm rebalance;
Diskgroup altered.


If ASM diskgroup is mounted in restricted mode,Database Instance will not be able to access files residing in the ASM Diskgroup 

SQL> startup
ORACLE instance started.

Total System Global Area 418484224 bytes
Fixed Size 1300200 bytes
Variable Size 268437784 bytes
Database Buffers 142606336 bytes
Redo Buffers 6139904 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '+DGEXT11GASM/o11g/datafile/test.256.627345731'


       Unlike normal Database Instance, alter system disable restricted session is disabled for ASM Instance.
       You need to shutdown and startup ASM Instance in Normal mode to access files reside in the ASM Diskgroup for Database Instance

 

ASM Instance 
============= 
SQL> alter system disable restricted session; 
alter system disable restricted session 

ERROR at line 1: 
ORA-15000: command disallowed by current instance type 

SQL>shutdown immediate
SQL>startup

 

4ASMCMD - ASM command line utility

       Oracle database 10gR2 provides two new options to access and manage Automatic Storage Management (ASM) files and related information via command line interface - asmcmd and ASM ftp. This article will talk about asmcmd and provide sample Linux shell script. to demonstrate the asmcmd in action.

 

       asmcmd can be used by Oracle database administrators to query and manage their ASM systems. Oracle Support Services engineers can use asmcmd to easily retrieve ASM related info for diagnosing and debugging purposes.

       asmcmd can be used against ASM versions 10gR1 (10.1.0.n) and 10gR2 (10.2.0.n). In ASM version 10.2 asmcmd is provided by default ASM installation.

       To use asmcmd in ASM version 10.1 environment we can just copy relevant files from 10.2 installation into the 10.1 environment as follows.

 

Linux/UNIX:

Copy/ftp asmcmd and asmcmdcore from 10.2 install into 10.1 $ORACLE_HOME/bin , where $ORACLE_HOME is the oracle home where ASM is installed. These two files should be owned by the oracle user and the dba group, and have the following permissions:

asmcmd: 550

asmcmdcore: 440

 

Windows:

Copy/ftp asmcmd.bat and asmcmdcore from 10.2 install into %ORACLE_HOME%/bin, where %ORACLE_HOME% is the oracle home where ASM is installed.

 

ASMCMD - ASM command line utility

Reference summary of asmcmd commands.

cd      Changes the current directory to the specified directory.

du      Displays the total disk space occupied by ASM files in the
        specified ASM directory and all its subdirectories, recursively.

exit    Exits ASMCMD.

find    Lists the paths of all occurrences of the specified name (with
        wildcards) under the specified directory.

help    Displays the syntax and description of ASMCMD commands.

ls      Lists the contents of an ASM directory, the attributes of the
        specified file, or the names and attributes of all disk groups.

lsct    Lists information about current ASM clients.

lsdg    Lists all disk groups and their attributes.

mkalias Creates an alias for a system-generated filename.

mkdir   Creates ASM directory.

pwd     Displays the path of the current ASM directory.

rm      Deletes the specified ASM files or directories.

rmalias Deletes the specified alias, retaining the file that the alias
        points to.
 
一个小脚本:
        asm.sh, a Linux shell script, that demonstrates some of the asmcmd functionality.
 

#!/bin/bash 

# asm.sh - ASM reports by Bane Radulovic, Oracle Corporation, 2005 

# asm.sh [-db DBNAME] 
#   Report on all disk groups in the current ASM instance; 
#   Current ASM instance is determined by env variable ORACLE_SID; 
#   asmcmd should be in the PATH 
# asm.sh [-db DBNAME] -space 
#   Report on space used by dataabase DBNAME 
#   If DBNAME not specified report on total disk space used by all databases 
# asm.sh [-db DBNAME] -files 
#   Report all files for database DBNAME  managed by this ASM instance 
#   If DBNAME not specified report all files managed by this ASM instance 

# Usage notes: 
# All arguments are optional. 
# If database name is not supplied, report on all disk groups/space/files. 
# Report on total space in all disk groups only if database name not specified. 

# Open issues 
#   1. We don't detect if ASM instance is not up and if the environment is 
#      not set up (ASM sid, asmcmd in PATH) 
#   2. The script is not very useful - it's for demo purposes only :) 
#   3. We may need to expand this to show the extents/AUs allocation 
#      or something more usefull; 



# Parse command line arguments 


while [ $# -gt 0 ] 
do 
  case $1 in 
    -db) shift; DBNAME=$1;; # Database name 
    -space) SPACE=TRUE;;    # User wants the space usage report 
    -files) FILES=TRUE;;    # User wants the report on all files in db DBNAME 
    -help) echo "Usage: asm.sh [-db DBNAME] [-space] [-files]";exit 1;; 
  esac; 
  shift 
done 

echo "" 


# Get all ASM disk groups using the 'asmcmd ls' command 


for DIRECTORY in `asmcmd ls -d % 2>/dev/null` 
  do 
  echo $DIRECTORY | cut -f1 -d'/' >> /tmp/groups$$ 
  done 

if [ -s /tmp/groups$$ ] 
  then 
    echo "ASM instance $ORACLE_SID manages the following disk group(s):" 
    echo "==================================" 
    cat /tmp/groups$$ 
    echo "" 
  else 
    echo "There are no disk groups in the ASM instance $ORACLE_SID" 
    echo "" 
    exit 2 
fi 


# If -files was specified, report on all files for database DBNAME 


if [ $FILES ] 
  then 
    if [ $DBNAME ] 
    then 
      for GRP in `cat /tmp/groups$$` 
        do 
          asmcmd find $GRP/$DBNAME % >>/tmp/files$$ 2>/dev/null 
        done 
        if [ -s /tmp/files$$ ] 
          then 
            echo "$ORACLE_SID files for database $DBNAME:" 
            echo "==================================" 
            cat /tmp/files$$ 
          else 
           echo "Database $DBNAME does not have any files in disk group $GRP." 
        fi 
      echo "" 
    else 
      echo "The list of all files managed by ASM instance $ORACLE_SID:" 
      echo "==================================" 
      for GRP in `cat /tmp/groups$$` 
        do 
        asmcmd find $GRP % 2>/dev/null 
        done 
    fi 
fi 


# If -space was specified, report on the space usage per ASM group 


if [ $SPACE ] 
  then 
    if [ $DBNAME ] 
    then 
      for GRP in `cat /tmp/groups$$` 
        do 
        asmcmd du $GRP/$DBNAME > /tmp/grp$$ 2>/dev/null 
        if [ -s /tmp/grp$$ ] 
          then 
            echo "Space usage by database $DBNAME in disk group $GRP:" 
            echo "==================================" 
            cat /tmp/grp$$ 
            echo "" 
            rm -f /tmp/grp$$ 
          else 
            echo "Database $DBNAME does not use disk group $GRP." 
        fi 
      done 
    else 
      for GRP in `cat /tmp/groups$$` 
        do 
        asmcmd du $GRP > /tmp/grp$$ 2>/dev/null 
        if [ -s /tmp/grp$$ ] 
          then 
            echo "Total Space usage by all databases in disk group $GRP:" 
            echo "==================================" 
            cat /tmp/grp$$ 
            echo "" 
            rm -f /tmp/grp$$ 
        fi 
      done 
    fi 
fi 


# Clean up 


rm -f /tmp/groups$$ 
rm -f /tmp/files$$ 
rm -f /tmp/grp$$ 


# All work done 


exit 0

 

脚本示例:

1. Default output

[oracle@rac1 bin]$ ./asm.sh

 

ASM instance +ASM1 manages the following disk group(s):

==================================

DATA

FRA

 

2. Report on disk space for database dave

[oracle@rac1 bin]$ ./asm.sh -db dave -space

 

ASM instance +ASM1 manages the following disk group(s):

==================================

DATA

FRA

 

Space usage by database dave in disk group DATA:

==================================

Used_MB      Mirror_used_MB

    226                 226

 

Space usage by database dave in disk group FRA:

==================================

Used_MB      Mirror_used_MB

   3185                3185  

 


3. Report all files for database dave

 

[oracle@rac1 bin]$ ./asm.sh -db dave -files

 

ASM instance +ASM1 manages the following disk group(s):

==================================

DATA

FRA

 

+ASM1 files for database dave:

==================================

+DATA/dave/ONLINELOG/

+DATA/dave/ONLINELOG/group_1.261.746989315

+DATA/dave/ONLINELOG/group_2.262.746989325

+DATA/dave/ONLINELOG/group_3.265.746989333

+DATA/dave/ONLINELOG/group_4.266.746989343

+DATA/dave/PARAMETERFILE/

+DATA/dave/PARAMETERFILE/spfile.268.746989771

+DATA/dave/PARAMETERFILE/spfile.269.746989859

+DATA/dave/spfiledave.ora

+FRA/dave/ARCHIVELOG/

+FRA/dave/ARCHIVELOG/2011_03_29/

+FRA/dave/ARCHIVELOG/2011_03_29/thread_1_seq_1.270.747082809

+FRA/dave/ARCHIVELOG/2011_03_29/thread_2_seq_1.269.747068457

...

+FRA/dave/ARCHIVELOG/2011_04_21/thread_1_seq_36.332.749084429

+FRA/dave/ARCHIVELOG/2011_04_21/thread_2_seq_28.331.749080839

+FRA/dave/ONLINELOG/

+FRA/dave/ONLINELOG/group_1.257.746989321

+FRA/dave/ONLINELOG/group_2.258.746989329

+FRA/dave/ONLINELOG/group_3.259.746989339

+FRA/dave/ONLINELOG/group_4.260.746989347

 

 

4. Report on space and all files for database dave

[oracle@rac1 bin]$ ./asm.sh -space -db dave -files
 


Some minor issues and usage warnings for asmcmd version 10.2.0.1.

       1. When using asmcmd non interactively (e.g. in a shell scripts) it may be easier to use % (not *) for wildcard matching, to avoid shell substitution issues.

 

       2. asmcmd does not support wildcard substitution in CD command. This will be addressed in later versions of asmcmd.

 

       3. 'asmcmd du' always reports "Mirror_used_MB" space for all ASM disk groups. If the disk group is not mirrored, "Used_MB" and "Mirror_used_MB" values would be the same (represening the taken disk space in megabytes). In a normal redundancy disk group (i.e. with one mirror) the space reported under "Mirror_used_MB" would be double the ammout reported under "Used_MB". Similarly, for the triple mirrored disk group we would expect the three times the space reported under "Mirror_used_MB".

 

5ASMCMD - New commands in 11gR1

        We have introduced three new functionalities to the ASMCMD utility to improve node recovery after a crash,repair bad blocks on a disk, copy files and simplify the listing of ASM disks in a diskgroup 


       ASMCMD is also extended to include ASM disk group metadata backup and restore functionality.This provides the ability to create a pre-existing ASM diksgroup with the same disk paths, disk names,failure groups,attributes,templates and alias directory structure

 

ASMCMD - New commands in 11gR1

 

1.  ASMCMD cp command

       The ASMCMD cp option allows you to copy files between ASM disk groups and OS file systems and between two ASM servers. 

The following file copy can be performed 

- ASM Diskgroup to OS file system 
- OS file system to ASM Diskgroup 
- ASM Diskgroup to another diskgroup

 

1ASM Diskgroup to OS File system 

ASMCMD> cp +DGEXT/orcl/datafile/tbsext.256.628847401 /home/oracle/tbsext.dbf 
source +DGEXT/orcl/datafile/tbsext.256.628847401 
target /home/oracle/tbsext.dbf 
copying file(s)... 
file, /home/oracle/tbsext.dbf, copy committed. 

2OS File system to ASM Diskgroup 

ASMCMD> cp /home/oracle/tbsext.dbf +DGEXTBK/prod/datafile/tbsext.dbf 
source /home/oracle/tbsext.dbf 
target +DGEXTBK/prod/datafile/tbsext.dbf 
copying file(s)... 
file, +DGEXTBK/prod/datafile/tbsext.dbf, copy committed.

 

3ASM Diskgroup to another diskgroup


SQL> create diskgroup dgext external redundancy disk '/dev/raw/raw1','/dev/raw/raw2';


Diskgroup created. 

SQL> create diskgroup DGEXTBK external redundancy disk '/dev/raw/raw3','/dev/raw /raw4'; 
Diskgroup created. 

ASMCMD> cd DGEXTBK 
ASMCMD> mkdir prod 
ASMCMD> cd prod 
ASMCMD> mkdir datafile 
ASMCMD> cd datafile 
ASMCMD> pwd 
+DGEXTBK/prod/datafile 

SQL> create tablespace TBSEXT datafile '+DGEXT' size 10m; 
Tablespace created. 

SQL> select name from v$datafile; 

NAME 
------------------------------------------------------------- 
+DGEXT/orcl/datafile/tbsext.256.628847401

cp +DGEXT/orcl/datafile/tbsext.256.628847401 +DGEXTBK/prod/datafile/tbsext  

ASMCMD> cp +DGEXT/orcl/datafile/tbsext.256.628847401 +DGEXTBK/prod/datafile/tbsext  
source +DGEXT/orcl/datafile/tbsext.256.628847401  
target +DGEXTBK/prod/datafile/tbsext  
copying file(s)...file, +DGEXTBK/prod/datafile/tbsext, copy committed.  

The tbsext is alias name created in the folder +DGEXTBK/prod/datafile and actual file is created in the ASMTESTING folder

ASMCMD> ls -ltr  
Type Redund Striped Time Sys Name  
N tbsext => +DGEXTBK/ASMTESTING/DATAFILE/TESTING.256.628848885 

Also Refer Note 452158.1 ASMCMD cp command fails with ORA-15046 
 

 

2.  ASMCMD md_backup and md_restore 

       ASMCMD is extended to include ASM disk group metadata backup and restore functionality.This provides the ability to recreate a pre-existing ASM disk group with the same disk paths,disk names, failure groups, attributes, templates and alias directory structure 

       In 10g if an ASM disk group is lost, then it is only possible to restore the lost files using RMAN but you have to manually recreate the ASM disk group and any required user directories/templates. 

       In 11g we can take backup of ASM diskgroup metadata.The md_backup command creates a backup file containing metadata for one or more disk groups. By default all the mounted disk groups are included in the backup file which is saved in the current working directory.
 If the name of the backup file is not specified, ASM names the file AMBR_BACKUP_INTERMEDIATE_FILE. 


ASMCMD> md_backup -b dgbk -g dgext 
Disk group to be backed up: DGEXT 

       In restore mode, it reads the previously generated file to reconstruct the diskgroup and its metadata. You have the possibility to control the behaviour in restore mode to do a full,nodg or newdg restore.

 

The full mode restores the diskgroup exactly as it was at the time of backup

 

ASMCMD> md_restore -b dgbk -t full -g dgext 
Current Diskgroup being restored: DGEXT 
ASMCMD-09352: CREATE DISKGROUP failed 
ORA-15018: diskgroup cannot be created 
ORA-15030: diskgroup name "DGEXT" is in use by another diskgroup (DBD ERROR: OCIStmtExecute) 

SQL> drop diskgroup dgext; 
Diskgroup dropped. 

ASMCMD> md_restore -b dgbk -t full -g dgext 
Current Diskgroup being restored: DGEXT 
Diskgroup DGEXT created! 
System template XTRANSPORT modified! 
System template ONLINELOG modified! 
System template DATAGUARDCONFIG modified! 
System template AUTOBACKUP modified! 
System template TEMPFILE modified! 
System template ARCHIVELOG modified! 
System template ASM_STALE modified! 
System template BACKUPSET modified! 
System template DUMPSET modified! 
System template FLASHBACK modified! 
System template PARAMETERFILE modified! 
System template CONTROLFILE modified! 
System template DATAFILE modified! 
System template CHANGETRACKING modified! 

SQL> select group_number,name,type from v$asm_diskgroup; 

GROUP_NUMBER NAME                 TYPE 
------------ -------------------- ------ 
           1 DGEXT                EXTERN

 

The 'nodg' mode restore the attributes,templates, and alias directory structure specified in the backup file to an existing disk group.

 

ASMCMD> md_restore -b dgbk -t nodg -g dgext 
Current Diskgroup being restored: DGEXT 
System template XTRANSPORT modified! 
System template ONLINELOG modified! 
System template DATAGUARDCONFIG modified! 
System template AUTOBACKUP modified! 
System template TEMPFILE modified! 
System template ARCHIVELOG modified! 
System template ASM_STALE modified! 
System template BACKUPSET modified! 
System template DUMPSET modified! 
System template FLASHBACK modified! 
System template PARAMETERFILE modified! 
System template CONTROLFILE modified! 
System template DATAFILE modified! 
System template CHANGETRACKING modified!

 

The 'newdg' mode allows the user to override the disk group name,disk, and failgroup specifications as part of a diskgroup creation,but retains the attribute,template and, alias directory structure from the backup

 

ASMCMD>  md_restore -b dgbk -t newdg -o 'DGEXT:DG' 
Current Diskgroup being restored: DGEXT 
Current Diskgroup name replace by: DG 
Diskgroup DG created! 
System template XTRANSPORT modified! 
System template ONLINELOG modified! 
System template DATAGUARDCONFIG modified! 
System template AUTOBACKUP modified! 
System template TEMPFILE modified! 
System template ARCHIVELOG modified! 
System template ASM_STALE modified! 
System template BACKUPSET modified! 
System template DUMPSET modified! 
System template FLASHBACK modified! 
System template PARAMETERFILE modified! 
System template CONTROLFILE modified! 
System template DATAFILE modified! 
System template CHANGETRACKING modified!

 

SQL> select group_number,name,type from v$asm_diskgroup 

GROUP_NUMBER NAME                 TYPE 
------------ -------------------- ------ 
           1 DG                   EXTERN

 

6Can you create a second voting disk in a different ASM diskgroup when using External Redundancy in 11.2
       Oracle Server - Enterprise Edition - Version: 11.2.0.2 to 11.2.0.2 - Release: 11.2 to 11.2

 

 

       you cannot create more than 1 voting disk in the same or on another/different diskgroup disk when using External Redundancy in 11.2.

 

 

The rules are as follows:

(1).      External = 1 voting disk

(2).      Normal= 3 voting disk

(3).      High= 5 voting disk


       external redundancy diskgroup depends on the third party hardware vendor to handle the redundancy.

       if you want to have the voting disk multiplexed at ASM level then it is recommended you change your redundancy to Normal so that ASM provides this redundancy for you.


7Create SPFILE Under ASM

       -- Suppose that the database instance name is "osmdr"

-- In the database instance 

When trying to create a new spfile from a pfile under sqlplus

SQL> create SPFILE='+DATA/osmdr/spfileosmdr.ora' from pfile='/home/oracle/osmdr.init';
File created.

SQL>create SPFILE='+DATA/osmdr/spfileosmdr.ora' from PFILE='/home/oracle/osmdr.init';  

File created



Under ASM , the spileosmdr.ora is link to the wrong location under ASM

ASMCMD> ls -alr 
Type Redund Striped Time Sys Name 
N spfileosmdr.ora => 
+DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.272.613162051 
ASMCMD>

 

Q. Why it will create +DATA/DB_UNKNOW instead of +DATA/osmdr ?

 

 

-- Considering the TESTCASE hereunder

       The string "DB_UNKNOWN/PARAMETERFILE/SPFILE" is a generic system tag for the proxied spfile creation. This will be used in case the database instance has no open client session to the ASM instance at the time of the 'create spfile' command. That is, the ASM instance is not aware of the db name and therefore uses "DB_UNKNOWN". 

Please see the TESTCASE section above that demonstrates two scenarios;

[A] where the DB_UNKNOWN directory structure gets created, and [B] where the spfile is created in the directory //PARAMETERFILE.

 

 

TESTCASE  

[A] No open database connection to the ASM instance 

-- In the ASM instance 

SQL> select * from v$asm_client; 

no rows selected 

 

-- In the database instance

SQL> show parameter db_name 

NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 
db_name string V1020

SQL> select * from v$asm_client; 

no rows selected

SQL> create spfile='+DG1' from pfile; 
File created.

 

-- In asmcmd 
-- The DB_UNKNOWN directory structure gets created

ASMCMD> pwd 
+dg1 
ASMCMD> ls 
DB_UNKNOWN/ 
V1020/ 
ASMCMD> ls DB_UNKNOWN/ 
PARAMETERFILE/ 
ASMCMD> ls DB_UNKNOWN/PARAMETERFILE/ 
SPFILE.259.613339345

-- Remove the parameter file from the ASM diskgroup

ASMCMD> rm DB_UNKNOWN/PARAMETERFILE/SPFILE.259.613339345

-- Now the DB_UNKNOWN directory structure gets automatically removed

 

[B] 

-- In the database instance 
-- Open a connection to the ASM instance by accessing a datafile in an ASM diskgro 
-- In this example, a tablespace with a datafile in an ASM diskgroup is onlined

 

SQL> alter tablespace ts1 online; 
Tablespace altered. 

SQL> select * from v$asm_client; 

GROUP_NUMBER INSTANCE_NAM DB_NAME STATUS SOFTWARE 
------------ ------------ -------- ------------ - 
1 +ASM V1020 CONNECTED 10.2.0.3.0 10.2.0.0.0

-- In the ASM instance

 

SQL> select * from v$asm_client; 
GROUP_NUMBER INSTANCE_NAM DB_NAME STATUS SOFTW 
------------ ------------ -------- ----------- 
1 V1020 V1020 CONNECTED 10.2.0.3.0 10.2.0.1.0

 

-- In the database instance

SQL> create spfile='+DG1' from pfile; 
File created.

 

-- In asmcmd

ASMCMD> pwd 
+dg1 
ASMCMD> ls 
V1020/ 
ASMCMD> ls V1020/ 
DATAFILE/ 
PARAMETERFILE/ 
ASMCMD> ls V1020/PARAMETERFILE/ 
spfile.259.613339813

 

-- Result: 
- The spfile is located in the directory //PARAMETERFILE 
- No DB_UNKNOWN directory structure got created


8Database Creation on 11.2 Grid Infracture with Role Separation ( ORA-15025, KFSG-00312, ORA-15081 )
       This document discusses the necessary steps to take before creating a 11.2 / 11.1 / 10.2 database with 11.2 Grid Infrastructure when Role Separation is in place.

 

 

In case of role separation we typically have the following groups and users:

Grid Infrastructure Home Owner : grid
Primary Group : oinstall
Secondary Groups : asmadmin, asmdba, asmoper

RDBMS Home Owner : oracle
Primary Group : oinstall
Secondary Groups : dba, oper, asmdba

 

       In case of stand alone set up, if the 11.2 Database be managed by Oracle Restart ( srvctl stop/start ), then 'grid' user should also be a part of 'dba' group.


The ASM Disks would be owned by grid:asmadmin

11.2  Database
       If we use 11.2 DBCA to create the database, everything will go through fine. If we create the 11.2 database manually, then before creating the database we need to run the following command:

$ su - grid
$ cd /bin
$ ./setasmgidwrap o=<11.2 RDBMS Home>/bin/oracle

       If we do not do this, database creation will fail with ORA-15025 as it will not be able to access the ASM Disks.



Pre11.2 Database
       For pre11.2 Databases ( 11.1 and 10.2 ), whether creating the database using DBCA or manually, apply Patch 9575578 to the 11.2 Grid Home. After applying the patch run:

$ su - grid
$ cd /bin
$ ./setasmgidwrap o=<11.1/10.2 RDBMS Home>/bin/oracle

Without the patch script. setasmgidwrap will fail with KFSG-00312


9Exact Steps To Migrate ASM Diskgroups To Another SAN Without Downtime
       The present document explains in detail the exact steps to migrate ASM diskgroups from one SAN to another SAN without a downtime.

 

Solution

 

       If your plans are replacing the current disks associated to your diskgroups with a new storage, this operation can be accomplished without any downtime, so you can follow the next steps 

1) Backup all your databases and valid the backup (always required to protect your data). 

2) Add the new path (new disks from the new storage) to your asm_disktring to be recognized by ASM: 

Example:

SQL> alter system set asm_disktring = '/dev/emcpowerc*' , '/dev/emcpowerh*'; 

Where: '/dev/emcpowerc*' are the current disks. 
Where: '/dev/emcpowerh*' are the new disks.


3)  Confirm that the new disks are being detected by ASM:

SQL> select path from v$asm_disk;



4)  Add the new disks to your desired diskgroup:

SQL> alter diskgroup add disk 
’, 
’, 
’, 
’, 



’ rebalance power <#>; 



5)  Then wait until the rebalance operation completes:

SQL> select * from v$asm_operation; 
SQL> select * from gv$asm_operation;


6)  Finally, remove the old disks:

SQL> alter diskgroup drop disk 







  rebalance power <#>;


7)  Then wait until the rebalance operation completes:

SQL> select * from v$asm_operation; 
SQL> select * from gv$asm_operation;



8)  Done, your ASM diskgroups and database have been migrated to the new storage. 

Note:

       Alternatively, we can execute add disk & drop disk statements in one operation, in that way only one rebalance operation will be started as follow:

 

SQL> alter diskgroup add disk '', .., '' drop disk , , .., rebalance power <#>;


This is more efficient than separated commands (add disk & drop disk statements).


10Extracting Datafile Blocks From ASM
        This note explains how to use RMAN to copy an ASM based datafile onto the file system. Once the datafile is on the file system, the OS command dd can be used to extract the block(s) from the datafile.

-- 注意,用dd 命令修复坏块 只适用与逻辑坏块。 物理坏块不适用。

 


       The approach presented here may be used with any ASM redundancy. It should be noted that this method requires the free space on the file system equivalent to the size of datafile being extracted.

       This method can be used for datafiles, datafile copies and archived redo log files stored within ASM.

 

Extracting Datafile Blocks From ASM

       Lets look at an example of corruption reported on an ASM datafile with normal redundancy. The objective is to extract the corrupted block from the datafile, patch it and put it back into the database. 

1. Corruption reported:

 

SQL> SELECT COUNT(*) FROM T1;
SELECT COUNT(*) FROM T1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 65)
ORA-01110: data file 7: '+GROUPA/aa10g/datafile/ts_bane.267.3'

 

2. Take the file offline, to make sure there are no further changes while the block is being examined/patched.

 

SQL> alter database datafile 7 offline;
Database altered.

 

3. Use RMAN 'BACKUP AS COPY DATAFILE' command to extract the datafile from ASM onto the file system:

RMAN> CONNECT TARGET;
connected to target database: AA10G (DBID=1449158500)

RMAN> BACKUP AS COPY DATAFILE 7 FORMAT '/tmp/df_%f';
Starting backup at 
Uusing target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=215 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=+GROUPA/aa10g/datafile/ts_bane.267.3
output filename=/tmp/df_7 tag=TAG20041230T125410 recid=7 stamp=546267252
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 

 

4. Now that the file is on the file system, the OS command dd can be used to extract the block from the copy of the datafile:

       $ dd if=df_7 of=f7b65.dd count=1 skip=65 bs=8192

 

5. Lets say that at this step the block has been examined and patched.

 

6. Put the patched block back into df_7:

       $ dd if=f7b65.dd of=df_7 bs=8192 seek=65 count=1 conv=notrunc

 

7. Restore the patched datafile from the file system back into ASM:

 

RMAN> CONNECT TARGET;
connected to target database: AA10G (DBID=1449158500)

RMAN> run {
RESTORE DATAFILE 7 FROM TAG 'TAG20041230T125410';
RECOVER DATAFILE 7;
SQL "ALTER DATABASE DATAFILE 7 ONLINE";
}

Starting restore at 
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=213 devtype=DISK
channel ORA_DISK_1: restoring datafile 00007
input datafilecopy recid=7 stamp=546267252 filename=/tmp/df_7
destination for restore of datafile 00007: +GROUPA/aa10g/datafile/ts_bane.267.3
channel ORA_DISK_1: copied datafilecopy of datafile 00007
output filename=+GROUPA/aa10g/datafile/ts_bane.267.3 recid=8 stamp=546267683
Finished restore at 

Starting recover at 
using channel ORA_DISK_1
starting media recovery
media recovery complete
Finished recover at 

sql statement: ALTER DATABASE DATAFILE 7 ONLINE
RMAN>

 

8. Once the patched datafile has been restored, select from table T1 works as expected:

 

SQL> SELECT COUNT(*) FROM T1;

COUNT(*)
--------
1536


11How To Add a New Disk(s) to An Existing Diskgroup on RAC (Best Practices).

        Before you add the disk(s) to your production diskgroup, you can use the next steps to validate whether the candidate disk(s) is/are in good shape and can be accessed from each node (without harm the existing production diskgroups), as follow:

 

Solution

1)  As Oracle OS user, run the next commands on each node (if ASMLIB API is used):

$> id 
$> /etc/init.d/oracleasm  scandisks 
$> /etc/init.d/oracleasm  listdisks 
$> /etc/init.d/oracleasm  querydisk 

Note: All the disks must be visible from each node. 

2)  Confirm the disks are visible (I/O) at OS level (as Oracle OS user) from each node:

$> id 
$> ls -l   
$> dd if= of=/dev/null count=100 bs=8192

Example: 

$> dd if=/dev/raw/raw1 of=/dev/null count=100 bs=8192  # raw device 
$> dd if=/dev/oracleasm/disks/PRODA07 of=/dev/null count=100 bs=8192  # ASMLIB device

 

3)  Create a new temporary diskgroup:

SQL> CREATE DISKGROUP TEST EXTERNAL REDUNDANCY DISK '';

 

4)  Check if the diskgroup is created and mounted:

SQL> SELECT STATE, NAME FROM V$ASM_DISKGROUP;

 

5)  If so, then manually mount it on the other ASM instance(s):

SQL> ALTER DISKGROUP TEST MOUNT; 
SQL> SELECT STATE, NAME FROM V$ASM_DISKGROUP;

 

6 If the previous steps had success and the TEST diskgroup can be successfully mounted (on each ASM instance), then drop the TEST diskgroup as follow:

 

SQL> alter diskgroup test dismount; --(from all the ASM instances, except from one). 

SQL> DROP DISKGROUP TEST; --(from the ASM instance, which the diskgroup is still mounted).

 

7)  Then the candidate disk is ready to be added to the desired diskgroup:

SQL> ALTER DISKGROUP  ADD DISK '';

 

References

NOTE:288002.1 - OERR: ORA-15075 disk(s) are not visible cluster-wide

 

12How To Change ASM SYS PASSWORD

       To change the ASM SYS Password

Things tried:

SQL> password
Changing password for SYS
Old password:
New password:
Retype new password:
ERROR:
ORA-00600: internal error code, arguments: [15051], [], [], [], [], [], [], []

 

SQL> select INSTANCE_NAME from v$instance; 

INSTANCE_NAME 
---------------- 
+ASM 

SQL> ALTER USER sys IDENTIFIED BY  REPLACE
ALTER USER sys IDENTIFIED BY  REPLACE

ERROR at line 1: 
ORA-01109: database not open 

 

The following error also might occur:
SQL> alter user sys identified by ;
alter user sys identified by 
*
ERROR at line 1:
ORA-01031: insufficient privileges

Solution

       We can not change the password for ASM databases via alter user command. 
       The password should be the one provided when the password file was created,also REMOTE_LOGIN_PASSWORDFILE should be set to EXCLUSIVE on all instances. 

       If you want to change the password then you would need to recreate the password file using the orapwd utility 
 

Recreate the password file for the ASM instance as follows:

1.  Set the ORACLE_HOME and ORACLE_SID to the ASM instance
2.  connect /as sysdba from sqlplus
3.  If the value of the "remote_login_passwordfile" parameter in the pfile or spfile is EXCLUSIVE, you must shutdown your instance 
4.  RENAME or DELETE the existing password file PWD.ora( In Windows) / orapw ( in UNIX) 
5.  Issue the command:

WINDOWS:

orapwd file=/database/PWD.ora password=

UNIX:

orapwd file=/dbs/PWD password=

 

 

       The passwordfile can be recreated for ASM while ASM instance is up. Usually for normal DB instances, we recommended that DB instances be shutdown before changing the passwordfile.

      

13How To Change The Asm Rebalancing Power After Starting The Rebalancing Process
       The rebalance power defaults to the value of the ASM_POWER_LIMIT initialization parameter (default = 1).


       The higher the limit, the faster a rebalance operation may complete. Lower values cause rebalancing to take longer, but consume fewer processing and I/O resources.       This leaves these resources available for other applications, such as the database.

The POWER value can be from 0 to 11 where 0 stops rebalancing and 11 is the fastest. 

It is possible to adjust this parameter dynamically, however adjusting ASM_POWER_LIMIT only affects future rebalances. It does not affect an in progress rebalance.

To change the power of an in progress rebalance, a new rebalance command should be issued with the POWER clause.

ALTER DISKGROUP REBALANCE [POWER n];

The asm_power_limit can be found in the v$asm_operation view.



Test Case:
       Altering a diskgroup (add, drop, undrop, resize) will trigger an automatic rebalance operation according to ASM_POWER_LIMIT initialization parameter if no POWER clause specified in the alter command. If the optional POWER clause is specified, ASM will rebalance the diskgroup using the integer value to override the value that the ASM_POWER_LIMIT initialization parameter.

 

SQL> show parameter limit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
asm_power_limit                      integer     1

 

SQL> alter diskgroup dg1 add disk
  2  '//./ORCLDISKD10',
  3  '//./ORCLDISKD20',
  4  '//./ORCLDISKD30';

Diskgro
up altered.


One rebalance process started (ARB0)

 

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE
------------ ----- ---- ---------- ---------- ---------- ---------- ----------
           1 REBAL WAIT          1          0          0          0     



ASM alert.log

Fri Dec 26 16:55:08 2008
NOTE: starting rebalance of group 1/0x8c6014a7 (DG1) at power 1
Starting background process ARB0
ARB0 started with pid=14, OS id=316
Fri Dec 26 16:55:08 2008
NOTE: assigning ARB0 to group 1/0x8c6014a7 (DG1)

 

After running a rebalance command, the running rebalance process will be stopped and new  rebalance slave processes will spawned depending on POWER used

 

SQL> alter diskgroup dg1 rebalance power 8;
Diskgroup altered.

 

 


8 rebalance slave processes  spawned (ARB0,....,ARB7)

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE
------------ ----- ---- ---------- ---------- ---------- ---------- ----------
           1 REBAL RUN           8          8          0        407          0


ASM alert.log

Fri Dec 26 16:55:11 2008
SQL> alter diskgroup dg1 rebalance power 8 

NOTE: stopping process ARB0
Fri Dec 26 16:55:11 2008
NOTE: rebalance interrupted for group 1/0x8c6014a7 (DG1)
Fri Dec 26 16:55:11 2008
NOTE: PST update: grp = 1
NOTE: requesting all-instance PST refresh for group=1
Fri Dec 26 16:55:11 2008
NOTE: PST refresh pending for group 1/0x8c6014a7 (DG1)
SUCCESS: refreshed PST for 1/0x8c6014a7 (DG1)
Fri Dec 26 16:55:14 2008
NOTE: starting rebalance of group 1/0x8c6014a7 (DG1) at power 8
Starting background process ARB0
Starting background process ARB1
ARB0 started with pid=14, OS id=1628
Fri Dec 26 16:55:15 2008
Starting background process ARB2
ARB1 started with pid=15, OS id=1920
Fri Dec 26 16:55:15 2008
Starting background process ARB3
ARB2 started with pid=16, OS id=1964
Fri Dec 26 16:55:15 2008
Starting background process ARB4
ARB3 started with pid=17, OS id=1664
Fri Dec 26 16:55:15 2008
Starting background process ARB5
ARB4 started with pid=18, OS id=1836
Fri Dec 26 16:55:15 2008
Starting background process ARB6
ARB5 started with pid=19, OS id=1972
Fri Dec 26 16:55:15 2008
Starting background process ARB7
ARB6 started with pid=20, OS id=1800 


14How to move a datafile from a file system to ASM

       Moving a datafile from the file system can be achived in two ways.

1. While the database is shutdown (in mount stage).
2
. While the database is running (with the selected tablespace offline).

.  While the database is shutdown (in mount stage).

       Moving oracle datafile while the database is in mount stage is performed in the following way:

1.  Shutdown and mount the database.

[oracle@linux] sqlplus '/as sysdba'
SQL> shutdown immediate;
SQL> startup mount;


2.  Ensure you have enough space in the ASM diskgroup to copy the datafile.
     First identify the size of the datafile you wish to move.

SQL> select file#, name, (bytes/1048576) File_Size_MB from v$datafile;

FILE#   NAME                         FILE_SIZE_MB
-----   ---------------------------- --------------
...
4       /oradata/PROD/users01.dbf    2500
...

* In this example we will be moving users01.dbf


[oracle@linux] export ORACLE_SID=+ASM

SQL> select NAME, STATE, TOTAL_MB, FREE_MB from v$asm_diskgroup;

NAME                           STATE       TOTAL_MB   FREE_MB
------------------------------ ----------- ---------- ----------
DGROUP1                        MOUNTED     100        3
DGROUP2                        MOUNTED     4882       4830


3. Connect to RMAN and copy the datafile from the filesystem to the select ASM diskgroup.

 

[oracle@linux] rman target=/

RMAN> copy datafile 4 to '+DGROUP2';

Starting backup at 2006/09/05 12:14:23
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=31 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/oradata/PROD/users01.dbf
output filename=+DGROUP2/PROD/datafile/users01.258.600351265 tag=TAG20060905T121424 recid=10 stamp=600351264
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:01
Finished backup at 2006/09/05 12:19:24


4.  Update the controlfile with the new location of the datafile.

 

[oracle@linux] rman target /


RMAN> switch datafile 4 to copy;

datafile 4 switched to datafile copy "+DGROUP2/PROD/datafile/users01.258.600351265".

 

5.  The file is now if the new location.

 

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
..
+DGROUP2/PROD/datafile/users01.258.600351265
..

 

6. The database may now be opened.


.  While the database is running (with the select tablespace offline).
       In order to move a datafile on a running active database the tablespace where the datafile resides must be placed offline.

1. Identify the tablespace which contains the datafile and offline the tablespace.

 

SQL> select tablespace_name, file_name from dba_data_files where file_id=4;

TABLESPACE_NAME    FILE_NAME
------------------ ------------------------------
USERS              /oradata/RMAN/users01.dbf


SQL> alter tablespace USERS offline;

 

* * * * * Continue with Steps 2 - 5 above. * * * * *



6.  After you have successfully completed the above steps (2 -5) place the tablespace online;

SQL> alter tablespace USERS online;


     The datafile has now been successfully moved to the ASM diskgroup.


15How to move a datafile from ASM to the file system
       Moving a datafile from ASM to the file system can be performed in two ways:


1. While the database is shutdown (in mount stage). This is the only option if datafiles to be moved are from system or undo tablespaces.
2
. While the database is running (with the selected tablespace offline).


.  While the database is shutdown (in mount stage).


       Moving oracle datafile while the database is in mount stage is performed in the following way:

1. Shutdown and mount the database.

[oracle@linux] sqlplus '/as sysdba'

SQL> shutdown immediate;

SQL> startup mount;

 

2.  Ensure you have enough space on the file system to copy the datafile.

First identify the size of the datafile you wish to move.

 

SQL> select file#, name, (bytes/1048576) File_Size_MB from v$datafile;

FILE#  NAME                                           FILE_SIZE_MB
-----  ---------------------------------------------  ------------
...
4      +DGROUP2/PROD/datafile/users01.258.600351265   2500
...

* In this example we will be moving the users01 datafile.


[oracle@linux] df -k

Filesystem                  1K-blocks      Used      Available   Use% Mounted on
/dev/vg01/root              10321208       3716884   6080036     38%  /
/dev/sda1                   147766         15521     124616      12%  /boot
none                        1022976        0         1022976     0%   /dev/shm
/dev/vg01/tmp               2064208        58380     1900972     3%   /tmp
/dev/vg01/u01               20158332       18557600  576732      97%  /u01
/dev/vg01/backups           6092608        5266016   517140      92%  /backups
/dev/vg01/oradata           15487600       11876492  2824676     81%  /oradata
/dev/vg01/recovery_area     15487600       11465932  3235236     78%  /recovery_area


3. Connect to RMAN and copy the datafile from the ASM diskgroup to the filesystem.

 

[oracle@linux] rman target=/

RMAN> copy datafile 4 to '/oradata/PROD/users01.dbf';

Starting backup at 2006/09/06 15:46:13
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DGROUP3/rman/datafile/users.258.600450259
output filename=/oradata/PROD/users01.dbf tag=TAG20060906T154614 recid=22 stamp=600450375
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2006/09/06 15:46:15


4.  Update the controlfile with the new location of the datafile.

 

[oracle@linux] sqlplus '/as sysdba'

SQL> alter database rename file '+DGROUP3/PROD/datafile/users.258.600450259' to '/oradata/PROD/users01.dbf'; 

Database altered.


5.  The file is now if the new location.

 

SQL> select file#, name from v$datafile;

FILE#    NAME
------   ------------------------------------------------------------------
..
4        /oradata/PROD/users01.dbf
..

 

6. The database may now be opened.


.  While the database is running (with the select tablespace offline).

       In order to move a datafile on a running active database the tablespace where the datafile resides must be placed offline.


1.  Identify the tablespace which contains the datafile and offline the tablespace.


SQL> select tablespace_name, file_name from dba_data_files where file_id=4;

TABLESPACE_NAME    FILE_NAME
------------------ ------------------------------------------
USERS              +DGROUP3/PROD/datafile/users.258.600450259


SQL> alter tablespace USERS offline;

 

* * * * * Continue with Steps 2 - 5 above. * * * * *



6.  After you have successfully completed the above steps (2 -5) place the tablespace online;

SQL> alter tablespace USERS online;

 

The datafile has now been successfully moved to the ASM diskgroup.


16How to copy a datafile from ASM to a file system not using RMAN

       How to move a datafile from a file system to ASM

Moving a datafile from the file system can be achived in two ways.
1. While the database is shutdown (in mount stage).
2
. While the database is running (with the selected tablespace offline).

.  While the database is shutdown (in mount stage).

       Moving oracle datafile while the database is in mount stage is performed in the following way:

1.  Shutdown and mount the database.

[oracle@linux] sqlplus '/as sysdba'
SQL> shutdown immediate;
SQL> startup mount;


2.  Ensure you have enough space in the ASM diskgroup to copy the datafile.
     First identify the size of the datafile you wish to move.

SQL> select file#, name, (bytes/1048576) File_Size_MB from v$datafile;

FILE#   NAME                         FILE_SIZE_MB
-----   ---------------------------- --------------
...
4       /oradata/PROD/users01.dbf    2500
...

* In this example we will be moving users01.dbf


[oracle@linux] export ORACLE_SID=+ASM

SQL> select NAME, STATE, TOTAL_MB, FREE_MB from v$asm_diskgroup;

NAME                           STATE       TOTAL_MB   FREE_MB
------------------------------ ----------- ---------- ----------
DGROUP1                        MOUNTED     100        3
DGROUP2                        MOUNTED     4882       4830


3. Connect to RMAN and copy the datafile from the filesystem to the select ASM diskgroup.

 

[oracle@linux] rman target=/

RMAN> copy datafile 4 to '+DGROUP2';

Starting backup at 2006/09/05 12:14:23
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=31 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/oradata/PROD/users01.dbf
output filename=+DGROUP2/PROD/datafile/users01.258.600351265 tag=TAG20060905T121424 recid=10 stamp=600351264
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:01
Finished backup at 2006/09/05 12:19:24


4.  Update the controlfile with the new location of the datafile.

 

[oracle@linux] rman target /


RMAN> switch datafile 4 to copy;

datafile 4 switched to datafile copy "+DGROUP2/PROD/datafile/users01.258.600351265".

 

5.  The file is now if the new location.

 

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
..
+DGROUP2/PROD/datafile/users01.258.600351265
..

 

6. The database may now be opened.


.  While the database is running (with the select tablespace offline).
       In order to move a datafile on a running active database the tablespace where the datafile resides must be placed offline.

1. Identify the tablespace which contains the datafile and offline the tablespace.

 

SQL> select tablespace_name, file_name from dba_data_files where file_id=4;

TABLESPACE_NAME    FILE_NAME
------------------ ------------------------------
USERS              /oradata/RMAN/users01.dbf


SQL> alter tablespace USERS offline;

 

* * * * * Continue with Steps 2 - 5 above. * * * * *



6.  After you have successfully completed the above steps (2 -5) place the tablespace online;

SQL> alter tablespace USERS online;


     The datafile has now been successfully moved to the ASM diskgroup.

Moving a datafile from ASM to the file system can be performed in two ways:


1. While the database is shutdown (in mount stage). This is the only option if datafiles to be moved are from system or undo tablespaces.
2
. While the database is running (with the selected tablespace offline).


.  While the database is shutdown (in mount stage).


       Moving oracle datafile while the database is in mount stage is performed in the following way:

1. Shutdown and mount the database.

[oracle@linux] sqlplus '/as sysdba'

SQL> shutdown immediate;

SQL> startup mount;

 

2.  Ensure you have enough space on the file system to copy the datafile.

First identify the size of the datafile you wish to move.

 

SQL> select file#, name, (bytes/1048576) File_Size_MB from v$datafile;

FILE#  NAME                                           FILE_SIZE_MB
-----  ---------------------------------------------  ------------
...
4      +DGROUP2/PROD/datafile/users01.258.600351265   2500
...

* In this example we will be moving the users01 datafile.


[oracle@linux] df -k

Filesystem                  1K-blocks      Used      Available   Use% Mounted on
/dev/vg01/root              10321208       3716884   6080036     38%  /
/dev/sda1                   147766         15521     124616      12%  /boot
none                        1022976        0         1022976     0%   /dev/shm
/dev/vg01/tmp               2064208        58380     1900972     3%   /tmp
/dev/vg01/u01               20158332       18557600  576732      97%  /u01
/dev/vg01/backups           6092608        5266016   517140      92%  /backups
/dev/vg01/oradata           15487600       11876492  2824676     81%  /oradata
/dev/vg01/recovery_area     15487600       11465932  3235236     78%  /recovery_area


3. Connect to RMAN and copy the datafile from the ASM diskgroup to the filesystem.

 

[oracle@linux] rman target=/

RMAN> copy datafile 4 to '/oradata/PROD/users01.dbf';

Starting backup at 2006/09/06 15:46:13
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DGROUP3/rman/datafile/users.258.600450259
output filename=/oradata/PROD/users01.dbf tag=TAG20060906T154614 recid=22 stamp=600450375
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2006/09/06 15:46:15


4.  Update the controlfile with the new location of the datafile.

 

[oracle@linux] sqlplus '/as sysdba'

SQL> alter database rename file '+DGROUP3/PROD/datafile/users.258.600450259' to '/oradata/PROD/users01.dbf'; 

Database altered.


5.  The file is now if the new location.

 

SQL> select file#, name from v$datafile;

FILE#    NAME
------   ------------------------------------------------------------------
..
4        /oradata/PROD/users01.dbf
..

 

6. The database may now be opened.


.  While the database is running (with the select tablespace offline).

       In order to move a datafile on a running active database the tablespace where the datafile resides must be placed offline.


1.  Identify the tablespace which contains the datafile and offline the tablespace.


SQL> select tablespace_name, file_name from dba_data_files where file_id=4;

TABLESPACE_NAME    FILE_NAME
------------------ ------------------------------------------
USERS              +DGROUP3/PROD/datafile/users.258.600450259


SQL> alter tablespace USERS offline;

 

* * * * * Continue with Steps 2 - 5 above. * * * * *



6.  After you have successfully completed the above steps (2 -5) place the tablespace online;

SQL> alter tablespace USERS online;

 

The datafile has now been successfully moved to the ASM diskgroup.

How to copy a datafile from ASM to a file system not using RMAN

步骤如下:

1.  Log onto the target database that is local to the ASM instance as the sys user.

2.  create source directory within the target database.
SQL> create or replace directory SOURCE_DIR as '+DGROUP1/V10ASM/datafile/';
Directory created.

(In this example +DGROUP1/V10ASM/datafile/ is the source directory where the datafile is located and where you wish to copy the file from.)

3.  create destination directory within database.

SQL> create or replace directory ORACLE_DEST as '/restore';
Directory created.

(In this example /restore is the destination directory where the datafile is to be copied to.)

4.  Execute the dbms_file_transfer package.

SQL> 
BEGIN
dbms_file_transfer.copy_file(source_directory_object =>
'SOURCE_DIR', source_file_name => 'system.272.617284341',
destination_directory_object => 'ORACLE_DEST',
destination_file_name => 'system.dbf');
END;


PL/SQL procedure successfully completed.

(In this example system.272.617284341 is the file I wish to transfer and the destination file is system.dbf)

The file has now been copied to the /restore directory.

 

17How to Copy Archivelog Files From ASM to Filesystem and vice versa
       
This note will guide a DBA in copying archivelog files currently located in a ASM diskgroup to a file system.  This is commonly useful when a DBA needs to copy archivelog files from  a primary to standby database in a dataguard environment.

       The article here provides RMAN examples to copy archivelogs files from ASM to filesytem and vice versa.

 

步骤如下:

       RMAN allow you to copy archivelog files to a tape or filesystem. The following examples provide RMAN scripts for copying all archivelog files to '/tmp'.

 

-- Copy all archivelog files to a location in filesystem 
rman> 
run 

allocate channel c1 type disk format '/tmp/arc_%U'; 
backup archivelog all; 



-- Back up all archived logs created more than 7 and less than 30 days ago. 
run { 
allocate channel ch1 type disk format '/tmp/arc_%U'; 
backup archivelog from time 'SYSDATE-30' until time 'SYSDATE-7'; 


-- Back up all archived logs from sequence # 250 to sequence # 301 and deletes the archived redo logs after the backup is complete. 

run { 
allocate channel ch1 type disk format '/tmp/arc_%U'; 
backup 
archivelog from logseq 250 until logseq 301 thread 1 


In FORMAT clause, %U is replaced with unique file names when creating archivelog backups.



The above RMAN statements create a file in the following format in the destination directory (/etc).

 

-rw-r----- 1 oracle dba2 46623232 Sep 28 13:14 arc_03kqcj6g_1_1

 

       Then, one can restore archive logs that are already located on a filesystem using restore command:

run { 
set archivelog destination to '/tmp'; 
restore archivelog all; 
}

 

       If you do not specify SET ARCHIVELOG DESTINATION, then RMAN restores archived redo log files to the flash recovery area (when FRA is configured ).  Refer to RMAN options to restore only the required archive logfiles based on sequence number or creation timestamp.

 

18How to Copy asm files between remote ASM instances using ASMCMD command

       Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.2 - Release: 11.1 to 11.2

 

 

11g new feature,you can use asmcmd to copy files between remote instances, Enables you to copy files between ASM disk groups on local instances and remote instances. You can also use this command to copy files from ASM disk groups to the operating system.

cp -ifr @..:/

user_name@host_name..

The user_name, host_name, and SID are required. The default port number is 1521.

Example :-

asmcmd>cp -ifr +DATA/RAC/PARAMETERFILE/spfile.257.678975489 sys@stgrac1.1521.+ASM2:+FRA/RAC/ARCHIVELOG/spfile



Troubleshooting-ASMCMD remote copy 

asmcmd remote copy works through listener connection. 
ASMCMD remote connection can fail with below generic error. 
ASMCMD-08202: internal error: [asmcmdshare_error_msg_05] [8201] 
[8201] means unable to connect remote ASM Instance. 
It could be due to following reason.

 

* not able to reach remote host. 
* Remote host listener is down. 
* Remote ASM Instance is not registered with listener and running non-default port. 
* sysasm remote connection does not work. 
* Incorrect password given for sys user. 
* Remote ASM Instance password file missing.


We need to enable additional tracing for asmcmd connection to get a exact failure message. 

++ set DBI_TRACE environment variable for asmcmd perl tracing. 

export DBI_TRACE=1 

++ Now connect using asmcmd and re-produce the issue.

 

Example 1:-

asmcmd>cp +data/spfileorcl.ora.289.686235413 sys@stgrac1.1521.+ASM1:+test 

-> DBI->connect(dbi:Oracle:host=stgrac1;port=1521;sid=+ASM1, sys, ****, HASH (0x8b2b044)) 
connect using '(DESCRIPTION=(ADDRESS=(HOST=stgrac1)(PROTOCOL=tcp)(PORT=1521))(CONNECT_DATA=(SID=+ASM1)))' 
ERROR: '1031' 'ORA-01031: insufficient privileges 

(DBD ERROR: OCISessionBegin)' 
<- DESTROY= undef at DBI.pm line 591 
DBI connect('host=stgrac1;port=1521;sid=+ASM1','sys',...) failed: ORA-01031: insufficient privileges (DBD ERROR: OCISessionBegin) 
KK FROM HERE A 
ASMCMD-08202: internal error: [asmcmdshare_error_msg_05] [8201]


Here we can see that asmcmd copy failed due to ORA-01031. 

ASMCMD uses SYSASM by default if -a option is not used. 

Here the problem is sysasm privelege was not given to sys user on remote ASM Instance. Given the SYSASM privilege to SYS ( or the user trying to connect ). When you grant a system privilege, the password file is updated. 


SQL> grant sysasm to sys; 
Grant succeeded. 

SQL> select * from v$pwfile_users; 
USERNAME SYSDB SYSOP SYSAS 
------------------------------ ----- ----- ----- 
SYS TRUE TRUE TRUE 

Now the remote asmcmd copy works fine 


Example 2:-

ASMCMD> cp -ifr thread_2_seq_5.264.678983423 sys@bderac2-vip.1521.+ASM2:+FRA/RAC/ARCHIVELOG/
Enter password: ***
ASMCMD-08016: copy source->'+FRA/RAC/ARCHIVELOG/2009_02_16/thread_2_seq_5.264.678983423' and target->'+FRA/RAC/ARCHIVELOG/thread_2_seq_5.264.678983423' failed
ORA-17628: Oracle error 19505 returned by remote Oracle server
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 258
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)
ASMCMD>



Solution:-
       The cp command failed because the target ASM file name was not specified  or File name should not contain the file number/incarnation.We can not copy OMF form. files without specifying file name

 

cp -ifr thread_2_seq_5.264.678983423 sys@bderac2-vip.1521.+ASM2:+FRA/RAC/ARCHIVELOG/thread_2_seq_5

The file number/incarnation will be created automatically during the copy.


19How To Create manually an ASM Instance From Scratch
       
This note will explain how to create an ASM instance from scratch instead of using DBCA.

 

步骤如下:

       Before you start, its recommended that ASM instance installed in a separate Oracle home, it is very useful in the case of applying patches and upgrade. For example you can upgrade ASM home from 10.2.0.3 to 10.2.0.4 while database home still on 10.2.0.3.

1)  Because CSS must be running before any ASM instance, you have to make sure that CSS service is running:

# crsctl check css
Cluster Synchronization Services appears healthy


       If its not running, you should configure CSS process by running $ORACLE_HOME/bin/localconfig script.:

 

# $ORACLE_HOME/bin/localconfig all

/etc/oracle does not exist. Creating it now.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configuration for local CSS has been initialized

Adding to inittab
Startup will be queued to init within 90 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.

CSS is active on these nodes.
DEV1
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)


2) Create the init+ASM.ora file in $ORACLE_HOME/dbs (this ORACLE_HOME being the newly created one).

(Change any of these parameters if needed for your environment)

# Default asm_diskstring values for supported platforms:
# Solaris (32/64 bit) /dev/rdsk/*
# Windows NT/XP //./orcldisk*
# Linux (32/64 bit) /dev/raw/*
# HPUX /dev/rdsk/*
# HPUX(Tru 64) /dev/rdisk/*
# AIX /dev/rhdisk/*

ASM_DISKSTRING=''
INSTANCE_TYPE='ASM'
LARGE_POOL_SIZE=40M
REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
USER_DUMP_DEST=$ORACLE_HOME/admin/+ASM/udump
BACKGROUND_DUMP_DEST=$ORACLE_HOME/admin/+ASM/bdump
CORE_DUMP_DEST=$ORACLE_HOME/admin/+ASM/cdump


3)  Creating ADMIN directories:

$ cd $ORACLE_HOME
$ mkdir dbs
$ mkdir admin
$ cd admin
$ mkdir +ASM
$ cd +ASM
$mkdir udump
$mkdir bdump
$mkdir cdump


4)  Starting the ASM Instance

For UNIX platforms:

# su - oracle
$ ORACLE_SID=+ASM; export ORACLE_SID
$ sqlplus "/ as sysdba"
SQL> startup


For Microsoft Windows platforms:

C:/> oradim -new -asmsid +ASM -syspwd change_on_install
-pfile D:/oracle/product/10.2.0/admin/+ASM/pfile/init.ora -spfile
-startmode manual -shutmode immediate

Instance created.

C:/> oradim -edit -asmsid +ASM -startmode a
C:/> set oracle_sid=+ASM
C:/> sqlplus "/ as sysdba"

SQL> startup pfile='C:/oracle/product/10.1.0/admin/+ASM/pfile/init.ora';


5)  Create SPFILE from PFILE:

SQL> create spfile='+ASM' from pfile;


6)  For Unix platform, put an entry in the oratab file for the ASM intance:

+ASM::Y


You may get the following error on first start:

ORA-15110: no diskgroups mounted

       This error can be safely ignored, when creating a new diskgroup, the diskgroup name will be added automatically to the asm_diskgroups parameter and you will not get this error again.

 

20How To Create An SYSASM User On Release 11g

       On release 11g and onwards, you need to create an ASM user and grant the SYSASM role. The purpose of the SYSASM role is to provide a level of separation between the RDBMS & ASM credentials.

       The SYSASM role, which has full capability on the ASM instance, is authenticated through the OSASM user group, similar to the SYSDBA roles, which is authenticated through OSDBA.

1) The following example shows how to connect as SYSASM and configure a new user for SYSASM role:

 

+ASM:oracle> export ORACLE_SID=+ASM

+ASM:oracle>sqlplus "/as sysasm"

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Oct 1 15:44:00 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user ASMUSER identified by oracle;

User created.

SQL> grant SYSASM, SYSOPER to ASMUSER;

Grant succeeded.

SQL> exit


2) Then connect as ASMUSER:

 

+ASM:oracle> export ORACLE_SID=+ASM

+ASM:oracle>sqlplus "ASMUSER/oracle as sysasm"

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Oct 1 15:45:26 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$pwfile_users;

USERNAME SYSDBA SYSOPE SYSASM 
------------------------------ ------ ------ ------ 
SYS TRUE TRUE TRUE 
ASMUSER FALSE TRUE TRUE

 

21How to Create Transportable Tablespaces Where the Source and Destination are ASM-Based

       The purpose of this note is to describe the method to create transportable tablespaces(TTS) where the source and destination databases are ASM-based. This method uses standard utilities such as; DataPump and the database package dbms_file_transfer. The dbms_file_transfer package will be used to transfer the Datapump metadata and datafiles to the remote database server.

       This note will illustrate a working example of creating and plugging in transportable tablespaces. In the example two servers, host1 and host2 are in different locations, with each one on an independent Hitachi storage array. Server host1 is the source database  and will house database named db1. The server host2, which is deemed the target server and will subscribe to the transportable tables, will house the database called db2.

 

示例如下:

       This note will illustrate a working example of creating and plugging in transportable tablespaces. In the example two servers, host1 and host2 are in different locations, with each one on an independent Hitachi storage array. Server host1 is the source database and will house database named db1. The server host2, which is deemed the target server and will subscribe to the transportable tables, will house the database called db2. 

TTS Implementation illustration

Preliminary effort to setup TTS

1.  Create or use two existing tablespaces on the source database. Although having two tablespaces is not necessary, it was merely shown here to exercise object dependency. Note that OMF is being employed in this illustration, thus please set the init.ora parameter DB_CREATE_FILE_DEST to the appropriate disk group name. 

SQL> show parameter db_create_file_dest
NAME TYPE VALUE
---------------------- ----------- ------------------------------
db_create_file_dest string +DATA

SQL> create tablespace tts_1; 

SQL> create tablespace tts_2;

2. Create a table in TTS_1 and an index in TTS_2 to ensure the tablespaces have object dependencies:

SQL> connect scott/tiger
Connected.
SQL> create table emp_copy tablespace tts_1 as select * from emp;

Select count(*) from emp_copy;
10 rows returned

SQL> create index emp_copy_i on emp_copy (empno) 
2 tablespace tts_2;


3. Check to make sure that you have a self contained transportable set. Oracle provides a PLSQL package that aids in this check.

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘tts_1,tts_2’ TRUE);

Then query the TRANSPORT_SET_VIOLATIONS view, to see if any 
dependency violations exist.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

No rows returned


4. Create a new Service names entry, which will point to the destination database where the tablespaces will be transported. For example, add the following lines to tnsnames.ora:

DB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = host2.us.oracle.com)
(INSTANCE_NAME = db2)
)



5. As SYSTEM, create a database link between the two databases. This is needed because we will be using the DBMS_FILE_TRANSFER package to move metadata between the two databases.

SQL> create database link db2 connect to system identified by manager1 using 'db2';

5. Create a directory object in the source database db1, to hold the dumpfile. Since we are using ASM, this needs to be an ASM object:

make sure the directory path ends with an ‘/’ 

SQL> create directory tts_dump as ’+DATA/’;

6. Create another directory object in the source database, which points to an operating system path, for the log file:

SQL> create directory tts_dump_log as ‘/export/home/tts_log/’;


7. Create a directory object in the source database that points to the datafiles. 

SQL> create directory tts_datafile as ’+DATA/db1/datafile/’;

8. Grant read/write access to the user you will perform. the export as (only needed if using a non-privileged user):

SQL> grant read, write on directory tts_dump to system;

SQL> grant read, write on directory tts_dump_log to system;

SQL> grant read, write on directory tts_dump_datafile to system;


9. Repeat the last four steps (5-8) on the target database db2, as well.

10. Make all tablespaces in the transportable set, to read-only.

SQL> ALTER TABLESPACE tts_1 READ ONLY;

SQL> ALTER TABLESPACE tts_2 READ ONLY;

11. Check the status of the tablespaces on the source database

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
TTS_1 READ ONLY
TTS_2 READ ONLY

Export metadata

12. Export the metadata for the two tablespaces

[ora10g@host1]$ expdp system/manager1 directory=tts_dump dumpfile=tts1_db1.dmp logfile=tts_dump_log:tts.log
transport_tablespaces=tts_1,tts_2 transport_full_check=y

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02": system/******** directory=tts_datafile dumpfile=tts1.dmp logfile=tts_dump_log:tts.log transport
_tablespaces=tts_1,tts_2 transport_full_check=y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02" successfully loaded/unloaded
***********************************************************************Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_02 is:
+DATA/tts1.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02" successfully completed at 14:00:34
Movement of data and Enabling TTS 

13. Use DBMS_FILE_TRANSFER to send the dump file across to the target

[ora10g@host1]$ sqlplus system/manger1
SQL> begin
2 dbms_file_transfer.put_file
3 (source_directory_object => 'tts_dump',
4 source_file_name => 'tts1_db1.dmp',
5 destination_directory_object => 'tts_dump',
6 destination_file_name => 'tts1_db1.dmp',
7 destination_database => 'db2');
8 end;
9 /

14. Check the file names on the source database for the two tablespaces being transported.

SQL> SELECT file_name FROM dba_data_files
2 WHERE tablespace_name LIKE 'TTS%';

FILE_NAME
-------------------------------------------------+DATA/db2/datafile/tts_1.294.590721319
+DATA/db2/datafile/tts_2.295.586721335

15. Transfer the two datafiles to the target database using DBMS_FILE_TRANSFER.

TTS1 datafile 

SQL> begin
2 dbms_file_transfer.put_file
3 (source_directory_object => 'tts_datafile',
4 source_file_name => 'tts_1.294.570721319',
5 destination_directory_object => ' tts_datafile',
6 destination_file_name => 'tts1_db1.dbf',
7 destination_database => 'db2');
8 end;
9 /

TTS2 datafile
SQL> begin
2 dbms_file_transfer.put_file
3 (source_directory_object => 'tts_datafile',
4 source_file_name => ‘tts_2.295.586721335’,
5 destination_directory_object => 'tts_datafile',
6 destination_file_name => 'tts2_db1.dbf',
7 destination_database => 'db2');
8 end;
9 /


16. On the host2 (target server) import the datafile metadata using DataPump.

imp.par has the following contents:

directory=tts_dump
dumpfile=tts1_db1.dmp
logfile=tts_dump_log:tts1.log
TRANSPORT_DATAFILES='+DATA1/tts1_db1.dbf','+DATA1/tts2_db1.dbf'
keep_master=y


Note, for the TRANSPORT_DATAFILES parameter, you can either use the alias names (files names in the dbms_file_transfer), or use the systems generated names generated by DBMS_FILE_TRANSFER (these start with the name “File_Transfer.xxxx.xxxxxx’). To determine the name system generated names, use the asmcmd line tool by simply doing a 
“cd +DATA/db2/datafile”, followed by the ls –l command.


[ora10g@host2]$ impdp system/oracle parfile=imp.par

Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_03" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_03": system/******** parfile=impdp.par 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_03" successfully completed at 15:05:00

17. Switch the tablespaces back to read-write mode.

SQL> ALTER TABLESPACE tts_1 READ WRITE;

SQL> ALTER TABLESPACE tts_2 READ WRITE;


18. Verify that the datafiles are successfully plugged in.

SQL> select name from v$datafile;

NAME
--------------------------------------------------
+DATA/db2/datafile/system.271.599658207
+DATA/db2/datafile/undotbs1.268.599658207
+DATA/db2/datafile/sysaux.270.599658207
+DATA/db2/datafile/users.267.599658209
+DATA/db2/datafile/example.262.599658459
+DATA/db2/datafile/tts2_db1.dbf
+DATA/db2/datafile/tts1_db1.dbf


19. Validate the data got there by selecting the required tables.

SQL> create table emp_copy tablespace tts_1 as select * from emp;

SQL> Select count(*) from emp_copy;
10 rows returned


22How To Delete Archive Log Files Out Of +Asm

       1. Run the following SQL to find the full path for the archivelog files.


SELECT CONCAT('+'||gname, SYS_CONNECT_BY_PATH(aname,'/')) full_path,
dir, sys FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex, a.ALIAS_DIRECTORY dir, a.SYSTEM_CREATED sys
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
ORDER BY dir desc, full_path asc;


The results will look similar to the following.

+DSKGRP1/MAXCP/ARCHIVELOG/2004_11_15/thread_1_seq_970.1236.1

2. When the file is created by Oracle the format in +ASM is:
   DISKGROUP_NAME/db_name/file_type/creation_date/.

This SQL will generate the SQL necessary to delete all archivelogs out of +ASM.


Note: Change the  and to the actual values from what is returned from previous SQL output.

 

select 'alter diskgroup DSKGRP1 drop file
''//ARCHIVELOG/'|| to_char(b.creation_date,'YYYY_MM_DD') ||'/'|| a.name||''';'
from v$asm_alias a, v$asm_file b
where a.group_number = b.group_number
and a.file_number = b.file_number
and b.type='ARCHIVELOG'
order by a.name;

This will generate SQL similar to the following.

alter DISKGROUP DSKGRP1 drop file  '+DSKGRP1/MAXCP/ARCHIVELOG/2004_11_15/thread_1_seq_970.1236.1';


23How To Drop and Recreate ASM Diskgroup

       This article describe how we can drop and recreate ASM diskgroup when its not possible to perform. this through SQL command.


When a member disk in external redundancy diskgroup is no longer present (or destroyed beyond repair) and you want to drop the diskgroup completely and recreate it or add the disks of the diskgroup to other diskgroups. The diskgroup can not be mounted, because one of the disks is missing, hence the "drop diskgroup" command will not work.

In 10g you can use a workaround by erasing the header of the disk using the dd command.

In 11g, the diskgroup can be dropped when its dismounted with FORCE option:

Oracle Database Storage Administrator's Guide - 11g Release 1 (11.1)
Administering ASM Disk Groups
Dropping Disk Groups
http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmdiskgrps.htm#i1020539


Erasing the header using "dd" command is very dangerous operation and need to be done under support supervision and when it confirmed by support that fixing the header is impossible.

In all cases and before the erasing operation you should gather the following information:

1. alert.log file from all ASM instances going back to last successful mount for diskgroup.
2. OS logs.
3. Backup disk header for all member disks in diskgroup:

dd if=/dev/raw/raw13 of=/tmp/raw13.txt bs=1048576 count=100

 

Solution

1. Erase ASM metadata information for disk using OS command:

!!! YOU HAVE TO BE CAREFUL WHEN TYPING DISK NAME !!!

For Unix platforms using 'dd' Unix command.
==================================

Example:

dd if=/dev/zero of=/dev/raw/raw13 bs=1048576 count=50

 

The dd utility copies the specified input file to the specified output. 

Description for used options:

'if=file'
    Read from file/device.
'of=file'
    Write to file/device.
'bs=bytes'
    Set both input and output block sizes to bytes.
'count=blocks'
    Copy blocks 'ibs'-byte blocks from the input file


This puts zeroes to the first 50 MB of disk /dev/raw/raw13, erasing all information. 

For MS Windows platforms using 'asmtool' (command line) or 'asmtoolg' (GUI)
============================================================

Example:

C:/>asmtool -delete //./ORCLDISKASM7


2. Connect to ASM instance to check the current disk status:

SQL> select header_status,path from v$asm_disk;


You should see the header_status for the cleared disks as " CANDIDATE " which means that disk's metadata erased and it can be used again.

select GROUP_NUMBER,NAME,HEADER_STATUS,TOTAL_MB,FREE_MB from V$ASM_DISK;

GROUP_NUMBER NAME                           HEADER_STATU   TOTAL_MB    FREE_MB
------------ ------------------------------ ------------ ---------- ----------
           0                                CANDIDATE         10000          0
           1 DATA_0000                      MEMBER            10000       9933
           1 DATA_0003                      MEMBER            10000       9933


If you dropped all diskgroup member disks, you should not see the diskgroup any more because all its member disks were cleared.

SQL> select name,STATE from v$asm_diskgroup;


3. Now, you can use the erased disk(s) to create a new disk group or add the disk to existing diskgroup.


24How to duplicate a controlfile when ASM is involved
       This document presents different options to duplicate a controlfile in environments using ASM.  The procedure applies either to duplicate a controlfile into ASM using a controlfile stored in file system or to duplicate a controlfile into ASM using a controlfile already stored in ASM.

 

Case One:

Duplicating a controlfile into ASM when original controlfile is stored on a file system

On the database instance:

1. Identify the location of the current controlfile:
    SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oradata2/102b/oradata/P10R2/control01.ctl'
 

2. Shutdown the database and start the instance:
    SQL> shutdown normal
    SQL> startup nomount


3. Use RMAN to duplicate the controlfile:
    $ rman nocatalog
    RMAN>connect target
    RMAN>restore controlfile to '' from '';

RMAN> restore controlfile to '+DG1' from '/oradata2/102b/oradata/P10R2/control01.ctl';

Starting restore at 23-DEC-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 23-DEC-05


       We are only specifying the name of the diskgroup, so Oracle will create an OMF (Oracle Managed File).  Use ASMCMD or sqlplus to identify the name assigned to the controlfile 

4. On the ASM instance, identify the name of the controlfile:

 

Using ASMCMD:
$ asmcmd
ASMCMD> cd
ASMCMD> find -t controlfile . *

 

Changing the current directory to the diskgroup where the controlfile was created will speed the search.

Output:

 

ASMCMD> find -t controlfile . *
+DG1/P10R2/CONTROLFILE/backup.308.577785757
ASMCMD>

 

Note the name assigned to the controlfile. Although the name starts with the backup word, that does not indicate is a backup of the file.  This just the name assigned for the identical copy of the current controlfile.

 

5. On the database side:

ü  Modify init.ora or spfile, adding the new path to parameter control_files.

ü  if using init.ora, just modify the control_files parameter and restart the database.

ü  If using spfile,

 

1) startup nomount the database instance
2) alter system set control_files='+DG1/P10R2/CONTROLFILE/backup.308.577785757','/oradata2/102b/oradata/P10R2/control01.ctl' scope=spfile;

For RAC instance:

alter system set control_files='+DG1/P10R2/CONTROLFILE/backup.308.577785757','/oradata2/102b/oradata/P10R2/control01.ctl' scope=spfile sid='*';

3) shutdown immediate 

4) start the instance.

 

       Verify that new control file has been recognized. If the new controlfile was not used, the complete procedure needs to be repeated.

 

Case TwoDuplicating a controlfile into ASM  using a specific name

       It is also possible to duplicate the controlfile using a specific name for the new controlfile.  In the following example, the controlfile is duplicated into a new diskgroup where   controlfiles have not been created before.

On the ASM instance:

A. Create the directory  to store the new controlfile.  

     SQL> alter diskgroup add directory '+//CONTROLFILE';

    Note that ASM uses directories to store the files and those are created automatically when using OMF files. (just specifying the diskgroup name).  Asumming that other OMF files were created on the diskgroup, the first directory (DB_NAME) already exist, so it is only required to create the directory for the controlfile.

     

SQL> alter diskgroup DG1 add directory '+DG1/P10R2/CONTROLFILE';

      ASMCMD can also be used

ASMCMD>cd dg1
ASMCMD>mkdir controlfile

 

On the database instance:

B. Edit init.ora or spifile and modify parameter control_file:
     control_files='+DG1/P10R2/CONTROLFILE/control02.ctl','/oradata2/102b/oradata/P10R2/control01.ctl'

C. Identify the location of the current controlfile:
   

SQL> select name from v$controfile;
          NAME
          --------------------------------------------------------------------------------
/oradata2/102b/oradata/P10R2/control01.ctl'


D. Shutdown the database and start the instance:
    SQL> shutdown normal
    SQL> startup nomount


E. Use RMAN to duplicate the controlfile:
    $ rman nocatalog
    RMAN>connect target
    RMAN>restore controlfile to '' from '';

RMAN> restore controlfile to '+DG1/PROD/controlfile/control02.ctl' from '/oradata2/102b/oradata/P10R2/control01.ctl';

Starting restore at 23-DEC-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 23-DEC-05


F. Start the database:
    SQL> alter database mount;
    SQL> alter database open;


       Now, using ASMCMD to search for information for the controlfiles,  the find -t contrlfile command will return two records.  That does not indicate there were created two controlfiles.  The name specified is an alias name and is only an entry in the ASM metadata (V$ASM_ALIAS). Oracle will create the alias and the OMF entry when user specifies the file name.

Case ThreeDuplicating a controlfile into ASM when original controlfile is stored on ASM

If using spfile to start the instance:

1. Modify the spfile specifically the parameter control_files. In this example, a second controlfile is going to be created on same diskgroup DATA1.

sql> alter system set control_files='+DATA1/v102/controlfile/current.261.637923577','+DATA1' scope=spfile sid='*';



2. Start the instance in NOMOUNT mode.

3. From rman, duplicate the controlfile

$ rman nocatalog
RMAN>connect target
RMAN> restore controlfile from '+DATA1/v102/controlfile/current.261.637923577';


The output for the execution is like:

Starting restore at 08-NOV-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 instance=V1021 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATA1/v102/controlfile/current.261.637923577
output filename=+DATA1/v102/controlfile/current.269.638120375
Finished restore at 08-NOV-07

Note that the command prints the name of the new created file: +DATA1/v102/controlfile/current.269.638120375

4. Mount and Open the database

RMAN> sql 'alter database mount';
RMAN> sql 'alter database open';



5. Validate both controlfiles are present

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA1/v102/controlfile/current.261.637923577
+DATA1/v102/controlfile/current.269.638120375



6. Modify the control_file parameter with the complete path of the new file:

sql> alter system set control_files='+DATA1/v102/controlfile/current.261.637923577','+DATA1/v102/controlfile/current.269.638120375'
scope=spfile sid='*';


Next time instance are restarted, will pick both files.


When using init.ora file:


1) Edit init.ora and add new disk group name or same disk group name for mirroring controlfiles.

Example:

control_files=('+GROUP1','+GROUP2')


(2) Start the instance in NOMOUNT mode.

(3)  Execute restore command, to duplicate the controlfile using the original location. Presuming, your current controlfile location DISK path is '+data/V10G/controlfile/Current.260.605208993' , execute:

RMAN> restore controlfile from '+data/V10G/controlfile/Current.260.605208993';

Starting restore at 29-APR-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=317 devtype=DISK

channel ORA_DISK_1: copied controlfile copy
output filename=+GROUP2/v10g/controlfile/backup.268.7
output filename=+GROUP2/v10g/controlfile/backup.260.5
Finished restore at 29-APR-05


(4) Mount and open the database:

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> alter database open;

database opened

RMAN> exit


(5) Verify new mirrored controlfiles via sqlplus

SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +GROUP2/v10g/controlfile/backup.268.7, +GROUP2/v10g/controlfile/backup.260.5


25How to Increase CSS Misscount in single instance ASM installations

       CSS is an essential component of ASM installations as it is required to syncrohize ASM resources and ASM crashes in the absence of CSS component.

       When there is any OS resource saturation (ie, CPU, memory shortage, etc), CSS sometimes could not obtain the OS resource and thus determines there is a problem and abort itself. A solution for this issue is to add more CPU/memory resources to the server or to move resource intensive applications (grid control, etc.) and some databases to another server to consume less resources on this server.

       Another workaround is to bump up the CSS misscount. This will give the OS more time to recover before css eviction. The purpose of this note is to document the steps needed to modify the CSS misscount in single instance ASM installations.

       Those steps should NOT be followed for RAC installations. Please follow Note 284752.1 to see the steps needed to modify the CSS misscount in 10g RAC environment. Please also review Note 294430.1 to understand the implications before editing misscount settings.

 

 

Solution

Steps to increase CSS process miscount to 300 (5 minutes). 

1)  Shut down all ASM instances and DB instances depending on ASM. 

2) Run $ORACLE_HOME/bin/localconfig delete to remove the existing CSS configuration. 

3) Edit the $ORACLE_HOME/bin/localconfig file and look for the CLSCFG invocation, it should look something like this: 

$CLSCFG -local -o $CH -l "AMERICAN_AMERICA.WE8ISO8859P1" 

At the end of the line, add trace and misscount params as follows:

#Create CSS keys only if needed to 
$CLSCFG -local -o $CH -l "AMERICAN_AMERICA.WE8ISO8859P1" -misscount 300 

4)  Re-add CSS by running $ORACLE_HOME/bin/localconfig add. 

5)  Restart ASM and DB instances.

 

 

10g RAC: Steps To Increase CSS Misscount Reboottime and Disktimeout

  The purpose of this note is to document the steps needed to modify the CSS misscount, reboottime and  disktimeout settings.  Please review Note 294430.1 to understand the implications before editing these settings.

 

 

Steps To Modify The CSS Miscount

--------------------------------

 

  1) Shut down CRS on all but one node. For exact steps use Note 309542.1

  2) Execute crsctl as root to modify the misscount:

     $ORA_CRS_HOME/bin/crsctl set css misscount

     where is the maximum i/o latency to the voting disk +1 second

  3) Reboot the node where adjustment was made

  4) Start all other nodes shutdown in step 1

 

With the Patch:4896338 for 10.2.0.1 there are two additional settings that can be tuned.  This change is incorporated into the 10.2.0.2 and 10.1.0.6 patchsets.  

 

 

These following are only relevant on 10.2.0.1 with Patch:4896338In addition to MissCount, CSS now has two more parameters:

  1) reboottime (default 3 seconds) - the amount of time allowed for a node

     to complete a reboot after the CSS daemon has been evicted. (I.E. how

     long does it take for the machine to completely shutdown when you do a

     reboot)

  2) disktimeout (default 200 seconds) - the maximum amount of time allowed

     for a voting file I/O to complete; if this time is exceeded the voting

     disk will be marked as offline.  Note that this is also the amount of

     time that will be required for initial cluster formation, i.e. when no

     nodes have previously been up and in a cluster.

 

       $CRS_HOME/bin/crsctl set css reboottime [-force]  ( is seconds)

       $CRS_HOME/bin/crsctl set css disktimeout [-force] ( is seco

 

Confirm the new css  misscount setting via ocrdump

 

 

CSS Timeout Computation in 10g RAC (10gR1 and 10gR2)

The purpose of this Note is to document default CSS misscount timeout calculations in 10g  Release 1,  10g Release 2 , 11g and higher versions.

Scope and Application

  • Define misscount parameter
  • Define the default calculations for the misscount parameter
  • Describe Cluster Synchronization Service (CSS) heartbeats and their interrelationship
  • Describe the cases where the default calculation may be too sensitive

CSS Timeout Computation in Oracle Clusterware

MISSCOUNT DEFINITION AND DEFAULT VALUES
The CSS misscount parameter represents the maximum time, in seconds, that a network heartbeat can be missed before entering into a cluster reconfiguration to evict the node. The following are the default values for the misscount parameter and their respective versions when using Oracle Clusterware* in seconds:

OS

10g (R1 &R2)

11g

Linux

60

30

Unix

30

30

VMS

30

30

Windows

30

30

*CSS misscount default value when using vendor (non-Oracle) clusterware is 600 seconds. This is to allow the vendor clusterware ample time to resolve any possible split brain scenarios.

On AIX platforms with HACMP starting with 10.2.0.3 BP#1, the misscount is 30. This is documented in Note 551658.1

CSS HEARTBEAT MECHANISMS AND THEIR INTERRELATIONSHIP
The synchronization services component (CSS) of the Oracle Clusterware maintains two heartbeat mechanisms 1.) the disk heartbeat to the voting device and 2.) the network heartbeat  across the interconnect which establish and confirm valid node membership in the cluster. Both of these heartbeat mechanisms have an associated timeout value. The disk heartbeat has an internal i/o timeout interval (DTO Disk TimeOut), in seconds, where an i/o to the voting disk must complete. The misscount parameter (MC), as stated above, is the maximum time, in seconds, that a network heartbeat  can be missed. The disk heartbeat i/o timeout interval is directly related to the misscount parameter setting. There has been some variation in this relationship 
between versions as described below:

9.x.x.x

NOTE, MISSCOUNT WAS A  DIFFERENT ENTITY IN THIS RELEASE

10.1.0.2

No one should be on this version

10.1.0.3

DTO = MC - 15 seconds

10.1.0.4

DTO = MC - 15 seconds

10.1.0.4+Unpublished Bug 3306964

DTO = MC - 3 seconds

10.1.0.4 with CRS II Merge patch

DTO =Disktimeout (Defaults to 200 seconds) Normally OR Misscount seconds only during initial Cluster formation or Slightly before reconfiguration

10.1.0.5

IOT = MC - 3 seconds

10.2.0.1 +Fix for unpublished Bug 4896338

IOT=Disktimeout (Defaults to 200 seconds) Normally OR Misscount seconds only during initial Cluster formation or Slightly before reconfiguration

10.2.0.2

Same as above (10.2.0.1 with Patch Bug:4896338

10.1 - 11.1

During node join and leave (reconfiguration) in a cluster we need to reconfigure, in that particular case we use Short Disk TimeOut (SDTO) which is in all versions SDTO = MC – reboottime (usually 3 seconds)

Misscount drives cluster membership reconfigurations and directly effects the availability of the cluster. In most cases, the default settings for MC should be acceptable.  Modifying the default value of misscount not only influences the timeout interval for the i/o to the voting disk, but also influences the tolerance for missed network heartbeats across the interconnect.

LONG LATENCIES TO THE VOTING DISKS
If I/O latencies to the voting disk are greater than the default DTO calculations noted above, the cluster may experience CSS node evictions depending on (a)the Oracle Clusterware (CRS) version, (b)whether merge patch has been applied and (c)the state of the Cluster. More details on this are covered in the section "Change in Behavior. with CRS Merge PATCH (4896338 on 10.2.0.1)".

These latencies can be attributed to any number of problems in the i/o subsystem or problems with any component in the i/o path. The following is a non exhaustive list of reported problems which resulted in CSS node eviction due to latencies to the voting disk longer than the default Oracle Clusterware i/o timeout value(DTO):

  1. QLogic HBA cards with a Link Down Timeout greater than the default misscount.
  2. Bad cables to the SAN/storage array that effect i/o latencies
  3. SAN switch (like Brocade) failover latency greater than the default misscount
  4. EMC Clariion Array when trespassing the SP to the backup SP greater than default misscount
  5. EMC PowerPath path error detection and I/O repost and redirect greater than default misscount  
  6. NetApp Cluster (CFO) failover latency greater than default misscount
  7. Sustained high CPU load which effects the CSSD disk ping monitoring thread
  8. Poor SAN network configuration that creates latencies in the I/O path.

The most common problems relate to multi-path IO software drivers, and the reconfiguration times resulting from a failure in the IO path. Hardware and (re)configuration issues that introduce these latencies should be corrected. Incompatible failover times with underlying OS, network or storage hardware or software may be addressed given a complete understanding of the considerations listed below.

Misscount should NOT be modified to workaround the above-mentioned issues. Oracle support recommends that you apply the latest patchset which changes the CSS behaviour. More details covered in next section.


Change in Behavior. with Bug:4896338 applied on top of 10.2.0.1
Starting with 10.2.0.1+Bug:4896338, CSS will not evict the node from the cluster due to (DTO) I/O to voting disk taking more than misscount seconds unless it is during the initial cluster formation or slightly before reconfiguration. 
So if we have a N number of nodes in a cluster and one of the nodes takes more than misscount seconds to access the voting disk, the node will not be evicted as long as the access to the voting disk is completed within disktimeout seconds. Consequently with this patch, there is no need to increase the misscount at all.

Additionally this merge patch introduces Disktimeout  which is the amount of time that a lack of disk ping to voting disk(s) will be tolerated.

Note:  applying the patch will not change your value for Misscount. 

The table below explains in the conditions under which the eviction will occur

Network Ping

Disk Ping

Reboot

Completes within misscount seconds

Completes within Misscount seconds

N

Completes within Misscount seconds

Takes more than misscount seconds but less than Disktimeout seconds

N

Completes within Misscount seconds

Takes more than Disktimeout seconds

Y

Takes more than Misscount Seconds

Completes within Misscount seconds

Y

 


 

  

* By default Misscount is less than Disktimeout seconds

CONSIDERATIONS WHEN CHANGING MISSCOUNT FROM THE DEFAULT VALUE

  1. Customers drive SLA and cluster availability. The customer ultimately defines Service Levels and availability for the cluster. Before recommending any change to misscount, the full impact of that change should be described and the impact to cluster availability measured.
  2. Customers may have timeout and retry logic in their applications. The impact of delaying reconfiguration may cause 'artificial' timeouts of the application, reconnect failures and subsequent logon storms.
  3. Misscount timeout values are version dependent and are subject to change. As we have seen, misscount calculations are variable between releases and between versions within a release. Creating a false dependency on misscount calculation in one version may not be appropriate for later versions.
  4. Internal I/O timeout interval (DTO) algorithms may change in later releases as stated above, there exists a direct relationship between the internal I/O timeout interval and misscount. This relationship is subject to change in later releases.
  5. An increase in misscount to compensate for i/o latencies directly effects reconfiguration times for network failures. The network heartbeat is the primary indicator of connectivity within the cluster. Misscount is the tolerance level of missed 'check ins' that trigger cluster reconfiguration. Increasing misscount will prolong the time to take corrective action in the event of network failure or other anomalies effecting the availability of a node in the cluster. This directly effects cluster availability.
  6. Changing misscount to workaround voting disk latencies will need to be corrected when the underlying disk latency is corrected, misscount needs to be set back to the default The customer needs to document the change and set the parameter back to the default when the underlying storage I/O latency is resolved.
  7. Do not change default misscount values if you are  running Vendor Clusterware along with Oracle Clusterware. The default values for misscount should not be changed when using vendor clusterware. Modifying misscount in this environment may cause clusterwide outages and potential corruptions.
  8. Changing misscount parameter incurs a clusterwide outage. As note below, the customer will need to schedule 
    a clusterwide outage to make this change.
  9. Changing misscount should not be used to compensate for poor configurations or faulty hardware
  10. Cluster and RDBMS availability are directly effected by high misscount settings.
  11. In case of stretched clusters and stretched storage systems and a site failure where we loose one storage and N number of nodes we go into a reconfiguration state and then we revert to ShortDiskTimeOut value as internal I/O timeout for the votings. Several cases are known with stretched clusters where when a site failure happen the storage failover cannot complete within SDTO. If the I/O to the votings is blocked more than SDTO the result is node evictions on the surviving side.


To Change MISSCOUNT back to default Please refer to Note:284752.1
THIS IS THE ONLY SUPPORTED METHOD. NOT FOLLOWING THIS METHOD RISKS EVICTIONS AND/OR CORRUPTING THE OCR

10g Release 2 MIRRORED VOTING DISKS AND VENDOR MULTIPATHING SOLUTIONS 

Oracle RAC 10g Release 2 allows for multiple voting disks so that  the customer does not have to rely on a multipathing solution from a storage vendor. You can have n voting disks (up to 31) where n = m*2+1 where m is the number of disk failures you  want to survive. Oracle recommends each voting disk to be on a separate physical disk.


26How to map asm files with online database files

       One of the features of ASM is databases consolidation.  A single diskgroup will be the repository for multiple databases. The result,  having a large number of datafiles.  If the files were created using OMF (Oracle Managed Files)  format, when the datafile is deleted on the database side, it is also deleted on the ASM side.  There may be situations when the datafiles are not deleted on the ASM side, particularly when not using OMF format.


       With large diskgroups holding large number of datafiles it is necessary to make good usage of the storage and reclaim space allocated for files not used by the databases. 

       The following script. will provide the list of files stored in ASM and CURRENTLY NOT OPENED  by any database client of the diskgroups.   It is very important to validate the results of the query, particularly for files that could have been marked temporary offline.   It does not apply to READ ONLY tablespaces, because once the file is opened by the database, it will be returned by the query.

       Note:  The parameter file (spfile) will be reported always as not opened.   Always double check the file before deleting it.

Software Requirements/Prerequisites

- The query has to be executed on the ASM instance.
- Diskgroups need  to be mounted.

Configuring the Script

None. Download the script. and execute on the ASM instance

Running the Script

$ sqlplus '/ as sysdba'
sql>@files_not_opened

Caution

This script. is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Proofread this script. before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script. may not be in an executable state when you first receive it. Check over the script. to ensure that errors of this type are corrected.

Script


The following code can be downloaded here

set pagesize 0
set linesize 200
col full_alias_path format a80
/*+ ----------------------------------------------------------------
    Query will return all the files stored on ASM but not currently
    opened by any database client of the diskgroups
    ordered by group number, file type
    ---------------------------------------------------------------*/

select * from (
/*+ -----------------------------------------------------------------
    1st branch returns all the files stored on ASM
    -----------------------------------------------------------------*/
select  x.gnum,x.filnum,x.full_alias_path,f.ftype from (
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
            a.reference_index rindex,a.group_number gnum,a.file_number filnum
      FROM v$asm_alias a, v$asm_diskgroup g
      WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex) x,
(select group_number gnum,file_number filnum, type ftype from v$asm_file order by group_number,file_number) f
where x.filnum != 4294967295
and x.gnum=f.gnum and x.filnum=f.filnum
MINUS
/*+ --------------------------------------------------------------
    2nd branch returns all the files stored on ASM
    and currently opened by any database client of the diskgroups
    -----------------------------------------------------------------*/
select x.gnum,x.filnum,x.full_alias_path,f.ftype
from ( select id1 gnum,id2 filnum from v$lock where type='FA' and  (lmode=4 or lmode=2)) l,
(
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
            a.reference_index rindex,a.group_number gnum,a.file_number filnum
      FROM v$asm_alias a, v$asm_diskgroup g
      WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex
) x,
(select group_number gnum,file_number filnum, type ftype from v$asm_file order by group_number,file_number) f
where x.filnum != 4294967295 and
x.gnum=l.gnum and x.filnum=l.filnum and x.gnum=f.gnum and x.filnum=f.filnum) q
order  by q.gnum,q.ftype;

 

The key of this sql is the reference to v$lock view.  For any file stored on ASM and opened by a database instance, there will be an entry on v$lock:

 Column

Description

type

The enqueue name is FA (File Access)

ID1

For this enqueue, represents the diskgroup number

ID2

For this enqueue, represents the ASM file number

mode

If the file is opened, mode will be 4 (Shared) for 10g, and 2 (row S) for 11g


The first branch of the main query will return ALL the ASM files stored on the diskgroup
The second branch of the main query will return all the ASM files currently opened by any database.
The query finally will return the files stored on ASM but not present on the second query (MINUS), which basically will be the files not currently opened.

Script. Output

This is the first example of the results of the execution of the script.  Note it includes all type of files like ARCHIVELOGS, FLASHBACK LOGS, REDOLOGS. Once it has been confirmed the files are not required any more, then they can be deleted using ASMCMD or SQL running the command ALTER DISKGROUP ... DROP FILE '';


GNUM     FILNUM FULL_ALIAS_PATH
---------- ---------- ----------------------------------------------------------------------
         1        270 +DATA1/G102/ONLINELOG/group_2.270.645209903
         1       3247 +DATA1/G102/ARCHIVELOG/2008_01_30/thread_2_seq_54.3247.645369463
         1       3249 +DATA1/G102/ARCHIVELOG/2008_01_30/thread_1_seq_59.3249.645367259
         1       3250 +DATA1/G102/FLASHBACK/log_104.3250.645367249
         1       3251 +DATA1/G102/FLASHBACK/log_103.3251.645369221
         1       3252 +DATA1/G102/ARCHIVELOG/2008_01_30/thread_1_seq_58.3252.645367197
         1       3254 +DATA1/G102/ARCHIVELOG/2008_01_30/thread_2_seq_53.3254.645369095
         1       3255 +DATA1/G102/ARCHIVELOG/2008_01_30/thread_1_seq_57.3255.645366969
         1       3256 +DATA1/G102/ARCHIVELOG/2008_01_30/thread_2_seq_52.3256.645368439
         1       3257 +DATA1/G102/ARCHIVELOG/2008_02_04/thread_2_seq_59.3257.645786351
         1       3258 +DATA1/G102/FLASHBACK/log_13.3258.645786863





As mentioned before, it is extremely important to review the results of the script.  In this following example, these datafiles are not currently opened but they belong to a database currently closed. 

       GNUM     FILNUM FULL_ALIAS_PATH
---------- ---------- ----------------------------------------------------------------------
         2        256 +DG_NETAPPS/V102/DATAFILE/UNDOTBS2.256.643467013
         2        257 +DG_NETAPPS/NETAPP/DATAFILE/SYSTEM.257.643477635
         2        258 +DG_NETAPPS/NETAPP/DATAFILE/SYSAUX.258.643477637
         2        259 +DG_NETAPPS/NETAPP/DATAFILE/UNDOTBS1.259.643477641
         2        260 +DG_NETAPPS/NETAPP/DATAFILE/USERS.260.643477643
         2        261 +DG_NETAPPS/NETAPP/CONTROLFILE/Current.261.643478451
         2        262 +DG_NETAPPS/NETAPP/ONLINELOG/group_1.262.643478461
         2        263 +DG_NETAPPS/NETAPP/ONLINELOG/group_2.263.643478517
         2        264 +DG_NETAPPS/NETAPP/ONLINELOG/group_3.264.643478569
         2        265 +DG_NETAPPS/NETAPP/TEMPFILE/TEMP.265.643478655
         2        266 +DG_NETAPPS/NETAPP/PARAMETERFILE/spfile.266.643478737
         2        266 +DG_NETAPPS/NETAPP/spfilenetapp.ora
         2        267 +DG_NETAPPS/NETTEST/DATAFILE/UNDOTBS1.267.643723749

       
27How To Migrate ASMLIB devices to Block Devices (non-ASMLIB)
        The next example can be used to remove ASMLIB I/O access  and allow ASM to access the block devices directly:

 

<<< Before Continue With The Next Action Plan, Please Make Sure You Have A Full Database Backup Of All The Databases Allocated In Your Diskgroup>>>

 

1) List all the existing ASMLIB disks to obtain the major and minor numbers (for this example I created DISK1 on /dev/sda5):

 


# /etc/init.d/oracleasm createdisk DISK1 /dev/sda5 
Marking disk "/dev/sda5" as an ASM disk:                   [  OK  ] 

$> ls -l /dev/sda5 
brw-rw----  1 root disk 8, 5 May  5 22:27 /dev/sda5 

$> oracleasm querydisk DISK1 
Disk "DISK1" is a valid ASM disk on device [8, 5]

 

2) List all the existing diskgroups (for this example I created the TEST diskgroup):

 

SQL> create diskgroup TEST external redundancy disk 'ORCL:DISK1'; 

Diskgroup created. 

SQL> select name from v$asm_diskgroup; 

NAME 
------------------------------ 
TEST

 

3) Check disk header thru kfed associated with all the ASM disks to make sure they are valid:

 


$> kfed read /dev/oracleasm/disks/DISK1 
kfbh.endian:                          1 ; 0x000: 0x01 
kfbh.hard:                          130 ; 0x001: 0x82 
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD 
kfbh.datfmt:                          1 ; 0x003: 0x01 
kfbh.block.blk:                       0 ; 0x004: T=0 NUMB=0x0 
kfbh.block.obj:              2147483648 ; 0x008: TYPE=0x8 NUMB=0x0 
kfbh.check:                  3723615947 ; 0x00c: 0xddf1decb 
kfbh.fcn.base:                        0 ; 0x010: 0x00000000 
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000 
kfbh.spare1:                          0 ; 0x018: 0x00000000 
kfbh.spare2:                          0 ; 0x01c: 0x00000000 
kfdhdb.driver.provstr:    ORCLDISKDISK1 ; 0x000: length=13 
kfdhdb.driver.reserved[0]:   1263749444 ; 0x008: 0x4b534944 
kfdhdb.driver.reserved[1]:           49 ; 0x00c: 0x00000031 
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000 
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000 
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000 
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000 
kfdhdb.compat:                168820736 ; 0x020: 0x0a100000 

 

4) Dismount the diskgroups:

 


SQL> alter diskgroup TEST dismount; 

Diskgroup altered.

 

5) Shutdown the ASM instance(s).

 

SQL> shutdown immediate 

ASM instance shutdown

 

6) Unload the ASMLIB module and dismount the asmlib filesystem:

 

root@asmstorage ~]# cd /etc 
[root@asmstorage etc]# cd init.d 
[root@asmstorage init.d]# ./oracleasm stop 
Unmounting ASMlib driver filesystem:                       [  OK  ] 
Unloading module "oracleasm":                              [  OK  ]

 

7) Change the ownership for the block devices associated with the ASMLIB disks to oracle:dba:

 

[root@asmstorage dev]# ls -l  /dev/sda5 
brw-rw----  1 root disk 8, 5 May  5 22:27 /dev/sda5 
[root@asmstorage dev]# chown oracle:dba /dev/sda5 
[root@asmstorage dev]# ls -l  /dev/sda5 
brw-rw----  1 oracle dba 8, 5 May  5 22:27 /dev/sda5

 

8) Startup the ASM instance(s):

 

SQL> startup 
ASM instance started 

Total System Global Area  284565504 bytes 
Fixed Size                  1299428 bytes 
Variable Size             258100252 bytes 
ASM Cache                  25165824 bytes 
ORA-15110: no diskgroups mounted

 

9) Update the asm_diskstring parameter pointing to the block devices:

 

SQL> alter system set asm_diskstring = '/dev/sda5' scope=memory; 

System altered. 

SQL> show parameter asm_diskstring  

NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
asm_diskstring                       string      /dev/sda5 
SQL> 

 

10) Finally, mount the diskgroups:

 

SQL>  alter diskgroup test mount; 

Diskgroup altered. 

SQL>  select name, state from v$asm_diskgroup; 

NAME                           STATE 
------------------------------ ----------- 
TEST                           MOUNTED


28How to move ASM database files from one diskgroup to another
      
 The preferred way of doing the file movement amoung ASM DISKGROUPS is using RMAN. RMAN is critical to Automatic Storage Management and is responsible for tracking the ASM filenames and for deleting obsolete ASM files. Since ASM files cannot be accessed through normal operating system interfaces, RMAN is the preferred means of copying ASM file.



The steps involved in moving a datafile from a diskgroup to another is as given below.

1) Identify the data file to be moved.
2) Identify the diskgroup on to which the file has to be moved.
3) Take the file offline.
4) Copy the file to new diskgroup using Either RMAN or DBMS_FILE_TRANSFER.
5) Rename the file to point to new location.
6) Recover the file.
7) Bring the file online.
8) Verify the new file locations.
9) Delete the file from its original location.



详细步骤如下:


1) Identify the data file to be moved.
 ----------------------------------------
         In database instance

         SQL:ORCL> SELECT FILE_NAME FROM DBA_DATA_FILES:
                            +ASMDSK2/orcl/datafile/users.256.565313879 <======= Move this to ASDSK1.
                            +ASMDSK1/orcl/sysaux01.dbf
                            +ASMDSK1/orcl/undotbs01.dbf
                            +ASMDSK1/orcl/system01.dbf

2) Identify the diskgroup on to which the file has to be moved.
--------------------------------------------------------------
      In ASM instance

SQL:ASM> SELECT GROUP_NUMBER, NAME FROM V$ASM_DISKGROUP;

                           GROUP_NUMBER NAME
                             ------------ ---------
                                             1 ASMDSK1
                                             2 ASMDSK2

3) Take the file offline.

--------------------------
SQL:ORCL> ALTER DATABASE DATAFILE '+ASMDSK2/orcl/datafile/users.256.565313879' OFFLINE;


 4) Now Copy the file from Source diskgroup ASMDSK1 to target Diskgroup ASMDSK2.
--------------------------------------------------------------------------------------------
   Either 
  
    4. a)   DBMS_FILE_TRANSFER   package or 
     4. b)   RMAN 

  

 can be used for this step.
       ( The step 5 to step 8  is based on the filenames  from method b).

        4.a).Using DBMS_FILE_TRANSFER package  
        
        SQL:ORCL>create or replace directory orcl1 as '+asmdsk1/orcl/datafile';
        SQL:ASM> Alter disgroup asmdsk2 add directory  '+asmdsk2/test';
        SQL:ORCL> create or replace directory orcl2 as '+asmdsk2/test';
        
        SQL:ORCL>
                BEGIN
                  DBMS_FILE_TRANSFER.COPY_FILE(
                  source_directory_object => 'ORCL1',
                  source_file_name => 'users.259.565359071',
                  destination_directory_object => 'ORCL2',
                  destination_file_name => 'USERS01.DBF');
                END;                           

Database altered.

 

          4 b).Using RMAN copy the file to new diskgroup.

            $ rman target system@orcl10
            target database Password:
            connected to target database: ORCL (DBID=1089529226)

            RMAN>
RMAN> COPY DATAFILE '+ASMDSK2/orcl/datafile/users.256.565313879' TO '+ASMDSK1';

                   Starting backup at 03-AUG-05
                   using target database controlfile instead of recovery catalog
                   allocated channel: ORA_DISK_1
                   channel ORA_DISK_1: sid=146 devtype=DISK
                   channel ORA_DISK_1: starting datafile copy
                   input datafile fno=00004 name=+ASMDSK2/orcl/datafile/users.256.565313879
                   output filename=+ASMDSK1/orcl/datafile/users.259.565359071 tag=TAG20050803T12110
                   9 recid=2 stamp=565359071
                   channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
                   Finished backup at 03-AUG-05

 

5)  Rename the file to point to new location. 
------------------------------------------- 
       
If you have used DBMS_FILE_TRANSFER (method 4 a)) use the following command to rename: 
       SQL:ORCL> ALTER DATABASE RENAME FILE '+ASMDSK2/orcl/datafile/users.256.565313879' TO '+ASMDSK1/orcl/datafile/users.259.565359071' 

Database altered. 

      If you have used RMAN (method 4 b) use the following option of RMAN 
                 RMAN run { 
                                      set newname for datafile '+ASMDSK2/orcl/datafile/users.256.565313879'                                                                         to '+ASMDSK1/orcl/datafile/users.259.565359071' ; 
                                    switch datafile all; --
更新控制文件
                                    } 

6)  Recover the file.
-------------------
           SQL:ORCL> RECOVER DATAFILE '+ASMDSK1/orcl/datafile/users.259.565359071'
                             Media recovery complete.

7)  Bring the file online.
-----------------------
             SQL:ORCL>ALTER DATABASE DATAFILE '+ASMDSK1/orcl/datafile/users.259.565359071' ONLINE

                                 Database altered.

8) Verify the new file location.
---------------------------------
           SQL:ORCL> SELECT FILE_NAME FROM DBA_DATA_FILES;

                    FILE_NAME
                   -------------------------------------------------------------------------------
                  +ASMDSK1/orcl/datafile/users.259.565359071
                  +ASMDSK1/orcl/sysaux01.dbf
                  +ASMDSK1/orcl/undotbs01.dbf
                  +ASMDSK1/orcl/system01.dbf

 

9) Delete the file from its original location either per SQLPLUS or per ASMCMD:

   e.g.: SQL:ASM> ALTER DISKGROUP ASMDSK2 DROP FILE users.256.565313879;

   or:   ASMCMD> rm -rf

Note:
      Most Automatic Storage Management files do not need to be manually deleted because, as Oracle managed files, they are removed automatically when they are no longer needed.

      However, if you need to drop an Oracle Managed File (OMF) manually you should use the fully qualified filename if you reference the file. Otherwise you will get an error (e.g. ORA-15177).

   e.g.: SQL:ASM> ALTER DISKGROUP ASMDSK2 DROP FILE '+ASMDSK2/orcl/datafile/users.256.565313879';

 

Note: The steps provided above assume that the database is open and in Archivelog mode.    Besides these steps are not appropriated for system or sysaux datafiles. For System and Sysaux an approach similar to the one given below can be used:

 

   1. Create a Copy of datafile in target Diskgroup: 
      RMAN> backup as copy tablespace system format ''; 
       RMAN> backup as copy tablespace sysaux format ''; 

   2. Then shutdown the database and restart to a mounted state 

      RMAN> shutdown immediate; 
      RMAN> startup mount; 

   3. switch the datafiles to the copy 

     RMAN> switch tablespace system to copy; 
      RMAN> switch tablespace sysaux to copy; 

   4. Recover the changes made to these tablespaces; 
      RMAN> recover database;

 

29How to move ASM spfile to a different disk group

       The goal is to move ASM spfile from one disk group to another.


       During initial ASM setup the spfile might have been created in a default disk group (e.g. DATA).  The requirement is now to move the ASM spfile to another disk group.

       According to Oracle ASM documentation it should be possible to use 'asmcmd spmove' command to move ASM spfile:
       Oracle Database Storage Administrator's Guide 11g Release 2 (11.2)
       Section ASMCMD Instance Management Commands
              spmove

      Purpose: Moves an Oracle ASM SPFILE from source to destination and automatically updates the GPnP profile.

But an attempt to move the ASM spfile fails as follows:

$ asmcmd spmove +DATA/asm/asmparameterfile/REGISTRY.253.715881237 +PLAY/spfileASM.ora
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA/asm/asmparameterfile/REGISTRY.253.715881237' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)

 

解决方法:

 

方法一:To move ASM spfile to another disk group, either make use of intermediate pfile:

1.        Create intermediate pfile from the current spfile

2.        Create spfile in a new disk group from the intermediate pfile

3.        Restart the HA stack to verify that ASM starts up fine with moved spfile

4.        Remove the original spfile

 

方法二:or use 'asmcmd spcopy' command

1.        Copy spfile with -u option - to update GPnP profile in RAC environment

2.        Restart the HA stack to verify that ASM starts up fine with copiedspfile

3.        Remove the original spfile

 

 

方法一示例:

       This is an example of moving ASM spfile in a single instance environment, by making use intermediate pfile

1. Create intermediate pfile from the current spfile

 

$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.1.0 Production on
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option

SQL> show parameter spfile

NAME    TYPE    VALUE
------- ------- --------------------------------------------------
spfile  string  +DATA/asm/asmparameterfile/registry.253.715881237

SQL> create pfile='/tmp/pfile+ASM.ora' from spfile;

File created.



2. Create spfile in a new disk group from the intermediate pfile

SQL> create spfile='+PLAY' from pfile='/tmp/pfile+ASM.ora';

File created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option



3. Restart the HA stack to verify that ASM starts up fine with moved spfile

$ crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ''
...
CRS-4133: Oracle High Availability Services has been stopped.

$ crsctl start has
CRS-4123: Oracle High Availability Services has been started.



Verify that the new spfile is being used

$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option

SQL> show parameter spfile

NAME    TYPE    VALUE
------- ------- --------------------------------------------------
spfile  string  +PLAY/asm/asmparameterfile/registry.253.715963539

SQL> select name, state from v$asm_diskgroup;

NAME  STATE
----- -----------
DATA  MOUNTED
PLAY  MOUNTED

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option


The use of new spfile can also be verified with asmcmd as follows:

$ asmcmd spget
+PLAY/asm/asmparameterfile/registry.253.715963539


4. Remove the original spfile

$ asmcmd rm +DATA/asm/asmparameterfile/registry.253.715881237



方法二示例:
And this is an example of moving ASM spfile with 'asmcmd spcopy'

1. Copy spfile with -u option - to update GPnP profile in RAC environment

 

$ asmcmd spget
+DATA/asm/asmparameterfile/registry.253.722601213

$ asmcmd lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Votin g_files Name
MOUNTED EXTERN N 512 4096 1048576 14658 9814 0 9814 0 N DATA/
MOUNTED EXTERN N 512 4096 1048576 9772 8507 0 8507 0 N PLAY/
MOUNTED EXTERN N 512 4096 1048576 9772 9212 0 9212 0 N RECO/

$ asmcmd spcopy -u +DATA/asm/asmparameterfile/registry.253.722601213 +PLAY/spfileASM.ora


2. Restart the HA stack to verify that ASM starts up fine with copiedspfile

$ crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ''
...
CRS-4133: Oracle High Availability Services has been stopped.

$ crsctl start has
CRS-4123: Oracle High Availability Services has been started.

$ asmcmd spget
+PLAY/spfileASM.ora


3. Remove the original spfile

$ asmcmd ls -l +DATA/asm/asmparameterfile
Type Redund Striped Time Sys Name
ASMPARAMETERFILE UNPROT COARSE JUN 25 10:00:00 Y REGISTRY.253.722601213

$ asmcmd rm +DATA/asm/asmparameterfile/registry.253.722601213


       NOTE: The disk group that holds ASM spfile has to have COMPATIBLE.ASM value of 11.2 or higher.


30How To Move Controlfile To