ASM Diskgroup usage

ASM Diskgroup usage

select dg.name dg_name, round(sum(d.total_mb)/1024,0) total_gb, round(sum(d.free_mb)/1024,0) free_gb from v$asm_diskgroup dg, v$asm_disk d where dg.group_number=d.group_number and dg.name like '%MQM_DG%' group by dg.name order by dg_name;


select dg.name dg_name, round(sum(d.total_mb)/1024,0) total_gb, round(sum(d.free_mb)/1024,0) free_gb from v$asm_diskgroup dg, v$asm_disk d where dg.group_number=d.group_number group by dg.name order by dg_name;

ASM Administration Commands

ASM Administration Commands

check ASMCMD’s version
$ asmcmd -V

Check diskgroup including the dismounted DGs
$ asmcmd
lsdg -g  --discovery

Check ASM disk status
$ asmcmd
lsdsk -k -g

Check ASM disk status for candidate disk
$  asmcmd
lsdsk -k -g --candidate

Clients accessing ASM instance:
$ asmcmd
lsct

Check a particular diskgroup.
$ asmcmd
 lsdg DATA

Check listed Mounted ASM disks.
$ asmcmd
$lsdsk

Check details of ASM disks.
$ asmcmd
lsdsk -k

Check above information for a particular disk.
$ asmcmd
lsdsk -k -G <dg_name>

Check Input/Output State details of disks.
$ asmcmd
iostat

Check details for a particular Disk.
$ asmcmd
ostat -G FRA

Check information of Reads/Writes Error.
$ asmcmd
iostat -e

Check information about OracleASM ServerParameter file.
$ asmcmd
spget

Check permissions on a file.
$ asmcmd
ls --permission

Check disk usage.
$ asmcmd
du

To find file with name.
$ asmcmd
find + system* data*

To list currently open files by all instances in ASM Use Below Command.
$ asmcmd
lsof

ASM Views And Scripts

ASM Views And Scripts

Views:          

V$ASM_DISKGROUP :- Describes a disk group (number, name, size related info, state, and redundancy type)       

V$ASM_CLIENT:- Identifies databases using disk groups managed by the ASM instance.                    

V$ASM_DISK:- Contains one row for every disk discovered by the ASM instance, including disks that are not part of any disk group.                 

V$ASM_FILE:- Contains one row for every ASM file in every disk group mounted by the ASM instance.         

V$ASM_TEMPLATE:- Contains one row for every template present in every disk group mounted by the ASM instance.   

V$ASM_ALIAS:- Contains one row for every alias present in every disk group mounted by the ASM instance.   

v$ASM_OPERATION:- Contains one row for every active ASM long running operation executing in the ASM instance.


Scripts:

ASM Disk Groups
==============

SELECT g.group_number  "Group"
,      g.name          "Group Name"
,      g.state         "State"
,      g.type          "Type"
,      g.total_mb/1024 "Total GB"
,      g.free_mb/1024  "Free GB"
,      100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance"
,      100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance"
,      100*(min(d.free_mb/d.total_mb)) "MinFree"
,      100*(max(d.free_mb/d.total_mb)) "MaxFree"
,      count(*)        "DiskCnt"
FROM v$asm_disk d, v$asm_diskgroup g
WHERE d.group_number = g.group_number and
d.group_number <> 0 and
d.state = 'NORMAL' and
d.mount_status = 'CACHED'
GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb
ORDER BY 1;

ASM Disks In Use
==============
select group_number  "Group"
,      disk_number   "Disk"
,      header_status "Header"
,      mode_status   "Mode"
,      state         "State"
,      create_date   "Created"
--,      redundancy    "Redundancy"
,      total_mb/1024 "Total GB"
,      free_mb/1024  "Free GB"
,      name          "Disk Name"
--,      failgroup     "Failure Group"
,      path          "Path"
--,      read_time     "ReadTime"
--,      write_time    "WriteTime"
--,      bytes_read/1073741824    "BytesRead"
--,      bytes_written/1073741824 "BytesWrite"
,      read_time/reads "SecsPerRead"
,      write_time/writes "SecsPerWrite"
from   v$asm_disk_stat
where header_status not in ('FORMER','CANDIDATE')
order by group_number
,        disk_number;


