Network ACL error after 12c database upgrade

begin
    dbms_network_acl_admin.append_host_ace (
    host=>'*',
    lower_port => 1,
    upper_port => 65535,
         ace=> sys.xs$ace_type( privilege_list=>sys.XS$NAME_LIST('JDWP') ,
                                principal_name=>'HMA_TM_PROD',
                                principal_type=>sys.XS_ACL.PTYPE_DB));
end;
/


BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => '*',
    lower_port => 1,
    upper_port => 65535,
    ace        => xs$ace_type(privilege_list => xs$name_list('connect'),
                              principal_name => 'HMA_TM_PROD',
                              principal_type => xs_acl.ptype_db));
END;
/

*************************************
send test email from database

Create proc

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_message   IN VARCHAR2,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);
  UTL_SMTP.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.quit(l_mail_conn);
END;
/

Add privs


BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'send_mailx.xml',
    description  => 'Purpose of the acl is to send mail',
    principal    => 'sravan',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

   DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'send_mailx.xml',
    host        => '*',
    lower_port  => 1,
    upper_port  => 9999);

  COMMIT;
END;
/


Send
BEGIN
  send_mail(p_to        => 'xxx10563@xxx.local',
            p_from      => 'xxx-dba-alerts@xxx.local',
            p_message   => 'This is a test message.',
            p_smtp_host => '10.120.xx.xx');
END;
/

OEM 12c Unable To Complete Network Operation Against My Oracle Support

cd /opt/app/oracle/gc_inst/user_projects/domains/GCDomain/bin/


cp setDomainEnv.sh setDomainEnv.sh.old

Stop the OMS and web tier:

emctl stop oms



vi setDomainEnv.sh


Find

JAVA_OPTIONS="${JAVA_OPTIONS} ${JAVA_PROPERTIES} -Dwlw.iterativeDev=${iterativeDevFlag} -Dwlw.testConsole=${testConsoleFlag} -Dwlw.logErrorsToConsole=${logErrorsToConsoleFlag}"

Change to:

JAVA_OPTIONS="${JAVA_OPTIONS} ${JAVA_PROPERTIES} -Dwlw.iterativeDev=${iterativeDevFlag} -Dwlw.testConsole=${testConsoleFlag} -Dwlw.logErrorsToConsole=${logErrorsToConsoleFlag} -Dcom.sun.net.ssl.enableECC=false"

Restart the OMS and web tier.:

emctl start oms


rollback for



Fix MOS oracle unable to connect on hisipoem


/opt/app/oracle/gc_inst/user_projects/domains/GCDomain/bin/


cp setDomainEnv.sh.old setDomainEnv.sh

sql profile syntax for force match

execute dbms_sqltune.accept_sql_profile(task_name =>'H43369.4496173495', task_owner => 'xxxx',replace => TRUE, force_match => TRUE);

Find lost weblogic domain password for OEM 12c

To find weblogic password follow below steps

First go to below location

/opt/app/oracle/middlewarer2/oracle_common/common/bin



**********************************************************************************************
create a file name with decrypt.py

vi decrypt.py

#/bin/python
#=====================================================================
#
# $Id: decrypt.py $
#
# PURPOSE:    Script to decrypt any Password or Username
#             within a WebLogic Server Domain
#
# PARAMETERS: none
#
# NOTES:      none
#
# AUTHOR:     Dirk Nachbar, https://dirknachbar.blogspot.com
#
# MODIFIED:
#
#
#=====================================================================

# Import weblogic.security.internal and weblogic.security.internal.encryption
from weblogic.security.internal import *
from weblogic.security.internal.encryption import *

# Provide Domain Home Location
domain = raw_input("Provide Domain Home location: ")

# Get encryption service with above Domain Home Location
encryptService = SerializedSystemIni.getEncryptionService(domain)
clearOrEncryptService = ClearOrEncryptedService(encryptService)

# Provide the encrypted password or username, e.g. from boot.properties
encrypted_pwd = raw_input("Provide encrypted password or username (e.g.: {AES}jNdVLr...): ")

# Clear the encrypted value from escaping characters
cleared_pwd = encrypted_pwd.replace("\\", "")

# Personal security hint :-)
raw_input("Make sure that nobody is staying behind you :-) Press ENTER to see the password ...")

# Decrypt the encrypted password or username
print "Value in cleartext is: " + clearOrEncryptService.decrypt(cleared_pwd)



#END

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


You can get the encrypted password and username from below location


cd /opt/app/oracle/middlewarer2/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/security

cat boot.properties
#Generated by Configuration Wizard on Sat Oct 20 10:20:22 PDT 2012
username={AES}tFHZTUTQ5xhgS3WQicmnSPgTfFv1xswVwndUaCeb7qk=
password={AES}QZ06i3z6EAoK82eb20n0dZr+xYAxUAk26HtgmOt4Pp0=

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

export DOMAIN_HOME=/opt/app/oracle/middlewarer2/gc_inst/user_projects/domains/GCDomain

cd /opt/app/oracle/middlewarer2/oracle_common/common/bin

Make sure decrypt.py has execute permission

./wlst.sh decrypt.py

