Category: Ora-Errors

ORA-09925: Unable to create audit trail file

PROBLEM:

Users are getting below error, while trying to connect to the database.

ERROR:
ORA-09925: Unable to create audit trail file
Linux-ia64 Error: 28: No space left on device
Additional information: 9925
ORA-01075: you are currently logged on

SOLUTION:

The error occurs , because the mount point where audit logs are written is filled.

check the mount point :

[oracle@ram ~]$ df -h

Filesystem    Size     Used     Avail   Use%    Mounted on
/dev/sda2      20G     9.6G      8.7G    53%      /
tmpfs          3.0G    276K      3.0G    1%       /dev/shm
/dev/sda1      194M    105M      79M     58%      /boot
/dev/sda5      45G     40G       3.4G    93%      /u01
.host:/        293G    203G      91G     70%      /mnt/hgfs

[oracle@ram ~]$

We can see that mount point is filled, so database is not able to write audit logs in adump location.

To fix this, clear space from that mount point And make sure free space is available for the audit logs.

Once space is available, user can connect easily.

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba                       FB Group:https://www.facebook.com/groups/894402327369506/                   FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba

ORA-00257:archiver error, connect internal only until freed

PROBLEM:

Application log shows below error and users are unable to connect to the database.

0RA-00257:archiver error, connect internal only until freed
ORA-16014:log 1 sequence# 480 not archived, no available destinations
ORA-00312:online log 1 thread 1:’/data/oradata/orcl/redo01.log’

SOLUTION:

This error comes, when the archive destination is full and there is no space to accommodate new archive logs.

Check archive dest location.

There are different solution for this.

OPTION 1:  Try to delete old archive logs to free up space.

rman target /

delete archivelog all completed before 'sysdate-3';

OPTION 2:  Change the archive log location:

In case you can’t delete the archive logs from existing location, then we can change the archive dest to a new mount point.

SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
log_archive_dest_1                   string      LOCATION=/archive/POCD


SQL> alter system set log_archive_dest_1='LOCATION=/dump/arch' scope=both;

System altered.


SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------
log_archive_dest_1                  string      LOCATION=/dump/arch
-- Switch logfile

alter system switch logfile;

After implementing any one of the solutions, the database may take few minutes to resume archiving and normal operation.

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                       FB Group:https://www.facebook.com/groups/894402327369506/                   FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba

TNS-12542: TNS:address Already In Use

PROBLEM:

While starting the listener, getting an error like

TNS-12542: TNS:address already in use

# lsnrctl start LISTENER_TEST

LSNRCTL for Solaris: Version 12.1.0.2.0 - Production on 03-SEP-2018 11:06:57

Copyright (c) 1991, 2017, Oracle.  All rights reserved.

Starting /oracle/app/oracle/product/12.1.0.2/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 12.1.0.2.0 - Production

System parameter file is /oracle/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora

Log messages written to /oracle/app/oracle/diag/tnslsnr/ram.doyen.com/listener_test2/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ram.doyen.com)(PORT=1524)))

Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ram.doyen.com)(PORT=1524)))

TNS-12542: TNS:address already in use 

TNS-12560: TNS:protocol adapter error

  TNS-00512: Address already in use

   Solaris Error: 125: Address already in use

Listener failed to start. See the error message(s) above...

SOLUTION:

To find out the issue, Let’s check the content of the listener in listener.ora file.

LISTENER_TEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.doyen.com)(PORT = 1524)) --- >>> 
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.doyen.com)(PORT = 1524)) --- >>>

)
)

SID_LIST_LISTENER_TEST =
(SID_LIST =
(SID_DESC =
(SID_NAME = FRANCE)
(ORACLE_HOME = /oracle/app/oracle/product/12.1.0.2/dbhome_1)
)
)

Inside the listener entry, we have 2 ADDRESS entries with same host(ram.doyen.com) and same port number(1524) .

So starting the listener is failing with conflict.

To fix the issue, give different ports for both the ADDRESS entries.

the listener will look as below:( 1524 and 1526)

