Tuesday, July 01, 2008

Create ASM instance and Diskgroups with Oracle 11g on Windows XP PC

Purpose:
Manually create an ASM instances and use blank files to simulate ASM disks for creating ASM diskgroups

Environment:
Oracle 11g on Window XP

Referenece
http://www.idevelopment.info/data/Oracle/DBA_tips/Automatic_Storage_Management/ASM_22.shtml


Steps

1. Create an instance parameter file

----- file: c:\app\oracle\admin\+ASM\init.ora ----------
instance_type=asm
--------------------------------------------------------

Note: This file only contains one line

2. For windows, manually create a new Windows Service

C:\> oradim -new -asmsid +ASM -syspwd oracle

3. Starting the ASM instance

set ORACLE_SID=+ASM

C:\> sqlplus "/ as sysdba"

SQL&> startup pfile='c:\app\oracle\admin\+ASM\init.ora'
ASM instance started

Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 509162388 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

4. create spfile from pfile

SQL> create spfile from pfile='c:\app\oracle\admin\+ASM\pfile\init.ora';

File created.

shutdown/startup (using spfile)

5. Modify init.ora parameter:

SQL> alter system set "_asm_allow_only_raw_disks"=false scope=spfile;
SQL> alter system set asm_diskstring='C:\asmdisks\_file*' scope=both;

6. Create files for ASM disks:

asmtool -create c:\asmdisks\_file_disk10 100
asmtool -create c:\asmdisks\_file_disk11 100
asmtool -create c:\asmdisks\_file_disk12 100
asmtool -create c:\asmdisks\_file_disk13 100


7. Create ASM diskgroups

e.g.



CREATE DISKGROUP orcl_dg1 external REDUNDANCY
DISK 'c:\asmdisks\_file_disk12',
'c:\asmdisks\_file_disk13';


idle> @diskgroup_show.sql
idle> col name format a20
idle> select name, state, type, total_mb, free_mb from v$asm_diskgroup;

NAME STATE TYPE TOTAL_MB FREE_MB
-------------------- ----------- ------ ---------- ----------
ORCL_DG2 MOUNTED EXTERN 200 148
ORCL_DG1 MOUNTED EXTERN 200 148


idle> @disk_discover.sql
idle> col path format a30
idle> SELECT name, mount_status, header_status, state, path
2 FROM v$asm_disk
3 order by name;

NAME MOUNT_S HEADER_STATU STATE PATH
-------------------- ------- ------------ -------- ------------------------------
ORCL_DG1_0000 CACHED MEMBER NORMAL C:\ASMDISKS\_FILE_DISK12
ORCL_DG1_0001 CACHED MEMBER NORMAL C:\ASMDISKS\_FILE_DISK13
ORCL_DG2_0000 CACHED MEMBER NORMAL C:\ASMDISKS\_FILE_DISK10
ORCL_DG2_0001 CACHED MEMBER NORMAL C:\ASMDISKS\_FILE_DISK11






8. Using ASM disk group - example



dennis@TESTDB11> @cr_asm_ts1
dennis@TESTDB11> set echo on
dennis@TESTDB11> spool cr_asm_ts1
dennis@TESTDB11>
dennis@TESTDB11> drop tablespace asm_ts1 including contents and datafiles;

Tablespace dropped.

dennis@TESTDB11>
dennis@TESTDB11> create tablespace asm_ts1 datafile '+ORCL_DG1' size 20M;

Tablespace created.

dennis@TESTDB11>
dennis@TESTDB11> select tablespace_name, file_name from dba_data_files
2 where tablespace_name like 'ASM%';

TABLESPACE_NAME
------------------------------
FILE_NAME
----------------------------------------------------------------------------------
----
ASM_TS1
+ORCL_DG1/testdb11g/datafile/asm_ts1.256.658928691


dennis@TESTDB11>
dennis@TESTDB11> drop table t1;
drop table t1
*
ERROR at line 1:
ORA-00942: table or view does not exist


dennis@TESTDB11>
dennis@TESTDB11> create table t1 tablespace asm_ts1
2 as
3 select * from all_objects
4 where rownum < 10;

dennis@TESTDB11> select table_name, tablespace_name from user_tables
2 where table_name='T1';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T1 ASM_TS1

No comments: