[20160923]取出备份集的archivelog文件.txt-白红宇

[20160923]取出备份集的archivelog文件.txt

--这个测试来源1次帮别人解决问题时遇到的情况，当时需要使用logminer分析archivelog文件，因为要求对方把archivelog拿过来在我

--的电脑分析。前提是要使用 EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS); 生成数据字典文件在

--归档日志中，可以参考我以前写的blog，链接：

=> [20141208]使用logminer看远程归档文件.txt

=> [20141210]使用logminer看远程归档文件补充

--结果对方使用rman把需要的archive备份传了过来，当时要在我的电脑取出archivelog有点急，请求对方一个文件(压缩)传给我，

--这样解决问题。今天有空，看看如果使用备份集是archivelog，在别的机器如何取出。

1.环境：

SCOTT@book> @ &r/ver1

PORT_STRING VERSION BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--//建立archivelog的备份。

RMAN> backup archivelog all format '/u01/backup/archive_%U';

Starting backup at 2016-09-23 10:52:27

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=6 RECID=2 STAMP=923304579

input archived log thread=1 sequence=7 RECID=3 STAMP=923309427

input archived log thread=1 sequence=8 RECID=4 STAMP=923309458

input archived log thread=1 sequence=9 RECID=5 STAMP=923309467

input archived log thread=1 sequence=10 RECID=6 STAMP=923309547

channel ORA_DISK_1: starting piece 1 at 2016-09-23 10:52:27

channel ORA_DISK_1: finished piece 1 at 2016-09-23 10:52:28

piece handle=/u01/backup/archive_03rgh5fb_1_1 tag=TAG20160923T105227 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2016-09-23 10:52:28

$ cd /u01/app/oracle/archivelog/book

$ ls -l

total 46464

-rw-r----- 1 oracle oinstall 1024 2016-09-23 10:52:27 1_10_896605872.dbf

-rw-r----- 1 oracle oinstall 11609600 2016-09-23 09:29:39 1_6_896605872.dbf

-rw-r----- 1 oracle oinstall 35895808 2016-09-23 10:50:27 1_7_896605872.dbf

-rw-r----- 1 oracle oinstall 2048 2016-09-23 10:50:58 1_8_896605872.dbf

-rw-r----- 1 oracle oinstall 1536 2016-09-23 10:51:07 1_9_896605872.dbf

2.测试是否可以取出：

--在家里的机器是否可以取出里面的文件archivelog。

SYS@test> @ ver1

PORT_STRING VERSION BANNER CON_ID

------------------------------ -------------- -------------------------------------------------------------------------------- ----------

IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0

RMAN> catalog start with 'd:\temp\dd\archive_03rgh5fb_1_1';

using target database control file instead of recovery catalog

searching for all files that match the pattern d:\temp\dd\archive_03rgh5fb_1_1

List of Files Unknown to the Database

=====================================

File Name: D:\TEMP\DD\archive_03rgh5fb_1_1

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

no files cataloged

List of Files Which Were Not Cataloged

=======================================

File Name: D:\TEMP\DD\archive_03rgh5fb_1_1

RMAN-07518: Reason: Foreign database file DBID: 1337401710 Database Name: BOOK

--数据库不同。dbid也不一样，不行catalog。

--如何取出里面的archivelog呢？

--编写脚本，实际上这个脚本就是安装建立数据库的脚本改了一下。

--可以参考链接http://blog.itpub.net/267265/viewspace-2036568/=>[20160304]奇怪的回滚段2.txt

set verify off;

set echo off;

set serveroutput on;

select TO_CHAR(systimestamp,'YYYYMMDD HH24:MI:SS') from dual;

variable devicename varchar2(255);

declare

omfname varchar2(512) := NULL;

done boolean;

begin

dbms_output.put_line(' ');

dbms_output.put_line(' Allocating device.... ');

dbms_output.put_line(' Specifying datafiles... ');

:devicename := dbms_backup_restore.deviceAllocate;

dbms_output.put_line(' Specifing datafiles... ');

-- dbms_backup_restore.restoreSetDataFile;

-- dbms_backup_restore.restoreDataFileTo(1, '/tmp/system01.dbf', 0, 'SYSTEM');

dbms_backup_restore.restoresetarchivedlog(DESTINATION=>'d:/temp/dd');

dbms_backup_restore.restorearchivedlog(1, 6);

dbms_backup_restore.restorearchivedlog(1, 7);

dbms_backup_restore.restorearchivedlog(1, 8);

dbms_backup_restore.restorearchivedlog(1, 9);

dbms_backup_restore.restorearchivedlog(1, 10);

dbms_output.put_line(' Restoring ... ');

dbms_backup_restore.restoreBackupPiece('d:/temp/dd/archive_03rgh5fb_1_1', done);

if done then

dbms_output.put_line(' Restore done.');

else

dbms_output.put_line(' ORA-XXXX: Restore failed');

end if;

dbms_backup_restore.deviceDeallocate;

end;

--//执行如上脚本：

SYS@test> @ arch.rman

TO_CHARSYSTIMEST

