Month: May 2021

Unable to login with Root user PAM: User account has expired in Solaris

When i try to login to root user after installation using putty, i face some authentication error even though i give correct password.

When i checked the shadow file i can see some alphanumeric code in root entry like this

root@solaris:/# cat /etc/shadow |grep root
root:$5$rounds=10000$LLUf8IA3$A3HZoGOAyXB6Rdx0jmNiy/3buKaZWS7eY78Aa7icucB:18777::::::

After sometime i searched over internet and find a solution to modify the shadow file only for root entry which was still not working

root::::::::

I got the below error

-bash-4.4$ su - root
Password:
Roles can only be assumed by authorized users
su: Permission denied

and

PAM: User account has expired

Then i checked /etc/user_attr file and found that root user was considered as a role but not a normal user

root@solaris:/# cat /etc/user_attr
#
# Copyright (c) 1999, 2013, Oracle and/or its affiliates. All rights reserved.
#
# The system provided entries are stored in different files
# under "/etc/user_attr.d".  They should not be copied to this file.
#
# Only local changes should be stored in this file.
#
root::::type=role

So i directly logged in to administrator account and modified the type from role to normal and it worked fine

-bash-4.4$ su - root
Password:
Oracle Corporation      SunOS 5.11      11.4    Aug 2018
root@solaris:~#

IMPACT: Oracle Solaris is not running with Spectre Vulnerability Mitigation Enabled REC-ACTION: Update the CPU with the Spectre capable microcode.

Follow the below procedure to overcome this message during solaris installation

List all the virtualbox vm’s

Here my vm name is orsol

F:\VIRTUALBOX>VBoxManage list vms

"OS8.8VirtualBox" {05f808f0-9159-453d-8b54-973c8de21bf4}
"orsol" {7c753311-9ee5-4607-a8a7-561931fa6d92}

Modify the vm with spectre control on

F:\VIRTUALBOX>VBoxManage modifyvm orsol --spec-ctrl on

OSWATCHER INSTALL

Download OSWATCHER file from the below link

https://support.oracle.com/epmos/main/downloadattachmentprocessor?attachid=301137.1:OSW_file.

Copy the file to the server using winscp and untar the OSWBB file

[oracle@orcl19x oswbb]$ tar -xvf oswbb840.tar

Go to the oswbb directory

[oracle@orcl19x oswbb]$ cd oswbb/
[oracle@orcl19x oswbb]$ ls
analysis        docs                locks           oswib.sh     sarsub.sh
archive         Example_extras.txt  ltop.sh         oswnet.sh    src
arpsub.sh       Exampleprivate.net  mpsub.sh        oswrds.sh    startOSWbb.sh
call_du.sh      genprvnet.sh        nfssub.sh       oswsub.sh    stopOSWbb.sh
call_sar.sh     gif                 OSWatcherFM.sh  piddsub.sh   tar_up_full_arc
call_uptime.sh  ifconfigsub.sh      OSWatcher.sh    pidsub.sh    tar_up_partial_
data            iosub.sh            oswbba.jar      psmemsub.sh  tmp

Check if ksh package is installed on your system

[oracle@orcl19x oswbb]$ rpm -qa|grep ksh
ksh-20120801-142.0.1.el7.x86_64

[oracle@orcl19x oswbb]$ ln -s /bin/ksh /usr/bin/ksh
ln: failed to create symbolic link ‘/usr/bin/ksh’: File exists

[oracle@orcl19x oswbb]$ ls -lrt /usr/bin/ksh
lrwxrwxrwx. 1 root root 21 Dec 13 11:21 /usr/bin/ksh -> /etc/alternatives/ksh

Sample output from oswatcher script with snapshot interval of 5 and archive data collection for 10 hours

[oracle@orcl19x oswbb]$ ./OSWatcher.sh 5 10
Setting the archive log directory to/home/oracle/oswbb/oswbb/archive

