对部分表空间作不完全恢复

最新推荐文章于 2021-04-16 01:35:00 发布

csdn_201601

最新推荐文章于 2021-04-16 01:35:00 发布

阅读量272

点赞数

http://grassbell.itpub.net/post/26/32426[@more@]

ft，前两天因为程序的bug，有个服务人员把客户的资料给删了，3天后才知道。然后他们领导找到我，让我搞定。
整个库有170g，他们提供了一个机器只有50g的空间，做全库的不完全恢复是不可能了。后来大师在metalink上找到了这么一个方法，
可以只恢复被删除记录的表所在的表空间，偶搞了一把，比TSPITR 好用多了。

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=223543.1

在恢复用的机器上配置好与源数据库相同的目录，环境变量设好ORACLE_SID和ORACLE_HOME。
拷贝主库的control file过来，将存储rman备份的nsf直接mount过来，也使用相同的目录，这样方便restore。

run
{
--强制所有的数据文件offline，只是为了安全的目的。
sql "alter database mount clone database";

--为需要恢复的数据文件指定新的路径，也是安全考虑，防止在主库上恢复的时候，覆盖主库文件。
--因为这里是在另外一个库上作恢复，所以可以设成和主库一样。
--注意，undo和system是必须要恢复的，呵呵
set newname for datafile 2 to '/disk2/oradata/crmtemp/undotbs01.dbf';
set newname for datafile 69 to '/disk2/oradata/crmtemp/undotbs03.dbf';
set newname for datafile 79 to '/disk4/oradata/crmtemp/undotbs04.dbf';
set newname for datafile 93 to '/data2/oradata/crmtemp/undotbs05.dbf';
set newname for datafile 1 to '/disk1/oradata/crmtemp/system01.dbf';
set newname for datafile 45 to '/disk1/oradata/crmtemp/appdata01.dbf';
set newname for datafile 46 to '/disk1/oradata/crmtemp/appdata02.dbf';

--restore 表空间
restore tablespace system,UNDOTBS1,APPDATA1M;

--其他的数据文件offline drop
sql "alter database datafile 3,4,5,6,7,8,13,14,15,16,18,19,20,23,25,26,27,28,29,30,31,33,35,134 offline drop";

--将newname的变化应用的controlfile
switch datafile all;

--把需要恢复的数据文件online
sql "alter database datafile 2, 69, 79, 93, 1, 45, 46 online";

--不完全恢复，关键就是这里使用了skip tablespace,把不需要恢复的ts跳过
recover database until TIME "TO_DATE('2005-6-7 15:00:00', 'yyyy-mm-dd hh24:mi:ss')"
skip tablespace TEMP,
INDX,
TOOLS,
ADMIN_DATA1M,
TPCRM,
...;
}

恢复完后，需要重建controlfile，因为目前的controlfile是clone状态的，需要
alter database backup controlfile to trace;
然后提取创建脚本。因为没有online redo，所以使用resetlog的那种方式。

创建完毕后：
alter database open resetlogs;

数据库打开，马上把需要的表exp出来：
exp userid=zeus/zeus file=/home/oracle/xiaojinyu.dmp tables=(CS_COL_CONTENT,CS_COLUMNS,SAMPLE,SAMPLE_GROUP) rows=Y

然后imp到开发库上，交给应用处理。

另外，大家有时间可以测试一下，能否直接在sqlplus 里面直接作
recover database until TIME "TO_DATE('2005-6-7 15:00:00', 'yyyy-mm-dd hh24:mi:ss')"
skip tablespace TEMP.....;

真的没时间搞了。

利用RMAN来TSPITR较为简单. [回复]

这和RAMN来进行TSPITR有些类似,利用RMAN本来提供TSPITR方法也很容易实现你的这个要求.

利用RMAN进行TSPITR步骤简单,也非常清晰.命令的写法也较为简单. 而手工进行TSPITR步骤实在太复杂.

sydongsun 评论于: 2005.06.17 07:09

作了个试验,不是RMAN的操作 [回复]

