Sunday, October 4, 2020

Add new disks to ASM DISKSPACE PROD

 ASMCMD>

ASMCMD> lsdsk -t -g FRA

ASMCMD> lsdsk -t -G FRA

Create_Date  Mount_Date  Repair_Timer  Path

10-JAN-19    26-FEB-20   0             /dev/dm-8

09-MAR-17    26-FEB-20   0             /dev/dm-9

ASMCMD> lsdsk -t -G FRA

Create_Date  Mount_Date  Repair_Timer  Path

10-JAN-19    26-FEB-20   0             /dev/dm-8

09-MAR-17    26-FEB-20   0             /dev/dm-9

ASMCMD> lsdsk -t -G DATA

Create_Date  Mount_Date  Repair_Timer  Path

02-MAR-19    26-FEB-20   0             /dev/dm-10

25-SEP-19    26-FEB-20   0             /dev/dm-12

09-MAR-17    26-FEB-20   0             /dev/dm-5

16-SEP-18    26-FEB-20   0             /dev/dm-6

09-MAR-17    26-FEB-20   0             /dev/dm-7

ASMCMD>


Add below lines in 20asm-devices.rules


KERNEL=="dm-*", PROGRAM="scsi_id --page=0x83 --whitelisted --device=/dev/%k",RESULT=="360002ac0000000000b007ba3000157e7",Name="FRA9", OWNER:="oraprod", GROUP:="oinstall"

KERNEL=="dm-*", PROGRAM="scsi_id --page=0x83 --whitelisted --device=/dev/%k",RESULT=="360002ac0000000000b007ba1000157e7",Name="DATA9", OWNER:="oraprod", GROUP:="oinstall"


Final contents of 20asm-devices.rules 

Make sure 20asm-devices.rules file backup is taken before modifying.


After modifying asm rules file complete below steps:
=======================================================


/sbin/udevadm control --reload-rules
/sbin/udevadm trigger --type=devices --action=change
ll /dev/dm-*

pract:
======
-bash-4.1#
-bash-4.1# /sbin/udevadm control --reload-rules
-bash-4.1# ll /dev/dm-*
brw-rw----. 1 root    disk     253,  0 Feb 26 17:24 /dev/dm-0
brw-rw----. 1 root    disk     253,  1 Feb 26 17:24 /dev/dm-1
brw-rw----. 1 oraprod oinstall 253, 10 Apr 19 15:09 /dev/dm-10
brw-rw----. 1 root    disk     253, 11 Feb 26 17:26 /dev/dm-11
brw-rw----. 1 oraprod oinstall 253, 12 Apr 19 15:09 /dev/dm-12
brw-rw----. 1 root    disk     253, 13 Feb 26 17:26 /dev/dm-13
brw-rw----. 1 root    disk     253, 14 Feb 26 17:26 /dev/dm-14
brw-rw----. 1 root    disk     253, 15 Feb 26 17:25 /dev/dm-15
brw-rw----. 1 root    disk     253, 16 Feb 26 17:24 /dev/dm-16
brw-rw----. 1 root    disk     253, 17 Feb 26 17:24 /dev/dm-17
brw-rw----. 1 root    disk     253, 18 Feb 26 17:24 /dev/dm-18
brw-rw----. 1 root    disk     253, 19 Feb 26 17:24 /dev/dm-19
brw-rw----. 1 oragrid oinstall 253,  2 Apr 19 15:09 /dev/dm-2
brw-rw----. 1 root    disk     253, 20 Feb 26 17:24 /dev/dm-20
brw-rw----. 1 root    disk     253, 21 Feb 26 17:24 /dev/dm-21
brw-rw----. 1 root    disk     253, 22 Feb 26 17:24 /dev/dm-22
brw-rw----. 1 root    disk     253, 23 Apr 19 13:46 /dev/dm-23
brw-rw----. 1 root    disk     253, 24 Apr 19 13:46 /dev/dm-24
brw-rw----. 1 oragrid oinstall 253,  3 Apr 19 14:01 /dev/dm-3
brw-rw----. 1 oragrid oinstall 253,  4 Apr 19 14:01 /dev/dm-4
brw-rw----. 1 oragrid oinstall 253,  5 Apr 19 15:09 /dev/dm-5
brw-rw----. 1 oragrid oinstall 253,  6 Apr 19 15:09 /dev/dm-6
brw-rw----. 1 oragrid oinstall 253,  7 Apr 19 15:09 /dev/dm-7
brw-rw----. 1 oraprod oinstall 253,  8 Apr 19 15:09 /dev/dm-8
brw-rw----. 1 oragrid oinstall 253,  9 Apr 19 15:09 /dev/dm-9
-bash-4.1# /sbin/udevadm trigger --type=devices --action=change
-bash-4.1# ll /dev/dm-*
brw-rw----. 1 root    disk     253,  0 Apr 19 15:10 /dev/dm-0
brw-rw----. 1 root    disk     253,  1 Apr 19 15:10 /dev/dm-1
brw-rw----. 1 oraprod oinstall 253, 10 Apr 19 15:10 /dev/dm-10
brw-rw----. 1 root    disk     253, 11 Apr 19 15:10 /dev/dm-11
brw-rw----. 1 oraprod oinstall 253, 12 Apr 19 15:10 /dev/dm-12
brw-rw----. 1 root    disk     253, 13 Apr 19 15:10 /dev/dm-13
brw-rw----. 1 root    disk     253, 14 Apr 19 15:10 /dev/dm-14
brw-rw----. 1 root    disk     253, 15 Apr 19 15:10 /dev/dm-15
brw-rw----. 1 root    disk     253, 16 Apr 19 15:10 /dev/dm-16
brw-rw----. 1 root    disk     253, 17 Apr 19 15:10 /dev/dm-17
brw-rw----. 1 root    disk     253, 18 Apr 19 15:10 /dev/dm-18
brw-rw----. 1 root    disk     253, 19 Apr 19 15:10 /dev/dm-19
brw-rw----. 1 oragrid oinstall 253,  2 Apr 19 15:10 /dev/dm-2
brw-rw----. 1 root    disk     253, 20 Apr 19 15:10 /dev/dm-20
brw-rw----. 1 root    disk     253, 21 Apr 19 15:10 /dev/dm-21
brw-rw----. 1 root    disk     253, 22 Apr 19 15:10 /dev/dm-22
brw-rw----. 1 oraprod oinstall 253, 23 Apr 19 15:10 /dev/dm-23
brw-rw----. 1 oraprod oinstall 253, 24 Apr 19 15:10 /dev/dm-24
brw-rw----. 1 oragrid oinstall 253,  3 Apr 19 15:10 /dev/dm-3
brw-rw----. 1 oragrid oinstall 253,  4 Apr 19 15:10 /dev/dm-4
brw-rw----. 1 oragrid oinstall 253,  5 Apr 19 15:10 /dev/dm-5
brw-rw----. 1 oragrid oinstall 253,  6 Apr 19 15:10 /dev/dm-6
brw-rw----. 1 oragrid oinstall 253,  7 Apr 19 15:10 /dev/dm-7
brw-rw----. 1 oraprod oinstall 253,  8 Apr 19 15:10 /dev/dm-8
brw-rw----. 1 oragrid oinstall 253,  9 Apr 19 15:10 /dev/dm-9
-bash-4.1# pwd



oragrid@pansoft ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 19 15:26:45 2020

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 Real Application Clusters and Automatic Storage Management options

SQL>  select state, name, type from v$asm_diskgroup;

