查看: 1316|回复: 0

[Oracle数据库] 关于表空间offline小知识点

发表于 2018-3-8 08:00:02
  1. 1、表空间offline的原因
  2. Taking Tablespaces Offline
  3. Taking a tablespace offline makes it unavailable for normal access.
  4. You may want to take a tablespace offline for any of the following reasons:
  5. To make a portion of the database unavailable while allowing normal access to the remainder of the database
  6. To perform an offline tablespace backup (even though a tablespace can be backed up while online and in use)
  7. To make an application and its group of tables temporarily unavailable while updating or maintaining the application
  8. To rename or relocate tablespace data files ##上次文档就是因为要重命名数据文件offline了表空间
复制代码
  1. 实验1
  2. SQL> archive log list;
  3. Database log mode Archive Mode
  4. Automatic archival Enabled
  5. Archive destination +DATADG/arch
  6. Oldest online log sequence 266
  7. Next log sequence to archive 268
  8. Current log sequence 268
  9. SQL>
  10. SQL> alter tablespace users offline ; ##默认使用normal参数
  11. Tablespace altered.
  12. SQL> alter system switch logfile;
  13. System altered.
  14. SQL> shutdown immediate;
  15. Database closed.
  16. Database dismounted.
  17. ORACLE instance shut down.
  18. SQL>
  19. SQL> startup
  20. ORACLE instance started.
  21. Total System Global Area 1653518336 bytes
  22. Fixed Size 2253784 bytes
  23. Variable Size 1006636072 bytes
  24. Database Buffers 637534208 bytes
  25. Redo Buffers 7094272 bytes
  26. Database mounted.
  27. Database opened.
  28. SQL>
  29. SQL> alter tablespace users online;
  30. Tablespace altered.
  31. SQL> select tablespace_name,status from dba_tablespaces;
  32. TABLESPACE_NAME STATUS
  33. ------------------------------ ---------
  34. SYSTEM ONLINE
  35. SYSAUX ONLINE
  36. UNDOTBS1 ONLINE
  37. TEMP ONLINE
  38. USERS ONLINE
  39. UNDOTBS2 ONLINE
  40. 6 rows selected.
  41. SQL>
复制代码
  1. 实验2
  2. SQL> archive log list;
  3. Database log mode Archive Mode
  4. Automatic archival Enabled
  5. Archive destination +DATADG/arch
  6. Oldest online log sequence 267
  7. Next log sequence to archive 269
  8. Current log sequence 269
  9. SQL> alter tablespace users offline immediate; ##参数immediate
  10. Tablespace altered.
  11. SQL> alter system switch logfile;
  12. System altered.
  13. SQL> shutdown immediate;
  14. Database closed.
  15. Database dismounted.
  16. ORACLE instance shut down.
  17. SQL> startup
  18. ORACLE instance started.
  19. Total System Global Area 1653518336 bytes
  20. Fixed Size 2253784 bytes
  21. Variable Size 1006636072 bytes
  22. Database Buffers 637534208 bytes
  23. Redo Buffers 7094272 bytes
  24. Database mounted.
  25. Database opened.
  26. SQL> alter tablespace users online;
  27. alter tablespace users online
  28. *
  29. ERROR at line 1:
  30. ORA-01113: file 4 needs media recovery
  31. ORA-01110: data file 4: '+DATADG/orcl/datafile/users.259.954803993'
  32. SQL> recover tablespace users;
  33. Media recovery complete.
  34. SQL> alter tablespace users online;
  35. Tablespace altered.
  36. SQL> select tablespace_name,status from dba_tablespaces;
  37. TABLESPACE_NAME STATUS
  38. ------------------------------ ---------
  39. SYSTEM ONLINE
  40. SYSAUX ONLINE
  41. UNDOTBS1 ONLINE
  42. TEMP ONLINE
  43. USERS ONLINE
  44. UNDOTBS2 ONLINE
  45. 6 rows selected.
  46. SQL>
复制代码

小结:
默认offline表空间执行一致性检查点,使用normal参数来offline表空间。
如果使用immediate参数offline表空间需要进行recover操作。大家可以看实验过程了解。



回复

使用道具 举报