We have a production DB that is used for reporting is been attempted to clone with NetApp Snapmirror technology. The current clone is done with old method placing the tablespaces of PROD DB in backup mode and then copying the files using OS “cp” tool to the designated clone DB directories and run the necessary DB layer clone steps. This works fine but now taking extra long time to complete and impacting business functions.
With NetApp SNapmirror technology all steps are working up to the DB layer clone steps. There is an issue with very last steps of recovering the DB after SnapMirror has performed. The step used is “alter database recover automatic using backup controlfile until cancel” as that seems to be the only option available according Oracle Doc ID: DATAFILE NEEDS MORE RECOVERY TO BE CONSISTENT ORA-1194 ORA-1547 ORA-1110 (Doc ID 1528788.1) - Scenario 2: Backup Controlfile is Used for recovery.
Doing this recovery interactively via SQL*PLUS needs placing the redo log file path when prompted. Then DB recover successfully. See below for output.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;
ORA-00279: change 75786588505 generated at 12/06/2024 13:41:35 needed for
thread 1
ORA-00289: suggestion : /arch_poct/archive/OFPOC/arch_1186748276_1_18.arc
ORA-00280: change 75786588505 for thread 1 is in sequence #18


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
'/redo_poct/oradata/OFPOC/redo06.log'
Log applied.
Media recovery complete.
SQL> alter database open resetlogs ;

Database altered.

But this clone process has to be done on daily basis and must be automated. When scripted there is no way with SQL*PLUS to be read from a file containing ‘/redo_poct/oradata/OFPOC/redo06.log' for it’s standard input.

Below is the script

#! /usr/bin/ksh

sqlplus "/ as sysdba" < /projects/of/poc/spt/log/setup_sql_redo_20241217.log <<EOF
set echo on
alter database recover automatic using backup controlfile until cancel;
EOF

Where

cat /projects/of/poc/spt/log/setup_sql_redo_20241217.log
'/redo_poct/oradata/OFPOC/redo06.log'
Output
… /projects/gen/ksh/working> ./TestRecovery.sh

SQL*Plus: Release 11.2.0.2.0 Production on Tue Dec 17 17:29:00 2024

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL> alter database recover automatic using backup controlfile until cancel
*
ERROR at line 1:
ORA-00279: change 75787505640 generated at 12/16/2024 11:21:36 needed for
thread 1
ORA-00289: suggestion : /arch_poct/archive/OFPOC/arch_1186748276_1_157.arc
ORA-00280: change 75787505640 for thread 1 is in sequence #157
ORA-00278: log file '/arch_poct/archive/OFPOC/arch_1186748276_1_157.arc' no
longer needed for this recovery
ORA-00308: cannot open archived log
'/arch_poct/archive/OFPOC/arch_1186748276_1_157.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3


SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

____________________________________________________________________

Test case 2:
#! /usr/bin/ksh

sqlplus "/ as sysdba" <<EOF
set echo on
alter database recover automatic using backup controlfile until cancel;
EOF

Unix Prompt >> /projects/gen/ksh/working> cat /projects/of/poc/spt/log/setup_sql_redo_20241217.log|./TestRecovery.sh

SQL*Plus: Release 11.2.0.2.0 Production on Tue Dec 17 17:38:37 2024

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL> alter database recover automatic using backup controlfile until cancel
*
ERROR at line 1:
ORA-00279: change 75787505640 generated at 12/16/2024 11:21:36 needed for
thread 1
ORA-00289: suggestion : /arch_poct/archive/OFPOC/arch_1186748276_1_157.arc
ORA-00280: change 75787505640 for thread 1 is in sequence #157
ORA-00278: log file '/arch_poct/archive/OFPOC/arch_1186748276_1_157.arc' no
longer needed for this recovery
ORA-00308: cannot open archived log
'/arch_poct/archive/OFPOC/arch_1186748276_1_157.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3


SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
END JOB
____________________________________________________________________

Can this done with interactive command “alter database recover automatic using backup controlfile until cancel; “ in a shells script?

Appreciate an answer at your earliest as this becoming an issue.

Thanks