STATE       NAME                           TYPE
----------- ------------------------------ ------
MOUNTED     CRS                            EXTERN
MOUNTED     DATA                           EXTERN
MOUNTED     FRA                            EXTERN

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
[oragrid@pansoft ~]$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576      3072     2672                0            2672              0             Y  CRS/
MOUNTED  EXTERN  N         512   4096  1048576   1536000   290164                0          290164              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576    358400   331470                0          331470              0             N  FRA/
ASMCMD> exit
[oragrid@pansoft ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 19 15:28:27 2020

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 Real Application Clusters and Automatic Storage Management options

SQL> select GROUP_NUMBER,MOUNT_STATUS,STATE,REDUNDANCY,NAME,PATH from v$asm_disk;

GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME
------------ ------- -------- ------- ------------------------------
PATH
--------------------------------------------------------------------------------
           0 CLOSED  NORMAL   UNKNOWN
/dev/dm-24

           0 CLOSED  NORMAL   UNKNOWN
/dev/dm-23

           2 CACHED  NORMAL   UNKNOWN DATA_0004
/dev/dm-12


GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME
------------ ------- -------- ------- ------------------------------
PATH
--------------------------------------------------------------------------------
           2 CACHED  NORMAL   UNKNOWN DATA_0003
/dev/dm-10

           3 CACHED  NORMAL   UNKNOWN FRA_0000
/dev/dm-9

           3 CACHED  NORMAL   UNKNOWN FRA_0001
/dev/dm-8


GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME
------------ ------- -------- ------- ------------------------------
PATH
--------------------------------------------------------------------------------
           2 CACHED  NORMAL   UNKNOWN DATA_0001
/dev/dm-7

           2 CACHED  NORMAL   UNKNOWN DATA_0002
/dev/dm-6

           2 CACHED  NORMAL   UNKNOWN DATA_0000
/dev/dm-5


GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME
------------ ------- -------- ------- ------------------------------
PATH
--------------------------------------------------------------------------------
           1 CACHED  NORMAL   UNKNOWN CRS_0002
/dev/dm-4

           1 CACHED  NORMAL   UNKNOWN CRS_0001
/dev/dm-3

           1 CACHED  NORMAL   UNKNOWN CRS_0000
/dev/dm-2


12 rows selected.

SQL> Alter diskgroup FRA add disk '/dev/dm-24' NAME FRA9;

Diskgroup altered.

SQL> select GROUP_NUMBER,MOUNT_STATUS,STATE,REDUNDANCY,NAME,PATH from v$asm_disk;

GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME
------------ ------- -------- ------- ------------------------------
PATH
--------------------------------------------------------------------------------
           0 CLOSED  NORMAL   UNKNOWN
/dev/dm-23

           2 CACHED  NORMAL   UNKNOWN DATA_0004
/dev/dm-12

           2 CACHED  NORMAL   UNKNOWN DATA_0003
/dev/dm-10


GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME
------------ ------- -------- ------- ------------------------------
PATH
--------------------------------------------------------------------------------
           3 CACHED  NORMAL   UNKNOWN FRA_0000
/dev/dm-9

           3 CACHED  NORMAL   UNKNOWN FRA_0001
/dev/dm-8

           2 CACHED  NORMAL   UNKNOWN DATA_0001
/dev/dm-7


GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME
------------ ------- -------- ------- ------------------------------
PATH
--------------------------------------------------------------------------------
           2 CACHED  NORMAL   UNKNOWN DATA_0002
/dev/dm-6

           2 CACHED  NORMAL   UNKNOWN DATA_0000
/dev/dm-5

           1 CACHED  NORMAL   UNKNOWN CRS_0002
/dev/dm-4


GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME
------------ ------- -------- ------- ------------------------------
PATH
--------------------------------------------------------------------------------
           1 CACHED  NORMAL   UNKNOWN CRS_0001
/dev/dm-3

           1 CACHED  NORMAL   UNKNOWN CRS_0000
/dev/dm-2

           3 CACHED  NORMAL   UNKNOWN FRA9
/dev/dm-24


12 rows selected.

SQL> Alter diskgroup DATA add disk '/dev/dm-23' NAME DATA9;

Diskgroup altered.

SQL> select GROUP_NUMBER,MOUNT_STATUS,STATE,REDUNDANCY,NAME,PATH from v$asm_disk;

GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME
------------ ------- -------- ------- ------------------------------
PATH
--------------------------------------------------------------------------------
           2 CACHED  NORMAL   UNKNOWN DATA_0004
/dev/dm-12

           2 CACHED  NORMAL   UNKNOWN DATA_0003
/dev/dm-10

           3 CACHED  NORMAL   UNKNOWN FRA_0000
/dev/dm-9


GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME
------------ ------- -------- ------- ------------------------------
PATH
--------------------------------------------------------------------------------
           3 CACHED  NORMAL   UNKNOWN FRA_0001
/dev/dm-8

           2 CACHED  NORMAL   UNKNOWN DATA_0001
/dev/dm-7

           2 CACHED  NORMAL   UNKNOWN DATA_0002
/dev/dm-6


GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME
------------ ------- -------- ------- ------------------------------
PATH
--------------------------------------------------------------------------------
           2 CACHED  NORMAL   UNKNOWN DATA_0000
/dev/dm-5

           1 CACHED  NORMAL   UNKNOWN CRS_0002
/dev/dm-4

           1 CACHED  NORMAL   UNKNOWN CRS_0001
/dev/dm-3


GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME
------------ ------- -------- ------- ------------------------------
PATH
--------------------------------------------------------------------------------
           1 CACHED  NORMAL   UNKNOWN CRS_0000
/dev/dm-2

           3 CACHED  NORMAL   UNKNOWN FRA9
/dev/dm-24

           2 CACHED  NORMAL   UNKNOWN DATA9
/dev/dm-23


12 rows selected.

SQL> select GROUP_NUMBER,MOUNT_STATUS,STATE,REDUNDANCY,NAME,PATH from v$asm_disk;

GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME
------------ ------- -------- ------- ------------------------------
PATH
--------------------------------------------------------------------------------
           2 CACHED  NORMAL   UNKNOWN DATA_0004
/dev/dm-12

           2 CACHED  NORMAL   UNKNOWN DATA_0003
/dev/dm-10

           3 CACHED  NORMAL   UNKNOWN FRA_0000
/dev/dm-9


GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME
------------ ------- -------- ------- ------------------------------
PATH
--------------------------------------------------------------------------------
           3 CACHED  NORMAL   UNKNOWN FRA_0001
/dev/dm-8

           2 CACHED  NORMAL   UNKNOWN DATA_0001
/dev/dm-7

           2 CACHED  NORMAL   UNKNOWN DATA_0002
/dev/dm-6


GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME
------------ ------- -------- ------- ------------------------------
PATH
--------------------------------------------------------------------------------
           2 CACHED  NORMAL   UNKNOWN DATA_0000
/dev/dm-5

           1 CACHED  NORMAL   UNKNOWN CRS_0002
/dev/dm-4

           1 CACHED  NORMAL   UNKNOWN CRS_0001
/dev/dm-3


GROUP_NUMBER MOUNT_S STATE    REDUNDA NAME
------------ ------- -------- ------- ------------------------------
PATH
--------------------------------------------------------------------------------
           1 CACHED  NORMAL   UNKNOWN CRS_0000
/dev/dm-2

           3 CACHED  NORMAL   UNKNOWN FRA9
/dev/dm-24

           2 CACHED  NORMAL   UNKNOWN DATA9
/dev/dm-23


12 rows selected.

SQL> select operation, state,power, actual, sofar, est_work, est_minutes
from v$asm_operation where group_number=1;  2

no rows selected

SQL> select state, name, type from v$asm_diskgroup;

STATE       NAME                           TYPE
----------- ------------------------------ ------
MOUNTED     CRS                            EXTERN
MOUNTED     DATA                           EXTERN
MOUNTED     FRA                            EXTERN

SQL> select name, total_mb/1024 "Total GiB" from v$asm_diskgroup;

NAME                            Total GiB
------------------------------ ----------
CRS                                     3
DATA                                 1800
FRA                                   500

SQL>


at No comments:

TAF basic policy RAC

 PUNE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racscan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pune)
      (FAILOVER_MODE=(METHOD=basic)(TYPE=select))
     (LOAD_BALANCE=YES)
     (FAILOVER=ON)
    )
  )

SQL> select INSTANCE_NAME, status from v$instance;
INSTANCE_NAME    STATUS
---------------- ------------
pune1            OPEN

SQL> select INSTANCE_NAME, status from v$instance;
INSTANCE_NAME    STATUS
---------------- ------------
pune2            OPEN
On a different window:
----------------------

[oracle@rac1pub117 admin]$ sqlplus shoaib/shoaib@pune

sql> select * from dba_source;

On Node 2:
----------
[oracle@rac1pub117 admin]$ sqlplus / as sysdba
sql> select inst_id,username,service_name,failover_type,failover_method,failed_over from gv$session where username='SHOAIB';
INST_ID   USERNAME SERVICE_NAME FAILOVER_TYPE FAILOVER_METHOD FAILED-OVER
-------------------------------------------------------------------------
         1 SHOAIB    pune           SELECT BASIC         NO


On Node 1:
----------

sql> shut immediate;
On Node 2:
----------
sql>select inst_id,username,service_name,failover_type,failover_method,failed_over from gv$session where username='SHOAIB';
INST_ID   USERNAME SERVICE_NAME FAILOVER_TYPE FAILOVER_METHOD FAILED-OVER
-------------------------------------------------------------------------
         2 SHOAIB    pune           SELECT BASIC         YES



at No comments:

RAC How to apply database patch on both nodes

 Ceck Oracle service status


[root@rac1pub108 Patch]# crs_stat -t


2: Stop database instance one by one


Stop database instance one by one

[oracle@rac-node1 ~]$ srvctl stop instance -i prod1 -d prod

[oracle@rac-node1 ~]$ srvctl stop instance -i prod2 -d prod



3: Stop ASM instance on each node one by one


[oracle@rac-node1 ~]$ srvctl stop asm -n rac-node1

[oracle@rac-node1 ~]$ srvctl stop asm -n rac-node2


4: Stop nodapplications one by one on each node


[oracle@rac-node1 ~]$ srvctl stop nodeapps -n rac-node1

[oracle@rac-node1 ~]$ srvctl stop nodeapps -n rac-node2




5: Check status again


[oracle@rac-node1 ~]$ crs_stat -t



[oracle@rac1pub108 17811429]$ opatch apply -all_nodes


at No comments:

OCM Patching Procedure RAC

 How to apply a patch using ocm file
Run this script on below location
[grid@rac1pub108 bin]$ pwd
/u01/app/11204/grid/OPatch/ocm/bin
[grid@rac1pub108 bin]$
[grid@rac1pub108 bin]$ ./emocmrsp
The OCM configuration response file (ocm.rsp) was successfully created.
[grid@rac1pub108 bin]$ ls -ltr
total 16
-rwxr-x--- 1 grid oinstall 9063 Dec  5 14:26 emocmrsp
-rw-r--r-- 1 grid oinstall  621 Dec  6 14:59 ocm.rsp
[grid@rac1pub108 bin]$
 
Check invalid objects
SQL> select count(*) from all_objects where status='INVALID';
  COUNT(*)
----------
         0
