12C grid CDB異機恢復+歸檔(DDL操作)
備份：
run{
allocate channel a1 type disk;
crosscheck backup;
delete noprompt expired backup;
crosscheck archivelog all;
delete noprompt expired archivelog all;
backup as compressed backupset database format '/u01/app/rman/20231120/full_%T_%d_%s_%p.bak' filesperset=8;
sql 'alter system archive log current';
backup as compressed backupset archivelog all format '/u01/app/rman/20231120/arc_%T_%d_%s_%p.bak' delete all input;
backup current controlfile format '/u01/app/rman/20231120/ctl_%T_%d_db_%s_%p.bak';
release channel a1;
}
恢復：
#啟動例項到nomount starup nomount
#恢復控制檔案 restore controlfile from '/u01/app/rman/20231120/ctl_20231120_SUENCDB_db_10_1.bak’; alter database mount;
#透過語句重定向資料檔案
set define off
set serveroutput on
set linesize 300
declare
datafileloc varchar2(80) := '/u01/app/oracle/oradata/suencdb/';
execres varchar2(200);
begin
for res in (select file#,substr(df.name,instr(df.name,'/',-1)+1,(length(df.name)-instr(df.name,'/',-1))) tname,replace(ct.name,'$','') pdb from v$datafile df,GV$CONTAINERS ct where df.con_id=ct.con_id)
loop
if res.pdb = 'CDBROOT' then
execres := 'SET NEWNAME FOR DATAFILE '||res.file#||' to '||chr(39)||datafileloc||res.tname||chr(39)||';';
else
execres := 'SET NEWNAME FOR DATAFILE '||res.file#||' to '||chr(39)||datafileloc||res.pdb||'/'||res.tname||chr(39)||';';
end if;
dbms_output.put_line(execres);
end loop ;
for res2 in (select replace(name,'$','') name from v$containers)
loop
if res2.name != 'CDBROOT' then
dbms_output.put_line('mkdir '||datafileloc||res2.name);
end if;
end loop;
for res3 in (select member,substr(member,instr(member,'/',-1)+1,(length(member)-instr(member,'/',-1))) mname from v$logfile)
loop
dbms_output.put_line('alter database rename file '||chr(39)||res3.member||chr(39)||' to '||chr(39)||datafileloc||res3.mname||chr(39)||';');
end loop;
for res4 in (select name,substr(name,instr(name,'/',-1)+1,(length(name)-instr(name,'/',-1))) mname from v$tempfile)
loop
dbms_output.put_line('alter database rename file '||chr(39)||res4.name||chr(39)||' to '||chr(39)||datafileloc||res4.mname||chr(39)||';');
end loop;
end;
/
SQL> conn / as sysdba
Connected.
SQL> set define off
set serveroutput on
set linesize 300
declare
datafileloc varchar2(80) := '/u01/app/oracle/oradata/suencdb/';
execres varchar2(200);
begin
for res in (select file#,substr(df.name,instr(df.name,'/',-1)+1,(length(df.SQL> name)-instr(df.name,'/',-1))) tname,replace(ct.name,'$','') pdb from v$datafile df,GV$CONTAINERS ct where df.con_id=ct.con_id)
loop
if res.pdb = 'CDBROOT' then
execres := 'SET NEWNAME FOR DATAFILE '||res.file#||' to '||chr(39)||datafileloc||res.tname||chr(39)||';';
else
execres := 'SET NEWNAME FOR DATAFILE '||res.file#||' to '||chr(39)||datafileloc||res.pdb||'/'||res.tname||chr(39)||';';
end if;
dbms_output.put_line(execres);
end loop ;
for res2 in (select replace(name,'$','') name from v$containers)
loop
if res2.name != 'CDBROOT' then
dbms_output.put_line('mkdir '||datafileloc||res2.name);
end if;
end loop;
for res3 in (select member,substr(member,instr(member,'/',-1)+1,(length(member)-instr(member,'/',-1))) mname from v$logfile)
loop
dbms_output.put_line('alter database rename file '||chr(39)||res3.member||chr(39)||' to '||chr(39)||datafileloc||res3.mname||chr(39)||';');
end loop;
for res4 in (select name,substr(name,instr(name,'/',-1)+1,(length(name)-instr(name,'/',-1))) mname from v$tempfile)
loop
dbms_output.put_line('alter database rename file '||chr(39)||res4.name||chr(39)||' to '||chr(39)||datafileloc||res4.mname||chr(39)||';');
end loop;
end;
/SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
SET NEWNAME FOR DATAFILE 7 to '/u01/app/oracle/oradata/suencdb/users.269.1151807581';
SET NEWNAME FOR DATAFILE 5 to '/u01/app/oracle/oradata/suencdb/undotbs1.265.1151807509';
SET NEWNAME FOR DATAFILE 3 to '/u01/app/oracle/oradata/suencdb/sysaux.263.1151807497';
SET NEWNAME FOR DATAFILE 1 to '/u01/app/oracle/oradata/suencdb/system.261.1151807475';
SET NEWNAME FOR DATAFILE 4 to '/u01/app/oracle/oradata/suencdb/PDBSEED/sysaux.264.1151807505';
SET NEWNAME FOR DATAFILE 2 to '/u01/app/oracle/oradata/suencdb/PDBSEED/system.262.1151807483';
SET NEWNAME FOR DATAFILE 6 to '/u01/app/oracle/oradata/suencdb/PDBSEED/undotbs1.266.1151807513';
SET NEWNAME FOR DATAFILE 8 to '/u01/app/oracle/oradata/suencdb/SUENDB/system.273.1151816933';
SET NEWNAME FOR DATAFILE 9 to '/u01/app/oracle/oradata/suencdb/SUENDB/sysaux.272.1151816933';
SET NEWNAME FOR DATAFILE 10 to '/u01/app/oracle/oradata/suencdb/SUENDB/undotbs1.271.1151816933';
SET NEWNAME FOR DATAFILE 11 to '/u01/app/oracle/oradata/suencdb/SUENDB/users.275.1151816971';
SET NEWNAME FOR DATAFILE 12 to '/u01/app/oracle/oradata/suencdb/SUENDB/test.276.1153380955';
mkdir /u01/app/oracle/oradata/suencdb/PDBSEED
mkdir /u01/app/oracle/oradata/suencdb/SUENDB
alter database rename file '+DATA01/SUENCDB/ONLINELOG/group_1.258.1151807463' to '/u01/app/oracle/oradata/suencdb/group_1.258.1151807463';
alter database rename file '+DATA01/SUENCDB/ONLINELOG/group_2.259.1151807467' to '/u01/app/oracle/oradata/suencdb/group_2.259.1151807467';
alter database rename file '+DATA01/SUENCDB/ONLINELOG/group_3.260.1151807471' to '/u01/app/oracle/oradata/suencdb/group_3.260.1151807471';
alter database rename file '+DATA01/SUENCDB/TEMPFILE/temp.267.1151807513' to '/u01/app/oracle/oradata/suencdb/temp.267.1151807513';
alter database rename file '+DATA01/SUENCDB/092688D31CCAFCE2E0650A002774D035/TEMPFILE/temp.268.1151807515' to '/u01/app/oracle/oradata/suencdb/temp.268.1151807515';
alter database rename file '+DATA01/SUENCDB/0928BC6FEBDAF6F1E0650A002774D035/TEMPFILE/temp.274.1151816949' to '/u01/app/oracle/oradata/suencdb/temp.274.1151816949';
PL/SQL procedure successfully completed.
#釋放資料檔案
run{
allocate channel c1 device type disk;
SET NEWNAME FOR DATAFILE 7 to '/u01/app/oracle/oradata/suencdb/users.269.1151807581';
SET NEWNAME FOR DATAFILE 5 to '/u01/app/oracle/oradata/suencdb/undotbs1.265.1151807509';
SET NEWNAME FOR DATAFILE 3 to '/u01/app/oracle/oradata/suencdb/sysaux.263.1151807497';
SET NEWNAME FOR DATAFILE 1 to '/u01/app/oracle/oradata/suencdb/system.261.1151807475';
SET NEWNAME FOR DATAFILE 4 to '/u01/app/oracle/oradata/suencdb/PDBSEED/sysaux.264.1151807505';
SET NEWNAME FOR DATAFILE 2 to '/u01/app/oracle/oradata/suencdb/PDBSEED/system.262.1151807483';
SET NEWNAME FOR DATAFILE 6 to '/u01/app/oracle/oradata/suencdb/PDBSEED/undotbs1.266.1151807513';
SET NEWNAME FOR DATAFILE 8 to '/u01/app/oracle/oradata/suencdb/SUENDB/system.273.1151816933';
SET NEWNAME FOR DATAFILE 9 to '/u01/app/oracle/oradata/suencdb/SUENDB/sysaux.272.1151816933';
SET NEWNAME FOR DATAFILE 10 to '/u01/app/oracle/oradata/suencdb/SUENDB/undotbs1.271.1151816933';
SET NEWNAME FOR DATAFILE 11 to '/u01/app/oracle/oradata/suencdb/SUENDB/users.275.1151816971';
restore database;
switch datafile all;
switch tempfile all;
release channel c1;
}
#把後面的歸檔日誌備份集註冊到控制檔案 RMAN> catalog start with '/u01/app/rman/20231120’; searching for all files that match the pattern /u01/app/rman/20231120 List of Files Unknown to the Database ===================================== File Name: /u01/app/rman/20231120/ctl_20231120_SUENCDB_db_10_1.bak File Name: /u01/app/rman/20231120/arc_20231120_SUENCDB_12_1.bak File Name: /u01/app/rman/20231120/ctl_20231120_SUENCDB_db_14_1.bak 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/app/rman/20231120/ctl_20231120_SUENCDB_db_10_1.bak File Name: /u01/app/rman/20231120/arc_20231120_SUENCDB_12_1.bak File Name: /u01/app/rman/20231120/ctl_20231120_SUENCDB_db_14_1.bak
#釋放歸檔日誌到指定位置
RUN{
SET ARCHIVELOG DESTINATION TO '/u01/app/oracle/arch/';
RESTORE ARCHIVELOG all;
}
#應用歸檔日誌 RMAN> recover database; Starting recover at 20-NOV-23 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 32 is already on disk as file /u01/app/oracle/arch/1_32_1151807457.dbf archived log for thread 1 with sequence 33 is already on disk as file /u01/app/oracle/arch/1_33_1151807457.dbf archived log for thread 1 with sequence 34 is already on disk as file /u01/app/oracle/arch/1_34_1151807457.dbf archived log for thread 1 with sequence 35 is already on disk as file /u01/app/oracle/arch/1_35_1151807457.dbf archived log for thread 1 with sequence 36 is already on disk as file /u01/app/oracle/arch/1_36_1151807457.dbf archived log file name=/u01/app/oracle/arch/1_32_1151807457.dbf thread=1 sequence=32 archived log file name=/u01/app/oracle/arch/1_33_1151807457.dbf thread=1 sequence=33 archived log file name=/u01/app/oracle/arch/1_34_1151807457.dbf thread=1 sequence=34 archived log file name=/u01/app/oracle/arch/1_35_1151807457.dbf thread=1 sequence=35 creating datafile file number=12 name=+DATA01/SUENCDB/0928BC6FEBDAF6F1E0650A002774D035/DATAFILE/test.276.1153380955 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 11/20/2023 08:54:08 RMAN-20505: create datafile during recovery ORA-01119: error in creating database file '+DATA01' ORA-17502: ksfdcre:4 Failed to create file +DATA01 ORA-15001: diskgroup "DATA01" does not exist or is not mounted ORA-15374: invalid cluster configuration RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/app/oracle/arch/1_35_1151807457.dbf' ORA-00283: recovery session canceled due to errors ORA-01244: unnamed datafile(s) added to control file by media recovery ORA-01110: data file 12: '+DATA01/SUENCDB/0928BC6FEBDAF6F1E0650A002774D035/DATAFILE/test.276.1153380955'
#歸檔日誌內的DDL語句報錯，處理
SQL> select file#,name from v$datafile where name like '%UNNAME%';
FILE#
----------
NAME
--------------------------------------------------------------------------------
12
/u01/app/oracle/product/12.2.0/db_1/dbs/UNNAMED00012
#重定向新的資料檔案，並restore
run{
SET NEWNAME FOR DATAFILE 12 to '/u01/app/oracle/oradata/suencdb/test.276.1153380955';
restore datafile 12;
switch datafile 12;
}
RMAN> run{
SET NEWNAME FOR DATAFILE 12 to '/u01/app/oracle/oradata/suencdb/test.276.1153380955';
restore datafile 12;
switch datafile 12;
}2> 3> 4> 5>
executing command: SET NEWNAME
Starting restore at 20-NOV-23
using channel ORA_DISK_1
creating datafile file number=12 name=/u01/app/oracle/oradata/suencdb/test.276.1153380955
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 20-NOV-23
datafile 12 switched to datafile copy
input datafile copy RECID=24 STAMP=1153386722 file name=/u01/app/oracle/oradata/suencdb/test.276.1153380955
RMAN> recover database;
Starting recover at 20-NOV-23
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 35 is already on disk as file /u01/app/oracle/arch/1_35_1151807457.dbf
archived log for thread 1 with sequence 36 is already on disk as file /u01/app/oracle/arch/1_36_1151807457.dbf
archived log for thread 1 with sequence 37 is already on disk as file /u01/app/oracle/arch/1_37_1151807457.dbf
archived log for thread 1 with sequence 38 is already on disk as file /u01/app/oracle/arch/1_38_1151807457.dbf
archived log file name=/u01/app/oracle/arch/1_35_1151807457.dbf thread=1 sequence=35
archived log file name=/u01/app/oracle/arch/1_36_1151807457.dbf thread=1 sequence=36
archived log file name=/u01/app/oracle/arch/1_37_1151807457.dbf thread=1 sequence=37
archived log file name=/u01/app/oracle/arch/1_38_1151807457.dbf thread=1 sequence=38
unable to find archived log
archived log thread=1 sequence=39
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/20/2023 19:53:14
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 39 and starting SCN of 2010087
#重定向redo和temp檔案 SQL> alter database rename file '+DATA01/SUENCDB/ONLINELOG/group_1.258.1151807463' to '/u01/app/oracle/oradata/suencdb/group_1.258.1151807463'; alter database rename file '+DATA01/SUENCDB/ONLINELOG/group_2.259.1151807467' to '/u01/app/oracle/oradata/suencdb/group_2.259.1151807467'; alter database rename file '+DATA01/SUENCDB/ONLINELOG/group_3.260.1151807471' to '/u01/app/oracle/oradata/suencdb/group_3.260.1151807471'; alter database rename file '+DATA01/SUENCDB/TEMPFILE/temp.267.1151807513' to '/u01/app/oracle/oradata/suencdb/temp.267.1151807513'; alter database rename file '+DATA01/SUENCDB/092688D31CCAFCE2E0650A002774D035/TEMPFILE/temp.268.1151807515' to '/u01/app/oracle/oradata/suencdb/temp.268.1151807515'; alter database rename file '+DATA01/SUENCDB/0928BC6FEBDAF6F1E0650A002774D035/TEMPFILE/temp.274.1151816949' to '/u01/app/oracle/oradata/suencdb/temp.274.1151816949'; Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered.
#使用resetlogs的方式開啟資料庫例項 SQL> alter database open resetlogs; Database altered.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28572479/viewspace-2996278/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 12c RMAN 異機恢復Oracle
- 【12c 庫異機恢復】實驗
- oracle基於歸檔的增量異地恢復Oracle
- oracle 12c 針對cdb的差異0備與對pdb進行恢復Oracle
- 歸檔資料庫中的不可恢復操作資料庫
- 【備份恢復】非歸檔模式下丟失任意資料檔案 恢復操作模式
- oracle 異機恢復Oracle
- 恢復之非歸檔模式下的恢復模式
- 【基本操作】快速恢復區存在時修改歸檔路徑
- rman恢復方案和oracle異機恢復Oracle
- rman datafile恢復(歸檔模式)模式
- 記一次簡單的異機恢復操作
- Oracle RMAN異機恢復Oracle
- oracle冷備份、恢復和異機恢復Oracle
- ORACLE RMAN異機異目錄恢復Oracle
- DG歸檔日誌缺失恢復
- 無備份恢復(歸檔模式)模式
- ORACLE非歸檔下的恢復Oracle
- 【備份恢復】恢復 丟失已歸檔重做日誌檔案
- 恢復案例：無歸檔，掉電，控制檔案全部丟失恢復
- oracle的RMAN異機恢復Oracle
- RMAN異機恢復總結
- Oracle備份恢復之熱備份恢復及異機恢復Oracle
- 恢復案例：歸檔模式下丟失全部資料檔案的恢復模式
- standby全庫rman備份檔案恢復到異機
- 恢復案例：無歸檔，丟失全部控制檔案、日誌檔案恢復案例
- 非歸檔模式恢復資料庫模式資料庫
- Data Guard跳歸檔恢復的案例
- 只有冷備和歸檔，能否恢復？
- standby缺失primary歸檔，手工同步恢復
- 【ASK_ORACLE】Oracle 12c之CDB與PDB的備份與恢復（一）什麼是CDB與PDB？Oracle
- 【RECO_ORACLE】Oracle 12c之CDB與PDB的備份與恢復（四）PDB的幾種恢復方式Oracle
- 恢復歸檔日誌檔案的常用方法
- 兩篇oracle異機恢復文章Oracle
- rman恢復--歸檔模式有備份，丟失資料檔案的恢復模式
- rman恢復--歸檔模式無備份，丟失資料檔案的恢復模式
- 【BAK_ORACLE】Oracle 12c之CDB與PDB的備份與恢復（三）CDB與PDB的備份方式Oracle
- 【BBED】丟失歸檔檔案情況下的恢復