Logical Standby with Apply Process Fails With ORA-308

Recently, we encounter the Logical Standby Apply Process Fails With ORA-308 while testing the logical standby. Due to the archive log in Logical is automatically delete after a while.

To solve it, we first stop the automatically delete archivelog
exec dbms_logstdby.apply_set('LOG_AUTO_DELETE', 'FALSE');


Then, We check the ASM diskspace for the archivelog that is needed by standby.
Then we copy the archive log to the local folder for transfering it to standby by issuing below . The FLASH_DIR and EXPORT_DUMP_DIR is oracle directory, FLASH_DIR is the ASM disk space like +FLASHGRP/TESTDB/archivelog/

dbms_file_Transfer.copy_file('FLASH_DIR','1_3470_694714878.dbf','EXPORT_DUMP_DIR','1_3470_694714878.dbf');


After copying the file to standby, then we need to register the archivelog using below method.

Register the log file in standby

SQL> alter database register logfile '/TESTDB/archive/1_3470_694714878.dbf';

alter database register logfile '/TESTDB/archive/1_3470_694714878.dbf'

*

ERROR at line 1:

ORA-01289: cannot add duplicate logfile

If failed , then we have to update the logmnr table manually,

Stopped the logical standby.

SQL> alter database stop logical standby apply;

SQL> select file_name from system.logmnr_log$ where sequence#=3470;

FILE_NAME

------------------------------------------------------------------------------------------------------------------------------------------------------

+FLASHGRP/testdb/archivelog/2010_09_22/thread_1_seq_3470.3572.730402825

SQL> UPDATE SYSTEM.LOGMNR_LOG$ SET

FILE_NAME= 2 '/TESTDB/archive/1_3470_694714878.dbf' where sequence#=3470;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter database start logical standby apply;

Database altered.

References:-
Logical Standby Apply Process Fails With ORA-308 [ID 274676.1]

Oracle Listener on windows failed with faulting module oran110.dll

Recently we encounter the failing of listener, in our oracle XE , the error is due to a bug in the oracle. which is fixed in •9.2.0.8 Patch 1 ,•10.2.0.2 Patch 6 onwards , •10.2.0.3 Patch 2 onwards .

To workaround this issue,

in the Services, set the oracle listener to auto restart after few minutes if it encounter the failing of this services.

Metalink note:-
Windows TNS Listener Crash with Faulting Module ORANL10.DLL / ORANL9.DLL [ID 388017.1]



Event Viewer,
Faulting application TNSLSNR.EXE, version 0.0.0.0, faulting module oranl10.dll, version 10.2.0.1, fault address 0x000227ed.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Unwrapping Oracle PLSQL

To unwrap the oracle 10g and 11g source code , you can see the blog below with the source code to do that.

http://blog.teusink.net/2010/04/unwrapping-oracle-plsql-with-unwrappy.html
=== Oracle 10g/11g PL/SQL unwrapper - by Niels Teusink - blog.teusink.net ===

If you dont' have the perl then can use this website that provide the unwrap

http://hz.codecheck.ch/UnwrapIt/Unwrapped.jsp



Requirement: -
Python 2.6 from http://www.python.org/

Running dbms_scheduler within a timing interval

To run a statspack snap within a scheduled timing , you can use the below, it will start the job at 8 am and run until 8pm from monday until friday.


exec dbms_scheduler.create_job(JOB_NAME => 'JOB_STATSPACK_SNAP',job_type=> 'PLSQL_BLOCK', JOB_ACTION => 'BEGIN STATSPACK.SNAP; END; ', START_DATE => trunc(sysdate) + 8/24 , REPEAT_INTERVAL => 'FREQ=HOURLY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=8,9,10,11,12,13,14,15,16,17,18,19,20', ENABLED => TRUE, AUTO_DROP =>FALSE, COMMENTS =>'Statpacks snap') ;

another example , would be below running in between the time 8am til 8pm with interval 15 mins


exec dbms_scheduler.create_job(JOB_NAME => 'JOB_STATSPACK_SNAP',job_type=> 'PLSQL_BLOCK', JOB_ACTION => 'BEGIN STATSPACK.SNAP; END; ', START_DATE => trunc(sysdate) + 8/24 , REPEAT_INTERVAL => FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=8,9,10,11,12,13,14,15,16,17,18,19;BYMINUTE=0,15,30,45', ENABLED => TRUE, AUTO_DROP =>FALSE, COMMENTS =>'Statpacks snap') ;

To check if your calendar string is correct , you can use below to do this.


declare
L_start_date TIMESTAMP;
l_next_date TIMESTAMP;
l_return_date TIMESTAMP;
begin
l_start_date := trunc(SYSTIMESTAMP);
L_RETURN_DATE := L_START_DATE;
for ctr in 1..100 loop
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=8,20',
l_start_date, l_return_date, l_next_date
);
dbms_output.put_line('Next Run on: '
to_char(l_next_date,'mm/dd/yyyy hh24:mi:ss')
);
l_return_date := l_next_date;
end loop;
end;
/