失眠网 > rman 恢复---归档丢失and数据文件损坏

rman 恢复---归档丢失and数据文件损坏

时间：2023-08-06 20:45:00

在归档日志有丢失的情况下并且数据文件损坏，恢复数据库。用rman恢复，归档有丢失，恢复到指定的sequence 1、在数据库open的情况下，做一些操作，确定这些操作所在的归档日志文件

SQL> select group#,sequence#,members,status from v$log; GROUP# SEQUENCE# MEMBERS STATUS

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

1 10 1 ACTIVE

2 11 1 CURRENT

3 9 1 ACTIVE SQL> show user;

USER is "HAOZG"

SQL> create table test11(name varchar2(10),age number); Table created. SQL> insert into test11 values('haozg',28); 1 row created. SQL> commit; Commit complete. SQL> / Commit complete. SQL> insert into test11 values('zhangf',29); 1 row created. SQL> commit; Commit complete. SQL> alter system switch logfile; System altered. SQL> select group#,sequence#,members,status from v$log; GROUP# SEQUENCE# MEMBERS STATUS

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

1 10 1 ACTIVE

2 11 1 ACTIVE

3 12 1 CURRENT 上面做的操作保存在sequence是11的归档日志文件中。 2、做全库备份 RMAN> run{

allocate channel c1 type disk maxpiecesize=500m;

backup current controlfile format '+dgasm/backup/haozg/ctl_%d_%s';

backup full database format '+dgasm/backup/haozg/db_%d_%s_%p_%t';

sql 'alter system archive log current';

release channel c1;

}2> 3> 4> 5> 6> 7> using target database control file instead of recovery catalog

allocated channel: c1

channel c1: SID=44 device type=DISK Starting backup at 01-JUL-12

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

including current control file in backup set

channel c1: starting piece 1 at 01-JUL-12

channel c1: finished piece 1 at 01-JUL-12

piece handle=+DGASM/backup/haozg/ctl_ora11_41 tag=TAG0701T101120 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:03

Finished backup at 01-JUL-12 Starting backup at 01-JUL-12

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00001 name=+DGASM/ora11/datafile/system.257.785186755

input datafile file number=00002 name=+DGASM/ora11/datafile/sysaux.258.785186845

input datafile file number=00003 name=+DGASM/ora11/datafile/undotbs1.259.785186901

input datafile file number=00004 name=+DGASM/ora11/datafile/users.272.787470817

channel c1: starting piece 1 at 01-JUL-12

channel c1: finished piece 1 at 01-JUL-12

piece handle=+DGASM/backup/haozg/db_ora11_42_1_787486286 tag=TAG0701T101126 comment=NONE

channel c1: starting piece 2 at 01-JUL-12

channel c1: finished piece 2 at 01-JUL-12

piece handle=+DGASM/backup/haozg/db_ora11_42_2_787486286 tag=TAG0701T101126 comment=NONE

channel c1: backup set complete, elapsed time: 00:02:00

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel c1: starting piece 1 at 01-JUL-12

channel c1: finished piece 1 at 01-JUL-12

piece handle=+DGASM/backup/haozg/db_ora11_43_1_787486406 tag=TAG0701T101126 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

Finished backup at 01-JUL-12 sql statement: alter system archive log current released channel: c1 RMAN> 3、然再做操作，确定所在的归档文件 SQL> select group#,sequence#,members,status from v$log; GROUP# SEQUENCE# MEMBERS STATUS

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

1 13 1 CURRENT

2 11 1 INACTIVE

3 12 1 ACTIVE SQL> create table test22(name varchar2(10),age number); Table created. SQL> insert into test22 values('guany',30); 1 row created. SQL> commit; Commit complete. SQL> alter system switch logfile; System altered. SQL> select group#,sequence#,members,status from v$log; GROUP# SEQUENCE# MEMBERS STATUS

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

1 13 1 ACTIVE

2 14 1 CURRENT

3 12 1 ACTIVE SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> select group#,sequence#,members,status from v$log; GROUP# SEQUENCE# MEMBERS STATUS

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

1 16 1 CURRENT

2 14 1 ACTIVE

3 15 1 ACTIVE 上面的操作在sequence是13 的归档日志文件中 4、到数据库的归档目录下删除sequence 是13的归档日志文件 [oracle@haozg archivelog]$ ls -al

total 9904

drwxr-xr-x 2 oracle oinstall 4096 Jul 1 10:16 .

drwxrwxr-x 12 oracle oinstall 4096 Jun 18 17:04 ..

-rw-r----- 1 oracle oinstall 2560 Jul 1 10:03 1_10_787471195.dbf

-rw-r----- 1 oracle oinstall 20992 Jul 1 10:08 1_11_787471195.dbf

-rw-r----- 1 oracle oinstall 10061312 Jul 1 10:13 1_12_787471195.dbf

-rw-r----- 1 oracle oinstall 18944 Jul 1 10:16 1_13_787471195.dbf

-rw-r----- 1 oracle oinstall 2560 Jul 1 10:03 1_9_787471195.dbf

