Find ASM Files - Script

SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off

COLUMN full_alias_path FORMAT a65 HEAD 'File Name'
COLUMN system_created FORMAT a8 HEAD 'SystemCreated?'
COLUMN bytes FORMAT 999,999 HEAD 'MBytes'
COLUMN space FORMAT 999,999 HEAD 'MSpace'
COLUMN type FORMAT a18 HEAD 'File Type'
COLUMN redundancy FORMAT a12 HEAD 'Redundancy'
COLUMN striped FORMAT a8 HEAD 'Striped'
COLUMN creation_date FORMAT a20 HEAD 'Creation Date'
COLUMN disk_group_name noprint
BREAK ON report ON disk_group_name SKIP 1
compute sum label "" of bytes space on disk_group_name
compute sum label "Grand Total: " of bytes space on report

SELECT
CONCAT('+' disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path
, round(bytes/1024/1024)
, space/1024/1024
, NVL(LPAD(type, 18), '') type
-- , creation_date
, disk_group_name
, LPAD(system_created, 4) system_created
FROM
( SELECT
g.name disk_group_name
, a.parent_index pindex
, a.name alias_name
, a.reference_index rindex
, a.system_created system_created
, f.bytes bytes
, f.space space
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM
v$asm_file f RIGHT OUTER JOIN v$asm_alias a USING (group_number, file_number)
JOIN v$asm_diskgroup g USING (group_number)
)
WHERE type IS NOT NULL
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
/

How to find ASM Diskgroup - Script

SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off

COLUMN group_name FORMAT a16 HEAD 'Disk GroupName'
COLUMN sector_size FORMAT 99,999 HEAD 'SectorSize'
COLUMN block_size FORMAT 99,999 HEAD 'BlockSize'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'AllocationUnit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report

SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/

How to move a datafile from a file system to ASM

Oracle Server - Enterprise Edition - Version: 10.1.0.5.0 to 10.0.2.0.3
--Information in this document applies to any platform.

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

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

ii. While the database is running (with the selected tablespace offline).

i. 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 3DGROUP2 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 catalogallocated
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.

ii. 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.dbfSQL> 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 Recreate ASM Diskgroups in Oracle10g

ASM:
Automatic Storage Management (ASM) is a feature in Oracle Database 10g/11g that provides the database administrator with a simple storage management interface that is consistent across all server and storage platforms. As a vertically integrated file system and volume manager, purpose-built for Oracle database files, ASM provides the performance of async I/O with the easy management of a file system. ASM provides capability that saves the DBAs time and provides flexibility to manage a dynamic database environment with increased efficiency.




Steps to Re-Create ASM Diskgroups

In the event you cannot mount your ASM disk groups, you will be unable to start any databases using those disk groups. Here is a possible error reported when mounting ASM disk groups:

SQL> startup mount
ORA-15032: not all alterations performed
ORA-15063: diskgroup "" lacks quorum of 2 PST disks; 0 found
This error may occur if:
a) ASM disk(s) is not visible on the operating system.
b) asm_diskstring parameter is not set correctly on ASM instance(s)
c) ASM metadata in disk is overwritten or corrupted


If you have seen this error or another error indicating ASM metadata corruption and have verified that the disk(s) is visable with correct permissions on the operating system and that the asm_diskstring parameter is set correctly, your ASM metadata may be corrupted. If this is the case, you may need to re-create your ASM instance(s) and disk group(s).
The steps are:
1. Ensure that you have a prior RMAN backup of all databases using ASM
2. Shut down your ASM instance(s)
3. Use dd to clear the metadata from ASM disks
4. Re-create your ASM disk group(s)
5. Restore databases

Step 1: Ensure that you have a prior RMAN backup of all databases using ASM

The only way you can recover from ASM metadata corruption is to have a prior RMAN backup of the database in an area that would not be affected by an ASM instance outage. As part of your recovery strategy, you should consider integrating tape or other tertiary storage to safeguard your backups.

Example of RMAN backup:
1. Connect RMAN to the target database for backup

rman nocatalog target /

2. Now Backup your Database, Archive logs and Control files.

Example:
RMAN> backup device type disk format '/u03/backup/%U' database plus archivelog;
RMAN> backup device type disk format '/u03/backup/ctrlf_%U' current controlfile;

3. Manually make copies of your spfiles.
Example:
CREATE PFILE='/u03/app/oracle/product/10.1.0/dbs/init.ora'
FROM SPFILE='/+DATA/V10FJ/spfile.ora';

If you do not have a good backup of all databases (datafiles, controlfiles,redo logs, archive logs),

DO NOT CONTINUE BEYOND STEP 1!

Step 2: Shutdown your ASM instance(s)

Stop your database instances and ASM instances with sqlplus or srvctl (RAC)SQLPLUS

Example:
setenv ORACLE_SID +ASM

sqlplus '/ as sysdba'

SQL> shutdown immediate

setenv ORACLE_SID DBSCOTT

sqlplus '/ as sysdba'
SQL> shutdown immediate

SRVCTL (RAC) Example:
srvctl stop asm -n
srvctl stop asm -n
srvctl stop database -d

Step3: Use DD to clear the metadata from ASM disks

All ASM metadata must be cleared before attempting to re-create ASM instances and diskgroups.

Example Command:
dd if=/dev/zero of=/dev/rdsk/c1t4d0s4 bs=8192 count=12800

Step 4: Re-create your ASM disk group(s)

Set your ORACLE_SID to your ASM instance and create a new diskgroup.

Example:

setenv ORACLE_SID +ASM

sqlplus '/ as sysdba'

SQL> startup nomount

SQL> create diskgroup data disk '/dev/rdsk/c1t4d0s4';

SQL> shutdown immediate

SQL> startup mount

Step 5: Restore database

1. Start instance using the local copy of your pfile from step 1.

setenv ORACLE_SID DBSCOTT

sqlplus '/ as sysdba'

SQL> startup nomount pfile=init.ora

2. Use RMAN to restore the controlfiles and database.
Example:
rman target /RMAN> restore controlfile from '/u03/backup/ctrlf_'; -- where is the unique string generated by %U.

RMAN> alter database mount;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open resetlogs;

3. Connect to the ASM instance and get the controlfile name.
Example:
setenv ORACLE_SID +ASM

sqlplus '/ as sysdba'

SQL> select name, alias_directory from v$asm_alias;

Look for the controlfile name under the CONTROLFILE directory eg: Current.256.1

4. Edit the init.ora and change the control_files parameter to point to the one identified from the ASM v$asm_alias view.

5. Re-create the spfile. Example:

SQL> create spfile='+DATA/V10FJ/spfileV10FJ.ora' from pfile='/u03/app/oracle/product/10.1.0/dbs/pfile.out';

6. Shutdown and restart the instance to use the newly created spfile.

7. Repeat the "STEP 5" section for additional databases.

Keywords
ASM ; V$ASM_ALIAS ; V$ASM_ALIAS ; V$ASM_DISK ; V$ASM_DISKGROUP ;

Reference: Oracle metalink ID:268481.1