Oracle & MySQL Support

InfraStack-Labs Oracle & MySQL DBA Services help you manage, maintain, and optimize your critical Oracle systems. We deliver 24/7, year-round support with flexible monthly contracts that don’t lock you in.

Please contact me :- ajith.narayanan@infrastack-labs.in

Monday, November 11, 2013

REFRESHING ORACLE TIMESTEN DATASTORE USING PRODUCTION BACKUPS

Today I was searching for some old document , But saw this document created by me to refresh the timesten database and thought of posting it, This might be helpful for somebody who is searching for timesten refresh procedure.

Note:- I do not remember anything about timesten database these days, this procedure was created by me atleast 4 years ago, When I first learned to timesten refresh.


Very Important Note:- I created this document and following this document one of my team members lost his job, Don't get scared.....It was not because the document was wrong, but the person refreshed production from dev environment instead of reverse.

(1) SOURCE DATASTORE(PROD)
    ======================
(1.1) Identify from where timesten binaries are running
      =================================================
ajithpathiyil1% ps -ef|grep -i timesten
timesten  1458  1455   0   Sep 26 ?           0:58 /export/MYTTINST/TimesTen/7.0.5_64/bin/timestensubd -verbose -userlog /export/CHE
timesten  1459  1455   0   Sep 26 ?           2:49 /export/MYTTINST/TimesTen/7.0.5_64/bin/timestensubd -verbose -userlog /export/CHE
timesten  1455     1   0   Sep 26 ?          33:06 /export/MYTTINST/TimesTen/7.0.5_64/bin/timestend -initfd 13
timesten  1460  1455   0   Sep 26 ?           0:57 /export/MYTTINST/TimesTen/7.0.5_64/bin/timestensubd -verbose -userlog /export/CHE
timesten  1461  1455   0   Sep 26 ?         587:39 /export/MYTTINST/TimesTen/7.0.5_64/bin/timestensubd -verbose -userlog /export/CHE
timesten  1462  1455   0   Sep 26 ?           1:17 /export/MYTTINST/TimesTen/7.0.5_64/bin/ttcserver -verbose -userlog /export/MYTTINST
timesten 22251 21439   0 03:19:13 pts/8       0:00 grep -i timesten
timesten  3566     1   0   Sep 30 ?           3:26 /export/client/app/oracle/product/10.2.0/agent10g/perl/bin/perl /export/client/
timesten 12511  1455   0   Oct 09 ?           9:56 /export/MYTTINST/TimesTen/7.0.5_64/bin/timestenrepd -verbose -userlog /export/CHE
timesten  3583  3566   0   Sep 30 ?          84:07 /export/client/app/oracle/product/10.2.0/agent10g/bin/emagent
timesten  9199  9189   0 03:15:05 ?           0:00 /bin/sh -c /bin/ksh
timesten 20383  9200   3 03:18:38 ?           0:36 gzip /home/edgprod/cheprod/logs/tt_backups/BACKUP_MYTTINSTTLNP1A_121010.0.bac /ho
timesten 21439 21430   0 03:18:59 pts/8       0:00 -csh
timesten 22250 21439   0 03:19:13 pts/8       0:00 ps -ef
timesten  9200  9199   0 03:15:05 ?           0:00 /bin/ksh
timesten 17518     1   0   Oct 08 ?           0:37 direct -SDCTT_AJITHPATHIYIL1
timesten 17551 17518   0   Oct 08 ?           0:00 directc -R3 -W4
ajithpathiyil1%

(1.2) Identify the binary location for the timesten source datastore

      ===============================================================
      In case of multiple datastores as in above case.identify the exact bin folder from where our source datastore is running(SOURCE) 

(1.3) Find the TTSTATUS

      =================
ajithpathiyil1% ./ttstatus
TimesTen status report as of Tue Oct 12 03:42:13 2010

Daemon pid 1455 port 44710 instance 7.0.5_64

TimesTen server pid 1462 started on port 44711
No TimesTen webserver running

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

