AnsweredAssumed Answered

How to free up Oracle ASM Disk Space?

Question asked by Gaurav Kabra on Dec 18, 2018
Latest reply on Jan 3, 2019 by Gaurav Kabra

Like • Show 1 Like1
Comment • 8

Problem Statement:

Oracle ASM partition used by RSA IG&L is running out of space on the Production environment.

We have checked the existing configurations on the server and we can see that 1TB of disk space was allocated to Oracle ASM, out of which only 60GB is free. The AVUSER database export (.dmp file) is only 45GB in size.

Customer Environment:

RSA IG&L v7.0 Patch 3 – Hardware Appliance with Oracle 12C and Wildfly 8.2 Application Server on Suse Linux 11

Steps Taken Till Now:

We have done some housekeeping as suggested on the KB articles listed below but that has not solved the issue:

We had found another KB article which may have had some resolution steps, but that link does not seem to exist now:

https://community.rsa.com/articles/en_US/How_To/KB-1241-How-to-free-up-Oracle-ASM-disk-space

Gaurav Kabra Jan 3, 2019 6:02 AM

Correct Answer

Thanks very much, Mostafa for your assistance in this issue. We found out that the Data Guard was not working for some reason due to which the archive logs generated on the Primary Oracle DB were not getting deleted (as a part of the scheduled cron job which deletes the archive logs) as they were not getting applied on the Secondary Oracle DB.

We had to manually delete the hidden archive logs (both on primary and secondary Oracle servers) on the ASM disk space, which is only accessible if you run a very specific set of commands. I am posting the steps here for everyone's benefit:

Run the below commands as an oracle user on a hardware appliance with ASM configured:

export ORACLE_SID=+ASM;
export ORACLE_HOME=/u01/app/12.1.0/grid;

asmcmd
ls
cd AVDB/ --> (cd AVDB_STD/ incase this is done on Standby database with ORACLE_SID=AVDB_STD)

ls
cd ARCHIVELOG/

ls
rm -r <OLD_ARCHIVE_LOG_FILES>

Don't forget to change the environment variables for ORACLE_SID and ORACLE_HOME back to their original values.

See the reply in context

No one else had this question

Outcomes

Mostafa Helmy

Dec 18, 2018 10:41 AM

If the dump file is only 45GB yet you have nearly 1TB used … then I am guessing you are running the database in ARCHIVELOG mode and have tons of archive logs pilled on the ASM file system. I would check with the DBAs to see what is using up the ASM space and how to keep that under control.

Actions

Gaurav Kabra @ Mostafa Helmy on Dec 18, 2018 11:04 AM

Hi Mostafa,

Good to hear from you! Hope you're well.

As it's a hardware appliance, the customer doesn't have a dedicated DBA. Is there a way to check if the DB is running in ARCHIVELOG mode? How can we clean up the old piled up archive logs which can free up the ASM file system?

Actions

Mostafa Helmy

@ Gaurav Kabra on Dec 18, 2018 11:47 AM

Hi Gaurav, I'm fine thanks! Hope you are doing well yourself

You can check if ARCHIVELOG mode is running using either of the following queries (as SYSDBA):

ARCHIVE LOG LIST;

SELECT log_mode FROM gv$database;

You can also check the largest objects in the database using the following query (as SYSDBA):

SELECT

segment_type,

round(SUM(bytes) / 1024 / 1024 / 1024,1) size_gb

FROM

dba_segments

GROUP BY

segment_type

ORDER BY

2 DESC;

Also another thing, this was just a guess I had but if this is an appliance and you do find that they are running in ARCHIVELOG mode then they should disable it. We do not support or enable ARCHIVELOG mode by default our local databases, and if someone chooses to do so they need to have a DBA that can maintain the archive logs generated.

So first verify using the above queries and then we can see what can be done to free up space.

Actions

Gaurav Kabra @ Mostafa Helmy on Dec 18, 2018 12:23 PM

Thanks for the suggestions, Mostafa. Please see the output below:

ARCHIVE LOG LIST;

Database log mode Archive log Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2669
Next log sequence to archive 2674
Current log sequence 2674

SELECT log_mode FROM gv$database;

LOG_MODE
---------------
ARCHIVELOG

SELECT
segment_type, round(SUM(bytes) / 1024 / 1024 / 1024,1) size_gb
FROM dba_segments GROUP BY segment_type
ORDER BY 2 DESC;

SEGMENT_TYPE SIZE_GB
--------------------------------
TABLE 50.8
INDEX 45
LOBSEGMENT 3.1
TYPE2 UNDO 0.8
TABLE PARTITION 0.5
CLUSTER 0.1
INDEX PARTITION 0.1
SYSTEM STATISTICS 0
ROLLBACK 0
NESTED TABLE 0
TABLE SUBPARTITION 0
LOBINDEX 0
LOB PARTITION 0

Actions

Mostafa Helmy

@ Gaurav Kabra on Dec 18, 2018 1:07 PM

