Unable to create standby database - RMAN-11003, ORA-01620
Date: Fri, 30 Jun 2023 14:45:48 -0600
Message-ID: <CAJzM94DK3vpkMdaf1Ap2UALjz2JjjwhH22kzfME-pOosGRAwxA_at_mail.gmail.com>
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 bytesRMAN> 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:06RMAN-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.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jun 30 2023 - 22:45:48 CEST