ASM views on ASM instance after database Installation

I have created new database with ASM based filesystem, I will execute some queries to check the result. The result will be different from my last post ASM views before database installation.

V$ASM_ALIAS: Displays a row for each alias present in every disk group mounted by the ASM instance.
SQL> select * from v$ASM_ALIAS;











V$ASM_CLIENT: Displays a row for each database instance using a disk group managed by the ASM instance.







V$ASM_DISK: Displays a row for each disk discovered by the ASM instance, including disks which are not part of any disk group.





V$ASM_DISKGROUP: Displays a row for each disk group discovered by the ASM instance.





V$ASM_FILE: Displays a row for each file for each disk group mounted by the ASM instance.











V$ASM_OPERATION: Displays a row for each file for each long running operation executing in the ASM instance.











V$ASM_TEMPLATE: Displays a row for each template present in each disk group mounted by the ASM instance.

Creating Database with ASM File System

I am creating database using DBCA, and specify Automatic Storage Management as FileSystem.
Welcome screen of DBCA (Database Configuration Assistant)

















Step one of DBCA, Here we will select the Create a Database option.

















Step three, My database name and sid is "RAKESH"

















Step 6 of 13

















After pressing the next button, window will prompt for sys ASM password

















In next step, It will ask for the diskgroup that to be used for the Storage, As I have created only one diskgroup DATA, I will select it only.

















In next step I specified the files should be OMF Files

















Step 9 of 13: Flash Recovery Area,

















After the successful installation of Oracle Database with ASM File system, I log in at Enterprise Manager to verify datafiles storage. On the main page of Oracle EM, I got new link for ASM instance, check below image.




















After clicking on the +ASM_standby link, I was directed to a below ASM page in EM. .















Data Files in ASM

ASM views on ASM Instance before database Installation.

As in my previous post ASM basic configuration I created an ASM instance, now I will execute some queries to get some more detail & get some experience on ASM instance. I have not created database yet, its simple ASM instance creation without having database instance, So queries result can be different from the one with ASM based filesystem. I will create ASM database in my next post.

A very good article has been given on ASM 10g at ORACLE-BASE website, and list of v$views has been given in this article, I will execute them at ASM instance one by one.

The ASM configuration can be viewed using the v$ASM_, which often contain different information depending on whether they are queried from the ASM instance, or a dependent database instance.
















V$ASM_ALIAS : Displays a row for each alias present in every disk group mounted by the ASM instance. Here the ASM_ALIAS will not return any rows, because we have not created any database that use this ASM as filesystem.







V$ASM_CLIENT: Displays a row for each database instance using a disk group managed by the ASM instance.
As we have not created any database yet, who can use this diskgroup (data) in ASM, so query will not result any rows at this time.









V$ASM_DISK : Displays a row for each disk discovered by the ASM instance, including disks which are not part of any disk group.
As, I have created two disks, then query should return two rows for each disk.





V$ASM_DISKGROUP : Displays a row for each disk group discovered by the ASM instance.
I have created only one diskgroup DATA, query should return one row only.







V$ASM_FILE : Displays a row for each file for each disk group mounted by the ASM instance.








V$ASM_OPERATION : Displays a row for each file for each long running operation executing in the ASM instance.









V$ASM_TEMPLATE: Displays a row for each template present in each disk group mounted by the ASM instance.

ASM basic configuration at LOCAL DISK

I am writing below steps to create a ASM instance, ASM diskgroup and ASM disks on local machine, as I dont have RAW device, it will be on LOCAL DISK (NTFS) D drive, Once, the ASM instance is created, I will use DBCA to create database and specify Automatic Storage management as filesystem.
ONE:Creating disk for ASM Instance, I am creating three disk of 1GB size.

D:\>mkdir asmdisk

D:\>asmtool -create d:\asmdisk\disk1 1024

D:\>asmtool -create d:\asmdisk\disk2 1024

D:\>
I have created a folder asmdisk, and two disks each size of 1GB. Now, we can use these disks to create ASM disk group.

2 : Creating an ASM instance

We need to configure Cluster Synchronization Services.
The Cluster Synchronization Services is required to enable synchronization between ASM and its client database.

2.a- Configuration of CSS ( Cluster Synchronization Servie )

C:\oracle\product\10.2.0\db_2\BIN>localconfig add
Step 1: creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'administrator', privgrp ''..
Operation successful.
Step 2: creating new CSS service
successfully created local CSS service
successfully added CSS to home

C:\oracle\product\10.2.0\db_2\BIN>

When we check services OracleCSService is created and started.

2.b :We need to create a init pfile (init.+ASM.ora) for ASM instance

INSTANCE_TYPE=ASM
DB_UNIQUE_NAME=+ASM
LARGE_POOL_SIZE=8M
ASM_DISKSTRING='D:\asmdisk\*'
_ASM_ALLOW_ONLY_RAW_DISKS=FALSE

2.c : we need to create a password file for ASM instance, and its password is soni.

C:\oracle\product\10.2.0\db_2\BIN>orapwd file=C:\oracle\product\10.2.0\db_2\database\PWD+ASM.ora password=soni

C:\oracle\product\10.2.0\db_2\BIN>

2.d : we need to create Oracle ASM instance

C:\oracle\product\10.2.0\db_2\BIN>oradim -NEW -ASMSID +ASM -STARTMODE auto
Instance created.

THREE: Creating an ASM disk group.

SQL> select path, mount_status from v$asm_disk;

PATH MOUNT_S
---------------------------------------- -------
D:\ASMDISK\DISK1 CLOSED
D:\ASMDISK\DISK2 CLOSED

SQL> create diskgroup data external redundancy disk
2 'D:\ASMDISK\DISK1', 'D:\ASMDISK\DISK2';

Diskgroup created.

SQL> select path, mount_status from v$asm_disk;

PATH MOUNT_S
---------------------------------------- -------
D:\ASMDISK\DISK1 CACHED
D:\ASMDISK\DISK2 CACHED

SQL> create spfile from pfile;

File created.

SQL> startup force;
ASM instance started

Total System Global Area 79691776 bytes
Fixed Size 1247396 bytes
Variable Size 53278556 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted


SQL> alter system set asm_diskgroups= data scope=spfile;

System altered.

SQL> startup force;
ASM instance started

Total System Global Area 79691776 bytes
Fixed Size 1247396 bytes
Variable Size 53278556 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL>




I will use DBCA to create database and specify Automatic Storage management as filesystem.