SQL>
First copy database and grid patch in one folder
[root@rac1pub108 DB_GRID_PATCH]# ll
total 8
drwxr-xr-x 4 root root 4096 Dec  6 15:05 17811429
drwxr-xr-x 5 root root 4096 Dec  6 15:03 25920335
[root@rac1pub108 DB_GRID_PATCH]# chown -R grid:oinstall 25920335/
[root@rac1pub108 DB_GRID_PATCH]# chown -R oracle:oinstall 17811429/
[root@rac1pub108 DB_GRID_PATCH]# ll
total 8
drwxr-xr-x 4 oracle oinstall 4096 Dec  6 15:05 17811429
drwxr-xr-x 5 grid   oinstall 4096 Dec  6 15:03 25920335
[root@rac1pub108 DB_GRID_PATCH]#
[oracle@rac2pub111 u01]$ srvctl  status database -d prod
Instance prod1 is running on node rac1pub108
Instance prod2 is running on node rac2pub111
[oracle@rac2pub111 u01]$
[root@rac1pub108 u01]# opatch auto /u01/DB_GRID_PATCH/ -ocmrf /u01/app/11204/grid/OPatch/ocm/bin/ocm.rsp -oh /u01/app/11204/grid/ -oh /u01/app/oracle/product/11204/dbhome_1/
[root@rac1pub108 u01]# opatch auto /u01/DB_GRID_PATCH/ -ocmrf /u01/app/11204/grid/OPatch/ocm/bin/ocm.rsp -oh /u01/app/11204/grid/ -oh /u01/app/oracle/product/11204/dbhome_1/
Executing /u01/app/11204/grid/perl/bin/perl /u01/app/oracle/product/11204/dbhome_1/OPatch/crs/patch11203.pl -patchdir /u01 -patchn DB_GRID_PATCH -ocmrf /u01/app/11204/grid/OPatch/ocm/bin/ocm.rsp -oh /u01/app/11204/grid/ -oh /u01/app/oracle/product/11204/dbhome_1/ -paramfile /u01/app/11204/grid/crs/install/crsconfig_params
This is the main log file: /u01/app/oracle/product/11204/dbhome_1/cfgtoollogs/opatchauto2017-12-06_15-35-54.log
This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:
/u01/app/oracle/product/11204/dbhome_1/cfgtoollogs/opatchauto2017-12-06_15-35-54.report.log
2017-12-06 15:35:54: Starting Clusterware Patch Setup
Using configuration parameter file: /u01/app/11204/grid/crs/install/crsconfig_params
Enter 'yes' if you have unzipped this patch to an empty directory to proceed  (yes/no):yes
Enter 'yes' if you have unzipped this patch to an empty directory to proceed  (yes/no):yes
Stopping RAC /u01/app/oracle/product/11204/dbhome_1 ...
Stopped RAC /u01/app/oracle/product/11204/dbhome_1 successfully
patch /u01/DB_GRID_PATCH/18203835  apply successful for home  /u01/app/oracle/product/11204/dbhome_1
Starting RAC /u01/app/oracle/product/11204/dbhome_1 ...
Started RAC /u01/app/oracle/product/11204/dbhome_1 successfully
opatch auto succeeded.
[root@rac1pub108 u01]#
Grid patch auto
[root@rac1pub108 DB_GRID_PATCH]# opatch auto /u01/DB_GRID_PATCH/ -ocmrf /u01/app/11204/grid/OPatch/ocm/bin/ocm.rsp -oh /u01/app/11204/grid/
Executing /u01/app/11204/grid/perl/bin/perl /u01/app/oracle/product/11204/dbhome_1/OPatch/crs/patch11203.pl -patchdir /u01 -patchn DB_GRID_PATCH -ocmrf /u01/app/11204/grid/OPatch/ocm/bin/ocm.rsp -oh /u01/app/11204/grid/ -paramfile /u01/app/11204/grid/crs/install/crsconfig_params
This is the main log file: /u01/app/oracle/product/11204/dbhome_1/cfgtoollogs/opatchauto2017-12-06_15-41-43.log
This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:
/u01/app/oracle/product/11204/dbhome_1/cfgtoollogs/opatchauto2017-12-06_15-41-43.report.log
2017-12-06 15:41:43: Starting Clusterware Patch Setup
Using configuration parameter file: /u01/app/11204/grid/crs/install/crsconfig_params
Enter 'yes' if you have unzipped this patch to an empty directory to proceed  (yes/no):yes
Enter 'yes' if you have unzipped this patch to an empty directory to proceed  (yes/no):yes
Stopping CRS...
Stopped CRS successfully
patch /u01/DB_GRID_PATCH/25920335  apply successful for home  /u01/app/11204/grid
Starting CRS...
Installing Trace File Analyzer
CRS-4123: Oracle High Availability Services has been started.
opatch auto succeeded.
[root@rac1pub108 bin]# tail -f /u01/app/oracle/product/11204/dbhome_1/cfgtoollogs/opatchauto2017-12-06_15-28-34.report.log
***********  Configuration Data  ***********
* It shows only those targets that will be patched in this session *
rac_home=/u01/app/oracle/product/11204/dbhome_1      owner=oracle      opatch_ver=11.2.0.3.17
*********** Steps to be executed as owner unless specified as root ***********


1: /u01/app/oracle/product/11204/dbhome_1/OPatch/opatch prereq CheckComponents -ph /u01/DB_GRID_PATCH/17811429 -oh /u01/app/oracle/product/11204/dbhome_1
2: /u01/app/oracle/product/11204/dbhome_1/OPatch/opatch prereq CheckComponents -ph /u01/DB_GRID_PATCH/25920335 -oh /u01/app/oracle/product/11204/dbhome_1
:q!
^[:L^C
[root@rac1pub108 bin]# tail -f /u01/app/oracle/product/11204/dbhome_1/cfgtoollogs/opatchauto2017-12-06_15-41-43.report.log
***********  Configuration Data  ***********
* It shows only those targets that will be patched in this session *

crs_home=/u01/app/11204/grid      owner=grid      opatch_ver=11.2.0.3.17
*********** Steps to be executed as owner unless specified as root ***********

1: /u01/app/11204/grid/OPatch/opatch prereq CheckComponents -ph /u01/DB_GRID_PATCH/25920335 -oh /u01/app/11204/grid
2: /u01/app/11204/grid/OPatch/opatch prereq CheckConflictAgainstOH -ph /u01/DB_GRID_PATCH/25920335 -oh /u01/app/11204/grid
3: /u01/app/11204/grid/crs/install/rootcrs.pl -unlock : run as root
4: /u01/app/11204/grid/tfa/bin/tfactl stop : run as root
5: /sbin/fuser -k /u01/app/11204/grid/bin/crsctl.bin : run as root
6: /u01/app/11204/grid/OPatch/opatch prereq CheckApplicable -ph /u01/DB_GRID_PATCH/25920335 -oh /u01/app/11204/grid
7: /u01/app/11204/grid/OPatch/opatch napply /u01/DB_GRID_PATCH/25920335 -local -silent -ocmrf /u01/app/11204/grid/OPatch/ocm/bin/ocm.rsp -oh /u01/app/11204/grid -invPtrLoc /u01/app/11204/grid/oraInst.loc
8: /u01/app/11204/grid/bin/emctl start dbconsole
9: /u01/app/11204/grid/rdbms/install/rootadd_rdbms.sh : run as root
10: /u01/app/11204/grid/crs/install/rootcrs.pl -patch : run as root



at No comments:

TAF Policy | RAC

 PUNE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racscan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pune)
      (FAILOVER_MODE=(METHOD=preconnect)(TYPE=select))
     (LOAD_BALANCE=YES)
     (FAILOVER=ON)

On differet window of node 1:
-----------------------------

sqlpulus shoaib/shoaib@pune
sql> select * from dba_sources;



On node 2:
----------
sqlplus / as sysdba


sql>
COLUMN inst_id    FORMAT 13
COLUMN username FORMAT a9
COLUMN service_name     FORMAT a9
COLUMN failover_type    FORMAT a15
COLUMN failover_method  FORMAT a11
COLUMN failed_over FORMAT a11

select inst_id,username,service_name,failover_type,failover_method,failed_over from gv$session where username='SHOAIB';

   INST_ID USERNAME  SERVICE_N FAILOVER_TYPE   FAILOVER_ME FAILED_OVER
---------- --------- --------- --------------- ----------- -----------
         2 SHOAIB    pune      NONE            NONE        NO
         1 SHOAIB    pune      SELECT          PRECONNECT  NO


On node 1:
----------
sqlplus / as sysdba
sql> shut immediate;




On node 2:
----------

sql> COLUMN inst_id     FORMAT 13
COLUMN username         FORMAT a9
COLUMN service_name     FORMAT a9
COLUMN failover_type    FORMAT a15
COLUMN failover_method  FORMAT a11
COLUMN failed_over      FORMAT a11

select inst_id,username,service_name,failover_type,failover_method,failed_over from gv$session where username='SHOAIB';

INST_ID USERNAME  SERVICE_N FAILOVER_TYPE   FAILOVER_ME FAILED_OVER
---------- --------- --------- --------------- ----------- -----------
         2 SHOAIB    pune      SELECT          PRECONNECT  YES




at No comments:

RAC patching 11g

 [grid@rac1pub101 u01]$ vi grid.env
export ORACLE_SID=+ASM1
export ORACLE_HOME=/u01/app/11203/grid
export PATH=$PATH:$ORACLE_HOME/bin:.
export PATH=$PATH:$ORACLE_HOME/OPatch

[grid@rac1pub101 u01]$ . grid.env
[grid@rac1pub101 u01]$ opatch version
OPatch Version: 11.2.0.3.4
OPatch succeeded.

[grid@rac1pub101 grid]$ cd $ORACLE_HOME
[grid@rac1pub101 grid]$ mv OPatch/ OPatch_bkp
[grid@rac1pub101 grid]$ cd /u01/Patch/
[grid@rac1pub101 Patch]$ cp -rf OPatch/ $ORACLE_HOME
[grid@rac1pub101 u01]$ opatch version
OPatch Version: 11.2.0.3.17
OPatch succeeded.
[root@rac1pub101 u01]# crsctl stat resource -t
ora.pune.db
      1        ONLINE  ONLINE       rac1pub101               Open               
      2        ONLINE  ONLINE       rac2pub102               Open 

