Troubleshoot oem status down

If a target shows down in oem best way to troubleshoot is logging into agent from browser and clicking the target and select metric called response. Once you click it, a new page with error message will be displayed. This makes troubleshooting a lot easier.

https://haeissapbgwv.hke.local:3872/emd/browser/main/

Just replace the red part with agent name of the target and enter host oracle id and password.

RAC dataguard

standby

Source database : frepo

unq - frepo
db_name - frepo
instance names - frepo1, frepo2


Target database : frepodr

unq - frepodr
db_name - frepo
instance names - frepo1, frepo2

Create temporary listener

LISTENER_TEMP=
      (DESCRIPTION=
       (ADDRESS_LIST=
        (ADDRESS= (PROTOCOL=TCP)(HOST=exadb01)(PORT=1525))
      )
     )

SID_LIST_LISTENER_TEMP =
    (SID_LIST=
     (SID_DESC=
     (SID_NAME=frepo1)
     (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1)
     )
    )
******************************************************************************
Primary

Add tns entries of source and target on all the 2 nodes.

frepo =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pxadb01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = frepo)
    )
  )

frepoDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = exadb01)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = frepo1)
    )
  )
******************************************************************************
Standby

Add tns entries of source and target on all the 3 nodes.

frepo =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pxadb01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = frepo)
    )
  )

frepoDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = exadb01)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = frepo1)
    )
  )
******************************************************************************
Primary
ALTER DATABASE FORCE LOGGING;
alter system set log_archive_max_processes=4 scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+RECO_DG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=frepo' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=frepodr VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=frepodr' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=both sid='*';
alter system set STANDBY_FILE_MANAGEMENT='AUTO'scope=both sid='*';
alter system set FAL_SERVER='frepodr'scope=both sid='*';
alter system set FAL_CLIENT='frepo'scope=both sid='*';
alter system set log_archive_config='dg_config=(frepo,frepodr)' scope=both sid='*';


create pfile from spfile;

alter user sys identified by "xxxxxxxx" account unlock;

orapwd file=orapwfrepo1 password=xxxxxxxx entries=5 force=y

scp  initfrepo1.ora oracle@exadb01:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initfrepo1.ora



scp  orapwfrepo1 oracle@exadb01:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwfrepo1
scp  orapwfrepo1 oracle@exadb02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwfrepo2


******************************************************************************
Standby

At this point Parameter file on standby server will have all parameters including RAC

Rename the init file as below to differentiate

cp initfrepo1.ora initfrepo1.ora.rac

Now edit the initfrepo1.ora and remove all the RAC related parameters which includes below

instance related, cluster_database, threads, undo etc.. Keep the frepo1_xxx parameters as is.

2nd instance pfile

frepo1.__db_cache_size=1056964608
frepo1.__java_pool_size=16777216
frepo1.__large_pool_size=33554432
frepo1.__pga_aggregate_target=536870912
frepo1.__sga_target=1610612736
frepo1.__shared_io_pool_size=0
frepo1.__shared_pool_size=419430400
frepo1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/frepo/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='frepo'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=frepodrXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_listener='exadb-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1610612736
*.standby_file_management='AUTO'
*.db_unique_name='frepodr'
*.LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=frepodr'
*.LOG_ARCHIVE_DEST_2='SERVICE=frepo VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=frepo'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.FAL_SERVER='frepo'
*.FAL_CLIENT='frepodr'
*.control_files='+DATA/frepo/controlfile/control01.ctl'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'
*.log_archive_config='dg_config=(frepo,frepodr)'




Create respective directories here as per pfile

. oraenv=frepo1

startup nomount

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

Primary

rman target sys/xxxxxxxx@frepo auxiliary sys/xxxxxxxx@frepoDR


duplicate target database for standby from active database dorecover;

After finish exit rman console

******************************************************************************
Standby

create a rac pfile called initmain.ora
It looks like below

