Questions on ASM and VPD

Reference: http://www.iselfschooling.com/mcd12_ora10g/Oracle10g_Answers.htm

Q: What does ASM provide?
A: ASM provides a vertical integration of the file system and the volume manager that is specifically built for Oracle database files.

Q: What are its key features and benefits?
A:
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

Q: Does ASM have a data dictionary?
A: ASM instance does not have a data dictionary and it is restricted to few SQL commands and Dynamic Performance view.

Q: How do you create a disk group by using ASM?
A: 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’;

Q: How do you delete a disk group by using ASM?
A: Dropping a diskgroup:
SQL> DROP DISKGROUP dgora1 INCLUDING CONTENTS;

Q: How do you add a disk to an existing disk group?
A: 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;

Q: How do you remove a disk to an existing disk group?
A: To remove a disk:
SQL> ALTER DISKGROUP dgora1 DROP DISK a5;

Q: Can you undo the removed disk? How?
A: Yes. 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;

Q: How do you display a list of your diskgroups?
A: To display a list of diskgroups.
SQL> SELECT name FROM v$asm_diskgroup;

Q: How do you display a list of associated ASM disks?
A: To 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
/

Q: How do you display a list of associated ASM files?
A: To display a list of associated ASM files.
SQL> SELECT group_number, file_number, bytes, type, striped
FROM v$asm_file
/

Q: How do you create a tablespace that uses an ASM disk group?
A: To create a tablespace that is stored in the ASM disk group dgora1.
SQL> CREATE TABLESPACE mytablespace2
DATAFILE ‘+dgora1’ SIZE 100m
/

Q: How do you add one addition disk to your system?
A: Do the following.
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.

Q: What is VPD in the Oracle 10g Database?
A: In the Oracle 10g database, there is a feature called Virtual Private Database (VPD). It enables you to build applications that enforce your security policy. When a user requests a query, the server dynamically modifies the user’s SQL statement, which is not transparent to the user. The modification is based on a WHERE clause returned by a function.