当前位置：首页 > news >正文

Rman还原

news 2026/6/14 23:11:36

1、原服务器创建pfile
create pfile='/home/oracle/dqpfile.ora' from spfile;

2、目标服务器
mv dqpfile.ora /home/oracle/product/11.2.0/dbs/
vim /home/oracle/product/11.2.0/dbs/dqpfile.ora

alter database backup controlfile to '/home/oracle/dq/control01.ctl'

show parameter format;

SET LINESIZE 200
SELECT PARAMETER, VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

3、目标服务器创建spfile
create spfile from pfile='/home/oracle/product/11.2.0/dbs/dqpfile.ora';

4、启动
startup mount pfile='/home/oracle/product/11.2.0/dbs/dqpfile.ora'
startup nomount pfile='/home/oracle/product/11.2.0/dbs/dqpfile.ora'

select FILE#,NAME,TS# from v$datafile;

1 /oracle/home/oradata/jwell/system01.dbf 0
2 /oracle/home/oradata/jwell/sysaux01.dbf 1
3 /oracle/home/oradata/jwell/undotbs01.dbf 2
4 /oracle/home/oradata/jwell/users01.dbf 4
5 /oracle/home/oradata/jwell/example01.dbf 6
6 /oracle/home/product/11.2.0/dbhome_1/dbs/E:JWWLWMSJWWL01.DBF 7
7 /oracle/home/product/11.2.0/dbhome_1/dbs/JWWL02.DBF 7
8 /oracle/home/product/11.2.0/dbhome_1/dbs/JWWL03.DBF 7
9 /oracle/home/oradata/jwell/jwwl04.dbf 7
10 /oracle/home/oradata/jwell/jwwl05.dbf 7
11 /oracle/home/oradata/jwell/jwwl06.dbf 7
12 /oracle/home/oradata/jwell/jwwl07.dbf 7
13 /oracle/home/oradata/jwell/jwwl08.dbf 7
14 /oracle/home/oradata/jwell/jwwl09.dbf 7
15 /oracle/home/oradata/jwell/jwwl10.dbf 7
16 /oracle/home/oradata/jwell/jwwl11.dbf 7
17 /oracle/home/oradata/jwell/jwwl12.dbf 7

13681948608488 01/02/2025 13:51:53

13681948608489 01/02/2025 13:51:53

5、设置DBID
rman target /

select DBID from v$database;

set dbid=781653821

shutdoun
alter database unmount;

6、还原
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c6 device type disk;
set dbid=781653821;
sql 'alter database mount';
set until scn 13681948608489;
set newname for datafile 1 to "/home/oracle/oradata/jwell/system01.dbf";
set newname for datafile 2 to "/home/oracle/oradata/jwell/sysaux01.dbf";
set newname for datafile 3 to "/home/oracle/oradata/jwell/undotbs01.dbf";
set newname for datafile 4 to "/home/oracle/oradata/jwell/users01.dbf";
set newname for datafile 5 to "/home/oracle/oradata/jwell/example01.dbf";
set newname for datafile 6 to "/home/oracle/oradata/jwell/E:JWWLWMSJWWL01.DBF";
set newname for datafile 7 to "/home/oracle/oradata/jwell/JWWL02.DBF";
set newname for datafile 8 to "/home/oracle/oradata/jwell/JWWL03.DBF";
set newname for datafile 9 to "/home/oracle/oradata/jwell/jwwl04.dbf";
set newname for datafile 10 to "/home/oracle/oradata/jwell/jwwl05.dbf";
set newname for datafile 11 to "/home/oracle/oradata/jwell/jwwl06.dbf";
set newname for datafile 12 to "/home/oracle/oradata/jwell/jwwl07.dbf";
set newname for datafile 13 to "/home/oracle/oradata/jwell/jwwl08.dbf";
set newname for datafile 14 to "/home/oracle/oradata/jwell/jwwl09.dbf";
set newname for datafile 15 to "/home/oracle/oradata/jwell/jwwl10.dbf";
set newname for datafile 16 to "/home/oracle/oradata/jwell/jwwl11.dbf";
set newname for datafile 17 to "/home/oracle/oradata/jwell/jwwl12.dbf";
restore database until scn 13681948608489;
recover database until scn 13681948608489;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
}

