TSPITR恢复表空间后 查询报错ORA-00376 ORA-01110

系统:Rocky Linux
8.6

数据库Oracle 19.12
(多租户ASM单实例)

问题描述:表emp(TSP_ITEM)、dept(TSP_IDX)被drop掉,运用TSPITR恢复回来后查询报错ORA-00376、ORA-01110,如下所示:

RMAN> recover tablespace tsp_item,tsp_idx
2> until time '2022-03-25 16:45:02'
3> auxiliary destination '/home/oracle';
……(省略若干)

SQL> conn c##liujun/liujun
Connected.
SQL> select table_name from tabs;

TABLE_NAME
--------------------------------------------------------------------------------
EMP
DEPT
说明:表已恢复,但无法查询.
SQL> select count(*) from emp;
select count(*) from emp
*
ERROR at line 1:
ORA-00376: file 31 cannot be read at this time
ORA-01110: data file 31: '/home/oracle/tsp_item01.dbf'

SQL> select count(*) from dept;
select count(*) from dept
*
ERROR at line 1:
ORA-00376: file 31 cannot be read at this time
ORA-01110: data file 31: '/home/oracle/tsp_item01.dbf'

解决过程:

SQL> conn / as sysdba
Connected.
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ --------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TSP_ITEM OFFLINE
TSP_IDX OFFLINE

7 rows selected.

SQL> alter tablespace tsp_item online;

Tablespace altered.

SQL> alter tablespace tsp_idx online;

Tablespace altered.

SQL> conn c##liujun/liujun
Connected.
SQL> desc dept
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTID NOT NULL NUMBER
DEPTNAME VARCHAR2(20)
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NUMBER
LAST_NAME VARCHAR2(20)
DEPTID NUMBER

总结:TSPITR恢复后需要将表空间ONLINE.