Tuesday, July 24, 2018

18 Steps for Oracle 18c Installation and Database Creation for On Premise Environment

18 Steps for Oracle 18c Installation and Database Creation for On Premise Environment

Hi DBAs,

Oracle 18c Rel3 On premise Database released as expected in Jul 2018. Since I work and Spend most of my time
working with On Premise Databases, Applications and Fusion Middleware components, so decided to write this post based on

My practical experience with Oracle 18c today to assist DBAs.

Below are my 18 steps for Oracle 18c Installation on Linux 64-bit Operating System:



Step 1: Copy and Unzip the Software on server.

Once you unzip the Oracle18c Rel3 Software you will notice while extracting lot of linking of binaries happen and it will
create ORACLE_HOME in same path, this is interesting and new feature of 18c Database.f

cd Oracle_18c_Rel3_Linux_64bit/
  ls -ltr
  unzip LINUX.X64_180000_db_home.zip


inflating: md/gdal/bin/gdalenhance
  inflating: md/gdal/bin/gdallocationinfo
  inflating: md/gdal/bin/ogrinfo
   creating: md/lib/
  inflating: md/lib/libsdogdal.so
    linking: bin/lbuilder            -> ../nls/lbuilder/lbuilder
    linking: lib/libocci.so          -> libocci.so.18.1
    linking: lib/libodm18.so         -> libodmd18.so
    linking: lib/libagtsh.so         -> libagtsh.so.1.0
    linking: lib/libclntsh.so        -> libclntsh.so.18.1
    linking: lib/libjavavm18.a       -> ../javavm/jdk/jdk8/lib/libjavavm18.a
    linking: javavm/lib/jce.jar      -> ../../javavm/jdk/jdk8/lib/jce.jar
    linking: javavm/admin/cbp.jar    -> ../../javavm/jdk/jdk8/admin/cbp.jar
    linking: lib/libclntshcore.so    -> libclntshcore.so.18.1
    linking: lib/libclntsh.so.11.1   -> libclntsh.so
    linking: lib/libclntsh.so.10.1   -> libclntsh.so
    linking: precomp/public/ORACA.H  -> oraca.h
    linking: precomp/public/SQLCA.H  -> sqlca.h
    linking: precomp/public/SQLDA.H  -> sqlda.h
    linking: precomp/public/ORACA.COB  -> oraca.cob
    linking: precomp/public/SQLCA.COB  -> sqlca.cob
    linking: javavm/admin/classes.bin  -> ../../javavm/jdk/jdk8/admin/classes.bin
    linking: javavm/admin/libjtcjt.so  -> ../../javavm/jdk/jdk8/admin/libjtcjt.so
    linking: javavm/admin/lfclasses.bin  -> ../../javavm/jdk/jdk8/admin/lfclasses.bin
    linking: javavm/lib/security/cacerts  -> ../../../javavm/jdk/jdk8/lib/security/cacerts
    linking: javavm/lib/sunjce_provider.jar  -> ../../javavm/jdk/jdk8/lib/sunjce_provider.jar
    linking: javavm/lib/security/java.security  -> ../../../javavm/jdk/jdk8/lib/security/java.security
    linking: javavm/lib/security/local_policy.jar  -> ../../../javavm/jdk/jdk8/lib/security/local_policy.jar
    linking: javavm/lib/security/US_export_policy.jar  -> ../../../javavm/jdk/jdk8/lib/security/US_export_policy.jar
 extracting: install/.img.bin
finishing deferred symbolic links:
  bin/lbuilder           -> ../nls/lbuilder/lbuilder
  lib/libocci.so         -> libocci.so.18.1
  lib/libodm18.so        -> libodmd18.so
  lib/libagtsh.so        -> libagtsh.so.1.0
  lib/libclntsh.so       -> libclntsh.so.18.1
  lib/libjavavm18.a      -> ../javavm/jdk/jdk8/lib/libjavavm18.a
  javavm/lib/jce.jar     -> ../../javavm/jdk/jdk8/lib/jce.jar
  javavm/admin/cbp.jar   -> ../../javavm/jdk/jdk8/admin/cbp.jar
  lib/libclntshcore.so   -> libclntshcore.so.18.1
  lib/libclntsh.so.11.1  -> libclntsh.so
  lib/libclntsh.so.10.1  -> libclntsh.so
  precomp/public/ORACA.H -> oraca.h
  precomp/public/SQLCA.H -> sqlca.h
  precomp/public/SQLDA.H -> sqlda.h
  precomp/public/ORACA.COB -> oraca.cob
  precomp/public/SQLCA.COB -> sqlca.cob
  javavm/admin/classes.bin -> ../../javavm/jdk/jdk8/admin/classes.bin
  javavm/admin/libjtcjt.so -> ../../javavm/jdk/jdk8/admin/libjtcjt.so
  javavm/admin/lfclasses.bin -> ../../javavm/jdk/jdk8/admin/lfclasses.bin
  javavm/lib/security/cacerts -> ../../../javavm/jdk/jdk8/lib/security/cacerts
  javavm/lib/sunjce_provider.jar -> ../../javavm/jdk/jdk8/lib/sunjce_provider.jar
  javavm/lib/security/java.security -> ../../../javavm/jdk/jdk8/lib/security/java.security
  javavm/lib/security/local_policy.jar -> ../../../javavm/jdk/jdk8/lib/security/local_policy.jar
  javavm/lib/security/US_export_policy.jar -> ../../../javavm/jdk/jdk8/lib/security/US_export_policy.jar
[oracle@orasolsTest Oracle_18c_Rel3_Linux_64bit]$


