Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member
0 Kudos

DBA routinely did backup database task to disk files OR tapes ,

but sometime, DBA also need to do some database restore practice for disaster recovery !

The following steps is used to give DBA an real example for reference !!

*Steps 1: issue sp_iqfile to check iqfile contents
==========================================================

sgsinvm0257:~/IQ-15_4/demo> dbisql -nogui -c "uid=DBA;pwd=sql;eng=sgsinvm0257_iqdemo"

(DBA)> sp_iqfile;
DBSpaceName                                                                                                                      DBFileName                                                                                                                       Path                                                                                                                                                                                                                                                             SegmentType  RWMode Online Usage DBFileSize Reserve StripeSize BlkTypes                                                                                                                                                                                                                                                        FirstBlk             LastBlk              OkToDrop
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IQ_SYSTEM_MAIN                                                                                                                   IQ_SYSTEM_MAIN                                                                                                                   iqdemo.iq                                                                                                                                                                                                                                                        MAIN         RW     T      11    200M       100M    1K         1H,2528F,32D,96A,128M                                                                                                                                                                                                                                           1                    25600                N       
iq_main                                                                                                                          iq_main                                                                                                                          iqdemo_main.iq                                                                                                                                                                                                                                                   MAIN         RW     T      10    100M       200M    1K         1H,1168A                                                                                                                                                                                                                                                        1045440              1058239              N       
iq_main                                                                                                                          iq_main2                                                                                                                         /data/sybase/iq1540/IQ-15_4/demo/iqdemo_main2.iq                                                                                                                                                                                                                 MAIN         RW     T      46    100M       30M     1K         1H,5840A                                                                                                                                                                                                                                                        2090880              2103679              N       
test_dbspace                                                                                                                     filehist1                                                                                                                        /data/sybase/iq1540/IQ-15_4/demo/test_dbspace.iq                                                                                                                                                                                                                 MAIN         RW     T      1     100M       0B      1K         1H,93A                                                                                                                                                                                                                                                          3136320              3149119              N       
user_dbspace                                                                                                                     user_dbspace                                                                                                                     /data/sybase/iq1540/IQ-15_4/demo/user_dbspace.iq                                                                                                                                                                                                                 MAIN         RW     T      1     100M       0B      1K         1H,48A                                                                                                                                                                                                                                                          4181760              4194559              N       
IQ_SYSTEM_TEMP                                                                                                                   IQ_SYSTEM_TEMP                                                                                                                   iqdemo.iqtmp                                                                                                                                                                                                                                                     TEMPORARY    RW     T      3     25M        200M    1K         1H,64F,16A                                                                                                                                                                                                                                                      1                    3200                 N    

(6 rows)

Execution time: 0.415 seconds

(DBA)> quit

/* NOTE: sysfile could be different with  iqfile , please check iqfile !! */

sgsinvm0257:~/IQ-15_4/demo> dbisql -nogui -c "uid=DBA;pwd=sql;eng=sgsinvm0257_iqdemo"

(DBA)> select * from sysfile;
file_id file_name                                                                                                                                                                                                                                                        dbspace_name                                                                                                                     store_type lob_map                                                                                                                                                                                                                                                          dbspace_id
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0       /data/sybase/iq1540/IQ-15_4/demo/iqdemo.db                                                                                                                                                                                                                       system                                                                                                                           1          (NULL)                                                                                                                                                                                                                                                           0         
15      /tmp/.SQLAnywhere/sgsinvm0257_iqdemo/tmp/tmp_000000                                                                                                                                                                                                              temporary                                                                                                                        1          (NULL)                                                                                                                                                                                                                                                           15        
16384   iqdemo.iq                                                                                                                                                                                                                                                        IQ_SYSTEM_MAIN                                                                                                                   2          (NULL)                                                                                                                                                                                                                                                           16384     
16385   iqdemo.iqtmp                                                                                                                                                                                                                                                     IQ_SYSTEM_TEMP                                                                                                                   2          (NULL)                                                                                                                                                                                                                                                           16385     
16386   iqdemo.iqmsg                                                                                                                                                                                                                                                     IQ_SYSTEM_MSG                                                                                                                    2          (NULL)                                                                                                                                                                                                                                                           16386     
16387   iqdemo_main.iq                                                                                                                                                                                                                                                   iq_main                                                                                                                          2          (NULL)                                                                                                                                                                                                                                                           16387     
16388   /data/sybase/iq1540/IQ-15_4/demo/iqdemo_main2.iq                                                                                                                                                                                                                 iq_main                                                                                                                          2          (NULL)                                                                                                                                                                                                                                                           16387     
16389   /data/sybase/iq1540/IQ-15_4/demo/test_dbspace.iq                                                                                                                                                                                                                 test_dbspace                                                                                                                     2          (NULL)                                                                                                                                                                                                                                                           16388     
16390   /data/sybase/iq1540/IQ-15_4/demo/user_dbspace.iq                                                                                                                                                                                                                 user_dbspace                                                                                                                     2          (NULL)                                                                                                                                                                                                                                                           16389  

