Skip to Content
SAP IQ

Restore Coordinator On Different Location

Tags:

Following are the steps to be followed,  for restoring IQ 15.x and  16.x  MPX coordinator server on different host machine:

  1. Take  full backup of  the Cordinator server wth DBA authority.

 

  1> backup database to /usr/sybase/local/mpx/iqdemo.dmp'

  2>   go

 

Note: Database can also be backup using mulitiple stripes, For complete syntax check

   http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc01759.1601/doc/pdf/iqbackup.pdf

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00801.1510/pdf/iqrefso.pdf

 

2. To restore own all the servers (coordinator and all econdary nodes) in multiplex.  Make sure backup   files   are  moved to  another location

in binary mode.

   Note: If restoring backup from production coordinator to DR site, and it is not possible to shutdown MPX on production, then on DR host, utility_db can be
started with coordinator server name(production coordinator server name) with startup parameter, DoBroadcast=NO

  4.  If restoring to different location, it is  better  to have directory structure  same on target site as  on the source site, if not,  then create a symbolic link  with exactly same name as source on target host.

 

For Example

Source Directory
(iq154w)

/usr/sybase/rel154_iq/IQ-15_4/demo/mpx

   Temp files for IQ_SYSTEM_TEMP:

 

/usr/sybase/rel154_iq/IQ-15_4/demo/tempdev

 

Target restore directory:

   /usr/sybase/rel154_iq/IQ-15_4/demo/mpx6

 

Target restore temp file directory:

 

/usr/sybase/rel154_iq/IQ-15_4/demo/mydev

 

Setup Symbolic links:

   ln -s /usr/sybase/rel154_iq/IQ-15_4/demo/mpx6 /usr/sybase/rel154_iq/IQ-15_4/demo/mpx

ln -s /usr/sybase/rel154_iq/IQ-15_4/demo/mydev /usr/sybase/rel154_iq/IQ-15_4/demo/tempdev

 

4.Change  to restore directory

    sybase@myhost mpx]$ pwd

    /usr/sybase/rel154_iq/IQ-15_4/demo/mpx6

 

5. Start the utility_db using the coordinator server name or new server name

 

[sybase@myhost mpx]$ cat iqrestore.cfg

# iqdemo.cfg

#
------------------------------------------------------------

  # Default startup parameters for the ASIQ demo database

  # ------------------------------------------------------------

 

-n  iq154w

-x  tcpip{port=8999}

 

# The following parameters are also found in the configuration file

# /usr/sybase/rel154_iq/IQ-15_4/scripts/default.cfg.  Any parameters not specified below

# and not in the start up parameter list, will be added by start_iq

# using default.cfg as a guide.

   -c  48m

  -gc 20

  -gd all

  -gl all

  -gm 10

  -gp 4096

  -iqmc 100

  -iqtc 100

6. connect to utility_db and run restore command 

[sybase@myhost mpx]$ cat restore.sql

restore database '/usr/sybase/rel154_iq/IQ-15_4/demo/mpx6/iqdemo.db'

  from '/redeye2/sbagai/rel154_iq/IQ-15_4/demo/mpx6/iqdemo.dmp'

  ;

 

[sybase@redeye mpx]$ dbisql -c 'uid=DBA;pwd=sql;eng=iq154w;dbn=utility_db' -host myhost -port 8999 -nogui restore.sql

Execution time: 23.485 seconds

[sybase@mynost mpx]$

7. Shutdown utility_db

[sybase@redeye mpx]$ stop_iq

8. Make sure dbfile  for IQ_SYSTEM_TEMP exist as before and or files of the correct size, check with cksum , if you have issues with temporary dbfile,  then start
coordinator with –iqnotemp <size> switch in addition to –iqmpx_sn 1 and iqmpx_ov 1 switches.

9.  Start coordinator server with single node(–iqmpx_sn 1) and override (iqmpx_ov 1 )switches

  [sybase@myhost mpx]$ cat iqdemoov.cfg

# iqdemo.cfg

#
------------------------------------------------------------

# Default startup parameters for the ASIQ demo database

#-----------------------------------------------------------

-n  iq154w

-x  tcpip{port=8999}

 

# The following parameters are also found in the configuration file

# /usr/sybase/rel154_iq/IQ-15_4/scripts/default.cfg.  Any parameters not specified below

# and not in the start up parameter list, will be added by start_iq

# using default.cfg as a guide.

  -c  48m

  -gc 20

  -gd all

  -gl all

  -gm 10

  -gp 4096

  -iqmc 100

  -iqtc 100

-iqmpx_sn 1

  -iqmpx_ov 1

  [sybase@myhost mpx]$ start_iq @iqdemoov.cfg iqdemo.db

10.  Drop all the secondary nodes, using drop multiplex server statement

 

[sybase@myhost  mpx]$ iisql -UDBA -Psql -Siq154w

  1> drop multiplex server iq154q

  2> go

11. Once last secondary node is dropped, coordinator shuts down automatically,  signifying conversion to simplex.

12.  Restart the coordinator without single node or override switches(-iqmpx_sn 1, -iqmpx_ov)

    

        [sybase@myhost mpx]$ start_iq @iqdemo.cfg iqdemo.db

