http://www.expertDBA.com/oracle/articles/ASM_Hands_On_Training.pdf

Note: Above mentioned training document is copied/taken from the Oracle Blog http://blogs.oracle.com.

As a first step we need to install support ASM libraries to configure the ASM. This library is provided to enable ASM I/O to Linux disks without the limitations of the standard Unix I/O API.

The ASMLib software is available from the Oracle Technology Network at http://www.oracle.com/technology/tech/linux/asmlib/index.html

The oracleasmlib package provides the actual ASM library. The oracleasm-support package provides the utilities used to get the ASM driver up and running. Both of these packages need to be installed.

The remaining packages provide the kernel driver for the ASM library. Each package provides the driver for a different kernel. You must install the appropriate package for the kernel you are running. Use the “uname -r command to determine the version of the kernel. The oracleasm kerel driver package will have that version string in its name. For example, if you were running Red Hat Enterprise Linux 4 AS, and the kernel you were using was the 2.6.9-5.0.5.ELsmp kernel, you would choose the oracleasm-2.6.9-5.0.5-ELsmp package. So, to install these packages on RHEL4 on an Intel x86 machine, you might use the command:

[root@ssk ~]# rpm -Uvh oracleasm-support-2.1.3-1.el5.i386.rpm
[root@ssk ~]# rpm -Uvh oracleasm-2.6.18-53.el5PAE-2.0.4-1.el5.i686.rpm
[root@ssk ~]# rpm -Uvh oracleasmlib-2.0.4-1.el5.i386.rpm

 If you were on a different machine, the package names would be slightly different, replacing ‘i686′ with the appropriate architecture. Use the package names relevant for your distribution.

NOTE: Distributions with the Linux 2.4 kernel still use the 1.0 kernel driver, while distributions based on the Linux 2.6 kernel use the 2.0 kernel driver. All distributions use version 2.0 of the support and library packages. See the driver matrix for more information.

Once the above steps are completed, ASMLib is now installed on the system.

 Next step is to Configure the Oracle ASM library driver:

Run the /etc/init.d/oracleasm script with the ‘configure’ option. It will ask for the user and group that default to owning the ASM driver access point. If the database was running as the ‘oracle’ user and the ‘dba’ group, the output would look like this:

[root@ssk ~]# /etc/init.d/oracleasm configure

Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets (‘[]‘).  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort. 

Default user to own the driver interface [oracle]:
Default group to own the driver interface [dba]:
Start Oracle ASM library driver on boot (y/n) [y]:
Scan for Oracle ASM disks on boot (y/n) [y]:
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                               [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]

[root@ssk ~]#

This should load the oracleasm.o driver module and mount the ASM driver filesystem. By selecting enabled = ‘y’ during the configuration, the system will always load the module and mount the filesystem on boot.

NOTE: Reference: Oracle documentation: http://www.oracle.com/technology/tech/linux/asmlib/install.html

While configuring Oracle ASM if you are getting the following error:

Initializing the Oracle ASMLib driver:                                  [FAILED]

There could be two reasons:

1. RPM installed for ASMLIB are not correct: Check and reinstall the correct version.
   1. a.      Use the following links:
      http://www.oracle.com/technology/tech/linux/asmlib/install.html
      http://www.oracle.com/technology/tech/linux/asmlib/index.html
2. Other reason could be: — SELinux is turned on

SELinux would bring trouble for Oracle ASMLib, not only this, it played OCFS, OEM grid control, Oracle cluster service as well. Turn it off by disabling it. Oracle will not complain.

Solution:
Turn off SELinux or put it in permssive mode.

How-to:
1) Go to modify /etc/selinux/config in redhat or /boot/grub/grub.conf in other linux with GRUB boot loader. It looks like this:

# SELINUX= can take one of these three values:
#       enforcing – SELinux security policy is enforced.
#       permissive – SELinux prints warnings instead of enforcing.
#       disabled – SELinux is fully disabled.
SELINUX=enforcing —> change it to SELINUX=disabled
2) Reboot server to take it effect

You can set up it into permissive mode right away without reboot.

 Usage: setenforce [ Enforcing | Permissive | 1 | 0 ]

 The automatic start can be enabled or disabled with the ‘enable’ and ‘disable’ options to /etc/init.d/oracleasm:

[root@ssk ~]# /etc/init.d/oracleasm disable

[root@ssk ~]# /etc/init.d/oracleasm enable

[root@ssk ~]# /etc/init.d/oracleasm disable
Writing Oracle ASM library driver configuration: done
Dropping Oracle ASMLib disks:                                                   [  OK  ]
Shutting down the Oracle ASMLib driver:                              [  OK  ]

 [root@ssk ~]# /etc/init.d/oracleasm enable
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                                       [  OK  ]
Scanning the system for Oracle ASMLib disks:                       [  OK  ]

[root@ssk ~]#

 Next step is of making Disks Available to ASMLib

To create a Disk Group

[root@ssk ~]# /etc/init.d/oracleasm  createdisk VOL1 /dev/hdc11
Marking disk “/dev/hdc11″ as an ASM disk: [ OK ]
[root@ssk ~]# /etc/init.d/oracleasm  createdisk VOL1 /dev/hdc12
Marking disk “/dev/hdc12″ as an ASM disk: [ OK ]
[root@ssk ~]# /etc/init.d/oracleasm  createdisk VOL1 /dev/hdc13
Marking disk “/dev/hdc13″ as an ASM disk: [ OK ]

Note: If above commands fails then please make sure the disk that you are specifying is not already being used or not already mounted:

To Delete a Disk Group

[root@ssk ~]# /etc/init.d/oracleasm  deletedisk VOL1
Deleting Oracle ASM disk “VOL1″ [ OK ]

To query the disks

[root@ssk ~]# /etc/init.d/oracleasm querydisk /dev/hdc11
Device “/dev/hdc11″ is marked an ASM disk with the label “VOL1″

[root@ssk ~]# /etc/init.d/oracleasm querydisk /dev/hdc12
Device “/dev/hdc12″ is marked an ASM disk with the label “VOL2″

[root@ssk ~]# /etc/init.d/oracleasm querydisk /dev/hdc13
Device “/dev/hdc13″ is marked an ASM disk with the label “VOL3″