Data store /export/MYTTINSTTLNP1/ttdata/MYTTINSTTLNP1A
There are 119 connections to the data store
Data store is in shared mode
Shared Memory KEY 0x16005815 ID 1 (ISM LOCKED)
Type            PID     Context             Connection Name              ConnID
Process         24843   0x0000000109ace810  java                            108
Process         24843   0x0000000109b28940  java                            109
Process         24843   0x0000000109b82a70  java                            110
Process         24843   0x0000000109bdcba0  java                            111
Process         24843   0x0000000109c36cd0  java                            113
Process         470     0x0000000100a71400  java                              1
Process         470     0x0000000100b843c0  java                              2
Process         952     0x0000000100b4ee70  java                              3
Replication     12511   0x00000001002e8980  REPHOLD                           5
Replication     12511   0x00000001002f9640  REPLISTENER                       6
Replication     12511   0x000000010030a300  LOGFORCE                          7
Replication     12511   0x00000001003cac20  TRANSMITTER                       4
Replication     12511   0x00000001003db8e0  TRANSMITTER                     112
Replication     12511   0x00000001003fc5c0  TRANSMITTER                      10
Replication     12511   0x000000010053d9e0  RECEIVER                          8
Subdaemon       1461    0x000000010015bc40  Worker                         2042
Subdaemon       1461    0x00000001002525e0  Flusher                        2046
Subdaemon       1461    0x00000001002632a0  Checkpoint                     2047
Subdaemon       1461    0x0000000100273f60  Aging                          2043
Subdaemon       1461    0x00000001002c4dc0  HistGC                         2045
Subdaemon       1461    0x0000000100356000  Monitor                        2044
RAM residence policy: Always
Replication policy  : Always
Replication agent is running.
Cache agent policy  : Manual
------------------------------------------------------------------------
Access control enabled.
End of report
ajithpathiyil1%

(2) Identify backups for restoration in TARGET non-prod server

    ===========================================================

(2.1) For NA check the crontab -l for the backup script and its log file to know the backup location

(2.2) For EMEA/APAC check the OS script in the scheduled GRID jobs (Use the target datastore name to find the backup job scheduled for the PROD)

In this example the below OS script was identified from GRID

==============================================FIND BACKUP LOCATION FOR PROD TTDUMPS=====================================================
export DT=`date +'%%d%%m%%y`
export TT_INSTANCE=MYTTINSTTLNP1S
/export/MYTTINST/TimesTen/7.0.5_64/bin/ttbackup -dir /home/edgprod/cheprod/logs/tt_backups -fname BACKUP_MYTTINSTTLNP1S_$DT MYTTINSTTLNP1S
if [ "$?" -ne "0" ]; then
 echo "Backup Failed"
 exit 1
fi
gzip /home/edgprod/cheprod/logs/tt_backups/BACKUP_MYTTINSTTLNP1S_$DT*
if [ "$?" -ne "0" ]; then
 echo "GZip Failed"
 exit 1
fi

find /home/edgprod/cheprod/logs/tt_backups -name 'BACKUP_MYTTINSTTLNP1S*' -mtime +7 -exec rm -f {} \;


if [ "$?" -ne "0" ]; then

 echo "Find/Delete Failed"
 exit 1
fi
============================================================================================================================================


(3) SCP the latest backups to the target to any directory

==========================================================
Push to TARGET host
scp BACKUP_MYTTINSTTLNP1S_121010.0*  timesten@ajithpathiyil2.lab.com:/opt/timesten/REFRESH_BKP
(or)

Pull from TARGET host

scp e396939@ajithpathiyil1.lab.com:/home/edgprod/cheprod/logs/tt_backups/BACKUP_MYTTINSTTLNP1S_121010.0* .


(3) TARGET Datastore

    ================
