Archives February 2022

CRS-601: Internal errorRC: 4, File: clsModifyParser.cpp, Line: 402CRS-4000: Command Modify failed, or completed with errors.

Cause:

Syntax parse error

[root@x3dbzx36 u01]# crsctl modify resource ora.diskmon -attr “ENABLED=1”
CRS-601: Internal error
RC: 4, File: clsModifyParser.cpp, Line: 402
CRS-4000: Command Modify failed, or completed with errors.

Action:

Correct the syntax. Here double quotes are with different font and hence OS could not parse it

crsctl modify resource ora.diskmon -attr "ENABLED=1"
PRVF-5507 : NTP daemon or service is not running on any node but NTP configuration file exists on the following node(s): node1,node2  – Cause:  The configuration file was found on at least one node though no NTP daemon or service was running.

Cause:

While installation of cluster, ntpd is useful to synchronize the time with the cluster

Action:

If it is non production system, then we can rely on CTSS cluster service to synchronize the time with cluster. Hence to overcome this error, as workaround – ntpd can be disable unless it is a critical system. Otherwise ntpd needs to be configured

[root@x3dbzx69 u01]# service ntpd stop
Shutting down ntpd:                                        [FAILED]
[root@x3dbzx69 u01]# chkconfig ntpd off
[root@x3dbzx69 u01]# mv /etc/ntp /etc/ntp.bkp
[root@x3dbzx69 u01]# mv /etc/ntp.conf /etc/ntp.conf.bkp

CLONE PDB IN LOCAL CDB USING CONVERT IN FILESYSTEM WITH OMF ENABLED ORACLE 19c

Cloning a PDB is often a requirement in oracle database since the launch of multitenant from 12c. But as days pass on, in the recent oracle versions, it is compulsory to have multitenant systems due to containerization of all applications to utilize all the system resources efficiently

Source CDB: DB9ZX | Destination CDB: DB9ZX (Both 19c)

In this post, we will perform PDB clone in the same CDB with OMF enabled datafiles in filesystem

Prechecks:

Check if archive log mode is enabled. If not enable it.

kIsH@Xhydra<>select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

Check if local undo is enabled.

kIsH@Xhydra<>select * from database_properties where PROPERTY_NAME like '%UNDO%';

PROPERTY_NAME
--------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
LOCAL_UNDO_ENABLED
TRUE
true if local undo is enabled

Clone PDB ZXPDB1 ===> ZXPDB5

Check the status of PDB

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ZXPDB1                         READ WRITE NO
         4 ZXPDB2                         READ WRITE NO
         5 ZXPDB3                         READ WRITE NO
         6 ZXPDB4                         READ WRITE NO

Check the current container

SQL> show con_name

CON_NAME
------------------------------
ZXPDB1

Check the datafiles under PDB

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/apps01/oradata/DB9ZX/D8F8C25E9C2EFAD3E055000000000001/datafile/o1_mf_system_k1o
wybbn_.dbf

/apps01/oradata/DB9ZX/D8F8C25E9C2EFAD3E055000000000001/datafile/o1_mf_sysaux_k1o
wybd5_.dbf

/apps01/oradata/DB9ZX/D8F8C25E9C2EFAD3E055000000000001/datafile/o1_mf_undotbs1_k
1owybd9_.dbf

/apps01/oradata/DB9ZX/D8F8C25E9C2EFAD3E055000000000001/datafile/o1_mf_users_k1ow
zxpw_.dbf

Restart the PDB in open read only mode to restrict any writes to the source PDB

SQL> shu immediate;
Pluggable Database closed.
SQL> startup open read only;
Pluggable Database opened.

Again change the container to CDB root because cloning cannot be performed from PDB

SQL> alter session set container=CDB$ROOT;

Session altered.

Create the clone PDB using the below syntax. Here FILE_NAME_CONVERT is the parameter which convert the source datafile location and files to a new directory for new PDB.

