Oracle ASM Queries for DBA

The GV$ASM views,Automatic Storage Management views are very important to gather asm structure and components related details of Oracle ASM,I find some time to have the queries which will help to gather all details with regards to Oracle ASM.

Below are some dynamic views and queries for knowing Oracle asm structure and components details:


SQL> SELECT *FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> SELECT TABLE_NAME FROM DICT WHERE TABLE_NAME LIKE '%ASM%';

TABLE_NAME
------------------------------
V$ASM_ACFSSNAPSHOTS
V$ASM_ACFSVOLUMES
V$ASM_ALIAS
V$ASM_ATTRIBUTE
V$ASM_CLIENT
V$ASM_DISK
V$ASM_DISKGROUP
V$ASM_DISKGROUP_STAT
V$ASM_DISK_IOSTAT
V$ASM_DISK_STAT
V$ASM_FILE

TABLE_NAME
------------------------------
V$ASM_FILESYSTEM
V$ASM_OPERATION
V$ASM_TEMPLATE
V$ASM_USER
V$ASM_USERGROUP
V$ASM_USERGROUP_MEMBER
V$ASM_VOLUME
V$ASM_VOLUME_STAT
GV$ASM_ACFSSNAPSHOTS
GV$ASM_ACFSVOLUMES
GV$ASM_ALIAS

TABLE_NAME
------------------------------
GV$ASM_ATTRIBUTE
GV$ASM_CLIENT
GV$ASM_DISK
GV$ASM_DISKGROUP
GV$ASM_DISKGROUP_STAT
GV$ASM_DISK_IOSTAT
GV$ASM_DISK_STAT
GV$ASM_FILE
GV$ASM_FILESYSTEM
GV$ASM_OPERATION
GV$ASM_TEMPLATE

TABLE_NAME
------------------------------
GV$ASM_USER
GV$ASM_USERGROUP
GV$ASM_USERGROUP_MEMBER
GV$ASM_VOLUME
GV$ASM_VOLUME_STAT

38 rows selected.


1)GV$ASM_DISKGROUP:
This view displays asm instances related details along with one row for every ASM diskgroup discovered by the ASM instance on the node.

SQL> SELECT INST_ID||' '||NAME ||' '||STATE||' '||TOTAL_MB||' '||FREE_MB
FROM GV$ASM_DISKGROUP;

INST_ID||''||NAME||''||STATE||''||TOTAL_MB||''||FREE_MB
--------------------------------------------------------------------------------
2 GRID_DATA_01 MOUNTED 12084 11152
2 TESTASMDB_REDO_01 CONNECTED 65296 40504
2 TESTASMDB_REDO_02 CONNECTED 65296 40504
2 TESTASMDB_FRA_01 CONNECTED 261872 142831
2 TESTASMDB_DATA_01 CONNECTED 1048016 59453
1 GRID_DATA_01 MOUNTED 12084 11152
1 TESTASMDB_DATA_01 CONNECTED 1048016 59453
1 TESTASMDB_FRA_01 CONNECTED 261872 142831
1 TESTASMDB_REDO_01 CONNECTED 65296 40504
1 TESTASMDB_REDO_02 CONNECTED 65296 40504

2)GV$ASM_ALIAS:
This view displays all system and user-defined aliases.
There is one row for every alias present in every diskgroup mounted by the ASM instance. The RDBMS instance displays no rows in this view.


SQL> SELECT INST_ID||''||NAME||''||FILE_NUMBER||''||ALIAS_DIRECTORY
FROM GV$ASM_ALIAS;


INST_ID||''||NAME||''||FILE_NUMBER||''||ALIAS_DIRECTORY
--------------------------------------------------------------------------------
2 USERS.323.750185521 323 N
2 ASMWORK.320.750263763 320 N
2 ASMMASTER.319.750263765 319 N
2 UNDOTBS3.306.768750281 306 N
2 TEMPFILE 4294967295 Y
2 TEMP.325.750185505 325 N

3)GV$ASM_ATTRIBUTE:
This Oracle Database 11g view displays one row for each ASM attribute defined.
These attributes are listed when they are defined in CREATE DISKGROUP or ALTER DISKGROUP statements. DISK_REPAIR_TIMER is an example of an attribute.

SQL> SELECT INST_ID||' '||NAME||' '|| GROUP_NUMBER
FROM GV$ASM_ATTRIBUTE;

no rows selected

4)GV$ASM_CLIENT:
This view displays one row for each RDBMS instance that has an opened ASM diskgroup.

SQL> SELECT INST_ID||' '||INSTANCE_NAME||' '||DB_NAME||' '||STATUS
FROM GV$ASM_CLIENT;

INST_ID||''||INSTANCE_NAME||''||DB_NAME||''||STATUS
--------------------------------------------------------------------------------
1 +ASM1 TESTASMDB CONNECTED
1 +ASM1 TESTASMDB CONNECTED
1 +ASM1 TESTASMDB CONNECTED
1 +ASM1 TESTASMDB CONNECTED
2 +ASM2 TESTASMDB CONNECTED
2 +ASM2 TESTASMDB CONNECTED
2 +ASM2 TESTASMDB CONNECTED
2 +ASM2 TESTASMDB CONNECTED



