Archive

Archive for the ‘Oracle Basics’ Category

How does an instance registers with the listener !!!

August 23, 2013 Leave a comment

If you check the status of the listener, some times we will see as instance status is UNKNOWN or instance is READY…. So what are these messages?…When do they appear…?

Lets explore it now,

There are basically 2 ways of getting a database registered with listener

1. Static Registration
2. Dynamic Registration

Static Registration:

This is the first and old method of registering a database with the listener.

In this scenario instance is listed in the SID_LIST section of listener.ora file like below,

LISTENER_bharatdb =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.localdomain)(PORT = 1522))
)
)

SID_LIST_LISTENER_bharatdb =
(SID_LIST =
(SID_DESC =
(SID_NAME = bharatdb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
)
)

Start the listener

[oracle@rac1 admin]$ lsnrctl start LISTENER_bharatdb

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 22-AUG-2013 06:12:07
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 11.2.0.3.0 – Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/11.2.0/db_1/log/diag/tnslsnr/rac1/listener_bharatdb/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.localdomain)(PORT=1522)))

STATUS of the LISTENER
————————
Alias                     LISTENER_bharatdb
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 – Production
Start Date                22-AUG-2013 06:12:07
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/db_1/log/diag/tnslsnr/rac1/listener_bharatdb/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1522)))
Services Summary…
Service “bharatdb” has 1 instance(s).
Instance “bharatdb”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

If you can see, listener started stating that it knows there is a services by name bharatdb but its status is unknown.

Check connection to database:

[oracle@rac1 admin]$ sqlplus dbaoper@bharatdb

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 22 06:15:47 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

We got an error stating that database isn’t available.

Check for pmon process to confirm it,

[oracle@rac1 admin]$ ps -ef | grep pmon
oracle    3830  2218  0 06:16 pts/1    00:00:00 grep pmon

DATABASE IS DOWN !!!!!!

Now we understood  why the listener reported that database knows a service but its status is UNKNOWN.It’s because we have hardcoded the instance name in the listener file.

Dynamic Registration:

Dynamically registering the instance with the listener helps us to solve the above issue.

By default instance automatically registers with the default listener named LISTENER which will be listening on port 1521.

With this, it is no longer necessary to list the database instance in the listener.ora file.  Instead, the database instance will contact the listener directly and register itself.  PMON process is responsible for dynamic registration.

Let check this out,

I have removed my listner.ora by renaming it.

[oracle@rac1.localdomain admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin

[oracle@ rac1.localdomain admin]$ ls -lrt
total 24
-rw-r–r–. 1 oracle oinstall  205 May 11  2011 shrept.lst
drwxr-xr-x. 2 oracle oinstall 1024 Aug 21 06:37 samples
-rw——-. 1 oracle oinstall  310 Aug 22 04:12 listener.ora_orig

NOTE: Default listener is able to run without listener.ora file.

Start the listener

[oracle@rac1 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 22-AUG-2013 05:30:47
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 11.2.0.3.0 – Production
Log messages written to /u01/app/oracle/product/11.2.0/db_1/log/diag/tnslsnr/rac1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.31)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 – Production
Start Date                22-AUG-2013 05:30:47
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/product/11.2.0/db_1/log/diag/tnslsnr/10.0.0.31/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.31)(PORT=1521)))
The listener supports no services
The command completed successfully

Observe that the listener has started but doesn’t know about any services i.e instances here

Check the availability of the database.

[oracle@rac1] $ps –ef | grep pmon

Database is down.

Start the database now

[oracle@rac1] sqlplus / as sysdba
 Sql> startup

Now check the listener status again

[oracle@rac1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 22-AUG-2013 05:34:48
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 – Production
Start Date                22-AUG-2013 05:30:47
Uptime                    0 days 0 hr. 4 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/product/11.2.0/db_1/log/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521)))
Services Summary…
Service “bharatdb” has 1 instance(s).
Instance “bharatdb”, status READY, has 1 handler(s) for this service…
The command completed successfully

Now you can see that listener knows about the service bharatdb and the status is shown as Ready. This didn’t come from listener file because I have removed it.

Also notice also that, unlike the static registration, this time the status is READY. The listener knows the instance is ready because the instance itself told the listener it was ready.

This works only if the listener is running on default port 1521.

Lets change the port number and verify dynamic registration

$cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.localdomain)(PORT = 1522))
)
)

Start the listener,

