Restart Oracle Database when FRA is full

The flash recovery area on Oracle is set to a fixed maximum size and it is possible for it to become full. When this happens Oracle will stop accepting inserts, updates, queries etc and will stop accepting connections except for SYSDBA. It will also fail to start if it is shutdown. To restart the server follow the steps below, they involve running commands as SYSDBA and editing the init.ora file.

If Oracle fails to start and startup.log shows eg:

ORA-03113: end-of-file on communication channel
Process ID: 9174
Session ID: 191 Serial number: 3

Look in alert_SID.log.

If it shows:

Errors in file /u01/app/oracle/diag/rdbms/t1234/T1234trace/T1234D_ora_10245.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 46773248 bytes disk space from 8388608000 limit
ARCH: Error 19809 Creating archive log file to '/u01/app/oracle/flash_recovery_area/T1234/archivelog/2012_11_13/o1_mf_1_20_%u_.arc'

Then the flash recovery area is full.

To fix on a test instance (ie not production data) run

sqlplus / as SYSDBA

startup mount 

alter database noarchivelog;

Remove all unwanted files from the file system

Then go into rman 

rman TARGET sys/pwd@SID

crosscheck archivelog all;

delete expired archivelog all;
delete force obsolete;

Then sqlplus / as SYSDBA

alter database archivelog;

alter database open;

To set the recovery window - in RMAN
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;

To manage flash recovery area size -
alter system set db_recovery_file_dest_size=xG SCOPE=BOTH;

To Move the FRA create a directory (preferably on a different disk) and
vi <ORACLE_HOME>/dbs/init.ora
update the line:
db_recovery_file_dest='/u02/flash_recovery_area'

(You can also change db_recovery_file_dest in here. Back up the init.ora first)

Audit unsuccessful login in Oracle 11gR2

Oracle can audit access by object, statement and by privilege as well as via the fine grained auditing capability. This is the command to enable auditing of unsuccessful login which might indicate an attack on the database.


audit session whenever not successful;

Shrink Oracle DBF Files

SQL commands for finding block size and size of all tablespace files in an Oracle database. THe script produces the necessary commands to reduce the disk space used in the related dbf files.

select value from v$parameter where name = 'db_block_size'

COLUMN SHRINK_DATAFILES FORMAT A75 WORD_WRAPPED
COLUMN VALUE NEW_VAL BLKSIZE
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size';
SELECT 'ALTER DATABASE DATAFILE ''' || FILE_NAME || ''' RESIZE ' || CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) ||
'M;' SHRINK_DATAFILES FROM DBA_DATA_FILES DBADF,
(SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) AND CEIL(BLOCKS*&&BLKSIZE/1024/1024)- CEIL((NVL(HWM,1)* &&BLKSIZE)/1024/1024 ) > 0;

SHRINK_DATAFILES
---------------------------------------------------------------------------
ALTER DATABASE DATAFILE 'F:\ORACLE\ORADATA\MYDBF\RBS01.DBF' RESIZE 25M;

Unlock Locked SYSDBA

Commands for unlocking a locked SYSDBA account in Oracle.


SET ORACLE_SID=orcl
sqlplus / AS SYSDBA
ALTER USER SYSTEM ACCOUNT UNLOCK
or ALTER USER SYSTEM IDENTIFIED BY <NEW PASSWORD> ACCOUNT UNLOCK