Then my guess was correct. The database is running ARCHIVELOG mode (which we do not support). I would check first why the database has been changed to ARCHIVELOG mode? The main reasons for this are either to use RMAN backup/recovery (not supported) or having Data Guard enabled (also not supported by normal RSA Support). If no special RSA Support agreement is made to support this and document the setup, then they must have a proper DBA to manage the side effects of ARCHIVELOG mode.

Having said that, here are a few things to try:

Check if Data Guard is being used (as SYSDBA)

select CASE WHEN count(1)>0 THEN 'Data Guard in use' ELSE 'No Data Guard' END HAS_DG from v$archive_dest where status = 'VALID' and target = 'STANDBY';

Clear expired archive logs (using RMAN)

CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;

The problem is that if you find Data Guard enabled, the process is more complex than that since there are many other factors contributing to this (Standby database status, Data Guard replication status … etc).

Actions

Gaurav Kabra @ Mostafa Helmy on Dec 18, 2018 1:08 PM

Hi Mostafa,

Please see the output of the query below:

select CASE WHEN count(1)>0 THEN 'Data Guard in use' ELSE 'No Data Guard' END HAS_DG

fromv$archive_dest where status = 'VALID' and target = 'STANDBY';

HAS_DG
-------------
Data Guard in use

Do you have some documentation on how to remove expired logs when Data Guard is in use?

Really appreciate your time and help so far on this.

1 person found this helpful

Actions

Mostafa Helmy

@ Gaurav Kabra on Dec 18, 2018 2:05 PM

Well you could try a few things first:

Make sure Data Guard is in sync, otherwise it would be a reason why archive logs are piling up. Run the following query on both Primary and Standby databases at the same time and check if the numbers match:

