Re-Creating control file from existing database following ORA-00202: control file: ORA-27070: OSD-04006: Data error (cyclic redundancy check).

How to create a control file from existing database

Real life example, Control files got corrupted due to HW issue

Errors in file d:\software\oracle\diag\rdbms\igt\igt\trace\igt_ora_2324.trc:

ORA-00202: control file: 'D:\ORACLE_DB\DB1\DB_IGT\ORA_CONTROL_01.CTL'

ORA-27070: async read/write failed

OSD-04006: ReadFile() failure, unable to read from file

O/S-Error: (OS 23) Data error (cyclic redundancy check).

ORA-204 signalled during: alter database mount exclusive...

Option 1 - Generate a script for recreating the control files using a command below.

can work only if database is mounted or open

Option 2 - Write same script manually...

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

The trace keyword tells oracle to generate a script containing a create controlfile command 

and store it in the trace directory identified in the 

user_dump_dest parameter of the init.ora file.

SQL> show parameter user_dump_dest

NAME            TYPE    VALUE

--------------- ------- ---------------------------------------------

user_dump_dest  string  d:\software\oracle\diag\rdbms\igt\igt\trace

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database altered.

================================================================

This is the output d:\software\oracle\diag\rdbms\igt\igt\trace\igt_ora_6632.trc

================================================================

Trace file 

d:\software\oracle\diag\rdbms\igt\igt\trace\igt_ora_6632.trc

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

Windows NT Version V5.2 Service Pack 2

CPU                 : 16 - type 586, 2 Physical Cores

Process Affinity    : 0x00000000

Memory (Avail/Total): Ph:1619M/4095M, Ph+PgF:5712M/8010M, VA:1093M/2047M

Instance name: igt

Redo thread mounted by this instance: 1

Oracle process number: 25

Windows thread id: 6632, image: ORACLE.EXE (SHAD)

*** 2022-12-19 17:38:18.381

*** SESSION ID:(242.10210) 2022-12-19 17:38:18.381

*** CLIENT ID:() 2022-12-19 17:38:18.381

*** SERVICE NAME:(igt) 2022-12-19 17:38:18.381

*** MODULE NAME:(SQL*Plus) 2022-12-19 17:38:18.381

*** ACTION NAME:() 2022-12-19 17:38:18.381

 

-- The following are current System-scope REDO Log Archival related

-- parameters and can be included in the database initialization file.

--

-- LOG_ARCHIVE_DEST=''

-- LOG_ARCHIVE_DUPLEX_DEST=''

--

-- LOG_ARCHIVE_FORMAT=arch%T_%s_%r.arc

--

-- DB_UNIQUE_NAME="igt"

--

-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'

-- LOG_ARCHIVE_MAX_PROCESSES=4

-- ARCHIVE_LAG_TARGET=1800

-- STANDBY_FILE_MANAGEMENT=MANUAL

-- STANDBY_ARCHIVE_DEST=%ORACLE_HOME%\RDBMS

-- FAL_CLIENT=''

-- FAL_SERVER=''

--

-- LOG_ARCHIVE_DEST_1='LOCATION=m:\oracle_db\db2\db_igt\arch'

-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'

-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'

-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'

-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'

-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'

-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--

-- Below are two sets of SQL statements, each of which creates a new

-- control file and uses it to open the database. The first set opens

-- the database with the NORESETLOGS option and should be used only if

-- the current versions of all online logs are available. The second

-- set opens the database with the RESETLOGS option and should be used

-- if online logs are unavailable.

-- The appropriate set of statements can be copied from the trace into

-- a script file, edited as necessary, and executed when there is a

-- need to re-create the control file.

--

--     Set #1. NORESETLOGS case

--

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- Additional logs may be required for media recovery of offline

-- Use this only if the current versions of all online logs are

-- available.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT;

CREATE CONTROLFILE REUSE DATABASE "IGT" NORESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 1024

    MAXINSTANCES 8

    MAXLOGHISTORY 584

LOGFILE

  GROUP 1 'D:\ORACLE_DB\DB1\DB_IGT\ORA_REDO_01_A.RDO'  SIZE 100M,

  GROUP 2 'D:\ORACLE_DB\DB1\DB_IGT\ORA_REDO_02_A.RDO'  SIZE 100M,

  GROUP 3 'D:\ORACLE_DB\DB1\DB_IGT\ORA_REDO_03_A.RDO'  SIZE 100M

-- STANDBY LOGFILE

DATAFILE

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_SYSTEM_01.DBF',

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_SYSAUX_01.DBF',

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_UNDOTBS_01.DBF',

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_GIN_01.DBF',

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_GININDEX_01.DBF',

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_IGT_TABLE_01.DBF',

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_IGT_INDEX_01.DBF',

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_WORKAREA_01.DBF',

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_DWH_TABLE_01.DBF',

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_DWH_INDEX_01.DBF'

CHARACTER SET WE8ISO8859P1

;

-- Configure RMAN configuration record 1

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

