How to Move ASM DATABASE FILES from ONE DISKGROUP
TO ANOTHER
Steps to Move ASM DATABASE FILES from ONE DISKGROUP
TO ANOTHER
Since ASM files cannot be accessed through normal operating system interfaces, RMAN is the preferred means of copying ASM file.
The steps to moving a datafile from a diskgroup to another is as below, using RMAN.
1) Identify the datafile to be moved.
2) Identify the diskgroup on to which the datafile has to be moved.
3) Take the datafile offline.
4) Copy the datafile to new diskgroup using Either RMAN or DBMS_FILE_TRANSFER.
5) Rename the datafile to point to new location.
6) Recover the datafile.
7) Bring the datafile online.
8) Verify the new datafile locations.
9) Delete the datafile from its original location.
1) Identify the datafile to be moved.
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
+ASMDISK2/orcl/datafile/users.256.565313879 <======= Move this to ASMDISK1.
+ASMDISK1/orcl/sysaux01.dbf
+ASMDISK1/orcl/undotbs01.dbf
+ASMDISK1/orcl/system01.dbf
2) Identify the diskgroup on to which the datafile has to be moved.
SQL> SELECT GROUP_NUMBER, NAME FROM V$ASM_DISKGROUP;
GROUP_NUMBER NAME
------------ ---------
1 ASMDISK1
2 ASMDISK2
3) Take the datafile offline.
SQL> ALTER DATABASE DATAFILE '+ASMDISK2/orcl/datafile/users.256.565313879' OFFLINE;
4) Copy the datafile to new diskgroup using Either RMAN or DBMS_FILE_TRANSFER.
a) DBMS_FILE_TRANSFER package or
b) RMAN
a).Using DBMS_FILE_TRANSFER package
SQL> create or replace directory orcl1 as '+ASMDISK1/orcl/datafile';
SQL> Alter disgroup ASMDISK2 add directory '+ASMDISK2/test';
SQL> create or replace directory orcl2 as '+ASMDISK2/test';
SQL>
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'ORCL1',
source_file_name => 'users.259.565359071',
destination_directory_object => 'ORCL2',
destination_file_name => 'USERS01.DBF');
END;
Database altered.
-------------------- OR --------------------
b).Using RMAN copy the file to new diskgroup.
$ rman target /
connected to target database: ORCL (DBID=1020304050)
RMAN> COPY DATAFILE '+ASMDISK2/orcl/datafile/users.256.565313879' TO '+ASMDISK1';
Starting backup at 03-AUG-98
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+ASMDISK2/orcl/datafile/users.256.565313879
output filename=+ASMDISK1/orcl/datafile/users.259.565359071 tag=TAG19980803T12110
9 recid=2 stamp=565359071
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 03-AUG-98
5) Rename the datafile to point to new location.
If you have used DBMS_FILE_TRANSFER (method 4 a)) use the following command to rename:
SQL> ALTER DATABASE RENAME FILE '+ASMDISK2/orcl/datafile/users.256.565313879' TO
'+ASMDISK1/orcl/datafile/users.259.565359071';
Database altered.
If you have used RMAN (method 4 b) use the following option of RMAN
RMAN>
run
{
set newname for datafile '+ASMDISK2/orcl/datafile/users.256.565313879'
to '+ASMDISK1/orcl/datafile/users.259.565359071';
switch datafile all;
}
6) Recover the datafile.
SQL> RECOVER DATAFILE '+ASMDISK1/orcl/datafile/users.259.565359071';
Media recovery complete.
7) Bring the datafile online.
SQL> ALTER DATABASE DATAFILE '+ASMDISK1/orcl/datafile/users.259.565359071' ONLINE;
Database altered.
8) Verify the new datafile locations.
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
-------------------------------------------------------------------------------
+ASMDISK1/orcl/datafile/users.259.565359071
+ASMDISK1/orcl/sysaux01.dbf
+ASMDISK1/orcl/undotbs01.dbf
+ASMDISK1/orcl/system01.dbf
9) Delete the datafile from its original location.
SQL> ALTER DISKGROUP ASMDISK2 DROP FILE users.256.565313879;
or
ASMCMD> rm -rf <filename>
Note:-
====
The steps provided above assume that the database is open and in Archivelog mode.
Besides these steps are not appropriated for system or sysaux datafiles.
For System and Sysaux an approach similar to the one given below can be used:-
1. Create a Copy of datafile in target Diskgroup
RMAN> backup as copy tablespace system format '<New DG>';
RMAN> backup as copy tablespace sysaux format '<New DG>';
2. Then shutdown the database and restart to a mounted state
RMAN> shutdown immediate;
RMAN> startup mount;
3. switch the datafiles to the copy
RMAN> switch tablespace system to copy;
RMAN> switch tablespace sysaux to copy;
4. Recover the changes made to these tablespaces
RMAN> recover database;
Note:-
====
Most ASM files do not need to be manually deleted because, as Oracle managed files, they are removed automatically
when they are no longer needed.
However, if you need to drop an Oracle Managed File (OMF) manually you should use the fully qualified filename if
you reference the file. Otherwise you will get an error (e.g. ORA-15177).
ALTER DISKGROUP ASMDISK2 DROP FILE '+ASMDISK2/orcl/datafile/users.256.565313879';
Since ASM files cannot be accessed through normal operating system interfaces, RMAN is the preferred means of copying ASM file.
The steps to moving a datafile from a diskgroup to another is as below, using RMAN.
1) Identify the datafile to be moved.
2) Identify the diskgroup on to which the datafile has to be moved.
3) Take the datafile offline.
4) Copy the datafile to new diskgroup using Either RMAN or DBMS_FILE_TRANSFER.
5) Rename the datafile to point to new location.
6) Recover the datafile.
7) Bring the datafile online.
8) Verify the new datafile locations.
9) Delete the datafile from its original location.
1) Identify the datafile to be moved.
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
+ASMDISK2/orcl/datafile/users.256.565313879 <======= Move this to ASMDISK1.
+ASMDISK1/orcl/sysaux01.dbf
+ASMDISK1/orcl/undotbs01.dbf
+ASMDISK1/orcl/system01.dbf
2) Identify the diskgroup on to which the datafile has to be moved.
SQL> SELECT GROUP_NUMBER, NAME FROM V$ASM_DISKGROUP;
GROUP_NUMBER NAME
------------ ---------
1 ASMDISK1
2 ASMDISK2
3) Take the datafile offline.
SQL> ALTER DATABASE DATAFILE '+ASMDISK2/orcl/datafile/users.256.565313879' OFFLINE;
4) Copy the datafile to new diskgroup using Either RMAN or DBMS_FILE_TRANSFER.
a) DBMS_FILE_TRANSFER package or
b) RMAN
a).Using DBMS_FILE_TRANSFER package
SQL> create or replace directory orcl1 as '+ASMDISK1/orcl/datafile';
SQL> Alter disgroup ASMDISK2 add directory '+ASMDISK2/test';
SQL> create or replace directory orcl2 as '+ASMDISK2/test';
SQL>
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'ORCL1',
source_file_name => 'users.259.565359071',
destination_directory_object => 'ORCL2',
destination_file_name => 'USERS01.DBF');
END;
Database altered.
-------------------- OR --------------------
b).Using RMAN copy the file to new diskgroup.
$ rman target /
connected to target database: ORCL (DBID=1020304050)
RMAN> COPY DATAFILE '+ASMDISK2/orcl/datafile/users.256.565313879' TO '+ASMDISK1';
Starting backup at 03-AUG-98
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+ASMDISK2/orcl/datafile/users.256.565313879
output filename=+ASMDISK1/orcl/datafile/users.259.565359071 tag=TAG19980803T12110
9 recid=2 stamp=565359071
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 03-AUG-98
5) Rename the datafile to point to new location.
If you have used DBMS_FILE_TRANSFER (method 4 a)) use the following command to rename:
SQL> ALTER DATABASE RENAME FILE '+ASMDISK2/orcl/datafile/users.256.565313879' TO
'+ASMDISK1/orcl/datafile/users.259.565359071';
Database altered.
If you have used RMAN (method 4 b) use the following option of RMAN
RMAN>
run
{
set newname for datafile '+ASMDISK2/orcl/datafile/users.256.565313879'
to '+ASMDISK1/orcl/datafile/users.259.565359071';
switch datafile all;
}
6) Recover the datafile.
SQL> RECOVER DATAFILE '+ASMDISK1/orcl/datafile/users.259.565359071';
Media recovery complete.
7) Bring the datafile online.
SQL> ALTER DATABASE DATAFILE '+ASMDISK1/orcl/datafile/users.259.565359071' ONLINE;
Database altered.
8) Verify the new datafile locations.
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
-------------------------------------------------------------------------------
+ASMDISK1/orcl/datafile/users.259.565359071
+ASMDISK1/orcl/sysaux01.dbf
+ASMDISK1/orcl/undotbs01.dbf
+ASMDISK1/orcl/system01.dbf
9) Delete the datafile from its original location.
SQL> ALTER DISKGROUP ASMDISK2 DROP FILE users.256.565313879;
or
ASMCMD> rm -rf <filename>
Note:-
====
The steps provided above assume that the database is open and in Archivelog mode.
Besides these steps are not appropriated for system or sysaux datafiles.
For System and Sysaux an approach similar to the one given below can be used:-
1. Create a Copy of datafile in target Diskgroup
RMAN> backup as copy tablespace system format '<New DG>';
RMAN> backup as copy tablespace sysaux format '<New DG>';
2. Then shutdown the database and restart to a mounted state
RMAN> shutdown immediate;
RMAN> startup mount;
3. switch the datafiles to the copy
RMAN> switch tablespace system to copy;
RMAN> switch tablespace sysaux to copy;
4. Recover the changes made to these tablespaces
RMAN> recover database;
Note:-
====
Most ASM files do not need to be manually deleted because, as Oracle managed files, they are removed automatically
when they are no longer needed.
However, if you need to drop an Oracle Managed File (OMF) manually you should use the fully qualified filename if
you reference the file. Otherwise you will get an error (e.g. ORA-15177).
ALTER DISKGROUP ASMDISK2 DROP FILE '+ASMDISK2/orcl/datafile/users.256.565313879';
How to Copy files from ASM to filesystem and
filesystem to ASM
Copy files from ASM to
filesystem
---------------------------------
$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> lsdg
ASMCMD> cd FRA/TESTDB/EXPDP
ASMCMD> ls
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
ASMCMD> cp dumpfile1.dmp dumpfile1.dmp dumpfile1.dmp /oracle/backup/testdb/expdp
copying +FRA/TESTDB/EXPDP/dumpfile1.dmp -> /oracle/backup/testdb/expdp/dumpfile1.dmp
copying +FRA/TESTDB/EXPDP/dumpfile2.dmp -> /oracle/backup/testdb/expdp/dumpfile2.dmp
copying +FRA/TESTDB/EXPDP/dumpfile3.dmp -> /oracle/backup/testdb/expdp/dumpfile3.dmp
ASMCMD> exit
$cd /oracle/backup/testdb/expdp/
$ls -lrt dumpfile*.dmp
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
Copy files from filesystem to ASM
---------------------------------
$cd /oracle/backup/testdb/expdp/
$ls -lrt dumpfile*.dmp
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> lsdg
ASMCMD> cd FRA/TESTDB/IMPDP
ASMCMD> ls
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile1.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile1.dmp -> +FRA/TESTDB/EXPDP/dumpfile1.dmp
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile2.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile2.dmp -> +FRA/TESTDB/EXPDP/dumpfile2.dmp
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile3.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile3.dmp -> +FRA/TESTDB/EXPDP/dumpfile3.dmp
ASMCMD> ls
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
---------------------------------
$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> lsdg
ASMCMD> cd FRA/TESTDB/EXPDP
ASMCMD> ls
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
ASMCMD> cp dumpfile1.dmp dumpfile1.dmp dumpfile1.dmp /oracle/backup/testdb/expdp
copying +FRA/TESTDB/EXPDP/dumpfile1.dmp -> /oracle/backup/testdb/expdp/dumpfile1.dmp
copying +FRA/TESTDB/EXPDP/dumpfile2.dmp -> /oracle/backup/testdb/expdp/dumpfile2.dmp
copying +FRA/TESTDB/EXPDP/dumpfile3.dmp -> /oracle/backup/testdb/expdp/dumpfile3.dmp
ASMCMD> exit
$cd /oracle/backup/testdb/expdp/
$ls -lrt dumpfile*.dmp
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
Copy files from filesystem to ASM
---------------------------------
$cd /oracle/backup/testdb/expdp/
$ls -lrt dumpfile*.dmp
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> lsdg
ASMCMD> cd FRA/TESTDB/IMPDP
ASMCMD> ls
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile1.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile1.dmp -> +FRA/TESTDB/EXPDP/dumpfile1.dmp
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile2.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile2.dmp -> +FRA/TESTDB/EXPDP/dumpfile2.dmp
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile3.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile3.dmp -> +FRA/TESTDB/EXPDP/dumpfile3.dmp
ASMCMD> ls
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
Schema Refresh using ASM Diskgroups
How to create Datapump Export Dumps within ASM
diskgroups
Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type;
EXPORT (EXPDP)
$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> cd DATA01/TESTDB
ASMCMD> mkdir EXPDP
1. Create a directory in ASM.
SQL> create or replace directory DATAPUMP_ASM_DIR as '+DATA01/TESTDB/EXPDP';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_ASM_DIR TO SYSTEM;
2. Create a logfile directory in filesystem, since logfiles can't be stored in ASM.
SQL> create or replace directory DATAPUMP_LOG_DIR as '/oracle/backup/testdb/expdp';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_LOG_DIR TO SYSTEM;
3. Create logfile directory in filesystem.
$ cd /oracle/backup/testdb/
$ mkdir expdp
$ chmod -R 755 expdp
$ cd expdp
$pwd
/oracle/backup/testdb/expdp
$ df -h /oracle/backup/testdb/expdp -- or
$df -gt /oracle/backup/testdb/expdp
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
col DIRECTORY_PATH for a80;
select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------- ---------------- ---------------------------------------
SYS DATAPUMP_ASM_DIR +DATA01/testdb/expdp
SYS DATAPUMP_LOG_DIR /oracle/backup/testdb/expdp
Where DATAPUMP_ASM_DIR is a ASM based directory
and DATAPUMP_LOG_DIR is on a filesystem.
Here, Export is written to ASM based dictionary,
but log is written to another directory which is on filesystem.
We can create export dumps to the ASM diskgroups. But we can’t write expdp log to ASM (as it is a text file).
$vi schema_export_in_ASM.par
user_id="/ as sysdba"
directory=DATAPUMP_ASM_DIR
dumpfile=source_schemaname_expdp_%U.dmp
logfile=DATAPUMP_LOG_DIR:schema_export_in_ASM.log
schemas=schema1,schema2
exclude=statistics
parallel=6
compression=all
cluster=n ---- 11g
:wq
$nohup expdp parfile=schema_export_in_ASM.par &
$tail -f nohup.out or
$tail -f schema_export_in_ASM.log
=======================================================================
IMPORT (IMPDP)
Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type;
$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> cd DATA01/TESTDB
ASMCMD> mkdir IMPDP
1. Create a directory in ASM.
SQL> create or replace directory DATAPUMP_ASM_DIR as '+DATA01/TESTDB/IMPDP';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_ASM_DIR TO SYSTEM;
2. Create a logfile directory in filesystem, since logfiles can't be stored in ASM.
SQL> create or replace directory DATAPUMP_LOG_DIR as '/oracle/backup/testdb/impdp';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_LOG_DIR TO SYSTEM;
3. Create logfile directory in filesystem.
$ cd /oracle/backup/testdb/
$ mkdir impdp
$ chmod -R 755 impdp
$ cd impdp
$pwd
/oracle/backup/testdb/impdp
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
col DIRECTORY_PATH for a80;
select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------- ---------------- ---------------------------------------
SYS DATAPUMP_ASM_DIR +DATA01/testdb/impdp
SYS DATAPUMP_LOG_DIR /oracle/backup/testdb/impdp
Where DATAPUMP_ASM_DIR is a ASM based directory
and DATAPUMP_LOG_DIR is on a filesystem.
Here, Export is written to ASM based dictionary,
but log is written to another directory which is on filesystem.
We can create export dumps to the ASM diskgroups. But we can’t write expdp log to ASM (as it is a text file).
$vi import_schema_export_in_ASM.par
user_id="/ as sysdba"
directory=DATAPUMP_ASM_DIR
dumpfile=source_schemaname_expdp_%U.dmp
logfile=DATAPUMP_LOG_DIR:import_schema_export_in_ASM.log
schemas=schema1,schema2
parallel=6
cluster=n ---- 11g
If required,
REMAP_SCHEMA=source_schema1:destination_schema1,source_schema2:destination_schema2
REMAP_TABLESPACE=source_tablespace1:destination_tablespace1,source_tablespace2:destination_tablespace2
:wq
$nohup impdp parfile=import_schema_export_in_ASM.par &
$tail -f nohup.out
$tail -f import_schema_export_in_ASM.log
Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type;
Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type;
EXPORT (EXPDP)
$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> cd DATA01/TESTDB
ASMCMD> mkdir EXPDP
1. Create a directory in ASM.
SQL> create or replace directory DATAPUMP_ASM_DIR as '+DATA01/TESTDB/EXPDP';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_ASM_DIR TO SYSTEM;
2. Create a logfile directory in filesystem, since logfiles can't be stored in ASM.
SQL> create or replace directory DATAPUMP_LOG_DIR as '/oracle/backup/testdb/expdp';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_LOG_DIR TO SYSTEM;
3. Create logfile directory in filesystem.
$ cd /oracle/backup/testdb/
$ mkdir expdp
$ chmod -R 755 expdp
$ cd expdp
$pwd
/oracle/backup/testdb/expdp
$ df -h /oracle/backup/testdb/expdp -- or
$df -gt /oracle/backup/testdb/expdp
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
col DIRECTORY_PATH for a80;
select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------- ---------------- ---------------------------------------
SYS DATAPUMP_ASM_DIR +DATA01/testdb/expdp
SYS DATAPUMP_LOG_DIR /oracle/backup/testdb/expdp
Where DATAPUMP_ASM_DIR is a ASM based directory
and DATAPUMP_LOG_DIR is on a filesystem.
Here, Export is written to ASM based dictionary,
but log is written to another directory which is on filesystem.
We can create export dumps to the ASM diskgroups. But we can’t write expdp log to ASM (as it is a text file).
$vi schema_export_in_ASM.par
user_id="/ as sysdba"
directory=DATAPUMP_ASM_DIR
dumpfile=source_schemaname_expdp_%U.dmp
logfile=DATAPUMP_LOG_DIR:schema_export_in_ASM.log
schemas=schema1,schema2
exclude=statistics
parallel=6
compression=all
cluster=n ---- 11g
:wq
$nohup expdp parfile=schema_export_in_ASM.par &
$tail -f nohup.out or
$tail -f schema_export_in_ASM.log
=======================================================================
IMPORT (IMPDP)
Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type;
$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> cd DATA01/TESTDB
ASMCMD> mkdir IMPDP
1. Create a directory in ASM.
SQL> create or replace directory DATAPUMP_ASM_DIR as '+DATA01/TESTDB/IMPDP';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_ASM_DIR TO SYSTEM;
2. Create a logfile directory in filesystem, since logfiles can't be stored in ASM.
SQL> create or replace directory DATAPUMP_LOG_DIR as '/oracle/backup/testdb/impdp';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_LOG_DIR TO SYSTEM;
3. Create logfile directory in filesystem.
$ cd /oracle/backup/testdb/
$ mkdir impdp
$ chmod -R 755 impdp
$ cd impdp
$pwd
/oracle/backup/testdb/impdp
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
col DIRECTORY_PATH for a80;
select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------- ---------------- ---------------------------------------
SYS DATAPUMP_ASM_DIR +DATA01/testdb/impdp
SYS DATAPUMP_LOG_DIR /oracle/backup/testdb/impdp
Where DATAPUMP_ASM_DIR is a ASM based directory
and DATAPUMP_LOG_DIR is on a filesystem.
Here, Export is written to ASM based dictionary,
but log is written to another directory which is on filesystem.
We can create export dumps to the ASM diskgroups. But we can’t write expdp log to ASM (as it is a text file).
$vi import_schema_export_in_ASM.par
user_id="/ as sysdba"
directory=DATAPUMP_ASM_DIR
dumpfile=source_schemaname_expdp_%U.dmp
logfile=DATAPUMP_LOG_DIR:import_schema_export_in_ASM.log
schemas=schema1,schema2
parallel=6
cluster=n ---- 11g
If required,
REMAP_SCHEMA=source_schema1:destination_schema1,source_schema2:destination_schema2
REMAP_TABLESPACE=source_tablespace1:destination_tablespace1,source_tablespace2:destination_tablespace2
:wq
$nohup impdp parfile=import_schema_export_in_ASM.par &
$tail -f nohup.out
$tail -f import_schema_export_in_ASM.log
Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type;
Oracle ASM Interview Questions and
Answers
What is ASM in Oracle?
Oracle ASM is Oracle’s volume manager specially designed for Oracle database data. It is available since Oracle database version 10g and many improvements have been made in versions 11g release 1 and 2.
ASM offers support for Oracle RAC clusters without the requirement to install 3rd party software, such as cluster aware volume managers or filesystems.
ASM is shipped as part of the database server software (Enterprise and Standard editions) and does not cost extra money to run.
ASM simplifies administration of Oracle related files by allowing the administrator to reference disk groups
rather than individual disks and files, which are managed by ASM.
The ASM functionality is an extention of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to provide balanced and secure storage. The new ASM functionality can be used in combination with existing raw and cooked file systems, along with OMF and manually managed files.
Advantages of ASM in Oracle?
Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system
Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance
Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage
Uses redundancy features available in intelligent storage arrays
The storage system can store all types of database files
Using disk group makes configuration easier, as files are placed into disk groups
ASM provides stripping and mirroring (fine and coarse gain - see below)
ASM and non-ASM oracle files can coexist
Striping—ASM spreads data evenly across all disks in a disk group to optimize performance and utilization. This even distribution of database files eliminates the need for regular monitoring and I/O performance tuning.
For example, if there are six disks in a disk group, pieces of each ASM file are written to all six disks. These pieces come in 1 MB chunks known as extents. When a database file is created, it is striped (divided into extents and distributed) across the six disks, and allocated disk space on all six disks grows evenly. When reading the file, file extents are read from all six disks in parallel, greatly increasing performance.
Mirroring—ASM can increase availability by optionally mirroring any file. ASM mirrors at the file level, unlike operating system mirroring, which mirrors at the disk level. Mirroring means keeping redundant copies, or mirrored copies, of each extent of the file, to help avoid data loss caused by disk failures. The mirrored copy of each file extent is always kept on a different disk from the original copy. If a disk fails, ASM can continue to access affected files by accessing mirrored copies on the surviving disks in the disk group.
ASM supports 2-way mirroring, where each file extent gets one mirrored copy, and 3-way mirroring, where each file extent gets two mirrored copies.
Online storage reconfiguration and dynamic rebalancing—ASM permits you to add or remove disks from your disk storage system while the database is operating. When you add a disk, ASM automatically redistributes the data so that it is evenly spread across all disks in the disk group, including the new disk. This redistribution is known as rebalancing. It is done in the background and with minimal impact to database performance. When you request to remove a disk, ASM first rebalances by evenly relocating all file extents from the disk being removed to the other disks in the disk group.
Managed file creation and deletion—ASM further reduces administration tasks by enabling files stored in ASM disk groups to be Oracle-managed files. ASM automatically assigns filenames when files are created, and automatically deletes files when they are no longer needed.
What is ASM instance in Oracle?
The ASM functionality is controlled by an ASM instance. This is not a full database instance, just the memory structures and as such is very small and lightweight.
Characteristics of Oracle ASM instance
--------------------------------------
1. do not have controlfile and datafiles, do not have online redo logs
2. do have init.ora and a passwordfile
3. for connecting remotely, create passwordfile and set following in init.ora
remote_login_passwordfile=exclusive
create a password file:
$ORACLE_HOME/bin/orapwd file=orapw+ASM1 password=yourpw entries=10
4. ASM instance can not be in open status as there are not datafiles. Can be in mount (although
there is no controlfile) and nomount status. When in mount status, database can use the
diskgroup. The mount status actually means mount disk groups.
What are ASM Background Processes in Oracle?Both an Oracle ASM instance and an Oracle Database instance are built on the same technology. Like a database instance, an Oracle ASM instance has memory structures (System Global Area) and background processes. Besides, Oracle ASM has a minimal performance impact on a server. Rather than mounting a database, Oracle ASM instances mount disk groups to make Oracle ASM files available to database instances.
There are at least two new background processes added for an ASM instance:
ASM Instance Background Processes:
---------------------------------
ARBx (ASM) Rebalance working processARBn performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these processes running at a time, named ARB0, ARB1, and so on.These processes are managed by the RBAL process. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.
RBAL (Re-balancer) RBAL runs in both database and ASM instances. In the database instance, it does a global open of ASM disks. In an ASM instance, it also coordinates rebalance activity for disk groups.RBAL, which coordinates rebalance activities
for disk resources controlled by ASM.
Database Instance ASM Background Processes:
------------------------------------------In the database instances, there are three background process to support ASM, namely:
ASMB, this process contact CSS using the group name and acquires the associated ASM connect string. The connect string is subsequently used to connect to the ASM instance.
RBAL, which performs global opens on all disks in the disk group.A global open means that more than one database instance can be accessing the ASM disks at a time.
O00x, a group slave processes, with a numeric sequence starting at 000.
What are the components of components of ASM are disk groups?
The main components of ASM are disk groups, each of which comprise of several physical disks that are controlled as a single unit. The physical disks are known as ASM disks, while the files that reside on the disks are know as ASM files. The locations and names for the files are controlled by ASM, but user-friendly aliases and directory structures can be defined for ease of reference.
Failure groups are defined within a disk group to support the required level of redundancy. For two-way mirroring you would expect a disk group to contain two failure groups so individual files are written to two locations.
What are ASM instance initialization parameters?
INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS.
DB_UNIQUE_NAME - Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.
ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.
ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.
ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.
Advantages of ASM in Oracle?
Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system
Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance
Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage
Uses redundancy features available in intelligent storage arrays
The storage system can store all types of database files
Using disk group makes configuration easier, as files are placed into disk groups
ASM provides stripping and mirroring (fine and coarse gain - see below)
ASM and non-ASM oracle files can coexist
Striping—ASM spreads data evenly across all disks in a disk group to optimize performance and utilization. This even distribution of database files eliminates the need for regular monitoring and I/O performance tuning.
For example, if there are six disks in a disk group, pieces of each ASM file are written to all six disks. These pieces come in 1 MB chunks known as extents. When a database file is created, it is striped (divided into extents and distributed) across the six disks, and allocated disk space on all six disks grows evenly. When reading the file, file extents are read from all six disks in parallel, greatly increasing performance.
Mirroring—ASM can increase availability by optionally mirroring any file. ASM mirrors at the file level, unlike operating system mirroring, which mirrors at the disk level. Mirroring means keeping redundant copies, or mirrored copies, of each extent of the file, to help avoid data loss caused by disk failures. The mirrored copy of each file extent is always kept on a different disk from the original copy. If a disk fails, ASM can continue to access affected files by accessing mirrored copies on the surviving disks in the disk group.
ASM supports 2-way mirroring, where each file extent gets one mirrored copy, and 3-way mirroring, where each file extent gets two mirrored copies.
Online storage reconfiguration and dynamic rebalancing—ASM permits you to add or remove disks from your disk storage system while the database is operating. When you add a disk, ASM automatically redistributes the data so that it is evenly spread across all disks in the disk group, including the new disk. This redistribution is known as rebalancing. It is done in the background and with minimal impact to database performance. When you request to remove a disk, ASM first rebalances by evenly relocating all file extents from the disk being removed to the other disks in the disk group.
Managed file creation and deletion—ASM further reduces administration tasks by enabling files stored in ASM disk groups to be Oracle-managed files. ASM automatically assigns filenames when files are created, and automatically deletes files when they are no longer needed.
Why should we use separate ASM home?
ASM should be installed separately from the database software in its own ORACLE_HOME directory. This will allow you the flexibility to patch and upgrade ASM and the database software independently.
How many ASM instances should one have?
Several databases can share a single ASM instance. So, although one can create multiple ASM instances on a single system, normal configurations should have one and only one ASM instance per system.
For clustered systems, create one ASM instance per node (called +ASM1, +ASM2, etc).
How many diskgroups should one have?
Generally speaking one should have only one disk group for all database files - and, optionally a second for recovery files (see FRA).
Data with different storage characteristics should be stored in different disk groups. Each disk group can have different redundancy (mirroring) settings (high, normal and external), different fail-groups, etc. However, it is generally not necessary to create many disk groups with the same storage characteristics (i.e. +DATA1, +DATA2, etc. all on the same type of disks).
To get started, create 2 disk groups - one for data and one for recovery files. Here is an example:
CREATE DISKGROUP data EXTERNAL REDUNDANCY DISK '/dev/d1', '/dev/d2', '/dev/d3', ....;
CREATE DISKGROUP recover EXTERNAL REDUNDANCY DISK '/dev/d10', '/dev/d11', '/dev/d12', ....;
Here is an example how you can enable automatic file management with such a setup:
ALTER SYSTEM SET db_create_file_dest = '+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET db_recovery_file_dest = '+RECOVER' SCOPE=SPFILE;
You may also decide to introduce additional disk groups - for example, if you decide to put historic data on low cost disks, or if you want ASM to mirror critical data across 2 storage cabinets.
What is ASM Rebalancing?
The rebalancing speed is controlled by the ASM_POWER_LIMIT initialization parameter. Setting it to 0 will disable disk rebalancing.
ALTER DISKGROUP data REBALANCE POWER 11;
What happens when an Oracle ASM diskgroup is created?
When an ASM diskgroup is created, a hierarchialfilesystem structure is created.
How does this filesystem structure appear?
Oracle ASM diskgroup'sfilesystem structure is similar to UNIX filesystem hierarchy or Windows filesystem hierarchy.
Where are the Oracle ASM files stored?
Oracle ASM files are stored within the Oracle ASM diskgroup. If we dig into internals, oracle ASM files are stored within the Oracle ASM filesystem structures.
How are the Oracle ASM files stored within the Oracle ASM filesystem structure?
Oralce ASM files are stored within the Oracle ASM filesystem structures as objects that RDBMS instances/Oracle database instance access. RDBMS/Oracle instance treats the Oracle ASM files as standard filesystem files.
What are the Oracle ASM files that are stored within the Oracle ASM file hierarchy?
Files stored in Oracle ASM diskgroup/Oracl ASM filestructures include:
1) Datafile
2) Controlfiles
3) Server Parameter Files(SPFILE)
4) Redo Log files
What happens when you create a file/database file in ASM?What commands do you use to create database files?
Some common commands used for creating database files are :
1) Create tabespace
2) Add Datafile
3) Add Logfile
For example,
SQL> CREATE TABLESPACE TS1 DATAFILE '+DATA1' SIZE 10GB;
Above command creates a datafile in DATA1 diskgroup
How can you access a databasefile in ASM diskgroup under RDBMS?
Once the ASM file is created in ASM diskgroup, a filename is generated. This file is now visible to the user via the standard RDBMS view V$DATAFILE.
What will be the syntax of ASM filenames?
ASM filename syntax is as follows:
+diskgroup_name/database_name/database_file_type/tag_name.file_number.incarnation
where,
+diskgroup_name - Name of the diskgroup that contains this file
database_name - Name of the database that contains this file
datafile - Can be one among 20 different ASM file types
tag_name - corresponds to tablespace name for datafiles, groupnumber for redo log files
file_number - file_number in ASM instance is used to correlate filenames in database instance
incarnation_number - It is derived from the timestamp. IT is used to provide uniqueness
What is an incarnation number?
An incarnation number is a part of ASM filename syntax. It is derived from the timestamp. Once the file is created, its incarnation number doesnot change.
What is the use of an incarnation number in Oracle ASM filename?
Incarnation number distinguishes between a new file that has been created using the same file number and another file that has been deleted
ASM's SPFile will be residing inside ASM itself. This could be found out in number of ways, looking at the alert log of ASM when ASM starts
Machine: x86_64
Using parameter settings in server-side spfile +DATA/asm/asmparameterfile/registry.253.766260991
System parameters with non-default values:
large_pool_size = 12M
instance_type = "asm"
remote_login_passwordfile= "EXCLUSIVE"
asm_diskgroups = "FLASH"
asm_diskgroups = "DATA"
asm_power_limit = 1
diagnostic_dest = "/opt/app/oracle"
Or using the asmcmd's spget command which shows the spfile location registered with GnP profile
ASMCMD> spget
+DATA/asm/asmparameterfile/registry.253.766260991
What is ASM in Oracle?
Oracle ASM is Oracle’s volume manager specially designed for Oracle database data. It is available since Oracle database version 10g and many improvements have been made in versions 11g release 1 and 2.
ASM offers support for Oracle RAC clusters without the requirement to install 3rd party software, such as cluster aware volume managers or filesystems.
ASM is shipped as part of the database server software (Enterprise and Standard editions) and does not cost extra money to run.
ASM simplifies administration of Oracle related files by allowing the administrator to reference disk groups
rather than individual disks and files, which are managed by ASM.
The ASM functionality is an extention of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to provide balanced and secure storage. The new ASM functionality can be used in combination with existing raw and cooked file systems, along with OMF and manually managed files.
Advantages of ASM in Oracle?
Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system
Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance
Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage
Uses redundancy features available in intelligent storage arrays
The storage system can store all types of database files
Using disk group makes configuration easier, as files are placed into disk groups
ASM provides stripping and mirroring (fine and coarse gain - see below)
ASM and non-ASM oracle files can coexist
Striping—ASM spreads data evenly across all disks in a disk group to optimize performance and utilization. This even distribution of database files eliminates the need for regular monitoring and I/O performance tuning.
For example, if there are six disks in a disk group, pieces of each ASM file are written to all six disks. These pieces come in 1 MB chunks known as extents. When a database file is created, it is striped (divided into extents and distributed) across the six disks, and allocated disk space on all six disks grows evenly. When reading the file, file extents are read from all six disks in parallel, greatly increasing performance.
Mirroring—ASM can increase availability by optionally mirroring any file. ASM mirrors at the file level, unlike operating system mirroring, which mirrors at the disk level. Mirroring means keeping redundant copies, or mirrored copies, of each extent of the file, to help avoid data loss caused by disk failures. The mirrored copy of each file extent is always kept on a different disk from the original copy. If a disk fails, ASM can continue to access affected files by accessing mirrored copies on the surviving disks in the disk group.
ASM supports 2-way mirroring, where each file extent gets one mirrored copy, and 3-way mirroring, where each file extent gets two mirrored copies.
Online storage reconfiguration and dynamic rebalancing—ASM permits you to add or remove disks from your disk storage system while the database is operating. When you add a disk, ASM automatically redistributes the data so that it is evenly spread across all disks in the disk group, including the new disk. This redistribution is known as rebalancing. It is done in the background and with minimal impact to database performance. When you request to remove a disk, ASM first rebalances by evenly relocating all file extents from the disk being removed to the other disks in the disk group.
Managed file creation and deletion—ASM further reduces administration tasks by enabling files stored in ASM disk groups to be Oracle-managed files. ASM automatically assigns filenames when files are created, and automatically deletes files when they are no longer needed.
What is ASM instance in Oracle?
The ASM functionality is controlled by an ASM instance. This is not a full database instance, just the memory structures and as such is very small and lightweight.
Characteristics of Oracle ASM instance
--------------------------------------
1. do not have controlfile and datafiles, do not have online redo logs
2. do have init.ora and a passwordfile
3. for connecting remotely, create passwordfile and set following in init.ora
remote_login_passwordfile=exclusive
create a password file:
$ORACLE_HOME/bin/orapwd file=orapw+ASM1 password=yourpw entries=10
4. ASM instance can not be in open status as there are not datafiles. Can be in mount (although
there is no controlfile) and nomount status. When in mount status, database can use the
diskgroup. The mount status actually means mount disk groups.
What are ASM Background Processes in Oracle?Both an Oracle ASM instance and an Oracle Database instance are built on the same technology. Like a database instance, an Oracle ASM instance has memory structures (System Global Area) and background processes. Besides, Oracle ASM has a minimal performance impact on a server. Rather than mounting a database, Oracle ASM instances mount disk groups to make Oracle ASM files available to database instances.
There are at least two new background processes added for an ASM instance:
ASM Instance Background Processes:
---------------------------------
ARBx (ASM) Rebalance working processARBn performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these processes running at a time, named ARB0, ARB1, and so on.These processes are managed by the RBAL process. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.
RBAL (Re-balancer) RBAL runs in both database and ASM instances. In the database instance, it does a global open of ASM disks. In an ASM instance, it also coordinates rebalance activity for disk groups.RBAL, which coordinates rebalance activities
for disk resources controlled by ASM.
Database Instance ASM Background Processes:
------------------------------------------In the database instances, there are three background process to support ASM, namely:
ASMB, this process contact CSS using the group name and acquires the associated ASM connect string. The connect string is subsequently used to connect to the ASM instance.
RBAL, which performs global opens on all disks in the disk group.A global open means that more than one database instance can be accessing the ASM disks at a time.
O00x, a group slave processes, with a numeric sequence starting at 000.
What are the components of components of ASM are disk groups?
The main components of ASM are disk groups, each of which comprise of several physical disks that are controlled as a single unit. The physical disks are known as ASM disks, while the files that reside on the disks are know as ASM files. The locations and names for the files are controlled by ASM, but user-friendly aliases and directory structures can be defined for ease of reference.
Failure groups are defined within a disk group to support the required level of redundancy. For two-way mirroring you would expect a disk group to contain two failure groups so individual files are written to two locations.
What are ASM instance initialization parameters?
INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS.
DB_UNIQUE_NAME - Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.
ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.
ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.
ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.
Advantages of ASM in Oracle?
Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system
Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance
Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage
Uses redundancy features available in intelligent storage arrays
The storage system can store all types of database files
Using disk group makes configuration easier, as files are placed into disk groups
ASM provides stripping and mirroring (fine and coarse gain - see below)
ASM and non-ASM oracle files can coexist
Striping—ASM spreads data evenly across all disks in a disk group to optimize performance and utilization. This even distribution of database files eliminates the need for regular monitoring and I/O performance tuning.
For example, if there are six disks in a disk group, pieces of each ASM file are written to all six disks. These pieces come in 1 MB chunks known as extents. When a database file is created, it is striped (divided into extents and distributed) across the six disks, and allocated disk space on all six disks grows evenly. When reading the file, file extents are read from all six disks in parallel, greatly increasing performance.
Mirroring—ASM can increase availability by optionally mirroring any file. ASM mirrors at the file level, unlike operating system mirroring, which mirrors at the disk level. Mirroring means keeping redundant copies, or mirrored copies, of each extent of the file, to help avoid data loss caused by disk failures. The mirrored copy of each file extent is always kept on a different disk from the original copy. If a disk fails, ASM can continue to access affected files by accessing mirrored copies on the surviving disks in the disk group.
ASM supports 2-way mirroring, where each file extent gets one mirrored copy, and 3-way mirroring, where each file extent gets two mirrored copies.
Online storage reconfiguration and dynamic rebalancing—ASM permits you to add or remove disks from your disk storage system while the database is operating. When you add a disk, ASM automatically redistributes the data so that it is evenly spread across all disks in the disk group, including the new disk. This redistribution is known as rebalancing. It is done in the background and with minimal impact to database performance. When you request to remove a disk, ASM first rebalances by evenly relocating all file extents from the disk being removed to the other disks in the disk group.
Managed file creation and deletion—ASM further reduces administration tasks by enabling files stored in ASM disk groups to be Oracle-managed files. ASM automatically assigns filenames when files are created, and automatically deletes files when they are no longer needed.
Why should we use separate ASM home?
ASM should be installed separately from the database software in its own ORACLE_HOME directory. This will allow you the flexibility to patch and upgrade ASM and the database software independently.
How many ASM instances should one have?
Several databases can share a single ASM instance. So, although one can create multiple ASM instances on a single system, normal configurations should have one and only one ASM instance per system.
For clustered systems, create one ASM instance per node (called +ASM1, +ASM2, etc).
How many diskgroups should one have?
Generally speaking one should have only one disk group for all database files - and, optionally a second for recovery files (see FRA).
Data with different storage characteristics should be stored in different disk groups. Each disk group can have different redundancy (mirroring) settings (high, normal and external), different fail-groups, etc. However, it is generally not necessary to create many disk groups with the same storage characteristics (i.e. +DATA1, +DATA2, etc. all on the same type of disks).
To get started, create 2 disk groups - one for data and one for recovery files. Here is an example:
CREATE DISKGROUP data EXTERNAL REDUNDANCY DISK '/dev/d1', '/dev/d2', '/dev/d3', ....;
CREATE DISKGROUP recover EXTERNAL REDUNDANCY DISK '/dev/d10', '/dev/d11', '/dev/d12', ....;
Here is an example how you can enable automatic file management with such a setup:
ALTER SYSTEM SET db_create_file_dest = '+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET db_recovery_file_dest = '+RECOVER' SCOPE=SPFILE;
You may also decide to introduce additional disk groups - for example, if you decide to put historic data on low cost disks, or if you want ASM to mirror critical data across 2 storage cabinets.
What is ASM Rebalancing?
The rebalancing speed is controlled by the ASM_POWER_LIMIT initialization parameter. Setting it to 0 will disable disk rebalancing.
ALTER DISKGROUP data REBALANCE POWER 11;
What happens when an Oracle ASM diskgroup is created?
When an ASM diskgroup is created, a hierarchialfilesystem structure is created.
How does this filesystem structure appear?
Oracle ASM diskgroup'sfilesystem structure is similar to UNIX filesystem hierarchy or Windows filesystem hierarchy.
Where are the Oracle ASM files stored?
Oracle ASM files are stored within the Oracle ASM diskgroup. If we dig into internals, oracle ASM files are stored within the Oracle ASM filesystem structures.
How are the Oracle ASM files stored within the Oracle ASM filesystem structure?
Oralce ASM files are stored within the Oracle ASM filesystem structures as objects that RDBMS instances/Oracle database instance access. RDBMS/Oracle instance treats the Oracle ASM files as standard filesystem files.
What are the Oracle ASM files that are stored within the Oracle ASM file hierarchy?
Files stored in Oracle ASM diskgroup/Oracl ASM filestructures include:
1) Datafile
2) Controlfiles
3) Server Parameter Files(SPFILE)
4) Redo Log files
What happens when you create a file/database file in ASM?What commands do you use to create database files?
Some common commands used for creating database files are :
1) Create tabespace
2) Add Datafile
3) Add Logfile
For example,
SQL> CREATE TABLESPACE TS1 DATAFILE '+DATA1' SIZE 10GB;
Above command creates a datafile in DATA1 diskgroup
How can you access a databasefile in ASM diskgroup under RDBMS?
Once the ASM file is created in ASM diskgroup, a filename is generated. This file is now visible to the user via the standard RDBMS view V$DATAFILE.
What will be the syntax of ASM filenames?
ASM filename syntax is as follows:
+diskgroup_name/database_name/database_file_type/tag_name.file_number.incarnation
where,
+diskgroup_name - Name of the diskgroup that contains this file
database_name - Name of the database that contains this file
datafile - Can be one among 20 different ASM file types
tag_name - corresponds to tablespace name for datafiles, groupnumber for redo log files
file_number - file_number in ASM instance is used to correlate filenames in database instance
incarnation_number - It is derived from the timestamp. IT is used to provide uniqueness
What is an incarnation number?
An incarnation number is a part of ASM filename syntax. It is derived from the timestamp. Once the file is created, its incarnation number doesnot change.
What is the use of an incarnation number in Oracle ASM filename?
Incarnation number distinguishes between a new file that has been created using the same file number and another file that has been deleted
ASM's SPFile will be residing inside ASM itself. This could be found out in number of ways, looking at the alert log of ASM when ASM starts
Machine: x86_64
Using parameter settings in server-side spfile +DATA/asm/asmparameterfile/registry.253.766260991
System parameters with non-default values:
large_pool_size = 12M
instance_type = "asm"
remote_login_passwordfile= "EXCLUSIVE"
asm_diskgroups = "FLASH"
asm_diskgroups = "DATA"
asm_power_limit = 1
diagnostic_dest = "/opt/app/oracle"
Or using the asmcmd's spget command which shows the spfile location registered with GnP profile
ASMCMD> spget
+DATA/asm/asmparameterfile/registry.253.766260991
oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon
Set the environment for the database
------------------------------------
uname
cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab
export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin
Database Details
----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME,INSTANCE_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
ASM Diskgroup Utilization Script
--------------------------------
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 500
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name FORMAT a12 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a45 HEAD 'Path'
COLUMN disk_file_name FORMAT a12 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a12 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL "" OF total_mb used_mb ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a ,v$asm_disk b
where a.group_number (+) =b.group_number
ORDER BY a.name
/
To find the free space in an ASM disk:
--------------------------------------
set pages 9999 lines 900
select group_number, disk_number, name, failgroup, create_date, path, total_mb,free_mb from v$asm_disk;
To find the free space in an ASM diskgroup:
-------------------------------------------
set pages 9999 lines 900
select name, group_number, name, type, state, total_mb, free_mb from v$asm_diskgroup;
To see the current ASM operations in Progress:
----------------------------------------------
set pages 9999 lines 900
select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from v$asm_operation;
ASM Diskgroup Information
-----------------------------
set pages 9999 lines 900
set head off
select 'Diskgroup Information' from dual;
set head on
column name format a15
column DG# format 99
select group_number DG#, name, state, type, total_mb, free_mb, round(free_mb/total_mb*100,2) pct_free from
v$asm_diskgroup;
Monitor space used in ASM Disk Groups
-------------------------------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/
Disk Group Name File Name File Size (MB) Used Size (MB) Free Size (MB) Pct. Used
-------------------- -------------------- -------------------- -------------- -------------- -------
Space used by database files.No other files found for cleanup.
There are 2 undo tablespaces for this database. Reclaimed some freespace from non-default undo tablespace
(i.e:"UNDOTBS1".)
Current status:
---------------
ORADATA diskgroup having xxxgb of freespace out of yyyygb.
http://dbasanthosh.wordpress.com/2012/05/
-----------------------------------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a20 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label “” of total_mb used_mb free_mb on disk_group_name
compute sum label “Grand Total: ” of total_mb used_mb free_mb on report
SELECT NVL(a.name, '[CANDIDATE]') disk_group_name,b.path disk_file_path,b.name disk_file_name,
b.total_mb total_mb,(b.total_mb – b.free_mb) used_mb,b.free_mb free_mb,
ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b
USING (group_number) ORDER BY a.nameUSING (group_number) ORDER BY a.name
V$ASM_DISK Header_Status:
------------------------
UNKNOWN - Automatic Storage Management disk header has not been read
CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement
INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software version.
PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP
statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.
MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk
group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option
FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.
CONFLICT - Automatic Storage Management disk was not mounted due to a conflict
FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and OCR disks.
---------------------------------------------
ps -ef | grep pmon
Set the environment for the database
------------------------------------
uname
cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab
export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin
Database Details
----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME,INSTANCE_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
ASM Diskgroup Utilization Script
--------------------------------
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 500
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name FORMAT a12 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a45 HEAD 'Path'
COLUMN disk_file_name FORMAT a12 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a12 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL "" OF total_mb used_mb ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a ,v$asm_disk b
where a.group_number (+) =b.group_number
ORDER BY a.name
/
To find the free space in an ASM disk:
--------------------------------------
set pages 9999 lines 900
select group_number, disk_number, name, failgroup, create_date, path, total_mb,free_mb from v$asm_disk;
To find the free space in an ASM diskgroup:
-------------------------------------------
set pages 9999 lines 900
select name, group_number, name, type, state, total_mb, free_mb from v$asm_diskgroup;
To see the current ASM operations in Progress:
----------------------------------------------
set pages 9999 lines 900
select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from v$asm_operation;
ASM Diskgroup Information
-----------------------------
set pages 9999 lines 900
set head off
select 'Diskgroup Information' from dual;
set head on
column name format a15
column DG# format 99
select group_number DG#, name, state, type, total_mb, free_mb, round(free_mb/total_mb*100,2) pct_free from
v$asm_diskgroup;
Monitor space used in ASM Disk Groups
-------------------------------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/
Disk Group Name File Name File Size (MB) Used Size (MB) Free Size (MB) Pct. Used
-------------------- -------------------- -------------------- -------------- -------------- -------
Space used by database files.No other files found for cleanup.
There are 2 undo tablespaces for this database. Reclaimed some freespace from non-default undo tablespace
(i.e:"UNDOTBS1".)
Current status:
---------------
ORADATA diskgroup having xxxgb of freespace out of yyyygb.
http://dbasanthosh.wordpress.com/2012/05/
-----------------------------------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a20 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label “” of total_mb used_mb free_mb on disk_group_name
compute sum label “Grand Total: ” of total_mb used_mb free_mb on report
SELECT NVL(a.name, '[CANDIDATE]') disk_group_name,b.path disk_file_path,b.name disk_file_name,
b.total_mb total_mb,(b.total_mb – b.free_mb) used_mb,b.free_mb free_mb,
ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b
USING (group_number) ORDER BY a.nameUSING (group_number) ORDER BY a.name
V$ASM_DISK Header_Status:
------------------------
UNKNOWN - Automatic Storage Management disk header has not been read
CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement
INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software version.
PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP
statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.
MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk
group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option
FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.
CONFLICT - Automatic Storage Management disk was not mounted due to a conflict
FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and OCR disks.
ASM Tablespace Utilization Scripts
http://files.meetup.com/1729503/1729503/All_about_ASM.pdf
ps -ef | grep pmon
ps -ef | grep tns
ps -ef | grep d.bin
Set the environment for the database
------------------------------------
uname
cat /etc/oratab OR cat /var/opt/oracle/oratab
export ORACLE_SID=<DBNAME>
export ORACLE_HOME=<>
export PATH=$PATH:$ORACLE_HOME/bin
To Check Database
-----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a10
select name DB_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,version DB_VERSION,DATABASE_STATUS,DATABASE_ROLE,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
ASM Space Report
----------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (GB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (GB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (GB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
SELECT
distinct name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, round(total_mb/1024) "total_gb"
, round(free_mb/1024) "free_gb"
, round((total_mb - free_mb) / 1024) "used_gb"
, round((1- (free_mb / total_mb))*100, 2) "pct_used"
from v$asm_diskgroup ORDER BY name
/
ASM Disk Space Usage Report In
Detail
-------------------------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a50 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label 'Disk Group' of total_mb used_mb on disk_group_name
compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status = 'MEMBER'
ORDER BY a.name
/
OTHER ASM QUERIES
-----------------
set pages 9999 lines 900
col path for a50
select path,label, group_number, disk_number, NAME,TYPE, state, header_status,mount_status, mount_date,create_date, round(TOTAL_MB/1024) "TOTAL_GB", round(FREE_MB/1024) "FREE_GB",round((TOTAL_MB-FREE_MB) * 100 / TOTAL_MB,2) || '%' "USED_PERCENTAGE", SYSDATE from v$asm_disk order by path;
set pages 9999 lines 900
col path for a50
select path,label, group_number, disk_number, NAME,TYPE, state, header_status,mount_status, mount_date, round(TOTAL_MB/1024) "TOTAL_GB", round(FREE_MB/1024) "FREE_GB", round((TOTAL_MB-FREE_MB) * 100 / TOTAL_MB,2) || '%' "USED_PERCENTAGE", SYSDATE from v$asm_diskgroup order by path;
SELECT GROUP_NUMBER, NAME, TYPE, STATE, ceil (total_mb/1024) TOTAL_GB,ceil (free_mb/1024) FREE_GB,ceil ((required_mirror_free_mb)/1024),ceil ((usable_file_mb)/1024), SYSDATE FROM V$ASM_DISKGROUP order by NAME;
select table_name from dict where table_name like '%ASM%';
-------------------------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a50 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label 'Disk Group' of total_mb used_mb on disk_group_name
compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status = 'MEMBER'
ORDER BY a.name
/
OTHER ASM QUERIES
-----------------
set pages 9999 lines 900
col path for a50
select path,label, group_number, disk_number, NAME,TYPE, state, header_status,mount_status, mount_date,create_date, round(TOTAL_MB/1024) "TOTAL_GB", round(FREE_MB/1024) "FREE_GB",round((TOTAL_MB-FREE_MB) * 100 / TOTAL_MB,2) || '%' "USED_PERCENTAGE", SYSDATE from v$asm_disk order by path;
set pages 9999 lines 900
col path for a50
select path,label, group_number, disk_number, NAME,TYPE, state, header_status,mount_status, mount_date, round(TOTAL_MB/1024) "TOTAL_GB", round(FREE_MB/1024) "FREE_GB", round((TOTAL_MB-FREE_MB) * 100 / TOTAL_MB,2) || '%' "USED_PERCENTAGE", SYSDATE from v$asm_diskgroup order by path;
SELECT GROUP_NUMBER, NAME, TYPE, STATE, ceil (total_mb/1024) TOTAL_GB,ceil (free_mb/1024) FREE_GB,ceil ((required_mirror_free_mb)/1024),ceil ((usable_file_mb)/1024), SYSDATE FROM V$ASM_DISKGROUP order by NAME;
select table_name from dict where table_name like '%ASM%';
Datafiles of a particular TableSpace
------------------------------------
set pages 9999 lines 300
col tablespace_name for a30
col file_name for a80
select tablespace_name,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAXSIZE_MB from dba_data_files where tablespace_name='&TABLESPACE_NAME' order by 1,2;
ASM Disk Database Files Report
------------------------------
set pages 9999 lines 300
col full_alias_path for a70
col file_type for a15
select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASE_NAME'
)
connect by prior rindex = pindex;
All schema object details in a tablespace
-----------------------------------------
set pages 9999 lines 300
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
To resize a datafile (ASM)
---------------------------
ALTER DATABASE DATAFILE '&FILE_NAME' RESIZE 4096M;
ALTER DATABASE DATAFILE '&FILE_NAME' AUTOEXTEND ON MAXSIZE 8G;
To add a new datafile in a tablespace (ASM)
--------------------------------------------
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile
select tablespace_name,file_name from dba_data_files where file_name like '%&datafilename%';
ALTER TABLESPACE <TABLESPACE NAME> ADD DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
To Create a new tablespace (ASM)
---------------------------------
CREATE TABLESPACE <TABLESPACE NAME> DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
Schemas in a tablespace
-----------------------
set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
V$ASM_DISK Header_Status:
------------------------
UNKNOWN - Automatic Storage Management disk header has not been read
CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement
INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software
version.
PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP
statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies
that an additional platform-specific action has been taken by an administrator to make the disk available for
Automatic Storage Management.
MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk
group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option
FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new
disk group with the ALTER DISKGROUP statement.
CONFLICT - Automatic Storage Management disk was not mounted due to a conflict
FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and
OCR disks.
ASM Space Report
----------------
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 500
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name FORMAT a12 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a45 HEAD 'Path'
COLUMN disk_file_name FORMAT a12 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a12 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL "" OF total_mb used_mb ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a ,v$asm_disk b
where a.group_number (+) =b.group_number
ORDER BY a.name;
To find the free space in an ASM disk:
--------------------------------------
select group_number, disk_number, name, failgroup, create_date, path, total_mb,free_mb from v$asm_disk;
To find the free space in an ASM diskgroup:
-------------------------------------------
select name, group_number, name, type, state, total_mb, free_mb from v$asm_diskgroup;
To see the current ASM operations in Progress:
----------------------------------------------
select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from v$asm_operation;
set head off
select 'Diskgroup Information' from dual;
set head on
column name format a15
column DG# format 99
select group_number DG#, name, state, type, total_mb, free_mb, round(free_mb/total_mb*100,2) pct_free from v$asm_diskgroup;
Monitor space used in ASM Disk Groups
-------------------------------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/
Disk Group Name File Name File Size (MB) Used Size (MB) Free Size (MB) Pct. Used
-------------------- -------------------- -------------------- -------------- -------------- -------
Space used by database files.No other files found for cleanup.
There are 2 undo tablespaces for this database. Reclaimed some freespace from non-default undo tablespace (i.e:"UNDOTBS1".)
Current status:
---------------
ORADATA diskgroup having xxx gb of freespace out of yyyy gb.
http://dbasanthosh.wordpress.com/2012/05/
-----------------------------------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a20 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label “” of total_mb used_mb free_mb on disk_group_name
compute sum label “Grand Total: ” of total_mb used_mb free_mb on report
SELECT NVL(a.name, '[CANDIDATE]') disk_group_name,b.path disk_file_path,b.name disk_file_name,
b.total_mb total_mb,(b.total_mb – b.free_mb) used_mb,b.free_mb free_mb,
ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b
USING (group_number) ORDER BY a.nameUSING (group_number) ORDER BY a.name
/
Install Instructions
====================
Download ASM Patch
Check DR sync status
shutdown all Applications related to databases
PROD
====
Blackout all the sevices on database server Node 1 and Node 2 (OEM)
Capture the pre-shutdown status at OS level.
Capture the pre-shutdown status at Database level.
shutdown all the instances on the database server (Node 1)
shutdown ASM instance on server (Node 1)
shutdown all the instances on the database server (Node 2)
shutdown ASM instance on server (Node 2)
Install Patch on Node 1
-----------------------
set environment variables to asm
export ORACLE_HOME=<>
cd <PATCH_NUMBER>
$ORACLE_HOME/Opatch/opatch apply -local
ps -ef|grep asm
. oraenv
+ASM
Install Patch on Node 2
-----------------------
set environment variables to asm
export ORACLE_HOME=<>
cd <PATCH_NUMBER>
$ORACLE_HOME/Opatch/opatch apply -local
ps -ef|grep asm
. oraenv
+ASM
Validate whether the Patches are applied or not
-----------------------------------------------
$ORACLE_HOME/Opatch/opatch lsinventory
start ASM instance on server (Node 1)
start all the instances on the database server (Node 1)
start ASM instance on server (Node 2)
start all the instances on the database server (Node 2)
start the listeners and validate the connections (Node 1)
start the listeners and validate the connections (Node 2)
Database Validation
-------------------
validate all the Database services for all databases (Node 1)
validate all the Database services for all databases (Node 2)
Application Validation
----------------------
Startup the applications and validate and inform Application team
Application team should validate.
DR
==
Same steps as in PROD
Check DR sync status
FALLBACK Plan
=============
set environment variables
Rollback Patches on both Node 1 and Node 2
$ORACLE_HOME/Opatch/opatch -id <PATCH_NO> rollback
Database Validation
-------------------
validate all the Database services for all databases (Node 1)
validate all the Database services for all databases (Node 2)
Application Validation
----------------------
Startup the applications and validate and inform Application team
Application team should
validate.
How to add / drop disk in ASM
http://sanoralife.blogspot.in/2012/02/how-to-adddrop-disk-in-asm.html
Its not that quite often your SA asks you to release one of the disks that you been using it for a while for your database but if they do then you need to know how to remove the disk and add a new disk from your ASM.
Here are the steps to Add and Drop a Disk from ASM Diskgroup. Note that asm_power_limit parameter once set is permanent as it writes to both spfile as well (check alert log when you do alter session set asm_power_limit = 10 command to see that it converts it into scope=both).
asm_power_limit parameter
-------------------------
In simple words, Think that you are increasing the work force to finish the job (re-balancing the disk(s)) quicker. You can read more in Oracle Docs.
Steps to Add a Disk:
--------------------
1.Assign the disk to ORACLEASM
/etc/init.d/oracleasm createdisk ASM_DATA_10 /dev/mapper/mpath1p1
2.ScanDisk in ALL NODES
/etc/init.d/oracleasm scandisks
3.Check the header_status in v$asm_disks to make sure that the disk can be added (check below for each status description).
a.Login as sqlplus / as sysasm – note SYSASM here… (for 11g)
b.set lines 200
select group_number, substr(path,1,20) path, substr(name,1,20) disk_name, total_mb, free_mb, state, header_status, mode_status from v$asm_disk;
4.Now Add the Disk to diskgroup.
alter diskgroup ASM_DATA_DISK add disk 'ORCL:ASM_DATA_DISK'; --At this point the new disk is added to the group and Re-Balancing is occurring in the background (basically spreading the data across all the disks along with the new one in that diskgroup)
5.Check the status in v$asm_operations for rebalancing action.
select * from gv$asm_operation;
Now, You are ready to start using the new disk.
Steps to Drop a Disk:
---------------------
6.Drop the old Disk
a.alter diskgroup ASM_DATA__DISK drop disk ASM_DATA_01; --At this point, all the data on disk that you are dropping is being distributed to other disks within that diskgroup.
b.select *from gv$asm_operation; --to check the status on rebalancing while dropping the disk
7.Once dropped from ASM, delete from oracleasm library:
# /etc/init.d/oracleasm deletedisk ASM_DATA_01
8.ScanDisk in ALL NODES
/etc/init.d/oracleasm scandisks
http://sanoralife.blogspot.in/2012/02/how-to-adddrop-disk-in-asm.html
Its not that quite often your SA asks you to release one of the disks that you been using it for a while for your database but if they do then you need to know how to remove the disk and add a new disk from your ASM.
Here are the steps to Add and Drop a Disk from ASM Diskgroup. Note that asm_power_limit parameter once set is permanent as it writes to both spfile as well (check alert log when you do alter session set asm_power_limit = 10 command to see that it converts it into scope=both).
asm_power_limit parameter
-------------------------
In simple words, Think that you are increasing the work force to finish the job (re-balancing the disk(s)) quicker. You can read more in Oracle Docs.
Steps to Add a Disk:
--------------------
1.Assign the disk to ORACLEASM
/etc/init.d/oracleasm createdisk ASM_DATA_10 /dev/mapper/mpath1p1
2.ScanDisk in ALL NODES
/etc/init.d/oracleasm scandisks
3.Check the header_status in v$asm_disks to make sure that the disk can be added (check below for each status description).
a.Login as sqlplus / as sysasm – note SYSASM here… (for 11g)
b.set lines 200
select group_number, substr(path,1,20) path, substr(name,1,20) disk_name, total_mb, free_mb, state, header_status, mode_status from v$asm_disk;
4.Now Add the Disk to diskgroup.
alter diskgroup ASM_DATA_DISK add disk 'ORCL:ASM_DATA_DISK'; --At this point the new disk is added to the group and Re-Balancing is occurring in the background (basically spreading the data across all the disks along with the new one in that diskgroup)
5.Check the status in v$asm_operations for rebalancing action.
select * from gv$asm_operation;
Now, You are ready to start using the new disk.
Steps to Drop a Disk:
---------------------
6.Drop the old Disk
a.alter diskgroup ASM_DATA__DISK drop disk ASM_DATA_01; --At this point, all the data on disk that you are dropping is being distributed to other disks within that diskgroup.
b.select *from gv$asm_operation; --to check the status on rebalancing while dropping the disk
7.Once dropped from ASM, delete from oracleasm library:
# /etc/init.d/oracleasm deletedisk ASM_DATA_01
8.ScanDisk in ALL NODES
/etc/init.d/oracleasm scandisks
ASMLib Troubleshooting
ASMLib Troubleshooting in Linux
http://linuxpkd.blogspot.in/2011/07/oracle-asmlib-troubleshooting-in-linux.html
This is the guide will help you how to do troubleshoot the oracleasmlib step by step;
1. rpm -qa | grep asm ---> It should be shows the list of installed oracle asm package
# rpm -qa |grep asm
oracleasm-support-2.0.3-1
oracleasmlib-2.0.2-1
oracleasm-2.6.9-22.ELsmp-2.0.3-1
2. rpm -ql oracleasm-support ---> which would show the scripts location.
# rpm -ql oracleasm-support
/etc/init.d/oracleasm
/etc/sysconfig/oracleasm
/usr/lib/oracleasm/oracleasm_debug_link
/usr/sbin/asmscan
/usr/sbin/asmtool
/etc/init.d/oracleasm is the command used to configure the ASM and scanning , configuring, litsing and querying the
disks from ASM. this script calls the asmtool and asmscan command.as per the scanning devices, there is possibility to
modify the file in /etc/sysconfig/oracleasm or else the excludes some of the functions in this file.
3. and ensure the oracleasm module to loaded to kernel.
[root@test1a ~]# lsmod | grep -i oracleasm
oracleasm 84136 1
[root@test1a ~]#
4. Verify the modules info as like,
[root@test1a ~]# modinfo oracleasm
filename: /lib/modules/2.6.18-194.el5/kernel/drivers/addon/oracleasm/oracleasm.ko
description: Kernel driver backing the Generic Linux ASM Library.
author: Joel Becker <joel.becker@oracle.com>
version: 2.0.5
license: GPL
srcversion: 6D09F6DEC4890E127C660DD
depends:
vermagic: 2.6.18-194.el5 SMP mod_unload gcc-4.1
5. Make sure the disks which are using, kernel should knows by the device in place of /dev or /etc/partitions. All the asmlib disks
should be partition before to create disk.
6. as the configuration part of ASMLib,run /etc/init.d/oracleasm configure as below,
[root@test1a ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting <ENTER> without typing an
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface [oracle]:
Default group to own the driver interface [dba]:
Start Oracle ASM library driver on boot (y/n) [y]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: [ OK ]
Scanning system for ASM disks: [ OK ]
7. after oracleasm configured the status of the output should be like below,
[root@test1a ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: [ OK ]
Checking if /dev/oracleasm is mounted: [ OK ]
8. all the configured ASM disks should exitsts the default mount point location.
[root@test1a ~]# ls -l /dev/oracleasm
total 0
drwxr-xr-x 1 root root 0 Jul 18 16:52 disks
drwxrwx--- 1 oracle dba 0 Jul 18 16:52 iid
[root@test1a ~]# ls -l /dev/oracleasm/disks
total 0
brw-rw---- 1 oracle dba 120, 112 Jul 18 16:52 ARCHLOG
brw-rw---- 1 oracle dba 120, 224 Jul 18 16:52 DATA1
brw-rw---- 1 oracle dba 120, 208 Jul 18 16:52 DATA2
9. create a disk using by createdisk parameters:
#/etc/init.d/oracleasm createdisk VOL1 /dev/mapper/mapth1
10. For scanning the createdisk or ASM disks as below,
[root@test1a ~]# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks: [ OK ]
11. For lists out the ASM disk which is under in ASM, use the below command,
[root@test1a ~]# /etc/init.d/oracleasm listdisks
ARCHLOG
DATA1
DATA2
DATA3
LOG1
LOG2
LOG3
LOG4
12. To query the asm disks, whether it is in VALID or notVALID state.
[root@test1a ~]# /etc/init.d/oracleasm querydisk LOG1
Disk "LOG1" is a valid ASM disk on device [120, 48]
13. To remove or delete the ASM disks, use below command,
[root@test1a ~]# /etc/init.d/oracleasm deletedisk LOG1
14. /usr/sbin/oracleasm-discover -- it will discover the asm configured disks with that associated name.
[root@test1a ~]# /usr/sbin/oracleasm-discover
15. how to identify the configured ASM disk to physical Disk name as tricky step use the blkid command.
[root@test1a ~]# /sbin/blkid
http://linuxpkd.blogspot.in/2011/07/oracle-asmlib-troubleshooting-in-linux.html
This is the guide will help you how to do troubleshoot the oracleasmlib step by step;
1. rpm -qa | grep asm ---> It should be shows the list of installed oracle asm package
# rpm -qa |grep asm
oracleasm-support-2.0.3-1
oracleasmlib-2.0.2-1
oracleasm-2.6.9-22.ELsmp-2.0.3-1
2. rpm -ql oracleasm-support ---> which would show the scripts location.
# rpm -ql oracleasm-support
/etc/init.d/oracleasm
/etc/sysconfig/oracleasm
/usr/lib/oracleasm/oracleasm_debug_link
/usr/sbin/asmscan
/usr/sbin/asmtool
/etc/init.d/oracleasm is the command used to configure the ASM and scanning , configuring, litsing and querying the
disks from ASM. this script calls the asmtool and asmscan command.as per the scanning devices, there is possibility to
modify the file in /etc/sysconfig/oracleasm or else the excludes some of the functions in this file.
3. and ensure the oracleasm module to loaded to kernel.
[root@test1a ~]# lsmod | grep -i oracleasm
oracleasm 84136 1
[root@test1a ~]#
4. Verify the modules info as like,
[root@test1a ~]# modinfo oracleasm
filename: /lib/modules/2.6.18-194.el5/kernel/drivers/addon/oracleasm/oracleasm.ko
description: Kernel driver backing the Generic Linux ASM Library.
author: Joel Becker <joel.becker@oracle.com>
version: 2.0.5
license: GPL
srcversion: 6D09F6DEC4890E127C660DD
depends:
vermagic: 2.6.18-194.el5 SMP mod_unload gcc-4.1
5. Make sure the disks which are using, kernel should knows by the device in place of /dev or /etc/partitions. All the asmlib disks
should be partition before to create disk.
6. as the configuration part of ASMLib,run /etc/init.d/oracleasm configure as below,
[root@test1a ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting <ENTER> without typing an
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface [oracle]:
Default group to own the driver interface [dba]:
Start Oracle ASM library driver on boot (y/n) [y]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: [ OK ]
Scanning system for ASM disks: [ OK ]
7. after oracleasm configured the status of the output should be like below,
[root@test1a ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: [ OK ]
Checking if /dev/oracleasm is mounted: [ OK ]
8. all the configured ASM disks should exitsts the default mount point location.
[root@test1a ~]# ls -l /dev/oracleasm
total 0
drwxr-xr-x 1 root root 0 Jul 18 16:52 disks
drwxrwx--- 1 oracle dba 0 Jul 18 16:52 iid
[root@test1a ~]# ls -l /dev/oracleasm/disks
total 0
brw-rw---- 1 oracle dba 120, 112 Jul 18 16:52 ARCHLOG
brw-rw---- 1 oracle dba 120, 224 Jul 18 16:52 DATA1
brw-rw---- 1 oracle dba 120, 208 Jul 18 16:52 DATA2
9. create a disk using by createdisk parameters:
#/etc/init.d/oracleasm createdisk VOL1 /dev/mapper/mapth1
10. For scanning the createdisk or ASM disks as below,
[root@test1a ~]# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks: [ OK ]
11. For lists out the ASM disk which is under in ASM, use the below command,
[root@test1a ~]# /etc/init.d/oracleasm listdisks
ARCHLOG
DATA1
DATA2
DATA3
LOG1
LOG2
LOG3
LOG4
12. To query the asm disks, whether it is in VALID or notVALID state.
[root@test1a ~]# /etc/init.d/oracleasm querydisk LOG1
Disk "LOG1" is a valid ASM disk on device [120, 48]
13. To remove or delete the ASM disks, use below command,
[root@test1a ~]# /etc/init.d/oracleasm deletedisk LOG1
14. /usr/sbin/oracleasm-discover -- it will discover the asm configured disks with that associated name.
[root@test1a ~]# /usr/sbin/oracleasm-discover
15. how to identify the configured ASM disk to physical Disk name as tricky step use the blkid command.
[root@test1a ~]# /sbin/blkid
ASMLib disk Mapping to physical device
ASMLib disk Mapping to physical device
www.theplayground.de/joomla/index.php?option=com_content&view=article&id=306:ma
# oracleasm querydisk /dev/mapper/vol001
Device "/dev/mapper/vol001" is marked an ASM disk with the label "VOL001"
But what if you want to know what physical device is hidden behind the name of an ASMLib disk?
# oracleasm listdisks
VOL001
VOL002
VOL003
VOL004
...
The first attempt doesn't reveal too much:
# oracleasm querydisk VOL001
Disk "VOL001" is a valid ASM disk
# oracleasm querydisk -v -d VOL001
Disk "VOL001" is a valid ASM disk on device /dev/dm-4[253,4]
# oracleasm querydisk -v -p VOL001
Disk "ORC0001" is a valid ASM disk
/dev/mapper/vol001: LABEL="VOL001" TYPE="oracleasm"
ASM Troubleshooting
# /proc/partitions - shows the major minor numbers for the device at OS level
# asmcmd lsdsk -p
# /etc/init.d/oracleasm status
# /etc/init.d/oracleasm listdisks
# ls -l /dev/oracleasm
# ls -l /dev/oracleasm/disks
# oracleasm query disk /dev/mapper/<ASMLABEL>
# fdisk -l /dev/mapper/<ASMLABEL>
# fdisk -l /dev/sd-10 ----- OS level
Set the environment for the asm
-------------------------------
uname
cat /etc/oratab OR cat /var/opt/oracle/oratab
export ORACLE_SID=<DBNAME>
export ORACLE_HOME=<>
export PATH=$PATH:$ORACLE_HOME/bin
ps -ef| grep asm
. oraenv
+ASM
ASM Space Report
----------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
SELECT
distinct name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM gv$asm_diskgroup ORDER BY name
/
ASM Disk Space Usage Report In Detail
-------------------------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label 'Disk Group' of total_mb used_mb on disk_group_name
compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status <> 'PROVISIONED'
ORDER BY a.name
/
OTHER ASM QUERIES
-----------------
set pages 9999 lines 900
SELECT GROUP_NUMBER, NAME, TYPE, STATE, ceil (total_mb/1024) TOTAL_GB,ceil (free_mb/1024) FREE_GB,
ceil ((required_mirror_free_mb)/1024),ceil ((usable_file_mb)/1024), SYSDATE
FROM V$ASM_DISKGROUP order by NAME;
SELECT GROUP_NUMBER, NAME, TYPE, STATE, ceil (total_mb/1024) TOTAL_GB,ceil (free_mb/1024) FREE_GB,
ceil ((required_mirror_free_mb)/1024),ceil ((usable_file_mb)/1024), SYSDATE
FROM GV$ASM_DISKGROUP order by NAME;
select GROUP_NUMBER,DISK_NUMBER,TOTAL_MB,FREE_MB,NAME,MOUNT_STATUS from v$asm_disk;
select INST_ID,GROUP_NUMBER,DISK_NUMBER,TOTAL_MB,FREE_MB,NAME,MOUNT_STATUS from gv$asm_disk;
select table_name from dict where table_name like '%ASM%';
ASM TABLESPACE Scripts
http://files.meetup.com/1729503/1729503/All_about_ASM.pdf
When a tablespace is getting filled up, action need to be taken (Preference-wise)
1. Resize the datafile.
2. If AUTOEXTEND ON,then resize the maxsize of the datafile.
3. Add a new datafile to the tablespace.
Note:-
Check the availability of the free space on the disk at OS level.
df -h (Linux,AIX)
df -gt
oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon
Set the environment for the database
------------------------------------
uname
cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab
export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin
Database Details
----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME,INSTANCE_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
ASM Tablespace Utilization Script
----------------------------------
SET LINESIZE 300
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (GB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (GB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (GB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
SELECT
distinct name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, round(total_mb/1024) "total_gb"
, round(free_mb/1024) "free_gb"
, round((total_mb - free_mb) / 1024) "used_gb"
, round((1- (free_mb / total_mb))*100, 2) "pct_used"
from v$asm_diskgroup ORDER BY name
/
ASM Disk Space Usage Script (In Detail)
---------------------------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label 'Disk Group' of total_mb used_mb on disk_group_name
compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status = 'MEMBER'
ORDER BY a.name
/
Datafiles of a particular TableSpace
-------------------------------------
set pages 9999 lines 300
col tablespace_name for a30
col file_name for a80
select tablespace_name,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAXSIZE_MB
from dba_data_files where tablespace_name='&tablespace_name' order by 1,2;
ASM Disk Database Files Script
------------------------------
set pages 9999 lines 300
col full_alias_path for a70
col file_type for a15
select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASE_NAME'
)
connect by prior rindex = pindex;
TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
To resize a datafile (ASM)
--------------------------
alter database datafile '&datafile_name' resize 4096M;
If AUTOEXTEND ON
----------------
alter database datafile '&datafile_name' autoextend on maxsize 8G;
To add a new datafile in a tablespace (ASM)
-------------------------------------------
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile
select tablespace_name,file_name from dba_data_files where file_name like '%&datafile_name%';
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE 8G;
To Create a new tablespace (ASM)
---------------------------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED;
Schemas in a tablespace
-----------------------
set pages 9999 lines 300
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
All schema object details in a tablespace
-----------------------------------------
set pages 9999 lines 300
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
When a tablespace is getting filled up, action need to be taken (Preference-wise)
1. Resize the datafile.
2. If AUTOEXTEND ON,then resize the maxsize of the datafile.
3. Add a new datafile to the tablespace.
Note:-
Check the availability of the free space on the disk at OS level.
df -h (Linux,AIX)
df -gt
oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon
Set the environment for the database
------------------------------------
uname
cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab
export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin
Database Details
----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME,INSTANCE_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
ASM Tablespace Utilization Script
----------------------------------
SET LINESIZE 300
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (GB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (GB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (GB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
SELECT
distinct name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, round(total_mb/1024) "total_gb"
, round(free_mb/1024) "free_gb"
, round((total_mb - free_mb) / 1024) "used_gb"
, round((1- (free_mb / total_mb))*100, 2) "pct_used"
from v$asm_diskgroup ORDER BY name
/
ASM Disk Space Usage Script (In Detail)
---------------------------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label 'Disk Group' of total_mb used_mb on disk_group_name
compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status = 'MEMBER'
ORDER BY a.name
/
Datafiles of a particular TableSpace
-------------------------------------
set pages 9999 lines 300
col tablespace_name for a30
col file_name for a80
select tablespace_name,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAXSIZE_MB
from dba_data_files where tablespace_name='&tablespace_name' order by 1,2;
ASM Disk Database Files Script
------------------------------
set pages 9999 lines 300
col full_alias_path for a70
col file_type for a15
select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASE_NAME'
)
connect by prior rindex = pindex;
TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
To resize a datafile (ASM)
--------------------------
alter database datafile '&datafile_name' resize 4096M;
If AUTOEXTEND ON
----------------
alter database datafile '&datafile_name' autoextend on maxsize 8G;
To add a new datafile in a tablespace (ASM)
-------------------------------------------
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile
select tablespace_name,file_name from dba_data_files where file_name like '%&datafile_name%';
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE 8G;
To Create a new tablespace (ASM)
---------------------------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED;
Schemas in a tablespace
-----------------------
set pages 9999 lines 300
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
All schema object details in a tablespace
-----------------------------------------
set pages 9999 lines 300
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
No comments:
Post a Comment