Testing for discovery of OS Utilities...
VMSTAT found on your system.
IOSTAT found on your system.
MPSTAT found on your system.
IP found on your system.
TOP found on your system.
Warning... /proc/slabinfo not found on your system. Check to see if this user ha                         s permission to access this file.
PIDSTAT found on your system.
NFSIOSTAT found on your system.
TRACEROUTE found on your system.

Discovery of CPU CORE COUNT
CPU CORE COUNT will be used by oswbba to automatically look for cpu problems

CPU CORE COUNT = 1
VCPUS/THREADS = 1

Discovery completed.

Starting OSWatcher v8.4.0  on Sat May 29 22:15:40 IST 2021
With SnapshotInterval = 5
With ArchiveInterval = 10

OSWatcher - Written by Carl Davis, Center of Expertise,
Oracle Corporation
For questions on install/usage please go to MOS (Note:301137.1)

Data is stored in directory: /home/oracle/oswbb/oswbb/archive

Starting Data Collection...

oswbb heartbeat:Sat May 29 22:15:45 IST 2021
oswbb heartbeat:Sat May 29 22:15:51 IST 2021
oswbb heartbeat:Sat May 29 22:15:56 IST 2021
oswbb heartbeat:Sat May 29 22:16:01 IST 2021
oswbb heartbeat:Sat May 29 22:16:06 IST 2021
oswbb heartbeat:Sat May 29 22:16:12 IST 2021
oswbb heartbeat:Sat May 29 22:16:17 IST 2021
oswbb heartbeat:Sat May 29 22:16:22 IST 2021
oswbb heartbeat:Sat May 29 22:16:27 IST 2021
oswbb heartbeat:Sat May 29 22:16:32 IST 2021
oswbb heartbeat:Sat May 29 22:16:38 IST 2021
oswbb heartbeat:Sat May 29 22:16:43 IST 2021
oswbb heartbeat:Sat May 29 22:16:48 IST 2021
oswbb heartbeat:Sat May 29 22:16:53 IST 2021
oswbb heartbeat:Sat May 29 22:16:59 IST 2021
***Warning. VMSTAT response is spanning snapshot intervals.
***Warning. MPSTAT response is spanning snapshot intervals.
***Warning. IOSTAT response is spanning snapshot intervals.
***Warning. PIDSTAT -d response is spanning snapshot intervals.
***Warning. PIDSTAT response is spanning snapshot intervals.
***Warning. TOP response is spanning snapshot intervals.

To run the script in background, use nohup mode

[oracle@orcl19x oswbb]$ nohup ./OSWatcher.sh 30 10 &
[1] 7682
[oracle@orcl19x oswbb]$ nohup: ignoring input and appending output to ‘nohup.out’

There are lot of reports generated by this script and choose the one for which you need to analyze

[oracle@orcl19x archive]$ java -jar /home/oracle/oswbb/oswbb/oswbba.jar -I /home/oracle/oswbb/oswbb/archive -B May 29 22:15:14 2021 -E May 29 22:16:14 2021

Validating times in the archive...


Starting OSW Analyzer V8.4.0
OSWatcher Analyzer Written by Oracle Center of Expertise
Copyright (c)  2019 by Oracle Corporation

Parsing Data. Please Wait...

Scanning file headers for version and platform info...


Parsing file orcl19x_pidstat_21.05.29.2200.dat ...

Parsing file orcl19x_iostat_21.05.29.2200.dat ...
This directory already exists. Rewriting...

Parsing file orcl19x_vmstat_21.05.29.2200.dat ...

Parsing file orcl19x_netstat_21.05.29.2200.dat ...

Parsing file orcl19x_top_21.05.29.2200.dat ...

Parsing file orcl19x_ps_21.05.29.2200.dat ...


Parsing Completed.


