oracle备份整个过程,数据库备份与恢复全过程

最新推荐文章于 2024-01-17 15:10:36 发布
最新推荐文章于 2024-01-17 15:10:36 发布
阅读量528 收藏
点赞数
文章标签： oracle备份整个过程

由于客户需要用真实的数据测试，所以我一开始作了rman全数据库备份，包括日志归档的备份，但是最后的时候，日志备份出现了备份失败的错误，不过没有关系，我可以只要恢复到rman备份的时刻就可以了，共112g

RMAN>run{

1>allocate channel c1 type disk;

2>allocate channel c2 type disk;

3>backp

4>   format = '/oracle/r_back/%d_%s_%t'

5>(database);

6>backup archivelog all;

7>release channel c1;

8>release channel c2;

9>}

后来备份完成以后,在前天恢复的

RMAN> sql 'alter session set nls_date_format="dd mm yyyy hh24:mi:ss"';

using target database controlfile instead of recovery catalog

sql statement: alter session set nls_date_format="dd mm yyyy hh24:mi:ss"

RMAN>run{

1>allocae channel c1 type disk;

2>allocae channel c2 type disk;

3>allocae channel c3 type disk;

4>allocae channel c4 type disk;

5>allocae channel c5 type disk;

6>allocae channel c6 type disk;

7>allocae channel c7 type disk;

8>set until time '16 10 2006 09:00:00';

9>restore database;

10>recover database;

11>release channel c1;

12>release channel c2;

13>release channel c3;

14>release channel c4;

15>release channel c5;

16>release channel c6;

17>release channel c7;

18>}

恢复完成，但是最后的时候,提示日志没有恢复成功,这个无所谓的,因为我没有当时需要恢复日志的要求,

后来,就开始打开了,

可是打开的时候出现了问题

$ sqlplus

SQL*Plus: Release 9.2.0.1.0 - Production on Sun Oct 22 09:17:16 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: / as sysdba

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

错了呵呵

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/oracle/oradata/MOSS/system01.dbf'

system01.dbf数据文件需要介质恢复,于是试探一下恢复整个数据库,因为提示这个问题的话,可能所有的数据文件都需要介质恢复

SQL> recover database;

ORA-00279: change 8998462319508 generated at 10/17/2006 09:17:27 needed for

thread 1

ORA-00289: suggestion : /oracle/products/9.2/dbs/arch1_12671.dbf

ORA-00280: change 8998462319508 for thread 1 is in sequence #12671

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log '/oracle/products/9.2/dbs/arch1_12671.dbf'

ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log '/oracle/products/9.2/dbs/arch1_12671.dbf'

ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory

Additional information: 3

SQL> recover database;

ORA-00279: change 8998462319508 generated at 10/17/2006 09:17:27 needed for

thread 1

ORA-00289: suggestion : /oracle/products/9.2/dbs/arch1_12671.dbf

ORA-00280: change 8998462319508 for thread 1 is in sequence #12671

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

糟糕,原来的一些自动备份的跟踪文件跟删掉了,晕....

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/oracle/oradata/MOSS/system01.dbf'

介质恢复一下数据文件吧,可能也会找不到跟踪文件

SQL>

SQL> conn / as sysdba

Connected.

SQL> recover database datafile '/oracle/oradata/MOSS/system01.dbf';

ORA-00274: illegal recovery option DATAFILE

SQL> recover datafile '/oracle/oradata/MOSS/system01.dbf';

ORA-00279: change 8998462319509 generated at 10/17/2006 09:17:27 needed for

thread 1

ORA-00289: suggestion : /oracle/products/9.2/dbs/arch1_12671.dbf

ORA-00280: change 8998462319509 for thread 1 is in sequence #12671

Specify log: {=suggested | filename | AUTO | CANCEL}

auot

ORA-00308: cannot open archived log 'auot'

ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory

Additional information: 3

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log '/oracle/products/9.2/dbs/arch1_12671.dbf'

ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log '/oracle/products/9.2/dbs/arch1_12671.dbf'

ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory

Additional information: 3

晕,真的找不到了,怎么办呢,先备份一下控制文件

SQL> alter database backup controlfile to trace;

Database altered.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/oracle/oradata/MOSS/system01.dbf'

SQL> recover datafile '/oracle/oradata/MOSS/system01.dbf'

ORA-00279: change 8998462319509 generated at 10/17/2006 09:17:27 needed for

thread 1

ORA-00289: suggestion : /oracle/products/9.2/dbs/arch1_12671.dbf

ORA-00280: change 8998462319509 for thread 1 is in sequence #12671

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> recovery database until cancel;

SP2-0734: unknown command beginning "recovery d..." - rest of line ignored.

SQL> l

1* alter database open

SQL> recover database until cancel;

