Month: March 2021

ORA-16038: log 1 sequence# 191 cannot be archived – ORA-19809: limit exceeded for recovery files – ORA-00312: online log 1 thread 1: ‘+DATA/exdbx/onlinelog/group_1.261.1063822485’ – ORA-00312: online log 1 thread 1: ‘+FRA/exdbx/onlinelog/group_1.257.1063822491’

Cause:

RAC database instances were not starting after investigating for long time.

ORA-16038: log 1 sequence# 191 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '+DATA/exdbx/onlinelog/group_1.261.1063822485'
ORA-00312: online log 1 thread 1: '+FRA/exdbx/onlinelog/group_1.257.1063822491'
USER (ospid: 17363): terminating the instance due to error 16038
Tue Mar 30 12:12:36 2021
System state dump requested by (instance=1, osid=17363), summary=[abnormal instance termination].
System State dumped to trace file /u01/base/diag/rdbms/exdbx/exdbx1/trace/exdbx1_diag_17302_20210330121236.trc
Dumping diagnostic data in directory=[cdmp_20210330121236], requested by (instance=1, osid=17363), summary=[abnormal instance termination].
Instance terminated by USER, pid = 17363

srvctl throw error with respect to archivelog sequence and ended up with end of file on communicatio channel

[oracle@exdbadm01 ~]$ srvctl start database -d exdbx
PRCR-1079 : Failed to start resource ora.exdbx.db
CRS-5017: The resource action "ora.exdbx.db start" encountered the following error:
ORA-16038: log 1 sequence# 191 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '+DATA/exdbx/onlinelog/group_1.261.1063822485'
ORA-00312: online log 1 thread 1: '+FRA/exdbx/onlinelog/group_1.257.1063822491'
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0/grid/log/exdbadm02/agent/crsd/oraagent_oracle/oraagent_oracle.log".

CRS-2674: Start of 'ora.exdbx.db' on 'exdbadm02' failed
CRS-5017: The resource action "ora.exdbx.db start" encountered the following error:
ORA-03113: end-of-file on communication channel
Process ID: 17363
Session ID: 1 Serial number: 3
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0/grid/log/exdbadm01/agent/crsd/oraagent_oracle/oraagent_oracle.log".

CRS-2674: Start of 'ora.exdbx.db' on 'exdbadm01' failed
CRS-2632: There are no more servers to try to place resource 'ora.exdbx.db' on that would satisfy its placement policy

When i checked FRA,sufficient space was available but still error persists.

kish@exdbx<>show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +FRA
db_recovery_file_dest_size           big integer 4868M

Workaround:

After investigating on the root cause, archivelog files were directly removed using OS utility and not cleared using RMAN graceful archive delete



RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;

After running above archivelog delete commands in RMAN,issue got cleared

[oracle@exdbadm01 lib]$ srvctl stop database -d exdbx                                                                 [oracle@exdbadm01 lib]$ srvctl start database -d exdbx
[oracle@exdbadm01 lib]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 30 17:30:28 2021

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

kish@exdbx<>select status from gv$instance;

STATUS
------------
OPEN
OPEN

ORA-01119: error in creating database file ‘+DATA’ – ORA-17502: ksfdcre:4 Failed to create file +DATA – ORA-15041: diskgroup “DATA” space exhausted

Cause:

The error creeps in ,when datafile or tempfile cant be added due to space crunch on asm diskgroup

kish@exdbx<>create temporary tablespace temp2 tempfile '+DATA' size 1G;
create temporary tablespace temp2 tempfile '+DATA' size 1G
*
ERROR at line 1:
ORA-01119: error in creating database file '+DATA'
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup "DATA" space exhausted

Workaround:

Check the space for DATA diskgroup.We have 112 MB space on the diskgroup which has external redundancy.So there is no room for incoming datafile or tempfile to accomodate in DATA diskgroup

ASMCMD> lsdg --discovery
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  4194304      5760      112                0             112              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  4194304      1440     1304                0            1304              0             N  DATA2/
MOUNTED  EXTERN  N         512   4096  4194304      5040     1784                0            1784              0             N  FRA/
MOUNTED  EXTERN  N         512   4096  1048576      1440     1042                0            1042              0             Y  MGMT/

Check with your storage team to allocate the disks to the server and check disk header status in v$asm_disk

If it is exadata,then all the luns should be added as celldisks which in turn should be mapped to ASM griddisks.In my case, after storage team added 4 arrays(cell20,cell21,cell22,cell23),symbolic link should be created from the logical cylinders

[root@exceladm00 raw]# ls -lrt
total 0
lrwxrwxrwx 1 root root 9 Feb 25 01:38 cell23 -> /dev/sdad
lrwxrwxrwx 1 root root 9 Feb 25 01:38 cell22 -> /dev/sdac
lrwxrwxrwx 1 root root 9 Feb 25 01:38 cell21 -> /dev/sdab
lrwxrwxrwx 1 root root 9 Feb 25 01:38 cell20 -> /dev/sdaa