[root@ssk ~]# /etc/init.d/oracleasm querydisk /dev/hdc10
Device “/dev/hdc10″ is not marked as an ASM disk

 [root@ssk ~]# /etc/init.d/oracleasm querydisk VOL1
Disk “VOL1″ is a valid ASM disk

[root@ssk ~]# /etc/init.d/oracleasm querydisk VOL4
Disk “VOL4″ does not exist or is not instantiated

[root@ssk ~]#

To list and query the existing disks

[root@ssk ~]# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3

# /etc/init.d/oracleasm querydisk VOL1
Disk “VOL1″ is a valid ASM disk on device [8, 2]

 When a disk is added to a RAC setup, the other nodes need to be notified about it. Run the ‘createdisk’ command on one node, and then run ’scandisks’ on every other node:

  [root@ca-test1 /]# /etc/init.d/oracleasm scandisks
  Scanning system for ASM disks                              [  OK  ]
We are now done with installation and configuration of ASMLib on Linux platform.

 Creating New Oracle ASM Instance:

 Create the password file:

[oracle@ssk ~]$ orapwd file=$ORACLE_HOME/dbs/orapw+ASM password=changeIt entries=5

Create required directories:

[oracle@ssk ~]$ mkdir -p $ORACLE_BASE/admin/+ASM
[oracle@ssk ~]$ cd $ORACLE_BASE/admin/+ASM
[oracle@ssk +ASM]$ mkdir bdump
[oracle@ssk +ASM]$ mkdir udump
[oracle@ssk +ASM]$ mkdir cdump
[oracle@ssk +ASM]$ mkdir pfile
 

Create the init+ASM.ora file:

Using vi editor or any other editor you like, create the init+ASM.ora file under the $ORACLE_HOME/dbs
directory and add the below lines into this file.

asm_diskgroups=’DATA_GRP’
asm_diskstring=’ORCL:*’
background_dump_dest=’/u01/app/admin/+ASM/bdump’
core_dump_dest=’/u01/app/admin/+ASM/cdump’
instance_type=’asm’
large_pool_size=12M
remote_login_passwordfile=’SHARED’
user_dump_dest=’/u01/app/admin/+ASM/udump’

[oracle@ssk ~]$ cat $ORACLE_HOME/dbs/init+ASM.ora
asm_diskgroups=’PROD_DB_GRP’
asm_diskstring=’ORCL:*’
background_dump_dest=’/u01/app/admin/+ASM/bdump’
core_dump_dest=’/u01/app/admin/+ASM/cdump’
instance_type=’asm’
large_pool_size=12M
remote_login_passwordfile=’SHARED’
user_dump_dest=’/u01/app/admin/+ASM/udump’
[oracle@ssk ~]$

Create spfile+ASM.ora and start the instance using that file:

[oracle@ssk ~]$ export ORACLE_SID=+ASM
[oracle@ssk ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Sun Dec 4 21:17:35 2005

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

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup mount

ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1217836 bytes
Variable Size 57502420 bytes
ASM Cache 25165824 bytes
ORA-15032: not all alterations performed
ORA-15063: ASM discovered an insufficient number of disks for diskgroup
“DATA_GRP”

SQL> show parameter disk
 NAME                                 TYPE        VALUE
———————————— ———– ——————————
asm_diskgroups                     string      DATA_GRP
asm_diskstring                       string      ORCL:*
disk_asynch_io                       boolean     TRUE

SQL> create diskgroup data_grp
failgroup f1 disk ‘ORCL:DSK1′
failgroup f2 disk ‘ORCL:DSK2′;

 Diskgroup created.   

SQL> set linesize 100
SQL> col name format a15
SQL> col path format a15
SQL> select name, path from v$asm_disk where name is not null;

NAME            PATH
————— —————
DSK1            ORCL:DSK1
DSK2            ORCL:DSK2

SQL> select name, type, total_mb, free_mb from v$asm_diskgroup;

    NAME            TYPE     TOTAL_MB   FREE_MB
    ————— —— ———- ———-
    DATA_GRP        NORMAL      59999      59897

NDB_MGM=”ndb_mgm”
ndb_ps=`ps -ef|grep “ndb_mgmd”|grep -v grep |wc -l`
node_count=`$NDB_MGM -e “show” |grep -w “id”|wc -l`

echo ${Log}
> ${Log}

if [ $ndb_ps -ne 1 ] ; then
echo “Looks like NDB_MGMD is not running”
echo “check MANAGEMENT Node status”
exit 1
### need to work on this condition.
else
echo “MANAGEMENT Node is up, proceed with next step”
echo “checking individual node status”
$NDB_MGM -e “show”|grep -w “id”|awk ‘{print $1}’|cut -c 4- |while read node_id
do
# check to see ndbd_mgm daemon is running on $HOST and
# node are running
NODE_STATUS=`$NDB_MGM -e “$node_id status” | egrep -v ‘(Connected to Management Server|^$)’`
case $NODE_STATUS in
*started*)
## echo “for datanode if status is started then ndbd is up”
echo “OK – $NODE_STATUS” >>${Log}
;;
“Node $node_id: connected”*)
## echo “for MGMD & SQL if status is connected then things are fine”
echo “OK – $NODE_STATUS” >>${Log}
;;
*starting*)
echo “WARRING – $NODE_STATUS” >>${Log}
;;
*)
echo “ERROR – $NODE_STATUS” >>${Log}
;;
esac
# done with all nodes
done
fi ## closing if

cnt=`cat ${Log}|grep -vw “OK”|wc -l`
if [ $cnt -eq 0 ] ; then
echo “ALL NODE STATUS is FINE”
cat ${Log}
exit 0
else
echo “${Header.Start}”
echo “CHECK MYSQL-CLUSTER NODE STATUS”
$NDB_MGM -e “show”
echo “${Header.Stop}”
exit 1
fi

This script will restore all the objects of all the schema(including mysql schema) use filters for selective restore.

#!/bin/sh
#============================================================================
# File:         DV_MySQL_restore.sh
# Description:  restore individual table backup to each database.
# Usage:        sh DV_MySQL_restore.sh >DV_MySQL_restore.log 2>DV_MySQL_restore.err
#============================================================================

MYSQL=”/usr/local/mysql/bin/mysql”
BKP_DATE=$(date ‘+%u’)  ## current format of backup day/time
HOST=`hostname`
DNAME=${HOST}-${BKP_DATE}* ## for latest backupdir based on day.
MUSER=”xxxxxx”
MPASS=”xxxxxx”
SRC_BACKUP_DIR=”/mnt/backup/”$DNAME

