Creating a new DiskGroup, specifying the Allocation Unit Size for it, creating a Tablespace in a Pluggable Database and creating multiple Segments (Tables) with multiple Extents.
I have added a new disk to my 12.1 VM.
The new disk is /dev/asm-disk6.
I create an ASM DiskGroup with AU Size of 1MB.
I then create a Tablespace in a Pluggable Database specifically on this DG, instead of the default location for new Tablespace datafiles.
Thus, we can see that the Usable Space in the Disk Group is less than the DiskGroup size, even with External Redundancy that specifies no mirroring of ASM Extents. The Usable space in the Disk Group is 1987MB after creating a 100MB datafile.
Bytes available in the datafile are 99MB because a Locally Managed Tablespace reserves space for the Extent Map in the datafile header blocks. The datafile, belonging to a Tablespace in a Pluggable Database has a path that is identified by the DB_UNIQUE_NAME (RAC) and the PDB GUID as the identifier (44BBC69CE8F552AEE053334EA8C07365)
Now, I create 5 small tables, each with 5 extents of 64KB in the Tablespace. I use DEFERRED_SEGMENT_CREATION=FALSE to force creation of the Segment and allocation of all the Extents.
Thus, a short demo of :
1. Creating a new ASM DiskGroup with a specified AU Size
2. Creating a Tablespace in the new DG (not being the default location)
3. Creating multiple tables with pre-allocated Extents
4. Verifying the Usable Space in the DiskGroup and Datafile
.
.
.
I have added a new disk to my 12.1 VM.
SQL> select path, name, total_mb, header_status, state, group_number
2 from v$asm_disk
3 order by 1
4 /
PATH
--------------------------------------------------------------------------------
NAME TOTAL_MB HEADER_STATU STATE GROUP_NUMBER
------------------------------ ---------- ------------ -------- ------------
/dev/asm-disk1
DATA_0000 5114 MEMBER NORMAL 1
/dev/asm-disk2
DATA_0001 5114 MEMBER NORMAL 1
/dev/asm-disk3
FRA_0000 5114 MEMBER NORMAL 2
/dev/asm-disk4
OCRVOTE_0000 5114 MEMBER NORMAL 3
/dev/asm-disk5
DATA_0002 12284 MEMBER NORMAL 1
/dev/asm-disk6
0 CANDIDATE NORMAL 0
6 rows selected.
SQL>
The new disk is /dev/asm-disk6.
I create an ASM DiskGroup with AU Size of 1MB.
SQL> create diskgroup NEWDG_1M_AU external redundancy
2 disk '/dev/asm-disk6'
3 attribute 'compatible.asm'='12.1', 'compatible.rdbms'='12.1','au_size'='1M';
Diskgroup created.
SQL>
SQL> select path, name, total_mb, header_status, state, group_number
2 from v$asm_disk
3 order by 1
4 /
PATH
--------------------------------------------------------------------------------
NAME TOTAL_MB HEADER_STATU STATE GROUP_NUMBER
------------------------------ ---------- ------------ -------- ------------
/dev/asm-disk1
DATA_0000 5114 MEMBER NORMAL 1
/dev/asm-disk2
DATA_0001 5114 MEMBER NORMAL 1
/dev/asm-disk3
FRA_0000 5114 MEMBER NORMAL 2
/dev/asm-disk4
OCRVOTE_0000 5114 MEMBER NORMAL 3
/dev/asm-disk5
DATA_0002 12284 MEMBER NORMAL 1
/dev/asm-disk6
NEWDG_1M_AU_0000 2149 MEMBER NORMAL 4
6 rows selected.
SQL>
SQL> select group_number, name, sector_size, block_size, allocation_unit_size, state
2 from v$asm_diskgroup
3 order by 1
4 /
GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE
-------------------- -----------
1 DATA 512 4096
1048576 MOUNTED
2 FRA 512 4096
1048576 MOUNTED
3 OCRVOTE 512 4096
1048576 MOUNTED
4 NEWDG_1M_AU 512 4096
1048576 MOUNTED
SQL>
I then create a Tablespace in a Pluggable Database specifically on this DG, instead of the default location for new Tablespace datafiles.
SQL> alter session set container=PDB;
Session altered.
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +DATA
SQL>
SQL> create tablespace NEWTS_ON_1M_AU
2 datafile '+NEWDG_1M_AU'
3 extent management local autoallocate segment space management auto;
Tablespace created.
SQL>
SQL> select group_number, name, total_mb, usable_file_mb
2 from v$asm_diskgroup
3 order by 1
4 /
GROUP_NUMBER NAME TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
1 DATA 22512 12865
2 FRA 5114 2851
3 OCRVOTE 5114 4820
4 NEWDG_1M_AU 2149 1987
SQL>
SQL> drop tablespace NEWTS_ON_1M_AU including contents and datafiles;
Tablespace dropped.
SQL> select group_number, name, total_mb, usable_file_mb
2 from v$asm_diskgroup
3 order by 1
4 /
GROUP_NUMBER NAME TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
1 DATA 22512 12865
2 FRA 5114 2851
3 OCRVOTE 5114 4820
4 NEWDG_1M_AU 2149 2089
SQL>
SQL> create tablespace NEWTS_ON_1M_AU
2 datafile '+NEWDG_1M_AU'
3 extent management local autoallocate segment space management auto;
Tablespace created.
SQL> select group_number, name, total_mb, usable_file_mb
2 from v$asm_diskgroup
3 order by 1
4 /
GROUP_NUMBER NAME TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
1 DATA 22512 12865
2 FRA 5114 2851
3 OCRVOTE 5114 4820
4 NEWDG_1M_AU 2149 1987
SQL>
SQL> select file_name, bytes/1048576, user_bytes/1048576
2 from dba_data_files
3 where tablespace_name = 'NEWTS_ON_1M_AU'
4 /
FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576 USER_BYTES/1048576
------------- ------------------
+NEWDG_1M_AU/RAC/44BBC69CE8F552AEE053334EA8C07365/DATAFILE/newts_on_1m_au.256.95
3594325
100 99
SQL>
Thus, we can see that the Usable Space in the Disk Group is less than the DiskGroup size, even with External Redundancy that specifies no mirroring of ASM Extents. The Usable space in the Disk Group is 1987MB after creating a 100MB datafile.
Bytes available in the datafile are 99MB because a Locally Managed Tablespace reserves space for the Extent Map in the datafile header blocks. The datafile, belonging to a Tablespace in a Pluggable Database has a path that is identified by the DB_UNIQUE_NAME (RAC) and the PDB GUID as the identifier (44BBC69CE8F552AEE053334EA8C07365)
Now, I create 5 small tables, each with 5 extents of 64KB in the Tablespace. I use DEFERRED_SEGMENT_CREATION=FALSE to force creation of the Segment and allocation of all the Extents.
SQL> connect hemant/hemant@PDB
Connected.
SQL> alter session set deferred_segment_creation=FALSE;
Session altered.
SQL> create table t1 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);
Table created.
SQL> create table t2 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);
Table created.
SQL> create table t3 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);
Table created.
SQL> create table t4 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);
Table created.
SQL> create table t5 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);
Table created.
SQL>
SQL>
SQL> connect system/manager@PDB
Connected.
SQL> select sum(bytes)/1024, count(*)
2 from dba_extents
3 where tablespace_name = 'NEWTS_ON_1M_AU'
4 /
SUM(BYTES)/1024 COUNT(*)
--------------- ----------
25600 25
SQL> select file_name, bytes/1048576, user_bytes/1048576
2 from dba_data_files
3 where tablespace_name = 'NEWTS_ON_1M_AU'
4 /
FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576 USER_BYTES/1048576
------------- ------------------
+NEWDG_1M_AU/RAC/44BBC69CE8F552AEE053334EA8C07365/DATAFILE/newts_on_1m_au.256.95
3594325
100 99
SQL> select group_number, name, total_mb, usable_file_mb
2 from v$asm_diskgroup
3 order by 1
4 /
GROUP_NUMBER NAME TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
1 DATA 22512 12865
2 FRA 5114 2832
3 OCRVOTE 5114 4820
4 NEWDG_1M_AU 2149 1987
SQL>
Thus, a short demo of :
1. Creating a new ASM DiskGroup with a specified AU Size
2. Creating a Tablespace in the new DG (not being the default location)
3. Creating multiple tables with pre-allocated Extents
4. Verifying the Usable Space in the DiskGroup and Datafile
.
.
.
No comments:
Post a Comment