Thanks Tom. What do you mean by moving pieces, could you explain more? Besides the redo log files, are there other pieces?

Is the logical standby less stable and prone to errors when I don't use the unsupported data types or skip those not supported in replication ?

Is the underlying mechanism the same as stream in logical standby replication?

We are choosing DR solution with reporting ability, seems physical standby is better, then we need one pair of servers to be configured using physical, and the other pair using logical standby for reporting. So need more licenses.

Tom,

What are your thoughts on using downstream change data capture to build a datamart instead of logical dataguard?

https://asktom.oracle.com/Misc/oramag/on-constraints-case-and-cursors.html

You can take backup off the physical standby database and never on the primary. In case of a failure these backups can be applied to the primary. This saves a lot (depending the size to be backed up) of CPU cycles on the primary. Logical standby can't do that.

Hi Tom,

Thanks for the great site and great answers to the questions.

When setting up dataguard, what network ports are needed sqlnet(e.g. 1521 port)? and is it possible to have one way connection from production to DR site suppose I don't need production becomes DR when DR is activated?

To reduce the workload on the OLTP instance, we are planning to have a separate instance for reporting purpose. Data from OLTP instance will be replicated to reporting database using physical standby(11g feature). My questions are

1) Is it possible to create any additional indexes, materialized views on reporting instance(physical standby db –oracle 11g)?

2) If we delete the data (drop partitions) from OLTP database, will it delete from the same from the reporting instance(in physical standby database)?

We have RAC oracle database 11g and we don't want to overburden the OLTP instance. So we thought of bringing the changed data from the OLTP instance to reporting instance via logical standby or streams and log the changes with the help of using total recall feature in the reporting instance.
Our idea is to retain the historical data using the total recall feature in reporting instance and bring the changed data from the OLTP instance to reporting instance via logical standby or streams
RAC database: OLTP instance ---> mine the changed data via logical standby or streams ---reporting instance (track all the changes made to a transaction using total recall feature in this instance)

My doubts are:-

1) Whether the total recall feature will work with work with logical standby database or streams?

2) How to transport the total recall feature enabled tables from one database to another. Whether the data pump will work with flashback enabled tables?

3) How to capture the changes from one instance to another with oracle streams. I could not find much documentation on how the streams work on RAC database. Any pointer /suggestion on this would be much appreciated.

If you have any other solution, please share with me.

Thanks a lot for your wonderful service to the oracle community.

Much impressed by your crisp answers. Thanks for clarifying my doubts.

Is there any way to keep the history of changes (using total recall feature) in another instance without using oracle streams? Basically, we are trying to isolate the OLTP from the history database. Please shed some light on this.

Hi Tom,

in 11g , with physical standby whilst no writes are permitted against real tables, can we write to global temporary tables at all?

I had to check to failover from primary to physical standby database as a drill in real environment, for that i planned to have dowtime and open the standby database in read/write mode after shutting the production database , i configured the flasahback at standby database and opened the standby database after saving restore point , but when i tried to revert the standby database at that restore point it gave me a error.


SQL> flashback database to restore point before_open_standby
  2  /
flashback database to restore point before_open_standby
*
ERROR at line 1:
ORA-38769: FLASHBACK DATABASE failed after modifying data.
ORA-27103: internal error
Additional information: 27129
Additional information: -2
Additional information: 3

Following are the stpes which i followed to failover at standby database after clean shutting down of production database.



SQL> alter database recover managed standby database cancel
  2  /

Database altered.

SQL> select open_mode from v$database
  2  /

OPEN_MODE
------------------------------
MOUNTED

SQL> alter database flashback on
  2  /

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session
  2  /

Database altered.

SQL> alter database recover managed standby database cancel
  2  /

Database altered.

SQL> create restore point before_open_standby guarantee flashback database
  2  /

Restore point created.

SQL> column SCN format 999999999999999999999
SQL> select scn,storage_size,time,name from v$restore_point
  2  /

          SCN STORAGE_SIZE TIME    NAME
------------- ------------   ---------------------------------- ----
5970295936140     16613376   13-NOV-11 01.01.39.000000000 PM BEFORE_OPEN_STANDBY

SQL> alter database activate standby database
  2  /

Database altered.

SQL> startup mount force
ORACLE instance started.

Total System Global Area 1.1476E+10 bytes
Fixed Size                  1263428 bytes
Variable Size            2868906172 bytes
Database Buffers         8589934592 bytes
Redo Buffers               15511552 bytes
Database mounted.

SQL> alter database set standby database to maximize performance
  2  /

Database altered.

SQL> alter database open
  2  /

Database altered.

After that one of our user connected to application EBS R12 which is also configured for the that standby database and he entered some records and commit.after that i tried to revert back standby database to that restore point which i captured before opening of standby database as primary.

SQL> flashback database to restore point before_open_standby
  2  /
flashback database to restore point before_open_standby
*
ERROR at line 1:
ORA-38769: FLASHBACK DATABASE failed after modifying data.
ORA-27103: internal error
Additional information: 27129
Additional information: -2
Additional information: 3

I confused and did not get any solution for that on that time for that i had to recreated the standby database again from the very scratch.Please let me know what i did wrong and why it was not reverted to its restore point.

Hello,

If the application performs complete or fast refresh of materialized view on primary database, what are the actions that take place on physical standby database?

We have noticed that when MV refresh happens, the standby gets delayed in applying log files. We haven't proven this effect by relating to MV, but I want to first understand the concepts here.

