Stay at Home!! Be Safe!! Take Care!!

For Any queries, please mail us at support@funoracleapps.com

ORA-20011: Approximate NDV failed: ORA-00600: internal error code, arguments:

 ORA-20011: Approximate NDV failed: ORA-00600: internal error code, arguments


Error :

After DB upgrade , as part of R12 dbtier post-installation procedure, required to run adstats.sql and getting the following error

declare
*
ERROR at line 1:
ORA-20011: Approximate NDV failed: ORA-00600: internal error code, arguments:
[1350], [1], [23], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 25335
ORA-06512: at "SYS.DBMS_STATS", line 25877
ORA-06512: at line 3
ORA-06512: at line 33

Cause:

ORA_NLS10 variable not set.

Solution :

1. Make sure that $ORACLE_HOME/nls/data/old/cr9idata.pl executed to create $ORACLE_HOME/nls/data/9idata as per Database upgrading notes.

2. Make sure that the ORA_NLS10 environment variable is set to the full path of the nls/data/9idata directory:

echo $ORA_NLS10
export ORA_NLS10=$ORACLE_HOME/nls/data/9idata

3- If the instance was not started with ORA_NLS10 set correctly restart it.

4- Retest the issue by running adstats.sql again as it should run successfully now.

How to setup UDEV instead of ASM libraries for ASM on Linux

How to setup UDEV instead of ASM libraries for ASM on Linux


In this post I am going to share how to use UDEV on Linux server for ASM disk in place for ASMlib.
It will help to directly allot the raw disk to your asm disk group.It can be used in Red Hat, Oracle, Centos Linux variants using UDEV.

Udev is the mechanism used to create and name /dev device nodes corresponding to the devices that are present in the system. Udev uses matching information provided by sysfs with rules provided by the user to dynamically add the required device nodes.

Steps to use udev for ASM

1. Attach a raw disk to the server.

2. Use lsblk or fdiks -l command to find the new added disk names.

example:

/dev/sdg
/dev/sde
/dev/sdf
/dev/sdc
/dev/sdd

3. Find the ID_Serial for each associated disk, if we directly use the disk name it might get chnaged after the server reboot.

udevadm info --query=property /dev/sdg
udevadm info --query=property /dev/sde
udevadm info --query=property /dev/sdf
udevadm info --query=property /dev/sdc
udevadm info --query=property /dev/sdd

4. Go to cd /etc/udev/rules.d directory and add the new disk details in file 99-oracle-asmdevices.rules as below. Make sure to update ID_SERIAL with your correct value and disk owner and group should be correct based on the env. Also I will give a virtual name/symbolink for eash disk as oraasm01/02/03 etc.


