Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2013
    Posts
    2

    Exclamation Unanswered: Automated, scheduled, unattenden Oracle Dump and Restore

    Hi

    We have a requirement to dump(backup) and backup a oracle 11g database, completely automated, unattended and last but not least scheduled. When this runs both you and me will be asleep, since it will happen during night.

    Dump typically will occur ones a 24hour. Restore maybe 100 times during 24hours! Database needs after restore to be identical, both in terms of relational schema, population, (binary layout), performance and access rights.
    Database size 5-10 GB. Time window for restore = 1-2 minutes.
    Linux(64bit) , Oracle 11g.

    We have three different users accessing the database with different access rights to tables and objects. They needs be copied and usable after the restore too.

    We believe we should use expdp and impdp. But I need much, much more info on syntax etc.

    For example, how is the database made fully accessible after the restore, including for the three users? Also I think there are "unlock" commands needed in regards to the users, which by default is not stored in the database?
    To help visualize script: UserA, UserB, UserC, DatabaseA

    I'm interested in EXACT Oracle command syntax (or list of commands).
    ( I'm not interested hearing ramblings on how this conceptually could work ,or not, or general stories about internals of Oracle that was very complicated to develop, written by alien life forms etc.etc.)

    We do this already for SQL Server for which is very simple, just a BACKUP DATABASE x.mdb ... and RESTORE DATABASE x.mdb and restore takes 30 seconds.

    Thank you ,
    Qa4ever

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Restore from what? (The last backup?). Also are you hoping to keep the databases online during the backups? And what about during the restores?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Jan 2013
    Posts
    2

    Exclamation more info re. requirements.

    The restores, that will be performed about hundred (100) times a day, will be done based on the one and only backup dump made during the night.

    There is no need to keep database open or up while the restore process is ongoing. Neither when the backup is performed.

    It might help to understand the requirements knowing that dbrestore will be used as a completely automated process during daily-build-testing of a server application.

    If restore takes longer time than 2 minutes on Oracle we could/need maybe think using some form of rollbacking to a specific time during the night. Again that roolback will happen 50-100 times a day.

    Whatever solution used/recommended, it is super important that the performance characteristics of the database do not differ before and after the restore.

    Thank you !
    QA4Ever

  4. #4
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Seeing as the 'restores' are being done so often, is it feasible instead for you to apply an image to the server instead of restoring the database?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >If restore takes longer time than 2 minutes on Oracle

    Not possible for any DB larger than a few thousand bytes in total size.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Aug 2012
    Posts
    29
    1) take an daily export using expdp (as you wanted once a day)
    2) create a .sh file having commands to import the database dump previously taken ( i hope you will find out commands to take import on yopur own)
    3) run the .sh script when you want to take it to the previous stage
    4) if you want to unlock users, you can add those commands to the .sh scripts after import command

    Hope this helps !

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Is this second database going to be used in a read only manner or will the users be doing updates?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •