open resetlogs后数据恢复

最新推荐文章于 2024-07-22 15:12:37 发布

cuiwu7783

最新推荐文章于 2024-07-22 15:12:37 发布

阅读量131

点赞数

文章标签：数据库

系统环境：

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(*)

----------

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.

6、RESETLOGS后备份控制文件

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

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

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

COUNT(*)

----------

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

14 rows created.

SQL> commit;

Commit complete.

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

COUNT(*)

----------

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

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(*)

----------

--未还原上次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(*)

----------

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

--模拟生产数据

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

28 rows created.

SQL> commit;

Commit complete.

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

COUNT(*)

----------

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

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