还原表空间USERS无效.Oracle备份 [英] Restore tablespace USERS doesn't work. Oracle backup

问题描述

我创建了一个架构和my_table_test;

I created a schema and my_table_test;

ALTER SESSION SET CURRENT_SCHEMA=c##wojtek_admin; 

CREATE TABLE my_table_test
( id_test INT 
);

然后，我使用以下代码创建了备份: BACKUP TABLESPACE USERS FORMAT'c:\ FRA \ users％u'; 然后我放下my_table_test并运行以下命令:

Then I created a backup using: BACKUP TABLESPACE USERS FORMAT 'c:\FRA\users%u'; Then I dropped my_table_test and run below commands:

RMAN> RUN{
2> SQL 'ALTER TABLESPACE USERS OFFLINE';
3> RESTORE TABLESPACE USERS;
4> RECOVER TABLESPACE USERS;
5> SQL 'ALTER TABLESPACE USERS ONLINE';
6> }

为什么我恢复了用户桌面后删除了my_table_test?

Why my_table_test is dropped after I restored USERS TABLESPACE?

推荐答案

首先，需要注意的是，您似乎正在使用Multitenant数据库，但是您决定将用户数据放入根容器中，这是通常这是一个坏主意-您应该使用可插拔数据库来存储几乎所有内容(这也意味着您不需要为用户名加上 C ## 前缀.

First, it's important to note that it looks like you're using a Multitenant Database but you've decided to put user data inside the root container, this is generally a bad idea - you should be using a Pluggable Database for pretty much everything (this will also mean you don't need to prefix your usernames with C##.

您的restore和restore语句将恢复表空间直到现在.如果要在删除表空间之前将其恢复到原来的状态，那么Oracle还需要在 system 表空间(用于数据字典)上做一些工作-但您不仅要还原和恢复您的 USERS 表空间.您需要将备份还原到其他位置，将其恢复到所需的位置，然后使用 USERS 表空间并将其放回到原始数据库中.

Your restore and recover statements will recover the tablespace up until now. If you want to recover it to before you dropped the tablespace then Oracle will also need to do some work on the system tablespace (for the data dictionary) - but you don't only want to restore and recover your USERS tablespace. You'd need to restore your backup somewhere else, recover it to the desired point, then take the USERS tablespace and put it back into your original database.

这简称为Tablespace时间点恢复，Oracle为您完成了所有硬脚本工作，但是您应该阅读

This is simply referred to as Tablespace Point In Time Recovery, Oracle has helpfully done all the hard scripting work for you, but you should read what's going on https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmtspit.htm#BRADV89790

RECOVER TABLESPACE users
  UNTIL ?
  AUXILIARY DESTINATION '?';

(阅读完文档后，您将了解如何填写此文件).

(Once you've read the docs you'll see how to fill this out).

还具有在表级别上轻松执行此操作的功能，因此，如果您只想恢复该删除的表而不希望恢复该表空间中的所有其他内容，则可以:

There is also the ability to do this easily at a table level, so if you want to only recover that dropped table and not revert everything else in that tablespace, you could:

RECOVER TABLE c##wojtek_admin.my_table_test
UNTIL ?
AUXILIARY DESTINATION '?'  ;

请参见 https://oracle-base.com/articles/12c/rman-table-point-in-time-recovery-12cr1 了解更多信息.

这篇关于还原表空间USERS无效.Oracle备份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！