Oracle by Anand: ORA Error

Showing posts with label ORA Error. Show all posts

ORA-39181: Only partial table data may be exported due to fine grain access control on "TEST"."CARDS"

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

Cause

This is expected behavior

ORA-39181 is caused by an unprivileged user who tries to export a table with a fine grain access control policiy applied.

Solution

To avoid this:

Grant the privilege EXEMPT ACCESS POLICY to the exporting user

-or-

Disable the VPD policy.

Extract of Logs

expdp admin@ORCL1 parfile=ORCL_TEST_EXP.par

Export: Release 12.1.0.2.0 - Production on Fri Mar 9 10:13:33 2018

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Starting "ADMIN"."SYS_EXPORT_SCHEMA_01": admin/********@ORCL1 parfile=ORCL_TEST_EXP.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 375.4 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/RLS_POLICY/RLS_POLICY
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

.....................................................................................

ORA-39181: Only partial table data may be exported due to fine grain access control on "TEST"."TE_CLAIM"
. . exported "TEST"."TE_CLXIM"                        37.48 KB      23 rows
. . exported "TEST"."TE_CLXIMBUSINESSDXTX"            6.882 KB      24 rows
. . exported "TEST"."TE_CLXIMOT"                      7.523 KB      23 rows
. . exported "TEST"."TE_CLXIMSEXRCHIDX"               14.92 KB      23 rows
. . exported "TEST"."TE_CLEXRINGXDDDXTX"              15.82 KB     324 rows
. . exported "TEST"."TE_CLEXRINGITEM"                 7.406 KB      48 rows
. . exported "TEST"."TE_CLEXRINGRECORD"               7.171 KB      48 rows
ORX-39181: Only partial table data may be exported due to fine grain access control on "TEST"."TE_DOCUMENT"

.....................................................................................

Master table "ADMIN"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ADMIN.SYS_EXPORT_SCHEMA_01 is:
/u01/exp/ORCL/ORCL_TEST.dmp
Job "ADMIN"."SYS_EXPORT_SCHEMA_01" completed with 21 error(s) at Fri Mar 9 10:14:55 2018 elapsed 0 00:01:12

ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

ORCL1> show parameter spfile

NAME TYPE VALUE
------------------------------------ --------------- ------------------------------
spfile string +DATA01/ORCL/spfile

ORCL1> alter system set db_unique_name="ORCLP" scope=spfile sid='*';
alter system set db_unique_name="ORCLP" scope=spfile sid='*'
*
ERROR at line 1:

ORA-32017: failure in updating SPFILE
ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

Bug 20876249 : COULD NOT MODIFY DB_UNIQUE_NAME RESOURCE EXISTS

Workaround

1. Manually edit the pfile and added the entry

*.db_unique_name='ORCLP'

2. startup the db using the pfile

SQL> startup pfile='/local/dbms/oracle/product/12.1.0.2/db_1/dbs' ;

3.created the spfile from the pfile

SQL> create spfile='+DATA01/ORCL/spfile' from
pfile='/local/dbms/oracle/product/12.1.0.2/db_1/dbs';

File created.

4.set the spfile parameter
SQL> alter system set spfile='+DATA01/ORCL/spfile' ;
System altered.

ORA-54013 ON FLASHBACK TABLE WITH VIRTUAL COLUMNS

ORA-54013: INSERT operation disallowed on virtual columns

Solution:

Oracle does not support direct flashback on a table with virtual column. the reason for the same is that the values for virtual column are derived rather than being stored on disk and there is no undo for them.

Virtual column is not officially supported by flashback table feature. ( Including 12c )

Refer Metalink note Doc ID 1943791.1 for more details

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

ORA-1652: unable to extend temp segment

Error

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl1p:

*************************************************************************

2017-09-06 08:39:08.701000 +02:00

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP [CITI]

Solution

You can resize the tempfile if it is restricted to limited size

SQL> alter database tempfile '/u01/app/oradata/temp01.dbf' resize 10240M

You can now configure the tempfile to grow up to certain size or up the maximum size allowed by oracle (32G)

SQL> alter database tempfile '/u01/app/oradata/temp01.dbf' autoextend on next 100m maxsize 20480;

SQL> alter database tempfile '/u01/app/oradata/temp01.dbf' autoextend on next 100m maxsize unlimited;

If your tempfile already reached to maximum allowed file size limit then you must add new tempfile

Add tempfile in your temp tablespace to allow the TEMP tablespace to grow further

SQL> alter tablespace temp add tempfile '/u01/app/oradata/temp02.dbf' size 10240M reuse autoextend on next 100m maxsize unlimited;

ORA-03113: end-of-file on communication channel

How do I fix this ORA-03113 error?

I executed oerr Utility and here is the Output

$oerr ora 03113

ORA-03113: end-of-file on communication channel

Cause: The connection between Client and Server process was broken.

Action: There was a communication error that requires further investigation.

So as you can see from Action it is clearly pointing that it is a communication error which requires further Investigation

I will give you here some importent tip which will help you to solve this issue

open the database alertlog and look for any additional error message. Rosolving the additional Problem apearing in the alertlog will resolve this issue.

There could be several possible reasons of ORA-03113

Server machine crashed
Oracle internal Errors
Flash recover area is full
Process has been aborted
Session has been killed
Client incorrectly handling multiple Connections
Network related issues
Oracle Database is crashed or crashing while starting
etc.etc.etc...

As you can see the list is quite Long and therefore to successfully troubleshoot the issue you must look into the database logfile and/or tracefile and try to grap the additional error message which is causing ORA-03113.

If you have any question or you are struggeling with ORA-03113 leave me your message/comment in comment box below. I will feel houner to assist you to resolve your queries/issues

ORA-12578: TNS:wallet open failed

Are you facing ORA-12578: TNS:wallet open failed. No Problem you are at right place then.

$ sqlplus /@ORCL1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 24 17:51:12 2017

ERROR:
ORA-12578: TNS:wallet open failed

Easiest way to resolve the issue by changing WALLET_OVERRIDE = TRUE to FALSE in SQLNET.ORA

edit your sqlnet.ora file and modify SQLNET.WALLET_OVERRIDE parameter value from TRUE to FALSE.
Change this:

SQLNET.WALLET_OVERRIDE = TRUE

to this:

SQLNET.WALLET_OVERRIDE = FALSE

Try Again.

Hope this helps

ORA-44787: Service cannot be switched into

Hot Fix ORA-44787: Service cannot be switched into

SQL> alter session set container=GIMR_DSCREP_10;
ERROR:
ORA-44787: Service cannot be switched into.

Cause: The service did not exist in the new pluggable database or the service was not started in the pluggable database.

Action: Use a valid service name for the pluggable database or start the service in the pluggable database first.

The Easiest way to resolve this issue is, Restart the PDB.

Note:- Please be Aware of the fact that restarting the PDB will lead to downtime of the PDB

SQL> alter pluggable database GIMR_DSCREP_10 close immediate;

Pluggable database altered.

SQL> alter pluggable database GIMR_DSCREP_10 open;

Pluggable database altered.

SQL> alter session set container=GIMR_DSCREP_10;

Session altered.

ORA-00905: missing keyword

How to fix ORA-00905: missing Keyword

ERROR at line 1:
ORA-00905: missing Keyword

Cause

You tried to execute a statement, but you missed a required Keyword

Resolution

Verify the Syntax correct the Errors and rerun it.

An example

QL> create diskgroup DATA normal redundency FAILGROUP FG1 disk '/dev/oracleasm/disks/DISK5' FAILGROUP FG2 '/dev/oracleasm/disks/DISK6';

create diskgroup DATA normal redundency FAILGROUP FG1 disk '/dev/oracleasm/disks/DISK5' FAILGROUP FG2 '/dev/oracleasm/disks/DISK6'
*
ERROR at line 1:
ORA-00905: missing Keyword

Findings

1> spelling mistake at keword redundency (redundancy)

2> missing Keyword disk after FG2

Corrected Version of the command executed successfully

create diskgroup DATA normal redundancy FAILGROUP FG1 disk '/dev/oracleasm/disks/DISK5' FAILGROUP FG2 disk '/dev/oracleasm/disks/DISK6';

Diskgroup created.

ORA-00018: maximum number of sessions exceeded

How to Fix ORA-00018: maximum number of sessions exceeded

Cause: All session state objects are in use.

Action: Increase the value of the SESSIONS initialization Parameter.

Steps.

sqlplus / as sysdba

SQL: show parameter sessions

500

SQL: create pfile='/tmp/initORCL.ora' from spfile;

SQL: Alter System set sessions= 700 scope=both;

ORA-00020: maximum number of processes (string) exceeded

How to Resolve ORA-00020: maximum number of processes (string) exceeded

Cause: All process state objects are in use.

Action: Increase the value of the PROCESSES initialization Parameter.

Steps:

sqlplus / as sysdba

SQL: show parameter processes

300

Backup your init file before changing the Parameter

SQL: create pfile='/tmp/initORCL.ora' from spfile;

SQL: Alter System set processes= 600 scope=spfile;

SQL: shutdown immediate

SQL: Startup

Note:- Since processes is an static Parameter you must restart the DB / Instance to bring the modified value in effect

Disk "DISK1" already exists

#oracleasm createdisk disk1 /dev/sdb6

Disk "DISK1" already exists

Solution:

ASM Disk Name in the createdisk command is already in use. Use the disk Name which is not in use.

#oracleasm createdisk disk2 /dev/sdb6

How to find the ASM Disk Name used by ASM Lib

To find the list of disk names already in use by Oracle ASMLib run below command as root user

#oracleasm listdisks

ORA-30036: unable to extend segment by 8 in undo tablespace

How to Resolve ORA-30036

Cause :- Undo tablespace is full.

Check how much free space availiable in UNDO tablespace

SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024/1024 "FREE SPACE(GB)"
FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='UNDOTBS1' GROUP BY TABLESPACE_NAME;

Solution:- Extend undo tablespace.

There are two possibilities

Add a New datafile to UNDO tablespace,

ensure you have sufficient space under /mount_point where you are adding the datafile.
adjust path, size, autoexend etc according to your requirement

alter tablespace UNDOTBS1 add datafile '/ora_app/product/oradata/oemrp/undotbs02.dbf' size 100M autoextend on next 100M maxsize unlimited;

Tablespace altered.

2. Resize the existing datafile if possible

alter database datafile '/ora_app/product/oradata/oemrp/undotbs01.dbf' resize 1G;
Database altered.

Error: ORA-16541: database is not enabled

ORA-16541: database is not enabled

DGMGRL> disable configuration;
Disabled.
DGMGRL> enable configuration;
ORA-16541: database is not enabled

Configuration details cannot be determined by DGMGRL
DGMGRL> remove configuration;
Error: ORA-16541: database is not enabled

Failed.
DGMGRL> show configuration;
ORA-16541: database is not enabled

Configuration details cannot be determined by DGMGRL

The configuration was already broken and therefore you need to recreate the configuration

Solution:-
Remove the configuration from partner database eg if remove configuration is failing from primary try remove configuration from standby

DGMGRL> remove configuration
Removed configuration

If you try to create the configuration from standby you will encounter below error therefore please always create the configuration from current primary

DGMGRL> CREATE CONFIGURATION 'ANDY_conf' AS PRIMARY DATABASE IS 'ANDYS1' CONNECT IDENTIFIER IS 'ANDYS1.oracle.com';
Error: ORA-16584: operation cannot be performed on a standby database

DGMGRL> CREATE CONFIGURATION 'ANDY_conf' AS PRIMARY DATABASE IS 'ANDYS1' CONNECT IDENTIFIER IS 'ANDYS1.oracle.com';
Configuration "ANDY_conf" created with primary database "ANDYS1"
DGMGRL>
DGMGRL> ENABLE CONFIGURATION;
Enabled.

DGMGRL> ADD DATABASE ANDYP AS CONNECT IDENTIFIER IS 'ANDYP.oracle.com' MAINTAINED AS PHYSICAL;
Database "ANDYP" added
DGMGRL> show configuration;

Configuration - ANDY_conf

Protection Mode: MaxAvailability
Databases:
ANDYS1 - Primary database
ANDYP - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> ENABLE DATABASE ANDYP
Enabled.
DGMGRL> show configuration;

Configuration - ANDY_conf

Protection Mode: MaxAvailability
Databases:
ANDYS1 - Primary database
ANDYP - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

ORA-00261: log 12 of thread 1 is being archived or modified

drop standby logfile group reporting ORA-00261 and ORA-00312

------------------------------------------------------------------------------------------------------------

SQL> alter database drop standby logfile group 12;
alter database drop standby logfile group 12
*
ERROR at line 1:
ORA-00261: log 12 of thread 1 is being archived or modified
ORA-00312: online log 12 thread 1:
'/ora01_data1/STDBY1P/flash/STDBY1PP/onlinelog/o1_mf_12_bohc1hts_.log'

Solution

SQL> alter database clear logfile group 12;

Database altered.

SQL> alter database drop standby logfile group 12;

Database altered.

SQL> alter database add standby logfile group 12 '/ora04_STDBY1P/data1/redo/STDBY_redoG12M1.rdo';

Database altered.

ORA-19809: limit exceeded for recovery files:RMAN Duplicate

Error Message
-----------------------------------------------------------------------------------------
ORACLE error from auxiliary database: ORA-19809: limit exceeded for recovery fils
ORA-19804: cannot reclaim 1073741824 bytes disk space from 5242880000 limit
RMAN-05535: WARNING: All redo log files were not defined properly.
-----------------------------------------------------------------------------------------
Cause:-
RMAN fails to restore the archive logs used for media recovery because it is not able to allocate space in FRA to restore those archive logs because of mismatch size of FRA between target and auxiliary database.
Also this is listed as oracle bug
Bug 13741583 – RMAN duplication erroneously fails with ORA-19804 using fast recovery area [ID 13741583.8].

------------------------------------------------------------------------------------------------
Solution:
Increase the FRA size at auxiliary database to equals or grater than the target database

ORA-16792 configuration property value is inconsistent with database setting

Having datagurad broker enabled it is recommended to change any parameter using DGMGRL only if not done so there might be a possibility that the values of one or more configuration properties were inconsistent with database in-memory settings or server parameter file settings

DGMGRL> show configuration

Configuration - PRODCONFIG

Protection Mode: MaxPerformance

Databases:

PROD1P- Primary database

PROD1PS1- Physical standby database

Warning: ORA-16792: configurable property value is inconsistent with database setting

Fast-Start Failover: DISABLED

Configuration Status:

WARNING

Solution:

Step1> Disable and enable broker

first on standby

SQL[PROD1PS1]> alter system set dg_broker_start=false

Wait for a minute and the enable

SQL[PROD1PS1]> alter system set dg_broker_start=true

Check if the problem is resolved

Step2> If the problem not resolved after step 1 perform the step1 for primary as well and again check if the problem is resolved

Step3> If the problem is not resolved after step 2 also then

Identify the parameter which is inconsistent and manually set the parameter using dgmgrl if eg. say the db_file_name_convert is inconsistent then correct as mentioned below

DGMGRL> edit DATABASE "PROD1P" SET PROPERTY DbFileNameConvert = ‘/u01/app/Oradata’, ‘/u02/app/Oradata’