[root@rac1pub101 u01]# cd /u01/app/11203/grid/crs/install/
[root@rac1pub101 install]# ./rootcrs.pl -unlock
Using configuration parameter file: ./crsconfig_params
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac1pub101'
CRS-2673: Attempting to stop 'ora.crsd' on 'rac1pub101'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac1pub101'
CRS-2673: Attempting to stop 'ora.oc4j' on 'rac1pub101'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'rac1pub101'
CRS-2673: Attempting to stop 'ora.cvu' on 'rac1pub101'
CRS-2673: Attempting to stop 'ora.CRS.dg' on 'rac1pub101'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'rac1pub101'
CRS-2673: Attempting to stop 'ora.pune.db' on 'rac1pub101'
CRS-2677: Stop of 'ora.cvu' on 'rac1pub101' succeeded
CRS-2672: Attempting to start 'ora.cvu' on 'rac2pub102'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'rac1pub101' succeeded
CRS-2673: Attempting to stop 'ora.rac1pub101.vip' on 'rac1pub101'
CRS-2677: Stop of 'ora.rac1pub101.vip' on 'rac1pub101' succeeded
CRS-2672: Attempting to start 'ora.rac1pub101.vip' on 'rac2pub102'
CRS-2677: Stop of 'ora.registry.acfs' on 'rac1pub101' succeeded
CRS-2677: Stop of 'ora.pune.db' on 'rac1pub101' succeeded
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'rac1pub101'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'rac1pub101'
CRS-2676: Start of 'ora.cvu' on 'rac2pub102' succeeded
CRS-2676: Start of 'ora.rac1pub101.vip' on 'rac2pub102' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'rac1pub101' succeeded
CRS-2677: Stop of 'ora.FRA.dg' on 'rac1pub101' succeeded
CRS-2677: Stop of 'ora.CRS.dg' on 'rac1pub101' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac1pub101'
CRS-2677: Stop of 'ora.asm' on 'rac1pub101' succeeded
CRS-2677: Stop of 'ora.oc4j' on 'rac1pub101' succeeded
CRS-2672: Attempting to start 'ora.oc4j' on 'rac2pub102'
CRS-2676: Start of 'ora.oc4j' on 'rac2pub102' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'rac1pub101'
CRS-2677: Stop of 'ora.ons' on 'rac1pub101' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'rac1pub101'
CRS-2677: Stop of 'ora.net1.network' on 'rac1pub101' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac1pub101' has completed
CRS-2677: Stop of 'ora.crsd' on 'rac1pub101' succeeded
CRS-2673: Attempting to stop 'ora.mdnsd' on 'rac1pub101'
CRS-2673: Attempting to stop 'ora.crf' on 'rac1pub101'
CRS-2673: Attempting to stop 'ora.ctssd' on 'rac1pub101'
CRS-2673: Attempting to stop 'ora.evmd' on 'rac1pub101'
CRS-2673: Attempting to stop 'ora.asm' on 'rac1pub101'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'rac1pub101'
CRS-2677: Stop of 'ora.mdnsd' on 'rac1pub101' succeeded
CRS-2677: Stop of 'ora.evmd' on 'rac1pub101' succeeded
CRS-2677: Stop of 'ora.crf' on 'rac1pub101' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'rac1pub101' succeeded
CRS-2677: Stop of 'ora.asm' on 'rac1pub101' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'rac1pub101'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'rac1pub101' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rac1pub101'
CRS-2677: Stop of 'ora.drivers.acfs' on 'rac1pub101' succeeded
CRS-2677: Stop of 'ora.cssd' on 'rac1pub101' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'rac1pub101'
CRS-2677: Stop of 'ora.gipcd' on 'rac1pub101' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'rac1pub101'
CRS-2677: Stop of 'ora.gpnpd' on 'rac1pub101' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac1pub101' has completed
CRS-4133: Oracle High Availability Services has been stopped.
Successfully unlock /u01/app/11203/grid

[grid@rac1pub101 22502505]$ pwd
/u01/26030799/22502505


[grid@rac1pub101 22502505]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.17
Copyright (c) 2017, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/11203/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/11203/grid/oraInst.loc
OPatch version    : 11.2.0.3.17
OUI version       : 11.2.0.4.0
Log file location : /u01/app/11203/grid/cfgtoollogs/opatch/opatch2017-12-05_16-11-00PM_1.log
Lsinventory Output file location : /u01/app/11203/grid/cfgtoollogs/opatch/lsinv/lsinventory2017-12-05_16-11-00PM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: rac1pub101.dba.com
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Grid Infrastructure 11g                                       11.2.0.4.0
There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

--------------------------------------------------------------------------------
OPatch succeeded.


[grid@rac1pub101 22502505]$ opatch apply -local



[grid@rac1pub101 22502505]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.17
Copyright (c) 2017, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/11203/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/11203/grid/oraInst.loc
OPatch version    : 11.2.0.3.17
OUI version       : 11.2.0.4.0
Log file location : /u01/app/11203/grid/cfgtoollogs/opatch/opatch2017-12-05_16-19-08PM_1.log
Lsinventory Output file location : /u01/app/11203/grid/cfgtoollogs/opatch/lsinv/lsinventory2017-12-05_16-19-08PM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: rac1pub101.dba.com
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Grid Infrastructure 11g                                       11.2.0.4.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :
Patch  22502505     : applied on Tue Dec 05 16:16:15 IST 2017
Unique Patch ID:  19880366
Patch description:  "ACFS Patch Set Update : 11.2.0.4.160419 (22502505)"
   Created on 17 Feb 2016, 00:12:23 hrs PST8PDT
   Bugs fixed:
     21369858, 16318126, 19690653, 17503605, 17203009, 17359415, 20140148
     17611362, 17164243, 19053182, 17696547, 17488768, 18168684, 21519796
     18143006, 21208140, 17428148, 17070158, 20438706, 17510275, 17172303
     18610307, 17376318, 17721778, 22198405, 17699423, 18915417, 18155334
     18321597, 19919907, 18185024, 17636008, 17363999, 20681968, 17475946


--------------------------------------------------------------------------------
OPatch succeeded.


[grid@rac1pub101 25869727]$ opatch apply -local
Composite patch 25869727 successfully applied.
Log file location: /u01/app/11203/grid/cfgtoollogs/opatch/opatch2017-12-05_16-22-11PM_1.log
OPatch succeeded.

[grid@rac1pub101 25869727]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.17
Copyright (c) 2017, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/11203/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/11203/grid/oraInst.loc
OPatch version    : 11.2.0.3.17
OUI version       : 11.2.0.4.0
Log file location : /u01/app/11203/grid/cfgtoollogs/opatch/opatch2017-12-05_17-00-40PM_1.log
Lsinventory Output file location : /u01/app/11203/grid/cfgtoollogs/opatch/lsinv/lsinventory2017-12-05_17-00-40PM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: rac1pub101.dba.com
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Grid Infrastructure 11g                                       11.2.0.4.0
There are 1 products installed in this Oracle Home.

Interim patches (2) :
Patch  25869727     : applied on Tue Dec 05 16:58:18 IST 2017
Unique Patch ID:  21364644
Patch description:  "Database Patch Set Update : 11.2.0.4.170718 (25869727)"
   Created on 21 Jun 2017, 01:40:19 hrs PST8PDT
