Oracle DB Size becomes too large

[former_member949224]

Dear Friends,

I have been using SAP ECC 6.0 with Oracle 10g & OS is HP-UX .Till day i am taking backup in LTO 3 Ultrium tape 800GB through hp tape library

My DB Size increased from 390GB to 410GB,The Tape which allows only 400gb and in compression it allows me to store 800gb.

Now i am taking backup in compression mode,it takes too long time.Previously backup time will be 2 hrs,now after compression it is more than 5hrs.

How to reduce backup time?Pls help to resolve this issue..

Is there any alternate way to take backup in short time?

Regards

AR

[Former Member]

Well, you could look into the incremental backup option. Else, you have to look into the 11g upgrade.

[Former Member]

First of all, you may plan to archive old documents on the system, in order to decrease db size. Then, consider tablespace reorganization. After that the db size will be decreased.

Secondly, are you using software compression or hardware comression? Be sure that you use HW compression.

Third suggestion is upgrade to 11g and use db compression.

Best regards,

Orkun Gedik

[volker_borowski2]

Hi!

I guess you are doing wrong!

You should backup with brbackup parameter "COMPRESS=hardware" NOT with "COMPRESS=YES" which will in fact be SW compression. The problem is, brbackup needs to have information about the compression ratio to calculate with. This is why you would do a "COMPRESS=ONLY" run once every two months so brbackup can calculate.

Second:

The tapesize parameter is only neccessary, if brbackup really needs to manage multiple volumes. On LTO3 with a DB compression of average 1:4, you should be far away from hitting the end of the tape. So I'd go:

configure COMPRESS=NO (to avoid the need of doing compress runs)

configure TAPE_SIZE as i.e. four TB to be far out of switching volumes

use hardware compression.

Since you have not got a second tape anyway, there is nothing wrong with this setup, until you really hit an end-of-tape error. You might need to go LTO4 or two volumes then, but right now, I think ther will be no need.

Volker

[former_member949224]

Hi Volker,

Thanks for ur reply

Right now i am following COMPRESS=YES .We have HP LTO 3 Tape and only one tape drive.

I am not sure LTO 4 tape will be supported by the tape drive or not.

Before taking compression backup we ran compression check  by following

cmd prompt

Brtools

4 - Backup and database copy

8 - Additional functions

1- Update of compression rates

c

c

BR0125I Compression only - no backup

c

c

After completing the above checkup,it gave

BR0115I Compression rate for all files 3.0801:1

Now while taking backups,we simply follows compression = yes

Do u want me to give compression = hardware ??If i give hardware compression,do i have to change any parameters from the current settings?Thanks in advance.

I also need to check  " brbackup -i force -v scratch -n 1 -c " option.

Regards

AR

[volker_borowski2]

Hi,

"compress = yes " basicly leads to the backup beinig completely piped through a compression filter like "zip" or "compress". This is direct cpu processing which is why it takes so long.

You specify "compress = hardware" and need to adjust the tape_address* parameters to devicefiles which support compression, thats is all.

The tape_size stuff is only for brbackup to be able to calculate volume management, in case you need more than one tape.

An LTO 3 stores 400 GB uncompressed. (800 is a vendor value which assumes 1:2 compression).

Since the compress run calculated 1:3, you should be able to save up to 1.2 TB on an LTO 3.

So just tell brbackup your tape is big enough.

Volker

[Former Member]

Do the below changes as said per Volker

In Oracle_home/dbs initSID.sap

# compression flag [no | yes | hardware | only]

# default: no

compress = hardware

[Former Member]

Hi

Check in DB which table is taken more space,check SAP standard jobs working or not properly.we also faced same issue,our TST03 table taken 500GB space due to our standard job is not deleting old spool.so we have deleted spool manually.May be u also check in ur tables and indexes which are taking more space and try to reorganize them.

Thanks

[Former Member]

Hi,

I don't think its because of rapid growth of your database rather I treat this as a old technique of backup that you are using. Now a days there are so many backup tools (third party) supporting by SAP if you are ready to get.

Database size of 400G is not creating issues here, but you have to get into more advanced tools and techniques to suffice the requirements.

In this case I would prefer to use LTOs which supports more than a TB backups (LTO-4 etc).

Regards,

Nick Loy

[former_member949224]

Dear All,

Thanks to everyone..

We have only one tape drive in our organization.I have tried taking backup with 2 tapes,I initialized volumes in brtools..Though we have only one tape drive,backup was running in only one tape till 96% and then gives options to change the tape,i changed the tape in tape drive,but it says volume not found.

[Former Member]

have you scratch the second tape and tried.

scratch command as below

brbackup -i force -v scratch -n 1 -c

[former_member949224]

Hi Hari,

I am taking backup through BRTOOLS

Login to your system as sidadm and from the command promt execute BRTOOLS

Select option

4 = Backup and database copy

8 = Additional Functions

Tape Intialization

1 = Database backup

3 = Tape volumes for backup (volume)

6 = Backup type and enter it as OFFLINE/ONLINE_CONS

Continue C

Continue C

This is how we use to take backup.Here where to give scratch?we use force option while initializing tape.But have not used scratch.

What does it do?Because it is Production Server.

Regards

AR

[Former Member]

when u use the second tape u make the tape empty and load it.

su - sidadm

brbackup -i force -v scratch -n 1 -c

Select option

4 = Backup and database copy

1 = Database backup

3 = Tape volumes for backup (volume)

6 = Backup type and enter it as OFFLINE/ONLINE_CONS

Continue C

Continue C

as mentioned in ur previous forum..after 96% over...u load ur tape new tape and continue..

scratch will delete ur tape data...

[former_member197466]

Hi Asshok,

SAP note  842240, point 2 suggested some  options to optimal the backup runtime.

Also, for the large basis tables, please read SAP note 706478 to prevent Basis tables from increasing.

Cheers,

Ivy

[martin_E]

Asshok,

  Before scheduling the downtime for a database reorganisation, it may be worthwhile looking at  Information Lifecycle Management (what used to be called Archiving) to free up space.

hth

[JPReyes]

Get a tape library and use 2 tapes...

Also, you could check if a DB reorganization could bring your DB size down, but really you will be pushing your fate down the line only to be faced with the same issue later.

Backup strategy is individual to each company needs so you should have to discuss this with your Line Manager.

Regards, Juan