Memory usage for AMM RAC database and changing RAC database to ASMM

SQL> select sum(bytes/1024/1024) Current_SGA_SIZE_in_MB from v$sgastat;
CURRENT_SGA_SIZE_IN_MB
----------------------
        1804.448437
SQL> select sum(bytes/1024/1024) MAX_SGA_SIZE_in_MB from  v$sgainfo    where name = 'Maximum SGA Size';
MAX_SGA_SIZE_IN_MB
------------------
    2592.84766
SQL> show parameter memory_max_target;
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
memory_max_target             big integer 1600M
SQL> select (value/1024/1024) Current_PGA_IN_USE_in_MB from v$pgastat where name = 'total PGA inuse';
CURRENT_PGA_IN_USE_IN_MB
------------------------
          788.078938
SQL> select (value/1024/1024) MAX_PGA_ALLOCATED_in_MB from v$pgastat where name = 'maximum PGA allocated';
MAX_PGA_ALLOCATED_IN_MB
-----------------------
         1567.658203
SQL> select (value/1024/1024) PGA_TARGET_in_MB    from v$pgastat where name = 'aggregate PGA target parameter';
PGA_TARGET_IN_MB
----------------
         480





Memory usage of exisitng AMM: 
 - memory reserved  for PGA/SGA: 8 GB 
 - current PGA size 790 MB
 - current SGA size 1804 MB
 - free memory for future PGA/SGA usage: ~ 2.5 GB

For switching ASMM this can be translated into 
  SGA_MAX_SIZE             : 3 GB
  SGA_TARGET               : 3 GB
  PGA_AGGREGATE_TARGET     :  2 GB

For further tuning check : V$PGA_TARGET_ADVICE

Execute the  following commands.
Disable AMM
  SQL> alter system reset memory_max_target scope=spfile  sid='*';
  SQL> alter system reset memory_target  scope=spfile  sid='*';

Enable ASMM
  SQL> alter system set SGA_MAX_SIZE=3G scope=spfile  sid='*';
  SQL> alter system set SGA_TARGET=3G scope=spfile  sid='*'; 
  SQL> alter system set PGA_AGGREGATE_TARGET=2G scope=spfile  sid='*';  

Reboot database and verify that we have switched from AMM to ASMM
SQL> show parameter memory
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
memory_max_target             big integer 0
memory_target                 big integer 0
--> AMM disabled 

SQL> show parameter sga
NAME                     TYPE          VALUE
------------------------ ----------- ------------------------------
sga_max_size             big integer 3G
sga_target               big integer 3G

SQL> show parameter pga
NAME                     TYPE     VALUE
------------------------ ----------- ------------------------------
pga_aggregate_target     big integer 2G

--> ASMM enabled !

OPatch, Mainting patches in Oracle Database

OPatch is an Oracle utility that assists us to apply interim patches to Oracle’s rdbms software amd clusterware. We can find opatch utility in $ORACLE_HOME/Opatch directory. 

For example, let we apply patch number 11114547  to our 11.2.0.4 database.

Applying Patch:

1- Backup Oracle Home directory.

       $ tar -cf ora11g.tar ora11g

2- Download patch file p11114547_10205_Linux-x86-64.zip via metalink. And copy it to database server.

3- Unzip the patch file.

       $ unzip p11114547_10205_Linux-x86-64.zip

4- Apply patch with opatch utility.

       $ cd 11114547

       $ ORACLE_HOME/OPatch/opatch apply

To see list of applied patches :

        $ORACLE_HOME/OPatch/opatch lsinventory

For example:

        $ORACLE_HOME/OPatch/opatch lsinventory

   Invoking OPatch 11.2.0.4.9

Oracle Interim Patch Installer version 11.2.0.4.9

Copyright (c) 2011, Oracle Corporation. All rights reserved.

Oracle Home : /oracle/ora10g

Central Inventory : /oracle/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.2.0.4.9

OUI version : 11.2.0.5.0

OUI location : /oracle/ora10g/oui

Log file location : /oracle/ora10g/cfgtoollogs/opatch/opatch2017-09-14_12-13-12PM.log

Patch history file: /oracle/ora10g/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /oracle/ora10g/cfgtoollogs/opatch/lsinv/lsinventory2017-09-14_12-13-12PM.log

Installed Top-level Products (3):

Oracle Database 10g 10.2.0.1.0

Oracle Database 10g Release 2 Patch Set 3 10.2.0.4.0

Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0

There are 3 products installed in this Oracle Home.

Interim patches (2) :

Patch 8943287 : applied on Fri Oct 21 20:39:46 EEST 2011

Unique Patch ID: 12722995

