Unable to create standby database - RMAN-11003, ORA-01620

From: Sandra Becker <sbecker6925_at_gmail.com>
Date: Fri, 30 Jun 2023 14:45:48 -0600
Message-ID: <CAJzM94DK3vpkMdaf1Ap2UALjz2JjjwhH22kzfME-pOosGRAwxA_at_mail.gmail.com>



Oracle: EE 10.2.0.4
OS: RHEL5.11 Unfortunately, we are unable to upgrade the OS and/or the database due to issues at the application level.

We need to create a standby database from a production financial database. Once it reaches a certain date/time, we need to disconnect it and rename it to produce our quarterly financials. We are getting errors ORA-01620 and ORA-1507. We tried dropping the database first, but get the same errors. We've been following the same procedure every quarter for the past 4 years and have never encountered this problem before. For some reason, it keeps skipping the restore of the database and wants us to start managed recovery. Any suggestions would be appreciated.

*Script*

sqlplus -S / as sysdba <<EOF
startup nomount
alter system set cluster_database = false scope = spfile sid = '*'; alter system set job_queue_processes = 0 scope = spfile sid = '*'; alter system set standby_file_management = 'AUTO' scope = both sid = '*'; alter system set db_name = 'mrpprod' scope = spfile sid = '*'; alter system set fal_server = 'mrpprod' scope = both sid = '*'; alter system set fal_client = 'mrpfin_db1' scope = both sid = '*'; shutdown immediate
startup nomount
EOF rman log=/home/oracle/mrpfin/logs/mrpfin_dup.log_${ts} <<EOF connect target sys/"ssl#dba9mrpprod"_at_mrpprod2 CONNECT AUXILIARY /
RUN
{
  ALLOCATE AUXILIARY CHANNEL a1 TYPE DISK;   ALLOCATE AUXILIARY CHANNEL a2 TYPE DISK;   DUPLICATE TARGET DATABASE FOR STANDBY; }
EOF sqlplus -S / as sysdba <<EOF
  alter database flashback on;
  ALTER DATABASE ADD STANDBY LOGFILE THREAD 1

    group 20 SIZE 1073741824,
    group 21 SIZE 1073741824,
    group 22 SIZE 1073741824,

    group 23 SIZE 1073741824;
  ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
    group 30 SIZE 1073741824,
    group 31 SIZE 1073741824,
    group 32 SIZE 1073741824,

    group 33 SIZE 1073741824;
  alter database recover managed standby database using current logfile disconnect from session;
EOF
*Output from nohup.out*

System altered.

System altered.

System altered.

System altered.

System altered.

System altered.

ORA-01507: database not mounted

ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 8388608000 bytes

Fixed Size                  2097304 bytes
Variable Size            1224740712 bytes
Database Buffers         7147094016 bytes
Redo Buffers               14675968 bytes
RMAN> RMAN> RMAN> 2> 3> 4> 5> 6> RMAN> alter database flashback on
*

ERROR at line 1:
ORA-01507: database not mounted

  ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
*

ERROR at line 1:
ORA-01507: database not mounted

  ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
*

ERROR at line 1:
ORA-01507: database not mounted

  alter database recover managed standby database using current logfile disconnect from session
*

ERROR at line 1:
ORA-01507: database not mounted

*Ouput from duplicate script*

RMAN>
connected to target database: MRPPROD (DBID=2986866382)

RMAN>
connected to auxiliary database: MRPPROD (not mounted)

RMAN> 2> 3> 4> 5> 6>
using target database control file instead of recovery catalog allocated channel: a1
channel a1: sid=156 devtype=DISK

allocated channel: a2
channel a2: sid=155 devtype=DISK

Starting Duplicate Db at 30-JUN-23

contents of Memory Script:
{

   restore clone standby controlfile;
   sql clone 'alter database mount standby database'; }
executing Memory Script

Starting restore at 30-JUN-23

channel a1: starting datafile backupset restore
channel a1: restoring control file
channel a1: reading from backup piece

/remote/rmanbk/mrpprod/2023_06_29/MRPPROD_147505_1140781088_1.rman channel a1: restored backup piece 1
piece
handle=/remote/rmanbk/mrpprod/2023_06_29/MRPPROD_147505_1140781088_1.rman tag=TAG20230629T113808
channel a1: restore complete, elapsed time: 00:00:09 output filename=/u06/mrpfin/controlfile/control1.ctl output filename=/u06/mrpfin/controlfile/control2.ctl Finished restore at 30-JUN-23

sql statement: alter database mount standby database released channel: a1
released channel: a2

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/30/2023 11:19:06
RMAN-03015: error occurred in stored script Memory Script RMAN-03009: failure of sql command on clone_default channel at 06/30/2023 11:19:06
RMAN-11003: failure during parse/execution of SQL statement: alter database mount standby database
ORA-01620: no public threads are available for mounting

RMAN> Recovery Manager complete.

Sandy B.

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jun 30 2023 - 22:45:48 CEST

Original text of this message