DBAtlas · Database operations atlas

Return a tablespace to a point in the past (TSPITR)

OracleRMAN

Command

-- 1) Is the tablespace self-contained (SQL, on the target DB):
EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'USERS', incl_constraints => TRUE, full_check => TRUE);
SELECT * FROM transport_set_violations;
-- 2) Objects created AFTER the target time that TSPITR will lose:
SELECT owner, name, tablespace_name,
       TO_CHAR(creation_time,'YYYY-MM-DD HH24:MI:SS') AS created
FROM   ts_pitr_objects_to_be_dropped
WHERE  tablespace_name = 'USERS'
  AND  creation_time > TO_DATE('2026-01-15 03:00:00','YYYY-MM-DD HH24:MI:SS');
# 3) RMAN: with an automatic auxiliary instance
RECOVER TABLESPACE users
  UNTIL TIME "TO_DATE('2026-01-15 03:00:00','YYYY-MM-DD HH24:MI:SS')"
  AUXILIARY DESTINATION '/u03/aux';
# 4) The tablespace is left OFFLINE: take a backup first, then bring it online
BACKUP TABLESPACE users;
ALTER TABLESPACE users ONLINE;

What it does

RMAN recovers SYSTEM/UNDO/SYSAUX and the target tablespace up to the requested time in a temporary auxiliary instance, then plugs it back into the target database with transportable tablespace; the rest of the database stays current.

When to use it

When a faulty batch job or a wrong DROP/TRUNCATE damaged data in only one tablespace (or a self-consistent set of tablespaces) and flashback options are not sufficient.

Caution

ALL changes made in that tablespace and all objects created after the target time are lost. The tablespace set must be self-contained (no index/table, partition, LOB or FK in another tablespace). It cannot be used for tablespaces containing SYSTEM, UNDO or SYS objects. After TSPITR, earlier backups of that tablespace are unusable; take a backup immediately. AUXILIARY DESTINATION needs free space for SYSTEM+SYSAUX+UNDO+the target tablespace.

Open in DBAtlas →

Related