-----------------

20160926 22:36:37

Allocating device....

Specifying datafiles...

Specifing datafiles...

Restoring ...

Restore done.

PL/SQL procedure successfully completed.

D:\temp\dd>ls -l ARC00*

-rw-rw-rw- 1 user group 11609600 Sep 26 22:36 ARC0000000006_0896605872.0001

-rw-rw-rw- 1 user group 35895808 Sep 26 22:36 ARC0000000007_0896605872.0001

-rw-rw-rw- 1 user group 2048 Sep 26 22:36 ARC0000000008_0896605872.0001

-rw-rw-rw- 1 user group 1536 Sep 26 22:36 ARC0000000009_0896605872.0001

-rw-rw-rw- 1 user group 1024 Sep 26 22:36 ARC0000000010_0896605872.0001

--//对比前面的大小一致。

D:\temp\dd>md5sum ARC0*

101233b20bad9ffdd99d2cde051e2221 *ARC0000000006_0896605872.0001

20f1efbb275f3a93a1eab15240378cfb *ARC0000000007_0896605872.0001

31462ea8a684aa3efc866378c5139a78 *ARC0000000008_0896605872.0001

4c269d8acdfef4769cb808fdc903c9d3 *ARC0000000009_0896605872.0001

816b32f56304990da2edf645a1262491 *ARC0000000010_0896605872.0001

--明天到单位验证md5s是否正确。

$ md5sum 1_[6-9]_896605872.dbf 1_10_896605872.dbf

bfe41590af10ae5520a4811dc7844349 1_6_896605872.dbf

56efdee679fe4e874fbf75dd7f86a9c7 1_7_896605872.dbf

c380653ceb6403a6fd6567cedfa6ba7d 1_8_896605872.dbf

35841b9b849013e03c62444b0bba9fa4 1_9_896605872.dbf

64533fca9548442ed46de84ad317c1f9 1_10_896605872.dbf

--奇怪md5sum不一致，为什么？

$ xxd -c32 1_10_896605872.dbf >|w.txt

$ xxd -c32 ARC0000000010_0896605872.0001>|l.txt

$ diff -Nur w.txt l.txt

--- w.txt 2016-09-27 08:22:54.000000000 +0800

+++ l.txt 2016-09-27 08:23:23.000000000 +0800

@@ -1,5 +1,5 @@

-0000000: 0022 0000 0000 c0ff 0000 0000 0000 0000 6758 0000 0002 0000 0100 0000 7d7c 7b7a ."....?........gX..........}|{z

-0000020: a081 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 ................................

+0000000: 0022 0000 0000 c0ff 0000 0000 0000 0000 c7d9 0000 0002 0000 0100 0000 7d7c 7b7a ."....?........琴..........}|{z

~~~~~

+0000020: 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 ................................

0000040: 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 ................................

0000060: 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 ................................

0000080: 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 ................................

--仅仅第17，18字节不一致。这个很像数据文件的OS头部(因为出现7d 7c 7b 7a)。也许跟os版本有关。我取出archivelog的版本是

--oracle 12c for windows。

--先暂时放弃。

--补充测试，下午想一下，看看是否可以catalog看看。

RMAN> catalog start with '/u01/backup/ARC0000000010_0896605872.0001';

using target database control file instead of recovery catalog

searching for all files that match the pattern /u01/backup/ARC0000000010_0896605872.0001

List of Files Unknown to the Database

=====================================

File Name: /u01/backup/ARC0000000010_0896605872.0001

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/backup/ARC0000000010_0896605872.0001

RMAN> list archivelog all ;

List of Archived Log Copies for database with db_unique_name BOOK

=====================================================================

Key Thrd Seq S Low Time

------- ---- ------- - -------------------

2 1 6 A 2016-08-17 11:03:10

Name: /u01/app/oracle/archivelog/book/1_6_896605872.dbf

3 1 7 A 2016-09-23 09:29:38

Name: /u01/app/oracle/archivelog/book/1_7_896605872.dbf

4 1 8 A 2016-09-23 10:50:27

Name: /u01/app/oracle/archivelog/book/1_8_896605872.dbf

5 1 9 A 2016-09-23 10:50:58

Name: /u01/app/oracle/archivelog/book/1_9_896605872.dbf

11 1 10 A 2016-09-23 10:51:07

Name: /u01/backup/ARC0000000010_0896605872.0001

10 1 10 A 2016-09-23 10:51:07

Name: /u01/backup/1_10_896605872.dbf

6 1 10 A 2016-09-23 10:51:07

Name: /u01/app/oracle/archivelog/book/1_10_896605872.dbf

7 1 11 A 2016-09-23 10:52:27

Name: /u01/app/oracle/archivelog/book/1_11_896605872.dbf

8 1 12 A 2016-09-24 09:00:52

Name: /u01/app/oracle/archivelog/book/1_12_896605872.dbf

9 1 13 A 2016-09-25 14:00:39

Name: /u01/app/oracle/archivelog/book/1_13_896605872.dbf

--说明这样没有问题的。