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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
34 | |
16 | |
15 | |
12 | |
12 | |
10 | |
9 | |
8 | |
8 | |
8 |