until now I create a daily Logical backup backup of my Oracle 11gR2 Database at midnight while the database is running but the client application is in an idle state so that no queries are executed on the database.
Now I also want to implement a second Backup during the Day while the Database and the Client application are both up and running and queries (select/update/insert/delete) are executed.
Because I have already well tested Backup and Restore Scripts I want to continue using expdp and impdp.
This second "during the day" backup would not be directly imported in the production system after a potential data loss. I would import it on a mirrored test system and then manually use OracleSqlExplorer to query the lost data.
This leads to the following questions:
- If I use expdp to perform a backup during the database runtime is it guaranteed that the database where I take the backup from and SQL statements are executed during the backup process does contain its integrity and consistency ?
- Do I need to add certain Parameters to the expdp command to guarantee consistency ? I found this:
"expdp options for creating a consistent export dump: FLASHBACK_SCN, FLASHBACK_TIME, CONSISTENT=Y"
So far I use this linux shell script:
$ ORACLE_HOME/bin/expdp \"$ USERNAME/$ PASSWORD as sysdba\" SCHEMAS=<csv list of schemas> REUSE_DUMPFILES=Yes DIRECTORY=backup DUMPFILE=$ BACKUP_NAME.dmp
- Can I use a backup create with expdp during the database runtime as a valid source for a impdp without having to worry about integrity and consistency ? For question number 2 I found a thread that says NO