Oracle Tips and Tricks — David Fitzjarrell

January 27, 2017

“Back It Up!!!”

Filed under: General — dfitzjarrell @ 14:21

“Expectations is the place you must always go to before you get to where you're going.” 
― Norton Juster, The Phantom Tollbooth   

In a recent post in the Oracle General database forum the following question was asked:


I have 3 schema's of almost equal sizes, off which only two are creating the backup's (Hot backup). One of the schema's is not creating 
any backup, by not creating I mean, the backup file that is generated size is too small than the other two files.

The size of the other two backup files is almost 20 GB while the third one is only 54 Bytes !!!

Below are the commands I am using for backup,

alter tablespace SCHEMA begin backup; ( DB level )
tar cpvf - SCHEMA_TS | compress -f > backup.tar.Z ( OS level )

The DB (Oracle Database 11g) is in Archive log mode and no error is being thrown while running the above commands.

Could you please help me in solving this issue.

Any reference related to this would also be of great help.

Thanks in advance !!

There are issues with that sequence of statements, the first is calling that a ‘backup’. The issue with that is it’s highly likely that, after the tablespace files are restored, the recovery will fail and the database will be left in an unusable state. The obvious omission is the archivelogs; nowhere in that command sequence is found any statement using tar to copy the archivelogs generated before, during and after that ‘backup’ is completed; apparently the entire script was not posted to the thread so additional steps that script might execute were not available to view. Since no recovery testing is reported (if such a script exists its contents were not presented) it’s very possible that this ‘backup’ is taken on faith, and unfortunately faith isn’t going to be of much help here.

Yet another problem is the lack of any query to determine the actual datafiles associated with the given tablespace; a ‘backup’ missing that important information means that not all required datafiles will be copied, making the tablespace incomplete and Oracle unable to recover it. This again leads to a down database with no hope of opening.

It was suggested several times in the thread that the poster stop using this ‘backup’ and move to RMAN to create dependable, reliable and recoverable backups. Why this method was in use was explained with this post:

I am new to Oracle DB and the guy who worked before me, wrote a (backup) script where he just created a tar of the table space files.

which leads one to wonder how this DBA thought he or she would restore these tablespaces to a useful and usable state should the time come. The poster added:

I want to use RMAN but thought of solving this issuse first (worst case scenario) and then create a new backup script using RMAN.

Honestly this is not the problem that needs to be solved; the problem is generating a reliable backup and RMAN has been proven time and again as the tool for that job. Further discussion lead to the realization that not all files were being sent to tar which explained the size discrepancy but didn’t truly address the recoverability issue. Anyone can take a so-called ‘backup’ using any number of tools and operating system utilities; it’s restoring and recovering from those ‘backups’ that tells the tale of success or failure, and failure in restoring and recovering a production database isn’t an option.

Sometimes you don’t get what you expect.

Create a free website or blog at