Sub-patch  24732075; "Database Patch Set Update : 11.2.0.4.170418 (24732075)"
Sub-patch  24006111; "Database Patch Set Update : 11.2.0.4.161018 (24006111)"
Sub-patch  23054359; "Database Patch Set Update : 11.2.0.4.160719 (23054359)"
Sub-patch  22502456; "Database Patch Set Update : 11.2.0.4.160419 (22502456)"
Sub-patch  21948347; "Database Patch Set Update : 11.2.0.4.160119 (21948347)"
Sub-patch  21352635; "Database Patch Set Update : 11.2.0.4.8 (21352635)"
Sub-patch  20760982; "Database Patch Set Update : 11.2.0.4.7 (20760982)"
Sub-patch  20299013; "Database Patch Set Update : 11.2.0.4.6 (20299013)"
Sub-patch  19769489; "Database Patch Set Update : 11.2.0.4.5 (19769489)"
Sub-patch  19121551; "Database Patch Set Update : 11.2.0.4.4 (19121551)"
Sub-patch  18522509; "Database Patch Set Update : 11.2.0.4.3 (18522509)"
Sub-patch  18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)"
Sub-patch  17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"
   Bugs fixed:
     17184721, 21538558, 16091637, 18092127, 17381384, 15979965, 16731148
     13837378, 18441944, 16314254, 17835048, 13558557, 17201159, 25427662
     17853498, 17246576, 18356166, 18440047, 18681862, 20387265, 16875449
     19788842, 17296856, 21330264, 14010183, 17648596, 17551063, 17025461
     17267114, 22507210, 17912217, 17889583, 18202441, 17040764, 25655390
     16524926, 17478145, 19358317, 22148226, 18747196, 18641419, 17036973
     17811789, 14285317, 16542886, 18009564, 16618694, 8322815, 16832076
     16692232, 18247991, 22507234, 17570240, 13871092, 17848897, 17441661
     14034426, 17465741, 16596890, 17437634, 20506706, 21343897, 21453153
     18339044, 22321741, 17951233, 18430495, 21787056, 22380919, 19469538
     20506715, 17811429, 19721304, 18230522, 19554106, 19458377, 17612828
     6599380, 22092979, 22321756, 17040527, 17811438, 18641461, 14657740
     13364795, 21387964, 17346671, 17588480, 18235390, 17889549, 19309466
     16472716, 20596234, 18331850, 18641451, 17344412, 19461270, 21179898
     17546761, 24842886, 14521849, 18203835, 18964939, 18203838, 18203837
     17313525, 22195457, 18139690, 16837842, 22296366, 14106803, 17842825
     22657942, 21352646, 20657441, 16360112, 22195441, 17389192, 14565184
     17205719, 18440095, 22195448, 14354737, 14764829, 13944971, 16571443
     21868720, 17186905, 17080436, 18673342, 22905130, 17027426, 19972569
     19972568, 19972566, 17282229, 19972564, 16870214, 19615136, 17390431
     18762750, 16613964, 18098207, 17957017, 18471685, 19730508, 21538485
     18264060, 17323222, 17754782, 17600719, 18317531, 17852463, 17596908
     17655634, 16228604, 20074391, 19972570, 18996843, 19854503, 16042673
     17835627, 20334344, 18000422, 20861693, 17393683, 17551709, 20506699
     19006849, 18456514, 18277454, 17258090, 17174582, 17242746, 16399083
     17824637, 17762296, 22168163, 17397545, 16450169, 12364061, 20067212
     18856999, 19211724, 19463893, 21343775, 19463897, 17853456, 18673304
     20004021, 26030218, 21668627, 16194160, 17477958, 16538760, 12982566
     18259031, 20296213, 18293054, 17610798, 19699191, 18135678, 17311728
     16785708, 10136473, 19777862, 24560906, 17786518, 18315328, 18334586
     12747740, 19032867, 18096714, 18899974, 17390160, 17232014, 16422541
     18673325, 18155762, 14015842, 19827973, 22683225, 17726838, 18554871
     23177648, 18051556, 20803583, 17922254, 15990359, 21972320, 18282562
     16855292, 16668584, 21343838, 20299015, 18043064, 17446237, 18093615
     17694209, 17288409, 17274537, 13955826, 16934803, 17634921, 17501491
     16315398, 22683212, 17006183, 13829543, 18191164, 17655240, 19393542
     18384391, 21538567, 16198143, 21847223, 25823754, 17892268, 20142975
     19584068, 17165204, 18508861, 21756699, 16901385, 18554763, 18189036
     17443671, 17385178, 17936109, 14829250, 20925795, 20509482, 17478514
     16850630, 13951456, 16595641, 15861775, 14054676, 16912439, 17299889
     17297939, 18619917, 16833527, 17798953, 17816865, 18607546, 17571306
     21286665, 17341326, 17851160, 20558005, 17586955, 19049453, 21051840
     17587063, 16956380, 18328509, 25423453, 14133975, 18061914, 21051833
     18522509, 18765602, 18199537, 17332800, 13609098, 18384537, 22502493
     14338435, 17945983, 21067387, 16392068, 17752995, 21051862, 25505382
     17237521, 16863422, 18244962, 19544839, 24433711, 17156148, 18973907
     17877323, 17449815, 18180390, 17088068, 17037130, 20004087, 19466309
     25505371, 11733603, 18084625, 21051858, 18674024, 21051852, 18091059
     16306373, 25369547, 18306996, 19915271, 18193833, 17787259, 20631274
     25879656, 16344544, 14692762, 18614015, 17346091, 18228645, 17721717
     18436307, 19888853, 21756677, 11883252, 17891943, 22353199, 16384983
     19121551, 12816846, 17982555, 17761775, 22243719, 17265217, 25505394
     17071721, 16721594, 21756661, 18262334, 15913355, 17891946, 17672719
     17602269, 17239687, 17042658, 17238511, 17811456, 17284817, 20879889
     17752121, 17394950, 16579084, 17011832, 22195465, 14602788, 18325460
     24476265, 24476274, 12611721, 16903536, 19689979, 17006570, 16043574
     18783224, 16494615, 21526048, 19197175, 16069901, 17811447, 17308789
     22195477, 17865671, 19013183, 17343514, 17325413, 18316692, 16180763
     17348614, 14368995, 21983325, 17393915, 16285691, 20331945, 17883081
     24316947, 17705023, 17614227, 19578350, 22195485, 14084247, 13645875
     16777840, 19727057, 14852021, 18744139, 18674047, 17716305, 18482502
     17622427, 19289642, 22195492, 14458214, 18723434, 17767676, 25505407
     19258504, 17786278, 17082983, 17365043, 21351877, 13498382, 18331812
     16065166, 16685417, 18031668, 22893153, 16943711, 21517440, 17649265
     13866822, 18094246, 24528741, 14245531, 17783588, 17082359, 20448824
     18280813, 23330119, 16268425, 25600421, 17302277, 18018515, 17215560
     24411921, 19271443, 17016369, 20777150, 23330124, 20441797, 19769489
     25093656, 17545847, 18260550, 13853126, 23536835, 17227277, 25957038
     19207117, 9756271, 18868646, 17614134, 17546973, 18704244, 19680952
     17050888, 18828868, 18273830, 17360606, 16992075, 17375354, 12905058
     18362222, 17571039, 17468141, 18436647, 17235750, 21168487, 16220077
     16929165
Patch  22502505     : applied on Tue Dec 05 16:16:15 IST 2017
Unique Patch ID:  19880366
Patch description:  "ACFS Patch Set Update : 11.2.0.4.160419 (22502505)"
   Created on 17 Feb 2016, 00:12:23 hrs PST8PDT
   Bugs fixed:
     21369858, 16318126, 19690653, 17503605, 17203009, 17359415, 20140148
     17611362, 17164243, 19053182, 17696547, 17488768, 18168684, 21519796
     18143006, 21208140, 17428148, 17070158, 20438706, 17510275, 17172303
     18610307, 17376318, 17721778, 22198405, 17699423, 18915417, 18155334
     18321597, 19919907, 18185024, 17636008, 17363999, 20681968, 17475946


--------------------------------------------------------------------------------
OPatch succeeded.



[grid@rac1pub101 25920335]$ opatch apply -local


Composite patch 25869727 successfully applied.
Log file location: /u01/app/11203/grid/cfgtoollogs/opatch/opatch2017-12-05_16-22-11PM_1.log
OPatch succeeded.

[grid@rac1pub101 25920335]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.17
Copyright (c) 2017, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/11203/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/11203/grid/oraInst.loc
OPatch version    : 11.2.0.3.17
OUI version       : 11.2.0.4.0
Log file location : /u01/app/11203/grid/cfgtoollogs/opatch/opatch2017-12-05_17-06-31PM_1.log
Lsinventory Output file location : /u01/app/11203/grid/cfgtoollogs/opatch/lsinv/lsinventory2017-12-05_17-06-31PM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: rac1pub101.dba.com
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Grid Infrastructure 11g                                       11.2.0.4.0
There are 1 products installed in this Oracle Home.

Interim patches (3) :
Patch  25920335     : applied on Tue Dec 05 17:05:15 IST 2017
Unique Patch ID:  21279331
Patch description:  "OCW Patch Set Update : 11.2.0.4.170718 (25920335)"
   Created on 27 Jun 2017, 05:40:19 hrs PST8PDT
   Bugs fixed:
     19270660, 18328800, 18508710, 18691572, 20038451, 21251192, 22162062
     21232394, 20365005, 17750548, 17387214, 17617807, 14497275, 20219458
     17733927, 18180541, 23757020, 18962892, 17292250, 17378618, 16759171
     20110156, 17843489, 17065496, 21694632, 13991403, 17273020, 22762046
     17155238, 18261183, 18053580, 20012766, 21245437, 20218012, 17013634
     17886392, 20995001, 17039197, 17947785, 16317771, 10052729, 22353346
     20340620, 16237657, 20317221, 15917869, 18199185, 18399991, 20186278
     18024089, 17374271, 16849642, 20246071, 20746251, 14270845, 23621494
     20552947, 18414137, 18882642, 17001914, 17927970, 14378120, 16346413
     15986647, 18068871, 21222147, 18143836, 16206997, 21982225, 25490238
     19168690, 20235511, 18343490, 21875360, 16613232, 19276791, 17722664
     20440643, 12928658, 18952577, 16249829, 18520351, 18226143, 18265482
     16076412, 18229842, 17172091, 20676340, 17818075, 20091753, 18231837
     14373486, 20136892, 17483479, 20551654, 18120545, 18729166, 13843841
     21225209, 17405302, 18709496, 18330979, 18744838, 14525998, 18187697
     17087371, 20531190, 20598625, 14385860, 18348155, 19479503, 12928592
     17516024, 18370031, 17764053, 19272663, 17551223, 14671408, 18272135
     14207615, 21255373, 17500165, 18875012, 14769643, 25656952, 19558324
     18464784, 18848125, 19241857, 17955615, 14851828, 20315294, 14693336
     20014326, 17352230, 16284825, 17238586, 17089344, 17405605, 21327402
     17531342, 19398098, 17640316, 17159489, 13823394, 16543190, 22024217
     17983675, 20795241, 16281493, 17598201, 18346135, 17481314, 15986311
     19601468, 17208793, 18700935, 18999857, 14076173, 18428146, 18352845
     17435488, 17592037, 20408163, 18352846, 19616601, 17391726, 17387779
     15851860, 14777968, 20141091, 16206882, 19885321, 21113068, 20175174
     17305100, 15832129, 16901346, 25591658, 17985714, 18536826, 17780903
     18752378, 18946768, 16875342, 16876500, 17769597, 19955755, 16429265
     18336452, 17273003, 17209968, 25484507, 17059927, 17046460, 20094984
     19319357, 16988311, 18053631, 16867761, 18774591, 20235486, 21442094
     19359787, 15869775, 19642566, 17447588, 21152052, 16798862, 15920201