timesten@MYTTINSTDEV:REFRESH_BKP:[24] cd /opt/timesten/7.0.5.64/solaris/TimesTen/tt70/bin/
timesten@MYTTINSTDEV:bin:[25] ./ttstatus
TimesTen status report as of Tue Oct 12 04:10:44 2010

Daemon pid 4188 port 17001 instance tt70

TimesTen server pid 4193 started on port 17003
No TimesTen webserver running

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

Data store /MYTTINST_U1/ttds/UTTDS_CHEOLN5/ds/UTTDS_CHEOLN5
There are no connections to the data store
Replication policy  : Manual
Cache agent policy  : Manual
------------------------------------------------------------------------
Data store /MYTTINST_U1/ttds/UTT70_SUPPLN1/ds/UTT70_SUPPLN1
There are 30 connections to the data store
Data store is in shared mode
Shared Memory KEY 0x16003301 ID 16777266 (ISM LOCKED)
Type            PID     Context             Connection Name              ConnID
Process         12254   0x00000001006186e0  java                             12
Process         12254   0x0000000102d43760  java                              2
Process         12254   0x0000000102e88c60  java                              5
Process         12254   0x0000000103be6ff0  java                             23
Process         12254   0x0000000103c37d30  java                             24
Process         9561    0x00000001007565c0  java                              1
Subdaemon       12193   0x000000010015bc40  Worker                         2042
Subdaemon       12193   0x00000001001ce3f0  Flusher                        2047
Subdaemon       12193   0x00000001001df0b0  Checkpoint                     2043
Subdaemon       12193   0x0000000100252370  Aging                          2045
Subdaemon       12193   0x0000000100263030  HistGC                         2046
Subdaemon       12193   0x0000000100273cf0  Monitor                        2044
RAM residence policy: Always
Replication policy  : Manual
Cache agent policy  : Manual
------------------------------------------------------------------------
Access control enabled.
End of report
timesten@MYTTINSTDEV:bin:[26]


(3.1) Confirm all backup files have been received at TARGET

     ======================================================
timesten@MYTTINSTDEV:REFRESH_BKP:[28]  ls -ltr BACKUP_MYTTINSTTLNP1S_121010*gz|wc -l
      37

(3.2) Unzip or Uncompress the backup files if necessary

      ==================================================

(4) NOW START RESTORE FROM BACKUP USING THE BELOW COMMANDS

============================================================

./ttadmin -rampolicy manual UTTDS_CHPINTNY1 

/apps/timesten/11.2.1.6.1/TimesTen/tt1121/bin/ttdaemonadmin -stop
/apps/timesten/11.2.1.6.1/TimesTen/tt1121/bin/ttdaemonadmin -start
ttdestroy UTTDS_CHPINTNY1 
ttrestore -d . -f filename UTTDS_CHPINTNY1



/apps/timesten/11.2.1.6.1/TimesTen/tt1121/bin/ttadmin -rampolicy manual UTTDS_CHPINTNY1

/apps/timesten/11.2.1.6.1/TimesTen/tt1121/bin/ttadmin -ramunload UTTDS_CHPINTNY1
/apps/timesten/11.2.1.6.1/TimesTen/tt1121/bin/ttdestroy UTTDS_CHPINTNY1
/apps/timesten/11.2.1.6.1/TimesTen/tt1121/bin/ttrestore -fname temp1 -dir . UTTDS_CHPINTNY1


./ttisql UTTDS_CHPINTNY1

repschemes;
drop replication ; 
xladeletebookmark;
xladeletebookmark ;
===============================================================================

(4.1) Destroy the TARGET datastore

      =============================
/opt/timesten/7.0.5.64/solaris/TimesTen/tt70/bin/ttadmin -rampolicy manual UTT70_SUPPLN1 
/opt/timesten/7.0.5.64/solaris/TimesTen/tt70/bin/ttdestroy UTT70_SUPPLN1 

Note:- Using ttstatus command make sure no user connections are existing(Confirm with user if the datastore can be destroyed)


(4.2) Change to the directory that contains the backup files from PROD

      =================================================================
