ORA-00471: DBWR process terminated with error : Oracle

kamres · █ 09.10.2018 19:31

При попытке обновить данные в таблице через dbForge Studio остановилась база, теперь при запуске выдает:

Код:

ORA-00471: DBWR process terminated with error

База в архивлоге. Как его победить?
в алерте

Код:

Starting up ORACLE RDBMS Version: 10.2.0.4.0.
System parameters with non-default values:
  processes                = 400
  sessions                 = 445
  timed_statistics         = TRUE
  sga_max_size             = 5368709120
  __shared_pool_size       = 1073741824
  shared_pool_size         = 1073741824
  __large_pool_size        = 1073741824
  large_pool_size          = 1073741824
  __java_pool_size         = 1073741824
  java_pool_size           = 1073741824
  trace_enabled            = FALSE
  control_files            = D:\ORACLE\ORADATA\KAMAVTO\CONTROL01.CTL, D:\ORACLE\ORADATA\KAMAVTO\CONTROL02.CTL, D:\ORACLE\ORADATA\KAMAVTO\CONTROL03.CTL
  db_block_size            = 8192
  __db_cache_size          = 1073741824
  db_cache_size            = 1073741824
  db_recycle_cache_size    = 67108864
  db_cache_advice          = OFF
  compatible               = 10.2.0.3.0
  log_archive_start        = TRUE
  db_recovery_file_dest    = E:\backup_log
  db_recovery_file_dest_size= 966367641600
  fast_start_mttr_target   = 600
  transactions_per_rollback_segment= 1
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  undo_retention           = 10800
  recyclebin               = off
  O7_DICTIONARY_ACCESSIBILITY= TRUE
  remote_login_passwordfile= EXCLUSIVE
  audit_sys_operations     = FALSE
  db_domain                = 
  instance_name            = KAMAVTO
  job_queue_processes      = 10
  background_dump_dest     = D:\ORACLE\ADMIN\KAMAVTO\BDUMP
  user_dump_dest           = D:\ORACLE\ADMIN\KAMAVTO\UDUMP
  max_dump_file_size       = 5000000
  core_dump_dest           = D:\ORACLE\ADMIN\KAMAVTO\CDUMP
  commit_write             = batch,nowait
  optimizer_features_enable= 10.2.0.4
  audit_trail              = XML
  sort_area_size           = 4194304
  db_name                  = KAMAVTO
  open_cursors             = 300
  star_transformation_enabled= FALSE
  query_rewrite_enabled    = FALSE
  query_rewrite_integrity  = stale_tolerated
  pga_aggregate_target     = 1048576000
  optimizer_dynamic_sampling= 2
Deprecated system parameters with specified values:
  log_archive_start        
End of deprecated system parameter listing
PMON started with pid=2, OS id=5508
PSP0 started with pid=4, OS id=3368
MMAN started with pid=6, OS id=5520
DBW0 started with pid=8, OS id=4952
DBW1 started with pid=10, OS id=3176
LGWR started with pid=12, OS id=3604
CKPT started with pid=14, OS id=5676
SMON started with pid=16, OS id=5688
RECO started with pid=18, OS id=5416
CJQ0 started with pid=20, OS id=2616
MMON started with pid=22, OS id=3732
MMNL started with pid=24, OS id=6112
Tue Oct 09 18:48:02 2018
alter database mount exclusive
Tue Oct 09 18:48:06 2018
Setting recovery target incarnation to 1
Tue Oct 09 18:48:06 2018
Successful mount of redo thread 1, with mount id 377811490
Tue Oct 09 18:48:06 2018
Database mounted in Exclusive Mode
Completed: alter database mount exclusive
Tue Oct 09 18:48:06 2018
alter database open
Tue Oct 09 18:48:06 2018
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Tue Oct 09 18:48:06 2018
Started redo scan
Tue Oct 09 18:48:07 2018
Completed redo scan
 54948 redo blocks read, 4095 data blocks need recovery
Tue Oct 09 18:48:08 2018
Started redo application at
 Thread 1: logseq 324781, block 92874
Tue Oct 09 18:48:08 2018
Recovery of Online Redo Log: Thread 1 Group 1 Seq 324781 Reading mem 0
  Mem# 0: D:\ORACLE\ORADATA\KAMAVTO\REDO01.LOG
Tue Oct 09 18:48:08 2018
Recovery of Online Redo Log: Thread 1 Group 2 Seq 324782 Reading mem 0
  Mem# 0: D:\ORACLE\ORADATA\KAMAVTO\REDO02.LOG
