Is possible do an online snapshot backup , without archive logs at Oracle Database

The documentation on PITR recovery that comes with pgpool is really incomplete, and downright misleading at points. It suggests keeping all the archive files on the master than then having a recovery_command that uses scp to copy from there to each standby. That's worthless. Each standby needs to have its own copy of the archive, or there's no real redundancy there. If the master fails in that situation, you have nothing useful. The archive_command on the master should distribute files to the standby systems instead. There is no need to save a copy of the archive on the master at all if you do that.

For cleaning up the archives on each standby, the poor recommendation for the recovery_command given there shouldn't be followed either. Instead pg_standby should be called as the recovery command. See the real documentation on Warm Standby Servers for more details. If you're using pg_standby, one of the things it should gets passed is the "%r" parameter, which is the information needed to remove old archive logs. Use pg_standby that way and it will clean up the archives for you.

The main non-trivial part here is writing an archive_command script that properly copies data to both standby servers. I don't have a good example to point at, but what you need to do is check for errors after each copy, and provide a success return code only if both copies happen.

To begin with, storing ibdata on a separate volume from the datadir is a bad idea because of

• MetaData Issues : I wrote about this as well
  • Why does InnoDB store all databases in one file? (See WARNING)
  • How to Recover an InnoDB table whose files were moved around
  • Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql
• Performance Issues : I wrote about that back in August 2011
  • MySQL InnoDB data file directly on a disk slice (fixed-size)?

Getting back to your problem, the reason LVM for ibdata1 is not good has to do with the metadata in ibdata1 at any given point-in-time. If you were to LVM Snapshot ibdata1, then create new tables, and drop some tables, somne of the corresponding .frm files within datadir would not match. The time needed to coalesce that large an ibdata would be astronomical (unless 21 hours is acceptable to you). I tried using rsync as opposed to LVM and I fared no better.

The safest and most sensible way to clear the space with innodb_file_per_table disabled is to do the following on the Slave

• Step 01) mysqldump all databases
• Step 02) shutdown mysql
• Step 03) delete ibdata1
• Step 04) startup mysql
• Step 05) reload the mysqldump

Without this method, ibdata1 can never be shrunk otherwise. Even in my post in StackOverflow on InnoDB Cleanup (using innodb_file_per_table), you have to do those same 5 steps.