Created on 23 Aug 2010, 11:45:16 hrs PST8PDT

Bugs fixed:

8943287

Rollback applied patch:

Sometimes a patch is applied to the system may need to take back because of its effect. In this case, rollback is performed as follows.

$ORACLE_HOME/OPatch/opatch rollback -id 11114547

Gather diagnostic Information using TFA in Oracle Database

Oracle Trace File Analyzer (TFA) provides a number of diagnostic tools in a single bundle, making it easy to gather diagnostic information about the Oracle database and clusterware, which in turn helps with problem resolution when dealing with Oracle Support.

If possible we should install Oracle Trace File Analyzer as root. This will give you the highest capabilities. If Oracle Trace File Analyzer is already installed, reinstalling will perform an upgrade to the existing location. If it is not already installed, the recommended location is /opt/oracle.tfa

To install as root:

Download the appropriate Oracle Trace File Analyzer zip, copy to required machine and unzip.

Run the installTFA command: $ ./installTFA

To install as an ORACLE_HOME owner use the –extractto option. This tells Oracle Trace File Analyzer where to install to. The installer includes a JVM, but if you want to use one already installed use the –javahome option to point to it.

./installTFA -extractto -javahome

If we do not want to use ssh, we can install on each host using a local install. Then we use tfactl syncnodes to generate and deploy the relevant SSL certificates.

Now the time to collect TFA.