Tue Oct 09 18:48:08 2018
Completed redo application
Tue Oct 09 18:48:08 2018
Hex dump of (file 9, block 59382) in trace file d:\oracle\admin\kamavto\bdump\kamavto_dbw0_4952.trc
Corrupt block relative dba: 0x0240e7f6 (file 9, block 59382)
Bad header found during preparing block for write
Data in bad block:
 type: 6 format: 2 rdba: 0x0240e7f6
 last change scn: 0x0001.0eb628d8 seq: 0x2 flg: 0x28
 spare1: 0x0 spare2: 0x0 spare3: 0x540e
 consistency value in tail: 0x28d80602
 check value in block header: 0x0
 block checksum disabled
Tue Oct 09 18:48:09 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_dbw0_4952.trc:
ORA-00600: internal error code, arguments: [kcbzpbuf_1], [4], [1], [], [], [], [], []

Tue Oct 09 18:48:09 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_dbw0_4952.trc:
ORA-00600: internal error code, arguments: [kcbzpbuf_1], [4], [1], [], [], [], [], []

DBW0: terminating instance due to error 471
Tue Oct 09 18:48:09 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_p014_4676.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:09 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_p013_980.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:09 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_p012_584.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:09 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_p011_5700.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:10 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_p010_6044.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:10 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_p009_4592.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:10 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_p008_2152.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:10 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_p007_4488.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:10 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_p002_3936.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:10 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_p000_5588.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:10 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_p001_3408.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:11 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_pmon_5508.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:11 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_p004_4588.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:11 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_psp0_3368.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:11 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_mman_5520.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:11 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_ckpt_5676.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:11 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_dbw1_3176.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:11 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_lgwr_3604.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:12 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_p003_5956.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:13 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_p005_5872.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:14 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_p006_5272.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:14 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_reco_5416.trc:
ORA-00471: DBWR process terminated with error

Tue Oct 09 18:48:14 2018
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_smon_5688.trc:
ORA-00471: DBWR process terminated with error

сейчас статус

Код:

INSTANCE_NAME    STATUS
---------------- ------------
kamavto          MOUNTED

при попытке startup open;

Код:

SQL> startup open;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 5368709120 bytes
Fixed Size                  2171616 bytes
Variable Size            4221361440 bytes
Database Buffers         1140850688 bytes
Redo Buffers                4325376 bytes
Database mounted.
ORA-03113: end-of-file on communication channel

судя по всему не смог записать

Код:

Hex dump of (file 9, block 59382) in trace file d:\oracle\admin\kamavto\bdump\kamavto_dbw1_4188.trc
Corrupt block relative dba: 0x0240e7f6 (file 9, block 59382)
Bad header found during preparing block for write
Data in bad block:
 type: 6 format: 2 rdba: 0x0240e7f6
 last change scn: 0x0001.0eb628d8 seq: 0x2 flg: 0x28
 spare1: 0x0 spare2: 0x0 spare3: 0x540e
 consistency value in tail: 0x28d80602
 check value in block header: 0x0
 block checksum disabled

OlegON · █ 09.10.2018 20:36

Оптимизатор используешь? Почему не смог записать-то?

kamres · █ 09.10.2018 20:43

Оптимизатор не использую, не записал похоже что dbForge Studio очень много пытался записать.

OlegON · █ 09.10.2018 22:43

нет, это, скорее, с дисками голяк, либо винда или антивирусы... ищи сначала, что с винтами...

kamres · █ 10.10.2018 08:22

Видимых проблем с дисками нет, в журналах винды ошибок нет, антивируса нет, восстановил так:

https://olegon.ru/showthread.php?t=14917

Цитата:

Stels ➤ восстановил всё по тем же путям
а дальше:

Код:

startup mount;
alter database recover automatic database using backup controlfile until cancel;
alter database recover cancel;
alter database open resetlogs;

kamres · █ 15.10.2020 21:07

Цитата:

kamres ➤ восстановил так:
https://olegon.ru/showthread.php?t=14917
Цитата:
Stels ➤ восстановил всё по тем же путям
а дальше:
Код:
startup mount;
alter database recover automatic database using backup controlfile until cancel;
alter database recover cancel;
alter database open resetlogs;

ORACLE V10.2.0.4.0 - 64bit Production

Приветствую всех, что то в этот раз не получается восстановить при накатывании архивлогов, подскажите куда копать?
Как всегда отключили электричество и после включения база не работает, сохранил то что есть, база в режиме архивлог:

SQL код:

 Errors in file d:\oracle\admin\kamavto\bdump\kamavto_smon_3416.trc:
