NOLOGGING - "before backup " the datafile needs media recovery

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

NOLOGGING - "before backup " the datafile needs media recovery

NOLOGGING - "before backup " the datafile needs media recovery

2005-08-20 - By Sami Seerangan

Fortunately this happened in our dev environment but still I am trying to
recover the data if possible.
Version :9.2.0.6 <http://9.2.0.6>

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113 (See ORA-01113.ora-code.com): file 4 needs media recovery
ORA-01110 (See ORA-01110.ora-code.com): data file 4: 'Z:\ORADATA\TOSHDB\CRMF01.DBF'

SQL> alter database open;

Database altered.

SQL> select count(*) from t_nologging;
select count(*) from t_nologging
*
ERROR at line 1:
ORA-01578 (See ORA-01578.ora-code.com): ORACLE data block corrupted (file # 4, block # 12692)
ORA-01110 (See ORA-01110.ora-code.com): data file 4: 'Z:\ORADATA\TOSHDB\CRMF01.DBF'
ORA-26040 (See ORA-26040.ora-code.com): Data block was loaded using the NOLOGGING option

I loaded data using NOLOGGING option on Friday (10/19/05) and BEFORE I DO
BACKUP the datafile(CRMF01.DBF- where nologging table resides) got lost.
Now I restored CRMF01.DBF from Wednesday's (10/17/05) Full database backup
and recovered.

There are some LOGGING DMLs happened on the subject table between 10/17/05
and 10/19/05.
How to recover those transactions?

I don't concern about Friday's work because I can load them again.

Fortunately this happened in our dev environment but still I am trying to
recover the data if possible. 
Version :<a href="http://9.2.0.6">9.2.0.6</a> 
 
SQL> alter database open; 
alter database open 
* 
ERROR at line 1: 
ORA-01113 (See ORA-01113.ora-code.com): file 4 needs media
recovery 
ORA-01110 (See ORA-01110.ora-code.com): data file 4: 'Z:
\ORADATA\TOSHDB\CRMF01.DBF' 
 
 
SQL> alter database open; 
 
Database altered. 
 
 
SQL> select count(*) from
t_nologging; 
select count(*) from t
_nologging 
    &nbsp
;             
   * 
ERROR at line 1: 
ORA-01578 (See ORA-01578.ora-code.com): ORACLE data block
corrupted (file # 4, block # 12692) 
ORA-01110 (See ORA-01110.ora-code.com): data file 4: 'Z:
\ORADATA\TOSHDB\CRMF01.DBF' 
ORA-26040 (See ORA-26040.ora-code.com): Data block was
loaded using the NOLOGGING option 
 
 
 
I loaded data using NOLOGGING
option on Friday (10/19/05) and BEFORE I DO BACKUP the datafile(CRMF01
.DBF- where nologging table resides) got lost. 
Now I restored CRMF01.DBF from Wednesday's (10/17/05) Full database
backup and recovered.
 
 
 
There are some LOGGING DMLs
happened on the subject table between 10/17/05 and 10/19/05. 
How to recover those
transactions? 
 
I don't concern about Friday
's work because I can load them again.