Please run TFA which collects all clusterware logs from all nodes (needs to be done as root from node1's gi_home): 

TFA from each node: 

==> TFA Collector- The Preferred Tool for Automatic or ADHOC Diagnostic Gathering Across All Cluster Nodes ( Doc ID 1513912.1 ) 

Examples: 

/bin/tfactl diagcollect -all -from "" -to "" 

/bin/tfactl diagcollect -all -since [2d|8h] 

/bin/tfactl diagcollect -for "" <--- 12hrs="" after="" and="" before="" collect="" font="" given="" nbsp="" the="" time="" will="">

Generate Incident Report in Oracle Database

The Automatic Diagnostics Repository (ADR) is a hierarchical file-based repository for handling diagnostic information. 

Directory structure is as:

$ADR_BASE/diag/rdbms/{DB-name}/{SID}/alert
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/cdump
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/hm
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/incident
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/trace
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/{others}

To generate incident report quickly, we can follow the below steps:

adrci> show problem
adrci> show incident

adrci> show incident -mode detail -p "incident_id=incident_no" 
adrci> ips create package problem <problem_id>correlate all
adrci> ips generate package  in "/tmp"

Set up password less authentication in ssh

To setup SSH passwordless authentication(User Equivalence) Between 2 Servers Linux, we can follow the below steps.

Lets assume we have only 2 servers : ServerA, ServerB and we need to configure password-less login between both the servers.

We need to login to ServerA and identify the .ssh directory exists under the home directory

In case it doesn't exist we can create it.

cd /root

mkdir .ssh

chmod 700 .ssh

On Linux for root user it is normally /root/.ssh

$ cd /root/.ssh

$ ssh-keygen -t rsa

This will create 2 files id_rsa and id_rsa.pub. One is a private key file and other is the public key file.

$ cat id_rsa.pub >> auth_keys_a

Now copy this file to the ServerB using scp utility

$ scp auth_keys_a root@ServerB:/root/.ssh

Now on ServerB identify the .ssh directory which should ideally be on the same location /root/.ssh

$ cd /root/.ssh

$ ssh-keygen - rsa

This will again create the public and private keyfile

$ cat id_rsa.pub >> auth_keys_b

Now scope this file back to ServerA

$ scp auth_keys_b root@ServerA:/root/.ssh

Now we can try to ssh between the nodes

From ServerA

ssh root@serverB

From ServerB

ssh root@serverA

SQLplus user profile; showing connection name in the sqlplus prompt

SQLplus user profile is maintained in the file glogin.sql which is executed at the time of login in to a database connection. The default location of this file is

 $ORACLE_HOME/sqlplus/admin/

Usually when a query is executed through sqlplus terminal, at first it tries to fetch profile form the current directory, if no there in that case from the default location.

Suppose, by defualt if we connect to a database using  sqlplus, it dones not show the connected database information in the screen.  It only prompts with: 

sqlplus>

Now if we want to  show the connected database information in the sqlplus command line as:

username@db_connect_name>

In that case, we cas easily do that by modification in the glogin file as below:

set sqlprompt "_user '@' _connect_identifier >"



There are some other keywords for using in glogin file as:

SET LINESIZE	Followed by a number, sets the number of characters as page width of the query results.
SET NUMFORMAT	Followed by a number format (such as $99,999), sets the default format for displaying numbers in query results.
SET PAGESIZE	Followed by a number, sets the number of lines per page.
SET PAUSE	Followed by ON, causes SQL*Plus to pause at the beginning of each page of output (SQL*Plus continues scrolling after you enter Return). Followed by text, sets the text to be displayed each time SQL*Plus pauses (you must also set PAUSE to ON).
SET SQLPROMPT	Followed by the connect information variable in the form: SET SQLPROMPT '&_CONNECT_IDENTIFIER > ' changes the SQL*Plus command-line prompt to display the SID of the database you are connected to.
SET TIME	Followed by ON, displays the current time before each command prompt.

Stop NetworkManager to overwrite /etc/resolv.conf

Network manager overwrite the /etc/resolv.conf file. To stop overwriting this file. We can follwo the below steps

# systemctl disable NetworkManager.service
# systemctl stop NetworkManager.service

Or If we don't want to stop or disable the network manager service, in that case. We will have to tell NetwoekManager not to modify the DNS settings:

 /etc/NetworkManager/NetworkManager.conf
 [main]
 dns=none

Linux command terminal output and redirecting to file

Oracle DB user with password no expiry

Usually in default user profile, password expires for a user after certain period. If we want that password won't expire for a user as it may be required for the users used in the application end, we can implement that

1.   by creating a new profile with LIMIT password no expired and 
2.   assigning this profile to our target users.

create profile pro_pass_no_exp limit password_life_time UNLIMITED;

Profile created.

alter user dbbl profile pro_pass_no_exp;

User altered.

Displaying lines numbers in Cat command

The syntax is:
cat -n fileName

OR
cat --number paul.txt

or  we cab use the more command/less command as filter when text can not be fitted on the screen:
cat --number foo.c | more
cat --number foo.c | less

The -b / --number-nonblank option number all nonempty output lines, starting with one and the syntax is:
cat -b fileName

OR
cat --number--nonblank filename

Finally, we can suppress or remove repeated empty output lines with the -s / --squeeze-blank option:
cat -s -n fileName
cat -s -n /etc/hosts




we can use the cat or nl command to display line numbers:

cat -n hello.c
nl hello.c



A note about sed
In the case just to print 7th and 9th line we can use the sed command:



sed -n -e 7p -e 9p /etc/resolv.conf

Multiple lines comment and uncommnet in VIM

To comment out blocks in vim:

• press Esc (to leave editing or other mode)
• hit ctrl+v (visual block mode)
• use the up/down arrow keys to select lines you want (it won't highlight everything - it's OK!)
• Shift+i (capital I)
• insert the text you want, i.e. %
• press EscEsc

To uncomment:

Put your cursor on the first # character, press CtrlV (or CtrlQ for gVim), and go down until the last commented line and press x, that will delete all the # characters vertically.

Duplicating a line in Vim Eiditor

yy or Y to copy the line 
or 
dd to delete (cutting) the line

then

p to paste the copied or deleted text after the current line 
or 
P to paste the copied or deleted text before the current line

Problem to mount NFS : mount: wrong fs type, bad option, bad superblock

Sometime, we may get error as  mount: wrong fs type, bad option, bad superblock while trying to mount NFS file in ubuntu. For my case, i faced the problem showing the error as:

The reason behind it that for several filesystems (e.g. nfs, cifs) we might need a /sbin/mount. helper program.
As we are trying to mount NFS. The /sbin/mount.nfs helper program is provided by nfs-common. We can install it with:

sudo apt-get update
sudo apt install nfs-common

On the other hand, if we are trying to mount CIFS, the helper program is provided by cifs-utils. We can install it using below command:

sudo apt install cifs-utils


Then we can mount our target as:
We will have to create a directory where to mount the nfs,

sudo mount :/ /

Get elapsed time for an individual command

In sqlplus, to see the elapsed time for an individual query, we can use the "set timing on" command.

SQL> set timing on;

SQL> select surname from personal_details where firstname='udvas';

Elapsed: 00:00:02.52

Again, the "set timing on" command is a SQL*Plus command, but we can measure run time for Oracle SQL with a variety of Oracle tools.

Sometimes when working on SQL command optimizations, all that is desired is a rough timing estimate; namely, the SQL*Plus client elapsed execution time, or simple clock time. Often that simple metric is sufficient for some very basic tuning needs. SQL*Plus has a built-in capability to do exactly this - it is the SET TIMING  command. It essentially records the clock time before and after the SQL command execution, then displays the run time difference.

This commands works for the single command.

VisualVM for monitoring remote jvm

Using VisualVM we can monitor all the running Java Process and status of JDK behind these processes. VisualVM can be found inside the JDK bin directory.

By running VisualVM, by default we can access the locally run processes. To get the Remote Server processes, we have to connect with that server through credential. And in the target server, jstatd must be in running.

jstatd: 

Monitors Java Virtual Machines (JVMs) and enables remote monitoring tools to attach to JVMs. This command is experimental and unsupported. 

The jstatd command is an RMI server application that monitors for the creation and termination of instrumented Java HotSpot VMs and provides an interface to enable remote monitoring tools to attach to JVMs that are running on the local host.

To run jstatd, java security permission is required. For this purpose, at the starting of jstatd, argument will have to be passed in the command line as:

 jstatd -J-Djava.security.policy=/home/paul/jstatd.all.policy

content of jstatd.all.policy  is as:

bash-4.3$ cat /home/oracle/jstatd.all.policy

grant codebase "file:${java.home}/../lib/tools.jar" {

   permission java.security.AllPermission;

};

It permits only the min required security related permission .

SVN Quick Note : Check all revision and download specific revision

We can use the below command for the revision history with commit message

svn log -l5 -v

To dowload specific revision version from SVN:
svn checkout http://10.78.11.131/svn/mbsdb@7 testsvn/

* Here 7 is the revision no.
** testsvn is the target directory where to download

ORA-02297: cannot disable constraint -dependencies exist

ORA-02297: cannot disable constraint -dependencies exist

Whenever you try to disable a constraint of a table it fails with error message ORA-02297: cannot disable constraint -dependencies exist as below.

SQL> alter table transaction disable constraint TRANSACTION_PK;
alter table transaction disable constraint TRANSACTION_PK
*
ERROR at line 1:
ORA-02297: cannot disable constraint (OMS.TRANSACTION_PK) - dependencies exist

Reason behind this problem is as:
Disable constraint command fails as the table is parent table and it has foreign key that are dependent on this constraint.

This problem can be solved in different ways.

Two solutions exist for this problem.
1)Find foreign key constraints on the table and disable those foreign key constraints and then disable this table constraint.

