asm管理的dg資料檔案缺失的處理方法

一次由於DG磁碟組滿了，而這段時間生產上又產生新的資料檔案，導致在dg上新建的資料檔案變成UNNAMED命名的資料檔案了

1、        生產庫透過convert備份缺失的檔案

RMAN> convert datafile '+DATA/ctsdb/datafile/ts_recent.492.751535649' format '/oracle_tmp/bk_weekend_maint/rman_bk20110614/ts_recent99.dbf';

Starting backup at 18-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=+DATA/ctsdb/datafile/ts_recent.492.751535649
converted datafile=/oracle_tmp/bk_weekend_maint/rman_bk20110614/ts_recent99.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:26
Finished backup at 18-JUN-11

RMAN> convert datafile '+DATA/ctsdb/datafile/ts_recent.493.751535653' format '/oracle_tmp/bk_weekend_maint/rman_bk20110614/ts_recent100.dbf';

Starting backup at 18-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=+DATA/ctsdb/datafile/ts_recent.493.751535653
converted datafile=/oracle_tmp/bk_weekend_maint/rman_bk20110614/ts_recent100.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:36
Finished backup at 18-JUN-11

RMAN> convert datafile '+DATA/ctsdb/datafile/ts_recent.494.751535657' format '/oracle_tmp/bk_weekend_maint/rman_bk20110614/ts_recent101.dbf';

Starting backup at 18-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=+DATA/ctsdb/datafile/ts_recent.494.751535657
converted datafile=/oracle_tmp/bk_weekend_maint/rman_bk20110614/ts_recent101.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:36
Finished backup at 18-JUN-11

2、複製到dg，透過convert命令把檔案複製回dg
RMAN> convert datafile '/oracle_tmp/bk_weekend_maint/rman_bk20110614/ts_recent99.dbf' format '+DATA/ctsdb/datafile/ts_recent.492.751535649';

Starting backup at 18-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1654 instance=ctsdb1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=519 instance=ctsdb1 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=1099 instance=ctsdb1 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=1098 instance=ctsdb1 devtype=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: sid=1103 instance=ctsdb1 devtype=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: sid=494 instance=ctsdb1 devtype=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: sid=487 instance=ctsdb1 devtype=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: sid=1650 instance=ctsdb1 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=/oracle_tmp/bk_weekend_maint/rman_bk20110614/ts_recent99.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/18/2011 09:02:10
ORA-19699: cannot make copies with compression enabled

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 8;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/paic/stock/cts/data/cts/rman_bk/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/paic/stock/cts/data/app/product/10.2.0/db_1/dbs/snapcf_ctsdb1.f'; # default

RMAN> CONFIGURE DEVICE TYPE DISK clear;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 8;
RMAN configuration parameters are successfully reset to default value
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
released channel: ORA_DISK_5
released channel: ORA_DISK_6
released channel: ORA_DISK_7
released channel: ORA_DISK_8

RMAN> convert datafile '/oracle_tmp/bk_weekend_maint/rman_bk20110614/ts_recent99.dbf' format '+DATA/ctsdb/datafile/ts_recent.492.751535649';

Starting backup at 18-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1654 instance=ctsdb1 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=/oracle_tmp/bk_weekend_maint/rman_bk20110614/ts_recent99.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/18/2011 09:02:48
ORA-01276: Cannot add file +DATA/ctsdb/datafile/ts_recent.492.751535649.  File has an Oracle Managed Files file name.


OMF管理的資料檔案需要直接說名磁碟組即可，注意一定要asmcmd中記錄每個檔案的檔名，它產生的檔名都不確定的
RMAN> convert datafile '/oracle_tmp/bk_weekend_maint/rman_bk20110614/ts_recent99.dbf' format '+DATA';

Starting backup at 18-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/oracle_tmp/bk_weekend_maint/rman_bk20110614/ts_recent99.dbf
converted datafile=+DATA/ctsdbdg/datafile/ts_recent.489.754131787
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Finished backup at 18-JUN-11

RMAN> convert datafile '/oracle_tmp/bk_weekend_maint/rman_bk20110614/ts_recent100.dbf' format '+DATA';

Starting backup at 18-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/oracle_tmp/bk_weekend_maint/rman_bk20110614/ts_recent100.dbf
converted datafile=+DATA/ctsdbdg/datafile/ts_recent.502.754131985
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:08
Finished backup at 18-JUN-11

RMAN> convert datafile '/oracle_tmp/bk_weekend_maint/rman_bk20110614/ts_recent101.dbf' format '+DATA';