run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c6 device type disk;
set dbid=781653821;
sql 'alter database mount';
set until time "to_date('2024-12-31 22:02:36','yyyy-mm-dd hh24:mi:ss')";
set newname for datafile 1 to "/home/oracle/oradata/jwell/system01.dbf";
set newname for datafile 2 to "/home/oracle/oradata/jwell/sysaux01.dbf";
set newname for datafile 3 to "/home/oracle/oradata/jwell/undotbs01.dbf";
set newname for datafile 4 to "/home/oracle/oradata/jwell/users01.dbf";
set newname for datafile 5 to "/home/oracle/oradata/jwell/example01.dbf";
set newname for datafile 6 to "/home/oracle/oradata/jwell/E:JWWLWMSJWWL01.DBF";
set newname for datafile 7 to "/home/oracle/oradata/jwell/JWWL02.DBF";
set newname for datafile 8 to "/home/oracle/oradata/jwell/JWWL03.DBF";
set newname for datafile 9 to "/home/oracle/oradata/jwell/jwwl04.dbf";
set newname for datafile 10 to "/home/oracle/oradata/jwell/jwwl05.dbf";
set newname for datafile 11 to "/home/oracle/oradata/jwell/jwwl06.dbf";
set newname for datafile 12 to "/home/oracle/oradata/jwell/jwwl07.dbf";
set newname for datafile 13 to "/home/oracle/oradata/jwell/jwwl08.dbf";
set newname for datafile 14 to "/home/oracle/oradata/jwell/jwwl09.dbf";
set newname for datafile 15 to "/home/oracle/oradata/jwell/jwwl10.dbf";
set newname for datafile 16 to "/home/oracle/oradata/jwell/jwwl11.dbf";
set newname for datafile 17 to "/home/oracle/oradata/jwell/jwwl12.dbf";
restore database until time "to_date('2024-12-31 22:02:36','yyyy-mm-dd hh24:mi:ss')";
recover database until time "to_date('2024-12-31 22:02:36','yyyy-mm-dd hh24:mi:ss')";
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
}

select FILE#,NAME,TS# from v$tempfile;

create temporary tablespace temp tempfile '/home/oracle/oradata/jwell/temp1.dbf' size 2048m autoextend off;

alter database rename file '原路径' to '新路径';

ALTER DATABASE DROP LOGFILE MEMBER '/backup/onlineredo/redo01b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/backup/onlineredo/redo02b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/backup/onlineredo/redo03b.log';

-- 查看当前REDO日志组的配置
SELECT group#, bytes/1024/1024 "Size (MB)", members, ARCHIVED, STATUS FROM v$log;

-- 添加一个新的日志组，并为它指定一个成员
ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/dbname/redo04a.log') SIZE 50M;

ALTER DATABASE ADD LOGFILE MEMBER '/home/oracle/oradata/jwell/redo01.log' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '/home/oracle/oradata/jwell/redo02.log' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/home/oracle/oradata/jwell/redo03.log' TO GROUP 3;

col FIRST_CHANGE format a30;
select group#,MEMBER from v$logfile;

ALTER DATABASE DROP LOGFILE MEMBER '/oracle/home/oradata/jwell/redo01.log';
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/home/oradata/jwell/redo02.log';
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/home/oradata/jwell/redo03.log';

3 /oracle/home/oradata/jwell/red
o03.log

2 /oracle/home/oradata/jwell/red
o02.log

1 /oracle/home/oradata/jwell/red
o01.log

1 /backup/onlineredo/redo01b.log

ALTER DATABASE ADD LOGFILE MEMBER '/home/oracle/oradata/jwell/redo01.log' TO GROUP 1;

alter database open resetlogs;