[oracle@rac1 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 22-AUG-2013 05:46:13
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 11.2.0.3.0 – Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/11.2.0/db_1/log/diag/tnslsnr/rac1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.localdomain)(PORT=1522)))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 – Production
Start Date                22-AUG-2013 05:46:13
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/db_1/log/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1522)))
The listener supports no services
The command completed successfully Start the database

Start the database

SQL> startup
 ORACLE instance started.
 Total System Global Area  492781568 bytes
 Fixed Size                  2229504 bytes
 Variable Size             343935744 bytes
 Database Buffers          134217728 bytes
 Redo Buffers               12398592 bytes
 Database mounted.
 Database opened.

Check the listener status,

[oracle@rac1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 22-AUG-2013 05:46:35
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.localdomain)(PORT=1522)))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 – Production
Start Date                22-AUG-2013 05:46:13
Uptime                    0 days 0 hr. 0 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/db_1/log/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1522)))
The listener supports no services
The command completed successfully

If you can see, database is unable to locate the listener and couldn’t register itself.

So how to solve this now !!!!!

We have 2 options,

  1. Go with static registration process or
  2. Set LOCAL_LISTENER initialization parameter

 

The LOCAL_LISTENER parameter:

Setting this parameter will allow the database to register itself with a listener running on non-default port.

This parameter specifies a network name that should be resolved through tnsnames.ora

Checking for this parameter in my database,

SQL> sho parameter db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      bharatdb

SQL> sho parameter local_listener

NAME                                 TYPE        VALUE
———————————— ———– ——————————
local_listener                       string

Here I don’t have this parameter set. So lets set it,

Option 1:

Provide the network address details directly

SQL> alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=rac1)(PORT=1522))' scope=both;
 System altered.

Option 2:

Set a value and resolve it through tnsentry

SQL> alter system set local_listener=listener_bharatdb scope=both;
 System altered.

Now resolve listener_bharatdb in tnsnames.ora

In tnsnames.ora:

[oracle@rac1 admin]$ cat tnsnames.ora
 listener_bharatdb=(ADDRESS = (PROTOCOL=TCP)(HOST=rac1)(PORT=1522))

Start the database & Check the listener status now,

[oracle@rac1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 22-AUG-2013 05:59:43
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.localdomain)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                22-AUG-2013 05:46:13
Uptime                    0 days 0 hr. 13 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/db_1/log/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1522)))
Services Summary...
Service "bharatdb" has 1 instance(s).
Instance "bharatdb", status READY, has 1 handler(s) for this service...
The command completed successfully

Here also you can see listener status as READY because, database itself is registering with the listener.

Another case,

If database is started before listener, database couldn’t find any listener to register with. So we need to force a registration of the instance as below

Ofcourse, LOCAL_LISTENER param should be set for this to work.

Sql> alter system register.
 System altered.

So now when you see an instance with status “READY”, you know that PMON communicates with the listener. An instance with status UNKNOWN is statically registered.

Hope this post cleared few of your doubts 🙂

Advertisements

Managing ASM Disk Groups

May 27, 2013 Leave a comment

View Mounted Disks

SQL> SELECT group_number, disk_number, mount_status, header_status,
mode_status, state, redundancy, name, failgroup, path
FROM v$asm_disk
WHERE mode_status = 'ONLINE';
GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE 
REDUNDA NAME FAILGROUP PATH
------------ ----------- ------- ------------ ------- ------ ------- ----- --------- --------
1 0 CACHED MEMBER ONLINE NORMAL UNKNOWN DATA1 DATA1 ORCL:DATA1

HEADER_STATUS column indicates the use of the disk and may contain one of the following values:

  • MEMBER – This disk is assigned to a disk group.
  • PROVISIONED – The disk has been stamped with ASM data thereby prepared for assigned to a disk group and use by ASM. At present though, the disk is not being used.
  • CANDIDATE – The disk has been discovered and is potentially usable by ASM, but otherwise no actions have been taken to utilize the device.

Create Disk Groups

SQL> CREATE DISKGROUP sidDG1 NORMAL REDUNDANCY
       FAILGROUP sidDB1FGa DISK
         '/dev/data2',
         '/dev/disk3'
       FAILGROUP sidDB1FGb DISK
         '/dev/data2a',
         '/dev/data3a'
Diskgroup created.

Drop Disk Group

SQL> DROP DISKGROUP sidDG1 INCLUDING CONTENTS;
Diskgroup dropped.

Adding a disk

