Re: Unable to create standby database - RMAN-11003, ORA-01620
Date: Fri, 30 Jun 2023 20:21:12 -0600
Message-ID: <CAJzM94ANkzh-Prngaua8w+VVNtpjKoD8q=ZHOJdOYhXoO_PwJA_at_mail.gmail.com>
Update: We have been on the phone with Rimini support ever since I posted
here. Still no solution. We know the following:
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-lReceived on Sat Jul 01 2023 - 04:21:12 CEST