5)GV$ASM_DISK:
This view contains specifics about all disks discovered by the ASM isntance,
including mount status, disk state, and size.There is one row for every disk discovered by the ASM instance.


SQL> SELECT INST_ID||' '||STATE||' '||TOTAL_MB||' '||FREE_MB||' '||NAME
FROM GV$ASM_DISK;

INST_ID||''||STATE||''||TOTAL_MB||''||FREE_MB||''||NAME
--------------------------------------------------------------------------------
2 NORMAL 2014 1843 GRID_DATA_01_0000
2 NORMAL 2014 1844 GRID_DATA_01_0001
2 NORMAL 2014 1843 GRID_DATA_01_0002
2 NORMAL 2014 1878 GRID_DATA_01_0003
2 NORMAL 2014 1875 GRID_DATA_01_0004
2 NORMAL 2014 1869 GRID_DATA_01_0005
2 NORMAL 8162 5063 TESTASMDB_REDO_01_0000
2 NORMAL 8162 5061 TESTASMDB_REDO_01_0001
2 NORMAL 8162 5065 TESTASMDB_REDO_01_0002

6)GV$ASM_DISK_IOSTAT:
This displays information about disk I/O statistics for each ASM Client. If this view is queried from the database instance, only the rows for that instance are shown.

SQL> SELECT INST_ID||' '||INSTNAME||' '||DBNAME||' '||GROUP_NUMBER||' '||DISK_NUMBER||' '||READS||' '||WRITES||' '||READ_TIME||' '||WRITE_TIME
FROM GV$ASM_DISK_IOSTAT;

7)GV$ASM_DISK_STAT:
This view contains similar content as the v$ASM_DISK, except v$ASM_DISK_STAT reads disk information from cache and thus performs no disk discovery.
Thsi view is primarily used form quick acces to the disk information without the overhead of disk discovery.

8)GV$ASM_DISKGROUP_STAT:
This view contains all the similar view contents as the v$ASM_DISKGROUP,
except that v$ASM_DISK_STAT reads disk information from the cache and thus performs no disk discovery.
This view is primarily used for quick access to the diskgroup information without the overhead of disk discovery.


SQL> SELECT INST_ID||' '||NAME||' '||ALLOCATION_UNIT_SIZE||' '||STATE||' '||TOTAL_MB||' '||FREE_MB
FROM GV$ASM_DISKGROUP_STAT;

INST_ID||''||NAME||''||ALLOCATION_UNIT_SIZE||''||STATE||''||TOTAL_MB||''||FREE_M
--------------------------------------------------------------------------------
1 GRID_DATA_01 1048576 MOUNTED 12084 11152
1 TESTASMDB_DATA_01 1048576 CONNECTED 1048016 59453
1 TESTASMDB_FRA_01 1048576 CONNECTED 261872 142831
1 TESTASMDB_REDO_01 1048576 CONNECTED 65296 40504
1 TESTASMDB_REDO_02 1048576 CONNECTED 65296 40504
2 GRID_DATA_01 1048576 MOUNTED 12084 11152
2 TESTASMDB_REDO_01 1048576 CONNECTED 65296 40504
2 TESTASMDB_REDO_02 1048576 CONNECTED 65296 40504
2 TESTASMDB_FRA_01 1048576 CONNECTED 261872 142831
2 TESTASMDB_DATA_01 1048576 CONNECTED 1048016 59453

10 rows selected.

9)GV$ASM_FILE:
This view displays information about ASM files.
There is one row for every ASM file in every diskgroup mounted by the ASM instance. In a RDBMS instance, V$ASM_FILE displays no row.


SELECT INST_ID||''||FILE_NUMBER||''||BLOCK_SIZE||''||CREATION_DATE||''||REDUNDANCY
FROM GV$ASM_FILE;

INST_ID||''||FILE_NUMBER||''||BLOCK_SIZE||''||CREATION_DATE||''||REDUNDANCY
--------------------------------------------------------------------------------
1 335 512 17-MAR-12 UNPROT
1 336 512 15-MAR-12 UNPROT
1 337 512 15-MAR-12 UNPROT
1 339 512 18-MAR-12 UNPROT
1 340 512 23-MAR-12 UNPROT

10)GV$ASM_OPERATION:
This view describes the progress of an influx ASM rebalance operation.
In a RDBMS instance,GV$ASM_OPERATION displays no rows.

SQL> SELECT INST_ID||' '||OPERATION||' '||STATE||' '||POWER||' '||ACTUAL||' '||SOFAR
FROM GV$ASM_OPERATION;

11)GV$ASM_TEMPLATE:
This view contains information on user and system-defined templated.
GV$ASM_TEMPLATE displays one row for every template present in every diskgroup mounted by the ASM instance.
In a RDBMS instance,GV$ASM_TEMPLATE displays one row for every template present in every diskgroup mounted by the ASM instance with which the RDBMS instance communicates.

