Borndba

Got lot of insight on various process in DG environment.

http://forums.oracle.com/forums/thread.jspa?messageID=4090175

convert this post to pdf.

After a while did the DG creation using rman. Referred note#789370.1

take init.ora of both primary and standby backup

run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format ‘/oradata/orabackup01/Primary/rman/%U’;
backup archivelog all format ‘/oradata/orabackup01/Primary/rman/%U’;
backup current controlfile for standby format ‘/oradata/orabackup01/Primary/rman/%U’;
}

alter system switch logfile;

scp the files to standby server. Use similar location.

on secondary

$ rman target sys/syspwd@Primary_dg auxiliary /

Recovery Manager: Release 10.2.0.2.0 – Production on Tue May 18 04:12:27 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: Primary (DBID=863765789)
connected to auxiliary database: standby (not mounted)

RMAN> duplicate target database for standby dorecover;

Starting Duplicate Db at 18-MAY-2010 04:12:51
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: sid=155 devtype=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: sid=154 devtype=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 6.5 (2008052301)
allocated channel: ORA_AUX_SBT_TAPE_2
channel ORA_AUX_SBT_TAPE_2: sid=153 devtype=SBT_TAPE
channel ORA_AUX_SBT_TAPE_2: Veritas NetBackup for Oracle – Release 6.5 (2008052301)

contents of Memory Script:
{
set until scn 8978424429104;
restore clone standby controlfile;
sql clone ‘alter database mount standby database’;
}
executing Memory Script

executing command: SET until clause

Starting restore at 18-MAY-2010 04:25:19
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oradata/orabackup01/standby/rman/jolduq5h_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oradata/orabackup01/standby/rman/jolduq5h_1_1 tag=TAG20100518T011321
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:18
output filename=+DISK_GROUP1/standby/controlfile/control01_standby.ctl
Finished restore at 18-MAY-2010 04:25:40

sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1
released channel: ORA_AUX_DISK_2
released channel: ORA_AUX_SBT_TAPE_1
released channel: ORA_AUX_SBT_TAPE_2

