`
LJ你是唯一LT
  • 浏览: 244053 次
社区版块
存档分类
最新评论

rman模拟故障恢复实验

oracle 
阅读更多
补充1：restore database和recover database的区别
restore 只是用备份来还原,recover是用archivelog或者online log

举例说明：
假设我时间点A,做了个备份,时间点B数据库挂了
restore database ;// 这个操作利用时间点A做的备份来还原,返回到时间点A
recover database ;//这个操作利用archivelog and online log做recover,从时间点A,推进到时间点B
restore 是转储 也是還原被损坏文件(RMAN经常用)
recover 是恢复 通过redo log & archive log恢复


补充2：rman模拟故障恢复过程（所有spfile、controlfile、datafile均丢失）-前提是数据库故障前有rman备份
1）RMAN> show all;
CONFIGURE CONTROLFILE AUTOBACKUP ON;   ---控制文件备份的同时，会自动备份参数文件

SQL> select dbid from v$database;  --1669126943

2）先给数据库做个备份：
RMAN> backup database format '/orabak/whole_%d_%U';
Starting backup at 18-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/oradata/tinadb/sysaux01.dbf
input datafile file number=00001 name=/u01/oradata/tinadb/system01.dbf
input datafile file number=00005 name=/u01/oradata/tinadb/ts_tina01.dbf
input datafile file number=00003 name=/u01/oradata/tinadb/undotbs01.dbf
input datafile file number=00004 name=/u01/oradata/tinadb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-DEC-15
channel ORA_DISK_1: finished piece 1 at 18-DEC-15
piece handle=/orabak/whole_TINADB_0vqp4nrf_1_1 tag=TAG20151218T143214 comment=NONE   ---注意这行，可以看到备份集的具体名称和tag标签
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36
Finished backup at 18-DEC-15

Starting Control File and SPFILE Autobackup at 18-DEC-15
piece handle=/u01/oracle/TINADB/autobackup/2015_12_18/o1_mf_s_898785171_c77b4mv5_.bkp comment=NONE
--注意这行，我们可以看到控制文件和spfile都自动备份了，因为我配置了CONFIGURE CONTROLFILE AUTOBACKUP ON;

Finished Control File and SPFILE Autobackup at 18-DEC-15

[root@oratest orabak]# cd /orabak
[root@oratest orabak]# ll
-rw-r-----. 1 oracle oinstall 1238605824 Dec 18 14:32 whole_TINADB_0vqp4nrf_1_1  

[root@oratest orabak]# cd /u01/oracle/TINADB/autobackup/2015_12_18/
[root@oratest 2015_12_18]# ll
-rw-r-----. 1 oracle oinstall 10158080 Dec 18 14:32 o1_mf_s_898785171_c77b4mv5_.bkp
  
3）模拟spfile，controlfile，datafile全部都丢失
SQL> shutdown immediate;

删除文件：
[oracle@oratest dbs]$ cd /u01/oracle/dbs/
[oracle@oratest dbs]$ rm -f pfiletinadb.ora  spfiletinadb.ora  

[oracle@oratest dbs]$ cd /u01/oradata/tinadb/
[oracle@oratest dbs]$ rm -f *.dbf  redo*.log  control01.ctl

[root@oratest test]# cd /u01/fast_recovery_area/tinadb/
[root@oratest tinadb]# rm -f control02.ctl   

4)以oracle默认的参数文件init.ora启动后，恢复spfile
RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/oracle/dbs/inittinadb.ora'
starting Oracle instance without parameter file for retrieval of spfile  
Oracle instance started
Total System Global Area     158662656 bytes
Fixed Size                     2226456 bytes
Variable Size                 92276456 bytes
Database Buffers              58720256 bytes
Redo Buffers                   5439488 bytes  

RMAN> set dbid=1669126943   --一定要设置dbid才行
executing command: SET DBID

RMAN> restore spfile from autobackup;
Starting restore at 18-DEC-15
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151218
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151217
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151216
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151215
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151214
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151213
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151212
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/18/2015 15:20:27
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

由于使用的默认参数文件启动，如果更改过autobackup的位置或格式（allocate或format），
恢复时就会找不到路径，可以从警告日志找到autobackup的位置，用日志号最新一个恢复参数文件  


RMAN> restore spfile from '/u01/oracle/TINADB/autobackup/2015_12_18/o1_mf_s_898785171_c77b4mv5_.bkp';
---刚刚备份时生成的那个controlfile和spfile的备份
Starting restore at 18-DEC-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/oracle/TINADB/autobackup/2015_12_18/o1_mf_s_898785171_c77b4mv5_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 18-DEC-15

5）以新生成的spfile启动库并恢复控制文件
RMAN> shutdown immediate;        
Oracle instance shut down

RMAN> set dbid=1669126943
executing command: SET DBID

RMAN> startup nomount;         
connected to target database (not started)
Oracle instance started
Total System Global Area    2087780352 bytes
Fixed Size                     2229944 bytes
Variable Size                520096072 bytes
Database Buffers            1560281088 bytes
Redo Buffers                   5173248 bytes

RMAN> restore controlfile from autobackup;
Starting restore at 18-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK

recovery area destination: /u01/oracle/
database name (or database unique name) used for search: TINADB
channel ORA_DISK_1: AUTOBACKUP /u01/oracle/TINADB/autobackup/2015_12_18/o1_mf_s_898785171_c77b4mv5_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151218
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/oracle/TINADB/autobackup/2015_12_18/o1_mf_s_898785171_c77b4mv5_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/oradata/tinadb/control01.ctl
output file name=/u01/fast_recovery_area/tinadb/control02.ctl    --还是原来的那两个目录
Finished restore at 18-DEC-15

6）恢复库
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

RMAN> restore database;   --利用之前的全备恢复到备份的时刻状态
Starting restore at 18-DEC-15
using channel ORA_DISK_1

skipping datafile 1; already restored to file /u01/oradata/tinadb/system01.dbf
skipping datafile 2; already restored to file /u01/oradata/tinadb/sysaux01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/tinadb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/tinadb/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/oradata/tinadb/ts_tina01.dbf
channel ORA_DISK_1: reading from backup piece /orabak/whole_TINADB_0vqp4nrf_1_1
channel ORA_DISK_1: piece handle=/orabak/whole_TINADB_0vqp4nrf_1_1 tag=TAG20151218T143214
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 18-DEC-15

RMAN> recover database;  ---利用归档和在线日志回复数据库到最新状态
Starting recover at 18-DEC-15
using channel ORA_DISK_1
starting media recovery

archived log for thread 1 with sequence 109 is already on disk as file /u01/oracle/TINADB/archivelog/1_109_898687982.dbf
archived log for thread 1 with sequence 110 is already on disk as file /u01/oracle/TINADB/archivelog/1_110_898687982.dbf
archived log file name=/u01/oracle/TINADB/archivelog/1_109_898687982.dbf thread=1 sequence=109
archived log file name=/u01/oracle/TINADB/archivelog/1_110_898687982.dbf thread=1 sequence=110
unable to find archived log
archived log thread=1 sequence=111
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/18/2015 15:39:45
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 111 and starting SCN of 1889531


可见，出现此错误的原因是恢复需要的日志记录在控制文件或恢复目录中找不到。解决方法分两种情况：
1.如果相关的日志存在且可用的话，就将此日志记录添加到控制文件或恢复目录中。
2.如果相关的日志已经被删除了或不可用了，那么就按照错误的提示scn将数据库恢复到此scn，本案例是2292709。
也就是说此时数据库只能进行不完全恢复了，在打开数据库时得使用resetlogs打开。

RMAN> recover database until scn 1889531;
Starting recover at 18-DEC-15
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 18-DEC-15

RMAN> alter database open resetlogs;
database opened

整个过程恢复完成！！！


补充3：rman恢复数据库到某一个指定时刻---基于归档日志

1）当前正常环境
SQL> select * from tina.salgrade;
     GRADE LOSAL    HISAL
---------- ---------- ----------
1   700     1200
2 1201     1400
3 1401     2000
4 2001     3000
5 3001     9999

RMAN> crosscheck archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK
validation succeeded for archived log
archived log file name=/u01/oracle/TINADB/archivelog/1_5_898789368.dbf RECID=101 STAMP=898791906
validation succeeded for archived log
archived log file name=/u01/oracle/TINADB/archivelog/1_6_898789368.dbf RECID=102 STAMP=898791928
validation succeeded for archived log
archived log file name=/u01/oracle/TINADB/archivelog/1_7_898789368.dbf RECID=103 STAMP=898792550
Crosschecked 3 objects

SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
--------------------------------------------
18-DEC-15 16.35.09.058343 PM +08:00

SQL> alter system archive log current;
System altered.


2）误操作
SQL> drop table tina.salgrade;   ---16:35之后进行的操作
Table dropped.

SQL> select * from tina.salgrade;
select * from tina.salgrade                *
ERROR at line 1:
ORA-00942: table or view does not exist

3）利用归档日志，回退到操作之前
启动库到mount状态
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2087780352 bytes
Fixed Size     2229944 bytes
Variable Size   520096072 bytes
Database Buffers 1560281088 bytes
Redo Buffers     5173248 bytes
Database mounted.

回退：
RMAN> run{
set until time "to_date('2015-12-18 16:35','yyyy-mm-dd hh24:mi')";
restore database;
recover database;
}2> 3> 4> 5>

executing command: SET until clause

Starting restore at 18-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/tinadb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/tinadb/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/oradata/tinadb/ts_tina01.dbf
channel ORA_DISK_1: reading from backup piece /orabak/tinadb/db_0_tinadb_13qp4tpm_1_1
channel ORA_DISK_1: piece handle=/orabak/tinadb/db_0_tinadb_13qp4tpm_1_1 tag=TAG20151218T161342
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/tinadb/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/tinadb/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /orabak/tinadb/db_0_tinadb_14qp4tpm_1_1
channel ORA_DISK_1: piece handle=/orabak/tinadb/db_0_tinadb_14qp4tpm_1_1 tag=TAG20151218T161342  --先去读取最近一次的全备
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 18-DEC-15

Starting recover at 18-DEC-15
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /u01/oracle/TINADB/archivelog/1_5_898789368.dbf
archived log for thread 1 with sequence 6 is already on disk as file /u01/oracle/TINADB/archivelog/1_6_898789368.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/oracle/TINADB/archivelog/1_7_898789368.dbf
channel ORA_DISK_1: starting archived log restore to default destination  ---开始恢复归档日志。
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=3
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_DISK_1: reading from backup piece /orabak/arch19qp4ts7_41_1
channel ORA_DISK_1: piece handle=/orabak/arch19qp4ts7_41_1 tag=TAG20151218T161501
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/oracle/TINADB/archivelog/1_3_898789368.dbf thread=1 sequence=3
archived log file name=/u01/oracle/TINADB/archivelog/1_4_898789368.dbf thread=1 sequence=4
archived log file name=/u01/oracle/TINADB/archivelog/1_5_898789368.dbf thread=1 sequence=5
media recovery complete, elapsed time: 00:00:01
Finished recover at 18-DEC-15

SQL> alter database open resetlogs;
Database altered.

SQL> select * from tina.salgrade;   --数据果然回来了。

     GRADE LOSAL    HISAL
---------- ---------- ----------
1   700     1200
2 1201     1400
3 1401     2000
4 2001     3000
5 3001     9999

SQL> alter system switch logfile;
System altered.

完成！



分享到：
| rman命令学习-tina（下）
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    rman异构恢复.zip

    通过实践和模拟实验，你可以更好地掌握RMAN的用法，理解如何在不同的环境下有效地保护和恢复数据，确保业务连续性。这不仅涉及到备份脚本的编写，还包括了解备份策略、恢复步骤以及在复杂环境中的应用，比如RAC和...

    ORACLE RMAN初级入门教程

    通过创建不同类型的备份，模拟恢复场景，可以加深对RMAN的理解并积累实际操作经验。 总的来说，这篇"ORACLE RMAN初级入门教程"旨在帮助初学者快速掌握RMAN的基本用法，通过实际操作提升数据库管理和恢复的能力。在...

    oracle维护之rman相关

    当数据库出现故障时，RMAN的恢复功能至关重要。基本步骤包括： 1. 使用`restore database`命令恢复数据库结构。 2. 使用`recover database`或`recover tablespace`命令应用日志，完成物理恢复。 3. 如果涉及归档日志...

    Oracle数据库日志文件损坏时修复方法的实验研究

    - **模拟故障与恢复策略**：通过模拟不同的故障场景，比如日志文件损坏、数据文件丢失等，实验研究了数据库在归档模式和非归档模式下的表现。针对每种故障类型，研究了最有效的恢复流程，包括但不限于利用备份文件、...

    Oracle数据库日志文件损坏时修复方法的实验研究.pdf

    文章通过模拟数据库故障的方式，对不同情况下的修复方法进行了实验研究。例如，当只有重做日志损坏时，可以尝试使用数据库的自动检查点信息来重建日志。如果损坏的是归档日志，可能需要找到最近的完整归档日志副本，...

    TSM SAN Agent for ORACLE RAC 实验

    选择一个测试环境，模拟故障场景，尝试从TSM恢复数据，以检验整个流程的有效性。 8. **监控和维护**：定期监控备份日志，检查备份状态和失败原因。根据需求调整备份策略，如增加备份频率、改变备份窗口等。 通过这...

    Oracle9i实验的答案：作为Oracle9i实验指导书的详细答案

    实验应包含备份计划的制定，以及在模拟故障场景下如何执行恢复操作。 七、性能优化 Oracle9i提供了一系列性能优化工具和方法，如SQL*Profiler用于查找性能瓶颈，Explain Plan分析查询执行计划，以及调整表分区和...

    Oracle-backup[归纳].pdf

    在实施备份与恢复策略前，应建立一个模拟环境进行测试，以验证备份的有效性和恢复的可行性。这有助于熟悉操作流程，并减少在生产环境中出错的风险。 二、备份与恢复方法 2.1 用户管理的备份与恢复 用户管理的备份...

    OCM考试解答 OCM题库讲解 中文版

    这包括对数据库架构、存储结构、性能优化和故障恢复等深入理解。 2. **RMAN（Recovery Manager）**：Oracle数据库的备份和恢复工具，OCM候选人需要熟练掌握RMAN的使用，包括备份策略、增量备份、归档日志管理等。 ...

    oracle的健康检查__dba经常做的事情

    - 验证备份的完整性和可恢复性，可以使用RMAN（恢复管理器）进行测试恢复。 - 监控备份日志，及时发现备份失败。 4. **数据库优化**： - 使用`EXPLAIN PLAN`分析SQL执行计划，查找效率低下的查询。 - 应用索引...

    Oracle+10g+OCA操作中文教材PDF【1Z0-042】(manual+Lab)

    10. **实验与实践**：10g_DBA1_1Z0-042_v2_6_lab.pdf提供了实际操作练习，帮助巩固理论知识，包括模拟真实的数据库环境进行故障恢复、性能调优和安全管理等操作。 通过这本教材，学习者可以系统地学习Oracle 10g...

    Oracle_OCM全套教材_包括Tunning.rar

    - 模拟考试环境：通过模拟实验加深对理论知识的理解，提高实际操作技能。 - 实战案例分析：研究真实的性能问题案例，学习如何应用调优技巧解决实际问题。 Oracle OCM认证要求考生具备全面深入的Oracle数据库知识...

    oracle 10g试验附录 样本数据库.rar

    6. 数据备份与恢复：学习如何使用RMAN（恢复管理器）进行数据库备份和恢复操作，这对于数据库管理员来说是至关重要的技能。 7. 性能优化：可能包含关于索引创建、查询优化器的工作原理，以及如何使用SQL Trace和 ...

    OCP课程文档 安装包 虚机教材 题

    - **故障模拟**：模拟数据库错误或故障，让学生学习如何诊断问题并实施恢复策略。 ### 4. **练习题目** 练习题目是检验学习效果的重要手段，可能包括选择题、填空题、问答题和实际操作题，覆盖了Oracle 11g管理的...

    OCP(042-043-047)考试题

    3. **047考试**：这可能涉及到高级数据库管理技术，如数据迁移、数据泵、RMAN（恢复管理器）备份和恢复、闪回技术以及集群环境下的数据库管理。考生需要掌握如何在复杂环境中维护数据库的稳定性和可用性。 **学习...

    Oracle数据块损坏之10231内部事件不完全恢复

    6. **数据验证和修复**：恢复完成后，需要验证数据的完整性，可能还需要进行一些手动的数据修复工作，特别是对于部分记录丢失或损坏的情况。 7. **优化恢复策略**：在未来的数据保护策略中，应确保定期备份，启用...

    oracle考试，ocp考试及笔记

    4. 故障诊断与解决：学习如何处理数据库故障，包括数据丢失、系统崩溃、网络问题等，熟悉RMAN（Recovery Manager）和闪回技术的使用。 5. 安全性管理：理解Oracle的用户权限、角色、审计机制，以及如何设置访问控制...

Magicbox
Global site tag (gtag.js) - Google Analytics