[oracle@haozg archivelog]$ mv 1_13_787471195.dbf ../

[oracle@haozg archivelog]$ ls

1_10_787471195.dbf 1_11_787471195.dbf 1_12_787471195.dbf 1_9_787471195.dbf

[oracle@haozg archivelog]$ ls -al

total 10168

drwxr-xr-x 2 oracle oinstall 4096 Jul 1 10:20 .

drwxrwxr-x 12 oracle oinstall 4096 Jul 1 10:19 ..

-rw-r----- 1 oracle oinstall 2560 Jul 1 10:03 1_10_787471195.dbf

-rw-r----- 1 oracle oinstall 20992 Jul 1 10:08 1_11_787471195.dbf

-rw-r----- 1 oracle oinstall 10061312 Jul 1 10:13 1_12_787471195.dbf

-rw-r----- 1 oracle oinstall 282624 Jul 1 10:20 1_14_787471195.dbf

-rw-r----- 1 oracle oinstall 3072 Jul 1 10:20 1_15_787471195.dbf

-rw-r----- 1 oracle oinstall 2560 Jul 1 10:03 1_9_787471195.dbf 5、关闭数据库，删除user数据文件，模拟数据文件损坏 oracle 用户下操作

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> grid 用户下操作

ASMCMD> ls

SYSAUX.258.785186845

SYSTEM.257.785186755

UNDOTBS1.259.785186901

USERS.272.787470817

ASMCMD> rm -f user*

ASMCMD> ls

SYSAUX.258.785186845

SYSTEM.257.785186755

UNDOTBS1.259.785186901 6、打开数据库，出现错误 SQL> startup monut;

SP2-0714: invalid combination of STARTUP options

SQL> startup mount;

ORACLE instance started. Total System Global Area 146472960 bytes

Fixed Size 1335080 bytes

Variable Size 92274904 bytes

Database Buffers 50331648 bytes

Redo Buffers 2531328 bytes

Database mounted.

SQL>

SQL> alter database open;

alter database open

ERROR at line 1:

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '+DGASM/ora11/datafile/users.272.787470817' 7、转储数据库 RMAN> restore database; Starting restore at 01-JUL-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 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 00001 to +DGASM/ora11/datafile/system.257.785186755

channel ORA_DISK_1: restoring datafile 00002 to +DGASM/ora11/datafile/sysaux.258.785186845

channel ORA_DISK_1: restoring datafile 00003 to +DGASM/ora11/datafile/undotbs1.259.785186901

channel ORA_DISK_1: restoring datafile 00004 to +DGASM/ora11/datafile/users.272.787470817

channel ORA_DISK_1: reading from backup piece +DGASM/backup/haozg/db_ora11_42_1_787486286

channel ORA_DISK_1: piece handle=+DGASM/backup/haozg/db_ora11_42_1_787486286 tag=TAG0701T101126

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: reading from backup piece +DGASM/backup/haozg/db_ora11_42_2_787486286

channel ORA_DISK_1: piece handle=+DGASM/backup/haozg/db_ora11_42_2_787486286 tag=TAG0701T101126

channel ORA_DISK_1: restored backup piece 2

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

Finished restore at 01-JUL-12 8、然后再去open，提示需要介质恢复 SQL> alter database open;

alter database open

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '+DGASM/ora11/datafile/system.257.785186755' 9、recover database RMAN> recover database; Starting recover at 01-JUL-12

using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 12 is already on disk as file /oracle/archivelog/1_12_787471195.dbf

archived log for thread 1 with sequence 14 is already on disk as file /oracle/archivelog/1_14_787471195.dbf

archived log for thread 1 with sequence 15 is already on disk as file /oracle/archivelog/1_15_787471195.dbf

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 07/01/ 10:32:14

RMAN-06053: unable to perform. media recovery because of missing log

RMAN-06025: no backup of archived log for thread 1 with sequence 13 and starting SCN of 970506 found to restore 提示缺少sequence 为13 的归档日志文件，然后用下面的命令恢复到sequence 为12 的状态。 RMAN> recover database until sequence 12; Starting recover at 01-JUL-12

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 07/01/ 10:36:06

RMAN-06556: datafile 1 must be restored from backup older than SCN 968324 仍然出现错误，又回到了老问题，切记：恢复到sequence 为13的情况下，实际上只应用归档到sequence 12，不包括sequence 为13

的归档日志，所以用下面的命令继续恢复： RMAN> recover database until sequence 13; Starting recover at 01-JUL-12

using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 12 is already on disk as file /oracle/archivelog/1_12_787471195.dbf

archived log file name=/oracle/archivelog/1_12_787471195.dbf thread=1 sequence=12

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

Finished recover at 01-JUL-12 RMAN> 完成了不完全恢复10、open 数据库 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; Database altered. SQL> select * from test11;

select * from test11

ERROR at line 1:

ORA-00942: table or view does not exist 由于做了不完全恢复，所以用resetlogs方式打开数据库

检查数据： SQL> connect haozg/haozg

Connected.