ORA-20011: Approximate NDV failed: ORA-00600: internal error code, arguments:
Himanshu
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
Himanshu
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
Himanshu
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
Himanshu
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
Himanshu
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
Himanshu
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
Himanshu
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
Subscribe to:
Posts
(
Atom
)