Use a shell script to generate symbolic link commands in one go

fdisk -l 2>/dev/null | grep "805 MB" | awk '{ printf "%s%02d\n", "ln -s "$2" cell", NR }'|sed "s/://"

  392  ln -s /dev/sdaa cell20
  393  ln -s /dev/sdab cell21
  394  ln -s /dev/sdac cell22
  395  ln -s /dev/sdad cell23

In storage server,check if physical disk which were allocated reflect!

CellCLI> list physicaldisk

         /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell20    /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell20          normal
         /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell21    /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell21          normal
         /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell22    /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell22          normal
         /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell23    /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell23          normal

Create celldisk from the physical disk with size clause to allocate only specific size for capacity

CellCLI> create celldisk  all;
CellDisk FD_06_stocell successfully created
CellDisk FD_07_stocell successfully created
CellDisk FD_08_stocell successfully created
CellDisk CD_cell20_stocell successfully created
CellDisk CD_cell21_stocell successfully created
CellDisk CD_cell22_stocell successfully created
CellDisk CD_cell23_stocell successfully created

Next map your asm to celldisks with griddisk creation

CellCLI> create griddisk all harddisk prefix=DATA
Cell disks were skipped because they had no freespace for grid disks: CD_cell10_stocell, CD_cell11_stocell, CD_cell12_stocell, CD_cell13_stocell, CD_cell14_stocell, CD_cell15_stocell, CD_cell16_stocell, CD_cell17_stocell, CD_cell18_stocell.
GridDisk DATA_CD_cell20_stocell successfully created
GridDisk DATA_CD_cell21_stocell successfully created
GridDisk DATA_CD_cell22_stocell successfully created
GridDisk DATA_CD_cell23_stocell successfully created

Newly added disk header status should reflect as CANDIDATE disk.Below,there are 4 candidate disks which can be added to the DATA diskgroup for space.

SQL> select name,header_status,state from v$asm_disk;

NAME                           HEADER_STATU STATE
------------------------------ ------------ --------
                               CANDIDATE    NORMAL
                               CANDIDATE    NORMAL
                               CANDIDATE    NORMAL
                               CANDIDATE    NORMAL
DATA_CD_CELL07_STOCELL         MEMBER       NORMAL
MGMT_CD_CELL15_STOCELL         MEMBER       NORMAL
FRA_CD_CELL08_STOCELL          MEMBER       NORMAL
MGMT_CD_CELL16_STOCELL         MEMBER       NORMAL
DATA_CD_CELL19_STOCELL         MEMBER       NORMAL
FRA_CD_CELL12_STOCELL          MEMBER       NORMAL
FRA_CD_CELL11_STOCELL          MEMBER       NORMAL

NAME                           HEADER_STATU STATE
------------------------------ ------------ --------
DATA_CD_CELL09_STOCELL         MEMBER       NORMAL
FRA_CD_CELL09_STOCELL          MEMBER       NORMAL
DATA_CD_CELL05_STOCELL         MEMBER       NORMAL
DATA_CD_CELL06_STOCELL         MEMBER       NORMAL
FRA_CD_CELL14_STOCELL          MEMBER       NORMAL
FRA_CD_CELL10_STOCELL          MEMBER       NORMAL
DATA_CD_CELL03_STOCELL         MEMBER       NORMAL
FRA_CD_CELL13_STOCELL          MEMBER       NORMAL
DATA_CD_CELL04_STOCELL         MEMBER       NORMAL
DATA_CD_CELL01_STOCELL         MEMBER       NORMAL
DATA_CD_CELL02_STOCELL         MEMBER       NORMAL

NAME                           HEADER_STATU STATE
------------------------------ ------------ --------
DATA_CD_CELL08_STOCELL         MEMBER       NORMAL

23 rows selected.

Use the below command with rebalance power 9 to speed up the rebalance operation.Make sure there is sufficient CPU power on your host so that 9 parallel I/O’s can be performed by ARB process.

SQL> alter diskgroup DATA add disk 'o/192.168.56.33/DATA_CD_cell2*' reba                                      lance power 9


