oracle stream – Technology Geek

Oracle Streams ORA-44001: invalid schema

Posted on March 22, 2015December 23, 2018 by Osama Mustafa in Database

SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 job finished
DECLARE
*
ERROR at line 1:
ORA-23616: Failure in executing block 7 for script
13F5CD569383406C82E7F63EE3F927E0 with
ORA-44001: invalid schema
ORA-06512: at “SYS.DBMS_RECO_SCRIPT_INVOK”, line 139
ORA-06512: at “SYS.DBMS_STREAMS_RPC”, line 465
ORA-06512: at “SYS.DBMS_RECOVERABLE_SCRIPT”, line 659
ORA-06512: at “SYS.DBMS_RECOVERABLE_SCRIPT”, line 682
ORA-06512: at “SYS.DBMS_STREAMS_MT”, line 7972
ORA-06512: at “SYS.DBMS_STREAMS_ADM”, line 2674
ORA-06512: at line 5

The error related to apply_queue parameter in oracle stream is not set with valid value, therefore set the parameter correctly like the below

apply_queue_user => ‘STREAM_ADMIN’

Cheers
Osama Mustafa

waiting for dictionary redo first scn

Posted on March 14, 2012December 23, 2018 by Osama Mustafa in Database

Capture status : Streams Waiting for dictionary first scn

Example :

Capture is waiting on redo log file with SCN 5611274208824
Capture

The First SCN 5611441137818
Start SCN 5611441137818

Applied Scn 5611441373264
Required SCN 5611441137818

You can get the above information from enterprise manager
Maintenance -> Stream -> management -> capture (choose capture name and edit).

Note :
1-Applied Scn + Required SCN : you can’t change them (read only).

2-Set First SCN + Start SCN same as Required SCN .

[Document 313279.1] Master Note for Troubleshooting Streams capture ‘WAITING For REDO’ or INITIALIZING

ACTION Plan #1 :

A) Restore archived redo logs starting with sequence
B) If you can not restore logs, then capture must be droppend and recreated.
Note 471695.1 – Required Steps to Recreate a Capture Process

You should be running dbms_capture_adm.build on a regular basis to allow you to rebuild capture without having to resync.

ACTION Plan #2 :

look at the output from below sql and ensure that ALL logs (by time) are online and available.

++ Registered Log Files for Capture ++
COLUMN CONSUMER_NAME HEADING ‘Capture|Process|Name’ FORMAT A15
COLUMN SOURCE_DATABASE HEADING ‘Source|Database’ FORMAT A10
COLUMN SEQUENCE# HEADING ‘Sequence|Number’ FORMAT 999999
COLUMN NAME HEADING ‘Archived Redo Log|File Name’ format a35
column first_scn HEADING ‘Archived Log|First SCN’
COLUMN FIRST_TIME HEADING ‘Archived Log Begin|Timestamp’
column next_scn HEADING ‘Archived Log|Last SCN’
COLUMN NEXT_TIME HEADING ‘Archived Log Last|Timestamp’
COLUMN MODIFIED_TIME HEADING ‘Archived Log|Registered Time’
COLUMN DICTIONARY_BEGIN HEADING ‘Dictionary|Build|Begin’ format A6
COLUMN DICTIONARY_END HEADING ‘Dictionary|Build|End’ format A6
COLUMN PURGEABLE HEADING ‘Purgeable|Archive|Log’ format a9

SELECT r.CONSUMER_NAME,
r.SOURCE_DATABASE,
r.SEQUENCE#,
r.NAME,
r.first_scn,
r.FIRST_TIME,
r.next_scn,
r.next_time,
r.MODIFIED_TIME,
r.DICTIONARY_BEGIN,
r.DICTIONARY_END,
r.purgeable
FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
WHERE r.CONSUMER_NAME = c.CAPTURE_NAME
AND r.SEQUENCE# > 48056
ORDER BY source_database, consumer_name, r.first_scn;

After verifying that all those logs are available, then for capture , advance first/start snc
login to strmadmin

exec dbms_capture_adm.alter_capture(‘capture-name’,first_scn=>same as the above);
exec dbms_capture_adm.alter_capture(‘capture-name’,start_scn=>same as the above);

Verify

SELECT CAPTURE_NAME, FIRST_SCN, START_SCN, APPLIED_SCN, REQUIRED_CHECKPOINT_SCN FROM ALL_CAPTURE;

Restart capture.

##To check if SCN changed :

SELECT SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(‘)+1,4) PROCESS_NAME,
c.CAPTURE_NAME,
C.STARTUP_TIME,
c.SID,
c.SERIAL#,
c.STATE,
c.state_changed_time,
FROM gV$STREAMS_CAPTURE c, gV$SESSION s
WHERE c.SID = s.SID AND
c.SERIAL# = s.SERIAL#;