##check if the backup dir exist or not..
if [ ! -d $SRC_BACKUP_DIR ]; then
echo Directory NOT found..
echo manually unzip the .tar.bz2 file to make sure we use the correct backupfile.
echo Plz check the target dir for backups.
exit
else
echo Directory found continue with backup
cd $SRC_BACKUP_DIR
fi
### directory found.Proceed with backup

cd $SRC_BACKUP_DIR
### Get all databases name from source host,Assuming we have access to mysql on source from target###
DATABASES=`dir |egrep -vw “information_schema|mysql”`
for db in $DATABASES
do
cd ${SRC_BACKUP_DIR}/${db} ### ON the assumtion that /mnt/backup is a SHARED MOUNT POINT i.e accessible from the TEST SERVER too
echo “starting restore of database — $db”
$MYSQL -u$MUSER -p$MPASS -e “CREATE DATABASE IF NOT EXISTS ${db}”
## database created proceed with each table restore.Get list of tables from source.
TABLES=`ls |grep -v $db”_routines_triggers.sql.gz”`
for table in $TABLES
do
cd $SRC_BACKUP_DIR/$db/
FILE=$table  ##use .gz for compressed files.
echo $table
gunzip < $FILE | $MYSQL -q -u$MUSER -p$MPASS -D ${db} #for .gz files. if compression is done.
###$MYSQL -q -u$MUSER -p$MPASS -D ${db} < $FILE  ## for each table with .sql file
done
## once table restore is done.proceed with routines_triggers restore in each database.
echo “starting restore of routines/triggers to database — $db”
cd ${SRC_BACKUP_DIR}/${db}/
ROUTINE=${db}_routines_triggers.sql.gz #for .gz files. if compression is done.
###$MYSQL -q -u$MUSER -p$MPASS -D ${db} < $ROUTINE  ## for routines/triggers in each db.
gunzip < $ROUTINE | $MYSQL -u$MUSER -p$MPASS -D ${db}
echo “completed restore of database — $db”
done
## restore of triggers/routines is done for each db.
## all database restore is completed.

### MySQL Setup ###
##BKP_DATE=$(date ‘+%m%d%Y’)
BKP_DATE=$(date ‘+%u%H’)  ## current format of backup day/time
HOST=`hostname`
MUSER=”xxxxx”
MPASS=”xxxxx”
BACKUP_DIR=”/mnt/backup/” #${HOST}-${BKP_DATE}
DNAME=${HOST}-${BKP_DATE}
MYSQLDUMP=”/usr/local/mysql/bin/mysqldump”
MYSQL=”/usr/local/mysql/bin/mysql”

cd $BACKUP_DIR
#mkdir ${HOST}-${BKP_DATE}
echo “starting backup job at `date ‘+%m%d%Y %H:%M %Z’` ”

### Get all databases name ###
###$MYSQL -u$MUSER -p$MPASS –skip-column-names -e “show databases” |grep -v “information_schema” |while read db
DATABASES=`$MYSQL -u$MUSER -p$MPASS –skip-column-names -e “show databases”`
for db in $DATABASES
do
### Create dir for each databases(DUE TO FILESYSTEM BUGS), backup tables in individual files ###
#echo ${BACKUP_DIR}
#echo ${BACKUP_DIR}/${db}
mkdir -p -m 777 ${BACKUP_DIR}/$DNAME
cd ${BACKUP_DIR}/$DNAME
mkdir -m 777 ${db}

#### directory creation is done.Proceed with backup
echo “  ”
echo “starting backup of database — $db”
echo “————————————-”
###$MYSQL -u $MUSER -p$MPASS $db -e “show tables” |grep -v “Tables_in_”  |while read table
TABLES=`$MYSQL -u $MUSER -p$MPASS $db -e “show tables” |grep -v “Tables_in_”`
for table in $TABLES
do
FILE=$BACKUP_DIR/$DNAME/$db/$table.sql.gz
echo $table;
## for schema,data/routines  make sure we backup mysql database also.
$MYSQLDUMP -q –skip-triggers –single-transaction -u $MUSER -p$MPASS $db $table | gzip > $FILE  ##dump only table schema and table data
done
## to backup routines/triggers for each db.
ROUTINE=$BACKUP_DIR/$DNAME/${db}/${db}_routines_triggers.sql.gz
$MYSQLDUMP -t -d -q –allow-keywords -R -u $MUSER -p$MPASS $db $table | gzip > $ROUTINE        ##triggers are enabled by default
echo “completed the backup of database — $db”
done

echo “##################################”
echo “backup job completed at `date ‘+%m%d%Y %H:%M %Z’` ”
echo “All database backup complted”
echo ” ”
echo “Deleting old backups”
rm -rf ${BACKUP_DIR}/${DNAME}.tar.bz2
echo “Compressing the backupfiles”
##create   bzipped   tar  archive  of  the  directory
cd ${BACKUP_DIR}
tar -cjf ${DNAME}.tar.bz2 ${DNAME}
##tar -cvf – <directory name> | gzip -c > <directory name>.tar.gz
echo “backup archiving is done.plz verify the files”
ls -lhtr ${BACKUP_DIR}/${DNAME}.tar.bz2
rm -rf ${BACKUP_DIR}/${DNAME}
## done with backup

Pre-req: Need to have maatkit checksum tool installed on the server.

create tables checksum and checksum_results

CREATE TABLE checksum (
     db         char(64)     NOT NULL,
     tbl        char(64)     NOT NULL,
     chunk      int          NOT NULL,
     boundaries char(100)    NOT NULL,
     this_crc   char(40)     NOT NULL,
     this_cnt   int          NOT NULL,
     master_crc char(40)         NULL,
     master_cnt int              NULL,
     ts         timestamp    NOT NULL,
     PRIMARY KEY (db, tbl, chunk)
  );

CREATE TABLE checksum_results (
     db         char(64)     NOT NULL,
     tbl        char(64)     NOT NULL,
     chunk      char(64)     NOT NULL,
     boundaries char(100)    NOT NULL,
     this_crc   char(40)     NOT NULL,
     this_cnt   int          NOT NULL,
     master_crc char(40)         NULL,
     master_cnt int              NULL,
     ts         timestamp    NOT NULL,
 );

