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

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