|
|
Cooperative Extension Service |
|
 |
||
|
|
||
|
Agricultural
Experiment Station |
||
 |
|
|
Topics:
Oracle and Banner freeze up
Posting document crashes posting (fgractg)
Posting (fgractg) - mandatory (NOT NULL) column is missing or NULL
during insert (on Journal Voucher F... documents)
Unable to extend table by x bytes, maximum number of extents exceeded
faainve screen - DUP_VAL_ON_INDEX
Tax processing set to N for invoice
DNNI records not fully posting
Document attempting to post to closed encumbrance (fgaencb)
Unable to locate an appropriate queue id. Approval process aborted. (forappl)
Another user is currently processing this employee. (phareds)
Invoice not showing up in approvals
PPAIDEN getting error on Biographic screen
Banner processes, sqlplus queries, backups, etc., slowing down dramatically
Running Posting (fgractg) and Approvals (forappl) manually
Reactivating terminated employees having old (terminated) home and distribution
org codes
Reversing a journal voucher by recreating fgbjvcd records from a document that
has posted to the fgbtrnh tables
fabmatc crashed with unique constraint violation for PK_FOBUAPP
Loading Web Tailor changes from one database to another
Focus Problems
MicroFocus COBOL Problems
Recovery of corrupted Oracle databases
Unix commands
Shutdown/Startup Banner
Web Info
Changing non-default OAS administrator password on the NT
Creating a database on the NT that hasn't been created before
Cloning a database (using import) on the NT that had been created before
Rebuilding PROD by importing a full database export file
Deleting a database and it's services on the NT
-----------------------------------------------------------------------------
Oracle and Banner freeze up
Current Banner and sqlplus sessions freeze up, and users trying
to log on get "ERROR: ORA-00257: archiver error. Connect internal
only, until freed."
Caused by disk containing archivelogs (/u01) filling up, which is
seen by "df -k" showing 100% used for /u01. Oracle stops until
there is room to continue writing archivelogs.
To remove old archivelogs in PROD (which is what usually fills up),
make sure those files have been written out to tape during the PROD
backup (log into userid oracle on AIX and check mail for latest
backup run log). Then, from userid oracle or root in AIX:
cd /u01/oradata/PROD/archivelogs
remove_old_logs.shl
enter the number of days to keep (at least 1 if the last backup
was done last night, or, at least 3 if this is Monday and the
last backup was done Friday night).
Make sure nothing else is running and filling up those archivelogs,
such as an errant posing document.
-----------------------------------------------------------------------------
Posting document crashes posting (fgractg) (or, see encumbrance crash below)
(or, see "mandatory (NOT NULL) column is missing or NULL during insert" below)
Monitor (monitor.lst) reports error on document to system console.
(The monitor scripts are in my Oracle Scripts collection.)
Usually caused by invalid or missing data entered into or generated
by a posting document.
Remove the document from the posting queue:
cd /home/dba_scripts
sqlplus
log in as system or as a dba user
@fixdoc.sql
enter document number given at end of monitor.lst, such as
I0015001 (document must begin with E, F, J, or I; otherwise,
fixdoc won't remove the document; there is also a fixwho.sql
script to just show who this document belongs to, without
removing the document.)
exit;
Report the errant document to the user that entered the document,
which is output by fixdoc, so that the user can fix the document
(we don't fix those documents, we just remove problem documents
from the posting queue and have the user fix them). (If the error
is "mandatory (NOT NULL) column is missing or NULL during insert"
on a Journal Voucher F document, see below on how the USER can fix
it.)
It may take 10 minutes or so for the error to stop showing up in
the monitor.lst file. Just delete monitor.lst again if the same
error for the same document shows up in those first few minutes;
otherwise, if it shows up later, the document was probably fixed
incorrectly.
Remove /home/jobsub/monitor.lst to stop reporting that error and
to start monitoring for subsequent errors.
-----------------------------------------------------------------------------
Posting (fgractg) - mandatory (NOT NULL) column is missing or NULL
during insert (on Journal Voucher F... documents)
Usually caused by HFRD or HGRS journal type entries (check both)
without encumbrance numbers, item numbers, and/or sequence numbers
for redistribution.
NOTE: The USER that entered this document must do the fix from Banner.
Go into form FGAJVCD, enter problem document number, next block,
next screen, F7, enter HFRD in Journal Type field, F8, scroll
through records until empty fields found on any of those three
entries and fix them (fill them in and save them). Do the same
for HGRS. (Whoever entered the document originally will have to
fix them, since they know what to enter into those fields.)
-----------------------------------------------------------------------------
Unable to extend table by x bytes, maximum number of extents exceeded
Caused by trying to add more records to a table than it can currently
hold, thus going over its allocation limit.
This can be temporarily fixed by changing the table's maximum number
of extents from 99 to 121:
sqlplus
log in as system or as a dba user or as the owner of the table
alter table owner.tablename storage (maxextents 121);
where owner.tablename is the owner and table name, such
as saturn.spriden
exit;
The permanent fix is to export, drop, recreate, and import the table,
which should be done by the DBA.
-----------------------------------------------------------------------------
faainve screen - DUP_VAL_ON_INDEX
(full message: INSERT_FROM_ENCUMB trigger raised unhandled
exception DUP_VAL_ON_INDEX)
Caused by trying to reuse an invoice number (such as invh_code
I0015001 - replace this value below with your invoice number)
after crashing out of faainve, or, trying to remove those invoice
records (Records | Remove twice in faainve), which left some
records for that invoice still in farinva (farinva_invh_code)
and/or farinvc (farinvc_invh_code).
Check to see what records are in farinva and farinvc for that
invoice number (this shows actual number, such as I0015001):
cd /home/dba_scripts
sqlplus
log in as system or as a dba user
select * from farinva where farinva_invh_code like '&invoice';
select * from farinvc where farinvc_invh_code like '&invoice';
Check to see if there are any other tables with that invh_code:
@fieldin.sql
INVH_CODE
C
I0015001
Make sure the invoice hasn't posted - shown by no records in
fgbtrnd or fgbtrnh tables for that invoice number:
select * from fgbtrnd where fgbtrnd_doc_code = '&invoice';
select * from fgbtrnh where fgbtrnh_doc_code = '&invoice';
If it has posted, you can't remove it. Otherwise, delete the
leftover invoice records:
delete from farinva where farinva_invh_code = '&invoice';
delete from farinvc where farinvc_invh_code = '&invoice';
exit;
Now, that invoice number can be reused.
-----------------------------------------------------------------------------
Tax processing set to N for invoice
Caused by tax group code (farinvc_tgrp_code) missing on the commodity
record. This occurs when someone starts an invoice (on FAAINVE) on a
general encumbrance or blanket purchase order, then leaves the document
without completing it. When they attempt to return to the document,
they will get this message.
The solution is to set the value of farinvc_tgrp_code equal to the value
of fabinvh_tgrp_code (setting the commodity record tax group code equal
to the header record tax group code for the same invoice).
sqlplus
log in as system or as a dba user or as the owner of the table
select farinvc_tgrp_code,fabinvh_tgrp_code from farinvc,fabinvh where
fabinvh_code = farinvc_invh_code and farinvc_invh_code = '&invoice';
update farinvc set farinvc_tgrp_code = (select fabinvh_tgrp_code from
fabinvh where fabinvh_code = farinvc_invh_code) where
farinvc_invh_code = '&invoice' and farinvc_tgrp_code is null;
If it changes more than one record, enter:
rollback;
to roll the change back, and check with the user to see if more than one
commodity record is involved. If so, and if the number of records changed
equals the number of records expected, do the update again. Otherwise,
just exit (exit;) without doing the update so that something else can be
done to fix the problem.
If it changes just one record, do a commit; and/or exit.
-----------------------------------------------------------------------------
DNNI records not fully posting (from Banner listserv: question from
Bruce Andrews, answer from Bill Gourlie)
In our attempts to post a manual check relating to payroll expenses/
liabilities through accounts payable, we discovered the following:
1. The expenses posted properly
2. The liabilities did not post relating to DNNI; the INNI portion
did post
3. The check did not post
4. The system did not generate an error message indicating problems in
posting/completion
The Rule Codes used for INNI are G010 & G015
The Rule Codes used for DNNI are G010, G015, G020, G022, G027,and G073
This problem has been inconsistent in nature. Some checks have posted
properly.
Additionally, it appears to have happened in other areas. These checks
only account for approximately 70% of the total credit balance in the
accrued liabilities total. Thus, indicating other areas where this
problem exists and has not been detected.
I suspect the invoice includes a record with amount .00, which DNNI will
not post. Try enabling the "Allow Zero Posting" checkbox on the Rule
Process codes. (We turn them back off so that we don't get .00 records
for all checks.)
-----------------------------------------------------------------------------
Document attempting to post to closed encumbrance (fgaencb)
Find which close encumbrance is being posted to by the document:
grep J0001597 *.lis
Shows:
27330.lis:Starting document J0001597 (Document type 20 ) at 15-JUN-99 05:18:09
27330.lis:Document J0001597 is attempting to post to closed encumbrance E0000941
, item 0 , sequence 1
Change fgbencd statuses back to O (Open) for the closed encumbrance:
update fgbencd set fgbencd_status = 'O'
where fgbencd_num = 'E0000941' and fgbencd_status = 'C';
-----------------------------------------------------------------------------
Unable to locate an appropriate queue id. Approval process aborted. (forappl)
Full message in forappl log (like 15204.lis):
Starting document M0000135 document type number 60 change sequence number
submission number
Unable to locate an appropriate queue id. Approval process aborted.
item number : 1 : sequence number : 1 :
-- or --
Starting document M0000213 document type number 60 change sequence number
submission number
Rule group must exist on Rule Group/Rule Class Maintenance Form.
Rule class = WRIT
Approvals are turned off for fixed assets, but end up in approval queue
anyway. Document numbers starting with M.
Change completed indicator and approved indicator in the Adjustment to
Fixed Assets Header Table (ffbadjh) to No and delete its records from
the Unapproved Document Table (fobuapp):
select ffbadjh_complete_ind,ffbadjh_appr_ind from ffbadjh
where ffbadjh_code = 'M0000135';
select * from fobuapp where fobuapp_doc_code = 'M0000135';
update ffbadjh set ffbadjh_complete_ind = 'N', ffbadjh_appr_ind = 'N'
where ffbadjh_code = 'M0000135';
delete from fobuapp where fobuapp_doc_code = 'M0000135';
commit;
Then, have the user go into FFAADJF, enter the document code (M0000135),
ctrl-pgdn twice, click on ">" button, and click on Complete button. Says
sent to approval queue, but, bypasses forappl (automatically approved,
doesn't go through forappl).
-----------------------------------------------------------------------------
Another user is currently processing this employee. (phareds)
This occurs when a user crashed out of the phareds form earlier, leaving
records for that session in the temporary phrreds work table used by the
form. The solution is to remove those leftover records from phrreds,
without removing any other records that a current session may be using.
See what is in phrreds, and make sure it is the person's record that is
having the problem. Then, remove those pidm records from phrreds. (Don't
remove any records currently being used by someone else which are active
right now.)
select distinct phrreds_pidm,phrreds_activity_date from phrreds;
select * from spriden where spriden_pidm in
(select distinct phrreds_pidm from phrreds);
delete from phrreds where phrreds_pidm = 1234;
- or - truncate table payroll.phrreds;
-----------------------------------------------------------------------------
Invoice not showing up in approvals
Form faiinve is showing the document complete but not approved. When
this occurs, you need to reopen (uncomplete) the document and then have
the user tab through and recomplete it (in faainve).
To reopen the document, the document must not have already been posted
(checking fobappd and fgbtrnh), then change the complete indicator to
the appropriate value (either an 'N' or an 'I', depending upon the
particular table usage). This is done using the uncomplete.sql script
(which was derived from fixdoc.sql).
Uncomplete the document if it is not posted:
cd /home/dba_scripts
@uncomplete.sql
You may have to try this uncomplete/recomplete a couple of times to
finally get a stuck invoice to go through. (Make sure forappl is
running so that it can try to process the re-completed document.)
-----------------------------------------------------------------------------
PPAIDEN getting error on Biographic screen: "To employ this person, SSN,
Birth Date, Gender, Ethnic info needed."
Also, Banner won't allow PEAEMPL information to be entered for that
employee.
For some reason, the SSN/SIN/TFN field on PPAIDEN didn't get filled in
automatically when the ID field was filled in by the user. When we
filled in the SSN/SIN/TFN field, Banner let us add the PEAEMPL screen
information for that employee.
-----------------------------------------------------------------------------
Banner processes, sqlplus queries, backups, etc., slowing down dramatically.
Check to see if any old focmod processes are hanging around taking up cpu
cycles. Log in unix as userid root, and issue the following:
ps -ef | grep focmod
If you see old processes such as:
sbaker 26916 41112 120 Jun 08 - 8467:11 focmod
kill them with:
kill -9 26916
using the first number of the ps line for that process. The effect of these
orphan processes can be dramatic. Our backup process, over a week's time,
began taking twice as long to run. When we killed those orphan focmod
processes, the runtime reverted back to its original length on the next backup.
-----------------------------------------------------------------------------
Running Posting (fgractg) and Approvals (forappl) manually (Note: our flag
files are named PROD.fgractg (posting) and PROD.forappl (approvals) - yours
may be named differently):
Turn off automatic posting and approvals:
cd /home/jobsub
rm PROD.fgractg
rm PROD.forappl
Wait 5 minutes, or however long your timer is set, for them to terminate.
ps -ef | grep general
Run whatever jobs to check that produce the document, and approve it.
From Banner, run FORAPPL using the NOPRINT option, to put the document in
the approvals queue.
Have the user or responsible party approve the document in Banner.
From Banner, run FORAPPL using the NOPRINT option, to remove the approved
document from the approvals queue.
From Banner, run FGRACTG using the NOPRINT option, to post the document.
Check the posting and approvals .log and .lis files for errors:
ls -ltr *forappl*
ls -ltr *fgractg*
From user ID root, turn on automatic posting and approvals (you may have
your own script to do this, such as start_sleep_wake.shl):
login root
./swbringup.shl (or: su - jobsub "-c sh start_sleep_wake.shl")
Running posting manually from telnet on a document to check it for errors
(such as posting to a closed account), leaving approvals running sleep/wake:
Turn off automatic posting:
cd /home/jobsub
rm PROD.fgractg
Wait 5 minutes for it to terminate.
Have the appropriate person in the business office approve the document
to be posted, if it hasn't been approved yet. (May have to unapprove
it first and reapprove it if it is stuck).
Check to see if the document is in the approval table (forappl completed?):
select * from fobappd where fobappd_doc_num = '&document_number';
From telnet:
fgractg fimsmgr/<password>
enter char 'x' when prompted to run once.
just press enter when prompted again to not run again.
Check the fgractg.lis and .log file for errors in the document.
From user ID jobsub, turn on automatic posting (note: it can't go directly
to the flag file, such as doing "fgractg >PROD.fgractg", since it thinks
that the program is already running when it sees the pre-created flag file):
ORAENV_ASK=NO; export ORAENV_ASK
ORACLE_SID=PROD; export ORACLE_SID; . oraenv
fwakeup.shl -w 180 -d /home/jobsub -u fimsmgr/retired fgractg >ssw.lis
cat ssw.lis
cat ssw.lis >PROD.fgractg
chmod 777 PROD.fgractg
-----------------------------------------------------------------------------
Reactivating terminated employees having old (terminated) home and distribution
org codes:
Find the person's pidm:
select spriden_pidm pidm from spriden where spriden_id = '&ssn';
See what their home and distribution org codes were at termination:
col old_home format a8
col old_dist format a8
select pebempl_orgn_code_home old_home,pebempl_orgn_code_dist old_dist
from pebempl where pebempl_pidm = &pidm;
See what the termination dates are for those org code(s):
select ftvorgn_orgn_code org,ftvorgn_eff_date eff,ftvorgn_term_date term
from ftvorgn where ftvorgn_orgn_code in ('&old_home','&old_dist')
order by 1,2,3;
If those orgs are terminated, assign new org codes to their pebempl record:
update pebempl set PEBEMPL_ORGN_CODE_HOME = '&new_home'
where PEBEMPL_ORGN_CODE_HOME = '&old_home' and pebempl_pidm = &pidm;
update pebempl set PEBEMPL_ORGN_CODE_DIST = '&new_dist'
where PEBEMPL_ORGN_CODE_DIST = '&old_dist' and pebempl_pidm = &pidm;
Go into PEAEMPL and change the employee status from terminated to active
and save the information.
Go back into PEAEMPL and change the Current Hire date and remove the Last
Work Day Date and save the information.
Be sure to check the check mailing address and the residence address for
all reactivated employees.
-----------------------------------------------------------------------------
Reversing a journal voucher by recreating fgbjvcd records from a document that
has posted to the fgbtrnh tables:
Check to make sure the reversing JV document number isn't being used:
select 'J' || lpad(fobseqn_maxseqno_7+1,7,'0') OUTPUTDOCUMENT_SET from fobseqn
where fobseqn_seqno_type = 'J';
Run fieldin.sql on doc_num and doc_code for the above number (such as J00005722).
If it is being used, update the sequence number and check again:
select 'J' || lpad(fobseqn_maxseqno_7+1,7,'0') OUTPUTDOCUMENT_SET from fobseqn
where fobseqn_seqno_type = 'J' for update;
update fobseqn set fobseqn_maxseqno_7 = fobseqn_maxseqno_7 + 1
where fobseqn_seqno_type = 'J';
commit;
Run jvreversal.sql, entering the fgbtrnh document number (such as F0000898)
as the INPUTDOCUMENT, noting the output document number produced.
commit;
Do the following in Banner:
Go into FGAJVCD on that J* document.
Put in a dummy amount, such as 100.00 (to create the fgbjvch header record).
Change the transaction date to right after the fgbtrnh transaction date (or,
could use SQL below).
Exit the form.
Go into FGIJSUM to get the actual amount.
Go back into FGAJVCD and changed it to that actual amount.
To change the transaction date on the new fgbjvch header record to be right
after the transaction date on the fgbtrnh record, you could do the following
instead of changing it on the FGAJVCD form:
update fgbjvch set fgbjvch_trans_date = (select trunc(max(fgbtrnh_trans_date))+1
from fgbtrnh where fgbtrnh_doc_code = '&INPUTDOCUMENT') where fgbjvch_doc_num =
'&OUTPUTDOCUMENT';
-----------------------------------------------------------------------------
fabmatc crashed with unique constraint violation for PK_FOBUAPP (in line 1781 of
fabmatc.pc verison 4.0):
See if there are any fabinvh records without completion code of 'Y' that match
records in fobuapp (even though the code shows farinva instead of fabinvh):
select fabinvh_code,fabinvh_complete_ind from fabinvh
where fabinvh_code in (select fobuapp_doc_code from fobuapp);
Delete records in fobuapp where fabinvh_complete_ind is 'R':
delete from fobuapp where fobuapp_doc_code in (select fabinvh_code
from fabinvh where fabinvh_code in (select fobuapp_doc_code from fobuapp)
and fabinvh_complete_ind = 'R');
-----------------------------------------------------------------------------
Loading Web Tailor changes from one database to another (such as PROD to TEST here):
Export source database WTAILOR tables in this order: TWGBWMNU, TWGRMENU,
TWGRINFO, TWGRWMRL:
. oraenv
PROD
exp wtailor/<password> file=sourcewt.dmp tables=TWGBWMNU,TWGRMENU,TWGRINFO,TWGRWMRL
Export target database WTAILOR tables in this order: TWGBWMNU, TWGRMENU,
TWGRINFO, TWGRWMRL (just in case a problem occurs).
. oraenv
TEST
exp wtailor/<password> file=targetwt.dmp tables=TWGBWMNU,TWGRMENU,TWGRINFO,TWGRWMRL
Login target database as WTAILOR.
Truncate tables in this order:
truncate table TWGRWMRL;
truncate table TWGRINFO;
truncate table TWGRMENU;
delete from TWGBWMNU;
Import source database WTAILOR tables into target database, such as:
imp wtailor/<password> file=sourcewt.dmp full=yes ignore=yes
-----------------------------------------------------------------------------
Focus Problems:
LICENSE: Cannot Find the Registration
To restart the License Server for Focus after rebooting the database
server or after a Focus user getting the message "LICENSE: Cannot Find
the Registration" (your directory structure may be different here):
login as root
cd /u03/focus/focbin
./mnlicens <your license number> -c ./focmod
-----------------------------------------------------------------------------
MicroFocus COBOL Problems:
If the database server goes down, you will need to start up the COBOL
License Manager after you bring the database server back up.
To start the COBOL License Manager:
login as root
cd /usr/lib/mflmf
sh ./mflmman
If you get the error message "The license database is corrupt":
./lmfgetpv
If it shows that the license manager is running, kill it (I've not
had to do these so far):
./lmfgetpv -k
ps -aef | grep mflm
and kill any tasks listed by those ps/grep commands
rm mflmfdb*
sh ./mflmadm
F2 (to enter serial and license keys)
Type the serial number: <your serial number>
Press the Tab key
Type the license number (do not enter any spaces):
<your license number>
Press the Enter key
F3 (to install serial and license keys)
Escape
Escape
Quit without refresh (whichever Y or N does that)
sh ./mflmman
Other information that MicroFocus Support may ask you if you call them
at (800) 443-1601:
Product: Object COBOL for UNIX, Version <your version number>
Customer Number: <your customer number>
PRN: <your PRN number>
Licensed machine: <your machine name>
-----------------------------------------------------------------------------
Recovery of corrupted Oracle databases:
DUL software (Oracle). Cost: $10,000. No guarantees. The following
contact brought in the software and performed the extraction for the
company I heard about (took 4 hours):
Dan Slotkoff
Advisory Systems Engineer Specialist
Oracle Support Services
215-345-8379
-----------------------------------------------------------------------------
Unix commands:
Status of printers:
lpstat
Status of specific printer (such as hp3si_cr1):
lpstat -php3si_cr1
Removing mail (as user root), such as oracle's mail:
cd /var/spool/mail
rm oracle
Export and zip at the same time using pipe:
mknod /tmp/exp_pipe p
gzip -cN1 </tmp/exp_pipe >/u05/oradata/prod.dmp.gz &
echo <systempassword> | exp system file=/tmp/exp_pipe \
full=y log=/u05/oradata/prod.dmp.log >/dummy 2>/dummy
rm -f /tmp/exp_pipe
-----------------------------------------------------------------------------
Shutdown/Startup Banner:
To shutdown all Banner, sleep/wake, jobsub, and listener processes, do the following
from root ("#" prompt) after "cd /" (except for the "su" parts with "$" prompt):
1) Remove posting (fgractg) and approvals (forappl) sleep/wake flag files (ours
begin with "PROD.") to tell them to shut down after their next run:
# cd /
# rm /home/jobsub/PROD.*
2) Shut down the web processes on the NT:
From an NT command line prompt:
owsctl stop
Or, from OAS Manager (<OAS server URL>:8888, click on OAS Manager)
on the web, select ALL, then Stop icon (red box).
3) Wait 5 minutes til fgractg and forappl have shut down, checking for their
processes no longer running using:
# ps -ef | grep general
4) Shut down jobsub:
# su - jobsub
$ kill -9 -1
5) Shut down listener (". oraenv" must show PROD before issuing lsnrctl command):
# su - oracle
PROD
$ lsnrctl stop
6) Shut down the PROD database:
$ dbshut
6a) Shut down SEED individually, if needed (do "ps -ef | grep SEED" to see if its
processes are running and need to be shut down; that's a period and blank
before "oraenv".):
$ . oraenv
SEED
$ svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown immediate
SVRMGR> exit
$ exit
(If the "shutdown immediate" fails, do "shutdown abort" then "startup" then
"shutdown immediate". If the "shutdown abort" fails, exit out of svrmgr and do
"ps -ef | grep ora_pmon_SEED" and use the first number listed for that process after
"oracle" on that line in a kill command, such as "kill -9 52726". If you kill the
process, you might have to do a "startup force", "shutdown immediate", and
"startup" to start up that database, instead of just the "startup" command below.)
To startup all Banner, sleep/wake, jobsub, and listener processes, do the following
from root ("#" prompt) after "cd /" (except for the "su" parts with "$" prompt):
1) Start up the PROD database:
# cd /
# su - oracle
PROD
$ dbstart
1a) Start up SEED individually, if needed (if it had to be shut down earlier):
$ . oraenv
SEED
$ svrmgrl
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> exit
2) Start up listener (still su'ed to oracle):
$ . oraenv
PROD
$ lsnrctl start
$ exit
3) Start up jobsub (you may have your own script to do this):
# ./jsbringup.shl
4) Start up posting and approvals (sleep/wake) (you may have your own script
to do this):
# ./swbringup.shl
(That script may wait 5 minutes before starting the processes to allow the
currently-running sleep/wake processes to terminate.)
5) Start up the web processes on the NT:
From an NT command line prompt:
owsctl start
Or, from OAS Manager (<OAS server URL>:8888, click on OAS Manager)
on the web, select ALL, then Start icon (green triangle).
If there was a power outage or some other interruption that brought the
database server down, you will also need to start up the Focus license
manager if the users can't run Focus now, getting a license manager error:
# cd /u03/focus/focbin
# ./mnlicens <your license number> -c ./focmod
And, you will need to start up the Oracle daemon listener (if you installed
that for running system commands from PL/SQL):
# su - oracle
PROD
$ cd /home/common
$ nohup daemon >/dev/null 2>&1 &
And, you may have to start up the COBOL license manager if you can't compile:
To start the COBOL License Manager:
login as root
cd /usr/lib/mflmf
sh ./mflmman
If you get the error message "The license database is corrupt":
./lmfgetpv
If it shows that the license manager is running, kill it (I've not
had to do these so far):
./lmfgetpv -k
ps -aef | grep mflm
and kill any tasks listed by those ps/grep commands
rm mflmfdb*
sh ./mflmadm
F2 (to enter serial and license keys)
Type the serial number: <your serial number>
Press the Tab key
Type the license number (do not enter any spaces):
<your license number>
Press the Enter key
F3 (to install serial and license keys)
Escape
Escape
Quit without refresh (whichever Y or N does that)
sh ./mflmman
-----------------------------------------------------------------------------
Web Info:
GOATPAC - Reset User ID's Pin to birthday DDMMYY, or, if no birthday, to last
6 digits of User ID. (User must then enter new pin after getting into the web
with temp pin.)
GOATPAD - See User ID's Pin and other info, along with history of changes.
-----------------------------------------------------------------------------
Changing non-default OAS administrator password on the NT (myntbox here):
Apparently the oaspasswd utility expects the administrator to be called "admin",
instead of checking to see if OAS was set up with a different administrator name
("administrator" here), so it changes svnode.cfg to use admin as the name, along
with the new password. So, the Node Manager tries to restart with a different
administrator name. And, changing the password through the OAS Administrator
Listeners | Security | Basic web page doesn't work, either (the new password
isn't accepted at login, just the old password).
Therefore, in order to change the administrator password when you have a name
different than admin, from an NT command prompt, you have to:
cd d:\oas\v4081\ows\admin\website40\httpd_myntbox\node
(the directory containing svnode.cfg)
owsctl stop
oaspasswd -s website40
(changing to new password)
edit svnode.cfg
(changing "admin:" near bottom under (Users) to "administrator:",
substituting the administrator name)
owsctl stop -nodemgr
(or, Stop the Oracle Web NodeManager service from the Start |
Settings | Control Panel | Services dialog box)
owsctl start -nodemgr
(or, Start the Oracle Web NodeManager service from the Start |
Settings | Control Panel | Services dialog box)
owsctl start
You can now use your new password when logging in through the Node Manager
Server name/password prompt for OAS Manager.
-----------------------------------------------------------------------------
Creating a database on the NT that hasn't been created before (such as TEST here)
(these are mainly setting up directories, files, and NT services; Note: your file
names will differ from these):
1) Create directories on the NT for the database files (the SID directory and the
bdump, udump, and archivelog subdirectories), such as d:\oradata\test,
d:\oradata\test\bdump, d:\oradata\test\udump, and d:\oradata\test\archivelogs
(NT doesn't use a cdump directory).
2) Copy the initPROD.ora file from the AIX $ORACLE_HOME/dbs to another AIX
directory to change it's name to the new SID name (such as to initTEST.ora)
and to edit the db_name and to change the file names to the new NT directory
names and to remove the core_dump_dest parameter, and to possibly change the
log_archive_format and log_archive_dest (which is just a full directory name
on the NT) parameters. Then, FTP the new initTEST.ora file (as ascii) from
the AIX to the NT D:\oracle\v805\DATABASE directory.
3) Bring up a command shell, set the SID, and create the NT services (combine
the oradim80 options on one line, not split as shown below):
d:
set ORACLE_SID=test
D:\oracle\v805\bin\oradim80 -new -sid test -intpwd <systempassword>
-startmode auto -pfile D:\oracle\v805\database\inittest.ora
D:\oracle\v805\bin\oradim80 -startup -sid test -starttype srvc,inst
-usrpwd <systempassword> -pfile D:\oracle\v805\database\inittest.ora
4) Run the steps similar to the "Cloning a database on the NT" steps below
(except for shutting down the database and deleting the files).
<see steps below>
-----------------------------------------------------------------------------
Cloning a database (using import) on the NT that had been created before
(such as TEST here; Note: your file names and sizes will differ from these):
1) From AIX (database server), run stub.sql to generate the "create database"
SQL script (stub_create.sql) to model TEST after PROD (run as system or
from a dba account):
cd /home/dba_scripts
rm stub_create.sql
sqlplus
@stub.sql
d:\oradata\test
d:\oradata\test
test
exit
2) On the NT, FTP the generated stub_create.sql (as ascii) and the latest
zipped PROD dump file (like /u05/oradata/prod1.dmp.gz or prod2.dmp.gz)
from the nightly backup (as binary) from the AIX to the NT:
ftp
open <database server address>
oracle
<oraclepassword>
lcd d:\oradata\test
cd /home/dba_scripts
type ascii
get stub_create.sql
cd /u05/oradata
type binary
get prod1.dmp.gz (takes around 3 minutes)
bye
3) Unzip the zipped PROD dump file (using WinZip), which takes around 30
minutes.
4) Edit stub_create.sql, such as to spool to d:\oradata\test\stub.lst,
to change the pfile pathname to d:\oracle\v805\database\initTEST.ora,
and any other pathnames as needed, and to remove the EXIT line.
5) Shut down the database and delete all datafiles (.dbf) , control files
(.ctl), and log files (.rdo) for it (such as in d:\oradata\test):
d:
set ORACLE_SID=test
svrmgr30
connect internal
shutdown immediate
exit
cd d:\oradata\test
del *.dbf
del *.ctl
del *.rdo
6) Run stub_create.sql to recreate the new datafiles and load the Oracle
system procedures and tables (which takes about an hour):
cd d:\oracle\v805\rdbms80\admin
svrmgr30
@d:\oradata\test\stub_create.sql
7) Continue with the following:
-- From svrmgr30 (after stub_create.sql):
alter user sys identified by <syspassword>;
alter user system identified by <systempassword>;
create tablespace rbs_big
datafile 'd:\oradata\test\rbsbTEST_1.dbf' size 330008K
default storage (initial 6144000 next 6144000 pctincrease 0
minextents 2 maxextents 249);
create rollback segment rbs_big tablespace rbs_big
storage (initial 6144000 next 6144000 minextents 3
maxextents 50);
-- WRITE THE FOLLOWING DOWN FOR LATER RE-CREATION OF TEMP:
select file_name,bytes/1024 from dba_data_files where tablespace_name = 'TEMP';
drop tablespace temp;
shutdown immediate
host
-- Remove the temp files displayed by the above select statement:
del d:\oradata\test\tempTEST_1.dbf
del d:\oradata\test\tempTEST_2.dbf
exit
startup pfile=d:\oracle\v805\database\initTEST.ora
create tablespace temp
datafile 'd:\oradata\test\tempTEST_1.dbf' size 420008K,
'd:\oradata\test\tempTEST_2.dbf' size 420008K temporary
default storage (initial 6144000 next 6144000 pctincrease 0
maxextents 249);
select segment_name,status from dba_rollback_segs;
-- Offline the rollback segments displayed by the above select statement:
alter rollback segment rbs_big online;
alter rollback segment rbs1 offline;
alter rollback segment rbs2 offline;
alter rollback segment rbs3 offline;
exit
-- Do the full import here (which takes about 3 hours) --
cd d:\oradata\test
imp80 system/<systempassword> file=prod.dmp full=y ignore=y log=prod.log
svrmgr30
connect internal
select segment_name,status from dba_rollback_segs;
alter rollback segment rbs1 online;
alter rollback segment rbs2 online;
alter rollback segment rbs3 online;
alter rollback segment rbs_big offline;
select file_name from dba_data_files where tablespace_name = 'RBS_BIG';
select file_name from dba_data_files where tablespace_name = 'TEMP';
drop rollback segment rbs_big;
drop tablespace rbs_big;
drop tablespace temp including contents;
shutdown immediate
host
-- Remove the large rollback segment and temp file displayed by the above
-- select statements:
del d:\oradata\test\rbsbTEST_1.dbf
del d:\oradata\test\tempTEST_1.dbf
del d:\oradata\test\tempTEST_2.dbf
exit
startup pfile=d:\oracle\v805\database\initTEST.ora
-- Recreate the initial temp files that were removed earlier:
create tablespace temp datafile
'd:\oradata\test\tempTEST_1.dbf' size 81920K,
'd:\oradata\test\tempTEST_2.dbf' size 40008K temporary
default storage (initial 256K next 512K pctincrease 0
minextents 1 maxextents 249);
-- From userid sys, after creating bansecr ID:
grant select on dba_role_privs to bansecr;
grant select on dba_sys_privs to bansecr;
-- grant select on exu8dfr to bansecr;
grant select on user$ to bansecr;
grant select on defrole$ to bansecr;
grant select on dba_roles to bansecr;
grant execute on dbms_pipe to public;
-- For guapswd routines (if you are using my password expiration routines):
grant select on dba_users to bansecr;
grant select on dba_jobs to bansecr;
grant execute on dbms_sys_sql to oas_public;
-- Then, from baninst1:
grant execute on baninst1.G$_foreign_sql_pkg to bansecr;
-- Then, from a dba account (run several times for dependencies):
@d:\oradata\guraltr
update gubinst set gubinst_instance_name = 'TEST';
select * from dba_sequences where sequence_name = 'GJBPSEQ';
drop sequence general.gjbpseq;
create sequence general.gjbpseq minvalue 90000 cycle cache 30 maxvalue 99999999;
-- Then, from bansecr (if you are using my password expiration routines):
execute guapswd_stop;
8) If you want to access TEST from Web For Employees, you'll have to shut down
the web processes on the NT and start them up again (see steps 3 and 12 of
"Rebuilding PROD" below.
-----------------------------------------------------------------------------
Rebuilding PROD by importing a full database export file (make sure the export
was done with the "compress=n" option) (similar to import cloning of TEST; takes
around 8 hours total: 2 hours for pre- and post- processing, 6 hours for import;
Note: your file names and sizes will differ from these):
1) From AIX (database server), shut down sleep/wake posting (fgractg) and
approvals (forappl) by removing their flag files to tell them to shut down
after their next run (such as around 5 minutes):
rm /home/jobsub/PROD.*
2) Shut down the listener (". oraenv" must show PROD before issuing the
lsnrctl command):
login oracle (or, from root: su - oracle)
PROD
lsnrctl stop
lsnrctl status
3) Shut down the web processes on the NT:
From an NT command line prompt:
owsctl stop
Or, from OAS Manager (<OAS server URL>:8888, click on OAS Manager)
on the web, select ALL, then Stop icon (red box).
4) From AIX, run stub.sql to generate the "create database" SQL script
(stub_create.sql) for PROD (run as system or from a dba account):
cd /home/dba_scripts
rm stub_create.sql
sqlplus
@stub.sql
/u03/oradata/PROD
<enter>
<enter>
exit
5) Edit stub_create.sql, such as to spool to /home/dba_scripts/stub.lst,
to change any other pathnames if needed, and to remove the EXIT line.
6) Shut down the database and delete all datafiles (.dbf) , control files
(.ctl), and log files (.rdo) for it (such as in /u03/oradata/PROD,
/u02/oradata/PROD, /u01/oradata/PROD, and /u00/oradata/PROD, with
archive log files in /u01/oradata/PROD/archivelogs):
svrmgrl
connect internal
shutdown immediate
exit
cd /u03/oradata/PROD
rm *.dbf *.ctl *.rdo
cd /u02/oradata/PROD
rm *.dbf *.ctl *.rdo
cd /u01/oradata/PROD
rm *.dbf *.ctl *.rdo
cd /u00/oradata/PROD
rm *.dbf *.ctl *.rdo
7) Run stub_create.sql to recreate the new datafiles and load the Oracle
system procedures and tables (which takes about an hour):
cd $ORACLE_HOME/rdbms/admin
svrmgrl
@/home/dba_scripts/stub_create.sql
8) Continue with the following:
-- From svrmgrl (after stub_create.sql):
alter user sys identified by <syspassword>;
alter user system identified by <systempassword>;
create tablespace rbs_big
datafile '/u05/oradata/PROD/rbsbPROD_1.dbf' size 330008K
default storage (initial 6144000 next 6144000 pctincrease 0
minextents 2 maxextents 249);
create rollback segment rbs_big tablespace rbs_big
storage (initial 6144000 next 6144000 minextents 3
maxextents 50);
-- WRITE THE FOLLOWING DOWN FOR LATER RE-CREATION OF TEMP:
select file_name,bytes/1024 from dba_data_files where tablespace_name = 'TEMP';
drop tablespace temp;
shutdown immediate
host
-- Remove the temp files displayed by the above select statement:
rm /u03/oradata/PROD/tempPROD_1.dbf
rm /u03/oradata/PROD/tempPROD_2.dbf
exit
startup
create tablespace temp
datafile '/u05/oradata/PROD/tempPROD_1.dbf' size 420008K,
'/u05/oradata/PROD/tempPROD_2.dbf' size 420008K temporary
default storage (initial 6144000 next 6144000 pctincrease 0
maxextents 249);
select segment_name,status from dba_rollback_segs;
-- Offline the rollback segments displayed by the above select statement:
alter rollback segment rbs_big online;
alter rollback segment rbs1 offline;
alter rollback segment rbs2 offline;
alter rollback segment rbs3 offline;
exit
-- Do the full import here (which takes about 6 hours) --
cd /u05/oradata
rm nohup.out
mknod /tmp/imp_pipe p
nohup gunzip -c prod1.dmp.gz >/tmp/imp_pipe &
nohup imp system/<systempassword> file=/tmp/imp_pipe full=y ignore=y \
buffer=1024000 commit=y log=prod1.log &
rm -f /tmp/imp_pipe
svrmgrl
connect internal
select segment_name,status from dba_rollback_segs;
alter rollback segment rbs1 online;
alter rollback segment rbs2 online;
alter rollback segment rbs3 online;
alter rollback segment rbs_big offline;
select file_name from dba_data_files where tablespace_name = 'RBS_BIG';
select file_name from dba_data_files where tablespace_name = 'TEMP';
drop rollback segment rbs_big;
drop tablespace rbs_big;
drop tablespace temp including contents;
shutdown immediate
host
-- Remove the large rollback segment and temp file displayed by the above
-- select statements:
rm /u05/oradata/PROD/rbsbPROD_1.dbf
rm /u05/oradata/PROD/tempPROD_1.dbf
rm /u05/oradata/PROD/tempPROD_2.dbf
exit
startup
-- Recreate the initial temp files that were removed earlier:
create tablespace temp datafile
'/u03/oradata/PROD/tempPROD_1.dbf' size 81920K,
'/u03/oradata/PROD/tempPROD_2.dbf' size 40008K temporary
default storage (initial 256K next 512K pctincrease 0
minextents 1 maxextents 249);
-- From userid sys, after creating bansecr ID:
grant select on dba_role_privs to bansecr;
grant select on dba_sys_privs to bansecr;
-- grant select on exu8dfr to bansecr;
grant select on user$ to bansecr;
grant select on defrole$ to bansecr;
grant select on dba_roles to bansecr;
grant execute on dbms_pipe to public;
-- For guapswd routines (if you are using my password expiration routines):
grant select on dba_users to bansecr;
grant select on dba_jobs to bansecr;
grant execute on dbms_sys_sql to oas_public;
-- Then, from baninst1:
grant execute on baninst1.G$_foreign_sql_pkg to bansecr;
-- Then, from a dba account (run several times for dependencies):
@guraltr
9) From AIX, start up the listener:
login oracle (or, from root: su - oracle)
PROD
lsnrctl start
10) Start up jobsub (you may have your own script to do this):
login root
./jsbringup.shl
11) Start up sleep/wake (posting and approvals) (you may have your own script
to do this):
./swbringup.shl
12) Start up the web processes on the NT:
From an NT command line prompt:
owsctl start
Or, from OAS Manager (<OAS server URL>:8888, click on OAS Manager)
on the web, select ALL, then Start icon (green triangle).
13) Start up the Oracle daemon listener (if you installed that for running
system commands from PL/SQL):
login oracle (or, from root: su - oracle)
PROD
cd /home/common
nohup daemon >/dev/null 2>&1 &
-----------------------------------------------------------------------------
Deleting a database and it's services on the NT (such as TEST here):
D:\> set ORACLE_SID=TEST
D:\> svrmgr30
svrmgr> connect internal
svrmgr> shutdown immediate
svrmgr> exit
D:\> d:\oracle\v805\bin\oradim80 -delete -sid test (to remove services)
remove all datafiles, control files, redo logs, and directories in
d:\oradata\prod, as well as d:\oracle\v805\database\initTEST.ora
and any svrmgr and sqlplus shortcuts for TEST on the NT.
This Page was Last Updated on 06/15/06
You Are Visitor Number |
|
|
© 2006 |
|
|
University of Arkansas • Division of Agriculture |
Mission
•
Disclaimer
•
EEO
•
|