If a new directory for the Clone PDB datafiles are required, then create a new directory else skip this step

Note:ORA-16000: database or pluggable database open for read-only access error will be thrown if you try to clone PDB in PDB itself

ORA-01276: Cannot add file .File has an Oracle Managed Files file name. error will be thrown if base directory is specified to convert since database is OMF enabled (‘/apps01/oradata/DB9ZX’,’/apps01/oradata/DBZXPDB5′)

SQL> create pluggable database ZXPDB5
  2  from
  3  ZXPDB1 FILE_NAME_CONVERT=('/apps01/oradata/DB9ZX/D8F8C25E9C2EFAD3E055000000000001/datafile/o1_mf_','/apps01/oradata/DBZXPDB5');

Pluggable database created.

Change the session to source PDB and restart the PDB in open read write mode

SQL> alter session set container=ZXPDB1;

Session altered.

SQL> shu immediate;
Pluggable Database closed.
SQL> startup;
Pluggable Database opened.

Set the container to new cloned PDB and startup PDB in open read write mode

SQL> alter session set container=ZXPDB5;

Session altered.

SQL> startup;
Pluggable Database opened.
SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ZXPDB1                         READ WRITE NO
         4 ZXPDB2                         READ WRITE NO
         5 ZXPDB3                         READ WRITE NO
         6 ZXPDB4                         READ WRITE NO
         7 ZXPDB5                         READ WRITE NO

Check the cloned datafiles location

SQL> !ls -lrt /apps01/oradata/DBZXPDB5*
-rw-r-----. 1 oracle oinstall   5251072 Feb 27 03:06 /apps01/oradata/DBZXPDB5users_k1owzxpw_.dbf
-rw-r-----. 1 oracle oinstall  37756928 Feb 27 03:24 /apps01/oradata/DBZXPDB5temp_k1owybdc_.dbf
-rw-r-----. 1 oracle oinstall 346038272 Feb 27 03:29 /apps01/oradata/DBZXPDB5sysaux_k1owybd5_.dbf
-rw-r-----. 1 oracle oinstall 104865792 Feb 27 03:30 /apps01/oradata/DBZXPDB5undotbs1_k1owybd9_.dbf
-rw-r-----. 1 oracle oinstall 283123712 Feb 27 03:30 /apps01/oradata/DBZXPDB5system_k1owybbn_.dbf
[DBT-10011] Incorrect ownership/permissions detected for the file (/u01/app/oracle/product/21.0.0/dbhome_1/bin/oradism).

Cause:

No permission to access binary file in oracle home during installation

Cause - Following nodes does not have required file ownership/permissions: Node :dbzx21 PRVG-11960 : Set user ID bit is not set for file "/u01/app/oracle/product/21.0.0/dbhome_1/bin/oradism" on node "dbzx21". PRVG-2031 : Owner of file "/u01/app/oracle/product/21.0.0/dbhome_1/bin/oradism" did not match the expected value on node "dbzx21". [Expected = "root(0)" ; Found = "oracle(54321)"]   Action - Run the Oracle Home root script as the "root" user to fix the permissions.

Action:

Provide root ownership to the file by running root.sh script

[root@DBZX21 apps01]# /u01/app/oracle/product/21.0.0/dbhome_1/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/21.0.0/dbhome_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
[root@DBZX21 apps01]# ls -lrt /u01/app/oracle/product/21.0.0/dbhome_1/bin/oradism
-rwsr-x--- 1 root oinstall 1867552 Jul 28  2021 /u01/app/oracle/product/21.0.0/dbhome_1/bin/oradism
CRS-4402: The CSS daemon was started in exclusive mode but found an active CSS d aemon on node 1, number 1, and is terminating

Cause:

The error occur during 11g grid installation. When root.sh script is invoked on node 2, oracle finds that node 1 is started in exclusive mode and need node 2 to join the cluster. Hence oracle restarts the cluster to join.