Enter 1 to Display CPU Process Queue Graphs
Enter 2 to Display CPU Utilization Graphs
Enter 3 to Display CPU Other Graphs
Enter 4 to Display Memory Graphs
Enter 5 to Display Disk IO Graphs
Enter 61 to Display Individual OS Process I/O RPS Graphs
Enter 62 to Display Individual OS Process I/O WPS Graphs
Enter 63 to Display Individual OS Process Percent User CPU Graphs
Enter 64 to Display Individual OS Process Percent System CPU Graphs
Enter 65 to Display Individual OS Process Percent Total CPU (User + System) Graphs
Enter 66 to Display Individual OS Process Percent Memory Graphs

Enter GP to Generate Individual Process Profile
Enter GC to Generate All CPU Gif Files
Enter GM to Generate All Memory Gif Files
Enter GD to Generate All Disk Gif Files
Enter GN to Generate All Network Gif Files

Enter L to Specify Alternate Location of Gif Directory
Enter Z to Zoom Graph Time Scale (Does not change analysis dataset)
Enter B to Returns to Baseline Graph Time Scale (Does not change analysis dataset)
Enter R to Remove Currently Displayed Graphs

Enter X to Export Parsed Data to Flat File
Enter S to Analyze Subset of Data(Changes analysis dataset including graph time scale)
Enter A to Analyze Data
Enter D to Generate DashBoard

Enter Q to Quit Program

I choose GP option to generate individual process report

Please Select an Option:GP

Ospid's Ordered by Average Percent Total CPU:

PID     Average Percent Total CPU    Command

3162                         0.12    ora_pman_orcl19
8883                         0.12    top
8542                         0.13    ora_m000_orcl19
2678                         0.13    pcscd
5232                         0.26    ora_dia0_orcl19

Specify Ospid to View (Q to exit):   8542
Generating file gif/May29221452_1622307537/8542/CPU_PERCENT_USER.gif
Generating file gif/May29221452_1622307537/8542/CPU_PERCENT_SYSTEM.gif
Generating file gif/May29221452_1622307537/8542/CPU_PERCENT_TOTAL.gif
Generating file gif/May29221452_1622307537/8542/MEMORY_PERCENT_TOTAL.gif
Generating file gif/May29221452_1622307537/8542/WPS.gif
Generating file gif/May29221452_1622307537/8542/RPS.gif
Profile has been written to the gif directory.

You will get a gif graph on the process specific CPU consumption

Similarly , look for other options for all parameters

How to check history of tablespace autoextend status in oracle

I was in a meeting call reviewing the last weeks highlights of critical activity performed on all of the oracle databases. Suddenly i got a bridge call notification from the monitoring team that tablespace got exhausted due to space crunch as the application data got a sudden growth. I was told that one of the tablespace autoextend option was disabled and tablespace was unable to extend to a max size. I tried to find the rca about the tablespace autogrowth status for a specific time. So lets look at the ways to find it !

Check the current status of USERS tablespace

SQL> select tablespace_name,autoextensible from dba_data_files;

TABLESPACE_NAME                AUT
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
EXAMPLE                        YES
USERS                          YES

Check if DB level audit is enabled

SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      NONE

SQL> show parameter enable_ddl_logging

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging                   boolean     TRUE

Enable DB level audit if not enabled

SQL> alter system set audit_trail=DB scope=spfile;

System altered.

SQL> alter system set enable_ddl_logging=true;

System altered.

Check the current time

SQL> select to_char(sysdate,'dd-mm-yy hh24:mi:ss') as B4_change from dual;

B4_CHANGE
-----------------
29-05-21 20:15:04

Disable autoextend for the datafile

20:15:14 SQL>  alter database datafile '/apps01/oradata/orcl19xusers01.dbf' autoextend off;

Database altered.

Check the tablespace autoextend status after disable

SQL> select tablespace_name,autoextensible from dba_data_files;

TABLESPACE_NAME                AUT
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
EXAMPLE                        YES
USERS                          NO

Again enable the autoextend status to check the difference

20:15:32 SQL> alter database datafile '/apps01/oradata/orcl19xusers01.dbf' autoextend on;

Database altered.