ORA-00279: change 8998462319508 generated at 10/17/2006 09:17:27 needed for

thread 1

ORA-00289: suggestion : /oracle/products/9.2/dbs/arch1_12671.dbf

ORA-00280: change 8998462319508 for thread 1 is in sequence #12671

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log '/oracle/products/9.2/dbs/arch1_12671.dbf'

ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log '/oracle/products/9.2/dbs/arch1_12671.dbf'

ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory

Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/oracle/oradata/MOSS/system01.dbf'

怎么办呢,文件都丢失了,那不就惨了,难道还需要把冷备份的数据,覆盖回去么?不行,这个阶段找不到跟踪文件是日志里面存在记录,于是,决定清空日志

SQL>

^H^H^H^H^H00308

SP2-0042: unknown comm00308" - rest of line ignored.

SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> l

1* alter database clear unarchived logfile group 1

SQL> c /1/2

1* alter database clear unarchived logfile group 2

SQL> /

Database altered.

SQL> l

1* alter database clear unarchived logfile group 2

SQL> c /2/3

1* alter database clear unarchived logfile group 3

SQL> /

Database altered.

SQL> l

1* alter database clear unarchived logfile group 3

SQL> c /3/4

1* alter database clear unarchived logfile group 4

SQL> /

Database altered.

SQL> l

1* alter database clear unarchived logfile group 4

SQL> c /4/5

1* alter database clear unarchived logfile group 5

SQL> /

Database altered.

SQL> l

1* alter database clear unarchived logfile group 5

SQL> c /5/6

1* alter database clear unarchived logfile group 6

SQL> /

Database altered.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/oracle/oradata/MOSS/system01.dbf'

不好意思,还没有介质恢复数据文件,呵呵.........

SQL> recover datafile  '/oracle/oradata/MOSS/system01.dbf'

Media recovery complete.

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> alter database open ;

alter database open

*

ERROR at line 1:

ORA-01113: file 2 needs media recovery

ORA-01110: data file 2: '/oracle/oradata/MOSS/undotbs01.dbf'

SQL> recover datafile  '/oracle/oradata/MOSS/undotbs01.dbf'

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 3 needs media recovery

ORA-01110: data file 3: '/oracle/oradata/MOSS/cwmlite01.dbf'

SQL> recover datafile  '/oracle/oradata/MOSS/cwmlite01.dbf'

Media recovery complete.

SQL> alter database open

2  ;

alter database open

*

ERROR at line 1:

ORA-01113: file 4 needs media recovery

ORA-01110: data file 4: '/oracle/oradata/MOSS/drsys01.dbf'

SQL> recover datafile '/oracle/oradata/MOSS/drsys01.dbf'

Media recovery complete.

SQL>

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 5 needs media recovery

ORA-01110: data file 5: '/oracle/oradata/MOSS/example01.dbf'

SQL>

SQL> recover datafile 5;

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 6 needs media recovery

ORA-01110: data file 6: '/oracle/oradata/MOSS/indx01.dbf'

SQL> recover datafile 6;

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 7 needs media recovery

ORA-01110: data file 7: '/oracle/oradata/MOSS/odm01.dbf'

SQL>

SQL> recover datafile 7;

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 8 needs media recovery

ORA-01110: data file 8: '/oracle/oradata/MOSS/tools01.dbf'

SQL> recover datafile 8;

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 9 needs media recovery

ORA-01110: data file 9: '/oracle/oradata/MOSS/users01.dbf'

SQL> recover datafile

9;

SP2-0226: Invalid line number

SQL> recover datafile 9;

Media recovery complete.

SQL>

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 10 needs media recovery

ORA-01110: data file 10: '/oracle/oradata/MOSS/xdb01.dbf'

SQL> recover datafile 10;

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 11 needs media recovery

ORA-01110: data file 11: '/oracle/oradata/MOSS/MOSS.dbf'

SQL> alter datafile 11;

alter datafile 11

*

ERROR at line 1:

ORA-00940: invalid ALTER command

SQL> recover datafile 11;

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 12 needs media recovery

ORA-01110: data file 12: '/oracle/oradata/MOSS/rman_ts.dbf'

SQL> recover datafile 12;

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 13 needs media recovery

ORA-01110: data file 13: '/oracle/oradata/MOSS/MOSS_CC1.dbf'

SQL> recover datafile 13;

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 14 needs media recovery

ORA-01110: data file 14: '/oracle/oradata/MOSS/MOSS_INDEX.dbf'

SQL> alter datafile 14;

alter datafile 14

*

ERROR at line 1:

ORA-00940: invalid ALTER command

SQL> recover datafile 14;

Media recovery complete.

SQL> recover datafile 15;

Media recovery complete.

SQL> recover datafile 16;

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 17 needs media recovery