File Types in Diskgroups
==================
select g.name                                   "Group Name"
,      f.TYPE                                   "File Type"
,      f.BLOCK_SIZE/1024||'k'                   "Block Size"
,      f.STRIPED
,        count(*)                               "Files"
,      round(sum(f.BYTES)/(1024*1024*1024),2)   "Gb"
from   v$asm_file f,v$asm_diskgroup g
where  f.group_number=g.group_number
group by g.name,f.TYPE,f.BLOCK_SIZE,f.STRIPED
order by 1,2;
clear break


Instances currently accessing these diskgroups
=================================
select c.group_number  "Group"
,      g.name          "Group Name"
,      c.instance_name "Instance"
from   v$asm_client c
,      v$asm_diskgroup g
where  g.group_number=c.group_number;


Free ASM disks and their paths
=======================
select header_status                   "Header"
, mode_status                     "Mode"
, path                            "Path"
, lpad(round(os_mb/1024),7)||'Gb' "Disk Size"
from   v$asm_disk
where header_status in ('FORMER','CANDIDATE')
order by path;


Current ASM disk operations
=====================
select * from   v$asm_operation;

ASMLib download

ASMLib download

$ rpm -qa | grep asmlib

$ /usr/sbin/oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes

If the ASMLib RPM's are missing in your environment.

Please download the appropriate rpms for your version.

http://www.oracle.com/technetwork/server-storage/linux/asmlib/ol6-1709075.html

Automatic Storage Management (ASM)

Automatic Storage Management (ASM)

Automatic Storage Management (ASM) is an option and a new feature of Oracle 10g and onwards that simplifies the storage management for all Oracle database file types. It renders the capabilities of a volume manager and filesystem together into
the Oracle database kernel. Although, it inherits the Stripe And Mirror Everything (SAME) functionality, it strips the data (extents) evenly across the ASM disks of a disk group by default and provides the mirroring functionality as an option. The
management and administration of ASM is made easy through a well-known set of SQL statements, such as, CREATE, ALTER, DROP, and through GUI tools.

While the ASM was initially intended for managing and maintaining only the Oracle database files and other related files, its functionality has been significantly improved in 11g R1 and R2 versions to manage all types of data. The following are some of the key features and benefits of ASM:

-It simplifies the storage configuration management for Oracle datafiles and other files.
-It eliminates the need for third-party software, (for example, volume manager) to manage the storage for the databases.
-When a datafile is created, it is divided into equally sized (1, 2, 4, 8, 16, 32,or 64 MB) extents that are scattered evenly across the disks of a disk group to provide balanced I/O to improve performance and prevent hot spot symptoms.
-It is built on the Stripe and Mirror Everything (SAME) functionality.
-It supports both non-RAC and RAC databases efficiently.
-It has the ability to add and remove ASM disks online without actually disturbing the ongoing operations.
-It can be managed and administrated using a set of known SQL statements.
-It performs automatic online redistribution for the data whenever a disk is being added or dropped.
-With ASM 11g R2, in addition to all database file types, it can also be used to store non-Oracle datafile types such as binaries, images, and so on. Beginning with ASM 11g R2, it provides the ability of a preferred read functionality, when ASM mirroring features are enabled.
-It supports multiversioning of databases.
-It supports a multipathing feature to prevent outages from disk path failures.

About ASM Disk Group

About ASM Disk Group

A disk group is a logical container for one or more ASM disks and is the highest level of data structure in ASM. When a database is configured to employ the disk group, the disk group then becomes the default location for its datafiles. The disk group
can be used to place various database file types, such as datafiles, online redo, archivelogs, RMAN backupsets, OCR and Voting disks (in 11g R2), and more. ASM also provides the flexibility of utilizing a single disk group by multiple ASM instances
and databases across a cluster.

After a disk group is successfully created and mounted for the first time in the ASM instance, the name of the disk group is automatically affiliated with the ASM_DISKGROUPS initialization parameter to be able to mount the disk group at ASM
instance restarts.

In general, when a datafile is created in a disk group, the datafile extents are striped/distributed evenly across the available disks of the disk group. Optionally, you can also set the following specified mirroring level at the disk group to protect the data
integrity by storing redundant copies of data (extents) in a separate failure group to cope with the disk outage symptom:

