ASM Views

The following V$views are used for managing ASM.

View Name	Description	Instance
V$ASM_DISKGROUP	Diskgroups discovered by ASM	DB
V$ASM_DISK	Disks discovered by ASM including disks that are not part of any Diskgroup.	DB
V$ASM_FILE	ASM files mounted by ASM instance	ASM
V$ASM_ALIAS	Filename alias of the ASM Files	ASM
V$ASM_OPERATION	Long running ASM operations	ASM
V$ASM_TEMPLATE	Template present in diskgroup	DB
V$ASM_CLIENT	Lists one row per each ASM instance for which the database instance has any open ASM files	DB

Thanks

Drop a file from diskgroup

Syntax

ALTER DISKGROUP diskgroup_name DROP FILE ‘filename’, ‘filename’..;

Note: ASM drops all the aliases associated with the filename.

Example 1

SQL> Alter Diskgroup ASMDATA DROP FILE ‘+ASMDB/gcprod/datafile/users.259.661355087’ , ‘+ASMDB/gcprod/datafile/sysaux.257.661355085’;

Thanks

Manage ASM Directories

In order to use ASM filename aliases, you need to create Directories.

Syntax:

To add directory
ALTER DISKGROUP diskgroup_name ADD DIRECTORY ‘filename’, ‘filename’..;

To rename directory
ALTER DISKGROUP diskgroup_name RENAME DIRECTORY ‘old_dirname’ TO ‘new_dirname’, ‘old_dirname’ TO ‘new_dirname’..;

To drop directory
ALTER DISKGROUP diskgroup_name DROP DIRECTORY ‘filename’ [FORCE or NOFORCE], ‘filename’ [FORCE or NOFORCE]..;

Where,

FORCE – To drop the directory even if it contains any alias definitions.
NOFORCE – It will not drop the directory if it contains any alias definitions

Example 1 (Add directory)

SQL> Alter Diskgroup asmdata ADD DIRECTORY ‘+ASMDATA/datafile’;

Example 2 (Rename directory)

SQL> Alter Diskgroup asmdata RENAME DIRECTORY ‘+ASMDATA/datafile’ TO ‘+ASMDATA/indexfile’;

EXAMPLE 3 (Drop directory)

SQL> Alter Diskgroup asmdata DROP DIRECTORY ‘+ASMDATA/indexfile’ FORCE;

Thanks

Mounting and Dismounting a Diskgroup

Diskgroups are automatically mounted at ASM instance startup and dismounted at ASM instance shutdown. To manually mount and dismount the diskgroups use the following statements,

Syntax:

ALTER DISKGROUP ALL or diskgroup_name MOUNT or DISMOUNT;

Example 1 (Mount a specific diskgroup)

SQL> Alter Diskgroup ASMIND Mount;

Example 2 (Dismount all diskgroups)

SQL> Alter Disgroup ALL DisMount;

Note

In the case of High and Normal Redundancy diskgroups you need all set of extents available for the diskgroups to mount. The unavailable disks may be used for redundant copies and you may mount the diskgroups without those disks. If there are sufficient disks available for the diskgroups to mount you can use the force option to mount it.

Alter Diskgroup diskgroup_name Mount Force;

The unavailable disks are taken offline. ASM drops those disks after the DISK_REPAIR_TIME expires.

In RAC ASM environments, you cannot MOUNT FORCE a disk group that is already mounted by another instance because the disks have been accessed by another instance and the disks are not locally accessible.

Thanks

Checking a Diskgroup

It checks the internal consistency of the diskgroup metadata. If any errors are found they are reported in the alert log.

Instance: ASM Instance
Privilege: Sysdba

Syntax

ALTER DISKGROUP diskgroup_name CHECK check_diskgroup_clause [REPAIR or NOREPAIR]

Where,

Check_dikgroup_clause= [ALL] or [DISK diskname] or [DISKS IN FAILGROUP failgroup_name] [FILE filename]

REPAIR = ASM attempts to repair any errors.
NOREPAIR= ASM reports the error in the alert log and does not attempt to repair it.

Example 1 (checks all the disks in the diskgroup)

SQL> Alter Diskgroup ASMDATA CHECK ALL;

Example 2 (checks only specific disks in the diskgroup)

SQL> Alter Diskgroup ASMIND CHECK
DISK data_05,
DISK data_06 NOREPAIR;

Example 3 (Checks all the disks in the failure group).

SQL> Alter Diskgroup ASMDATA CHECK
DISKS IN FAILGROUP ctrl_1;

Example 4 (checks specific files in the diskgroup)