-- Configure RMAN configuration record 2

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE 'M:\ORACLE_DB\DB2\DB_IGT\ARCH\ARCH001_1_779550848.ARC';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE

-- All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.

ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMPORARY ADD TEMPFILE 'D:\ORACLE_DB\DB1\DB_IGT\ORA_TEMPORARY_01.DBF'

     SIZE 629145600  REUSE AUTOEXTEND ON NEXT 104857600  MAXSIZE 6000M;

-- End of tempfile additions.

--

--     Set #2. RESETLOGS case

--

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- The contents of online logs will be lost and all backups will

-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT;

CREATE CONTROLFILE REUSE DATABASE "IGT" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 1024

    MAXINSTANCES 8

    MAXLOGHISTORY 584

LOGFILE

  GROUP 1 'D:\ORACLE_DB\DB1\DB_IGT\ORA_REDO_01_A.RDO'  SIZE 100M,

  GROUP 2 'D:\ORACLE_DB\DB1\DB_IGT\ORA_REDO_02_A.RDO'  SIZE 100M,

  GROUP 3 'D:\ORACLE_DB\DB1\DB_IGT\ORA_REDO_03_A.RDO'  SIZE 100M

-- STANDBY LOGFILE

DATAFILE

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_SYSTEM_01.DBF',

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_SYSAUX_01.DBF',

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_UNDOTBS_01.DBF',

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_GIN_01.DBF',

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_GININDEX_01.DBF',

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_IGT_TABLE_01.DBF',

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_IGT_INDEX_01.DBF',

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_WORKAREA_01.DBF',

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_DWH_TABLE_01.DBF',

  'D:\ORACLE_DB\DB1\DB_IGT\ORA_DWH_INDEX_01.DBF'

CHARACTER SET WE8ISO8859P1

;

-- Configure RMAN configuration record 1

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

-- Configure RMAN configuration record 2

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE 'M:\ORACLE_DB\DB2\DB_IGT\ARCH\ARCH001_1_779550848.ARC';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMPORARY ADD TEMPFILE 'D:\ORACLE_DB\DB1\DB_IGT\ORA_TEMPORARY_01.DBF'

     SIZE 629145600  REUSE AUTOEXTEND ON NEXT 104857600  MAXSIZE 6000M;

-- End of tempfile additions.

--

By Example:

SQL> SHUTDOWN IMMEDIATE;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> STARTUP NOMOUNT;

ORACLE instance started.

Total System Global Area  523108352 bytes

Fixed Size                  1348420 bytes

Variable Size             461376700 bytes

Database Buffers           54525952 bytes

Redo Buffers                5857280 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "igt" NORESETLOGS ARCHIVELOG

     MAXLOGFILES 16

     MAXLOGMEMBERS 3

     MAXDATAFILES 1024

     MAXINSTANCES 8

     MAXLOGHISTORY 1

LOGFILE 

     GROUP 1 ('d:\oracle_db\db1\db_igt\ora_redo_01_a.rdo') SIZE 100M,

     GROUP 2 ('d:\oracle_db\db1\db_igt\ora_redo_02_a.rdo') SIZE 100M,

     GROUP 3 ('d:\oracle_db\db1\db_igt\ora_redo_03_a.rdo') SIZE 100M 

DATAFILE 'd:\oracle_db\db1\db_igt\ora_system_01.dbf',

     'D:\oracle_db\db1\db_igt\ORA_DWH_INDEX_01.DBF',

     'D:\oracle_db\db1\db_igt\ORA_DWH_TABLE_01.DBF',

     'D:\oracle_db\db1\db_igt\ORA_GIN_01.DBF',

     'D:\oracle_db\db1\db_igt\ORA_GININDEX_01.DBF',

     'D:\oracle_db\db1\db_igt\ORA_IGT_INDEX_01.DBF',

     'D:\oracle_db\db1\db_igt\ORA_IGT_TABLE_01.DBF',

     'D:\oracle_db\db1\db_igt\ORA_SYSAUX_01.DBF',

     'D:\oracle_db\db1\db_igt\ORA_SYSTEM_01.DBF',

     'D:\oracle_db\db1\db_igt\ORA_WORKAREA_01.DBF',

     'D:\oracle_db\db1\db_igt\'

CHARACTER SET WE8ISO8859P1;

At this point, control files should be recreated, and have a new timestamp.

SQL> RECOVER DATABASE;

Media recovery complete.

SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER TABLESPACE TEMPORARY ADD TEMPFILE 'D:\ORACLE_DB\DB1\DB_IGT\ORA_TEMPORARY_01.DBF'

 SIZE 629145600  REUSE AUTOEXTEND ON NEXT 104857600  MAXSIZE 6000M;

Tablespace altered.

 

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP;

ORACLE instance started.

Total System Global Area  523108352 bytes

Fixed Size                  1348420 bytes

Variable Size             461376700 bytes

Database Buffers           54525952 bytes

Redo Buffers                5857280 bytes

Database mounted.

Database opened.

Now is a good idea to take a backup 