frepo1.__db_cache_size=1056964608
frepo2.__db_cache_size=1056964608
frepo2.__java_pool_size=16777216
frepo1.__java_pool_size=16777216
frepo1.__large_pool_size=33554432
frepo2.__large_pool_size=33554432
frepo2.__pga_aggregate_target=536870912
frepo1.__pga_aggregate_target=536870912
frepo2.__sga_target=1610612736
frepo1.__sga_target=1610612736
frepo2.__shared_io_pool_size=0
frepo1.__shared_io_pool_size=0
frepo2.__shared_pool_size=419430400
frepo1.__shared_pool_size=419430400
frepo2.__streams_pool_size=0
frepo1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/frepo/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='frepo'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
frepo1.instance_number=1
frepo2.instance_number=2
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_listener='pxadb-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1610612736
*.standby_file_management='AUTO'
frepo2.thread=2
frepo1.thread=1
frepo2.undo_tablespace='UNDOTBS2'
frepo1.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest='+DATA'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=frepodrXDB)'
*.db_unique_name='frepodr'
*.LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=frepodr'
*.LOG_ARCHIVE_DEST_2='SERVICE=frepo VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=frepo'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.FAL_SERVER='frepo'
*.FAL_CLIENT='frepodr'
*.control_files='+DATA/frepo/controlfile/control01.ctl'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'
*.log_archive_config='dg_config=(frepo,frepodr)'



Make spfile from that


create spfile='+DATA/frepo/parameterfile/spfilefrepo.ora' from pfile='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initmain.ora';

shut immediate;

vi initfrepo1.ora
spfile='+DATA/frepo/parameterfile/spfilefrepo.ora'

scp  initfrepo1.ora oracle@exadb02:/opt/app/oracle/product/11.2.0.4/dbs/initfrepo2.ora

******************************************************************************
Standby

Adding the 2 instances to cluster


srvctl add database -d frepo -o /u01/app/oracle/product/11.2.0.4/dbhome_1 -p +DATA/frepo/parameterfile/spfilefrepo.ora -r physical_standby -a DATA,REDO

srvctl add instance -d frepo -i frepo1 -n exadb01
srvctl add instance -d frepo -i frepo2 -n exadb02

srvctl start database -d frepo

srvctl status database -d frepo

srvctl config database -d frepo

******************************************************************************
Standby
After verifying the config stop the database on all the 2 instances and start on a single instance.

srvctl start instance -d frepo -i frepo1

sqlplus / as sysdba

start MRP process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

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


Note

To stop MRP process use

alter database recover managed standby database cancel;
******************************************************************************

To very dataguard sync use below commands

Standby

select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

PRIMARY

select thread#,max(sequence#) from v$archived_log group by thread#;

or