Patch  25869727     : applied on Tue Dec 05 16:58:18 IST 2017
Unique Patch ID:  21364644
Patch description:  "Database Patch Set Update : 11.2.0.4.170718 (25869727)"
   Created on 21 Jun 2017, 01:40:19 hrs PST8PDT
Sub-patch  24732075; "Database Patch Set Update : 11.2.0.4.170418 (24732075)"
Sub-patch  24006111; "Database Patch Set Update : 11.2.0.4.161018 (24006111)"
Sub-patch  23054359; "Database Patch Set Update : 11.2.0.4.160719 (23054359)"
Sub-patch  22502456; "Database Patch Set Update : 11.2.0.4.160419 (22502456)"
Sub-patch  21948347; "Database Patch Set Update : 11.2.0.4.160119 (21948347)"
Sub-patch  21352635; "Database Patch Set Update : 11.2.0.4.8 (21352635)"
Sub-patch  20760982; "Database Patch Set Update : 11.2.0.4.7 (20760982)"
Sub-patch  20299013; "Database Patch Set Update : 11.2.0.4.6 (20299013)"
Sub-patch  19769489; "Database Patch Set Update : 11.2.0.4.5 (19769489)"
Sub-patch  19121551; "Database Patch Set Update : 11.2.0.4.4 (19121551)"
Sub-patch  18522509; "Database Patch Set Update : 11.2.0.4.3 (18522509)"
Sub-patch  18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)"
Sub-patch  17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"
   Bugs fixed:
     17184721, 21538558, 16091637, 18092127, 17381384, 15979965, 16731148
     13837378, 18441944, 16314254, 17835048, 13558557, 17201159, 25427662
     17853498, 17246576, 18356166, 18440047, 18681862, 20387265, 16875449
     19788842, 17296856, 21330264, 14010183, 17648596, 17551063, 17025461
     17267114, 22507210, 17912217, 17889583, 18202441, 17040764, 25655390
     16524926, 17478145, 19358317, 22148226, 18747196, 18641419, 17036973
     17811789, 14285317, 16542886, 18009564, 16618694, 8322815, 16832076
     16692232, 18247991, 22507234, 17570240, 13871092, 17848897, 17441661
     14034426, 17465741, 16596890, 17437634, 20506706, 21343897, 21453153
     18339044, 22321741, 17951233, 18430495, 21787056, 22380919, 19469538
     20506715, 17811429, 19721304, 18230522, 19554106, 19458377, 17612828
     6599380, 22092979, 22321756, 17040527, 17811438, 18641461, 14657740
     13364795, 21387964, 17346671, 17588480, 18235390, 17889549, 19309466
     16472716, 20596234, 18331850, 18641451, 17344412, 19461270, 21179898
     17546761, 24842886, 14521849, 18203835, 18964939, 18203838, 18203837
     17313525, 22195457, 18139690, 16837842, 22296366, 14106803, 17842825
     22657942, 21352646, 20657441, 16360112, 22195441, 17389192, 14565184
     17205719, 18440095, 22195448, 14354737, 14764829, 13944971, 16571443
     21868720, 17186905, 17080436, 18673342, 22905130, 17027426, 19972569
     19972568, 19972566, 17282229, 19972564, 16870214, 19615136, 17390431
     18762750, 16613964, 18098207, 17957017, 18471685, 19730508, 21538485
     18264060, 17323222, 17754782, 17600719, 18317531, 17852463, 17596908
     17655634, 16228604, 20074391, 19972570, 18996843, 19854503, 16042673
     17835627, 20334344, 18000422, 20861693, 17393683, 17551709, 20506699
     19006849, 18456514, 18277454, 17258090, 17174582, 17242746, 16399083
     17824637, 17762296, 22168163, 17397545, 16450169, 12364061, 20067212
     18856999, 19211724, 19463893, 21343775, 19463897, 17853456, 18673304
     20004021, 26030218, 21668627, 16194160, 17477958, 16538760, 12982566
     18259031, 20296213, 18293054, 17610798, 19699191, 18135678, 17311728
     16785708, 10136473, 19777862, 24560906, 17786518, 18315328, 18334586
     12747740, 19032867, 18096714, 18899974, 17390160, 17232014, 16422541
     18673325, 18155762, 14015842, 19827973, 22683225, 17726838, 18554871
     23177648, 18051556, 20803583, 17922254, 15990359, 21972320, 18282562
     16855292, 16668584, 21343838, 20299015, 18043064, 17446237, 18093615
     17694209, 17288409, 17274537, 13955826, 16934803, 17634921, 17501491
     16315398, 22683212, 17006183, 13829543, 18191164, 17655240, 19393542
     18384391, 21538567, 16198143, 21847223, 25823754, 17892268, 20142975
     19584068, 17165204, 18508861, 21756699, 16901385, 18554763, 18189036
     17443671, 17385178, 17936109, 14829250, 20925795, 20509482, 17478514
     16850630, 13951456, 16595641, 15861775, 14054676, 16912439, 17299889
     17297939, 18619917, 16833527, 17798953, 17816865, 18607546, 17571306
     21286665, 17341326, 17851160, 20558005, 17586955, 19049453, 21051840
     17587063, 16956380, 18328509, 25423453, 14133975, 18061914, 21051833
     18522509, 18765602, 18199537, 17332800, 13609098, 18384537, 22502493
     14338435, 17945983, 21067387, 16392068, 17752995, 21051862, 25505382
     17237521, 16863422, 18244962, 19544839, 24433711, 17156148, 18973907
     17877323, 17449815, 18180390, 17088068, 17037130, 20004087, 19466309
     25505371, 11733603, 18084625, 21051858, 18674024, 21051852, 18091059
     16306373, 25369547, 18306996, 19915271, 18193833, 17787259, 20631274
     25879656, 16344544, 14692762, 18614015, 17346091, 18228645, 17721717
     18436307, 19888853, 21756677, 11883252, 17891943, 22353199, 16384983
     19121551, 12816846, 17982555, 17761775, 22243719, 17265217, 25505394
     17071721, 16721594, 21756661, 18262334, 15913355, 17891946, 17672719
     17602269, 17239687, 17042658, 17238511, 17811456, 17284817, 20879889
     17752121, 17394950, 16579084, 17011832, 22195465, 14602788, 18325460
     24476265, 24476274, 12611721, 16903536, 19689979, 17006570, 16043574
     18783224, 16494615, 21526048, 19197175, 16069901, 17811447, 17308789
     22195477, 17865671, 19013183, 17343514, 17325413, 18316692, 16180763
     17348614, 14368995, 21983325, 17393915, 16285691, 20331945, 17883081
     24316947, 17705023, 17614227, 19578350, 22195485, 14084247, 13645875
     16777840, 19727057, 14852021, 18744139, 18674047, 17716305, 18482502
     17622427, 19289642, 22195492, 14458214, 18723434, 17767676, 25505407
     19258504, 17786278, 17082983, 17365043, 21351877, 13498382, 18331812
     16065166, 16685417, 18031668, 22893153, 16943711, 21517440, 17649265
     13866822, 18094246, 24528741, 14245531, 17783588, 17082359, 20448824
     18280813, 23330119, 16268425, 25600421, 17302277, 18018515, 17215560
     24411921, 19271443, 17016369, 20777150, 23330124, 20441797, 19769489
     25093656, 17545847, 18260550, 13853126, 23536835, 17227277, 25957038
     19207117, 9756271, 18868646, 17614134, 17546973, 18704244, 19680952
     17050888, 18828868, 18273830, 17360606, 16992075, 17375354, 12905058
     18362222, 17571039, 17468141, 18436647, 17235750, 21168487, 16220077
     16929165
Patch  22502505     : applied on Tue Dec 05 16:16:15 IST 2017
Unique Patch ID:  19880366
Patch description:  "ACFS Patch Set Update : 11.2.0.4.160419 (22502505)"
   Created on 17 Feb 2016, 00:12:23 hrs PST8PDT
   Bugs fixed:
     21369858, 16318126, 19690653, 17503605, 17203009, 17359415, 20140148
     17611362, 17164243, 19053182, 17696547, 17488768, 18168684, 21519796
     18143006, 21208140, 17428148, 17070158, 20438706, 17510275, 17172303
     18610307, 17376318, 17721778, 22198405, 17699423, 18915417, 18155334
     18321597, 19919907, 18185024, 17636008, 17363999, 20681968, 17475946


--------------------------------------------------------------------------------
OPatch succeeded.

[root@rac1pub101 u01]# cd /u01/app/11203/grid/crs/install/

[root@rac1pub101 install]# ./rootcrs.pl -patch
Using configuration parameter file: ./crsconfig_params
Installing Trace File Analyzer
CRS-4123: Oracle High Availability Services has been started.

at No comments:

How to change DISKGROUP name RAC

 [grid@rac1pub108 ~]$ cd /u01

[grid@rac1pub108 u01]$ . grid.env

[grid@rac1pub108 u01]$ asmcmd umount DATA

[grid@rac1pub108 u01]$ asmcmd lsdg

State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name

MOUNTED  NORMAL  N         512   4096  1048576      3069     2143             1023             560              0             Y  CRS/

MOUNTED  NORMAL  N         512   4096  1048576    159990   151538                0           75769              0             N  FRA/

[grid@rac1pub108 u01]$ renamedg phase=both dgname=DATA newdgname=DATADG verbose=true


Parsing parameters..


Parameters in effect:


         Old DG name       : DATA

         New DG name          : DATADG

         Phases               :

                 Phase 1

                 Phase 2

         Discovery str        : (null)

         Clean              : TRUE

         Raw only           : TRUE

renamedg operation: phase=both dgname=DATA newdgname=DATADG verbose=true

Executing phase 1

Discovering the group

Performing discovery with string:

Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA1 with disk number:0 and timestamp (33058144 -463167488)

Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA2 with disk number:1 and timestamp (33058144 -463167488)

Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA3 with disk number:2 and timestamp (33058144 -463167488)

Checking for hearbeat...

Re-discovering the group

Performing discovery with string:

Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA1 with disk number:0 and timestamp (33058144 -463167488)

Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA2 with disk number:1 and timestamp (33058144 -463167488)

Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA3 with disk number:2 and timestamp (33058144 -463167488)

Checking if the diskgroup is mounted or used by CSS

Checking disk number:0

Checking disk number:1

Checking disk number:2

Generating configuration file..

Completed phase 1

Executing phase 2

Looking for ORCL:DATA1

Modifying the header

Looking for ORCL:DATA2

Modifying the header

Looking for ORCL:DATA3

Modifying the header

Completed phase 2

Terminating kgfd context 0x7f82a23370a0

[grid@rac1pub108 u01]$  asmcmd mount DATADG

[grid@rac1pub108 u01]$ asmcmd lsdg

State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name

MOUNTED  NORMAL  N         512   4096  1048576      3069     2143             1023             560              0             Y  CRS/

MOUNTED  NORMAL  N         512   4096  1048576    239985   215617            79995           67811              0             N  DATADG/

MOUNTED  NORMAL  N         512   4096  1048576    159990   151538                0           75769              0             N  FRA/

[grid@rac1pub108 u01]$asmcmd mount DATADG


at No comments:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

 root@pandba:/u01/app/oracle/product/12.1.0/dbhome_1/network/admin#

root@pandba:/u01/app/oracle/product/12.1.0/dbhome_1/network/admin# more listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.


LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = pandba)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )



SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

       (GLOBAL_DBNAME = GISUAT)

       (SID_NAME = GISUAT))

     (SID_DESC =

        (GLOBAL_DBNAME = PDBORCL)

        (SID_NAME = PDBORCL))

  )




ENABLE_GLOBAL_DYNAMIC_ENDPOINT_PDB_LISTENER=ON          # line added by Agent

VALID_NODE_CHECKING_REGISTRATION_PDB_LISTENER=SUBNET           # line added by Agent


root@pandba:/u01/app/oracle/product/12.1.0/dbhome_1/network/admin#



root@pandba:/u01/app/oracle/product/12.1.0/dbhome_1/network/admin# more tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.


GISUAT =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = pandba)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = GISUAT)

    )

  )


PDBORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = pandba)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PDBORCL)

    )

  )

root@pandba:/u01/app/oracle/product/12.1.0/dbhome_1/network/admin#



root@pandba:/u01/app/oracle/product/12.1.0/dbhome_1/network/admin# more sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.


#NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=11

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

root@pandba:/u01/app/oracle/product/12.1.0/dbhome_1/network/admin#



oracle@pandba:~$ sqlplus gisadmin/password@PDBORCL


SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 27 08:07:05 2020


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



Connected to:

Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production


SQL> show user;

USER is "GISADMIN"

SQL> exit

Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

oracle@pandba:~$ m


at No comments:

Wednesday, September 30, 2020

list of dba users ACCOUNT_STATUS Expired

 


SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE,PROFILE,LOCK_DATE from dba_users;


USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA PROFILE                        LOCK_DATE

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

SYS                            OPEN                             22-OCT-15 DEFAULT

SYSTEM                         OPEN                             30-AUG-17 DEFAULT

OUTLN                          OPEN                             03-OCT-15 DEFAULT

DBSNMP                         OPEN                             03-OCT-15 DEFAULT

ABM                            OPEN                             09-SEP-04 DEFAULT

AHL                            OPEN                             09-SEP-04 DEFAULT

ORDSYS                         OPEN                             03-OCT-15 DEFAULT

ORDPLUGINS                     OPEN                             03-OCT-15 DEFAULT

MDSYS                          OPEN                             03-OCT-15 DEFAULT

at No comments:

E-Business Suite : Responsibilities Are Not Visible When End Date Is Removed

1. Run the request ‘Sync responsibility role data into the WF table’.

2. Run the request “Synchronize WF LOCAL tables”.

3. Run the request “Workflow Directory Services User/Role Validation” (with parameters 10000, yes,yes, yes).

4. Have the user log off and back on and verify that the responsibility appears.


at No comments:

Query to check long running sql

 

Concurrent programs running for + hours

col PROGRAM for a12;
col ACTION for a12;
col MODULE  for a12;
col EVENT for a12;
select status,sid,serial#,module,program,action,event from v$session where logon_time < sysdate-0.125 and action like 'Concurrent Request%';
col PROGRAM for a12;
col ACTION for a12;
col MODULE  for a12;
col EVENT for a12;
select status,sid,serial#,module,program,action,event from v$session where logon_time < sysdate-0.050 and action like 'Concurrent Request%';

select * from v$session where logon_time < sysdate-0.050 and action like '%Concurrent Request%';
prompt Inactive form session with login time greater than 6 hours
prompt *********************************************************************************************************
select sid,serial#,module,action,event,to_char(logon_time,'dd:mm:yyyy hh24:mi:ss'),last_call_et/3600 "last call et in hours" from v$session where status likE 'INACTIVE' and logon_time < sysdate-0.25 and action like 'FRM:%' order by 
logon_time;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 HIGH RESOURCE CONSUMING SQL

select sql_text,
       username,
       disk_reads_per_exec,
       buffer_gets,
       disk_reads,
       parse_calls,
       sorts,
       executions,
       rows_processed,
       hit_ratio,
       first_load_time,
       sharable_mem,
       persistent_mem,
       runtime_mem,
       cpu_time,
       elapsed_time,
       address,
       hash_value
from
(select sql_text ,
        b.username ,
 round((a.disk_reads/decode(a.executions,0,1,
 a.executions)),2)
       disk_reads_per_exec,
       a.disk_reads ,
       a.buffer_gets ,
       a.parse_calls ,
       a.sorts ,
       a.executions ,
       a.rows_processed ,
       100 - round(100 *
       a.disk_reads/greatest(a.buffer_gets,1),2) hit_ratio,
       a.first_load_time ,
       sharable_mem ,
       persistent_mem ,
       runtime_mem,
       cpu_time,
       elapsed_time,
       address,
       hash_value
from
   sys.v_$sqlarea a,
   sys.all_users b
where
   a.parsing_user_id=b.user_id and
   b.username not in ('sys','system')
order by 3 desc)
where rownum < 21;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
modukle name TA_auto_apply
at No comments:

If Actual and Target OPP processes do not match EBS

 Check from backend which ones are "ACTIVE"

SQL> select sid,serial#,program,module,status,process,logon_time from v$session where module like'%OPP%';


       SID    SERIAL# PROGRAM
---------- ---------- ------------------------------------------------
MODULE                                           STATUS   PROCESS      LOGON_TIM
------------------------------------------------ -------- ------------ ---------
      4748          1 JDBC Thin Client
FNDCPOPP                                         INACTIVE              15-JUL-20
      4750          3 JDBC Thin Client
FNDCPOPP                                         INACTIVE              15-JUL-20
      4753         17 JDBC Thin Client
FNDCPOPP                                         INACTIVE              15-JUL-20

       SID    SERIAL# PROGRAM
---------- ---------- ------------------------------------------------
MODULE                                           STATUS   PROCESS      LOGON_TIM
------------------------------------------------ -------- ------------ ---------
      5769         31 JDBC Thin Client
FNDCPOPP                                         ACTIVE                20-JUL-20
      5773         10 JDBC Thin Client
FNDCPOPP                                         ACTIVE                20-JUL-20
      5780          4 JDBC Thin Client
FNDCPOPP                                         ACTIVE                20-JUL-20

6 rows selected.



select sid,serial#,program,module,status,process,logon_time from v$session where module like'%OPP%'and status='INACTIVE';


ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;


alter system kill session '4748,1' immediate;

alter system kill session '4750,3' immediate;

alter system kill session '4753,17' immediate;


SQL> select sid,serial#,status,logon_time,module from v$session where module like'%OPP%';

  SID    SERIAL# STATUS   LOGON_TIM
---------- ---------- -------- ---------
MODULE
------------------------------------------------
      5769         31 ACTIVE   20-JUL-20
FNDCPOPP
      5773         10 ACTIVE   20-JUL-20
FNDCPOPP
      5780          4 ACTIVE   20-JUL-20
FNDCPOPP


Check from front end.


Actual and Target processes should both be 3.

at No comments:

Oracle Grant privilege to function


SQL> desc apps.xxta_inter_acces;

FUNCTION apps.xxta_inter_acces RETURNS NUMBER(38)

 Argument Name                  Type                    In/Out Default?

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

 P_SR                           VARCHAR2                IN

 P_TEL                          VARCHAR2                IN



SQL> create user xxalj identified by xxalj;


User created.


SQL> grant connect,resource to xxalj;


Grant succeeded.


SQL> grant select on dual to xxalj;


Grant succeeded.


SQL> conn apps/taapps11i

Connected.


grant execute on apps.xxta_inter_acces to xxalj;


SQL> conn xxalj/xxalj

Connected.


SQL> desc apps.xxta_inter_acces;

FUNCTION apps.xxta_inter_acces RETURNS NUMBER(38)

 Argument Name                  Type                    In/Out Default?

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

 P_SR                           VARCHAR2                IN

 P_TEL                          VARCHAR2                IN



OBJECT_NAME

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

OWNER                          OBJECT_TYPE

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

XXTA_INTER_ACCES

APPS                           FUNCTION


at No comments:

find username for request_id EBS

 select a.request_id,b.user_name from fnd_concurrent_requests a,fnd_user b where a.requested_by=b.user_id and a.request_id='&requestid';

at No comments:

how to delete Delete Trace Files

 oratest#df -k .

/db03                  (/dev/vgdb01/lvDB03    ) : 261776736 total allocated Kb

                                                   5561302 free allocated Kb

                                                  256215434 used allocated Kb

                                                        97 % allocation used


find /db03/oracle/10R2/admin/PROD_oratest/udump -mtime +15 -name "*.trc"|wc -l


find /db03/oracle/10R2/admin/PROD_oratest/udump -mtime +15 -name "*.trc" -exec rm "{}" ";"


oratest#df -k .

/db03                  (/dev/vgdb01/lvDB03    ) : 261564248 total allocated Kb

                                                   8765334 free allocated Kb

                                                  252798914 used allocated Kb

                                                        96 % allocation used


find /db03/oracle/10R2/admin/PROD_oratest/bdump -mtime +15 -name "*.trc"|wc -l


find /db03/oracle/10R2/admin/PROD_oratest/bdump -mtime +15 -name "*.trc" -exec rm "{}" ";"


oratest#df -k .

/db03                  (/dev/vgdb01/lvDB03    ) : 261560275 total allocated Kb

                                                   8825460 free allocated Kb

                                                  252734815 used allocated Kb

                                                        96 % allocation used


find /db03/oracle/10R2/admin/PROD_oratest/udump -mtime +35 -name "*.trc"|wc -l


find /db03/oracle/10R2/admin/PROD_oratest/udump -mtime +35 -name "*.trc" -exec rm "{}" ";"


oratest#df -k .

/db03                  (/dev/vgdb01/lvDB03    ) : 261474396 total allocated Kb

                                                  10118857 free allocated Kb

                                                  251355539 used allocated Kb

                                                        96 % allocation used

at No comments:

Delete Apache logs & rti files

 find /app01/oracle/prodcomn/conf/PROD_approd1/iAS/Apache/Apache/logs -type f -mtime +180|wc -l


approd1#df -k .

/app01                 (/dev/vgapp01/app01    ) : 102400000 total allocated Kb

                                                   2210727 free allocated Kb

                                                  100189273 used allocated Kb

                                                        97 % allocation used


find /app01/oracle/prodcomn/conf/PROD_approd1/iAS/Apache/Apache/logs -mtime +180 -name "access_log*"|wc -l


find /app01/oracle/prodcomn/conf/PROD_approd1/iAS/Apache/Apache/logs -mtime +180 -name "access_log*" -exec rm "{}" ";"


approd1#df -k .

/app01                 (/dev/vgapp01/app01    ) : 101759060 total allocated Kb

                                                  13011907 free allocated Kb

                                                  88747153 used allocated Kb

                                                        87 % allocation used


find /app01/oracle/prodcomn/conf/PROD_approd1/iAS/Apache/Apache/logs -mtime +365 -name "OracleSoapAuditLog*"|wc -l


find /app01/oracle/prodcomn/conf/PROD_approd1/iAS/Apache/Apache/logs -mtime +365 -name "OracleSoapAuditLog*" -exec rm "{}" ";"


find /app01/oracle/prodcomn/conf/PROD_approd1/iAS/Apache/Apache/logs -type f -mtime +365 |xargs rm -Rf {} \;

find /app01/oracle/prodcomn/conf/PROD_approd1/iAS/Apache/Apache/logs -type f -mtime +365|wc -l



===========Delete ".rti" and "f60dump" files from $APPLCSF/$APPLLOG===========

find $APPLCSF/$APPLLOG -mtime +1 -name "*.rti"|wc -l


find $APPLCSF/$APPLLOG -mtime +1 -name "*.rti" -exec rm "{}" ";"


find $APPLCSF/$APPLLOG -mtime +1 -name "f60*dump*"|wc -l


find $APPLCSF/$APPLLOG -mtime +1 -name "f60*dump*" -exec rm "{}" ";"

at No comments:

Query to find Concurrent programs running for more then 4 hours

 set line 200
set pagesize 500
col PROGRAM_NAME format a30
col concreq format a8
col Username format a10
col opid format a4
col dbuser format a6
SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
           fcp.USER_CONCURRENT_PROGRAM_NAME "Program_Name",
                fu.user_name "Username",
               round((sysdate - actual_start_date) * 24 ,2) "Running_Hrs", 
           SUBSTR(proc.os_process_id,1,15) clproc,
           SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
           SUBSTR(vsess.username,1,10) dbuser,
           SUBSTR(vproc.spid,1,10) svrproc,
           vsess.sid sid,
           vsess.serial# serial#
    FROM   fnd_concurrent_requests req,
           fnd_concurrent_processes proc,
           fnd_lookups look,
           fnd_lookups look1,
           v$process vproc,
           v$session vsess,
           fnd_concurrent_programs_vl fcp,
        fnd_user fu
    WHERE  req.controlling_manager = proc.concurrent_process_id(+)
    AND    req.status_code = look.lookup_code
    AND    look.lookup_type = 'CP_STATUS_CODE'
    AND    req.phase_code = look1.lookup_code
    AND    look1.lookup_type = 'CP_PHASE_CODE'
    AND    look1.meaning = 'Running'
    and    req.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID
    AND    proc.oracle_process_id = vproc.pid(+)
    AND    vproc.addr = vsess.paddr(+)
    AND    fu.user_id = req.requested_by
    AND    round((sysdate - actual_start_date) * 24) > 4;
at No comments:

To find Blocking Session

 To find Blocking Session

========================================

col WAIT_CLASS for a12;

select blocking_session,sid,serial#,wait_class,seconds_in_wait,status

from v$session where blocking_session is not NULL order by blocking_session;




select sid,serial#,event,module,action,status,program,blocking_session,last_call_et/60 min from v$session where sid=&sid;



select p.spid,s.sid,s.serial#,s.status,s.last_call_et,s.module,s.osuser from V$process p,V$session s where s.paddr = p.addr and p.spid=&pid;



SQL> alter system kill session '5213,45198';


To find out work flow blocking session 


select sid,serial#,event,module,action,status,program,blocking_session,last_call_et from V$session where module like '%WFBG%';



**********************************************************************************

Blocking session 


select l1.sid, ' IS BLOCKING ', l2.sid

  from v$lock l1, v$lock l2

  where l1.block =1 and l2.request > 0

  and l1.id1=l2.id1

  and l1.id2=l2.id2;

********************************************************************************

BLocking session


select s1.username || '@' || s1.machine

   || ' ( SID=' || s1.sid || ' )  is blocking '

   || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

   from v$lock l1, v$session s1, v$lock l2, v$session s2

   where s1.sid=l1.sid and s2.sid=l2.sid

   and l1.BLOCK=1 and l2.request > 0

   and l1.id1 = l2.id1

   and l2.id2 = l2.id2 ;

************************************************************************

Blocking session


select do.object_name,

  row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,

  dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )

  from v$session s, dba_objects do

  where sid=543

  and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;



alter system kill session 'SID,SERIAL#';

at No comments:

Thursday, September 24, 2020

Configure Agents with OBIEE 12c and EBS Cookie Integration



1. Create a new session initialization block called 'EBS Initialize Session Cookies'

2. Click on 'Edit Data Source' and input the default Initialization String as:


select '-1', '0' from dual;


3. Select the Oracle EBS OLTP Connection Pool

4. Press 'Edit Target Data' button and create 2 variables: ICX_SESSION_COOKIE and ACF

5. Define session variable: ICX_SESSION_COOKIE:

6. Check the "Enable any user to set the value" checkbox

7. Check the 'Security Sensitive" checkbox

8. Set the Default Initializer to:

'-1'


9. Define session variable: ACF

10. Check the "Enable any user to set the value" checkbox

11. Check the "Security Sensitive" checkbox

12. Set the Default Initializer to:

'0'


13. Redefine the 'Execute on connect' query for the Oracle EBS OLTP Connection Pools as follows:


Declare /* valueof (NQ_SESSION.ACF) */ v_icx_session_cookie varchar2(100) := 'valueof(NQ_SESSION.ICX_SESSION_COOKIE)'; begin if v_icx_session_cookie <> '-1' then APP_SESSION.validate_icx_session(v_icx_session_cookie); end if; end;


14. Update the session variable initialization block 'EBS Security Context' query to be:


select FND_GLOBAL.RESP_ID,
FND_GLOBAL.RESP_APPL_ID,
FND_GLOBAL.SECURITY_GROUP_ID,
FND_GLOBAL.RESP_NAME,
FND_GLOBAL.USER_ID,
FND_GLOBAL.EMPLOYEE_ID,
NVL(FND_GLOBAL.USER_NAME,'IMPERSONATOR') from dual
where NVL(FND_GLOBAL.USER_NAME,'IMPERSONATOR') != 'GUEST'
NQS_PASSWORD_CLAUSE(and FND_GLOBAL.USER_NAME != 'GUEST')NQS_PASSWORD_CLAUSE


15. Restart OBIEE services.

at No comments:
Subscribe to: Posts (Atom)

Featured post

Migrate OCR & vote from external to normal or High redundancy in ASM

How to Move OCR & Vote from external to Normal or high. 1)  Create New diskgroup(CRS) with suitable redundancy for OCR and Voting files....

Popular Posts