ITPub博客

首页 > Linux操作系统 > Linux操作系统 > open resetlogs后数据恢复

open resetlogs后数据恢复

原创 Linux操作系统 作者:wailon 时间:2013-11-10 21:15:08 0 删除 编辑

系统环境:

SQL> select * from v$version whererownum=1;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination           /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence     1

Next log sequence to archive   2

Current log sequence           2

1、建立备份集

RMAN> backup as compressed backupset database;

Starting backup at 06-JUN-13

using target database control file insteadof recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=23 device type=DISK

channel ORA_DISK_1: starting compressed fulldatafile backup set

channel ORA_DISK_1: specifying datafile(s)in backup set

input datafile file number=00001name=/u01/app/oracle/oradata/wailon/system01.dbf

input datafile file number=00002name=/u01/app/oracle/oradata/wailon/sysaux01.dbf

input datafile file number=00003name=/u01/app/oracle/oradata/wailon/undotbs01.dbf

input datafile file number=00005name=/u01/app/oracle/oradata/wailon/tbs_lrj.dbf

input datafile file number=00004name=/u01/app/oracle/oradata/wailon/users01.dbf

channel ORA_DISK_1: starting piece 1 at06-JUN-13

channel ORA_DISK_1: finished piece 1 at06-JUN-13

piecehandle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/0aobg5b7_1_1tag=TAG20130606T072111 comment=NONE

channel ORA_DISK_1: backup set complete,elapsed time: 00:01:05

Finished backup at 06-JUN-13

Starting ControlFile and SPFILE Autobackupat 06-JUN-13

piecehandle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-2746988849-20130606-05comment=NONE

Finished Control File and SPFILE Autobackupat 06-JUN-13

2、手工备份控制文件

SQL> alter database backup controlfile to '/u01/controlfile.bk';

3、模拟产生数据,切换日志并执行检查点,关闭数据库

SQL> create table wailon.tab as select *from scott.emp;

Table created.

SQL> select count(*) from wailon.tab;

COUNT(*)

----------

       14

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

4、用手工备份的控制文件替换现有的控制文件

[oracle@RAC1 ~]$ cp /u01/controlfile.bk/u01/app/oracle/oradata/wailon/control01.ctl

[oracle@RAC1 ~]$ cp /u01/controlfile.bk/u01/app/oracle/oradata/wailon/control02.ctl

5、恢复并以RESETLOGS打开数据库

SQL> startup

ORACLE instance started.

Total System Global Area  417546240 bytes

Fixed Size                  2228944 bytes

Variable Size             281021744 bytes

Database Buffers          125829120 bytes

Redo Buffers                8466432 bytes

Database mounted.

ORA-01589: must use RESETLOGS orNORESETLOGS 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 beconsistent

ORA-01110: data file 1:'/u01/app/oracle/oradata/wailon/system01.dbf'

SQL> recover database;

ORA-00283: ?

ORA-01610: ?ACKUP CONTROLFILE ?

SQL> recover database using backup controlfile;

SQL> recover database using backupcontrolfile;

ORA-00279: change 1129473 generated at06/06/2013 06:37:10 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817367830.dbf

ORA-00280: change 1129473 for thread 1 isin sequence #1

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

auto

ORA-00279: change 1129589 generated at06/06/2013 07:26:11 needed for thread 1

ORA-00289: suggestion :/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817367830.dbf

ORA-00280: change 1129589 for thread 1 isin sequence #2

ORA-00278: log file'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817367830.dbf' no longerneeded for this

recovery

ORA-00308: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817367830.dbf'

ORA-27037: ??

Linux-x86_64 Error: 2: No such file ordirectory

Additional information: 3

SQL> select * from v$logfile;

   GROUP# STATUS  TYPE    MEMBER                                            IS_

---------- ------- --------------------------------------------------------- ---

        3         ONLINE /u01/app/oracle/oradata/wailon/redo03.log          NO

        2         ONLINE  /u01/app/oracle/oradata/wailon/redo02.log          NO

        1         ONLINE /u01/app/oracle/oradata/wailon/redo01.log          NO

SQL> select group#,thread#,sequence#,archived,status,first_change# from v$log;

   GROUP#    THREAD#  SEQUENCE# ARC STATUS           FIRST_CHANGE#

