[20180718]拷贝数据文件从dg库.txt

1.测试环境:

SCOTT@book> @ 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

--//主库:192.168.100.78

--//备库:192.168.100.78

--//注意检查dg是否应用日志正常略.

--//操作全部在主库执行.

2.假设主库数据文件6破坏.借助备库恢复主库.

CREATE TABLESPACE TEA DATAFILE

  '/mnt/ramdisk/book/tea01.dbf' SIZE 40M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED

LOGGING

ONLINE

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT MANUAL

FLASHBACK ON;

SCOTT@book> create table deptx tablespace tea as select * from dept;

Table created.

SCOTT@book> select count(*) from deptx;

  COUNT(*)

----------

         4

--//假设数据文件6破坏.

$ dd if=/dev/zero of=/mnt/ramdisk/book/tea01.dbf count=10 bs=1M

10+0 records in

10+0 records out

10485760 bytes (10 MB) copied, 0.0130315 seconds, 805 MB/s

3.测试:

SCOTT@book> alter system flush buffer_cache;

System altered.

SCOTT@book> select count(*) from deptx;

select count(*) from deptx

*

ERROR at line 1:

ORA-03135: connection lost contact

Process ID: 2772

Session ID: 274 Serial number: 5

--//实际上脏块文件无法写盘,在alert出现:

ALTER SYSTEM: Flushing buffer cache

Wed Jul 18 08:31:59 2018

Read of datafile '/mnt/ramdisk/book/tea01.dbf' (fno 6) header failed with ORA-01210

Hex dump of (file 6, block 1) in trace file /u01/app/oracle/diag/rdbms/book/book/trace/book_ckpt_2678.trc

Corrupt block relative dba: 0x01800001 (file 6, block 1)

Completely zero block found during datafile header read

Rereading datafile 6 header failed with ORA-01210

Hex dump of (file 6, block 1) in trace file /u01/app/oracle/diag/rdbms/book/book/trace/book_ckpt_2678.trc

Corrupt block relative dba: 0x01800001 (file 6, block 1)

Completely zero block found during datafile header read

Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ckpt_2678.trc:

ORA-63999: data file suffered media failure

ORA-01122: database file 6 failed verification check

ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'

ORA-01210: data file header is media corrupt

Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ckpt_2678.trc:

ORA-63999: data file suffered media failure

ORA-01122: database file 6 failed verification check

ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'

ORA-01210: data file header is media corrupt

CKPT (ospid: 2678): terminating the instance due to error 63999

Wed Jul 18 08:32:00 2018

System state dump requested by (instance=1, osid=2678 (CKPT)), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/book/book/trace/book_diag_2662_20180718083200.trc

Dumping diagnostic data in directory=[cdmp_20180718083200], requested by (instance=1, osid=2678 (CKPT)), summary=[abnormal instance termination].

Instance terminated by CKPT, pid = 2678

4.恢复:

SYS@book> startup

ORACLE instance started.

Total System Global Area  634732544 bytes

Fixed Size                  2255792 bytes

Variable Size             197133392 bytes

Database Buffers          427819008 bytes

Redo Buffers                7524352 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'

SYS@book> alter database datafile 6 offline;

Database altered.

SYS@book> alter database open ;

Database altered.

$ rman target sys/oracle@bookdg auxiliary sys/oracle@book

--//注意主库以auxiliary方式登陆.备库以target方式登陆.也是与以前做duplicate写反.

RMAN>  BACKUP AS COPY DATAFILE 6 AUXILIARY FORMAT '/mnt/ramdisk/book/tea01.dbfx' reuse;

Starting backup at 2018-07-18 08:40:59

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=/mnt/ramdisk/book/tea01.dbf

output file name=/mnt/ramdisk/book/tea01.dbfx tag=TAG20180718T084059

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 2018-07-18 08:41:01

--//害怕覆盖,使用不同的文件名.注:后面的参数reuse可以不用,如果没有覆盖的情况下.

--//192.168.100.78 .改名

$ ls -l /mnt/ramdisk/book/tea01.dbf*

-rw-r----- 1 oracle oinstall 10485760 2018-07-18 08:31:59 /mnt/ramdisk/book/tea01.dbf

-rw-r----- 1 oracle oinstall 41951232 2018-07-18 08:41:00 /mnt/ramdisk/book/tea01.dbfx

$ mv /mnt/ramdisk/book/tea01.dbf /mnt/ramdisk/book/tea01.dbf_bad

$ mv /mnt/ramdisk/book/tea01.dbfx /mnt/ramdisk/book/tea01.dbf

--//注:再次注意,有一次测试不小心,dd if=/dev/zero of=/mnt/ramdisk/book/tea01.dbf count=10 bs=1M

--//原来的文件变成了10M.注意dd一定要加conv=notrunc参数.再次提醒自己.

SYS@book> alter database datafile 6 online;

alter database datafile 6 online

*

ERROR at line 1:

ORA-01113: file 6 needs media recovery

ORA-01110: data file 6: '/mnt/ramdisk/book/tea01.dbf'

SYS@book> recover datafile 6;

ORA-00279: change 13277205619 generated at 07/05/2018 08:49:40 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_726_896605872.dbf

ORA-00280: change 13277205619 for thread 1 is in sequence #726

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 13277209856 generated at 07/05/2018 09:23:42 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_727_896605872.dbf

ORA-00280: change 13277209856 for thread 1 is in sequence #727

ORA-00279: change 13277213030 generated at 07/13/2018 08:43:31 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_728_896605872.dbf

ORA-00280: change 13277213030 for thread 1 is in sequence #728

ORA-00279: change 13277213473 generated at 07/18/2018 08:26:06 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_729_896605872.dbf

ORA-00280: change 13277213473 for thread 1 is in sequence #729

Log applied.

Media recovery complete.

SYS@book> alter database datafile 6 online;

Database altered.

SYS@book> select count(*) from scott.deptx;

  COUNT(*)

----------

         4

--//OK,现在已经恢复正常可以使用了.