cloud computing in OCI

Hi Friend  , Hope you are doing well .  

Today i am with new and very popular topic called Cloud computing . As we know now a days cloud, cloud computing , VCN , OCI words are very popular because  database and applications are moving to cloud . 

cloud providers like oracle, Microsoft , Amazon are ready to serve your database management on rent basis and that's  the reason cloud called pay as you go Model . it means just pay  for those how much you need it to manage db  or infrastructure.  

so Friends what is cloud Computing  

cloud computing is on demand delivery of IT resources over internet as pay as you go model.

so as a client no need to invest money for IT infrastructure like hardware , server , networking , cpu , database , Application etc.
just login to cloud with your authentication take subscription from cloud  providers and use it then pay per  your bill .

Why cloud :  ----

there are many advantage  to move towards cloud  from on premises :---

1) Capital Expenses ---- it will reduce capital expenses like  no need to pay for server, cpu and infra .

2) pay as you go ----- every cloud vendor provides monthly billing so you can pay as needed like you can use 10 cpu in high load or 1 cpu in low load as per req can pay for that only.

3) global ---- every cloud vendor provides global data center like they have region and availability domain concept and you can keep your database and Application in that region which ever nearest or fusible to you .

4) Scalable ---- cloud technology is very much scalable in terms of infra-structure .  whenever you want you can scale-up or scale-down your infra as per your use.

What  are the cloud computing services 

Basically we three type of cloud computing services  . type depend on our usage  like what exactly we  are using 

ex few organisation only needed software and they have their own infrastructure and programming i.e platform  .  few needed infra as well  so on that cloud providers have divided cloud in 3 parts

1) Infrastructure as a service  ( Iaas)  -----   it provides vertualised infrastucture on internet and

client can use cpu,server, hardware , ram etc as how much they have requirement.

2) Plateform as a service (Paas) ------  Provides Plateform for Application devolopement .

3) Software as a service (Saas) -----  Provider software on rent basis , it means all infra and plateform would be with cloud provider and you can only use it . managing part by them .


you can login on    "  cloud.oracle.com "   or create a user account  go to payment page provide your credit card details  it will deduct 1 Dollar ($) of amount and it will  return you the same amount in your card . basically its a free account from oracle for one month period , and can learn as much as yo can .

if any doubt regarding this , please comment and ask , I will reply you .

concurrent request session details in R12

Hi Friends , Hope you are doing well . I with new topic today  how to find concurrent request status and  db session details for the same .

Suppose , user submitted concurrent request and its taking long time than expected .  user will reach DBA to find out the cause so there will be two possibilities

1)      Session is in active state and programme is executing /processing the record .

2)      Session is in hung , not processing the record.

Sqlplus apps/<pw>

select status_code,phase_code  from  fnd_concurrent_requests  where request_id='75531471';

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id

FROM apps.fnd_concurrent_requests a,

apps.fnd_concurrent_processes b,

gv$process c,

gv$session d

WHERE a.controlling_manager = b.concurrent_process_id

AND c.pid = b.oracle_process_id

AND b.session_id=d.audsid

AND a.request_id =&req_id

AND a.phase_code = 'R';

It will ask you the req id and will provide you database session  sid , serial# and other details for that particular session

Then onec we got sid , we can get to know the session details from below query

set echo off
set linesize 132
set verify off
set feedback off
set serveroutput on;
declare
     SID number                := 0 ;
     SERIAL number             := 0 ;
     username varchar(20)      := '';
     Status varchar(8)         := '';
     machine  varchar(10)      := '';
     terminal   varchar(25)    := '';
     program   varchar(30)     := '';
     Module varchar(30)        := '';
     Action varchar(20)        := '';
     sql_hash_value  number    := 0 ;
     logontime varchar(30)     := '';
     last_call_et number       := 0 ;
     proc number               := 0 ;
     spid number               := 0 ;
     event varchar(30)         := '';
     state varchar(30)         := '';
     sql_text varchar(2000)    := '';
cursor cur1 is
select  a.sid  sid,                                      
     a.serial#  serial,                                 
     a.username username,
     a.status status ,
     a.machine machine,
     a.terminal terminal,
     a.program program,
     a.module module,
     a.action action,
     a.sql_hash_value sql_hash_value,
     to_char(a.logon_time,'DD-Mon-YYYY HH:MI:SS') logontime,
     a.last_call_et last_call_et,
     a.process proc,
     b.spid  spid,
     sw.event event,
     sw.state  state
from   gv$session a, gv$process b,  gv$session_wait  sw
where  a.paddr=b.addr and a.inst_id=b.inst_id
     and a.sid='&1'
     and a.inst_id=sw.inst_id
     and a.sid=sw.sid;
begin
  DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');
  DBMS_OUTPUT.PUT_LINE(' Database session detail for the shadow process ');
  DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');
for m in cur1
loop