rman target / 

RMAN> run {

   configure controlfile autobackup on;

   configure backup optimization on;

   set controlfile autobackup format for device type disk to 'p:\backup\ora_online\20221219_1811\%F';

   allocate channel 'dev_0' type disk format = 'p:\backup\ora_online\20221219_1811\dbf_%d_%T_%U';

   backup full database;

   backup archivelog from time = 'sysdate-10/1440' format 'p:\backup\ora_online\20221219_1811\arch_%d_%T_%U';

   release channel 'dev_0';

}

exit;

retentionRedundancy=2

rman target / 

report obsolete redundancy = $retentionRedundancy;

exit;

rman target / 

allocate channel for delete type disk;

delete noprompt force archivelog all backed up $retentionRedundancy times to device type disk;

delete noprompt force copy of archivelog all completed before 'sysdate-10';

delete noprompt force obsolete redundancy = $retentionRedundancy;

release channel;

exit;

Golden Gate, Report process status from bash to a file

Report GG processes status to a file

mkdir /software/ogg/191/gg_report

mkdir /software/ogg/191/gg_report/scripts

mkdir /software/ogg/191/gg_report/log

/software/ogg/191/gg_report/scripts/get_gg_status.oby

SEND DPM_I_01 STATUS
SEND DPM_P_01 STATUS
SEND DPM_S_01 STATUS
SEND EXT_I_01 STATUS
SEND EXT_P_01 STATUS
SEND EXT_S_01 STATUS
SEND REP_I_01 STATUS
SEND REP_P_01 STATUS
SEND REP_S_01 STATUS

/software/ogg/191/gg_report/scripts/report_gg_status.sh

#!/bin/bash
echo OBEY /software/ogg/191/gg_report/scripts/get_gg_status.oby | /software/ogg/191/ggsci > /software/ogg/191/gg_report/log/gg_status_all.log

ORA-01489: result of string concatenation is too long

The Issue

When running in sqlplus, the output is a long concatenated String.

In case row would overflow over 4000 characters, an error would come:

ORA-01489: result of string concatenation is too long

The solution

Convert first member of the concatenation to CLOB, and concatenate the rest.

For example:

This is the SQL:

SELECT key1||','||imsi||','||msisdn||','||voice_mo1||','||voice_mt1||','||

sms_mo1||','||data1||','||voice_mo2||','||voice_mt2||','||sms_mo2||','||

data2||','||voice_mo3||','||voice_mt3||','||sms_mo3||','||data3||','||

voice_mo4||','||voice_mt4||','||sms_mo4||','||data4||','||voice_mo5||','||

voice_mt5||','||sms_mo5||','||data5||','||voice_mo6||','||voice_mt6||','||

sms_mo6||','||data6||','||voice_mo7||','||voice_mt7||','||sms_mo7||','||

data7||','||voice_mo8||','||voice_mt8||','||sms_mo8||','||data8||','||

voice_mo9||','||voice_mt9||','||sms_mo9||','||data9||','||voice_mo10||','||

voice_mt10||','||sms_mo10||','||data10||','||voice_mo11||','||voice_mt11||','||

sms_mo11||','||data11||','||voice_mo12||','||voice_mt12||','||sms_mo12||','||

data12||','||voice_mo13||','||voice_mt13||','||sms_mo13||','||data13||','||

voice_mo14||','||voice_mt14||','||sms_mo14||','||data14||','||

voice_mo15||','||voice_mt15||','||sms_mo15||','||data15||','||

voice_mo16||','||voice_mt16||','||sms_mo16||','||data16||','||

voice_mo17||','||voice_mt17||','||sms_mo17||','||data17||','||

voice_mo18||','||voice_mt18||','||sms_mo18||','||data18||','||

voice_mo19||','||voice_mt19||','||sms_mo19||','||data19||','||

voice_mo20||','||voice_mt20||','||sms_mo20||','||data20||','||

voice_mo21||','||voice_mt21||','||sms_mo21||','||data21||','||

voice_mo22||','||voice_mt22||','||sms_mo22||','||data22||','||

voice_mo23||','||voice_mt23||','||sms_mo23||','||data23||','||

voice_mo24||','||voice_mt24||','||sms_mo24||','||data24||','||

voice_mo25||','||voice_mt25||','||sms_mo25||','||data25||','||

voice_mo26||','||voice_mt26||','||sms_mo26||','||data26||','||

voice_mo27||','||voice_mt27||','||sms_mo27||','||data27||','||

voice_mo28||','||voice_mt28||','||sms_mo28||','||data28||','||

voice_mo29||','||voice_mt29||','||sms_mo29||','||data29||','||

voice_mo30||','||voice_mt30||','||sms_mo30||','||data30||','||

voice_mo31||','||voice_mt31||','||sms_mo31||','||data31||','||

TO_CHAR(ts_last_modified,'YYYYMMDD hh24:mi:ss')||','||affiliate_id

FROM SFI_CUSTOMER_USAGE_HOURLY;

