ORA-07445: exception encountered


What is the significance of the error ORA-7445?

[oracle@dbtest ~]$ oerr ora 7445
07445, 00000, "exception encountered: core dump [%s] [%s] [%s] [%s] [%s] [%s]"
// *Cause: An OS exception occurred which should result in the creation of a core file.  This is an internal error.
// *Action: Contact your customer support representative.


An ORA-7445 error is raised by an Oracle server process when it has received a fatal signal from the operating system.
ORA-7445 errors are likely caused by OS problems rather than an Oracle problem.
An ORA-7445 is a generic error, and can occur anywhere in the Oracle code.  

Before contacting the customer support, Read the following MOS notes to understand better on the error ORA-7445


ORA-16957: SQL Analyze time limit interrupt

Cause:
The ORA-16957 error is an internal error code used to indicate that SQL analyze has reached its time limit.

Action:
According to MOS Doc ID 1275248.1  , 
(This is an unpublished bug. The bug is fixes in version 12.1)
The error ORA-16957: SQL Analyze time limit interrupt, can be ignored safely, as it does not indicate any problems with the database.

If the error ORA-16957: SQL Analyze time limit interrupt comes along with
ORA-00600: internal error code, arguments: [17092], [16957], [], [], [], [], [], []
you cannot simply ignore it, you have to  read the below MOS note and apply recommended patches:

How to configure ASM in windows?

Automatic Storage Management, or ASM, is a facility provided with the Oracle database for managing your disks. It is an Oracle-aware logical volume manager, or LVM, that can stripe and mirror database files and recovery files across a number of physical devices. This is an area where the database administration domain
overlaps with the system administration domain.
Many databases will not use ASM: they will store their files on the volumes provided by the operating system, which may well be managed by an LVM. But if you do not have a proper logical LVM, as will probably be the case with low-end systems running on, for example, Linux or Windows, then ASM provides an excellent (and bundled) alternative to purchasing and installing one. On high-end systems, ASM can work with whatever LVM is provided by the operating system.

You can play with ASM even in your local computer by creating virtual ASM disks. There are different ways to do it in windows and Linux. Here I am explaining a method to create asm disks in window.

You can just follow the steps given below.


Step 1: Creating a dummy disks

We need 1G free space to do this demo. We are going to create two dummy disks of 512 MB.


(Assuming D:  drive is having more than 1G free space )

Create a directory called asmdisks in D:\ drive 


D:\>cd asmdisks
D:\asmdisks>asmtool -create F:\asmdisks\ disk1 512
D:\asmdisks>asmtool -create F:\asmdisks\ disk2 512



Now you have 2 dummy disks  of 512MB

Step 2:  Configuring Cluster Synchronization Service

C:\>c:\oracle\product\ 10.2.0\db_ 1\BIN\localconfi g add

Step 1: stopping local CSS stack
Step 2: deleting OCR repository
Step 3: creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'ap\arogyaa' , privgrp ''..
Operation successful.
Step 4: creating new CSS service
successfully created local CSS service
successfully reset location of CSS setup



step 3: Creating init file (pfile)

 Save a file as "C:\oracle\product\ 10.2.0\db_ 1\database\ init+ASM. ora" with the following lines in it.

INSTANCE_TYPE= ASM
DB_UNIQUE_NAME= +ASM
LARGE_POOL_SIZE= 8M
ASM_DISKSTRING= 'D:\asmdisks\ *'
_ASM_ALLOW_ONLY_ RAW_DISKS= FALSE

Step 4: Creating service and password file


c:\> oradim -NEW -ASMSID +ASM -STARTMODE auto
c:\> orapwd file=C:\oracle\ product\10.2.0\db_1\ database\ PWD+ASM.ora password=oracle

Step 5:  Creating  ASM disk group and enter in parameter file
 Create asm disk group


set ORACLE_SID=+ASM
sqlplus / as sysdba
SQL> select path, mount_status from v$asm_disk;
PATH                  MOUNT_STATUS
--------------------- -------------
D:\ASMDISKS\DISK1     CLOSED

