aix下oracle的空間使用

包括 系統表空間system,輔助表空間sysaux,兩個還原表空間undotbs1和undotbs2,三個臨時表空間temp,temp02,temp03,資料表空間test01,test02,test03,test04,test06和兩個使用者表空間users和example.

SQL>create tablespace test01 datafile '/db/oradata/orcl/test01.dbf' size 10M uniform size 64K;

SQL>create tablespace test02 datafile '/db/oradata/orcl/test02.dbf' size 10M autoallocate;

SQL>create tablespace test03 datafile '/db/oradata/orcl/test03.dbf' size 10M reuse autoextend on next 500K maxsize 100M;

SQL>alter system set db_create_file_dest = '/db/oradata/orcl';

SQL>create tablespace test04;

SQL>select tablespace_name,file_name from dba_data_files;

SQL>create bigfile tablespace test06 datafile '/db/oradata/orcl/test06.dbf'

size 20M autoextend on;

SQL>create undo tablespace undotbs2 datafile '/db/oradata/orcl/undotbs2.dbf'

size 10M autoextend on retention noguarantee;

SQL>create temporary tablespace temp02

tempfile '/db/oradata/orcl/temp02.dbf' size 10M autoextend on;

SQL>create temporary teblespace temp03

tempfile 'db/oradata/orcl/temp03.dbf' size 10M autoextend on

tablespace group tbs_group1;

SQL>alter tablespace temp02 tablespace group tbs_group1;

SQL>alter tablespace temp02 tablespace group '';

SQL>select * from dba_tablespace_groups;

SQL>select tablespace_name,contents,status from dba_tablespaces;

***********表空間擴容

SQL>alter tablespace users add datafile '/db/oradata/orcl/users02.dbf' size 50M;

SQL>alter database datafile '/db/oradata/orcl/users02.dbf' resize 100M;

SQL>alter database datafile '/db/oradata/orcl/users02.dbf' autoextend on next 50M maxsize 1000M;

#lslv lv1 --一個裸裝置的配置資訊

#extendlv -a ie -ex lv1 472 hdisk5 hdisk6 --為lv1擴充套件空間

*****************表空間維護

SQL>show user;

SQL>alter tablespace users offline;

SQL>select tablespace_name ,status from dba_tablespace where tablespace_name='USERS';

SQL>show user

SQL>alter tablespace users online;

SQL>select file_name,tablespace_name from dba_data_files

where tablespace_name='USERS';

SQL>alter database datafile '/db/oradata/orcl/users01.dbf' offline;

SQL>alter database datafile '/db/oradata/orcl/users01.dbf' online;

SQL>recover datafile '/db/oradata/orcl/users01.dbf';

SQL>alter database datafile '/db/oradata/orcl/users01.dbf' online;

SQL>alter tablespace USERS read only;

SQL>alter tablespace USERS read write;

SQL>alter tablespace USERS RENAME TO USERS01;

SQL>drop tablespace test01 including contents and datafiles;

SQL>drop tablespace temp01 including contents and datafiles;

SQL>drop tablespace undotbs2 including contents and datafiles;

**************還可以在OEM中 維護表空間

**********還原表空間管理

SQL>show parameter undo;

SQL>show parameter undo_tablespace;

SQL>create undo tablespace undotbs2 datafile '/db/oradata/orcl/undotbs02.dbf'

size 10M autoextend on;

SQL>alter system set undo_tablespace=undotbs2 scope=both;

SQL>drop tablespace undotbs1 uncluding contents and datafiles;

SQL>select tablespace_name from dba_tablespaces where contents='UNDO';

*********同樣可以在OEM中管理還原表空間

********重做日誌管理

牽涉到三方面:(1)日誌組和日誌成員的建立(2)日誌組和日誌成員的刪除(3)日誌切換和檢查點

增加日誌組前,需要考慮:

(1)日誌檔案的大小:10g,11g預設為50M,日誌大小與使用者業務系統的繁忙程式相關.日誌的大小最好能承載資料庫幾十分鐘的業務量.太小會造成日誌組切換頻繁;太大則導致系統恢復時間增長.

(2)日誌組數目的多少:在發生日誌切換時,一個可以使用的下一個日誌組很重要.日誌組少,可能會發生由於等待資料庫歸檔等操作的完成,需要使用的下一個日誌組不可用,造成效能故障.如果從告警檔案內讀到有checkpoint not complete等資訊,表明日誌組少。

SQL>col member format a30;

SQL>select a.group#,a.member,b.bytes/1024/1024 from v$logfile a,v$log b

where a.group# = b.group#; --顯示３組

SQL>alter database add logfile 'db/oradata/orcl/redo04.log' size 50M; --新增組

SQL>alter database add logfile group 8

'db/oradata/orcl/redo08.log' size 50M;--加組

SQL>alter database add logfile member

'/db/oradata/orcl/redo01b.log' to group 1; --新增成員

SQL>alter database add logfile member

'/db/oradata/orcl/redo02b.log' to group 2; --新增成員

SQL>alter database add logfile member

'/db/oradata/orcl/redo03b.log' to group 3; --新增成員

SQL>alter database add logfile member

'/db/oradata/orcl/redo04b.log' to group 4; --新增成員

SQL>alter database add logfile member

'/db/oradata/orcl/redo08b.log' to group 8; --新增成員

SQL> alter system set db_create_file_dest = 'db/oradata/orcl/';

SQL>alter database add logfile group 9;

**********日誌組和組成員的刪除

SQL> alter database drop logfile member '/db/oradata/orcl/redo08b.log';

注意:如果要刪除的日誌成員是當前日誌組的日誌成員,則可以先進行一次日誌切換,然後刪除.如果這個日誌成員是組裡的最後一個成員,則不能執行刪除操作.

SQL>alter database drop logfile group 8;

SQL>alter database clear logfile group 3; --重構日誌組3的所有成員

*********控制檔案管理

SQL>select name from v$controlfile; --顯示3個

SQL>alter system set control_files='/db/oradata/orcl/control01.ctl','db/oradata/orcl/control02.ctl','db/oradata/orcl

control03.ctl','db/oradata/orcl/control04.ctl' scope=spfile;

SQL>shutdown immediate;

SQL>host cp /db/oradata/orcl/control01.ctl /db/oradata/orcl/control04.ctl;

SQL>startup;

SQL>select name from v$controlfile; --顯示4個

$ rm /db/oradata/orcl/control04.ctl;

報錯

SQL>host cp /db/oradata/orcl/control01.ctl /db/oradata/orcl/control04.ctl;

SQL>startup

成功啟動

[@more@]