Oracle database and EMC network MML error

using target database control file instead of recovery catalog
RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 7 days
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 04/18/2019 11:00:14
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2



You need to link below

$ cd $ORACLE_HOME/lib

$ ln -s /usr/lib/libnsrora.so libobk.so

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
/