How a simple space can create Havoc !!!

Adding space is one of the day-to-day task for a database admin. I still remember Arup Nanda’s interview in Sangam-18, where he talks about autonomous database, and how the repetitive task will be commoditize. We have already orchestrated the repetitive tasks such as data file addition and similar stuff. However, there are one-off instances where automation fails or when we have to prepare for a sale forecast and intervene manually.

 

It is the simplest task, even every academia would be aware of, at the same time, even a space can cause huge penalties.

 

This caught my eye when one fine day we were struck by the outage. During our analysis we found that, data file was added inappropriately in $ORACLE_HOME location without any proper naming convention and file extension. We were living with it until someone came and performed the cleanup and removed the unknown file.

 

Fortunately, we had backups available as per the defined polices within RTO and RPO. That is the reason you will find why DBA’s are obsessed with backups.

 I am prepared for the worst, but hope for the best - Benjamin Disraeli

 

Case Study:

 How we end up in such situations. Data file created in $ORACLE_HOME and we know the consequences if home are filled or already witnessed the cleanup accidents.

 

 

DBA in hurry towards end of the shift runs this.

 Example 1: accidental copy paste error introduces space.

  

 

Example 2: Missed the plus symbol

 

 

 Notice, nothing throws error and successfully executes and accumulates as slow poison in long run. These are oracle managed file naming convention and by default it creates in $ORACLE_HOME location.

 Correct way:

 

 

The idea is the less you type the less mistake you make. I used below script manually and rest is taken care by OMF.

 

TO Reduce human error.

----------------------

select 'alter tablespace '||upper('&Tablespace_name')||' add datafile '''||'+'||a.name||''' size '||'&Size_GB'||'G autoextend ON;' from v$asm_diskgroup a where a.name like '%DATA%';

 

Example:

 

 

 

Feel free to use the table space related scripts uploaded in github.