Tag: Metalink

Oracle Stream Stop Replication without any error

Posted on 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 :
COLUMN CONSUMER_NAME HEADING ‘Capture|Process|Name’ FORMAT A15
COLUMN SOURCE_DATABASE HEADING ‘Source|Database’ FORMAT A10
COLUMN SEQUENCE# HEADING ‘Sequence|Number’ FORMAT 99999
COLUMN NAME HEADING ‘Required|Archived Redo Log|File Name’ FORMAT A40

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)

Manually Install/De-Install Database Options in Oracle Database 10gR2

Posted on by Osama Mustafa in Database
Hi,

When a new database is created using DBCA, by default some of the common database options are installed like Oracle JVM and Oracle Text. But, when you manually create the database none of them get installed. You need to manually install them. I thought of sharing Metalink Note Id’s for some of the Common Database Options which you can manually install/de-install once database has been created:

Oracle JVM
Note:276554.1 How to Reload the JVM in 10.1.0.X and 10.2.0.X


Oracle Text
Note:280713.1 Manual installation, deinstallation of Oracle Text 10gR1 and 10gR2


Oracle XML DB
Note:243554.1 How to Deinstall and Reinstall XML Database (XDB)


Oracle Multimedia
Installation, Upgrade and Downgrade information can be found in the following Manual: Oracle® interMedia User’s Guide 10g Release 2 (10.2) Appendix B Installing and Upgrading Oracle interMedia 


Oracle OLAP
Note:296187.1 How To Manually Install Oracle OLAP into a 9i or 10g Database After the DB Has Been Created 


Oracle Spatial
Note:270588.1 Steps for Manual Installation / Verification of Spatial 10g


Oracle Ultra Search
Note:337992.1 How to Manually Install and Uninstall Ultra Search 10gR2 Server Components 


Oracle Label Security
Note:171155.1 How to Install / Deinstall Oracle Label Security Oracle9i/10g


Sample Schemas
  Note:340112.1 How To Install Sample Schemas in 10g?


Enterprise Manager Repository
Note:114763.1 How to Create Enterprise Manager 2.1 Repository Using EMCA


Oracle Application Express
Note:445205.1 How to download and install Application Express / HTMLDB


Oracle Warehouse Builder
Note:459961.1 How to Install Warehouse Builder Repository(10.2.0.1.31) on a RAC Database on Windows and UNIX 


Oracle Database Vault
Note:445092.1 How to Install Database Vault Patches on top of 10.2.0.3


Oracle Database Extensions for .NET
Note:374820.1 How to Install the Oracle Database Extensions for .NET (ODE.NET) Hope you find this document helpful 🙂

Thanks For our Friend Momen For this Artical its was posted in his blog.
and i share for more useful information .