select max(sequence#) from v$archived_log where  resetlogs_change#=(SELECT resetlogs_change# FROM v$database)

resize or extend /u01 on exadata

First find how much space available to extend

[root@xxx-bac02 ~]# vgdisplay -s
  "VGExaDb" 3.27 TiB  [185.00 GiB used / 3.09 TiB free]

Here we have 3.09TB free

First check the existing size of /u01

df -kh |grep u01

/dev/mapper/VGExaDb-LVDbOra1                  99G   27G   67G  29% /u01

Currently it is 100GB

I want to increase to 150GB

So here are the commands

lvresize -L +50G /dev/mapper/VGExaDb-LVDbOra1

resize2fs  /dev/mapper/VGExaDb-LVDbOra1

Now if you recheck the size again

df -kh |grep u01

/dev/mapper/VGExaDb-LVDbOra1                 148G   36G  105G  26% /u01

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

For XFS file system on new exadata machines

determine if it xfs or ext etc

[root@nasipedwdb-bac02 ~]# df -Th |grep /

Filesystem                                  Type      Size  Used Avail Use% Mounted on

/dev/mapper/VGExaDb-LVDbSys1                xfs        15G   12G  3.3G  79% /

lvresize -L +50G /dev/mapper/VGExaDb-LVDbSys1

xfs_growfs -d /

[root@nasipedwdb-bac02 ~]# df -kh

Filesystem                                   Size  Used Avail Use% Mounted on

/dev/mapper/VGExaDb-LVDbSys1                  65G   12G   54G  19% /

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

creating swapspace on exadata

[root@n dev]# swapon -s

Filename                                Type            Size    Used    Priority

/dev/dm-4                               partition       16777212        0       -2

Add 110gb swap file

dd if=/dev/zero of=/swapspace bs=1M count=112640

mkswap /swapspace

chmod 600 swapspace

swapon /swapspace

 swapon -s

Filename                                Type            Size    Used    Priority

/dev/dm-4                               partition       16777212        0       -2

/swapspace                              file    115343356       0       -3

[root@ /]# free -g

              total        used        free      shared  buff/cache   available

Mem:           1007         382         341          48         283         555

Swap:           125           0         125

Set persistent permissions across reboot in linux and UDEV rules for ASM disks

For asm disks owner should be oracle:dba

You need to setup udev rules or edit rc.local 

else after reboot permissions will change back to root

There are 2 methods to do it. Oracle recommends setting up UDEV

Here is alternative to udev

vi /etc/rc.local

#!/bin/bash

# THIS FILE IS ADDED FOR COMPATIBILITY PURPOSES

#

# It is highly advisable to create own systemd services or udev rules

# to run scripts during boot instead of using this file.

#

# In contrast to previous versions due to parallel execution during boot

# this script will NOT be run after all other services.

#

# Please note that you must run 'chmod +x /etc/rc.d/rc.local' to ensure

# that this script will be executed during boot.

touch /var/lock/subsys/local

chown oracle:dba /dev/mapper/mpatha

chown oracle:dba /dev/mapper/mpathb

chown oracle:dba /dev/mapper/mpathc

chmod 660 /dev/mapper/mpatha

chmod 660 /dev/mapper/mpathb

chmod 660 /dev/mapper/mpathc

After that check status of service. If its not enabled enable it

systemctl status rc-local.service

systemctl enable rc-local

systemctl start rc-local

2nd method using UDEV

#############preparing disks for ASM

/usr/lib/udev/scsi_id -g -u -d /dev/sdc

3624a93707784b3a3c17b437c00011074

/usr/lib/udev/scsi_id -g -u -d /dev/sdd

3624a93707784b3a3c17b437c0001106f

/usr/lib/udev/scsi_id -g -u -d /dev/sde

3624a93707784b3a3c17b437c00011071

vi /etc/scsi_id.config

options=-g

wq!

vi /etc/udev/rules.d/99-asmdevices.rules

KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="3624a93707784b3a3c17b437c00011074", SYMLINK+="asm-disk1", OWNER="oracle", GROUP="dba", MODE="0660"

KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="3624a93707784b3a3c17b437c0001106f", SYMLINK+="asm-disk2", OWNER="oracle", GROUP="dba", MODE="0660"

KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="3624a93707784b3a3c17b437c00011071", SYMLINK+="asm-disk3", OWNER="oracle", GROUP="dba", MODE="0660"

##############Load rules

/sbin/partprobe /dev/sdc1

/sbin/partprobe /dev/sdd1

/sbin/partprobe /dev/sde1

##################Test rules

udevadm test /block/sdc/sdc1

udevadm test /block/sdd/sdd1

udevadm test /block/sde/sde1

#################Restart UDEV Service

udevadm control --reload-rules

check permissions

cd /dev

ls -lHL /dev/tas*

chown -R oracle:dba asm-disk1

chown -R oracle:dba asm-disk2

chown -R oracle:dba asm-disk3

chmod 660 asm-disk1

chmod 660 asm-disk2

chmod 660 asm-disk3

ls -lHL /dev/asm*

set password not to expire linux

To check current password expire date

# chage -l tibero
Last password change                                    : Jul 03, 2019
Password expires                                        : Oct 02, 2019
Password inactive                                       : never
Account expires                                         : never
Minimum number of days between password change          : 0
Maximum number of days between password change          : 91
Number of days of warning before password expires       : 7

To change

# passwd -x -1 tibero

Adjusting aging data for user tibero.

passwd: Success

Verify change again

# chage -l tibero

Last password change                                    : Jul 03, 2019

Password expires                                        : never

Password inactive                                       : never

Account expires                                         : never

Minimum number of days between password change          : 0

Maximum number of days between password change          : -1

Number of days of warning before password expires       : 7

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
/

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;

/

[BUG] root.sh fails while installing 12.2.0.1 grid

DNS errors during the root.sh

Environment :-
=============
Oracle :- 12.2 Cluster Installation
Operating System :- Solaris Sparc 11 – 64bit

Issue :-
=======
root.sh Fails With CLSRSC-175 Error For Grid Iinfrastructure 12.2.0.1 on Solaris for hard DNS check and expects results from DNS to exactly match the FQDN.

Oracle Bug :-
===========
Bug 26002739 – SOLARIS:ROOTUPGRADE.SH FAILING DUE HARD DNS CHECK

Error when root.sh was execueted.
===============================

2018/05/02 10:24:39 CLSRSC-175: Failed to write the checkpoint ‘ROOTCRS_STACK’ with status ‘START’ (error code 1)
2018/05/02 10:24:41 CLSRSC-175: Failed to write the checkpoint ‘ROOTCRS_AFDINST’ with status ‘START’ (error code 1)
2018/05/02 10:24:49 CLSRSC-175: Failed to write the checkpoint ‘ROOTCRS_AFDINST’ with status ‘SUCCESS’ (error code 1)
2018/05/02 10:24:49 CLSRSC-175: Failed to write the checkpoint ‘ROOTCRS_AFDINST’ with status ‘SUCCESS’ (error code 1)
2018/05/02 10:24:51 CLSRSC-175: Failed to write the checkpoint ‘ROOTCRS_STACK’ with status ‘FAIL’ (error code 1)

Other logs under cfgtoollogs folder
cluutil8.log
.
.
[main] [ 2018-05-02 09:57:19.314 PDT ] [ClusterwareCkpt.:162] UnknownHostException caught
[main] [ 2018-05-02 09:57:19.315 PDT ] [ClusterUtil.main:338] nettle: DNS name not found [response code 3]

Oracle Support Suggestion:-
==========================

The issue look to be caused by below bug, which enforces hard DNS check and expects results from DNS to exactly match the FQDN.

Bug 26002739 – SOLARIS:ROOTUPGRADE.SH FAILING DUE HARD DNS CHECK

Please attempt the below workaround and provide feedback:

1) Take backup of “GRID_HOME/bincluutil” & “GRID_HOME/crs/sbs/cluutil.sbs” “file

