Re: Streams and realtime apply

From: Nassyam Basha <nassyambasha_at_gmail.com>
Date: Tue, 30 Sep 2014 12:43:50 +0530
Message-ID: <CAABGLu+rpn1oPN6maZoL5DStz-Ppr_0hLz63Y4VAc2OGfsuYXw_at_mail.gmail.com>

At this point it looks me only issue might around the size of redo logs, Ensure have same or more size of standby redo log files than online redo log files &
Have same or more number of standby redo logs than online redo logs.

Reset once downstream_real_time_mine and again retry please?

Thank you.

On Tue, Sep 30, 2014 at 12:36 PM, Tony Adolph <tony.adolph.dba_at_gmail.com> wrote:

> I missed an answer to part of your reply. These are the steps I took:
>
> 1) tnsnames setup on both site
> 2) unique names set
> 3) log_archive_config, log_archive_dest_1, log_archive_dest_2 set on both
> sites as per my first post
> 4) checked config, this sql returns no error:
> col destination for a10
> select destination, status, archived_thread#, archived_seq#, ERROR,
> SYNCHRONIZATION_STATUS
> from v$archive_dest_status
> where status != 'INACTIVE';
> but SYNCHRONIZATION_STATUS = 'CHECK CONFIGURATION'
> 5) redo logs on primary:
> GROUP# THREAD# MEMBER SEQ ARC
> STATUS FIRST_CHANGE# FIRST_TIME MBYTES
> ------ ------- ---------------------------------------------- ------- ---
> -------- ------------- ------------------ --------
> 1 1 /u02/oradata/RL1STPOC/redo01.log 33 YES
> INACTIVE 4907755 07-Sep-14 15:06:49 500.00
> 2 1 /u02/oradata/RL1STPOC/redo02.log 34 YES
> INACTIVE 4910048 07-Sep-14 15:08:08 500.00
> 3 1 /u02/oradata/RL1STPOC/redo03.log 35 NO
> CURRENT 5289549 08-Sep-14 16:54:51 500.00
> 6) 4 standby redo logs created on downstream database (500MB)
> 7) streamsadmin user and all privs added
> 8) dblinks created. I've created links in both directions, but only for
> convenience, i.e. I can run commands downstream from the primary database.
> This is is only a POC, I wont be able to do this in production, db links
> will not be allowed. Possibly a link from downstream to primary but
> definitely not allowed the other way.
> 9) streams pool set to 15m downstream
> 10) ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS on all tables to be
> replicated. Done on primary
> 11) maintain tables run:
> begin
> dbms_streams_adm.maintain_tables_at_rl1stpoc(table_names =>
> <list of tables>
> source_directory_object => null,
> destination_directory_object => null,
> source_database =>
> 'ROPSTPOC',
> destination_database =>
> 'RL1STPOC',
> capture_name =>
> 'STRM_CAPTURE',
> capture_queue_table =>
> 'STRM_CAPTURE_T',
> capture_queue_name =>
> 'STRM_CAPTURE',
> capture_queue_user =>
> 'STREAMSADMIN',
> propagation_name => null,
> apply_name =>
> 'STRM_APPLY',
> apply_queue_table =>
> 'STRM_APPLY_T',
> apply_queue_name =>
> 'STRM_APPLY',
> apply_queue_user =>
> 'STREAMSADMIN',
> include_ddl => true,
> bi_directional => false,
> perform_actions => true,
> instantiation =>
> DBMS_STREAMS_ADM.INSTANTIATION_TABLE_NETWORK);
> dbms_output.put_line('RL1STPOC maintain_tables done');
>
> exception
> when others then
> dbms_output.put_line(sqlerrm);
> end;
>
> No errors here, but the instantiation does not rebuild the tables. We
> have referenced partition tables which causes this to fail (without
> exception). So I manually exported teh tables, dropped on downstream and
> imported (expdp/impdp)
> 12) set_table_instantiation_sc
>
> select current_scn into l_current_scn from v$database;
> DBMS_APPLY_ADM.set_table_instantiation_scn_at_RL1STPOC(source_object_name
> => i.table_name,
>
> source_database_name => 'ROPSTPOC',
>
> instantiation_scn => l_current_scn);
> no errors
> 13) DBMS_CAPTURE_ADM.SET_PARAMETER_at_RL1STPOC(
> capture_name => 'STRM_CAPTURE',
> parameter => 'downstream_real_time_mine',
> value => 'Y');
>
> Get the error
> ERROR at line 1:
> ORA-26761: Standby Redo Logs not available for real time mining
> ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 177
> ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 291
> ORA-06512: at line 6
>
> Any ideas?
> Rgds
> Tony
>
> On 30 September 2014 10:50, Tony Adolph <tony.adolph.dba_at_gmail.com> wrote:
>
>> Hi Nassyam,
>>
>> Thanks for your feedback.
>>
>> I have tried multiple configurations / attempts to setup downstream
>> realtime capture. A restriction I have is that the Primary database
>> cannot have a db link "to" the downsteam database. For the setup /
>> instantiation I have a db link from the downsteam database to the Primary
>> database.
>>
>> When I try:
>>
>> DBMS_CAPTURE_ADM.SET_PARAMETER(
>> capture_name => 'STRM_CAPTURE',
>> parameter => 'downstream_real_time_mine',
>> value => 'Y');
>>
>> I get the error
>>
>> ERROR at line 1:
>> ORA-26761: Standby Redo Logs not available for real time mining
>> ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 177
>> ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 291
>> ORA-06512: at line 6
>>
>> Any pointers appreciated
>> Rgds
>> Tony
>>
>>
>> On 29 September 2014 20:04, Nassyam Basha <nassyambasha_at_gmail.com> wrote:
>>
>>> Sorry for misreading your question.
>>> I would like to know some *basic *information,
>>> in order to avail realtime downstream capture, have you performed all
>>> the necessary steps dblink creation, creating capture using
>>> DBMS_CAPTURE_ADM and downstream_real_time_mine capture parameter enabled?
>>>
>>> Thanks.
>>>
>>> On Mon, Sep 29, 2014 at 7:28 PM, Tony Adolph <tony.adolph.dba_at_gmail.com>
>>> wrote:
>>>
>>>> Hi Nassyam,
>>>>
>>>> Thanks for the feedback.
>>>>
>>>> The Remote Site is not in managed recovery mode. Its up and running,
>>>> only a subset of tables will be replicated to it using Streams.
>>>>
>>>> Rgds
>>>> Tony
>>>>
>>>> On 29 September 2014 17:49, Nassyam Basha <nassyambasha_at_gmail.com>
>>>> wrote:
>>>>
>>>>> Hello Tony,
>>>>>
>>>>> For me configuration looks good and seems to be quiet strange..
>>>>>
>>>>> When you start MRP is there any informational messages? The MRP status
>>>>> shows whether it started with real time apply or not.
>>>>> If you haven't started MRP as "alter database recover managed standby
>>>>> database using current logfile disconnect from session", then i would
>>>>> suggest to start and see and share your observations?
>>>>>
>>>>> And can you get output for below query too?
>>>>> SQL> select recovery_mode from v$archive_dest_status where
>>>>> recovery_mode !='IDLE';
>>>>>
>>>>> What about the size of the ORL on Primary?
>>>>> How many redo log groups on Primary?
>>>>> Also add block#, blocks in the same query you have used "select
>>>>> PROCESS, STATUS, THREAD#, SEQUENCE# from v$managed_standby;"
>>>>>
>>>>> Thank you.
>>>>>
>>>>>
>>>>> On Mon, Sep 29, 2014 at 7:05 PM, Tony Adolph <
>>>>> tony.adolph.dba_at_gmail.com> wrote:
>>>>>
>>>>>> Hi All,
>>>>>>
>>>>>> I'm trying to setup realtime downstream capture have missed something
>>>>>> in the setup as I cannot get the standby redo logs "working". They show
>>>>>> INACTIVE at all times.
>>>>>>
>>>>>> My Env:
>>>>>>
>>>>>> Single instance (not RAC) Enterprise Edition 11.2.0.4 on all sites.
>>>>>>
>>>>>> Primary Site: ROPSTPOC
>>>>>> Remote Location: RL1STPOC
>>>>>>
>>>>>> ROPSTPOC and RL1STPOC can tnsping each other and archived redo logs
>>>>>> are getting shipped to RL1STPOC successfully.
>>>>>>
>>>>>> ROPSTPOC (Primary Site) settings:
>>>>>>
>>>>>> DG_CONFIG=(ROPSTPOC,RL1STPOC)'
>>>>>> log_archive_dest_1='location=use_db_recovery_file_dest'
>>>>>>
>>>>>> log_archive_dest_2='SERVICE=RL1STPOC ASYNC NOREGISTER
>>>>>> VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RL1STPOC
>>>>>> TEMPLATE=/u03/fast_recovery_area/RL1STPOC_arch_%t_%s_%r.log';
>>>>>>
>>>>>> RL1STPOC settings:
>>>>>>
>>>>>> log_archive_dest_1='location=use_db_recovery_file_dest
>>>>>> VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)'
>>>>>> log_archive_dest_2='location=/u05/fast_recovery_area
>>>>>> VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)'
>>>>>> log_archive_config='DG_CONFIG=(RL1STPOC,ROPSTPOC)
>>>>>>
>>>>>> On Remote Location: RL1STPOC
>>>>>>
>>>>>> alter database ADD STANDBY LOGFILE
>>>>>> '/u02/oradata/RL1STPOC/stby_redo01.log' size 500m;
>>>>>> alter database ADD STANDBY LOGFILE
>>>>>> '/u02/oradata/RL1STPOC/stby_redo02.log' size 500m;
>>>>>> alter database ADD STANDBY LOGFILE
>>>>>> '/u02/oradata/RL1STPOC/stby_redo03.log' size 500m;
>>>>>> alter database ADD STANDBY LOGFILE
>>>>>> '/u02/oradata/RL1STPOC/stby_redo04.log' size 500m;
>>>>>>
>>>>>> On ROPSTPOC (Primary Site) settings
>>>>>>
>>>>>> SYS_at_ROPSTPOC> alter system archive log current;
>>>>>>
>>>>>> On Remote Location: RL1STPOC: alert log:
>>>>>>
>>>>>> Mon Sep 29 17:21:45 2014
>>>>>> RFS[2]: Opened log for thread 1 sequence 782 dbid 1309217406 branch
>>>>>> 856117438
>>>>>>
>>>>>> SYS_at_RL1STPOC> select PROCESS, STATUS, THREAD#, SEQUENCE# from
>>>>>> v$managed_standby;
>>>>>>
>>>>>> PROCESS STATUS THREAD# SEQUENCE#
>>>>>> --------- ------------ ------------ ------------
>>>>>> ARCH CLOSING 1 93
>>>>>> ARCH CLOSING 1 91
>>>>>> ARCH CONNECTED 0 0
>>>>>> ARCH CLOSING 1 92
>>>>>> RFS IDLE 0 0
>>>>>> RFS IDLE 0 0
>>>>>> RFS IDLE 1 782
>>>>>> RFS IDLE 0 0
>>>>>>
>>>>>> But the standby logs never get used:
>>>>>>
>>>>>> GROUP# THREAD# MEMBER
>>>>>> SEQ ARC STATUS FIRST_CHANGE# FIRST_TIME MBYTES
>>>>>> ------ ------- ----------------------------------------------------
>>>>>> ------- --- ------------ ------------- ------------------ --------
>>>>>> 4 0 /u02/oradata/RL1STPOC/stby_redo01.log
>>>>>> 0 YES UNASSIGNED 500.00
>>>>>> 5 0 /u02/oradata/RL1STPOC/stby_redo02.log
>>>>>> 0 YES UNASSIGNED 500.00
>>>>>> 6 0 /u02/oradata/RL1STPOC/stby_redo03.log
>>>>>> 0 YES UNASSIGNED 500.00
>>>>>> 7 0 /u02/oradata/RL1STPOC/stby_redo04.log
>>>>>> 0 YES UNASSIGNED 500.00
>>>>>>
>>>>>> Other parts of my Streams setup work, but only when I manually switch
>>>>>> a log on the Primary site.
>>>>>>
>>>>>> What have I missed?
>>>>>> Rgds
>>>>>> Tony
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Nassyam Basha.
>>>>> Oracle DBA
>>>>> *The Pythian Group * <http://www.pythian.com/>
>>>>> 11g OCP Certified, Blogger
>>>>> Co-Author: Oracle Data Guard 11gR2
>>>>> <http://www.amazon.in/Oracle-Guard-11gR2-Administration-Beginners/dp/1849687900>
>>>>> Member of Oraworld-team <http://www.oraworld-team.com>
>>>>>
>>>>> Visit My Blog <http://www.oracle-ckpt.com>
>>>>> Let's Connect - Linkedin Profile
>>>>> <http://in.linkedin.com/in/nassyambasha/>
>>>>> My Twitter <https://twitter.com/nassyambasha>
>>>>> My Facebook <https://www.facebook.com/nassyambasha>
>>>>>
>>>>>
>>>>
>>>
>>>
>>> --
>>> Nassyam Basha.
>>> Oracle DBA
>>> *The Pythian Group * <http://www.pythian.com/>
>>> 11g OCP Certified, Blogger
>>> Co-Author: Oracle Data Guard 11gR2
>>> <http://www.amazon.in/Oracle-Guard-11gR2-Administration-Beginners/dp/1849687900>
>>> Member of Oraworld-team <http://www.oraworld-team.com>
>>>
>>> Visit My Blog <http://www.oracle-ckpt.com>
>>> Let's Connect - Linkedin Profile
>>> <http://in.linkedin.com/in/nassyambasha/>
>>> My Twitter <https://twitter.com/nassyambasha>
>>> My Facebook <https://www.facebook.com/nassyambasha>
>>>
>>>
>>
>

-- 
Nassyam Basha.
Oracle DBA
*The Pythian Group * <http://www.pythian.com/>
11g OCP Certified, Blogger
Co-Author: Oracle Data Guard 11gR2
<http://www.amazon.in/Oracle-Guard-11gR2-Administration-Beginners/dp/1849687900>
Member of Oraworld-team <http://www.oraworld-team.com>

Visit My Blog <http://www.oracle-ckpt.com>
Let's Connect - Linkedin Profile <http://in.linkedin.com/in/nassyambasha/>
My Twitter <https://twitter.com/nassyambasha>
My Facebook <https://www.facebook.com/nassyambasha>

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

Received on Tue Sep 30 2014 - 09:13:50 CEST

This message: [ Message body ]
Next message: Tony Adolph: "Re: Streams and realtime apply"
Previous message: Tony Adolph: "Re: Streams and realtime apply"
In reply to: Tony Adolph: "Re: Streams and realtime apply"
Next in thread: Tony Adolph: "Re: Streams and realtime apply"
Reply: Tony Adolph: "Re: Streams and realtime apply"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message