Creating trace directory
User ignored Prerequisites during installation
Installing Trace File Analyzer
OLR initialization - successful
Adding Clusterware entries to inittab
CRS-4402: The CSS daemon was started in exclusive mode but found an active CSS d                                                                  aemon on node x3dbx01, number 1, and is terminating
An active cluster was found during exclusive startup, restarting to join the clu                                                                  ster
Configure Oracle Grid Infrastructure for a Cluster ... succeeded

Action:

root.sh script is succeeded. Ignore the error

M2Crypto.SSL.SSLError: tlsv1 alert protocol version

The error can be solved by removing existing yum repository files. Take a backup of the repos files to a backup directory

[root@192 ahf]# cp /etc/yum.repos.d/* /yumrepo/
[root@192 ahf]# rm -rf /etc/yum.repos.d/* 

Clean up using yum

[root@192 ahf]# yum clean all
Loaded plugins: rhnplugin, security
Cleaning up Everything

Try executing yum now, it works

[root@192 ahf]# yum install net-snmp-utils-5.7.2-43.el7.x86_64.rpm -y
Loaded plugins: rhnplugin, security
This system is not registered with ULN.
You can use up2date --register to register.
ULN support will be disabled.
Setting up Install Process
No package net-snmp-utils-5.7.2-43.el7.x86_64.rpm available.
Nothing to do
INCREASE THE ABBREVIATED OR COLLAPSED STRING LENGTH OF STRACE OUTPUT

I was using strace to troubleshoot an error but the strings are not printed full in the output

[root@exdbxadm02 oracle]#strace mycommand

write(6, "Cell: o/123 could not ..., 59) = 59
close(6)                                = 0
gettimeofday({1645098549, 250207}, NULL) = 0
gettimeofday({1645098549, 250259}, NULL) = 0
open("/dev/raw", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = -1 ENOENT (No such file or directory)
close(5)                                = 0
close(4)                                = 0
brk(0x38bc000)                          = 0x38bc000
gettimeofday({1645098549, 250560}, NULL) = 0
munmap(0x7fe0423f0000, 143360)          = 0
exit_group(0)                           = ?

To expand the strings, use strace with -s option with a string length greater than default which is 32

[root@exdbxadm02 oracle]#strace -s 900 mycommand

write(6, "Cell: o/123  could not be opened during discovery\n", 59) = 59
close(6)                                = 0
gettimeofday({1645098549, 250207}, NULL) = 0
gettimeofday({1645098549, 250259}, NULL) = 0
open("/dev/raw", O_RDONLY|O_NONBLOCK|O_DIRECTORY) = -1 ENOENT (No such file or directory)
close(5)                                = 0
close(4)                                = 0
brk(0x38bc000)                          = 0x38bc000
gettimeofday({1645098549, 250560}, NULL) = 0
munmap(0x7fe0423f0000, 143360)          = 0
exit_group(0)                           = ?
SQL TO CHECK STATISTICS PREFERENCES ORACLE

To check the optimizer statistics parameter settings for DBMS_STATS, this query will be helpful

select 'AUTOSTATS_TARGET =====> '||DBMS_STATS.GET_PREFS('AUTOSTATS_TARGET')||'' as "STATS_PARAM" from dual
 union all
select 'CASCADE =====> '||DBMS_STATS.GET_PREFS('CASCADE')||'' from dual
 union all
select 'CONCURRENT =====> '||DBMS_STATS.GET_PREFS('CONCURRENT')||'' as "STATS_PARAM" from dual
 union all
select 'DEGREE =====> '||DBMS_STATS.GET_PREFS('DEGREE')||'' from dual
 union all
select 'ESTIMATE_PERCENT =====> '||DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT')||'' from dual
 union all
select 'GRANULARITY =====> '||DBMS_STATS.GET_PREFS('GRANULARITY')||'' from dual
 union all
select 'INCREMENTAL =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL')||'' from dual
 union all
select 'INCREMENTAL_LEVEL =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL_LEVEL')||'' from dual
 union all