ACTION==""add|change"", SUBSYSTEM==""block"", ENV{ID_SERIAL}==""36000c297a90d6a9681b1d2a5a1b88568"", GROUP=""dba"", SYMLINK+=""oraasm01"" OWNER=""oraasm"", MODE=""0660""
ACTION==""add|change"", SUBSYSTEM==""block"", ENV{ID_SERIAL}==""36000c29906aa418e9822a0947ce9ad22"", GROUP=""dba"", SYMLINK+=""oraasm02"" OWNER=""oraasm"", MODE=""0660""
ACTION==""add|change"", SUBSYSTEM==""block"", ENV{ID_SERIAL}==""36000c291823fef46b990dbc1fe7bebdf"", GROUP=""dba"", SYMLINK+=""oraasm03"" OWNER=""oraasm"", MODE=""0660""
ACTION==""add|change"", SUBSYSTEM==""block"", ENV{ID_SERIAL}==""36000c2944397980b1b7f91a3d0c99e56"", GROUP=""dba"", SYMLINK+=""oraasm04"" OWNER=""oraasm"", MODE=""0660""
ACTION==""add|change"", SUBSYSTEM==""block"", ENV{ID_SERIAL}==""36000c2954d200a2927a1e815945e603d"", GROUP=""dba"", SYMLINK+=""oraasm05"" OWNER=""oraasm"", MODE=""0660"""


5. Reload the udev rules to identify the new disks.

udevadm control --reload-rules
udevadm trigger --type=devices --action=change


Note:
# #OL5
# /sbin/udevcontrol reload_rules

# #OL6 and OL7
# udevadm control --reload-rules

# #OL5 and OL6 : Not needed for OL7
# /sbin/start_udev

6. Verify the disk permissions and permissions

ls -l /dev/oraasm05
ls -l /dev/oraasm03
ls -l /dev/oraasm04
ls -l /dev/oraasm01
ls -l /dev/oraasm02

7. Make sure the ASM disk string should be set as  asm_diskstring='/dev/ora*' to identify the disks.

8. Attach the new disks to the diskgroup in asm now.



Example:

SQL> set pages 40000 lines 120
col PATH for a30
select DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,PATH FROM V$ASM_DISK;
SQL> 
DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE    PATH
----------- ------- ------------ ------- -------- ------------------------------
          4 CACHED  MEMBER       ONLINE  NORMAL   /dev/oraasm05
          2 CACHED  MEMBER       ONLINE  NORMAL   /dev/oraasm03
          3 CACHED  MEMBER       ONLINE  NORMAL   /dev/oraasm04
          0 CACHED  MEMBER       ONLINE  NORMAL   /dev/oraasm01
          1 CACHED  MEMBER       ONLINE  NORMAL   /dev/oraasm02

How to change the Timezone for any specific OEM Agent

How to change the Timezone for any specific OEM Agent





In this post I am going to share how to change the timezone of an OEM Agent.


Steps:


1) Check Current Timezone of the agent from the OEM repository database


select target_name, timezone_region

from sysman.mgmt_targets

where target_name='funoracleapps.lab'

order by 1,2;


2) Stop the Agent having wrong timezone


emctl stop agent


3) Reset Timezone for agent

emctl resetTZ agent


It will give command to run on OEM repository like below


Oracle Enterprise Manager Cloud Control 12c Release 5

Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.

Updating /oem/oracle/agent12c/agent_inst/sysman/config/emd.properties...

Successfully updated /oem/oracle/agent12c/agent_inst/sysman/config/emd.properties.

Login as the em repository user and run the  script:

exec mgmt_target.set_agent_tzrgn('funoracleapps.lab:3872','Asia/Singapore')

and commit the changes

This can be done for example by logging into sqlplus and doing

SQL> exec mgmt_target.set_agent_tzrgn('funoracleapps.lab:3872','Asia/Singapore')

SQL> commit



4) Execute command on the OEM respository database


SQL> exec mgmt_target.set_agent_tzrgn('funoracleapps.lab:3872','Asia/Singapore')

SQL> commit


Commit complete.


5) Check the OEM Repository again


select target_name, timezone_region

from sysman.mgmt_targets

where target_name='funoracleapps.lab'

order by 1,2;


6) Start agent again


emctl start agent

Queries related to Lookup in Oracle EBS

Queries related to Lookup in Oracle EBS


Scripts:

Lookup Information:

select *
  from apps.fnd_lookup_types_vl
 Where lookup_type = '&lookup_type';


Values of  lookup:

select *
  from apps.fnd_lookup_values
 Where lookup_type = '&lookup_type';

How to Change the Interface Name in Linux

How to Change the Interface Name in Linux

Old Name: ens192
New Name: eth0

Check interface details

/etc/init.d/network status shows this output:

Configured devices:
lo eth0
Currently active devices:
lo enp192

Rename the interface 

ip link set enp192 down
ip link set enp192 name eth0
ip link set eth0 up

Move configuration file and update the values

mv /etc/sysconfig/network-scripts/ifcfg-{enp192,eth0}

sed -ire "s/NAME=\"enp192\"/NAME=\"eth0\"/" /etc/sysconfig/network-scripts/ifcfg-eth0

sed -ire "s/DEVICE=\"enp192\"/NAME=\"eth0\"/" /etc/sysconfig/network-scripts/ifcfg-eth0

How to Set or Change the Time Zone in Linux Server

How to Set or Change the Time Zone in Linux Server


1. Check  the Current Time Zone

timedatectl
  
  Local time: Mon 2020-07-06 22:22:40 +08
  Universal time: Mon 2020-07-06 14:22:40 UTC
        RTC time: Mon 2020-07-06 14:22:40
       Time zone: Asia/Singapore (+08, +0800)
     NTP enabled: yes
NTP synchronized: yes
 RTC in local TZ: no
      DST active: n/a

The output above shows that the system’s time zone is set to Singapore.

The system time zone is configured by a link i.e. /etc/localtime file to a binary time zone’s identifier in the /usr/share/zoneinfo directory.

$ ls -l /etc/localtime
lrwxrwxrwx 1 root root 36 Jul  6 22:03 /etc/localtime -> ../usr/share/zoneinfo/Asia/Singapore

2. Change  the Time Zone Now

Make sure to identify the exact time zone which needs to be set in Region/City format.

To view all available time zones, use the timedatectl command or list the files in the /usr/share/zoneinfo directory:

# timedatectl list-timezones

America/Montserrat
America/Nassau
America/New_York
America/Nipigon
America/Nome
America/Noronha


Run Command
sudo timedatectl set-timezone America/New_York
or  doing in UTC then

sudo timedatectl set-timezone UTC

To verify the change, invoke the timedatectl command again:


timedatectl
                    Local time: Mon 2020-07-06 14:28:53 UTC
  Universal time: Mon 2020-07-06 14:28:53 UTC
        RTC time: Mon 2020-07-06 14:28:53
       Time zone: UTC (UTC, +0000)
     NTP enabled: no
NTP synchronized: yes
 RTC in local TZ: yes
      DST active: n/a

--------------------------------------------------------------------------

We can also change Time Zone by Creating a Symlink(Especially used in old system)

If you are running an older Linux distribution and the timedatectl utility is not present on your system, you can change the timezone by symlinking /etc/localtime to the time zone in the /usr/share/zoneinfo directory.

Remove the current symlink or file:

sudo rm -rf /etc/localtime or  sudo unlink /etc/localtime 

Identify the timezone you want to configure and create a symlink:

sudo ln -s /usr/share/zoneinfo/UTC /etc/localtime

Verify the /etc/localtime file 

 ls -l /etc/localtime
lrwxrwxrwx. 1 root root 25 Jul  6  2020 /etc/localtime -> ../usr/share/zoneinfo/UTC

Query to find OPP database session details

Query to find OPP database session details






Query:

select s.ECID ,s.inst_id, s.SID,s.SERIAL#,p.spid,s.status,s.machine, s.ACTION, s.MODULE, s.TERMINAL,s.sql_id,s.last_call_et,s.event, s.client_info,s.PLSQL_SUBPROGRAM_ID,s.PROGRAM,s.client_identifier
, ( SELECT max( substr( sql_text , 1, 40 )) FROM gv$sql sq WHERE sq.sql_id = s.sql_id ) AS sql_text
, ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = 0) AS plsql_entry_object
, ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = plsql_entry_subprogram_id) AS plsql_entry_subprogram
, ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = 0) AS plsql_entry_object
, ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = PLSQL_SUBPROGRAM_ID) AS plsql_entry_subprogram
, 'alter system kill session ' || '''' || s.SID || ',' || s.serial# ||',@'|| s.inst_id||''''|| ' immediate;' kill_session
from gv$session s ,gv$process p
where
s.module like '%OPP%'
and p.addr=s.paddr
and p.inst_id = s.inst_id