SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------
db_recovery_file_dest string G:\app\Vishwakarma\flash_re
ery_area
db_recovery_file_dest_size big integer 3912M
recovery_parallelism integer 0
SQL>
SQL> alter system set db_recovery_file_dest_size=1M;
System altered.
SQL>
SQL>
SQL>
SQL>
SQL> alter system switch logfile;
System altered.
SQL> create table test1(id int);
Table created.
SQL> insert into test1 values(12434);
1 row created.
SQL> insert into test1 values(12434);
1 row created.
SQL> insert into test1 select * from test1;
2 rows created.
SQL> /
4 rows created.
SQL> /
8 rows created.
SQL> /
16 rows created.
SQL> /
32 rows created.
SQL> /
64 rows created.
SQL> /
128 rows created.
SQL> /
256 rows created.
SQL> /
512 rows created.
SQL> /
1024 rows created.
SQL> /
2048 rows created.
SQL> /
4096 rows created.
SQL> /
8192 rows created.
SQL> /
16384 rows created.
SQL> /
32768 rows created.
SQL> /
65536 rows created.
SQL> /
131072 rows created.
SQL> /
262144 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
C:\Users\Vishwakarma>
C:\Users\Vishwakarma>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat May 20 12:07:30 2017
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing option
SQL> @dbinfo
NAME LOG_MODE OPEN_MODE DATABASE_ROLE
--------- ------------ -------------------- ----------------
TEST1 ARCHIVELOG READ WRITE PRIMARY
SQL> select count(*) from test1;
COUNT(*)
----------
524288
So, after inserting 524288 row db_recovery_file_dest_size of 1048576 bytes is 100.00% used and it database gets stale for any further insert or update (DML).
AlertlLog..............................................................
Errors in file g:\app\vishwakarma\diag\rdbms\test1\test1\trace\test1_arc0_2088.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 1918464 bytes disk space from 1048576 limit
ARC0: Error 19809 Creating archive log file to 'G:\APP\VISHWAKARMA\FLASH_RECOVERY_AREA\TEST1\ARCHIVELOG\2017_05_20\O1_MF_1_4_%U_.ARC'
Thread 1 cannot allocate new log, sequence 7
Checkpoint not complete
Current log# 3 seq# 6 mem# 0: G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\REDO03.LOG
Thread 1 cannot allocate new log, sequence 7
All online logs needed archiving
Current log# 3 seq# 6 mem# 0: G:\APP\VISHWAKARMA\PRODUCT\11.2.0\DBHOME_1\ORADATA\TEST1\REDO03.LOG
Sat May 20 12:06:28 2017
Errors in file g:\app\vishwakarma\diag\rdbms\test1\test1\trace\test1_arc1_3556.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 1048576 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Errors in file g:\app\vishwakarma\diag\rdbms\test1\test1\trace\test1_arc1_3556.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 1918464 bytes disk space from 1048576 limit
ARC1: Error 19809 Creating archive log file to 'G:\APP\VISHWAKARMA\FLASH_RECOVERY_AREA\TEST1\ARCHIVELOG\2017_05_20\O1_MF_1_4_%U_.ARC'