Soumya's Database Blog : December 2015

Maintaining Online Redo Log Files on oracle 11g

Oracle Version: 11g

SQL> set linesize 250
SQL> select * from V$LOG;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 19 52428800 512 1 YES INACTIVE 1315147 25-DEC-15 1350603 26-DEC-15
2 1 20 52428800 512 1 YES ACTIVE 1350603 26-DEC-15 1359188 26-DEC-15
3 1 21 52428800 512 1 NO CURRENT 1359188 26-DEC-15 2.8147E+14

SQL> select * from V$LOGFILE;

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------- ----------------------------------------
3 ONLINE /u01/app/oracle/oradata/prim/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/prim/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/prim/redo01.log NO

Adding Online Redo Log File Groups.

SQL> ALTER DATABASE ADD LOGFILE GROUP 5 '/u01/app/oracle/oradata/prim/redo05.log' size 50M;

Adding Online Redo Log File Members.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/prim/redo1a.log' TO GROUP 1, '/u01/app/oracle/oradata/prim/redo2a.log' TO GROUP 2 ,
'/u01/app/oracle/oradata/prim/redo3a.log' to GROUP 3 ;

SQL> select * from V$LOGFILE;

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------- ----------------------------------------
3 ONLINE /u01/app/oracle/oradata/prim/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/prim/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/prim/redo01.log NO
1 INVALID ONLINE /u01/app/oracle/oradata/prim/redo1a.log NO
2 INVALID ONLINE /u01/app/oracle/oradata/prim/redo2a.log NO
3 INVALID ONLINE /u01/app/oracle/oradata/prim/redo3a.log NO

Dropping Online Redo Log File Groups.
First of all ORACLE will never allow you to drop the current ONLINE redolog file
SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