[oracle@orasolsTest Oracle_18c_Rel3_Linux_64bit]$ ls -ltr
total 4457740
-rw-r--r--.  1 oracle oinstall        852 Aug 18  2015 env.ora
-rw-r--r--.  1 oracle oinstall       2927 Oct 14  2016 schagent.conf
-rwxr-x---.  1 oracle oinstall       1783 Mar  8  2017 runInstaller
drwxr-x---.  7 oracle oinstall         69 Feb  8 01:11 xdk
drwxr-xr-x.  3 oracle oinstall         19 Feb  8 01:11 wwg
drwxr-xr-x.  4 oracle oinstall         31 Feb  8 01:11 usm
drwxr-xr-x.  5 oracle oinstall         45 Feb  8 01:11 suptools
drwxr-xr-x.  6 oracle oinstall         54 Feb  8 01:11 srvm
drwxr-xr-x.  3 oracle oinstall         17 Feb  8 01:11 sqlj
drwxr-xr-x.  3 oracle oinstall         18 Feb  8 01:11 slax
-rw-r-----.  1 oracle oinstall         10 Feb  8 01:11 root.sh.old.1
drwxr-xr-x.  3 oracle oinstall         21 Feb  8 01:11 relnotes
drwxr-xr-x.  4 oracle oinstall         29 Feb  8 01:11 racg
drwxr-xr-x.  5 oracle oinstall         52 Feb  8 01:11 R
drwxr-xr-x.  4 oracle oinstall         33 Feb  8 01:11 owm
drwxr-xr-x.  3 oracle oinstall         19 Feb  8 01:11 oss
drwxr-xr-x.  3 oracle oinstall         19 Feb  8 01:11 ordim
drwxr-xr-x.  4 oracle oinstall         34 Feb  8 01:11 oracore
drwxr-xr-x.  5 oracle oinstall         42 Feb  8 01:11 olap
drwxr-xr-x.  4 oracle oinstall         31 Feb  8 01:11 mgw
drwxr-xr-x.  2 oracle oinstall          6 Feb  8 01:11 log
drwxr-xr-x.  3 oracle oinstall         18 Feb  8 01:11 has
drwxr-xr-x.  3 oracle oinstall         19 Feb  8 01:11 dv
drwxr-xr-x.  3 oracle oinstall         20 Feb  8 01:11 diagnostics
drwxr-xr-x.  3 oracle oinstall         19 Feb  8 01:11 dbjava
drwxr-xr-x.  3 oracle oinstall         20 Feb  8 01:11 data
drwxr-xr-x.  3 oracle oinstall         18 Feb  8 01:11 css
drwxr-xr-x.  6 oracle oinstall         78 Feb  8 01:12 plsql
drwxr-xr-x.  2 oracle oinstall         22 Feb  8 01:12 dbs
drwxr-xr-x.  2 oracle oinstall         33 Feb  8 01:12 utl
drwxr-xr-x.  2 oracle oinstall         29 Feb  8 01:12 instantclient
drwxr-xr-x. 13 oracle oinstall        198 Feb  8 01:12 dmu
drwxr-xr-x.  3 oracle oinstall         35 Feb  8 01:12 ucp
drwxr-xr-x.  3 oracle oinstall         35 Feb  8 01:12 jdbc
drwxr-xr-x.  2 oracle oinstall         26 Feb  8 01:12 QOpatch
drwxr-xr-x.  4 oracle oinstall         67 Feb  8 01:12 ords
drwxr-xr-x.  5 oracle oinstall        119 Feb  8 01:13 sdk
drwxr-xr-x.  6 oracle oinstall       4096 Feb  8 01:13 apex
drwxr-xr-x. 22 oracle oinstall       4096 Feb  8 01:13 sqldeveloper
drwxr-xr-x.  8 oracle oinstall        101 Feb  8 01:13 odbc
drwxr-xr-x. 11 oracle oinstall        119 Feb  8 01:14 ctx
-rwx------.  1 oracle oinstall        786 Feb  8 01:14 root.sh.old
drwxr-x---. 14 oracle oinstall       4096 Jun  4 04:20 OPatch
drwxr-xr-x.  6 oracle oinstall         53 Jul 18 20:17 sqlplus
-rwx------.  1 oracle oinstall        638 Jul 18 20:18 root.sh
drwxr-xr-x.  2 oracle oinstall       4096 Jul 18 20:39 jlib
drwxr-xr-x.  5 oracle oinstall        153 Jul 18 20:41 sqlpatch
drwxr-xr-x.  7 oracle oinstall        246 Jul 18 20:44 jdk
drwxr-xr-x. 13 oracle oinstall        140 Jul 18 20:44 rdbms
drwxr-xr-x.  6 oracle oinstall         56 Jul 18 20:44 precomp
drwxr-xr-x.  5 oracle oinstall         39 Jul 18 20:44 perl
drwxr-xr-x.  7 oracle oinstall         64 Jul 18 20:44 ord
drwxr-xr-x.  7 oracle oinstall         65 Jul 18 20:44 opmn
drwxr-xr-x.  5 oracle oinstall         46 Jul 18 20:44 nls
drwxr-xr-x. 10 oracle oinstall        106 Jul 18 20:44 network
drwxr-xr-x.  9 oracle oinstall         98 Jul 18 20:44 md
drwxr-xr-x. 10 oracle oinstall        112 Jul 18 20:44 ldap
drwxr-xr-x.  8 oracle oinstall         82 Jul 18 20:44 javavm
drwxr-xr-x.  5 oracle oinstall         41 Jul 18 20:44 hs
drwxr-xr-x.  4 oracle oinstall         30 Jul 18 20:44 drdaas
drwxr-xr-x.  3 oracle oinstall         20 Jul 18 20:44 demo
drwxr-xr-x.  7 oracle oinstall         71 Jul 18 20:44 cv
drwxr-xr-x.  6 oracle oinstall         55 Jul 18 20:44 crs
drwxr-xr-x.  9 oracle oinstall         93 Jul 18 20:44 assistants
drwxr-xr-x.  8 oracle oinstall        226 Jul 18 20:44 oui
drwxr-xr-x.  3 oracle oinstall      12288 Jul 18 20:44 lib
drwxr-x---. 13 oracle oinstall        219 Jul 18 20:44 inventory
drwxr-xr-x.  5 oracle oinstall        191 Jul 18 20:44 deinstall
drwxr-xr-x.  4 oracle oinstall         87 Jul 18 20:44 clone
drwxr-xr-x.  2 oracle oinstall       8192 Jul 18 20:44 bin
drwxr-xr-x.  2 oracle oinstall        102 Jul 18 20:44 addnode
drwxr-xr-x. 10 oracle oinstall       4096 Jul 18 20:53 install
-rw-r--r--.  1 oracle oinstall 4564649047 Jul 24 08:46 LINUX.X64_180000_db_home.zip
[oracle@orasolsTest Oracle_18c_Rel3_Linux_64bit]$

Step 2:Verify and check the Shared Memory Operating System level

It is very important to resize kernel.shmmax and kernel.shmall parameter in /etc/sysctl.conf as below