ORA-01110: data file 17: '/oracle/oradata/MOSS/MOSS_SP1.dbf'

SQL> recover datafile 17;

Media recovery complete.

SQL> recover datafile 18;

Media recovery complete.

SQL> recover datafile 19;

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 20 needs media recovery

ORA-01110: data file 20: '/oracle/oradata/MOSS/MOSS_LY1.dbf'

SQL> recover datafile 20;

Media recovery complete.

SQL> recover datafile 21;

Media recovery complete.

SQL> recover datafile 22;

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 23 needs media recovery

ORA-01110: data file 23: '/oracle/oradata/MOSS/perfstat.dbf'

SQL> recover datafile 23;

Media recovery complete.

SQL> recover datafile 24;

ORA-01179: file 24 does not exist

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 25 needs media recovery

ORA-01110: data file 25: '/oracle/oradata/MOSS/BILL_.dbf'

SQL> recover datafile 25;

Media recovery complete.

SQL> recover datafile 26;

ORA-01179: file 26 does not exist

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 27 needs media recovery

ORA-01110: data file 27: '/oracle/oradata/MOSS/MOSS_MONTH1.dbf'

SQL> recover datafile 27;

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 28 needs media recovery

ORA-01110: data file 28: '/oracle/oradata/MOSS/MOSS_MONTH2.dbf'

SQL> recover datafile 28;

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 29 needs media recovery

ORA-01110: data file 29: '/oracle/oradata/MOSS/MOSS_MONTH3.dbf'

SQL> recover datafile 29;

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 30 needs media recovery

ORA-01110: data file 30: '/oracle/oradata/MOSS/IBAS_DATA.dbf'

SQL> recover datafile 30;

Media recovery complete.

SQL>

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 31 needs media recovery

ORA-01110: data file 31: '/oracle/oradata/MOSS/MOSS_MONTH4.dbf'

SQL> recover datafile 31;

Media recovery complete.

SQL> recover datafile 32;

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 33 needs media recovery

ORA-01110: data file 33: '/oracle/oradata/MOSS/MOSS_MONTH6.dbf'

SQL> recover datafile 33;

Media recovery complete.

SQL> recover datafile 34;

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 35 needs media recovery

ORA-01110: data file 35: '/oracle/oradata/MOSS/MOSS_MONTH8.dbf'

SQL> recover datafile 35;

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 36 needs media recovery

ORA-01110: data file 36: '/oracle/oradata/MOSS/MOSS_MONTH9.dbf'

SQL> recover datafile 36;

Media recovery complete.

SQL> recover datafile 37;

Media recovery complete.

SQL> recover datafile 38;

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 39 needs media recovery

ORA-01110: data file 39: '/oracle/oradata/MOSS/MOSS_MONTH12.dbf'

SQL> recover datafile 39;

Media recovery complete.

SQL> recover datafile 40;

ORA-01179: file 40 does not exist

SQL> alter database open;

Database altered.

SQL>

ok   问题解决,三个小时已经过去了,呵呵.............

马上联系开发人员,确定数据没有出错,马上rman备份了一次,算是放心了

