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