“SAP on Oracle”

1. Close, I think you should be concerned with the redo itself rather than the log switches it is causing.
2. I think this is because fast_start_mttr_target is set to zero, but again this should not be the issue to focus on.
3. Here is the heart of the matter:

Since you can't change the source database to have a materialized view log, every MV refresh has to delete all the records and re-insert them all, which creates your excessive redo. There are many different approaches you could take to this issue. Here are some.

• Simply increase the redo log size. This will decrease the impact of frequent switches, but the redo will still be generated.
• Roll your own Materialized View. This is more work, but would probably generate significantly less redo.
  
  • Your refresh could Truncate the table and do an insert with the Append hint if you can tolerate the absence of data during the refresh.
  • Drop the table and do a CTAS from the source with the append hint is another option that sacrifices availability for speed and reduced redo.
  • If you need the data to be available you could selectively update and insert data based on what actually changed. This would introduce a bit more load on the source database, but would probably decrease your redo significantly.
  • Depending on your data needs you might get away with only handling inserts above the last known sequence number, and/or updates when only certain significant columns have changes. The more custom the solution the less redo, but also the more work to maintain.
• A more drastic approach would be to recover the tablespace involved to your second database periodically, but since you are using XE, that probably isn't an option.

first think about what it costs if the database gets lost. For this there are several levels.

1. lost completely and forever because of lack of backup
2. lost production because you perform a restore without being able to recover last TX.
3. lost production because the site was unavailable

For all scenarios there are solutions. Normally I work for sites that won't allow the loss of any data. Most of the times they also want protection against unplanned downtime. This is the ultimate protection. As most things, it also has a price. Most protection has the highest cost. But if you need this, there will be a business case for it. That is why you should start with the question what dataloss costs your company. This will give you a budget.

For documentation start with DBA Essentials A very nice third party solution for DR can be found here dbvisit standby

Hiring support from a good dba can make a huge difference for your company. A good dba has your business in mind when he/she proposes solutions. Your question a asking a bit much. You need dba support. For an overview pick the Oracle concepts guide. Read it and use it to help you hire a good dba.