Interview Questions : ASM
!! Oracle ASM Interview Questions and Answers !!
--------------------------------------------------------------------------
ORACLE ASM
--------------------------------------------------------------------------
*. What is ASM?
Automatic Storage Management (ASM) is an integrated, high-performance database file system and disk manager. ASM is based on the principle that the database should manage storage instead of requiring an administrator to do it. ASM eliminates the need for you to directly manage potentially thousands of Oracle database files.
In Oracle Database 10g/11g there are two types of instances: database and ASM instances. The ASM instance, which is generally named +ASM, is started with the INSTANCE_TYPE=ASM init.ora parameter. This parameter, when set, signals the Oracle initialization routine to start an ASM instance and not a standard database instance. Unlike the standard database instance, the ASM instance contains no physical files; such as logfiles, controlfiles or datafiles, and only requires a few init.ora parameters for startup.
Upon startup, an ASM instance will spawn all the basic background processes, plus some new ones that are specific to the operation of ASM. The STARTUP clauses for ASM instances are similar to those for database instances. For example, RESTRICT prevents database instances from connecting to this ASM instance. NOMOUNT starts up an ASM instance without mounting any disk group. MOUNT option simply mounts all defined diskgroups
For RAC configurations, the ASM SID is +ASMx instance, where x represents the instance number.
*. What are the key benefits of ASM?
ASM provides filesystem and volume manager capabilities built into the Oracle database kernel. Withthis capability, ASM simplifies storage management tasks, such as creating/laying out databases and disk space management. Since ASM allows disk management to be done using familiar create/alter/drop SQL statements, DBAs do not need to learn a new skill set or make crucial decisions on provisioning.
The following are some key benefits of ASM:
1.ASM spreads I/O evenly across all available disk drives to prevent hot spots and maximize performance.
2.ASM eliminates the need for over provisioning and maximizes storage resource utilization facilitating database consolidation.
3.Inherent large file support.
4.Performs automatic online redistribution after the incremental addition or removal of storage capacity.
5.Maintains redundant copies of data to provide high availability, or leverages 3rd party RAID functionality.
6.Supports Oracle Database as well as Oracle Real Application Clusters (RAC).
7.Capable of leveraging 3rd party multipathing technologies.
8.For simplicity and easier migration to ASM, an Oracle database can contain ASM and non-ASM files.
9.Any new files can be created as ASM files whilst existing files can also be migrated to ASM.
10.RMAN commands enable non-ASM managed files to be relocated to an ASM disk group.
11.Enterprise Manager Database Control or Grid Control can be used to manage ASM disk and file activities.
*. What are different types of redundancies in ASM & explain?
Normal redundancy - : for 2-way mirroring, requiring two failure groups, when ASM allocates an extent for a normal redundancy file, ASM allocates a primary copy and a secondary copy. ASM chooses the disk on which to store the secondary copy in a different failure group other than the primary copy.
High redundancy - for 3-way mirroring, requiring three failure groups, in this case the extent is mirrored across 3 disks.
External redundancy - to not use ASM mirroring. This is used if you are using hardware mirroring or third party redundancy mechanism like RAID, Storage arrays.
*. How to find out the databases, which are using the ASM instance?
ASMCMD> lsct
SQL> select DB_NAME from V$ASM_CLIENT;
*. What is a diskgroup?
A disk group consists of multiple disks and is the fundamental object that ASM manages. Each disk group contains the metadata that is required for the management of space in the disk group. The ASM instance manages the metadata about the files in a Disk Group in the same way that a file system manages metadata about its files. However, the vast majority of I/O operations do not pass through the ASM instance. In a moment we will look at how file
I/O works with respect to the ASM instance.
*. What happens when an Oracle ASM diskgroup is created?
When an ASM diskgroup is created, a hierarchialfilesystem structure is created.
*. What is an incarnation number?
An incarnation number is a part of ASM filename syntax. It is derived from the timestamp. Once the file is created, its incarnation number doesnot change.
*. What is ASM striping ?
ASM spreads data evenly across all disks in a disk group to optimize performance and utilization. This even distribution of database files eliminates the need for regular monitoring and I/O performance tuning.
To balance loads across all of the disks in a disk group
To reduce I/O latency
*. What are different types of stripings in ASM & their differences?
1.Fine Striping :- Fine striping writes 128 KB data to each ASM Disk in the diskgroup in a round robin fashion, 128 KB goes tothe first disk, then the next 128 KB, goes to the next disk, etc. According to manual, The fine-grained stripe size always equals 128 KB in any configuration; this provides lower I/O latency for small I/O operations.” Small I/O operations sure sounds like a good candidate for redo logs, control files etc. The size for coarse striping can be set using the "_asm_ausize parameter."
2.Coarse-grained striping :-With coarse grained striping ASM writes data to each disk in the same round robin fashion, but writes chunks in the size of the ASM instance’s allocation unit (AU) size, default is 1MB. The size for fine grained striping can be set using the "_asm_stripesize" parameter.
*. We have a 16 TB database. I’m curious about the number of disk groups we should use; e.g. 1 large disk group, a couple of disk groups, or otherwise?
For VLDBs you will probably end up with different storage tiers; e.g with some of our large customers they have Tier1 (RAID10 FC), Tier2 (RAID5 FC), Tier3 (SATA), etc. Each one of these is mapped to a diskgroup.
*. Can my RDBMS and ASM instances run different versions?
Yes. ASM can be at a higher version or at lower version than its client databases. There’s two
components of compatiblity:
Software compatibility
Diskgroup compatibility attributes:
"compatible.asm" ---alter diskgroup dg2 set attribute 'compatible.asm'='11.1';
"compatible.rdbms" ----alter diskgroup dg2 set attribute 'compatible.rdbms'='11.1';
*. Where do I run my database listener from; i.e., ASM HOME or DB HOME?
It is recommended to run the listener from the ASM HOME. This is particularly important for RAC env, since the listener is a node-level resource. In this config, you can create additional [user] listeners from the database homes as needed.
*. How do I backup my ASM instance?
Not applicable! ASM has no files to backup, as its does not contain controlfile,redo logs etc.
*. When should I use RMAN and when should I use ASMCMD copy?
RMAN is the recommended and most complete and flexible method to backup and transport database files in ASM.
ASMCMD copy is good for copying single files
• Supports all Oracle file types
• Can be used to instantiate a Data Guard environment
• Does not update the controlfile
• Does not create OMF files
*. I’m going to do add disks to my ASM diskgroup, how long will this rebalance take?
Rebalance time is heavily driven by the three items:
1) Amount of data currently in the diskgroup
2) IO bandwidth available on the server
3) ASM_POWER_LIMIT or Rebalance Power Level (GV$ASM_OPERATION;)
ASM_POWER_LIMIT : specifies the disk rebalancing speed of the ASM instance. The higher the limit, the faster rebalancing operation, but consume lot of CPU. Lower values will take longer, but consume fewer processing and I/O resources.
• Background process "ARBx" performs the rebalance activity (where x is a number).
• If the POWER clause of a rebalance operation is not specified, then the default power will be the value of ASM_POWER_LIMIT.
• The range of asm_power_limit is from 0 to 1024. Default is one.
*. We are migrating to a new storage array. How do I move my ASM database from storage A to storage B?
Given that the new and old storage are both visible to ASM, simply add the new disks to the ASM disk group and drop the old disks. ASM rebalance will migrate data online. (NYBC)
*. How does ASM work with multipathing software?
It works great! Multipathing software is at a layer lower than ASM, and thus is transparent.
You may need to adjust ASM_DISKSTRING to specify only the path to the multipathing pseudo devices.
*. What are the file types that ASM support and keep in disk groups?
Control files ,Flashback logs ,Data Pump dump sets ,Data files ,DB SPFILE ,Data Guard configuration ,Temporary data files ,RMAN backup sets ,Change tracking bitmaps ,Online redo logs ,RMAN data file copies ,OCR files ,Archive logs ,Transport data files ,ASM SPFILE.
*. Below are the HEADER_STATUS in the v$ASM_DISK. I have taken below status from 11gR2.
·UNKNOWN - Automatic Storage Management disk header has not been read
·CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement
·INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software version.
·PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.
·MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option
·FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.
·CONFLICT - Automatic Storage Management disk was not mounted due to a conflict
·FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and OCR disks.
*. Whats is Kfed?
kfed is a utility which can be used to view the ASM Disk information. Syntax for using it is
kfed read devicename
*. ASM Specific Init.ora Parameters
.cluster_database= true
.asm_diskstring = '/dev/sd*1'
.instance_type=asm
.shared_pool_size=100M
.large_pool_size = 80M
.db_cache_size=60M
.asm_diskgroups = 'DATA','FRA'
.processes=128
*. ASM Instance Background Processes:
---------------------------------
•ARBx (ASM) : Rebalance working process ARBn performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these processes running at a time, named ARB0, ARB1, and so on.These processes are managed by the RBAL process. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.
•RBAL (Re-balancer) : RBAL runs in both database and ASM instances. In the database instance, it does a global open of ASM disks. In an ASM instance, it also coordinates rebalance activity for disk groups.RBAL, which coordinates rebalance activities
for disk resources controlled by ASM.
Database Instance ASM Background Processes:
------------------------------------------
In the database instances, there are three background process to support ASM, namely:
•ASMB, : this process contact CSS using the group name and acquires the associated ASM connect string. The connect string is subsequently used to connect to the ASM instance.
•RBAL, :which performs global opens on all disks in the disk group.A global open means that more than one database instance can be accessing the ASM disks at a time.
.O00x, : a group slave processes, with a numeric sequence starting at 000.
PATCHING : (way of patching " best way use auto patch ")
command : $ORACLE_HOME/OPatch/./opatch auto $DIR/$GRID_DB_PATCH -oh $ORACLE_HOME -ocmrf $ORACLE_HOME/dbs/ocm.rsp
OCW : primary patch for ASM
ACFS : seconday patch for ASM
DB PSU : optinal but manditory patch for asm
REM VIEW |ASM INSTANCE |DB INSTANCE
REM ----------------------------------------------------------------------------------------------------------
REM V$ASM_DISKGROUP |Describes a disk group (number, name, size |Contains one row for every open ASM
REM |related info, state, and redundancy type) |disk in the DB instance.
REM V$ASM_CLIENT |Identifies databases using disk groups |Contains no rows.
REM |managed by the ASM instance. |
REM V$ASM_DISK |Contains one row for every disk discovered |Contains rows only for disks in the
REM |by the ASM instance, including disks that |disk groups in use by that DB instance.
REM |are not part of any disk group. |
REM V$ASM_FILE |Contains one row for every ASM file in every |Contains rows only for files that are
REM |disk group mounted by the ASM instance. |currently open in the DB instance.
REM V$ASM_TEMPLATE |Contains one row for every template present in |Contains no rows.
REM |every disk group mounted by the ASM instance. |
REM V$ASM_ALIAS |Contains one row for every alias present in |Contains no rows.
REM |every disk group mounted by the ASM instance. |
REM v$ASM_OPERATION |Contains one row for every active ASM long |Contains no rows.
REM |running operation executing in the ASM instance. |
COMMANDS :
/etc/init.d/oracleasm
start-----start asm instance
stop-----stop asm instance
restart---restart asm
configure--set configuration of asm ( -i)
status----to check asm status
enable----enable asm services
disable---disable asm services
listdisks--check the asm disks
deletedisk---delete disk from asm
scandisks----scan disk from diskgreoup
querydisk /dev/sdd1 ----check disk on which disk greoup
createdisk VOL1 /dev/sdb1 ----create disk using file system
renamedisk /dev/sdb1 VOL1 ----rename disk
ADD -- srvctl add asm -n node_name -i +asm_instance_name -o oracle_home
REMOVE-- srvctl remove asm -n node_name [-i +asm_instance_name]
ENABLE -- srvctl enable asm -n node_name [-i ] +asm_instance_name
DISABLE -- srvctl disable asm -n node_name [-i +asm_instance_name]
START -- srvctl start asm -n node_name [-i +asm_instance_name] [-o start_options]
STOP -- srvctl stop asm -n node_name [-i +asm_instance_name] [-o stop_options]
CONFIG-- srvctl config asm -n node_name
STATUS -- srvctl status asm -n node_name
CHECK REBALABCE : v$asm_operation.
CHECCK CLIENT : v$asm_clients or lsct
CHECK SPACE : v$asm_disk, v$asm_diskgroup
CHECK ALISE : v$asm_alias (X$KFFXP)
create diskgroup : create diskgroup FRA1 external redundancy disk '/dev/vx/rdsk/oraASMdg/fra1'
--ATTRIBUTE 'compatible.rdbms' = '11.1', 'compatible.asm' = '11.1';
alter diskgroup
check: alter diskgroup FRA1 check all;
mount: alter diskgroup FRA1 mount;
add disk: alter diskgroup FRA1 add disk '/dev/vx/rdsk/oraASMdg/fra2'; OR '/dev/vx/rdsk/oraASMdg/fra*';
remove disk:alter diskgroup FRA1 drop disk 'FRA1_0002';
rebalance : alter diskgroup DATA1 rebalance power 10;
drop diskgreoup: drop diskgroup DATA1 including contents;
•copy command :
ASM to filesystem -->
ASMCMD> cp dumpfile1.dmp dumpfile1.dmp dumpfile1.dmp /oracle/backup/testdb/expdp
copying +FRA/TESTDB/EXPDP/dumpfile1.dmp -> /oracle/backup/testdb/expdp/dumpfile1.dmp
copying +FRA/TESTDB/EXPDP/dumpfile2.dmp -> /oracle/backup/testdb/expdp/dumpfile2.dmp
copying +FRA/TESTDB/EXPDP/dumpfile3.dmp -> /oracle/backup/testdb/expdp/dumpfile3.dmp
•File system to asm : -->
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile1.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile1.dmp -> +FRA/TESTDB/EXPDP/dumpfile1.dmp
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile2.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile2.dmp -> +FRA/TESTDB/EXPDP/dumpfile2.dmp
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile3.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile3.dmp -> +FRA/TESTDB/EXPDP/dumpfile3.dmp
ASMCMD> ls
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
OR
set timing on
BEGIN
dbms_file_transfer.get_file('SOURCE_DUMP',
'test.dmp',
'SOURCEDB',
'TARGET_DUMP',
'test.dmp');
END;
/
OR
RMAN> copy datafile '/u01/oradata/racdb/trtst01.dbf' to '+DATADG';
RMAN> COPY DATAFILE '+ASMDISK2/orcl/datafile/users.256.565313879' TO '+ASMDISK1';
•If you have used RMAN (method 4 b) use the following option of RMAN
RMAN>
run
{
set newname for datafile '+ASMDISK2/orcl/datafile/users.256.565313879'
to '+ASMDISK1/orcl/datafile/users.259.565359071';
switch datafile all;
}
•Delete the datafile from its original location.
SQL> ALTER DISKGROUP ASMDISK2 DROP FILE users.256.565313879;
or
ASMCMD> rm -rf <filename>
--------------------------------------------------------------------------
ORACLE ASM
--------------------------------------------------------------------------
*. What is ASM?
Automatic Storage Management (ASM) is an integrated, high-performance database file system and disk manager. ASM is based on the principle that the database should manage storage instead of requiring an administrator to do it. ASM eliminates the need for you to directly manage potentially thousands of Oracle database files.
In Oracle Database 10g/11g there are two types of instances: database and ASM instances. The ASM instance, which is generally named +ASM, is started with the INSTANCE_TYPE=ASM init.ora parameter. This parameter, when set, signals the Oracle initialization routine to start an ASM instance and not a standard database instance. Unlike the standard database instance, the ASM instance contains no physical files; such as logfiles, controlfiles or datafiles, and only requires a few init.ora parameters for startup.
Upon startup, an ASM instance will spawn all the basic background processes, plus some new ones that are specific to the operation of ASM. The STARTUP clauses for ASM instances are similar to those for database instances. For example, RESTRICT prevents database instances from connecting to this ASM instance. NOMOUNT starts up an ASM instance without mounting any disk group. MOUNT option simply mounts all defined diskgroups
For RAC configurations, the ASM SID is +ASMx instance, where x represents the instance number.
*. What are the key benefits of ASM?
ASM provides filesystem and volume manager capabilities built into the Oracle database kernel. Withthis capability, ASM simplifies storage management tasks, such as creating/laying out databases and disk space management. Since ASM allows disk management to be done using familiar create/alter/drop SQL statements, DBAs do not need to learn a new skill set or make crucial decisions on provisioning.
The following are some key benefits of ASM:
1.ASM spreads I/O evenly across all available disk drives to prevent hot spots and maximize performance.
2.ASM eliminates the need for over provisioning and maximizes storage resource utilization facilitating database consolidation.
3.Inherent large file support.
4.Performs automatic online redistribution after the incremental addition or removal of storage capacity.
5.Maintains redundant copies of data to provide high availability, or leverages 3rd party RAID functionality.
6.Supports Oracle Database as well as Oracle Real Application Clusters (RAC).
7.Capable of leveraging 3rd party multipathing technologies.
8.For simplicity and easier migration to ASM, an Oracle database can contain ASM and non-ASM files.
9.Any new files can be created as ASM files whilst existing files can also be migrated to ASM.
10.RMAN commands enable non-ASM managed files to be relocated to an ASM disk group.
11.Enterprise Manager Database Control or Grid Control can be used to manage ASM disk and file activities.
*. What are different types of redundancies in ASM & explain?
Normal redundancy - : for 2-way mirroring, requiring two failure groups, when ASM allocates an extent for a normal redundancy file, ASM allocates a primary copy and a secondary copy. ASM chooses the disk on which to store the secondary copy in a different failure group other than the primary copy.
High redundancy - for 3-way mirroring, requiring three failure groups, in this case the extent is mirrored across 3 disks.
External redundancy - to not use ASM mirroring. This is used if you are using hardware mirroring or third party redundancy mechanism like RAID, Storage arrays.
*. How to find out the databases, which are using the ASM instance?
ASMCMD> lsct
SQL> select DB_NAME from V$ASM_CLIENT;
*. What is a diskgroup?
A disk group consists of multiple disks and is the fundamental object that ASM manages. Each disk group contains the metadata that is required for the management of space in the disk group. The ASM instance manages the metadata about the files in a Disk Group in the same way that a file system manages metadata about its files. However, the vast majority of I/O operations do not pass through the ASM instance. In a moment we will look at how file
I/O works with respect to the ASM instance.
*. What happens when an Oracle ASM diskgroup is created?
When an ASM diskgroup is created, a hierarchialfilesystem structure is created.
*. What is an incarnation number?
An incarnation number is a part of ASM filename syntax. It is derived from the timestamp. Once the file is created, its incarnation number doesnot change.
*. What is ASM striping ?
ASM spreads data evenly across all disks in a disk group to optimize performance and utilization. This even distribution of database files eliminates the need for regular monitoring and I/O performance tuning.
To balance loads across all of the disks in a disk group
To reduce I/O latency
*. What are different types of stripings in ASM & their differences?
1.Fine Striping :- Fine striping writes 128 KB data to each ASM Disk in the diskgroup in a round robin fashion, 128 KB goes tothe first disk, then the next 128 KB, goes to the next disk, etc. According to manual, The fine-grained stripe size always equals 128 KB in any configuration; this provides lower I/O latency for small I/O operations.” Small I/O operations sure sounds like a good candidate for redo logs, control files etc. The size for coarse striping can be set using the "_asm_ausize parameter."
2.Coarse-grained striping :-With coarse grained striping ASM writes data to each disk in the same round robin fashion, but writes chunks in the size of the ASM instance’s allocation unit (AU) size, default is 1MB. The size for fine grained striping can be set using the "_asm_stripesize" parameter.
*. We have a 16 TB database. I’m curious about the number of disk groups we should use; e.g. 1 large disk group, a couple of disk groups, or otherwise?
For VLDBs you will probably end up with different storage tiers; e.g with some of our large customers they have Tier1 (RAID10 FC), Tier2 (RAID5 FC), Tier3 (SATA), etc. Each one of these is mapped to a diskgroup.
*. Can my RDBMS and ASM instances run different versions?
Yes. ASM can be at a higher version or at lower version than its client databases. There’s two
components of compatiblity:
Software compatibility
Diskgroup compatibility attributes:
"compatible.asm" ---alter diskgroup dg2 set attribute 'compatible.asm'='11.1';
"compatible.rdbms" ----alter diskgroup dg2 set attribute 'compatible.rdbms'='11.1';
*. Where do I run my database listener from; i.e., ASM HOME or DB HOME?
It is recommended to run the listener from the ASM HOME. This is particularly important for RAC env, since the listener is a node-level resource. In this config, you can create additional [user] listeners from the database homes as needed.
*. How do I backup my ASM instance?
Not applicable! ASM has no files to backup, as its does not contain controlfile,redo logs etc.
*. When should I use RMAN and when should I use ASMCMD copy?
RMAN is the recommended and most complete and flexible method to backup and transport database files in ASM.
ASMCMD copy is good for copying single files
• Supports all Oracle file types
• Can be used to instantiate a Data Guard environment
• Does not update the controlfile
• Does not create OMF files
*. I’m going to do add disks to my ASM diskgroup, how long will this rebalance take?
Rebalance time is heavily driven by the three items:
1) Amount of data currently in the diskgroup
2) IO bandwidth available on the server
3) ASM_POWER_LIMIT or Rebalance Power Level (GV$ASM_OPERATION;)
ASM_POWER_LIMIT : specifies the disk rebalancing speed of the ASM instance. The higher the limit, the faster rebalancing operation, but consume lot of CPU. Lower values will take longer, but consume fewer processing and I/O resources.
• Background process "ARBx" performs the rebalance activity (where x is a number).
• If the POWER clause of a rebalance operation is not specified, then the default power will be the value of ASM_POWER_LIMIT.
• The range of asm_power_limit is from 0 to 1024. Default is one.
*. We are migrating to a new storage array. How do I move my ASM database from storage A to storage B?
Given that the new and old storage are both visible to ASM, simply add the new disks to the ASM disk group and drop the old disks. ASM rebalance will migrate data online. (NYBC)
*. How does ASM work with multipathing software?
It works great! Multipathing software is at a layer lower than ASM, and thus is transparent.
You may need to adjust ASM_DISKSTRING to specify only the path to the multipathing pseudo devices.
*. What are the file types that ASM support and keep in disk groups?
Control files ,Flashback logs ,Data Pump dump sets ,Data files ,DB SPFILE ,Data Guard configuration ,Temporary data files ,RMAN backup sets ,Change tracking bitmaps ,Online redo logs ,RMAN data file copies ,OCR files ,Archive logs ,Transport data files ,ASM SPFILE.
*. Below are the HEADER_STATUS in the v$ASM_DISK. I have taken below status from 11gR2.
·UNKNOWN - Automatic Storage Management disk header has not been read
·CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement
·INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software version.
·PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.
·MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option
·FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.
·CONFLICT - Automatic Storage Management disk was not mounted due to a conflict
·FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and OCR disks.
*. Whats is Kfed?
kfed is a utility which can be used to view the ASM Disk information. Syntax for using it is
kfed read devicename
*. ASM Specific Init.ora Parameters
.cluster_database= true
.asm_diskstring = '/dev/sd*1'
.instance_type=asm
.shared_pool_size=100M
.large_pool_size = 80M
.db_cache_size=60M
.asm_diskgroups = 'DATA','FRA'
.processes=128
*. ASM Instance Background Processes:
---------------------------------
•ARBx (ASM) : Rebalance working process ARBn performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these processes running at a time, named ARB0, ARB1, and so on.These processes are managed by the RBAL process. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.
•RBAL (Re-balancer) : RBAL runs in both database and ASM instances. In the database instance, it does a global open of ASM disks. In an ASM instance, it also coordinates rebalance activity for disk groups.RBAL, which coordinates rebalance activities
for disk resources controlled by ASM.
Database Instance ASM Background Processes:
------------------------------------------
In the database instances, there are three background process to support ASM, namely:
•ASMB, : this process contact CSS using the group name and acquires the associated ASM connect string. The connect string is subsequently used to connect to the ASM instance.
•RBAL, :which performs global opens on all disks in the disk group.A global open means that more than one database instance can be accessing the ASM disks at a time.
.O00x, : a group slave processes, with a numeric sequence starting at 000.
PATCHING : (way of patching " best way use auto patch ")
command : $ORACLE_HOME/OPatch/./opatch auto $DIR/$GRID_DB_PATCH -oh $ORACLE_HOME -ocmrf $ORACLE_HOME/dbs/ocm.rsp
OCW : primary patch for ASM
ACFS : seconday patch for ASM
DB PSU : optinal but manditory patch for asm
REM VIEW |ASM INSTANCE |DB INSTANCE
REM ----------------------------------------------------------------------------------------------------------
REM V$ASM_DISKGROUP |Describes a disk group (number, name, size |Contains one row for every open ASM
REM |related info, state, and redundancy type) |disk in the DB instance.
REM V$ASM_CLIENT |Identifies databases using disk groups |Contains no rows.
REM |managed by the ASM instance. |
REM V$ASM_DISK |Contains one row for every disk discovered |Contains rows only for disks in the
REM |by the ASM instance, including disks that |disk groups in use by that DB instance.
REM |are not part of any disk group. |
REM V$ASM_FILE |Contains one row for every ASM file in every |Contains rows only for files that are
REM |disk group mounted by the ASM instance. |currently open in the DB instance.
REM V$ASM_TEMPLATE |Contains one row for every template present in |Contains no rows.
REM |every disk group mounted by the ASM instance. |
REM V$ASM_ALIAS |Contains one row for every alias present in |Contains no rows.
REM |every disk group mounted by the ASM instance. |
REM v$ASM_OPERATION |Contains one row for every active ASM long |Contains no rows.
REM |running operation executing in the ASM instance. |
COMMANDS :
/etc/init.d/oracleasm
start-----start asm instance
stop-----stop asm instance
restart---restart asm
configure--set configuration of asm ( -i)
status----to check asm status
enable----enable asm services
disable---disable asm services
listdisks--check the asm disks
deletedisk---delete disk from asm
scandisks----scan disk from diskgreoup
querydisk /dev/sdd1 ----check disk on which disk greoup
createdisk VOL1 /dev/sdb1 ----create disk using file system
renamedisk /dev/sdb1 VOL1 ----rename disk
ADD -- srvctl add asm -n node_name -i +asm_instance_name -o oracle_home
REMOVE-- srvctl remove asm -n node_name [-i +asm_instance_name]
ENABLE -- srvctl enable asm -n node_name [-i ] +asm_instance_name
DISABLE -- srvctl disable asm -n node_name [-i +asm_instance_name]
START -- srvctl start asm -n node_name [-i +asm_instance_name] [-o start_options]
STOP -- srvctl stop asm -n node_name [-i +asm_instance_name] [-o stop_options]
CONFIG-- srvctl config asm -n node_name
STATUS -- srvctl status asm -n node_name
CHECK REBALABCE : v$asm_operation.
CHECCK CLIENT : v$asm_clients or lsct
CHECK SPACE : v$asm_disk, v$asm_diskgroup
CHECK ALISE : v$asm_alias (X$KFFXP)
create diskgroup : create diskgroup FRA1 external redundancy disk '/dev/vx/rdsk/oraASMdg/fra1'
--ATTRIBUTE 'compatible.rdbms' = '11.1', 'compatible.asm' = '11.1';
alter diskgroup
check: alter diskgroup FRA1 check all;
mount: alter diskgroup FRA1 mount;
add disk: alter diskgroup FRA1 add disk '/dev/vx/rdsk/oraASMdg/fra2'; OR '/dev/vx/rdsk/oraASMdg/fra*';
remove disk:alter diskgroup FRA1 drop disk 'FRA1_0002';
rebalance : alter diskgroup DATA1 rebalance power 10;
drop diskgreoup: drop diskgroup DATA1 including contents;
•copy command :
ASM to filesystem -->
ASMCMD> cp dumpfile1.dmp dumpfile1.dmp dumpfile1.dmp /oracle/backup/testdb/expdp
copying +FRA/TESTDB/EXPDP/dumpfile1.dmp -> /oracle/backup/testdb/expdp/dumpfile1.dmp
copying +FRA/TESTDB/EXPDP/dumpfile2.dmp -> /oracle/backup/testdb/expdp/dumpfile2.dmp
copying +FRA/TESTDB/EXPDP/dumpfile3.dmp -> /oracle/backup/testdb/expdp/dumpfile3.dmp
•File system to asm : -->
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile1.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile1.dmp -> +FRA/TESTDB/EXPDP/dumpfile1.dmp
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile2.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile2.dmp -> +FRA/TESTDB/EXPDP/dumpfile2.dmp
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile3.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile3.dmp -> +FRA/TESTDB/EXPDP/dumpfile3.dmp
ASMCMD> ls
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
OR
set timing on
BEGIN
dbms_file_transfer.get_file('SOURCE_DUMP',
'test.dmp',
'SOURCEDB',
'TARGET_DUMP',
'test.dmp');
END;
/
OR
RMAN> copy datafile '/u01/oradata/racdb/trtst01.dbf' to '+DATADG';
RMAN> COPY DATAFILE '+ASMDISK2/orcl/datafile/users.256.565313879' TO '+ASMDISK1';
•If you have used RMAN (method 4 b) use the following option of RMAN
RMAN>
run
{
set newname for datafile '+ASMDISK2/orcl/datafile/users.256.565313879'
to '+ASMDISK1/orcl/datafile/users.259.565359071';
switch datafile all;
}
•Delete the datafile from its original location.
SQL> ALTER DISKGROUP ASMDISK2 DROP FILE users.256.565313879;
or
ASMCMD> rm -rf <filename>
USER managment
USER :
Purpose
Use the
You can issue this statement in an Automatic Storage Management cluster to add a user and password combination to the password file that is local to the ASM instance of the current node. Each node's ASM instance can use this statement to update its own password file. The password file itself must have been created by the
You can enable a user to connect to the database through a proxy application or application server. For syntax and discussion, refer to ALTER USER.
Prerequisites
You must have the
Only a user authenticated
an Oracle user is a database account with login access to the database;
an Oracle schema is an Oracle user plus the collection of database objects owned by the user.
commands :
--user mirror
save as an .sql file and Run --> insert the schema name .
ACCEPT username char PROMPT 'Enter username for the :'
set pages 0
set echo off heading off feedback off
SELECT 'CREATE USER ' || u.username ||' IDENTIFIED ' ||' BY VALUES ''' || c.password || ''' DEFAULT TABLESPACE ' || u.default_tablespace ||' TEMPORARY TABLESPACE ' || u.temporary_tablespace ||' PROFILE ' || u.profile || case when account_status= 'OPEN' then ';' else ' Account LOCK;' end "--Creation Statement"
FROM dba_users u,user$ c where u.username=c.name and u.username=upper('&&username')
UNION
select 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE|| case when ADMIN_OPTION='YES' then ' WITH ADMIN OPTION;' else ';' end "Granted Roles"
from dba_role_privs where grantee= upper('&&username')
UNION
select 'GRANT '||PRIVILEGE||' TO '||GRANTEE|| case when ADMIN_OPTION='YES' then ' WITH ADMIN OPTION;' else ';' end "Granted System Privileges"
from dba_sys_privs where grantee= upper('&&username')
UNION
select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||case when GRANTABLE='YES' then ' WITH GRANT OPTION;' else ';' end "Granted Object Privileges"
from DBA_TAB_PRIVS where GRANTEE=upper('&&username');
set heading on pages 1000
col USERNAME for a25
col PASSWORD for a25
col account_status for a23
col PROFILE for a15
col DEFAULT_TABLESPACE for a20
col TEMPORARY_TABLESPACE for a20
PROMPT
SELECT A.USERNAME,B.PASSWORD,A.ACCOUNT_STATUS,A.PROFILE,A.DEFAULT_TABLESPACE,A.TEMPORARY_TABLESPACE FROM DBA_USERS A, USER$ B WHERE A.USER_ID=B.USER# AND USERNAME=UPPER('&&username');
PROMPT
PROMPT USER's OBJECT COUNT:
PROMPT --------------------
select USERNAME,
count(decode(o.TYPE#, 2,o.OBJ#,'')) Tables,
count(decode(o.TYPE#, 1,o.OBJ#,'')) Indexes,
count(decode(o.TYPE#, 5,o.OBJ#,'')) Syns,
count(decode(o.TYPE#, 4,o.OBJ#,'')) Views,
count(decode(o.TYPE#, 6,o.OBJ#,'')) Seqs,
count(decode(o.TYPE#, 7,o.OBJ#,'')) Procs,
count(decode(o.TYPE#, 8,o.OBJ#,'')) Funcs,
count(decode(o.TYPE#, 9,o.OBJ#,'')) Pkgs,
count(decode(o.TYPE#,12,o.OBJ#,'')) Trigs,
count(decode(o.TYPE#,10,o.OBJ#,'')) Deps
from obj$ o,
dba_users u
where u.USER_ID = o.OWNER# (+) and u.USERNAME=upper('&&username')
group by USERNAME
order by USERNAME;
set heading off
PROMPT
select 'SCHEMA SIZE: '||ceil(sum(bytes)/1024/1024)||' MB' from dba_segments where owner=UPPER('&&username') group by owner;
PROMPT ------------
PROMPT
select 'Number of Invalid Objects: '||count(*) from dba_objects where STATUS = 'INVALID' and owner=upper('&&username');
PROMPT --------------------------
PROMPT
select 'Number of Connected Sessions: ' || count(*) from gv$session where username=upper('&&username');
PROMPT -----------------------------
Grants :
select 'grant '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' to GJAIN;' FROM DBA_TAB_PRIVS WHERE GRANTEE='EFIGUERO';
select 'grant '||PRIVILEGE||' to GJAIN;' from dba_sys_privs where GRANTEE='EFIGUERO';
select 'grant '||GRANTED_ROLE||' TO GJAIN;' from dba_role_privs where grantee='EFIGUERO';
CREATE USER statement to create and configure a database user,
which is an account through which you can log in to the database, and
to establish the means by which Oracle Database permits access by the
user.You can issue this statement in an Automatic Storage Management cluster to add a user and password combination to the password file that is local to the ASM instance of the current node. Each node's ASM instance can use this statement to update its own password file. The password file itself must have been created by the
ORAPWD utility.You can enable a user to connect to the database through a proxy application or application server. For syntax and discussion, refer to ALTER USER.
Prerequisites
You must have the
CREATE USER system privilege. When you create a user with the CREATE USER statement, the user's privilege domain is empty. To log on to Oracle Database, a user must have the CREATE SESSION system privilege. Therefore, after creating a user, you should grant the user at least the CREATE SESSION system privilege. Refer to GRANT for more information.Only a user authenticated
AS SYSASM can issue this command to modify the Automatic Storage Management instance password file. an Oracle user is a database account with login access to the database;
an Oracle schema is an Oracle user plus the collection of database objects owned by the user.
commands :
--user mirror
save as an .sql file and Run --> insert the schema name .
ACCEPT username char PROMPT 'Enter username for the :'
set pages 0
set echo off heading off feedback off
SELECT 'CREATE USER ' || u.username ||' IDENTIFIED ' ||' BY VALUES ''' || c.password || ''' DEFAULT TABLESPACE ' || u.default_tablespace ||' TEMPORARY TABLESPACE ' || u.temporary_tablespace ||' PROFILE ' || u.profile || case when account_status= 'OPEN' then ';' else ' Account LOCK;' end "--Creation Statement"
FROM dba_users u,user$ c where u.username=c.name and u.username=upper('&&username')
UNION
select 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE|| case when ADMIN_OPTION='YES' then ' WITH ADMIN OPTION;' else ';' end "Granted Roles"
from dba_role_privs where grantee= upper('&&username')
UNION
select 'GRANT '||PRIVILEGE||' TO '||GRANTEE|| case when ADMIN_OPTION='YES' then ' WITH ADMIN OPTION;' else ';' end "Granted System Privileges"
from dba_sys_privs where grantee= upper('&&username')
UNION
select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||case when GRANTABLE='YES' then ' WITH GRANT OPTION;' else ';' end "Granted Object Privileges"
from DBA_TAB_PRIVS where GRANTEE=upper('&&username');
set heading on pages 1000
col USERNAME for a25
col PASSWORD for a25
col account_status for a23
col PROFILE for a15
col DEFAULT_TABLESPACE for a20
col TEMPORARY_TABLESPACE for a20
PROMPT
SELECT A.USERNAME,B.PASSWORD,A.ACCOUNT_STATUS,A.PROFILE,A.DEFAULT_TABLESPACE,A.TEMPORARY_TABLESPACE FROM DBA_USERS A, USER$ B WHERE A.USER_ID=B.USER# AND USERNAME=UPPER('&&username');
PROMPT
PROMPT USER's OBJECT COUNT:
PROMPT --------------------
select USERNAME,
count(decode(o.TYPE#, 2,o.OBJ#,'')) Tables,
count(decode(o.TYPE#, 1,o.OBJ#,'')) Indexes,
count(decode(o.TYPE#, 5,o.OBJ#,'')) Syns,
count(decode(o.TYPE#, 4,o.OBJ#,'')) Views,
count(decode(o.TYPE#, 6,o.OBJ#,'')) Seqs,
count(decode(o.TYPE#, 7,o.OBJ#,'')) Procs,
count(decode(o.TYPE#, 8,o.OBJ#,'')) Funcs,
count(decode(o.TYPE#, 9,o.OBJ#,'')) Pkgs,
count(decode(o.TYPE#,12,o.OBJ#,'')) Trigs,
count(decode(o.TYPE#,10,o.OBJ#,'')) Deps
from obj$ o,
dba_users u
where u.USER_ID = o.OWNER# (+) and u.USERNAME=upper('&&username')
group by USERNAME
order by USERNAME;
set heading off
PROMPT
select 'SCHEMA SIZE: '||ceil(sum(bytes)/1024/1024)||' MB' from dba_segments where owner=UPPER('&&username') group by owner;
PROMPT ------------
PROMPT
select 'Number of Invalid Objects: '||count(*) from dba_objects where STATUS = 'INVALID' and owner=upper('&&username');
PROMPT --------------------------
PROMPT
select 'Number of Connected Sessions: ' || count(*) from gv$session where username=upper('&&username');
PROMPT -----------------------------
Grants :
select 'grant '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' to GJAIN;' FROM DBA_TAB_PRIVS WHERE GRANTEE='EFIGUERO';
select 'grant '||PRIVILEGE||' to GJAIN;' from dba_sys_privs where GRANTEE='EFIGUERO';
select 'grant '||GRANTED_ROLE||' TO GJAIN;' from dba_role_privs where grantee='EFIGUERO';