分类目录归档：Oracle

Oracle 19C 备库DML重定向—DML Redirection

发表于 2024 年 2 月 12 日 由 惜分飞

在19c之前，oracle Data Guard备用数据库上不能执行DML操作，但是，从19c开始备库就可以进行DML操作了；Active Data Guard备用数据库上运行DML操作，可以在备用数据库上运行只读应用程序，偶尔执行DML（太频繁影响主库性能），备库上的DML操作可以透明地重定向到主数据库并在主数据库上运行。也包括PL/SQL块中的DML语句。Active Data Guard会话将等待，直到将相应的更改发送到Active Data Guard备用数据库并将其应用于Active Data Guard备用数据库为止。在DML操作期间将保持读取一致性，并且运行DML的备用数据库可以查看其未提交的更改。但是，所有其他备用数据库实例只有在提交事务后才能查看这些更改。
在主库上创建测试表

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
	 0


SQL> select database_role ,open_mode from v$database;

DATABASE_ROLE	 OPEN_MODE
---------------- --------------------
PRIMARY 	 READ WRITE

SQL> create table system.t_xff as select * from dba_objects;

Table created.

SQL> select count(*) from system.t_xff;

  COUNT(*)
----------
     72407

在备库上进行dml操作

SQL> select database_role ,open_mode from v$database;

DATABASE_ROLE	 OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY



SQL> select count(*) from system.t_xff;

  COUNT(*)
----------
     72407

SQL> alter session enable adg_redirect_dml;

Session altered.

SQL> delete from system.t_xff;

72407 rows deleted.

SQL> commit;

Commit complete.

在主库上验证备库dml操作结果

SQL> select count(*) from system.t_xff;

  COUNT(*)
----------
	 0

SQL>

在18c中可以通过_enable_proxy_adg_redirect隐含参数实现dml重定向

发表在 Data Guard | 标签为 , , | 评论关闭

ORA-01595/ORA-600 4194处理

发表于 2024 年 2 月 5 日 由 惜分飞

又一个客户由于服务器断电，导致oracle无法正常启动报ORA-600 4194错误

Sat Jan 20 13:45:22 2024
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 32 processes
Started redo scan
Completed redo scan
 read 31 KB redo, 5 data blocks need recovery
Started redo application at
 Thread 1: logseq 9, block 3
Recovery of Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0
  Mem# 0: D:\ORACLE\ORADATA\xff\REDO03.LOG
Completed redo application of 0.02MB
Completed crash recovery at
 Thread 1: logseq 9, block 66, scn 81924188
 5 data blocks read, 5 data blocks written, 31 redo k-bytes read
Thread 1 advanced to log sequence 10 (thread open)
Thread 1 opened at log sequence 10
  Current log# 1 seq# 10 mem# 0: D:\ORACLE\ORADATA\xff\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
Errors in file d:\oracle\diag\rdbms\xff\xff\trace\xff_smon_5164.trc  (incident=384222):
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\oracle\diag\rdbms\xff\xff\incident\incdir_384222\xff_smon_5164_i384222.trc
Errors in file d:\oracle\diag\rdbms\xff\xff\trace\xff_ora_6832.trc  (incident=384270):
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\oracle\diag\rdbms\xff\xff\incident\incdir_384270\xff_ora_6832_i384270.trc
Doing block recovery for file 3 block 207
Resuming block recovery (PMON) for file 3 block 207
Block recovery from logseq 10, block 64 to scn 81924393
Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
  Mem# 0: D:\ORACLE\ORADATA\xff\REDO01.LOG
Block recovery stopped at EOT rba 10.66.16
Block recovery completed at rba 10.66.16, scn 0.81924391
Doing block recovery for file 3 block 160
Resuming block recovery (PMON) for file 3 block 160
Block recovery from logseq 10, block 64 to scn 81924389
Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
  Mem# 0: D:\ORACLE\ORADATA\xff\REDO01.LOG
Block recovery completed at rba 10.64.16, scn 0.81924390
Errors in file d:\oracle\diag\rdbms\xff\xff\trace\xff_smon_5164.trc:
ORA-01595: 释放区 (2) 回退段 (3) 时出错
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
Trace dumping is performing id=[cdmp_20240120134525]
Doing block recovery for file 3 block 3857
Resuming block recovery (PMON) for file 3 block 3857
Block recovery from logseq 10, block 64 to scn 81924396
Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
  Mem# 0: D:\ORACLE\ORADATA\xff\REDO01.LOG