ORA-00600: internal error code, arguments: [4194], [65], [28], [], [], [], [], []
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_j000_3936.trc:
ORA-00600: internal error code, arguments: [4193], [54658], [54691], [], [], [], [], []

Восстановил холодную копию, работает, пробую накатить архивлоги, но получаю:

SQL код:

 SQL> alter database recover automatic database using backup controlfile until cancel;
alter database recover automatic database using backup controlfile until cancel
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 1742330 change 179108728603653 time
10/15/2020 19:10:35
ORA-00334: archived log:
'E:\BACKUP_LOG\KAMAVTO\ARCHIVELOG\2020_10_15\O1_MF_1_213702_HRHGPTQX_.ARC'

SQL> select * from v$log;

SQL код:

     GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ----------------------------- ---------
         1          1     213700 1073741824          1 YES INACTIVE    7027757780 15-OCT-20

         2          1     213701 1073741824          1 YES INACTIVE    7027766052 15-OCT-20

         6          1     213698 1073741824          1 YES INACTIVE    7027505423 14-OCT-20


    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ----------------

         4          1     213702 1073741824          1 NO  CURRENT    7027888453 15-OCT-20

         5          1     213697 1073741824          1 YES INACTIVE    7027199245 14-OCT-20

         3          1     213699 1073741824          1 YES INACTIVE    7027749260 15-OCT-20


6 rows selected.

На этом 213702 номере архивлога и встает, у него и размер отличается от остальных:

(0,01Мб)

Как то его пройти можно? Чтобы восстановить до 213706?

OlegON · █ 15.10.2020 21:21

никак не пройдешь, иначе хз что бы получилось... видимо, в момент записи архивлога питание и дернули... то, что есть вообще не работает и никак не заводится? если нет - восстанавливайся до 213701.
https://olegon.ru/showthread.php?t=27419

OlegON · █ 15.10.2020 21:28

Скорее всего ой, если не сможешь завести по статейкам с битыми редо, как я тут уже писал

Цитата:

PURPOSE:
This article discusses the internal error "ORA-600 [4193]", what
it means and possible actions. The information here is only applicable
to the versions listed and is provided only for guidance.

ERROR:

Format: ORA-600 [4193] [a] [b]
VERSIONS:
versions 6.0 to 12.1

DESCRIPTION:

A mismatch has been detected between Redo records and Rollback (Undo)
records.

We are validating the Undo block sequence number in the undo block against
the Redo block sequence number relating to the change being applied.

This error is reported when this validation fails.

ARGUMENTS:
Arg [a] Undo record seq number
Arg [b] Redo record seq number

FUNCTIONALITY:
KERNEL TRANSACTION UNDO

IMPACT:
PROCESS FAILURE
POSSIBLE ROLLBACK SEGMENT CORRUPTION

SUGGESTIONS:

This error may indicate a rollback segment corruption.

This may require a recovery from a database backup depending on
the situation.

Цитата:

PURPOSE:
This article discusses the internal error "ORA-600 [4194]", what
it means and possible actions. The information here is only applicable
to the versions listed and is provided only for guidance.

ERROR:

Format: ORA-600 [4194] [a] [b]

VERSIONS:
versions 6.0 to 12.1

DESCRIPTION:

A mismatch has been detected between Redo records and rollback (Undo)
records.

We are validating the Undo record number relating to the change being
applied against the maximum undo record number recorded in the undo block.

This error is reported when the validation fails.

ARGUMENTS:
Arg [a] Maximum Undo record number in Undo block
Arg [b] Undo record number from Redo block

FUNCTIONALITY:
Kernel Transaction Undo called from Cache layer

IMPACT:
PROCESS FAILURE
POSSIBLE ROLLBACK SEGMENT CORRUPTION

SUGGESTIONS:

This error may indicate a rollback segment corruption.

This may require a recovery from a database backup depending on
the situation.

kamres · █ 15.10.2020 21:33

питание скакануло где то в 11-50, на 05-55 стоит перезагрузка сервера в это время и создался 213702:

SQL код:

 Deprecated system parameters with specified values:
  log_archive_start        
