Skip to Content

SAP Sybase IQ: How to Restore Your Backups to Another systemJason L. Froebe – Tech tips and How Tos for Fellow Techies |…

SAP/Sybase’s documentation isn’t very clear for new IQ dbas and developers. Once such item is simply restoring an IQ database on to another system. Unlike ASE, you need to specify the new file locations if they are different than the source server.

Assumptions:

1. IQ software has been installed

2. The new dbfile locations are symbolic links to raw partitions OR the path exists but not the files

3. You have a valid SYSAM license for the new IQ instance.

4. The new IQ instance name is set (via -n instance)

5. The old directory for the .db, .log and .mir exists (use a symbolic link if you wish)

Obtain dbspace file names with sp_iqfile:

select DBFileName, Path, DBFileSize from sp_iqfile();

DBFileName Path DBFileSize

‘IQ_SYSTEM_MAIN’ ‘/dba/syb/old_iq/sybdev/IQ_MAIN/old_iqmain001.iq’ ’32G’

‘IQ_USER_MAIN_FILE_01’ ‘/dba/syb/old_iq/sybdev/IQ_USER_MAIN/old_iqusermain001.iq’ ‘1024G’

‘IQ_SYSTEM_TEMP’ ‘/dba/syb/old_iq/sybdev/IQ_TEMP/old_iqtemp001.iqtmp’ ’32G’

‘IQ_SYSTEM_TEMP_002’ ‘/dba/syb/old_iq/sybdev/IQ_TEMP/old_iqtemp002.iqtmp’ ’32G’

Create a restore.sql file renaming the DBFileName to the new locations:

restore database ‘new_iq’

FROM ‘/dba/backup/sybbackup/old_iq.20140423100111.17760.IQfullbkp’

RENAME IQ_SYSTEM_MAIN TO ‘/dba/syb/new_iq/sybdev/IQ_MAIN/new_iqmain001.iq’

RENAME IQ_SYSTEM_TEMP TO ‘/dba/syb/new_iq/sybdev/IQ_TEMP/new_iqtemp001.iq’

RENAME IQ_SYSTEM_TEMP_002 TO ‘/dba/backup/sybbackup/new_iqtemp002.iq’

RENAME IQ_SYSTEM_MSG TO ‘/dba/syb/new_iq/instlog/new_iq.iqmsg’

RENAME IQ_USER_MAIN_FILE_01 TO ‘/dba/syb/new_iq/sybdev/IQ_USER_MAIN/new_iqusermain001.iq’;

Stop the destination IQ instance if it is running and start the utility database:

$ stop_iq

Checking system …

The following 1 server(s) are owned by ‘sybdba’

## Owner          PID   Started  CPU Time  Additional Information

— ———  ——-  ——–  ——–  ————————————

1: sybdba       13909     Apr24  00:43:46  SVR:new_iq DB:new_iq PORT:58116

              /dba/syb/new_iq/sybase/IQ-16_0/bin64/iqsrv16 @/dba/syb/new_iq/sybdb/new_iq.cfg /dba/syb/new_iq/sybdb/new_iq.db -gn 65 -o /dba/syb/new_iq/sybase/IQ-16_0/logfiles/

${SYBASE}/IQ-16_0/bin64/start_iq -n utility_db -gu dba -c 48m -gc 20 -iqgovern 30 \

        -gd all -gl all -gm 10 -gp 4096 -ti 4400 -z -zr all -zo $SYBASE/IQ-16_0/logfiles/utility_db.out \

        -o $SYBASE/IQ-16_0/logfiles/utility_db.srvlog -iqmc 100 -iqtc 100 -x “tcpip{port=9000}”

Starting server utility_db on localhost at port 9000 (04/30 09:37:16)

Run Directory       : /dba/syb/new_iq/sybdb

Server Executable   : /dba/syb/new_iq/sybase/IQ-16_0/bin64/iqsrv16

Server Output Log   : /dba/syb/new_iq/instlog/utility_db.srvlog

Server Version      : 16.0.0.653/sp03 16.0.0/Linux 2.6.18-194.el5

Open Client Version : N/A

User Parameters     : ‘-n’ ‘utility_db’ ‘-gu’ ‘dba’ ‘-c’ ’48m’ ‘-gc’ ’20’ ‘-iqgovern’ ’30’ ‘-gd’ ‘all’ ‘-gl’ ‘all’ ‘-gm’ ’10’ ‘-gp’ ‘4096’ ‘-ti’ ‘4400’ ‘-z’ ‘-zr’ ‘all’ ‘-zo’ ‘/dba/syb/new_iq/instlog/utility_db.out’ ‘-o’ ‘/dba/syb/new_iq/instlog/utility_db.srvlog’ ‘-iqmc’ ‘100’ ‘-iqtc’ ‘100’ ‘-x’ ‘tcpip{port=9000}’

Default Parameters  : -gn 25

….

Remove the db, log and mir files:

$ rm instance.db instance.log instance.mir

Restore the full backup:

dbisql -c “uid=dba;pwd=sql;eng=utility_db;dbn=utility_db” -port 9000 -host $( hostname ) -nogui “restore.sql”

