Oracle knowledge: ORA-16014 error and flash space full

ORA-16014 error and flash space full

Create table user as select name from t_userinfo created yesterday
The t_userinfo table is very large. After a while, I have been reading and writing the hard disk for three hours.
PL/SQL DEVELOPER cannot be interrupted.
Check SQL _fulltext of v $ sqlarea to find SQL _Id = gutz8yut71m22.
Session_id = 646 serial # = 57 is found in v $ session.
Then alter system kill session '2017, 57 'cannot be deleted.
Check v $ session_wait. The event is log siwtch.
Shutdown immediate cannot be disabled either. Therefore, Oracle service must be disabled in windows.
When I started the system again, I was afraid that the database would be rolled back because I had deleted the archive log by 1.1 GB, and I was afraid that it could not be recovered.

1. Problems and Solutions

SQL> select status from v $ instance;

STATUS
------------
MOUNTED

SQL> alter database open;
Alter database open
*
Row 3 has an error:
ORA-16014: log 2 serial number 27 Unarchived, no available destination
ORA-00312: Online log 2 thread 1:
'D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ REDO02.LOG'

SQL> show parameter db_recovery_file

NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_recovery_file_dest string D: \ oracle \ product \ 10.2.0/flash
_ Recovery_area
Db_recovery_file_dest_size big integer 2G

SQL> alter system archive log current;
Alter system archive log current
*
Row 3 has an error:
ORA-01109: the database is not open

SQL> alter system switch logfile;
Alter system switch logfile
*
Row 3 has an error:
ORA-01109: the database is not open

SQL> show parameter db_recovery

NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_recovery_file_dest string C: \ oracle \ product \ 10.2.0/flash
_ Recovery_area
Db_recovery_file_dest_size big integer 2G

SQL> alter system set db_recovery_file_dest_size = 3G scope = both;

The system has been changed.

SQL> alter database open;

The database has been changed.

(1). Check the usage of the flash recovery area:

SQL> select * from v $ flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
----------------------------------------------------------------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 7.23 0 48
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0

You have selected 6 rows.

SQL>

(2) computing the space occupied by the flash recovery area:

SQL> select sum (percent_space_used) * 3/100 from v $ flash_recovery_area_usage;

SUM (PERCENT_SPACE_USED) * 3/100
-----------------------------
2.1369

You can also use the following statement to check
SELECT substr (name, 1, 30) name, space_limit AS quota, space_used AS used, space_reclaimable

AS reclaimable, number_of_files AS files
FROM v $ recovery_file_dest;

The create table as statement produces a lot of archives, causing the online redo log to be unable to be archived. Here, we set

The db_recovery_file_dest_size parameter increases the flash recovery area to solve this problem.

(3) You can also delete unnecessary backups in the flash recovery area to release the flash recovery area space.

Question:

C: \ RMAN
Rman> connect target sys/pwd @ test
(1). delete obsolete;
(2). crosscheck backupset; delete expired backupset;
(3). crosscheck archivelog all; delete expired archivelog all;
Oracle points out that we can execute the following command:
RMAN> backup recovery area;
This problem can also be solved by backing up the content in the flash back area to a third-party media.
Is to remember.