(9 rows)

Execution time: 0.452 seconds

(DBA)> quit


*Steps 2: shutdown existing IQ server
==========================================================

sgsinvm0257:~/IQ-15_4/demo> stop_iq

Checking system ...

The following 1 server(s) are owned by 'sybase'

## Owner          PID   Started  CPU Time  Additional Information
-- ---------  -------  --------  --------  ------------------------------------
1: sybase       11058     09:57  00:00:02  SVR:sgsinvm0257_iqdemo DB:iqdemo PORT:2638
              /data/sybase/iq1540/IQ-15_4/bin64/iqsrv15 @iqdemo.cfg iqdemo.db -gn 25 -o /data/sybase/iq1540/IQ-15_4/logfiles/sgsinvm0257_iqdemo.0031.srvlog -hn 5
--

        Please note that 'stop_iq' will shut down a server completely
        without regard for users, connections, or load process status.
        For more control, use the 'dbstop' utility, which has options
        that control stopping servers based on active connections.

Do you want to stop the server displayed above <Y/N>? y

Shutting down server (11058) ...
Checkpointing server (11058) ...
Server shutdown.

*Steps 3: start up utility_db server
==========================================================

sgsinvm0257:~/IQ-15_4/demo> start_iq -n utility_db


Starting server utility_db on sgsinvm0257 at port  (01/17 10:03:40)

Run Directory       : /data/sybase/iq1540/IQ-15_4/demo
Server Executable   : /data/sybase/iq1540/IQ-15_4/bin64/iqsrv15
Server Output Log   : /data/sybase/iq1540/IQ-15_4/logfiles/utility_db.0004.srvlog
Server Version      : 15.4.0.3027/ESD 3
Open Client Version : N/A
User Parameters     : '-n' 'utility_db'
Default Parameters  : -c 48m -gc 20 -gd all -gl all -gm 10 -gp 4096 -ti 4400 -gn 25

I. 01/17 10:03:42. Sybase IQ
I. 01/17 10:03:42. Version 15.4
I. 01/17 10:03:42. (64bit mode)
I. 01/17 10:03:42. Copyright 1992-2013 by Sybase, Inc. All rights reserved
I. 01/17 10:03:42. Copyright (c) 2001-2012, Sybase, Inc.
I. 01/17 10:03:42. Portions copyright (c) 1988-2012, iAnywhere Solutions, Inc. All rights reserved.
I. 01/17 10:03:42. Use of this software is governed by the Sybase License Agreement.
I. 01/17 10:03:42. Refer to http://www.sybase.com/softwarelicenses.
I. 01/17 10:03:42.
I. 01/17 10:03:42. Processors detected: 1
I. 01/17 10:03:42. Maximum number of physical processors the server will use: 1
I. 01/17 10:03:42. Running Linux 3.0.26-0.7-default #1 SMP Tue Apr 17 10:27:57 UTC 2012 (3829766) on X86_64
I. 01/17 10:03:42. Server built for X86_64 processor architecture
I. 01/17 10:03:42. 49152K of memory used for caching
I. 01/17 10:03:42. Minimum cache size: 49152K, maximum cache size: 184604K
I. 01/17 10:03:42. Using a maximum page size of 4096 bytes


=============================================================
IQ server starting with:
     10 connections         (       -gm )
     12 cmd resources       ( -iqgovern )
     76 threads             (     -iqmt )
    512 Kb thread stack size   (   -iqtss  )
  38912 Kb thread memory size ( -iqmt * -iqtss )
      1 IQ number of cpus  ( -iqnumbercpus )
      0 MB maximum size of IQMSG file ( -iqmsgsz )
      0 copies of IQMSG file archives ( -iqmsgnum )
=============================================================