select 'INCREMENTAL_STALENESS =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL_STALENESS')||'' from dual
 union all
select 'METHOD_OPT =====> '||DBMS_STATS.GET_PREFS('METHOD_OPT')||'' from dual
 union all
select 'OPTIONS =====> '||DBMS_STATS.GET_PREFS('OPTIONS')||'' from dual
 union all
select 'STALE_PERCENT =====> '||DBMS_STATS.GET_PREFS('STALE_PERCENT')||'' from dual;

For specific table

select 'AUTOSTATS_TARGET =====> '||DBMS_STATS.GET_PREFS('AUTOSTATS_TARGET','KISH','XSAL1')||'' as "STATS_PARAM" from dual
 union all
select 'CASCADE =====> '||DBMS_STATS.GET_PREFS('CASCADE','KISH','XSAL1')||'' from dual
 union all
select 'CONCURRENT =====> '||DBMS_STATS.GET_PREFS('CONCURRENT','KISH','XSAL1')||'' as "STATS_PARAM" from dual
 union all
select 'DEGREE =====> '||DBMS_STATS.GET_PREFS('DEGREE','KISH','XSAL1')||'' from dual
 union all
select 'ESTIMATE_PERCENT =====> '||DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT','KISH','XSAL1')||'' from dual
 union all
select 'GRANULARITY =====> '||DBMS_STATS.GET_PREFS('GRANULARITY','KISH','XSAL1')||'' from dual
 union all
select 'INCREMENTAL =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL','KISH','XSAL1')||'' from dual
 union all
select 'INCREMENTAL_LEVEL =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL_LEVEL','KISH','XSAL1')||'' from dual
 union all
select 'INCREMENTAL_STALENESS =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL_STALENESS','KISH','XSAL1')||'' from dual
 union all
select 'METHOD_OPT =====> '||DBMS_STATS.GET_PREFS('METHOD_OPT','KISH','XSAL1')||'' from dual
 union all
select 'OPTIONS =====> '||DBMS_STATS.GET_PREFS('OPTIONS','KISH','XSAL1')||'' from dual
 union all
select 'STALE_PERCENT =====> '||DBMS_STATS.GET_PREFS('STALE_PERCENT','KISH','XSAL1')||'' from dual;
SHELL SCRIPT TO KILL STATISTICS JOB IF IT EXCEEDS A TIME AUTOMATICALLY ORACLE

Use this script to kill the statistics running sessions automatically by setting the script in crontab during the scheduled window

#!/bin/bash

trap 'rm -rf /home/oracle/oratab_new /home/oracle/STAT_KILL.sql' EXIT

#For logging purpose
_LOG_0()
{
echo "*************************************$1"
}

#Set the environment variables
_SET_ENV_1()
{
cat /etc/oratab|grep -v '#'|grep -v '^$' > /home/oracle/oratab_new
while read x
   do
     IFS=':' read -r -a array <<< $x
                ORACLE_SID="${array[0]}"
                ORACLE_HOME="${array[1]}"
                _LOG_0
                echo "ENVIRONMENT VARIABLES SET"
                _LOG_0
                echo $ORACLE_SID
                echo $ORACLE_HOME
                export PATH=$PATH:$ORACLE_HOME/bin
   done < /home/oracle/oratab_new
}

