[20180718]拷貝數據文件從dg庫.txt

[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,現在已經恢復正常可以使用了.