[oracle@orasolsTest Oracle_18c_Rel3_Linux_64bit]$ free -m
              total        used        free      shared  buff/cache   available
Mem:          64172        2071       32258        3009       29842       58432
Swap:         20479           0       20479
[oracle@orasolsTest Oracle_18c_Rel3_Linux_64bit]$ ipcs -lm

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 524288
max total shared memory (kbytes) = 8388608
min seg size (bytes) = 1

[oracle@orasolsTest Oracle_18c_Rel3_Linux_64bit]$ exit
logout
[root@orasolsTest u02]# vi /etc/sysctl.conf
[root@orasolsTest u02]# cat /etc/sysctl.conf
# sysctl settings are defined through files in
# /usr/lib/sysctl.d/, /run/sysctl.d/, and /etc/sysctl.d/.
#
# Vendors settings live in /usr/lib/sysctl.d/.
# To override a whole file, create a new file with the same in
# /etc/sysctl.d/ and put new settings there. To override
# only specific settings, add a file with a lexically later
# name in /etc/sysctl.d/ and put new settings there.
#
# For more information, see sysctl.conf(5) and sysctl.d(5).

#Add kernel parameters

fs.aio-max-nr = 1048576
fs.file-max = 6815744
#kernel.shmall = 2097152
#kernel.shmmax = 536870912
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586

[root@orasolsTest u02]# sysctl -p
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

Step 3: Create and Configure a Single Instance Database



Step 4: Choose as Server Class if you are doing on Server or Desktop for your own System


Step 5: Choose Enterprise Edition

 Standard Edition 2 seems new from Oracle 18c on premise DB


Step 6: Specify ORACLE_BASE location. 

It should be under same path where you extracted the Oracle 18c Release 3 software. Details in my post in Step 1.




Step 7: Choose General Purpose and Specify the Database Identifiers in the next screen.



Step 8: Specify configuration options

Memory
Character Set
Sample schemas







Step 9: Specify Database Storage options



Step 9: Specify Management options if required(optional step)


Step 10: Specify RECOVERY options if required(optional step)




Step 10: Specify SAMPLE options if required(optional step)


Step 11:Specify Privilege OS groups



Step 12: Installer does series of prerequisite checks


Step 13:Verify Summary and Click Install for installation and Database creation of Oracle 18c 







Step 14:Execute root.sh script to perform series of actions :

After executing 'root.sh' you will get Oracle Trace File Analyzer (TFA) location if you want you can setup it as well. This is new feature from Oracle 18c Release 3 Database.

[root@orasolsTest Oracle_18c_Rel3_Linux_64bit]# pwd
/u02/ora18c/soft/Oracle_18c_Rel3_Linux_64bit
[root@orasolsTest Oracle_18c_Rel3_Linux_64bit]# sh root.sh

[root@orasolsTest soft]# ls
Oracle_18c_Rel3_Linux_64bit
[root@orasolsTest soft]# cd Oracle_18c_Rel3_Linux_64bit/
[root@orasolsTest Oracle_18c_Rel3_Linux_64bit]# pwd
/u02/ora18c/soft/Oracle_18c_Rel3_Linux_64bit
[root@orasolsTest Oracle_18c_Rel3_Linux_64bit]# sh root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u02/ora18c/soft/Oracle_18c_Rel3_Linux_64bit

Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Do you want to setup Oracle Trace File Analyzer (TFA) now ? yes|[no] :
no
Oracle Trace File Analyzer (TFA - Non Daemon Mode) is available at :
    /u02/ora18c/soft/Oracle_18c_Rel3_Linux_64bit/suptools/tfa/release/tfa_home/bin/tfactl

Note :
1. tfactl will use TFA Daemon Mode if TFA already running in Daemon Mode and user has access to TFA
2. tfactl will configure TFA Non Daemon Mode only if user has no access to TFA Daemon mode or TFA Daemon mode is not

installed

OR

Oracle Trace File Analyzer (TFA - Daemon Mode) can be installed by running this script :
    /u02/ora18c/soft/Oracle_18c_Rel3_Linux_64bit/suptools/tfa/release/tfa_home/install/roottfa.sh

[root@orasolsTest Oracle_18c_Rel3_Linux_64bit]#


Step 15: After executing 'root.sh' continue with the Installation of the product




Click Close. This completes Oracle 18c Software Installation and 


Step 16: Post installation Steps:

=> Create the Environment file for Oracle 18c
=> Source the Environment

[oracle@orasolsTest ~]$ cat ora18c.env
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

#PATH=$PATH:$HOME/.local/bin:$HOME/bin
PATH=$PATH:$HOME/bin:/u02/ora18c/soft/Oracle_18c_Rel3_Linux_64bit/bin
export PATH
export ORACLE_SID=ora18c
export ORACLE_HOME=/u02/ora18c/soft/Oracle_18c_Rel3_Linux_64bit
export TNS_ADMIN=/u02/ora18c/soft/Oracle_18c_Rel3_Linux_64bit/network/admin

export TEMP=/tmp
export TMPDIR=/tmp

#export ALERT_LOG=/u02/ora18c/soft/Oracle_18c_Rel3_Linux_64bit/diag/rdbms/crimsdb/crimsdb/trace/

[oracle@orasolsTest ~]$ . ora18c.env
[oracle@orasolsTest ~]$ echo $ORACLE_SID
ora18c
[oracle@orasolsTest ~]$ echo $ORACLE_HOME
/u02/ora18c/soft/Oracle_18c_Rel3_Linux_64bit
[oracle@orasolsTest ~]$

Step 17: Connect to the Oracle 18c Database and validate it.

[oracle@orasolsTest ~]$ . ora18c.env
[oracle@orasolsTest ~]$ echo $ORACLE_SID
ora18c
[oracle@orasolsTest ~]$ echo $ORACLE_HOME
/u02/ora18c/soft/Oracle_18c_Rel3_Linux_64bit


