BARMAN archiver errors: FAILED (duplicates: xxx)

 One of our PostgreSQL cluster BARMAN backup was constantly getting "archiver errors: FAILED (duplicates: xxx)" warning via "barman check serverx" command. Also we saw following warnings in barman logfile;

2021-05-18 09:57:02,365 [13875] barman.wal_archiver INFO:       Error: 00000001000000630000005B is already present in server serverx. File moved to errors directory.

 We realized that our server admin was rebooted serverx without notifying us a week ago. So we did'nt properly shut down postgresql cluster.

While investigating the problem we noticed that; everytime serverx postgresql cluster switches a wal file, it tries to send every wal file under pg_wal directory. So duplicate wal files send to barman and they move to "errors" directory. This happens also manually executing switch wal command: "SELECT pg_switch_wal();"

[EN] ORA-27303: additional information: startup egid = 1000 (oinstall), current egid = 1300 (dba)

On one of our test database server we have 3 standalone databases. On 2 of them we saw following alertlog warnings and they were both down.

Thu Nov 29 11:04:27 2018
Errors in file /u02/app/oracle/diag/rdbms/dbx/DBX/trace/DBX_j000_38928500.trc:
ORA-27140: attach to post/wait facility failed
ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: Not owner
ORA-27302: failure occurred at: skgpwinit6
ORA-27303: additional information: startup egid = 1000 (oinstall), current egid = 1300 (dba)
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process

[EN] Do not delete /tmp/.oracle or /var/tmp/.oracle folder contents

Today our RMAN backups suddenly started to get the following errors on a specific server.

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row

RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows

ORACLE error from target database:

ORA-29701: unable to connect to Cluster Synchronization Service

 When we check the status of the css, we saw the following warning:

[oracle@hostx:/home/oracle:]crsctl stat res -t
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Status failed, or completed with errors. 

[EN] Cleaning SYSAUX Optimizer Statistics History

When new optimizer statistics collected for a database object, old statisitcs are sent to SYSAUX tablespace. This information is stored in "Server Manageability - Optimizer Statistics History" component of SYSAUX tablespace.

By this feature we can use DBMS_STATS.RESTORE... procedures.

The default retention for Optimizer Statistics History is 31 days. Old information are automaticly deleted by Oracle Database. Without any manual intervention.

But sometimes this 31 day retention may be too much. In one of my database this component was 75 GB.

--Check SYSAUX components and their sizes.

select * from v$sysaux_occupants order by space_usage_kbytes desc;

[EN] Sending HTML UTF-8 e-mail via UTL_SMTP

You can directly use SYS.UTL_SMTP package from your e-mail sending code. But for ease of use i recommend creating the following procedure. Because if you use UTL_SMTP directly from your code, you have to write UTL_SMTP package parameters again and again. With this following procedure you can easily send e-mails from your e-mail sending code.

Please carefully examine and change parts specific to your site on the following code. Like "smtp_hostname","smtp_port".

[EN] FAL[client]: Failed to request gap sequence ... FAL[client]: All defined FAL servers have been attempted.

Problem:

Because of a network problem one of our physical standby database, there was some gaps.

...
      9597          2 +ADATA/astb/archivelog/2015_11_09/thread_2_seq_9597.1827.895359343
      9600          2 +ADATA/astb/archivelog/2015_11_09/thread_2_seq_9600.1828.895360629
      9601          2 +ADATA/astb/archivelog/2015_11_09/thread_2_seq_9601.1831.895361495
      9604          2 +ADATA/astb/archivelog/2015_11_09/thread_2_seq_9604.1825.895363191
      9607          2 +ADATA/astb/archivelog/2015_11_10/thread_2_seq_9607.1824.895364061
      9608          2 +ADATA/astb/archivelog/2015_11_10/thread_2_seq_9608.1823.895364505
      9609          2 +ADATA/astb/archivelog/2015_11_10/thread_2_seq_9609.1826.895365409
...

After the network problem was solved by our system team, Standby RFS processes can succesfully get recent archived logs. But could not get earlier gap archived logs. Even not try to get them. We monitor this via V$MANAGED_STANDBY view.

When we try to restart media recovery on the standby site. We still see the following alertlog warnings.

[EN] Argument list too long

PROBLEM:

/u01/app/oracle/product/11.2.0/grid/rdbms/audit $ rm *.aud
-bash: /bin/rm: Argument list too long

SOLUTION:

/u01/app/oracle/product/11.2.0/grid/rdbms/audit $ ls -la | grep ".aud" | awk {'print "rm "$9'} > rm_audit_log.sh
ksh rm_audit_log.sh

[EN] Who is locking my user ?

