Oracle database's fixed_date feature usage script

Some time, there is requirement to advance the database’s date to test month end or advance date related batch process. One way to change the date at O/s level but it may difficult if there are more than databases in Database Server box.

Below script is used fixed_date feature to set advance date and increments / synchronies the time after every 2 minutes through script.

Create a folder (for example dt) and create below script files.

Create or Edit instance_list_node2 to include database(s) for fix /Advance date

# cat instance_list_node2
orcl
demodb
testdb

Main script to get changed date and time of database(s).

# cat clock_change.ksh

#!/usr/bin/ksh
############################################
# never-ending loop to change database clock
############################################
export ORACLE_HOME=/oracle/oradata01/as10g # change the Oracle home name
export JOBDIR=/home/oracle/scripts/dt # change the folder name
export NODENUM=2
export LISTFILE=/home/oracle/scripts/dt/instance_list_node${NODENUM} # change the folder name
cd $JOBDIR

# start with q resync of hours and minutes...
CHANGE_TYPE=1
exec < ${LISTFILE}
while read line
do
export ORACLE_SID=${line}
TYPE=RESYNC
${ORACLE_HOME}/bin/sqlplus / as sysdba @${JOBDIR}/resync_clock_change_sql_generator.sql
TIME=`date '+%H:%M:%S'`
sed -e "s/\*/${TIME}/g" ${JOBDIR}/resync_clock_change_model.sql > ${JOBDIR}/resync_clock_change.sql
${ORACLE_HOME}/bin/sqlplus / as sysdba @${JOBDIR}/resync_clock_change.sql

if [ $? -eq 0 ]
then
echo ${ORACLE_SID} $TYPE" clock update ran " `date` >> ${JOBDIR}/clock_change.log
else
echo " " >> ${JOBDIR}/clock_change.log
echo " " >> ${JOBDIR}/clock_change.log
echo ${ORACLE_SID} $TYPE" CLOCK UPDATE FAILED " `date` >> ${JOBDIR}/clock_change.log
echo " " >> ${JOBDIR}/clock_change.log
echo " " >> ${JOBDIR}/clock_change.log
fi
done
sleep 120

#
# This is an infinite loop...
#
while [ 1 -eq 1 ]
do
CHANGE_TYPE=0
HR=`date '+%H'`
exec < ${LISTFILE}
while read line
do
export ORACLE_SID=${line}

if [ ${HR} -eq 01 ]
then
CHANGE_TYPE=1
fi
if [ ${HR} -eq 07 ]
then
CHANGE_TYPE=1
fi
if [ ${HR} -eq 13 ]
then
CHANGE_TYPE=1
fi
if [ ${HR} -eq 19 ]
then
CHANGE_TYPE=1
fi

if [ ${CHANGE_TYPE} -eq 1 ]
then
TYPE=RESYNC
${ORACLE_HOME}/bin/sqlplus / as sysdba @${JOBDIR}/resync_clock_change_sql_generator.sql
TIME=`date '+%H:%M:%S'`
sed -e "s/\*/${TIME}/g" ${JOBDIR}/resync_clock_change_model.sql > ${JOBDIR}/resync_clock_change.sql
${ORACLE_HOME}/bin/sqlplus / as sysdba @${JOBDIR}/resync_clock_change.sql
else
CHANGE_TYPE=2
TYPE=INCREMENTAL
${ORACLE_HOME}/bin/sqlplus / as sysdba @${JOBDIR}/clock_change1.sql
fi

if [ ${?} -eq 0 ]
then
echo ${ORACLE_SID} ${TYPE}" clock update ran " `date` >> ${JOBDIR}/clock_change.log
else
echo " " >> ${JOBDIR}/clock_change.log
echo " " >> ${JOBDIR}/clock_change.log
echo ${ORACLE_SID} $TYPE" CLOCK UPDATE FAILED " `date` >> ${JOBDIR}/clock_change.log
echo " " >> ${JOBDIR}/clock_change.log
echo " " >> ${JOBDIR}/clock_change.log
fi
done
sleep 120
done


To get new date after 24 hrs.

# cat resync_clock_change_sql_generator.sql
set feedback off
set heading off
spool /home/oracle/scripts/dt/resync_clock_change_model.sql # change the folder name
select 'alter system set FIXED_DATE = '''to_char(sysdate,'YYYY-MM-DD-*')''' ;' from dual;
select 'exit;' from dual;
spool off
exit;


To get changed new time

# cat clock_change1.sql
set feedback off
set heading off
spool /home/oracle/scripts/dt/clock_change2.sql # change the folder name
select 'alter system set FIXED_DATE = '''to_char(sysdate + 2/1440,'YYYY-MM-DD-HH24:MI:SS')''' ;' from dual;
select 'exit;' from dual;
spool off
@/home/oracle/scripts/dt/clock_change2.sql ; # change the folder name
exit;


# Intermediate file

# cat resync_clock_change.sql
alter system set FIXED_DATE = '2012-01-12-19:59:18' ;
exit;

# Intermediate file

# cat clock_change2.sql
alter system set FIXED_DATE = '2012-01-12-23:11:18' ;
exit;

To implement the script:


chmod 777 clock_change.ksh

SQL> shutdown immediate;
SQL> startup mount
SQL>alter system set FIXED_DATE='2011-12-31-10:00:00'; # any advance /back date

To start script (infinite loop)

#nohup ./clock_change.ksh &

To Verify the date and time of database. This script will change the time after every 2 mins and date will be changed after 24 hrs.

SQL>alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS';

SQL> select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD
-------------------
31-12-2011 10:00:00

To stop the script for particular database

Remove the database name from file instance_list_node2

To stop the script

#ps -ef grep clock_change
#kill -9 process_id