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

Hi Sandy,

Your problem is in the mounting phase of your duplicate database. Either the problem is with the instance (which is what I suspect and hopefully has better clues in its alert log) or the duplicate control file. It hasn’t got far enough in the startup to be dealing with recovery.

Have you tried mounting a different control file on this instance?

I assume there’s nothing wrong with your primary DB?

Thanks,
Andy

On Fri, Jun 30, 2023 at 8:44 PM, Quanwen Zhao <quanwenzhao_at_gmail.com> wrote:

> Hello Sandra :-),
>
> Based on your description I found out Oracle Database is 10.2, you know,
> version 10gR2 seems like to not support rman duplicate from active database
> you need to do a rman full backup on the primary and then do restore and
> recover on physical standby, you can read -
> *https://docs.oracle.com/cd/B19306_01/server.102/b14239/create_ps.htm#i67520*
> <https://docs.oracle.com/cd/B19306_01/server.102/b14239/create_ps.htm#i67520>.
> By the way 11g starts supporting *RMAN DUPLICATE FORM ACTIVE DATABASE*.
>
> Best Regards
> Quanwen Zhao
>
> Sandra Becker <sbecker6925_at_gmail.com> 于2023年7月1日周六 10:22写道：
>
>> Update: We have been on the phone with Rimini support ever since I
>> posted here. Still no solution. We know the following:
>>
>> - Thread 2 is PRIVATE in the primary database
>> - We have been trying to test commands and establish an
>> implementation plan on a test database without any success
>> - They have had us issue the *alter database disable thread 1 *in
>> the test database, which fails
>> - They had us create a second thread in the database, which we
>> eventually were able to set to private, the re-enable as public, but it's
>> closed
>> - Unfortunately, the first person we were speaking with did not do a
>> handover to the person we are working with now
>>
>>
>> On Fri, Jun 30, 2023 at 3:43 PM Sandra Becker <sbecker6925_at_gmail.com>
>> wrote:
>>
>>> 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.
>>>
>>>
>>
>> --
>> Sandy B.
>>
>>

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