Oracle: 10.2.0.5 (PSU 3)
What will happen after putting a tablespace in a backup mode ?
Some of the Oracle DBA believes in a myth that a datafile is "frozen" and there are no writes to the datafiles.
This is a bullshit and I will shatter this myth.
To perform a test I will create a tablespace TEST and put in a backup mode.
16:31:44 SQL> create tablespace TEST datafile '/u01/app/oracle/oradata/ORCL2/test01.dbf' size 10M; Tablespace created. 16:32:00 SQL> alter tablespace TEST begin backup; Tablespace altered. |
I am checking in a alert log if the tablespace was put in a backup mode.
Sun May 22 16:31:51 CEST 2011 create tablespace TEST datafile '/u01/app/oracle/oradata/ORCL2/test01.dbf' size 10M Sun May 22 16:31:51 CEST 2011 Starting control autobackup Control autobackup written to DISK device handle '/u01/app/oracle/ORCL2/autobackup/2011_05_22/o1_mf_s_751825911_6xl7lqy9_.bkp' Completed: create tablespace TEST datafile '/u01/app/oracle/oradata/ORCL2/test01.dbf' size 10M Sun May 22 16:32:10 CEST 2011 alter tablespace TEST begin backup Sun May 22 16:32:10 CEST 2011 Completed: alter tablespace TEST begin backup Sun May 22 16:34:00 CEST 2011 |
16:32:05 SQL> select file_id, tablespace_name, file_name from dba_data_files where tablespace_name='TEST'; FILE_ID TABLESPACE_NAME FILE_NAME ------- --------------- ---------------------------------------- 2 TEST /u01/app/oracle/oradata/ORCL2/test01.dbf 16:32:10 SQL> select a.FILE#, a.STATUS, a.CHANGE#, a.TIME from v$backup a, v$datafile b where a.FILE#=b.FILE# and a.status='ACTIVE'; FILE# STATUS CHANGE# TIME ----- ------- --------- --------------------- 2 ACTIVE 13309240 2011-05-22 16:32:10 |
After putting the tablespace TEST in the backup mode I will create a table.
16:32:22 SQL> create table BARTEK (string varchar2(20)) tablespace TEST; Table created. 16:32:30 SQL> select object_id from dba_objects where object_name='BARTEK'; OBJECT_ID ---------- 57788 16:32:44 SQL> select FILE#,BLOCK#,DIRTY from v$bh where OBJD=57788; FILE# BLOCK# D ---------- ---------- - 2 11 Y 2 9 Y 2 10 Y |
The table BARTEK is in the database buffer cache but the blocks are dirty.
I will make them not dirty by forcing a checkpoint.
16:32:57 SQL> alter system checkpoint; System altered. 16:34:00 SQL> select FILE#,BLOCK#,DIRTY from v$bh where OBJD=57788; FILE# BLOCK# D ---------- ---------- - 2 11 N 2 9 N 2 10 N |
Now I am inserting one row into the table BARTEK.
16:34:06 SQL> insert into BARTEK values ('Hello World !'); 1 row created. 16:34:24 SQL> select dbms_rowid.rowid_relative_fno(rowid) file_num, dbms_rowid.rowid_block_number(rowid) block_num, string from BARTEK ; FILE_NUM BLOCK_NUM STRING ---------- ---------- -------------------- 2 15 Hello World ! 16:34:43 SQL> select FILE#,BLOCK#,DIRTY from v$bh where OBJD=57788 and BLOCK#=15; FILE# BLOCK# D ---------- ---------- - 2 15 Y 16:35:09 SQL> commit; Commit complete. 16:35:20 SQL> select FILE#,BLOCK#,DIRTY from v$bh where OBJD=57788 and BLOCK#=15; FILE# BLOCK# D ---------- ---------- - 2 15 Y |
Even I inserted one row into the table BARTEK and committed but the block no 15 is still dirty in the database buffer cache.
So it was not written into the datafile.
osb3@ORCL2(16:36)[/u01/app/oracle/oradata/ORCL2]$ dd if=test01.dbf bs=8192 skip=15 count=1 | strings | grep 'Hello World' 1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied, 2.3467e-05 seconds, 349 MB/s |
I will make the checkpoint to write a data to the datafiles.
16:37:41 SQL> alter system checkpoint; System altered. 16:38:08 SQL> select FILE#,BLOCK#,DIRTY from v$bh where OBJD=57788 and BLOCK#=15; FILE# BLOCK# D ---------- ------ - 2 15 N |
And the data were written to the datafile.
osb3@ORCL2(16:37)[/u01/app/oracle/oradata/ORCL2]$ dd if=test01.dbf bs=8192 skip=15 count=1 | strings | grep 'Hello World' 1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied, 2.3187e-05 seconds, 353 MB/s Hello World ! |
Finally I can end the backup mode.
16:40:40 SQL> alter tablespace TEST end backup; Tablespace altered. 16:41:15 SQL> select a.FILE#, b.name, a.STATUS, a.CHANGE#, a.TIME from v$backup a, v$datafile b where a.FILE#=b.FILE# and a.status='ACTIVE'; no rows selected 16:41:22 SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- ------------------- 1 NOT ACTIVE 0 2 NOT ACTIVE 13309240 2011-05-22 16:32:10 3 NOT ACTIVE 0 4 NOT ACTIVE 0 5 NOT ACTIVE 0 6 NOT ACTIVE 0 10 NOT ACTIVE 0 7 rows selected. 16:43:14 SQL> select FILE#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME from v$datafile; FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME ----- ------------------ ------------------- 1 13309523 2011-05-22 16:40:36 2 13309523 2011-05-22 16:40:36 3 13309523 2011-05-22 16:40:36 4 13309523 2011-05-22 16:40:36 5 13309523 2011-05-22 16:40:36 6 13309523 2011-05-22 16:40:36 10 13309523 2011-05-22 16:40:36 7 rows selected. |
Sun May 22 16:41:15 CEST 2011 alter tablespace test end backup Sun May 22 16:41:15 CEST 2011 Completed: alter tablespace test end backup Sun May 22 16:44:36 CEST 2011 |
My test was based on the very good article under the website
Brak komentarzy:
Prześlij komentarz