Possible scenarios when receiving optisk error in Oracle Database, or when sessions disconnect abnormally

 A problem occurs at database level after requesting huge data from database. Database sessions disconnect abnormally while there is still packets being received. I was able to detect the problem using network monitoring tools.

One possible issue is related to the timing required to process data on one side and the expire time for inactive socket connection. The firewall could interpret the time spent by one side as inactive connection and after a while to drop the connection. Situation like this could be avoided by using the sqlnet.expire_time <> 0 on server side. By using this parameter there is no forced disconnection, a pulse sent to client understood by firewall as traffic but not visible to the client application.


Another issue that is when running via a firewall related to data contents / type of data or queries. FIXUP or FIXIT or DEEP PACKET INSPECTIONS have caused issues on sqlnet communication. I short those features change the data packet but if does not change the header details, due to this when sqlnet reads the header and the data doesn't match, it will report a packet corruption. This is possible when the firewall have these features turned on.

Using Intrusion Protection System (IPS) also could cause the same problem since IPS will lookup data packets try to modify it, or in some cause will delay packet transferee due to data fragmentation.

For more details reference:

Note 805088.1 ORA-3135 with Recovery Catalog Creation Across the Network.

Note 815165.1 ORA-12569 While Trying To Query SYS.DBA_AUDIT_TRAIL Via Oracle Net.

Thing would help further is tracing client - server connection and checking the listener.log and altert.log file if any errors reported.

Oracle Views with some examples

Oracle Views

Some useful Oracle views

dba_cons_columns	dba_constraints	dba_data_files
dba_ddl_locks	dba_errors	dba_extents
dba_free_space	dba_indexes	dba_jobs
dba_lock	dba_objects	dba_role_privs
dba_roles	dba_rollback_segs	dba_segments
dba_source	dba_tab_columns	dba_tables
dba_temp_files	dba_triggers	dba_ts_quotas
dba_undo_extents	dba_users	dba_mview_logs
dba_mview_refresh_times	dba_mviews	v$archived_log
v$asm_disk	v$backup_datafile	v$controlfile
v$database	v$datafile	v$db_cache_advice
v$filestat	v$instance	v$lock
v$locked_object	v$log	v$logfile
v$parameter	v$process	v$segment_statistics
v$session	v$session_longops	v$session_wait
v$sesstat	v$sort_usage	v$sqlarea
v$statname	v$sysstat	v$system_event
v$tempfile	v$transaction	dba_part_tables
dba_queues	dba_resumable	dba_tab_partitions

Converting from Rollback to Undo

This procedure will drop the existing rollback segments and tablespace, and replace them with an auto-managed undo tablespace.


1. Identify the rollback segments and tablespaces to be removed:

select segment_name, tablespace_name, status

from dba_rollback_segs and tablespace name != 'SYSTEM';

2. Produce offline and drop statements for each rollback segment:

select 'alter rollback segment ' || segment_name || ' offline;' from dba_rollback_segs
where tablespace_name = '&TS'
and tablespace name != 'SYSTEM'

select 'drop rollback segment ' || segment_name || ';'

from dba_rollback_segs

where tablespace_name = '&TS'

and tablespace name != 'SYSTEM'

Review the output and then cut and paste it back into sqlplus.


3. Make a note of the old tablespace's size and location, then drop it. 

drop tablespace including contents and datafiles;

4. Create a new 'undo' tablespace in place of the old one.

create undo tablespace undo 

datafile '' size 

extent management local

5. Update the initialisation parameters

If you are using a spfile (and you should be!) run the following commands:

alter system reset rollback_segments scope=spfile sid='*'

alter system set undo_management=auto scope=spfile

alter system set undo_tablespace=undo scope=spfile

If you are still using a pfile (init.ora) then do the following:

Remove the following settings:

rollback_segments=...

Add/alter these two lines:

undo_management=auto

undo_tablespace=undo

6. Restart the instance

Note the location of the alert log. If there is a problem during start-up, you will probably receive nothing more than a message saying 'instance aborted'. The alert log will contain a slightly more detailed explanation.
Restart the instance:


shutdown immediate

startup


Rerun the query from step 1. There will be a whole load of rollback segments with names like '_SYSSMU1. This is an indictaion that the instance is now using auto undo. 
Alter undo retentionalter system set undo_retention=500 scope=memory; 

What's in undo 

select tablespace_name, status, count(*) as HOW_MANY
from dba_undo_extents
group by tablespace_name, status

Export Datapump problem when using parallel paremeter in 10gR2 10.2.0.2 RAC database

When doing export datapump for Oracle database 10gR2 patch 2 RAC database, you should watch when using parallel parameter to export the data to more than one file at the same time.

when  running the following command it fails with an error, even it was running successfully before:

expdp system/pass@db10g schemas=test directory=TEST_DIR  

parallel=4 

dumpfile=test_%U.dmp logfile=test.log

This reported by oracle as a bug, and as a work around you limit the parallel parameter to 1 or patch your database to higher version such as 10.2.0.3 or 10.2.0.4. As this was solved in these patch numbers.

expdp system/pass@db10g schemas=test directory=TEST_DIR 

parallel=1 

dumpfile=test_%U.dmp logfile=test.log

Oracle Database, and RAC: How to delete, add, remove OCR and Voting Disk

This post is directed for DBAs who need to modify, or move OCR and voting disk locations. Some times a DBA wants to move to new storage area location. Typically, one would simply copy or dd the files once the new storage has been presented to the hosts. But when moveing OCR and/or voting disk its a little bit different.


ADD/REMOVE/REPLACE OCR Device
You must be logged in as the root user, because root own the OCR files.

Make sure there is a recent copy of the OCR file before making any changes:
ocrconfig –showbackup

If there is not a recent backup copy of the OCR file, an export can be taken for the current OCR file. Use the following command to generate an export of the online OCR file:
ocrconfig –export -s online

To recover using the export file use the following command:
ocrconfig import