作了个试验,不是RMAN的操作

SQL> conn hr/hr@backup
已连接。
SQL> set time on

13:52:34 SQL> select count(*) from operator;

COUNT(*)
----------
1293

已用时间: 00: 00: 00.00
13:53:14 SQL> delete operator;

已删除1293行。

已用时间: 00: 00: 00.00
13:53:19 SQL> commit;

提交完成。

已用时间: 00: 00: 00.00
13:53:21 SQL> conn scott/tiger@backup;
已连接。
13:53:48 SQL> delete emp;

已删除12行。

已用时间: 00: 00: 00.00
13:53:54 SQL> delete dept;

已删除4行。

已用时间: 00: 00: 00.00
13:53:58 SQL> commit;
13:54:00 SQL> conn hr/hr@backup;
已连接。
13:54:10 SQL> alter system switch logfile;

系统已更改。

已用时间: 00: 00: 00.01
13:54:16 SQL> /

系统已更改。

已用时间: 00: 00: 00.01
13:54:17 SQL> /

系统已更改。

已用时间: 00: 00: 05.02
13:54:23 SQL> /

系统已更改。

已用时间: 00: 00: 00.01
13:54:25 SQL> alter database backup controlfile to 'e:ct.ctl';

数据库已更改。

已用时间: 00: 00: 00.05
13:58:18 SQL>

+++++++++++++++++++++++++++++++++++++++++++++++++
copy需要的三个数据文件，和备份的控制文件到本机进行恢复。

14:00:53 SQL> startup nomount;
ORACLE 例程已经启动。

Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
14:01:01 SQL> alter database mount;
alter database mount
*
ERROR 位于第 1 行:
ORA-01991: ???????'C:oracleora92DATABASEPWDbackup.ORA'

14:01:12 SQL> alter database rename file 'H:ORADATAU2SYSTEM01.DBF'
14:01:44 2 to 'C:oraclebackORADATAu2system01.dbf';

数据库已更改。

14:01:54 SQL> alter database rename file 'H:ORADATAU2undotbs01.DBF'
14:02:01 2 to 'C:oraclebackORADATAu2undotbs01.dbf';

数据库已更改。

14:02:07 SQL> alter database rename file 'H:ORADATAU2user_data01.DBF'
14:02:19 2 to 'C:oraclebackORADATAu2user_data01.dbf';

数据库已更改。

14:02:26 SQL> alter database datafile 'H:ORADATAU2USERS01.DBF' offline drop;

数据库已更改。

14:02:34 SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

C:ORACLEBACKORADATAU2SYSTEM01.DBF
C:ORACLEBACKORADATAU2UNDOTBS01.DBF
H:ORADATAU2USERS01.DBF
C:ORACLEBACKORADATAU2USER_DATA01.DBF

14:02:42 SQL> recover database until time '2005-08-10 12:25:13' skip tablespace
temp,users;
ORA-00905: ?????

14:03:05 SQL> alter session set nls_language='english';

Session altered.

14:03:21 SQL> recover database until time '2005-08-10 12:25:13' skip tablespace
temp,users;
ORA-00905: missing keyword

14:03:24 SQL> recover database until time '2005-08-10 12:25:13';
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

14:03:31 SQL> recover database until time '2005-08-10 13:52:33';
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

14:05:24 SQL> recover database until time '2005-08-10 13:52:33' using backup con
trolfile;
ORA-00279: change 52768 generated at 08/10/2005 11:09:48 needed for thread 1
ORA-00289: suggestion : C:ORACLEBACKORADATAARC1_124.DBF
ORA-00280: change 52768 for thread 1 is in sequence #124

14:05:48 Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 52997 generated at 08/10/2005 11:15:46 needed for thread 1
ORA-00289: suggestion : C:ORACLEBACKORADATAARC1_125.DBF
ORA-00280: change 52997 for thread 1 is in sequence #125
ORA-00278: log file 'C:ORACLEBACKORADATAARC1_124.DBF' no longer needed for
this recovery