SQL> Alter Diskgroup ASMIND CHECK
FILE ‘+ASMDB/gcprod/datafile/users.259.661355087’,
FILE ‘+ASMDB/gcprod/datafile/sysaux.257.661355085’;

Thanks

Rebalance a Diskgroup

Instance: ASM Instance
Privilege: Sysdba

Syntax

ALTER DISKGROUP diskgroup_name rebalance_diskgroup_clause

Where,

Rebalance_diskgroup_clause = REBALANCE POWER integer WAIT/NOWAIT
Integer – 0 to 11. If you omit ASM takes the value from ASM_POWER_LIMIT init parameter.
WAIT – The control is not returned to the user until the rebalance operation completes.
NOWAIT – The control immediately returns to the user and the rebalance operation takes place at the background and user can continue to do other operation.

Example 1

SQL> Alter Diskgroup ASMDATA REBALANCE POWER 10 wait;

Thanks

Resize a Disk

Note: Resizing disks in a diskgroup invokes implicit rebalancing of all the disks within the diskgroup.

Instance: ASM Instance
Privilege: Sysdba

Syntax

ALTER DISKGROUP diskgroup_name RESIZE resize_disk_clause rebalance_diskgroup_clause

Where

resize_disk_clause =[ALL SIZE size_clause] or [DISK diskname SIZE size_clause] or [DISKS IN FAILGROUP failgroup_name SIZE size_clause]

Note: If you omit the size clause then ASM uses the size returned by the OS.

Rebalance_diskgroup_clause = REBALANCE POWER integer WAIT/NOWAIT
Integer – 0 to 11. If you omit ASM takes the value from ASM_POWER_LIMIT init parameter.
WAIT – The control is not returned to the user until the rebalance operation completes.
NOWAIT – The control immediately returns to the user and the rebalance operation takes place at the background and user can continue to do other operation.


Example 1 (resizes all the disks in the diskgroup)

SQL> Alter Diskgroup ASMDATA RESIZE ALL SIZE 100G;

Example 2 (resizes only specific disks in the diskgroup)

SQL> Alter Diskgroup ASMIND RESIZE
DISK data_05 SIZE 100G,
DISK data_06
REBALANCE POWER 5;

Example 3 (resizes only specific disks in the failure group).

SQL> Alter Diskgroup ASMDATA RESIZE
DISKS IN FAILGROUP ctrl_1 SIZE 100G;

Thanks

Undrop Disk from Diskgroup

Note: To undrop a disk, the header_status from v$asm_disk view must be dropping. You cannot undrop a completely dropped disk.

Instance: ASM Instance
Privilege: Sysdba

Syntax

ALTER DISKGROUP [ALL/diskgroupname, diskgroupname..] UNDROP DISKS

Example 1

SQL> Alter Diskgroup asmdata undrop disks;

Example 2

SQL> Alter Diskgroup ALL undrop disks;

Thanks

Add Disk to Diskgroup

Note: Adding new disks to diskgroup invokes implicit rebalancing of all the disks within the diskgroup.

Instance: ASM Instance
Privilege: Sysdba

Syntax

ALTER DISKGROUP diskgroup_name ADD [FAILGROUP failgroup_name] DISK add_disk_clause rebalance_diskgroup_clause

Where

Add_disk_clause = search_string [NAME disk_name] [SIZE size_clause] [FORCE/NOFORCE]
Force – Add disk to the diskgroup even if the disk is part of some other diskgroup.
NoForce – ASM returns error if the disk is part of some other diskgroup.

Rebalance_diskgroup_clause = REBALANCE POWER integer WAIT/NOWAIT
Integer – 0 to 11. If you omit ASM takes the value from ASM_POWER_LIMIT init parameter.
WAIT – The control is not returned to the user until the rebalance operation completes.
NOWAIT – The control immediately returns to the user and the rebalance operation takes place at the background and user can continue to do other operation.

Example 1

SQL> Alter Diskgroup ASMDATA ADD
FAILGROUP ctrl_1
DISK
'/u01/asmdisks/disk1' NAME data_05 NOFORCE,
'/u01/asmdisks/disk2' NAME data_06 NOFORCE;

Example 2

SQL> Alter Diskgroup ASMIND ADD
DISK '/dev/rhdisk33'
REBALANCE POWER 5 ;

Thanks

Dropping a Diskgroup in ASM

Instance: ASM Instance
Privilege: Sysdba
Syntax:

DROP DISKGROUP diskgroup_name INCLUDING/EXCLUDING CONTENTS;

