SAP Sybase IQ: How to Restore Your Backups to Another system
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.
Great instructions Jason.
They work perfectly. Thanks
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
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
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.
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
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
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,