Re: Duplicate fails with ORA-01194

From: De DBA <dedba_at_tpg.com.au>
Date: Wed, 25 May 2016 20:27:31 +1000
Message-ID: <57457E13.2010207_at_tpg.com.au>

Thanks Hemant and Mark for their pointers. I should have posted here straight away instead of raising an SR!

• the older log I was mentioning is sequence 136278 - it was not copied as part of the backup phase, but included in the recovery nevertheless
• ${__REFRESH:-${__MASK}} evaluates to the intended SID for the copy, not a timestamp
• the reason to use +RECO rather than +DATA is purely available space. This ODA was reconfigured at some point, and most of the space is allocated to +RECO. Not sure why.

In the end the focus on the archived logs was a furphy. I was pointing it out as Oracle Support was transfixed by the fact that RMAN chose to apply some ALs from +RECO/target-SB, rather than +RECO/auxDB. They were convinced that this was the cause, whereas it does clearly not matter from which location the AL is read.. The work-around they suggested, to use +DATA for log_archive_dest_1, seemed to work, though.

The answer lies in the recovery time/scn which I gather is determined from the newest archived log. As I am duplicating from a (very) Active DG standby, the database by the time the copy finishes (about 1 hour) has well & truly progressed due to redo apply. In this case, we use LGWR transport, standby-redo log files and real-time apply. Now it becomes a race condition: the redo is applied to the standby /before/ the redo log is archived. It is not possible to archive the redo log on the standby, so in 99 out of 100 times the redo log that contains the last required redo is not yet archived when RMAN starts to recover the copy, and recovery fails. The 100th time we got lucky and believe that the "workaround" fixed it.

The solution is simply to suspend redo apply before the duplication.

Cheers,
Tony

On 25/05/16 13:44, Chitale, Hemant K wrote:
>>> but strangely one older log from the target is also applied
> Which one ? What are the SEQUENCE#, FIRST_TIME and NEXT_TIME for the "older log" ?
>
> Is SEQUENCE# 136279 really generated after the backup of the last datafile ? Can you check the FIRST_TIME and NEXT_TIME for it and relate to the datafile backup ?
>
> Hemant K Chitale
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of De DBA
> Sent: Tuesday, May 24, 2016 6:06 PM
> To: oracle-l_at_freelists.org
> Subject: Duplicate fails with ORA-01194
>
> G'day.
>
> I'm trying to duplicate a database from the active standby, but I keep running into "RMAN-06136: ORACLE error from auxiliary database: ORA-01194: file 1 needs more recovery to be consistent". As you can see below, I've raised an SR with Oracle about this, and we thought to have found it. The work-around was to place the archived log destination on a different disk group than the standby uses, so that the copy would not catalogue the archived logs of the target. Sadly, this seems to have been a fluke. When I ran the duplicate again today, the error resurfaced.
>
> This is Oracle 11.2.0.4.5 (not patched for a while, I know), Active Data Guard on an ODA. The target is the standby database, which is open read-only with real-time apply. The auxiliary is to reside in the same ASM storage as the target. I know that it is possible, I've done it many times before without a hitch on stand-alone servers.
>
> The duplicate fails in the recovery phase with this output:
>
> <snip>
> Starting recover at 24-05-2016 12:35
>
> starting media recovery
>
> archived log for thread 1 with sequence 136278 is already on disk as file +RECO/target-SB/archivelog/2016_05_24/thread_1_seq_136278.3066.912686281
> archived log for thread 1 with sequence 136279 is already on disk as file +DATA/auxDB/archivelog/2016_05_24/thread_1_seq_136279.443.912688537
> ...
> archived log file name=+RECO/target-SB/archivelog/2016_05_24/thread_1_seq_136278.3066.912686281 thread=1 sequence=136278
> archived log file name=+DATA/auxDB/archivelog/2016_05_24/thread_1_seq_136279.443.912688537 thread=1 sequence=136279
> ...
> Oracle Error:
> ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
> ORA-01194: file 1 needs more recovery to be consistent
> ORA-01110: data file 1: '+RECO/auxDB/datafile/system.10055.912688507'
> </snip>
>
> All copied archived logs are applied without error, but strangely one older log from the target is also applied. This is the first place in the log where this particular log appears. It is not even mentioned in the cataloguing phase.
>
> This is the procedure I use (it is part of a shell script - the ${} variables are never seen by RMAN):
>
> connect target ${SYSUSER}_at_${__SOURCE}
> connect auxiliary ${SYSUSER}_at_${__REFRESH:-${__MASK}}
>
> run{
> allocate channel c1 device type disk;
> allocate channel c2 device type disk;
> allocate channel c3 device type disk;
> allocate channel c4 device type disk;
> allocate channel c5 device type disk;
> allocate channel c6 device type disk;
> allocate channel c7 device type disk;
> allocate channel c8 device type disk;
> allocate channel c9 device type disk;
> allocate channel c0 device type disk;
>
> allocate auxiliary channel a1 device type disk;
> allocate auxiliary channel a2 device type disk;
> allocate auxiliary channel a3 device type disk;
> allocate auxiliary channel a4 device type disk;
> allocate auxiliary channel a5 device type disk;
> allocate auxiliary channel a6 device type disk;
> allocate auxiliary channel a7 device type disk;
> allocate auxiliary channel a8 device type disk;
> allocate auxiliary channel a9 device type disk;
> allocate auxiliary channel a0 device type disk;
>
> duplicate
> target database
> to ${__REFRESH:-${__MASK}}
> from active database
> spfile
> parameter_value_convert ( '${__SOURCE}' , '${__REFRESH:-${__MASK}}' )
> set control_files = '+RECO', '+REDO'
> set db_unique_name = '${__REFRESH:-${__MASK}}'
> set db_create_file_dest = '+RECO'
> set db_create_online_log_dest_1 = '+REDO'
> ####
> # set db_recovery_file_dest = '+RECO'
> # set db_recovery_file_dest_size = '100G'
> ## Work-around for bug 17877323 ORA-00600[krbi_init_fra_metadata_not_backup_ctl]: disable FRA
> reset db_recovery_file_dest
> reset db_recovery_file_dest_size
> ## SR3-12369406641: Workaround RMAN recover issue: dont use same dg as target for archived logs
> ## set log_archive_dest_1 = 'location=+RECO'
> set log_archive_dest_1 = 'location=+DATA'
> ###
> set local_listener = 'l_${__REFRESH:-${__MASK}}'
> set parallel_degree_limit = 'IO'
> set parallel_degree_policy = 'AUTO'
> set parallel_adaptive_multi_user = 'FALSE'
> set memory_target = '3G'
> reset sga_target
> reset sga_max_size
> reset pga_aggregate_target
> reset shared_pool_reserved_size
> reset log_archive_config
> reset log_archive_dest_2
> reset log_archive_dest_state_2
> reset log_archive_format
> reset fal_server
> reset dg_broker_start
> reset use_large_pages
> ;
>
> }
>
> exit
>
> ___
> Any thoughts?
>
> Cheers,
>
> Tony
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
> This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html

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

Received on Wed May 25 2016 - 12:27:31 CEST