E-mail Generation for Daily Health Check Report via mailx OS utility

For e-mail generation we need SQL script and shell script files. SQL script extract data from DB to health check report and the shell script generate e-mail via mailx utility with sql output and then scheduled this for daily. 

SQL Scripts

[oracle@kiawiz ~]$ cat ts.sql
set feedback off echo off
set linesize 100
set pagesize 200
set colsep |
SET TRIMSPOOL ON
spool /home/oracle/tablespace.log
column tablespace_name format a25
column FreePC format a15
column FullPC format a15
select b.tablespace_name,round(tbs_size,0) SizeMb,round((tbs_size- a.free_space),0)UsedMb,round(a.free_space,0) FreeMb,to_char((round(((a.free_space*100)/tbs_size),2)),'00.00')||'%' FreePC,to_char((100-round(((a.free_space*100)/tbs_size),2)),'00.00')||'%' FullPC
from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name
UNION
select tablespace_name,((free_space/1024)/1024) free_space
from dba_temp_free_space) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name=b.tablespace_name
order by 100-round(((a.free_space*100)/tbs_size),2) desc;

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

column object_name format a30
column owner format a15
select object_name,owner,object_type from dba_objects where status ='INVALID';
spool off
exit

Schell Script

[oracle@kiawiz ~]$ cat tablespace_new.sh
#!/bin/sh
. /finsys/db/tech_st/11.1.0/PROD_kiawiz.env
cat /home/oracle/tablespace.log
sqlplus '/ as sysdba' << EOF
@ts.sql;
EOF
if [ `cat /home/oracle/tablespace.log|wc -l` -gt 0 ]
then

echo -e "\n"  2>&1>>/home/oracle/tablespace.log
df -h 2>&1>>/home/oracle/tablespace.log

cat /home/oracle/tablespace.log |mailx -s "Production instance - Health Check" test@gmail.com
rm -rf /home/oracle/tablespace.log
fi

Schedule .sh file via crontab

00 09 * * 0-6 . /home/oracle/tablespace_new.sh

ORA-00020: maximum number of processes (%s) exceeded

Problem

The issue is due to process/sessions parameter exceeds the limit. Need to increase those parameter to solve this issue.


Solution

When ORA-00020 occurred then you wont able to  connect to the DB via sys user to change the parameter. You can use following steps to overcome the issue.

1. Login to DB using following command

[oracle@prod 11.1.0]$ sqlplus -prelim "/ as sysdba"

2. Shutdown the DB

SQL>shutdown abort;

3. Exit from session

SQL>exit;

4. Connect to DB via sys

[oracle@prod 11.1.0]$ sqlplus / as sysdba

5. Start the DB

SQL>startup;

6. Check the Value for Process and sessions parameter

SQL>select resource_name, current_utilization, max_utilization, limit_value from v$resource_limit where resource_name in ('sessions', 'processes');

7. Change the Parameter values

SQL> alter system set processes=300 scope=spfile;
SQL> alter system set sessions=500 scope=spfile;

Tablespace Related Queries

Create Tablespace   

SQL> create tablespace xxli_ts01 datafile '/finsys/UAT1/db/apps_st/data/xxli_data.dbf' SIZE 1500M autoextend on next 256M maxsize 10000M;

Resize Tablespace 

SQL> alter database datafile '/finsys/UAT1/db/apps_st/data/sysaux02.dbf' resize 7000M;

Add Datafile

SQL> alter TABLESPACE APPS_TS_TX_DATA add datafile '/finsys/UAT1/db/apps_st/data/a_txn_data05.dbf' SIZE 1800M autoextend on next 256M maxsize 3500M;

Auto extend on/off

SQL> ALTER DATABASE DATAFILE '/finsys/UAT1/db/apps_st/data/xxkia01.dbf' AUTOEXTEND ON maxsize unlimited;

SQL> ALTER DATABASE DATAFILE '/finsys/UAT1/db/apps_st/data/xxkia01.dbf' AUTOEXTEND OFF;

Unlimited Quota

SQL> alter user apps quota unlimited on apps_ts_tx_data;

Drop datafile