2) Edit both files and make following change ( under SunOS block )

Comment :: JRE_OPTIONS=”-d64 -Dsun.net.spi.nameservice.provider.1=dns,sun -Xms256m -Xmx512m”

Add new line right below commented text :: JRE_OPTIONS=”-d64 -Xms256m -Xmx512m”

===== updated SunOS block should look like ::

SunOS)
LD_LIBRARY_PATH_64=$ORACLE_HOME/lib:$ORACLE_HOME/srvm/lib:$LD_LIBRARY_PATH_64
export LD_LIBRARY_PATH_64
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/srvm/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
# JRE_OPTIONS=”-d64 -Dsun.net.spi.nameservice.provider.1=dns,sun -Xms256m -Xmx512m”
JRE_OPTIONS=”-d64 -Xms256m -Xmx512m”
;;

======

3) Then execute root.sh from 12.2 GI home and share result.

After the workaround root.sh was successful.

See the below reference note :-

Please try the workaround given in the bug or use the patch p26002739_122010_SOLARIS64.zip

Ref: root.sh or rootupgrade.sh Fails With CLSRSC-175 Error For Grid Iinfrastructure 12.2.0.1 on Solaris ( Doc ID 2285577.1 )

How to Apply a Grid Infrastructure Patch Before root script (root.sh or rootupgrade.sh) is Executed? ( Doc ID 1410202.1 )

Hope this helps.

3 Node RAC Dataguard

standby

Create temporary listener

