Notes based on my experience in Oracle, MySQL, SQL-Server and MongoDB databases. Created for my reference. Please test the codes before using in production.
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
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.
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;
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
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.
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);