LISTENER_TEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.doyen.com)(PORT = 1524)) --- >>> 
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.doyen.com)(PORT = 1526)) --- >>>
)
)

SID_LIST_LISTENER_TEST =
(SID_LIST =
(SID_DESC =
(SID_NAME = FRANCE)
(ORACLE_HOME = /oracle/app/oracle/product/12.1.0.2/dbhome_1)
)
)

Now start the listener.

# lsnrctl start LISTENER_TEST

LSNRCTL for Solaris: Version 12.1.0.2.0 - Production on 03-JAN-2019 12:08:09

Copyright (c) 1991, 2017, Oracle. All rights reserved.

Starting /oracle/app/oracle/product/12.1.0.2/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 12.1.0.2.0 - Production
System parameter file is /oracle/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/ram.doyen.com/listener_test/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ram.doyen.com)(PORT=1524)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ram.doyen.com)(PORT=1526)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ram.doyen.com)(PORT=1524)))
STATUS of the LISTENER
------------------------
Alias LISTENER_TEST
Version TNSLSNR for Solaris: Version 12.1.0.2.0 - Production
Start Date 03-JAN-2019 12:08:09
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/ram.doyen.com/listener_test/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ram.doyen.com)(PORT=1524)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ram.doyen.com)(PORT=1526)))
Services Summary...
Service "FRANCE" has 1 instance(s).
Instance "FRANCE", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Listener started successfully.

Listener started successfully and listening on both 1524 and 1526 port. So in simple words, port should be unique for each ADDRESS entry of the listener.

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                        FB Group:https://www.facebook.com/groups/894402327369506/                 FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba

ORA-01950: no privileges on tablespace ‘USERS’

PROBLEM:

While creating a table using CTAS, got an error,                                                    ORA-01950: no privileges on tablespace ‘USERS’.

SQL> create table FRANCE.EMPLO as select * from user_objects;
create table EMPLO as select * from user_objects
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’

SOLUTION:

This error comes, when the user the user doesn’t have tablespace quota.

1. Check the tablespace quota.

select username,tablespace_name,bytes/1024/1024/1024, MAX_BYTES/1024/1024/1024 from dba_ts_quotas where username='FRANCE';

no rows selected.

2. Grant some QUOTA to the user.

Now we user FRANCE has 5G quota on tablespace USERS. Let’s run the same create statement.
SQL> create table FRANCE.EMPLO as select * from user_objects;

Table created.
It worked. Now, check how much quota has been used.
SQL> select username,tablespace_name,bytes/1024/1024/1024, MAX_BYTES/1024/1024/1024 from dba_ts_quotas where username='FRANCE';

USERNAME TABLESPACE_NAME BYTES/1024/1024/1024 MAX_BYTES/1024/1024/1024
--------  -------------- -------------------   -------------------- 
FRANCE         USERS        .005493164                  5
Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                      FB Group:https://www.facebook.com/groups/894402327369506/                 FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba

ORA-12988: cannot drop column from table owned by SYS

PROBLEM:

While trying to drop a column from a table, owned by SYS, got below error.(ORA-12988)

SOLUTION:

oerr ora 12988
12988, 00000, “cannot drop column from table owned by SYS”
// *Cause: An attempt was made to drop a column from a system table.
// *Action: This action is not allowed

We can’t drop a column from a table owned by SYS user. There is an alternative method to achieve this.

step 1. Create the table under different schema(using CTAS) let’s say DBACLASS user.

step 2. Now drop the column from the newly created table

step 3. Now drop the original table owned by SYS

step 4. Again create the table using CTAS from the dbaclass.test4 table.

 

In this case, if any indexes were present, then those need to be recreated again.

Catch Me On:- Hariprasath Rajaram Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                       FB Group:https://www.facebook.com/groups/894402327369506/                 FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba

ORA-28365: wallet is not open

Problem:-

[oracle@prod101:~ orcl101] expdp tables=scott.tde_test directory=TEST_DIR dumpfile=emp121.dmp logfile=emp121.log

Export: Release 18.0.0.0.0 – Production on Fri Aug 24 00:48:16 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

