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
