Managing an Automatic Storage Management Instance:
Startup and shutdown ASM Instance:
Starting Up ASM Instance:
An ASM instance is managed in much the same way as a database instance, with a few exceptions. The major difference is that an ASM instance is never opened like a regular Oracle instance is opened, only mounted; therefore, we can issue the STARTUP NOMOUNT; command to start just the ASM instance's memory processes, and then finish mounting the database with the ALTER DATABASE MOUNT; command. We can also open the ASM instance immediately by issuing the STARTUP MOUNT; command
$> export ORACLE_SID=+ASM
$> sqlplus "sys as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Tue Dec 13 16:58:17 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ASM instance started
Total System Global Area 100663296 bytes
Fixed Size 787648 bytes
Variable Size 99875648 bytes
Database Buffers 0 bytes
Redo Buffers 0 bytes
ASM diskgroups mounted.
Shutdown ASM Instance:
To shut down this ASM instance, Set the ORACLE_SID environment variable and then simply issue the SHUTDOWN IMMEDIATE; command:
$> export ORACLE_SID=+ASM
$> sqlplus "sys as sysdba"
SQL> Connected.
SQL> shutdown immediate;
ASM diskgroups dismounted
ASM instance shutdown
SQL>
Implications of Shutting Down an Active ASM Instance: When an ASM instance is shut down, it is important to be aware of the implications for any regular Oracle database instance that is using ASM files stored on that ASM instance. The ASM files will not be accessible to those regular Oracle databases until the ASM instance is restarted.
Create Disk Group:
NORMAL REDUNDANCY: (Two – Way Mirroring)
create diskgroup +DATA normal redundancy
2 failgroup f1 disk '/dev/raw/raw5' name disk5
3 failgroup f2 disk '/dev/raw/raw6' name disk6;
HIGH REDUNDANCY: (Three – Way Mirroring)
create diskgroup +DATA normal redundancy
2 failgroup f1 disk '/dev/raw/raw5' name disk5
3 failgroup f2 disk '/dev/raw/raw6' name disk6;
4 failgroup f2 disk '/dev/raw/raw6' name disk7;
EXTERNAL REDUNDANCY: (If we want no mirroring by ASM)
CREATE DISKGROUP +DATA EXTERNAL REDUNDANCY DISK '/dev/raw/raw1','/dev/raw/raw2';
Adding Disks into Disk Group:
alter diskgroup +DATA add disk '/dev/raw/raw7' name disk7;
Dropping Disk
alter diskgroup +DATA drop disk disk7;
Note:
When we drop the last disk from the FAILGROUPS, the FAILGROUP automatically drops.
Resize Disk Group:
alter diskgroup +DATA resize disk raw5 size 800M;
Mounting and Dismounting Disk Groups
Disk groups that are specified in the ASM_DISKGROUPS initialization parameter are mounted automatically at ASM instance startup. We can do this manually using below.
Mounting Disk Group:
ALTER DISKGROUP +DATA MOUNT;
Dismounting Disk Group:
ALTER DISKGROUP +DATA DISMOUNT;
Create Tablespace with ASM:
Create tablespace TBS_NAME datafile ‘+DATA’ Size 1024m;
Viewing ASM Instance Information Via SQL Queries:
There are several dynamic and data dictionary views available to view an ASM configuration from within the ASM instance itself:
V$ASM_ALIAS - Shows every alias for every disk group mounted by the ASM instance
V$ASM_CLIENT - Shows which database instance(s) are using any ASM disk groups that are being mounted by this ASM instance
V$ASM_DISK - Lists each disk discovered by the ASM instance, including disks that are not part of any ASM disk group
V$ASM_DISKGROUP - Describes information about ASM disk groups mounted by the ASM instance
V$ASM_FILE - Lists each ASM file in every ASM disk group mounted by the ASM instance
V$ASM_OPERATION - Like its counterpart, V$SESSION_LONGOPS, it shows each long-running ASM operation in the ASM instance
V$ASM_TEMPLATE - Lists each template present in every ASM disk group mounted by the ASM instance
We can also able to query the following dynamic views against database instance to view the related ASM storage components of that instance:
V$ASM_DISKGROUP - Shows one row per each ASM disk group that's mounted by the local ASM instance
V$ASM_DISK - Displays one row per each disk in each ASM disk group that are in use by the database instance
V$ASM_CLIENT - Lists one row per each ASM instance for which the database instance has any open ASM files
The benefits of ASM:
• Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system
• Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance
• Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage
• Uses redundancy features available in intelligent storage arrays
• The storage system can store all types of database files
• Using disk group makes configuration easier, as files are placed into disk groups
• ASM provides stripping and mirroring
• ASM and non-ASM oracle files can coexist
ASM Operations
ASM Operations
1. Instance name
select instance_name from v$instance;
2. Create disk group
create diskgroup +DATA normal redundancy
2 failgroup f1 disk '/dev/raw/raw5' name disk5
3 failgroup f2 disk '/dev/raw/raw6' name disk6;
4 failgroup f2 disk '/dev/raw/raw6' name disk7;
CREATE DISKGROUP +DATA EXTERNAL REDUNDANCY DISK '/dev/raw/raw1','/dev/raw/raw2';
Note: force is used if disk has been in a previous diskgroup, external redundancy uses third party mirroring i.e SAN
3. Add disks to a group alter diskgroup +DATA add disk
'/dev/raw/raw7' name disk7;
/dev/raw/raw8' name disk8;
4. Remove disks from a group
alter diskgroup diskgrpA drop disk disk6;
5. Remove disk group
drop diskgroup diskgrpA including contents
6. Resizing disk group
alter diskgroup diskgrpA resize disk 'disk3' size 500M;
7. Undo remove disk group
alter database diskgrpA undrop disks;
8. Display diskgroup info
select name, group_number, name, type, state, total_mb, free_mb from v$asm_diskgroup;
select group_number, disk_number, name, failgroup, create_date, path, total_mb from v$asm_disk;
select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from v$asm_operation;
9. Rebalance a diskgroup
(after disk failure and disk has been replaced)
alter diskgroup diskgrpA rebalance power 8;
Note: to speed up rebalancing increase the level upto 11, remember that this will also decrease performance, you can also use the wait parameter this will hold the commandline until it is finished.
10. Dismount or mount a diskgroup
alter diskgroup diskgrpA dismount;
alter diskgroup diskgrpA mount;
11. Check a diskgroups integrity
alter diskgroup diskgrpA check all;
12. Diskgroup Directory
alter diskgroup diskgrpA add directory '+diskgrpA/dir1'
Note: this is required if you use aliases when creating databse files
i.e '+diskgrpA/dir/control_file1'
13. Adding and drop aliases
alter diskgroup diskgrpA add alias '+diskgrpA/dir/second.dbf' for '+diskgrpB/datafile/table.763.1';
alter diskgroup diskgrpA drop alias '+diskgrpA/dir/second.dbf'
14. Drop files from a diskgroup
alter diskgroup diskgrpA drop file '+diskgrpA/payroll/payroll.dbf';
15. Using ASM Disks
Examples of using ASM disks
create tablespace test datafile ‘+diskgrpA’ size 100m;
alter tablespace test add datafile ‘+diskgrpA’ size 100m;
alter database add logfile group 4 ‘+dg_log1’,’+dg_log2’ size 100m;
alter system set log_archive_dest_1=’location=+dg_arch1’;
alter system set db_recovery_file_dest=’+dg_flash’;
16. Display performance.
select path, reads, writes, read_time, write_time,
read_time/decode(reads,0,1,reads) "AVGRDTIME",
write_time/decode(writes,0,1,writes) "AVGWRTIME"
from v$asm_disk_stat;
1. Instance name
select instance_name from v$instance;
2. Create disk group
create diskgroup +DATA normal redundancy
2 failgroup f1 disk '/dev/raw/raw5' name disk5
3 failgroup f2 disk '/dev/raw/raw6' name disk6;
4 failgroup f2 disk '/dev/raw/raw6' name disk7;
CREATE DISKGROUP +DATA EXTERNAL REDUNDANCY DISK '/dev/raw/raw1','/dev/raw/raw2';
Note: force is used if disk has been in a previous diskgroup, external redundancy uses third party mirroring i.e SAN
3. Add disks to a group alter diskgroup +DATA add disk
'/dev/raw/raw7' name disk7;
/dev/raw/raw8' name disk8;
4. Remove disks from a group
alter diskgroup diskgrpA drop disk disk6;
5. Remove disk group
drop diskgroup diskgrpA including contents
6. Resizing disk group
alter diskgroup diskgrpA resize disk 'disk3' size 500M;
7. Undo remove disk group
alter database diskgrpA undrop disks;
8. Display diskgroup info
select name, group_number, name, type, state, total_mb, free_mb from v$asm_diskgroup;
select group_number, disk_number, name, failgroup, create_date, path, total_mb from v$asm_disk;
select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from v$asm_operation;
9. Rebalance a diskgroup
(after disk failure and disk has been replaced)
alter diskgroup diskgrpA rebalance power 8;
Note: to speed up rebalancing increase the level upto 11, remember that this will also decrease performance, you can also use the wait parameter this will hold the commandline until it is finished.
10. Dismount or mount a diskgroup
alter diskgroup diskgrpA dismount;
alter diskgroup diskgrpA mount;
11. Check a diskgroups integrity
alter diskgroup diskgrpA check all;
12. Diskgroup Directory
alter diskgroup diskgrpA add directory '+diskgrpA/dir1'
Note: this is required if you use aliases when creating databse files
i.e '+diskgrpA/dir/control_file1'
13. Adding and drop aliases
alter diskgroup diskgrpA add alias '+diskgrpA/dir/second.dbf' for '+diskgrpB/datafile/table.763.1';
alter diskgroup diskgrpA drop alias '+diskgrpA/dir/second.dbf'
14. Drop files from a diskgroup
alter diskgroup diskgrpA drop file '+diskgrpA/payroll/payroll.dbf';
15. Using ASM Disks
Examples of using ASM disks
create tablespace test datafile ‘+diskgrpA’ size 100m;
alter tablespace test add datafile ‘+diskgrpA’ size 100m;
alter database add logfile group 4 ‘+dg_log1’,’+dg_log2’ size 100m;
alter system set log_archive_dest_1=’location=+dg_arch1’;
alter system set db_recovery_file_dest=’+dg_flash’;
16. Display performance.
select path, reads, writes, read_time, write_time,
read_time/decode(reads,0,1,reads) "AVGRDTIME",
write_time/decode(writes,0,1,writes) "AVGWRTIME"
from v$asm_disk_stat;
Migrate Non-ASM to ASM
Creating ASM Instance
1. Check CSS must be running before any ASM Instance
Check CSS Running or Not?
cd $ORACLE_HOME/bin
crsctl check css
2. If its not running, you should configure CSS process by running $ORACLE_HOME/bin/localconfig script:
login as a root
$ORACLE_HOME/bin/localconfig add
Sample Output:
/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)
# 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=/dev/raw
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. Create the init+ASM.ora file in $ORACLE_HOME/dbs
# 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=/dev/raw
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
4. Creating ADMIN directories
set ASM instance ORACLE_HOME
mkdir -p $ORACLE_HOME/admin/+ASM/bdump
mkdir -p $ORACLE_HOME/admin/+ASM/cdump
mkdir -p $ORACLE_HOME/admin/+ASM/hdump
mkdir -p $ORACLE_HOME/admin/+ASM/pfile
mkdir -p $ORACLE_HOME/admin/+ASM/udump
mkdir $ORACLE_HOME/dbs
5. Starting the ASM Instance
Starting ASM Instance
# su - oracle
$ ORACLE_SID=+ASM; export ORACLE_SID
$ sqlplus "/ as sysdba"
SQL> startup
6. Create SPFILE from PFILE
create spfile='+ASM' from pfile;
7. For Unix platform, put an entry in the oratab file for the ASM intance
ORATAB
+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.
Create ASM Disk group
SQL> shutdown
ASM instance shutdown
SQL> startup
ASM instance started
SQL> alter system set asm_diskstring = '/dev/raw/raw1', '/dev/raw/raw2', '/dev/raw/raw3', '/dev/raw/raw4';
System altered.
SQL> alter system set asm_diskgroups = 'DATA';
System altered.
To get started, create 2 disk groups - one for data and one for recovery files. Here is an example:
CREATE DISKGROUP data EXTERNAL REDUNDANCY DISK '/dev/d1', '/dev/d2', '/dev/d3', ....;
CREATE DISKGROUP recover EXTERNAL REDUNDANCY DISK '/dev/d10', '/dev/d11', '/dev/d12', ....;
Here is an example how you can enable automatic file management with such a setup:
ALTER SYSTEM SET db_create_file_dest = '+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET db_recovery_file_dest = '+RECOVER' SCOPE=SPFILE;
Steps To Migrate/Move a Database From Non-ASM to ASM
1. Edit the pfile/spfile of DB to point the new control_file location on ASM
if your disk group name is '+DATA'
control_files='+DATA//control01.ctl'
2. Startup the database in nomount state
SQL> Startup nomount
3. From RMAN session, copy the control file from old location to new location
Restore the controlfile from old location to new location:
RMAN> CONNECT TARGET
RMAN> RESTORE CONTROLFILE FROM '/u01/TST/control01.ctl';
Here /u01/TST/control01.ctl is the old location of control file.
4. From SQL session, mount the database
SQL> ALTER DATABASE MOUNT;
5. Using RMAN, copy the datafile from NON-ASM to ASM
Copy the controlfile from Non-ASM to ASM:
RMAN>BACKUP AS COPY DATABASE FORMAT '+DATA';
run {
allocate channel dev1 type disk;
allocate channel dev2 type disk;
BACKUP AS COPY INCREMENTAL LEVEL 0 DATABASE
FORMAT '+DISK' TAG 'ORA_ASM_MIGRATION;
}
Using RMAN, rename the datafile , using the following command
Rename the datafile using the following command:
RMAN> SWITCH DATABASE TO COPY
Switch tempfile and open database.
6. Switch tempfile and open database:
RMAN> run {
set newname for tempfile 1 to '+DATA';
set newname for tempfile 2 to '+DATA';
...
switch tempfile all;
}
RMAN> ALTER DATABASE OPEN;
7. Do the following maintenance
SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+ASM_Disk_group' TO GROUP 1;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+ASM_Disk_group' TO GROUP 2;
SQL> ALTER DATABASE DROP LOGFILE MEMBER '';
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER DATABASE DROP LOGFILE MEMBER '';
SQL> ALTER SYSTEM SWITCH LOGFILE;
1. Check CSS must be running before any ASM Instance
Check CSS Running or Not?
cd $ORACLE_HOME/bin
crsctl check css
2. If its not running, you should configure CSS process by running $ORACLE_HOME/bin/localconfig script:
login as a root
$ORACLE_HOME/bin/localconfig add
Sample Output:
/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)
# 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=/dev/raw
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. Create the init+ASM.ora file in $ORACLE_HOME/dbs
# 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=/dev/raw
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
4. Creating ADMIN directories
set ASM instance ORACLE_HOME
mkdir -p $ORACLE_HOME/admin/+ASM/bdump
mkdir -p $ORACLE_HOME/admin/+ASM/cdump
mkdir -p $ORACLE_HOME/admin/+ASM/hdump
mkdir -p $ORACLE_HOME/admin/+ASM/pfile
mkdir -p $ORACLE_HOME/admin/+ASM/udump
mkdir $ORACLE_HOME/dbs
5. Starting the ASM Instance
Starting ASM Instance
# su - oracle
$ ORACLE_SID=+ASM; export ORACLE_SID
$ sqlplus "/ as sysdba"
SQL> startup
6. Create SPFILE from PFILE
create spfile='+ASM' from pfile;
7. For Unix platform, put an entry in the oratab file for the ASM intance
ORATAB
+ASM:
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.
Create ASM Disk group
SQL> shutdown
ASM instance shutdown
SQL> startup
ASM instance started
SQL> alter system set asm_diskstring = '/dev/raw/raw1', '/dev/raw/raw2', '/dev/raw/raw3', '/dev/raw/raw4';
System altered.
SQL> alter system set asm_diskgroups = 'DATA';
System altered.
To get started, create 2 disk groups - one for data and one for recovery files. Here is an example:
CREATE DISKGROUP data EXTERNAL REDUNDANCY DISK '/dev/d1', '/dev/d2', '/dev/d3', ....;
CREATE DISKGROUP recover EXTERNAL REDUNDANCY DISK '/dev/d10', '/dev/d11', '/dev/d12', ....;
Here is an example how you can enable automatic file management with such a setup:
ALTER SYSTEM SET db_create_file_dest = '+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET db_recovery_file_dest = '+RECOVER' SCOPE=SPFILE;
Steps To Migrate/Move a Database From Non-ASM to ASM
1. Edit the pfile/spfile of DB to point the new control_file location on ASM
if your disk group name is '+DATA'
control_files='+DATA/
2. Startup the database in nomount state
SQL> Startup nomount
3. From RMAN session, copy the control file from old location to new location
Restore the controlfile from old location to new location:
RMAN> CONNECT TARGET
RMAN> RESTORE CONTROLFILE FROM '/u01/TST/control01.ctl';
Here /u01/TST/control01.ctl is the old location of control file.
4. From SQL session, mount the database
SQL> ALTER DATABASE MOUNT;
5. Using RMAN, copy the datafile from NON-ASM to ASM
Copy the controlfile from Non-ASM to ASM:
RMAN>BACKUP AS COPY DATABASE FORMAT '+DATA';
run {
allocate channel dev1 type disk;
allocate channel dev2 type disk;
BACKUP AS COPY INCREMENTAL LEVEL 0 DATABASE
FORMAT '+DISK' TAG 'ORA_ASM_MIGRATION;
}
Using RMAN, rename the datafile , using the following command
Rename the datafile using the following command:
RMAN> SWITCH DATABASE TO COPY
Switch tempfile and open database.
6. Switch tempfile and open database:
RMAN> run {
set newname for tempfile 1 to '+DATA';
set newname for tempfile 2 to '+DATA';
...
switch tempfile all;
}
RMAN> ALTER DATABASE OPEN;
7. Do the following maintenance
SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+ASM_Disk_group' TO GROUP 1;
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+ASM_Disk_group' TO GROUP 2;
SQL> ALTER DATABASE DROP LOGFILE MEMBER '';
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER DATABASE DROP LOGFILE MEMBER '';
SQL> ALTER SYSTEM SWITCH LOGFILE;
Subscribe to:
Posts (Atom)
