Is EXPDP the best backup approach when running Oracle on Windows with no RMAN?

Question

Well, I'm the SQL Server DBA here, and we have a Oracle Senior Dba that manages Oracle Databases and users. I'm curious about how he is handling these servers/users.

What is the best approach, when running oracle, to make backups?

If I'm not mistaken, dumps are not backups. right?

What he does here is:

1)A script create a dynamic query with EXPDP for ALL users and sent it to a TXT file:

SELECT  'EXPDP parfile=export_options.par dumpfile='
        || to_char(sysdate, 'YYYYMMDD_')
        || username
        || '_ServerName.dmp log='
        || to_char(sysdate, 'YYYYMMDD_') 
        || username
        || '_ServerName.log schemas='
        || USERNAME
        || ' directory=dumpdir compression=ALL schemas=' || USERNAME
        FROM dba_users
        where created > '01/01/2005' and username not in (Some users)
        order by created desc

2)then it creates a lot of EXPDPs inside a txt file:

EXPDP parfile=export_options.par dumpfile=20190221_USER_SERVER.dmp log=20190221_USER_SERVER.log schemas=USERdirectory=dumpdir compression=ALL schemas=USER

EXPDP parfile=export_options.par dumpfile=20190221_USER_SERVER.dmp log=20190221_USER_SERVER.log schemas=USERdirectory=dumpdir compression=ALL schemas=USER

EXPDP parfile=export_options.par dumpfile=20190221_USER_SERVER.dmp log=20190221_USER_SERVER.log schemas=USERdirectory=dumpdir compression=ALL schemas=USER

3)a WINDOWS TASK SCHEDULER runs this TXT running EXPDP on all users and sending them do a folder.

Is this something that you guys normally do on Oracle? I would like to now if this is something I could use as a best pratice.

Thanks.

Answer 1

When you install an Oracle database RMAN is installed along with it. This has been the case for many years.

As I work at the Enterprise level backup has a fairly restrictive meaning and an RMAN backup is the only backup solution that Oracle supports. Even third party solutions like Commvault still use RMAN commands to backup the database.

Dump files are very useful for refreshing development but you cannot do a complete restore of a database from a dump file. You would need to install a new database first. See this excellent answer from Phil here

Ultimately the answer is what is required for backups?

• Is there a mean time to recovery?
• A disaster recovery plan?
• SLA level agreements to clients?

If your shop has to answer these kind of questions then best practice is RMAN backups copied to another server with older backups sent off site and aged out after so many days.

Answer 2

Answering your Questions

Question: What is the best approach, when running oracle, to make backups?

First of all backup != dump. The Oracle documentation says a lot in the first paragraph of the section 9.2 Database Backup and Recovery Concepts (Oracle | Help Centre | 2 Day DBA):

To back up your database means to make copies of your data files, control file, and archived redo log files (if your database runs in ARCHIVELOG mode). Restoring a database means copying the physical files that comprise the database from a backup medium, typically disk or tape, to their original or to new locations. Database recovery is the process of updating database files restored from a backup with the changes made to the database after the backup by applying incremental backups and redo logs to the restored files.

A backup is not just the data, but the control file and the redo log files (Similar to the TLOG files in Microsoft SQL Server) too.

In the next section 9.4 Backing Up Your Database ther is the following introduction:

This section describes how to back up your database with Oracle Recovery Manager (RMAN). The Oracle suggested strategy for disk-only backups provides efficient daily backup of the database. This strategy enables you to quickly return your database to its state at any point during the previous 24 hours.

So far the concept of dumping the schema/user as being a "backup" has not been mentioned.

Answer

This means that the best supported approach of backing up an Oracle Database is probably to use RMAN.

---

This partly answers your next question:

Question: If I'm not mistaken, dumps are not backups. right?

Answer

Correct. As seen form an Oracle perspective dumps are not backups.

Oracle's introduction into the Data Pump 1 Overview of Oracle Data Pump utility lists the tool as being:

Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another.

No mention of backup.

---

Question: Is this something that you guys normally do on Oracle?

This probably depends on a lot of factors and varying configurations. Speaking for myself,...

Answer

...I can say that I don't use Data Pump (expdp/impdp) as a way of backing up the database. I use RMAN (Recovery Manager) to backup a consistent state of the database, together with the control file, which will allow me to restore the database and recover the database to a certain point-in-time.

I do use Data Pump as a way of exporting portions of a database to import into a different database.