[20160329]表空間與資料檔案.txt

lfree發表於2016-03-29

[20160329]表空間與資料檔案.txt

--昨天跟別人聊天,提到招聘DBA,一些dba這些基本的概念不清楚.
--表空間可以是一個邏輯的概念,包含多個資料檔案.而一個資料檔案僅僅屬於一個表空間.

--表空間offline,一般不需要recover 恢復.除非加入immediate 引數.
--而資料檔案offline,一定需要恢復,才能online.如果是非歸檔模式必須在後面加入drop引數(自己曾經對於這存在混亂).
--不要誤解後面這個drop不是刪除的意思,我以前理解就存在錯誤.
--而是表示可能無法恢復.

--表空間可以設定為read only,資料檔案不行.

--透過例子說明其中的細節:

1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE TABLESPACE TEA DATAFILE
  '/mnt/ramdisk/book/tea01.dbf' AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

2.表空間offline:
SYS@book> alter tablespace tea offline;
Tablespace altered.

SYS@book> alter tablespace tea online;
Tablespace altered.
-- 不需要恢復。

SYS@book> alter tablespace tea offline immediate;
Tablespace altered.

SYS@book> alter tablespace tea online;
alter tablespace tea online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'

-- 可以發現加入引數immediate,不會發檔案檢查點,需要恢復。
SYS@book> recover datafile 7;
Media recovery complete.

SYS@book> alter tablespace tea online;
Tablespace altered.

3.資料檔案offline:
SYS@book> alter database datafile 7 offline;
Database altered.

SYS@book> alter database datafile 7 online;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'
--資料檔案offline,要online必須恢復。也就是offline不會發檔案檢查點。

SYS@book> recover datafile 7;
Media recovery complete.

SYS@book> alter database datafile 7 online;
Database altered.

--我的測試庫是設定在歸檔模式的,加不加drop一樣.
SYS@book> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archivelog/book
Oldest online log sequence     16
Next log sequence to archive   18
Current log sequence           18

SYS@book> alter database datafile 7 offline drop;
Database altered.

SYS@book> alter database datafile 7 online;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'


SYS@book> recover datafile 7;
Media recovery complete.

SYS@book> alter database datafile 7 online;
Database altered.

4.資料檔案offline,再online可以不需要恢復嗎?

--只要先表空間offline,或者read only

SYS@book> alter tablespace tea offline;
Tablespace altered.

SYS@book> alter database datafile 7 online;
Database altered.

--注意這個時候不需要恢復,雖然資料檔案online了,但是表空間並沒有online。

SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                             TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ----------------
    1        13227498882 2016-03-29 15:19:56                7       13227286650 ONLINE              1003 YES /mnt/ramdisk/book/system01.dbf   SYSTEM
    2        13227498882 2016-03-29 15:19:56             1834       13227286650 ONLINE               999 YES /mnt/ramdisk/book/sysaux01.dbf   SYSAUX
    3        13227498882 2016-03-29 15:19:56           923328       13227286650 ONLINE               919 YES /mnt/ramdisk/book/undotbs01.dbf  UNDOTBS1
    4        13227498882 2016-03-29 15:19:56            16143       13227286650 ONLINE              1003 YES /mnt/ramdisk/book/users01.dbf    USERS
    5        13227498882 2016-03-29 15:19:56           952916       13227286650 ONLINE               916 YES /mnt/ramdisk/book/example01.dbf  EXAMPLE
    6        13227498882 2016-03-29 15:19:56          1314508       13227286650 ONLINE               932 YES /mnt/ramdisk/book/sugar01.dbf    SUGAR
    7        13227499647 2016-03-29 15:30:21      13227207527       13227286650 ONLINE                41 YES /mnt/ramdisk/book/tea01.dbf      TEA
7 rows selected.

SYS@book> select * from scott.empx where rownum<=1;
select * from scott.empx where rownum<=1
                    *
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'

