currentonlineredologfile丢失的处理方法-mysql教程

昨天做了rm -rf操作后的恢复演练，而且是在没有任何备份的情况下。今天在做破坏性操作前，做了个rman全备，然后在线删除全部数据库文件，包括控制文件，数据文件，在线日志文件，归档文件等。来看看有什么方法可以让数据库恢复运行，由于是current redo logf

昨天做了rm -rf操作后的恢复演练，而且是在没有任何备份的情况下。今天在做破坏性操作前，做了个rman全备，然后在线删除全部数据库文件，包括控制文件，数据文件，在线日志文件，归档文件等。来看看有什么方法可以让数据库恢复运行，由于是current redo logfile丢失，那么本次测试是会丢失数据的，所以可以作为不完全恢复的一个案例。

--登入数据库进行dml操作

[oracle@ora10g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 26 13:40:37 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from aaron8219.test1;

INT
----------
1
2

SQL> insert into aaron8219.test1 values(3);

1 row created.

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

GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 INACTIVE 1
2 CURRENT 2
3 INACTIVE 0

--不提交，另开一个session进行rm -rf操作
[root@ora10g ~]# cd /u01/app/oracle/oradata
[root@ora10g oradata]# ll
total 4
drwxr-x--- 2 oracle oinstall 4096 Aug 25 16:09 ora10g
[root@ora10g oradata]# rm -rf ora10g/
[root@ora10g oradata]# ll
total 0

--原session直接abort关闭，模拟current online redo logfile丢失
SQL> shutdown abort

--用之前刚生成的rman全备来还原数据库各类文件
[oracle@ora10g ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 26 13:44:58 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ora10g (not mounted)

RMAN> restore controlfile from &＃39;/rmanbak/full_ORA10G_4175411955_20140826_02pgtq5h_1_1.bak&＃39;;

Starting restore at 26-AUG-14
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/u01/app/oracle/oradata/ora10g/control01.ctl
output filename=/u01/app/oracle/oradata/ora10g/control02.ctl
output filename=/u01/app/oracle/oradata/ora10g/control03.ctl
Finished restore at 26-AUG-14

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 26-AUG-14
Starting implicit crosscheck backup at 26-AUG-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 26-AUG-14

Starting implicit crosscheck copy at 26-AUG-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 26-AUG-14

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/ORA10G/autobackup/2014_08_26/o1_mf_s_856615092_9zr3snsq_.bkp

using channel ORA_DISK_1


channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/ora10g/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/ora10g/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/ora10g/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/ora10g/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/ora10g/example01.dbf
channel ORA_DISK_1: reading from backup piece /rmanbak/full_ORA10G_4175411955_20140826_01pgtq36_1_1.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/rmanbak/full_ORA10G_4175411955_20140826_01pgtq36_1_1.bak tag=TAG20140826T123653
channel ORA_DISK_1: restore complete, elapsed time: 00:01:46
Finished restore at 26-AUG-14

RMAN> exit

Recovery Manager complete.

由于rman并不备份在线日志文件，刚才的rman只是做了个全库备份和控制文件的备份，可以看到redo01.log，redo02.log，redo03.log这3个文件并不存在

[oracle@ora10g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 26 14:52:35 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> col member for a60
SQL> select group#,member from v$logfile;


GROUP# MEMBER
---------- ------------------------------------------------------------
3 /u01/app/oracle/oradata/ora10g/redo03.log
2 /u01/app/oracle/oradata/ora10g/redo02.log
1 /u01/app/oracle/oradata/ora10g/redo01.log

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: &＃39;/u01/app/oracle/oradata/ora10g/system01.dbf&＃39;

由于只是restore了数据库，还未recover，数据库是无法打开的

SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile;
ORA-00279: change 502729 generated at 08/26/2014 15:36:54 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_08_26/o1_mf_1_3_%u_.arc
ORA-00280: change 502729 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
&＃39;/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_08_26/o1_mf_1_3_%u_.arc&＃39;
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log
&＃39;/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_08_26/o1_mf_1_3_%u_.arc&＃39;
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 502729 generated at 08/26/2014 15:36:54 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_08_26/o1_mf_1_3_%u_.arc
ORA-00280: change 502729 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL>

由于之前的归档没有用rman备份，而现在也不存在了，无法recover数据库，就算用resetlogs也无法open数据库。此时，只有通过隐含参数_allow_resetlogs_corruption来open数据库了

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfileora10g.ora

--创建pfile
SQL> create pfile from spfile;

File created.

--编辑$ORACLE_HOME/dbs/initora10g.ora初始化参数文件，在文件末尾添加*._allow_resetlogs_corruption = true
[oracle@ora10g ora10g]$ vi $ORACLE_HOME/dbs/initora10g.ora


ora10g.__db_cache_size=180355072
ora10g.__java_pool_size=4194304
ora10g.__large_pool_size=4194304
ora10g.__shared_pool_size=92274688
ora10g.__streams_pool_size=0
*.audit_file_dest=&＃39;/u01/app/oracle/admin/ora10g/adump&＃39;
*.background_dump_dest=&＃39;/u01/app/oracle/admin/ora10g/bdump&＃39;
*.compatible=&＃39;10.2.0.1.0&＃39;
*.control_files=&＃39;/u01/app/oracle/oradata/ora10g/control01.ctl&＃39;,&＃39;/u01/app/oracle/oradata/ora10g/control02.ctl&＃39;,&＃39;/u01/app/oracle/oradata/ora10g/control03.ctl&＃39;#Restore Controlfile
*.core_dump_dest=&＃39;/u01/app/oracle/admin/ora10g/cdump&＃39;
*.db_block_size=8192
*.db_domain=&＃39;&＃39;
*.db_file_multiblock_read_count=16
*.db_name=&＃39;ora10g&＃39;
*.db_recovery_file_dest=&＃39;/u01/app/oracle/flash_recovery_area&＃39;
*.db_recovery_file_dest_size=2147483648
*.dispatchers=&＃39;(PROTOCOL=TCP) (SERVICE=ora10gXDB)&＃39;
*.job_queue_processes=10
*.log_archive_format=&＃39;%t_%s_%r.dbf&＃39;
*.nls_language=&＃39;SIMPLIFIED CHINESE&＃39;
*.nls_territory=&＃39;CHINA&＃39;
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile=&＃39;EXCLUSIVE&＃39;
*.sga_target=285212672
*.undo_management=&＃39;AUTO&＃39;
*.undo_tablespace=&＃39;UNDOTBS1&＃39;
*.user_dump_dest=&＃39;/u01/app/oracle/admin/ora10g/udump&＃39;
*._allow_resetlogs_corruption=TRUE


--关闭数据库实例，用pfile启动
SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup pfile=$ORACLE_HOME/dbs/initora10g.ora
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 100664912 bytes
Database Buffers 180355072 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

--最后用resetlogs打开数据库
SQL> alter database open resetlogs;

Database altered.

SQL>

[oracle@ora10g ora10g]$ ll
total 1051020
-rw-r----- 1 oracle oinstall 7061504 Aug 26 16:03 control01.ctl
-rw-r----- 1 oracle oinstall 7061504 Aug 26 16:03 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 Aug 26 16:03 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Aug 26 16:03 example01.dbf
-rw-r----- 1 oracle oinstall 52429312 Aug 26 16:03 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Aug 26 16:03 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Aug 26 16:03 redo03.log
-rw-r----- 1 oracle oinstall 251666432 Aug 26 16:03 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Aug 26 16:03 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 Aug 26 16:03 temp01.dbf
-rw-r----- 1 oracle oinstall 31465472 Aug 26 16:03 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Aug 26 16:03 users01.dbf
[oracle@ora10g ora10g]$

再次查看数据文件，发现重新生成了3个online redo logfile了

--resetlogs把SEQUENCE#序列号重新变成1
SQL> select group#,sequence# from v$log;

GROUP# SEQUENCE#
---------- ----------
1 0
2 1
3 0

--测试表只留下了原来的数据库，因为没有commit，新插入的第3条记录丢失，就算commit，也是会丢数据的，因为redo logfile也被删除了

SQL> select * from aaron8219.test1;
INT
----------
1
2
注意，最后要把刚才设置的隐含参数_allow_resetlogs_corruption = true去掉，并重新创建spfile，否则数据库将来会有引起不一致的风险，设置该隐含参数只是下下策，为了能让数据库open而不得已为之，丢失数据库是肯定的了，因为这是在极端情况下得测试，通常只有在丢失了状态为current的online redo logfile才会丢失数据，如果只是inactive的redo logfile，就算使用alter database open resetlogs;也不意味着100%丢数据。另外，要注意的是，当使用using backup controlfile关键词时，必须要配合使用open resetlogs来打开数据库。以resetlogs方式open数据库后，必须重新做一次数据库全备，因为一旦incarnation改变之后，数据库原来的备份集就失效了。