Usage : checksum_run.sh <MastHost> <SlaveHost>  > checksum_run.log 2>checksum_run.err
#====================================================================================
##### Verify if mysql deamon is running #####
SRC=$1
TRG=$2
USER=’xxxxx’
PWD=’xxxx’
LogDir=/tmp
Log=${LogDir}/Checksum_test.log
MailList=”abc@xyz.com”
Hostname=`hostname`

> ${Log}

mysql_statuschk()
{
process=`ps -ef | egrep -iw ‘mysqld_safe’ | egrep -v egrep | wc -l`
myping=`mysqladmin -u$USER -p$PWD “ping”|egrep -iw ‘alive’|wc -l`
if [ $process -eq 0 ] || [ $myping -eq 0 ] ; then
echo “MySQL Process is Unavailable on ${Hostname}!” > ${Log}
echo “Verify if mysqld is running!” >> ${Log}
echo “Hostname:” $Hostname >> ${Log}
echo “Uptime:” `uptime` >> ${Log}
echo  >> ${Log}
ps -ef|egrep -iw “mysql” >> ${Log}
##### Verify MYSQLADMIN Status #####
echo “MySQL Admin status:” >> ${Log}
echo “——————-” >> ${Log}
mysqladmin -u$USER -p$PWD “ping” >> ${Log}
mysqladmin -u$USER -p$PWD “status” >> ${Log}
echo “If mysqladmin status is not alive! then mysql seems to be down.” >> ${Log}
mailx -s  “SEV1/${Hostname}/MySQL Process is Unavailable.” ${MailList} < ${Log}
return 1
else
## if mysql is up and running.
echo “MySQL is up and running.”
return 0
fi
}

maatkit_chksum()
{
###### MAATKIT RUNNING STATUS CHECK ######
Hostname=`hostname`
echo “Hostname:” $Hostname >> ${Log}
echo “Starting checksum for partitions at `date ‘+%m%d%Y %H:%M %Z’` ” >> ${Log}
MK_STAT=`ps -ef | grep “mk-table-checksum” | grep -v “grep” | wc -l`
seconds=`mysql -h$TRG -u$USER -p$PWD -e “show slave status\G”|egrep Seconds|awk {‘print $2′}`
if [ ${MK_STAT} -eq 0 ] || [ $seconds -eq 0 ]; then
####### MAATKIT CHECKSUM RUN #########
##make sure we truncate the table test.checksum before looping in for each partition.
mysql -u$USER -p$PWD -D test -e “truncate table test.checksum”
mysql -u$USER -p$PWD -D test -e “truncate table test.checksum_results”

## Now get the partition information to loop into.
PLIST=`mysql -u$USER -p$PWD –skip-column-names -e “select partition_name from information_schema.partitions where table_schema=’<dbname>’ and table_name=’<tblname> ‘ “|tr -d ‘p’`
for PART in $PLIST
do
### test for single partition without chunk
echo “Starting for partition — $PART”
echo
mk-table-checksum –replicate=test.checksum –databases=<dbname> –tables=<tblname> h=$SRC –where=”<colname> > date_format($PART,’%Y-%m-%d 00:00:00′) and <colname> < date_format($PART,’%Y-%m-%d 23:59:59′)”
##to insert partition cheksum result into new tables.
mysql -u$USER -p$PWD -D test -e “set @par=’p$PART’; insert into test.checksum_results (db, tbl, chunk, boundaries, this_cnt, this_crc,master_crc,master_cnt,ts) select db, tbl, @par, boundaries, this_cnt, this_crc,master_crc,master_cnt,ts from test.checksum where tbl=’<tblname>’”
done
echo “Completed  checksum for table paritions at `date ‘+%m%d%Y %H:%M %Z’` ” >> ${Log}
######### done with individual partition checksum.Now proceed with reports..
PRC=`ps -ef|grep “mk-table-checksum”|egrep -v “grep”|wc -l`
seconds2=`mysql -h$TRG -u$USER -p$PWD -e “show slave status\G”|egrep Seconds|awk {‘print $2′}`
### To ensure that checksum is replicating to slave after finishing in master ###
sleep 5
### checking for mk-table-checksum running status ###
while [ $PRC -gt 0 ] || [ ${seconds2} -gt 0 ] ;do
echo “The checksum script is still running!”
echo “(OR)”
echo “The slave is still lagging behind the master/replicating the checksum to slave”
echo “Lets wait until the process gets completed.”
sleep ${seconds2} ## sleep 900
seconds2=`mysql -h$TRG -u$USER -p$PWD -e “show slave status\G”|egrep Seconds|awk {‘print $2′}`
PRC=`ps -ef|grep “mk-table-checksum”|egrep -v “grep”|wc -l`
echo `ps -ef|grep “mk-table-checksum”|egrep -v “grep”`
done
##verify checksum results on slave
DIFFER=`mysql -t -h$TRG -u$USER -p$PWD -e “SELECT db, tbl, chunk, this_cnt-master_cnt AS cnt_diff,this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc) AS crc_diff FROM test.checksum_results WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)”|wc -l`
if [ $DIFFER -gt 0 ];then
echo “data discrepancy found b/w master and slave.”  >> ${Log}
mysql -t -h$TRG -u$USER -p$PWD -e “SELECT db, tbl, chunk, this_cnt-master_cnt AS cnt_diff,this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc) AS crc_diff FROM test.checksum_results WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)” >> ${Log}
echo “  ” >> ${Log}
echo “Checksum report” >> ${Log}
mysql -t -h$TRG -u$USER -p$PWD -e “select * from test.checksum_results” >> ${Log}
mailx -s “${Hostname}-Data discrepancy report b/w Master $SRC and slave $TRG.” ${MailList} < ${Log}

### NEW condition to report errors -Kris ##
else if [ $DIFFER -eq 0 ];then
echo “Data is consistent and in sync b/w master and slave.”  >> ${Log}
mysql -t -h$TRG -u$USER -p$PWD -e “select * from test.checksum_results” >> ${Log}
mailx -s “${Hostname}-Data consistency report b/w Master $SRC and slave $TRG.” ${MailList} < ${Log}
else
echo “Some issues with the checksum run” >> ${Log}
echo “Please check the table test.checksum_results for details” >> ${Log}
ps -ef |grep “checksum” >> ${Log}
echo “Please invistigate the issues” >> ${Log}
mailx -s “${Hostname} – Error!! found with checksum run.Plz Invistigate” ${MailList} < ${Log}
fi ##closing the else condition
fi ## closing for checksum report.
else
echo “The mk-table-checsum is already running” >>  ${Log}
echo “(OR)”  >> ${Log}
echo “The slave is $TRG lagging behind the master $SRC ” >>  ${Log}
mailx -s “${Hostname}-Table checksum is already running.” ${MailList} < ${Log}
exit 2
fi
}

