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