[oracle@orasolsTest ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 24 13:15:04 2018

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
[oracle@orasolsTest ~]$ exit
logout
[root@orasolsTest Oracle_18c_Rel3_Linux_64bit]# clear
[root@orasolsTest Oracle_18c_Rel3_Linux_64bit]# su - oracle
Last login: Tue Jul 24 13:07:10 +03 2018 on pts/0
[oracle@orasolsTest ~]$ . ora18c.env
[oracle@orasolsTest ~]$ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Tue Jul 24 13:38:06 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA18C (DBID=604293408)

RMAN> exit


Recovery Manager complete.
[oracle@orasolsTest ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jul 24 13:38:19 2018
Version 18.3.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> select name from v$database;

NAME
---------
ORA18C

SQL> select status from v$instance;

STATUS
------------
OPEN


SQL> select *from v$version;

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
         0

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
    CON_ID
----------

Step 18: Validate the New Components added in Oracle 18c

We have Oracle Database Vault and Oracle Label security( Security components ) new from Oracle 18c Database.

SQL>
SQL> set linesize 12000
SQL> set pages 12000
SQL> col COMP_NAME for a35
SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

COMP_ID                        COMP_NAME                           VERSION                        STATUS
------------------------------ ----------------------------------- ------------------------------

--------------------------------------------
CATALOG                        Oracle Database Catalog Views       18.0.0.0.0                     VALID
CATPROC                        Oracle Database Packages and Types  18.0.0.0.0                     VALID
RAC                            Oracle Real Application Clusters    18.0.0.0.0                     OPTION OFF
JAVAVM                         JServer JAVA Virtual Machine        18.0.0.0.0                     VALID
XML                            Oracle XDK                          18.0.0.0.0                     VALID
CATJAVA                        Oracle Database Java Packages       18.0.0.0.0                     VALID
APS                            OLAP Analytic Workspace             18.0.0.0.0                     VALID
XDB                            Oracle XML Database                 18.0.0.0.0                     VALID
OWM                            Oracle Workspace Manager            18.0.0.0.0                     VALID
CONTEXT                        Oracle Text                         18.0.0.0.0                     VALID
ORDIM                          Oracle Multimedia                   18.0.0.0.0                     VALID
SDO                            Spatial                             18.0.0.0.0                     VALID
XOQ                            Oracle OLAP API                     18.0.0.0.0                     VALID
OLS                            Oracle Label Security               18.0.0.0.0                     VALID
DV                             Oracle Database Vault               18.0.0.0.0                     VALID

15 rows selected.


Enjoy have a nice time with Oracle 18c Database. Enjoy DBA Tasks.

In my upcoming posts I will focus on Administration Tasks for Oracle 18c... Stay Tune..


Thanks,

Rafi.









Friday, April 20, 2018

HP drivers compatibility with Red Hat Enterprise Linux 7 kernel

Hi DBAs,

Recently I installed   Red Hat Enterprise Linux  7.3  on HP Hardware and applied latest redhat patches which upgraded the kernel version to 7.4 . I came across issue where I was getting hpdsa HP driver error while booting with latest kernel after applying latest Red Hat patches because the kernel version got updated to latest 7.4 kernel due to this issue Red Hat Linux Operating system was not booting up and was giving error. After doing some research and with assistance of Red hat Support I was able to figure and resolve this issue. Sharing details of the Error and Steps followed to resolve this issue.

Kernel details:


Error:Unable to handle kernel NULL pointer dereference 
                                           Modules linked  hpdsa


Observation and Resolution:

 There is a problem with Raid driver for Gen9 Server . We had installed the driver during installation but the module was close driver from HPE and it needs to be upgraded accordingly. Our updated kernel was not kernel for Red Hat 7.3 but is was for Redhat 7.4


Red Hat Enterprise Linux 7.0 (Maipo), June 10,2014   ; 4 years ago, uses Linux Kernel 3.10.0-123
Red Hat Enterprise Linux 7.1, (Maipo), March 5,2015 ; 3 years ago, uses Linux Kernel 3.10.0-229
Red Hat Enterprise Linux 7.2 (Maipo), Nov 19,2015 ;   3 years ago, uses Linux Kernel 3.10.0-327
Red Hat Enterprise Linux 7.3 (Maipo), Nov   3,2016 ; 2 years ago, uses Linux Kernel 3.10.0-514
Red Hat Enterprise Linux 7.4 (Maipo), Aug  1,2017 ; 1 years ago, uses Linux Kernel 3.10.0-693

Latest driver for HP can be found in below location:

https://downloads.linux.hpe.com/SDR/repo/spp-gen9/2018.03.0/packages



As we can see for 7.4 kernel we have a different driver available

Each Red Hat release has one kernel version and only fixes so from 3.10.0-514 they will make only 3.10.0-514.XYX ,so same kernel module will work but if you upgrade to newer Red Hat Kernel (7.4) than new kernel module is required.

Download the driver module kmod-hpdsa-1.2.10-123,rhel7u4.x86_64.rpm and copy to the server.
Boot the RHEL server with older version of  kernel(Red Hat Enterprise Linux 7.3 (Maipo), Nov   3,2016 ; 2 years ago, uses Linux Kernel 3.10.0-514) which is working fine and install the rpm as below:

rpm –ivh packagename.rpm
rpm -ivh  kmod-hpdsa-1.2.10-123,rhel7u4.x86_64.rpm

After successfully installing RPM verify the kernel version, HP Driver module version and reboot the server. Use below commands for validations.

#rpm -q kernel
#rpm -a|grep -i kernel*
#modinfo hpdsa
# init 6

Choose below kernel to boot the server:

Red Hat Enterprise Linux 7.4 (Maipo), Aug  1,2017 ; 1 years ago, uses Linux Kernel 3.10.0-693

Now it will work and the issue is resolved.

Enjoy Learning and Working on Red Hat Linux ...

Thanks,





Friday, April 13, 2018

clrg comand for Oracle Apps DBA in Oracle SuperCluster Environment

Hi All,

In Oracle SuperCluster Environment we use important command 'clrg' for stopping and Starting of Oracle Web tier and Concurrent Manager Services.

clrg stands for clresourcegroup, which assist us to manage resource groups for Oracle Solaris Cluster data services or  on Oracle SuperCluster Environment

Below are commands we use on Oracle Apps nodes as 'root' Operating system user on Solaris Environment where Oracle Supercluster is running:

In order to:
·         Stop application on only application server 1 to stop all opmn and concurrent manager services on apps server1 and server2 completely.

  # clrg offline opmn-rg
  # clrg offline cm-rg


 Start application on only application server 1:

   # clrg online cm-rg
  #  clrg online opmn-rg

·         Stop application on one application server (i.e Apps Server 1 or Apps Server 2):

 # clrg offline –n nodename opmn-rg
# clrg offline –n nodename cm-rg

·         Start application on one application servers (i.e Apps Server 1 or Apps Server 2):

 # clrg online –n nodename cm-rg
 # clrg online –n nodename opmn-rg

·         Monitor the cluster resources
 # clrg status
 # clrs status

·         Monitor the full cluster staus:

# cluster status

Example :

root@erpclusterapps1:~# cluster status

=== Cluster Resource Groups ===

Group Name     Node Name          Suspended     State
----------     ---------          ---------     -----
scalmnt-rg     erpclusterapps2     No            Online
               erpclusterapps1     No            Online

cm-rg          erpclusterapps2     No            Online
               erpclusterapps1     No            Online

opmn-rg        erpclusterapps2     No            Online
               erpclusterapps1     No            Online


=== Cluster Resources ===

Resource Name     Node Name          State      Status Message
-------------     ---------          -----      --------------
erp-fs-rs         erpclusterapps2     Online     Online
                  erpclusterapps1     Online     Online

cm-rs             erpclusterapps2     Online     Online - Service is online.
                  erpclusterapps1     Online     Online - Service is online.

cmlsr-rs          erpclusterapps2     Online     Online - Service is online.
                  erpclusterapps1     Online     Online - Service is online.

opmn-rs           erpclusterapps2     Online     Online
                  erpclusterapps1     Online     Online

Hope it is useful. Enjoy learning Apps DBA on Exadata / Supercluster Environment


Thanks,

Rafi

Monday, January 8, 2018

Completed Red Hat Certified System Administrator 7 (RHCSA) EXAM (EX200)

Hi Friends,

I always believe DBA should have strong knowledge and experience in working on Unix flavor operating system. In this regard Red Hat Linux is always my favourite  operating system and always enjoy working on this Unix flavor operating system.

By Almighty 's and some efforts of mine I completed Red Hat Certified System Administrator 7 (RHCSA) EXAM(EX200) certification.

Link below for the credential validation:

https://www.redhat.com/rhtapps/certification/verify/?certId=170-277-842



Preparation:

Red Hat Enterprise Linux(RHEL) version 7 Training from Reputed training institute or Partner network should help in passing this exam. You can always mail me to rafidba.alvi@gmail.com for any assistance.


Enjoy learning Red Hat Linux.


Thanks,

Rafi

Friday, December 29, 2017

Useful Database Health Check scripts

Hi Friends,

Below are some useful Database Health Check scripts we can use for monitoring purpose.

These SQL scripts assists us to monitor Database and diagnose in case of any issues.

1) fra_space_check.sql( Flash recovery area and DR sync)

set lines 200;
select (SPACE_LIMIT/1024/1024/1024)SPACE_LIMIT_gb,
           (SPACE_USED/1024/1024/1024)SPACE_USED_gb,
           (SPACE_RECLAIMABLE/1024/1024/1024)SPACE_RECLAIMABLE_gb,
           ((SPACE_LIMIT/1024/1024/1024)-(SPACE_USED/1024/1024/1024)+(SPACE_RECLAIMABLE/1024/1024/1024)) AVAILABLE_SPACE,
       ROUND((SPACE_USED-SPACE_RECLAIMABLE)/SPACE_LIMIT*100,1)"PERCENT_FULL_OF_100%"
       FROM V$RECOVERY_FILE_DEST;

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


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

2)max_tablespace_size.sql(Tablespace space check)

