Home » RDBMS Server » Server Administration » tablesapce offline
| tablesapce offline [message #60845] |
Tue, 09 March 2004 19:43  |
vadlamani
Messages: 39
Registered: November 2003
|
Member |
|
|
hi,
can anyone make me understand this concept
When we take a tablespace offline ( for DB in noarchive log mode) ,then a check point is occured & the dirty buffers are written to the datafile.Alright!!
I updated some tables in USERS tablespace...then through another session i have taken the USERS tablespace offline, then i have issued rollback, then i got ROLLBACK COMPLETE..( but this means my data from the Rollback segements is written back to the DATAFILE. How can this be possible my tablespace is offline)
Can some one tell me whats happening!!
thank you
satish
|
|
|
|
| Re: tablesapce offline [message #60847 is a reply to message #60845] |
Tue, 09 March 2004 23:18  |
sachin kumar gupta
Messages: 157
Registered: March 2003
|
Senior Member |
|
|
I think the sentence "this means my data from the Rollback segements is written back to the DATAFILE." is wrong.
First of all once u commit or rollback, doesn't means that the changes has been written to datafile immediately. The changes in this problem might have been done at database buffer cache & redolog buffer cache level. So rollback didn't required the tablespace to be online.
Pls. wait for more lights by dba gurus.
Regards,
Sachin
|
|
|
|
| Re: tablesapce offline [message #60857 is a reply to message #60845] |
Wed, 10 March 2004 10:14 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Ok,lets see what goes on..
-- Confirm checkpoint SCN increases when a tablespace goes offline
14:11:24 SQL> select max(CHECKPOINT_CHANGE#) from v$datafile;
MAX(CHECKPOINT_CHANGE#)
-----------------------
391172624
14:11:46 SQL> alter tablespace users offline;
Tablespace altered.
14:11:55 SQL> select max(CHECKPOINT_CHANGE#) from v$datafile;
MAX(CHECKPOINT_CHANGE#)
-----------------------
391181210
-- Confirmed.
--------------------------------------------
Dirty buffers written to online datafiles during Checkpoint : YES
------------------------------
Session 1 :
14:20:17 SQL> select table_name,tablespace_name from user_Tables where table_name='T1';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T1 USERS
14:20:34 SQL> select count(*) from t1;
COUNT(*)
----------
1
14:20:54 SQL> delete from t1;
1 row deleted.
Session 2:
----------
-- lets get the checkpoint SCN and other details of this datafile
13:01:14 SQL> select name,status,CHECKPOINT_CHANGE#,to_char(CHECKPOINT_TIME,'DD-MON-YY HH:MI'),
LAST_CHANGE#,to_char(LAST_TIME,'DD-MON-YY HH:MI'),OFFLINE_CHANGE#,ONLINE_CHANGE#
from v$datafile where name='/oradb04/dbatest/users01.dbf';
14:22:08 2 14:22:08 3
NAME
----------------------------------------------------------------------------------------------------
STATUS CHECKPOINT_CHANGE#
------- ------------------
TO_CHAR(CHECKPOINT_TIME,'DD-MO LAST_CHANGE#
--------------------------------------------------------------------------- ------------
TO_CHAR(LAST_TIME,'DD-MON-YYH OFFLINE_CHANGE#
--------------------------------------------------------------------------- ---------------
ONLINE_CHANGE#
--------------
/oradb04/dbatest/users01.dbf
ONLINE [b]391181296[/b]
10-MAR-04 02:12
391181210
[b]391181296[/b]
-- this is the latest scn
14:22:39 SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
391182973
-- this is the latest checkpoint scn
14:22:54 SQL> select max(CHECKPOINT_CHANGE#) from v$datafile;
MAX(CHECKPOINT_CHANGE#)
-----------------------
[b]391181296[/b]
-- OFFLINE the tablespace
14:23:31 SQL> alter tablespace users offline;
Tablespace altered.
14:23:41 SQL> select max(CHECKPOINT_CHANGE#) from v$datafile;
MAX(CHECKPOINT_CHANGE#)
-----------------------
391183133
-- All the datafiles got checkpointed with the latest SCN and this is recorded in the controlfile
14:25:07 SQL> select name,status,CHECKPOINT_CHANGE#,to_char(CHECKPOINT_TIME,'DD-MON-YY HH:MI'),
LAST_CHANGE#,to_char(LAST_TIME,'DD-MON-YY HH:MI'),OFFLINE_CHANGE#,ONLINE_CHANGE#
from v$datafile where name='/oradb04/dbatest/users01.dbf';
14:25:16 2 14:25:16 3
NAME
----------------------------------------------------------------------------------------------------
STATUS CHECKPOINT_CHANGE#
------- ------------------
TO_CHAR(CHECKPOINT_TIME,'DD-MO LAST_CHANGE#
--------------------------------------------------------------------------- ------------
TO_CHAR(LAST_TIME,'DD-MON-YYH OFFLINE_CHANGE#
--------------------------------------------------------------------------- ---------------
ONLINE_CHANGE#
--------------
/oradb04/dbatest/users01.dbf
OFFLINE [b]391183133[/b]
10-MAR-04 02:23 391183133
10-MAR-04 02:23 391181210
[b]391181296[/b]
-- the ONLINE_CHANGE# shows the latest SCN when it was online(391181296 : which was the max(checkpoint_change#) ,just before we offlined it).
-- the OFFLINE_CHANGE# will get updated when the datafile is brought online
-- just do one more checkpoint for testing
14:25:16 SQL> alter system checkpoint;
System altered.
14:26:52 SQL> select name,status,CHECKPOINT_CHANGE#,to_char(CHECKPOINT_TIME,'DD-MON-YY HH:MI'),
LAST_CHANGE#,to_char(LAST_TIME,'DD-MON-YY HH:MI'),OFFLINE_CHANGE#,ONLINE_CHANGE#
from v$datafile where name='/oradb04/dbatest/users01.dbf';
14:26:59 2 14:26:59 3
NAME
----------------------------------------------------------------------------------------------------
STATUS CHECKPOINT_CHANGE#
------- ------------------
TO_CHAR(CHECKPOINT_TIME,'DD-MO LAST_CHANGE#
--------------------------------------------------------------------------- ------------
TO_CHAR(LAST_TIME,'DD-MON-YYH OFFLINE_CHANGE#
--------------------------------------------------------------------------- ---------------
ONLINE_CHANGE#
--------------
/oradb04/dbatest/users01.dbf
OFFLINE [b]391183133[/b]
10-MAR-04 02:23 391183133
10-MAR-04 02:23 391181210
[b]391181296[/b]
-- Nothing changes here. They remain frozen. But other datafiles get checkpointed.
Lets go back to Session 1, where delete is pending,
Session 1:
-------------
-- I am even committing the transaction and it goes through ( Rollback goes through , too )
14:20:59 SQL> commit;
Commit complete.
-- Commit,Rollback need not necessarily update the datafile. They will need to update rollback segments and online redo , however.
-- but the Select fails
14:35:00 SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '/oradb04/dbatest/users01.dbf'
-- Nor can I initial another DML,now
14:35:19 SQL> delete from t1;
delete from t1
*
ERROR at line 1:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '/oradb04/dbatest/users01.dbf'
-- this file will need recovery
14:26:59 SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR
---------- ------- ------- -----------------------------------------------------------------
CHANGE# TIME
---------- ---------
9 OFFLINE OFFLINE OFFLINE NORMAL
0
-- and recovery will be performed when you bring the datafile online
14:46:52 SQL> alter tablespace users online;
Tablespace altered.
14:47:03 SQL> select name,status,CHECKPOINT_CHANGE#,to_char(CHECKPOINT_TIME,'DD-MON-YY HH:MI'),
LAST_CHANGE#,to_char(LAST_TIME,'DD-MON-YY HH:MI'),OFFLINE_CHANGE#,ONLINE_CHANGE#
from v$datafile where name='/oradb04/dbatest/users01.dbf';
14:47:09 2 14:47:09 3
NAME
----------------------------------------------------------------------------------------------------
STATUS CHECKPOINT_CHANGE#
------- ------------------
TO_CHAR(CHECKPOINT_TIME,'DD-MO LAST_CHANGE#
--------------------------------------------------------------------------- ------------
TO_CHAR(LAST_TIME,'DD-MON-YYH OFFLINE_CHANGE#
--------------------------------------------------------------------------- ---------------
ONLINE_CHANGE#
--------------
/oradb04/dbatest/users01.dbf
ONLINE 391186679
10-MAR-04 02:47
[b]391183133[/b]
391186679
-- you see ,its checkpointed again and the OFFLINE_CHANGE# is updated with the SCN ,when the datafile went offline. Oracle knows what changes need to be applied , to bring the datafile online.
14:38:36 SQL> select count(*) from t1;
COUNT(*)
----------
0
-- now you see the committed data .
-Thiru
|
|
|
|
| Re: tablesapce offline [message #60863 is a reply to message #60857] |
Wed, 10 March 2004 18:37 |
vadlamani
Messages: 39
Registered: November 2003
|
Member |
|
|
Thiru,
"Oracle knows what changes need to be applied , to
bring the datafile online"
I want to know from where does the ORALCE applies changes, because the redo log files can be over written.
regards
satish
|
|
|
|
| Re: tablesapce offline [message #60864 is a reply to message #60857] |
Wed, 10 March 2004 18:43 |
vadlamani
Messages: 39
Registered: November 2003
|
Member |
|
|
Thiru,
"Oracle knows what changes need to be applied , to
bring the tablespace online"
I want to know from where does the ORALCE applies changes, because the redo log files can be over written when the TABLESPACE is OFFLINE, & when the datailfe is brought online from where will ORACLE know the changes.
regards
satish
|
|
|
|
| Re: tablesapce offline [message #60869 is a reply to message #60864] |
Thu, 11 March 2004 06:09 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
When you offline the tablespace,Oracle doesnt allow you to initiate another DML on any of the tables in that tablespace.However it allows for the transaction to be complete(ie commit or rollback) as we have seen. It stores the undo entries related to that active transaction in the deferred rollback segment in the SYSTEM tablespace when the tablespace goes offline and when the tablespace is brought online,it applies those entries to the data blocks ,completing the recovery.
-Thiru
|
|
|
|
Goto Forum:
Current Time: Tue Jan 07 23:23:48 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