This is giving ORA-01489: result of string concatenation is too long

Converting sql to SELECT CLOB() does not solve error

SELECT TO_CLOB(key1|| ','||imsi||','||msisdn||','||voice_mo1||','||voice_mt1||','||

sms_mo1||','||data1||','||voice_mo2||','||voice_mt2||','||sms_mo2||','||

data2||','||voice_mo3||','||voice_mt3||','||sms_mo3||','||data3||','||

voice_mo4||','||voice_mt4||','||sms_mo4||','||data4||','||voice_mo5||','||

voice_mt5||','||sms_mo5||','||data5||','||voice_mo6||','||voice_mt6||','||

sms_mo6||','||data6||','||voice_mo7||','||voice_mt7||','||sms_mo7||','||

data7||','||voice_mo8||','||voice_mt8||','||sms_mo8||','||data8||','||

voice_mo9||','||voice_mt9||','||sms_mo9||','||data9||','||voice_mo10||','||

voice_mt10||','||sms_mo10||','||data10||','||voice_mo11||','||voice_mt11||','||

sms_mo11||','||data11||','||voice_mo12||','||voice_mt12||','||sms_mo12||','||

data12||','||voice_mo13||','||voice_mt13||','||sms_mo13||','||data13||','||

voice_mo14||','||voice_mt14||','||sms_mo14||','||data14||','||

voice_mo15||','||voice_mt15||','||sms_mo15||','||data15||','||

voice_mo16||','||voice_mt16||','||sms_mo16||','||data16||','||

voice_mo17||','||voice_mt17||','||sms_mo17||','||data17||','||

voice_mo18||','||voice_mt18||','||sms_mo18||','||data18||','||

voice_mo19||','||voice_mt19||','||sms_mo19||','||data19||','||

voice_mo20||','||voice_mt20||','||sms_mo20||','||data20||','||

voice_mo21||','||voice_mt21||','||sms_mo21||','||data21||','||

voice_mo22||','||voice_mt22||','||sms_mo22||','||data22||','||

voice_mo23||','||voice_mt23||','||sms_mo23||','||data23||','||

voice_mo24||','||voice_mt24||','||sms_mo24||','||data24||','||

voice_mo25||','||voice_mt25||','||sms_mo25||','||data25||','||

voice_mo26||','||voice_mt26||','||sms_mo26||','||data26||','||

voice_mo27||','||voice_mt27||','||sms_mo27||','||data27||','||

voice_mo28||','||voice_mt28||','||sms_mo28||','||data28||','||

voice_mo29||','||voice_mt29||','||sms_mo29||','||data29||','||

voice_mo30||','||voice_mt30||','||sms_mo30||','||data30||','||

voice_mo31||','||voice_mt31||','||sms_mo31||','||data31||','||

TO_CHAR(ts_last_modified,'YYYYMMDD hh24:mi:ss')||','||affiliate_id)

FROM SFI_CUSTOMER_USAGE_HOURLY;

This is giving ORA-01489: result of string concatenation is too long

But this works:

SELECT TO_CLOB(key1) ||','||imsi||','||msisdn||','||voice_mo1||','||voice_mt1||','||

sms_mo1||','||data1||','||voice_mo2||','||voice_mt2||','||sms_mo2||','||

data2||','||voice_mo3||','||voice_mt3||','||sms_mo3||','||data3||','||

voice_mo4||','||voice_mt4||','||sms_mo4||','||data4||','||voice_mo5||','||

voice_mt5||','||sms_mo5||','||data5||','||voice_mo6||','||voice_mt6||','||

sms_mo6||','||data6||','||voice_mo7||','||voice_mt7||','||sms_mo7||','||

data7||','||voice_mo8||','||voice_mt8||','||sms_mo8||','||data8||','||

voice_mo9||','||voice_mt9||','||sms_mo9||','||data9||','||voice_mo10||','||

voice_mt10||','||sms_mo10||','||data10||','||voice_mo11||','||voice_mt11||','||

sms_mo11||','||data11||','||voice_mo12||','||voice_mt12||','||sms_mo12||','||

data12||','||voice_mo13||','||voice_mt13||','||sms_mo13||','||data13||','||

voice_mo14||','||voice_mt14||','||sms_mo14||','||data14||','||

voice_mo15||','||voice_mt15||','||sms_mo15||','||data15||','||

voice_mo16||','||voice_mt16||','||sms_mo16||','||data16||','||

voice_mo17||','||voice_mt17||','||sms_mo17||','||data17||','||

voice_mo18||','||voice_mt18||','||sms_mo18||','||data18||','||

voice_mo19||','||voice_mt19||','||sms_mo19||','||data19||','||

voice_mo20||','||voice_mt20||','||sms_mo20||','||data20||','||

voice_mo21||','||voice_mt21||','||sms_mo21||','||data21||','||

voice_mo22||','||voice_mt22||','||sms_mo22||','||data22||','||

voice_mo23||','||voice_mt23||','||sms_mo23||','||data23||','||