Use the following steps, if someone is locking an oracle database user by entering wrong password.
Wrong password attemp limit is determined by FAILED_LOGIN_ATTEMPTS profile parameter.
So check it first. In our case we assume that it's value is 3.

ORA-01017: invalid username/password; logon dedied
ORA-01017: invalid username/password; logon dedied
ORA-01017: invalid username/password; logon dedied
ORA-28000: the account is locked

[EN] ORA-01111: name for data file n is unknown - rename to correct file

PROBLEM:

On one of our physical standby database, we noticed that media recovery was stooped.
When we try to start it again we got the following errors in altertlog;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (PRODSTB)
Fri Aug 17 09:11:05 2015
MRP0 started with pid=39, OS id=8061128
MRP0: Background Managed Standby Recovery process started (PRODSTB)
started logmerger process
Fri Aug 17 09:11:10 2015
Managed Standby Recovery starting Real Time Apply
MRP0: Background Media Recovery terminated with error 1111
Errors in file /u01/app/oracle/diag/rdbms/prodstb/PRODSTB/trace/PRODSTB_pr00_7985076.trc:
ORA-01111: name for data file 24 is unknown - rename to correct file
ORA-01110: data file 24: '/u01/app/oracle/product/11.2.0.4/db/dbs/UNNAMED00024'
ORA-01157: cannot identify/lock data file 24 - see DBWR trace file
ORA-01111: name for data file 24 is unknown - rename to correct file
ORA-01110: data file 24: '/u01/app/oracle/product/11.2.0.4/db/dbs/UNNAMED00024'
Managed Standby Recovery not using Real Time Apply
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (PRODSTB)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION

[EN] ORA-00600: internal error code, arguments: [kcbtema_10], [1], [], [], [], [], [], []

ERROR:

During startup we got the following error. The version of the database is; 10.2.0.5.

SQL> startup
ORACLE instance started.

Total System Global Area 3372220416 bytes
Fixed Size                  2071640 bytes
Variable Size            1291846568 bytes
Database Buffers         2046820352 bytes
Redo Buffers               31481856 bytes
Database mounted.
ORA-00600: internal error code, arguments: [kcbtema_10], [1], [], [], [], [],[], []


SQL>

Also we noticed the following entires in alertlog;

[EN] RMAN connection error: ORA-28041: Authentication protocol internal error

$ rman target rman/xxx@db1;

Recovery Manager: Release 10.2.0.5.0 - Production on Tue May 27 18:44:36 2014

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

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04005: error from target database: 

ORA-28041: Authentication protocol internal error

We got this error after applying PSU12 to one of our 10.2.0.5 production database. It was on a HP-UX Itanium 64 bit server. We applied the PSU successfully, but later we noticed that it gave some WARNINGs.

Here are the warnings;

[EN] ORA-19706: invalid SCN

ERROR:

During a select through a database link we got the following error;

===> select sysdate from dual@TARGET_DBX
select sysdate from dual@TARGET_DBX
                         *
Error at line 1
ORA-19706: invalid SCN
ORA-02063: önceki line, kaynağı TARGET_DBX Script Terminated on line 1.

Also we noticed the following message in the alert.log;

*********************************************************************************
Rejected the attempt to advance SCN over limit by 8 hours worth to 0x0ca5.1y0772c8, by distributed transaction remote logon, remote DB: TARGET_DBX.WORLD.
 Client info : DB logon user USERX, machine hostx program JDBC Thin Client, and OS user app_usr1 *********************************************************************************

[EN] ORA-28030: Server encountered problems accessing LDAP directory service

After IDM entegration, we got

"ORA-28030: Server encountered problems accessing LDAP directory service"

error during logon to database via TOAD.

[EN] ORA-00245 control file backup failed; target is likely on a local file system

------------------
-- ERROR --
------------------

At the end of a full backup of our database we got the following error during control file and spfile file autobackup;

piece handle=TRYY_ARCH.9up06jbt_1_1 tag=TAG20140209T130029 comment=API Version 2.0,MMS Version 1.1.0.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:10
channel ORA_SBT_TAPE_2: finished piece 1 at 09-FEB-14
piece handle=TRYY_ARCH.9vp06jc0_1_1 tag=TAG20140209T130029 comment=API Version 2.0,MMS Version 1.1.0.0
channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:07
Finished backup at 09-FEB-14

Starting Control File and SPFILE Autobackup at 09-FEB-14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03015: error occurred in stored script GLOBAL_TRYY_FULL_BACKUP
RMAN-03009: failure of Control File and SPFILE Autobackup command on ORA_SBT_TAPE_1 channel at 02/09/2014 13:00:58
ORA-00245: control file backup failed; target is likely on a local file system

Recovery Manager complete.

[EN] ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

-- ERROR --