Restore the incremental backup(s):

dbisql -c “uid=dba;pwd=sql;eng=utility_db;dbn=utility_db” -port 9000 -host $( hostname ) -nogui “restore_incrementals.sql”

Stop the utility database:

$ stop_iq

Start the IQ server to ensure it comes up then shut it back down.

If the name of the server has changed (e.g. old_iq -> new_iq), then we need to update the log and mir files. First let’s find out where the log and mir files are currently set to in the db file:

$ dblog new_iq.db

SQL Anywhere Transaction Log Utility Version 16.0.0.653

“new_iq.db” is using log file “/dba/syb/old_iq/sybdb/old_iq.log”

“new_iq.db” is using log mirror file “/dba/syb/old_iq/sybdb/old_iq.mir”

Transaction log starting offset is 0702994164

Transaction log current relative offset is 0000397583

Set the log file to “new_iq.log”:

$ dblog -t new_iq.log new_iq.db

SQL Anywhere Transaction Log Utility Version 16.0.0.653

“new_iq.db” was using log file “/dba/syb/old_iq/sybdb/old_iq.log”

“new_iq.db” is using log mirror file “/dba/syb/old_iq/sybdb/old_iq.mir”

“new_iq.db” is now using log file “new_iq.log”

Transaction log starting offset is 0702994164

Transaction log current relative offset is 0000397625

We need to clear the mir file(s) before we can assign a new one:

$ dblog -r new_iq.db

SQL Anywhere Transaction Log Utility Version 16.0.0.653

“new_iq.db” is using log file “new_iq.log”

“new_iq.db” was using log mirror file “/dba/syb/old_iq/sybdb/im00.mir”

“new_iq.db” is now using no log mirror file

Transaction log starting offset is 0702994164

Transaction log current relative offset is 0000397625

Set the mir file:

$ dblog -m new_iq.mir new_iq.db

SQL Anywhere Transaction Log Utility Version 16.0.0.653

“new_iq.db” is using log file “new_iq.log”

“new_iq.db” was using no log mirror file

“new_iq.db” is now using log mirror file “new_iq.mir”

Transaction log starting offset is 0702994164

Transaction log current relative offset is 0000397625

Start your IQ instance.

To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

  1. Eric Campbell

    Hi Jason

    I have a database in which the DBspaces were allocated on file systems and I backed up this database and restored in on a new database with DBspaces allocated on raw partitions.  Everything worked fine except my raw partitions are not fully used.

    As an example one of the files on filesystem was 350 GB but the equivalent raw partition was 500GB.  When the restore was completed the size of this IQ file was 350GB.  So 150 GB on my raw disk is wasted.

    Is there a safe way to extend this file to use up the additional 150GB on the raw device?  Something equivalent to disk reinit in ASE?

    Your response is appreciated.

    -Eric

    (0) 
    1. Chris Baker

      If you provided sufficient reserve when you created the dbspace dbfile device using the RESERVE option and size to allow growth, then you can grow the existing device to fit the space available up to the maximum provided in the original SIZE + RESERVE.

      ALTER DBSPACE <dbspace>
      ALTER FILE <dbfile> ADD 150GB

      Otherwise, you will have to alter the file to read-only, relocate the data to other devices in the dbspace and drop the dbfile.  Then recreate it to the new size.

      Chris

      (0) 
      1. Eric Campbell

        Thanks for your response.

        I thought if you use a raw partition for the file then you don’t have to specify size.  The size of the raw device would be the default value.

        There was no RESERVE clause used when the DBspace was created.

        I don’t think I have the space available to “relocate the data to other devices in the dbspace..”

        I’m looking at over 650GB of unused space over 4 raw devices.

        (0) 
        1. Chris Baker

          You are correct that all the raw device size will be used.  The RESERVE option is still available, however, for just such a possibility that you have hit.

          Unfortunately, then, you will have to find some way to relocate data off the devices one-at-a-time while you recreate them.  Perhaps you can drop some indexes on the tables (e.g. HG) while you do the intial relocation.

          Once you have some space back you can recreate the indexes while you relocate the rest.

          Chris

          (0) 
  2. Kumar Rajesh

    Hi Jason,

    Thanks for information, can you guide me how to retain security data of original system, we are facing on challenge here.

    we restored to our test environment from PROD backup, once restored we see that all the objects are owned by prod user which is expected with restore but how we can retain the all DB users which were there in test environment before restore and how we can change the owner of the tables and views.



    Thanks,

    Rajesh

    (0) 
  3. ranga nadig

    Hello Experts ,

    we are using the SAPIQ as NLS solution .

    we are have restored the SAPIQ system from source system to target  .

    i used the below command to change the schema

    select ‘alter table “‘ || suser_name(creator) || ‘”.”‘ || table_name || ‘” alter owner to “SAPXXX”‘ from systab where suser_name(creator) = ‘SAPXXX’ and table_type_str = ‘BASE.

    but when i check the table all the tables are owned by the source schems ,

    the above command i used , is it correct command ?

    please can you help me

    Regards,

     

    (0) 

Leave a Reply