### MAIN PROGRAM STARTS HERE ###
# If no parameters  passed then display script usage message
if [ "$1" = '' ] || [ "$2" = '' ]; then
echo “Usage: $0 <MasterHost> <SlaveHost>”
exit 1
fi

mysql_statuschk
#if [ $? -nt 1 ]
if [ $? -eq 0 ]
then
maatkit_chksum
fi

ACCEPT schema CHAR PROMPT “Enter username for remote schema: ”
ACCEPT passwd CHAR PROMPT “Enter password for remote schema: ” HIDE
ACCEPT tnssvc CHAR PROMPT “Enter SQL*Net / Net8 service for remote schema: ”
ACCEPT dblinkname CHAR PROMPT “Enter the dblink name to be created: ”
ACCEPT rem_schema CHAR PROMPT “Enter the dblink name: ”

PROMPT

ACCEPT report CHAR PROMPT “Enter filename for report output: ”

SET FEEDBACK OFF
SET VERIFY OFF

CREATE DATABASE LINK &dblinkname CONNECT TO &schema IDENTIFIED BY &passwd USING ‘&tnssvc’;

SET TRIMSPOOL ON

SPOOL &report

SELECT SUBSTR (RPAD (TO_CHAR (SYSDATE, ‘mm/dd/yyyy hh24:mi:ss’), 25), 1, 25)
“REPORT DATE AND TIME”
FROM SYS.dual;

COL local_schema FORMAT a35 TRUNC HEADING “LOCAL SCHEMA”
COL remote_schema FORMAT a35 TRUNC HEADING “REMOTE SCHEMA”

SELECT USER || ‘@’ || C.global_name local_schema,
A.username || ‘@’ || B.global_name remote_schema
FROM user_users@&rem_schema A, global_name@&rem_schema B, global_name C
WHERE ROWNUM = 1;

SET PAGESIZE 9999
SET LINESIZE 250
SET FEEDBACK 1

SET TERMOUT OFF

PROMPT

REM Object differences
REM ==================

COL object_name FORMAT a30

PROMPT SUMMARY OF OBJECTS MISSING FROM LOCAL SCHEMA

SELECT object_type, COUNT (*)
FROM
(
SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name)
FROM user_objects@&rem_schema
MINUS
SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name)
FROM user_objects
)
GROUP BY object_type
ORDER BY object_type;

PROMPT SUMMARY OF EXTRANEOUS OBJECTS IN LOCAL SCHEMA

SELECT object_type, COUNT (*)
FROM
(
SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name)
FROM user_objects
WHERE object_type != ‘DATABASE LINK’
OR object_name NOT LIKE ‘&rem_schema.%’
MINUS
SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name)
FROM user_objects@&rem_schema
)
GROUP BY object_type
ORDER BY object_type;

PROMPT OBJECTS MISSING FROM LOCAL SCHEMA

SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name) object_name
FROM user_objects@&rem_schema
MINUS
SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name) object_name
FROM user_objects
ORDER BY object_type, object_name;

PROMPT EXTRANEOUS OBJECTS IN LOCAL SCHEMA

SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name) object_name
FROM user_objects
WHERE object_type != ‘DATABASE LINK’
OR object_name NOT LIKE ‘&rem_schema.%’
MINUS
SELECT object_type,
DECODE (object_type,
‘INDEX’, DECODE (SUBSTR (object_name, 1, 5),
‘SYS_C’, ‘SYS_C’, object_name),
‘LOB’, DECODE (SUBSTR (object_name, 1, 7),
‘SYS_LOB’, ‘SYS_LOB’, object_name),
object_name) object_name
FROM user_objects@&rem_schema
ORDER BY object_type, object_name;

PROMPT OBJECTS IN LOCAL SCHEMA THAT ARE NOT VALID

SELECT object_name, object_type, status
FROM user_objects
WHERE status != ‘VALID’
ORDER BY object_name, object_type;

REM Table differences
REM =================

PROMPT TABLE COLUMNS MISSING FROM ONE SCHEMA
PROMPT (NOTE THAT THIS REPORT DOES NOT LIST DISCREPENCIES IN COLUMN ORDER)

(
SELECT table_name, column_name, ‘Local’ “MISSING IN SCHEMA”
FROM user_tab_columns@&rem_schema
WHERE table_name IN
(
SELECT table_name
FROM user_tables
)
MINUS
SELECT table_name, column_name, ‘Local’ “MISSING IN SCHEMA”
FROM user_tab_columns
)
UNION ALL
(
SELECT table_name, column_name, ‘Remote’ “MISSING IN SCHEMA”
FROM user_tab_columns
WHERE table_name IN
(
SELECT table_name
FROM user_tables@&rem_schema
)
MINUS
SELECT table_name, column_name, ‘Remote’ “MISSING IN SCHEMA”
FROM user_tab_columns@&rem_schema
)
ORDER BY 1, 2;

COL schema FORMAT a15
COL nullable FORMAT a8
COL data_type FORMAT a9
COL data_length FORMAT 9999 HEADING LENGTH
COL data_precision FORMAT 9999 HEADING PRECISION
COL data_scale FORMAT 9999 HEADING SCALE
COL default_length FORMAT 9999 HEADING LENGTH_OF_DEFAULT_VALUE

PROMPT DATATYPE DISCREPENCIES FOR TABLE COLUMNS THAT EXIST IN BOTH SCHEMAS