ACTION PLAN #3 :

If the Missing Archivelog found on ASM but Capture can’t see them you have to do the following :

ALTER DATABASE REGISTER LOGICAL LOGFILE ” FOR ”;

ALTER DATABASE REGISTER OR REPLACE LOGICAL LOGFILE ‘PATH’ FOR ‘Capture-name’;

Then Check By :

select name, sequence# from v$archived_log — use gv$archived_log in RAC
where between FIRST_CHANGE# and NEXT_CHANGE#
order by name;

Hope this will be Useful for you
Finally Don’t forget after you resolve the problem ro run this command once everyday to rebuild capture without having to resync

SQL> exec DBMS_CAPTURE_ADM.BUILD;

Check This Link : Stream Capture

Thank you
Osama Mustafa
Oracle Database Consultant

Replicating data via materialized views

Posted on March 6, 2012December 23, 2018 by Osama Mustafa in Database

Sometimes We Need The Same Data For specific Tables On Other Database , What Should I Do ?

As We Know There’s Oracle Solution For this issue :
1- Oracle Stream.
2- Golden Gate.
3-Standby Database.

The Above Solution Depends On what you need . What If I need One Table Or Two Or Three Table on another Database Some will say export/import , Sql Loader .

But why we don’t Use Materialized Views ,The below Steps Shows how to Replicating data via materialized views

The main difference between regular and materialized view is that the latter does not query the original tables for every user request. The materialized view holds a copy of their data instead.

Source Database : Source
Target Database: Target

1-create the USERS table and add some sample records inside.

