ORA-25152: TEMPFILE cannot be dropped at this time during rconfig

Scenario:

Conversion of single instance database in shared NFS storage to RAC/ASM using "rconfig" command. Database version is 11.2.0.3.0

Received following error in the "Setting TEMP tablespace" step.

<ErrorDetails>
             oracle.sysman.assistants.rconfig.engine.StorageCreationException: o
racle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-25152: TEMPFILE cannot be dropped at this time
Operation Failed. Refer logs at /u01/app/oracle/cfgtoollogs/rconfig/rconfig_05_14_13_21_18_17.log for more details.
</ErrorDetails>

Verification:

The database had 4 temp files out of which 2 were succesfully moved to ASM. The other two files had issues.

Normally this error is noticed while trying to drop a temporary tablespace which is used by users at the execution of the command.

In this case on RAC conversion there was not much chance to identify and kill the process using temp file.

Solution:

a) Drop the tempfiles before trying rconfig. 
b) Then restart the RAC conversion using rconfig. 
c) After RAC conversion add the temp files which was dropped earlier.

Use the following command to drop temp files.

alter database tempfile '<PATH>/temp02.dbf' drop including datafiles;


I dropped both the problematic tempfiles and then removed the files physically from ASM. Later I reinitiated the rconfig command and the conversion happened.

After RAC conversion add both tempfiles, which were dropped earlier.

alter tablespace temp2 add tempfile size 524288000; 

Erros During IMPDP: ORA-39070: Unable to open the log file

Scenario:

user is trying to import a dump file to a schema in Oracle 11.2.0.1.0, but ends up getting following error

Command used:

impdp system/<PWD> DIRECTORY=DUMP_DIR DUMPFILE=TEST1.DMP REMAP_SCHEMA=USER2:USER1 REMAP_TABLESPACE=USERS:test table_exists_action=replace LOGFILE=Test1_IMPORT.log 

ORA-39002: invalid operation 
ORA-39070: Unable to open the log file. 
ORA-29283: invalid file operation 
ORA-06512: at "SYS.UTL_FILE", line 536 
ORA-29283: invalid file operation 

Verification:

This is essentially a permission issue, because of which IMPDP command cannot complete its work.

We need to complete the following three steps to identify where the permission issue is.


1. Directory Name verification: From the dba_directories view the DUMP directory is physically present in the file system and the path name and case matches exactly.
2. DB user permissions: The DB user who is performing the import is SYSTEM, a DBA user who will have required permissions. Even all permissions were granted again.
3. Oracle user permissions: Check oracle user's (DB S/W Owner) permissions on the DUMP directory. Try to create a file in the DUMP directory as "oracle" user.

Reason:

The "oracle" user (DB S/W owner) did not have read/write permissions on the physical directory where dump file was placed (directory specified in IMPDP command)

Solution:

Grant read and write permission on the parent directory and its subdirectories to "oracle" user and restart the import.

ORA-39127: unexpected error from call to "EXFSYS"."DBMS_EXPFIL_DEPASEXP" during datapump export

Scenario:

A full database export using both conventional export utility and datapump has so many errors and warnings in their log file.

Conventional Export log


EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 14:
PLS-00201: identifier 'EXFSYS.DBMS_EXPFIL_DEPASEXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling EXFSYS.DBMS_EXPFIL_DEPASEXP.schema_info_exp


Datapump Export log


ORA-39127: unexpected error from call to "EXFSYS"."DBMS_EXPFIL_DEPASEXP"."SYSTEM_INFO_EXP"
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 316
ORA-06512: at "SYS.DBMS_METADATA", line 9263
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
ORA-39127: unexpected error from call to "EXFSYS"."DBMS_EXPFIL_DEPASEXP"."SYSTEM_INFO_EXP"
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 316


Reason: 


EXFSYS schema is missing in the database. To install the EXFSYS schema execute the script "catexf.sql" as SYS user.

Solution:

conn as sysdba and execute


@?/rdbms/admin/catexf.sql


Restart the export now. There would not be similar errors.

Interim Patch is holding the lock from this patch

Scenario:

Applying a forms patch on 10g AS. On running "opatch apply" the patching has failed due to

Interim Patch <PatchNo> is holding the lock from this patch <PatchNo>,
probably due to previous unsuccessful operation

Reason:

Opatch creates a file patch_locked under %ORACLE_HOME%\.patch_storage directory while applying a patch.If that patch application has been interrupted or canceled , or the patch has not been properly rollbacked, the patch_locked file may not be removed. Because of this subsequent opatch run can find this patch and will fail. 

Solution:

Delete or rename the patch_locked file. 

Reapply the patch using opatch utility.

AIA deployment Exception: ORA-01882: timezone region not found

Scenario:

AIA Foundation Pack installation on top of SOA 11.1.1.5.0 in a Windows server is failing during its deployment phase.

Error:

JBO-29000: Unexpected exception caught: java.sql.SQLDataException, msg=ORA-01882: timezone region not found

     [wlst]   File "D:\Middleware_App\FMW\AIA_App\Infrastructure\Install\AID\lib\py\deployApplication.py", line 49, in ?
     [wlst]   File "<iostream>", line 376, in activate
     [wlst]   File "<iostream>", line 1848, in raiseWLSTException
     [wlst] WLSTException: Error occured while performing activate : Error while Activating changes. : [HTTP:101216]Servlet: "HttpSoapService" failed to preload on startup in Web application: "AIAValidationSystemServlet".
     [wlst] oracle.jbo.JboException: JBO-29000: Unexpected exception caught: java.sql.SQLDataException, msg=ORA-01882: timezone region not found
     [wlst] at oracle.jbo.server.OracleSQLBuilderImpl.setSessionTimeZone(OracleSQLBuilderImpl.java:5541)


Reason: 

The timezone set or picked from AIA host is not valid in the AIA database or is or is not set to blank in the soa managed server.


Troubleshooting:

1. Verified that no Timezone is explicitly set for the SOA managed server

To verify => Navigate to WebLogic console -> Environment, Servers, soa_server1.

Check the Server Start tab, Arguments field.

In my case it was blank

2. Check the AIA database for current settings

Executed following SQL commands from AIA server to the AIA database

SQL> select dbtimezone from dual;

DBTIME
------
+00:00

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
--------------------------------------------------------
-05:00

3. Check the Timezone set for the server.

It was set to "central Time (US and Canada)"


Solution:

From the database I queried for possible Timezone names with names including Central (Since in O/S level it is set to Central Time, i filtered)

select * from v$timezone_names where tzname like '%entral%';

From the list, I decided to use "Canada/Central" as the data center hosting the server was in Canada


Then in the SOA_SERVER1 Arguments field (WebLogic console -> Environment, Servers, soa_server1 -> Server Start), I included the following line and saved.

-Duser.timezone=Canada/Central

To double confirm I restarted the SOA_SERVER1 managed server.

Re-initiated the AIA installation after cleaning up the previous failed installation. This time it was successful.