Script to check the Checkpoint SCN number in Oracle Database

Download the script from HERE.

[oracle@node1 u01]$ more checkpoint.sh
#!/bin/bash
echo "***************************************************************"
echo "**************** Checking the Database SCN *********************"
echo "***************************************************************"

export ORACLE_SID=$1

if [ -z $1 ]; then

   echo " Plz enter the DB name "
   exit;
fi


sqlplus -S / as sysdba@$1 << EOF

COL NAME FOR A50
SET LINE 100
SET PAGESIZE 100

prompt SCN of CONTROLFILE;

SELECT CHECKPOINT_CHANGE# FROM V\$DATABASE;

EOF


sqlplus -S / as sysdba@$1<<EOF

COL NAME FOR A50
SET LINE 100
SET HEAD OFF
SET PAGESIZE 100

prompt SCN of DATAFILE;

SELECT NAME,CHECKPOINT_CHANGE#,LAST_CHANGE# AS "STOP SCN" FROM V\$DATAFILE;

EOF


sqlplus -S / as sysdba@$1 << EOF

COL NAME FOR A50
SET LINE 100
SET PAGESIZE 100

prompt  SCN of DATAFILE HEADER;

SELECT NAME,CHECKPOINT_CHANGE# AS "START SCN" FROM V\$DATAFILE_HEADER;

EOF

OUTPUT.

[oracle@node1 u01]$ sh checkpoint.sh node1

***************************************************************

**************** Checking the Database SCN *********************

***************************************************************

SCN of CONTROLFILE

CHECKPOINT_CHANGE#

------------------

          20412372

SCN of DATAFILE

+DATA/node/datafile/system.256.827631593                     20412372

+DATA/node/datafile/sysaux.257.827631595                     20412372

+DATA/node/datafile/undotbs1.258.827631595                   20412372

+DATA/node/datafile/users.259.827631595                      20412372

+DATA/node/datafile/example.264.827631759                    20412372

+DATA/node/datafile/undotbs2.265.827632181                   20412372

+DATA/node/datafile/test.283.829159407                       20412372

+DATA/node/datafile/idx.282.832023975                        20412372

+DATA/node/datafile/test.280.832037367                       20412372

9 rows selected.

SCN of DATAFILE HEADER

NAME                                                START SCN

-------------------------------------------------- ----------

+DATA/node/datafile/system.256.827631593             20412372

+DATA/node/datafile/sysaux.257.827631595             20412372

+DATA/node/datafile/undotbs1.258.827631595           20412372

+DATA/node/datafile/users.259.827631595              20412372

+DATA/node/datafile/example.264.827631759            20412372

+DATA/node/datafile/undotbs2.265.827632181           20412372

+DATA/node/datafile/test.283.829159407               20412372

+DATA/node/datafile/idx.282.832023975                20412372

+DATA/node/datafile/test.280.832037367               20412372

9 rows selected.