Appreciate your help.

Hello,

You are correct. During MV refresh, we are seeing 6-7 times more redo generated. We are suspecting this could be the cause for slowness in apply process on standby. We have ruled out any network latency while shipping redo files. However, our redo log file size is 250MB. Logfile switch is happening every minute (some times even less). Could increasing the logfile size helps in addressing this problem?

Thanks,

Looking at the present situation, standby probably doesn't have the ability to catch up when there are lot of redo files.

About my other question - Would increasing size of redo files to reduce number of log switches help?

Thanks,

In continuation to my previous question, does increasing the capacity by adding a node to primary and standby help in addressing this problem? We increased the redo log files to 700MB (almost 3 times than the size before) and we still see gap in apply rate.

Thanks,

Thank you. It makes lot of sense now.

But if my redo generation spikes up on some part of day, what can we do to make sure the standby still keeps up with primary?

We tried working on parameters on the standby database like parallel_execution_message_size (set to 32k), recovery_parallelism set to 32, parallel_max_servers was increased to 64 (was 32 before. Ours is 16 CPU box, 120GB memory), increased the buffer_cache to 32G (it was 24G before). Any other parameters that can be used to tune this?

We write data synchronously to disks due to OS/filesystem limitation we have (Veritas on HP-UX doesn't support asynchronous IO).

Thanks,

For the reader above. What about your log shipping time? Does that lag behind as well causing a knock on affect to the apply rate?

If you're shipping is late then is it possible to increase bandwidth?

If not and everything is being shipped in a reasonable time, then is it a problem if there's a delay applying? The data is safe and at your standby. If a disaster happened then by the time you wanted to switch standby, it might have already caught up and applied.

just a thought. cheers
Keir

Hello,

When the amount of redo on primary increases by more than 70GB per hour we start seeing delay in apply. Shipping redo files to standby database server is fine as we observed information in alert.log (no network latency). The only time we see delay is when the amount of redo increases on primary. I have put the sample information from v$archived_log. You notice here the size of redo varies.

The apply lag would normally be 1 second and it starts going up and I have seen going up to 2 hours. The active apply rate when there is high amount of redo to be applied is around 20MB/sec (which is 72GB per hour).

COMPLETION_TIM LOGSWITCHES TOTAL_SIZE_IN_GIG
-------------------- ----------- -----------------

14-jul-2012 13:00:00 121 73.82
14-jul-2012 14:00:00 299 175.07
14-jul-2012 15:00:00 232 134.08
14-jul-2012 16:00:00 52 27.67
14-jul-2012 17:00:00 26 11.47
14-jul-2012 19:00:00 4 1.65
14-jul-2012 21:00:00 2 1.11
15-jul-2012 00:00:00 48 21.44
15-jul-2012 02:00:00 8 .27
15-jul-2012 13:00:00 4 .91
16-jul-2012 02:00:00 4 .77

Here are the some information that I extracted about apply lag:
NAME VALUE
-------------------------------- -------------
apply lag +00 00:00:19
apply lag +00 00:00:19
apply lag +00 00:07:34
apply lag +00 00:19:07
apply lag +00 00:31:28
apply lag +00 00:42:39
apply lag +00 00:53:03
apply lag +00 01:03:35
apply lag +00 01:14:29
apply lag +00 01:26:51
apply lag +00 01:36:58
apply lag +00 01:46:50
apply lag +00 01:57:00
apply lag +00 02:04:06
apply lag +00 02:03:35
apply lag +00 01:47:48
apply lag +00 01:05:40
apply lag +00 00:05:34

Thanks,

Yes. The OS and Storage team didn't see any disk latency during the high volume apply time. The storage of primary and standby shares the similar architecture and hardware.

If there is a IO issue on standby, what would be the wait events if we run statspack?

Thanks,

Hi Tom, As this is a post on logical and physical standby databases, i have few questions on their usage for upgrading and migrating 10g databases. We have few 10gR2 databases, few 11gR1 and few 11gR2 databases. We need to upgrade 10gR2 and 11gR1 databases to 11gR2 and migrate to a new hardware/server and 11gR2 databases to migrate as-is to new servers. I was planning to use physical standby for this but instead I have been reading a lot about using logical standby for such a upgrade.
1. Why can't i use a physical standby for such a migration because physical standby is an exact copy of my primary database. The only documentation i found is about using SQL Apply for database upgrades/migration.
2. Can we upgrade between different versions using logical standby or physical standby? I believe moving existing databases as-is without upgrade is possible using physical standby also, isn't it?
3. Can't i just use this strategy for existing 11gR1 and 11gR2 databases - create a physical standby on new server with desired oracle version, ensure both primary and standby are in synch, switch over to the standby and do a role transition.

Will really appreciate your prompt response sir.

Thanks
Rohit

@Rohit

Physical stby has certain limitations like both has to be in same version, so we can't use for upgradation process.

Logical stby does not replicate changes in sys/system schemas.
This supports cross oracle version replication and an be used in upgradation.
Currently we are following same approach to upgrade 10gr2 to 11gr2.

Thanks for the clarification Tom

@Shyamakanta - We are actually planning to do some 10gR2 to 11gR2 ugrades and migration from AIX to SUSE Linux platform. Would you be able to share your experience/planning regarding the changes necessary at the DB code level and app code level w.r.t 10g to 11g upgrade? Since this thread is not meant for upgrades/migrations, can you please drop me a mail at rohit.gupta04@gmail.com where i can follow up with you on some specific questions