contents of Memory Script:
{
set until scn 8978424429104;
set newname for clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 7 to new;
set newname for clone datafile 8 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 10 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 12 to new;
set newname for clone datafile 13 to new;
restore
check readonly
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed temporary file 1 to +DISK_GROUP1 in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 18-MAY-2010 04:26:02
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=153 devtype=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: sid=154 devtype=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: sid=155 devtype=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 6.5 (2008052301)
allocated channel: ORA_AUX_SBT_TAPE_2
channel ORA_AUX_SBT_TAPE_2: sid=156 devtype=SBT_TAPE
channel ORA_AUX_SBT_TAPE_2: Veritas NetBackup for Oracle – Release 6.5 (2008052301)

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DISK_GROUP1
restoring datafile 00005 to +DISK_GROUP1
restoring datafile 00007 to +DISK_GROUP1
restoring datafile 00008 to +DISK_GROUP1
restoring datafile 00009 to +DISK_GROUP1
restoring datafile 00010 to +DISK_GROUP1
restoring datafile 00013 to +DISK_GROUP1
channel ORA_AUX_DISK_1: reading from backup piece /oradata/orabackup01/standby/rman/jkldupnn_1_1
channel ORA_AUX_DISK_2: starting datafile backupset restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +DISK_GROUP1
restoring datafile 00003 to +DISK_GROUP1
restoring datafile 00004 to +DISK_GROUP1
restoring datafile 00006 to +DISK_GROUP1
restoring datafile 00011 to +DISK_GROUP1
restoring datafile 00012 to +DISK_GROUP1
channel ORA_AUX_DISK_2: reading from backup piece /oradata/orabackup01/standby/rman/jjldupnm_1_1
channel ORA_AUX_DISK_2: restored backup piece 1
piece handle=/oradata/orabackup01/standby/rman/jjldupnm_1_1 tag=TAG20100518T010558
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:06:15
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oradata/orabackup01/standby/rman/jkldupnn_1_1 tag=TAG20100518T010558
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:06:31
Finished restore at 18-MAY-2010 04:32:41

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy recid=41 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/system.413.719295981
datafile 2 switched to datafile copy
input datafile copy recid=42 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/undo1.407.719295981
datafile 3 switched to datafile copy
input datafile copy recid=43 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/sysaux.345.719295971
datafile 4 switched to datafile copy
input datafile copy recid=44 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/users.416.719295983
datafile 5 switched to datafile copy
input datafile copy recid=45 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/ts_aud_data_purge.405.719295981
datafile 6 switched to datafile copy
input datafile copy recid=46 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/tspan_data.351.719295975
datafile 7 switched to datafile copy
input datafile copy recid=47 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/tspan_index.410.719295979
datafile 8 switched to datafile copy
input datafile copy recid=48 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/fuego_data.474.719295975
datafile 9 switched to datafile copy
input datafile copy recid=49 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/fuego_index.368.719295977
datafile 10 switched to datafile copy
input datafile copy recid=50 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/adw_data.342.719295971
datafile 11 switched to datafile copy
input datafile copy recid=51 stamp=719296362 filename=+DISK_GROUP1/standby/datafile/adw_index.409.719295981
datafile 12 switched to datafile copy
input datafile copy recid=52 stamp=719296363 filename=+DISK_GROUP1/standby/datafile/reserve.411.719295979
datafile 13 switched to datafile copy
input datafile copy recid=53 stamp=719296363 filename=+DISK_GROUP1/standby/datafile/streams_tbs.406.719295983

contents of Memory Script:
{
set until scn 8978424429104;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 18-MAY-2010 04:32:45
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2

starting media recovery

Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘+DISK_GROUP1/standby/datafile/system.413.719295981′

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/18/2010 04:33:00
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 203315 lowscn 8978424428500 found to restore
RMAN-06025: no backup of log thread 1 seq 203314 lowscn 8978424427332 found to restore
RMAN-06025: no backup of log thread 1 seq 203313 lowscn 8978424419381 found to restore
RMAN-06025: no backup of log thread 1 seq 203312 lowscn 8978255748027 found to restore

exit from rman. Ignore the above error time being.

go to primary

alter system set log_archive_dest_state_2 = enable ;

go to standby

alter database recover managed standby database disconnect from session;

This should catch up the standby with the missing arch reported above.

execute the below command in standby, you will get the status.

select sequence#,applied from v$archived_log order by sequence#;

convert this post to pdf.

It’s common missing the arch log file needed for standby recovery, there by standby going out of sync. One way before 10g is rebuilding the standby from scratch.

From 10g, you can create a incremental backup from primary and apply the incremental to standby site.

For Source and Target in ASM, use metalink note

Steps to perform for Rolling forward a standby database using RMAN incremental backup when primary and standby are in ASM filesystem [ID 836986.1]

BACKUP FROM SOURCE:

~~~~~~~~~~~~~~~~~~~

get the SCN from checkpoint_change#@v$datafile_header  of standby database. You can also take the current_scn@v$database. But v$database value comes from controlfile and if the controlfile value is recent than datafile, you may not be successful.

RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 329412309 DATABASE FORMAT ‘/local/eastus/orabackup00/rman/incr1_%U’;

if you want compressed backup

BACKUP AS COMPRESSED backupset DEVICE TYPE DISK INCREMENTAL FROM SCN 8977054477894  DATABASE FORMAT  ‘/local/eastus/orabackup00/rman/incr1_%U’;

Starting backup at 06-MAY-09

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=422 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00015 name=/local/eastus/oradata01/eastusp1/eastusp1rdgdbo_data01.dbf

input datafile fno=00002 name=/local/eastus/oradata03/eastusp1/eastusp1undotbs01.dbf

input datafile fno=00010 name=/local/eastus/oradata05/eastusp1/eastusp1audit_data01.dbf

input datafile fno=00014 name=/local/eastus/oradata05/eastusp1/eastusp1_sysaux_01.dbf

input datafile fno=00013 name=/local/eastus/oradata05/eastusp1/eastusp1statspack_data.dbf

input datafile fno=00008 name=/local/eastus/oradata05/eastusp1/eastusp1xdb01.dbf

input datafile fno=00004 name=/local/eastus/oradata05/eastusp1/eastusp1drsys01.dbf

input datafile fno=00009 name=/local/eastus/oradata05/eastusp1/netiq_data01.dbf

input datafile fno=00003 name=/local/eastus/oradata05/eastusp1/eastusp1cwmlite01.dbf

input datafile fno=00005 name=/local/eastus/oradata05/eastusp1/eastusp1odm01.dbf

input datafile fno=00001 name=/local/eastus/oradata00/eastusp1/eastusp1system01.dbf

input datafile fno=00006 name=/local/eastus/oradata00/eastusp1/eastusp1tools01.dbf

input datafile fno=00007 name=/local/eastus/oradata00/eastusp1/eastusp1users01.dbf

input datafile fno=00012 name=/local/eastus/oradata02/eastusp1/eastusp1east4_idx01.dbf

input datafile fno=00017 name=/local/eastus/oradata02/eastusp1/eastusp1rdgdbo_idx01.dbf

input datafile fno=00011 name=/local/eastus/oradata01/eastusp1/eastusp1east4_data01.dbf

input datafile fno=00016 name=/local/eastus/oradata01/eastusp1/eastusp1ASI_Data01.dbf

channel ORA_DISK_1: starting piece 1 at 06-MAY-09

channel ORA_DISK_1: finished piece 1 at 06-MAY-09

piece handle=/local/eastus/orabackup00/rman/incr1_tvkebumh_1_1 tag=TAG20090506T160952 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:07:05

Finished backup at 06-MAY-09

TRANSFER THE INCREMENTAL BACKUP TO TARGET

Catalog the incremental backup. This help rman to indentify the new backup.

RMAN> CATALOG START WITH ‘/local/eastus/orabackup00/rman/incr1_tvkebumh_1_1′;

searching for all files that match the pattern /local/eastus/orabackup00/rman/incr1_tvkebumh_1_1

List of Files Unknown to the Database

=====================================

File Name: /local/eastus/orabackup00/rman/incr1_tvkebumh_1_1

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files…

cataloging done

List of Cataloged Files

=======================

File Name: /local/eastus/orabackup00/rman/incr1_tvkebumh_1_1

Recover with redo clause

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 06-MAY-09

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: /local/eastus/oradata00/eastusp1/eastusp1system01.dbf

destination for restore of datafile 00002: /local/eastus/oradata03/eastusp1/eastusp1undotbs01.dbf

destination for restore of datafile 00003: /local/eastus/oradata05/eastusp1/eastusp1cwmlite01.dbf

destination for restore of datafile 00004: /local/eastus/oradata05/eastusp1/eastusp1drsys01.dbf

destination for restore of datafile 00005: /local/eastus/oradata05/eastusp1/eastusp1odm01.dbf

destination for restore of datafile 00006: /local/eastus/oradata00/eastusp1/eastusp1tools01.dbf

destination for restore of datafile 00007: /local/eastus/oradata00/eastusp1/eastusp1users01.dbf

destination for restore of datafile 00008: /local/eastus/oradata05/eastusp1/eastusp1xdb01.dbf

destination for restore of datafile 00009: /local/eastus/oradata05/eastusp1/netiq_data01.dbf

destination for restore of datafile 00010: /local/eastus/oradata05/eastusp1/eastusp1audit_data01.dbf

destination for restore of datafile 00011: /local/eastus/oradata01/eastusp1/eastusp1east4_data01.dbf

destination for restore of datafile 00012: /local/eastus/oradata02/eastusp1/eastusp1east4_idx01.dbf

destination for restore of datafile 00013: /local/eastus/oradata05/eastusp1/eastusp1statspack_data.dbf

destination for restore of datafile 00014: /local/eastus/oradata05/eastusp1/eastusp1_sysaux_01.dbf

destination for restore of datafile 00015: /local/eastus/oradata01/eastusp1/eastusp1rdgdbo_data01.dbf

destination for restore of datafile 00016: /local/eastus/oradata01/eastusp1/eastusp1ASI_Data01.dbf

destination for restore of datafile 00017: /local/eastus/oradata02/eastusp1/eastusp1rdgdbo_idx01.dbf

channel ORA_DISK_1: reading from backup piece /local/eastus/orabackup00/rman/incr1_tvkebumh_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/local/eastus/orabackup00/rman/incr1_tvkebumh_1_1 tag=TAG20090506T160952

channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

Finished recover at 06-MAY-09

RMAN>

You can check the checkpoint_change#@datafile_header, it should have increased and should be equal to the point you started the incremental backup.

Controlfile of standby may not be updated after this excercise. Recreate the standby controlfile from primary again.

convert this post to pdf.

########

– You can copy below script to notepad and change “SOURCE” to your SOURCE DB and “TARGET” to your TARGET DB
########

Execute in both source and target:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

connect SYS/password as SYSDBA

EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
drop user strmadmin cascade;

    create user STRMADMIN identified by STRMADMIN123;
    GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;
    execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');
    alter system set aq_tm_processes=1;
   
    connect STRMADMIN/STRMADMIN123

    BEGIN
     DBMS_STREAMS_ADM.SET_UP_QUEUE(
      queue_table => 'STREAMS_QUEUE_TABLE',
      queue_name => 'STREAMS_QUEUE',
      queue_user => 'STRMADMIN');
    END;
    /

create db link from source to target and vice versa

Test using

select * from global_name@db_link;

verify it is returning correct value
Steps to be carried out at the Destination Database
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Add apply rules for the Schema at the destination database :

connect STRMADMIN/STRMADMIN123
 BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name => 'SCOTT',
    streams_type => 'APPLY ',
    streams_name => 'STRMADMIN_APPLY',
    queue_name => 'STRMADMIN.STREAMS_QUEUE',
    include_dml => true,
     include_ddl => true,
    source_database => 'SOURCE');
 END;
 /
  BEGIN
      DBMS_APPLY_ADM.ALTER_APPLY(
       apply_name => 'STRMADMIN_APPLY',
       apply_user => 'SCOTT'
              );
 END;
 /

  BEGIN
 DBMS_APPLY_ADM.SET_PARAMETER(
   APPLY_NAME => 'STRMADMIN_APPLY',
   PARAMETER => 'disable_on_error',
   VALUE => 'N');
  END;
/
 

DECLARE
 v_started number;
BEGIN
 SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
 FROM DBA_APPLY WHERE APPLY_NAME = 'STRMADMIN_APPLY';
 if (v_started = 0) then
   DBMS_APPLY_ADM.START_APPLY(apply_name  => 'STRMADMIN_APPLY');
 end if;
END;
/

 
Steps to be carried out at the Source Database SOURCE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 Connect to STRMADMIN schema at source and execute below steps

connect STRMADMIN/STRMADMIN123

 BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name => 'SCOTT',
    streams_type => 'CAPTURE',
    streams_name => 'STREAM_CAPTURE',
    queue_name => 'STRMADMIN.STREAMS_QUEUE',
    include_dml => true,
   include_ddl => true,
   inclusion_rule => true,
    source_database => 'SOURCE');
 END;
 /

     BEGIN
 DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
     schema_name => 'SCOTT',
     streams_name => 'STREAM_PROPAGATE',
     source_queue_name => 'STRMADMIN.STREAMS_QUEUE',
     destination_queue_name => 'STRMADMIN.STREAMS_QUEUE@TARGET',
     include_dml => true,
     include_ddl => true,
     source_database => 'SOURCE');
 END;
 /