set lines 200 pages 300;
select TABLESPACE_NAME,sum(BYTES)/1024/1024/1024,sum(MAXBYTES)/1024/1024/1024,sum(BYTES)/1024/1024/1024*100/(sum

(MAXBYTES)/1024/1024/1024) "Used%" from dba_data_files where AUTOEXTENSIBLE='YES' group by TABLESPACE_NAME order by 4;

3)longops_session.sql( long running operations in Database)

set linesize 180
col opname for a30
col username for a15
SELECT SID, SERIAL#, username,to_char(start_time, 'dd Mon, yyyy hh24:mi:ss') as start_time, opname, SOFAR, TOTALWORK,

ROUND(SOFAR/TOTALWORK*100,2) COMPLETE FROM  V$SESSION_LONGOPS
 WHERE
TOTALWORK != 0
AND    SOFAR != TOTALWORK
order by 1;

4)temp_space_check.sql(For Temporary tablespace space check)

col name for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management
"ExtManag",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes,
0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by
tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';


5)events_details.sql(Wait Events monitoring)

set pages 300;
set lines 200;
col event for a50;
col username for a10;
 select s.sid ,username,status ,s.module,s.sql_id,w.event,w.SECONDS_IN_WAIT,w.STATE from v$session_wait w,v$session s

where w.sid=s.sid and w.EVENT <> 'SQL*Net message from client' and username is not null and w.event <> 'Streams AQ:

waiting for messages in the queue';

6)log_switches_info.sql(Log switches in a day)

set lines 250;
set pages 200;
select to_char(first_time,'DD-MON-YYYY') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23" from v$log_history group by to_char(first_time,'DD-

MON-YYYY') order by 1;

7)logon_sesion.sql( Logon time for a session in Database)

select sid,serial#,to_char(logon_time,'DD-MON-YYYY:HH24:MI:SS'),program,module from v$session where sid=&1;

8)sessinfo_db.sql( Session information in Database)

select sid,serial#,program from v$session where sid=&1;

9)plan_query.sql( To get execution plan display of a query by providing sql_id from v$sql)

set lines 200;
set pages 1000;
select * from table(dbms_xplan.display_cursor('&1'));

10)sync_prod_db.sql(To check max sequence number in the primary database)

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

11)tablespace_space.sql( To get tablespace details type and space usage in Database)

set linesize 180
set pagesize 100
col "Name" for a30

SELECT d.tablespace_name "Name", d.contents "Type", d.status "Status",TO_CHAR(NVL(a.bytes / 1024 / 1024,

0),'99G999G990D90')
"Total Size (M)", TO_CHAR(NVL(NVL(f.bytes, 0),0)/1024/1024, '99G999G990D90')
"Free (MB)", TO_CHAR(NVL((NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00')
"Free %" FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) order by "Free %";

12)drsync_check.sql( Check the applied and unapplied archives on the Standby Database side)

select sequence# from v$archived_log where applied='NO';
select max(sequence#) from v$archived_log where applied='YES';
exit;
/


13)invalids.sql( To count the invalids objects in the Database)

select count(*) from dba_objects where status='INVALID';

14) lock_session.sql( To find the locking session details in the Database)