-External redundancy: Relies on the STORAGE (RAID)-level mirroring redundancy option to protect the data
-Normal redundancy: Provides a default two-way mirroring option
-High redundancy: Provides a three-way mirroring redundancy option of ASM files

As of 11g R2, the following limits have been imposed on the ASM instance.
-A maximum of 63 disk groups in a storage system
-1 million files per disk group

ASM Dynamic Views

ASM Dynamic Views

To manage and supervise the ASM instance and its primary components (disk and disk group) effectively, you really need to know the ASM specific dynamic views, and how to map them to extract useful information about disks, disk groups, and
so on. This section will help you to understand the use of the ASM specific dynamic views available in ASM.

There are about 19 dynamic views available, as of 11g R2, and each dynamic view provides different helpful information. All the ASM dynamic views are predefined with V$ASM_. In the following section, we are going to focus on a handful of ASM
dynamic views.

V$ASM_DISK
When an ASM instance completes the disk discovery operation by reading the disk header information, all disks (used and usable) will then list in the V$ASM_DISK view. Each individual disk has a row in the V$ASM_DISK dynamic view and contains very useful information. The PATH column specifies the disk's name and location. The HEADER_STATUS column, in most cases, contains the following three possible values:

• CANDIDATE: Indicates that the unused disks are ready for use.
• FORMER: Indicates that the disk was formerly part of a disk group and is now ready for use.
• MEMBER: Indicates that the disk is currently part of an active disk group.

Apart from the preceding values, the view also contains other useful information about the disks, such as total size, free size, physical reads, redundancy level, and so on. On the flipside, every time you run a query against this view, ASM initiates disk
discovery operations for the new disks, where it reads all disk header information. Querying against this view could be an expensive operation at times, and could impact performance.

The following list of SQL statements demonstrates some of the useful queries against the view. However, prior to running these commands, ensure you are connected to an ASM instance through sqlplus with SYSASM privilege:

SELECT path,header_status,total_mb FROM v$asm_disk WHERE header_status in ('CANDIDATE','FORMER','PROVISIONED');

The preceding command displays information about the disks that are eligible to use.

V$ASM_DISKGROUP
After a disk group is successfully created in the local ASM instance, the disk group summary is visible in the V$ASM_DISKGROUP view. Each disk group maintains a row in the view along with the important information, such as disk group number and name, total disk group size, used space, free space, redundancy type, compatibility, mount state, and so on. Every time a query is run against the view, it is likely to have a similar impact to querying the V$ASM_DISK views. The following SQL command extracts the mounted disk group's name, total disk group size, and the free space left in the group:

SELECT name,state,total_mb,usable_file_mb FROM v$asm_diskgroup;

V$ASM_OPERATION
V$ASM_OPERATION is one of the useful views that displays a row for each long running operation in the ASM instance. For example, when a disk is being dropped or attached to an existing disk group, an ASM should initiate and complete the
rebalancing operations just before releasing the subject disk. Therefore, the view will present useful details, such as the amount of work that has been completed, and show the estimated time (in minutes) required to complete the operations. This should help
you to understand how long the operation will take to complete.

V$ASM_DISK_STAT
Although the V$ASM_DISK and V$ASM_DISK_STAT views display nearly identical information, querying the V$ASM_DISK_STAT view results is a less expensive operation in comparison to the V$ASM_DISK view. On the flip side, this view doesn't display the details about new disks on the system that the ASM instance has yet to discover. As querying the view is less expensive, it is strongly recommended that you use this view in order to display the information and statistics (read/write)
about the existing disks.

V$ASM_DISKGROUP_STAT
The V$ASM_DISKGROUP_STAT view displays statistical information about the mounted disk groups in the ASM instance. Unlike the V$ASM_DISKGROUP view, a query against this view doesn't result in new disk discovery operations but is less expensive in terms of performance. Therefore, it is recommended to use this view to display existing disk group information and statistical information about the disk groups.

V$ASM_CLIENT
When the V$ASM_CLIENT view is queried in the ASM instance, it displays the information about the database instances that are using the disk groups mounted and managed by the ASM instance.

ASM Instance Startup/Shutdown

ASM Instance Startup/Shutdown