DBMS_OUTPUT.ENABLE(50000);
    DBMS_OUTPUT.PUT_LINE('  ');
    DBMS_OUTPUT.PUT_LINE( 'SID............ : ' || m.sid  );
    DBMS_OUTPUT.PUT_LINE('SERIAL#........ : ' || m.serial   );
    DBMS_OUTPUT.PUT_LINE('USERNAME....... : ' || m.username   );
    DBMS_OUTPUT.PUT_LINE('STATUS......... : ' || m.status     );
    DBMS_OUTPUT.PUT_LINE( 'Machine........ : ' || m.machine );
    DBMS_OUTPUT.PUT_LINE( 'Terminal....... : ' || m.terminal);
    DBMS_OUTPUT.PUT_LINE( 'Program........ : ' || m.program  );
    DBMS_OUTPUT.PUT_LINE('Module......... : ' || m.module  );
    DBMS_OUTPUT.PUT_LINE( 'Action......... : ' || m.action  );
    DBMS_OUTPUT.PUT_LINE('SQL Hash Value. : ' || m.sql_hash_value );
    DBMS_OUTPUT.PUT_LINE( 'Logon Time..... : ' || m.logontime );
    DBMS_OUTPUT.PUT_LINE(  'Last Call Et... : ' || m.last_call_et  );
    DBMS_OUTPUT.PUT_LINE( 'Process ID..... : ' || m.proc );
    DBMS_OUTPUT.PUT_LINE(  'SPID........... : ' || m.spid );
    DBMS_OUTPUT.PUT_LINE('Session Waiting for event:'||m.event );
    DBMS_OUTPUT.PUT_LINE('Session state ...........:'||m.state);
dbms_output.put_line('SQL_TEXT is..........:');
for rec in ( select sql_text from v$session s,v$sqltext v where
    s.sql_hash_value=v.hash_value and
    s.sql_address=v.address and s.sid=m.sid order by piece)
loop
dbms_output.put_line(rec.sql_text);
end loop;
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE(' ');
end loop;
end;
 / 

it will promt you SID , please provide SID from first query and get details  for particular session.

Check concurrent req log file and o/p file  in below location .

File	Location	Format
concurrent log	$APPLCSF/log	l<req_no >.req
Concurrent output	$APPLCSF/out	o <req_no>.out

For any troubleshooting of concurrent request, first check log file for concurrent request  and

Look for the issue.

Dear Reader , Please comment for any information or any concern about this blog .

Will update  you as per your comment and  feedback .

Invalid objects in oracle

Hi Friends, I am with a new topic today   “checking Invalid object and compiling “

This is very Important things to check invalid objects and compile them .

If you are going to apply patch , upgrade database or Application version , migration activity  or any type of alteration in your database , invalid objects will create .

I would always recommend before applying patch or upgrade, migrate activity take count of Invalid objects and also take a backup for same .

Check Invalid objects count ----

select OWNER,count (OBJECT_NAME) from dba_objects where status='INVALID'  group by OWNER;

Take backup of Invalid objects ----

Create table Invalid_date  as select * from dba_objects where status =’INVALID’;

Compile of Invalid objects ---------

cd $ORACLE_HOME/rdbms/admin

You wil get one script called utlrp.sql

Just run at sql prompt

Connect to sqlplus  as -----

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 17 19:44:28 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>@ utlrp.sql

It will compile these invalid objects , you can monitor also and check how many of compile here .

select OBJECT_NAME,CREATED,status,OWNER,EDITION_NAME,object_type from dba_objects where OBJECT_NAME in ('ONT_FEEDBACK_PKG','EGO_DUMMY_SUBSCR_PKG','BOM_TEST_SUBSCR_PKG');

OBJECT_NAM CREATED   STATUSOWNER   EDITION_NAME     OBJECT_TYPE

EGO_DUMMY_ 15-FEB-18 VALIDAPPS   V_20180215_0124    PACKAGE

SUBSCR_PKG

EGO_DUMMY_ 15-FEB-18 VALIDAPPS   V_20180215_0124    PACKAGE BODY

SUBSCR_PKG

BOM_TEST_S 15-FEB-18 VALIDAPPS   V_20180215_0124    PACKAGE

UBSCR_PKG

BOM_TEST_S 15-FEB-18 VALIDAPPS   V_20180215_0124    PACKAGE BODY

UBSCR_PKG

Manual compile database objects --------------

Ø  ALTER PACKAGE apps.BOM_TEST_UBSCR_PKG  COMPILE PACKAGE;

Ø  ALTER PACKAGE apps.BOM_TEST_S_UBSCR_PKG  COMPILE BODY;

If any error comes , we can find out by below command

Show error apps.BOM_TEST_UBSCR_PKG

It  will show the exact compilation error  and accordingly we can resolve it .

Dear Readers , Please let me know if any doubt or any issue or need to clarify any thing related to this post about invalid objects and compiling them . comment here , will reply you .

GYANUDBA: DBA Health check

GYANUDBA: DBA Health check: Hi Friends , hope you are Doing well . Here I going to tell you about basic check for database, table space , how to check alert lo...