timesten@MYTTINSTDEV:REFRESH_BKP:[109] /opt/timesten/7.0.5.64/solaris/TimesTen/tt70/bin/ttadmin -rampolicy manual UTT70_SUPPLN1
timesten@MYTTINSTDEV:REFRESH_BKP:[110] /opt/timesten/7.0.5.64/solaris/TimesTen/tt70/bin/ttadmin -ramunload UTT70_SUPPLN1
timesten@MYTTINSTDEV:REFRESH_BKP:[111] /opt/timesten/7.0.5.64/solaris/TimesTen/tt70/bin/ttdestroy UTT70_SUPPLN1
timesten@MYTTINSTDEV:REFRESH_BKP:[112] /opt/timesten/7.0.5.64/solaris/TimesTen/tt70/bin/ttrestore -fname BACKUP_MYTTINSTTLNP1S_121010 -dir . UTT70_SUPPLN1
/opt/timesten/7.0.5.64/solaris/TimesTen/tt70/bin/ttisql UTT70_SUPPLN1
repschemes;


xladeletebookmark;


Command> repschemes;


0 replication schemes found.

Command> xladeletebookmark;

Note:-  Use "drop replication ;" 


0 bookmarks found.


Note:- Use "xladeletebookmark ;"


Command> exit

Disconnecting...
Done.



(4.3) Send the TTSTSTAUS to the user

      =================================
timesten@MYTTINSTDEV:REFRESH_BKP:[113] /opt/timesten/7.0.5.64/solaris/TimesTen/tt70/bin/ttstatus
TimesTen status report as of Tue Oct 12 06:23:05 2010

Daemon pid 4188 port 17001 instance tt70

TimesTen server pid 4193 started on port 17003
No TimesTen webserver running

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

Data store /MYTTINST_U1/ttds/UTT70_SUPPLN1/ds/UTT70_SUPPLN1
There are 6 connections to the data store
Data store is in shared mode
Shared Memory KEY 0x04003303 ID 16777314 (ISM LOCKED)
Type            PID     Context             Connection Name              ConnID
Subdaemon       12193   0x000000010015bc40  Worker                         2042
Subdaemon       12193   0x00000001001ce3f0  Flusher                        2043
Subdaemon       12193   0x00000001001df0b0  Checkpoint                     2047
Subdaemon       12193   0x00000001003450c0  Aging                          2045
Subdaemon       12193   0x0000000100355d80  HistGC                         2046
Subdaemon       12193   0x0000000100396aa0  Monitor                        2044
RAM residence policy: Always
Replication policy  : Manual
Cache agent policy  : Manual
------------------------------------------------------------------------
Data store /MYTTINST_U1/ttds/UTTDS_CHEOLN5/ds/UTTDS_CHEOLN5
There are no connections to the data store
Replication policy  : Manual
Cache agent policy  : Manual
------------------------------------------------------------------------
Access control enabled.
End of report
timesten@MYTTINSTDEV:REFRESH_BKP:


Note :- If the TTSTATUS does not show the TARGET datastore use the below commands

        /opt/timesten/7.0.5.64/solaris/TimesTen/tt70/bin/ttadmin -rampolicy always UTT70_SUPPLN1
 /opt/timesten/7.0.5.64/solaris/TimesTen/tt70/bin/ttadmin -ramload UTT70_SUPPLN1


POSSIBLE ERRORS DURING REFRESH

##############################################################################################################################
timesten@MYTTINSTDEV:REFRESH_BKP:[65] /opt/timesten/7.0.5.64/solaris/TimesTen/tt70/bin/ttrestore -fname BACKUP_MYTTINSTTLNP1S_121010 -dir . UTT70_SUPPLN1
Restore started ...
Restore failed:
Error 12133: TT12133: Data store file already exists -- file "restore.c", lineno 961, procedure "doRestore"
timesten@MYTTINSTDEV:REFRESH_BKP:[66]

In this case 

===========

