Archiving data from Apps
Date: Tue, 15 Jun 1999 17:06:08 -0400
From: "Keeling, John" JKeeling@subaru.com
Subject: Re: Managing applications environment
How do you archive info from your apps? We are beginning to develop an
archiving strategy for the information contained/generated by our Oracle
applications. I am seeking to make the various key monthly financial reports
(FSGs) and key year end applications reports such as vendor masters, vendor
payments, 1099 reports forms, etc indexed by report and available on CD for
future reference. I have begun to make some initial contacts with a vendor
that transfer the data to a CD and or make the information available from a
server side solution. A couple of questions that I have started to
formulate in this area are:
Are there preferred vendors that specialize in doing this for Oracle apps?
Is there any synergy to doing this with any of the vendors that supply
extensions for scanning and indexing records that relate to your accounts
payable or accounts receivable apps (say scanned invoices that are on a
server). And on a somewhat loosely related topic, does anyone have an
effective strategy for maintaining the reporting relationships that existed
at points in time for your financial reports. A problem that we have is
changing reports or relationships or reclassing accounts makes it difficult
to recreate year's earlier reports when questions or audit comes up. (This
presumes that you overlay your FSGs from time to time and when you do that
you lose the original or previous reporting relationships from your GL). I
suppose we could keep the old reports (FSGs) and clone newer reports from
them, but this happens frequently and I am a bit leery of having an unwieldy
list of financial reports in the FSG LOV that tends to grow over time.
I would be very greatly for any input that anyone is willing to share!
John keeling
Subaru of America, Inc.
Archiving of Financials data
Date: Fri, 18 Jun 1999 14:13:48 -0400
From: ddicondina@SUNGARDRS.COM
Subject: Archiving
Hello All,
How are people handling archiving of financials? We are 10.7 Character
(GL, AP, AR)/currently migrating to NCA, database 7.3.4 running on Sun
Solaris. I am interested in 3rd party tools/software, hardware and
philosophy.
Dominic DiCondina
SunGard Recovery Services Inc.
610-341-8016
selective/time based archiving/ refresh dev from prod
Date: Fri, 18 Jun 1999 16:47:14 -0700
From: Phil_Palmer@Playstation.sony.com
Subject: Archiving Oracle Apps Data
Currently, we copy our production Oracle Applications Database to create a
development environment on an as-needed-basis. Our database size is not that
large (15GB) at this point, but we are anticipating its continued growth
(1.5GB/Month) to begin to hamper our ability to quickly create a development
environment in the future. Additionally, we need to think about archiving data
over the long haul to improve I/O performance, etc.
So my question is this:
Can anyone recommend a tool, utility or set of contributed library scripts that
allow for selective/time based database archiving and/or the ability to
partially refresh a development environment from production?
Phil Palmer
Sony Computer Entertainment America
http://www.playstation.com
Date: Fri, 18 Jun 1999 18:11:24 -0700
From: "Brais, Paul" BraisP@sybrondental.com
Subject: RE: Archiving Oracle Apps Data
I don't know about doing a partial refresh but we have a 100gig production
database and we routinely refresh 3 development environments using
SQLBacktrack from BMC software. The refresh including renaming the database
and other miscellaneous tasks takes about 7 hours. The only problems we run
into is the current tape not having all of the archive logs and we have to
get them from the current production or from another backup tape.
We are running on EMC disks subsystem and are negotiating to get TIMEFINDER
which will cut the refresh time in half or better, but for a smaller
database you should be able to get the job done with omniback or
sqlbacktrack.
Good Luck
Date: Mon, 21 Jun 1999 11:17:34 -0700
From: "Binh Pham" Binh.Pham@jpl.nasa.gov
Subject: Re: Archiving Oracle Apps Data
We have developed a set of scripts that do database refresh everynight from
our Production environment to another server for query only purposes. The
scripts are also used to help us refresh any other development or testing
environment as required.
It does take some efforts for us to develop these scripts and ironed out
all of the kinks. So far, it has been working pretty good and our database
size is 70 gigs and growing.
We can not give out the scripts since it belongs to our client.
Archiving Payroll results on CD
Date: Mon, 21 Jun 1999 17:04:08 +0200
From: Bert Vandenbussche Bert.Vandenbussche@argus-is.com
Subject: PAYROLL : Archiving Payroll results on CD
Hi,
I'm looking for information on prooven archiving strategies with respect to
Oracle Payroll. I understood Oracle itself has no standard functionality for
Archiving Oracle Applications data ( correct me if I'm wrong).
Does there exist any third-party products for archiving data on CD?
Or better does there exist any such product for archiving Oracle Payroll
Data on CD?
Any feedback on archiving strategies or reasons why we wouldn't need
archiving at all are welcome.
Regards,
Bert
Date: Mon, 21 Jun 1999 10:48:32 -0500
From: "Ken Conway" ken.conway@bosscorporation.com
Subject: Re: PAYROLL : Archiving Payroll results on CD
Bert:
You are correct. There is no current functionality to support archiving of
Oracle Payroll information.
It is possible to safely archive and delete all of your PayMIX and Batch
Element Entry tables unless Oracle Time Management or another third party
product has a specific reporting requirement that requires you retain
PayMIX data. We have deleted the information in these tables before.
However, you must be careful based on your specific situation before
determining that this is safe for your implementation.
Unfortunately, it is very risky to attempt the same approach with some of
the "large" tables such as pay_run_results, pay_run_result_values,
pay_payroll_actions, pay_element_entries_f, pay_element_entry_values_f, etc.
Oracle has to be careful with purging this data since it is clearly needed
for producing Quarterly Wage Listings and Year End Processing. Also, these
tables are used for Retro Pay processing. Finally, with Date Tracking, the
user can go back in time and review an employee's Statement of Earnings from
past years. If the data from the tables were purged and archived, this
could become a real mess.
I do have further thoughts and strategies on archiving payroll data. Please
feel free to call me at (214) 495-7654.
Hope this helps,
Ken
Ken Conway
BOSS Corporation
Better Organization Service Solutions
214-495-7654 (Voice)
214-495-7653 (Fax)
770-622-5500 (Headquarters)
Email: ken.conway@bosscorporation.com
http://www.bosscorporation.com
Purging data within OraApps
Date: Mon, 28 Jun 1999 10:56:13 +0000
From: Gary Wright gary.wright@experian.com
Subject: Purging Data within Oracle Apps ????????????
Does anyone know of any procedures/techniques/3rd party tools which
can be used to purge data within an Oracle Applications database.
We have cloned our live database to create a new database. The new
database is to have a new set of books for a new company. We really
need to clear our customers/suppliers/old transactions etc for the new
database as these transactions will simply not be used and will only clog
up the system. We created the new database from the old so we could
retain the original configuration.
Any Ideas anyone ???
Regards,
Gary.
Date: Mon, 28 Jun 1999 07:43:53 -0300
From: Melanie Perry Melanie.Perry@moncton.org
Subject: RE: Purging Data within Oracle Apps ????????????
Hi Gary
We have a similar situation here. We are going through the process right
now of purging at least 6 or 7 years of data for AP\PO and then we will
purge GL. This is currently only from our TEST system. Once we know the
procedure, then we will aply it to PROD.
We are using the AP\PO submit purge screen to initiate and confirm the
processes. Oracle Apps provides a process to do this for you. I musy say
though, we have had a few problems with it thus far but we have had
successful purges.
I can give you more info if you require it.
Melanie Perry
SSA \ DBA
The City of Moncton
Date: Mon, 28 Jun 1999 13:31:00 +0000
From: Gary Wright gary.wright@experian.com
Subject: RE: Purging Data within Oracle Apps ???????????? -Reply
Melanie,
Many thanks for replying, It would be great if you would provide any
information at all on the purging work you've been looking at.
Especially information on problems encountered and any patches you've
had to apply.
Thanks Again,
Gary.
Date: Mon, 28 Jun 1999 09:52:36 -0300
From: Melanie Perry Melanie.Perry@moncton.org
Subject: RE: Purging Data within Oracle Apps ???????????? -Reply
Hi Gary
The documentation is in the Oracle Apps manuals on purging. I suggest you
give it a read before starting anything because there is a procedure you
must follow.
As far as the process goes, it is fairly straightforward but we ran into
some problems.
I have a TAR opened now with Oracle - TAR 11981033.600. Give it a read on
the oracle website and if you need anything else, let me know. I could go
into details but everything is written in the TAR.
Oh, we are getting a signal 11 on the confirm category of the purge and
oracle can't seem to figure it out.....I applied a patch but it didn't make
any difference.
Melanie Perry
Date: Mon, 28 Jun 1999 08:43:31 -0500
From: "Marcia Michalik" marcia_michalik@edisonday.com
Subject: RE: Purging Data within Oracle Apps ????????????
As part of the leadership group for the Twin Cities Financial SIG, we have
been looking into third party software for purge/archive in order to present
alternatives to our members at our August meeting. The two companies we
have lined up are Kernel Tweaks and Bitbybit. Both have web sites. I got
these names off this server and both have received good reviews in the
recent past.
Regards,
Marcia Michalik
Edison Day & Associates, Ltd.
612.943.3981
marcia_michalik@edisonday.com
From: "Wagner, Brent" Brent_Wagner@StandardAero.ca
Subject: RE: Purging Data within Oracle Apps ????????
Date: Mon, 28 Jun 1999 09:41:56 -0500
Hi Gary / Melanie,
We have been attempting to do a PO / AP purge as well. We've had a TAR
opened with Oracle Support Canada for over 1 year. When we select Simple
Invoice Purge we get the wrong data and when we select Matched PO Purge we
also get the wrong data. Oracle provided 4 patches over the last year and
none fixed the problem (1 actually caused the Purge to crash). Support
searched webiv extensively, and they talked to development, response was,
"This is not a know problem." We have all of the latest forms and reports.
It was finally determined that the invoices were not being picked up because
the assets_addition_flag in ap_invoice_distribtions was set to 'U' even
though we are not using FA. Oracle updated this field to be a 'N' value and
more invoices were picked up. Also, the cleared_date in ap_checks_all is not
populated as we do not clear checks. This needs to have a date in it. Once
Oracle updated this field to the value of 01-JUN-97, over 300
pages of invoices were selected, (instead of 2 pages)!!! The purged has
successfully completed in TEST (finally) and we'll be trying Production
later this week. I suggest checking out your values in these two tables.
We're on 10.7, AP, AR, INV, GL, PO.
Cheers,
Brent DP Wagner
Analyst - Oracle Applications
Standard Aero Ltd. Winnipeg, MB. Canada
Brent_Wagner@standardaero.ca mailto:Brent_Wagner@standardaero.ca mailto:Brent_Wagner@standardaero.ca
From: Melanie Perry Melanie.Perry@moncton.org
Subject: RE: Purging Data within Oracle Apps ????????
Date: Mon, 28 Jun 1999 13:02:42 -0300
Wow! Thanks Brent.
I am experiencing the similar problems. The purge actually "purges" rows
but the before report and the after report do not match. The user are
concerned why this is and I don't believe we will want to advance to PROD
like this.
Also, I am getting a signal 11 on the confirm process. Any ideas? Did this
happen to you? Oracle said that getting this error is no big deal. I am a
little concerned to why they would say this. The "confirm" process seems to
spawn 4 jobs : 2 purge listing reports, the purge summarization job, and
then the actual delete job. Does this sound like the correct procedure?
Thanks
Melanie Perry
From: "Wagner, Brent" Brent_Wagner@StandardAero.ca
Subject: Purging Data within Oracle Apps ????????
Date: Mon, 28 Jun 1999 12:59:27 -0500
Yes, we get the 4 jobs and this seems to be the correct procedure. No, we
have never had the Signal 11 error. Are you maybe purging too much data?
Did you check those tables I mentioned? Isn't interesting that we both deal
with Oracle Support Canada, yet neither of our very similar TARs crossed
paths?
Brent DP Wagner
Analyst - Oracle Applications
Standard Aero Ltd. Winnipeg, MB. Canada
Accidental purge
Date: Wed, 30 Jun 1999 14:47:00 +0300
From: mohammed alshaarawi m_sharawy@tccbce.com
Subject: INV:PERIOD:TRAN:PURGE
Hi all
One of our users have accidentally purged all transactions for last period.
is there away to retrive all this transcations
thanx
Forcing roll forward thru archived redologs
Tim_Plunkett@praxair.com wrote:
Does anyone know a way to "force" oracle to roll forward through
archived
redo
logs AFTER the database has been opened? Here is what happened:
1) Cold backup taken 6/14/99. Database is run in archived log
mode.
2) On 6/15/99, an HP SCSI controller card failed, we lost two
mount
points, including the one with the system datafiles. The archived log
files
remained intact, with approx. 25 "new" archive logs since the last cold
backup.
3) Recovered database files.
4) Startup. ( I know, I should have not opened the database -
now
I
cannot perform media recovery!)
Is there any known way to "force" of "fool" oracle into applying these
redo-logs? A few consultants and even Oracle WW support has hinted at
a
way,
but either it does not really exist or is terribly difficult. Just
thought I
would pose the question to the "cream of the DBA crop" in this group.
Thanks in advance for any assistance, Tim.
Ronald Leedy rleedy@us.oracle.com
Ron Leedy rleedy@us.oracle.com on 06/15/99 09:59:35 AM
cc: (bcc: Tim Plunkett/USA/Praxair)
Subject: Re: Can you "fool" Oracle?
Tim,
Why don't you just restore the files again and follow the procedure
correctly?
Sure it means more down time but what is the production costs of the not
having
those transactions from the archive logs?
From: Tim_Plunkett@praxair.com Tim_Plunkett@praxair.com
To: Multiple recipients of list oraapps-dba@cpa.qc.ca
Date: Tuesday, June 15, 1999 11:48 AM
Subject: Re: Can you "fool" Oracle?
Ron,
I have already done as you mentioned. I was just curious of the
so-called
"alternate" method.
Thanks for the help, Tim.
From: "Timothy Brewer" tbrewer@tbsolns.com
Reply-To: oraapps-dba@cpa.qc.ca
To: Multiple recipients of list oraapps-dba@cpa.qc.ca
Subject: Re: Can you "fool" Oracle?
Date: Tue, 15 Jun 1999 17:00:39 -0400 (EDT)
Peers,
While this might be "interesting" from a technical standpoint, it seems to
me that you would forfeit the integrity of your database by doing so...
That is, an update statement could have been issued while the database was
open that took the current value of a column and added a value to it:
update po_lines_all
set quantity = quantity (from the original backup) + 5
where ...... ;
When in fact the update (perhaps called from a stored proc) should have
been:
update po_lines_all
set quantity = quantity (value from redo log number 22, say) + 5
where ..... ;
Then when you roll forward from redo logs that are actually before my
update
statement, it seems like you've just corrupted the database. (Corrupted
from the standpoint that the data is no longer valid --- not corrupted from
the standpoint that you cannot get the database open.)
Please correct me if I'm wrong. However, I could understand from a
standpoint that the users haven't gone in yet and you want to shut it down
and recover after you inadvertently opened the database.
I suppose I can see why Oracle would not want to support this.
Later,
Timothy J. Brewer
Sr. Oracle Consultant
TB Solutions, Inc.
Date: Tue, 22 Jun 1999 21:06:38 GMT
From: manu b manu024@hotmail.com
Subject: fooling Oracle?? what is the issue
Hi pals,
Forgive me for my limited knowledge but I want to know what was
wrong in doing the startup after recovering the db files from Cold backup.Or
are you suggesting that we should do restore from the cold backup and then
mount the database with the new set of db files(from restoration) and then
try to do incomplete recover until time of the last archvied log.
Will really appreciate if someone pls throw some light on this.
thanks
Date: Tue, 22 Jun 1999 16:52:27 -0600
From: Tim_Plunkett@praxair.com
To: oraapps-dba@cpa.qc.ca
Subject: Re: fooling Oracle?? what is the issue
Manu,
Since I wanted to apply the archive log files to a 'cold' restoration, I
should not have opened the database after I restored it from tape. Once the
database was opened, there was no way to do the incomplete recovery, since the
database thought it was already recovered. I should have just mounted the
database and then did the recovery.
No transactions took place after the database was opened, so I was looking
for a way to 'trick' the control files so I would not have to restore the
database from tape again. Unfortunately, this is what I had to do, but this
time, I followed the directions ;)
Tim.
Purge Tx history - Tables effected
Date: Thu, 22 Jul 1999 09:20:22 +1200
From: Mark Healey MHealey@swichtec.co.nz
Subject: Purge Transaction History
We are about to purge our transaction history. Does any know which tables
are effected?
Do not want any surprises
Mark (Austin) Healey
Applications Systems Administrator
Swichtec Power Systems Ltd
Purge/archive budget balances
Date: Fri, 23 Jul 1999 09:39:42 -0500
From: "Ary, Diane" dary@moorman.com
Subject: GL: Archive and Purge
We are using 10.7 character and are on database version 7.3.3.6. I have two
questions about the standard Oracle archive and purge functionality in GL.
1) The Oracle reference manual states you can only archive/purge actual
balances. What about budget balances?
2) The Oracle reference manual says you can archive/delete actual balances
as well as journal details and its associated journal references. What are
the associated journal references?
Diane Ary
Work Phone (217)-231-2271
Work Fax (217)-223-1915
EMail dary@moorman.com
Backup scripts/instructions for NT environment
Date: Wed, 21 Jul 1999 17:35:23 -0700
From: Carlos_Raul_Jimenez@notes.amdahl.com
Subject: NT backups scripts and instructions....
DBA's:
Does any DBA has some scripts to run hot/cold backups and exports on NT, I
need
to setup some NT boxes with backups scripts, I haven't done much with NT so
I will
appreciate any kind of help in this matter, some instructios how to set up
the scripts,
which account to run it from and how to set it so it will run on a schedule
sort of
crontab kind of jobs.
Any help will be welcome, thanks in advance.
Best regards, Carlos R. Jimenez. Sr. Oracle DBA. CJimenez Ind.
Cons., Inc.
Date: Thu, 22 Jul 1999 12:26:46 +0400
From: mzaveri@eppco.co.ae
Subject: Re: NT backups scripts and instructions....
Long back, I saw them on http://technet.oracle.com/ (You need to register.
It is free)
I don't exactly remember, it's in code or support area within that site.
Date: 22 Jul 99 08:59:12 EDT
From: Yigal Ran ydran@usa.net
To: Carlos_Raul_Jimenez@notes.amdahl.com, oraapps-dba@cpa.qc.ca
Subject: NT backups scripts and instructions....
Hi Carlos,
Try the attached scripts, you can replace the "NET' and "SVRMGR??" with
"ORADIM??" if you like.
Let me know if it's working for you.
Yigal
Purge fnd_conc_req_stat table
Date: Thu, 29 Jul 1999 14:38:43 -0500
From: Bharat Patel bpatel@DOMINOAMJET.com
Subject: fnd_conc_req_stat table
Does any body know the program name that purge the data from the above
mentioned table? As I can't find it in tech ref. manual.
Can I get rid of the data from this table? If yes what I have to take under
consideration?
Note : we are on 11.02.
thanks.
Bharat Patel
D.B.A.
bpatel@dominoamjet.com
847-244-2501 ex 1249
Date: Thu, 29 Jul 1999 13:23:46 -0700
From: "Bullard, James" James.Bullard@fluke.com
Subject: RE: fnd_conc_req_stat table
Yes you can, being new to the Apps I found in Chapter 8-25, 9-26 in the
reference manual. System Admin guide. In 10.7 I believe it was Purge
Concurrent Request and/or Manager Data.
HTH
James
Date: Thu, 29 Jul 1999 15:32:44 -0500
From: Bharat Patel bpatel@DOMINOAMJET.com
Subject: RE: fnd_conc_req_stat table
James that Purge program is running on ur system and it is still filling up
this table. That's why I posted in this forum.
thanks.
Bharat Patel
D.B.A.
bpatel@dominoamjet.com
847-244-2501 ex 1249
Date: Fri, 30 Jul 1999 18:50:04 -0000
From: "Gopalakrishnan K" gopalak@wiproge.med.ge.com
Subject: Re: fnd_conc_req_stat table
Hi Bharat !
This table contains details of ALL concurrent programs.
To purge this data you have to run one MORE CONCURRENT program
GO to SyS admin menu
Run the purge concurrent request
and give the number of days
fire a new report.
This will purge the table.
I am not in my worksite. so theere may be some mistakes in the menu path.
Basically you haave to run one mmore conc request to purge the table
DO NOT EVER TRY TO TRUNCATE THE TABLE..
GOpal
Archive / Purge HRMS
Subject: [orahrms-l] Archive/Purge functionality for Oracle HRMS
From: Ida Young imyoung@mail1.jpl.nasa.gov
Date: Fri, 14 May 1999 10:11:21 -0700
Oracle has Archive/Purge functionality available for PA in beta for release
10.7 Does anyone know if Oracle also has Archive/Purge functionality for
Oracle HRMS? Have anyone done this successfully for Oracle HRMS ? We are in
need of having to do archiving in the near future. If anyone has any
information in this area, please let us know.
Thank you.
Ida Young, IBS Product Implementation People Systems Supervisor
Email: ida.m.young@jpl.nasa.gov
Tel: (818) 354-9544
OE - SO_EXCEPTION table size increasing manifold - purge policy
Date: Thu, 12 Aug 1999 13:49:10 -0400
From: John Sweeney jsweeney@goaa.org
Subject: OE-SO_EXCEPTIONS table growing-Purge policy
We are running Oracle Financials 10.7 SC161, RDBMS 8.0.5 on Sun Solaris.
Three months after going live, our SO_EXCEPTIONS table has grown to 7,894
recs. All the exceptions are due to "Insufficient quantity to Pick
Release". The number of records is not a big deal now, but could eventually
get out of control.
Oracle's help text on this states:
"The Process Exception Report prints reports on Order Entry / Shipping's
concurrent programs. For example, you can use this report to see if
Receivables Interface transferred information successfully to Oracle
Receivables. Order Entry / Shipping automatically stores information for
this report for each concurrent program you run, except for OrderImport,
which has its own report...Suggestion: Your DBA should monitor the
SO_EXCEPTIONS table and delete records as applicable for your business.
Order Entry / Shipping does not automatically delete any records from this
table. "
QUESTION 1: Does anyone have a suggestion on a recommended policy for
purging this table? I realize this may be company-specific but I'm throwing
it out for thoughts anyway.
QUESTION 2: Does anyone know why we are running into so many Insufficient
quantity for Pick Release problems? What can we do to control this better?
This may be more of a business issue and may be tough to answer without
examining how we are running our Order Entry and Inventory shops.
John Sweeney
GOAA, IT Department
407-825-3280
Date: Thu, 12 Aug 1999 13:23:04 -0500
From: Bharat Patel bpatel@DOMINOAMJET.com
Subject: RE: OE-SO_EXCEPTIONS table growing-Purge policy
John, we are truncating this table every night after we take a backup. As it
is usual to grow it rapidly. It totally depends on the business decision.
You can keep the records for numbers of days and then delete it. Only thing
then after you need to do is imp/exp otherwise it never free up the space by
deleting records.
We set a cron job which does for us.
hope this helps.
Bharat Patel
D.B.A.
bpatel@dominoamjet.com
847-244-2501 ex 1249
Date: Fri, 13 Aug 1999 09:21:40 -0700 (PDT)
From: Carlos Jimenez cjimenezinc@yahoo.com
To: oraapps-l@cpa.qc.ca, Multiple recipients of list oraapps-l@cpa.qc.ca
Subject: Re: OE-SO_EXCEPTIONS table growing-Purge policy
John:
I think we (in this group) had talked about this
and I sent this before, but look into it and if you
like call Oracle get a tar number (to be sure) and
perhaps purge with the sql script attached, our
policy is if the concurrent request from FND_CON-
CURRENT_REQUESTS tables does not exist any more then
its corresponding record from so_exceptions shouldn't
either.
We run the following script from the concurrent
manager an a daily basis:
set long 90000
set head on
set echo on
select a.request_id,a.creation_date,
a.last_update_date,a.context,a.message_text
from so_exceptions a
where not exists (select b.request_id
from fnd_concurrent_requests b
where a.request_id = b.request_id)
/
delete from so_exceptions a
where not exists (select b.request_id
from fnd_concurrent_requests b
where a.request_id = b.request_id)
/
Best regards, Carlos R. Jimenez. Sr. Oracle DBA.