Migrate Non ASM to ASM Database - 11g

Prerequisite
Running Non ASM 11g Database

Migration Steps

Download and Install appropriate ASMLib RPM's

• oracleasm-support-2.1.7-1.el5.i386.rpm
• oracleasmlib-2.0.4-1.el5.i386.rpm
• oracleasm-[your-kernel-version].rpm

Then create the disk as follows which using creation of ASM instance

I have created 2 disks DISK1 and DISK2

Check the permission of created disk under /dev/oracleasm/disks/

Now unzip the grid software and install using ./runInstaller

And select 'Install and Configure Grid Infrastructure for Standalone Server' as we are not using ASM for RAC database.

Here you can able see the disks created earlier. I have kept the redundancy 'External' so that only one disk is required for a disk group. For redundancy 'Normal' we need to have a two disks in a diskgroup and for 'High' we need a minimum for three disks. Select the disk which has to be added to diskgroup named 'DATA'.

Provide the password for SYS and ASMSNMP

 Select the OS group or keep as It is

 Click Finish to begin the installation

Once the installation is completed you can check whether ASM processes are running

Now below are the steps to move Non ASM DB to ASM disks

SQL> select name from v$database;

NAME
---------
DEV

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DEV/users01.dbf
/u01/app/oracle/oradata/DEV/undotbs01.dbf
/u01/app/oracle/oradata/DEV/sysaux01.dbf
/u01/app/oracle/oradata/DEV/system01.dbf
/u01/app/oracle/oradata/DEV/example01.dbf

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DEV/temp01.dbf


SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfileDEV.ora


SQL> show parameter control_file;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oracle/oradata/DEV/co
                                                 ntrol01.ctl, /u01/app/oracle/f
                                                 lash_recovery_area/DEV/control
                                                 02.ctl

SQL> alter system set control_files='+DATA' scope=spfile;

System altered.

SQL> alter system set db_create_file_dest='+DATA' scope=spfile;
 
System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2213896 bytes
Variable Size             956303352 bytes
Database Buffers          687865856 bytes
Redo Buffers                7135232 bytes

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


