Re: Streams and realtime apply
- From: Tony Adolph <tony.adolph.dba@xxxxxxxxx>
- To: Nassyam Basha <nassyambasha@xxxxxxxxx>
- Date: Tue, 30 Sep 2014 11:17:49 +0400
All logs are 500M
On Primary, there are 3 x 500M
On downstreams there are 4 x 500M standby redo logs
Rgds
Tony
On 30 September 2014 11:13, Nassyam Basha <nassyambasha@xxxxxxxxx> wrote:
> 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@xxxxxxxxx>
> 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@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@RL1STPOC(source_object_name
>> => i.table_name,
>>
>> source_database_name => 'ROPSTPOC',
>>
>> instantiation_scn => l_current_scn);
>> no errors
>> 13) DBMS_CAPTURE_ADM.SET_PARAMETER@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@xxxxxxxxx>
>> 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@xxxxxxxxx>
>>> 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@xxxxxxxxx
>>>> > 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@xxxxxxxxx>
>>>>> 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@xxxxxxxxx> 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@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@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>
>
>
Other related posts:
