1

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.

|improve this question
  • Is this something that you guys normally do on Oracle? NOT as a Backup strategy. We use exports to move schema from prod to stage for example. But oracle backup is a different topic (RMAn). – MarcoP Feb 21 '19 at 14:35
  • I don't call a dump a backup but a copy, while my colleague uses the term "logical backup". But some important questions are: How many databases do you have? How big are they? Do you need point-in-time recovery, or can you live with only being able to restart to the time of the last dump? As @kevinsky mentions in his answer, RMAN is included, and it's pretty easy to setup, so, IMHO, you're kinda silly not to use it. – Colin 't Hart Feb 26 '19 at 18:53
0

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.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.