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

Rman还原

article 2025/1/24 18:55:01

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;


日志和归档重置
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


http://www.kler.cn/a/516678.html

相关文章：

  • 人脸识别打卡系统--基于QT(附源码)
  • vscode下poetry管理项目的debug配置
  • 在 Windows 11 中为 SMB 3.x 文件共享协议提供 RDMA 支持
  • Moretl FileSync增量文件采集工具
  • Android SystemUI——系统快捷设置面板（十三）
  • 【2025小年源码免费送】
  • Yii框架中的Cart组件：实现购物车功能
  • GC（垃圾回收）的分类
  • 使用 Elasticsearch 导航检索增强生成图表
  • linux-centosubuntu本地源配置
  • 蓝桥杯练习日常|c/c++竞赛常用库函数
  • 使用Python爬虫获取1688店铺所有商品信息的完整指南
  • C#高级：常用的扩展方法大全
  • ubuntu系统docker环境搭建
  • STM32调试手段：重定向printf串口
  • 重载C++运算符
  • salesforce FIELD_FILTER_VALIDATION_EXCEPTION
  • LVGL+FreeRTOS实战项目：智能健康助手（蓝牙模块篇）
  • 假期day1
  • NPM 与 Node.js 版本兼容问题：npm warn cli npm does not support Node.js
  • 文献阅读 250123-Accelerated dryland expansion under climate change
  • 从 TCP/IP 演进看按序流与性能
  • tortoiseSVN图标缺少绿色钩/tortoiseSVN图标不显示解决方案
  • EDI安全：2025年数据保护与隐私威胁应对策略
  • 【面试】Java 记录一次面试过程 三年工作经验
  • git rebase的使用