SQL> ALTER DISKGROUP sidDG1 ADD
FAILGROUP sidDB1FGa
DISK '/dev/data4'
FAILGROUP sidDB1FGb
DISK '/dev/data4a';
Diskgroup altered.

Dropping a Disk

SQL> ALTER DISKGROUP sidDG1 DROP DISK '/dev/data4';
Diskgroup altered.

Alter rebalance power

Override the default rebalancing power limit defined in the ASM_POWER_LIMIT attribute for any disk group

SQL> ALTER DISKGROUP sidDG1 REBALANCE POWER 5;
Diskgroup altered.

Mount/Dismount Disk Groups

SQL> ALTER DISKGROUP sidDG1 DISMOUNT;
Diskgroup altered.
SQL> ALTER DISKGROUP sidDG1 MOUNT;
Diskgroup altered.

View basic metadata

SQL> SELECT name, allocation_unit_size, state, type, offline_disks
     FROM v$asm_diskgroup;

NAME           ALLOCATION_UNIT_SIZE  STATE       TYPE   OFFLINE_DISKS
--------------- -------------------- ----------- ------ -------------
sidDG1                       1048576 MOUNTED     EXTERN             0
sidDG2                       1048576 CONNECTED   EXTERN             0
SQL> SELECT group_number, disk_number, mount_status, state, redundancy
     FROM v$asm_disk;

GROUP_NUMBER DISK_NUMBER MOUNT_S STATE    REDUNDA
------------ ----------- ------- -------- -------
           1           0 CACHED  NORMAL   UNKNOWN
           1           1 CACHED  NORMAL   UNKNOWN
           2           0 CACHED  NORMAL   UNKNOWN
           2           1 CACHED  NORMAL   UNKNOWN

Internal Metadata Consistency Check

There may be inconsistencies between the internal metadata maintained for disk group files and the actual storage of those files. This can be checked through

SQL> ALTER DISKGROUP data CHECK REPAIR;
Diskgroup altered.

Resize Diskgroup

The ALTER DISKGROUP … RESIZE operation will allow one to modify the size of storage and to perform a rebalance operation as well.

SQL> ALTER DISKGROUP data
       RESIZE DISK data1
       SIZE 300 G
       REBALANCE POWER 2;
Diskgroup altered.

If the new size is greater than disk capacity, the statement will fail.

Disk Group Version Compatibility

Compatible attribute settings at the disk group level can allow older relational databases to coexist with newer ones in the same ASM environment. This attribute also determines whether or not newer ASM features are enabled.

Setting

Description

COMPATIBLE.ASM

Determines the compatibility level of the ASM instance.

COMPATIBLE.RDBMS

Determines the compatibility level of all RDBMS instances using the ASM disk group.

Suppose that an Oracle 11g R2 ASM instance needed to support Oracle 10g R1 database instances, and that one wanted to ensure that no ASM features beyond Oracle 10g were used within the disk groups. One would set the compatibility attributes for both properties to 10.1. This is the case in the disk group listed next within the V$ASM_DISKGROUP view.

SQL> SELECT name, compatibility, database_compatibility
     FROM v$asm_diskgroup;

NAME       COMPATIBIL DATABASE_C
---------- ---------- ----------
DATA       10.1.0.0.0 10.1.0.0.0

Modifying Disk Group Compatibility

If we want to use this same ASM instance to take advantage of the new features within Oracle 11g create a different disk group named “sidDG1” using the same ASM instance. Next, within this new disk group one might enable the new features by a higher setting for COMPATIBLE.ASM.

SQL> ALTER DISKGROUP sidDG1
       SET ATTRIBUTE 'compatible.asm' = '11.2';
Diskgroup altered.

If we want to restrict the disk group to be compatible to database instances

SQL> ALTER DISKGROUP sidDG1
       SET ATTRIBUTE 'compatible.rdbms' = '11.2';
Diskgroup altered.

Disk Group Access Control

We can isolate access to ASM data for one instance from another database instance which may have distinct security authorizations. Otherwise, any user with SYSDBA rights would be able to access any ASM disk group or file for any instance using the ASM installation.

With access control, a user with SYSDBA rights is selectively granted SYSASM rights on the disk group or file to which access control has been granted.

Note: This feature is available starting with Oracle 11g R2.

Access Control Privileges

The following privileges may be assigned to a disk group or to an individual file within a disk group.

Privilege

Description

owner

This is the user which created the disk group or file for which access control privileges are assigned.

group

This is a list of users assembled into an ASM group and for which access control privileges will be assigned.

other

