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

Checked it out. We have that parameter set to a valid location. Thanks for the suggestion.

On Fri, Jun 30, 2023 at 3:31 PM Andy Sayer <andysayer_at_gmail.com> wrote:

> 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.
>>
>>

-- 
Sandy B.

--
http://www.freelists.org/webpage/oracle-l