[oracle@dev ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu May 14 11:05:40 2015

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

connected to target database: DEV (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/oradata/DEV/control01.ctl';

Starting restore at 14-MAY-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/dev/controlfile/current.257.879678343
Finished restore at 14-MAY-15

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> backup as copy database format '+DATA'
2> ;

Starting backup at 14-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/DEV/system01.dbf
output file name=+DATA/dev/datafile/system.258.879679631 tag=TAG20150514T112711 RECID=2 STAMP=879679658
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/DEV/sysaux01.dbf
output file name=+DATA/dev/datafile/sysaux.259.879679667 tag=TAG20150514T112711 RECID=3 STAMP=879679687
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/DEV/undotbs01.dbf
output file name=+DATA/dev/datafile/undotbs1.260.879679691 tag=TAG20150514T112711 RECID=4 STAMP=879679697
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/DEV/example01.dbf
output file name=+DATA/dev/datafile/example.261.879679699 tag=TAG20150514T112711 RECID=5 STAMP=879679703
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/dev/controlfile/backup.262.879679707 tag=TAG20150514T112711 RECID=6 STAMP=879679707
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/DEV/users01.dbf
output file name=+DATA/dev/datafile/users.263.879679709 tag=TAG20150514T112711 RECID=7 STAMP=879679709
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 14-MAY-15
channel ORA_DISK_1: finished piece 1 at 14-MAY-15
piece handle=+DATA/dev/backupset/2015_05_14/nnsnf0_tag20150514t112711_0.264.879679711 tag=TAG20150514T112711 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-MAY-15

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/dev/datafile/system.258.879679631"
datafile 2 switched to datafile copy "+DATA/dev/datafile/sysaux.259.879679667"
datafile 3 switched to datafile copy "+DATA/dev/datafile/undotbs1.260.879679691"
datafile 4 switched to datafile copy "+DATA/dev/datafile/users.263.879679709"
datafile 5 switched to datafile copy "+DATA/dev/datafile/example.261.879679699"

RMAN> alter database open;

database opened

RMAN> Exit

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/dev/datafile/users.263.879679709
+DATA/dev/datafile/undotbs1.260.879679691
+DATA/dev/datafile/sysaux.259.879679667
+DATA/dev/datafile/system.258.879679631
+DATA/dev/datafile/example.261.879679699

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DEV/temp01.dbf

SQL> alter database tempfile '/u01/app/oracle/oradata/DEV/temp01.dbf' drop including datafiles;

Database altered.

SQL> select file_name from dba_temp_files;

no rows selected

SQL> alter tablespace temp add tempfile size 300M;

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/dev/tempfile/temp.265.879682295

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DEV/redo03.log
/u01/app/oracle/oradata/DEV/redo02.log
/u01/app/oracle/oradata/DEV/redo01.log


SQL> alter database add logfile group 4 '+DATA' size 10M;

Database altered.

SQL> alter database add logfile group 5 '+DATA' size 10M;

Database altered.

SQL> alter database add logfile group 6 '+DATA' size 10M;

Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DEV/redo03.log
/u01/app/oracle/oradata/DEV/redo02.log
/u01/app/oracle/oradata/DEV/redo01.log
+DATA/dev/onlinelog/group_4.266.879682579
+DATA/dev/onlinelog/group_5.267.879682595
+DATA/dev/onlinelog/group_6.268.879682607

6 rows selected.


SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DEV/redo01.log
+DATA/dev/onlinelog/group_4.266.879682579
+DATA/dev/onlinelog/group_5.267.879682595
+DATA/dev/onlinelog/group_6.268.879682607

SQL>  

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         4 ACTIVE
         5 CURRENT
         6 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         4 ACTIVE
         5 ACTIVE
         6 CURRENT


SQL> alter system checkpoint;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         4 CURRENT
         5 INACTIVE
         6 INACTIVE

SQL> alter database drop logfile group 1;

Database altered.

SQL> 

Database Switchover and Failover on DR

Switchover

Convert primary database to standby

Ensure that the last redo data transmitted from the primary database was applied on the standby database

SQL>select sequence#,applied from v$archived_log;

Check whether the primary is ready for switch.

SQL>select switchover_status from v$database;

If this query returns "TO STANDBY", then the environment is ready to switch. If it returns "SESSIONS ACTIVE", then the switch command should be used with the 'session shutdown' option.

SQL>connect / as sysdba;
SQL>alter database commit to switchover to physical standby with session shutdown;

Shutdown and mount primary database for standby database

SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Verify the switchover status on new standby database

SQL>select switchover_status from v$database;

Above Query should return a value 'TO PRIMARY'

Check the Database role after switch

SQL>select database_role from v$database;

 
On the original standby database issue the following commands.

Convert standby database to primary

SQL>connect / as sysdba;
SQL>alter database commit to switchover to primary with session shutdown;

Shutdown and Startup old standby database as primary

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;

Check the Database role after switch

SQL>select database_role from v$database;

Failover

If the primary database is not available the standby database can be activated as a primary database using the following statements.

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
SQL>ALTER DATABASE ACTIVATE STANDBY DATABASE;

Compile Invalid Objects using UTLRP.SQL and UTLIRP.SQL in R12

UTLIRP.SQL

This is a SQL script which Invalidates PL/SQL modules (procedures, functions, packages, types, triggers, views in a database). This script expects standard.sql, dbmsstdx.sql files. 

There should be no other DDL on the database while running the script.

For better performance set following parameter

set  _disable_fast_validate=TRUE in init.ora file 

connected AS SYSDBA

start database in UPGRADE mode 

SQL>STARTUP UPGRADE; or SQL>ALTER DATABASE OPEN UPGRADE;

Run script utlirp.sql 

SQL>@<ORACLE_HOME>/rdbms/admin/utlirp.sql

UTLRP.SQL

This script recompiles all existing invalid PL/SQL modules in a database. this invokes utlrcmp.sql

There should be no other DDL on the database while running the script. 

Connected AS SYSDBA

Shut down the database and restart in normal mode 

Run script utlrp.sql 

SQL>@<ORACLE_HOME>/rdbms/admin/utlrp.sql

Setup Printers for Cheque printing on Oracle EBS R12

For Printer setup first need to setup from OS level and then setup from application

Setup the printer at the OS level

First we need to check the status for the Linux/UNIX based CUPS print server

[root@ etc /]# /etc/init.d/cups status

Then add the new printer to the LINUX environment, login to linux CUPS using following URL

http://localhost:631/admin

Click on Add Printer button and add printer details

Click Continue button and select Device from drop down list for network printer select Apps Socket

Enter IP for network printer 

Select printer model and type

And press continue. Now Printer is added from OS

Setup through Application

Navigation -  System Administrator -> Install -> Printer -> Register
Define a new printer by entering the Printer Name and Type (Printer name must be same for both OS and application)

For Custom style we can define new style  Add The printer to 'printer' profile option
Navigation -  System Administrator -> Profile -> System 

Select the style as 'PDF Publisher' and printer name for concurrent program.
Navigation -  System Administrator -> Concurrent -> Program -> Define 
Find the the relevant cheque printing program

Bounce the Concurrent Manager to reflect the changes

When run the concurrent program select the no of copies need to print

RMAN Error : ORA-19625: error identifying file

Problem

RMAN> run{
2> allocate channel ch1 device type disk format '+DATA/RAC/BACKUP/%T_DB_%U';
3> backup database;
4> backup archivelog all format '+DATA/RAC/BACKUP/%T_Archive_%U';
5> backup current controlfile format '+DATA/RAC/BACKUP/%T_Control_%U';
6> }

using target database control file instead of recovery catalog
allocated channel: chq
channel chq: SID=53 instance=RAC2 device type=DISK

Starting backup at 06-MAR-15
current log archived
released channel: chq
RMAN-00571: ================================================
RMAN-00569: ========= ERROR MESSAGE STACK FOLLOWS ==========
RMAN-00571: ================================================
RMAN-03002: failure of backup command at 03/06/2015 17:57:28
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /u01/archive/1_7_873551068.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Recovery Manager complete.

Solution

Delete expired archivelog those are not in the DB

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 instance=RAC2 device type=DISK
validation failed for archived log
archived log file name=/u01/archive/1_7_873551068.dbf RECID=3 STAMP=873556504
validation failed for archived log
archived log file name=/u01/archive/1_8_873551068.dbf RECID=2 STAMP=873556504
validation failed for archived log
archived log file name=/u01/archive/1_9_873551068.dbf RECID=4 STAMP=873557382
validation failed for archived log
archived log file name=/u01/archive/1_10_873551068.dbf RECID=5 STAMP=873557389
validation failed for archived log
validation succeeded for archived log
archived log file name=+DATA/rac/archivelog/1_14_873551068.dbf RECID=15 STAMP=873567711
validation succeeded for archived log
archived log file name=+DATA/rac/archivelog/1_15_873551068.dbf RECID=19 STAMP=873567781
validation succeeded for archived log
archived log file name=+DATA/rac/archivelog/1_16_873551068.dbf RECID=21 STAMP=873615567
Crosschecked 22 objects

RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 instance=RAC2 device type=DISK
List of Archived Log Copies for database with db_unique_name RAC
=========================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
3       1    7       X 05-MAR-15
        Name: /u01/archive/1_7_873551068.dbf

2       1    8       X 05-MAR-15
        Name: /u01/archive/1_8_873551068.dbf

4       1    9       X 05-MAR-15
        Name: /u01/archive/1_9_873551068.dbf

5       1    10      X 05-MAR-15
        Name: /u01/archive/1_10_873551068.dbf


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/u01/archive/1_7_873551068.dbf RECID=3 STAMP=873556504
deleted archived log
archived log file name=/u01/archive/1_8_873551068.dbf RECID=2 STAMP=873556504
deleted archived log
archived log file name=/u01/archive/1_9_873551068.dbf RECID=4 STAMP=873557382
deleted archived log
archived log file name=/u01/archive/1_10_873551068.dbf RECID=5 STAMP=873557389
deleted archived log
Deleted 11 EXPIRED objects

After delete expired backup, re-try the backup then it will successful 

RMAN> run{
2> allocate channel ch1 device type disk format '+DATA/RAC/BACKUP/%T_DB_%U';
3> backup database;
4> backup archivelog all format '+DATA/RAC/BACKUP/%T_Archive_%U';
5> backup current controlfile format '+DATA/RAC/BACKUP/%T_Control_%U';
6> }

allocated channel: ch1
channel ch1: SID=53 instance=RAC2 device type=DISK

Starting backup at 06-MAR-15
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/rac/datafile/system.256.873550825
input datafile file number=00002 name=+DATA/rac/datafile/sysaux.257.873550829
input datafile file number=00003
name=+DATA/rac/datafile/undotbs1.258.873550831
input datafile file number=00005 name=+DATA/rac/datafile/example.264.873551129
input datafile file number=00006
name=+DATA/rac/datafile/undotbs2.265.873551485
input datafile file number=00004 name=+DATA/rac/datafile/users.259.873550831
channel ch1: starting piece 1 at 06-MAR-15
channel ch1: finished piece 1 at 06-MAR-15
piece handle=+DATA/rac/backup/20150306_db_0dq15sbj_1_1 tag=TAG20150306T182451
comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:55
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 06-MAR-15
channel ch1: finished piece 1 at 06-MAR-15
piece handle=+DATA/rac/backup/20150306_db_0eq15sf6_1_1 tag=TAG20150306T182451
comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-MAR-15

Starting backup at 06-MAR-15
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=2 sequence=1 RECID=1 STAMP=873555316
input archived log thread=2 sequence=6 RECID=13 STAMP=873567601
input archived log thread=1 sequence=14 RECID=15 STAMP=873567711
input archived log thread=2 sequence=7 RECID=14 STAMP=873567610
input archived log thread=2 sequence=8 RECID=16 STAMP=873567733
input archived log thread=1 sequence=15 RECID=19 STAMP=873567781
input archived log thread=2 sequence=9 RECID=17 STAMP=873567774
input archived log thread=2 sequence=10 RECID=18 STAMP=873567777
input archived log thread=2 sequence=11 RECID=20 STAMP=873568822
input archived log thread=1 sequence=16 RECID=21 STAMP=873615567
channel ch1: starting piece 1 at 06-MAR-15
channel ch1: finished piece 1 at 06-MAR-15
piece handle=+DATA/rac/backup/20150306_archive_0fq15sfj_1_1
tag=TAG20150306T182658 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:07
Finished backup at 06-MAR-15

Starting backup at 06-MAR-15
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 06-MAR-15
channel ch1: finished piece 1 at 06-MAR-15
piece handle=+DATA/rac/backup/20150306_control_0gq15sfs_1_1
tag=TAG20150306T182708 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:07
Finished backup at 06-MAR-15
released channel: ch1

Enable/Disable Archivelog mode in oracle RAC DB

Enable Archivelog Mode

1. Connect to DB from one node

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 27 20:16:10 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

2. Check the archivelog mode

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence     8
Current log sequence           9

3. Disable the RAC

SQL> alter system set cluster_database=false scope=spfile sid='RAC1';
System altered.

4. Shutdown the All RAC instances

[oracle@ol5-112-rac1 ~]$ srvctl stop database -d RAC

5. Connect to DB 

[oracle@ol5-112-rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 27 20:18:19 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.

6. Mount the DB

SQL> startup mount;
ORACLE instance started.
Total System Global Area 1023004672 bytes
Fixed Size                  2219752 bytes
Variable Size             721420568 bytes
Database Buffers          293601280 bytes
Redo Buffers                5763072 bytes
Database mounted.

7. Enable Archivelog mode

SQL> alter database archivelog;
Database altered.

8. Enable the RAC

SQL> alter system set cluster_database=true scope=spfile sid='RAC1';
System altered.

9. Shutdown the DB

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

10. Start all the RAC instances

[oracle@ol5-112-rac1 ~]$ srvctl start database -d RAC

Disable Archivelog Mode

1. Connect to DB

[oracle@ol5-112-rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 27 20:28:41 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

2. Check the archivelog mode

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence     8
Next log sequence to archive   9
Current log sequence           9

3. Disable the RAC

SQL> alter system set cluster_database=false scope=spfile sid='RAC1';
System altered.

4. Shutdown the All RAC instances

[oracle@ol5-112-rac1 ~]$ srvctl stop database -d RAC

5. Connect to DB

[oracle@ol5-112-rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 27 20:11:10 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.

6. Mount the DB

SQL> startup mount;
ORACLE instance started.
Total System Global Area 1023004672 bytes
Fixed Size                  2219752 bytes
Variable Size             721420568 bytes
Database Buffers          293601280 bytes
Redo Buffers                5763072 bytes
Database mounted.

7. Disable Archivelog mode

SQL> alter database noarchivelog;
Database altered.

8. Enable the RAC

SQL> alter system set cluster_database=true scope=spfile sid='RAC1';
System altered.

9. Shutdown the DB

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

10. Start the All RAC instances

[oracle@ol5-112-rac1 ~]$ srvctl start database -d RAC

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