Sunday, July 14, 2013

Automatic Storage Management ASM Instance :

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> 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> shutdown immediate; 
ASM diskgroups dismounted 
ASM instance shutdown 


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

Note: Need some investigations for further ......
++++++++++++++++++++++++++++++++

No comments:

Post a Comment