1. 实验说明
该实验对Oracle数据库误操作drop tablespace including contents的情况进行恢复，
恢复操作前请提前做好备份。
复制2. 操作过程
[oracle@sourcedb enmo]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 18 03:13:57 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> create tablespace hsql datafile '/oradata/enmo/hsql01.dbf' size 100M;
Tablespace created.
SQL> create table hsql.t tablespace HSQL as select * from dba_objects;
insert into hsql.t select * from dba_objects;
commit;
insert into hsql.t select * from dba_objects;
commit;
Table created.
SQL> insert into hsql.t select * from dba_objects;
13523 rows created.
SQL>
Commit complete.
SQL>
13523 rows created.
SQL>
Commit complete.
SQL>
13523 rows created.
SQL>
SQL>
SQL> commit;
Commit complete.
SQL> select ts#,name,owner#,ONLINE$ from ts$;
TS# NAME OWNER# ONLINE$
---------- ------------------------------ ---------- ----------
0 SYSTEM 0 1
1 SYSAUX 0 1
2 UNDOTBS1 0 1
3 TEMPTS1 0 1
4 USERS 0 1
5 HSQL 0 1
6 rows selected.
SQL> select OBJ#,DATAOBJ#,OWNER#,NAME from obj$ where obj# in (select obj# from tab$ where ts#=5);
OBJ# DATAOBJ# OWNER# NAME
---------- ---------- ---------- ------------------------------
13804 13804 38 T
SQL> select obj#,dataobj#,ts#,file#,block# from tab$ where ts#=5;
OBJ# DATAOBJ# TS# FILE# BLOCK#
---------- ---------- ---------- ---------- ----------
13804 13804 5 5 130
SQL> select obj#,col#,name from col$ where obj# in (select obj# from tab$ where ts#=5) order by 1,2;
OBJ# COL# NAME
---------- ---------- ------------------------------
13804 1 OWNER
13804 2 OBJECT_NAME
13804 3 SUBOBJECT_NAME
13804 4 OBJECT_ID
13804 5 DATA_OBJECT_ID
13804 6 OBJECT_TYPE
13804 7 CREATED
13804 8 LAST_DDL_TIME
13804 9 TIMESTAMP
13804 10 STATUS
13804 11 TEMPORARY
OBJ# COL# NAME
---------- ---------- ------------------------------
13804 12 GENERATED
13804 13 SECONDARY
13804 14 NAMESPACE
13804 15 EDITION_NAME
15 rows selected.
SQL> select file#,status$,blocks,ts#,relfile# from file$ where ts#=5;
FILE# STATUS$ BLOCKS TS# RELFILE#
---------- ---------- ---------- ---------- ----------
5 2 12800 5 5
SQL> select FILE#,BLOCK#,TYPE#,TS#,BLOCKS,EXTENTS,EXTSIZE from seg$ where ts#=5;
FILE# BLOCK# TYPE# TS# BLOCKS EXTENTS EXTSIZE
---------- ---------- ---------- ---------- ---------- ---------- ----------
5 130 5 5 8 1 128
SQL> set linesize 200 pagesize 200
SQL> col segment_name for a20
SQL> select segment_name,tablespace_name,extent_id,file_id,block_id,blocks
2 from dba_extents
3 where segment_name='T';
SEGMENT_NAME TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
-------------------- ------------------------------ ---------- ---------- ---------- ----------
T HSQL 0 5 128 8
T HSQL 1 5 136 8
T HSQL 2 5 144 8
T HSQL 3 5 152 8
T HSQL 4 5 160 8
T HSQL 5 5 168 8
T HSQL 6 5 176 8
T HSQL 7 5 184 8
T HSQL 8 5 192 8
T HSQL 9 5 200 8
T HSQL 10 5 208 8
T HSQL 11 5 216 8
T HSQL 12 5 224 8
T HSQL 13 5 232 8
T HSQL 14 5 240 8
T HSQL 15 5 248 8
T HSQL 16 5 256 128
T HSQL 17 5 384 128
T HSQL 18 5 512 128
T HSQL 19 5 640 128
T HSQL 20 5 768 128
21 rows selected.
SQL> select count(1) from hsql.t;
COUNT(1)
----------
54092
SQL> select sysdate from dual;
SYSDATE
------------
18-APR-22
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2022-04-18 03:17:58
SQL> drop tablespace hsql including contents;
alter system flush shared_pool;
alter system flush buffer_cache;
select count(1) from hsql.t;
Tablespace dropped.
SQL>
System altered.
SQL>
System altered.
SQL> SQL> select count(1) from hsql.t
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL>
SQL>
SQL>
SQL> create table bak_ts$ tablespace users as select * from ts$;
Table created.
SQL> delete ts$ where ts#=5;
1 row deleted.
SQL> commit;
Commit complete.
SQL> insert into ts$
2 select * from ts$ as of timestamp to_timestamp('2022-04-18 03:17:58','yyyy-mm-dd hh24:mi:ss')
3 where ts#=5;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into obj$
2 select * from obj$ as of timestamp to_timestamp('2022-04-18 03:17:58','yyyy-mm-dd hh24:mi:ss')
3 where obj# in (select obj# from tab$ as of timestamp to_timestamp('2022-04-18 03:17:58','yyyy-mm-dd hh24:mi:ss')
4 where ts#=5);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into tab$
2 select * from tab$ as of timestamp to_timestamp('2022-04-18 03:17:58','yyyy-mm-dd hh24:mi:ss')
3 where ts#=5;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into col$
2 select OBJ#,
3 COL#,
4 SEGCOL#,
5 SEGCOLLENGTH,
6 OFFSET,
7 NAME,
8 TYPE#,
9 LENGTH,
10 FIXEDSTORAGE,
11 PRECISION#,
12 SCALE,
13 NULL$,
14 DEFLENGTH,
15 '',
16 INTCOL#,
17 PROPERTY,
18 CHARSETID,
19 CHARSETFORM,
20 SPARE1,
21 SPARE2,
22 SPARE3,
23 SPARE4,
24 SPARE5,
25 SPARE6 from col$ as of timestamp to_timestamp('2022-04-18 03:17:58','yyyy-mm-dd hh24:mi:ss')
26 where obj# in (select obj# from tab$ as of timestamp to_timestamp('2022-04-18 03:17:58','yyyy-mm-dd hh24:mi:ss')
27 where ts#=5) order by 1,2;
15 rows created.
SQL> commit;
Commit complete.
SQL> delete from file$ where file# in (
2 select file# from file$ as of timestamp to_timestamp('2022-04-18 03:17:58','yyyy-mm-dd hh24:mi:ss')
3 where ts#=5
4 );
1 row deleted.
SQL> commit;
Commit complete.
SQL> insert into file$
2 select * from file$ as of timestamp to_timestamp('2022-04-18 03:17:58','yyyy-mm-dd hh24:mi:ss')
3 where ts#=5;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into seg$
2 select * from seg$ as of timestamp to_timestamp('2022-04-18 03:17:58','yyyy-mm-dd hh24:mi:ss') where file#=5;
1 row created.
SQL> commit;
Commit complete.
SQL> commit;
Commit complete.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@sourcedb enmo]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 18 03:23:31 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='pfile.ora';
LRM-00109: could not open parameter file 'pfile.ora'
ORA-01078: failure in processing system parameters
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size 2253584 bytes
Variable Size 1073745136 bytes
Database Buffers 486539264 bytes
Redo Buffers 7471104 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ENMO" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 21
3 MAXLOGMEMBERS 5
4 MAXDATAFILES 100
5 MAXINSTANCES 1
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/oradata/enmo/redo01a.log' SIZE 100M BLOCKSIZE 512,
9 GROUP 2 '/oradata/enmo/redo02a.log' SIZE 100M BLOCKSIZE 512,
10 GROUP 3 '/oradata/enmo/redo03a.log' SIZE 100M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/oradata/enmo/system01.dbf',
14 '/oradata/enmo/sysaux01.dbf',
15 '/oradata/enmo/undotbs01.dbf',
16 '/oradata/enmo/users01.dbf',
17 '/oradata/enmo/hsql01.dbf'
18 CHARACTER SET ZHS16GBK
19 ;
Control file created.
SQL> exit
[oracle@sourcedb orastar_script]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 18 03:24:02 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @dbf
Session altered.
FILE# NAME STATUS TS# RFILE# RESETLOGS_CHANGE# RESETLOGS_TIME CHECKPOINT_CHANGE# CHECKPOINT_TIME CHECKPOINT_COUNT ERROR REC FUZ
---------- ------------------------------------------------------------ ------- ---------- ---------- ----------------- ------------------- ------------------ ------------------- ---------------- -------------------- --- ---
1 /oradata/enmo/system01.dbf ONLINE 0 1 1 2022-02-06 18:07:11 345094 2022-04-18 03:23:04 79 NO NO
2 /oradata/enmo/sysaux01.dbf ONLINE 1 2 1 2022-02-06 18:07:11 345094 2022-04-18 03:23:04 79 NO NO
3 /oradata/enmo/undotbs01.dbf ONLINE 2 3 1 2022-02-06 18:07:11 345094 2022-04-18 03:23:04 79 NO NO
4 /oradata/enmo/users01.dbf ONLINE 4 4 1 2022-02-06 18:07:11 345094 2022-04-18 03:23:04 79 NO NO
5 /oradata/enmo/hsql01.dbf ONLINE 5 5 1 2022-02-06 18:07:11 344771 2022-04-18 03:18:09 3 YES NO
SQL> recover datafile 5;
Media recovery complete.
SQL> @dbf
Session altered.
CHECKPOINT_CHANGE#
------------------
345094
FILE# NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME LAST_CHANGE# LAST_TIME
---------- ------------------------------------------------------------ ------------------ ------------------- ------------ -------------------
1 /oradata/enmo/system01.dbf 345094 2022-04-18 03:23:04 345094 2022-04-18 03:23:49
2 /oradata/enmo/sysaux01.dbf 345094 2022-04-18 03:23:04 345094 2022-04-18 03:23:49
3 /oradata/enmo/undotbs01.dbf 345094 2022-04-18 03:23:04 345094 2022-04-18 03:23:49
4 /oradata/enmo/users01.dbf 345094 2022-04-18 03:23:04 345094 2022-04-18 03:23:49
5 /oradata/enmo/hsql01.dbf 345094 345092 2022-04-18 03:23:04
FILE# NAME STATUS TS# RFILE# RESETLOGS_CHANGE# RESETLOGS_TIME CHECKPOINT_CHANGE# CHECKPOINT_TIME CHECKPOINT_COUNT ERROR REC FUZ
---------- ------------------------------------------------------------ ------- ---------- ---------- ----------------- ------------------- ------------------ ------------------- ---------------- -------------------- --- ---
1 /oradata/enmo/system01.dbf ONLINE 0 1 1 2022-02-06 18:07:11 345094 2022-04-18 03:23:04 79 NO NO
2 /oradata/enmo/sysaux01.dbf ONLINE 1 2 1 2022-02-06 18:07:11 345094 2022-04-18 03:23:04 79 NO NO
3 /oradata/enmo/undotbs01.dbf ONLINE 2 3 1 2022-02-06 18:07:11 345094 2022-04-18 03:23:04 79 NO NO
4 /oradata/enmo/users01.dbf ONLINE 4 4 1 2022-02-06 18:07:11 345094 2022-04-18 03:23:04 79 NO NO
5 /oradata/enmo/hsql01.dbf ONLINE 5 5 1 2022-02-06 18:07:11 345092 2022-04-18 03:23:04 4 NO NO
SQL> alter database open;
Database altered.
SQL> select count(1) from hsql.t;
COUNT(1)
----------
54092
SQL>
复制「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
墨天轮福利君
2年前
评论
0您好，您的文章已入选墨力原创作者计划合格奖，10墨值奖励已经到账请查收！
❤️我们还会实时派发您的流量收益。
2年前
点赞 评论
相关阅读
【专家有话说第五期】在不同年龄段，DBA应该怎样规划自己的职业发展？
墨天轮编辑部
1252次阅读
2025-03-13 11:40:53
Oracle RAC ASM 磁盘组满了，无法扩容怎么在线处理？
Lucifer三思而后行
757次阅读
2025-03-17 11:33:53
Oracle+Deepseek+Dify 实现数据库数据实时分析
bicewow
665次阅读
2025-03-06 09:41:49
【ORACLE】ORACLE19C在19.13版本前的一个严重BUG-24761824
DarkAthena
556次阅读
2025-03-04 14:33:31
Oracle避坑指南｜同名表导出难题：如何精准排除指定用户下的表？
szrsu
506次阅读
2025-03-05 00:42:34
2月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
448次阅读
2025-03-13 14:38:19
Ogg23ai高手必看-MySQL Innodb Cluster跟oracle的亲密接触
曹海峰
441次阅读
2025-03-04 21:56:13
【ORACLE】char类型和sql优化器发生的“错误”反应
DarkAthena
401次阅读
2025-03-04 23:05:01
什么，oracle 主机用户被删了？原来是虚惊一场！
Lucifer三思而后行
398次阅读
2025-03-03 21:12:09
Oracle 如何修改 db_unique_name？强迫症福音！
Lucifer三思而后行
337次阅读
2025-03-12 21:27:56
热门文章
oracle drop table purge无备份bbed恢复
2021-01-25 6636浏览
20230704_一键安装Oracle11g/19C/21C_单机/RAC集群/standalone/PostgreSQL_工具分享_开放源码
2021-11-04 5177浏览
20211119_一键安装Oracle 21C RAC_1731秒完成
2021-11-19 4769浏览
Oracle异常恢复_ORA-01189: file is from a different RESETLOGS than previous files
2021-08-11 4176浏览
从问题出发，解密Oracle rdba结构
2021-07-23 4079浏览
目录
