Posts
用闪回数据库恢复删除的表空间
10g OCP 教程上说不能恢复删除的表空间
"You cannot use Flashback Database in the following situations:
The control file has been restored or re-created.
A tablespace has been dropped.
A data file has been shrunk."
但是文档5.3.1 Limitations of Flashback Database 和 Prerequisites of Flashback Database 都没说有这个限制
11g文档 Table 2-7 How FLASHBACK DATABASE Responds to Datafile Status Changes 还提到 "Adds the datafile to the control file, but marks it as offline and does not flash it back. You can then restore and recover the datafile to the same time or SCN."
特测试一下
1. 打开闪回数据库功能
shutdown immediate startup mount exclusive show parameter db_flashback_retention_target alter database flashback on; select flashback_on from v$database;
2. 创建表空间并录入测试数据
create tablespace ts_a datafile '/home/oracle/app/oracle/oradata/test/ts_a.dbf' size 10m; create table t_a (a int) tablespace ts_a; insert into t_a values (1); commit; select current_scn from v$database; insert into t_a values (2); commit; select current_scn from v$database;
SQL> create tablespace ts_a datafile '/home/oracle/app/oracle/oradata/test/ts_a.dbf' size 10m;
Tablespace created.
SQL> create table t_a (a int) tablespace ts_a;
Table created.
SQL> insert into t_a values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5166171
SQL> insert into t_a values (2);
1 row created.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5166180
SQL>
3. 删除表空间
drop tablespace ts_a including contents; select current_scn from v$database;
SQL> drop tablespace ts_a including contents;
Tablespace dropped.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5166218
SQL>
4. 闪回数据库
shutdown immediate startup mount select current_scn from v$database; flashback database to scn &scn;
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2085320 bytes
Variable Size 603983416 bytes
Database Buffers 1526726656 bytes
Redo Buffers 14688256 bytes
Database mounted.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
0
SQL> flashback database to scn &scn;
Enter value for scn: 5166171
old 1: flashback database to scn &scn
new 1: flashback database to scn 5166171
flashback database to scn 5166171
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 7 will be lost if RESETLOGS is done
ORA-01111: name for data file 7 is unknown - rename to correct file
ORA-01110: data file 7: '/home/oracle/app/oracle/product/10.2/dbs/UNNAMED00007'
SQL>
这步报错了, 说不认识数据文件名, 要改成正确的日志:
... Mon May 4 08:18:34 2009 flashback database to scn 5166171 Mon May 4 08:18:35 2009 Flashback Restore Start Flashback: created tablespace #8: 'TS_A' in the controlfile. Flashback: created OFFLINE file 'UNNAMED00007' for tablespace #8 in the controlfile. Filename was: '/home/oracle/app/oracle/oradata/test/ts_a.dbf' when dropped. File will have to be restored from a backup and recovered. Flashback: deleted datafile #7 in tablespace #8 from control file. Flashback: dropped tablespace #8: 'TS_A' from the control file. Flashback Restore Complete Flashback Media Recovery Start parallel recovery started with 3 processes Mon May 4 08:18:36 2009 Recovery of Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0 Mem# 0: /home/oracle/app/oracle/oradata/test/redo03.rdo Mem# 1: /home/oracle/app/oracle/oradata/test/redo03b.rdo Mon May 4 08:18:36 2009 Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0 Mem# 0: /home/oracle/app/oracle/oradata/test/redo01.rdo Mem# 1: /home/oracle/app/oracle/oradata/test/redo01b.rdo Flashback recovery: Added file #7 to control file as OFFLINE and 'UNNAMED00007' because it was dropped during the flashback interval or it was added during flashback media recovery. File was originally created as: '/home/oracle/app/oracle/oradata/test/ts_a.dbf' File will have to be restored from a backup or recreated using ALTER DATABASE CREATE DATAFILE command, and the file has to be onlined and recovered. Mon May 4 08:18:36 2009 Incomplete Recovery applied until change 5166172 Flashback Media Recovery Complete ORA-38795 signalled during: flashback database to scn 5166171...
5. 按前面提示, 将数据文件改名
select * from v$datafile; select * from v$tablespace; alter database rename file '/home/oracle/app/oracle/product/10.2/dbs/UNNAMED00007' to '/home/oracle/app/oracle/oradata/test/ts_a.dbf'; select * from v$datafile;
SQL> select * from v$datafile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ---------------- ------------------ ---------- ---------- ------- ---------- ------------------ ------------------
UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME
--------------------- ------------------ ------------ ------------------ --------------- -------------- ------------------
BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
----------------------------------------------------------------------------------------------------------------------------------
PLUGGED_IN BLOCK1_OFFSET
---------- -------------
AUX_NAME
----------------------------------------------------------------------------------------------------------------------------------
FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TI
------------------- ------------------
...
7 5166051 04-MAY-09 8 7 RECOVER READ WRITE 5166051 04-MAY-09
0 0 0
0 0 10485760 8192
/home/oracle/app/oracle/product/10.2/dbs/UNNAMED00007
0 4294967295
UNKNOWN
0
7 rows selected.
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
...
8 TS_A YES NO YES
9 rows selected.
SQL> alter database rename file '/home/oracle/app/oracle/product/10.2/dbs/UNNAMED00007' to '/home/oracle/app/oracle/oradata/test/ts_a.dbf';
Database altered.
SQL> select * from v$datafile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ---------------- ------------------ ---------- ---------- ------- ---------- ------------------ ------------------
UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME
--------------------- ------------------ ------------ ------------------ --------------- -------------- ------------------
BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
----------------------------------------------------------------------------------------------------------------------------------
PLUGGED_IN BLOCK1_OFFSET
---------- -------------
AUX_NAME
----------------------------------------------------------------------------------------------------------------------------------
FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TI
------------------- ------------------
...
7 5166051 04-MAY-09 8 7 RECOVER READ WRITE 5166051 04-MAY-09
0 0 0
10485760 1280 10485760 8192
/home/oracle/app/oracle/oradata/test/ts_a.dbf
0 8192
UNKNOWN
0
7 rows selected.
SQL>
日志:
Mon May 4 08:21:46 2009 alter database rename file '/home/oracle/app/oracle/product/10.2/dbs/UNNAMED00007' to '/home/oracle/app/oracle/oradata/test/ts_a.dbf' Mon May 4 08:21:46 2009 Completed: alter database rename file '/home/oracle/app/oracle/product/10.2/dbs/UNNAMED00007' to '/home/oracle/app/oracle/oradata/test/ts_a.dbf'
6. 重新闪回数据库
flashback database to scn &scn;
SQL> flashback database to scn &scn; Enter value for scn: 5166171 old 1: flashback database to scn &scn new 1: flashback database to scn 5166171 Flashback complete. SQL>
日志:
Mon May 4 08:22:24 2009 flashback database to scn 5166171 Mon May 4 08:22:25 2009 Flashback Restore Start Deleted file /home/oracle/app/oracle/oradata/test/ts_a.dbf Flashback: deleted datafile #7 in tablespace #8 from control file. Flashback: dropped tablespace #8: 'TS_A' from the control file. Flashback Restore Complete Flashback Media Recovery Start parallel recovery started with 3 processes Mon May 4 08:22:25 2009 Recovery of Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0 Mem# 0: /home/oracle/app/oracle/oradata/test/redo03.rdo Mem# 1: /home/oracle/app/oracle/oradata/test/redo03b.rdo Mon May 4 08:22:26 2009 Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0 Mem# 0: /home/oracle/app/oracle/oradata/test/redo01.rdo Mem# 1: /home/oracle/app/oracle/oradata/test/redo01b.rdo Recovery created file /home/oracle/app/oracle/oradata/test/ts_a.dbf Successfully added datafile 7 to media recovery Datafile #7: '/home/oracle/app/oracle/oradata/test/ts_a.dbf' Mon May 4 08:22:26 2009 Incomplete Recovery applied until change 5166172 Flashback Media Recovery Complete Completed: flashback database to scn 5166171
7. 打开数据库, 查询
alter database open read only;
alter database open resetlogs;
select current_scn from v$database;
select * from t_a;
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16006: audit_trail destination incompatible with database open mode
SQL> alter database open resetlogs;
Database altered.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5166406
SQL> select * from t_a;
A
----------
1
SQL>
可以恢复日志:
Mon May 4 08:33:36 2009 alter database open read only Mon May 4 08:33:36 2009 ORA-16006 signalled during: alter database open read only... Mon May 4 08:35:41 2009 alter database open resetlogs Mon May 4 08:35:42 2009 RESETLOGS after incomplete recovery UNTIL CHANGE 5166172 Resetting resetlogs activation ID 1984070862 (0x764284ce) Mon May 4 08:35:53 2009 Setting recovery target incarnation to 4 Mon May 4 08:35:53 2009 Assigning activation ID 1984060563 (0x76425c93) LGWR: STARTING ARCH PROCESSES ARC0 started with pid=15, OS id=20301 Mon May 4 08:35:53 2009 ARC0: Archival started ARC1: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC1 started with pid=16, OS id=20303 Mon May 4 08:35:53 2009 Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: /home/oracle/app/oracle/oradata/test/redo01.rdo Current log# 1 seq# 1 mem# 1: /home/oracle/app/oracle/oradata/test/redo01b.rdo Successful open of redo thread 1 Mon May 4 08:35:53 2009 ARC0: Becoming the 'no FAL' ARCH ARC0: Becoming the 'no SRL' ARCH Mon May 4 08:35:53 2009 ARC1: Becoming the heartbeat ARCH Mon May 4 08:35:53 2009 SMON: enabling cache recovery Mon May 4 08:35:54 2009 Successfully onlined Undo Tablespace 1. Dictionary check beginning Dictionary check complete Mon May 4 08:35:54 2009 SMON: enabling tx recovery Mon May 4 08:35:54 2009 Database Characterset is AL32UTF8 Opening with internal Resource Manager plan where NUMA PG = 1, CPUs = 4 replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC QMNC started with pid=17, OS id=20305 Mon May 4 08:35:56 2009 LOGSTDBY: Validating controlfile with logical metadata Mon May 4 08:35:56 2009 LOGSTDBY: Validation complete Completed: alter database open resetlogs
8. 其它
如果删除表空间同时删除了数据文件(including contents and datafiles), 显然用闪回数据库是无法恢复的
外部链接:
FLASHBACK DATABASE(10gR2)
FLASHBACK DATABASE(11gR1)
-fin-
No comments:
Post a Comment