0

In Oracle 11.2 DB I have:
- transaction table in tablespace users and
- transaction_bkp table in trans_bkp tablespace

Transaction table holds data for 1 month and transaction_bkp should hold data as long as possible.

Problem is that trans_bkp tablespace becomes full after 6 months.

Idea to resolve this problem is to backup trans_bkp tablespace every month and then truncate table transaction_bkp.

How to do this backup?
If customer will need some specific data in past how can I delivery them.

sovadn
  • 15
  • 3

1 Answers1

0

Make use of RMAN and define your retention policy.

  1. Backup tablespace trans_pkp using something like (for as long as you like to keep it)

    BACKUP TABLESPACE TRANS_BKP KEEP FOREVER NOLOGS TAG 'FIRSTHALF2014;

  2. Truncate table transition_bkp

Restore:

  • Make use of TSPITR (Automatic Tablespace Point-in-Time Recovery)
  • Utilize RMAN duplicate from backup and set until time

You might want to use a naming convention for the tablespace. Like TRANS_BKP_01_2014, TRANS_BKP_02_2014, etc...

Bjarte Brandt
  • 4,191
  • 2
  • 23
  • 25
  • Tnx. What do you think about this solution compared to the one above? 1. Export transaction_bkp with expdp 2. copy exported file of that table to backup location 3. truncate table transaction_bkp? – sovadn Mar 06 '14 at 08:19