Managing an ASM instance is no different from managing the typical RDBMS database instances. The ASM instance could be managed by either using a set of SQLPLUS commands or the cluster aware SRVCTL utility. Nevertheless, it is strongly
recommended that you use the SRVCTL utility for managing the (start/stop) ASM instance in an RAC environment.

The ASM instance can be opened either in NOMOUNT, MOUNT, or RESTRICTED modes with the STARTUP command at the SQLPLUS prompt. When you have a planned maintenance on an ASM instance, you can open the ASM instance in a RESTRICT
mode to avoid any possible connections from the database instances. When the ASM instance is opened gracefully, it first discovers the disks and then mounts all the existing disk groups on the local instance.

To shut down the local ASM instance, you can use the various options available with the SHUTDOWN command in the SQLPLUS prompt. The supported options are NORMAL, IMMEDIATE, TRANSACTIONAL, and ABORT. As mentioned previously, you can use the SRVCTL utility to bring down the ASM instance as well. The following list of examples demonstrates how to start up/shut down an ASM instance using SQLPLUS and SRVCTL utilities:

srvctl stop asm –n raclinux1 –o normal:immediate:transactional:abort

export ORACLE_SID=+ASM1
export ORACLE_HOME=$ASM_HOME (in 11gR1)
export ORACLE_HOME=$ORA_GRID_HOME (in 11gR2)
sqlplus / as sysasm
SQL> SHUTDOWN NORMAL:IMMEDIATE:TRANSACTIONAL:ABORT

This example stops the ASM instance on raclinux1 node. Alternatively, you can also use either of the shutdown options.

srvctl start asm –n raclinux1 –o nomount:mount:restrict

export ORACLE_SID=+ASM1
export ORACLE_HOME=$ASM_HOME (in 11gR1)
export ORACLE_HOME=$ORA_GRID_HOME (in 11gR2)
sqlplus / as sysasm
SQL> STARTUP NOMOUNT:MOUNT:RESTRICT

This startup command starts up the ASM instance on raclinux1 node. When you start the instance in NOMOUNT mode, an ASM instance will be started without mounting the existing disk groups. When the instance is started in RESTRICT mode, no database
instance can attach to the ASM instance and all the disk groups are opened in restricted mode too.

Ensure that the database instances that are currently associated with the local ASM instance are stopped prior to shutting down the local ASM instance to avoid encountering an ASM instance shutdown error. In this case, the ASM instance will remain opened. Alternatively, you could use the ABORT option to forcefully shut down the currently connected database's instance before shutting down the ASM instance. It is also strongly recommended to dismount any existing Oracle Cluster File System (ACFS) to avoid any application I/O errors.

Note: If the voting disk and OCR files are placed in a disk group, you will not be able to stop the ASM instance.
In order to stop the ASM instance, you need to stop the cluster.

ASM Background Processes

ASM Background Processes

An ASM instance comes with a few additional background processes that assist the ASM instance to perform its course of action. Therefore, understanding the individual role played by these ASM-specific background processes in order to know how they help the ASM instance to manage and carry out its functionality, will be helpful to DBAs. Here Iam going to cover the most useful ASM-specific background processes and the part played by the individual ASM-specific background processes:

RBAL - Rebalancer: It opens all the device files as part of disk discovery and coordinates the ARB processes for rebalance activity.

ARBx - Actual Rebalancer: They perform the actual rebalancing activities. The number of ARBx processes depends on the ASM_POWER_LIMIT init parameter.

ASMB - ASM Bridge: This process is used to provide information to and from the Cluster Synchronization Service (CSS) used by ASM to manage the disk resources. Responsible to communicate the database Instance to the ASM Instance, provides the heartbeat to the ASM instance.

ORBn - Rebalance ASM data extent movements. There can be many of this at the same time.

OSMB - (ASM) Any database instance that is using an ASM disk group will contain a background process called OSMB. The OSMB process is responsible for communicating with the ASM instance..Helps to manage the drive storage.

GMON - ASM Diskgroup monitor process, monitors all the mounted diskgroups and is responsible for maintaning consisten disk membership and status information

PZ9n - Parallel slave processes are used to fetch data from GV$ dynamic view in a cluster

ORA-01031: insufficient privileges Could not validate ASMSNMP password

ORA-01031: insufficient privileges Could not validate ASMSNMP password

In Oracle 11gR2 grid
On one of the rac node create a asm password file and copy the file to remaining nodes of the cluster

