Hi All,

We are in a planning phase to do setup for Oracle database replication at our one the group company.

Currently it is Oracle 8.0.5 but can be migrate to 9i, if it so required.

We have following choices. I know, what and why it is for. Just curious about your views on reliability in terms of BaaN.

1. Standby Database Manual.
2. Managed Standby Database.
3. Data Guard (Available 8.1.7 onwards)
4. Advance Replication (8.1.7 onwards)
5. Oracle Streams (9.2 onwards)
6. Real Application Cluster

7. Any other, if you can suggest.

Thanks in advace.

Regards,

-Suhas
(Oracle DBA)

Hi Suhas,

you have to clearlky seperate the different technologies available in Oracle and see, what they were intended for.

Some are just variations and marketing for the same thing, like managed Standby and Data Guard. But some just have different Objectives, like Real Application Cluster and Oracle Streams.

So what do you want to achieve with your replication?

Regards

Markus


P.S: I have some experienc with these technologies and if you want a general advise: Stay away from logical standby. It just sucks, even in Oracle 10. The problems are to many to list them here.

Hi Markus,

Thanks for the valuable input.

I want to achive high-availability & data-security. So that, if something goes down to Production environment the fail-safe has to perform.

I am more curious, what other things need to be sync i.e. DD's of BaaN Application,etc. or replicating data is enough.

Yes, I too have some experience of logical standby, seems not up to the mark.

We have concluded something but stucked between Data Guard & Real Cluster. Can you please help?

Regards,

-Suhas

Hi Suhas,

On a Unix platform, I would advise to you a cluster solution, like MC/SG from HP-Ux or the equivalent from IBM. But you seem to be on a Windows platform, which is a strange starting point for any high available setup. :-)

Anyway:

From an oracle perspective you could achieve the high availibility both with RAC and also with a standby DB. Wether the standby is managed (by Data Guard) or manual, does not really matter.

From a Baan perspective you could just duplicate your Baan environment, by copying it regularly. After initial setup, you would at least need to copy the dict, the application and the tools directory. Maybe more, depending on your admin procedures.

But one thing you would need to do for sure: You need to validate your standby server, so that in case of a desaster and failover you can actually run a licence daemon there. Baan has indeed a special HA and network validation for this case, so that you can run two licence daemon on two seperate servers without interference on a single license.

Regards

Markus

Okay, Markus,

Thanks again for your valuable inputs.

Regards,

-Suhas

Question - why do you say that the Oracle Logical Standby sucks? I have used it in the past (unfortunately I am stuck on 8.1.7.4 for the foreseeable future) and I have found it to be very good for multiple reasons:

1) Able to get extremely close to point of failure with the absolutely minimum of down time
2) Able to wrap another application around the standby to use the data rather than have it be a resource hog without an ROI
3) Quick swap the Production and Standby
4) Doesn't cost like RAC or Streams

Thanks for your input as I am new to the BaaN Application (formerly used Oracle Applications) and was planning on pushing for an upgrade to 10g (which I have a development instance working just fine) and Logical Standby databases.

Hi,

Sucks in the sence, lot of archive logs management, interval time for apply recovery and physical involvement of DBA.

Also if failure happened, BaaN keeps the objects in the form of file. So you need to apply it.

I am curious, how you managed it, if it not sucks to you.

Regards,

-Suhas

Thanks for the reply, Suhas. I usually have no problem with managing files since I script all that work using KORN shell, PHP and BAT files or various combinations. The BAT files are because I am in a Windows shop now and I have to use some DOS (although PHP minimizes that).

I can get my BaaN files reset in less than 15 minutes using XCOPY /D which will copy only files that have changed. Saves a lot of time.

As far as the Logical Standby. In 9.2, it is quite useful for reporting and data warehouse and allows the Production database to perform without the stress of all the SELECT statements for the report/warehouse requests. I found an improvement of about 25% getting the reporting/warehouse off the Production instance. With the Logical, it is usually within minutes of real time. This is good enough for all my reporting and about 90% of warehouse requirements. The rest of the requirements can be run in Production if needed, but I frown on that - and since I have the passwords, they usually let me have my way! :D

By the way, I can do almost the same with a physical standby, but the difference between the standby and real time can vary from an hour or so to about a day, depending on how often I break to apply archivelogs. (I run 100M redo logs and can generate archivelogs at ~1 a minute during peak processing).

Let me know what your experience has been.


Thanx

What kind of License does Oracle require you to have?

If you have a database less than 4G, it is free using Oracle XE, otherwise it depends on number of CPUs in your server (although that can be subject to change. Check with Oracle for pricing). Also you can choose different flavors of Oracle - Enterprise, Standard, etc. which gives you different functionality/scalability.

If you are just going to play (non-Production), then there is no paid for license (sort of a GPL) since you can download the binaries for free with no restrictions.

Generally speaking, the database used with BaaN is sort of immaterial since BaaN doesn't use any of the really good features of any of the databases (the whole platform/database independent stuff - ridiculous IMHO, but I guess that is important to some). ;)

It all depends on what you need - especially how critical your requirement is.

We have the Enterprise Edition with a 400 gig Database and use concurrent user licenses (old agreement) but Oracle wants us to license another server to do it. All I want to do is copy redo logs to another server and if we have a problem switch over to it after starting it up and applying those logs.

Do you need to have a really quick recovery or can you live with a couple hours? If you have some time, then I would save the archivelogs to a SAN, daily hot backup datafiles, controlfiles, init file and redo logs to the SAN and transfer over to the new Production server if needed. The SAN would be the common ground for the files no matter which server the binaries reside on.

