ORA-16038: log 4 sequence# 10840 cannot be archived,ORA-19502: write error on file "", blockno (blocksize=,ORA-00312: online log 4 thread 1: '/ora01/oradata/pbm1/redo/redo04a',ORA-16014: log 4 seque

Use LogMiner and look at a couple of the logs.  That should tell you what types of things were going on.

Did you hot backup? This happens when the DBA forget to switch the tablespaces from backup to normal mode.

Hi  johnsone,
is the concepts of log miner is available in 8i.could you please tell me how to use log miner in 8i. how i will know that which query is causing problem. As this errored occured at feb 14 so how i will which query had created the problem.???

Yes log miner is available for Oracle 8i.

What you need are the archive logs from the time that this occurred.

There are 2 packages that you need to use DBMS_LOGMNR_D and DBMS_LOGMNR.  DBMS_LOGMNR_D is not necessary, but it helps with determinings what was going on as it will translate the object ids to the actual names.

What log miner can do is recreate the statements that occurred.  They will not be the exact statements, but they will be recreated statements that would accomplish the same thing.

After using DBMS_LOGMNR_D to create a dictionary file, you use DBMS_LOGMNR.ADD_LOGFILE to add the archive logs.  After adding the archive logs use DBMS_LOGMNR.START_LOGMNR to start the process.  I will caution you to not add a lot of log files, it takes a while to go through each one.

Now query V$LOGMNR_CONTENTS.  A real simple query to use as a guage to find out what was going on is this one:

select seg_owner, seg_name, operation, count(1)
from v$logmnr_contents
group by seg_owner, seg_name, operation;

You can run log miner in any database as long as it is the same version of Oracle.  I believe it has to be the same character set as well, but I am not sure about that one.  You need to create the dictionary using DBMS_LOGMNR_D in the source database, but you can take that file to any database and use it.  The database you run log miner is does not have to have the same users or schemas either.

Hi johnsone,
thank you very much.
one more qustion as you can see my post that error occured on feb 14. now is possible to know which query or session was creating a problem. is there chance that we can know with the help of statspack report that which query or session created a problem on 14th feb.

cheers,
Daniesh

It does not matter when the problem occurred.  You just need the archive files from when it occurred.

There is a session informatoin in V$LOGMNR_CONTENTS that could help you determine who/what ran the query.

From stats pack, I do not believe you would be able to tell which query or queries generated all the redo.

hi,

ORA-16038: log 4 sequence# 10840 cannot be archived
ORA-19502: write error on file "", blockno  (blocksize=)
ORA-00312: online log 4 thread 1: '/ora01/oradata/pbm1/redo/redo04a'
ORA-00312: online log 4 thread 1: '/usr1/oradata/pbm1/redo/redo04b'
Thu Feb 14 20:16:24 2008
Thread 1 advanced to log sequence 10842
  Current log# 6 seq# 10842 mem# 0: /ora01/oradata/pbm1/redo/redo06a
  Current log# 6 seq# 10842 mem# 1: /usr1/oradata/pbm1/redo/redo06b
  Current log# 6 seq# 10842 mem# 0: /ora01/oradata/pbm1/redo/redo06a
  Current log# 6 seq# 10842 mem# 1: /usr1/oradata/pbm1/redo/redo06b
Thu Feb 14 20:16:24 2008
ARC1: Beginning to archive log# 4 seq# 10840
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive log# 4 seq# 10840
ARCH: Archival stopped, error occurred. Will continue retrying
Thu Feb 14 20:16:24 2008
ORACLE Instance stndby - Archival Error
ARCH: Connecting to console port...
Thu Feb 14 20:16:24 2008
ORA-16014: log 4 sequence# 10840 not archived, no available destinations
ORA-00312: online log 4 thread 1: '/ora01/oradata/pbm1/redo/redo04a'
ORA-00312: online log 4 thread 1: '/usr1/oradata/pbm1/redo/redo04b'
ARCH: Connecting to console port...
ARCH:
 ORA-16014: log 4 sequence# 10840 not archived, no available destinations
ORA-00312: online log 4 thread 1: '/ora01/oradata/pbm1/redo/redo04a'
ORA-00312: online log 4 thread 1: '/usr1/oradata/pbm1/redo/redo04b'
Thu Feb 14 20:16:34 2008

is there any way to solve the above error???

Thanks,
Daniesh

hi johnsone,
thanks for your reply.
i think i will not get better reply than this.
thanks once again..

cheers,
Daniesh

what is the way to check tthat my log miner is configured or not???

Thanks,
Daniesh

is anyone looking into this???

The errors occurred because the archive destination file system filled.  Clearing out the archives would solve the issue.

To see if log miner is installed, you can log in as a privileged user and describe the two packages I mentioned.  If they are there, then log miner is installed.  They should be there because the packages are created when you run catproc.

hi johnsone,
suppose my log miner is already available on my database. now i want to use the log miner to extract the transactions information. we have to use   the new logminer dictionary or the old ones which was created earlier.how i will know which log miner dictionary is available. is there any dynamic views to check it.

The dictionary for log miner is not stored in Oracle and is not part of the data dictionary, is that where the confusion is coming in?

The log miner dictionary that is created with DBMS_LOGMNR_D is a mapping between the user ids and object ids that exist in the database to the real names and also to convert the datatypes stored in the archive logs to readable values.  If you build it using DBMS_LOGMNR_D.BUILD, then a text file will be created that is the dictionary mapping and will be used as an input to DBMS_LOGMNR.START_LOGMNR.  As I said before, this is optional, but very handy, as nobody really knows what the ids are and how to read raw data.

If there have been structural changes in your database since the archive logs were created, that is OK.  If log miner cannot determine what the value is, it will still report the ids.

Hi johsone,
what i am saying is now my clients want me to use log miner to find the problematic query.
i have never used it before. he has asked me that he wants the extract the log file information from 8 to 9 pm on feb 14. coulld you please tell me whole step. so that i can deploy this steps on PROD server.
please help.....

Hi johnsone,
i need to know one more thing which thing can we know with the help of log miner.
we can know only the query which  was fired at that particular day or also the cause ??
please explain me???

Thanks,
Daniesh

From log miner, you can get the reconstructed queries and the user that ran it.  From there it will probably take some detective work to determine the application that it came from, it all depends on your environment.

An archive/redo log does not contain queries that were run.  All an archive/redo log contains are change vectors.  The values that changed and where they are located in the data file.  Using that information log miner reconstructs a query that would accomplish the same thing, however it is very likely that it is not the original query.