unix command used by DBA

Hi Friends , hope you are Doing well .

Here I going to tell you about basic check for database at unix / linux level  which are generally used by DBA .  

1)    Df-hP  :   it will give all mount point size , available size  , mounted on  and % use  .

df -hP

Filesystem                                                                         Size  Used Avail Use% Mounted on

/dev/mapper/rootvg-rootlv                                                          7.8G  1.4G  6.1G  18% /

tmpfs                                                                              118G  1.6M  118G   1% /dev/shm

/dev/xvda1                                                                         477M  224M  224M  51% /boot

/dev/mapper/rootvg-homelv                                                          2.5G  2.1G  268M  89% /home

/dev/mapper/rootvg-tmplv                                                           9.8G  257M  9.1G   3% /tmp

/dev/mapper/rootvg-usrlv                                                           8.8G  3.1G  5.3G  37% /usr

/dev/mapper/rootvg-varlv                                                           3.9G  3.1G  645M  83% /var

/dev/mapper/Appsvg-U007                                                            197G  137G   50G  74% /u007

/dev/mapper/Redovg-U004                                                             63G   22G   38G  37% /u004

2)    Top :

 it will show you all process running on server , their memory consumption , pid, %CPU.

Also dhows load avg , total memory ,used and free memory .

$ top

top - 19:27:30 up 1 day, 20:04,  1 user,  load average: 5.70, 7.07, 8.49

Tasks: 783 total,   5 running, 778 sleeping,   0 stopped,   0 zombie

Cpu(s): 21.5%us,  2.2%sy,  0.1%ni, 75.4%id,  0.2%wa,  0.0%hi,  0.4%si,  0.3%st

Mem:  247362720k total, 241972860k used,  5389860k free,  5064440k buffers

Swap: 50327548k total,        0k used, 50327548k free, 211509480k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

 6315 oracle    20   0 64.4g  16g  16g R 91.6  6.9  73:32.02 oracle

25278 oracle    20   0 64.4g  16g  16g R 40.3  7.2 103:34.35 oracle

19877 oracle    20   0 64.2g  83m  77m S 16.5  0.0  22:06.73 oracle

3)    Date and  uptime :    it will show cureent date and time , uptime show server up from time

]$ date

Wed Jul 11 19:33:56 UTC 2018

[oracle@servername  ]$ uptime

 19:34:02 up 1 day, 20:10,  1 user,  load average: 8.31, 7.74, 8.25

4)    ps -ef|grep pmon :   it will check whether database is up or not also it will show how many database running on particular serever .

$ ps -ef|grep pmon

oracle   14253     1  0 Jul09 ?        00:00:24 ora_pmon_D1

oracle   18369     1  0 Jul09 ?        00:00:31 ora_pmon_D2

oracle   19779     1  0 Jul09 ?        00:00:20 ora_pmon_T1

oracle   21625     1  0 Jul10 ?        00:00:29 ora_pmon_D3

oracle   25230 17342  0 19:56 pts/0    00:00:00 grep pmon

5)     ps –ef|grep tns  :    it will show listener name and status .

$ ps -ef|grep tns

root       113     2  0 Jul09 ?        00:00:00 [netns]

oracle   23140     1  0 Jul10 ?        00:00:30 /u002/app/oracle/product/D3/11.2.0/bin/tnslsnr  D3 -inherit

oracle   23348     1  0 Jul09 ?        00:00:34 /u002/app/oracle/product/T1/12.1.0.2/bin/tnslsnr  T1 -inherit

oracle   23367     1  0 Jul09 ?        00:00:01 /u002/app/oracle/product/D1/11.2.0/bin/tnslsnr  D1 -inherit

oracle   23376     1  0 Jul09 ?        00:01:52 /u002/app/oracle/product/D2/11.2.0/bin/tnslsnr  D2 -inherit

oracle   30197 17342  0 20:01 pts/0    00:00:00 grep tns

6)    ls –lrt   :   ---   it will show all the fiels and directory listed in current dir.

]$ ls -lrt

total 4

drwxrwxrwx. 6 oracle dba 4096 May  3 20:31 oracle

7)    ps –ef|grep oracle  : -----

it will show all oracle process which are running currently   with there pid .

8)    ps –ef|grep applmgr  : ----

it will show all the application (oracle apps ) process running currently

9)    du –sh *

it will show all sub directory and files size under main directory

# du -sh

171G    .

]# du -sh *

184K    admin

12K     cfgtoollogs

14G     diag

158G    product

10)                       du -sm * | sort -rn | head -10   :

for seeing top 10 dir high space  which are consuming high space in main directory.

Note :  please run this command as a root user

# du -sm * | sort -rn | head -10

161154  product

13446   diag

1       cfgtoollogs

1       admin

11)                        find command :----

find . –name  ‘<file_name’

run this command  under main directory , it will give result o/p  of file  name along with full location on the server .

Please let me know if you are looking for any specific output command .