Backing Up Partitions Using RMAN

While RMAN (Recovery Manager) in Oracle Database does not support backing up individual partitions directly, you can effectively back up each partition if you have allocated each partition to a different tablespace. This approach allows you to back up each partition separately by backing up the corresponding tablespace.

Why Partition Backups via Tablespace?

By allocating each partition of a partitioned table to a different tablespace, you can leverage RMAN’s ability to backup individual tablespaces. This provides a flexible way to manage and back up your data at the granularity of partitions without needing to backup the entire table.

Example Scenario

In this example, we will demonstrate how to back up a single partition, truncate it, and subsequently restore it. Assume you have a partitioned sales table with each partition in a different tablespace:

-- Create tablespaces for partitions.
CREATE TABLESPACE ts20 DATAFILE './tss20.dbf' SIZE 200M AUTOEXTEND on;
CREATE TABLESPACE ts21 DATAFILE './tss21.dbf' SIZE 200M AUTOEXTEND on;
CREATE TABLESPACE ts22 DATAFILE './tss22.dbf' SIZE 200M AUTOEXTEND on;
CREATE TABLESPACE ts23 DATAFILE './tss23.dbf' SIZE 200M AUTOEXTEND on;
CREATE TABLESPACE tsxx DATAFILE './tssxx.dbf' SIZE 200M AUTOEXTEND on;


-- Create the partitioned table allocating each partition to its tablespace.
CREATE TABLE sales (
  sale_id NUMBER PRIMARY KEY,
  product_id NUMBER,
  customer_id NUMBER,
  sales_date DATE,
  amount NUMBER
)
PARTITION BY RANGE (sales_date) (
  PARTITION p20 VALUES LESS THAN (TO_DATE('2021-01-01', 'YYYY-MM-DD')) TABLESPACE ts20,
  PARTITION p21 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')) TABLESPACE ts21,
  PARTITION p22 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')) TABLESPACE ts22,
  PARTITION p23 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) TABLESPACE ts23,
  PARTITION pxx VALUES LESS THAN (MAXVALUE) TABLESPACE tsxx
);

We have filled the table with random records using the following PL/SQL block.

CREATE SEQUENCE sales_seq
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;

DECLARE
  v_sale_id      NUMBER;
  v_product_id   NUMBER;
  v_customer_id  NUMBER;
  v_sales_date   DATE;
  v_amount       NUMBER;
  v_start_date   DATE := TO_DATE('2020-01-01', 'YYYY-MM-DD');
  v_end_date     DATE := SYSDATE;
  v_total_rows   NUMBER := 50000; 
BEGIN
  FOR i IN 1..v_total_rows LOOP
    v_sale_id := sales_seq.NEXTVAL;
    v_product_id := trunc(DBMS_RANDOM.value(1, 1000)); 
    v_customer_id := trunc(DBMS_RANDOM.value(1, 10000)); 
    v_sales_date := TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_CHAR(v_start_date,'J') ,TO_CHAR(v_end_date,'J'))),'J');
    v_amount := ROUND(DBMS_RANDOM.value(1, 1000), 2); 
    
    INSERT INTO sales (sale_id, product_id, customer_id, sales_date, amount)
    VALUES (v_sale_id, v_product_id, v_customer_id, v_sales_date, v_amount);
  END LOOP;
  
  COMMIT;
END;
/

The next step is to backup the tablespace ts21 so we can restore it later. Remember that Archivelog mode must be enabled to use hot backups.

sh-4.2$ rman target /

Recovery Manager: Release 21.0.0.0.0 - Production on Fri Jun 7 11:52:29 2024
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

connected to target database

RMAN> backup tablespace ts21;

Starting backup at 08-JUN-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=201 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00057 name=/opt/oracle/oradata/XE/XEPDB1/tss21.dbf
channel ORA_DISK_1: starting piece 1 at 08-JUN-24
channel ORA_DISK_1: finished piece 1 at 08-JUN-24
piece handle=/opt/oracle/homes/OraDBHome21cXE/dbs/092sorm6_9_1_1 tag=TAG20240607T125326 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-JUN-24

Now, let’s check the number of records in each partition and truncate the partition containing sales from 2021.

SQL> select to_char(sales_date,'YYYY') year, count(*) records 
from sales
group by to_char(sales_date,'YYYY'); 

YEAR    RECORDS
---- ----------
2020      11266
2021      11126
2022      11232
2023      11451
2024       4925


SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') sys_time,  current_scn from v$database;

SYS_TIME            CURRENT_SCN
------------------- -----------
2024-06-08 12:54:05    27131576


SQL> alter table sales truncate partition p21;

Table SALES truncated.


SQL> select to_char(sales_date,'YYYY') year, count(*) records 
from sales
group by to_char(sales_date,'YYYY'); 

YEAR    RECORDS
---- ----------
2020      11266
2022      11232
2023      11451
2024       4925

As we can see, sales data from the year 2021 is no longer present. It is now time to restore this data. Importantly, the data from other years has remained unaffected throughout this process. This demonstrates the significant versatility of partitioned tables in managing a large number of records.

RMAN> SQL 'ALTER TABLESPACE ts21 OFFLINE';

sql statement: ALTER TABLESPACE ts21 OFFLINE

RMAN> RESTORE TABLESPACE ts21 UNTIL TIME "TO_TIMESTAMP('2024-06-08 12:54:00', 'YYYY-MM-DD HH24:MI:SS')";

Starting restore at 08-JUN-24
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00057 to /opt/oracle/oradata/XE/XEPDB1/tss21.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/homes/OraDBHome21cXE/dbs/092sorm6_9_1_1
channel ORA_DISK_1: piece handle=/opt/oracle/homes/OraDBHome21cXE/dbs/092sorm6_9_1_1 tag=TAG20240607T125326
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 08-JUN-24


RMAN> RECOVER TABLESPACE ts21;

Starting recover at 08-JUN-24
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 08-JUN-24

RMAN> SQL 'ALTER TABLESPACE ts21 ONLINE';

sql statement: ALTER TABLESPACE ts21 ONLINE

RMAN> 

Finally, let’s verify the restored partition.

SQL> select to_char(sales_date,'YYYY') year, count(*) records 
from sales
group by to_char(sales_date,'YYYY'); 

YEAR    RECORDS
---- ----------
2020      11266
2021      11126
2022      11232
2023      11451
2024       4925

Benefits of partitioned tables.

• Flexibility: Allows backing up individual partitions without needing to back up the entire table.
• Efficiency: Saves time and storage by backing up only the required partitions.
• Management: Simplifies data management and recovery processes by handling partitions separately.

Summary

Although RMAN does not allow direct backup of individual partitions, you can achieve the same effect by placing each partition in its own tablespace and then backing up each tablespace independently. This approach provides the flexibility to manage and back up partitions as needed, leveraging RMAN’s robust backup capabilities.