VIR01 Background Information
What's in VIR01?
There are a number of tables defined, but the ones that seem to actually have data are:
Result Set table
Parameters table (see below)
Result Set table
I'm not sure why there are multiple files for result sets and session tables. I think the three session tables are for GUI, WWW and ??? types of sessions, but I'm not sure.
The parameters table can be viewed using UTIL-G-2 and will look something like this:
Sequence Name Value Suppress Type Prefix
-------------------- --------- -------- ---- -------
1. change-file-name 0 y S
2. find-group 0 n S
3. last-acc-number 0 S
4. last-doc-number 0 y S
5. last-file-number 0 y S vir01-
6. last-word-number 0 y S
7. last-z34-sequence 0 y S
8. library-lock-status 0 n S
9. max-z02-count 0 y S
10. session-id 434 n S
The values will vary, of course.
The daily routine to clear VIR01 basically empties out all the files except Z52, which it reloads from a file which has all the parameters listed with values of zero. In an emergency, you could retype Z52 using one of the UTIL G utilities.
What to do with VIR01 problems. Case 1: Z52 problems - manifests as problems with the number of sessions (usually GUI sessions). Case 2: OPAC simply stops working; lots of "Oracle errors" in the www_server log for z05 and z63 (for example). Case 3: License Limit Exceeded for GUI users. ****See end of file for V18 ExLibris KB # 5711 that helped on 5/26/2009.****
Cases detailed below:
1. Z52 problems - manifests as problems with the number of sessions (usually GUI sessions).
2. OPAC simply stops working; lots of "Oracle errors" in the www_server log for z05 and z63 (for example).
3. GUI users get "License Limit" exceeded.
First, look at the most recent clear_vir01 log:
aleph@aleph-app-01(a21_1) DUK50 >>cd $alephe_scratch
aleph@aleph-app-01(a21_1) DUK50 >>ls -lt vir01_clear_vir01.* | more
-rw-rw-r-- 1 aleph exlibris 13001 Jul 21 06:00 vir01_clear_vir01.00833 -rw-rw-r-- 1 aleph exlibris 12650 Jul 20 06:00 vir01_clear_vir01.00832 ...
aleph@aleph-app-01(a21_1) DUK50 >>more vir01_clear_vir01.00833
You may want to compare to an older log to see what a normal run looks like.
Case 1: Z52 problems, for example
SQL-ALEPH_ADMIN> 06:00:26 SQL-ALEPH_ADMIN> CREATE UNIQUE INDEX VIR01.Z52_ID ON VIR01.Z52 (Z52_REC_KEY ASC) * ERROR at line 1: ORA-00955: name is already used by an existing object
(line break inserted for readablity)
The Z52 reload is the last step, so if we can fix this, all should be well. Another way to see if the VIR01 Z52 is okay is too change libraries to VIR01, then try Util-G-2. If you get a bunch of error messages, then we have z Z52 problem.
Subcase 1a: The Z52 file is okay, but the index is unusable. First, check if the Z52 file has good data in it. Try this:
aleph@aleph-app-01(a21_1) DUK50 >>s+ vir01
SQL*Plus: Release 126.96.36.199.0 - Production on Fri Jul 21 14:36:20 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 188.8.131.52.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 184.108.40.206.0 - Production SQL-VIR01> select z52_rec_key from z52;
10 rows selected.
If you get results like above, the file has good data in it. Next, check if the index is okay. Switch to library VIR01, then use Util-A-17-14 to check the index:
Enter Table Name : z52 make_library_file_list: env file_list_size does not exists. Using data_root/file_list as is Defined in file_list: __________________________________________________________ IND z52_id 10K 10K ts1 __________________________________________________________ Exist in the Database: INDEX_NAME STATUS INDEX_TYPE UNIQUENESS COLUMN_NAME --------------- ------- ---------- ----------- -------------------- Z52_ID VALID NORMAL UNIQUE Z52_REC_KEY
This what it looks like normally. If the Z52_ID line says status "unusable", then the index needs to be rebuilt.
Solution 1a: Rebuild the index.
UTIL-A-17-4 (Drop index), enter Z52_ID and confirm
UTIL-A-17-2 (Create index), enter Z52_ID and confirm
Case 2: OPAC simply stops working; lots of "Oracle errors" in the www_server log for z05 and z63 (for example). The clear_vir01 log showed that the drop and rebuild for z05 and z63 failed because the tables were in use.
Solution 2: Run Util-X-8 in "hot" mode, deleting all but the last 1 day. The first step takes a while to run - be patient.
Case 3: GUI users get "License Limit exceeded". There was an error with z65 in the vir01 log. Doing a hot clear-vir01 (Util-x-8), keeping 0 days, resolves the problem briefly, but it quickly recurs. (To monitor the license limit, do this: cd $LOGDIR; grep 'GUI users' pc_ser_6991.log) The problem is that the index for the z65 table isn't functioning, so every PC transaction causes a new "user" to be added to the table, instead of matching an existing user. The only solution is to drop and recreate the z65 table and index, which the cold clear_vir01 does, but the hot does not.
Solution 3: Stop the PC server and the WWW server, run clear-vir01 in cold mode, start the PC server and the WWW server.
Last case: No other ideas, let's just rerun the clear-vir01 job (Util-X-8). This has two options, hot and cold. The cold option is the same as the daily run. The hot option doesn't drop and rebuild Oracle tables. Instead it deletes records older than X number of days (you supply the X) from all the tables except Z52. It doesn't do anything to Z52. You can run the hot option anytime. If you want to run the cold option, you need to shutdown a bunch of stuff first, then restart them afterwards. According to the Ex Libris Knowledge Base, you need to stop the www_server and pc_server. When I've run it manually (successfully), I've also stopped (and later restarted) all ue* processes for all libraries, the batch queues for all libraries, plus the job daemon. That takes some doing!
Vir-01 had issues again this morning. We followed the steps below to rectify the situation.
KB 5711: "Can't log into Web, "unique constraint (VIR01.Z63_ID) violated" *MASTER RECORD*"
The z63 is the Web OPAC session-id table. The z63 unique-constraint error occurs when the system tries to write a z63 record with the same key as an existing one.
The vir01 util g/2 "session-id" counter is used to make the z63 session-id unique.
In this case, util g/2 was blank. This indicates that the vir01 z52 table is missing. (Probably because the z52 step of the previous night's clear_vir01 failed for some reason.)
Check the vir01/$data_files to see if there's a z52.init file. (This file is used by clear_vir01 to initialize the z52.) If not and if there *is* a z52.seq file, then copy the z52.seq to z52.init.
(1) rerun clear_vir01 or
(2) do p_file_04 to load the vir01 z52 table:
csh -f $aleph_proc/p_file_04 VIR01,z52,replace > & $alephe_scratch/p_file_04_vir01_z52.log &
followed by util a/17/1 for the vir01 z63 .
Error from 11/01/09 JF
After dropping a table in the clear VIR01 you get this error message:
ERROR at line 1:
ORA-00955: name is already used by an existing object
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
In this case it was VIR.Z05 (causing strange search results). When looking at the tables in VIR01, table Z05 was missing and trying to create it caused the same errors. Too fix this the Oracle DBA had to bounce the database. After the Oracle reboot clear vir01 works.
Reviewed and updated: Aug 15, 2017 KHN
Moved into SharePoint: Nov 02, 2009