NOTE: GroupBlock outside rolling migration privileged region
NOTE: Assigning number (2,8) to disk (o/192.168.56.33/DATA_CD_cell21_stocell)
NOTE: requesting all-instance membership refresh for group=2
NOTE: initializing header on grp 2 disk DATA_CD_CELL21_STOCELL
NOTE: requesting all-instance disk validation for group=2
Thu Mar 25 22:01:18 2021
NOTE: skipping rediscovery for group 2/0x1f0821be (DATA) on local instance.
NOTE: initiating PST update: grp = 2
Thu Mar 25 22:01:20 2021
GMON updating group 2 at 13 for pid 29, osid 9120
NOTE: PST update grp = 2 completed successfully
NOTE: membership refresh pending for group 2/0x1f0821be (DATA)
GMON querying group 2 at 14 for pid 19, osid 4885
NOTE: cache opening disk 8 of grp 2: DATA_CD_CELL21_STOCELL path:o/192.168.56.33                                      /DATA_CD_cell21_stocell
GMON querying group 2 at 15 for pid 19, osid 4885
Thu Mar 25 22:01:34 2021
SUCCESS: refreshed membership for 2/0x1f0821be (DATA)
Thu Mar 25 22:01:36 2021
SUCCESS: alter diskgroup DATA add disk 'o/192.168.56.33/DATA_CD_cell21_stocell'                                       rebalance power 9
NOTE: Attempting voting file refresh on diskgroup DATA
NOTE: Refresh completed on diskgroup DATA. No voting file found.
NOTE: starting rebalance of group 2/0x1f0821be (DATA) at power 9
Starting background process ARB0
Thu Mar 25 22:01:38 2021
ARB0 started with pid=36, OS id=9478
NOTE: assigning ARB0 to group 2/0x1f0821be (DATA) with 9 parallel I/Os

Disk are added to DATA diskgroup and datafile or tempfile to instance can be added

State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  Y         512   4096  4194304      7920     2248                0            2248              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  4194304      1440     1304                0            1304              0             N  DATA2/
MOUNTED  EXTERN  N         512   4096  4194304      5040     1752                0            1752              0             N  FRA/
MOUNTED  EXTERN  N         512   4096  1048576      1440     1042                0            1042              0             Y  MGMT/
kish@exdbx<>create temporary tablespace temp2 tempfile '+DATA2' size 100M autoextend on next 50M maxsize 1G;

Tablespace created.

Elapsed: 00:00:04.26

Does killing arch process crash the database instance?

What happen if you kill the archiver background process in oracle database? Does it kill the database or there is no impact for database? Great! Lets research

It is always good to practically test a theoritical question which are sceptical

[oracle@orcl12x ~]$ ps -ef|grep arc
oracle    3829     1  0 05:55 ?        00:00:00 ora_arc0_orcl12x
oracle    3831     1  0 05:55 ?        00:00:00 ora_arc1_orcl12x
oracle    3833     1  0 05:55 ?        00:00:00 ora_arc2_orcl12x
oracle    3835     1  0 05:55 ?        00:00:00 ora_arc3_orcl12x
oracle    3980  3361  0 05:56 pts/0    00:00:00 grep arc

I have killed all the archive process running in background.There is no archiver process running.

[oracle@orcl12x ~]$ kill -9 3829
[oracle@orcl12x ~]$ kill -9 3833
[oracle@orcl12x ~]$ kill -9 3835
[oracle@orcl12x ~]$ kill -9 3831

[oracle@orcl12x ~]$ ps -ef|grep arc
oracle    4036  3361  0 05:56 pts/0    00:00:00 grep arc

Lets check the database status

There is no impact for database on killing arch process as the process gets restart automatically once killed