set linesize 180
set pagesize 10000
col sql_fulltext for a100
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 ;

15) Temp_usage.sql( To find the temporary tablespace usage details)

SELECT A.tablespace_name tablespace,
             D.mb_total,
             SUM(A.used_blocks * D.block_size) / 1024/1024 mb_used,
             D.mb_total - SUM(A.used_blocks * D.block_size) / 1024/1024 mb_free
         FROM v$sort_segment A,
             (SELECT B.name, C.block_size, SUM(C.bytes) / 1024/1024 mb_total
                FROM v$tablespace B, v$tempfile C
               WHERE B.ts# = C.ts#
               GROUP BY B.name, C.block_size) D
         WHERE A.tablespace_name = D.name
        GROUP by A.tablespace_name, D.mb_total;

16) master_db_hc.sql( Master Health check script for Database,creates DBHEALTH.html report)

set feedback off;
set markup html on spool on;
spool DBHEALTH.html;

set termout off;

prompt ************  DAILY HEALTH CHECK SCRIPT DB************
prompt ************  SCRIPT FOR DB: RAFI ALVI   ************

prompt

prompt**---------------Time-----------------------------**
select sysdate from dual;

prompt**---------------Database Details-----------------------------**
prompt
SELECT DBID "DATABASE_ID", NAME "DB_NAME", LOG_MODE, OPEN_MODE, RESETLOGS_TIME  FROM V$DATABASE;
SELECT instance_name, status, to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM   sys.v_$instance;
column "Host Name" format a15;
column "Host Address" format a15;
SELECT UTL_INADDR.GET_HOST_ADDRESS "Host Address", UTL_INADDR.GET_HOST_NAME "Host Name" FROM DUAL;
SELECT BANNER "VERSION" FROM V$VERSION;
col "Database Size" format a15;
col "Free space" format a15;
select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",
round(free.p / 1024 / 1024/1024) || ' GB' "Free space"
from (select bytes from v$datafile
union all select bytes from v$tempfile
union all select bytes from v$log) used,
(select sum(bytes) as p from dba_free_space) free
group by free.p;

prompt**---------------SGA Component Size------------------------------**
set line 200;
select    pool, m_bytes from ( select     pool, to_char( trunc(sum(bytes)/1024/1024,2), '99999.99' ) as M_bytes
    from     v$sgastat
    where     pool is not null   group     by pool
    union
    select     name as pool, to_char( trunc(bytes/1024/1024,3), '99999.99' ) as M_bytes
    from     v$sgastat
    where    pool is null  order     by 2 desc
    ) UNION ALL
    select    'TOTAL' as pool, to_char( trunc(sum(bytes)/1024/1024,3), '99999.99' ) from v$sgastat;


prompt**---------------ASM SPACE DETAILS------------------------------**


select NAME,TOTAL_MB/1024 "Total",FREE_MB/1024 "Free",REQUIRED_MIRROR_FREE_MB/1024 "REQFORMIRROR",USABLE_FILE_MB/1024

"Usable" from v$asm_diskgroup;


prompt**---------------FRA USAGE and FREE SPACE------------------------------**

select (SPACE_LIMIT/1024/1024/1024)SPACE_LIMIT_gb,
       (SPACE_USED/1024/1024/1024)SPACE_USED_gb,
       (SPACE_RECLAIMABLE/1024/1024/1024)SPACE_RECLAIMABLE_gb,
       ((SPACE_LIMIT/1024/1024/1024)-(SPACE_USED/1024/1024/1024)+(SPACE_RECLAIMABLE/1024/1024/1024)) AVIALABLE_SPACE,
       ROUND((SPACE_USED-SPACE_RECLAIMABLE)/SPACE_LIMIT*100,1)"PERCENT_FULL_OF_100%"
       FROM V$RECOVERY_FILE_DEST;


prompt**--------------Monitoring Objects Created within 2 days---------------------**
select count(1) from user_objects where CREATED >= sysdate - 2;

prompt**--------------Counting Invalid object in Database---------------------------**
Select owner, object_type, count(*) from dba_objects where status='INVALID' group by  owner, object_type;

prompt**---------------Track Redolog Generation-------------------------------------**
select trunc(completion_time) logdate, count(*) logswitch, round((sum(blocks*block_size) / 1024 / 1024)) "REDO PER DAY

(MB)"
from v$archived_log
group by trunc(completion_time)
order by 1;

prompt**--------------Monitor DB Corruption--------------------**
set line 200;
SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name, d.STATUS,
    r.ERROR, r.CHANGE#, r.TIME FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
    WHERE t.TS# = d.TS# AND d.FILE# = r.FILE#;

prompt**---------------Tablespace Information--------------------------------------**
set pages 200;
select df.tablespace_name "Tablespace",
    totalusedspace "Used MB",
    (df.totalspace - tu.totalusedspace) "Free MB",
    df.totalspace "Total MB",
    round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct.Free"
    from
    (select tablespace_name,
    round(sum(bytes) / 1048576) TotalSpace
    from dba_data_files
   group by tablespace_name) df,
   (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
   from dba_segments
   group by tablespace_name) tu
   where df.tablespace_name = tu.tablespace_name
   order by "Pct.Free";

spool off;
set markup html off;

master_db_check.sh

#!/bin/sh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export GRID_HOME=/u01/app/11.2.0/grid
export PATH=$PATH:$ORACLE_HOME/bin:.:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin:$GRID_HOME/bin
export TNS_ADMIN=$GRID_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data/9idata
export ORACLE_SID=TESTDB1
DBHEALTH="$(date +%d%m%y)"
mkdir /home/oracle/DBA/dailyreports/$DBHEALTH
cd /home/oracle/DBA/dailyreports/$DBHEALTH
sqlplus / as sysdba <<EOF
@/home/oracle/DBA/scripts/dailyhc.sql
exit;
EOF
rm -f $DBHEALTH.html
mv DBHEALTH.html $DBHEALTH.html

18)arch_del.sh( Archive log deletion script)

#!/bin/sh

export ORACLE_BASE=/u01/app/oracle