SQL> alter tablespace APPS_TS_TX_DATA drop datafile '/finsys/UAT1/db/apps_st/data/xxkia01.dbf';

Move Datafile

1)

SQL> ALTER TABLESPACE XXTEST OFFLINE NORMAL;

2)

Copy the datafile (/tmp/test.dbf) to their new location (/finsys/UAT1/db/apps_st/data/) and rename (XXTEST1.DBF) them using the operating system command

3)

SQL> ALTER TABLESPACE XXTEST RENAME DATAFILE '/tmp/test.dbf' TO '/finsys/UAT1/db/apps_st/data/XXTEST1.DBF';

4)

SQL> ALTER TABLESPACE XXTEST ONLINE;

Tablespace Related Views

dba_data_files

dba_free_space

v$tablespace

Create Temp Tablespace

SQL> create temporary tablespace temp4 tempfile '/finsys/UAT1/db/apps_st/data/temp04.dbf' size 1000M tablespace group TEMP;

Resize Temp Tablespace

SQL> ALTER DATABASE TEMPFILE '/finsys/UAT1/db/apps_st/data/temp01.dbf' RESIZE 1000M;

Add Temp File

SQL> alter TABLESPACE TEMP1 add tempfile '/finsys/UAT1/db/apps_st/data/temp01.dbf' SIZE 2000M autoextend on next 256M maxsize 10240M;

Drop Temp File

SQL> alter TABLESPACE TEMP1 drop tempfile '/finsys/UAT1/db/apps_st/data/temp01.dbf';

Drop Temp Tablespace

SQL> DROP TABLESPACE temp1 including CONTENTS AND DATAFILES;

Make  Temp Tablespace default

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp4;

Adding tablespce to group

SQL> ALTER TABLESPACE temp01 TABLESPACE GROUP TEMP;

Remove tablespce from group

SQL> ALTER TABLESPACE temp01 TABLESPACE GROUP '';

Temp Tablespace Related Views

DBA_TEMP_FILES

dba_temp_free_space

DBA_TABLESPACE_GROUPS;

V$TEMP_SPACE_HEADER

Details/Usage of Tablespaces

set linesize 5000;
col tablespace_name for a30;
col FreePC for a10;
set pagesize 1000;
select
b.tablespace_name,
round(tbs_size,0) SizeMb,
round((tbs_size- a.free_space),0)UsedMb,
round(a.free_space,0) FreeMb,
to_char((round(((a.free_space*100)/tbs_size),2)),'00.00')||'%' FreePC,
to_char((100-round(((a.free_space*100)/tbs_size),2)),'00.00')||'%' FullPC
from 
(  select 
     tablespace_name, 
     round(sum(bytes)/1024/1024 ,2) as free_space 
   from dba_free_space 
   group by tablespace_name
 UNION
   select 
     tablespace_name,
     ((free_space/1024)/1024) free_space
   from dba_temp_free_space
)a, 

(  select 
     tablespace_name,
     sum(bytes)/1024/1024 as tbs_size 
   from dba_data_files 
   group by tablespace_name
 UNION
   select 
     tablespace_name,
     sum(bytes)/1024/1024 tbs_size
   from dba_temp_files
   group by tablespace_name 
)b
where a.tablespace_name=b.tablespace_name
order by 100-round(((a.free_space*100)/tbs_size),2) desc;

OR

set linesize 5000;
col tablespace_name for a30;
col FreePC for a10;
set pagesize 1000;
select
b.tablespace_name,
round(tbs_size,0) SizeMb,
round((a.tot_used),0)UsedMb,
round((tbs_size-a.tot_used),0) FreeMb,
decode((round(((a.tot_used*100)/tbs_size),2)),0,(100-round(((a.tot_used*100)/tbs_size),2)||'%'),(to_char((100-round(((a.tot_used*100)/tbs_size),2)),'00.00')||'%')) FreePC,
to_char((round(((a.tot_used*100)/tbs_size),2)),'00.00')||'%' FullPC
from 
(  select 
     tablespace_name, 
     sum(bytes/1024/1024) tot_used
   from dba_segments
   group by tablespace_name
 UNION
   select 
     tablespace_name,
     round(used_blocks*8/1024) tot_used
   from v$sort_segment 
)a, 
(  select 
     tablespace_name,
     sum(bytes)/1024/1024 as tbs_size 
   from dba_data_files 
   group by tablespace_name
 UNION
   select 
     tablespace_name,
     sum(bytes)/1024/1024 tbs_size
   from dba_temp_files
   group by tablespace_name 
)b
where a.tablespace_name=b.tablespace_name
order by round(((a.tot_used*100)/tbs_size),2) desc;

