Oracle ASM Commands

 

Oracle ASM Commands

ASM SQL * Plus commands

Start the ASM instance

$ export ORACLE_SID=+ASM

$ sqlplus '/as sysdba'

sql>startup

ASM instance started

Stop the ASM instance

$export ORACLE_SID=+ASM

$sqlplus '/as sysdba'

shutdown immediate

Adding Diskgroup

sql>create diskgroup oradg  external redundancy disk 'ORCL:VOL5';

Diskgroup created

sql>select  group_number,disk_number,mode_status,name from v$asm_disk;

sql>create diskgroup  disk_group_1 NORMAL REDUNDANCY  FAILGROUP failure_group_1 DISK

'/devices/diska1' NAME diska1,

'/devices/diska2' NAME diska2

FAILGROUP failure_group_2 DISK

'/devices/diskb1'NAME diskb1

'/devices/diskb2' NAME diskb2;

Drop a Diskgroup

Disk groups can be deleted using the DROP DISKGROUP statement

DROP DISKGROUP disk_group_1 INCLUDING CONTENTS;

Adding disks to existing groups

Disks can be added or removed from disk groups using the ALTER DISKGROUP statement

wild cards can be used to reference disks resulting strings does not match a disk already used by an existing disk group

Add disk

ALTER DISKGROUP disk_group_1 ADD DISK

'/devices/disk*3'

'/devices/disk*4';

Drop a Disk

ALTER DISKGROUP disk_group_1 DROP DISK diska2;

Resizing Disks

Disks can be resized using the RESIZE clause of the ALTER DISKGROUP statement .the statement can be used to resize individul disks ,all data in a failure group or all 

disks in the disk group ,if the SIZE class is ommitted the disks are resized to the size of the disk returned by the os

Resize a specif disk

ALTER DISKGROUP disk_group_1

RESIZE DISK diska1 SIZE 200G;

Resize all disks in a failure group

ALTER DISKGROUP disk_group_1

RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 200G;

Resize all disks in a diskgroup

ALTER DISKGROUP disk_group_1

RESIZE ALL SIZE 200G;

Manually Mounting asm diskgroups

Manually Disk groups are mounted at  ASM instance startup and unmounted at ASM instance shutdown ,mounting and dismounting will be done using ALTER DISKGROUP statement

ALTER DISKGROUP ALL DISMOUNT;

ALTER DISKGROUP ALL MOUNT;

ALTER DISKGROUP disk_group_1 DISMOUNT;

ALTER DISKGROUP disk_group_1 MOUNT;

Drop file inside ASM

Files are not deleted automatically they created by using alias names as they are not OMF(Oracle managed files)

when ever a recovery happens point of time before the file was created  for this scenearios manullay deleted the file

Drop file using an alias

ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/my_dir/my_file.dbf';

Drop file using a numeric from  filename

ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1.245.3';

Drop file using a fully qualified file name

ALTER DISKGROUP disk_group_1 DROP FILE'+disk_group_1/mydb/datafile/my_cs.369.3';

Checking Metadata

Check metadata for a specific file

ALTER DISKGROUP disk_group_1 CHECK FILE '+disk_group_1/my_dir/my_file.dbf';

Check metadata for a specific failure group in the disk group

ALTER DISKGROUP disk_group_1 CHECK FAILGROUP failure_group_1;

Check metadata for a specific disk in the disk group

ALTER DISKGROUP disk_group_1 CHECK DISK diska1;

Check metadata for asll disks in the diskgroup

ALTER DISKGROUP disk_group_1 CHECK ALL;

Rebalancing

Rebalancing speed is controlled by the ASM_POWER_LIMIT initialization parameter setting to 0 will disable disk rebalancing

ALTER DISKGROUP data REBALANCE POWER 12 WAIT;

Converting a database to ASM

Ensure the database is using SPFILE and not PFILE (it about time after all)set parameters on the target database

for eg :if we set both DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST  we should get mirrored control files and duplexed log files by default

sql> alter system set DB_CREATE_FILE_DEST='+DATA'

sql>alter system set DB_RECOVERY_FILE_DEST_SIZE= 20G;

sql>alter system set DB_RECOVERY_FILE_DEST='+RECOVER';

sql>alter system set CONTROL_FILES='+DATA';

start the database in NOMOUNT mode and restore the control file into the new location from the old location

RMAN>connect target/

RMAN> STARTUP NOMOUNT;

RMAN>RESTORE CONTROLFILE FROM 'old_control_file_name';

Mount the database and copy the database into the ASM disk group;

RMAN>ALTER DATABASE MOUNT;

RMAN> CONFIGURE DEVICE TYPE DISK PARALLESISM 6;

RMAN>BACKUP AS COPY  DATABASE FORMAT '+DATA';

Switch all datafiles to the new ASM location and open the database

RMAN>SWITCH DATABASE TO COPY;

RMAN>ALTER DATABASE OPEN;

Add new tempfiles and drop the old tempfiles

sql>alter tablespace temp add tempfile;

sql>alter database tempfile '-' DROP;

sql>select * from dba_temp_files;

optionally move SPFILE into ASM

sql>CREATE SPFILE '+DATA' FROM PFILE;

Move redo log files into ASM for each group

sql>ALTER DATABASE DROP LOGFILE GROUP 1;

sql>ALTER DATABASE ADD LOGFILE GROUP 1 SIZE 200M;

logfile is active and cannot be dropped issue an aaALTER DATABASE SWITCH LOGFILE ; command and try again

Convert a tablespace to ASM

ensure the database is in archive log mode and from rman

connect target;

sql "alter tablespace TSNAME offline";

backup as copy tablespace TSNAME to copy;

sql "alter tablespace TSNAME online";

exit;

Convert a datafile to ASM

ensure the database is in archive log mode and from rman

connect target;

sql "alter database datafile '--' offline";

backup as copy datafile'-- 'format '+DATA';   

switch datafile '--' to copy;

sql "alter database datafile '--' 'online";

exit;

Create new tablespace on ASM

CREATE TABLESPACE my_ts DATAFILE '+disk_group_1'SIZE 200M AUTOEXTEND ON;

  

Note :Info on  Oracle asm commands it may be differ from your environment prod,test,dev

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME