oracle 10g/11g database finding table size query

select
segment_name table_name,
sum(bytes)/(1024*1024) table_size_meg
from user_extents
where segment_type='TABLE'
and segment_name = '&table_name'
group by segment_name;

OAM 11gR2 enabling trace

a- Set oracle.oam TRACE:32:
(The AdminServer of the OAM Domain is running)
->$OAM_ORACLE_HOME/common/bin/wlst.sh
-> wls:/offline>> connect()
(connect to the AdminServer with OAM Administrator credentials)
-> wls> domainRuntime()
-> wls> listLoggers(pattern="oracle.oam.*",target="oam_server1")
-> wls> setLogLevel(target='oam_server1',logger='oracle.oam',level='TRACE:32',persist="1",addLogger=1)
-> wls> exit()

oid 11g ldap_bind: UnKnown Error Encountered , additional info: Exception: when_bind_replace plugin

Cause : reason is additional entry for old 10g oid plugin .Now you can define existing 11g oid plugin to support ad .Reffer my other blog .

ldap_bind: UnKnown Error Encountered

ldap_bind: additional info: Exception: when_bind_replace plugin

-bash-3.2$

Fix#

-bash-3.2$ ldapmodify -h abc.xyz.com -p 3060 -D "cn=orcladmin" -w -f whenplugin_disable.ldif

modifying entry cn=adwhencompare,cn=plugin,cn=subconfigsubentry

modifying entry cn=adwhenbind,cn=plugin,cn=subconfigsubentry

-bash-3.2$ cat whenplugin_disable.ldif

dn : cn=adwhencompare,cn=plugin,cn=subconfigsubentry

changetype: modify

replace :orclpluginenable

orclpluginenable : 0

dn : cn=adwhenbind,cn=plugin,cn=subconfigsubentry

changetype: modify

replace :orclpluginenable

orclpluginenable : 0

Note : you can login to odsm -advanced and delete the old plugin manually and then follow my blog to enable 11g oid plugin .

11gR2 CRS , clusterware commands

To Get CRSCTL  help
$crsctl -h

To Get Active Cluster Version
$crsctl query crs activeversion

To Get cluster version of given node
$crsctl query crs softwareversion [node_name]

Ex:$crsctl query crs softwareversion rac1

#crsctl start crs
#crsctl stop crs

(or)

#/etc/init.d/init.crs start
#/etc/init.d/init.crs stop

#crsctl enable crs
#crsctl disable crs

(or)

#/etc/init.d/init.crs enable
#/etc/init.d/init.crs disable

To get cluster stack health status
$crsctl check crs

To get the viability of CSS across nodes
$crsctl check cluster [-node node_name]   -- 11gR2 RACcommand

#crsctl start cluster -n HostName -- 11g R2
#crsctl stop cluster -n HostName -- 11g R2
#crsctl stop cluster -all  -- 11g R2

$ps -ef | grep d.bin
$crsctl check cssd
$crsctl check crsd
$crsctl check evmd
$crsctl check oprocd
$crsctl check ctss

#/etc/init.d/init.cssd stop
#/etc/init.d/init.cssd start

#/etc/rc.d/init.d/init.evmd
#/etc/rc.d/init.d/init.cssd
#/etc/rc.d/init.d/init.crsd

#mv /etc/rc3.d/S96init.cssd /etc/rc3.d/_S96init.cssd -- to stop cssd from autostarting after reboot

#crsctl check css votedisk
#crsctl query css votedisk -- lists the voting disks used by CSS command in 11gR2

#crsctl add css votedisk PATH
#crsctl add css votedisk PATH -force -- if Clusterware is not running
#crsctl delete css votedisk PATH
#crsctl delete css votedisk PATH -force -- if Clusterware is not running

#crsctl set css parameter_name value -- set parameters on OCR
#crsctl set css misscount 100
#crsctl unset css parameter_name -- sets CSS parameter to its default
#crsctl unset css misscount
#crsctl get css parameter_name -- gets the value of a CSS parameter
#crsctl get css disktimeout
#crsctl get css misscount
#crsctl get css reboottime

#crsctl start resources -- starts Clusterware resources
#crsctl start resource ora.DATA.dg
#crsctl stop resources -- stops Clusterware resources

$crsctl status resource
$crsctl status resource -t
$crsctl stat resource -t
#crsctl lsmodules crs -- lists CRS modules that can be used for debugging( need root privilage)
List CRSD Debug Module: AGENT
List CRSD Debug Module: AGFW
List CRSD Debug Module: CLSFRAME
List CRSD Debug Module: CLSVER
List CRSD Debug Module: CLUCLS
List CRSD Debug Module: COMMCRS
List CRSD Debug Module: COMMNS
List CRSD Debug Module: CRSAPP
List CRSD Debug Module: CRSCCL
List CRSD Debug Module: CRSCEVT
List CRSD Debug Module: CRSCOMM
List CRSD Debug Module: CRSD
List CRSD Debug Module: CRSEVT
List CRSD Debug Module: CRSMAIN
List CRSD Debug Module: CRSOCR
List CRSD Debug Module: CRSPE
List CRSD Debug Module: CRSPLACE
List CRSD Debug Module: CRSRES
List CRSD Debug Module: CRSRPT
List CRSD Debug Module: CRSRTI
List CRSD Debug Module: CRSSE
List CRSD Debug Module: CRSSEC
List CRSD Debug Module: CRSTIMER
List CRSD Debug Module: CRSUI
List CRSD Debug Module: CSSCLNT
List CRSD Debug Module: OCRAPI
List CRSD Debug Module: OCRASM
List CRSD Debug Module: OCRCAC
List CRSD Debug Module: OCRCLI
List CRSD Debug Module: OCRMAS
List CRSD Debug Module: OCRMSG
List CRSD Debug Module: OCROSD
List CRSD Debug Module: OCRRAW
List CRSD Debug Module: OCRSRV
List CRSD Debug Module: OCRUTL
List CRSD Debug Module: SuiteTes
List CRSD Debug Module: UiServer

$crsctl lsmodules css -- lists CSS modules that can be used for debugging
The following are the Cluster Synchronization Services modules::
    CSSD
    COMMCRS
    COMMNS
    CLSF
    SKGFD

$crsctl lsmodules evm -- lists EVM modules that can be used for debugging
The following are the Cluster Synchronization Services modules::
    CSSD
    COMMCRS
    COMMNS
    CLSF
    SKGFD

$crsctl start has   (HAS - High Availability Services)
$crsctl stop has

$crsctl check has
CRS-4638 Oracle High Availability Service is online

OCR Modules -- cannot be listed with crsctl lsmodules command
OCRAPI
OCRCLI
OCRSRV
OCRMAS
OCRMSG
OCRCAC
OCRRAW
OCRUTL
OCROSD

#crsctl debug statedump crs -- dumps state info for crs objects
#crsctl debug statedump css -- dumps state info for css objects
#crsctl debug statedump evm -- dumps state info for evm objects

#crsctl debug log crs [module:level]{,module:level} ...

-- Turns on debugging for CRS
#crsctl debug log crs CRSEVT:5,CRSAPP:5,CRSTIMER:5,CRSRES:5,CRSRTI:1,CRSCOMM:2
#crsctl debug log css [module:level]{,module:level} ...

-- Turns on debugging for CSS
#crsctl debug log css CSSD:1
#crsctl debug log evm [module:level]{,module:level} ...
-- Turns on debugging for EVM
#crsctl debug log evm EVMCOMM:1

#crsctl debug trace crs -- dumps CRS in-memory tracing cache
#crsctl debug trace css -- dumps CSS in-memory tracing cache
#crsctl debug trace evm -- dumps EVM in-memory tracing cache

#crsctl debug log res resource_name:level -- turns on debugging for resources
#crsctl debug log res "ora.lnx04.vip:1"

#crsctl trace all_the_above_commands -- tracing by adding a "trace" argument.
#crsctl trace check css
#crsctl backup -h
#crsctl backup css votedisk

Here is the list of the options for CRSCTL in 11gR2:
       crsctl add       - add a resource, type or other entity
       crsctl backup    - back up voting disk for CSS
       crsctl check     - check a service, resource or other entity
       crsctl config    - output autostart configuration
       crsctl debug     - obtain or modify debug state
       crsctl delete    - delete a resource, type or other entity
       crsctl disable   - disable autostart
       crsctl discover  - discover DHCP server
       crsctl enable    - enable autostart
       crsctl get       - get an entity value
       crsctl getperm   - get entity permissions
       crsctl lsmodules - list debug modules
       crsctl modify    - modify a resource, type or other entity
       crsctl query     - query service state
       crsctl pin       - Pin the nodes in the nodelist
       crsctl relocate  - relocate a resource, server or other entity
       crsctl replace   - replaces the location of voting files
       crsctl release   - release a DHCP lease
       crsctl request   - request a DHCP lease
       crsctl setperm   - set entity permissions
       crsctl set       - set an entity value
       crsctl start     - start a resource, server or other entity
       crsctl status    - get status of a resource or other entity
       crsctl stop      - stop a resource, server or other entity
       crsctl unpin     - unpin the nodes in the nodelist
       crsctl unset     - unset a entity value, restoring its default

How do I identify the voting disk/file location?
#crsctl query css votedisk

How to take backup of voting file/disk?
crsctl backup css votedisk

OAM 11gR2 enabling centralised logout page with accessgate for EBS R12.1.3

1.       Modify oacleaup for logout as below

/Oracle_WT1/instances/instance1/config/OHS/ohs1/htdocs/public/oacleanup.html