grid@test1 bin]$ orapwd file=/u01/app/11.2.0/grid/dbs/orapw+ASM password=oracleasm

grid@test1 dbs]$ scp orapw+ASM node2:/u01/app/11.2.0/grid/dbs/

grid@test1 dbs]$ scp orapw+ASM node3:/u01/app/11.2.0/grid/dbs/

Login in to Oracle database as

export ORACLE_SID=+ASM1

export ORACLE_HOME=/u01/app/11.2.0/grid

grid@test1 bin]$ sqlplus / as sysasm

SQL>create user asmsnmp identified by oracleasm

SQL> grant sysdba to asmsnmp;

Migrating Databases From NON-ASM to ASM

Migrating Databases From NON-ASM to ASM

1. First need to set below parameter for controlfile,datafile or FRA.

NOTE: I have two disk group here i am using "DGRP2" disk group.

SQL> alter system set control_files='+DGRP2' scope=spfile;
System altered.
SQL> alter system set db_create_file_dest='+DGRP2' scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest='+DGRP2' scope=spfile;
System altered.

2. Create New controlfile , datafile or spfile in ASM diskgroup.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.
Total System Global Area 263639040 bytes

Fixed Size 1332552 bytes

Variable Size 218106552 bytes

Database Buffers 37748736 bytes

Redo Buffers 6451200 bytes

Connect with RMAN session.

C:\>RMAN target=sys
Recovery Manager: Release 11.1.0.6.0 - Production on Thu Apr 17 10:12:53 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password:connected to target database: TEST (not mounted)

Restore Controlfile.

RMAN> restore controlfile from 'C:\app\m.taj\oradata\test\CONTROL01.ctl';
Starting restore at 17-APR-08using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=151 device type=DISK
channel ORA_DISK_1: copied control file copyoutput file name=+DGRP2/test/controlfile/current.256.652270419Finished restore at 17-APR-08

RMAN> alter database mount;
database mountedreleased channel: ORA_DISK_1

Take backup of database which we use for ASM diskgroup.

RMAN> backup as copy database format '+DGRP2';

Starting backup at 17-APR-08allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=151 device type=DISKchannel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBFoutput file name=+DGRP2/test/datafile/system.257.652270565 tag=TAG20080417T101550 RECID=1 STAMP=652270748channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:06channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=C:\APP\M.TAJ\ORADATA\TEST\SYSAUX01.DBFoutput file name=+DGRP2/test/datafile/sysaux.258.652270761 tag=TAG20080417T101550 RECID=2 STAMP=652270908channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:35channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=C:\APP\M.TAJ\ORADATA\TEST\UNDOTBS01.DBFoutput file name=+DGRP2/test/datafile/undotbs1.259.652270927 tag=TAG20080417T101550 RECID=3 STAMP=652270952channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35channel ORA_DISK_1: starting datafile copycopying current control fileoutput file name=+DGRP2/test/controlfile/backup.260.652270971 tag=TAG20080417T101550 RECID=4 STAMP=652270975channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=C:\APP\M.TAJ\ORADATA\TEST\USERS01.DBFoutput file name=+DGRP2/test/datafile/users.261.652270989 tag=TAG20080417T101550 RECID=5 STAMP=652270991channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 17-APR-08channel ORA_DISK_1: finished piece 1 at 17-APR-08piece handle=+DGRP2/test/backupset/2008_04_17/nnsnf0_tag20080417t101550_0.262.652271003 tag=TAG20080417T101550 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 17-APR-08

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DGRP2/test/datafile/system.257.652270565"
datafile 2 switched to datafile copy "+DGRP2/test/datafile/sysaux.258.652270761"
datafile 3 switched to datafile copy "+DGRP2/test/datafile/undotbs1.259.652270927"
datafile 4 switched to datafile copy "+DGRP2/test/datafile/users.261.652270989"

Again connect to sqlplus session and perform incomplete recovery

C:\>sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Apr 17 10:26:24 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 1071679 generated at 04/17/2008 10:11:10 needed for thread 1ORA-00289: suggestion : +DGRP2ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'TEST'ORA-00280: change 1071679 for thread 1 is in sequence #14
Specify log: {=suggested filename AUTO CANCEL}

CANCEL

Media recovery cancelled.