Username: scott
Password:

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Starting “SCOTT”.”SYS_EXPORT_TABLE_01″: scott/******** tables=scott.tde_test directory=TEST_DIR dumpfile=emp121.dmp logfile=emp121.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-31693: Table data object “SCOTT”.”TDE_TEST” failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-28365: wallet is not open

Solution:-

SQL> alter system set encryption key authenticated by “ORACLE@123”;

System altered.

SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
[oracle@prod101:~ orcl101] expdp tables=scott.tde_test directory=TEST_DIR dumpfile=emp122.dmp logfile=emp122.log

Export: Release 18.0.0.0.0 - Production on Fri Aug 24 01:01:55 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

Username: scott
Password:

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** tables=scott.tde_test directory=TEST_DIR dumpfile=emp122.dmp logfile=emp122.log 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."TDE_TEST" 5.546 KB 1 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/datapump/emp122.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 24 01:02:11 2018 elapsed 0 00:00:07

ORA-39174: Encryption Password Must Be Supplied

Problem:-

ORA-39002: invalid operation
ORA-39174: Encryption password must be supplied.

Solution:-

The export dump is an encrypted one. I.e while taking export , ENCRYPTION_PASSWORD has been provided.
So for importing also,  we need to pass the same password. So get the password and import as below.

impdp scott/tiger tables=oracledbwr directory=TEST_DIR dumpfile=oracledbwr.dmp logfile=oracledbwr.log remap_table=scott.oracledbwr:oracledbwr1 encryption_password=oracle123

ORA-31631: privileges are required ORA-39149: cannot link privileged user to non-privileged user

Problem:-Impdp (network_link) over db_link issue with ORA-31631

Source side

create database link chennai_link connect to hari identified by “hari” using ‘pdb1’;

[oracle@devdb ~]$ impdp system/Chennai#123@devdb directory=dump_dir network_link=chennai_link tables=hari.chennai remap_schema=hari:system LOGFILE=delhi.log
Import: Release 18.0.0.0.0 - Production on Fri Jul 13 04:20:43 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user

Solution

Target side

SQL> conn system/Chennai#123@pdb1
Connected.

SQL> grant imp_full_database,exp_full_database to hari;
Grant succeeded.

[oracle@devdb ~]$ impdp system/Chennai#123@devdb directory=dump_dir network_link=chennai_link tables=chennai remap_schema=hari:system LOGFILE=delhi.log

Import: Release 18.0.0.0.0 - Production on Fri Jul 13 04:27:37 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/********@devdb directory=dump_dir network_link=chennai_link tables=chennai remap_schema=hari:system LOGFILE=delhi.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SYSTEM"."CHENNAI" 107 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Fri Jul 13 04:27:58 2018 elapsed 0 00:00:20

 

ORA-39035: Data filter SUBQUERY has already been specified

Problem

[oracle@testdb ~]$ expdp hari/hari@pdb1 tables=emp directory=TEST_DIR query=”\’where salary > 5000\'” dumpfile=query1.dmp logfile=query.log

Export: Release 18.0.0.0.0 – Production on Tue Jul 10 20:23:44 2018
Version 18.1.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
ORA-39001: invalid argument value
ORA-39035: Data filter SUBQUERY has already been specified.

Solution

[oracle@testdb ~]$ expdp hari/hari@pdb1 tables=emp directory=TEST_DIR query=“‘where salary > 5000‘” dumpfile=query1.dmp logfile=query.log

Export: Release 18.0.0.0.0 – Production on Tue Jul 10 20:28:15 2018
Version 18.1.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Starting “HARI”.”SYS_EXPORT_TABLE_01″: hari/********@pdb1 tables=emp directory=TEST_DIR query=’where salary > 5000′ dumpfile=query1.dmp logfile=query.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “HARI”.”EMP” 13.71 KB 58 rows
Master table “HARI”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for HARI.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/datapump/query1.dmp
Job “HARI”.”SYS_EXPORT_TABLE_01″ successfully completed at Tue Jul 10 20:28:26 2018 elapsed 0 00:00:11