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

Hi Sandy,

Have you seen Doc ID 2325012.1 , suggests there’s probably something awry with your spfile/pfile on the duplicate side preventing it from mounting. I would imagine there would be details in the alert log if it’s not the exact problem described in the note.

Thanks,
Andy

On Fri, Jun 30, 2023 at 2:46 PM, Sandra Becker <sbecker6925_at_gmail.com> wrote:

> 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