1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Large database backup

Discussion in 'Security, Backup and Recovery' started by chainsaw, May 26, 2016.

  1. chainsaw

    chainsaw Newly Initiated

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    FL
    We have a somewhat large database server (about 8TB across 5 databases) with very high activity (125 log switches per hour per database). We are looking to reduce our recovery time and the large number of archive logs that would need to be applied takes too much time. What is the best solution for quickly recovering large Oracle databases with high activity (CDP solutions? Disk array snapshots? Oracle Flashback? Oracle Data Gard?) Are there other types of solutions?
     
    Last edited: May 27, 2016
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You report 2000 to 3000 log switches per hour for all 5 databases but how often will you need to restore and recover all 5? Log file size plays a part in the frequency of log switches, so one avenue of reducing number of archive logs generated is to increase the size of the log groups. Of course this doesn't decrease the volume of transactions that may need to be recovered but it does save time switching logs during recovery. Another question is how do you determine what 'too much time' is? Recovery usually is a fairly swift process; I have regularly cloned a 9 TB database using 4 GB archivelogs and the recovery time was small compared to the database restoration. Again, this was for one database; you have 5 totaling a bit less than the one database I cloned so the recovery can occur in parallel and require much less time.

    You really need to provide more detail if you want a usable answer to your question. Until then every 'answer' you get will be nothing better than speculation.
     
  3. chainsaw

    chainsaw Newly Initiated

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    FL
    I'm really looking for high-level approaches to backing up an environment like this. In designing the solution, we would look at a worst-case scenario of a full restore of all data. We are trying to improve the RTO beyond what we are doing today with an RMAN hot backup which takes about 6 hours to lay down the database and 25 hours to restore and recover 2 days worth of archive logs. FWIW the log file size is 4GB.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Using SAN snapshots might be an option; you won't know if those are applicable and usable until you try the approach and measure the time required to complete the restore. Any 'high-level' approach is, again, speculation until you actually put it into practice and measure the results. You still have not provided enough information for anyone to offer any usable option.

    You need to provide more detail than you have; if that's not possible then I doubt anyone here can give you any usable assistance.
     
  5. chainsaw

    chainsaw Newly Initiated

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    FL
    We would do a POC to test the solution. I can provide more details. I'm not sure what you are looking for.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You haven't stated:

    * Transaction load per database on this server (you report there are 5)
    * Storage capacity and medium (SAN,NAS, local disk, SSD)
    * Server configuration (CPU, RAM)
    * Server load (sessions, processes, traffic patterns)
    * Database sessions per database
    * Total file size for each database
    * Database storage (file system, raw devices, ASM)
    * What you consider a reasonable execution time for the backups and for the recovery
    * Likelihood of having to restore and recover all 5 databases at the same time
    * Whether or not database compression is in use and what type of compression that may be
    * Log switch rate per database -- 200o to 3000 across all 5 tells us nothing of use with respect to backup and recovery
    * Operating system (*NIX, Windows, whether or not this is a virtual configuration)

    All of the above determines how you might 'best' configure your backup and recovery solution.
     
  7. chainsaw

    chainsaw Newly Initiated

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    FL
    * Transaction load per database on this server (you report there are 5)
    Each database is about the same load:
    Core DB Read IOPS Peak 10K
    Core DB Read IOPS 95th 8K
    Core DB Read IOPS Avg 3K

    Core DB Write IOPS Peak 2.5K
    Core DB Write IOPS 95th 2.2K
    Core DB Write IOPS Avg 1K

    * Storage capacity and medium (SAN,NAS, local disk, SSD)
    27TB SAN

    * Server configuration (CPU, RAM)
    Oracle/Sun M9000 ; 20CPU / 80 cores; 640GB RAM

    * Server load (sessions, processes, traffic patterns)
    Daily batch load

    * Database sessions per database
    Around 3500 on each database.

    * Total file size for each database
    About 1.8TB each

    * Database storage (file system, raw devices, ASM)
    ASM

    * What you consider a reasonable execution time for the backups and for the recovery
    Backup <6hrs; Recovery as short as possible/feasible (cost will be a limiting factor)

    * Likelihood of having to restore and recover all 5 databases at the same time
    Solution will be designed for this scenario

    * Whether or not database compression is in use and what type of compression that may be
    No compression

    * Log switch rate per database -- 200o to 3000 across all 5 tells us nothing of use with respect to backup and recovery
    25/Hr per database (2000-3000 logs switches per day)

    * Operating system (*NIX, Windows, whether or not this is a virtual configuration)
    Solaris 10 (Physical)