使用backup as copy 备份部分表空间并做不完全恢复
来源：互联网 发布：阿里云合作公司有哪些 编辑：程序博客网 时间：2024/06/08 18:40
本文是测试在不停dataguard情况下,从dataguard用backup as copy方法导出部分表空间， 并基于这些表空间,恢复出一个源库的子集。,然后做不完全恢复, 恢复到一个指定的时间点。
（ 重点,在dataguard不停机情况下,不影像dataguard的正常使用）
（另外, backup as copy tablesapce， 不会在control file中留下记录,因此也可以不用考虑是否会对同城DG上进行的日常备份是否有影响 ）
测试环境说明:
(一) 准备工作
要导入的部分表空间,以及基于这些部分表空间建库的目录是
-/use01/app/fantest1
检查dataguard的情况
这个dataguard目前是正常同步的
15:28:23sys@-physical standby> select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
------------------------------ ------------------------------ ------------------------------
MOUNTED PHYSICAL STANDBY ld0cmadm
15:28:27 sys@d0cmadm-physical standby> select status,process,sequence#,blocks,block# from v$managed_standby;
STATUS PROCESS SEQUENCE# BLOCKS BLOCK#
-------------------- --------- ---------- ---------- ----------
CONNECTED ARCH 0 0 0
CLOSING ARCH 4936 1 1
CONNECTED ARCH 0 0 0
CLOSING ARCH 4935 2 1
CLOSING ARCH 4932 6 1
IDLE RFS 0 0 0
IDLE RFS 4937 1 616
IDLE RFS 0 0 0
APPLYING_LOG MRP0 4937 102400 615
我们要导出部分表空间,以及基于这些表空间建库的目录是/use01/app/fantest1
cnsz081384: > mkdir fantest1
cnsz081384: > chmod -R 777 fantest1
cnsz081384: > cd fantest1
cnsz081384: > pwd
/use01/app/fantest1
我们计划导出的表空间是TSPITRDATA,USERS
(本来users是可以不用的,但因为我们的很多用户默认是users表空间,所以一起导出)
检查要迁移的表空间自包含性
17:33:10 > begin
sys.dbms_tts.transport_set_check(ts_list=>'TSPITRDATA,USERS',incl_constraints=>TRUE);
3 end;
4 /
PL/SQL procedure successfully completed
17:33:19 > select * from sys.transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
另外,按照oracle要求,还必须要导出system, sysaxu，undo表空间,否则无法建库
测试模拟:
为了模拟dataguard是不断是有事务进行的情况,我们在对应的主库不间断做dml,对所有的表空间上做dml,包括导出和不导出的。以及SYSTEM表空间。
(二) 开始导出表空间
现在开始备份表空间,备份的语句如下
backup as copy tablespace SYSTEM format '/use01/app/fantest1/%U.dbf' ;
backup as copy tablespace SYSAUX format '/use01/app/fantest1/%U.dbf' ;
backup as copy tablespace UNDOTBS1 format '/use01/app/fantest1/undo%U.dbf' ;
backup as copy tablespace TSPITRDATA format '/use01/app/fantest1/%N%f.dbf' ;
backup as copy tablespace USERS format '/use01/app/fantest1/%N%f.dbf' ;
先备份system表空间,由于在备份system表空间时候,要自动备份控制文件和spifle,所以这里的格式只能使用%U,不能用对普通表空间的%N%f方式：
backup as copy tablespace SYSTEM format '/use01/app/fantest1/%U.dbf' ;
cnsz081384: > rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Aug 11 16:18:13 2017
RMAN> backup as copy tablespace SYSTEM format '/use01/app/fantest1/%U.dbf' ;
RMAN>
System表空间备份后,会有3个文件,其中
cnsz081384: > ls -l
total 2065980
---备份的spfile
-rw-r----- 1 odsz11g asmadmin 98304 Aug 11 16:19 1ksbl808_1_1.dbf
---备份的控制文件
-rw-r----- 1 odsz11g asmadmin 18300928 Aug 11 16:19 cf_D-_id-3340592182_1jsbl807.dbf
---备份的system表空间
-rw-r----- 1 odsz11g asmadmin 2097160192 Aug 11 16:19 data_-3340592182_TS-SYSTEM_FNO-1_1isbl7ug.dbf
备份SYSAUX
backup as copy tablespace SYSAUX format '/use01/app/fantest1/%U.dbf' ;
cnsz081384:> rman target
RMAN> backup as copy tablespace SYSAUX format '/use01/app/fantest1/%U.dbf' ;
备份undo
RMAN> backup as copy tablespace UNDOTBS1 format '/use01/app/fantest1/undo%U.dbf' ;
备份TSPITRDATA 和users表空间: 普通表空间的format格式为%N%f
RMAN> backup as copy tablespace TSPITRDATA format '/use01/app/fantest1/%N%f.dbf' ;
RMAN> backup as copy tablespace USERS format '/use01/app/fantest1/%N%f.dbf' ;
重命名system, sysaux,undo表空间的数据文件。
mv data_D-D0CMADM_I-3340592182_TS-SYSAUX_FNO-2_1lsbl8ba.dbf sysaux01.dbf
mv data_D-D0CMADM_I-3340592182_TS-SYSTEM_FNO-1_1isbl7ug.dbf system01.dbf
mv undodata_D-D0CMADM_I-3340592182_TS-UNDOTBS1_FNO-3_1msbl8g2.dbf undo01.dbf
备份之后的全部导出数据文件:
cnsz081384:d0cmadm > ls -l
total 13612736
-rw-r----- 1 odsz11g asmadmin 98304 Aug 11 16:19 1ksbl808_1_1.dbf
-rw-r----- 1 odsz11g asmadmin 18300928 Aug 11 16:19 cf_D_id-3340592182_1jsbl807.dbf
-rw-r----- 1 odsz11g asmadmin 3963625472 Aug 11 16:26 sysaux01.dbf
-rw-r----- 1 odsz11g asmadmin 2097160192 Aug 11 16:19 system01.dbf
-rw-r----- 1 odsz11g asmadmin 524296192 Aug 11 16:30 TSPITRDATA11.dbf
-rw-r----- 1 odsz11g asmadmin 104865792 Aug 11 16:31 TSPITRDATA12.dbf
-rw-r----- 1 odsz11g asmadmin 104865792 Aug 11 16:31 TSPITRDATA13.dbf
-rw-r----- 1 odsz11g asmadmin 104865792 Aug 11 16:31 TSPITRDATA14.dbf
-rw-r----- 1 odsz11g asmadmin 104865792 Aug 11 16:31 TSPITRDATA15.dbf
-rw-r----- 1 odsz11g asmadmin 104865792 Aug 11 16:31 TSPITRDATA16.dbf
-rw-r----- 1 odsz11g asmadmin 104865792 Aug 11 16:31 TSPITRDATA17.dbf
-rw-r----- 1 odsz11g asmadmin 104865792 Aug 11 16:31 TSPITRDATA18.dbf
-rw-r----- 1 odsz11g asmadmin 6077554688 Aug 11 16:30 undo01.dbf
-rw-r----- 1 odsz11g asmadmin 524296192 Aug 11 16:36 USERS4.dbf
为了测试我们的recover,我们在表空间copy完之后再创建一些测试表。
在我们执行recover之后,这个表也应该同样被恢复。
d0cmadm> create table backts_test_after tablespace TSPITRDATA as select * from dba_objects where rownum<10;
Table created
(三) 基于导出的表空间新建库:
1. 基于这些表空间空间建库:
先创建init.ora文件， 指定db_name, db_recovery_file_dest， 以及内存参数
cnsz081384:fantest1 > more initfantest1.ora
DB_NAME=fantest1
DB_UNIQUE_NAME=fantest1
CONTROL_FILES=/use01/app/fantest1/fantest1.ctl
db_recovery_file_dest = /use01/app/fantest1/archivelog/
db_recovery_file_dest_size =100G
COMPATIBLE=11.2.0
DB_BLOCK_SIZE=8192
REMOTE_LOGIN_PASSWORDFILE=exclusive
db_cache_size=800M
shared_pool_size=500M
pga_aggregate_target=512M
注意db_recovery_file_dest的设置:
这涉及到recover时候寻找archivelog的路径和文件名,也就是我们要copy archive的路径。
方式一: 如果源库使用omf管理, 则配置DB_CREATE_FILE_DEST来设置archive log的路径,这个方式需要创建SID/ARCHIVELOG这样的子目录,以满足omf的结构。
方式二: 如果源库不是使用omf,用LOG_ARCHIVE_DEST_1和log_archive_format,并且复制的archivelog也要和这个格式匹配
如果要把使用asm的omf管理中的归档日志 (这里指源库是asm的omf管理), 使用LOG_ARCHIVE_DEST_1,会比较麻烦,建议还是使用db_recovery_file_des方式设置
因为
例如
1．Asm omf管理中的归档文件的格式为: thread_1_seq_4973.5327.951755571
其中
1 是%t，
4973 是%s
5327. 951755571应该是asm的文件名后缀,不在归档文件格式中。
2. 使用LOG_ARCHIVE_DEST_1的话就必须指定log_archive_format参数
3. 而log_archive_format必须要求%s, %t, %r同时存在, 否则报错
15:04:17 idle> startup mount
ORA-19905: log_archive_format must contain %s, %t and %r
4. 而没有直接的方法把thread_1_seq_4973.5327.951755571转为%s, %t, %r 的方式。
参考: 归档的格式控制如下
%s log sequence number
%S log sequence number, zero filled
%t thread number
%T thread number, zero filled
%a activation ID %d database ID
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database
NOTE,
1. 你会问在recover的时候，不是可以手工输入archivelog的路径和文件名么,但那样的话要逐个手工输入,和麻烦也容易出错。
2. 如果把归档日志备份为备份集,在用catalog在新库注册,再来recover是否可以， 这个我没有测试。
5. 配置oracle环境变量,启动到nomount状态
cnsz081384: > . ./profile
Please Enter database sid:fantest1
fantest1
启动到nomount
17:16:48 idle> startup nomount
ORACLE instance started.
Total System Global Area 1419837440 bytes
Fixed Size 2253144 bytes
Variable Size 553651880 bytes
Database Buffers 838860800 bytes
Redo Buffers 25071616 bytes
6. 创建控制文件
只包括我们导出的表空间,
17:20:58 idle> CREATE CONTROLFILE REUSE SET DATABASE FANTEST1 RESETLOGS FORCE LOGGING ARCHIVELOG
2 MAXLOGFILES 32
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 8
6 MAXLOGHISTORY 1168
7 LOGFILE
8 GROUP 1 (
9 '/use01/app/fantest1/group1') SIZE 50M BLOCKSIZE 512,
10 GROUP 2 (
11 '/use01/app/fantest1/group2') SIZE 50M BLOCKSIZE 512,
12 GROUP 3 (
13 '/use01/app/fantest1/group3') SIZE 50M BLOCKSIZE 512
14 DATAFILE
15 '/use01/app/fantest1/sysaux01.dbf',
16 '/use01/app/fantest1/system01.dbf',
17 '/use01/app/fantest1/TSPITRDATA11.dbf',
(略)
25 '/use01/app/fantest1/undo01.dbf',
26 '/use01/app/fantest1/USERS4.dbf'
27 CHARACTER SET AL32UTF8;
Control file created.
(四) 对新建库做recover
１．Copy archivelog
创建存储放archivelog的目录
mkdir /use01/app/fantest1/archivelog
chmod 777 archivelog
cnsz081384: >mkdir FANTEST1
cnsz081384: > cd FANTEST1/
cnsz081384: > mkdir archivelog
cnsz081384: > cd archivelog/
cnsz081384: > mkdir 2017_08_11
cnsz081384: > mkdir 2017_08_12
cnsz081384: > mkdir 2017_08_13
cnsz081384: > mkdir 2017_08_14
cnsz081384: > chmod -R 777 archivelog
在dataguard库生成copy归档日志的脚本
set linesize200
col name format 'a200'
set pagesize300
select 'cp '||name
||' /use01/app/fantest1/archivelog/FANTEST1/archivelog/'||
substr( name,instr( name,'/',1,3)+1)
from v$archived_log where first_time>to_date('2017/8/11 14:00:00','yyyy/mm/dd hh24:mi:ss')and dest_id=1;
结果如下:
cp +FRA_SMALL_MDG/archivelog/2017_08_11/thread_1_seq_4929.5283.951750775 /use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_11/thread_1_seq_4929.5283.951750775
cp +FRA_SMALL_MDG/archivelog/2017_08_11/thread_1_seq_4930.5284.951751105 /use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_11/thread_1_seq_4930.5284.951751105
（省略。。。。）
２．执行上面生成的copy脚本
启动数据库到mount状态,
开始做recover,我们使用until time的方式来做recover.
15:41:13 idle> recover database using backup controlfile UNTIL TIME '2017-8-11 19:00:00';
ORA-00279: change 10093148066334 generated at 08/11/2017 16:17:55 needed for thread 1
ORA-00289: suggestion : /use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_11/thread_1_seq_4948.5302.951754703
ORA-00280: change 10093148066334 for thread 1 is in sequence #4948
15:41:19 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
(省略。。。。)
ORA-00279: change 10093150198177 generated at 08/11/2017 16:46:49 needed for thread 1
ORA-00289: suggestion : /use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_11/thread_1_seq_5000.5354.951756443
ORA-00280: change 10093150198177 for thread 1 is in sequence #5000
ORA-00278: log file '/use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_11/thread_1_seq_4999.5353.951756409' no longer needed for this recovery
ORA-00279: change 10093150240697 generated at 08/11/2017 16:47:22 needed for thread 1
ORA-00289: suggestion : /use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_11/thread_1_seq_5001.5355.951775253
ORA-00280: change 10093150240697 for thread 1 is in sequence #5001
ORA-00278: log file '/use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_11/thread_1_seq_5000.5354.951756443'no longer needed for this recovery
Log applied.
Media recovery complete.
可以分阶段RECOVER,只要这个时间点上对应的archive log存在。
15:43:05 idle> recover database using backup controlfile UNTIL TIME '2017-8-12 19:00:00';
ORA-00279: change 10093150250721 generated at 08/11/2017 19:00:00 needed for thread 1
ORA-00289: suggestion : /use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_11/thread_1_seq_5001.5355.951775253
ORA-00280: change 10093150250721 for thread 1 is in sequence #5001
ORA-00279: change 10093150363241 generated at 08/12/2017 18:05:37 needed for thread 1
ORA-00289: suggestion : /use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_12/thread_1_seq_5025.5379.951847549
ORA-00280: change 10093150363241 for thread 1 is in sequence #5025
ORA-00278: log file '/use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_12/thread_1_seq_5024.5378.951847537' no longer needed for this recovery
ORA-00279: change 10093150364526 generated at 08/12/2017 18:05:49 needed for thread 1
ORA-00289: suggestion : /use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_12/thread_1_seq_5026.5380.951861941
ORA-00280: change 10093150364526 for thread 1 is in sequence #5026
ORA-00278: log file '/use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_12/thread_1_seq_5025.5379.951847549' no longer needed for this recovery
Log applied.
Media recovery complete.
使用open resetlogs打开数据库。
16:15:26 idle> alter database open resetlogs;
Database altered.
新建库完成:
(五) 建库后的后续操作
把那些没有导出的表空间设置为offline,或者drop
17:33:09sys@fantest1-primary> select b.file_name,b.tablespace_name,b.online_status from dba_data_files b;
FILE_NAME TABLESPACE_NAME ONLINE_
------------------------------------------------------------ ------------------------------ -------
/app/fantest1/system01.dbf SYSTEM SYSTEM
/use01/app/fantest1/sysaux01.dbf SYSAUX ONLINE
/use01/app/fantest1/undo01.dbf UNDOTBS1 ONLINE
/use01/app/fantest1/USERS4.dbf USERS ONLINE
/use01/dev/oracle/11g/app/oracle/product/11.2.0.4.5/dbs/MISSI ESGDATA RECOVER
NG00005
/use01/dev/oracle/11g/app/oracle/product/11.2.0.4.5/dbs/MISSI UMCUSTDATA RECOVER
NG00006
/use01/dev/oracle/11g/app/oracle/product/11.2.0.4.5/dbs/MISSI ETLLOGDATA RECOVER
NG00007
/use01/dev/oracle/11g/app/oracle/product/11.2.0.4.5/dbs/MISSI ETLLOGIDX RECOVER
NG00008
/use01/dev/oracle/11g/app/oracle/product/11.2.0.4.5/dbs/MISSI BRMSDATA RECOVER
NG00009
/use01/dev/oracle/11g/app/oracle/product/11.2.0.4.5/dbs/MISSI MQCPDATA RECOVER
NG00010
/use01/app/fantest1/TSPITRDATA11.dbf TSPITRDATA ONLINE
/use01/app/fantest1/TSPITRDATA12.dbf TSPITRDATA ONLINE
/use01/app/fantest1/TSPITRDATA13.dbf TSPITRDATA ONLINE
/use01/app/fantest1/TSPITRDATA14.dbf TSPITRDATA ONLINE
/use01/app/fantest1/TSPITRDATA15.dbf TSPITRDATA ONLINE
/use01/app/fantest1/TSPITRDATA16.dbf TSPITRDATA ONLINE
/use01/app/fantest1/TSPITRDATA17.dbf TSPITRDATA ONLINE
/use01/app/fantest1/TSPITRDATA18.dbf TSPITRDATA ONLINE
设置为offline
ALTER TABLESPACE ESGDATA OFFLINE IMMEDIATE;
ALTER TABLESPACE UMCUSTDATA OFFLINE IMMEDIATE;
ALTER TABLESPACE ETLLOGDATA OFFLINE IMMEDIATE;
ALTER TABLESPACE ETLLOGIDX OFFLINE IMMEDIATE;
ALTER TABLESPACE BRMSDATA OFFLINE IMMEDIATE;
ALTER TABLESPACE MQCPDATA OFFLINE IMMEDIATE
再创建新的temp表空间，以及修改数据库的默认temp表空间。
原来的temp表空间存在,但没有数据文件。
17:41:50 sys@fantest1-primary> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
17:43:09 2 '/use01/app/fantest1/temp01.dbf' SIZE 100M AUTOEXTEND ON
17:43:09 3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;
Tablespace created.
17:43:09 sys@fantest1-primary> alter database default temporary tablespace temp2;
Database altered.
17:45:24 sys@fantest1-primary> select username,temporary_tablespace from dba_users
17:45:31 2 ;
USERNAME TEMPORARY_TABLESPACE
-------------------- ------------------------------
TSPITRDATA TEMP2
完成
(六) 其它注意事项：
指定的恢复时间,不要超过archive log的时间,也不要超过当前时间,否则会报错.
AUTO
ORA-00308: cannot open archived log '/use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_14/o1_mf_1_5053_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
而且报错之后,, suggestion也变了,输入AUTO找不到对应的归档文件,只能手工输入文件。
16:10:38 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_14/thread_1_seq_5057.5411.952012919
ORA-00279: change 10093150571082 generated at 08/14/2017 16:01:58 needed for thread 1
ORA-00289: suggestion : /use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_14/o1_mf_1_5058_%u_.arc
ORA-00280: change 10093150571082 for thread 1 is in sequence #5058
ORA-00278: log file '/use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_14/thread_1_seq_5057.5411.952012919' no longer needed for this recovery
重启一下数据库,可以让recover提示中的suggesttion回到正确的形式。
16:13:40 idle> startup mount
ORACLE instance started.
Total System Global Area 1419837440 bytes
Fixed Size 2253144 bytes
Variable Size 553651880 bytes
Database Buffers 838860800 bytes
Redo Buffers 25071616 bytes
Database mounted.
16:14:00 idle> recover database using backup controlfile UNTIL TIME '2017-8-14 16:06:00';
ORA-00279: change 10093150571089 generated at 08/14/2017 16:02:02 needed for thread 1
ORA-00289: suggestion : /use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_14/thread_1_seq_5059.5413.952013223
ORA-00280: change 10093150571089 for thread 1 is in sequence #5059
16:14:04 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
Log applied.
Media recovery complete.
手工输入文件的顺序不对,会报下面的错误。
16:10:47 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_14/thread_1_seq_5059.5413.952013223
ORA-00310: archived log contains sequence 5059; sequence 5058 required
ORA-00334: archived log: '/use01/app/fantest1/archivelog/FANTEST1/archivelog/2017_08_14/thread_1_seq_5059.5413.952013223'
- 使用backup as copy 备份部分表空间并做不完全恢复
- BACKUP AS COPY 恢复数据文件
- 用备份控制文件做不完全恢复下的完全恢复（数据文件备份<旧>--新建表空间--控制文件备份<次新>--日志归档文件<新>）
- 用备份控制文件做不完全恢复下的完全恢复（全备<老>--备份控制文件<次新>--新建表空间andy--日志文件<新>）
- 用备份控制文件做不完全恢复下的完全恢复（全备<老>--备份控制文件<次新>--删除表空间andy--日志文件<新>）
- backup as copy的强大
- 用户管理热备份还原drop掉的表空间(不完全恢复的情况)
- 使用rman恢复部分表空间
- 冷备份---不完全恢复
- BACKUP TABLESPACE（备份表空间）
- xtrabackup 备份mysql数据库三：partial backup 部分备份及恢复
- 备份恢复- 完全恢复和不完全恢复
- 通过rman backup as copy移动数据文件
- 冷备份----不完全恢复cancel
- Oracle备份与不完全恢复
- 使用表空间的备份 恢复IBM DB2数据库
- 如何使用 RMAN 异机恢复部分表空间
- Oracle备份恢复-手动不完全恢复
- jQuery—Ajax
- Mapped Statements collection does not contain value for错误
- Voip网络电话之PJSIP（二）
- 自媒体淘客必备的基础知识你知道这些吗
- 达内云笔记(五) Mybatis复杂SQL问题
- 使用backup as copy 备份部分表空间并做不完全恢复
- 1007. 素数对猜想 (20)
- apache安装笔记
- Codeforces 600B Queries about less or equal elements(二分、upper_bound()的应用)
- AIDE使用
- java使用Jsoup解析html字符串
- PHP 回调函数
- javaScript 继承
- 深度学习_资料汇总链接（目标检测/可视化/目标识别/自然语言处理/OCR）