Including Contents – Drops all the files associated with the disk group.
Excluding Contents – Drops the disk group only when it is empty. Default.

Example 1

SQL> Drop Diskgroup ASMDATA including contents;

Example 2

SQL> Drop Diskgroup ASMIND;

Thanks

Create a new Diskgroup in ASM

Instance: ASM Instance.
Privilege: Sysdba
Pre-requisite: Disks must be formatted. The path of the disk must be in the ASM_DISKSTRING. Oracle DB user must have read/write permission on the disk.

Syntax:

CREATE DISKGROUP diskgroup_name redundancy_level REDUNDANCY [FAILGROUP failgroup_name]DISK disk_clause

Where,
redundancy_level = HIGH or NORMAL or EXTERNAL
disk_clause = search_string [NAME disk_name] [SIZE size_clause] [FORCE/NOFORCE]

Force – Add disk to the diskgroup even if the disk is part of some other diskgroup.
NoForce – ASM returns error if the disk is part of some other diskgroup.

Example 1

CREATE DISKGROUP asm_data
NORMAL REDUNDANCY
FAILGROUP ctrl_1
DISK
'/u01/asmdisks/disk1' NAME data_01 NOFORCE
,'/u01/asmdisks/disk2' NAME data_02 NOFORCE
FAILGROUP ctrl_2
DISK
'/u03/asmdisks/disk3' NAME data_3 NOFORCE
,'/u03/asmdisks/disk4' NAME data_4 NOFORCE;

If the size clause is omitted the full disk is assigned.

Example 2

SQL> CREATE DISKGROUP ASMIND External REDUNDANCY DISK
'/dev/rhdisk6' SIZE 51200M ,
'/dev/rhdisk7' SIZE 51200M ,
'/dev/rhdisk8' SIZE 51200M ;

If the name clause is omitted the disks are given a system generated name.

Thanks

Force Log Switches

You can force the log switches using the following statements,

SQL> Alter System Switch LogFile;
SQL> Alter System Archive Log Current;

The difference between the two statements is that the first statement issues a log switch and the control immediately returns to the user. The user can perform other activities while the log file is being archived at the background. However the second statement will not return the control to the user until the current logfile is archived. It issues a log switch and the user session hangs until the log is archived.

A number of DBAs also confuse with the following statement,

SQL> Alter System Archive Log all;

The above statement will not issue a log switch. It will archive all the non-archived log files. If you have no logfiles that are unarchived, then issuing the above statement returns the error message,

ORA-00271 there are no logs that need archiving.

Thanks
If you like the post vote for me by clicking the following button

Recompile Invalid Objects

Script to find the invalid objects in the database

SQL> Select * From dba_objects where status = 'INVALID';

To Recompile Invalid Objects

Method 1: With access to SYS user

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

Method 2: Without access to SYS user (Cannot validate SYS objects)

SQL> Exec DBMS_UTILITY.compile_schema('SYSTEM');

Method 3: Manual Script

SQL> select 'ALTER ' OBJECT_TYPE ' ' OWNER '.' OBJECT_NAME
' COMPILE;'from dba_objects where status = 'INVALID'and object_type in ('PACKAGE','FUNCTION','PROCEDURE', 'VIEW', 'TRIGGER', 'SYNONYM');

SQL> Select 'ALTER PACKAGE' ' ' OWNER '.' OBJECT_NAME ' COMPILE BODY;' From dba_objects where status = 'INVALID'
And object_type in ('PACKAGE BODY');

SQL> Select 'ALTER MATERIALIZED VIEW' ' ' OWNER '.' OBJECT_NAME ' COMPILE;' From dba_objects where status = 'INVALID'
and object_type in ('UNDEFINED');

Thanks

Start and Stop iSQL*PLUS

The iSQL*PLUS Application Server must be running to start an iSQL*PLUS session. To start or stop the iSQL*PLUS Application Server on UNIX use the following commands,

$ isqlplusctl start

To Stop

$ isqlplusctl stop

Thanks

Database Properties

Here is a query to view important database properties like default permanent, default temporary tablespaces, NLS settings etc.

SQL> Select * From Database_Properties;

Thanks

Privilege to access all objects

Grant privilege to access all the objects from one schema to other

To grant privilege from one schema A to another Schema B

connect to schema A.

SQL> Grant Select any table to b;
SQL> Grant update any table to b;
or
SQL> Grant select any table, update any table, delete any table to b;

SQL> connect to schema B

SQL Select * from A.table_name;

there is no select any view privilege. the select any table privilege will automatically grant select privilege for views.

Note: The Grant select any table privilege is not object privilege, it is system privilege.