==================
General
==================
ora_fdba is the process for Oracle FDA
FDA - Flashback Data Archive
Flashback Data Archive was introduced in Oracle 11gR2 as a seamless way for providing a mechanism that tracks transactional changes to database tables.
This feature is often implemented to satisfy regulatory and compliance requirements for sensitive data such as financial, health, and data deemed to be sensitive requiring scrutiny at a later time.
Flashback Data Archive leverages existing technologies already in place within the Oracle RDBMS software.
The core piece that is leveraged is the rollback (aka undo) segments that are used to provide read consistency when queries are executed.
Undo segments hold transaction information and the “before image” of rows that have been modified. Instead of using a BEFORE change type trigger to collect the values of a row that is about to be modified, we can directly use the undo segments to capture that information.
Once the transaction is captured, it is then archived into the FBDA history tables along with metadata.
To see if the feature is enabled:
SHOW PARAMETER flashback
NAME VALUE
------------------------------ ------------------------------
db_flashback_retention_target 1440
_disable_flashback_archiver 0
==================
Issues
==================
Errors in igt_fbda_99999.trc
igt_fbda_29055.trc:FBDA: Error ORA-4030 in SQL "select count(FA#) from SYS_FBA_FA where bitand(FLAGS, 2) != 0"
igt_fbda_29055.trc:FBDA: Error ORA-4030 in SQL "select count(FA#) from SYS_FBA_FA where bitand(FLAGS, 2) != 0"
igt_fbda_29055.trc:FBDA: Error ORA-4030 in SQL "select count(FA#) from SYS_FBA_FA where bitand(FLAGS, 2) != 0"
igt_fbda_29055.trc:FBDA: Error ORA-4030 in SQL "select count(FA#) from SYS_FBA_FA where bitand(FLAGS, 2) != 0"
igt_fbda_29055.trc:FBDA: Error ORA-4030 in SQL "select count(FA#) from SYS_FBA_FA where bitand(FLAGS, 2) != 0"
igt_fbda_29055.trc:Error: ORA-4030 [ORA-04030: out of process memory when trying to allocate 63904 bytes (kxs-heap-f,frame segment)
igt_fbda_29055.trc: ORA-04030: out of process memory when trying to allocate 63904 bytes (kxs-heap-f,frame segment)
igt_fbda_29055.trc:ORA-04030: out of process memory when trying to allocate 63904 bytes (kxs-heap-f,frame segment)
Error in alert.log
2022-11-24T08:46:35.507803+00:00
DDE: Problem Key 'ORA 4030' was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
top
top - 09:54:57 up 254 days, 1:05, 4 users, load average: 2.21, 2.00, 1.81
Tasks: 880 total, 3 running, 877 sleeping, 0 stopped, 0 zombie
%Cpu(s): 10.0 us, 6.2 sy, 0.0 ni, 83.0 id, 0.5 wa, 0.0 hi, 0.3 si, 0.0 st
KiB Mem : 19791907+total, 11285169+free, 47707760 used, 37359616 buff/cache
KiB Swap: 10485756 total, 10485756 free, 0 used. 12048308+avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
18778 oracle 20 0 64.5g 285720 280120 R 90.8 0.1 0:16.22 ora_fbda_igt
18541 oracle 20 0 64.5g 52280 49364 S 27.5 0.0 0:03.82 ora_lg00_igt
18533 oracle 20 0 64.5g 138716 133848 S 17.4 0.1 0:02.59 ora_lgwr_igt
19481 oracle 20 0 64.5g 49644 44864 S 5.6 0.0 0:00.50 oracle_19481_ig
7900 root 20 0 0 0 0 S 4.9 0.0 1737:19 drbd_s_db1
18466 oracle -2 0 64.5g 19856 16916 S 2.6 0.0 0:00.90 ora_vktm_igt
19557 oracle 20 0 64.5g 51952 46952 S 2.6 0.0 0:00.21 oracle_19557_
ora_fbda has out of memory errors.
But FBA tables are empty.
SELECT * FROM DBA_FLASHBACK_ARCHIVE - Empty
SELECT * FROM DBA_FLASHBACK_ARCHIVE_TS - Empty
SELECT * FROM DBA_FLASHBACK_ARCHIVE_TABLES - Empty
SELECT * FROM DBA_FLASHBACK_ARCHIVE; - Empty
Per oracle technote: HIGH CPU USE BY THE FBDA PROCESS EVEN AFTER FULLY DISABLING THE FLASHBACK ARCHIVER (Doc ID 2559595.1)
There is a know issue where there is high CPU consumption by the FBDA process in the production instance due to the background process FBDA
The FBDA enabled tables were disabled but the FBDA bg process started and consumed high CPU attempting to archive nothing since no tables are enabled.
There are no SYS_FBA_TCRV_% or SYS_FBA_HIST_% tables which are the internal history tables for FBDA enabled tables. There is nothing showing enabled but the FBDA process continues to start and run. It's expected the process cannot be stopped when there are enabled tables.
SELECT segment_name, partition_name, bytes
FROM DBA_SEGMENTS
WHERE segment_name like 'SYS_FBA_HIST%'
ORDER BY segment_name;
no rows selected
SELECT segment_name,segment_type,bytes/1024/1024 MB
FROM DBA_SEGMENTS
WHERE segment_type='TABLE' and segment_name LIKE 'SYS_FBA_TCRV%';
no rows selected
SELECT T.FLASHBACK_ARCHIVE_NAME,
T.TABLE_NAME,
P.PARTITION_NAME,
P.NUM_ROWS,
ROUND ( (P.BLOCKS * 8) / 1024) SIZE_MB
FROM DBA_FLASHBACK_ARCHIVE_TABLES T,
DBA_TAB_PARTITIONS P
WHERE T.archive_table_name = P.table_name
AND partition_name='HIGH_PART'
ORDER BY T.flashback_archive_name, T.table_name, P.num_rows DESC;
no rows selected
SELECT MAX(ROUND ( (BLOCKS * 8) / 1024)) SIZE_MB
FROM DBA_TAB_PARTITIONS
WHERE PARTITION_NAME='HIGH_PART';
SIZE_MB
----------
SELECT p.spid "SPID",
b.name "Background Process",
s.status "STATUS",
s.sid "Session ID",
s.serial# "Serial No."
FROM V$PROCESS P,
V$BGPROCESS B,
V$SESSION S
WHERE s.paddr = p.addr
AND b.paddr(+) = p.addr
AND b.name = 'FBDA';
SPID Backg STATUS Session ID Serial No.
------------------ ----- ------------- ---------- ----------
18778 FBDA ACTIVE 2272 9338
set linesize 120
set numwidth 20
SELECT * FROM SYS_FBA_BARRIERSCN;
INST_ID BARRIERSCN ACTIVESCN STATUS SPARE
------- ------------ --------- --------- ---------
0 54983371892 54983371892
col owner format a20
col table_name format a25
col tablespace_name format a20
SELECT owner, table_name, tablespace_name
FROM DBA_TABLES
WHERE table_name LIKE 'SYS_FBA%';
OWNER TABLE_NAME TABLESPACE_NAME
-------------------- ------------------------- --------------------
SYS SYS_FBA_FA SYSTEM
SYS SYS_FBA_TSFA SYSTEM
SYS SYS_FBA_TRACKEDTABLES SYSTEM
SYS SYS_FBA_PARTITIONS SYSTEM
SYS SYS_FBA_USERS SYSTEM
SYS SYS_FBA_BARRIERSCN SYSTEM
SYS SYS_FBA_DL SYSTEM
SYS SYS_FBA_CONTEXT SYSTEM
SYS SYS_FBA_CONTEXT_AUD SYSTEM
SYS SYS_FBA_CONTEXT_LIST SYSTEM
SYS SYS_FBA_APP SYSTEM
SYS SYS_FBA_APP_TABLES SYSTEM
SYS SYS_FBA_COLS SYSTEM
SYS SYS_FBA_PERIOD SYSTEM
There are no TCRV or HIST tables
To force FBDA to be disabled follow this process.
Disable flashback archiving.
alter system set "_disable_flashback_archiver"=1;
System altered.
COL NAME FOR A30
COL VALUE FOR A30
SELECT name, value FROM V$PARAMETER
WHERE name = '_disable_flashback_archiver';
NAME VALUE
------------------------------ ----------
_disable_flashback_archiver 1
SELECT p.spid "SPID",
b.name "Background Process",
s.status "STATUS",
s.sid "Session ID",
s.serial# "Serial No."
FROM v$process p, v$bgprocess b, v$session s
WHERE s.paddr = p.addr
AND b.paddr(+) = p.addr
AND b.name = 'FBDA';
SPID Backg STATUS Session ID Serial No.
------------------------ ----- -------------------------------- -------------------- --------------------
18778 FBDA ACTIVE 2272 9338
kill -9 18778
Alternative to kill process - would be to bounce the instance
Once done - ora_fbda process is gone.
==================
script to kill fdba process
==================
#!/bin/bash
. /etc/sh/orash/oracle_login.sh igt
sqlplus / as sysdba << EOF
SET HEADING OFF
SET PAGESIZE 0
spool tmp_cmd_kill_fdba.sh
SELECT 'kill -9 '||p.spid
FROM v$process p, v$bgprocess b, v$session s
WHERE s.paddr = p.addr
AND b.paddr(+) = p.addr
AND b.name = 'FBDA';
EXIT;
EOF
less tmp_cmd_kill_fdba.sh | grep kill | grep -v SELECT > cmd_kill_fdba.sh
chmod 744 cmd_kill_fdba.sh
./cmd_kill_fdba.sh
rm -f tmp_cmd_kill_fdba.sh >/dev/null
exit
No comments:
Post a Comment