1 创建表空间space2 1* create tablespace space2 datafile '/data/vdb/u01/product/oracle/oradata/myyun1/sp
1 创建表空间space2
1* create tablespace space2 datafile '/data/vdb/u01/product/oracle/oradata/myyun1/space2.dbf' size 20MSQL> /Tablespace created.SQL> select tablespace_name,status from user_tablespaces;TABLESPACE_NAME STATUS------------------------------ ---------SYSTEM ONLINESYSAUX ONLINEUNDOTBS1 ONLINETEMP ONLINEUSERS ONLINETEST READ ONLYTEST2 READ ONLYSPACE2 ONLINE8 rows selected.# 在表空间里面创建一些数据SQL> create table tmp_10 tablespace space2 as select 'hello' name from dual;Table created.SQL> select * from tmp_10;NAME-----hello2 记录下当前时间点并在rman中进行备份
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;TO_CHAR(SYSDATE,'YY-------------------2016-12-14 20:26:21
[oracle@myyun1 ~]$ rman target /Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 14 09:52:36 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: MYYUN1 (DBID=4226016602)RMAN> backup database;Starting backup at 14-DEC-16using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=40 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00002 name=/data/vdb/u01/product/oracle/oradata/myyun1/sysaux01.dbfinput datafile file number=00001 name=/data/vdb/u01/product/oracle/oradata/myyun1/system01.dbfinput datafile file number=00003 name=/data/vdb/u01/product/oracle/oradata/myyun1/undotbs01.dbfinput datafile file number=00005 name=/data/vdb/u01/product/oracle/oradata/myyun1/test.dbfinput datafile file number=00006 name=/data/vdb/u01/product/oracle/oradata/myyun1/test2.dbfinput datafile file number=00004 name=/data/vdb/u01/product/oracle/oradata/myyun1/users01.dbfinput datafile file number=00007 name=/data/vdb/u01/product/oracle/oradata/myyun1/space2.dbfchannel ORA_DISK_1: starting piece 1 at 14-DEC-16channel ORA_DISK_1: finished piece 1 at 14-DEC-16piece handle=/data/vdb/u01/product/oracle/fast_recovery_area/MYYUN1/backupset/2016_12_14/o1_mf_nnndf_TAG20161214T095248_d519hjmz_.bkp tag=TAG20161214T095248 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:16channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 14-DEC-16channel ORA_DISK_1: finished piece 1 at 14-DEC-16piece handle=/data/vdb/u01/product/oracle/fast_recovery_area/MYYUN1/backupset/2016_12_14/o1_mf_ncsnf_TAG20161214T095248_d519j18o_.bkp tag=TAG20161214T095248 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 14-DEC-16# rman 数据库备份成功3 删除之前的表删除并重新建一个表
-- 把表 TMP_10 删除，然后重新建一个表 TMP_20drop table TMP_10 purge;create table tmp_20 tablespace SPACE2 as select sysdate time_now from dual;TIME_NOW2016-12-14 20:29:31# 在表tmp_20已经创建成功4 关闭数据库进行冷备份
[oracle@myyun1 oradata]$ cp myyun1 /tmp/myyun.back -r[oracle@myyun1 oradata]$ 5 rman启动到nomount状态下恢复控制文件
RMAN> startup nomount;Oracle instance startedTotal System Global Area 588746752 bytesFixed Size 2230592 bytesVariable Size 360711872 bytesDatabase Buffers 222298112 bytesRedo Buffers 3506176 bytesRMAN> restore controlfile from '/data/vdb/u01/product/oracle/fast_recovery_area/MYYUN1/backupset/2016_12_14/o1_mf_ncsnf_TAG20161214T202713_d52gol7w_.bkp';Starting restore at 14-DEC-16using channel ORA_DISK_1channel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:01output file name=/data/vdb/u01/product/oracle/oradata/myyun1/control01.ctloutput file name=/data/vdb/u01/product/oracle/fast_recovery_area/myyun1/control02.ctlFinished restore at 14-DEC-16# 控制文件恢复成功6 rman启动到mount状态下
RMAN> alter database mount;database mountedreleased channel: ORA_DISK_17 通过恢复脚本恢复之前删除掉的表空间space2
RMAN> run{2> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"'; 3> set until time '2016-12-14 09:51:06';4> restore database;5> recover database;6> }# 在rman中用下面的代码恢复恢复成功后打开数据库
RMAN> alter database open;using target database control file instead of recovery catalogRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of alter db command at 12/14/2016 10:29:50ORA-01589: must use RESETLOGS or NORESETLOGS option for database openRMAN> alter database open NORESETLOGS;database opened检查之前的表空间是否存在
SQL> select tablespace_name,status from user_tablespaces;TABLESPACE_NAME STATUS------------------------------ ---------SYSTEM ONLINESYSAUX ONLINEUNDOTBS1 ONLINETEMP ONLINEUSERS ONLINETEST READ ONLYTEST2 READ ONLYSPACE2 ONLINE8 rows selected.# 表空间space2 已经恢复， 1* select tablespace_name,table_name from user_tables where tablespace_name = 'SPACE2'SQL> /TABLESPACE_NAME TABLE_NAME------------------------------ ------------------------------SPACE2 TMP_10# 空间里面的表也恢复了SQL> select * from TMP_10;NAME-----hello
SQL> select * from dba_tables where table_name = 'TMP_20';no rows selected# 但是之前删除表空间space2之后在表空间users下面新建的表tmp_20被恢复掉不在了8 用数据传输工具exp 导出表空间的数据
SQL> alter tablespace space2 read only;Tablespace altered.# 先把表空间设置为只读[oracle@myyun1 ~]$ exp /'/ as sysdba/' file = '/tmp/space2.dump' transport_tablespace=y tablespaces=space2;Export: Release 11.2.0.3.0 - Production on Wed Dec 14 10:37:19 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in US7ASCII character set and UTF8 NCHAR character setserver uses AL32UTF8 character set (possible charset conversion)Note: table data (rows) will not be exportedAbout to export transportable tablespace metadata...For tablespace SPACE2 .... exporting cluster definitions. exporting table definitions. . exporting table TMP_10. exporting referential integrity constraints. exporting triggers. end transportable tablespace metadata exportExport terminated successfully without warnings.# 导出成功
9 重新在rman中备份整个数据库并关机恢复冷备份
冷备份恢复后再把之前的exp导出的表空间文件用imp 导入就可以了。
注意：上面rman恢复之后启动数据库用NORESETLOGS 而不是用RESETLOGS
