时间:2021-07-01 10:21:17 帮助过:24人阅读
Oracle日志文件和控制文件损坏的恢复: 恢复步骤: 1.加入_allow_resetlogs_corruption=true,_corrupted_rollback_segments=tru
Oracle日志文件和控制文件损坏的恢复:
恢复步骤:
1.加入_allow_resetlogs_corruption=true,_corrupted_rollback_segments=true,_offline_rollback_segments=true,启动到nomount
2.利用控制文件的备份,,进行脚本重建,由于current状态redo损坏,所以要resetlogs重建
3.重建后通过alter database open resetlogs;打开数据库,这种情况会丢失在redo中相关事务的数据
备份控制文件并模拟故障:
SQL> set linesize 150
SQL> alter database backup controlfile to trace as '/home/oracle/ctl.ctl' reuse;
Database altered.
SQL> insert into tb1 values (1);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 1 536870912 1 YES INACTIVE 1.2269E+11 2013-05-03 16:08:03
2 1 2 536870912 1 YES INACTIVE 1.2269E+11 2013-05-03 16:23:48
3 1 3 536870912 1 NO CURRENT 1.2269E+11 2013-05-11 07:26:45
4 1 0 536870912 1 YES UNUSED 0
5 1 0 536870912 1 YES UNUSED 0
SQL> shutdown abort;
oracle@readerlogdb-> rm -f redo03.log
oracle@readerlogdb-> rm -f control01.ctl control02.ctl control03.ctl
通过加入相关参数启动到nomount状态:
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 5049942016 bytes
Fixed Size 2090880 bytes
Variable Size 1375733888 bytes
Database Buffers 3657433088 bytes
Redo Buffers 14684160 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> create pfile='/home/oracle/pfile.ora' from spfile;
在备份的pfile中加入以下三行:
*._allow_resetlogs_corruption=true
*._corrupted_rollback_segments=true
*._offline_rollback_segments=true
NORESETLOGS和RESETLOGS在此实验中的区别:
SQL>CREATE CONTROLFILE REUSE DATABASE "SKYREAD" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 20
MAXLOGMEMBERS 5
MAXDATAFILES 1000
MAXINSTANCES 8
MAXLOGHISTORY 2337
LOGFILE
GROUP 1 '/database/oradata/skyread/redo01.log' SIZE 512M,
GROUP 2 '/database/oradata/skyread/redo02.log' SIZE 512M,
GROUP 3 '/database/oradata/skyread/redo03.log' SIZE 512M,
GROUP 4 '/database/oradata/skyread/redo04.log' SIZE 512M,
GROUP 5 '/database/oradata/skyread/redo05.log' SIZE 512M
DATAFILE
'/database/oradata/skyread/system01.dbf',
'/database/oradata/skyread/tbs_test.dbf',
'/database/oradata/skyread/sysaux01.dbf',
'/database/oradata/skyread/users01.dbf',
'/database2/oradata/skyread/TBS_MRPMUSIC01.dbf',
'/database/oradata/skyread/sf01.dbf',
'/database2/oradata/skyread/undotbs02'
CHARACTER SET UTF8;