Category: Database

This section related to Oracle database different version

ORA-12054 while trying to create materialized view with sysdate

Posted on by Osama Mustafa in Database
while trying to configure materialized view with sysdate today , Fast Refresh on commit , i received the following error :-

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view.

Regarding to Oracle Documentation there is some restriction should be considered while trying to create materialized view Check Here  or here.

But there is always workaround,

1- i create dummy table contain 1 row with 1 column only contains sysdate.
2- create Job that this sysdata on daily basis.
3- call this column in where condition of materialized view.

Problem solved.

Thanks
Osama

Import hangs On index level.

Posted on by Osama Mustafa in Database
Today some customer contact me complaining that they are trying to sync the development with UAT, when i check the issue everything was normal for the first time, but the alert logs indicate that database could complete the checkpoint.

so this what i did to make the import faster ( which i completed it as well ) :-

  • Change database to no archive mode.
  • increase sga_target temporary.
  • use parallel command depend on cpu core.
  • Use commit=N with import parameters.
  • you can exclude indexes by exclude=indexes but i don’t prefer this at all.
  • increase db_cache_size.
This simple steps that could help someone to make import little faster or solve hang issue.
let me know if you need anything 🙂
Cheers
Merry Christmas guys 
Osama

ORA-01502 index or partition of such index is in unusable state

Posted on by Osama Mustafa in Database
The following error appeared on application side while trying to deploy the new SRF on the application, as you see from the above error it’s database error and to solve it follow the below steps :-

from database side :-

sqlplus /as sysdba

From the above picture check the index name and put it inside this query :-

select owner,index_name,table_name,status from dba_indexes where index_name=’index_name’;

OWNER    INDEX_NAME    TABLE_NAME    STATUS
—–    ———-    ———-    ——
owner    index_name     table_name    INVALID

alter index ndex_name rebuild;

Try again

Cheers
Osama 

ORA-00845: MEMORY_TARGET not supported on this system

Posted on by Osama Mustafa in Database

SQL> alter system set memory_max_target=2GB scope=spfile;
System altered.
SQL> alter system set memory_target=2GB scope=spfile;
System altered.

 SQL> startup ;

ORA-00845: MEMORY_TARGET not supported on this system

Check the following Space :-

ECATSTDB oracle: :/home/oracle\>df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/Oracle-LogVol00
                       20G  3.3G   15G  18% /
/dev/mapper/Oracle-LogVol02
                       90G   45G   41G  53% /Oracle
/dev/sda1              99M   18M   77M  19% /boot
tmpfs                 3.0G     0  3.0G   0% /dev/shm

Increase the space

mount -t tmpfs shmfs -o size=6144m /dev/shm

now startup the database.

Cheers
Osama

ORA-39181: Only partial table data may be exported due to fine grain access control

Posted on by Osama Mustafa in Database
The below error appeared when trying to export the Schema using System User :-

ORA-39181: Only partial table data may be exported due to fine grain access control on “SOAPRD_MDS”.”MDS_DEPL_LINEAGES”
. . exported “SOAPRD_MDS”.”MDS_DEPL_LINEAGES”            5.164 KB       1 rows
ORA-39181: Only partial table data may be exported due to fine grain access control on “SOAPRD_MDS”.”MDS_LABELS”

the weird thing that VPD is not enabled to show this error but once i grant the system the required privileges the error stop appearing in the logs.

grant EXEMPT ACCESS POLICY to System;

Thanks
Osama

Enable Database Vault Using chopt Command

Posted on by Osama Mustafa in Database
I post before how to enable oracle database vault using make command, in this post i will talk about how to enable database vault but using chopt command which easier and faster.

Common Syntax :-
chopt [enable | disable] db_option

Value
Description
dm
Oracle Data Mining Database Files
dv
Oracle Database Vault
lbac
Oracle Label Security
olap
Oracle OLAP
partitioning
Oracle Partitioning
rat
Oracle Real Application Testing
ode_net
Oracle Database Extensions for .NET 1.x
ode_net_2
Oracle Database Extensions for .NET 2.0

Like the other way you need to shutdown database, listener and dbconsole
cd $ORACLE_HOME/bin
chopt enable lbac
and Start database, listener, and dbconsole , Enterprise should be configured to access database vault.
Cheers
osama 

ACFS-9459/ACFS-9201/ACFS-9459 Gird Infrastructure

Posted on by Osama Mustafa in Database
Environment: –

Operating system : Oracle Linux 6.6
Oracle DB and Grid Version : 11.2.0.4.0

The Error that appeared Under Gird log :-

[client(1624)]CRS-10001:25-Mar-15 14:09 ACFS-9459: ADVM/ACFS is not supported on this OS version: ‘3.8.13-44.1.1.el6uek.x86_64’
[client(1626)]CRS-10001:25-Mar-15 14:09 ACFS-9201: Not Supported
[client(1748)]CRS-10001:25-Mar-15 14:09 ACFS-9459: ADVM/ACFS is not supported on this OS version: ‘3.8.13-44.1.1.el6uek.x86_64’

Solution: –

There is patch should be applied “Patch 16318126: UEK3 SUPPORT FOR ACFS”

Download the patch from https://support.oracle.com

and check the document
ACFS Support On OS Platforms (Certification Matrix). (Doc ID 1369107.1)

Cheers
Osama mustafa