Category: ORA- ERRORS

ORA-14404: partitioned table contains partitions in a different tablespace

Subscribe to continue reading

Become a paid subscriber to get access to the rest of this post and other exclusive content.

OEM Generic Alert Log Error With Message “Dumping Current Patch Information”

Database alert log shows entry of patch description injected in alert log (this is a RDBMS feature which is introduced)

Solution

From RDBMS side:

Set below parameters defined at the spfile or pfile level in the database, to avoid dumping the patch information in alert log.

-- To avoid dumping the parameters information
_log_segment_dump_parameter=FALSE

To avoid dumping the patches information
_log_segment_dump_patch=FALSE

— —- OR ——-

From OEM side :

– Go to Database target > Monitoring > Metric and Collection settings > Database alert log metric
– Click on Edit for ‘Generic Alert log Error’

– Bottom left hand corner there is a section called Alert Log Filter Expression’

– Modify the ‘Alert Log Filter Expression’ and replace with value below

.*ORA-0*(54|604|1142|1146)\D.*|Patch Description.*|$

This will capture all Patch Description.* entries in Database alert log and omit/suppress these alert (prevent alert from being triggered)

If you don’t want to miss error like ORA-00604, then modify the filter expression as needed.

.*ORA-0*(54|1142|1146)\D.*|Patch Description.*|$

This will not suppress any error like ORA-00604.

ORA-44305: service is running ORA-44311: service not running when STOP or DELETE a Service

In RAC environment, ‘DBMS_SERVICE.ALL_INSTANCES’ is required to stop or delete a service. Otherwise ORA-44305 or ORA-44311 will occur.

SQL>  exec DBMS_SERVICE.DELETE_SERVICE('TEST_SERVICE');
BEGIN DBMS_SERVICE.DELETE_SERVICE('TEST_SERVICE'); END;

*
ERROR at line 1:
ORA-44305: service  TEST_SERVICE is running
ORA-06512: at "SYS.DBMS_SERVICE", line 68
ORA-06512: at "SYS.DBMS_SERVICE", line 458
ORA-06512: at line 1


SQL>  exec DBMS_SERVICE.STOP_SERVICE('TEST_SERVICE');
BEGIN DBMS_SERVICE.STOP_SERVICE('TEST_SERVICE'); END;

*
ERROR at line 1:
ORA-44311: service TEST_SERVICE not running
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 40
ORA-06512: at "SYS.DBMS_SERVICE", line 519
ORA-06512: at line 1


SQL>  exec DBMS_SERVICE.STOP_SERVICE('TEST_SERVICE',DBMS_SERVICE.ALL_INSTANCES);

PL/SQL procedure successfully completed.

SQL>  exec DBMS_SERVICE.DELETE_SERVICE('TEST_SERVICE');

PL/SQL procedure successfully completed.

SQL>  select SERVICE_ID,NAME,NETWORK_NAME,CREATION_DATE,ENABLED,PDB,GLOBAL_SERVICE from dba_services;

ORA-15046: ASM file name ‘+DATA2/TESTDB/PASSWORD/pwdtestdb.256.1157213397’ is not in single-file creation form

When relocating an ASM password file, the following errors occur:

ASMCMD>  pwcopy --dbuniquename TESTDB '+DATA1/TESTDB/PASSWORD/pwdtestdb.256.1157213397' '+DATA2/TESTDB/PASSWORD/' -f
...
..
.
ORA-15046: ASM file name '+DATA2/TESTDB/PASSWORD/pwdtestdb.256.1157213397' is not in single-file creation form
...
..
.

The destination name cannot be an OMF format. so the command should be like :

ASMCMD>  pwcopy --dbuniquename TESTDB '+DATA1/TESTDB/PASSWORD/pwdtestdb.256.1157213397' '+DATA2/TESTDB/PASSWORD/pwdtestdb.pwfile' -f

ORA-25152: TEMPFILE cannot be dropped at this time

Check whether the tempfile is being used by users.

SQL> show parameter db_files
SQL> SELECT file#, name from v$tempfile;
SQL> SELECT s.sid, s.serial#, s.username, s.status, u.tablespace, u.segfile#, u.contents, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
ORDER BY u.tablespace, u.segfile#, u.segblk#, u.blocks;

SQL> select sid, username, osuser, program, machine from v$session where sid in (<sid returned from above query>);

The segfile# from v$sort_usage corresponds to the sum of the value for parameter db_files and the value for file# from v$tempfile.

Close / Kill the sessions referenced in the query.

SQL> SELECT s.sid, s.serial#, s.username, s.status, u.tablespace, u.segfile#, u.contents, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
ORDER BY u.tablespace, u.segfile#, u.segblk#, u.blocks;  

no rows selected

SQL> alter database tempfile '+DATA/TESTDB/TEMPFILE/temp.264.1157213559' drop including datafiles;

Database altered.