ORACLE_HOME="/u01/app/oracle/db"
export ORACLE_HOME

export GRID_HOME=/u01/app/11.2.0/grid

ORACLE_SID="TESTDB"
export ORACLE_SID

ORA_NLS10="/u01/app/oracle/db/nls/data/9idata"
export ORA_NLS10

PATH="/u01/app/oracle/db/bin:/usr/ccs/bin:/usr/sbin:/u01/app/oracle/db/jdk/jre/bin:$PATH:."
export PATH

LINK_CNTRL=""
export LINK_CNTRL

TNS_ADMIN="/u01/app/oracle/db/network/admin/TESTDB_host01"
export TNS_ADMIN


cd home/oracle/DBA/logs/archdel
rman target / log="delarch_$(date +%d%m%y_%H%M%S).log" <<HERE
delete noprompt archivelog all completed before 'sysdate - 4';
crosscheck archivelog all;
list expired archivelog all;
HERE


19) locks.sql (locks on objects)

SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id;


Enjoy Monitoring Databases..

Thanks,

Rafi




Sunday, March 12, 2017

Cloning Oracle EBS Applications and Database

Hi DBAs,

Cloning is one of the regular tasks done by Apps DBA & DBAs. I have done lot of clonning
in various ways,below is one such task.Hope you will like it. Theses steps are applicable for
Oracle EBS Release 12.1.3 and Database version is 11.2.0.4.0


Best Oracle Apps DBA Blog  on Google Search Engine

Step 1: Source Environment Preparation Steps:
Run adpreclone.pl on DB and Application Tier
DB Tier:
cd $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME
perl adpreclone.pl dbtier
Application Tier:
cd $ADMIN_SCRIPTS_HOME
perl adpreclone.pl appsTier

Step 2: Take Backup of Database and Applications:
Database:
Use the below script to take full rman backup taken can be used for cloning on source Environment.
run
{
configure device type disk parallelism 6;
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c6 device type disk;
backup full database format '/backup/rman/EBSTEST/juneDD/EBSTEST_Full%d_%s_%T_%U' database;
backup format '/backup/rman/EBSTEST/juneNN/EBSTEST_ARCH%d_%s_%T_%U' archivelog all;
backup current controlfile format '/backup/rman/EBSTEST/juneNN/control%d%t_control.ctl';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
}

Note: Replace DD with the date or any other directory name as required.

Application Tier:
Compress the top directories using the below commands:
tar –cf /backup/PROD/appl/appsst<date>.tar /erpapp/apps/apps_st
tar –cf /backup/PROD/appl/techst<date>.tar /erpapp/apps/tech_st


Step 3:Copy and Extract Files to DEV Node:

Copy the appsst.tar and techst.tar to respective locations in DEV and extract using:
tar –xf appsst.tar
tar –xf techst.tar

Step 4:Preparing DEV for Refresh:

a) Shutdown Applications and Database Services
b) Take DEV backup if required
c) Take a backup of pfile
d) sqlplus ‘/as sysdba’
startup mount
alter system enable restricted session;
drop database;
e) cp initTESTEBS.ora initEBSTEST.ora
vi initEBSTEST.ora
db_name=EBSTEST
control_files='+DATA/TESTEBS/CONTROLFILE/ctr<new1>.dbf','+DATA/TESTEBS/CONTROLFILE/ctr<new2>.dbf ','+DATA/TESTEBS/CONTROLFILE/ctr<new3>.dbf '
export ORACLE_SID=EBSTEST
startup nomount


Step 5: Restore and Recover Database
Restore controlfile from backup of source Database which should be copied using scp to the
target DB server.

rman target /
restore controlfile from ‘<PATH OF CONTROLFILE BACKUP>’
alter database mount;
crosscheck backup;
delete expired backup;
crosscheck backup;
list backup;
Note:Find the latest sequence and thread number and use them in place of “SEQUENCE” and “N” in below script.

run
{
set until sequence <SEQUENCE> thread <N>;
configure device type disk parallelism 6;
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c6 device type disk;
set newname for database to '+DATA';
restore database;
switch datafile all;
recover database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
}
exit;

This completes the database restore and recovery.

Step 6: Configuring DEV Database

Now the Database is in Mount State with EBSTEST name.
Change the redolog file names of Thread 1 to +RECO
a. alter database rename file ‘+RECO/EBSTEST…’ to ‘NEW PATH’
alter database backup controlfile to trace
shut immediate
b. Rename trace file to ctrl.sql
vi ctrl.sql
Change “REUSE” to “SET”
Change “EBSTEST” to “EBSDEV”
Change “noresetlogs” to “resetlogs”
Change “archivelog” to “noarchivelog”
c. cp initEBSTEST.ora initEBSDEV.ora
Change db_name=AMDEV in initEBSDEV.ora
d. export ORACLE_SID=EBSDEV
e. sqlplus ‘/as sysdba’
@ctrl.sql
alter database open resetlogs;
f. Run Autoconfig

This completes the Database Cloning

Before starting cloning, modify FND Template as below:
cd $FND_TOP/admin/driver/fndtmpl.drv
txkWfClone.sh INSTE8_SETUP to txkWfClone.sh INSTALL_SETUP

Step 7:Clone Application Tier:
cd /erpapp/apps/apps_st/comn/clone/bin
perl adcfgclone.pl appsTier
Answer the prompts and do not start services once apply is complete

Step 8:Take a backup of context file 
vi $CONTEXT_FILE
Change the load balancing related parameters

Step 9:Start the EBS applications
5.11 adstrtal.sh apps/<password>

Post Cloning Steps:

a) Change logo
Upload the below logo to $OA_MEDIA/COMPLOGO_SC.jpg

b) Run the Data masking script:

 c) Cancel the Running/Pending Concurrent Requests
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code IN ('Q','I')
AND requested_start_date > SYSDATE
AND hold_flag = 'N';

UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE phase_code IN ('R','P');
Commit;

d) Disable Workflow Notification Mailer. Set the override address from OAM. Purge pending notifications.

UPDATE WF_NOTIFICATIONS
SET MAIL_STATUS = 'SENT', STATUS = 'CLOSED'
where status in ('OPEN', 'CANCELED')
and mail_status in ('MAIL', 'INVALID');

sqlplus apps/appspwd@db @$FND_TOP/patch/115/sql/wfntfqup.sql apps appspwd applsys

