OraFAQ Forum: Server Administration » Updating record in missing physical data files

Home » RDBMS Server » Server Administration » Updating record in missing physical data files

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Updating record in missing physical data files [message #165011]

Tue, 28 March 2006 00:27

abemie
Messages: 3
Registered: March 2006
Location: Kuala Lumpur, Malaysia

Junior Member

Hi

I'm simulating backup and recovery using hotbackup. My application running on Oracle 9i 9.2.0.4.0 revision database with ARCHIVE. OS pLatform is Red Hat AS 3 platform.

Steps taken :-

1. Backup using hotbackup

2. Insert 10000 records in table TB_AAA located in USERS tablespace.
This tablespace has one datafile located at /disk2/oradata/orassm/users01.dbf.

3. Physicallay remove the datafile /disk2/oradata/orassm/users01.dbf thru OS.

4. Repeat steps 2, by insert more records (> 40000).
Expected result : The system won;t allowed since the data file physically missing
Actual result : The system still allow and accept the record inserted. Why and how this work ?
The system also did not prompt any error messages in alert.log
By query from v$datafiles ... the data file still in ONLINE status.

---> select count(*) from TB_AAA = 50000

Recovery Stage
5. Make the data file OFFLINE

6. Copy the data file from hot backup done in step 1 thru OS

6. do recover data file '/disk2/oradata/orassm/users01.dbf'

7. Alter database data file /disk2/oradata/orassm/users01.dbf ONLINE;

---> select count(*) from TB_AAA = 50000. Expected result is 10000.

Can somebody advice me on this. I still confused why the data still can be inserted/updated although the physical data file is missing.

Thanks

Report message to a moderator

Re: Updating record in missing physical data files [message #165109 is a reply to message #165011]

Tue, 28 March 2006 06:21

tarundua
Messages: 1080
Registered: June 2005
Location: India

Senior Member

Can you post the session where you ran the commands?

For eg:

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1        212  104857600          1 NO  CURRENT
     43656989 28-MAR-06

         2          1        210  104857600          1 NO  INACTIVE
     43102603 26-MAR-06

         3          1        211  104857600          1 NO  ACTIVE
     43379859 27-MAR-06

The above is just an example.

Report message to a moderator

Re: Updating record in missing physical data files [message #165110 is a reply to message #165109]

Tue, 28 March 2006 06:26

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Did you try to restart the database ( after deleting the datafile)?
Please try it. you will understand.

Report message to a moderator

Re: Updating record in missing physical data files [message #165159 is a reply to message #165110]

Tue, 28 March 2006 10:41

abemie
Messages: 3
Registered: March 2006
Location: Kuala Lumpur, Malaysia

Junior Member

Hi

The error messages will be prompt only when i try to do shutdown immediate. It won't allow to proceed the database shutdown until I change the missing data file to be OFFLINE.

I still confuse where the records are really stored when the physical datafile which store the table schema is removed.

Report message to a moderator

Re: Updating record in missing physical data files [message #165171 is a reply to message #165011]

Tue, 28 March 2006 12:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I still confuse where the records are really stored when the physical datafile which store the table schema is removed.
Because the datafile is NOT really "removed" the records continue to be written into the datafile.
In Unix when process #1 opens a file for write & writes data into it; the inode remains valid even after process #2 deletes the file.
Only after process #1 closes the file, is the inode tagged as really having been deleted.

Report message to a moderator

Re: Updating record in missing physical data files [message #165362 is a reply to message #165171]

Wed, 29 March 2006 09:57

abemie
Messages: 3
Registered: March 2006
Location: Kuala Lumpur, Malaysia

Junior Member

Sorry because I not so sure if that is the reason.
Could it be because the database is running on archive mode, although the physical data file is corrupted/missing , the new records insert and updated will be stored in archive log.

But I still don't get full understanding / conclusion on this matter.
If u guys can simulate the same thing, maybe we can share te result is.

Thaks

Report message to a moderator

Previous Topic:	SQL Statement process
Next Topic:	SQL and email

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 18 18:42:43 CST 2025