[oracle@orcl12x ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 1 05:56:58 2021

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select status from v$instance;

STATUS
------------
OPEN

PLS-00201: identifier ‘DBMS_LOGSTDBY.BUILD’ must be declared

Cause:

SQL> execute dbms_logstdby.build;
BEGIN dbms_logstdby.build; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_LOGSTDBY.BUILD' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Workaround:

Check the user and status of the database.

Database should be in open mode

SQL> show user
USER is "SYS"
SQL> select status from v$instance;

STATUS
------------
MOUNTED

RMAN-00571: RMAN-00569: ERROR MESSAGE STACK FOLLOWS RMAN-06433: error installing recovery catalog – RMAN-03002: failure of register command at RMAN-06428: recovery catalog is not installed

Cause:

I face this weird error while installing recovery catalog.I have not installed catalog before and this is the first time i install catalog but not sure why this error creep in.

[oracle@orcl ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sat Mar 6 18:30:10 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1559282684)

RMAN> connect catalog rcat/rcat

connected to recovery catalog database
RMAN> create catalog;

error creating db
ORACLE error from recovery catalog database: ORA-00955: name is already used by an existing object

ORACLE error from recovery catalog database: ORA-00942: table or view does not exist

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06433: error installing recovery catalog

RMAN> register database;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of register command at 03/06/2021 18:28:49
RMAN-06428: recovery catalog is not installed

RMAN> exit

Workaround:

Drop the existing catalog and recreate catalog database from RMAN

RMAN> drop catalog;

recovery catalog owner is RCAT
enter DROP CATALOG command again to confirm catalog removal

RMAN> drop catalog;

recovery catalog dropped

RMAN> create catalog;

recovery catalog created

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

/u01/perl/bin/perl: /lib64/libc.so.6: version `GLIBC_2.14′ not found (required by /u01/perl/bin/perl)

The error occur when there is requirement for higher version of GLIBC library which mismatch with the OS version.You try to use a executable which is not compatible with the older version of OS (linux) and needs an upgrade.

[oracle@orcl u01]$ ./runInstaller
/u01/perl/bin/perl: /lib64/libc.so.6: version `GLIBC_2.14' not found (required by /u01/perl/bin/perl)

Download glibc package from official gnu forum

https://ftp.gnu.org/gnu/libc/

Search for the glibc-2.14.tar.gz file in the forum

Download the tar file and upload on the server

[root@orcl u01]# cd /home/
[root@orcl home]# ls
glibc-2.14.tar.gz  grid  oracle

Untar the file to directory

[root@orcl home]# tar xvfz glibc-2.14.tar.gz >/dev/null 2>&1

Create a directory called build inside the glibc folder which was extracted

[root@orcl home]#  cd glibc-2.14
[root@orcl glibc-2.14]# mkdir build
[root@orcl glibc-2.14]# cd build

Configure glibc and create the make file

[root@orcl build]# ../configure --prefix=/opt/glibc-2.14
checking if -g produces usable source locations for assembler-with-cpp... yes
checking for SSE4 support... yes
checking for assembler -mtune=i686 support... yes
checking for AVX support... yes
checking for -mno-vzeroupper support... no
checking for old glibc 2.0.x headers... no
checking whether -fPIC is default... no
configure: creating ./config.status
config.status: creating config.make
config.status: creating Makefile
config.status: creating config.h
config.status: executing default commands

Run make command

[oracle@orcl build]$ make
 
-I../sysdeps/ieee754/dbl-64/wordsize-64 -I../sysdeps/ieee754/dbl-64 -I../sysdeps/ieee754/flt-32 -I../sysdeps/ieee754 -I../sysdeps/generic/elf -I../sysdeps/generic -I../nptl  -I.. -I../libio -I.  -D_LIBC_REENTRANT -include ../include/libc-symbols.h   -DNOT_IN_libc=1    -o /home/oracle/glibc-2.14/build/elf/ldconfig.o -MD -MP -MF /home/oracle/glibc-2.14/build/elf/ldconfig.o.dt -MT /home/oracle/glibc-2.14/build/elf/ldconfig.o
gcc -nostdlib -nostartfiles -static -o /home/oracle/glibc-2.14/build/elf/ldconfig    /home/oracle/glibc-2.14/build/csu/crt1.o /home/oracle/glibc-2.14/build/csu/crti.o `gcc  --print-file-name=crtbegin.o` /home/oracle/glibc-2.14/build/elf/ldconfig.o /home/oracle/glibc-2.14/build/elf/cache.o /home/oracle/glibc-2.14/build/elf/readlib.o /home/oracle/glibc-2.14/build/elf/xmalloc.o /home/oracle/glibc-2.14/build/elf/xstrdup.o /home/oracle/glibc-2.14/build/elf/chroot_canon.o  -Wl,--start-group /home/oracle/glibc-2.14/build/libc.a -lgcc -lgcc_eh  -Wl,--end-group `gcc  --print-file-name=crtend.o` /home/oracle/glibc-2.14/build/csu/crtn.o
make[2]: Leaving directory `/home/oracle/glibc-2.14/elf'
make[1]: Leaving directory `/home/oracle/glibc-2.14'

Install glibc using make install

[oracle@orcldg build]$ sudo make install
rm -f /home/glibc-2.14/build/elf/symlink.list
test ! -x /home/glibc-2.14/build/elf/ldconfig || LC_ALL=C LANGUAGE=C \
          /home/glibc-2.14/build/elf/ldconfig  \
                                       /opt/glibc-2.14/lib /opt/glibc-2.14/lib
/home/glibc-2.14/build/elf/ldconfig: Can't open configuration file /opt/glibc-2.14/etc/ld.so.conf: No such file or directory
make[1]: Leaving directory `/home/glibc-2.14'

Export the library

[oracle@orcl u01]$ export LD_LIBRARY_PATH=/opt/glibc-2.14/lib:$LD_LIBRARY_PATH

Set the language variables for locale .Otherwise you might get error

[oracle@orcl /]$ localedef -i en_US -f UTF-8 en_US.UTF-8
echo "LC_ALL=en_US.UTF-8" >> /etc/environment
echo "en_US.UTF-8 UTF-8" >> /etc/locale.gen
echo "LANG=en_US.UTF-8" > /etc/locale.conf
locale-gen en_US.UTF-8