OPEN database with RESETLOGS option.

SQL> alter database open resetlogs;
Database altered.

4. Drop old tempfile and create new tempfile in existing temp tablespace

SQL> alter database tempfile 'c:\app\m.taj\oradata\test\temp01.dbf' 2 drop including datafiles;
Database altered.

SQL> alter tablespace temp add tempfile size 512m 2 autoextend on next 250m maxsize unlimited;
Tablespace altered.

SQL> select tablespace_name, file_name, bytes from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES------------------------------ ------------------------------ ----------TEMP +DGRP2/test/tempfile/temp.266. 536870912 652271571

5. Recreate All redolog group on ASM diskgroup

SQL> select a.group#, a.member, b.bytes

2 from v$logfile a, v$log b where a.group# = b.group#;

GROUP# MEMBER BYTES
---------- ------------------------------------ ---------
3 C:\APP\M.TAJ\ORADATA\TEST\REDO03.LOG 52428800
2 C:\APP\M.TAJ\ORADATA\TEST\REDO02.LOG 52428800
1 C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG 52428800

SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 UNUSED
3 UNUSED

SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.

SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 ACTIVE
3 CURRENT

SQL> alter database drop logfile group 1;

alter database drop logfile group 1*ERROR at line 1:ORA-01624: log 1 needed for crash recovery of instance test (thread 1)ORA-00312: online log 1 thread 1: 'C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG'

When you get above error message then set checkpoint with below command.

SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 10m;
Database altered.

SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 size 10m;
Database altered.

SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 size 10m;
Database altered.

SQL> column member format a30

SQL> select a.group#, a.member, b.bytes

2 from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES
---------- ------------------------------ ----------
3 +DGRP2/test/onlinelog/group_3. 10485760 277.652273117
2 +DGRP2/test/onlinelog/group_2. 10485760 274.652273019
1 +DGRP2/test/onlinelog/group_1. 10485760 271.652272977
1 +DGRP2/test/onlinelog/group_1. 10485760 272.652272979
2 +DGRP2/test/onlinelog/group_2. 10485760 275.652273021
3 +DGRP2/test/onlinelog/group_3. 10485760 278.652273119
6 rows selected.

6. Recreate SPFILE on ASM diskgroup

SQL> create pfile='c:\initTEST.ora' from spfile;
File created.
SQL> create spfile='+DGRP2/spfileTEST.ora' from pfile='c:\initTEST.ora';
File created.

7. Detele all backup copy from RMAN

RMAN> delete noprompt force copy;

Managing Diskgroups

Managing Diskgroups

Creating Diskgroup
1. Assign Unqiue name for each diskgroup
2. Specify redundancy level of disk group

There is three type of redundancy level
1. NORMAL redundancy with 2 way mirroring
2. HIGH redundancy with 3 way mirroring
3. EXTERNAL redundancy with NO mirroring.

3. Specify disks as belonging to specific failgroup.
If we manually not define any failgroup for disk then Oracle automatically create failgroup for each diskgroup.

4. Specify asm disks (v$asm_disk) for diskgroup.

5. Optionally specify disk group attributes such as compatibility or allocation unit size.

SQL> conn / as sysasm
Connected to an idle instance.
SQL> startup nomount
ASM instance started
Total System Global Area 267825152 bytes
Fixed Size 1332584 bytes
Variable Size 241326744 bytes
ASM Cache 25165824 bytes

Create DISKGROUP with NORMAL REDUNDANCY
SQL> CREATE DISKGROUP dgrp1 NORMAL REDUNDANCY
2 FAILGROUP controller1 DISK
3 'e:\asmdisks\disk1' NAME diska1,
4 'e:\asmdisks\disk2' NAME diska2
5 FAILGROUP controller2 DISK
6 'e:\asmdisks\disk3' NAME diska3;
Diskgroup created.

SQL> select group_number, name, type, total_mb, free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE TOTAL_MB FREE_MB
------------ ------------------------------ ------ ---------- ------
1 DGRP1 NORMAL 1536 1432

ADD NEW DISK in exiting DISKGROUP dgrp1
SQL> alter diskgroup dgrp1 add disk 'e:\asmdisks\disk4';
Diskgroup altered.

