LGWR is switching frequently in every 3,4,7 minutes. what should i do

Are you trying to migrate the data between different versions of Oracle?

1. Generally, you can also look at your environmental parameter settings (init.ora parms) and your optimizer statistics after doing data migration.

2. See also Oracle tips for migration click here

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.