voice_mo24||','||voice_mt24||','||sms_mo24||','||data24||','||

voice_mo25||','||voice_mt25||','||sms_mo25||','||data25||','||

voice_mo26||','||voice_mt26||','||sms_mo26||','||data26||','||

voice_mo27||','||voice_mt27||','||sms_mo27||','||data27||','||

voice_mo28||','||voice_mt28||','||sms_mo28||','||data28||','||

voice_mo29||','||voice_mt29||','||sms_mo29||','||data29||','||

voice_mo30||','||voice_mt30||','||sms_mo30||','||data30||','||

voice_mo31||','||voice_mt31||','||sms_mo31||','||data31||','||

TO_CHAR(ts_last_modified,'YYYYMMDD hh24:mi:ss')||','||affiliate_id

FROM SFI_CUSTOMER_USAGE_HOURLY;

Thread 1 cannot allocate new log, sequence 99999 Private strand flush not complete

===========

Issue

===========

Frequent messages "Thread 1 cannot allocate new log, sequence 15123. Private strand flush not complete" in alert.log, and timeouts in application.

This is a time frame, where batch job is running, performing DELETE task from major tables.

===========

Theory

===========

The issue - There are non commited transactions, that is still holding in use the redo log, and it cannot be allocated to be available to store new transactions.

===========

Solution:

===========

Option A - Add more Log File Groups - see this Technote Add Redo Log Group by Example. Fixing "log file switch" wait event

Option B - make Redo Logs bigger in size.

COL STATUS FOR A20

SELECT group#, sequence#, bytes, members, status 

  FROM V$LOG;

    GROUP#  SEQUENCE#      BYTES    MEMBERS STATUS

---------- ---------- ---------- ---------- --------------------

         1      15148  524288000          1 INACTIVE

         2      15149  524288000          1 ACTIVE

         3      15147  524288000          1 INACTIVE

         4      15150  524288000          1 CURRENT

         5      15145  524288000          1 INACTIVE

         6      15146  524288000          1 INACTIVE

 

524,288,000

Lets increase from 512M to 1024M

When Log File Group is in INACTIVE status - drop it and recreate in bigger size.

To switch between log files:

ALTER SYSTEM SWITCH LOGFILE;

ALTER DATABASE DROP LOGFILE GROUP 1;

ALTER DATABASE ADD LOGFILE GROUP 1 ('/oracle_db/db1/db_igt/ora_redo_01_a.rdo') SIZE 1024M REUSE;

ALTER DATABASE DROP LOGFILE GROUP 2;

ALTER DATABASE ADD LOGFILE GROUP 2 ('/oracle_db/db1/db_igt/ora_redo_02_a.rdo') SIZE 1024M REUSE;

ALTER DATABASE DROP LOGFILE GROUP 3;

ALTER DATABASE ADD LOGFILE GROUP 3 ('/oracle_db/db1/db_igt/ora_redo_03_a.rdo') SIZE 1024M REUSE;

ALTER DATABASE DROP LOGFILE GROUP 4;

ALTER DATABASE ADD LOGFILE GROUP 4 ('/oracle_db/db1/db_igt/ora_redo_04_a.rdo') SIZE 1024M REUSE;

ALTER DATABASE DROP LOGFILE GROUP 5;

ALTER DATABASE ADD LOGFILE GROUP 5 ('/oracle_db/db1/db_igt/ora_redo_05_a.rdo') SIZE 1024M REUSE;

ALTER DATABASE DROP LOGFILE GROUP 6;

ALTER DATABASE ADD LOGFILE GROUP 6 ('/oracle_db/db1/db_igt/ora_redo_06_a.rdo') SIZE 1024M REUSE;

COL STATUS FOR A20

SELECT group#, sequence#, bytes, status 

  FROM V$LOG;

  

    GROUP#  SEQUENCE#      BYTES STATUS

---------- ---------- ---------- --------------------

         1      15152 1073741824 CURRENT

         2          0 1073741824 UNUSED

         3          0 1073741824 UNUSED

         4      15150  524288000 ACTIVE

         5      15151  524288000 ACTIVE

         6          0 1073741824 UNUSED

 

Need to wait, for ACTIVE files, to become INACTIVE, and them recreate all the files with new size.

    GROUP#  SEQUENCE#      BYTES STATUS

---------- ---------- ---------- --------------------

         1      15152 1073741824 ACTIVE

         2      15153 1073741824 CURRENT

         3          0 1073741824 UNUSED

         4          0 1073741824 UNUSED

         5          0 1073741824 UNUSED

         6          0 1073741824 UNUSED

ORA-24247: network access denied by access control list (ACL)

==============
Issue
==============

When calling    UTL_SMTP.open_connection(mailhost, 25), 

There is the error:

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at "MANAGER.SEND_MAIL", line 16
ORA-06512: at line 3
View program sources of error stack?

==============
Solution
==============
Before Oracle 11g access to network services was controlled by granting privileges on packages such as UTL_HTTP, UTL_TCP, UTL_SMTP, and UTL_MAIL.

