RMAN Table Recovery Fails With ORA-16000 Error

刘泽宇

SYMPTOMS
Running RMAN Table Recovery in 19.16 version fails with below errors after the automatic auxiliary instance got opened in READ ONLY mode:

RMAN> recover table OWNER.TABLE_NAME until time "to_date('2022-11-01 10:00:00','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/location' datapump destination '/location';

sql statement: create spfile from memory
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10039: error encountered while polling for RPC completion on channel clone_default
RMAN-10006: error running SQL statement: select action from gv$session where sid=:1 and serial#=:2 and inst_id=:3
RMAN-10002: Oracle error: ORA-16000: database or pluggable database open for read-only access


CAUSE
During table recovery, RMAN restores SYSTEM, SYSAUX and UNDO tablespaces in an auxiliary instance till the specified point-in-time and then opens the auxiliary instance in READ ONLY mode. After this, the next action is to query the dictionary for the table being recovered. However, as we can see from the log, after the auxiliary instance opened in READ ONLY mode, an internal RMAN select on GV$SESSION failed with ORA-16000.

ORA-16000 for a SELECT query on read only instance is caused by below bug when the sql has a SQL plan baseline associated with it:

Doc ID 34491739.8 Bug 34491739 - sql with sql plan baseline fails with ORA-16000 on physical standby

SOLUTION
Bug 34491739 is fixed in 19.17 Database Release Update (RU). So, we can either apply the one-off patch for this bug on top of existing 19.16 RU or instead update to 19.17 RU to get the fix.

If patching cannot be performed immediately, but you need the table recovery to work, below workaround can be used:

+ Create a parameter file for the automatic auxiliary instance with just one parameter:

$ cat /tmp/init_aux.ora

optimizer_use_sql_plan_baselines=false

+ Run table recovery using above parameter file:

run {
SET AUXILIARY INSTANCE PARAMETER FILE TO '/tmp/init_aux.ora';
recover table OWNER.TABLE_NAME until time "to_date('2022-11-01 10:00:00','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/location' datapump destination '/location';
}