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.
The Convenience of Using Partition Tables for Quick Restoration
Partitioned tables provide a strategic advantage for managing and quickly restoring critical data in an Oracle Database. By dividing a large table into smaller, more manageable segments called partitions, each stored in its own tablespace, you can streamline backup and recovery processes significantly. This approach allows you to back up and restore only the affected partitions instead of the entire table, leading to faster recovery times and minimized downtime. For instance, in the event of data corruption or loss in a specific partition, you can isolate and restore just that partition, ensuring that the rest of the database remains operational and unaffected.
This strategic use of partitioned tables can have a profound impact on business operations. In today’s fast-paced business environment, the ability to quickly restore critical information means reduced downtime and continuous availability of essential data. This leads to enhanced business continuity and resilience, ensuring that operational disruptions are minimized and customer service remains uninterrupted. Furthermore, the ability to perform targeted backups and restores translates to more efficient use of storage resources and potentially lower costs associated with data management. Overall, partitioned tables not only improve data manageability and performance but also play a crucial role in maintaining the reliability and integrity of business operations.
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.
It’s a really useful article!