Verify the status of the tablespace

SQL> select tablespace_name,autoextensible from dba_data_files;

TABLESPACE_NAME                AUT
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
EXAMPLE                        YES
USERS                          YES

Use the below timestamp command to check the autoextend status history

20:15:19 SQL> select tablespace_name,autoextensible from dba_data_files  as of timestamp timestamp '2021-05-29 20:15:14';

TABLESPACE_NAME                AUT
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
EXAMPLE                        YES
USERS                          NO

Locate to the below directory to ddl logs

[oracle@orcl19x ~]$ cd $ORACLE_BASE/diag/rdbms/DBNAME/SID/log/ddl

[oracle@orcl19x ddl]$ pwd
/apps01/base/diag/rdbms/orcl19x/orcl19x/log/ddl

[oracle@orcl19x ddl]$ ls
log.xml

Check the ddl modification in the ddl audit logfile

<msg time='2021-05-29T20:32:15.415+05:30' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='orcl19x' host_addr='fe80::d9a1:1f74:379:20e3%enp0s3'
 version='1'>
 <txt>alter database datafile &apos;/apps01/oradata/orcl19xusers01.dbf&apos; autoextend off
 </txt>
</msg>
<msg time='2021-05-29T20:32:19.562+05:30' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='orcl19x' host_addr='fe80::d9a1:1f74:379:20e3%enp0s3'>
 <txt>alter database datafile &apos;/apps01/oradata/orcl19xusers01.dbf&apos; autoextend on
 </txt>
</msg>

ORA-32773: operation not supported for smallfile tablespace USERS

Tablespace autoextend cannot be modified for all the datafiles. So we have to specify the particular datafile to enable or disable autoextend

SQL> select tablespace_name,autoextensible from dba_data_files where tablespace_name='USERS';

TABLESPACE_NAME                AUT
------------------------------ ---
USERS                          YES

Command for tablespace will not work if it is smallfile tablespace as it can contain multiple datafiles

SQL> alter tablespace USERS autoextend off;
alter tablespace USERS autoextend off
*
ERROR at line 1:
ORA-32773: operation not supported for smallfile tablespace USERS

Below is the error code with recommendation

SQL> !oerr ora 32773
32773, 00000, "operation not supported for smallfile tablespace %s"
// *Cause: An attempt was made to perform an operation which is supported
//         only for bigfile tablespaces, e.g. resize tablespace.
// *Action: Use the appropriate clause of the ALTER DATABASE DATAFILE
//          command instead.

Check the status of tablespace if it is bigfile

SQL> select tablespace_name,bigfile from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME                BIG
------------------------------ ---
USERS                          NO

Check the physical datafile location

SQL> select file_name,TABLESPACE_NAME,autoextensible from dba_data_files where tablespace_name='USERS';

FILE_NAME            TABLESPACE_NAME                AUT
-------------------- ------------------------------ ---
/apps01/oradata/orcl USERS                          YES
19xusers01.dbf

Modify the autoextend status to ‘NO’ for specific physical datafile

SQL> alter database datafile '/apps01/oradata/orcl19xusers01.dbf' autoextend off;

Database altered.

MICROSOFT SQL SERVER 2019 SOFTWARE INSTALLATION

Go to the link and download developer edition which is free

https://www.microsoft.com/en-in/sql-server/sql-server-downloads

Select custom option

Choose the drive path on your system and click install

The required packages will be downloaded. Wait for sometime

After download, the sql server will be installed on the system

The home screen looks like this

Choose new SQL server stand alone …. from the first option

Choose specify a free edition

Accept the license

Skip this step and click on next

Ignore the warnings if it is not a production database. If the parameters are failed, then you need to investigate them

Select the instance packages which needs to be installed with database instance

Select default instance for basic installation

Click on next

Select the services that needs to be enabled as manual or automatic depending on your requirement

SQL server agent is used for monitoring and restarting the background services

SQL server database engine is the database itself which stores tables,indexes etc

