如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!
诗檀软件专业数据库修复团队
服务热线 ： 13764045638 QQ号:47079569 邮箱：[email protected]
这篇文章适合任何水平的DBA
前提：
–数据库处于归档模式
–数据库开启闪回模式
–被删除用户的时间应该在db_flash_retention_target 范围内并且归档日志是有效的
–被删除的用户下的任何对象不能做任何NOLOGGING操作
这个测试包括以下这些步骤
1：确定有足够的闪回日志能够闪回数据库
2：人为的模仿错误–删除用户
3：闪回数据库
4：打开闪回数据库到只读模式
5：检查用户和数据是否有效
6：将被删除的方案下所有数据导出
7：做完全恢复到目前的状态
8：导入用户去解决人为错误–删除用户
|
1. 确认数据库处于闪回模式开启状态 SQL> select flashback_on from v$database; FLASHBACK_ON —————— YES SQL> show parameter flash NAME VALUE ——————————- ———– db_flashback_retention_target 1440 SQL> select oldest_flashback_scn,oldest_flashback_time from $flashback_database_log ; OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM ——————————— ——————————– 24300236 29-MAY-2009 12:56:13 2. 创建一些表到已存在的用户下被删除来模拟情景 SQL> conn flashback_test/flashback Connected. SQL> create table flashback_testing ( col1 varchar2(20)); Table created. SQL> insert into flashback_testing values ( ‘flashback testing’); 1 row created. SQL> commit; Commit complete. SQL> alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’; Session altered. SQL> select sysdate from dual; SYSDATE ——————– 29-MAY-2009 14:32:50 SQL> conn / as sysdba Connected. SQL> drop user falshback_test CASCADE; User dropped. 3.为了恢复用户，使用闪回数据库恢复到用户被删除前的时间点 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 289406976 bytes Fixed Size 1290208 bytes Variable Size 142606368 bytes Database Buffers 142606336 bytes Redo Buffers 2904064 bytes Database mounted. SQL> flashback database to timestamp to_date(’29-MAY-2009 14:30:00′,’DD-MON-YYYY HH24:MI:SS’); Flashback complete. 4.打开数据库到只读模式 SQL> alter database open read only; Database altered. 5. 检查用户和数据是否正确 SQL> conn FALSHBACK_TEST/flashback Connected. SQL> select * from flashback_testing; COL1 ——————– flashback testing SQL> exit 6. 导出用户 C:\>exp owner=falshback_test file=E:\temp\exp1.dmp Export: Release 10.2.0.4.0 – Production on Fri May 29 20:23:40 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. Username: / as sysdba Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production With the Partitioning, OLAP and Data Mining options Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set About to export specified users … . exporting pre-schema procedural objects and actions . exporting foreign function library names for user FALSHBACK_TEST . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user FALSHBACK_TEST About to export FALSHBACK_TEST’s objects … . about to export FALSHBACK_TEST’s tables via Conventional Path … . . exporting table FLASHBACK_TESTING 1 rows exported . exporting synonyms . exporting views …. . exporting statistics Export terminated successfully without warnings. 7. 关闭数据库并恢复数据库到当前状态 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 289406976 bytes Fixed Size 1290208 bytes Variable Size 142606368 bytes Database Buffers 142606336 bytes Redo Buffers 2904064 bytes Database mounted. SQL> recover database; Media recovery complete. SQL> alter database open; Database altered. 现在数据库处于没有被删除的用户的状态，但是我们有完整的被删除用户的导出文件 8.创建被删除的用户然后导入数据从导出文件中 SQL> create user flashback_test identified by flashback default tablespace users quota unlimited on users; User created. SQL> — Grant the necessary priviledge to that user C:\>imp full=y file=E:\temp\exp1.dmp Import: Release 10.2.0.4.0 – Production on Fri May 29 20:26:14 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. Username: / as sysdba Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production With the Partitioning, OLAP and Data Mining options Export file created by EXPORT:V10.02.01 via conventional path import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set . importing SYS’s objects into SYS . importing FLASHBACK_TEST’s objects into FALSHBACK_TEST . . importing table “FLASHBACK_TESTING” 1 rows imported Import terminated successfully without warnings. 现在数据库没有数据丢失 限制: 1. 如果如何闪回日志或者归档日志丢失，将无法接下来的工作 2.如果有NOLOGGING操作，那么这些NOLOGGING事务将软损坏，因此，避免时间点或者SCN号闪回数据库取决于NOLOGGING操作 |
Leave a Reply