LISTENER_TEMP=
      (DESCRIPTION=
       (ADDRESS_LIST=
        (ADDRESS= (PROTOCOL=TCP)(HOST=exadb01)(PORT=1525))
      )
     )

SID_LIST_LISTENER_TEMP =
    (SID_LIST=
     (SID_DESC=
     (SID_NAME=apple1)
     (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1)
     )
    )
******************************************************************************
Primary

Add tns entries of source and target on all the 3 nodes.

apple =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = exadb10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = apple)
    )
  )

appleDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = exadb01)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = apple1)
    )
  )
******************************************************************************
Standby 

Add tns entries of source and target on all the 3 nodes.

apple =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = exadb10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = apple)
    )
  )

appleDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = exadb01)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = apple1)
    )
  )
******************************************************************************
Primary
ALTER DATABASE FORCE LOGGING;
alter system set log_archive_max_processes=4 scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/oracle/archive/apple VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=apple' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=appledr VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=apple' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=both sid='*';
alter system set STANDBY_FILE_MANAGEMENT='AUTO'scope=both sid='*';
alter system set FAL_SERVER='appleDR'scope=both sid='*';
alter system set FAL_CLIENT='apple'scope=both sid='*';
alter system set log_archive_config='dg_config=(apple,apple)' scope=both sid='*';

create pfile from spfile;

alter user sys identified by "xxxx" account unlock;

orapwd file=orapwapple1 password=xxxx entries=5 force=y

scp  initapple1.ora oracle@exadb01:/opt/app/oracle/product/11.2.0.4/dbs/initapple1.ora



scp  orapwapple1 oracle@exadb01:/opt/app/oracle/product/11.2.0.4/dbs/orapwapple1
scp  orapwapple1 oracle@exadb02:/opt/app/oracle/product/11.2.0.4/dbs/orapwapple2
scp  orapwapple1 oracle@exadb03:/opt/app/oracle/product/11.2.0.4/dbs/orapwapple3


******************************************************************************
Standby

At this point Parameter file on standby server will have all parameters including RAC

Rename the init file as below to differentiate

cp initapple1.ora initapple1.ora.rac

Now edit the initapple1.ora and remove all the RAC related parameters which includes below

instance related, cluster_database, threads, undo etc.. Keep the apple1_xxx parameters as is.

Modify the below parameters

*.LOG_ARCHIVE_DEST_1='LOCATION=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=apple'
*.LOG_ARCHIVE_DEST_2='SERVICE=apple VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=apple'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.FAL_SERVER='apple'
*.FAL_CLIENT='appledr'
*.control_files='+DATA/apple/controlfile/control01.ctl','+REDO/apple/controlfile/control02.ctl','+RECO/apple/controlfile/control03.ctl'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'

Create respective directories here as per pfile

. oraenv=apple1

startup nomount

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

Primary

rman target sys/xxxx@apple auxiliary sys/xxxx@appleDR


duplicate target database for standby from active database dorecover;

After finish exit rman console

******************************************************************************
Standby

create spfile from the rac pfile as below

create spfile='+CRS/apple/parameterfile/spfileapple.ora' from pfile='/opt/app/oracle/product/11.2.0.4/dbs/initapple1.ora.rac';

shut immediate;

vi initapple1.ora
spfile='+crs/apple/parameterfile/spfileapple.ora'

scp  initapple1.ora oracle@exadb02:/opt/app/oracle/product/11.2.0.4/dbs/initapple2.ora
scp  initapple1.ora oracle@exadb03:/opt/app/oracle/product/11.2.0.4/dbs/initapple3.ora

******************************************************************************
Standby

Adding the 3 instances to cluster


srvctl add database -d apple -o /opt/app/oracle/product/11.2.0.4 -p +CRS/apple/parameterfile/spfileapple.ora -r physical_standby -a DATA,REDO,RECO,CRS

srvctl add instance -d apple -i apple1 -n exadb01
srvctl add instance -d apple -i apple2 -n exadb02
srvctl add instance -d apple -i apple3 -n exadb03

srvctl start database -d apple

srvctl status database -d apple

srvctl config database -d apple