(
SELECT table_name, column_name, ‘Remote’ schema,
nullable, data_type, data_length, data_precision, data_scale,
default_length
FROM user_tab_columns@&rem_schema
WHERE (table_name, column_name) IN
(
SELECT table_name, column_name
FROM user_tab_columns
)
MINUS
SELECT table_name, column_name, ‘Remote’ schema,
nullable, data_type, data_length, data_precision, data_scale,
default_length
FROM user_tab_columns
)
UNION ALL
(
SELECT table_name, column_name, ‘Local’ schema,
nullable, data_type, data_length, data_precision, data_scale,
default_length
FROM user_tab_columns
WHERE (table_name, column_name) IN
(
SELECT table_name, column_name
FROM user_tab_columns@&rem_schema
)
MINUS
SELECT table_name, column_name, ‘Local’ schema,
nullable, data_type, data_length, data_precision, data_scale,
default_length
FROM user_tab_columns@&rem_schema
)
ORDER BY 1, 2, 3;

REM Index differences
REM =================

COL column_position FORMAT 999 HEADING ORDER

PROMPT INDEX DISCREPENCIES FOR INDEXES THAT EXIST IN BOTH SCHEMAS

(
SELECT A.index_name, ‘Remote’ schema, A.uniqueness, A.table_name,
B.column_name, B.column_position
FROM user_indexes@&rem_schema A, user_ind_columns@&rem_schema B
WHERE A.index_name IN
(
SELECT index_name
FROM user_indexes
)
AND B.index_name = A.index_name
AND B.table_name = A.table_name
MINUS
SELECT A.index_name, ‘Remote’ schema, A.uniqueness, A.table_name,
B.column_name, B.column_position
FROM user_indexes A, user_ind_columns B
WHERE B.index_name = A.index_name
AND B.table_name = A.table_name
)
UNION ALL
(
SELECT A.index_name, ‘Local’ schema, A.uniqueness, A.table_name,
B.column_name, B.column_position
FROM user_indexes A, user_ind_columns B
WHERE A.index_name IN
(
SELECT index_name
FROM user_indexes@&rem_schema
)
AND B.index_name = A.index_name
AND B.table_name = A.table_name
MINUS
SELECT A.index_name, ‘Local’ schema, A.uniqueness, A.table_name,
B.column_name, B.column_position
FROM user_indexes@&rem_schema A, user_ind_columns@&rem_schema B
WHERE B.index_name = A.index_name
AND B.table_name = A.table_name
)
ORDER BY 1, 2, 6;

REM Constraint differences
REM ======================

PROMPT CONSTRAINT DISCREPENCIES FOR TABLES THAT EXIST IN BOTH SCHEMAS

SET FEEDBACK OFF

CREATE TABLE temp_schema_compare
(
database NUMBER(1),
object_name VARCHAR2(30),
object_text VARCHAR2(2000),
hash_value NUMBER
);

DECLARE
CURSOR c1 IS
SELECT constraint_name, search_condition
FROM user_constraints
WHERE search_condition IS NOT NULL;
CURSOR c2 IS
SELECT constraint_name, search_condition
FROM user_constraints@&rem_schema
WHERE search_condition IS NOT NULL;
v_constraint_name VARCHAR2(30);
v_search_condition VARCHAR2(32767);
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_constraint_name, v_search_condition;
EXIT WHEN c1%NOTFOUND;
v_search_condition := SUBSTR (v_search_condition, 1, 2000);
INSERT INTO temp_schema_compare
(
database, object_name, object_text
)
VALUES
(
1, v_constraint_name, v_search_condition
);
END LOOP;
CLOSE c1;
OPEN c2;
LOOP
FETCH c2 INTO v_constraint_name, v_search_condition;
EXIT WHEN c2%NOTFOUND;
v_search_condition := SUBSTR (v_search_condition, 1, 2000);
INSERT INTO temp_schema_compare
(
database, object_name, object_text
)
VALUES
(
2, v_constraint_name, v_search_condition
);
END LOOP;
CLOSE c2;
COMMIT;
END;
/

SET FEEDBACK 1

(
SELECT REPLACE (TRANSLATE (A.constraint_name,’012345678′,’999999999′),
‘9′, NULL) constraint_name,
‘Remote’ schema, A.constraint_type, A.table_name,
A.r_constraint_name, A.delete_rule, A.status, B.object_text
FROM user_constraints@&rem_schema A, temp_schema_compare B
WHERE A.table_name IN
(
SELECT table_name
FROM user_tables
)
AND B.database (+) = 2
AND B.object_name (+) = A.constraint_name
MINUS
SELECT REPLACE (TRANSLATE (A.constraint_name,’012345678′,’999999999′),
‘9′, NULL) constraint_name,
‘Remote’ schema, A.constraint_type, A.table_name,
A.r_constraint_name, A.delete_rule, A.status, B.object_text
FROM user_constraints A, temp_schema_compare B
WHERE B.database (+) = 1
AND B.object_name (+) = A.constraint_name
)
UNION ALL
(
SELECT REPLACE (TRANSLATE (A.constraint_name,’012345678′,’999999999′),
‘9′, NULL) constraint_name,
‘Local’ schema, A.constraint_type, A.table_name,
A.r_constraint_name, A.delete_rule, A.status, B.object_text
FROM user_constraints A, temp_schema_compare B
WHERE A.table_name IN
(
SELECT table_name
FROM user_tables@&rem_schema
)
AND B.database (+) = 1
AND B.object_name (+) = A.constraint_name
MINUS
SELECT REPLACE (TRANSLATE (A.constraint_name,’012345678′,’999999999′),
‘9′, NULL) constraint_name,
‘Local’ schema, A.constraint_type, A.table_name,
A.r_constraint_name, A.delete_rule, A.status, B.object_text
FROM user_constraints@&rem_schema A, temp_schema_compare B
WHERE B.database (+) = 2
AND B.object_name (+) = A.constraint_name
)
ORDER BY 1, 4, 2;

REM Database link differences
REM =========================

PROMPT DATABASE LINK DISCREPENCIES

COL db_link FORMAT a40

(
SELECT db_link, ‘Remote’ schema, username, host
FROM user_db_links@&rem_schema
MINUS
SELECT db_link, ‘Remote’ schema, username, host
FROM user_db_links
)
UNION ALL
(
SELECT db_link, ‘Local’ schema, username, host
FROM user_db_links
WHERE db_link NOT LIKE ‘&rem_schema.%’
MINUS
SELECT db_link, ‘Local’ schema, username, host
FROM user_db_links@&rem_schema
)
ORDER BY 1, 2;

REM Sequence differences
REM ====================

PROMPT SEQUENCE DISCREPENCIES