timesten@MYTTINSTDEV:timesten:[135] cd /var/TimesTen

timesten@MYTTINSTDEV:TimesTen:[136] ls -ltr
total 312
-rw-r--r--   1 timesten timesten     470 Jul 28  2008 snmp.ini
-rw-r--r--   1 timesten timesten     130 Jul 28  2008 sys.ttconnect.ini
lrwxrwxrwx   1 timesten timesten      23 Jul 30  2008 log -> /MYTTINST_U2/ttlog/server
-rw-r--r--   1 timesten timesten   18944 Sep 19  2008 TT70.tar
-rw-r--r--   1 timesten timesten  117760 Oct 24  2008 tt70-24Oct-prd.tar
-rw-r--r--   1 timesten timesten    3996 Jul  7 08:23 sys.odbc.ini
-rw-r--r--   1 timesten timesten      32 Jul  7 08:26 chris,dat
drwxr-xr-x   2 timesten timesten     512 Oct 12 06:09 tt70
timesten@MYTTINSTDEV:TimesTen:[137] vi sys.odbc.ini
"sys.odbc.ini" 189 lines, 3996 characters
[UTT70_SUPPLN1]
Description=Test TimesTen Data Store
Driver=/opt/timesten/7.0.5.64/solaris/TimesTen/tt70/lib/libtten.so
Authenticate=1
DataStore=/MYTTINST_U1/ttds/UTT70_SUPPLN1/ds/UTT70_SUPPLN1<<<<<<<<<<<<<<<---------------------- font="">
LogDir=/MYTTINST_U2/ttlog/UTT70_SUPPLN1/logs  <<<<<<<<<<<<<<<<<<<<<<<<<<<---------------------- 2="" a="" after="" be="" command="" directories="" empty="" font="" in="" make="" note="" of="" should="" step="" the="" ttdestroy="" which="">
TypeMode=1
DatabaseCharacterSet=US7ASCII
Temporary=0
# Originally 1 to allow for creation
AutoCreate=1
# In MB
LogFileSize=64
# In KB
LogBuffSize=65536
Logging=1
LogPurge=1
MemoryLock=4
Overwrite=0
PermSize=7168
Preallocate=0
SMPOptLevel=1
TempSize=128
DurableCommits=0
ExclAccess=0
Isolation=1
#IsoOverride=0
LockLevel=0
LockWait=120
MatchLogOpts=0
ThreadSafe=1
WaitForConnect=1
CkptFrequency=300
CkptLogVolume=0
Connections=256
PermWarnThreshold=80
TempWarnThreshold=80

Note:- Make sure the following directories get empty after using ttdestroy command in step(4.2)


timesten@MYTTINSTDEV:TimesTen:[139] cd /MYTTINST_U1/ttds/UTT70_SUPPLN1/ds/

timesten@MYTTINSTDEV:ds:[140] ls -ltr
total 23892436
-rw-rw-rw-   1 timesten timesten 6261323072 Oct 12 07:33 UTT70_SUPPLN1.ds0
-rw-rw-rw-   1 timesten timesten 6261325824 Oct 12 07:38 UTT70_SUPPLN1.ds1
timesten@MYTTINSTDEV:ds:[141] cd /MYTTINST_U2/ttlog/UTT70_SUPPLN1/logs
timesten@MYTTINSTDEV:logs:[142] ls -ltr
total 452560
-rw-rw-rw-   1 timesten timesten 67108864 Oct 12 06:09 UTT70_SUPPLN1.res0
-rw-rw-rw-   1 timesten timesten 67108864 Oct 12 06:09 UTT70_SUPPLN1.res1
-rw-rw-rw-   1 timesten timesten 67108864 Oct 12 06:09 UTT70_SUPPLN1.res2
-rw-rw-rw-   1 timesten timesten 29278208 Oct 12 07:38 UTT70_SUPPLN1.log10546
timesten@MYTTINSTDEV:logs:[143]


HAPPY LEARNING!