Inside function  ---- function doLoad()  --uncomment below

        logoutHandler.addCallback('/ebsauth_/ssologout_callback');

        logoutHandler.addCookie('ObSSOCookie','domain=.xyz.com');

Enabling SSL/https on Webgate for OAM 11gR2

1. Make your wallet ready .Incase agent and oam server are on same host so the same oam wallet you created will be used .Else reffer my blogs oam ssl section for .

2.      Modify ssl.conf in ohs home to place path of wallet

-bash-3.2$ pwd
/Oracle_WT1/instances/instance1/config/OHS/ohs1
-bash-3.2$

3.        Also modify  mod_wl_ohs.conf  for accessgate changes as below for accesgate new port and change

-bash-3.2$ pwd
/Oracle_WT1/instances/instance1/config/OHS/ohs1
-bash-3.2$ cat mod_wl_ohs.conf


      WebLogicHost abc.xyz.com
      WebLogicPort 7052
        SecureProxy On
  WlSSLWallet ""
#      Debug ON
#      WLLogFile /tmp/weblogic.log
#      MatchExpression *.jsp

5. Bounce webgate
6.Validate index.html of webgate with https url

OAM(Oracle Access Manager) 11gR2 implementing ssl/https

 Section A –Creating Wallet /jkstore ready before

1.Create wallet  and CSR for OAM (source oid.env as owm executable is not present in oam)

 

a.       Create empty wallet and make autologin true .

b.      Create csr for (physical server and lbr url for oam and oid).

 

2.      Once intermediate,root ,ca and oam certificates are recieved from client

 

Make certs ready in x509 format using below option

 

Install all certificates on your local PC (oam  + root +intermidiate) .

Go to IE Browser ->tools->IE options->content->certificates->Other people , Intermidiate and trusted root tabs one by one

 

Export  all the 4 installed certificate in base 64 , x.509 format  and save to some folder .

 

Ftp all saved files to server in binary format

b.      Import trusted root and intermediate certificate to wallet

c.       Import all 4 certificates to wallet and save wallet .

 

3.   Convert wallet into a JKS trust store using below syntax.

Source OAM.env

$MW_HOME/oracle_common/bin/orapki wallet pkcs12_to_jks -wallet ./abcprdwallet -pwd  -jksKeyStoreLoc ./abcprdewalletK.jks -jksKeyStorepwd    -jksTrustStoreLoc ./oamprdewalletT.jks -jksTrustStorepwd

Oracle PKI Tool : Version 11.1.1.6.0

Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

 

 

 Section B .Making weblogin/oam ssl enabled

 

4. Configure WebLogic Server for SSL

The steps below take you through configuring SSL for a Managed Server.

The steps assumes the reader understands how to start the Admin Server and Managed Server.

a. Start the Admin Server in the Domain

b. Login to the WLS console e.g: http://weblogic.uk.oracle.com:7001/console

c. Select 'Environment' -> 'Servers' and click on the server you want to configure

d. Select the 'Keystores' tab

e. Select 'Keystore -> 'Change'

f.Select 'Custom Identity and Custome Trust'from the drop down list and click 'Save'

g. Enter the relevant information in the Keystores page:

'Custom Identity Keystore' : e.g /abcprdewalletK.jks

·         'Custom Identity Keystore' : JKS     (Note: This has to be UPPERCASE)

·         'Custom Identity Keystore Passphrase' : e.g: welcome

·         'Confirm Custom Identity Keystore Passphrase' : e.g: welcome

'Custom Trust Keystore' : e.g /prdewalletT.jks

·         'Custom Trust Keystore Type' : JKS    (Note: This has to be UPPERCASE)

·         'Custom Trust Keystore Passphrase' : e.g: welcome

·         'Confirm Custom Trust Keystore Passphrase' : e.g: welcome

·         Click 'Save'

h. Select the 'SSL' tab and enter the relevant information:

·         'Private Key Alias' : e.g server_cert

·         'Private Key Password' : e.g welcome

·         'Confirm Private Key Password': e.g welcome

·         Click 'Save'

i. Select 'Environment' -> 'Servers' and click on the Managed Server configured

j. In the 'General' tab:

·         Check 'SSL Listen Port Enabled'

·         'SSL Listen Port' : e.g 7012 (make sure this is not used by another process)

·         Click 'Save'

5. Start the Server. If the server is running successfuly you will see the following in the standard out or the Managed Server log file:

6.Make following changes in WLS 10.3.6 for oam 11gr2

Ensure that the following are enabled in the WebLogic Server Administration Console:

WebLogic Plug-In

Client Cert Proxy

To verify this:

Navigate to 'Environments' > 'Servers' > 'AdminServer(Admin)' 

Access the 'General' tab

Expand the 'Advanced' section and check the checkboxes for:

WebLogic Plug-In Enabled

Client Cert Proxy Enabled

 

7.  : Test you can access Weblogic via SSL