select max(sequence#) from v$archived_log;

If Data Guard is not in sync for any reason then you need to first fix the DG replication process or disable it.
One DG is either fixed or disabled, connect to RMAN then clear the archive logs as follows. Note: there is more than one way to do so, I got this from Google

      crosscheck backup;
      crosscheck archivelog all;
      delete noprompt obsolete;

Actions

Gaurav Kabra @ Mostafa Helmy on Jan 3, 2019 6:02 AM

Run the below commands as an oracle user on a hardware appliance with ASM configured:

export ORACLE_SID=+ASM;
export ORACLE_HOME=/u01/app/12.1.0/grid;

asmcmd
ls
cd AVDB/ --> (cd AVDB_STD/ incase this is done on Standby database with ORACLE_SID=AVDB_STD)

ls
cd ARCHIVELOG/

ls
rm -r <OLD_ARCHIVE_LOG_FILES>

Don't forget to change the environment variables for ORACLE_SID and ORACLE_HOME back to their original values.

Actions

8 Replies

Mostafa Helmy Dec 18, 2018 10:41 AM
Mark Correct
Correct Answer
If the dump file is only 45GB yet you have nearly 1TB used … then I am guessing you are running the database in ARCHIVELOG mode and have tons of archive logs pilled on the ASM file system. I would check with the DBAs to see what is using up the ASM space and how to keep that under control.
Like • Show 0 Likes0
Actions
- Gaurav Kabra @ Mostafa Helmy on Dec 18, 2018 11:04 AM
  Mark Correct
  Correct Answer
  Hi Mostafa,
  
  Good to hear from you! Hope you're well.
  
  As it's a hardware appliance, the customer doesn't have a dedicated DBA. Is there a way to check if the DB is running in ARCHIVELOG mode? How can we clean up the old piled up archive logs which can free up the ASM file system?
  Like • Show 0 Likes0
  Actions
  - Mostafa Helmy @ Gaurav Kabra on Dec 18, 2018 11:47 AM
    Mark Correct
    Correct Answer
    Hi Gaurav, I'm fine thanks! Hope you are doing well yourself
    
    You can check if ARCHIVELOG mode is running using either of the following queries (as SYSDBA):
    ARCHIVE LOG LIST;
    SELECT log_mode FROM gv$database;
    
    You can also check the largest objects in the database using the following query (as SYSDBA):
    SELECT
        segment_type,
        round(SUM(bytes) / 1024 / 1024 / 1024,1) size_gb
    FROM
        dba_segments
    GROUP BY
        segment_type
    ORDER BY
        2 DESC;
    
    Also another thing, this was just a guess I had but if this is an appliance and you do find that they are running in ARCHIVELOG mode then they should disable it. We do not support or enable ARCHIVELOG mode by default our local databases, and if someone chooses to do so they need to have a DBA that can maintain the archive logs generated.
    
    So first verify using the above queries and then we can see what can be done to free up space.
    Like • Show 1 Like1
    Actions
    - Gaurav Kabra @ Mostafa Helmy on Dec 18, 2018 12:23 PM
      Mark Correct
      Correct Answer
      Thanks for the suggestions, Mostafa. Please see the output below:
      
      ARCHIVE LOG LIST;
      Database log mode Archive log Mode
      Automatic archival Enabled
      Archive destination USE_DB_RECOVERY_FILE_DEST
      Oldest online log sequence 2669
      Next log sequence to archive 2674
      Current log sequence 2674
      SELECT log_mode FROM gv$database;
      LOG_MODE
      ---------------
      ARCHIVELOG
      SELECT
      segment_type, round(SUM(bytes) / 1024 / 1024 / 1024,1) size_gb
      FROM dba_segments GROUP BY segment_type
      ORDER BY 2 DESC;
      SEGMENT_TYPE SIZE_GB
      --------------------------------
      TABLE 50.8
      INDEX 45
      LOBSEGMENT 3.1
      TYPE2 UNDO 0.8
      TABLE PARTITION 0.5
      CLUSTER 0.1
      INDEX PARTITION 0.1
      SYSTEM STATISTICS 0
      ROLLBACK 0
      NESTED TABLE 0
      TABLE SUBPARTITION 0
      LOBINDEX 0
      LOB PARTITION 0
      Like • Show 0 Likes0
      Actions
      - Mostafa Helmy @ Gaurav Kabra on Dec 18, 2018 1:07 PM
        Mark Correct
        Correct Answer
        Then my guess was correct. The database is running ARCHIVELOG mode (which we do not support). I would check first why the database has been changed to ARCHIVELOG mode? The main reasons for this are either to use RMAN backup/recovery (not supported) or having Data Guard enabled (also not supported by normal RSA Support). If no special RSA Support agreement is made to support this and document the setup, then they must have a proper DBA to manage the side effects of ARCHIVELOG mode.
        
        Having said that, here are a few things to try:
        Check if Data Guard is being used (as SYSDBA)
        select CASE WHEN count(1)>0 THEN 'Data Guard in use' ELSE 'No Data Guard' END HAS_DG from v$archive_dest where status = 'VALID' and target = 'STANDBY';
        Clear expired archive logs (using RMAN)
        CROSSCHECK ARCHIVELOG ALL;
        DELETE EXPIRED ARCHIVELOG ALL;
        
        The problem is that if you find Data Guard enabled, the process is more complex than that since there are many other factors contributing to this (Standby database status, Data Guard replication status … etc).
        Like • Show 0 Likes0
        Actions
        Gaurav Kabra @ Mostafa Helmy on Dec 18, 2018 1:08 PM
        Mark Correct
        Correct Answer
        Hi Mostafa,
        
        Please see the output of the query below:
        
        select CASE WHEN count(1)>0 THEN 'Data Guard in use' ELSE 'No Data Guard' END HAS_DG
        fromv$archive_dest where status = 'VALID' and target = 'STANDBY';
        HAS_DG
        -------------
        Data Guard in use
        
        Do you have some documentation on how to remove expired logs when Data Guard is in use?
        Really appreciate your time and help so far on this.
        1 person found this helpful
        Like • Show 0 Likes0
        Actions
        Mostafa Helmy @ Gaurav Kabra on Dec 18, 2018 2:05 PM
        Mark Correct
        Correct Answer
        Well you could try a few things first:
        Make sure Data Guard is in sync, otherwise it would be a reason why archive logs are piling up. Run the following query on both Primary and Standby databases at the same time and check if the numbers match:
        select max(sequence#) from v$archived_log;
        If Data Guard is not in sync for any reason then you need to first fix the DG replication process or disable it.
        One DG is either fixed or disabled, connect to RMAN then clear the archive logs as follows. Note: there is more than one way to do so, I got this from Google
              crosscheck backup;
              crosscheck archivelog all;
              delete noprompt obsolete;
        Like • Show 1 Like1
        Actions
        Gaurav Kabra @ Mostafa Helmy on Jan 3, 2019 6:02 AM
        Unmark Correct
        Correct Answer
        Thanks very much, Mostafa for your assistance in this issue. We found out that the Data Guard was not working for some reason due to which the archive logs generated on the Primary Oracle DB were not getting deleted (as a part of the scheduled cron job which deletes the archive logs) as they were not getting applied on the Secondary Oracle DB.
        
        We had to manually delete the hidden archive logs (both on primary and secondary Oracle servers) on the ASM disk space, which is only accessible if you run a very specific set of commands. I am posting the steps here for everyone's benefit:
        
        Run the below commands as an oracle user on a hardware appliance with ASM configured:
        
        export ORACLE_SID=+ASM;
        export ORACLE_HOME=/u01/app/12.1.0/grid;
        asmcmd
        ls
        cd AVDB/ --> (cd AVDB_STD/ incase this is done on Standby database with ORACLE_SID=AVDB_STD)
        ls
        cd ARCHIVELOG/
        ls
        rm -r <OLD_ARCHIVE_LOG_FILES>
        Don't forget to change the environment variables for ORACLE_SID and ORACLE_HOME back to their original values.
        Like • Show 1 Like1
        Actions

How to free up Oracle ASM Disk Space?

Outcomes

Related Content

Recommended Content