Friday, June 22, 2007

ASM Adding a New DiskGroup

**The below Steps for creating Disk are Not Standard**
**They are Faking Hardware Steps**
[root@lab01 oracle]# cd /asmdisks/
[root@lab01 asmdisks]# dd if=/dev/zero of=/asmdisks/disk6 bs=1024k count=1000
1000+0 records in
1000+0 records out
[root@lab01 asmdisks]# dd if=/dev/zero of=/asmdisks/disk7 bs=1024k count=1000
1000+0 records in
1000+0 records out
[root@lab01 asmdisks]# /sbin/losetup /dev/loop6 /asmdisks/disk6
[root@lab01 asmdisks]# /sbin/losetup /dev/loop7 /asmdisks/disk7
[root@lab01 asmdisks]# /etc/init.d/oracleasm createdisk ASMD6 /dev/loop6
Marking disk "/dev/loop6" as an ASM disk: [ OK ]
[root@lab01 asmdisks]# /etc/init.d/oracleasm createdisk ASMD7 /dev/loop7
Marking disk "/dev/loop7" as an ASM disk: [ OK ]
[root@lab01 asmdisks]#
**The above Steps for creating Disk are Not Standard**
**They are Faking Hardware Steps**

SQL> select group_number,disk_number,name,state,mode_status,mount_status,total_mb,free_mb from v$asm_disk;

G# D# NAME STATE MODE_ST MOUNT_S TOTAL_MB FREE_MB
-- -- ----- -------- ------- ------- ---------- ----------
0 0 NORMAL ONLINE CLOSED 1000 0
0 1 NORMAL ONLINE CLOSED 1000 0
1 0 ASMD1 NORMAL ONLINE CACHED 1000 516
1 1 ASMD2 NORMAL ONLINE CACHED 1000 509
1 2 ASMD3 NORMAL ONLINE CACHED 1000 509
1 3 ASMD4 NORMAL ONLINE CACHED 1000 514
1 4 ASMD5 NORMAL ONLINE CACHED 1000 516

7 rows selected.

SQL>


SQL> CREATE DISKGROUP DG1 NORMAL REDUNDANCY
FAILGROUP failure_group_1 DISK 'ORCL:ASMD6'
FAILGROUP failure_group_2 DISK 'ORCL:ASMD7'; 2 3

Diskgroup created.

SQL>

ASM Alert Log File
SQL> CREATE DISKGROUP DG1 NORMAL REDUNDANCY
FAILGROUP failure_group_1 DISK 'ORCL:ASMD6'
FAILGROUP failure_group_2 DISK 'ORCL:ASMD7'
Sat Mar 17 21:46:42 2007
NOTE: initializing header on grp 2 disk ASMD6
NOTE: initializing header on grp 2 disk ASMD7
Sat Mar 17 21:46:42 2007
NOTE: PST update: grp = 2
Sat Mar 17 21:46:42 2007
NOTE: group DG1: initial PST locations: disk 0000 (PST copy 0)
NOTE: group DG1: initial PST locations: disk 0001 (PST copy 1)
Sat Mar 17 21:46:42 2007
NOTE: cache registered group DG1 number=2 incarn=0x54a10c2f
NOTE: cache opening disk 0 of grp 2: ASMD6 label:ASMD6
NOTE: cache opening disk 1 of grp 2: ASMD7 label:ASMD7
NOTE: cache creating group 2/0x54A10C2F (DG1)
NOTE: cache mounting group 2/0x54A10C2F (DG1) succeeded
NOTE: allocating F1X0 on grp 2 disk ASMD6
NOTE: allocating F1X0 on grp 2 disk ASMD7
NOTE: diskgroup must now be re-mounted prior to first use
NOTE: cache dismounting group 2/0x54A10C2F (DG1)
Sat Mar 17 21:46:53 2007
SUCCESS: diskgroup DG1 was created
NOTE: cache registered group DG1 number=2 incarn=0xaf310c31
Sat Mar 17 21:46:53 2007
NOTE: Hbeat: instance first (grp 2)
Sat Mar 17 21:46:57 2007
NOTE: start heartbeating (grp 2)
NOTE: cache opening disk 0 of grp 2: ASMD6 label:ASMD6
Sat Mar 17 21:46:57 2007
NOTE: F1X0 found on disk 0 fcn 0.0
NOTE: cache opening disk 1 of grp 2: ASMD7 label:ASMD7
NOTE: F1X0 found on disk 1 fcn 0.0
NOTE: cache mounting (first) group 2/0xAF310C31 (DG1)
NOTE: cache recovered group 2 to fcn 0.0
Sat Mar 17 21:46:57 2007
NOTE: opening chunk 1 at fcn 0.0 ABA
NOTE: seq=2 blk=0
Sat Mar 17 21:46:57 2007
NOTE: cache mounting group 2/0xAF310C31 (DG1) succeeded
SUCCESS: diskgroup DG1 was mounted
Sat Mar 17 21:46:59 2007
NOTE: recovering COD for group 2/0xaf310c31 (DG1)
SUCCESS: completed COD recovery for group 2/0xaf310c31 (DG1)


SQL> select group_number,disk_number,name,state,mode_status,mount_status,total_mb,free_mb from v$asm_disk;

G# D# NAME STATE MODE_ST MOUNT_S TOTAL_MB FREE_MB
-- -- ----- -------- ------- ------- ---------- ----------
1 0 ASMD1 NORMAL ONLINE CACHED 1000 516
1 1 ASMD2 NORMAL ONLINE CACHED 1000 509
1 2 ASMD3 NORMAL ONLINE CACHED 1000 509
1 3 ASMD4 NORMAL ONLINE CACHED 1000 514
1 4 ASMD5 NORMAL ONLINE CACHED 1000 516
2 0 ASMD6 NORMAL ONLINE CACHED 1000 949
2 1 ASMD7 NORMAL ONLINE CACHED 1000 949