GRANT EXECUTE ON UTL_HTTP TO MANAGER;
GRANT EXECUTE ON UTL_TCP TO MANAGER;
GRANT EXECUTE ON UTL_SMTP TO MANAGER;
GRANT EXECUTE ON UTL_MAIL TO MANAGER;

After 11.1 Oracle introduced Application Control Lists (ACL) as part of their Application Security and has now added Application Control Entry (ACE).
If you run into the ORA-24247: network access denied by access control list (ACL) error you can use DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE procedure to resolve the issue.

One has to create an ACE using the DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE procedure to grant access control privileges to a user. 

The procedure will append an access control entry with specified privilege to the ACL for the given host. 

If the ACL does not exist it will create it. 

The syntax:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
  host => '10.20.30.40',
  LOWER_PORT => NULL,
  UPPER_PORT => NULL,
  ACE => XS$ACE_TYPE(PRIVILEGE_LIST => xs$name_list('smtp'),
                       PRINCIPAL_NAME => 'MANAGER',
                       principal_type => xs_acl.ptype_db)
 );
 END;
 /

=============

Oracle 11 issue

=============

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

 
In oracle before 12.1, the above would fails with error  

ORA-06550: line 6, column 10:
PLS-00201: identifier 'XS$ACE_TYPE' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
   
Cause:
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE procedure is introduced starting 12c database. 
In 11.2.0.4 database it does not exists and it will not work.

Need to use the following syntax for creating ACL in 11.2.0.4 database

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL
 (
 acl => 'manager.xml',
 description => 'Mail Usage',
 principal => 'MANAGER',
 is_grant => TRUE,
privilege => 'connect');
END;
/
commit;
BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( 
   acl => 'manager.xml',
   host => '10.20.30.40');
  commit;
END;
/

=============

DBA_XXX tables

=============

SELECT * FROM DBA_NETWORK_ACLS;

HOST           LOWER_PORT UPPER_PORT ACL                    ACLID

-------------- ---------- ---------- ---------------------- ------------------------

10.20.20.200                         /sys/acls/manager.xml  EF39990944A18542E0533456

10.20.20.200 - is the IP of the server which hosts oracle service.

SELECT acl, aclid, principal, privilege FROM DBA_NETWORK_ACL_PRIVILEGES;

ACL                    ACLID                    PRINCIPAL            PRIVILEGE

---------------------- ------------------------ -------------------- ----------

/sys/acls/manager.xml  EF39990944A18542E0533456 MANAGER              connect

=============

General

=============

How to check if smtp host is open to connection

in case of failure to connect to smtp server:

ORA-20000: ORA-29278: SMTP transient error: 421 Service not available
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "MANAGER.SEND_MAIL", line 39
ORA-06512: at line 3

What to check:

check snmp service is running
/etc/rc.d/init.d/snmpd status
snmpd (pid 2099) is running...

check snmp server is reachable and correct
in case no connection:
snmpwalk -Os -c public -v 2c 10.20.30.44 iso.3.6.1.2.1.1.1
Timeout: No Response from 10.20.30.44

telnet 10.20.30.40 25
Trying 10.20.30.40...
Connected to 10.20.30.40.
Escape character is '^]'.
220 GER-IT-EX16-01.mydomain.local Microsoft ESMTP MAIL Service ready at Wed, 7 Dec 2022 09:38:24 +0100

ORA-14602: SUBPARTITION TEMPLATE is legal only for a composite partitioned table

ORA-14602: SUBPARTITION TEMPLATE is legal only for a composite partitioned table
One cannot do TEMPLATE SUBPARTITION to a table that was not Composite Partitioned.

Composite Partitioned, meaning PARTITION BY RANGE.
This is opposed to PARTITION BY LIST.

How to tell:
SELECT DISTINCT table_name, composite 

  FROM USER_TAB_PARTITIONS 

ORDER BY table_name;

 
TABLE_NAME                     COMPOSITE
------------------------------ ------------
GA_W_COUNTERS_HISTORY          NO
REP_DAILY_DNORM                YES
REP_MONTHLY_DNORM              YES
SFI_CUSTOMER_OPTIONS           NO
SFI_CUSTOMER_PROFILE           NO
SFI_CUSTOMER_PROFILE_TMP       NO
SFI_CUSTOMER_USAGE             NO
SFI_CUSTOMER_USAGE_HOURLY      NO
SFI_CUSTOMER_USAGE_HOURLY_TMP  NO
SFI_VISITOR_PROFILE            NO
SGA_SUBSCRIBER_SFI             NO
SGA_SUBSCRIBER_SFI_B           NO
SGA_W_IPN_SUBSCRIBER           YES
SGA_W_MAJOR_EVENTS             NO
SGA_W_MOCO_SUBSCRIBER          YES
SGA_W_MOCO_VISITOR             YES
SGA_W_PSMS_SUBSCRIBER          YES
SWMS_SUBSCRIBER                YES

Add Redo Log Group by Example. Fixing "log file switch" wait event