FRM-40735: ON-INSERT Trigger Raised Unhandled Exception ORA-29273

Problem

Following Error Occur While Creating Location in R12.

FRM-40735: ON-INSERT Trigger Raised Unhandled Exception ORA-29273

Solution

Set Profile option 'IRC: Geocode Host' to null for site level and log out and log back

Release/Version Number for Oracle EBS Components and Linux OS

Database Version

Connect sqlplus as apps user and run below query

 SQL>select banner from v$version;

Application Release

Connect sqlplus as apps user and run below quer

 SQL>select release_name from apps.fnd_product_groups;

Start CD Version

 cd startCD/Disk1/rapidwiz
 ./RapidWizVersion.sh

Report Builder

Log in as Application user, set environment variable and run below command

$ORACLE_HOME/bin/rwrun | grep Release

Forms Compiler

Log in as Application user, set environment variable and run below command

$ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version

Java

Log in as Application user, set environment variable and run below command

$ORACLE_HOME/jdk/bin/java -fullversion

Application Server or Webserver/Apache

Log in as Application user, set environment variable and run below command

$IAS_ORACLE_HOME/Apache/Apache/bin/httpd –version

JRE

Log in as Application user, set environment variable and run below command

cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version

Oracle Jinitiator

Log in as Application user, set environment variable and run below command

$grep jinit_ver_comma $CONTEXT_FILE

File Version of File System

$strings -a <file_name> | grep '$Header'

OR

$cat <file_name> | grep Header

Workflow Version

Connect sqlplus as apps user and run below quer

 SQL>select text ,name from wf_resources where name like '%WF_VERSION%';

PL/SQL Version

Log in as Application user, set environment variable and run below command

$ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version

FMW Webtier

Log in as Application user, set environment variable and run below command

$export ORACLE_HOME=$IAS_ORACLE_HOME
$ $ORACLE_HOME/OPatch/opatch lsinventory |grep WebTier

FMW Oracle Common

Log in as Application user, set environment variable and run below command

$export ORACLE_HOME=$FMW_HOME/oracle_common
$ $ORACLE_HOME/OPatch/opatch lsinventory |grep Oracle |grep Common

Weblogic Server

Log in as Application user, set environment variable and run below command

$ cd $FMW_HOME/wlserver_10.3/server/lib
$ java -cp weblogic.jar weblogic.version

Linux

$ lsb_release -a 

OR

$ cat /etc/redhat-release

Cloning EBS Instance using Cold backup

1. Extract file system from backup

$ tar -xvf test.tar

2. Change user and Permission to binary owner

$ Chown -R oracle:dba TEST
$ chmod -R 775 TEST

3. Switch to binary owner

$ su - oracle

4. Run postclone for DB Tier

$ cd /u01/TEST/db/tech_st/11.1.0/appsutil/clone/bin/
$ perl adcfgclone.pl dbTier

Target System Hostname (virtual or normal) [test] :

 Target Instance is RAC (y/n) [n] :

 Target System Database SID : TEST

 Target System Base Directory : /u01/TEST/

 Target System utl_file_dir Directory List :  /u01/TEST/db/tech_st/11.1.0/appsutil                                              
 Number of DATA_TOP's on the Target System [1] :

 Target System DATA_TOP Directory 1 [/findata/TEST/db/apps_st/data] :  /u01/TEST/db/apps_st/data

 Target System RDBMS ORACLE_HOME Directory  [/u01/TEST/db/tech_st/11.1.0] :

 Do you want to preserve the Display [kiawiz:0.0] (y/n)  : n

 Target System Display [test:0.0] :

 Do you want the the target system to have the same port values as the  source system  (y/n) [y] ? : n

 Target System Port Pool [0-99] : 30

5. After Successful of DB Clone then Run postclone for Application Tier