7 rows selected.

SQL>
SQL> select name,redundancy,failgroup,path from v$asm_disk where group_number=2;

NAME REDUNDA FAILGROUP PATH
----- ------- ---------------- --------------------
ASMD6 UNKNOWN FAILURE_GROUP_1 ORCL:ASMD6
ASMD7 UNKNOWN FAILURE_GROUP_2 ORCL:ASMD7

SQL>

SQL> CREATE TABLESPACE T1 DATAFILE '+DG1';

Tablespace created.

SQL>

AlertLog Content
Sat Mar 17 21:53:25 2007
CREATE TABLESPACE T1 DATAFILE '+DG1'
Sat Mar 17 21:53:29 2007
SUCCESS: diskgroup DG1 was mounted
Sat Mar 17 21:53:48 2007
SUCCESS: diskgroup DG1 was dismounted
Sat Mar 17 21:53:48 2007
SUCCESS: diskgroup DG1 was mounted
SUCCESS: diskgroup DG1 was dismounted
SUCCESS: diskgroup DG1 was mounted
Sat Mar 17 21:53:49 2007
Completed: CREATE TABLESPACE T1 DATAFILE '+DG1'

SQL> select group_number,disk_number,name,state,mode_status,mount_status,total_mb,free_mb from v$asm_disk where group_number=2;

G# D# NAME STATE MODE_ST MOUNT_S TOTAL_MB FREE_MB
-- -- ----- -------- ------- ------- ---------- ----------
2 0 ASMD6 NORMAL ONLINE CACHED 1000 846
2 1 ASMD7 NORMAL ONLINE CACHED 1000 846

SQL>

SQL> select name from v$datafile where ts# in ( select ts# from v$tablespace where name='T1');

NAME
--------------------------------------------------------------------------------
+DG1/odb/datafile/t1.256.617493211

SQL>
SQL> create user u1 identified by u1 default tablespace t1;
SQL> grant connect,resource to u1;
SQL> connect u1/u1
SQL> create table tt1 as select * from user_objects;

Table created.

SQL>

# - Posted by Senthil Rajendran - 3/17/07; 10:00:44 PM - Comment [0]

V$ASM and useful Queries

Query to find the Database that uses the disk group managed by the ASM Instance.
SQL> select instance_name,db_name,status from v$asm_client;

INSTANCE DB_NAME STATUS
-------- -------- ------------
ODB ODB CONNECTED

SQL>

Query to find the Disk that are discovered by the ASM Instance
SQL> select group_number,disk_number,name,state,mode_status,mount_status,total_mb,free_mb from v$asm_disk;

G# D# NAME STATE MODE_ST MOUNT_S TOTAL_MB FREE_MB
-- -- ----- -------- ------- ------- -------- -------
1 0 ASMD1 NORMAL ONLINE CACHED 1000 516
1 1 ASMD2 NORMAL ONLINE CACHED 1000 509
1 2 ASMD3 NORMAL ONLINE CACHED 1000 509
1 3 ASMD4 NORMAL ONLINE CACHED 1000 514
1 4 ASMD5 NORMAL ONLINE CACHED 1000 516

Query to find the Redundancy and FailGroup for Disks
SQL> select name,redundancy,failgroup,path from v$asm_disk;

NAME REDUNDA FAILGROUP PATH
----- ------- ---------- ----------
ASMD1 UNKNOWN ASMD1 ORCL:ASMD1
ASMD2 UNKNOWN ASMD2 ORCL:ASMD2
ASMD3 UNKNOWN ASMD3 ORCL:ASMD3
ASMD4 UNKNOWN ASMD4 ORCL:ASMD4
ASMD5 UNKNOWN ASMD5 ORCL:ASMD5

SQL>

Query to view the Disk Group and its details
SQL> select group_number,name,state,type,total_mb,free_mb,usable_file_mb,unbalanced from v$asm_diskgroup;

G# NAME STATE TYPE TOTAL_MB FREE_MB USABLE_FILE_MB U
-- ----- -------- ------ -------- -------- -------------- -
1 DATA MOUNTED NORMAL 5000 2564 782 N

SQL>

Query to view the ASM File Details
SQL> select group_number,file_number,bytes,space,type,redundancy,striped from v$asm_file;

GROUP_# FILE# BYTES SPACE TYPE REDUND STRIPE
------ ----- --------- ---------- -------------- ------ ------
1 256 503324672 1011875840 DATAFILE MIRROR COARSE
1 257 241180672 487587840 DATAFILE MIRROR COARSE
1 258 26222592 54525952 DATAFILE MIRROR COARSE
1 259 5251072 12582912 DATAFILE MIRROR COARSE
1 260 7061504 25165824 CONTROLFILE HIGH FINE
1 261 7061504 25165824 CONTROLFILE HIGH FINE
1 262 52429312 120586240 ONLINELOG MIRROR FINE
1 263 52429312 120586240 ONLINELOG MIRROR FINE
1 264 52429312 120586240 ONLINELOG MIRROR FINE
1 265 52429312 120586240 ONLINELOG MIRROR FINE
1 266 52429312 120586240 ONLINELOG MIRROR FINE
1 267 52429312 120586240 ONLINELOG MIRROR FINE
1 268 20979712 44040192 TEMPFILE MIRROR COARSE
1 269 2560 2097152 PARAMETERFIL MIRROR COARSE

14 rows selected.

SQL>

Popular Posts