I. 01/17 10:03:42. Database server started at Fri Jan 17 2014 10:03
I. 01/17 10:03:42. Trying to start SharedMemory link ...
I. 01/17 10:03:42.     SharedMemory link started successfully
I. 01/17 10:03:42. Trying to start TCPIP link ...
I. 01/17 10:03:42. Starting on port 2638
I. 01/17 10:03:47.     TCPIP link started successfully
I. 01/17 10:03:47. Now accepting requests
New process id is 11452

Server started successfully

*Steps 4: edit restore script, need to direct all dbspace/dbfile/iqmsg to '/demotest' directory
============================================================================================

sgsinvm0257:~/IQ-15_4/demo> cat testrestore.sql

restore database '/data/sybase/iq1540/IQ-15_4/demotest/iqdemo.db'
from '/data/sybase/iq1540/IQ-15_4/demotest/iqdemo.dmp'
RENAME IQ_SYSTEM_MAIN TO '/data/sybase/iq1540/IQ-15_4/demotest/iqdemo.iq'
RENAME IQ_SYSTEM_TEMP TO '/data/sybase/iq1540/IQ-15_4/demotest/iqdemo.iqtmp'
RENAME iq_main TO '/data/sybase/iq1540/IQ-15_4/demotest/iqdemo_main.iq'
RENAME iq_main2 TO '/data/sybase/iq1540/IQ-15_4/demotest/iqdemo_main2.iq'
RENAME filehist1 TO '/data/sybase/iq1540/IQ-15_4/demotest/test_dbspace.iq'
RENAME user_dbspace TO '/data/sybase/iq1540/IQ-15_4/demotest/user_dbspace.iq'
RENAME IQ_SYSTEM_MSG TO '/data/sybase/iq1540/IQ-15_4/demotest/iqdemo.iqmsg'

*Steps 5: execute restore script
==========================================================

sgsinvm0257:~/IQ-15_4/demo> dbisql -nogui -c "uid=DBA;pwd=sql;dbn=utility_db" read testrestore.sql
Execution time: 11.997 seconds

*Steps 6: review /demotest directory , if there is file restored back
===============================================================

sgsinvm0257:~/IQ-15_4/demo> cd ../demotest
sgsinvm0257:~/IQ-15_4/demotest> ls -al
total 728760
drwxr-xr-x  2 sybase users       4096 Jan 17 10:05 .
drwxr-xr-x 17 sybase sybase      4096 Jan 17 08:29 ..
-r--r--r--  1 sybase users    3698688 Jan 17 10:05 iqdemo.db
-rw-r--r--  1 sybase users   86406972 Jan 17 08:32 iqdemo.dmp.1
-rw-r--r--  1 sybase users  209715200 Jan 17 10:05 iqdemo.iq
-rw-r--r--  1 sybase users       4833 Jan 17 10:05 iqdemo.iqmsg
-rw-r--r--  1 sybase users   26214400 Jan 17 10:05 iqdemo.iqtmp
-rw-r--r--  1 sybase users       2286 Jan 17 10:05 iqdemo.lmp
-rw-r--r--  1 sybase users          0 Jan 17 10:05 iqdemo.log
-rw-r--r--  1 sybase users  104857600 Jan 17 10:05 iqdemo_main.iq
-rw-r--r--  1 sybase users  104857600 Jan 17 10:05 iqdemo_main2.iq
-rw-r--r--  1 sybase users  104857600 Jan 17 10:05 test_dbspace.iq
-rw-r--r--  1 sybase users  104857600 Jan 17 10:05 user_dbspace.iq

*Steps 7: shutdown utility_db
==========================================================

sgsinvm0257:~/IQ-15_4/demotest> stop_iq

Checking system ...

The following 1 server(s) are owned by 'sybase'

## Owner          PID   Started  CPU Time  Additional Information
-- ---------  -------  --------  --------  ------------------------------------
1: sybase       11452     10:03  00:00:07  SVR:utility_db DB:none PORT:
              /data/sybase/iq1540/IQ-15_4/bin64/iqsrv15 -n utility_db -c 48m -gc 20 -gd all -gl all -gm 10 -gp 4096 -ti 4400 -gn 25 -o /data/sybase/iq1540/IQ-15_4/
--

        Please note that 'stop_iq' will shut down a server completely
        without regard for users, connections, or load process status.
        For more control, use the 'dbstop' utility, which has options
        that control stopping servers based on active connections.