update wf_roles set NOTIFICATION_PREFERENCE=’DISABLED’,email_address=null;

e) Update per_all_people_f set email_address=null where email_address is not null;

f) Change the profile options:
Site Name to “DEV Instance”
Java Color Scheme to a color which is not same as PROD

e) Change the system, apps, sysadmin, xxcomp passwords
f) Add tempfile to Temp Tablespace
g) Change the URLs previously in Context File configured for Load Balancing in PROD and run autoconfig.

s_external_url http://testebs.COMPLOGO.ae:8000
s_login_page http://testebs.COMPLOGO.ae:8000/OA_HTML/AppsLogin
s_webentryhost testebs
s_webentrydomain COMPLOGO.ae

h) Update the Node information for all Concurrent Managers from Concurrent Manager Define to current node name and restart concurrent managers using adcmctl.sh
i) DEACTIVATE “Periodic Alert Scheduler”
j) Update Discoverer URL in Context File ( Application Tier - s_disco_url):
http://customsoadev.COMPLOGO.ae:7777
k) erpuat@COMPLOGO.ae shall be used as the Email ID for notifications in case if WF Notification Mailer is being enabled

Hope you followed clonning steps. Enjoy Apps DBA tasks.

I will soon be posting  ERP 12.2 cloning Stay tune...

Thanks,

Rafi

www.orasols.com


Wednesday, July 6, 2016

Changing Weblogic user password in Oracle EBS Applications 12.2.5

Hi All,

We can change the 'weblogic' user password in EBS Release 12.2.5 using txkUpdateEBSDomain.pl perl script. Before changing the 'weblogic'  user password we need to shutdown all the Middle Tier applications using 'adstpall.sh' script.





Best Oracle Apps DBA Blog  on Google Search Engine

Below are the steps involved.

Step 1:Source the run filesystem  in the Oracle EBS Applications 12.2 or higher.

/erpapp/testebs/upgrade
. EBSapps.env run

Step 2:Change the password using txkUpdateEBSDomain.pl  perl script.

applmgr@host1:/erpapp/testebs/upgrade$ perl /erpapp/testebs/upgrade/fs1/EBSapps/appl/fnd/12.0.0/patch/115/bin/txkUpdateEBSDomain.pl -action=updateAdminPassword

Program: txkUpdateEBSDomain.pl started at Wed Mar 23 13:51:40 2016

AdminServer will be re started after changing WebLogic Admin Password
All Mid Tier services should be SHUTDOWN before changing WebLogic Admin Password
Confirm if all Mid Tier services are in SHUTDOWN state. Enter "Yes" to proceed or anything else to exit: yes

Enter the full path of Applications Context File [DEFAULT - /erpapp/testebs/upgrade/fs1/inst/apps/testebs_host1/appl/admin/testebs_host1.xml]:
Enter the WLS Admin Password:
Enter the new WLS Admin Password:
Enter the APPS user password:

Executing: /erpapp/testebs/upgrade/fs1/FMW_Home/webtier/perl/bin/perl /erpapp/testebs/upgrade/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/adProvisionEBS.pl  ebs-get-serverstatus -contextfile=/erpapp/testebs/upgrade/fs1/inst/apps/testebs_host1/appl/admin/testebs_host1.xml -servername=AdminServer -promptmsg=hide -logfile=/erpapp/testebs/upgrade/fs1/inst/apps/testebs_host1/logs/appl/rgf/TXK/txkUpdateEBSDomain_Wed_Mar_23_13_51_40_2016/EBSProvisioner.log
ERROR : Admin Server is not RUNNING, cannot proceed further.
Exiting..applmgr@host1:/erpapp/testebs/upgrade$ cd $ADMIN_SCRIPTS_HOME
applmgr@host1:/erpapp/testebs/upgrade/fs1/inst/apps/testebs_host1/admin/scripts$ ./adadminsrvctl.sh start

You are running adadminsrvctl.sh version 120.10.12020000.10

Enter the WebLogic Admin password:
Enter the APPS Schema password:
Starting WLS Admin Server...


Domain updated successfully
Restarting AdminServer with new Admin Password.

You are running adadminsrvctl.sh version 120.10.12020000.10

Stopping WLS Admin Server...
Refer /erpapp/testebs/upgrade/fs1/inst/apps/testebs_host1/logs/appl/admin/log/adadminsrvctl.txt for details

AdminServer logs are located at /erpapp/testebs/upgrade/fs1/FMW_Home/user_projects/domains/EBS_domain_testebs/servers/AdminServer/logs

adadminsrvctl.sh: exiting with status 0

adadminsrvctl.sh: check the logfile /erpapp/testebs/upgrade/fs1/inst/apps/testebs_host1/logs/appl/admin/log/adadminsrvctl.txt for more information ...


You are running adnodemgrctl.sh version 120.11.12020000.12


NodeManager log is located at /erpapp/testebs/upgrade/fs1/FMW_Home/wlserver_10.3/common/nodemanager/nmHome1

adnodemgrctl.sh: exiting with status 0

adnodemgrctl.sh: check the logfile /erpapp/testebs/upgrade/fs1/inst/apps/testebs_host1/logs/appl/admin/log/adnodemgrctl.txt for more information ...


You are running adadminsrvctl.sh version 120.10.12020000.10

Starting WLS Admin Server...
Refer /erpapp/testebs/upgrade/fs1/inst/apps/testebs_host1/logs/appl/admin/log/adadminsrvctl.txt for details

AdminServer logs are located at /erpapp/testebs/upgrade/fs1/FMW_Home/user_projects/domains/EBS_domain_testebs/servers/AdminServer/logs

adadminsrvctl.sh: exiting with status 0

adadminsrvctl.sh: check the logfile /erpapp/testebs/upgrade/fs1/inst/apps/testebs_host1/logs/appl/admin/log/adadminsrvctl.txt for more information ...


*************** IMPORTANT ****************
WebLogic Admin Password is changed.
Restart all application tier services using control scripts.
********************************************

Step 3: Start the EBS applications using 'adstral.sh' 
$ADMIN_SCRIPTS_HOME
$./adstral.sh

Step 4:Login to admin console for Oracle EBS environment and verify the 'weblogic' user password.

Verify by login to admin console using the 'weblogic' user and make sure all the managed servers are up & running.


Happy Apps DBA Tasks,....


Thanks,

Rafi