oracle中误删数据重新恢复的过程,教训 ! 学习~
今天在工作过程中失误删除了select * from ccard.em_creditcard where sno = 99的记录,找同事帮我恢复了一下,以下记录恢复的过程。既作为教训也作为学习,引以为鉴,以后删除数据前一定要先备份。
基本思路把前一天的表空间备份出来恢复到一个临时的表空间,然后把记录重新Insert到误删的表空间中,幸亏这条记录是今天之前的,若是当天的数据也没有备份的话需要从日志来恢复了。
[oracle@Pay-DBsrv oracle]$ sqlplus /nolog
SQL> connect / as sysdba
Connected.
SQL> create user cc identified by cc
2 default tablespace users
3 temporary tablespace temp
4 ;
User created.
SQL> grant connect , resource to cc
2 ;
Grant succeeded.
SQL> exit
[oracle@Pay-DBsrv tmp]$ exp system file=ccard.dmp log=ccard.log owner=ccard direct=y
[oracle@Pay-DBsrv tmp]$ imp sys file=monca_full_20050929.dmp fromuser=ccard touser=cc log=temp.log
[oracle@Pay-DBsrv tmp]$ sqlplus /nolog
SQL> connect cc/cc
Connected.
SQL> set pagesize 1000
SQL> select * from em_creditcard where sno = 99;
SQL> connect / as sysdba
Connected.
SQL> insert into ccard.em_creditcard select * from cc.em_creditcard where sno = 99;
SQL> select * from ccard.em_creditcard where sno = 99;
SQL> commit ;
Commit complete.
SQL> drop user cc cascade;
SQL> exit;
今天在工作过程中失误删除了select * from ccard.em_creditcard where sno = 99的记录,找同事帮我恢复了一下,以下记录恢复的过程。既作为教训也作为学习,引以为鉴,以后删除数据前一定要先备份。
基本思路把前一天的表空间备份出来恢复到一个临时的表空间,然后把记录重新Insert到误删的表空间中,幸亏这条记录是今天之前的,若是当天的数据也没有备份的话需要从日志来恢复了。
[oracle@Pay-DBsrv oracle]$ sqlplus /nolog
SQL> connect / as sysdba
Connected.
SQL> create user cc identified by cc
2 default tablespace users
3 temporary tablespace temp
4 ;
User created.
SQL> grant connect , resource to cc
2 ;
Grant succeeded.
SQL> exit
[oracle@Pay-DBsrv tmp]$ exp system file=ccard.dmp log=ccard.log owner=ccard direct=y
[oracle@Pay-DBsrv tmp]$ imp sys file=monca_full_20050929.dmp fromuser=ccard touser=cc log=temp.log
[oracle@Pay-DBsrv tmp]$ sqlplus /nolog
SQL> connect cc/cc
Connected.
SQL> set pagesize 1000
SQL> select * from em_creditcard where sno = 99;
SQL> connect / as sysdba
Connected.
SQL> insert into ccard.em_creditcard select * from cc.em_creditcard where sno = 99;
SQL> select * from ccard.em_creditcard where sno = 99;
SQL> commit ;
Commit complete.
SQL> drop user cc cascade;
SQL> exit;