SYS@book> select tablespace_name,status from dba_tablespaces where tablespace_name in ('TEA','SYSTEM');
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
TEA                            OFFLINE

SYS@book> alter tablespace tea online;
Tablespace altered.

SYS@book> select tablespace_name,status from dba_tablespaces where tablespace_name in ('TEA','SYSTEM');
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
TEA                            ONLINE

SYS@book> alter tablespace tea online;
Tablespace altered.

SYS@book> select * from scott.empx where rownum<=1;
       EMPNO ENAME      JOB                MGR HIREDATE                     SAL         COMM       DEPTNO
------------ ---------- --------- ------------ ------------------- ------------ ------------ ------------
        7369 YYYY       CLERK             7902 1980-12-17 00:00:00          800                        20

--還有1種情況不需要恢復(不過這個是假的),就是在mount模式下online,等open時才需要恢復.實際上還是要恢復。

SYS@book> alter database datafile 7 offline;
Database altered.

SYS@book> alter database datafile 7 online;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'

shutdown immediate

SYS@book> startup mount
ORACLE instance started.
Total System Global Area    634732544 bytes
Fixed Size                    2255792 bytes
Variable Size               197133392 bytes
Database Buffers            427819008 bytes
Redo Buffers                  7524352 bytes
Database mounted.

SYS@book> alter database datafile 7 online;
Database altered.
--可以發現在mount狀態下online資料檔案不需要恢復。

SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                             TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ---------------
    1        13227500957 2016-03-29 15:35:56                7       13227286650 ONLINE              1006 NO  /mnt/ramdisk/book/system01.dbf   SYSTEM
    2        13227500957 2016-03-29 15:35:56             1834       13227286650 ONLINE              1002 NO  /mnt/ramdisk/book/sysaux01.dbf   SYSAUX
    3        13227500957 2016-03-29 15:35:56           923328       13227286650 ONLINE               922 NO  /mnt/ramdisk/book/undotbs01.dbf  UNDOTBS1
    4        13227500957 2016-03-29 15:35:56            16143       13227286650 ONLINE              1006 NO  /mnt/ramdisk/book/users01.dbf    USERS
    5        13227500957 2016-03-29 15:35:56           952916       13227286650 ONLINE               919 NO  /mnt/ramdisk/book/example01.dbf  EXAMPLE
    6        13227500957 2016-03-29 15:35:56          1314508       13227286650 ONLINE               935 NO  /mnt/ramdisk/book/sugar01.dbf    SUGAR
    7        13227500662 2016-03-29 15:35:04      13227207527       13227286650 ONLINE                44 YES /mnt/ramdisk/book/tea01.dbf      TEA
7 rows selected.

SYS@book> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'

--在open時提示要恢復資料檔案7.

SYS@book> alter database datafile 7 offline;
Database altered.

SYS@book> alter database open;
Database altered.

SYS@book> recover datafile 7;
Media recovery complete.

SYS@book> alter database datafile 7 online;
Database altered.

SYS@book> select * from scott.empx where rownum<=1;
       EMPNO ENAME      JOB                MGR HIREDATE                     SAL         COMM       DEPTNO
------------ ---------- --------- ------------ ------------------- ------------ ------------ ------------
        7369 YYYY       CLERK             7902 1980-12-17 00:00:00          800                        20

5.測試表空間read only的情況:

SYS@book> alter tablespace tea read only ;
Tablespace altered.

SYS@book> alter database datafile 7 offline;
Database altered.

SYS@book> alter database datafile 7 online;
Database altered.

SYS@book> update scott.empx set ename ='aaaa' where empno=7369;
update scott.empx set ename ='aaaa' where empno=7369
             *
ERROR at line 1:
ORA-00372: file 7 cannot be modified at this time
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'

SYS@book> alter tablespace tea read write ;
Tablespace altered.

--總之,只要概念清晰,上面的操作很好理解。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2071755/,如需轉載,請註明出處,否則將追究法律責任。

相關文章