GROUP# ARC STATUS
---------- --- ----------------
1 NO CURRENT
2 YES INACTIVE
3 YES INACTIVE

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance prim (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/prim/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/prim/redo01a.log'

We can drop the redolog groups with STATUS='INACTIVE' . To do so do a log switch which change the status of the redo log group.
SQL> alter system switch logfile;

System altered.

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

GROUP# ARC STATUS
---------- --- ----------------
1 YES INACTIVE
2 NO CURRENT
3 YES INACTIVE

SQL> alter database drop logfile group 1;

Database altered.

CURRENT:- Oracle Database uses only one redo log files at a time to store redo records written from the redo log buffer. The redo log file that LGWR is actively writing to is
called the current redo log file.
ACTIVE:- Redo log files that are required for instance recovery are called active redo log files
INACTIVE :-Redo log files that are no longer required for instance recovery are called inactive redo log files.
UNUSED:-Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
CLEARING:- Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
CLEARING_CURRENT:- Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.

Dropping Online Redo Log File Members.
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/prim/redo3a.log';

Database altered.

Verifying Blocks in Redo Log Files.
SQL> show parameter DB_BLOCK_CHECKSUM;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_checksum string TYPICAL
SQL> alter system set DB_BLOCK_CHECKSUM=true ;

System altered.

SQL> show parameter DB_BLOCK_CHECKSUM;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_checksum string TRUE

Clearing a Redo Log File.
A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the
ALTER DATABASE CLEAR LOGFILE statement can be used to reinitialize the file without shutting down the database.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

Database altered.

This statement overcomes two situations where dropping redo logs is not possible:

1.If there are only two log groups
2.The corrupt redo log file belongs to the current group

If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
Database altered.

How to take RMAN Full backup using Shell script

Create the following backup path:-
[oracle@server1 ~]$ mkdir -p /u01/backups/rman_backup/full_backup
[oracle@server1 ~]$ mkdir -p /u01/backups/scripts/

Make sure database is in archivelog mode , if its not put it on archivelog mode.

SQL> sqlplus / as sysdba
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

[oracle@server1 ~]$ vi /u01/backups/scripts/full_backup.sh

#!/bin/bash
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
export path1=/u01/backups/rman_backup/full_backup
date1=`date +%d%m%y_%H%M%S`
mkdir $path1/$date1
chown oracle:oinstall -R $path1/$date1
$ORACLE_HOME/bin/rman target / <<eof
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
report obsolete;
delete Noprompt obsolete;
configure CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$path1/$date1/control_%d_%F';
run
{
backup incremental level 0 database FORMAT '$path1/$date1/full_%d_%T_%t_%s_%p';
backup archivelog all FORMAT '$path1/$date1/archive_%d_%T_%t_%s_%p' ;
}
eof

cd $path1
file1=`ls -ltrh | tail -1 | awk '{print $9}'`
tar -zcvf $file1.tar.gz $file1

:wq (save & exit)

Now schedule the script using crontab from oracle user:-
#The script will run everynight at 12 A.M

[oracle@server1 ~]$ crontab -e
0 0 * * * /u01/backups/scripts/full_backup.sh > /dev/null

How to Install Mysql 5.7 on Centos 6.X/7.X

Step 1: Save the Repo File for The MySql 5.7 or Install the RPM for Mysql 5.7

[root@server1 ~]# vi /etc/yum.repos.d/mysql-community.repo

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

--- save n quit (:wq) ---

or,

For RHEL/CentOS 7
[root@server1 ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
[root@server1 ~]# yum localinstall mysql57-community-release-el7-7.noarch.rpm

For RHEL/CentOS 6
[root@server1 ~]# wget http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm
[root@server1 ~]# yum localinstall mysql57-community-release-el6-7.noarch.rpm

Step 2: Install Mysql 5.7

[root@server1 ~]# yum install mysql-community-server -y

Step 3. Start mysql.
[root@server1 ~]# service mysqld start
Starting mysqld: [ OK ]

Step 4: Reset the Mysql Temporary root Password
[root@server1 ~]# cat /var/log/mysqld.log |grep "temporary password"
2015-12-20T15:11:54.175060Z 1 [Note] A temporary password is generated for root@localhost: eZWxCsme5V+q

# mysql_secure_installation

Enter password for user root: <Enter the Temporary Password>

The existing password for the user account root has expired. Please set a new password.

New password: <New Password>

Re-enter new password: <Retype the Password>
****************
p.s. If you get an error like this
"Re-enter new password:
... Failed! Error: Your password does not satisfy the current policy requirements"

Add the following parameter in my.cnf file under [mysqld] section.
[root@server1 ~]# vi /etc/my.cnf
validate_password_policy=LOW

:wq

The error is caused by default password policy of mysql. Here we changed its policy to low to bypass the error.
****************
Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.

- Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

Step 4: Check the Version and Mysql Once

# mysql -u root -p<password>

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)

mysql> \q
Bye

[root@server1 ~]# mysql --version
mysql Ver 14.14 Distrib 5.7.10, for Linux (x86_64) using EditLine wrapper

------ DONE, Thanks --------

How to findout Basic information about SGA

Oracle Version :- 11g

SQL> select value from v$parameter where upper(name)='MEMORY_TARGET';

VALUE
--------------------------------------------------------------------------------
415236096

SQL> select value/1024/1024 mb
2 from v$parameter where upper(name) = 'SGA_MAX_SIZE';

MB
----------
396

-- size taken by a memory component
SQL> select pool, round(sum(BYTES)/1024/1024) MB
2 from V$SGASTAT
3 group by pool;

POOL MB
------------ ----------
98
java pool 4
streams pool 4
shared pool 148
large pool 4

SQL> select name , value/1024/1024 MB
2 from v$parameter
3 where upper(name) in (
4 'DB_CACHE_SIZE','DB_RECYCLE_CACHE_SIZE','DB_KEEP_CACHE_SIZE', '
5 DB_2k_CACHE_SIZE', 'DB_8k_CACHE_SIZE', 'DB_16k_CACHE_SIZE');

NAME MB
------------------------------ ----------
db_cache_size 0
db_keep_cache_size 0
db_recycle_cache_size 0

--For 10g
SQL> SELECT COMPONENT , CURRENT_SIZE/1024/1024 MB
2 FROM V$SGA_DYNAMIC_COMPONENTS
3 WHERE CURRENT_SIZE <>0;

COMPONENT MB
---------------------------------------------------------------- ----------
shared pool 148
large pool 4
java pool 4
streams pool 4
DEFAULT buffer cache 92

-- Oracle 11g
SQL> SELECT COMPONENT, ROUND(CURRENT_SIZE/1024/1024) CURRENT_SIZE ,
2 ROUND(MIN_SIZE/1024/1024) MIN, ROUND(MAX_SIZE/1024/1024) MAX
3 FROM V$MEMORY_DYNAMIC_COMPONENTS;

COMPONENT CURRENT_SIZE MIN MAX
---------------------------------------------------------------- ------------ ---------- ----------
shared pool 148 148 148
large pool 4 4 4
java pool 4 4 4
streams pool 4 4 4
SGA Target 260 260 260
DEFAULT buffer cache 92 92 92
KEEP buffer cache 0 0 0
RECYCLE buffer cache 0 0 0
DEFAULT 2K buffer cache 0 0 0
DEFAULT 4K buffer cache 0 0 0
DEFAULT 8K buffer cache 0 0 0

COMPONENT CURRENT_SIZE MIN MAX
---------------------------------------------------------------- ------------ ---------- ----------
DEFAULT 16K buffer cache 0 0 0
DEFAULT 32K buffer cache 0 0 0
Shared IO Pool 0 0 0
PGA Target 136 136 136
ASM Buffer Cache 0 0 0

-- To findout how Oracle has modified the memory area sizes by time

SQL> select START_TIME, END_TIME, STATUS, COMPONENT, OPER_TYPE, OPER_MODE,
PARAMETER, INITIAL_SIZE/1024/1024 INITIAL_SIZE_MB,
TARGET_SIZE/1024/1024 TARGET_SIZE_MB, FINAL_SIZE/1024/1024 FINAL_SIZE_MB
from V$MEMORY_RESIZE_OPS
order by START_TIME, END_TIME ;

START_TIM END_TIME STATUS COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE_MB TARGET_SIZE_MB FINAL_SIZE_MB
--------- --------- --------- ------------------------- ------------- --------- ---------------------- --------------- -------------- -------------
17-DEC-15 17-DEC-15 COMPLETE shared pool STATIC shared_pool_size 0 148 148
17-DEC-15 17-DEC-15 COMPLETE PGA Target STATIC pga_aggregate_target 0 136 136
17-DEC-15 17-DEC-15 COMPLETE java pool STATIC java_pool_size 0 4 4
17-DEC-15 17-DEC-15 COMPLETE streams pool STATIC streams_pool_size 0 4 4
17-DEC-15 17-DEC-15 COMPLETE SGA Target STATIC sga_target 0 260 260
17-DEC-15 17-DEC-15 COMPLETE large pool STATIC large_pool_size 0 4 4
17-DEC-15 17-DEC-15 COMPLETE ASM Buffer Cache STATIC db_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE DEFAULT buffer cache STATIC db_cache_size 0 92 92
17-DEC-15 17-DEC-15 COMPLETE DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0

START_TIM END_TIME STATUS COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE_MB TARGET_SIZE_MB FINAL_SIZE_MB
--------- --------- --------- ------------------------- ------------- --------- ---------------------- --------------- -------------- -------------
17-DEC-15 17-DEC-15 COMPLETE DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE KEEP buffer cache STATIC db_keep_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE RECYCLE buffer cache STATIC db_recycle_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE DEFAULT buffer cache INITIALIZING db_cache_size 92 92 92

16 rows selected.

How to obtain Oracle License Information on 11g

-- Number of users and CPU/Processors
SQL> select L.SESSIONS_MAX, L.SESSIONS_WARNING, L.SESSIONS_CURRENT,
2 L.SESSIONS_HIGHWATER,
3 L.USERS_MAX, L.CPU_COUNT_CURRENT, L.CPU_SOCKET_COUNT_CURRENT,
4 L.CPU_COUNT_HIGHWATER,
5 L.CPU_CORE_COUNT_CURRENT, L.CPU_CORE_COUNT_HIGHWATER,
6 L.CPU_SOCKET_COUNT_HIGHWATER
7 from v$license l;
SQL> set linesize 300
SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER USERS_MAX CPU_COUNT_CURRENT CPU_SOCKET_COUNT_CURRENT CPU_COUNT_HIGHWATER CPU_CORE_COUNT_CURRENT CPU_CORE_COUNT_HIGHWATER CPU_SOCKET_COUNT_HIGHWATER
------------ ---------------- ---------------- ------------------ ---------- ----------------- ------------------------ ------------------- ---------------------- ------------------------ --------------------------
0 0 3 12 0 1 1

-- Database Edition
SQL> select banner from v$version where BANNER like '%Edition%';
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

-- Oracle Partitioning installed
SQL> select decode(count(*), 0, 'No', 'Yes')
2 from dba_part_tables
3 where owner not in ('SYSMAN', 'SH', 'SYS', 'SYSTEM') and rownum = 1;

DEC
---
No

-- Oracle Spatial installed:
select decode(count(*), 0, 'No', 'Yes')
from all_sdo_geom_metadata where rownum = 1;

DEC
---
No

-- Oracle RAC installed:
SQL> select decode(count(*), 0, 'No', 'Yes')
2 from v$active_instances where rownum <= 2;

DEC
---
No

What is the difference between a physical and logical standby database ?

Q. What is the difference between a physical and logical standby database ?

Ans. 1.A physical standby database is a block-for-block identical copy of the primary database because it is kept in sync with the primary database by using media recovery to apply redo
that was generated on the primary database.

Instead a logical standby database is kept in sync with the primary database using the SQL Apply engine. SQL Apply Engine transforms redo data received from the primary into logical
SQL statements and then executes those SQL statements against the standby database: so a logical standby database has the same logical information but a different physical structure.

2.Physical standby schema matches exactly the source database.
Logical standby database does not have to match the schema structure of the source database.

3.Archived redo logs are transferred directly to the standby database which is always running in "recover" mode. Upon arrival, the archived redo logs are applied directly to the
standby database.
Logical standby uses LogMiner techniques to transform the archived redo logs into native DML statements (insert, update, delete). This DML is transported and applied to the standby
database.

4.Installing Physical standbys offers these benefits:

An identical physical copy of the primary database

Disaster recovery and high availability

High Data protection

Reduction in primary database workload

Performance Faster

Installing Logical standbys offer:

Simultaneous use for reporting, summations and queries

Efficient use of standby hardware resources

Reduction in primary database workload

It's important to remember that a logical standby does not support all Oracle datatypes.
You can run this query
select distinct owner, table_name
from dba_logstdby_unsupported
order by owner;
to see if you are using unsupported objects.
So before setting up a logical standby database, ensure the logical standby database can maintain the data types and tables in your primary database, otherwise your only and best choice is to setup a physical standby database.

Reference:-http://www.dba-oracle.com/t_difference_logical_physical_standby_database.htm

Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

How to find out a particular tablesize in mysql

mysql> SELECT
-> CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',SUBSTR(units,pw1*2+1,2)) DATSIZE,
-> CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',SUBSTR(units,pw2*2+1,2)) NDXSIZE,
-> CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',SUBSTR(units,pw3*2+1,2)) TBLSIZE
-> FROM
-> (
-> SELECT DAT,NDX,TBL,IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
-> FROM
-> (
-> SELECT data_length DAT,index_length NDX,data_length+index_length TBL,
-> FLOOR(LOG(IF(data_length=0,1,data_length))/LOG(1024)) px,
-> FLOOR(LOG(IF(index_length=0,1,index_length))/LOG(1024)) py,
-> FLOOR(LOG(data_length+index_length)/LOG(1024)) pz
-> FROM information_schema.tables
-> WHERE table_schema='cyclexaftest'
-> AND table_name='log_url'
-> ) AA
-> ) A,(SELECT 'B KBMBGBTB' units) B;
+---------+---------+----------+
| DATSIZE | NDXSIZE | TBLSIZE |
+---------+---------+----------+
| 5.52 MB | 6.03 MB | 11.55 MB |
+---------+---------+----------+
1 row in set (0.00 sec)

-----------------------------------------------------
Another query:-

mysql> SELECT table_name AS `Table`,
-> round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
-> FROM information_schema.TABLES
-> WHERE table_schema = "cyclexaftest"
-> AND table_name = "log_url";
+---------+------------+
| Table | Size in MB |
+---------+------------+
| log_url | 11.55 |
+---------+------------+
1 row in set (0.00 sec)

How To find out largest 10 tables in Mysql database

mysql> SELECT CONCAT(table_schema, '.', table_name),
-> CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,
-> CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
-> CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,
-> CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
-> ROUND(index_length / data_length, 2) idxfrac
-> FROM information_schema.TABLES
-> ORDER BY data_length + index_length DESC
-> LIMIT 10;

+-------------------------------------------+-------+-------+-------+------------+---------+
| CONCAT(table_schema, '.', table_name) | rows | DATA | idx | total_size | idxfrac |
+-------------------------------------------+-------+-------+-------+------------+---------+
| test.sg_postmeta | 0.38M | 0.03G | 0.02G | 0.05G | 0.62 |
| primary_prod.log_url_info | 0.34M | 0.04G | 0.00G | 0.04G | 0.00 |
| primary_prod.log_visitor_info | 0.24M | 0.04G | 0.00G | 0.04G | 0.00 |
| primary_prod.log_url | 0.33M | 0.01G | 0.01G | 0.03G | 0.97 |
| primary_prod.log_visitor | 0.26M | 0.02G | 0.00G | 0.02G | 0.00 |
| test.sg_posts | 0.05M | 0.01G | 0.01G | 0.01G | 0.81 |
| primary_prod.report_event | 0.05M | 0.00G | 0.01G | 0.01G | 3.01 |
| primary_prod.report_viewed_product_index | 0.03M | 0.00G | 0.01G | 0.01G | 2.88 |
| crm.vtiger_profile2field | 0.03M | 0.00G | 0.00G | 0.01G | 3.00 |
| crm.vtiger_role2picklist | 0.01M | 0.00G | 0.00G | 0.00G | 0.57 |
+-------------------------------------------+-------+-------+-------+------------+---------+
10 rows in set (1.07 sec)

Monitoring Data Guard Log Shipping using Shell Script with Mail alert

Version:- Oracle 11g
All the below scripts are stored and will run from oracle user & from Primary Database.

[oracle@server1 ~]$ vi /home/oracle/dg.sql
Set linesize 222
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(SELECT NAME DB_NAME FROM V$DATABASE),
(SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),(INSTR(HOST_NAME,'.')-1))))) HOSTNAME FROM V$INSTANCE
),
(SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' ),
(SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' ),
(SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES');
exit;

:wq

Now lets create the shell-script which will monitor the log gap in between of primary and standby database:-
From oracle user.
[oracle@server1 ~]$ vi /home/oracle/dgmonitor.sh

#!/bin/bash

. .bash_profile

sqlplus -S / as sysdba @/home/oracle/dg.sql > /home/oracle/loggap.txt

loggap=`cat /home/oracle/loggap.txt | awk '{print $6}'`

if [[ $loggap -gt 1 ]]; then

cat /home/oracle/loggap.txt|mailx -s "Attention!! There is a loggap between Primary and Standby Database at `date` BETWEEN CCUINE23 AND CCUINE103 DATABASE" support@xyz.com

rm -r /home/oracle/loggap.txt

exit 0

:wq

[oracle@server1 ~]$ chmod 777 dgmonitor.sh

Now schedule it on crontab to execute the script for every 15mins :-
[oracle@server1 ~]$ crontab -e
*/15 * * * * /home/oracle/dgmonitor.sh > /dev/null