ORA-00742: Log read detects lost write

 

Issue:-  We had a situation for a customer where his server was shutdown abruptly due to power cut at datacenter. After resuming the power, when he tried to start his database he encountered following error“ORA-00742: Log read detects lost write in thread 1 sequence 5789 block 805653”

Environment :- Windows 2019

DB Version:-     12.2.0.1

 

Solution: -

Login to database and shutdown cleanly

D:\oracle\product\12.2.0\dbhome\bin>sqlplus / as sysdba   SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 11 10:26:27 2024   Copyright (c) 1982, 2016, Oracle.  All rights reserved.   Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production   SQL> shutdown immediate; ORA-01109: database not open   Database dismounted. ORACLE instance shut down.

 

Lets try to open the database

D:\oracle\product\12.2.0\dbhome\bin>sqlplus / as sysdba   SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 11 10:28:29 2024   Copyright (c) 1982, 2016, Oracle.  All rights reserved. Connected to an idle instance.   SQL> startup ORACLE instance started.   Total System Global Area 1.2885E+10 bytes Fixed Size                  8761232 bytes Variable Size            2885681264 bytes Database Buffers         9965666304 bytes Redo Buffers               24793088 bytes Database mounted. ORA-00742: Log read detects lost write in thread 1 sequence 5789 block 805653 ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\ORCL\REDO01.LOG'

As we can see database is getting mounted, but it couldn’t open, and above error was thrown.

Th above error is generally observed due to a relog log corruption. In this case corruption seems to have happened on relog group 1.

 

SQL> select * from v$logfile; SQL> col member for a35 SQL> /       GROUP# STATUS  TYPE    MEMBER                              IS_     CON_ID ---------- ------- ------- ----------------------------------- --- ----------          4         ONLINE  D:\ORACLE\ORADATA\ORCL\REDO04.LOG   NO           0          3         ONLINE  D:\ORACLE\ORADATA\ORCL\REDO03.LOG   NO           0          2         ONLINE  D:\ORACLE\ORADATA\ORCL\REDO02.LOG   NO           0          1         ONLINE  D:\ORACLE\ORADATA\ORCL\REDO01.LOG   NO           0     SQL> select * from v$log;       GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------          1          1       5789  536870912        512          1 NO  CURRENT              342324096 11-MAR-24   1.8447E+19                    0          4          1       5788  536870912        512          1 YES INACTIVE             342320572 11-MAR-24    342324096 11-MAR-24          0          3          1       5787  536870912        512          1 YES INACTIVE             342313670 11-MAR-24    342320572 11-MAR-24          0          2          1       5786  536870912        512          1 YES INACTIVE             342313238 11-MAR-24    342313670 11-MAR-24          0

 

Lets try do an incomplete recovery of the database

SQL> recover database until cancel; ORA-00279: change 342324096 generated at 03/11/2024 05:04:10 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\FAST_RECOVERY_AREA\ORCL\ORCL\ARCHIVELOG\2024_03_11\O1_MF_1_5789_%U_.ARC ORA-00280: change 342324096 for thread 1 is in sequence #5789   Specify log: {<RET>=suggested | filename | AUTO | CANCEL} D:\ORACLE\ORADATA\ORCL\REDO01.LOG Log applied. Media recovery complete   Here we need to provide the redolog group which was corrupted.

 

 

Now lets try to open the database using resetlogs

SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-00600: internal error code, arguments: [krsi_al_hdr_update.15], [4294967295], [], [], [], [], [], [], [], [], [], []

 

As per Doc ID 2026541.1 , to fix the above error we need to rename the online corrupted redolog and initial resetlogs again

We renamed D:\ORACLE\ORADATA\ORCL\REDO01.LOG as D:\ORACLE\ORADATA\ORCL\REDO01_old.LOG and tried to do recovery again

 

SQL> recover database using backup controlfile until cancel; ORA-00279: change 342379894 generated at 03/11/2024 08:46:09 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\FAST_RECOVERY_AREA\ORCL\ORCL\ARCHIVELOG\2024_03_11\O1_MF_1_5789_%U_.ARC ORA-00280: change 342379894 for thread 1 is in sequence #5789     Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled.   SQL> alter database open resetlogs; Database alerted.   SQL> select open_mode from v$database;   OPEN_MODE -------------------- READ WRITE

 

 

