Search code examples
oracle-databasebackuprecoverytablespace

Oracle backup and recovery tablespace

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.

Solution

  • 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...