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

Oracle-Recover A Dropped User Using Flashback

Using flashback method, we can restore an accidentally dropped users in oracle. Basically, we will flashback the database to past, when the user was available, Then take an export dump of the schema, and restore the database to same current state.

Once database is up, we can import the dump. It will need some time( for flashback the database )

PREREQUISITE:

1. Database Must be in Archive log mode

2. Flashback must be enabled for the database.

3.  all the flashback log and archives should be available, from the time, the user is dropped

METHODS:

1. Make sure flashback and archive mode is enable.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> alter database flashback ON;
alter database flashback ON
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.


SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on,log_mode from v$database;

FLASHBACK_ON                   LOG_MODE
------------------             ------------
YES                            ARCHIVELOG

2. Let’s drop a user, to test the scenarios:

SQL> select table_name from dba_tables where owner='RAM';

TABLE_NAME
-------------
TABLE1
TABLE2
TABLE3

SQL> drop user ram cascade;

User dropped.
SQL> select table_name from dba_tables where owner='RAM';

no rows selected

SQL> conn ram/ram
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

3. flashback the database to past, when the user was available.

SQL> set time on;

21:08:33 SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

21:09:00 SQL> startup mount;
ORACLE instance started.

Total System Global Area 1879048192 bytes
Fixed Size 8622000 bytes
Variable Size 553648208 bytes
Database Buffers 1308622848 bytes
Redo Buffers 8155136 bytes
Database mounted.

21:09:18 SQL> flashback database to timestamp to_date('11-JAN-2019 21:05:11','DD-MON-YYYY HH24:MI:SS');

Flashback complete.

Elapsed: 00:00:01.31

4. Open the database in read only mode:

21:10:42 SQL> 
21:11:08 SQL> alter database open read only;

Database altered.

Elapsed: 00:00:00.65

21:11:29 SQL> select table_name from dba_tables where owner='RAM';

TABLE_NAME
-------------
TABLE1
TABLE2
TABLE3

Elapsed: 00:00:00.65

We can see the tables are available now.

5. Take export backup of the schema RAM:

6. Now restore the database to current stage:

Though it need some outage in the database, to restore the user, we are able to restore schema, without losing any other data in database.