========================

Fixing "log file switch" wait event

========================

Fix "log file switch" wait event by adding Redo Log Group by Example. 

"log file switch" wait event is related to frequent log file switches, and waits till the log file was  archived, and can be free for writing.

Current status
COL STATUS FOR A20

SELECT group#, sequence#, bytes, members, status 

FROM V$LOG;

    GROUP#  SEQUENCE#      BYTES    MEMBERS STATUS

---------- ---------- ---------- ---------- --------------------

         1      14794  524288000          1 CURRENT

         2      14792  524288000          1 INACTIVE

         3      14793  524288000          1 ACTIVE

set linesize 120

col MEMBER for A40

col STATUS for A10

col TYPE for A10

SELECT * FROM V$LOGFILE;

    GROUP# STATUS     TYPE       MEMBER                                   IS_RECOVERY_     CON_ID

---------- ---------- ---------- ---------------------------------------- ------------ ----------

         1            ONLINE     /oracle_db/db1/db_igt/ora_redo_01_a.rdo  NO                    0

         2            ONLINE     /oracle_db/db1/db_igt/ora_redo_02_a.rdo  NO                    0

         3            ONLINE     /oracle_db/db1/db_igt/ora_redo_03_a.rdo  NO                    0

Add 3 redo files

ALTER DATABASE ADD LOGFILE GROUP 4 ('/oracle_db/db1/db_igt/ora_redo_04_a.rdo') SIZE 500M REUSE;

ALTER DATABASE ADD LOGFILE GROUP 5 ('/oracle_db/db1/db_igt/ora_redo_05_a.rdo') SIZE 500M REUSE;

ALTER DATABASE ADD LOGFILE GROUP 6 ('/oracle_db/db1/db_igt/ora_redo_06_a.rdo') SIZE 500M REUSE;

SELECT group#, sequence#, bytes, members, status 

FROM V$LOG;

    GROUP#  SEQUENCE#      BYTES    MEMBERS STATUS

---------- ---------- ---------- ---------- ----------

         1      14794  524288000          1 ACTIVE

         2      14795  524288000          1 CURRENT

         3      14793  524288000          1 INACTIVE

         4          0  524288000          1 UNUSED

         5          0  524288000          1 UNUSED

         6          0  524288000          1 UNUSED

ALTER DATABASE DROP LOGFILE GROUP 1;

ALTER DATABASE DROP LOGFILE GROUP 2;

ALTER DATABASE DROP LOGFILE GROUP 3;

ALTER DATABASE ADD LOGFILE GROUP 1 ('/oracle_db/db1/db_igt/ora_redo_01_a.rdo') SIZE 1000M REUSE;

ALTER DATABASE ADD LOGFILE GROUP 2 ('/oracle_db/db1/db_igt/ora_redo_02_a.rdo') SIZE 1000M REUSE;

ALTER DATABASE ADD LOGFILE GROUP 3 ('/oracle_db/db1/db_igt/ora_redo_03_a.rdo') SIZE 1000M REUSE;

SET LINESIZE 120

COL MEMBER FOR A50

COL TYPE FOR A12

SELECT * FROM V$LOGFILE;

    GROUP# STATUS MEMBER                                  IS_RECOVERY
---------- ------ --------------------------------------- -----------
         1 ONLINE /oracle_db/db1/db_igt/ora_redo_01_a.rdo NO
         2 ONLINE /oracle_db/db1/db_igt/ora_redo_02_a.rdo NO
         3 ONLINE /oracle_db/db1/db_igt/ora_redo_03_a.rdo NO
         4 ONLINE /oracle_db/db1/db_igt/ora_redo_04_a.rdo NO
         5 ONLINE /oracle_db/db1/db_igt/ora_redo_05_a.rdo NO
         6 ONLINE /oracle_db/db1/db_igt/ora_redo_06_a.rdo NO

SQL> SELECT GROUP#, SEQUENCE#, BYTES, MEMBERS, STATUS 

     FROM V$LOG;
    GROUP#  SEQUENCE#      BYTES    MEMBERS STATUS
---------- ---------- ---------- ---------- ------------
         1     467716  524288000          1 INACTIVE
         2     467717  524288000          1 INACTIVE
         3     467713  524288000          1 INACTIVE
         4     467718  524288000          1 CURRENT
         5     467714  524288000          1 INACTIVE
         6     467715  524288000          1 INACTIVE

To switch to another logfile

ALTER SYSTEM SWITCH LOGFILE;

System altered.

SELECT group#, sequence#, bytes, members, status  FROM V$LOG;

    GROUP#  SEQUENCE#      BYTES    MEMBERS STATUS

---------- ---------- ---------- ---------- ----------

         1      14794  524288000          1 ACTIVE

         2      14795  524288000          1 ACTIVE

         3      14793  524288000          1 INACTIVE

         4      14796  524288000          1 CURRENT

         5          0  524288000          1 UNUSED

         6          0  524288000          1 UNUSED  
 
Log status:
UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed.
ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.