Oracle datapump error ORA-39077: unable to subscribe agent KUPC$A_1_083325450472000

Oracle datapump error ORA-39077: unable to subscribe agent KUPC$A_1_083325450472000

 

During import we faced following error.

Error Details:-

D:\oracle\product\19.3.0\dbhome_1\bin>impdp HOSPITAL/HOSPITAL@ORCL dumpfile=EXP_HOSPITAL_20231113140110_A.DMP logfile=IMP_HOSPITAL_20231113140110_A.LOG directory=dumps   Import: Release 19.0.0.0.0 - Production on Mon Nov 13 16:59:16 2023 Version 19.13.0.0.0   Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.   Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production ORA-31626: job does not exist ORA-31638: cannot attach to job SYS_IMPORT_FULL_01 for user HOSPITAL ORA-06512: at "SYS.KUPV$FT", line 1142 ORA-06512: at "SYS.KUPV$FT", line 1744 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPV$FT_INT", line 498 ORA-39077: unable to subscribe agent KUPC$A_1_165917326000000 to queue "KUPC$C_1_20231113165917_0" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPC$QUE_INT", line 294 ORA-00972: identifier is too long ORA-06512: at "SYS.DBMS_AQADM_SYS", line 9306 ORA-06512: at "SYS.DBMS_PRVTAQIS", line 1873 ORA-06512: at "SYS.DBMS_PRVTAQIS", line 3802 ORA-06512: at "SYS.DBMS_RULE_ADM", line 296 ORA-06512: at "SYS.DBMS_RULEADM_INTERNAL", line 106 ORA-24000: invalid value "SYS"."KUPC$C_1_20231113165917_0$10471", RULE SET should be of the form [SCHEMA.]NAME ORA-00972: identifier is too long ORA-06512: at "SYS.DBMS_RULEADM_INTERNAL", line 109 ORA-06512: at "SYS.DBMS_RULEADM_INTERNAL", line 97 ORA-06512: at "SYS.DBMS_RULE_ADM", line 290 ORA-06512: at "SYS.DBMS_PRVTAQIS", line 3757 ORA-06512: at "SYS.DBMS_PRVTAQIS", line 3709 ORA-06512: at "SYS.DBMS_PRVTAQIS", line 1756 ORA-06512: at "SYS.DBMS_PRVTAQIS", line 1516 ORA-06512: at "SYS.DBMS_AQADM_SYS", line 9900 ORA-06512: at "SYS.DBMS_AQADM_SYS", line 9269 ORA-06512: at "SYS.DBMS_AQADM", line 881 ORA-06512: at "SYS.KUPC$QUE_INT", line 267 ORA-06512: at "SYS.KUPC$QUE_INT", line 1360 ORA-06512: at line 1 ORA-06512: at "SYS.KUPC$QUEUE_INT", line 65 ORA-06512: at "SYS.KUPV$FT_INT", line 465 ORA-06512: at "SYS.KUPV$FT", line 1664

Cause:-

Once the sequence SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_N exceeds 10000, the name of a rule set then exceeds 30 characters, which should not be a problem any more with 19c version.

The max identifier length with DB compatibility set to < 12.2  is  30
The max identifier length with DB compatibility set to >= 12.2  is  128

The issue in this case was caused due to compatible setting 11.2.0.4.0, in which case the name of a rule set then cannot exceed 30 characters.

 

Solution:-

 

Since our database was of version 19c which was upgraded from 11.2.0.4 but the compatible was still set to “11.2.0.4.0”. Because of this the issue was present.

After changing the compatible parameter to “19.0.0” it was resolved

 

SQL> alter system set compatible=’19.0.0’ scope=spfile;

ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

 Env:-

DB : 12.1.0.2

GI: 19.0.0(Oracle restart)

OS: OEL 7

DB Type: Standalone Database

 

During execution of  rman duplicate database  following error is observed.

RMAN> run{ allocate auxiliary channel C1 DEVICE TYPE DISK; allocate auxiliary channel C2 DEVICE TYPE DISK; allocate auxiliary channel C3 DEVICE TYPE DISK; allocate auxiliary channel C4 DEVICE TYPE DISK; duplicate database to 'E1THS' backup location '/u01/rman18062021/databkp' nofilenamecheck; }

 

