Frequently used SQL Queries for ASM:
Few frequently used SQL queries
related to ASM :
1) Create Disk Group:
Create Disk groups using the CREATE DISKGROUP statement and specify
the level of redundancy.
Disk group redundancy types:
NORMAL REDUNDANCY à
Two-way mirroring,requiring two
failure groups.
HIGH REDUNDANCY à
Three-way mirroring,requiring
three failure groups.
EXTERNAL REDUNDANCY à
No mirroring for disks that are
already protected using hardware RAID
or MIRRORING.
Example 1 à EXTERNAL
REDUNDANCY
SQL> create diskgroup DATA external redundancy disk
'/dev/oracleasm/disks/DISK1' name DATA_1;
Example 2 à NORMAL REDUNDANCY
SQL> CREATE DISKGROUP data NORMAL REDUNDANCY FAILGROUP
failure_group_1 DISK '/dev/oracleasm/disks/DISK2' NAME
DATA_2,'/dev/oracleasm/disks/DISK3' NAME DATA_3,FAILGROUP failure_group_2 DISK
'/dev/oracleasm/disks/DISK4' NAME DATA_4,'/dev/oracleasm/disks/DISK5' NAME
DATA_5;
2) Drop Disk Group:
Using DROP DISKGROUP statement à
SQL> DROP DISKGROUP data INCLUDING CONTENTS;
3) Alter Disk Group:
Add or remove disks from disk
groups Using ALTER DISKGROUP statement. You can also use wildcard “*” to
reference disks.
3.1)
Add a disk à
SQL> ALTER
DISKGROUP data ADD DISK ”/dev/oracleasm/disks/DISK6′;
3.2) Drop/remove a disk.
SQL> ALTER
DISKGROUP data DROP DISK DATA_5;
3.3) Undrop disk
The UNDROP command used to undo
only pending drop of disks. After you drop the disks you cannot revert.
SQL> ALTER DISKGROUP data UNDROP DISKS;
3.4) Diskgroup Rebalance:
Disk groups can be rebalanced
manually Using REBALANCE clause and you can modify the POWER clause default
value.
SQL> ALTER DISKGROUP DATA REBALANCE POWER 8;
3.5)
MOUNT and DISMOUNT DiskGroups:
Normally Disk groups are
mounted at ASM instance startup and dismounted at shutdown.
Using MOUNT and DISMOUNT
options you can make one or more Disk Groups available or unavailable.
SQL> ALTER
DISKGROUP data MOUNT;
SQL> ALTER
DISKGROUP data DISMOUNT;
SQL> ALTER
DISKGROUP ALL MOUNT;
SQL> ALTER
DISKGROUP ALL DISMOUNT;
3.6) DiskGroup Check:
Use CHECK ALL to verify the
internal consistency of disk group metadata and repair in case of any error.
SQL> ALTER DISKGROUP data CHECK ALL;
3.7) DiskGroup resize:
Resize the one or all disks in
the Diskgroup.
Resize all disks in a failure
group.
SQL> ALTER DISKGROUP data RESIZE DISKS IN FAILGROUP
failure_group_1 SIZE 1024G;
Resize a specific disk.
SQL> ALTER DISKGROUP data RESIZE DISK DATA_0006 SIZE 100G;
Resize all disks in a disk
group.
SQL> ALTER DISKGROUP data RESIZE ALL SIZE 100G;
4) To find ASM Diskgroup and
Disks status
set lines 132
col name format a14
col PATH format a33
select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB,FREE_MB,VOTING_FILES from v$asm_diskgroup;
select
GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,VOTING_FILE,name,path
from v$asm_disk;
***********************************************************
Frequently used SQL Queries for ASM:
Few frequently used SQL queries related to ASM :
1) Create Disk Group:
Create Disk groups using the CREATE DISKGROUP statement and specify the level of redundancy.
Disk group redundancy types:
NORMAL REDUNDANCY:
Two-way mirroring,requiring two failure groups.
HIGH REDUNDANCY:
Three-way mirroring,requiring three failure groups.
EXTERNAL REDUNDANCY:
No mirroring for disks that are already protected using hardware RAID or MIRRORING.
Example 1 --> EXTERNAL REDUNDANCY
SQL> create diskgroup DATA external redundancy disk '/dev/oracleasm/disks/DISK1' name DATA_1;
Example 2 --> NORMAL REDUNDANCY
SQL> CREATE DISKGROUP data NORMAL REDUNDANCY FAILGROUP failure_group_1 DISK '/dev/oracleasm/disks/DISK2' NAME DATA_2,'/dev/oracleasm/disks/DISK3' NAME DATA_3,FAILGROUP failure_group_2 DISK '/dev/oracleasm/disks/DISK4' NAME DATA_4,'/dev/oracleasm/disks/DISK5' NAME DATA_5;
2) Drop Disk Group:
Using DROP DISKGROUP statement:
SQL> DROP DISKGROUP data INCLUDING CONTENTS;
3) Alter Disk Group:
Add or remove disks from disk groups Using ALTER DISKGROUP statement. You can also use wildcard "*" to reference disks.
3.1) Add a disk:
SQL> ALTER DISKGROUP data ADD DISK '/dev/oracleasm/disks/DISK6';
3.2) Drop/remove a disk:
SQL> ALTER DISKGROUP data DROP DISK DATA_5;
3.3) Undrop disk:
The UNDROP command used to undo only pending drop of disks. After you drop the disks you cannot revert.
SQL> ALTER DISKGROUP data UNDROP DISKS;
3.4) Diskgroup Rebalance:
Disk groups can be rebalanced manually Using REBALANCE clause and you can modify the POWER clause default value.
SQL> ALTER DISKGROUP DATA REBALANCE POWER 8;
3.5) MOUNT and DISMOUNT DiskGroups:
Normally Disk groups are mounted at ASM instance startup and dismounted at shutdown.
Using MOUNT and DISMOUNT options you can make one or more Disk Groups available or unavailable.
SQL> ALTER DISKGROUP data MOUNT;
SQL> ALTER DISKGROUP data DISMOUNT;
SQL> ALTER DISKGROUP ALL MOUNT;
SQL> ALTER DISKGROUP ALL DISMOUNT;
3.6) DiskGroup Check:
Use CHECK ALL to verify the internal consistency of disk group metadata and repair in case of any error.
SQL> ALTER DISKGROUP data CHECK ALL;
3.7) DiskGroup resize:
Resize the one or all disks in the Diskgroup.
Resize all disks in a failure group.
SQL> ALTER DISKGROUP data RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 1024G;
Resize a specific disk.
SQL> ALTER DISKGROUP data RESIZE DISK DATA_0006 SIZE 100G;
Resize all disks in a disk group.
SQL> ALTER DISKGROUP data RESIZE ALL SIZE 100G;
4) To find ASM Diskgroup and Disks status
set lines 132
col name format a14
col PATH format a33
select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB,FREE_MB,VOTING_FILES from v$asm_diskgroup;
select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,VOTING_FILE,name,path from v$asm_disk;
**********************************************
Tablespace And Datafiles in ASM:
Extending an ASM Datafile:
The following example shows how to extend a datafile to the APP_DATA tablespace in the +DATA ASM group.
1. Identify the datafiles for the tablespace you want to increase:
SQL> select file_name, bytes / 1024 / 1024 MB from dba_data_files where tablespace_name = 'APP_DATA' order by file_name;
FILE_NAME MB
------------------------------------------------------------ -------
+DATA/SID/datafile/app_data.259.669898683 20000
+DATA/SID/datafile/app_data.267.636216519 28100
2. Extened the Datafile:
SQL> alter database datafile '+DATA/SID/datafile/app_data.259.669898683' resize 30000M;
Tablespace altered.
Adding an ASM Datafile:
The following example shows how to add a datafile to the APP_DATA tablespace in the +DATA ASM group.
1. Find out where the current datafiles are defined. In this example the ASM group is +STORE1.
SQL> select file_name,bytes/1024/1024 MB from dba_data_files where tablespace_name = 'APP_DATA' order by file_name;
2. Add the datafile:
SQL> alter tablespace APP_DATA add datafile '+DATA' size 20000M;
Tablespace altered.
How to create tablespaces in RAC/ASM ?
SQL> create tablespace test datafile '+DATA' size 50M autoextend on next 100M extent management local segment space management auto;
Tablespace created.
SQL> select tablespace_name from dba_data_files;
TABLESPACE_NAME
------------------------------
USERS
UNDOTBS1
SYSAUX
SYSTEM
EXAMPLE
UNDOTBS2
TEST
SQL> alter tablespace test add datafile '+DATA' size 50M autoextend on next 100M;
Tablespace altered.
SQL> select file_name from dba_data_files where tablespace_name='TEST';
FILE_NAME
+DATA/orcl/datafile/test.271.823780803
+DATA/orcl/datafile/test.272.823781017
NOTE:
When ASM creates a datafile for a permanent tablespace, the datafile is set to auto-extensible with an unlimited maximum size and 100 MB default size. You can use the AUTOEXTEND clause to override this default extensibility and the SIZE clause to override the default size.
ASM applies attributes to the datafile, as specified in the system default template for a datafile as shown in the table in Managing disk group templates.
If there is a disk group named DATA, you can create a tablespace TBSLAVORO in that disk group with the following SQL statement:
SQL> CREATE TABLESPACE TBSLAVORO DATAFILE ‘+DATA’;
The following example illustrates the usage of Oracle ASM with defaults. This example enables Oracle ASM to create and manage the tablespace data file for you, using Oracle supplied defaults that are adequate for most situations.
Assume the following initialization parameter setting:
DB_CREATE_FILE_DEST = +data
The following statement creates the tablespace and its data file:
SQL> CREATE TABLESPACE tblspace;
Create Tablespaces Using ASM:
Creating a Tablespace Using an ASM Disk Group as the Destination
There are different ways to create tablespaces using ASM disks. This topic will first look at creating an ASM tablespace, allowing the default ASM disk location to be used (as a result of having set the DB_CREATE_FILE_DEST parameter). It will then describe how to create a tablespace datafile by explicitly referencing the ASM diskgroup that it is supposed to be assigned to.
Creating Tablespaces Using Default ASM Assignments:
If you have defined a default ASM location, you can use the create tablespace command to create a tablepsace that will have a file in the ASM diskgroup by default as seen in this example:
SQL> create tablespace test_rgf datafile size 100k;
You can see where Oracle put the datafile by querying the DBA_DATA_FILES view:
SQL> Select tablespace_name, file_name from dba_data_files Where tablespace_name='TEST_RGF';
TABLESPACE FILE_NAME
---------- ---------------------------------------------------------
TEST_RGF +COOKED_DGROUP1/rob10gr2/datafile/test_rgf.256.613064385
Note in this example that Oracle went ahead and filled out the rest of the path, giving you a complete filename in the DBA_DATA_FILES view to work with. You can also see this new file in the ASM instance using the V$ASM_FILES view as seen here:
SQL> select group_number, file_number, type, blocks, bytes from v$asm_file;
GROUP_NUMBER FILE_NUMBER TYPE BLOCKS BYTES
------------ ----------- -------------------- ---------- ----------
1 256 DATAFILE 14 114688
If you want to drop a tablespace that contains ASM files, you only need issue the drop tablespace command. Oracle will clean up all of the ASM datafiles associated with that tablespace.
You can have a mix of ASM datafiles and normal datafiles assigned to a tablespace as seen in this create tablespace statement:
SQL> Create tablespace part_asm_tbs Datafile 'c:\oracle\oradata\rob10gr2\part_asm_tbs_01.dbf' size 10M, '+COOKED_DGROUP1' size 100k;
You can see where the datafiles were created:
SQL> Select tablespace_name, file_name from dba_data_files Where tablespace_name='PART_ASM_TBS';
TABLESPACE_NAME FILE_NAME
--------------- ------------------------------------------------------------
PART_ASM_TBS C:\ORACLE\ORADATA\ROB10GR2\PART_ASM_TBS_01.DBF
PART_ASM_TBS +COOKED_DGROUP1/rob10gr2/datafile/part_asm_tbs.256.613066047
Note that in this case, if you drop the part_asm_tbs tablespace, only the ASM files related to that tablespace would be removed from the disk when you issue the drop tablespace command. In cases such as these, you need to make sure you include the including contents and datafiles parameter with the drop tablespace command.
Creating Tablespaces Referencing Specific ASM Disk Groups
There are going to be many times when you will not want to define a default ASM disk group to write all tablespaces too. In this case you will want to reference the specific ASM disk group that you want a datafile created in when you issue the create tablespace command. Here is an example:
SQL> create tablespace another_test datafile '+COOKED_DGROUP1' size 100k;
You can see where Oracle put the datafile by querying the DBA_DATA_FILES view:
SQL> Select tablespace_name, file_name from dba_data_files Where tablespace_name='ANOTHER_TEST';
TABLESPACE_NAME FILE_NAME
--------------- ------------------------------------------------------------
ANOTHER_TEST +COOKED_DGROUP1/rob10gr2/datafile/another_test.256.613065911
The create tablespace command comes with a number of different options when you are using cooked file systems and there is no reason you cannot use those options when you are using ASM file systems. For example, you can create a tablespace with autoextend enabled as seen here:
SQL> create tablespace another_test datafile '+COOKED_DGROUP1' size 100k autoextend on next 10m maxsize 300M;
If you want to create a tablespace using a template other than the default template, this is also possible as seen in this example:
SQL> create tablespace different_template datafile '+COOKED_DGROUP1(alternate_template)';
Tablespace Maintenance When Using Tablespaces Referencing Specific ASM Disk Groups
Tablespace maintenance is basically unchanged when using ASM disks. For example, you can add a datafile with the alter tablespace command as you normally would:
SQL> alter tablespace part_asm_tbs add datafile '+COOKED_DGROUP1' size 100k;
***********************************************************************************
Redo Logs in ASM:
Creating Online Redo Logs in an ASM Disk Group Location:
Creation of redo logs on ASM disks is straight-forward. If the database was configured to use ASM from the beginning then the existing online redo logs will already be on ASM disk groups. Assuming that the parameters DB_ONLINE_CREATE_LOG_DEST_n or DB_CREATE_FILE_DEST are set, you can simply issue the alter database add logfile command and Oracle will add a new redo log group to your database for you as seen in this example:
SQL> alter database add logfile size 100M;
You can also manually add a redo log file group to a disk group if you prefer using SQL such as the following which will create a new logfile group, and multiplex it, between two ASM disk groups:
SQL> alter database add logfile ('+COOKED_DGROUP1','+COOKED_DGROUP2') size 100M;
Defining an ASM Disk Group Location as an Archived Redo Log Storage Area:
Once you have created an ASM disk you may use it as the storage for archived redo logs. Set one of the LOG_ARCHIVE_DEST_N parameters to point to that ASM disk group as seen in this example:
SQL> alter system set log_archive_dest_1='location=+COOKED_DGROUP2';
You can check the ASM instance and see the new archived logs being created as seen in this example code:
SQL> select a.group_number, b.name, a.incarnation, a.file_number, a.type from v$ASM_DISKGROUPSb, v$asm_file a where a.group_number=b.group_number and a.type='ARCHIVELOG';
Add Redo Logs in ASM:
1. Show existing Redo Log Groups:
SQL> select inst_id,group#,status,type,member from gv$logfile;
SQL> select inst_id,group#,thread#,sequence#,bytes,members,status from gv$log;
– Redo group 1 and 2 are assigned to instance 1, Redo group 3 and 4 are assigned to instance 2
– Redo group 2 is active on instance 1 and instance 1 acts as recovery instance once instance 2 is down, therefore Redo group 3 is active on instance 1
– Redo group 3 is active on instance 2 and instance acts as recovery instance once instance 2 is down, therefore Redo group 1 is active on instance 2
2. Add log groups for thread 1 and thread 2 with two members, one is created on ‘+DAT1’ Diskgroup, another on '+REC1'
alter database add logfile thread 1 group 5 (‘+DAT1',’+REC1’) size 100M;
alter database add logfile thread 1 group 6 (‘+DAT1',’+REC1’) size 100M;
alter database add logfile thread 2 group 7 (‘+DAT1',’+REC1’) size 100M;
alter database add logfile thread 2 group 8 (‘+DAT1',’+REC1’) size 100M;
***********************************************************************************
ASM Commands:
Adding/Removing/Managing ASM instances:
--Use the following syntax to add configuration information about an existing ASM instance:
$ srvctl add asm -n node_name -i +asm_instance_name -o oracle_home
--Use the following syntax to remove an ASM instance:
$ srvctl remove asm -n node_name [-i +asm_instance_name]
--Use the following syntax to enable an ASM instance:
$ srvctl enable asm -n node_name [-i ] +asm_instance_name
--Use the following syntax to disable an ASM instance:
$ srvctl disable asm -n node_name [-i +asm_instance_name]
--Use the following syntax to start an ASM instance:
$ srvctl start asm -n node_name [-i +asm_instance_name] [-o start_options]
--Use the following syntax to stop an ASM instance:
$ srvctl stop asm -n node_name [-i +asm_instance_name] [-o stop_options]
--Use the following syntax to show the configuration of an ASM instance:
$ srvctl config asm -n node_name
--Use the following syntax to obtain the status of an ASM instance:
$ srvctl status asm -n node_name
NOTE:
For all of the SRVCTL commands in this section for which an option is not required, if the instance name “-i” is not specified the command applies to all ASM instances.
Managing DiskGroup inside ASM:
–Note that adding or dropping disks will initiate a rebalance of the data on the disks.
–The status of these processes can be shown by selecting from v$asm_operation.
--Quering ASM Disk Groups
SQl> col name format a25
SQl> col DATABASE_COMPATIBILITY format a10
SQl> col COMPATIBILITY format a10
SQl> select * from v$asm_diskgroup;
--or
SQl> select name, state, type, total_mb, free_mb from v$asm_diskgroup;
--Quering ASM Disks
SQl> col PATH format a55
SQl> col name format a25
SQl> select name, path, group_number, TOTAL_MB, FREE_MB, READS, WRITES, READ_TIME, WRITE_TIME from v$asm_disk order by 3,1;
SQl> select name, path, MOUNT_STATUS,HEADER_STATUS, MODE_STATUS, STATE, group_number, OS_MB, TOTAL_MB, FREE_MB, READS, WRITES, READ_TIME, WRITE_TIME, BYTES_READ, BYTES_WRITTEN, REPAIR_TIMER, MOUNT_DATE, CREATE_DATE from v$asm_disk;
Tuning and Analysis:
–Performance Statistics
–N.B Time in Hundred seconds!
SQl> col READ_TIME format 9999999999.99
SQl> col WRITE_TIME format 9999999999.99
SQl> col BYTES_READ format 99999999999999.99
SQl> col BYTES_WRITTEN format 99999999999999.99
SQl> select name, STATE, group_number, TOTAL_MB, FREE_MB,READS, WRITES, READ_TIME, WRITE_TIME, BYTES_READ, BYTES_WRITTEN, REPAIR_TIMER,MOUNT_DATE from v$asm_disk order by group_number, name;
--Check the Num of Extents in use per Disk inside one Disk Group:
SQl> select max(substr(name,1,30)) group_name, count(PXN_KFFXP) extents_per_disk,
DISK_KFFXP, GROUP_KFFXP from x$kffxp, v$ASM_DISKGROUP gr
where GROUP_KFFXP=&group_nr and GROUP_KFFXP=GROUP_NUMBER
group by GROUP_KFFXP, DISK_KFFXP order by GROUP_KFFXP, DISK_KFFXP;
--Find The File distribution Between Disks:
SQl> SELECT * FROM v$asm_alias WHERE name='PWX_DATA.272.669293645';
SQl> SELECT GROUP_KFFXP Group#,DISK_KFFXP Disk#,AU_KFFXP AU#,XNUM_KFFXP Extent# FROM X$KFFXP WHERE number_kffxp=(SELECT file_number FROM v$asm_aliasWHERE name='PWX_DATA.272.669293645');
--or
SQl> SELECT GROUP_KFFXP Group#,DISK_KFFXP Disk#,AU_KFFXP AU#,XNUM_KFFXP Extent# FROM X$KFFXP WHERE number_kffxp=&DataFile_Number;
--or
SQl> select d.name, XV.GROUP_KFFXP Group#, XV.DISK_KFFXP Disk#,
XV.NUMBER_KFFXP File_Number, XV.AU_KFFXP AU#, XV.XNUM_KFFXP Extent#,
XV.ADDR, XV.INDX, XV.INST_ID, XV.COMPOUND_KFFXP, XV.INCARN_KFFXP,
XV.PXN_KFFXP, XV.XNUM_KFFXP,XV.LXN_KFFXP, XV.FLAGS_KFFXP,
XV.CHK_KFFXP, XV.SIZE_KFFXP from v$asm_disk d, X$KFFXP XV
where d.GROUP_NUMBER=XV.GROUP_KFFXP and d.DISK_NUMBER=XV.DISK_KFFXP
and number_kffxp=&File_NUM order by 2,3,4;
--List the hierarchical tree of files stored in the diskgroup:
SQl> SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path FROM
(SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex;
Create and Modify Disk Group:
SQl> create diskgroup FRA1 external redundancy disk '/dev/vx/rdsk/oraASMdg/fra1'
ATTRIBUTE 'compatible.rdbms' = '11.1', 'compatible.asm' = '11.1';
SQl> alter diskgroup FRA1 check all;
--on +ASM2:
SQl> alter diskgroup FRA1 mount;
--Add a second disk:
SQl> alter diskgroup FRA1 add disk '/dev/vx/rdsk/oraASMdg/fra2';
--Add several disks with a wildcard:
alter diskgroup FRA1 add disk '/dev/vx/rdsk/oraASMdg/fra*';
--Remove a disk from a diskgroup:
SQl> alter diskgroup FRA1 drop disk 'FRA1_0002';
--Drop the entire DiskGroup
SQl> drop diskgroup DATA1 including contents;
--How to DROP the entire DiskGroup when it is in NOMOUNT Status
--Generate the dd command which will reset the header of all the
--disks belong the GROUP_NUMBER=0!!!!
SQl> select 'dd if=/dev/zero of=''' ||PATH||''' bs=8192 count=100' from v$asm_disk
where GROUP_NUMBER=0;
SQl> select * from v$asm_operation;
————————————————————————–
SQl> alter diskgroup FRA1 drop disk 'FRA1_0002';
SQl> alter diskgroup FRA1 add disk '/dev/vx/rdsk/fra1dg/fra3';
SQl> alter diskgroup FRA1 drop disk 'FRA1_0003';
SQl> alter diskgroup FRA1 add disk '/dev/vx/rdsk/fra1dg/fra4';
When a new diskgroup is created, it is only mounted on the local instance,
and only the instance-specific entry for the asm_diskgroups parameter is updated.
By manually mounting the diskgroup on other instances, the asm_diskgroups parameter on those instances are updated.
--on +ASM1:
SQl> create diskgroup FRA1 external redundancy disk '/dev/vx/rdsk/fradg/fra1'
ATTRIBUTE 'compatible.rdbms' = '11.1', 'compatible.asm' = '11.1';
--on +ASM2:
SQl> alter diskgroup FRA1 mount;
--It works even for on going balances!!!
SQl> alter diskgroup DATA1 rebalance power 10;
######################################################################
New ASM Command Line Utility (ASMCMD) Commands and Options:
ASMCMD Command Reference:
Command Description
--------------------
- cd Command Changes the current directory to the specified directory.
- cp Command Enables you to copy files between ASM disk groups on a local instance and remote instances.
- du Command Displays the total disk space occupied by ASM files in the specified
- ASM directory and all of its subdirectories, recursively.
- exit Command Exits ASMCMD.
- find Command Lists the paths of all occurrences of the specified name (with wildcards) under the specified directory.
- help Command Displays the syntax and description of ASMCMD commands.
- ls Command Lists the contents of an ASM directory, the attributes of the specified
- file, or the names and attributes of all disk groups.
- lsct Command Lists information about current ASM clients.
- lsdg Command Lists all disk groups and their attributes.
- lsdsk Command Lists disks visible to ASM.
- md_backup Command Creates a backup of all of the mounted disk groups.
- md_restore Command Restores disk groups from a backup.
- mkalias Command Creates an alias for system-generated filenames.
- mkdir Command Creates ASM directories.
- pwd Command Displays the path of the current ASM directory.
- remap Command Repairs a range of physical blocks on a disk.
- rm Command Deletes the specified ASM files or directories.
- rmalias Command Deletes the specified alias, retaining the file that the alias points to.
--------
-- kfed tool From Unix Prompt for reading ASM disk header.
kfed read /dev/vx/rdsk/fra1dg/fra1
SQl> CREATE and Manage Tablespaces and Datafiles on ASM:
SQl> CREATE TABLESPACE my_ts DATAFILE '+disk_group_1' SIZE 100M AUTOEXTEND ON;
SQl> ALTER TABLESPACE sysaux ADD DATAFILE '+disk_group_1' SIZE 100M;
SQl> ALTER DATABASE DATAFILE '+DATA1/dbname/datafile/audit.259.668957419' RESIZE 150M;
-------------------------
SQl> create diskgroup DATA1 external redundancy disk '/dev/vx/rdsk/oraASMdg/fra1'
ATTRIBUTE 'compatible.rdbms' = '11.1', 'compatible.asm' = '11.1';
SQl> select 'alter diskgroup DATA1 add disk ''' || PATH || ''';' from v$asm_disk
where GROUP_NUMBER=0 and rownum<=&Num_Disks_to_add;
SQl> select 'alter diskgroup FRA1 add disk ''' || PATH || ''';' from v$asm_disk
where GROUP_NUMBER=0 and rownum<=&Num_Disks_to_add;
--Remove ASM header
SQl> select 'dd if=/dev/zero of=''' ||PATH||''' bs=8192 count=100' from v$asm_disk
where GROUP_NUMBER=0;
Under the covers:
Asmcmd is implemented as a shell script that starts a Perl program called asmcmdcore. If you are a Perl programmer, you can easily extend this script to add additional commands and security checks.
Start asmcmd
The ASM Command Line Utility can be started by executing the asmcmd command. Here is a Linux/Unix example:
$ export ORACLE_SID=+ASM
$ cd $ORACLE_HOME/bin
$ asmcmd
ASMCMD> exit
$
The asmcmd utility also provides a useful "-p" option that will display the current path in the prompt. Here is an example:
$ asmcmd -p
ASMCMD [+] > cd FLASH
ASMCMD [+FLASH] >
Asmcmd commands
Some of the more interesting commands are described below -
Getting some help:
ASMCMD> help
Help for a spesific command:
ASMCMD> help ls
Navigating through the structure using "cd", "ls" and "pwd" commands:
ASMCMD> ls -l
State Type Rebal Unbal Name
MOUNTED EXTERN N N DISKGROUP1/
MOUNTED EXTERN N N FLASH/
ASMCMD> cd DISKGROUP1
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y SALES/
ASMCMD> cd SALES
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N spfilesales.ora => +DISKGROUP1/SALES/PARAMETERFILE/spfile.268.569267897
ASMCMD> cd DATAFILE
ASMCMD> pwd
+DISKGROUP1/SALES/DATAFILE
Find files:
ASMCMD> find / spfile*
+DISKGROUP1/SALES/PARAMETERFILE/spfile.268.569267897
+DISKGROUP1/SALES/spfilesales.ora
List all connected instances:
ASMCMD> lsct
DB_Name Status Software_Version Compatible_version Instance_Name
sales CONNECTED 10.2.0.1.0 10.2.0.1.0 sales1
sales CONNECTED 10.2.0.1.0 10.2.0.1.0 sales2
List all diskgroups:
ASMCMD> lsdg
State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED EXTERN N N 512 4096 1048576 11264 9885 0 9885 0 DISKGROUP1/
MOUNTED EXTERN N N 512 4096 1048576 10240 9906 0 9906 0 FLASH/
Check used space:
ASMCMD> du
Used_MB Mirror_used_MB
1523 1523
Delete files (be careful!!!):
ASMCMD> rm *
Are you sure? (y/n) y
Metadata (ASM superblocks) backup and recovery (Oracle 11g and above):
ASMCMD> md_backup –b /tmp/dg.backup –g DATA –g RECOVER
ASMCMD> md_restore –t full –g RECOVER /tmp/dg.backup
No comments:
Post a Comment