---------- ---------- ---------- ------------------- -------------

        1          1          1 NO CURRENT                1127284

        3          1          0 YES UNUSED                       0

        2          1          0 YES UNUSED                       0

SQL> recover database using backup controlfile;

ORA-00279: change 1129589 generated at06/06/2013 07:26:11 needed for thread 1

ORA-00289: suggestion :/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817367830.dbf

ORA-00280: change 1129589 for thread 1 isin sequence #2

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

/u01/app/oracle/oradata/wailon/redo02.log

Log applied.

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS orNORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

6RESETLOGS后备份控制文件

SQL> alter database backup controlfile to '/u01/controlfile_after_resetlogs.bk';

7、模拟产生数据,切换日志并执行检查点,关闭数据库

SQL> select count(*) from wailon.tab;

COUNT(*)

----------

       14

SQL> insert into wailon.tab select *from wailon.tab;

14 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from wailon.tab;

COUNT(*)

----------

       28

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

8、用reselogs之前的备份集还原并恢复数据库

情况一、恢复备份集的控制文件

[oracle@RAC1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 -Production on Thu Jun 6 04:59:14 2013

Copyright (c) 1982, 2011, Oracle and/or itsaffiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area     417546240 bytes

Fixed Size                     2228944 bytes

Variable Size                281021744 bytes

Database Buffers             125829120 bytes

Redo Buffers                   8466432 bytes

RMAN>--从备份集还原控制文件

RMAN> restore controlfile from'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-2746988849-20130606-05';

Starting restore at 06-JUN-13

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsedtime: 00:00:01

output filename=/u01/app/oracle/oradata/wailon/control01.ctl

output filename=/u01/app/oracle/oradata/wailon/control02.ctl

Finished restore at 06-JUN-13

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 06-JUN-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=134 devicetype=DISK

channel ORA_DISK_1: starting datafilebackup set restore

channel ORA_DISK_1: specifying datafile(s)to restore from backup set

channel ORA_DISK_1: restoring datafile00001 to /u01/app/oracle/oradata/wailon/system01.dbf

channel ORA_DISK_1: restoring datafile00002 to /u01/app/oracle/oradata/wailon/sysaux01.dbf

channel ORA_DISK_1: restoring datafile00003 to /u01/app/oracle/oradata/wailon/undotbs01.dbf

channel ORA_DISK_1: restoring datafile00004 to /u01/app/oracle/oradata/wailon/users01.dbf

channel ORA_DISK_1: restoring datafile00005 to /u01/app/oracle/oradata/wailon/tbs_lrj.dbf

channel ORA_DISK_1: reading from backuppiece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/0aobg5b7_1_1

channel ORA_DISK_1: piecehandle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/0aobg5b7_1_1tag=TAG20130606T072111

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete,elapsed time: 00:01:05

Finished restore at 06-JUN-13

--未还原上次RESETLOGS后备份的控制文件--开始--

RMAN> recover database;

Starting recover at 06-JUN-13

using channel ORA_DISK_1

starting media recovery

archived log filename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817367830.dbf thread=1sequence=1

archived log filename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817367830.dbf thread=1sequence=2

media recovery complete, elapsed time:00:00:01

Finished recover at 06-JUN-13

SQL> conn / as sysdba

Connected.

 

SQL> alter database open resetlogs;

Database altered.

--RESETLOGS后产生的数据就丢失了

SQL> select count(*) from wailon.tab;

COUNT(*)

----------

       14

--未还原上次RESETLOGS后备份的控制文件--结束--

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

--还原RESETLOGS后备份的控制文件

[oracle@RAC1 u01]$ cp/u01/controlfile_after_resetlogs.bk/u01/app/oracle/oradata/wailon/control01.ctl

[oracle@RAC1 u01]$ cp/u01/controlfile_after_resetlogs.bk/u01/app/oracle/oradata/wailon/control02.ctl

--恢复数据库

SQL> startup mount;

ORACLE instance started.

Total System Global Area  417546240 bytes

Fixed Size                  2228944 bytes

Variable Size             301993264 bytes

Database Buffers          104857600 bytes

Redo Buffers                8466432 bytes

Database mounted.

SQL> recover database using backup controlfile;

ORA-00279: change 1129393 generated at06/06/2013 07:21:12 needed for thread 1

ORA-00289: suggestion :/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817367830.dbf

ORA-00280: change 1129393 for thread 1 isin sequence #1

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

auto

ORA-00279: change 1129589 generated at06/06/2013 07:26:11 needed for thread 1

ORA-00289: suggestion :/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817367830.dbf

ORA-00280: change 1129589 for thread 1 isin sequence #2

ORA-00278: log file'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817367830.dbf' no longerneeded for this

recovery

ORA-00279: change 1129825 generated at06/06/2013 07:33:16 needed for thread 1

ORA-00289: suggestion :/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817371196.dbf

ORA-00280: change 1129825 for thread 1 isin sequence #1

ORA-00278: log file'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817367830.dbf' no longerneeded for this

recovery

ORA-00279: change 1130093 generated at06/06/2013 07:34:22 needed for thread 1

ORA-00289: suggestion :/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817371196.dbf

ORA-00280: change 1130093 for thread 1 isin sequence #2

ORA-00278: log file'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817371196.dbf' no longerneeded for this

recovery

ORA-00308: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817371196.dbf'

ORA-27037: ??

Linux-x86_64 Error: 2: No such file ordirectory

Additional information: 3

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 1130093 generated at06/06/2013 07:34:22 needed for thread 1

ORA-00289: suggestion :/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817371196.dbf

ORA-00280: change 1130093 for thread 1 isin sequence #2

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

cancel

Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.

--校验数据

SQL> select count(*) from wailon.tab;

COUNT(*)

----------

       28

 情况二、使用现有的控制文件

--模拟生产数据

SQL> insert into wailon.tab select *from wailon.tab;

28 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from wailon.tab;

COUNT(*)

----------

       56

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

--还原并恢复数据

[oracle@RAC1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 -Production on Thu Jun 6 07:55:00 2013

Copyright (c) 1982, 2011, Oracle and/or itsaffiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started

database mounted

Total System Global Area     417546240 bytes

Fixed Size                     2228944 bytes

Variable Size                301993264 bytes

Database Buffers             104857600 bytes

Redo Buffers                   8466432 bytes

RMAN> restore database;

Starting restore at 06-JUN-13

using target database control file insteadof recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=133 devicetype=DISK

channel ORA_DISK_1: starting datafilebackup set restore

channel ORA_DISK_1: specifying datafile(s)to restore from backup set

channel ORA_DISK_1: restoring datafile00001 to /u01/app/oracle/oradata/wailon/system01.dbf

channel ORA_DISK_1: restoring datafile00002 to /u01/app/oracle/oradata/wailon/sysaux01.dbf

channel ORA_DISK_1: restoring datafile00003 to /u01/app/oracle/oradata/wailon/undotbs01.dbf

channel ORA_DISK_1: restoring datafile00004 to /u01/app/oracle/oradata/wailon/users01.dbf

channel ORA_DISK_1: restoring datafile00005 to /u01/app/oracle/oradata/wailon/tbs_lrj.dbf

channel ORA_DISK_1: reading from backuppiece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/0aobg5b7_1_1

channel ORA_DISK_1: piecehandle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/0aobg5b7_1_1tag=TAG20130606T072111

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete,elapsed time: 00:01:05

Finished restore at 06-JUN-13

RMAN> recover database;

Starting recover at 06-JUN-13

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1is already on disk as file /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817367830.dbf

archived log for thread 1 with sequence 2is already on disk as file/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817367830.dbf

archived log for thread 1 with sequence 1is already on disk as file/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817371196.dbf

archived log filename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817367830.dbf thread=1sequence=1

archived log filename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_817367830.dbf thread=1sequence=2

archived log filename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_817371196.dbf thread=1sequence=1

media recovery complete, elapsed time:00:00:02

Finished recover at 06-JUN-13

--校验数据

SQL> select count(*) from wailon.tab;

COUNT(*)

----------

       56

9、总结

恢复数据库时,若是不完全恢复或者使用了旧的控制文件,需要使用OPEN RESETLOGS打开数据库,必须马上进行备份,否则有可能丢失数据。
如果当前的控制文件未损坏,就算未做备份,数据也有可能不会丢失。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/429786/viewspace-776250/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-11-08

  • 博文量
    51
  • 访问量
    290613