我喜欢你小朋友
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
完整版Oracle11数据库教程课件 第12章_备份与恢复（共66页）.ppt
12-18
【完整课程列表】 完整版Oracle11数据库教程课件 第1章_数据库理论基础（共13页）.ppt 完整版Oracle11数据库教程课件 第2章_Oracle体系结构（共11页）...完整版Oracle11数据库教程课件 第12章_备份与恢复（共66页）.ppt
ORA-01139:
09-22 4403
ORA-01139: RESETLOGS option only valid after an incomplete database recovery介绍一个意外情况，如果上面的方法不成功，数据库还是不能open，采...
NBU异机恢复报ORA-01139: RESETLOGS option only valid after an incomplete database recovery
qq_45678201的博客
12-03 2458
SQL> startup mount; Oracle 例程已经启动。 Total System Global Area 6797832192 bytes Fixed Size 2241264 bytes Variable Size 3523218704 bytes Database Buffers 3254779904 bytes Redo Buffers 17592320 bytes 数据库装载完毕。 SQL> alter database open resetlogs; .
oracle数据库定时备份
最新发布
Wait_for_life的博客
01-17 1029
系统上线后对数据库进行定时备份。
ora-01110,ora-01139错误
cuandui4473的博客
11-30 804
昨天晚上在自己笔记本上关库之后不小心把控制文件全部删除了，但是控制文件有rman的备份。没有数据库全备。丢失控制文件，如果有控制文件和数据库全备，是没什么问题的，restore controlfile from autoback...
oracle报错解决
weixin_40548182的博客
07-19 3474
登陆oracle，提示错误信息ORA-00257。
关于数据库冷备份与热备份的对比
12-14
一、 冷备份 　冷备份发生在数据库已经正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库。冷备份时将要害性文件拷贝到另外的位置的一种说法。... 再实施备份的全过程中，数据库必须要作
完整版Oracle11数据库教程课件 全套PPT课件资源集合 全套12个章节.rar
12-18
【完整课程列表】 完整版Oracle11数据库教程课件 第1章_数据库理论基础（共13页）.ppt 完整版Oracle11数据库教程课件 第2章_Oracle体系结构（共11页）...完整版Oracle11数据库教程课件 第12章_备份与恢复（共66页）.ppt
expdp异地备份全过程（非常详细）
12-13
详细记录了expdp异地备份过程，测试成功， 并后附可选参数介绍
Oracle备份还原.doc
12-20
oracle数据库备份还原 ------------------------备份------------------------------ 一、全备份 导出整个数据库, 命令 exp 用户名/密码@服务名 file=文件全路径 full=y ------------------------还原-------...
oracle报错02122,系统出现问题：ora-01033 oracle initialization or shutdown in progress
weixin_30346025的博客
04-07 168
土哥™ 说：This error may indicate a rollback segment corruption. This may require a recovery from a database backup depending on the situation.Oldllama [痛苦...] 说：完了，这下完了Oldllama [痛苦...] 说：只有DMPOldll...
[2020-12-25]oracle redo文件损坏的几种情况和处理方法
m15217321304的博客
12-25 1824
第一种情况 一、非current日志组并且状态不是active --//redo 损坏测试 --//非current日志组且状态不是active SQL> select * from t; ID ---------- 10 SQL> update t set id = 11; 1 row updated. SQL> col member for a34 SQL> select v1.group#,v1.status, member,se
存储过程-ora-错误码集锦
搞运维的万精油的博客
09-28 3487
引用：https://blog.csdn.net/qq_41553194/article/details/88032585 ORACL内部异常： ORA-00001: 违反唯一约束条件 (.) ORA-00017: 请求会话以设置跟踪事件 ORA-00018: 超出最大会话数 ORA-00019: 超出最大会话许可数 ORA-00020: 超出最大进程数 () ORA-00021: 会话附属于其它某些进程；无法转换会话 ORA-00022: 无效的会话 ID；访问被拒绝 ORA-00023: 会话引用进程私
Oracle 错误代码详解及解决方式--ORA
热门推荐
心寒的博客
04-21 2万+
ORA-00001: 违反唯一约束条件 (.) 错误说明：当在唯一索引所对应的列上键入重复值时，会触发此异常。 ORA-00017: 请求会话以设置跟踪事件 ORA-00018: 超出最大会话数 ORA-00019: 超出最大会话许可数 ORA-00020: 超出最大进程数 () ORA-00021: 会话附属于其它某些进程；无法转换会话 ORA-00022: 无效的会话 ID；访问被拒绝 ORA...
ORACLE之rman备份恢复及故障处理
霸王龙的日常专栏
09-28 2612
ORACLE之rman备份恢复及故障处理
oracle备份片过期,Oracle删除过期备份时报no channel allocated for maintenance(of an appropriate type)...
weixin_30600605的博客
04-04 326
问题描述：AIX 平台Oracle 10G,今天发现数据库归档空间满了，就通过crosscheck 后，执行delete force obsolete时报no channel allocated for maintenance(of an appropriate type) 错误，原因分析：经过查询可能是因为在之前的备份过程中采用了不同设备类型备份导致不能正常删除过期的备份解决方案：先摘录一段对C...
ORACLE10G RMAN中run代码块中自动释放通道
JACKY.昊昊 的专栏
11-13 2390
        在RMAN的run代码块中，oracle会自动释放channel，不用手工书写release channel channelname语句，测试如下:SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 1
2021-05-06
qq_52080662的博客
05-06 447
今天像平常一样，正常启动Oracle，但是出现以下报错： 于是接下来是解决方法： 下一步： 注意：现在做到这一步一直在报错，当时出现这个错误，我也不知道要如何解决，最后介质恢复成功，是在我试了几次修改时间的情况下。最后虽然仍然不知道为什么，但最后得到总结：时间要在在自己最后一次安装数据库的时间的基础上，尽量接近，例如：我的最近一次安装数据库的时间是在2021-04-30，但我设置日志空间是在2021-05-03，所以在我设置时间为：‘2021-05-01 00:00:00...
oracle数据库备份恢复
09-08
1. 创建备份：使用Oracle提供的RMAN（Recovery Manager）工具来创建数据库备份。RMAN是一个强大的备份和恢复工具，可以进行全库备份或者增量备份。可以使用RMAN命令来设置备份策略、目标位置和备份类型等。例如，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值