iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Oracle 10g New Features

 

Automatic Storage Management (ASM) in the Oracle 10g database

 

‘Non-violence is the greatest force at the disposal of mankind. It is mightier than the mightiest weapon of destruction devised by the ingenuity of man.’

Gandhi

 

Automatic Storage Management (ASM) in the Oracle 10g database

 

ASM provides a vertical integration of the file system and the volume manager that is specifically built for Oracle database files. Its key features and benefits are:

  • Stripes files rather than logical volumes

  • Online disk reconfiguration and dynamic rebalancing

  • Adjustable rebalancing speed

  • Provides redundancy on a file basis

  • Supports only Oracle database files

  • Custer-aware

  • Automatically installed

 

ASM instance do not have a data dictionary and it is restricted to few SQL commands and Dynamic Performance view. The following are some example of SQL commands.

 

Commands:

SQL> STARTUP/SHUTDOWN

SQL> ALTER DISKGROUP MOUNT/DISMOUNT

SQL> ALTER DISKGROUP ONLINE/OFFLINE DISK

SQL> ALTER DISKGROUP REBALANCE

SQL> ALTER DISKGROUP CHECK

…

 

Views:

V$ASM_TEMPLATE

V$ASM_DISKGROUP

V$ASM_CLIENT

V$ASM_FILE

V$ASM_ALIAS

V$ASM_DISK

V$ASM_OPERATION

…

 

The following are examples of creating and deleting disk groups.

Creating a diskgroup:

SQL> CREATE DISKGROUP dgora1 NORMAL REDUNDANCY

            FAILGROUP controller1 DISK

                        ‘/dev/rdsk/c0t0d0s2’ NAME mydisk SIZE 200G FORCE,

                        ‘/dev/rdsk/c0t1d0s2’,

                        ‘/dev/rdsk/c0t2d0s2’

            FAILGROUP controller2 DISK

                        ‘/dev/rdsk/c1t0d0s2’,

                        ‘/dev/rdsk/c1t1d0s2’,

                        ‘/dev/rdsk/c1t2d0s2’;

 

Dropping a diskgroup:

SQL> DROP DISKGROUP dgora1 INCLUDING CONTENTS;

 

The following are examples of how to add disks to an existing disk group.

SQL> ALTER DISKGROUP dgora1

                        ADD FAILGROUP controller1

                        ‘/dev/rdsk/c0t3d0s2’ NAME a5;

 

To remove a disk:

SQL> ALTER DISKGROUP dgora1 DROP DISK a5;

 

To undo the removed disk do the following SQL statement. This only works if the status of drop is pending or the drop function was not completed yet.

SQL> ALTER DISKGROUP dgora1 UNDROP DISKS;

 

Hands_On # 1:

Assuming you started your ASM instance.

 

$ ORACLE_SID=+ASM; export ORACLE_SID

$ sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> STARTUP

 

Display a list of your diskgroups.

SQL> SELECT name FROM v$asm_diskgroup;

 

Display a list of associated ASM disks.

SQL> COL name FORMAT a20

SQL> COL failgroup FORMAT a20

SQL> SELECT name, failgroup, bytes_read, bytes_written

            FROM v$asm_disk

            /

 

Display a list of associated ASM files.

SQL> SELECT group_number, file_number, bytes, type, striped

            FROM v$asm_file

            /

 

Open another session with different instance:

$ ORACLE_SID=school; export ORACLE_SID

$ $ sqlplus /nolog

SQL> CONNECT / AS SYSDBA

 

Display a list of all your datafiles.

SQL> COL file_name FORMAT a40

SQL> SELECT file_name, tablespace_name

            FROM dba_data_files

            /

 

Create a tablespace that is stored in the ASM disk group dgora1.

SQL> CREATE TABLESPACE mytablespace2

            DATAFILE ‘+dgora1’ SIZE 100m

            /

 

Display a list of all your datafiles. What do see?

SQL> COL file_name FORMAT a40

SQL> SELECT file_name, tablespace_name

            FROM dba_data_files

            /

Now, you should have one more line.

 

Go back on your ASM instance and display a list of associated ASM files.

SQL> SELECT group_number, file_number, bytes, type, striped

            FROM v$asm_file

            /

The result should be different than before. You added one more datafile to it.

 

Add one addition disk to your system.

SQL> HOST dd if=/dev/zero of=/u02/oradata/school/diska abs=1024k count=200

SQL> SELECT name, failgroup, bytes_read, bytes_written

            FROM v$asm_disk

            /

SQL> ALTER DISKGROUP dgora1

            ADD DISK ‘/u02/oradata/school/diska’

            /

 

Execute the following query until you get ‘no rows selected.’

SQL> SELECT operation, est_minutes

            FROM v$asm_operation

            /

 

Again, display a list of associated ASM disks.

SQL> SELECT name, failgroup, bytes_read, bytes_written

            FROM v$asm_disk

            /

Now, you should see one more disk was added to disk group.

 

 

Google
 
Web web site