Steps to replicate the data to destination:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

First load the dictionary information to redo log file, otherwise you might get “WAITING FOR DICTIONARY REDO” during the capture process.

exec DBMS_CAPTURE_ADM.BUILD();

get the scn of the logfile which contains dictionary information.

select  FIRST_CHANGE#,name from v$archived_log where dictionary_begin='YES';

note the first_change# from above query. Make sure the arch logfile is available in disk from the filename got from name column of above query.

Take the datapump export from source

expdp userid/password dumpfile=<filename> directory=<directory_name>  flashback_scn=<first_change#>

Note the flashback_scn option

import the datapump to target

impdp userid/password dumpfile=<filename> directory=<directory_name> streams_configuration=n

note the streams_configuration=n in impdp

go to source database

exec  DBMS_CAPTURE_ADM.STOP_CAPTURE(capture_name  => 'STREAM_CAPTURE',force=>true);

You can either give force=>true or leave it, based on your requirement.

execute dbms_capture_adm.drop_capture ('STREAM_CAPTURE');

execute DBMS_CAPTURE_ADM.CREATE_CAPTURE(queue_name => 'STRMADMIN.STREAMS_QUEUE', capture_name => 'STREAM_CAPTURE', use_database_link=>TRUE,start_scn=><first_change#>, first_scn=><first_change#>);

note the start_scn and first_scn, this value is taking from v$archived_log.

      begin
       dbms_capture_adm.set_parameter('STREAM_CAPTURE','_SGA_SIZE','2000');
       dbms_capture_adm.set_parameter('STREAM_CAPTURE','_CHECKPOINT_FREQUENCY','1000');
       dbms_capture_adm.set_parameter('STREAM_CAPTURE','TRACE_LEVEL','2');
       dbms_capture_adm.set_parameter('STREAM_CAPTURE','PARALLELISM','20');
       DBMS_CAPTURE_ADM.START_CAPTURE(capture_name  => 'STREAM_CAPTURE');
     end;
/

when you set TRACE_LEVEL parameters, incase if the capture process fails you can trace more information to debug.

Go to destination and execute

exec  DBMS_APPLY_ADM.START_APPLY(apply_name  => 'STRMADMIN_APPLY');

Make changes in primary and see whether it is getting replicated in secondary. You can create heart beat table.

heart beat table can be like

create table scott.streams_heartbeat(source_name varchar2(30),Change_dt Date);

insert into scott.streams_heartbeat('<SOURCE_DB_NAME>',SYSDATE);

COMMIT;

Update heart beat table every minute using dbms_job/scheduler or even crontab.

Execute the below statement,

select state from v$streams_capture

you should see “CAPTURING CHANGES”

convert this post to pdf.

Capture process might stop sometime due to space issue in sysaux tablespace. Checkpoint information is stored in logmnr_restart_ckpt$ for quicker restart. From 10.2 onwards oracle has enabled automatic purging using the parameter CHECKPOINT_RETENTION_TIME. By default, checkpoint information is stored for last 60 days.
In some environment 60 days could be very long.This is one reason why sysaux tablespace can grow fast.
You can change the default by setting below parameter,

exec dbms_capture_adm.alter_capture(capture_name =>,CHECKPOINT_RETENTION_TIME=> 7);

Also you can bring the hwm of the table using below command,

alter table system.LOGMNR_RESTART_CKPT$ enable row movement;
alter table system.LOGMNR_RESTART_CKPT$ shrink space ;
alter table system.LOGMNR_RESTART_CKPT$ disable row movement;

You can also shrink the primary key index on this table.

Also you can force manual checkpoint using “_checkpoint_force”=’Y’.

convert this post to pdf.

If you are managing logical standby, it’s going out of sync is common. If number of log applied is very huge, and you find transaction on one table consuming lot of time in applying.

Then skip the transaction on table as below

execute dbms_logstdby.skip('DML','SCHEMA_NAME','TABLE_NAME');

Once the remaining log file applied and standby is in sync with primary, you have instantiate the skipped table again.

One option to do is create a db_link to primary and instantiate using,

EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE(schema_name => <SCHEMA_NAME>, table_name => <TABLE_NAME>, dblink =><DB LINK NAME>);

There might be cases where the table you are trying to instantiate is very huge. That case you can use expdp to instantiate the table as below,

A. Stop SQL apply

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

B. put in a skip rule(to keep others from making changes to the table on the Logical)

EXECUTE DBMS_LOGSTDBY.SKIP('DML','TEST,'AAA');

C1.Drop and recreate the table test.aaa

drop table test.aaa;
create table test.aaa(num1 number, text1 varchar2(50));

C2. then get the datapump data from the Primary

On the Logical:

SELECT APPLIED_SCN, LATEST_SCN, MINING_SCN, RESTART_SCN FROM V$LOGSTDBY_PROGRESS;

expdp system/oracle tables=test.aaa dumpfile=expaaa.dmp flashback_scn=<scn#>

Note: Flashback scn will be restart_scn which you get from v$logstdby_progress.

4. then import the datapump data to the Logical

impdp system/oracle dumpfile=expaaa.dmp tables=test.aaa table_exists_actions=append

5. then unskip the skip rule for the table

EXECUTE DBMS_LOGSTDBY.UNSKIP(’DML’,'TEST’,’AAA’);

analyze table test.aaa compute statistics or use dbms_stats to gather statistics.

6. then start sql apply

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY ;

If you like to use exp/imp for instantiatiion follow Note 271455.1 – Synchronizing tables in a Logical Standby Database

convert this post to pdf.

If  you are in 10203 rac db and if you see crsd.bin as top consumer apply the CRS Bundle patch 3 #7117233. Till you apply the patch you can have a crontab scheduled to clean *.log in CRS_HOME/log/<hostname>/client 

convert this post to pdf.

When there is heavy load on production, logical standby will not be able to keep up the pace. This is because it has to mine the redo log convert to sql statement and apply to standby db. If there is huge gap, the apply process get struck.
Incase if you are able to know in advance about huge data load, you can set below parameters.

alter database stop logical standby apply;
execute dbms_logstdby.apply_set('MAX_SERVERS',60);
execute dbms_logstdby.apply_set('MAX_SGA',2000);
execute dbms_logstdby.apply_set('APPLY_SERVERS',52);
execute dbms_logstdby.apply_set('PREPARE_SERVERS',5);
execute dbms_logstdby.apply_set ('PRESERVE_COMMIT_ORDER','FALSE');
execute dbms_logstdby.apply_set('_MAX_LOG_LOOKBACK',1);
execute dbms_logstdby.apply_set('_EAGER_SIZE',1000);
alter database start logical standby apply;

convert this post to pdf.

Capture process in one of our client db was hanging with above message. While investigating we found out that some of the arch files need for capture is missing from os, it’s not in tape also. Only option left is to rebuild the streams. Since client is not worried about all the data being in target db, we proposed the alternate option. By which we drop the existing capture process and create a new capture process.

This is what needs to be done,

select queue_name,rule_set_name,queue_owner,rule_set_owner,negative_rule_set_owner, negative_rule_set_name from dba_capture where capture_name =’STREAM_CAPTURE’;

Note the output of above query.
Stop the capture,
exec DBMS_CAPTURE_ADM.STOP_CAPTURE(capture_name => ‘STREAM_CAPTURE’,force=>true);

Stop the apply in target,
exec DBMS_APPLY_ADM.STOP_APPLY(apply_name => ‘STRMADMIN_APPLY’);

Drop the existing capture in source,
execute dbms_capture_adm.drop_capture (‘STREAM_CAPTURE’);

Recreate the Capture in source,
execute DBMS_CAPTURE_ADM.CREATE_CAPTURE(queue_name => ‘STRMADMIN.STREAMS_QUEUE’, capture_name => ‘STREAM_CAPTURE’, use_database_link=>TRUE,rule_set_name => ‘STRMADMIN.RULESET$_244′,start_scn=>null, first_scn=>null);

Some of the values feed to above query could be feeded to CREATE_CAPTURE procedure. One important thing is note is start_scn and first_scn. If you know the scn from which to start and sure that arch file is there in disk, you can feed the start_scn and first_scn value. Otherwise set it as null.
Null value will take the current scn.

If you are feeding scn and arch file is not there, then capture process will stop with “Waiting for Dictionary Redo” message.

The above approach fixed the problem and streams started to run.

convert this post to pdf.