SQL> CONNECT SYSTEM/*********@Source
Connected.
SQL> CREATE USER APP1 IDENTIFIED BY APP1;

User created.

SQL> GRANT CONNECT,RESOURCE TO APP1;

Grant succeeded.

SQL> CONNECT APP1/APP1@Source
Connected.

SQL> CREATE TABLE USERS (USER_ID INTEGER PRIMARY KEY,
  FIRST_NAME VARCHAR2(30), LAST_NAME VARCHAR2(30), PASSWORD VARCHAR2(20));

Table created.

SQL> INSERT INTO USERS VALUES (1, 'BRIAN', 'ALDISS', 'PASS1')

1 row created.

SQL> INSERT INTO USERS VALUES (2, 'POUL', 'ANDERSON', 'PASS2')

1 row created.

SQL> INSERT INTO USERS VALUES (3, 'NEAL', 'ASHER', 'PASS3')

1 row created.

SQL> COMMIT

Commit complete.

2-After That We need Move to The Target Database , We need to Create Database Link to make connection between the two databases , editing the TNSNAMES.ORA file and adding a record for Source there

Source =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.11.11.10)
      (PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

We need to test it by “tnsping Source” .

3-connect to the local database (Target) and create the user account for the APP2 application.

SQL> CREATE USER APP2 IDENTIFIED BY APP2

User created.

SQL> GRANT CONNECT, RESOURCE, CREATE MATERIALIZED VIEW, CREATE DATABASE LINK TO APP2

Grant succeeded.

4-Connect To APP2 (User that we create it On target Database ).

SQL> CONNECT APP2@Target
Enter password:
Connected.

SQL> CREATE DATABASE LINK Source CONNECT TO APP1 IDENTIFIED BY APP1 USING 'Source';

Database link created.

Now You must be able to Select any table from user APP1 On Source Database .
For Example :

Select * from User@source .

5-After we did the above steps we will create materialized view On target Database :

SQL> CREATE MATERIALIZED VIEW V_USERS REFRESH NEXT
  SYSDATE+INTERVAL '10' MINUTE WITH ROWID AS SELECT * FROM USERS@Source;

“REFRESH NEXT SYSDATE + INTERVAL ’10’ MINUTE asks the database to refresh the materialized view every 10 minutes”

Notices : There’s Lot Of Attribute for materialized view You don’t have to use the same Sentence its depend on what you need and how you will use it.

6- To Test It

On APP2@Target Users

SQL > SELECT COUNT(*) FROM V_USERS

It Must Give you same Number Of Row In APP1@Source , If you want to test synchronization All you have to do is

On Source Database :

APP1@source

INSERT INTO USERS VALUES (4, 'ROBERT', 'ASPRIN', 'PASS4')

Wait For Sync .

Hope its Simple and Easy
Osama Mustafa

Oracle Stream Stop Replication without any error

Posted on October 21, 2011December 23, 2018 by Osama Mustafa in Database

I faces the most strange issue that you will ever seen , Everything was works fine without any problem but suddenly without any error Replication on database stopped Why ?

No one was know , I try everything that you could be imagine , i try to register the archive log manually :

ALTER DATABASE REGISTER LOGICAL LOGFILE
‘Archivelog-name’ FOR ‘Capture-name’;

But nothing works, after that i start trace Capture process maybe it will give me hints with following produce:

1. Stop the capture(DBMS_CAPTURE_ADM.STOP_CAPTURE)

2. Enable tracing:

sqlplus /nolog
connect / as sysdba
alter system set events ‘26700 trace name context forever, level 6’;
alter system set events ‘1349 trace name context forever , level 1024’;
exit

sqlplus strmadmin/passwd (streams admin)

exec dbms_capture_adm.set_parameter(‘yourcapturename’,’trace_level’,’127′);
exec dbms_capture_adm.start_capture(‘yourcapturename’);

Wait for about 30 minutes an check whether capture is or not progressing..

3. To turn off capture tracing:

sqlplus strmadmin/passwd (streams admin)

Stop the capture(DBMS_CAPTURE_ADM.STOP_CAPTURE)
exec dbms_capture_adm.set_parameter(‘yourcapturename’,’trace_level’,null);
exit

sqlplus /nolog
connect / as sysdba

alter system set events ‘26700 trace name context off’;
alter system set events ‘1349 trace name context off’;
exit

Yes Nothing works , No error , its make me crazy

Tried checkpoint force, stop/start capture.

tracing the capture process:

1. Stop the capture(DBMS_CAPTURE_ADM.STOP_CAPTURE)

exec dbms_capture_adm.set_parameter(‘yourcapturename‘,’trace_level’,’127′);

exec dbms_capture_adm.start_capture(‘yourcapturename‘);
“set trace off after 10-15 mins”:

2. To turn off capture tracing:
exec dbms_capture_adm.set_parameter(‘yourcapturename‘,’trace_level’,null);

Yea Nothing Works too

Then I start check if there’s some missing Archivelogs By :

select NAME,SEQUENCE#,STATUS,DELETED from V$ARCHIVED_LOG

Information about archive logs that needed by Oracle Stream :

SELECT r.CONSUMER_NAME,
r.SOURCE_DATABASE,
r.SEQUENCE#,
r.NAME
FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
WHERE r.CONSUMER_NAME = c.CAPTURE_NAME AND
r.NEXT_SCN >= c.REQUIRED_CHECKPOINT_SCN
order by 3;

and using this query could help sometimes to give you information TXN about apply process :

select apply_name, xidusn||’.’||xidslt||’.’||xidsqn txn_id, first_scn, first_message_create_time, message_count, spill_creation_time from dba_apply_SPILL_TXN;

Output : (Usually The last one)

APPLY_FROM_MARKA               9.27.637276                         5600212722474 17-OCT-11            25    18-OCT-11
APPLY_FROM_MARKA               4.23.369085                         5600212761449 17-OCT-11             1       18-OCT-11
APPLY_FROM_MARKA               56.17.156892                        5600242177417 18-OCT-11             1       18-OCT-11
APPLY_FROM_MARKA               4.40.369226                        5600243099471 18-OCT-11             1       18-OCT-11
APPLY_FROM_MARKA               24.21.419884                         5600247380679 18-OCT-11            15      19-OCT-11
APPLY_FROM_MARKA               14.20.166840                         5600278453686 19-OCT-11             1       19-OCT-11

After Check The TXN we need to Run the Stream to ignore the required TXN :

1-exec dbms_apply_adm.stop_apply(‘Apply-process-name’);

2-exec dbms_apply_adm.set_parameter(‘Apply-process-name’,’_ignore_transaction’,’TXN-Number’);

3-exec sys.purge_spill_txn(‘Apply-process-name’,’TXN-Number’);

4-exec dbms_apply_adm.start_apply(‘Apply-process-name’);

5-exec dbms_apply_adm.set_parameter(‘Apply-process-name’,’_ignore_transaction’,null);

Capture Site:

1-execute dbms_capture_adm.stop_capture(‘capture-Process-name’);

2-execute dbms_capture_adm.set_parameter(‘capture-Process-name’,’_ignore_transaction’,’TXN-Number’);

3-execute dbms_capture_adm.start_capture(‘capture-Process-name’);

But Nothing works too .

But I remember something Stream got hidden parameter _SGA_Size :

Capture needs to be configured to have more space.

The default amount of space for capture / log miner activity is 10

Steps :

exec dbms_capture_adm.set_parameter(‘Capture-procees-name’,’_SGA_SIZE’,’50’);

exec dbms_capture_adm.start_capture(‘Capture-procees-name’);

The above changes space for named capture process : ‘STRMADMIN_CAPTURE’ from 10M -> 50M

Thank God Its works again .

Note that i write every steps maybe something will be useful for you .

Some metalink note will be useful too :

Troubleshooting Long-Running Transactions in Oracle Streams (Doc ID 783927.1)

LogMiner out-of-memory (Doc ID 336705.1)