ORA-00338 - Log 3 Of Thread 1 Is More Recent Than Control File
May 9, 2013
I encountered error ORA-00338 while performing incomplete database recovery:
ORA-00283: recovery session canceled due to errors
ORA-00338: log 3 of thread 1 is more recent than control file
ORA-00312: online log 3 thread 1: '+DG_REDO/dbase/onlinelog/group_3.267.814820795'
Recovery Manager complete.
I performed this recovery on oracle 10g, ASM database.
1. What are possible causes behind this error ORA-00338 ?
2. This error occurs at the start of media recovery, while RMAN is applying archived logs. So does RMAN apply "online REDO" logs after applying "archived" logs required for recovery or before them ?
3. How does rman come to know that online log 3 is more recent than control file ? How can I get scn of online log & control file in my environment ?
Following are few details related to checkpoint scn/change# info. I found in my environment..Before recovery:
The controlfiles were corrupted due to some issue. So, I had to restore the recent backed up control file and then restore the database.
The weird thing which I have noticed is that. I tried backing up Yesterday's Control file. So, I would say that the backup pieces it should pick should be from yesterday. however, the backup pieces are getting restored from last week's backup.
I have to load data file into a table. And the requirement is as below:
Input Data:
1234|20130815|20130822|This is a test, this is the the part 3456|20130823|20130809|This is a test 3456|20130823|20130809|This is a test 3456|20130823|20130809|This is a test 3456|20130823|20130809|Siva 1234
The data should be inserted only in two rows as below:
When Value in first 3 fields is same, 4th field should be appended to the existing value in table.
1234|20130815|20130822|This is a test, this is the the part 3456|20130823|20130809|This is a testThis is a testThis is a testSiva 1234
is there any way you can get the name of the file loading the table in a control file? i have a table with a column called source_file, and need to populate it during the load.
I need to return 1 row for each ID1 value - and only the ID2 value of 24 and only the most recently dated record for the multiple ID2 values - query would return:
1246/1/2006410 2247/1/2007360
I have worked and worked on this and I am still stumped (part of the problem may be I am also trying to make this work in Crystal Reports but that is for another day). I need to make this work in Oracle first.
GROUP# THREAD# MEMBER ARCHIVED STATUS MB 1 1 +DATA01/mydb/redolog_group1_member1 NO INACTIVE 1536 1 1 +DATA01/mydb/redolog_group1_member2 NO INACTIVE 1536 2 1 +DATA01/mydb/redolog_group2_member2 NO CURRENT 1536 2 1 +DATA01/mydb/redolog_group2_member1 NO CURRENT 1536 3 1 +DATA01/mydb/redolog_group3_member2 NO INACTIVE 1536 3 1 +DATA01/mydb/redolog_group3_member1 NO INACTIVE 1536Tue Jul 10 18:37:48 2012 [code]....
Could an increase on the redo log file sizes solve this problem? Unfortunately I do not have a test platform, where I could have tested this.
As stated in "Determining which Oracle database session is being used by a specific Siebel user session [ID 863771.1]":
On the Oracle database side, you can query the V$SESSION table (make sure you are logged in as the tableowner) using the following WHERE clause format:
WHERE PROCESS='XXXX:YYYY'
XXXX = Server PID YYYY = Thread ID
So querying v$session, sessions opened by windows clients have in the PROCESS column values like "12345:876", where 12345=process id and 876=thread id.
Our application servers are installed in AIX, which is also multithreaded, but in the PROCESS column only process id is stored. Is there any way to find thread id?
FYI,I have taken control file backup and DB backup also 6 hour before the control file get corrupted,then there are quite lot of DB structure change, so Now how to recover my control file with the upto time DB state , since the backup was taken for control file 6 hour before the file get corrupted, now how do I revery the DB/control file, step by step with syntax as well.
I know this question must have been asked several time. I just want to know the many ways in which we can have a DB up and running even if we do not have any backup of control file not even the trace of the same.
I am just looking to my control file that I have. I have managed to successfully get my control file to load data into the required tables but just for my learning I was trying to amend the when clause with an and condition but keep getting an error.
My code: OPTIONS (DIRECT=TRUE, ERRORS=0, SKIP=1) load data infile 'A:/My Files/Feeds/20130211/data_summary_1_20131214.txt' "STR ' '"
APPEND into table I_DATA_1 WHEN (RID <> 'RID') fields terminated by "~|~" optionally enclosed by '"' TRAILING NULLCOLS ( RID, S_TIMESTAMP timestamp 'DD-MON-RR HH:MI:SS.FF3 PM' )
the above code works perfectly but if i change the following line:
WHEN (RID <> 'RID' AND 'S_TIMESTAMP' IS NOT NULL)than this gives me an error.
mission_id, mission_type_id, security_level and code_name.
What i have to do is get the 10 most recent missions and change their security level to the highest one in their mission_type_id but ONLY if code_name length is >7
So far I have this, the problem is that the oracle moans about the order and wants me to close the bracket before the order
sql
UPDATE missions m SET m.security_level = ( SELECT max(m2.security_level) FROM missions m2 WHERE m2.mission_type_id = m.mission_type_id AND length(m2.code_name) > 7 ) WHERE m.mission_ID IN ( Select m3.mission_id From missions m3 ORDER BY m3.mission_id desc)
I am receiving errors when trying to load the control file. The errors are as follows:
SQL*Loader-500 Unable to open file (homework.ctl) SQL*Loader-553 file not found SQL*Loader-559 SYstem error: The system cannot find the file specified.
My control file is located directly in the C drive (C:\homework.ctl). The control file contains the following
LOAD DATA INFILE 'c:\country.dat' APPEND INTO TABLE homework fields terminated by ',' optionally encloded by '"' (country, month, day) WHEN (month='April')
load data infile 'C:\Documents and Settings\xxxxx\Desktop\abc.txt' APPEND PRESERVE BLANKS INTO TABLE table1
[code]...
When I run the above control file in sqlldr, I'm getting the error as Record 1: Rejected - Error on table table1, column column3. ORA-01481: invalid number format model
In the table the column3 data type is NUMBER(6,2).: The column size in table is 6 and position of column3 in control file is only 4. Also if possible let me know how the same data (send me 2 dummy records) which exactly works for the above control file especially for column3 where decimal number comes in the flat file.
For generating the flat file, for column3, i'm using LPAD(:value,4,0) in the select query column list.
I have a problem with my control file [URL].........
What I did, I have a duplicate of the database with the invalid control file, let's call it DUMDB. I am using my control file and no recovery-catalog-db.
I recreated the control file. Now everything is working fine, new backups can be taken, restores can be made. I just need to restore a backup tagged with "LASTDEV". The control file for this backup is the invalid one. So I need a restore WITHOUT restoring the control file.
using target database control file instead of recovery catalog
List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 2.77G DISK 00:00:00 26-AUG-11 BP Key: 1 Status: AVAILABLE Compressed: YES Tag: LASTDEV Piece Name: /u03/flash_recovery_area/DUMDB/backupset/o1_mf_nnndf_LASTDEV_75fmkllw_.bkp
List of Datafiles in backup set 1 =========================== File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 63166185 26-AUG-11 /u02/oradata/dumdb/system01.dbf 2 Full 63166185 26-AUG-11 /u02/oradata/dumdb/sysaux01.dbf 3 Full 63166185 26-AUG-11 /u02/oradata/dumdb/undotbs01.dbf 5 Full 63166185 26-AUG-11 /u02/oradata/dumdb/stdtbs01.dbf 6 Full 63166185 26-AUG-11 /u02/oradata/dumdb/users.dbf
CODERMAN> crosscheck backupset 1;
using channel ORA_DISK_1 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u03/flash_recovery_area/DUMDB/backupset/o1_mf_nnndf_LASTDEV_75fmkllw_.bkp RECID=1 STAMP=761603187 Crosschecked 1 objects
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 09/11/2011 20:32:25 RMAN-06026: some targets not found - aborting restore RMAN-06023: no backup or copy of datafile 6 found to restore RMAN-06023: no backup or copy of datafile 5 found to restore RMAN-06023: no backup or copy of datafile 3 found to restore RMAN-06023: no backup or copy of datafile 2 found to restore RMAN-06023: no backup or copy of datafile 1 found to restore
This is exactly the same message if you do "RESTORE DATABASE FROM TAG='NONEXISTENTDUMMYTAGXXXX'" Why can the data files not be found even if they are listed in the backup?
We have a database that is accessed by ArcSDE, a product to modify maps. It uses BLOBs to store those maps.
We ran a load on the server and the response time was slow. By running the following query:
select event, total_waits, time_waited, avg_ms, round(ratio_to_report(time_waited) over () * 100) percent from (select substr(event, 1, 30) event, total_waits, time_waited, round(time_waited_micro / total_waits / 1000, 2) avg_ms from v$system_event where wait_class in ('System I/O') union select 'CPU' event, NULL, value, NULL from v$sysstat where statistic# = 12 order by 3 desc) where rownum <= 10;
ALTER DATABASE OPEN RESETLOGS * ERROR at line 1: ORA-00344: unable to re-create online log 'D:\ORADATA\PROD\REDO01B.ORA' ORA-27040: skgfrcre: create error, unable to create file OSD-04002: unable to open file O/S-Error: (OS 5) Access is denied.
differences of enabling thread as public or private?
lets assume that i have two threads (1, 2) for two instances (1, 2) and each of thread consists of 2 redolog groups - minimum configuration.in case i need add third node i need third thread.
what will be difference if i set this thread as public instead private?
I want to use rman to restore all my control files, I just did a level 0 incremental backup and ran the following script.
rman <<EOF connect target sys/sys@GOLD_AUX connect catalog rman/rman@GOLD_CAT RUN { STARTUP FORCE NOMOUNT; RESTORE CONTROLFILE; ALTER DATABASE MOUNT; ALTER DATABASE OPEN RESETLOGS; } EOF
My control files seem to be put back in the correct spot and suggest a way I can restore only my control files and have everything in sync with my datafiles.
I am trying to create a new controlfile. I used "alter database backup controlfile to trace;" and then I copied and pasted the contents of the trace file into the .sql file. My sql script is
Actually, the name of my database was 'STAR' and I changed it to "FIVESTAR" int this .sql script. Now when I run this script with SQL> @ <path of my sql script>, it gives me the following errors:
ERROR at line 1: ORA-01503: CREATE CONTROLFILE failed ORA-01504: database name 'FIVESTAR' does not match parameter db_name 'STAR'
I'm having a problem with SQL loader and the control file. I want to load a delimited file. The script will eventually be automated where the file name is passed in to the script, it's not a static name.
It's a simple SQL loader Unix script that I have created as follows
I took database backup without "change the controlfile configuration autobackup on".. and unfortunately i deleted my controlfile. Then I tried to restore my controlfile but it gives error,now how can i restore controlfile is there any other way to restore controlfile with aautobackup..
I was doing some R&D on my test machine trying to understand how controlfile works. I started up my db and then deleted (renamed)the controlfiles at OS level. I was expecting the db to shutdown. But it dint. Moreover when i queried select name from v$controlfile; it was still reflecting the old controlfile names. To check if it was still functioning, i created a new tablespace with one file and it also got created without any error.
What i dint understand is how could the db still be running when the control file was'nt there and new tablespace and datafile get created? As i understand, whenever a new file is created, an entry is made in the control file. But when control file dint exist where was the data about new tablespace and datafile written?