SQL> select group_number, name, type, total_mb, free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE TOTAL_MB FREE_MB
------------ ------------------------------ ------ ---------- ------
1 DGRP1 NORMAL 2048 1940

Managing Capacity in Disk Groups
When ASM provides redundancy, such as when you create a disk group with NORMAL or HIGH redundancy, you must have sufficient capacity in each disk group to manage a re-creation of data that is lost after a failure of one or two failure groups. After one or more disks fail, the process of restoring redundancy for all data requires space from the surviving disks in the disk group. If not enough space remains, then some files might end up with reduced redundancy.

Negative Values of USABLE_FILE_MB
Due to the relationship between FREE_MB, REQUIRED_MIRROR_FREE_MB, and USABLE_FILE_MB, USABLE_FILE_MB can become negative. Although this is not necessarily a critical situation, it does mean that:
Depending on the value of FREE_MB, you may not be able to create new files.

The next failure might result in files with reduced redundancy.

If USABLE_FILE_MB becomes negative, it is strongly recommended that you add more space to the disk group as soon as possible.

When Should External, Normal, or High Redundancy Be Used?
ASM mirroring runs on the database server and Oracle recommends to off load this processing to the storage hardware RAID controller by using external redundancy. You can use normal redundancy in the following scenarios:

Storage system does not have RAID controller
Mirroring across storage arrays
Extended cluster configurations

In general, ASM mirroring is the Oracle alternative to third party logical volume managers. ASM mirroring eliminates the need to deploy additional layers of software complexity in your Oracle database environment.

ASM Instance Creation on Windows

ASM Instance Creation on Windows

1. Create INIT.ORA file for asm instance

DIAGNOSTIC_DEST=C:\app\mqm\diag_asm_allow_only_raw_disks= FALSE instance_type=asm remote_login_passwordfile=exclusive
memory_target=52m

Note: Minimum required parameters.

C:\>oradim -new -asmsid +ASM -startmode manual
Instance created.

C:\>oradim -edit -asmsid +ASM -startmode a

C:\>oradim -edit -asmsid +ASM -syspwd change_on_install

C:\>set ORACLE_SID=+ASM

C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 8 11:22:47 2008

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

Connected to an idle instance.

SQL> startup pfile ='c:\init.ora';
ASM instance started

Total System Global Area 267825152 bytes
Fixed Size 1332584 bytes
Variable Size 241326744 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

SQL> create spfile from pfile='c:\init.ora';

File created.

SQL> shutdown
ORA-15100: invalid or missing diskgroup name

ASM instance shutdown
SQL> startup
ASM instance started

Total System Global Area 267825152 bytes
Fixed Size 1332584 bytes
Variable Size 241326744 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

SQL> SELECT group_number, disk_number, mount_status, header_status, state, path FROM v$asm_disk;
GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE PATH

----------- ----------- ------- ------------ -------- -------------------- 0 1 CLOSED CANDIDATE NORMAL E:\ASMDISKS\DISK2 0 0 CLOSED CANDIDATE NORMAL E:\ASMDISKS\DISK1

Preparing disk for ASM instance on Windows

Preparing disk for ASM instance on Windows

We can use DISKPART.EXE to create primary,logical partitions and using ASMTOOL (commandline) & ASMTOOLG (GUI)stamp(map) for ASM instance.

Preparing Disks for ASM

http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmprepare.htm#BHCDCFIH

Diskpart.exe

http://download.oracle.com/docs/cd/B28359_01/install.111/b28250/racstorage.htm#BABFIDGI

ASMTOOL & ASMTOOLG

http://download.oracle.com/docs/cd/B28359_01/install.111/b28250/racstorage.htm#sthref212

Demostration

E:\>asmtool -list
NTFS \Device\Harddisk0\Partition1 37997M
NTFS \Device\Harddisk0\Partition2 38319M

E:\>mkdir asmdisks
E:\>cd e:\asmdisks
E:\asmdisks>asmtool -create e:\asmdisks\DISK1 512
E:\asmdisks>asmtool -create e:\asmdisks\DISK2 512
E:\asmdisks>cd c:\app\m.taj\product\11.1.0

More Reference on disks preparing for ASM

http://www.databasejournal.com/features/oracle/article.php/3571371

http://www.databasejournal.com/img/jsc_ASM_Listing2.html

ASM - Introduction

