系统:Rocky Linux
8.6
问题描述:表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.
发表评论