******************************************************************************
Standby
After verifying the config stop the database on all the 3 instances and start on a single instance.

srvctl start instance -d apple -i apple1

sqlplus / as sysdba

start MRP process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

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


Note

To stop MRP process use

alter database recover managed standby database cancel;
******************************************************************************

To very dataguard sync use below commands

Standby

select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

PRIMARY

select thread#,max(sequence#) from v$archived_log group by thread#;

or

select max(sequence#) from v$archived_log where  resetlogs_change#=(SELECT resetlogs_change# FROM v$database)

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

Drop database links from other schemas using sys

Create or replace procedure DROP_LINK(schemaName varchar2, dbLink varchar2 ) is
            plsql   varchar2(1000);
            cur     number;
            uid     number;
            rc      number;
    begin
            select
                    u.user_id into uid
           from    dba_users u
           where   u.username = schemaName;
             plsql := 'drop database link "'||dbLink||'"';
             cur := SYS.DBMS_SYS_SQL.open_cursor;
             SYS.DBMS_SYS_SQL.parse_as_user(
                   c => cur,
                   statement => plsql,
                   language_flag => DBMS_SQL.native,
                   userID => uid
          );
             rc := SYS.DBMS_SYS_SQL.execute(cur);

             SYS.DBMS_SYS_SQL.close_cursor(cur);
   end;
   /
 
 exec Drop_LINK( 'WGPOMSUSR', 'DBNAME' );

 select * from dba_db_links;

 drop procedure Drop_DbLink;

Solaris password commands

Force user to change password on next login

passwd -f oracle


Turn off password aging

passwd -x -1 oracle

This displays password expiry time

passwd -s oracle

DataGuard Health Check

STANDBY

select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

PRIMARY

select thread#,max(sequence#) from v$archived_log group by thread#;

or

select max(sequence#) from v$archived_log where  resetlogs_change#=(SELECT resetlogs_change# FROM v$database)

or

SELECT sequence# || '|' ||
thread# || '|' ||
TO_CHAR(completion_time, 'DD-MON-RRRR HH24:MI:SS')
FROM v$archived_log
WHERE sequence#= (SELECT MAX(sequence#) FROM v$archived_log where resetlogs_change#=(SELECT resetlogs_change# FROM v$database))
AND thread#=1
AND resetlogs_change#=(SELECT resetlogs_change# FROM v$database)
AND dest_id=1
UNION ALL
SELECT sequence# || '|' ||
thread# || '|' ||
TO_CHAR(completion_time, 'DD-MON-RRRR HH24:MI:SS')
FROM v$archived_log
WHERE sequence#= (SELECT MAX(sequence#) FROM v$archived_log where resetlogs_change#=(SELECT resetlogs_change# FROM v$database))
AND resetlogs_change#=(SELECT resetlogs_change# FROM v$database)
AND thread#=2
AND dest_id=1;

Solaris script to find cpu cores

#!/bin/bash

/usr/bin/kstat -m cpu_info | egrep "chip_id|core_id|module: cpu_info" > /var/tmp/cpu_info.log

nproc=`(grep chip_id /var/tmp/cpu_info.log | awk '{ print $2 }' | sort -u | wc -l | tr -d ' ')`
ncore=`(grep core_id /var/tmp/cpu_info.log | awk '{ print $2 }' | sort -u | wc -l | tr -d ' ')`
vproc=`(grep 'module: cpu_info' /var/tmp/cpu_info.log | awk '{ print $4 }' | sort -u | wc -l | tr -d ' ')`

nstrandspercore=$(($vproc/$ncore))
ncoresperproc=$(($ncore/$nproc))

speedinmhz=`(/usr/bin/kstat -m cpu_info | grep clock_MHz | awk '{ print $2 }' | sort -u)`
speedinghz=`echo "scale=2; $speedinmhz/1000" | bc`

echo "Total number of physical processors: $nproc"
echo "Number of virtual processors: $vproc"
echo "Total number of cores: $ncore"
echo "Number of cores per physical processor: $ncoresperproc"
echo "Number of hardware threads (strands or vCPUs) per core: $nstrandspercore"
echo "Processor speed: $speedinmhz MHz ($speedinghz GHz)"

# now derive the vcpu-to-core mapping based on above information #

echo -e "\n** Socket-Core-vCPU mapping **"
let linenum=2

for ((i = 1; i <= ${nproc}; ++i ))
do
        chipid=`sed -n ${linenum}p /var/tmp/cpu_info.log | awk '{ print $2 }'`
        echo -e "\nPhysical Processor $i (chip id: $chipid):"

        for ((j = 1; j <= ${ncoresperproc}; ++j ))
        do
                let linenum=($linenum + 1)
                coreid=`sed -n ${linenum}p /var/tmp/cpu_info.log | awk '{ print $2 }'`
                echo -e "\tCore $j (core id: $coreid):"

                let linenum=($linenum - 2)
                vcpustart=`sed -n ${linenum}p /var/tmp/cpu_info.log | awk '{ print $4 }'`

                let linenum=(3 * $nstrandspercore + $linenum - 3)
                vcpuend=`sed -n ${linenum}p /var/tmp/cpu_info.log | awk '{ print $4 }'`

                echo -e "\t\tvCPU ids: $vcpustart - $vcpuend"
                let linenum=($linenum + 4)
        done
done

rm /var/tmp/cpu_info.log

Sample output

oracle:/export/home/oracle>./proc.sh

Total number of physical processors: 1

Number of virtual processors: 64

Total number of cores: 8

Number of cores per physical processor: 8

Number of hardware threads (strands or vCPUs) per core: 8

Processor speed: 2848 MHz (2.84 GHz)

** Socket-Core-vCPU mapping **

Physical Processor 1 (chip id: 0):

        Core 1 (core id: 1026):

                vCPU ids: 0 - 7

        Core 2 (core id: 1030):

                vCPU ids: 8 - 15

        Core 3 (core id: 1034):

                vCPU ids: 16 - 23

        Core 4 (core id: 1038):

                vCPU ids: 24 - 31

        Core 5 (core id: 1042):

                vCPU ids: 32 - 39

        Core 6 (core id: 1046):

                vCPU ids: 40 - 47

        Core 7 (core id: 1050):

                vCPU ids: 48 - 55

        Core 8 (core id: 1054):

                vCPU ids: 56 - 63

Apply psu and one off patches for single instance database with ASM

First make sure you have lastest version of OPatch in both oracle and grid homes.

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

ocm.rsp file to be generated by oracle user in both grid and db home


cd $ORACLE_HOME

cd OPatch/ocm/bin

./emo*


Create a response file using above


download patch and unzip to the staging folder

in my case

/export/home/oracle/oracle_software/25476126



First apply for grid home


export ORACLE_HOME=/opt/app/crs/11.2.0.4


./opatch auto /export/home/oracle/oracle_software/25476126 -oh /opt/app/crs/11.2.0.4 -ocmrf /opt/app/crs/11.2.0.4/OPatch/ocm/bin/ocm.rsp



Now apply for database home

export ORACLE_HOME=/opt/app/oracle/product/11.2.0/11.2.0.4/

./opatch auto /export/home/oracle/oracle_software/25476126 -oh /opt/app/oracle/product/11.2.0/11.2.0.4/ -ocmrf /opt/app/oracle/product/11.2.0/11.2.0.4/OPatch/ocm/bin/ocm.rsp


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

Apply one off patches

First apply from grid home

export ORACLE_HOME=/opt/app/crs/11.2.0.4

cd /opt/app/crs/11.2.0.4/OPatch
./opatch apply -oh /opt/app/crs/11.2.0.4 -local /export/home/oracle/oracle_software/10194190



Now apply for database home

export ORACLE_HOME=/opt/app/oracle/product/11.2.0/11.2.0.4/

cd /export/home/oracle/oracle_software/10194190

/opt/app/oracle/product/11.2.0/11.2.0.4/OPatch/opatch apply







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

NOTE

if response file creation fails with motif error in solaris

please install below

pkg install pkg://solaris/library/motif