Re: Streams and realtime apply

From: Tony Adolph <tony.adolph.dba_at_gmail.com>
Date: Tue, 30 Sep 2014 11:06:30 +0400
Message-ID: <CAHc2bHTgq0TthoXTBb6Jert14RkPWp9_rxrV9CLa=n=40-YPmQ_at_mail.gmail.com>

I missed an answer to part of your reply. These are the steps I took:

tnsnames setup on both site
unique names set
log_archive_config, log_archive_dest_1, log_archive_dest_2 set on both sites as per my first post
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'
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
4 standby redo logs created on downstream database (500MB)
streamsadmin user and all privs added
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.
streams pool set to 15m downstream
ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS on all tables to be replicated. Done on primary
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>
>>
>>
>

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

Received on Tue Sep 30 2014 - 09:06:30 CEST

This message: [ Message body ]
Next message: Nassyam Basha: "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: Nassyam Basha: "Re: Streams and realtime apply"
Reply: Nassyam Basha: "Re: Streams and realtime apply"

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

Original text of this message