[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;
-- 不需要恢複。
SYS@book> alter tablespace tea offline immediate;
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.
3.資料檔案offline:
SYS@book> alter database datafile 7 offline;
Database altered.
SYS@book> alter database datafile 7 online;
alter database datafile 7 online
--資料檔案offline，要online必須恢複。也就是offline不會發檔案檢查點。
--我的測試庫是設定在歸檔模式的,加不加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;
4.資料檔案offline,再online可以不需要恢複嗎?
--隻要先表空間offline,或者read only
--注意這個時候不需要恢複，雖然資料檔案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
*
ORA-00376: file 7 cannot be read at this time
SYS@book> select tablespace_name,status from dba_tablespaces where tablespace_name in ('TEA','SYSTEM');
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
TEA OFFLINE
TEA ONLINE
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------ ---------- --------- ------------ ------------------- ------------ ------------ ------------
7369 YYYY CLERK 7902 1980-12-17 00:00:00 800 20
--還有1種情況不需要恢複(不過這個是假的),就是在mount模式下online,等open時才需要恢複.實際上還是要恢複。
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.
--可以發現在mount狀态下online資料檔案不需要恢複。
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ---------------
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
SYS@book> alter database open;
alter database open
--在open時提示要恢複資料檔案7.
5.測試表空間read only的情況：
SYS@book> alter tablespace tea read only ;
SYS@book> update scott.empx set ename ='aaaa' where empno=7369;
update scott.empx set ename ='aaaa' where empno=7369
*
ORA-00372: file 7 cannot be modified at this time
SYS@book> alter tablespace tea read write ;
--總之，隻要概念清晰，上面的操作很好了解。
![linux-svn解除安裝與安裝[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)