Provide Domain Home location: /opt/app/oracle/middlewarer2/gc_inst/user_projects/domains/GCDomain
Provide encrypted password or username (e.g.: {AES}jNdVLr...): {AES}tFHZTUTQ5xhgS3WQicmnSPgTfFv1xswVwndUaCeb7qk=

Now you will get password in cleartext

12CR2 Grid and DB patching ( RAC and NON RAC)

Opatch Grid home owned by oracle:dba

Opatch db home owned by oracle:dba

Patch folder unzipped by oracle

opatch auto to be run by root


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

Pre req check

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

For Grid Infrastructure Home, as home user:

export ORACLE_HOME=/opt/app/crs/12.2.0.1/

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /export/home/oracle/oracle_software/12c/patches/28183653/28163133
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /export/home/oracle/oracle_software/12c/patches/28183653/28163190
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /export/home/oracle/oracle_software/12c/patches/28183653/28163235
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /export/home/oracle/oracle_software/12c/patches/28183653/26839277
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /export/home/oracle/oracle_software/12c/patches/28183653/27144050



For Database home, as home user:

export ORACLE_HOME=/opt/app/oracle/product/12.2.0/12.2.0.1


$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /export/home/oracle/oracle_software/12c/patches/28183653/28163133
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /export/home/oracle/oracle_software/12c/patches/28183653/28163190



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

Check System space before applying

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


For Grid Infrastructure Home, as home user:


vi /tmp/patch_list_gihome.txt

/export/home/oracle/oracle_software/12c/patches/28183653/28163133
/export/home/oracle/oracle_software/12c/patches/28183653/28163190
/export/home/oracle/oracle_software/12c/patches/28183653/28163235
/export/home/oracle/oracle_software/12c/patches/28183653/26839277
/export/home/oracle/oracle_software/12c/patches/28183653/27144050


Run the opatch command to check if enough free space is available in the Grid Infrastructure Home:

export ORACLE_HOME=/opt/app/crs/12.2.0.1/

$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_gihome.txt



For Database home, as home user:


vi /tmp/patch_list_dbhome.txt
/export/home/oracle/oracle_software/12c/patches/28183653/28163133
/export/home/oracle/oracle_software/12c/patches/28183653/28163190


Run opatch command to check if enough free space is available in the Database Home:

export ORACLE_HOME=/opt/app/oracle/product/12.2.0/12.2.0.1

$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_dbhome.txt


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

Applying patch

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

As root user

export ORACLE_HOME=/opt/app/crs/12.2.0.1/

export PATH=$PATH:/opt/app/crs/12.2.0.1/OPatch



For RAC (GRID and DB)


cd /opt/app/crs/12.2.0.1/OPatch/

./opatchauto apply /export/home/oracle/oracle_software/12c/patches/28183653/

FOR GRID and NON RAC

GRID:


cd /opt/app/crs/12.2.0.1/OPatch

./opatchauto apply /export/home/oracle/oracle_software/12c/patches/28183653/ -oh /opt/app/crs/12.2.0.1/


NON - RAC DB


cd /export/home/oracle/oracle_software/12c/patches/28183653/28163133

export ORACLE_HOME=/opt/app/oracle/product/12.2.0/12.2.0.1

$ORACLE_HOME/OPatch/opatch apply

undo tablespace usage based on user

SELECT s.inst_id,
        r.name                   rbs,
        nvl(s.username, 'None')  oracle_user,
        s.osuser                 client_user,
        p.username               unix_user,
        to_char(s.sid)||','||to_char(s.serial#) as sid_serial,
        p.spid                   unix_pid,
        TO_CHAR(s.logon_time, 'mm/dd/yy hh24:mi:ss') as login_time,
        t.used_ublk * 8192  as undo_BYTES,
                st.sql_text as sql_text
   FROM gv$process     p,
        v$rollname     r,
        gv$session     s,
        gv$transaction t,
        gv$sqlarea     st
  WHERE p.inst_id=s.inst_id
    AND p.inst_id=t.inst_id
    AND s.inst_id=st.inst_id
    AND s.taddr = t.addr
    AND s.paddr = p.addr(+)
    AND r.usn   = t.xidusn(+)
    AND s.sql_address = st.address
 --   AND t.used_ublk * 8192 > 10000
  AND t.used_ublk * 8192 > 1073741824
  ORDER
       BY undo_BYTES desc
/

Gather Incremantal statistics for large or partitioned tables

BEGIN

  DBMS_STATS.SET_TABLE_PREFS (  

      ownname  =>  'HMA_TM_PROD_GEN2_MONITOR'

,     tabname  =>  'ZZT_SERVICE_LOG'

,     pname    =>  'INCREMENTAL'

,     pvalue   =>  'true'

);

END;

--This should return True. This mean incremental is enabled

select dbms_stats.get_prefs('INCREMENTAL','HMA_TM_PROD_GEN2_MONITOR','ZZT_SERVICE_LOG') from dual;

BEGIN

  DBMS_STATS.GATHER_TABLE_STATS (

      ownname  => 'HMA_TM_PROD_GEN2_MONITOR'

,     tabname  => 'ZZT_SERVICE_LOG'

);

END;

/