备份：
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.