Block recovery stopped at EOT rba 10.66.16
Block recovery completed at rba 10.66.16, scn 0.81924391
Doing block recovery for file 3 block 272
Resuming block recovery (PMON) for file 3 block 272
Block recovery from logseq 10, block 64 to scn 81924390
Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
  Mem# 0: D:\ORACLE\ORADATA\xff\REDO01.LOG
Block recovery completed at rba 10.66.16, scn 0.81924391
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sat Jan 20 13:45:26 2024
QMNC started with pid=56, OS id=8052 
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x66F8B134] [PC:0x93C7D88, kgegpa()+38]
Dump file d:\oracle\diag\rdbms\xff\xff\trace\alert_xff.log
Doing block recovery for file 3 block 3857
Resuming block recovery (PMON) for file 3 block 3857
Block recovery from logseq 10, block 64 to scn 81924396
Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
  Mem# 0: D:\ORACLE\ORADATA\xff\REDO01.LOG
Block recovery completed at rba 10.66.16, scn 0.81924399
Errors in file d:\oracle\diag\rdbms\xff\xff\trace\xff_mmon_7876.trc  (incident=396089):
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: 在 "SYS.DBMS_HA_ALERTS_PRVT", line 310
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], 
Doing block recovery for file 3 block 3857
Resuming block recovery (PMON) for file 3 block 3857
Block recovery from logseq 10, block 64 to scn 81924396
Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
  Mem# 0: D:\ORACLE\ORADATA\xff\REDO01.LOG
Block recovery completed at rba 10.66.16, scn 0.81924399
Errors in file d:\oracle\diag\rdbms\xff\xff\trace\xff_pmon_8876.trc  (incident=384102):
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
Sat Jan 20 13:45:39 2024
Errors in file d:\oracle\diag\rdbms\xff\xff\trace\xff_pmon_8876.trc:
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
PMON (ospid: 8876): terminating the instance due to error 472
Instance terminated by PMON, pid = 8876

这个故障比较简单,由于undo回滚异常导致,对异常的undo进行处理,数据库正常open,重建undo表空间导出数据,完成本次恢复工作

发表在 Oracle备份恢复 | 标签为 , | 评论关闭

从ORA-00283 ORA-16433报错开始恢复

发表于 2024 年 2 月 3 日 由 惜分飞

接手一个客户无法正常启动的故障数据库，尝试recover 报ORA-00283 ORA-16433错误

[oracle@xff trace]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 27 04:46:23 2024

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


???:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs;
SQL> select open_mode from v$database;

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

SQL>
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode

通过对控制文件进行处理,再次尝试recover库

SQL> recover database;
ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 134877 change 3249721295 time 01/27/2024 00:21:05
ORA-00312: online log 1 thread 1:'/u01/app/oracle/oradata/xff/redo01.log'

由于redo和数据文件不匹配,无法正常recover库,尝试强制打开库报ORA-600 2662错误

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [3249721308], [0],[3249730440], [16777344],[],[],[],[],[],[]
ORA-00600: internal error code, arguments: [2662], [0], [3249721307], [0],[3249730440], [16777344],[],[],[],[],[],[]
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [3249721303], [0],[3249730440], [16777344],[],[],[],[],[],[]
Process ID: 117336
Session ID: 1146 Serial number: 11764

基于这种错误,尝试oradebug修改scn

SQL> oradebug setmypid
oradebug DUMPvar SGA kcsgscn_
Statement processed.
SQL> kcslf kcsgscn_ [06001FBB0, 06001FBE0) = 00000000 00000000 00000000 00000000 00000000 
SQL> oradebug poke 0x06001FBB0 4 0x10000000
oradebug DUMPvar SGA kcsgscn_
ORA-32521: error parsing ORADEBUG command:

发现报ORA-32521错误,证明常规的oradebug方法无法修改scn,参考相关文章:
oradebug poke ORA-32521/ORA-32519故障解决
第一次通过其他方法处理,由于计算失误导致数据库启动报ORA-600 2252错误

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2252], [45264], [0], [11641],[3340959744], [],[],[],[],[],[]

该错误是相关文章参考:
记录一次ORA-00600[2252]故障解决
ORA-00600: internal error code, arguments: [2252], [3987]
主机断电系统回到N年前数据库报ORA-600 kcm_headroom_warn_1错误
处理正确的scn值之后,数据库open成功,然后逻辑方式导出数据,恢复工作完成

SQL> alter database open ;

Database altered.
发表在 Oracle备份恢复 | 标签为 , , , , | 评论关闭