13.  Recreate all the secondary nodes with the correct location path manually/ Sybase central/Sybase Control Center

  [sybase@myhost q1]$ isql -UDBA -Psql -Siq154w

     1> create multiplex server iq154q

           2> database '/usr/sybase/rel154_iq/IQ-15_4/demo/mpx6/q1/iqdemo.db'

           3> host 'myhost' port 9001

           4> Role WRITER STATUS INCLUDED

       5> go

   After first secondary node is created, the coordinator server automatically shuts down, signifying conversion to multiplex.

   Note: In “create multiplex server” command you must provide database file extension .db

14.  Restart the coordinator server

    [sybase@redeye mpx]$ start_iq @iqdemo.cfg iqdemo.db

   When Coordinator server is started you will see warning, this warning can be ignored, because IQ_SYSTEM_TEMP dbspace doesn’t contain files, This is true for
all secondary nodes created in this step.

 

        I. 01/06 19:46:36. Finished checkpoint of iqdemo" (iqdemo.db) at Wed Jan 06 2012 19:46

        I. 01/06 19:46:38. Database "iqdemo" (iqdemo.db) started at Wed Jan 06 2012 19:46

        I. 01/06 19:46:38. IQ Multiplex Coordinator Server iq154w.

        I. 01/06 19:46:38. Database server started at Wed Jan 06 2012 19:46

        I. 01/06 19:46:38. Trying to start SharedMemory link ...

        I. 01/06 19:46:38.     SharedMemory link started successfully

        I. 01/06 19:46:38. Trying to start TCPIP link
        ...

        I. 01/06 19:46:38. WARNING : Multiplex environment incorrect for this server

        I. 01/06 19:46:38. Please connect and run procedure sp_iqmpxvalidate for help

        I. 01/06 19:46:43.     TCPIP link started successfully

        I. 01/06 19:46:43. Now accepting requests

     

                 New process id is 15948

                Server started successfully

15. synchronize secondary nodes(if you checked ‘Generate Admin Scripts’ when creating the mpx server in Sybase central, then you can run sync_server script instead of dbbackup

             /usr/sybase/rel154_iq/IQ-15_4/demo/mpxbk

           [sybase@myhost mpxbk]$ cd q1

           [sybase@myhost q1]$ ./sync_server

           SQL Anywhere Backup Utility Version 12.0.1.6567

        (724 of 722 pages, 100% complete)

           Database backup completed

          SQL Anywhere Transaction Log Utility Version 12.0.1.6537

         "/myhost/sybase/rel154_iq/IQ-15_4/demo/mpx/q1/iqdemo.db" was using log file "iqdemo.log"

          Transaction log filename not changed

        "/myhost/sybase/rel154_iq/IQ-15_4/demo/mpx/q1/iqdemo.db" was using no log mirror file

         Transaction log mirror filename not changed

         Transaction log starting offset is 0001060521

     Transaction log current relative offset is 0000006233

      Or   you can use following steps to synchronize secondary nodes:

    Chapter 7 : Backing Up and Restoring Data in a Multiplex Environment

   

       http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc01839.1601/doc/pdf/iqmpx.pdf

              a.  Remove the .LOG transaction log file in the secondary server directory.
             b. Back up the catalog store to   the directory where the secondary server runs:
c.  dbbackup -y -d -c "uid=dba;pwd=sql;links=tcpip{port=2763};eng=mpxnode_c" /host1/mpx/

             d. Set the log file path:

e.  dblog -r -t mpxtest.log mpxtest.db 
             f. Start the secondary node using  the start_iq command.

             g. Repeat the previous steps for   each secondary server in the multiplex.

  16.  Start secondary nodes

    [sybase@myhost q1]$ start_iq @iqdemo.cfg iqdemo.db

17. Connect to each secondary node and add files to IQ_SYSTEM_TEMP

  [sybase@myhost  q1]$ isql -UDBA -Psql -Siq154q

   1> alter dbspace IQ_SYSTEM_TEMP ADD FILE
iqdemotmp '/myhost/sybase/rel154_iq/IQ-15_4/demo/mpx6/q1/iqdemotmp' size 300

   2> go

18. Connect to coordinator server and run sp_iqmpxvalidate, it should report ‘no error detected’

  

    [sybase@myhost q1]$ isql -UDBA -Psql -Siq154w

                      1> exec sp_iqmpxvalidate

                     2> go

                  Messages

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

                No errors detected

 

       (1 row affected)

              (return status = 0)

  19. Optional: If required, incremental backups from source coordinator server can be restored on target(DR) coordinator server(To restore using incremental backup follow step 2-18)

     

NOTE: Sometimes if after restore, before file has been added to secondary node, you try to create local temporary table, on coordinator node,  you will get
error message that IQ_SYSTEM_TEMP dbspace does not have dbfiles.

    1> DECLARE LOCAL TEMPORARY TABLE temp(col1 int)

    2> go

    Msg 21, Level 23, State 0:

   SQL Anywhere Error -1009128: The IQ_SYSTEM_TEMP dbspace does not have dbfiles for this server.

  -- (s_blockmap.cxx 3589)

   In that case, follow steps from 17-18