New to TDP

[Raakin]

I am about to show my ignorance on this subject.
I am new to TDP for Oracle. I have downloaded the Red book and currenlty reading and rereading it.

In some discusion with the Oracle DBA and as I was explaing how the TDP makes use of RMAN and he asked if it was possible to have TSM backup Oracle while the database is online without using RMAN. Is this a possibility?

Are there other options to backup Oracle with TSM (I assume if the database needs to remain online TDP is the only option)?
What are the pros and cons of other methods?

Details:
TSM Server 5.4.x.x
TSM Client 5.5.2
TDP for Oracle 5.2 (Link to TDP)
Oracle database bits size 64

The AIX Admin has installed the TDP for Oracle as well.

 

[Mita201]

No, there is no other possibility. It has to be done using RMAN.

 

[heada]

Yes, it *is* possible to do it without TDP and RMAN but it takes a good amount of scripting and a good DBA. Not recommended but we backup a few hundred Oracle DBs online without TDPO every day.

-Aaron

 

[Raakin]

takes a good amount of scripting and a good DBA.

Aaron, our DBA is amazing. I would like to further explore this option. Is there any documentation you can lead me to? I would like to see what it takes and then allow my DBA to make the call.

 

[heada]

I know of no documentation except what a DBA would find by Oracle themselves but they need to script the process of gathering the list of all the tables, what files are in each table and then for each table: update the table to be in backup mode, backup the files for that table and once done with the backup update the table to no longer be in backup mode all while checking for errors. The DB must be in archive-log mode and once the backup is done, you have to backup all the arch-logs.

You also need to know how to recover from a database that was backed up online with archlogs.

Like I said, it is possible but *highly* not recommended for someone that doesn't know how it works.

-Aaron

 

[Mita201]

What is the point? Where is TSM in this story?

 

[heada]

The point is that with just the B/A client, you can perform TDP-O functions and without RMAN, another database to support. It is normally not as fast (no compression) but it is easier to setup and support if you have a good DBA. We have been doing this since ADSM 2.1 days (1995 or so) way before TDP-O.

How much money could you save by backing up 300 Oracle instances without paying for the TDP-O licenses?

-Aaron

 

[Raakin]

I was going to mention the cost as a very valid point.

Back to the main topic...

We have previously set up the DB to be in archive-log mode but the issue we ran into was after scripting TSM to put the DB into hot backup mode the DB was still being updated and thus a DB file was backed up four times potentially prior to failing. How do you get around that issue of having the DB online in hot backup mode (I noticed you the term 'backup mode' and I am assuming you are referring to hot backup mode) with the DB files still updating? I mean one could use a Management Class of DYNAMIC or SHRDYNAMIC but that gives a fuzzy backup. Does Oracle know how to deal with the fuzzy backup or is there a means to have the DB file not change and any change go to the Archive-log while that is backed up?

Also, to make sure we are comparing apples to apples, the oracle DB that we would back up is just over 3TB in size. Is your process DB size dependant?

 

[Mita201]

The point is that with just the B/A client, you can perform TDP-O functions and without RMAN, another database to support. It is normally not as fast (no compression) but it is easier to setup and support if you have a good DBA. We have been doing this since ADSM 2.1 days (1995 or so) way before TDP-O.

How much money could you save by backing up 300 Oracle instances without paying for the TDP-O licenses?

-Aaron

Well, in that case, you can backup anything without TDP (Oracle, DB2, MSSQL, Exchange, Domino) and backup resulting dump using BA client, but you have "two step restore" in that case. Or even better, you can back it up to fs, and then tar it directly to tape, without TSM, which will make you free of any TSM licenses. I understood question as:
I have already installed/purchased TSM, TDP-O, can I do Oracle backup utilising TDP-O or just TSM, online, and I do not like RMAN. So, money savings at the moment is probably not an option.

 

[chad_small]

Is the DB mirrored? We do this with some very large DB's that are part of EMC BCV's. Basically it's a split mirror, flat file backup. It works and can be quite fast depending on how you back it up. You can either do it file level or Image level, depending on what works best for you.

 

[Raakin]

Well, there will be a few DB's to be backed up, the largest is 3 TB. I believe all of them are mirrored. I know for certain that the largest DB had two mirrors on it, and we would break one mirror for the TSM backup... that worked fine but now we have a standby DB for that one and they want it backed up off of the standby. I do not think going back to the double mirror is an option.

I should note that I am currently testing all of this on a dev DB that is much smaller, around 250GB to 300GB.

It was decided to stop having the double mirror and just backup from the standbys. Trying to perfect the one process and have that be the standard backup procedure for all the DBs.

 

[heada]

I was going to mention the cost as a very valid point.

Back to the main topic...

We have previously set up the DB to be in archive-log mode but the issue we ran into was after scripting TSM to put the DB into hot backup mode the DB was still being updated and thus a DB file was backed up four times potentially prior to failing. How do you get around that issue of having the DB online in hot backup mode (I noticed you the term 'backup mode' and I am assuming you are referring to hot backup mode) with the DB files still updating? I mean one could use a Management Class of DYNAMIC or SHRDYNAMIC but that gives a fuzzy backup. Does Oracle know how to deal with the fuzzy backup or is there a means to have the DB file not change and any change go to the Archive-log while that is backed up?

Also, to make sure we are comparing apples to apples, the oracle DB that we would back up is just over 3TB in size. Is your process DB size dependant?

It isn't size dependent. It works on DBs from 2GB to 2TB (our range for non-SAP oracles) How exactly it works I'm not sure since I didn't set it up, the DBAs did. I know we do archives since it is a full each time and that is the easiest way to keep versioning (35 day retention = 35 versions)

If you've already purchased TDP-O, by all means, use it rather than trying to roll your own like we have. It'll be supported and much easier in the long run. If we didn't already have these scripts in place, we would be using TDP-O for all our databases.

-Aaron

 

[heada]

We also have a few Oracle hot-standby databases. For these, we configure the TNSListner to ship archlogs across the network and apply them to the stand-by database. For backups, we shutdown the database and back it up off-line. While this is going on, we rsync the archlogs to keep them in sync on the stand-by system and apply all the archlogs when the database starts up.

Again, the DBAs set all this up and know exactly how it works. I just know the TSM part of it (and the rsync, since I did that too).

-Aaron

 

[heada]

Well, in that case, you can backup anything without TDP (Oracle, DB2, MSSQL, Exchange, Domino) and backup resulting dump using BA client, but you have "two step restore" in that case. Or even better, you can back it up to fs, and then tar it directly to tape, without TSM, which will make you free of any TSM licenses. I understood question as:
I have already installed/purchased TSM, TDP-O, can I do Oracle backup utilising TDP-O or just TSM, online, and I do not like RMAN. So, money savings at the moment is probably not an option.

In my process, there is no dump needed. I backup (archive actually) directly to TSM. The only thing needed is the B/A client. This also lets you do things like LAN-Free even if the TDP doesn't support it (many didn't at first)

The restore process for a database backed up by our scripts is:

- retrieve the latest archive of the database and archlogs
- start the database and tell it to recover from the backup

It will then start the database, look to see what archlogs it needs and brings it up to a consistent, up-to-date state and apply them to the database.

I have always, and will continue to, advocate the use of TDP-O. If it has already been purchased, it is by far better to use that rather than any home grown system. It may be cheaper to use the home-grown system but it is a nightmare when it comes to support. There is only you to call.

-Aaron

 

[mikeatkc]

Has the TDP name changed to IBM Tivoli Storage Manager for Databases? The TDP stuff at ftp.software.ibm.com is really old, since 2003.