(
SELECT sequence_name, ‘Remote’ schema, min_value, max_value,
increment_by, cycle_flag, order_flag, cache_size
FROM user_sequences@&rem_schema
MINUS
SELECT sequence_name, ‘Remote’ schema, min_value, max_value,
increment_by, cycle_flag, order_flag, cache_size
FROM user_sequences
)
UNION ALL
(
SELECT sequence_name, ‘Local’ schema, min_value, max_value,
increment_by, cycle_flag, order_flag, cache_size
FROM user_sequences
MINUS
SELECT sequence_name, ‘Local’ schema, min_value, max_value,
increment_by, cycle_flag, order_flag, cache_size
FROM user_sequences@&rem_schema
)
ORDER BY 1, 2;

REM Private synonym differences
REM ===========================

PROMPT PRIVATE SYNONYM DISCREPENCIES

(
SELECT synonym_name, ‘Remote’ schema, table_owner, table_name, db_link
FROM user_synonyms@&rem_schema
MINUS
SELECT synonym_name, ‘Remote’ schema, table_owner, table_name, db_link
FROM user_synonyms
)
UNION ALL
(
SELECT synonym_name, ‘Local’ schema, table_owner, table_name, db_link
FROM user_synonyms
MINUS
SELECT synonym_name, ‘Local’ schema, table_owner, table_name, db_link
FROM user_synonyms@&rem_schema
)
ORDER BY 1, 2;

REM PL/SQL differences
REM ==================

PROMPT SOURCE CODE DISCREPENCIES FOR PACKAGES, PROCEDURES, AND FUNCTIONS
PROMPT THAT EXIST IN BOTH SCHEMAS

SELECT name, type, COUNT (*) discrepencies
FROM
(
(
SELECT name, type, line, text
FROM user_source@&rem_schema
WHERE (name, type) IN
(
SELECT object_name, object_type
FROM user_objects
)
MINUS
SELECT name, type, line, text
FROM user_source
)
UNION ALL
(
SELECT name, type, line, text
FROM user_source
WHERE (name, type) IN
(
SELECT object_name, object_type
FROM user_objects@&rem_schema
)
MINUS
SELECT name, type, line, text
FROM user_source@&rem_schema
)
)
GROUP BY name, type
ORDER BY name, type;

PROMPT SOURCE CODE DISCREPENCIES FOR PACKAGES, PROCEDURES, AND FUNCTIONS
PROMPT THAT EXIST IN BOTH SCHEMAS (CASE INSENSITIVE COMPARISON)

SELECT name, type, COUNT (*) discrepencies
FROM
(
(
SELECT name, type, line, UPPER (text)
FROM user_source@&rem_schema
WHERE (name, type) IN
(
SELECT object_name, object_type
FROM user_objects
)
MINUS
SELECT name, type, line, UPPER (text)
FROM user_source
)
UNION ALL
(
SELECT name, type, line, UPPER (text)
FROM user_source
WHERE (name, type) IN
(
SELECT object_name, object_type
FROM user_objects@&rem_schema
)
MINUS
SELECT name, type, line, UPPER (text)
FROM user_source@&rem_schema
)
)
GROUP BY name, type
ORDER BY name, type;

REM Trigger differences
REM ===================

PROMPT TRIGGER DISCREPENCIES

SET FEEDBACK OFF

TRUNCATE TABLE temp_schema_compare;

DECLARE
CURSOR c1 IS
SELECT trigger_name, trigger_body
FROM user_triggers;
CURSOR c2 IS
SELECT trigger_name, trigger_body
FROM user_triggers@&rem_schema;
v_trigger_name VARCHAR2(30);
v_trigger_body VARCHAR2(32767);
v_hash_value NUMBER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_trigger_name, v_trigger_body;
EXIT WHEN c1%NOTFOUND;
v_trigger_body := REPLACE (v_trigger_body, ‘ ‘, NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(9), NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(10), NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(13), NULL);
v_trigger_body := UPPER (v_trigger_body);
v_hash_value := dbms_utility.get_hash_value (v_trigger_body, 1, 65536);
INSERT INTO temp_schema_compare (database, object_name, hash_value)
VALUES (1, v_trigger_name, v_hash_value);
END LOOP;
CLOSE c1;
OPEN c2;
LOOP
FETCH c2 INTO v_trigger_name, v_trigger_body;
EXIT WHEN c2%NOTFOUND;
v_trigger_body := REPLACE (v_trigger_body, ‘ ‘, NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(9), NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(10), NULL);
v_trigger_body := REPLACE (v_trigger_body, CHR(13), NULL);
v_trigger_body := UPPER (v_trigger_body);
v_hash_value := dbms_utility.get_hash_value (v_trigger_body, 1, 65536);
INSERT INTO temp_schema_compare (database, object_name, hash_value)
VALUES (2, v_trigger_name, v_hash_value);
END LOOP;
CLOSE c2;
END;
/

SET FEEDBACK 1

(
SELECT A.trigger_name, ‘Local’ schema, A.trigger_type,
A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
A.status, B.hash_value
FROM user_triggers A, temp_schema_compare B
WHERE B.object_name (+) = A.trigger_name
AND B.database (+) = 1
AND A.table_name IN
(
SELECT table_name
FROM user_tables@&rem_schema
)
MINUS
SELECT A.trigger_name, ‘Local’ schema, A.trigger_type,
A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
A.status, B.hash_value
FROM user_triggers@&rem_schema A, temp_schema_compare B
WHERE B.object_name (+) = A.trigger_name
AND B.database (+) = 2
)
UNION ALL
(
SELECT A.trigger_name, ‘Remote’ schema, A.trigger_type,
A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
A.status, B.hash_value
FROM user_triggers@&rem_schema A, temp_schema_compare B
WHERE B.object_name (+) = A.trigger_name
AND B.database (+) = 2
AND A.table_name IN
(
SELECT table_name
FROM user_tables
)
MINUS
SELECT A.trigger_name, ‘Remote’ schema, A.trigger_type,
A.triggering_event, A.table_name, SUBSTR (A.referencing_names, 1, 30)
referencing_names, SUBSTR (A.when_clause, 1, 30) when_clause,
A.status, B.hash_value
FROM user_triggers A, temp_schema_compare B
WHERE B.object_name (+) = A.trigger_name
AND B.database (+) = 1
)
ORDER BY 1, 2, 5, 3;

REM View differences
REM ================

PROMPT VIEW DISCREPENCIES

SET FEEDBACK OFF

