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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
5 | |
5 | |
5 | |
5 | |
4 | |
4 | |
4 | |
4 | |
3 | |
3 |