Search My Oracle Blog

Custom Search

01 September, 2017

ASM, DiskGroup, AU Size, Tablespace and Table Extents

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.

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:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com