ASM - Introduction

ASM - Automatic storage management is Introduced in Oracle 10gr1.

ASM provides many of the same benefits as storage technologies such as RAID or logical volume managers (LVMs). Like these technologies, ASM enables you to create a single disk group from a collection of individual disk devices. It balances I/O to the disk group across all of the devices in the disk group. It also implements striping and mirroring to improve I/O performance and data reliability.
However, unlike RAID or LVMs, ASM implements striping and mirroring at the file level. This implementation enables you to specify different storage attributes for individual files in the same disk group.

Benefits of Automatic Storage Management

-Automatic Storage Management provides easier administration.

-There is no need to specify and manage filenames. Wherever a file is created, a disk group can be specified instead. Every new file automatically gets a new unique name. This prevents using the same filename in two different databases. Disk group naming avoids using two different names for the same file.

-For many situations, Automatic Storage Management provides the functions provided by external volume managers and file systems.

-Automatic Storage Management includes storage reliability features, such as mirroring. The storage reliability policy is applied on a file basis, rather than on a volume basis. Hence, the same disk group can contain a combination of files protected by mirroring, parity, or not protected at all.

-Automatic Storage Management improves performance.

-Automatic Storage Management maximizes performance by automatically distributing database files across all disks in a disk group. It has the performance of raw disk I/O without the inconvenience of managing raw disks.

-Unlike logical volume managers, Automatic Storage Management maintenance operations do not require that the database be shut down. This allows adding or dropping disks while the disks are in use.

-Automatic Storage Management eliminates the need for manual disk tuning. To help manage performance, file creation attributes are controlled by disk group-specific templates.

Creating Datapump Files In ASM

Creating Datapump Files In  ASM

This section describes how to create DataPump export dumps within ASM diskgroups.

1.Create a directory from ASM. This step is only necessary, if you plan on storing the dumps in a specific directory

alter diskgroup flash add directory '+flash/dumpsets';

2.Create a directory in database

create directory dumps as '+flash/dumpsets';

3.Create a logfile directory, since logfiles cant be stored in ASM

create directory logfile_dest as '/u01/admin/dumps_logs';

4.expdp parfile=expdp_parfile

where expdp_parfile contents are:

userid=system/manager directory='DUMPS' dumpfile=expdp.dat job_name=full_export logfile=logfile_dest:exp.log

5.Validate that the file is created within ASM.

SQL> select file_number, bytes, creation_date from v$asm_file where type ='DUMPS';

Duplicating A Controlfile Into ASM When Original Controlfile Is Stored On A File System

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$controfile;
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

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.

Duplicating 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.

Duplicating 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

How To Find Mapping of ASM Disks to Physical Devices

How To Find Mapping of ASM Disks to Physical Devices

How to find mapping of ASM disks to Physical Devices?
1.Login as root

# /etc/init.d/oracleasm listdisks
DSK1

2. Query Disk
]# /etc/init.d/oracleasm querydisk -d dsk1
Disk "DSK1" is a valid ASM disk on device [8, 17]

3. oracleasm querydisk dsk1 you will get in addition the major - minor numbers, that can be used to match the physical device,
# ls -l /dev |grep 8|grep 17
brw-r----- 1 root disk   8,   17 Aug 10 13:05 sdb1

Migrating Individual non-ASM datafiles to ASM

Migrating Individual non-ASM datafiles to ASM

This section will describe how to migrate individual tablespaces to ASM, while the database is online.
This illustration assumes that there is a tablespace named TEST located on a mounted filesystem.
Additionally, this same procedure can also be used to move a datafile from one diskgroup to another
diskgroup.

1. Connect to RMAN
RMAN> connect target

2. Make the target tablespace offline or read-only
RMAN> sql "alter tablespace TEST offline";

3. Copy the ISHAN tablespace to the DATA diskgroup
RMAN> backup as copy tablespace ishan format '+DATA';

4. On successful copy of the tablespace, switch all the datafiles in the TEST tablespace to ASM.
Determine the file number of the files to be switched (use v$datafile).
RMAN> switch datafile 6 to copy;

When the following message is received, it is deemed that the tablespace is
migrated successfully.
datafile 6 switched to datafile copy "+DATA/orcl/datafile/test.314.1"

Note, the original filesystem copy still exists, and can be deleted