select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,to_char(FIRST_CHANGE#) from v$log order by first_change# ;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

select 'alter database rename file '''||name||''' to ''/bre1/oradata'||substr(name,length(name)-INSTR(reverse(name),'/')+1,INSTR(reverse(name),'/'))||''';' from v$datafile
union all
select 'alter database rename file '''||name||''' to ''/bre1/oradata'||substr(name,length(name)-INSTR(reverse(name),'/')+1,INSTR(reverse(name),'/'))||''';' from v$tempfile
union all
select 'alter database rename file '''||member||''' to ''/bre1/oradata'||substr(member,length(member)-INSTR(reverse(member),'/')+1,INSTR(reverse(member),'/'))||''';' from v$logfile;
日志和归档重置
alter database rename file '/oracle/home/oradata/jwell/system01.dbf' to '/home/oracle/oradata/jwell/system01.dbf';
alter database rename file '/oracle/home/oradata/jwell/sysaux01.dbf' to '/home/oracle/oradata/jwell/sysaux01.dbf';
alter database rename file '/oracle/home/oradata/jwell/undotbs01.dbf' to '/home/oracle/oradata/jwell/undotbs01.dbf';
alter database rename file '/oracle/home/oradata/jwell/users01.dbf' to '/home/oracle/oradata/jwell/users01.dbf';
alter database rename file '/oracle/home/oradata/jwell/example01.dbf' to '/home/oracle/oradata/jwell/example01.dbf';
alter database rename file '/oracle/home/product/11.2.0/dbhome_1/dbs/E:JWWLWMSJWWL01.DBF' to '/home/oracle/oradata/jwell/E:JWWLWMSJWWL01.DBF';
alter database rename file '/oracle/home/product/11.2.0/dbhome_1/dbs/JWWL02.DBF' to '/home/oracle/oradata/jwell/JWWL02.DBF';
alter database rename file '/oracle/home/product/11.2.0/dbhome_1/dbs/JWWL03.DBF' to '/home/oracle/oradata/jwell/JWWL03.DBF';
alter database rename file '/oracle/home/oradata/jwell/jwwl04.dbf' to '/home/oracle/oradata/jwell/jwwl04.dbf';
alter database rename file '/oracle/home/oradata/jwell/jwwl05.dbf' to '/home/oracle/oradata/jwell/jwwl05.dbf';
alter database rename file '/oracle/home/oradata/jwell/jwwl06.dbf' to '/home/oracle/oradata/jwell/jwwl06.dbf';
alter database rename file '/oracle/home/oradata/jwell/jwwl07.dbf' to '/home/oracle/oradata/jwell/jwwl07.dbf';
alter database rename file '/oracle/home/oradata/jwell/jwwl08.dbf' to '/home/oracle/oradata/jwell/jwwl08.dbf';
alter database rename file '/oracle/home/oradata/jwell/jwwl09.dbf' to '/home/oracle/oradata/jwell/jwwl09.dbf';
alter database rename file '/oracle/home/oradata/jwell/jwwl10.dbf' to '/home/oracle/oradata/jwell/jwwl10.dbf';
alter database rename file '/oracle/home/oradata/jwell/jwwl11.dbf' to '/home/oracle/oradata/jwell/jwwl11.dbf';
alter database rename file '/oracle/home/oradata/jwell/jwwl12.dbf' to '/home/oracle/oradata/jwell/jwwl12.dbf';

ALTER DATABASE RENAME FILE '/oracle/home/oradata/jwell/redo03.log' TO '/home/oracle/oradata/jwell/redo03.log';
ALTER DATABASE RENAME FILE '/oracle/home/oradata/jwell/redo02.log' TO '/home/oracle/oradata/jwell/redo02.log';
ALTER DATABASE RENAME FILE '/oracle/home/oradata/jwell/redo01.log' TO '/home/oracle/oradata/jwell/redo01.log';
ALTER DATABASE RENAME FILE '/backup/onlineredo/redo01b.log' TO '/home/oracle/oradata/jwell/redo01b.log';
ALTER DATABASE RENAME FILE '/backup/onlineredo/redo02b.log' TO '/home/oracle/oradata/jwell/redo02b.log';
ALTER DATABASE RENAME FILE '/backup/onlineredo/redo03b.log' TO '/home/oracle/oradata/jwell/redo03b.log';

ALTER DATABASE RENAME FILE '/old_path/redo_log1.log' TO '/new_path/redo_log1.log';

select OPEN_MODE,to_char(CHECKPOINT_CHANGE#) from v$database;

SELECT a.name, to_char(a.CHECKPOINT_CHANGE#) aSCN, to_char(b.CHECKPOINT_CHANGE#) bSCN FROM v$datafile_header a, v$datafile b WHERE a.file# = b.file#;

7、恢复
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
set until scn 13681948608489;
recover database until scn 13681948608489;
release channel c1;
release channel c2;
}

run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
set until time "to_date('2024-12-31 22:02:36','yyyy-mm-dd hh24:mi:ss')";
recover database until time "to_date('2024-12-31 22:02:36','yyyy-mm-dd hh24:mi:ss')";
release channel c1;
release channel c2;
}

8、重置
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
set until scn 13681948608489;
recover database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

alter database open resetlogs;

RMAN> list backup of spfile;

RMAN> list backup of controlfile;

RMAN> list backup of database;

参考
https://blog.csdn.net/u010674953/article/details/117959291
https://www.modb.pro/db/1791300292209364992
https://blog.csdn.net/Auspicious_air/article/details/94905753
https://blog.csdn.net/su377486/article/details/38728075
https://www.cnblogs.com/rootq/archive/2010/03/05/1678969.html
https://www.jb51.net/article/127915.htm
https://www.cnblogs.com/lcword/p/11775657.html
https://blog.csdn.net/lixora/article/details/8846974
https://blog.csdn.net/coujiongyong0208/article/details/100496198
https://blog.csdn.net/huang_xw/article/details/6545273

查看全文

http://www.cnnetsun.cn/news/2860202.html