低价网站建设多少钱如何利用网络广告进行推广
使用drop table删除了一张数据表，尝试rollback回滚，却发现根本无法恢复该数据表。
原因是，drop语句在执行时会隐式执行commit提交语句，自然无法回滚了。
查了下资料，说是可以用flashback闪回的方式恢复数据，试了半天，很遗憾，最终没有成功。
教训：一定要慎用drop语句！！！
sys_pd@ORCL> select t.flashback_on from v$database t;FLASHBACK_ON
------------------------------------
NOsys_pd@ORCL> alter database flashback on;
alter database flashback on
*
第 1 行出现错误:
ORA-38706: 无法启用 FLASHBACK DATABASE 事件记录。
ORA-38707: 尚未启用介质恢复。sys_pd@ORCL> archieve log list;
SP2-0734: 未知的命令开头 "archieve l..." - 忽略了剩余的行。
sys_pd@ORCL> archive log list;
数据库日志模式 非存档模式
自动存档 禁用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 154
当前日志序列 156
sys_pd@ORCL> alter database achivelog;
alter database achivelog*
第 1 行出现错误:
ORA-02231: ALTER DATABASE 选项缺失或无效sys_pd@ORCL> alter database achivelog on;
alter database achivelog on*
第 1 行出现错误:
ORA-02231: ALTER DATABASE 选项缺失或无效sys_pd@ORCL> alter database archivelog;数据库已更改。sys_pd@ORCL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 154
下一个存档日志序列 156
当前日志序列 156
sys_pd@ORCL> alter database flashback on;数据库已更改。sys_pd@ORCL> select * from user_tables where table_name like '%emp%';
select * from user_tables where table_name like '%emp%'*
第 1 行出现错误:
ORA-01219: 数据库未打开: 仅允许在固定表/视图中查询sys_pd@ORCL> alter database open;数据库已更改。sys_pd@ORCL> select * from user_tables where table_name like '%emp%';未选定行sys_pd@ORCL> conn scott
已连接。GLOBAL_NAME
----------------------------------------------------------------------------------------------------
scott_pd@ORCLscott_pd@ORCL> select * from user_tables where table_name like '%emp%';未选定行scott_pd@ORCL> select * from user_tables where table_name= 'SCOTT';未选定行scott_pd@ORCL> conn system
已连接。GLOBAL_NAME
----------------------------------------------------------------------------------------------------
system_pd@ORCLsystem_pd@ORCL> select * from user_tables where table_name= 'SCOTT';未选定行system_pd@ORCL> select * from user_tables where rownum<10;TABLE_NAME
------------------------------------------------------------
TABLESPACE_NAME
------------------------------------------------------------
CLUSTER_NAME
------------------------------------------------------------
IOT_NAME STATUS PCT_FREE PCT_USED
------------------------------------------------------------ ---------------- ---------- ----------INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS
---------- ---------- -------------- ----------- ----------- ----------- ------------ ----------
FREELIST_GROUPS LOGGIN BA NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
--------------- ------ -- ---------- ---------- ------------ ---------- ---------- -----------
AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS
------------------------- -------------------
DEGREE
--------------------------------------------------------------------------------
INSTANCES
--------------------------------------------------------------------------------
CACHE TABLE_LOCK SAMPLE_SIZE LAST_ANALYZED PARTIT
---------------------------------------- ---------------- ----------- -------------- ------
IOT_TYPE TE SE NESTED BUFFER_POOL FLASH_CACHE CELL_FLASH_CAC ROW_MOVEMENT
------------------------ -- -- ------ -------------- -------------- -------------- ----------------
GLOBAL USER_S DURATION SKIP_CORRUPT MONITO
------ ------ ------------------------------ ---------------- ------
CLUSTER_OWNER DEPENDENCIES COMPRESSION
------------------------------------------------------------ ---------------- ----------------
COMPRESS_FOR DROPPE READ_O SEGMEN RESULT_CACHE
------------------------ ------ ------ ------ --------------
LOGMNR_SESSION_EVOLVE$
SYSAUXVALID 101 255 65536 1048576 1 2147483645YES N 0 0 0 0 0 00 011N ENABLED 0 30-3月 -10 NON N NO DEFAULT DEFAULT DEFAULT DISABLED
YES NO DISABLED YESDISABLED DISABLEDNO NO YES DEFAULTLOGMNR_GLOBAL$
SYSAUXVALID 101 255 65536 1048576 1 2147483645YES N 0 0 0 0 0 00 011N ENABLED 0 30-3月 -10 NON N NO DEFAULT DEFAULT DEFAULT DISABLED
YES NO DISABLED YESDISABLED DISABLEDNO NO YES DEFAULTLOGMNR_GT_TAB_INCLUDE$VALID 10 401 255 11 NO N11N ENABLED NOY N NO DEFAULT DEFAULT DEFAULT DISABLED
NO NO SYS$SESSION DISABLED NODISABLED DISABLED
BASIC NO NO YES DEFAULTLOGMNR_GT_USER_INCLUDE$VALID 10 401 255 11 NO N11N ENABLED NOY N NO DEFAULT DEFAULT DEFAULT DISABLED
NO NO SYS$SESSION DISABLED NODISABLED DISABLED
BASIC NO NO YES DEFAULTLOGMNR_GT_XID_INCLUDE$VALID 10 401 255 11 NO N11N ENABLED NOY N NO DEFAULT DEFAULT DEFAULT DISABLED
NO NO SYS$SESSION DISABLED NODISABLED DISABLED
BASIC NO NO YES DEFAULTLOGMNR_UID$
SYSAUXVALID 101 255 65536 1048576 1 2147483645YES N 0 0 0 0 0 00 011N ENABLED 0 30-3月 -10 NON N NO DEFAULT DEFAULT DEFAULT DISABLED
YES NO DISABLED YESDISABLED DISABLEDNO NO YES DEFAULTLOGMNRC_DBNAME_UID_MAP
SYSAUXVALID 101 255 65536 1048576 1 2147483645YES N 0 0 0 0 0 00 011N ENABLED 0 30-3月 -10 NON N NO DEFAULT DEFAULT DEFAULT DISABLED
YES NO DISABLED YESDISABLED DISABLEDNO NO YES DEFAULTLOGMNR_LOG$
SYSAUXVALID 101 255 65536 1048576 1 2147483645YES N 0 0 0 0 0 00 011N ENABLED 0 30-3月 -10 NON N NO DEFAULT DEFAULT DEFAULT DISABLED
YES NO DISABLED YESDISABLED DISABLEDNO NO YES DEFAULTLOGMNR_PROCESSED_LOG$
SYSAUXVALID 101 255 65536 1048576 1 2147483645YES N 0 0 0 0 0 00 011N ENABLED 0 30-3月 -10 NON N NO DEFAULT DEFAULT DEFAULT DISABLED
YES NO DISABLED YESDISABLED DISABLEDNO NO YES DEFAULT已选择9行。system_pd@ORCL> select distinct table_name from user_tables where rownum<10;TABLE_NAME
------------------------------------------------------------
MVIEW$_ADV_PRETTY
MVIEW$_ADV_TEMP
LOGMNR_SESSION$
MVIEW$_ADV_SQLDEPEND
MVIEW$_ADV_BASETABLE
MVIEW$_ADV_LOG
MVIEW$_ADV_WORKLOAD
LOGMNR_PARAMETER$
MVIEW$_ADV_FILTER已选择9行。system_pd@ORCL> conn scott
已连接。GLOBAL_NAME
----------------------------------------------------------------------------------------------------
scott_pd@ORCLscott_pd@ORCL> select distinct table_name from user_tables where rownum<10;TABLE_NAME
------------------------------------------------------------
DEPT
部门表
TOAD_PLAN_TABLE
员工表
EMP3
BONUS
EMP2
DEPT2
SALGRADE已选择9行。scott_pd@ORCL> select * from emp3;EMPNO ENAME JOB MGR HIREDATE SAL2 COMM
---------- -------------------- ------------------ ---------- -------------- ---------- ----------DEPTNO
----------7509 pd cont 2166 17-12月-07 5632000 18000405093 pdh cont 2166 03-8月 -15 640000 1000405021 hpd cont 2166 01-2月 -15 1280000 200040scott_pd@ORCL> select * from emp2;未选定行scott_pd@ORCL> create table emp as (select * from emp3);表已创建。scott_pd@ORCL> select * from emp;EMPNO ENAME JOB MGR HIREDATE SAL2 COMM
---------- -------------------- ------------------ ---------- -------------- ---------- ----------DEPTNO
----------7509 pd cont 2166 17-12月-07 5632000 18000405093 pdh cont 2166 03-8月 -15 640000 1000405021 hpd cont 2166 01-2月 -15 1280000 200040scott_pd@ORCL> drop table emp;表已删除。scott_pd@ORCL> rollback;回退已完成。scott_pd@ORCL> select * from emp;
select * from emp*
第 1 行出现错误:
ORA-00942: 表或视图不存在scott_pd@ORCL> flashback database to to_timestamp ('2017-02-13 11:20:00', 'YYYY-MM-DD HH24:MI:SS');
flashback database to to_timestamp ('2017-02-13 11:20:00', 'YYYY-MM-DD HH24:MI:SS')*
第 1 行出现错误:
ORA-38724: FLASHBACK DATABASE 命令选项无效。scott_pd@ORCL> flashback database to timestamp to_timestamp ('2017-02-13 11:20:00', 'YYYY-MM-DD HH24:MI:SS');
flashback database to timestamp to_timestamp ('2017-02-13 11:20:00', 'YYYY-MM-DD HH24:MI:SS')
*
第 1 行出现错误:
ORA-01031: 权限不足scott_pd@ORCL> conn system as sysdba
已连接。GLOBAL_NAME
----------------------------------------------------------------------------------------------------
sys_pd@ORCLsys_pd@ORCL> flashback database to timestamp to_timestamp ('2017-02-13 11:20:00', 'YYYY-MM-DD HH24:MI:SS');
flashback database to timestamp to_timestamp ('2017-02-13 11:20:00', 'YYYY-MM-DD HH24:MI:SS')
*
第 1 行出现错误:
ORA-38757: 要闪回数据库, 数据库必须已装载但不能打开。sys_pd@ORCL> startup mount
ORA-01081: 无法启动已在运行的 ORACLE - 请首先关闭它
sys_pd@ORCL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
sys_pd@ORCL> startup mount
ORACLE 例程已经启动。Total System Global Area 3373858816 bytes
Fixed Size 2180424 bytes
Variable Size 2164263608 bytes
Database Buffers 1191182336 bytes
Redo Buffers 16232448 bytes
数据库装载完毕。
sys_pd@ORCL> flashback database to timestamp to_timestamp ('2017-02-13 11:20:00', 'YYYY-MM-DD HH24:MI:SS');闪回完成。sys_pd@ORCL> select * from scott.emp;
select * from scott.emp*
第 1 行出现错误:
ORA-01219: 数据库未打开: 仅允许在固定表/视图中查询sys_pd@ORCL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项sys_pd@ORCL> alter database open noresetlogs;
alter database open noresetlogs
*
第 1 行出现错误:
ORA-01610: 使用 BACKUP CONTROLFILE 选项的恢复必须已完成sys_pd@ORCL> commit;提交完成。sys_pd@ORCL> alter database open noresetlogs;
alter database open noresetlogs
*
第 1 行出现错误:
ORA-01610: 使用 BACKUP CONTROLFILE 选项的恢复必须已完成sys_pd@ORCL> recover database;
完成介质恢复。
sys_pd@ORCL> alter database open;数据库已更改。sys_pd@ORCL> select * from emp;
select * from emp*
第 1 行出现错误:
ORA-00942: 表或视图不存在sys_pd@ORCL> select * from scott.emp;
select * from scott.emp*
第 1 行出现错误:
ORA-00942: 表或视图不存在sys_pd@ORCL> conn scott
已连接。GLOBAL_NAME
----------------------------------------------------------------------------------------------------
scott_pd@ORCLscott_pd@ORCL> select * from emp;
select * from emp*
第 1 行出现错误:
ORA-00942: 表或视图不存在scott_pd@ORCL> create table emp as (select * from emp3);表已创建。scott_pd@ORCL> select * from emp;EMPNO ENAME JOB MGR HIREDATE SAL2 COMM
---------- -------------------- ------------------ ---------- -------------- ---------- ----------DEPTNO
----------7509 pd cont 2166 17-12月-07 5632000 18000405093 pdh cont 2166 03-8月 -15 640000 1000405021 hpd cont 2166 01-2月 -15 1280000 200040scott_pd@ORCL> drop table emp;表已删除。scott_pd@ORCL> select * from emp;
select * from emp*
第 1 行出现错误:
ORA-00942: 表或视图不存在scott_pd@ORCL> rollback;回退已完成。scott_pd@ORCL> select * from emp;
select * from emp*
第 1 行出现错误:
ORA-00942: 表或视图不存在scott_pd@ORCL> conn system as sysdba
已连接。GLOBAL_NAME
----------------------------------------------------------------------------------------------------
sys_pd@ORCLsys_pd@ORCL> shutdonw immediate
SP2-0734: 未知的命令开头 "shutdonw i..." - 忽略了剩余的行。
sys_pd@ORCL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
sys_pd@ORCL> startup mount
ORACLE 例程已经启动。Total System Global Area 3373858816 bytes
Fixed Size 2180424 bytes
Variable Size 2164263608 bytes
Database Buffers 1191182336 bytes
Redo Buffers 16232448 bytes
数据库装载完毕。
sys_pd@ORCL> flashback database to timestamp to_timestamp ('2017-02-13 11:32:00', 'YYYY-MM-DD HH24:MI:SS');闪回完成。sys_pd@ORCL> recover database;
完成介质恢复。
sys_pd@ORCL> alter database open;数据库已更改。sys_pd@ORCL> select * from scott.emp;
select * from scott.emp*
第 1 行出现错误:
ORA-00942: 表或视图不存在sys_pd@ORCL> recover database using controlfile;
ORA-01906: 需要 BACKUP 关键字sys_pd@ORCL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 1 - file is in use or recovery
ORA-01110: data file 1: 'D:\APP\PANDENG.HUANG\ORADATA\ORCL\SYSTEM01.DBF'sys_pd@ORCL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
sys_pd@ORCL> startup mount
ORACLE 例程已经启动。Total System Global Area 3373858816 bytes
Fixed Size 2180424 bytes
Variable Size 2164263608 bytes
Database Buffers 1191182336 bytes
Redo Buffers 16232448 bytes
数据库装载完毕。
sys_pd@ORCL> flashback database to timestamp to_timestamp ('2017-02-13 11:32:00', 'YYYY-MM-DD HH24:MI:SS');闪回完成。sys_pd@ORCL> recover database using backup controlfile;
ORA-00279: 更改 3558973 (在 02/13/2017 11:32:25 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\PANDENG.HUANG\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_02_13\O1_MF_1_156_%U_.ARC
ORA-00280: 更改 3558973 (用于线程 1) 在序列 #156 中指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'D:\APP\PANDENG.HUANG\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_02_13\O1_MF_1_156_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: ？???？？?
O/S-Error: (OS 2) The system cannot find the file specified.ORA-00308: cannot open archived log
'D:\APP\PANDENG.HUANG\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_02_13\O1_MF_1_156_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: ？???？？?
O/S-Error: (OS 2) The system cannot find the file specified.sys_pd@ORCL> spool out