恢复Oracle USERS表空间后my_table_test表丢失问题咨询

Great question—this comes down to how RMAN handles complete recovery vs. point-in-time recovery. Let's walk through what happened step by step:

• First, you created my_table_test and then took a backup of the USERS tablespace. That backup definitely includes your table, since the backup happened after the table was created.
• Then you deleted the table. That delete operation got written to your database's redo logs (and archived logs, if archiving is turned on).
• When you ran your RMAN recovery block, you did a standard restore and recover. By default, RMAN performs complete recovery: it restores the backup, then applies every single redo/archived log that was generated after the backup was taken. That includes the log entry where you dropped the table.

So even though the backup had your table, the recovery process replayed all the changes that happened after the backup—including the table drop. That's why the table is still gone after recovery.

To get your table back, you need point-in-time recovery (PITR) #

You need to tell RMAN to stop applying logs before you deleted the table. Here's how to adjust your RMAN script:

1. First, figure out the exact timestamp or System Change Number (SCN) right before you ran the DROP TABLE command. You can get this from your database logs or using queries like SELECT CURRENT_SCN FROM V$DATABASE; (if you noted the SCN before deleting) or checking the timestamp of the drop operation in the alert log.

2. Modify your RMAN run block to include the UNTIL clause to stop recovery at that point:

RUN{
  SQL 'ALTER TABLESPACE USERS OFFLINE';
  RESTORE TABLESPACE USERS;
  -- Use either UNTIL TIME or UNTIL SCN, whichever you have
  RECOVER TABLESPACE USERS UNTIL TIME "TO_DATE('YYYY-MM-DD HH24:MI:SS', 'YYYY-MM-DD HH24:MI:SS')";
  -- Or RECOVER TABLESPACE USERS UNTIL SCN 1234567;
  SQL 'ALTER TABLESPACE USERS ONLINE';
}

This will restore the backup and only apply logs up to the moment before the table was deleted, so your my_table_test will be back.

One quick note: If your database is running in NOARCHIVELOG mode, this wouldn't happen—since no archived logs are kept, RMAN can only restore to the backup point, and your table would still exist. But since the recovery replayed the drop, it's safe to assume you're in ARCHIVELOG mode.

内容的提问来源于stack exchange，提问作者malababajaga