Keep RMAN Auxiliary Instance after failure

In this blog post, I will demonstrate how to keep an automatically created RMAN Auxiliary Instance after a failure occurred.

Table of Contents

1 Auxiliary Instance
2 Normal Behavior
3 Keep your Auxiliary Instance

Auxiliary Instance

To recover only a subset of objects of a database to a point-in-time in the past, Oracle Recovery Manager (RMAN) uses a second instance/database to restore and recover the required objects – this instance is called Auxiliary Instance. Afterward the objects, for example, a Tablespace or a Table, are “moved” back to the target database.

An Auxiliary Instance is required for the following actions:

Duplicate
PDB Point-in-Time Recovery
Table Point-in-Time Recovery
Tablespace Point-in-Time Recovery

There are two ways to use Auxiliary Instances. First, you can create an instance on your own and connect to it with CONNECT AUXILIARY. Or you can let RMAN handle the creation of the instance. For the latter one, you provide a file system location using the parameter AUXILIARY DESTINATION.

Normal Behavior

When RMAN created an Auxiliary Instance, the instance is killed and the files are removed at the end of the operation. It does not matter if the operation was successful or not.

In the following example, I use RECOVER TABLE to recover a table to a specific point-in-time. Instead of replacing the table directly, I create a Data Pump dump file for the selected table. To demonstrate a failure situation, I set DATAPUMP DESTINATION to a non-writeable location.

RMAN> CONNECT TARGET /
RMAN> RUN {
  RECOVER TABLE DEMO_TABREC.T1 UNTIL SCN 6653592
  AUXILIARY DESTINATION '/u01/auxiliary'
  DATAPUMP DESTINATION '/'
  DUMP FILE 't1.dmp'
  NOTABLEIMPORT;
}

Starting recover at 07.05.20
current log archived
using channel ORA_DISK_1
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='yDjC'

initialization parameters used for automatic instance:
db_name=TVDDB1
db_unique_name=yDjC_pitr_TVDDB1
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u00/app/oracle
_system_trig_enabled=FALSE
sga_target=2048M
processes=200
db_create_file_dest=/u01/auxiliary
log_archive_dest_1='location=/u01/auxiliary'
#No auxiliary parameter file used


starting up automatic instance TVDDB1

Oracle instance started

Total System Global Area    2147481656 bytes

Fixed Size                     8898616 bytes
Variable Size                486539264 bytes
Database Buffers            1644167168 bytes
Redo Buffers                   7876608 bytes
Automatic instance created

...

Performing export of tables...

Removing automatic instance
shutting down automatic instance 
Oracle instance shut down
Automatic instance removed
auxiliary instance file /u01/auxiliary/TVDDB1_SITE1/datafile/o1_mf_temp_hc7cgnvz_.tmp deleted
auxiliary instance file /u01/auxiliary/YDJC_PITR_TVDDB1/onlinelog/o1_mf_3_hc7cjr74_.log deleted
auxiliary instance file /u01/auxiliary/YDJC_PITR_TVDDB1/onlinelog/o1_mf_2_hc7cjr11_.log deleted
auxiliary instance file /u01/auxiliary/YDJC_PITR_TVDDB1/onlinelog/o1_mf_1_hc7cjqys_.log deleted
auxiliary instance file /u01/auxiliary/YDJC_PITR_TVDDB1/datafile/o1_mf_users_hc7cjmct_.dbf deleted
auxiliary instance file /u01/auxiliary/TVDDB1_SITE1/datafile/o1_mf_sysaux_hc7cfsrr_.dbf deleted
auxiliary instance file /u01/auxiliary/TVDDB1_SITE1/datafile/o1_mf_undotbs1_hc7cfsro_.dbf deleted
auxiliary instance file /u01/auxiliary/TVDDB1_SITE1/datafile/o1_mf_system_hc7cfsrd_.dbf deleted
auxiliary instance file /u01/auxiliary/TVDDB1_SITE1/controlfile/o1_mf_hc7cfmro_.ctl deleted
auxiliary instance file t1.dmp deleted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/07/2020 08:43:50
RMAN-06962: Error received during export of metadata
RMAN-06960:    EXPDP> ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/t1.dmp"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 13: Permission denied
Additional information: 1
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

For readability, I removed lines from the output. But you can see, that an automatic instance was created with Oracle Managed Files (OMF) and at the end, this instance along with its files is removed.

Unfortunately only the export failed. So if the instance would be still available, a second Data Pump export could be tried.

Keep your Auxiliary Instance

Now we do the same command as above, but this time I will use the non-documented option KEEP AUXILIARY. This option is related to the non-public Bug 22820798 : RMAN SHOULD HAVE SWITCH TO KEEP AUX FILES IN CASE OF FAILURE.

Currently, this option can only be used out-of-the-box in 19c. At the moment patches are only available for 12c Release 2. If you need this option in other releases, a backport is required.

Availability of patches for Bug 22820798

When the option is used, no removal of the automatic Auxiliary Instance is performed.

RMAN> RUN {
  RECOVER TABLE DEMO_TABREC.T1 UNTIL SCN 6653592
  AUXILIARY DESTINATION '/u01/auxiliary'
  DATAPUMP DESTINATION '/'
  DUMP FILE 't1.dmp'
  NOTABLEIMPORT
  KEEP AUXILIARY;
}

...

Performing export of tables...
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/08/2020 08:54:38
RMAN-06962: Error received during export of metadata
RMAN-06960:    EXPDP> ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/t1.dmp"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 13: Permission denied
Additional information: 1
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

You can connect to it, after setting ORACLE_SID to the name of the instance.

$> export ORACLE_SID=wpDu
$> sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 8 09:01:17 2020
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL>

The Oracle SID of the Auxiliary Instance can be found in the RMAN output.

Creating automatic instance, with SID='wpDu'