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

oracle-12c

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.

Best Answer

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.

Related Solutions

Oracle 12c RMAN – How to Backup and Restore with RMAN Oracle 12c

This is absolutely normal when you try to perform a complete recovery from a hot backup, because that is how hot backups work, you will never have the latest changes in your backups.

Your backup includes archivelogs up to sequence 29. The next changes were in log sequence 30, but log sequence 30 became the current redo log at the time your backup was finished, so you do not have any backup of that. This is not a problem, this is how a hot backups work, you will never have backups of the latest log sequence. Obviously, the next hot backup will back up that, but by the end of that backup, another sequence becomes the latest sequence.

If you simply try recover database;, RMAN tries to recover the database to the most current state, and it does not know where to stop (except when you have not only the archivelogs, but the redo logfiles also). Your last log sequence in the backup is 29, RMAN will complain about the next one, because it does not know where to stop.

If you want to avoid the above error, you can explicitly specify RMAN where to stop (perform a point-in-time recovery). For example:

recover database until sequence 30;

This would have had the same result as your recover command, without throwing an error. You can also specify UNTIL SCN and UNTIL TIME.

30 because:

When specifying log sequences, if the last created archived redo log has sequence n, then specify UNTIL SEQUENCE n+1 so that RMAN will apply n and then stop.

Or, you can just ignore this error and continue with alter database open resetlogs.

Creating Auxiliary Instance with RMAN DUPLICATE Without Target or Catalog

That is the typical error message you get on Windows without properly setting up the environment.

While the steps in the manual may work flawlessly on Linux/UNIX, on Windows you need extra steps. On Windows, you need to create a service with oradim, start it (if it was not started), and after that you can use sqlplus / as sysdba.

The bare minimum is:

set ORACLE_HOME=...
set PATH=%ORACLE_HOME%\bin;%PATH%
set ORACLE_SID=test

oradim -new -sid test

This should automatically start the service, but if it did not:

sc start Oracleservicetest

And after the above, you can login to the idle instance with:

sqlplus / as sysdba

Best Answer

Related Solutions

Oracle 12c RMAN – How to Backup and Restore with RMAN Oracle 12c

Creating Auxiliary Instance with RMAN DUPLICATE Without Target or Catalog

Related Question