Select Mixed mode and click on add to add the current user from which you logged in to system and provide the password.

Click accept. Here i chosen python hence this is an extensive step. If you dont need python packages,then dont select the package

Now we are ready to install MSSQL. Click on install

Wait for the installation to complete

Check the final summary of installation which should be marked successfull for the packages

Thats all for SQL server software installation on your system

SHELL SCRIPT TO MONITOR HIGHCPU IN LINUX

This shell script is going to help us in identifying high consistent CPU usage due to concurrent peak process on linux

You can modify the samples and time interval accordingly according to your system

Note: If your system is already CPU bound, then it is not recommended to run this script because of multiple loops in the script

#!/bin/bash -x
#Create log directory
idir=/home/oracle/idle
#Loop the CPU utilization using vmstat statistics for every 5 seconds with 30 samples and fetch idle CPU < 5
while true
  do
   #remove the old logfiles
   rm -rf idle highcpu
    vmstat 5 30|awk '{print $15}' >> $idir
   #delete empty spaces and column name 'id' from output file
   sed -i '/^$/d' $idir
   sed -i '/id/d' $idir
  cat $idir|grep -v 'id'|grep -v '^$' > idlelast
   #if idle CPU is less than 5%,then print boom to output file
cat idlelast|while read li;do if [ $li -lt 5 ];then echo "boom" >> highcpu;fi
   #count the number of boom from logfile
   report=`grep 'boom' highcpu|wc -l`
   #if there are more than 20 booms from output file, then CPU usage is high
  if [ $report -gt 20 ]
   then
  echo 'ALERT:HIGHCPU'|mail -s 'HIGH CPU' xxx.gmail.com
  fi
   #clean up lines from log
   #sed -i '1,11d' idlelast
     cleanup=`cat idlelast |wc -l`
    lastclean=$((cleanup))
    l="1,$cleanup"
   if [ $cleanup -gt 10 ];then sed -i "$l"'d' idlelast;fi
   sleep 30
   #print top CPU users
   echo 'TOP 5 CPU USERS' && ps -eo pcpu,user|sort -nr|head -5
  done
done

SAMPLE OUTPUT:

Temporary output file from the script

[oracle@exdbadm01 ~]$ cat idlelast
81
91
90
94
95
91
92
95
88
92

Top CPU users

TOP 5 CPU USERS
 0.6 root
 0.3 oracle
 0.2 oracle
 0.2 oracle
 0.1 root

Execution of script:

+ idir=/home/oracle/idle
+ true
+ rm -rf idle highcpu
+ vmstat 5 30
+ awk '{print $15}'
+ sed -i '/^$/d' /home/oracle/idle
+ sed -i /id/d /home/oracle/idle
+ cat /home/oracle/idle
+ grep -v id
+ grep -v '^$'
+ cat idlelast
+ read li
+ '[' 89 -lt 5 ']'
++ grep boom highcpu
++ wc -l
+ report=0
+ '[' 0 -gt 20 ']'
++ cat idlelast
++ wc -l
+ cleanup=30
+ lastclean=30
+ l=1,30
+ '[' 30 -gt 10 ']'
+ sed -i 1,30d idlelast
+ sleep 30
+ echo 'TOP 5 CPU USERS'
TOP 5 CPU USERS
+ ps -eo pcpu,user
+ sort -nr
+ head -5
 0.5 root
 0.1 root
 0.1 root
 0.1 root
 0.1 oracle

FIND AWR SNAPSHOT INTERVAL AND RETENTION ORACLE

To check the snapshot interval and retention of AWR in oracle you can use the below query


kish@exdbx<>select max(SNAP_INTERVAL) as SNAP_INTERVAL_MAX,max(RETENTION) as MAX_RETENTION from dba_hist_wr_control;

SNAP_INTERVAL_MAX    MAX_RETENTION
-------------------- ---------------------------------------------------------------------------
+00000 01:00:00.0    +00008 00:00:00.0