Thread by @FranckPachot

0⃣ I've heard many misconceptions about restoring tablespaces in @OracleDatabase so here a a little #backToTheBasics restore+recover concepts & how-to
👇

1⃣ A tablespace is a subset of the (monolith) database. There are physical operations (backup, transport, snapshot,...) that you don't want to do for the whole TB database. Then you dedicate some tablespaces to applications, datastores, large tables, read-only workloads,...

2⃣You may want to restore some data as-of yesterday (because you dropped a table by mistake for example) without having to restore the whole database. This is Tablespace Point In time Recovery (TSPITR). Let's say you dropped the tablespace USERS by mistake at 11:42 👇

3⃣ I've seen people thinking they can restore a TBS without mentioning a Point In Time, or by mentioning a PIT after the drop, to get its the latest state. But that's wrong because you don't actually restore the tablespace. You restore the database, skipping other tablespaces

4⃣ If you restore and recover beyond 11:42 in my example, this will just replay the DROP... You need to recover until the Point In Time just before the drop. Because you don't restore a tablespace. You restore a subset of the database and recover all what happens until the PIT

5⃣ Restoring tablespace USERS is actually restoring USERS datafiles, plus SYSTEM and SYSAUX (because you need the dictionary mapping logical tables to physical segments), and UNDO because there were ongoing transactions at this PIT that needs to be rolled back to stay ACID

6⃣ And of course, you cannot restore this in-place because you cannot overwrite the current SYSTEM/SYSAUX/UNDO with the one from the recovery Point In Time. You do that to an auxiliary instance and then move the recovered tablespace.
RMAN can automate it:

7⃣ you may prefer to just open the auxiliary database so that users can query to get their data back. Or you can recover further (if you opened it read-only)… Be careful if you have jobs running. Be careful about the service registered to the listener. RMAN DUPLICATE helps.

8⃣ with multitenant there's an intermediate level between the CDB database and the tablespaces. You can restore a PDB in-place (PDBPITR) because they contain their own metadata (SYSTEM) and local UNDO. When you connect RMAN to a CDB, prefix the tablespace names with PDB: name

9⃣ the importance of learning concept before using an easy GUI or API: if you ever did this operation manually (with SKIP TABLESPACE in RMAN RESTORE/RECOVER or DUPLICATE) you get it. If you just see 'restore tablespace' as a black box button, you may not know which PIT to set🤷‍♂️

🔟 and remember a dump is not a backup. A backup is not only there to restore to the PIT where it was taken. You can recover at any Point In Time within the retention window, in a predictable time
Allow yourself to make mistakes without loosing any data😎 blog.dbi-services.com/what-is-a-data…