_CHECK_DB_STATUS_2()
{
exit|sqlplus sys/password|grep 'ORA-01034'
if [ $? -eq 0 ]
 then
     echo "DB check failed:$ORACLE_SID is down.Start the database and retry"
 else
     _LOG_0
     echo "DB check passed:$ORACLE_SID is up"
_STAT_KILL_SCRIPT_3
_KILL_SESSION_4

fi
}
_STAT_KILL_SCRIPT_3()
{
$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' << EOF >> log_for_reference
spool STAT_KILL.sql
set heading off
set feedback off
col sid for 999999
col serial# for 999999
col elapsed_seconds for 999999
select 'ALTER SYSTEM KILL SESSION '''||sl.sid||','||sl.serial#||''' IMMEDIATE;'
from v\$session_longops sl
inner join v\$session se on (sl.sid = se.sid)
and
(sl.serial# = se.serial#)
where sl.message like '%Gather%Stat%'
  and
se.status = 'ACTIVE'
  and
sl.elapsed_seconds > 5;
spool off
EOF
STATKILL=/home/oracle/STAT_KILL.sql
chmod -R 775 $STATKILL
}



_KILL_SESSION_4()
{
while true
   do
    if [ -s $STATKILL ]
    then
        _LOG_0
        echo "STATS session is killed"
        _LOG_0
        exit|$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' @$STATKILL
    break
    else
        _LOG_0
        echo "No process to kill"
        _LOG_0
    break
    fi
   done
}
_SET_ENV_1
_CHECK_DB_STATUS_2

Just for testing purpose i executed a stats job and set the script in crontab for every second. The job got failed due to kill script after 5 seconds

SQL> exec dbms_stats.gather_database_stats();
BEGIN dbms_stats.gather_database_stats(); END;

*
ERROR at line 1:
ORA-00028: your session has been killed
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 168
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 14424
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 11319
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 11428
ORA-06512: at "SYS.DBMS_STATS", line 34983
ORA-06512: at "SYS.DBMS_STATS", line 36699
ORA-06512: at "SYS.DBMS_STATS", line 38023
ORA-06512: at "SYS.DBMS_STATS", line 38642
ORA-06512: at "SYS.DBMS_STATS", line 39786
ORA-06512: at "SYS.DBMS_STATS", line 41177
ORA-06512: at "SYS.DBMS_STATS", line 41978
ORA-06512: at "SYS.DBMS_STATS", line 42526
ORA-06512: at "SYS.DBMS_STATS", line 42597
ORA-06512: at line 1
SQL QUERY TO FIND USAGE OF FEATURE FOR LICENSING PURPOSES IN ORACLE

To find if any licensed feature is used in the database or not like AWR or partitioning or OLAP , query dba_feature_usage_statistics

set lines 200 pages 1000
col NAME for a15
col VERSION for a15
col DETECTED_USAGES for 999999
col DESCRIPTION for a20
col FEATURE_INFO for a15
select NAME,
       VERSION,
	   DETECTED_USAGES,
	   CURRENTLY_USED,
	   FIRST_USAGE_DATE,
	   LAST_USAGE_DATE,
	   DESCRIPTION,
	   FEATURE_INFO
from dba_feature_usage_statistics
where NAME like '%OLAP%' 
or 
NAME like '%Part%';

Sample output:

NAME            VERSION         DETECTED_USAGES CURRE FIRST_USA LAST_USAG DESCRIPTION          FEATURE_INFO
--------------- --------------- --------------- ----- --------- --------- -------------------- ---------------
OLAP - Analytic 11.2.0.4.0                    0 FALSE                     OLAP - the analytic
 Workspaces                                                               workspaces stored in
                                                                           the database.



SP2-0642: SQL*Plus internal error state 2131, context 0:0:0
Unsafe to proceed
OLAP - Cubes    11.2.0.4.0                    0 FALSE                     OLAP - number of cub
                                                                          es in the OLAP catal
                                                                          og that are fully ma
                                                                          pped and accessible
                                                                          by the OLAP API.

SP2-0642: SQL*Plus internal error state 2131, context 0:0:0
Unsafe to proceed
Partitioning (s 11.2.0.4.0                   22 TRUE  03-APR-21 10-FEB-22 Oracle Partitioning  1:T:RANGE::3::2
ystem)                                                                    option is being used ::::|1:I:RANGE:
                                                                           - there is at least :3::2::L