Basically, if you need to fully recover to a new server, you have the binaries installed on the new server and connect it to the SAN, modify a controlfile saved to trace and create a new, but up to real time, instance with the same database name. Now you have about a 1 hour downtime and you haven't had to move a thing. If you repair on the old server, you would be down for the repair period only. The license covers the actual running database, not the extraneous files that are generated or housed elsewhere. (multiplexed archivelogs for example would not require the server they are housed on to be licensed since it has no running instance).

I use a SAN and it makes things a lot easier. Some shops don't even use Archivelog mode since the MTBF of the redundant disk arrays is so long it makes the risk quite small, but that is up to the shop - I personally would not use that approach, but there are some big users out there that do.

Hope I didn't go off on a tangent and was of some small help.

That is good info. We don't use archivelog mode either. I do a full cold backup every night and generally don't roll thru all the redo logs until the weekend when Enterprise Planning runs. The only problem with using a SAN is that it might be part of the disaster. The other server I'm talking about is 100 miles away at a hardened facility. I could ship a backup tape to them once a week and turn on Archiving and copy those logs. Suppose I copied all the Oracle files to the other server and any Baan patches or mods were done to both (I have a single Baan user license to the other box) before hand, what would I have to do to start up the other machine with all the Baan licenses available?

Well, in the first place, if you are not in ARCHIVELOG mode, you are running a risk that you will not be able to recover to point-in-time of the problem. Even if you do a Cold backup every evening, that would mean you are one day plus backup time off of real time (worse case unless of course your cold backup is no good).

What I would suggest is go to ARCHIVELOG mode, multiplex your archivelog files to the local server, the hardened server (via TCP) and another location. Do a Hot backup every evening at the same time and a Cold backup on the weekend. Backup the files to your local server and then FTP or SSH them to the remote site.

Note that you are able to recover only to your most current archivelog plus the redo logs if they are still available.

You should be able to multiplex to the remote site if you can telnet/ftp/ssh to it. The archivelog will automatically write to the site if you have properly set it up in the initialization (init.ora) file.

If you need to recover, you copy the Hot backup files, archivelogs, init.ora and a copy of the controlfile that you write with the backups to the new server location and recreate the database and apply archivelogs until the database is synchronized.

That is a brief overview to a complex subject, but it should get you started. The BaaN part is easy - copy the files nightly to the hardened site. Now you should be less than one day out with BaaN.

Don't forget to test your backup/recovery setup at least monthly. I usually do this by having a TEST system that I overlay (clone, copy) at least every 6 weeks. That is the environment I use to test scripts and other nasty little programs that could adversly affect my Production system (which NEVER goes down! well at least not very often :D ).

If you need to recover, you copy the Hot backup files, archivelogs, init.ora and a copy of the controlfile that you write with the backups to the new server location and recreate the database and apply archivelogs until the database is synchronized.
:D ).


What do you mean by Hot backup files?

Cold backup = offline backup; Hot backup = online backup ; Hot backup files = online backup files (when you put a tablespace in begin backup mode, the corresponding tablespace datafiles)

Hotbackup files are generated when you do an online backup of the tablespaces in the Oracle instance.

First the database must be in Archivelog mode. When you first put the database into archivelog mode, you need to modify your init*.ora and enter the parameter for the archiving process (see MetaLink or your Oracle tech manuals for the process).

During the backup process, Oracle puts the tablespace into backup mode and copies the datafiles to another location that you have selected with the archive destination parameter in the init*.ora.

These HotBackup files can recover a database to point in time in the event of failure. Please note that you should also backup the Controlfile, init*.ora and redologs. Also make sure that you have a method of housekeeping the archivelog files as these will accumulate quickly, depending on the size of your redologs.

The Hotbackup process is complicated, but well worth the effort if you ever have to recover your database to the time that the failure occurred. If you are in Cold backup mode (nonArchivelog mode) then you can only recover to the time of your last SUCCESSFUL cold backup.

Hope I answered your question - check out the Backup/Recovery sections of your Oracle documentation.

Tuple, Thanks for the info.

I think there is much difference in your setup and mine.

We have 12 companies on one server working in 24/7 operations.

We can not afford 15 minuts dataloss. I am curious, Is your management agree to loose 14 min. data loss? I don't think, may be you have only one company.

If you change time from 15 min to 1 min, what about disk I/O? Does it work? Again what will happen for 59 sec. data?

You are saying that your redo logs/ arc file size is 100M. Suppose it used 90MB in 14 min. and database crashed. You have to loose 90MB data and you know 90MB means how much.;)

regards,

-Suhas

These are a copy of your datafiles that are created by using an online backup in Oracle.

alter tablespace {TABLESPACE_NAME} begin backup;

after the backup is finished:

alter tablespace {TABLESPACE_NAME} end backup;

Please note that these files are dependent on the Archivelogs you are saving. If you miss any of the Archivelogs or lose your current REDOLOGS, you can't recover to point-in-time of failure.

Again, this is a very involved subject and you can easily research it by doing a search for "hotbackup of Oracle database" in Google. This probably is not consistent with this thread, so I appreciate the Monitor's patience.

We run Oracle 9i on Windows in archive mode. I have always used Oracle's Rman utility to backup my system. Rman is Oracle's Hot Backup Utility and handles everything. I use Rman to backup to a physical file on a disk. Once on disk, my tape backup system pick's up the files allong with the archive log files on to tape every night.

Right now I have about 45GB in Oracle and Rman back's that up in 30 Minutes.

Last year I had a corruption in my Baan tablespace (not indexes). I was able to restore the tablespace using the Rman files allong with the archive files. The system works.

Below is the script that drives rman.

R:\OraBackup>type create_script.rcv
replace script Baan_backup {
allocate channel c1 type disk;
backup incremental level=0
format 'F:\Rman_bck\dF_%d_%s_%p.bck'
tag = Backup01
filesperset = 4
(Database include current controlfile);
release channel c1;
}

Roy