TRUNCATE TABLE temp_schema_compare;

DECLARE
CURSOR c1 IS
SELECT view_name, text
FROM user_views;
CURSOR c2 IS
SELECT view_name, text
FROM user_views@&rem_schema;
v_view_name VARCHAR2(30);
v_text VARCHAR2(32767);
v_hash_value NUMBER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_view_name, v_text;
EXIT WHEN c1%NOTFOUND;
v_text := REPLACE (v_text, ‘ ‘, NULL);
v_text := REPLACE (v_text, CHR(9), NULL);
v_text := REPLACE (v_text, CHR(10), NULL);
v_text := REPLACE (v_text, CHR(13), NULL);
v_text := UPPER (v_text);
v_hash_value := dbms_utility.get_hash_value (v_text, 1, 65536);
INSERT INTO temp_schema_compare (database, object_name, hash_value)
VALUES (1, v_view_name, v_hash_value);
END LOOP;
CLOSE c1;
OPEN c2;
LOOP
FETCH c2 INTO v_view_name, v_text;
EXIT WHEN c2%NOTFOUND;
v_text := REPLACE (v_text, ‘ ‘, NULL);
v_text := REPLACE (v_text, CHR(9), NULL);
v_text := REPLACE (v_text, CHR(10), NULL);
v_text := REPLACE (v_text, CHR(13), NULL);
v_text := UPPER (v_text);
v_hash_value := dbms_utility.get_hash_value (v_text, 1, 65536);
INSERT INTO temp_schema_compare (database, object_name, hash_value)
VALUES (2, v_view_name, v_hash_value);
END LOOP;
CLOSE c2;
END;
/

SET FEEDBACK 1

(
SELECT A.view_name, ‘Local’ schema, B.hash_value
FROM user_views A, temp_schema_compare B
WHERE B.object_name (+) = A.view_name
AND B.database (+) = 1
AND A.view_name IN
(
SELECT view_name
FROM user_views@&rem_schema
)
MINUS
SELECT A.view_name, ‘Local’ schema, B.hash_value
FROM user_views@&rem_schema A, temp_schema_compare B
WHERE B.object_name (+) = A.view_name
AND B.database (+) = 2
)
UNION ALL
(
SELECT A.view_name, ‘Remote’ schema, B.hash_value
FROM user_views@&rem_schema A, temp_schema_compare B
WHERE B.object_name (+) = A.view_name
AND B.database (+) = 2
AND A.view_name IN
(
SELECT view_name
FROM user_views
)
MINUS
SELECT A.view_name, ‘Remote’ schema, B.hash_value
FROM user_views A, temp_schema_compare B
WHERE B.object_name (+) = A.view_name
AND B.database (+) = 1
)
ORDER BY 1, 2;

REM Job queue differences
REM =====================

COL what FORMAT a30
COL interval FORMAT a30

PROMPT JOB QUEUE DISCREPENCIES

(
SELECT what, interval, ‘Remote’ schema
FROM user_jobs@&rem_schema
MINUS
SELECT what, interval, ‘Remote’ schema
FROM user_jobs
)
UNION ALL
(
SELECT what, interval, ‘Local’ schema
FROM user_jobs
MINUS
SELECT what, interval, ‘Local’ schema
FROM user_jobs@&rem_schema
)
ORDER BY 1, 2, 3;

REM Privilege differences
REM =====================

PROMPT OBJECT-LEVEL GRANT DISCREPENCIES

(
SELECT owner, table_name, ‘Remote’ schema, grantee, privilege, grantable
FROM user_tab_privs@&rem_schema
WHERE (owner, table_name) IN
(
SELECT owner, object_name
FROM all_objects
)
MINUS
SELECT owner, table_name, ‘Remote’ schema, grantee, privilege, grantable
FROM user_tab_privs
)
UNION ALL
(
SELECT owner, table_name, ‘Local’ schema, grantee, privilege, grantable
FROM user_tab_privs
WHERE (owner, table_name) IN
(
SELECT owner, object_name
FROM all_objects@&rem_schema
)
MINUS
SELECT owner, table_name, ‘Local’ schema, grantee, privilege, grantable
FROM user_tab_privs@&rem_schema
)
ORDER BY 1, 2, 3;

PROMPT SYSTEM PRIVILEGE DISCREPENCIES

(
SELECT privilege, ‘Remote’ schema, admin_option
FROM user_sys_privs@&rem_schema
MINUS
SELECT privilege, ‘Remote’ schema, admin_option
FROM user_sys_privs
)
UNION ALL
(
SELECT privilege, ‘Local’ schema, admin_option
FROM user_sys_privs
MINUS
SELECT privilege, ‘Local’ schema, admin_option
FROM user_sys_privs@&rem_schema
)
ORDER BY 1, 2;

PROMPT ROLE PRIVILEGE DISCREPENCIES

(
SELECT granted_role, ‘Remote’ schema, admin_option, default_role, os_granted
FROM user_role_privs@&rem_schema
MINUS
SELECT granted_role, ‘Remote’ schema, admin_option, default_role, os_granted
FROM user_role_privs
)
UNION ALL
(
SELECT granted_role, ‘Local’ schema, admin_option, default_role, os_granted
FROM user_role_privs
MINUS
SELECT granted_role, ‘Local’ schema, admin_option, default_role, os_granted
FROM user_role_privs@&rem_schema
)
ORDER BY 1, 2;

SPOOL OFF

SET TERMOUT ON

PROMPT
PROMPT Report output written to &report

SET FEEDBACK OFF

DROP TABLE temp_schema_compare;
DROP DATABASE LINK &rem_schema;

SET FEEDBACK 6
SET PAGESIZE 20
SET LINESIZE 80

set linesize 200
SELECT SUBSTR (df.NAME, 1, 65) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) – NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

2)

select d.tablespace_name, d.file_id, d.file_name, d.bytes allocated, nvl(f.bytes,0) free
from (select tablespace_name, file_id, file_name, sum(bytes) bytes from dba_data_files
group by tablespace_name, file_id, file_name) d,
(select tablespace_name, file_id, sum(bytes) bytes from dba_free_space
group by tablespace_name, file_id) f
where d.tablespace_name in (‘SYSTEM’,'SYSAUX’)
and d.tablespace_name = f.tablespace_name(+)
and d.file_id = f.file_id(+)
order by tablespace_name, file_id;