These are the access control privileges for any SYSDBA user who is not the owner or an authorized group.

Implementing Access Control

In order to implement access control, each database installation must be owned by a distinct system account. Access control will be implemented as below

  1. One connects to the database instance using SYSDBA rights.
  2. A new disk group is created for that database instance or an existing disk group is modified. Default access control privileges are assigned to the disk group at this same time and all subsequent files created within the disk group will inherit these privilege settings.
  3. Individual files whose default access control privileges were inherited from the disk group may be overridden with file-specific privileges.

Access Control Details

The specific access control privileges for a disk group are assigned in this manner:

  1. One enables access control for the disk group.
  2. One specifies the rights for the owner, access control user group and other users in the form of a 3-digit user mask, with each digit indicating the rights for the respective user accounts.
  3. One adds system accounts as authorized users for the disk group.
  4. One adds users from the list of authorized users to the list of user groups for the disk group.

The 3-digit mask may be understood as follows, with each of the 3 positions in the mask indicating the privilege assigned to the owner, user group and other users respectively.

Privilege

Description

0

Read-write privileges

2

Read-only privileges

6

No privileges

Mask

Description

000

Read-write privilege for the owner, user group and other users.

026

Read-write privilege for the owner, read-only privilege for the user group, no privileges for other users.

066

This is the default setting and indicates read-write privilege for the owner, no privileges for the user group, no privileges for other users.

Default Access Control for a Disk Group

SQL> ALTER DISKGROUP data
        SET ATTRIBUTE 'access_control.enabled' = 'true';
Diskgroup altered.

SQL> ALTER DISKGROUP data
        SET ATTRIBUTE 'access_control.umask' = '002';
Diskgroup altered.

Managing Disk Group Access Control Groups

With the access control privileges in effect for a disk group, one can then define which system accounts belong to the user group(s) associated with the disk group. Firstwe need to define one or more system accounts as users for the disk group. The owner is always considered to have access to the disk group.

SQL> ALTER DISKGROUP data
        ADD USER 'oracle1', 'administrator';
Diskgroup altered.

Next, these users may be associated with an access control group by means of the ALTER DISK GROUP … ADD USERGROUP statement. These users will now obtain the user group privileges assigned to the disk group earlier in this discussion.

SQL> ALTER DISKGROUP data
         ADD USERGROUP 'test4x_storageadmin'
         WITH MEMBER 'oracle1', 'administrator';
Diskgroup altered.

Now list the system accounts associated with the disk group user groups by querying V$ASM_USER.

SQL> SELECT group_number, user_number, os_name
     FROM v$asm_user;

GROUP_NUMBER USER_NUMBER OS_NAME
------------ ----------- --------------------
           1           1 oracle
           1           2 administrator
           1           3 oracle1

A new member to the user group may be added.

SQL> ALTER DISKGROUP data
         MODIFY USERGROUP 'test4x_storageadmin'
         ADD MEMBER 'oracle2';
Diskgroup altered.

Or a member may be dropped from the user group, thereafter obtaining its privileges from the other users position in the mask.

SQL> ALTER DISKGROUP data
         MODIFY USERGROUP 'test4x_storageadmin'
         DROP MEMBER 'oracle2';
Diskgroup altered.

Finally a user group may be dropped altogether.

SQL> ALTER DISKGROUP data
         DROP USERGROUP 'test4x_storageadmin';
Diskgroup altered.

Additional Disk Group Attributes

 

DISK_REPAIR_TME

Individual disks within a group for which redundancy is enabled can fail while the others remain operational. Until such time as the failed device is repaired and brought back online, extents updated on the other online disks of the group become stale and must be eventually copied to the failed disk once it is back online. As long as the failed disk is part of the group and the stale extents are tracked, the disk group may be repaired once the device is online again.

The disk group attribute DISK_REPAIR_TIME indicates the time period for which stale extent tracking should occur. If the offline disk is brought online during this period, then it will be resynchronized using fast mirror resync. If the offline disk is not brought online during this time period, then it will be dropped from the disk group permanently. At a later point in time a device can be added to the group and it may be either resynchronized or rebalanced with the other disk group members as needed.

The default value for DISK_REPAIR_TIME is 3.6 hours, but it may be modified using the following command.

SQL> ALTER DISKGROUP sidDG1
       SET ATTRIBUTE 'disk_repair_time' = '4h';
Diskgroup altered.

Fast Mirror Resync

We can quickly bring a disk within a disk group back online after a crash by means of the fast mirror resync capability.