$ cd /u01/TEST/apps/apps_st/comn/clone/bin/
$ perl adcfgclone.pl appsTier

Target System Hostname (virtual or normal) [test] :

 Target System Database SID : TEST

 Target System Database Server Node [test] :

 Target System Database Domain Name [kia.lk] :

 Target System Base Directory : /u01/TEST/

 Target System Tools ORACLE_HOME Directory  [/u01/TEST/apps/tech_st/10.1.2] :

 Target System Web ORACLE_HOME Directory  [/u01/TEST/apps/tech_st/10.1.3] :

 Target System APPL_TOP Directory [/u01/TEST/apps/apps_st/appl] :

 Target System COMMON_TOP Directory [/u01/TEST/apps/apps_st/comn] :

 Target System Instance Home Directory [/u01/TEST/inst] :

 Target System Root Service [enabled] :

 Target System Web Entry Point Services [enabled] :

 Target System Web Application Services [enabled] :

 Target System Batch Processing Services [enabled] :

 Target System Other Services [disabled] :

 Do you want to preserve the Display [kiawiz:0.0] (y/n)  : n

 Target System Display [test:0.0] :

 Do you want the the target system to have the same port values as the  source system  (y/n) [y] ? : n

 Target System Port Pool [0-99] : 30

 Checking the port pool 30
 done: Port Pool 30 is free
 Report file located at /u01/TEST/inst/apps/TEST_test/temp/portpool.lst
 Complete port information available at  /u01/TEST/inst/apps/TEST_test/temp/portpool.lst

 UTL_FILE_DIR on database tier consists of the following directories.

1. /usr/tmp
2. /u01/TEST/db/tech_st/11.1.0/appsutil
3. /u01/TEST/db/tech_st/11.1.0/appsutil/outbound/TEST_test
4. /usr/tmp
Choose a value which will be set as APPLPTMP value on the target node[1]

After successful of post clone, Can access to the system.

Installation and Configuration of 12c Database

Container and Pluggable Databases are the New future of 12c Databases.

We can give the name of pluggable while installing 12c

[root@logisticdev ~]# su - oracle

[oracle@logisticdev ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 21 13:40:33 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select banner from v$version;

BANNER
----------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

On Container Database

SQL> select sys_context('userenv','con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
----------------------------------------------------------
CDB$ROOT

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PD1                            READ WRITE

SQL> select name from v$tablespace;

NAME
------------------------------
SYSAUX
SYSTEM
UNDOTBS1
USERS
TEMP
SYSTEM
SYSAUX
TEMP
SYSTEM
SYSAUX
TEMP

NAME
------------------------------
USERS
EXAMPLE

13 rows selected.

On Pluggable Database

SQL> alter session set container =PD1;

Session altered.

SQL> select sys_context('userenv','con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
----------------------------------------------------------
PD1

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         3 PD1                            READ WRITE

SQL> select name from v$tablespace;

NAME
------------------------------
UNDOTBS1
SYSTEM
SYSAUX
TEMP
USERS
EXAMPLE

6 rows selected.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64 bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt ions

Cloning Pluggable Database

[oracle@logisticdev ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 21 13:48:29 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt ions

SQL> select sys_context('userenv','con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
---------------------------------------------------
CDB$ROOT

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PD1                            READ ONLY

SQL> alter pluggable database pd1 close;

Pluggable database altered.

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PD1                            MOUNTED

SQL> alter pluggable database pd1 open read only;
alter pluggable database pd1
Pluggable database altered.

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PD1                            READ ONLY

SQL> create pluggable database PD2 from PD1 file_name_convert=('/ora12c/oracle/a pp/oracle/oradata/TEST/pd1','/ora12c/oracle/app/oracle/oradata/TEST/pd2');

Pluggable database created.

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PD1                            READ ONLY
         4 PD2                            MOUNTED

SQL> alter pluggable database PD1 close;

Pluggable database altered.

SQL> alter pluggable database PD1 open;

Pluggable database altered.

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PD1                            READ WRITE
         4 PD2                            MOUNTED

Uninstall 12c Database

$ cd <ORACLE_HOME>/deinstall
$ ./deinstall