ASM File Handling Basics

ASM File Handling Basics

Description:
          In this blog we are going to see datafile,logfile,and archivelog create,resize delete in the ASM oracle database server.

Oracle ASM:

         Oracle ASM is a volume manager and a file system for Oracle database files that supports single-instance Oracle Database and Oracle Real Application Clusters (Oracle RAC) configurations. Oracle ASM is Oracle’s recommended storage management solution that provides an alternative to conventional volume managers, file systems, and raw devices.

ASM Disk Groups:

         Oracle ASM uses disk groups to store data files; an Oracle ASM disk group is a collection of disks that Oracle ASM manages as a unit. Within a disk group, Oracle ASM exposes a file system interface for Oracle database files.

  1. Check datafile logfile and archivelog location in ASM

SQL> select name from v$datafile;
NAME
————————————————————————————————–

+DATA/ORAASM/DATAFILE/system.257.1092813819

+DATA/ORAASM/DATAFILE/sysaux.258.1092813853

+DATA/ORAASM/DATAFILE/undotbs1.259.1092813869

+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.270.1092814419

+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.271.1092814421

+DATA/ORAASM/DATAFILE/users.260.1092813869

+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.272.1092814421

+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/system.276.1092815375

+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/sysaux.277.1092815375

+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/undotbs1.275.1092815375

+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/users.279.1092815459

+DATA/ORAASM/DATAFILE/tblspace.280.1093044407

+DATA/ORAASM/DATAFILE/tblspace.281.1093044531

13 rows selected.

Logfiles :

SQL>  select member from v$logfile;

MEMBER
————————————————————————————————–

+DATA/ORAASM/ONLINELOG/group_3.267.1092813959

+DATA/ORAASM/ONLINELOG/group_3.268.1092813965

+DATA/ORAASM/ONLINELOG/group_2.263.1092813937

+DATA/ORAASM/ONLINELOG/group_2.266.1092813951

+DATA/ORAASM/ONLINELOG/group_1.264.1092813937

+DATA/ORAASM/ONLINELOG/group_1.265.1092813949

6 rows selected.

Archivelogs:

SQL> archive log list

Database log mode        Archive Mode

Automatic archival        Enabled

Archive destination        USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     14

Next log sequence to archive   16

Current log sequence        16

SQL> show parameter db_recovery_file_dest

NAME      TYPE  VALUE

———————————— ———– ——————————

db_recovery_file_dest      string  +DATA

db_recovery_file_dest_size      big integer 12732M

  1. Create new Datafiles,logfiles, archivelogs in ASM Disks

Datafile:

SQL> create tablespace test datafile’+data’;

Tablespace created.

SQL> select name from v$datafile;

NAME
————————————————————————————————–

+DATA/ORAASM/DATAFILE/system.257.1092813819

+DATA/ORAASM/DATAFILE/sysaux.258.1092813853

+DATA/ORAASM/DATAFILE/undotbs1.259.1092813869

+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.270.1092814419

+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.271.1092814421

+DATA/ORAASM/DATAFILE/users.260.1092813869

+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.272.1092814421

+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/system.276.1092815375

+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/sysaux.277.1092815375

+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/undotbs1.275.1092815375

+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/users.279.1092815459

+DATA/ORAASM/DATAFILE/tblspace.280.1093044407

+DATA/ORAASM/DATAFILE/tblspace.281.1093044531

+DATA/ORAASM/DATAFILE/test.282.1094538795

14 rows selected.

SQL>

SQL> select tablespace_name,file_name from dba_data_files where tablespace_name=’TEST’;

TABLESPACE_NAME   FILE_NAME

TEST                           +DATA/ORAASM/DATAFILE/test.282.1094538795

Logfiles: 

SQL> select member from v$logfile;

MEMBER
————————————————————————————————–

+DATA/ORAASM/ONLINELOG/group_3.267.1092813959

+DATA/ORAASM/ONLINELOG/group_3.268.1092813965

+DATA/ORAASM/ONLINELOG/group_2.263.1092813937

+DATA/ORAASM/ONLINELOG/group_2.266.1092813951

+DATA/ORAASM/ONLINELOG/group_1.264.1092813937

+DATA/ORAASM/ONLINELOG/group_1.265.1092813949

6 rows selected.

SQL> alter database add logfile;

Database altered.

SQL>  select member from v$logfile;

MEMBER
————————————————————————————————–

+DATA/ORAASM/ONLINELOG/group_3.267.1092813959

+DATA/ORAASM/ONLINELOG/group_3.268.1092813965

+DATA/ORAASM/ONLINELOG/group_2.263.1092813937

+DATA/ORAASM/ONLINELOG/group_2.266.1092813951

+DATA/ORAASM/ONLINELOG/group_1.264.1092813937

+DATA/ORAASM/ONLINELOG/group_1.265.1092813949

+DATA/ORAASM/ONLINELOG/group_4.283.1094538977

+DATA/ORAASM/ONLINELOG/group_4.284.1094538977

8 rows selected.

SQL>

  1. Alter / Resize existing Datafiles,logfiles, archivelogs in ASM Disks:

Datafiles:

alter database datafile ‘+DATA/ORAASM/DATAFILE/test.282.1094538795’ resize 150m;

 select file_name,bytes/1024/1024mb from dba_data_files where tablespace_name=’TEST’ order by file_name;

SQL> alter database datafile ‘+DATA/ORAASM/DATAFILE/test.282.1094538795’ resize 150m;

Database altered.

SQL> select file_name,bytes/1024/1024mb from dba_data_files where tablespace_name=’TEST’ order by file_name;

FILE_NAME                                                                       MB

+DATA/ORAASM/DATAFILE/test.282.1094538795           150

SQL> 

Logfiles:
SQL> alter database drop logfile group 4;

Database altered.

SQL> select member from v$logfile;

MEMBER
————————————————————————————————–

+DATA/ORAASM/ONLINELOG/group_3.267.1092813959

+DATA/ORAASM/ONLINELOG/group_3.268.1092813965

+DATA/ORAASM/ONLINELOG/group_2.263.1092813937

+DATA/ORAASM/ONLINELOG/group_2.266.1092813951

+DATA/ORAASM/ONLINELOG/group_1.264.1092813937

+DATA/ORAASM/ONLINELOG/group_1.265.1092813949

6 rows selected.

SQL>

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 4 (‘+DATA’,’+DATA’) SIZE 1G;

SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 (‘+DATA’,’+DATA’) SIZE 1G;

Database altered.

SQL> select thread#,group#,bytes/1024/1024,members,status from v$log;

 

   THREAD#     GROUP# BYTES/1024/1024  MEMBERS STATUS

———- ———- ————— ———- —————-

 1     1   200        2 CURRENT

 1     2   200        2 INACTIVE

 1     3   200        2 INACTIVE

 1     4   100        2 UNUSED

 1     5  1024        2 UNUSED

SQL>

 

 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8