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

Yes, we tried mounting a different controlfile. Same error. The only thing we see odd in the primary is that thread 2 (only thread there) is enabled private. The duplicate is complaining about no public threads (ORA-1620). Not sure how/when/why it got set to private.

We have run the same duplicate script every quarter for 4 years and this is the first time we've encountere this issue. Very frustrating, especially with finance department asking "when will it be done?"

Sandy

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

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

-- 
Sandy B.

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