Following query will check dependent table and the dependent constraint name. After that disable child first and then parent constraint.

SQL> SELECT p.table_name "Parent Table", c.table_name "Child Table",
     p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
     FROM user_constraints p
     JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)
     WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
     AND c.constraint_type = 'R'
     AND p.table_name = UPPER('&table_name');
Enter value for table_name: transaction
old   7:      AND p.table_name = UPPER('&table_name')
new   7:      AND p.table_name = UPPER('transaction')

Parent Table                   Child Table                    Parent Constraint              Child Constraint
------------------------------ ------------------------------ ------------------------------ ------------------------------
TRANSACTION                    USER_SALARY_RECORD             TRANSACTION_PK                 SYS_C005564
TRANSACTION                    TRANSACTION_DETAIL             TRANSACTION_PK                 TRNSCTN_DTL_TRNSCTN_FK

SQL> alter table USER_SALARY_RECORD disable constraint SYS_C005564;
Table altered.

SQL> alter table TRANSACTION_DETAIL  disable constraint TRNSCTN_DTL_TRNSCTN_FK;
Table altered.

SQL> alter table transaction disable constraint TRANSACTION_PK;
Table altered.

2)Disable the constraint with cascade option.

SQL> alter table transaction disable constraint TRANSACTION_PK cascade;
Table altered.

Searching text in the file contents inside a directory in linux

Sometimes, We need to find out specific string of text in the files' Contents of a directory of linux system. Its to look for text within the file, no tin the file name. 


In such case we can user the below command:


grep -r -l "specific test" /directory/path/full/*criteria_if_required_in_file_name*log

NGINX: upstream timed out (110: Connection timed out) while reading response header from upstream

For my case, I got around this issue by clearing the connection keep-alive flag and specifying http version as per the answer here: 

https://stackoverflow.com/a/36589120/479632

server {
    location / {
        proxy_set_header   X-Real-IP $remote_addr;
        proxy_set_header   Host      $http_host;

        # these two lines here
        proxy_http_version 1.1;
        proxy_set_header Connection "";

        proxy_pass http://localhost:5000;
    }
}

Oracle Bug: kewastUnPackStats(): bad magic 1 (0x2b2b8f4652ad, 0)

Someitmes we may get the error in rdbms alert log...?

It generates thousands of lines each day which will expand the alert log size.

We can override this error by.

ALTER SYSTEM SET control_management_pack_access='NONE' SCOPE=MEMORY;

To get the solution permanantly, we need to deploy the patch.