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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【12c 庫異機恢復】實驗
- oracle 12c 針對cdb的差異0備與對pdb進行恢復Oracle
- oracle 異機恢復Oracle
- DG歸檔日誌缺失恢復
- 【RECO_ORACLE】Oracle 12c之CDB與PDB的備份與恢復（四）PDB的幾種恢復方式Oracle
- RMAN備份異機恢復
- 【ASK_ORACLE】Oracle 12c之CDB與PDB的備份與恢復（一）什麼是CDB與PDB？Oracle
- RMAN備份恢復典型案例——異機恢復未知DBID
- 【BAK_ORACLE】Oracle 12c之CDB與PDB的備份與恢復（三）CDB與PDB的備份方式Oracle
- 【BBED】丟失歸檔檔案情況下的恢復
- oracle dg 歸檔日誌恢復情況Oracle
- 兩篇oracle異機恢復文章Oracle
- logminer異機挖掘歸檔日誌
- 12C針對cdb全備與 PDB執行不完全恢復（基於SCN)
- 【ASK_ORACLE】Oracle 12c之CDB與PDB的備份與恢復（二）備份恢復之前你需要知道的Oracle
- Oracle使用備份檔案集恢復歸檔日誌Oracle
- [20181026]12c增強索引線上DDL操作.txt索引
- DM7使用DMRAMN執行歸檔恢復
- rman備份異機恢復（原創）
- Oracle 12c no-CDB轉換為CDBOracle
- oracle資料庫災難挽救應急方案之DDL誤操作恢復（drop）Oracle資料庫
- oracle資料庫災難挽救應急方案之DDL誤操作恢復（truncate）Oracle資料庫
- dg丟失歸檔，使用rman增量備份恢復
- 2.6.1 CDB中備份和恢復的概述
- Oracle 12c 備份與恢復Oracle
- Oracle 12c 手動建立CDB和非CDBOracle
- Oracle 12c 關閉歸檔模式Oracle模式
- 對歸檔模式下CLEAR 未歸檔日誌後恢復資料庫的一點看法模式資料庫
- 【12c cdb pdb】實驗
- oracle 12c non-cdb升級成cdb模式Oracle模式
- Oracle 12C新特性-RMAN恢復表Oracle
- pt-archiver工具歸檔和恢復資料一例Hive
- Oracle 12C 中CDB和PDB的引數檔案管理Oracle
- 從備份片中恢復某個指定得歸檔或者資料檔案
- ORACLE 11.2.0.4 RAC RMAN異機恢復之ORA-15001Oracle
- 異機使用完全備份恢復指定的PDB
- Oracle 12c CDB&PDBs管理Oracle
- [20190718]12c rman新特性 表恢復.txt