Starting backup at 18-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/oracle_tmp/bk_weekend_maint/rman_bk20110614/ts_recent101.dbf
converted datafile=+DATA/ctsdbdg/datafile/ts_recent.501.754132011
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Finished backup at 18-JUN-11

RMAN>

複製完之後把缺失的檔案rename 到 剛剛恢復的檔案

SQL> alter database rename file '/paic/stock/cts/data/app/product/10.2.0/db_1/dbs/UNNAMED00099' to '+data/ctsdbdg/datafile/TS_RECENT.502.754131985';

Database altered.

因為沒有記錄asm中的檔名，rename的時候99號檔案對應到101號檔案了，導致無法起庫，重新處理的辦法如下：


SQL> alter database create datafile 99 as '/oracle_tmp/bk_weekend_maint/rman_bk20110614/temp.dbf';

Database altered.

SQL> alter database create datafile 100 as '/oracle_tmp/bk_weekend_maint/rman_bk20110614/temp100.dbf';

Database altered.

SQL> alter database create datafile 101 as '/oracle_tmp/bk_weekend_maint/rman_bk20110614/temp101.dbf';

Database altered.


SQL> alter database rename file '/oracle_tmp/bk_weekend_maint/rman_bk20110614/temp.dbf' to '+DATA/ctsdbdg/datafile/ts_recent.489.754134461';

Database altered.

SQL> alter database rename file '/oracle_tmp/bk_weekend_maint/rman_bk20110614/temp101.dbf' to '+DATA/ctsdbdg/datafile/ts_recent.502.754134533';

Database altered.

SQL> alter database create datafile 100 as '/oracle_tmp/bk_weekend_maint/rman_bk20110614/temp100.dbf';

Database altered.

SQL> alter database rename file '/oracle_tmp/bk_weekend_maint/rman_bk20110614/temp100.dbf' to '+DATA/ctsdbdg/datafile/ts_recent.501.754134499';

Database altered.

SQL> alter database datafile 99 online;

Database altered.

SQL> alter database datafile 100 online;

Database altered.


SQL> alter database datafile 101 online;

Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01152: file 100 was not restored from a sufficiently old backup
ORA-01110: data file 100: '+DATA/ctsdbdg/datafile/ts_recent.501.754137787'


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover standby database using BACKUP CONTROLFILE ;
ORA-00279: change 8609364392472 generated at 06/18/2011 09:31:47 needed for
thread 2
ORA-00289: suggestion : +FRA
ORA-00280: change 8609364392472 for thread 2 is in sequence #2111


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 8609364392472 generated at 06/18/2011 09:31:46 needed for
thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 8609364392472 for thread 1 is in sequence #2060


ORA-00279: change 8609364392478 generated at 06/18/2011 09:31:47 needed for
thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 8609364392478 for thread 1 is in sequence #2061
ORA-00278: log file
'+FRA/ctsdbdg/archivelog/2011_06_18/thread_1_seq_2060.8720.754133509' no longer
needed for this recovery


ORA-00279: change 8609366981281 generated at 06/18/2011 10:01:43 needed for
thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 8609366981281 for thread 1 is in sequence #2062
ORA-00278: log file
'+FRA/ctsdbdg/archivelog/2011_06_18/thread_1_seq_2061.23512.754135311' no
longer needed for this recovery


ORA-00279: change 8609366981302 generated at 06/18/2011 10:01:44 needed for
thread 2
ORA-00289: suggestion : +FRA
ORA-00280: change 8609366981302 for thread 2 is in sequence #2112
ORA-00278: log file
'+FRA/ctsdbdg/archivelog/2011_06_18/thread_2_seq_2111.21528.754135311' no
longer needed for this recovery


ORA-00279: change 8609371383225 generated at 06/18/2011 10:31:44 needed for
thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 8609371383225 for thread 1 is in sequence #2063
ORA-00278: log file
'+FRA/ctsdbdg/archivelog/2011_06_18/thread_1_seq_2062.27809.754137105' no
longer needed for this recovery


ORA-16145: archival for thread# 1 sequence# 2063 in progress


SQL> alter database open read only;

Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.


SQL> select name,file#,status from v$datafile where file#>=99;

NAME
--------------------------------------------------------------------------------
     FILE# STATUS
---------- -------
+DATA/ctsdbdg/datafile/ts_recent.502.754137305
        99 ONLINE

+DATA/ctsdbdg/datafile/ts_recent.501.754137787
       100 ONLINE

+DATA/ctsdbdg/datafile/ts_recent.489.754137377
       101 ONLINE