Saturday, August 15, 2020

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



 






ITIL Process

ITIL Process Introduction In this Blog i am going to explain  ITIL Process, ITIL stands for Information Technology Infrastructure Library ...