Resolved: ORA-19809 limit exceeded for recovery files

How to fix ORA-19809 limit exceeded for recovery files.

In this article we will discuss 'how to resolve ORA-19809 and ORA-19804' error. This has very common issue while doing RMAN backup, we have faced ORA-19809 and ORA-19804 error due to recovery destination full. In this scenario we will have to remove old archivelog (expired or unwanted) or increase the  size of db_recovery_file_dest_size .

RMAN> backup tablespace users;

Starting backup at 24-SEP-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=130 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/data01/12cdatabase/oracle/oradata/gcdb1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 24-SEP-19
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 09/24/2019 14:38:54
ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 67108864 bytes disk space from 5033164800 limit


Check the current value of db_recovery_file_dest_size.

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest      string     /data01/12cdatabase/oracle/fast_recovery_area
db_recovery_file_dest_size             big integer 4800M


Now we will check the space used .

SQL> select SPACE_USED/1024/1024/1024 "SPACE_USED(GB)" ,SPACE_LIMIT/1024/1024/1024 "SPACE_LIMIT(GB)" from  v$recovery_file_dest;

SPACE_USED(GB) SPACE_LIMIT(GB)
-------------- ---------------
    4.68073177          4.6875


After that we need to increase the db_recovery_file_dest_size  

SQL>ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=10g SCOPE=SPFILE;
SQL> shut immediate;
SQL> Startup;


If you don't want to shutdown database.You may delete the expired archive log online. We should 'delete files from recovery area using rman' on regular basis.


RMAN> delete expired archivelog all;
RMAN>delete noprompt expired backup;
RMAN>delete noprompt expired archivelog all;
RMAN>delete noprompt obsolete recovery window of 7 days;



Verify the recovery_file_dest size.


SQL> select SPACE_USED/1024/1024/1024 "SPACE_USED(GB)" ,SPACE_LIMIT/1024/1024/1024 "SPACE_LIMIT(GB)" from  v$recovery_file_dest;


SPACE_USED(GB) SPACE_LIMIT(GB)
-------------- ---------------
    4.80296659              10


Now we can start the backup as below.