Do you want to stop the server displayed above <Y/N>? y

Shutting down server (11452) ...
Checkpointing server (11452) ...
Server shutdown.


*Steps 8: copy iqdemo.cfg to '/demotest' directory
==========================================================

sgsinvm0257:~/IQ-15_4/demotest> cp ../demo/iqdemo.cfg .
sgsinvm0257:~/IQ-15_4/demotest> ps -ef|grep iqsrv
sybase   11671  3188  0 10:07 pts/0    00:00:00 grep iqsrv


*Steps 9: under '/demotest' directory, start up iqdemo server
==========================================================

sgsinvm0257:~/IQ-15_4/demotest> start_iq @.cfg iqdemo.db


Starting server sgsinvm0257_iqdemo on sgsinvm0257 at port 2638 (01/17 10:07:31)

Run Directory       : /data/sybase/iq1540/IQ-15_4/demotest
Server Executable   : /data/sybase/iq1540/IQ-15_4/bin64/iqsrv15
Server Output Log   : /data/sybase/iq1540/IQ-15_4/logfiles/sgsinvm0257_iqdemo.0032.srvlog
Server Version      : 15.4.0.3027/ESD 3
Open Client Version : N/A
User Parameters     : '@iqdemo.cfg''iqdemo.db'
Default Parameters  : -gn 25

I. 01/17 10:07:34. Sybase IQ
I. 01/17 10:07:34. Version 15.4
I. 01/17 10:07:34. (64bit mode)
I. 01/17 10:07:34. Copyright 1992-2013 by Sybase, Inc. All rights reserved
I. 01/17 10:07:34. Copyright (c) 2001-2012, Sybase, Inc.
I. 01/17 10:07:34. Portions copyright (c) 1988-2012, iAnywhere Solutions, Inc. All rights reserved.
I. 01/17 10:07:34. Use of this software is governed by the Sybase License Agreement.
I. 01/17 10:07:34. Refer to http://www.sybase.com/softwarelicenses.
I. 01/17 10:07:34.
I. 01/17 10:07:34. Processors detected: 1
I. 01/17 10:07:34. Maximum number of physical processors the server will use: 1
I. 01/17 10:07:34. Running Linux 3.0.26-0.7-default #1 SMP Tue Apr 17 10:27:57 UTC 2012 (3829766) on X86_64
I. 01/17 10:07:34. Server built for X86_64 processor architecture
I. 01/17 10:07:34. 49152K of memory used for caching
I. 01/17 10:07:34. Minimum cache size: 49152K, maximum cache size: 184604K
I. 01/17 10:07:34. Using a maximum page size of 4096 bytes


=============================================================
IQ server starting with:
     10 connections         (       -gm )
     12 cmd resources       ( -iqgovern )
     76 threads             (     -iqmt )
    512 Kb thread stack size   (   -iqtss  )
  38912 Kb thread memory size ( -iqmt * -iqtss )
      1 IQ number of cpus  ( -iqnumbercpus )
      0 MB maximum size of IQMSG file ( -iqmsgsz )
      0 copies of IQMSG file archives ( -iqmsgnum )
=============================================================

I. 01/17 10:07:34. Starting database "iqdemo" (/data/sybase/iq1540/IQ-15_4/demotest/iqdemo.db) at Fri Jan 17 2014 10:07
I. 01/17 10:07:35. Transaction log: iqdemo.log
I. 01/17 10:07:35. Starting checkpoint of "iqdemo" (iqdemo.db) at Fri Jan 17 2014 10:07
I. 01/17 10:07:35. Finished checkpoint of "iqdemo" (iqdemo.db) at Fri Jan 17 2014 10:07
I. 01/17 10:07:36. Database "iqdemo" (iqdemo.db) started at Fri Jan 17 2014 10:07
I. 01/17 10:07:36. IQ Server sgsinvm0257_iqdemo.
I. 01/17 10:07:36. Database server started at Fri Jan 17 2014 10:07
I. 01/17 10:07:36. Trying to start SharedMemory link ...
I. 01/17 10:07:36.     SharedMemory link started successfully
I. 01/17 10:07:36. Trying to start TCPIP link ...
I. 01/17 10:07:36. Starting on port 2638
I. 01/17 10:07:41.     TCPIP link started successfully
I. 01/17 10:07:41. Now accepting requests
New process id is 11824

Server started successfully