[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.
--总之，只要概念清晰，上面的操作很好理解。