Error details:-

RMAN-03015: error occurred in stored script Memory Script RMAN-03009: failure of sql command on clone_default channel at 11/07/2022 02:29:26 RMAN-20000: abnormal termination of job step RMAN-11003: failure during parse/execution of SQL statement: alter system reset  db_unique_name scope=spfile RMAN-11001: Oracle Error: ORA-32010: cannot find entry to delete in SPFILE RMAN-03015: error occurred in stored script Memory Script RMAN-03009: failure of sql command on clone_default channel at 11/07/2022 02:29:06 RMAN-20000: abnormal termination of job step RMAN-11003: failure during parse/execution of SQL statement: alter system set  db_unique_name =  'E1THS' comment= 'Modified by RMAN duplicate' scope=spfile RMAN-11001: Oracle Error: ORA-32017: failure in updating SPFILE ORA-65500: could not modify DB_UNIQUE_NAME, resource exists RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 11/07/2022 02:29:26 RMAN-05501: aborting duplication of target database

 

Rationale:-

This issue is reported as Bug 20977794 in MOS for Oracle 12.1.0.2 version.The error is observed during execution of rman duplicate database where any attempt to change DB_UNIQUE_NAME for database when a CRS resource for it exists. As RMAN tries to make a change to db_unique_name, this error is observed.

 

Solution :-

Remove the existing entry to database which is going to be cloned from CRS registry

[oracle@server004 dbs]$ srvctl config database E1KRS E1THS   [oracle@server004 dbs]$ srvctl remove database -db E1THS Remove the database E1THS? (y/[n]) y

 

Now rerun the Rman duplicate command , it should work fine.

 

 

ORA-00845: MEMORY_TARGET not supported on this system

ORA-00845: MEMORY_TARGET not supported on this system
Problem Description:-
While creating a startup database using dbca the database creation GUI gives error message in a pop up window,
ORA-00845: MEMORY_TARGET not supported on this system
from where you can ignore the error message.
The similar scenario also occurs whenever you try to start your database using startup command, shows error message like below.

SQL> STARTUP
ORA-00845: MEMORY_TARGET not supported on this system

Cause of the Problem
•Starting from Oracle 11g the automatic memory management feature is now defined with parameter MEMORY_TARGET and MEMORY_MAX_TARGET.

•On linux file system the shared memory need to be mounted on /dev/shm directory on the operating system.

•And the size of /dev/shm needs to be greater than MEMORY_TARGET or MEMMORY_MAX_TARGET.

•The AMM (Automatic Memory Management) now in 11g manages both SGA and PGA together by MMAN process.

•The MEMORY_TARGET parameter in 11g comes for (SGA_TARGET+PGA_AGGREGATE_TARGET) which was in 10g.

•And MEMORY_MAX_TARGET parameter in 11g comes instead of SGA_MAX_TARGET parameter which was in 10g.

•The ORA-00845:can arises for the following two reasons on linux system.

1)If the shared memory which is mapped to /dev/shm directory is less than the size of MEMORY_TARGET or MEMORY_MAX_TARGET.
or,
2)If the shared memory is not mapped to /dev/shm directory.

olution of the Problem
Make sure /dev/shm is properly mounted. You can see it by,
#df -h or df -Th
The output should be similar like
$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2             8.7G  4.7G  3.6G  57% /
shmfs                 1G    512M 512M  50% /dev/shm


We see here for /dev/shm we have assigned 1G memory. Now if you set MEMORY_TARGET more than 1G then above ORA-845 will arise. For example if you have MEMORY_TARGET or MEMORY_MAX_TARGET set to 12G then you can mount shared memory to 13g like below.
As a root user,
# mount -t tmpfs shmfs -o size=20g /dev/shm
In order to make the settings persistence so that it will affect after restarting machine add an entry in /etc/fstab similar to the following:
vi /etc/fstab
shmfs /dev/shm tmpfs size=20g 0 0

:wq

#alter system set memory_target=20G scope=spfile;
and then bounce the database to affect the changes.