Consider the following scenario: an individual disk within a disk group crashes and for a period of two hours is offline from its disk group. Each update to a different extent creates a stale extent, although these are relatively few since updates during this period are concentrated on a few specific extents.

Once the disk comes online, re-synchronizing it with the other members of the disk group can be very time consuming. For this reason one can bring the disk online individually with the following command.

SQL> ALTER DISKGROUP sidDG1 ONLINE DISK d1_0001;
Diskgroup altered.

This immediately allows new writes to be included on this disk, avoiding any further backlog of stale extents. Then, as processing permits, extents which became stale during the disk outage are copied to the device. This enhancement provides a dramatic reduction in disk group failure repair time.

AU_SIZE

By default an allocation unit is 1 MB in size. However, if one knows in advance that very large files will be used in a disk group, then one can modify the allocation unit size at the time the disk group is created using the AU_SIZE attribute. The larger sizes permitted are 2, 4, 8, 16, 32 and 64 MB. With variable size extents the same formula in obtaining allocation units is used, but the size of the extents is much larger based upon the larger size allocation unit.

In this example a 2 MB allocation unit is specified for the disk group.

SQL> CREATE DISKGROUP sidDG1 NORMAL REDUNDANCY 
           FAILGROUP sidDB1FGa DISK '/dev/data2', '/dev/disk3' 
           FAILGROUP sidDB1FGb DISK '/dev/data2a', '/dev/data3a' 
                                        ATTRIBUTE 'au_size' = '2M'; 
Diskgroup created.

Disk Group File Attributes

One may also manage individual files within the disk groups on a limited basis.

File Permissions

All files created within the disk group will inherit the access control privileges associated with the disk group. However, each file may have its own unique set of access control privileges by means of the ALTER DISKGROUP … SET PERMISSION statement.

SQL> ALTER DISKGROUP data
      SET PERMISSION OWNER=Read write, GROUP=Read write, OTHER=Read only
      FOR FILE '+DATA/TEST4G/DATAFILE/SIDERISTS1.256.723751977';
Diskgroup altered.

Note: This feature is available starting with Oracle 11g R2.

File Name Aliases

When managing disk group files, it may be advantageous to assign an alias name. One may thereafter reference the file by its alias name rather than its system-assigned name.

SQL> ALTER DISKGROUP data
         ADD ALIAS '+DATA/TEST4G/DATAFILE/SiderisIndex' FOR
                   '+DATA/TEST4G/DATAFILE/SIDERISIDX1.263.723912065';
Diskgroup altered.

Obtaining File Metadata

Useful metadata for the ASM files is available from the V$ASM_FILE view.

SQL> SELECT group_number, file_number, bytes, type, permissions
     FROM v$asm_file;

GROUP_NUMBER FILE_NUMBER      BYTES TYPE                 PERMISSIONS
------------ ----------- ---------- -------------------- -----------------
           1         253       1536 ASMPARAMETERFILE     rw-rw-rw-
           1         256     114688 DATAFILE             rw-rw-rw-
           1         257    9748480 CONTROLFILE          rw-rw-rw-
           1         258    9748480 CONTROLFILE          rw-rw-rw-
           1         259       3584 PARAMETERFILE        rw-rw-rw-
           1         260    1056768 DATAFILE             rw-rw-rw-
           1         261   52429312 ONLINELOG            rw-rw-rw-
           1         262   52429312 ONLINELOG            rw-rw-rw-
           1         263    1056768 DATAFILE             rw-rw-rw-
           1         264     212992 DATAFILE             rw-rw-r--

Disk Group Templates

A disk group template is a set of attributes which define such things as redundancy mirroring and striping characteristics, intelligent data placement and access control settings for files stored within the disk group.

A system-defined set of templates exists for the different categories of files or file types managed by ASM. By means of such templates, files created within the disk group inherit a consistent set of storage attributes appropriate for their file type.

Each disk group may have its own templates with their own unique attributes appropriate for that disk group.

Examining Template Attributes

The V$ASM_TEMPLATE view will indicate the current set of templates associated with a disk group when the disk group is created. The view will also indicate the default attribute settings for the templates.

SQL> SELECT * FROM v$asm_template;

GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE S NAME              PRIM MIRR
------------ ------------ ------ ------ - ------------------ ---- ----
           1           60 UNPROT COARSE Y PARAMETERFILE      COLD COLD
           1           61 UNPROT COARSE Y ASMPARAMETERFILE   COLD COLD
           1           63 UNPROT COARSE Y DUMPSET            COLD COLD
           1           64 UNPROT FINE   Y CONTROLFILE        COLD COLD
           1           65 UNPROT COARSE Y FLASHFILE          COLD COLD
           1           66 UNPROT COARSE Y ARCHIVELOG         COLD COLD
           1           67 UNPROT COARSE Y ONLINELOG          COLD COLD
           1           68 UNPROT COARSE Y DATAFILE           COLD COLD
           1           69 UNPROT COARSE Y TEMPFILE           COLD COLD
           1          170 UNPROT COARSE Y BACKUPSET          COLD COLD
           1          171 UNPROT COARSE Y AUTOBACKUP         COLD COLD
           1          172 UNPROT COARSE Y XTRANSPORT         COLD COLD
           1          173 UNPROT COARSE Y CHANGETRACKING     COLD COLD
           1          174 UNPROT COARSE Y FLASHBACK          COLD COLD
           1          175 UNPROT COARSE Y DATAGUARDCONFIG    COLD COLD
           1          176 UNPROT COARSE Y OCRFILE            COLD COLD
           1          177 UNPROT COARSE Y OCRBACKUP          COLD COLD
           1          178 UNPROT COARSE Y ASM_STALE          COLD COLD

One will be able to modify these attributes, as well as the templates themselves, for each disk group.

Using a Template

By default if you want to place all data files within the hot region of the disk for a particular disk group modify the attributes of the system-supplied template named DATAFILE in this manner.

SQL> ALTER DISKGROUP data
         ALTER TEMPLATE "DATAFILE"
         ATTRIBUTES (UNPROTECTED COARSE HOT);
Diskgroup altered.

Thereafter, if one wanted to create a new data file using this template reference both the disk group and the template using the syntax ‘+DiskGroup(TemplateName)’.

SQL> ALTER TABLESPACE SiderisTS2
        ADD DATAFILE '+DATA(DATAFILE)' SIZE 100M;
Tablespace altered.

ASM Disk Group Sector Size

Disk groups include an attribute known as SECTOR_SIZE.  A substantial degradation in performance will occur if the SECTOR_SIZE setting is 512 while 4K sector disk devices are used. So we can use this setting to ensure that the operation of the disk group is consistent with the disk devices contained within the group.

SQL> CREATE DISKGROUP sidDG1 NORMAL REDUNDANCY
       FAILGROUP sidDB1FGa DISK
         '/dev/data2',
         '/dev/disk3'
       FAILGROUP sidDB1FGb DISK
         '/dev/data2a',
         '/dev/data3a'
       ATTRIBUTE 'sector_size' = '4096';
Diskgroup created.

SQL> SELECT group_number, sector_size
     FROM v$asm_diskgroup;

GROUP_NUMBER SECTOR_SIZE
------------ -----------
           1        4096

All devices within a disk group must have the same SECTOR_SIZE setting.

ASM Intelligent Data Placement

One would implement appropriate data placement of the data files using a few basic steps. First, one must ensure that the compatible.rdbms disk group attribute is set to the appropriate release.

SQL> ALTER DISKGROUP data
       SET ATTRIBUTE 'compatible.rdbms' = '11.2';
Diskgroup altered.

Next, using the ALTER DISKGROUP…MODIFY FILE command within the ASM instance, one can move the data files to the appropriate region of the disk.

SQL> ALTER DISKGROUP data
       MODIFY FILE
       '+DATA/test4g/datafile/siderists1.256.723751977'
       ATTRIBUTE (COLD);
Diskgroup altered.
SQL> ALTER DISKGROUP data
       MODIFY FILE
       '+DATA/test4g/datafile/siderisidx1.263.723912065'
       ATTRIBUTE (HOT);
Diskgroup altered.

Note: Initially this operation will only affect the placement of new file extensions. When rebalance operation happens, then all file extents will be placed according to the instructions given here.

In addition to the primary region of a file, one could also specify the placement of mirror regions using the MIRRORHOT and MIRRORCOLD attributes as well. Of course, the disk group must support mirroring in order for this option to succeed.

SQL> ALTER DISKGROUP data
       MODIFY FILE
       '+DATA/test4g/datafile/siderisidx1.263.723912065'
       ATTRIBUTE (HOT
                  MIRRORCOLD);
Diskgroup altered.
Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

StartupForce

Where all the Action Is !!!!

Pavan DBA's Blog

The DBA Knowledge Store

ORACLE-BASE - Latest Articles

Where all the Action Is !!!!