D:\ASMDISKS\DISK2     CLOSED

SQL> create diskgroup D1 external redundancy disk
 'D:\ASMDISKS\ DISK1',
 'D:\ASMDISKS\ DISK2';
Diskgroup created.


create spfile from pfile and add newly created diskgroup in to it.


SQL> create spfile from pfile;
SQL> startup force;
SQL> alter system set asm_diskgroups= D1 scope=spfile;

SQL> startup force
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1247420 bytes
Variable Size 57472836 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted

SQL>


Your AMS instance is ready with ASMDISKS mounted. You can use this disk to store ASMFILES.

how to check status of import ?

Here is the script to monitor the import activity in oracle. Using this script you can monitor how fast is the import activity going on.


select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;

Fractured block found during backing up datafile

It happens while taking RMAN backup.
If RMAN is unable to get a consistent image of the block it would report errors saying found fractured block while trying to backup that block. RMAN would however retry again and see if it is now able to get the consistent image of the block. we can see in Alert log file that it has found a valid block.

Please note it’s recommended to run the RMAN backup when the load on the database is less.

RMAN reads Oracle blocks from disk. If it finds that the block is fractured,  which means it is being actively used, it performs a reread of the block. If that fails again then the block is assumed to be corrupt.

cause: RMAN backups of datafile are being performed while the datafile is involved in heavy I/O.

fix: Run the backups when the tablespace has less I/O activity.


Alert log:
Thu Sep 05 21:04:54 2013
Hex dump of (file 5, block 259976) in trace file /sw/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5786.trc
Corrupt block relative dba: 0x0143f788 (file 5, block 259976)
Fractured block found during backing up datafile
Data in bad block:
type: 40 format: 2 rdba: 0x0143f788
last change scn: 0x056f.3979ebee seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x40a82802
check value in block header: 0x83eb
computed block checksum: 0xaa46

Reread of blocknum=259976, file=/data/oracle/oradata/orcl/users01.dbf. found valid data

How to move queue (AQ) tables?

Normally to move a table from one tablespace to another, we will use the following command

ALTER TABLE <TableName> MOVE TABLESPACE <NewTablespaceName>;

But we cannot move Advanced Queuing table in this way.

To get information on advanced queue tables, we can query against dba_queue_tables .

We have to install a new package to move queue tables from one tablesapce to another. 

There are separate packages available in the Oracle Support Website for different Oracle versions.


For Oracle Database - Enterprise Edition - Version 8.1.7.0 to 10.2.0.5 [Release 8.1.7 to 10.2] 
Visit the below given MOS Note



From this note, we have to download a plb file for your database version and run it to install a new package named move_aqt

Then we can run the package as given below, with the owner name, queue table name and the name of the tablespace where the table has to be moved.

exec move_aqt.move('<OWNER>','<QUEUE_TABLE_NAME>', '<TABLESPACE_NAME>');

More information is available in the note given above.


For Oracle Database - Enterprise Edition - Version 10.2.0.5 to 11.2.0.3 [Release 10.2 to 11.2]
visit the following MOS note.

How to perform an Online Move of Advanced Queueing Tables using DBMS_REDEFINITION (Doc ID 1410195.1)

From this note, we have to download a plb file for your database version and run it to install a new package named move_qt_pkg

Then run the package with parameters as given below.

BEGIN
move_qt_pkg.move_queue_table('OWNER','QUEUE_TABLE_NAME','FROM_TABLESPACE','TO_TABLESPACE');
END;
/


More information is available in the note given above.
------------------------------------------------------------------------

If you are trying to move all objects from a tablespace where AQ tables exists, you may encounter the following error.

ALTER TABLE "APPS"."JTF_PF_LOGGING_TABLE" MOVE TABLESPACE APPS_TS_QUEUES STORAGE (INITIAL 524288 NEXT 524288) NOPARALLEL NOLOGGING 
fails with
ORA-00904: "SYS_NC00090$": invalid identifier

There is nothing to worry,  according to 


This table is not used in R12 and can be safely dropped running:

DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'JTF_PF_LOGGING_TABLE', force => TRUE);