End of deprecated system parameter listing
MMAN started with pid=6, OS id=3320
DBW0 started with pid=8, OS id=3324
DBW1 started with pid=10, OS id=3328
LGWR started with pid=12, OS id=3332
CKPT started with pid=14, OS id=3336
SMON started with pid=16, OS id=3340
RECO started with pid=18, OS id=3344
CJQ0 started with pid=20, OS id=3348
MMON started with pid=22, OS id=3352
MMNL started with pid=24, OS id=3356
Thu Oct 15 05:58:28 2020
alter database mount exclusive
PMON started with pid=2, OS id=3312
PSP0 started with pid=4, OS id=3316
Thu Oct 15 05:58:32 2020
Setting recovery target incarnation to 2
Thu Oct 15 05:58:32 2020
Successful mount of redo thread 1, with mount id 442543044
Thu Oct 15 05:58:32 2020
Database mounted in Exclusive Mode
Completed: alter database mount exclusive
Thu Oct 15 05:58:32 2020
alter database open
Thu Oct 15 05:58:32 2020
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Thu Oct 15 05:58:32 2020
Started redo scan
Thu Oct 15 05:58:33 2020
Completed redo scan
 10052 redo blocks read, 580 data blocks need recovery
Thu Oct 15 05:58:33 2020
Started redo application at
 Thread 1: logseq 213702, block 2042789
Thu Oct 15 05:58:33 2020
Recovery of Online Redo Log: Thread 1 Group 4 Seq 213702 Reading mem 0
  Mem# 0: D:\ORACLE\ORADATA\KAMAVTO\REDO01.LOG4+.DBF
Thu Oct 15 05:58:34 2020
Completed redo application
Thu Oct 15 05:58:34 2020
Completed crash recovery at
 Thread 1: logseq 213702, block 2052841, scn 7028057328
 580 data blocks read, 580 data blocks written, 10052 redo blocks read
Thu Oct 15 05:58:34 2020
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=72, OS id=3680
ARC1 started with pid=74, OS id=3684
Thu Oct 15 05:58:34 2020
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Thu Oct 15 05:58:34 2020
Thread 1 advanced to log sequence 213703 (thread open)
Thread 1 opened at log sequence 213703
  Current log# 5 seq# 213703 mem# 0: D:\ORACLE\ORADATA\KAMAVTO\REDO02.LOG5+.DBF
Successful open of redo thread 1
Thu Oct 15 05:58:34 2020
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Thu Oct 15 05:58:34 2020
ARC0: Becoming the heartbeat ARCH
Thu Oct 15 05:58:34 2020
SMON: enabling cache recovery
Thu Oct 15 05:58:34 2020
Errors in file d:\oracle\admin\kamavto\bdump\kamavto_arc1_3684.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 620688670720 bytes is 97.02% used, and has 18526969856 remaining bytes available.

Thu Oct 15 05:58:34 2020
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
Thu Oct 15 05:58:36 2020
Successfully onlined Undo Tablespace 1.
Thu Oct 15 05:58:36 2020
SMON: enabling tx recovery
Thu Oct 15 05:58:36 2020
Database Characterset is CL8MSWIN1251
Opening with internal Resource Manager plan
where NUMA PG = 2, CPUs = 8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=76, OS id=3704
Thu Oct 15 05:58:41 2020
Completed: alter database open
Thu Oct 15 05:59:29 2020
WARNING: inbound connection timed out (ORA-3136)
Thu Oct 15 07:19:19 2020
Thread 1 advanced to log sequence 213704 (LGWR switch)
  Current log# 6 seq# 213704 mem# 0: D:\ORACLE\ORADATA\KAMAVTO\REDO03.LOG6+.DBF
Thu Oct 15 09:00:17 2020
Thread 1 advanced to log sequence 213705 (LGWR switch)
  Current log# 3 seq# 213705 mem# 0: D:\ORACLE\ORADATA\KAMAVTO\REDO03.LOG
Thu Oct 15 10:30:22 2020
Thread 1 advanced to log sequence 213706 (LGWR switch)
  Current log# 1 seq# 213706 mem# 0: D:\ORACLE\ORADATA\KAMAVTO\REDO01.LOG
Thu Oct 15 11:17:36 2020
Thread 1 advanced to log sequence 213707 (LGWR switch)
  Current log# 2 seq# 213707 mem# 0: D:\ORACLE\ORADATA\KAMAVTO\REDO02.LOG
Dump file d:\oracle\admin\kamavto\bdump\alert_kamavto.log
Thu Oct 15 11:55:05 2020

OlegON · █ 15.10.2020 21:38

Возможно, что что-то не записалось, потому, что ты лимит объема FRA неудачно поставил, может, WaitToKillServiceTimeout при ребуте еще сверху наложился... Ты остановку-то базы не показалю. Я не знаю, как факт, только часть блоков из истории выкинуть не получится. Это приведет к рассогласованию данных в базе. Поставь оптимизатор, рекомендую же.. Что так страдать-то... Он бы тебе FRA нормальным сделал... И UPS поставь.
А сейчас восстанавливай до битого журнала.