14:06:02 Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 53050 generated at 08/10/2005 11:15:47 needed for thread 1
ORA-00289: suggestion : C:ORACLEBACKORADATAARC1_126.DBF
ORA-00280: change 53050 for thread 1 is in sequence #126
ORA-00278: log file 'C:ORACLEBACKORADATAARC1_125.DBF' no longer needed for
this recovery

14:06:04 Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 53113 generated at 08/10/2005 11:15:48 needed for thread 1
ORA-00289: suggestion : C:ORACLEBACKORADATAARC1_127.DBF
ORA-00280: change 53113 for thread 1 is in sequence #127
ORA-00278: log file 'C:ORACLEBACKORADATAARC1_126.DBF' no longer needed for
this recovery

14:06:06 Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 53169 generated at 08/10/2005 11:15:50 needed for thread 1
ORA-00289: suggestion : C:ORACLEBACKORADATAARC1_128.DBF
ORA-00280: change 53169 for thread 1 is in sequence #128
ORA-00278: log file 'C:ORACLEBACKORADATAARC1_127.DBF' no longer needed for
this recovery

14:06:07 Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 53191 generated at 08/10/2005 11:16:33 needed for thread 1
ORA-00289: suggestion : C:ORACLEBACKORADATAARC1_129.DBF
ORA-00280: change 53191 for thread 1 is in sequence #129
ORA-00278: log file 'C:ORACLEBACKORADATAARC1_128.DBF' no longer needed for
this recovery

14:06:08 Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 53195 generated at 08/10/2005 11:16:40 needed for thread 1
ORA-00289: suggestion : C:ORACLEBACKORADATAARC1_130.DBF
ORA-00280: change 53195 for thread 1 is in sequence #130
ORA-00278: log file 'C:ORACLEBACKORADATAARC1_129.DBF' no longer needed for
this recovery

14:06:09 Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 54618 generated at 08/10/2005 12:26:57 needed for thread 1
ORA-00289: suggestion : C:ORACLEBACKORADATAARC1_131.DBF
ORA-00280: change 54618 for thread 1 is in sequence #131
ORA-00278: log file 'C:ORACLEBACKORADATAARC1_130.DBF' no longer needed for
this recovery

14:06:10 Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 54620 generated at 08/10/2005 12:26:58 needed for thread 1
ORA-00289: suggestion : C:ORACLEBACKORADATAARC1_132.DBF
ORA-00280: change 54620 for thread 1 is in sequence #132
ORA-00278: log file 'C:ORACLEBACKORADATAARC1_131.DBF' no longer needed for
this recovery

14:06:11 Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.
14:06:12 SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-create online log 'H:ORADATAU3REDO01.LOG'
ORA-27040: skgfrcre: create error, unable to create file
OSD-04002: N^7(4r?*ND6!#

14:06:23 SQL> alter database rename file 'H:ORADATAU3REDO01.LOG'
14:06:32 2 to 'C:oraclebackORADATAu3redo01.log';

Database altered.

14:06:39 SQL> alter database rename file 'H:ORADATAU3REDO02.LOG'
14:06:45 2 to 'C:oraclebackORADATAu3redo02.log';

Database altered.

14:06:50 SQL> alter database rename file 'H:ORADATAU3REDO03.LOG'
14:06:55 2 to 'C:oraclebackORADATAu3redo03.log';

Database altered.

14:07:01 SQL> alter database open resetlogs;

Database altered.

14:07:13 SQL> conn hr/hr
已连接。
14:07:32 SQL> select count(*) from ias_parameter_mst;
select count(*) from ias_parameter_mst
*
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在

14:07:34 SQL> select count(*) from operator;

COUNT(*)
----------
1293

14:07:51 SQL> conn scott/tiger
已连接。
14:08:02 SQL> select count(*) from dept;
select count(*) from dept
*
ERROR 位于第 1 行:
ORA-00376: 此时无法读取文件 3
ORA-01110: 数据文件 3: 'H:ORADATAU2USERS01.DBF'

14:08:34 SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/82387/viewspace-1027511/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/82387/viewspace-1027511/