SQL> SELECT INST_ID||''||GROUP_NUMBER||''||STRIPE||''||SYSTEM
FROM GV$ASM_TEMPLATE;


SQL> SELECT FS_NAME||' '||VOL_DEVICE||' '||SNAP_NAME||' '||CREATE_TIME
FROM V$ASM_ACFSSNAPSHOTS;

no rows selected


Enjoy Oracle ASM learning........

Best regards,

Rafi

How FNDLOAD Utility is useful for Oracle Apps DBA

Hi,
FNDLOAD(Generic Loader) utility is very useful for Apps DBA.Let us try to understand how it works and how we can utilize this utility well.

Understanding FNDLOAD utility:
The Generic Loader (FNDLOAD) is a concurrent program that can download data from an application entity into a portable,editable text file. This file can then be uploaded into any other database to copy the data.
Data structures supported by the Loader include master- detail relationships and foreign key relationships.
FNDLOAD uses script to ensure consistent migration of objects within Oracle Applications

FNDLOAD utility Modes of Operation:

The FNDLOAD(Generic Loader) utility operates in 2 modes:
1)Download mode or
2)Upload mode
In the download mode data is downloaded from a database according to a configuration (.lct) file and then converts the data into a Data (.ldt) file. This data file can be uploaded to a different database.In both downloading and uploading, the structure of the data involved is described by a configuration file.
The configuration file describes the structure of the data and also the access methods use to copy the data into or out of the database.
The same configuration file may be used for both uploading and downloading.
When downloading,the Generic Loader creates a second file, called the data file that contains the structured data selected for downloading.
The data file has a standard syntax for representing the data that has been downloaded.

When uploading,the Generic Loader reads a data file to get the data that it is to upload.In most cases, the data file was produced by a previous download, but may have come from another source.
The data file cannot be interpreted without the corresponding configuration file available.


FNDLOAD utility syntax:

DOWNLOAD COMMAND SYNTAX:

FNDLOAD 0 Y DOWNLOAD <${FND_TOP}/patch/115/import/

UPLOAD COMMAND SYNTAX:

FNDLOAD 0 Y UPLOAD <${FND_TOP}/patch/115/import/

FNDLOAD Usage Details:

FNDLOAD can be used to migrate the following system administrator objects between instances
1.Printer Styles
2.Lookup Types and codes
3.Descriptive Flexfield (DFF)
4.Key Flexfield (KFF)
5.Concurrent programs with the parameters
6.Request Sets (when the programs are not triggered based on success)
7.Value Sets and Value set Values
8.Profiles
9.Request Groups
10.Responsibilities
11.Forms
12.Functions
13.Menus
14.Messages

Merits of FNDLOAD utility:

1.Need to maintain a baseline environment (Source for Clone) and update it on a regular basis
2.Base environment can have issues and the cloning strategy made totally ineffective. In such a case every new environment created will have to be updated with a lot of changes
3.Cloning /Refresh not possible in short intervals
4.Selective replication of setups and AOL objects not possible with cloning
5.Environments not delivered as per timeline and affecting the Testing schedules
6.Manually maintaining Environments at different level of configuration is tedious
7.Time consuming to manually update multiple environment with defect fixes and error prone
8.FNDLOAD is fully supported and recommended by Oracle for migration of FND objects. Requires 0 learning curve and 0 investment.

Demerits of FNDLOAD utility:


1.This utility can be only used for FND (System administrator) objects only.
2.Application Patching mechanisms use FNDLOAD heavily. There is a possibility of negative impact.
3.There is no validation of sensitive data that is being migrated by the FNDLOAD tool itself.

Examples of FNDLOAD utility:

Download: FNDLOAD apps/$pwd O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup_techops_aris.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="CN" LOOKUP_TYPE="XXTEST_TECHOPS_ARIS_SITES"

Download: FNDLOAD apps/$pwd O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup_techops_points.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="CN" LOOKUP_TYPE="XXTEST_TECHOPS_POINTS_SITES"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_points_procedure.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_POINTS_PRCS_EAST"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_points_load.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_POINTS_EAST_LOAD"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_points_load.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_POINTS_MW_LOAD"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_mttr_load.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_MTTR_LOAD"

Download: FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_XXTEST_techops_mttr_process.ldt PROGRAM APPLICATION_SHORT_NAME="XXTEST" CONCURRENT_PROGRAM_NAME="XXTEST_TECHOPS_MTTR_DATA_PROCES"


Upload: FNDLOAD apps/$pwd O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup_techops_aris.ldt

Upload: FNDLOAD apps/$pwd O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup_techops_points.ldt

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_points_procedure.ldt

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_points_load.ldt

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_points_load.ldt

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_mttr_load.ldt

Upload: FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct concprg_xxtest_techops_mttr_process.ldt


Enjoy and Happy Oracle Apps DBA learning.

Hope it helps...


Best regards,

Rafi.