[oracle@prod ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Tue Sep 24 16:32:38 2019

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

connected to target database: GCDB1 (DBID=1045104251)

RMAN> backup tablespace users;

Starting backup at 24-SEP-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=305 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/data01/12cdatabase/oracle/oradata/gcdb1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 24-SEP-19
channel ORA_DISK_1: finished piece 1 at 24-SEP-19
piece handle=/data01/12cdatabase/oracle/fast_recovery_area/GCDB1/backupset/2019_09_24/o1_mf_nnndf_TAG20190924T163242_grmxymnv_.bkp tag=TAG20190924T163242 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-SEP-19

Starting Control File and SPFILE Autobackup at 24-SEP-19
piece handle=/data01/12cdatabase/oracle/fast_recovery_area/GCDB1/autobackup/2019_09_24/o1_mf_s_1019838764_grmxyplb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 24-SEP-19


Now we can see in RMAN Backup logs no 'ORA-19809 limit exceeded for recovery files' showing.

12c: Multitenant - Container Database (CDB) and Pluggable database (PDB) Backup and Recovery using RMAN.

As we know that Oracle introduced multitenant Container Database (CDB) and Pluggable Database (PDB) in 12c. Using RMAN client we can perform the backup of entire Container Database (CDB)  or individual Pluggable Database (PDB) and also perform the point-in- time recovery.

Before start the RMAN backup, we should aware about SQL interface for RMAN that another greatest and easiest feature that we can use SQL statement through RMAN. Prior to 12c, very limited SQL statement was supported with RMAN.

In this article we will explain how to use RMAN backup and recovery in multi-tenant environment.

CDB, PCDB and ROOT Backup:-

We can backup the Container Database (CDB) as same as non-container database using below RMAN command.          

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Backup Pluggable database (PDB) using below RMAN command.

RMAN> BACKUP PLUGGABLE DATABASE PDBORCL1, PDBORCL2;

Or connect to Pluggable database (PDB).

$RMAN target sys@PDBORCL1

RMAN> BACKUP DATABASE;

We can backup root using below.

RMAN> BACKUP DATABASE ROOT;

CDB,PCDB and ROOT Restore & Recovery:-

Restoration of container database (CDB) and non-container database both are same. We can restore the CDB using below scripts.

RMAN> RUN {

STARTUP MOUNT;

RESTORE DATABASE;

RECOVER DATABASE;

ALTER DATABASE OPEN;

}

Above script will restore whole container (CDB) and pluggable database (PDB).

For restoration of ROOT use below script.

RMAN> RUN {

STARTUP MOUNT;

RESTORE DATABASE ROOT;

RECOVER DATABASE ROOT;

ALTER DATABASE OPEN;

}

There are two way to restore Pluggable Database (PDB), either restore from root container or directly connect to Pluggable Database (PDB).

RMAN> RUN {

RESTORE PLUGGABLE DATABASE PDBORCL1, PDBORCL2;

RECOVER PLUGGABLE DATABASE PDBORCL1, PDBORCL2;

ALTER PLUGGABLE DATABASE PDBORCL1, PDBORCL2 OPEN;

}

Connect Pluggable database (PDB) and do the Restoration and recovery using below script.

$ RMAN TARGET=sys@PDBORCL

RMAN> RUN {

RESTORE DATABASE;

RECOVER DATABASE;

}

Point-in-time recovery of CDB and PDB:-

Follow the below steps to recover the database until a specified SCN, time or log sequence

Step 1: Shutdown and startup mount the database.

SQL> SHUTDOWN IMMEDAITE;

SQL> STARTUP MOUNT;

Step 2: First of all determine the SCN, time or log sequence that you want to recovery.

SCN – you can get the SCN from alert.log file

Sequence – v$log_history

Step 3. Perform the incomplete recovery

If specifying a time, then set NLS_LANG and NLS_DATE_FORMAT environment variables.

RUN {

SET UNTIL TIME 'SEP 10 2019 12:00:00';

# SET UNTIL SCN 400; # alternatively, specify SCN

# SET UNTIL SEQUENCE 423; # alternatively, specify log seq

RESTORE DATABASE; 

RECOVER DATABASE;

}

Note:- If datafile destination different as compare to source database. Use SET NEWNAME FOR DATAFILE  and then use SWITCH DATAFILE ALL. Pls check here for more information.

Before execute resetlogs , need to update redologfile and tempfiles. For more info check here.

Step 4. Open database with resetlogs.

SQL> ALTER DATABASE OPEN RESETLOGS;

How to move datafile online in oracle 12c

In this article we will discuss how to move/relocate datafile online in oracle. We know that Oracle introduced moving datafiles online in 12c to minimize the downtime. Prior to oracle 12c, required taking datafile offline and copy it at OS level and bring it back online again. Oracle 12c includes an enhancement to ALTER DATABASE MOVE DATAFILE command to moved, relocate or rename when database are online.

Check the existing datafiles:-

SQL>SELECT file#, name FROM v$datafile WHERE con_id = 1 ORDER BY file#;

FILE# NAME

---------- ------------------------------------------------------------------

  1 /data01/12cdatabase/oracle/oradata/gcdb1/system01.dbf

  3 /data01/12cdatabase/oracle/oradata/gcdb1/sysaux01.dbf

  4 /data01/12cdatabase/oracle/oradata/gcdb1/undotbs01.dbf

  6 /data01/12cdatabase/oracle/oradata/gcdb1/users01.dbf

Moving/relocate Datafile:-

SQL> ALTER DATABASE MOVE DATAFILE '/data01/12cdatabase/oracle/oradata/gcdb1/system01.dbf' TO '/tmp/system01.dbf';

Database altered.

SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1;

 FILE_ID FILE_NAME

----------------------------------------------------------------------

  1 /tmp/system01.dbf

Renaming a datafile:-

SQL> ALTER DATABASE MOVE DATAFILE '/data01/12cdatabase/oracle/oradata/gcdb1/system01.dbf' TO '/data01/12cdatabase/oracle/oradata/gcdb1/system01.dbf';

Database altered.

The next example uses the file number for the source file and keeps the original file.

SQL> ALTER DATABASE MOVE DATAFILE 1 TO '/tmp/system01.dbf' KEEP;

Migrate/relocate a data file from non-ASM to ASM:

SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '+DG_DATA';

Migrate/ relocate a data file from one ASM disk group to another:

SQL> ALTER DATABASE MOVE DATAFILE '+DG_DATA/DBNAME/DATAFILE/users_01.dbf ' TO '+DG_DATA_02';

Overwrite the datafile with the same name, if it exists at the new destination:

SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' REUSE;

Copy the file to a new destination while retaining the old copy in the old destination:

SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' KEEP;

Data Pump Enhancements in oracle database 12c

In this article we will discuss about Data Pump (expdp, impdp) Enhancements in Oracle Database 12c Release 1

NOLOGGING Option (DISABLE_ARCHIVE_LOGGING)

The TRANSFORM parameter of impdp has been extended to include a DISABLE_ARCHIVE_LOGGING option. The default setting of "N" has no any effect on logging behavior. Using a value "Y" reduces the logging associated with tables and indexes during the import by setting their logging attribute to NOLOGGING before the data is imported and resetting it to LOGGING once the operation is complete.

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

The effect can be limited to a specific type of object (TABLE or INDEX) by appending the object type.

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX

An example of its use is shown below.

$ impdp system/Password1@pdb1 directory=test_dir dumpfile=emp.dmp logfile=impdp_emp.log remap_schema=scott:test transform=disable_archive_logging:y

The DISABLE_ARCHIVE_LOGGING option has no effect if the database is running in FORCE LOGGING mode.

LOGTIME Parameter

The LOGTIME parameter determines if timestamps should be included in the output messages from the expdp and impdp utilities.

The allowable values are explained below.

• NONE : The default value, which indicates that no timestamps should be included in the output.
• STATUS : Timestamps are included in output to the console, but not in the associated log file.
• LOGFILE : Timestamps are included in output to the log file, but not in the associated console messages.
• ALL : Timestamps are included in output to the log file and console.

$ expdp scott/tiger@pdb1 tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log logtime=all

 

Export View as Table

The VIEWS_AS_TABLES parameter allows Data Pump to export the specified views as if they were tables. The table structure matches the view columns, with the data being the rows returned by the query supporting the views.

VIEWS_AS_TABLES=[schema_name.]view_name[:table_name], ...

Now export the view using the VIEWS_AS_TABLES parameter.

$ expdp scott/tiger views_as_tables=scott.emp_v directory=test_dir dumpfile=emp_v.dmp logfile=expdp_emp_v.log

Change Table Compression at Import

The TABLE_COMPRESSION_CLAUSE clause of the TRANSFORM parameter allows the table compression characteristics of the tables in an import to be altered on the fly.

$ impdp system/Password1@pdb1 directory=test_dir dumpfile=emp.dmp logfile=impdp_emp.log  remap_schema=scott:test transform=table_compression_clause:compress

The allowable values for the TABLE_COMPRESSION_CLAUSE include the following.

• NONE : The table compression clause is omitted, so the table takes on the compression characteristics of the tablespace.
• NOCOMPRESS : Disables table compression.
• COMPRESS : Enables basic table compression.
• ROW STORE COMPRESS BASIC : Same as COMPRESS.
• ROW STORE COMPRESS BASIC : Same as COMPRESS.
• ROW STORE COMPRESS ADVANCED : Enables advanced compression, also known as OLTP compression.
• COLUMN STORE COMPRESS FOR QUERY : Hybrid Columnar Compression (HCC) available in Exadata and ZFS storage appliances.
• COLUMN STORE COMPRESS FOR ARCHIVE : Hybrid Columnar Compression (HCC) available in Exadata and ZFS storage appliances.
• 
  

Change Table LOB Storage at Import.

The LOB_STORAGE clause of the TRANSFORM parameter allows the LOB storage characteristics of table columns in a non-transportable import to be altered on the fly.

Below are allowable values.

• SECUREFILE : The LOBS are stored as SecureFiles.
• BASICFILE : The LOBS are stored as BasicFiles.
• DEFAULT : The LOB storage is determined by the database default.
• NO_CHANGE : The LOB storage matches that of the source object.

$ impdp system/Password1@pdb1 directory=test_dir dumpfile=lob_table.dmp logfile=impdp_lob_table.log transform=lob_storage:securefile

Dumpfile Compression Options.

As part of the Advanced Compression option, you can specify the COMPRESSION_ALGORITHM parameter to determine the level of compression of the export dumpfile. This is not related to table compression discussed previously.

The meanings of the available values are described below.

• BASIC : The same compression algorithm used in previous versions. Provides good compression, without severely impacting on performance.
• LOW : For use when reduced CPU utilisation is a priority over compression ratio.
• MEDIUM : The recommended option. Similar characteristics to BASIC, but uses a different algorithm.
• HIGH : Maximum available compression, but more CPU intensive.

• $ expdp scott/tiger tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log compression=all compression_algorithm=medium

Encryption Password Enhancements.

In previous versions, data pump encryption required the ENCRYPTION_PASSWORD parameter to be entered on the command line, making password snooping relatively easy.

In Oracle 12c, the ENCRYPTION_PWD_PROMPT parameter enables encryption without requiring the password to be entered as a command line parameter. Instead, the user is prompted for the password at runtime, with their response not echoed to the screen.

ENCRYPTION_PWD_PROMPT=[YES | NO]

$ expdp scott/tiger tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log  encryption_pwd_prompt=yes

Transportable Database.

The TRANSPORTABLE option can now be combined with the FULL option to transport a whole database.

$ expdp system/Password1 full=Y transportable=always version=12 directory=TEMP_DIR dumpfile=orcl.dmp logfile=expdporcl.log