Last night we used our refresh scripts (rman duplicate) to rebuild the test database. Early this morning users reported the following error:
Since this problem wasn't reproducible in production we knew it had to be something with the refresh. The first thing we did was to look for differences between production and test databases. Some objects were invalid in test but recompiling them didn't resolve the issue. Checking through the log files for the refresh I noticed that autoconfig didn't complete successfully.
Uploading Context file /apps/appsora/oraappl/admin/ora_myserver.xml
Context File upload for /apps/appsora/oraappl/admin/ora_myserver.xml failed.
Check log file created by FNDCPUCF program.
. . .
[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution. Errors are grouped by directory and phase.
The report format is:
AutoConfig could not successfully execute the following scripts:
afcpctx.sh INSTE8_PRF 1
AutoConfig is exiting with status 1
afcpctx.sh uploads you context file to the database by calling FNDCPUCF. I assuming FNDCPUCF stands for FND Concurrent Process Upload Context File but I can't seem to find much information on it. Since it fires off a concurrent request the next step was to check the log files generated for errors. The following error was in one of them:
Uploading context file...
Oracle error -1187: ORA-01187: cannot read from file 702 because it failed verification tests
ORA-01110: data file 702: '/oradata/dbf/oradata/temp02.dbf' has been detected in FND_GSM_UTIL.APPEND_CTX_FRAGMENT.
I haven't encountered that error before so but a quick search on Metalink turned up the following note:
Subject: RMAN After 10.2.0.2 Duplicate Database Gets Ora-1187 And Ora-1110 on TempFiles
Doc ID: Note:374934.1
In 9i tempfile information is removed from the database during the duplication process and you need to re-add them manually. In 10g, tempfiles are now included. However, if the files already exist on the OS then the tempfiles are not re-created and you will see errors in the alert log. So, in order to resolve this issue we dropped the tempfiles and re-added them (as per the metalink note).
To drop tempfiles:
alter database tempfile '/u01/oracle/mydb/temp01.dbf' drop;
To add a new one (assuming your temporary tablespace is named temp):
alter tempspace temp add tempfile '/u01/oracle/mydb/temp01.dbf' size 500M;
Once this was done, autoconfig completed without error and we restarted the application.