SQL> drop user GENX cascade;
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

-- SOLUTION --

SQL> select * from dba_queue_tables where owner = 'GENX';
no rows selected

SQL> select * from dba_queues where owner = 'GENX';
no rows selected

[EN] Solaris IPMP group fail-over consistency check Failure - 11.2.0.3 Cluster GI installation prerequest check phase

--------------
-- ERROR --
--------------

During 11.2.0.3 cluster GI installation prerequest check phase, we got the following warning on both (2) nodes:

INFO: INFO: Solaris IPMP group fail-over consistency check: This is a check to verify the current selection of public and private network classifications is consistent with network interfaces in fail-over dependency of an IPMP group
INFO: INFO: Severity:CRITICAL
INFO: INFO: OverallStatus:VERIFICATION_FAILED

There was 2 interfaces configured for public interface on both nodes.

--node1
vnet0: flags=1000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index 2
       inet 10.86.233.11 netmask ffffffe0 broadcast 10.86.233.31
       groupname net1
vnet1: flags=69000842<BROADCAST,RUNNING,MULTICAST,IPv4,NOFAILOVER,STANDBY,INACTIVE> mtu 0 index 3
       inet 0.0.0.0 netmask 0 
       groupname net1
--node2
vnet0: flags=1000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index 2
        inet 10.86.233.12 netmask ffffffe0 broadcast 10.86.233.31
        groupname net1
vnet1: flags=69000842<BROADCAST,RUNNING,MULTICAST,IPv4,NOFAILOVER,STANDBY,INACTIVE> mtu 0 index 3
        inet 0.0.0.0 netmask 0 
        groupname net1

When active/passice IPMP configured, you got this error during 11.2.0.3 cluster GI installation prerequest check phase. But we can ignore this warning and continue installation. After installation completes you can fix this with the following steps:

[EN] ORA-00600: internal error code, arguments: [kcfrbd_3], [239], [3652081], [1], [2949120], [2949120], [], []

ERROR:

SQL> alter table INFRA.TBL_XXX drop partition PART_25;
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcfrbd_3], [239], [3652081], [1], [2949120], [2949120], [], []

SOLUTION:

If the datafile with number 239 belong to the active UNDO tablespace, then try to reduce the size of this datafile. And then increase to its original size if you like. If you can not  reduce the size of this datafile; then create a new UNDO tablespace, switch to this new UNDO tbalespace and then drop the old one.
This should fix the problem.

If not; as a workaround you can first move and then drop the partition.

[EN] ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []

ERROR:

SQL> alter table INFRA.TABLEXXX split partition PART15 at (7030081) into (partition PART16 tablespace TBS_XXX, partition  PART15);
ERROR at line 1:
ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []

SOLUTION:
alter session set events '14525 trace name context forever, level 1';
SQL> alter table INFRA.TABLEXXX split partition PART15 at (7030081) into (partition PART16 tablespace TBS_XXX, partition  PART15);

[TR] BLOCK CHANGE TRACKING

-----------------------------------------
-- BLOCK CHANGE TRACKING ---
-----------------------------------------

-----------------
-- Genel Bilgi --
-----------------

"Block Change Tracking" özelliği; incremental backup stratejisinde, incremental backupları hızlandırmak için kullanılır. Aksi halde incremental yedekleri alınan dbf'ler komple okunur. Block change tracking dosyası binay bir dosyadır ve ister primary ister standby db'lerde aktif hale getirilebilir. Yalnız standby db'lerde kullanılabilmesi için, "Oracle Active Data Guard" lisansının satın alınmış olması gerekir. Varsayılanda
kapalı olarak gelir. Etkisi özellikle çok fazla blok değişikliği olmayan db'lerde daha ciddidir. RMAN komutlarında değişiklik gerektirmez.

bct dosyasında sadece "BACKUP INCREMENTAL ..." komutuyla alınmış yedeklerin bilgisi tutulur. Yani;
INCREMENTAL LEVEL 0
INCREMENTAL LEVEL 1 DIFFERENTIAL
INCREMENTAL LEVEL 1 CUMULATIVE
Diğer yedekler tutulmaz.

[EN] 11G RAC ARCHIVELOG/NOARCHIVELOG MODE TRANSITION

ARCHIVELOG/NOARCHIVELOG mode transition is a little different in a RAC db than a standalone db.

You can see a transition example below;

11G NOARCHIVELOG to ARCHIVELOG mode transition


[DDD1]/u01/app/oracle/admin $ sqlplus / as sysdba
SQL> alter system set log_archive_dest_1= 'LOCATION=+ARCHIVE' scope=SPFILE sid='*';
SQL> alter system set log_archive_format = 'ddd_%t_%s_%r.arc' scope=SPFILE sid='*';