To drop the member or group, the Status should be UNUSED or INACTIVE.
ALTER DATABASE DROP LOGFILE MEMBER '/oracle_db/db1/db_igt/ora_redo_01_a.rdo';
ALTER DATABASE DROP LOGFILE GROUP 1;

SELECT  GROUP#, MEMBER FROM V$LOGFILE;

ALTER DATABASE ADD LOGFILE GROUP 1 ('/oracle_db/db1/db_igt/ora_redo_01_a.rdo') SIZE 1000M REUSE;
ALTER DATABASE ADD LOGFILE GROUP 2 ('/oracle_db/db1/db_igt/ora_redo_02_a.rdo') SIZE 1000M REUSE;
ALTER DATABASE ADD LOGFILE GROUP 3 ('/oracle_db/db1/db_igt/ora_redo_03_a.rdo') SIZE 1000M REUSE;

SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE   MEMBER                             IS_RECOVERY_
------ ------ ------- ---------------------------------------- -----
1      ONLINE         /oracle_db/db1/db_igt/ora_redo_01_a.rdo  NO
2      ONLINE         /oracle_db/db1/db_igt/ora_redo_02_a.rdo  NO
3      ONLINE         /oracle_db/db1/db_igt/ora_redo_03_a.rdo  NO


 SELECT GROUP#, SEQUENCE#, BYTES, MEMBERS, STATUS FROM V$LOG;
    GROUP#  SEQUENCE#      BYTES    MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------------------------------------------------------
         1      15463  524288000          1 CURRENT
         2      15461  524288000          1 INACTIVE
         3      15462  524288000          1 INACTIVE
ALTER SYSTEM SWITCH LOGFILE;

SQL> SELECT GROUP#, SEQUENCE#, BYTES, MEMBERS, STATUS FROM V$LOG;
    GROUP#  SEQUENCE#              BYTES    MEMBERS STATUS
---------- ---------- ------------------ ---------- ----------------------------------------------------------------
         1      15463          524288000          1 ACTIVE
         2      15464          524288000          1 CURRENT
         3      15462          524288000          1 INACTIVE
ALTER DATABASE DROP LOGFILE MEMBER '/oracle_db/db1/db_igt/ora_redo_01_a.rdo';
ALTER DATABASE DROP LOGFILE GROUP 3;


SELECT * FROM V$LOGFILE;
    GROUP# STATUS               TYPE                         MEMBER                                   IS_RECOVERY_
---------- -------------------- ---------------------------- ---------------------------------------- ------------
         1                      ONLINE                       /oracle_db/db1/db_igt/ora_redo_01_a.rdo  NO
         2                      ONLINE                       /oracle_db/db1/db_igt/ora_redo_02_a.rdo  NO
         3                      ONLINE                       /oracle_db/db1/db_igt/ora_redo_03_a.rdo  NO
 
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
SQL> SELECT GROUP#, SEQUENCE#, BYTES, MEMBERS, STATUS FROM V$LOG;
    GROUP#  SEQUENCE#              BYTES    MEMBERS STATUS
---------- ---------- ------------------ ---------- -----------------
         1      15463          524288000          1 ACTIVE
         2      15464          524288000          1 CURRENT
 
SQL> SELECT * FROM V$LOGFILE;
    GROUP# STATUS               TYPE                 MEMBER                                   IS_RECOVERY_
---------- -------------------- -------------------- ---------------------------------------- ------------
         1                      ONLINE               /oracle_db/db1/db_igt/ora_redo_01_a.rdo  NO
         2                      ONLINE               /oracle_db/db1/db_igt/ora_redo_02_a.rdo  NO
 
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('/oracle_db/db1/db_igt/ora_redo_03_a.rdo') SIZE 1000M REUSE;
Database altered.
SQL> SELECT GROUP#, SEQUENCE#, BYTES, MEMBERS, STATUS FROM V$LOG;
    GROUP#  SEQUENCE#              BYTES    MEMBERS STATUS
---------- ---------- ------------------ ---------- -----------------
         1      15463          524288000          1 ACTIVE
         2      15464          524288000          1 CURRENT
         3          0         1048576000          1 UNUSED
 
 
ALTER DATABASE DROP LOGFILE GROUP 1;  
ALTER DATABASE ADD LOGFILE GROUP 1 ('/oracle_db/db1/db_igt/ora_redo_01_a.rdo') SIZE 1000M REUSE;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE ADD LOGFILE GROUP 2 ('/oracle_db/db1/db_igt/ora_redo_02_a.rdo') SIZE 1000M REUSE;
SQL> SELECT GROUP#, SEQUENCE#, BYTES, MEMBERS, STATUS FROM V$LOG;
    GROUP#  SEQUENCE#              BYTES    MEMBERS STATUS
---------- ---------- ------------------ ---------- -----------------
         1          0         1048576000          1 UNUSED
         2          0         1048576000          1 UNUSED
         3      15465         1048576000          1 CURRENT

ora_fbda process is consuming CPU

==================

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