I 添加控制文件上面咱们有写到：控制文件最好放在3个不同的磁盘上，最少也要保证2个 1 查看控制文件属性SQL> show parameter control; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /home/oracle/cs/control01.ctl control_management_pack_access string DIAGNOSTIC+TUNING 2 添加02.ctl，就是修改参数值SQL> alter system set control_files='/home/oracle/cs/control01.ctl','/ooradata/test1/control02.dbf' scope=spfile; System altered. 3 关机，底层复制SQL> shutdown immediate[oracle@jcy1 test1]$ cp /home/oracle/cs/control01.ctl /ooradata/test1/control02.ctl--一定要对应control_files参数的路径 4 开机验证SQL> startupSQL> select name from v$controlfile; NAME ------------------------------ /home/oracle/cs/control01.ctl /ooradata/test1/control02.ctl第三个控制文件，同理 5 删除控制文件像之前，我在参数文件里，需要生成pfile，删掉了*.control_files=，再生成回来spfile，比较麻烦，现在我可以像添加SQL> alter system set control_files='/home/oracle/cs/control01.ctl' scope=spfile; System altered.只指定一个01.ctl，02.ctl就被删除了关机SQL> shutdown immediate开机验证SQL> startupSQL> select name from v$controlfile; NAME ------------------------------ /home/oracle/cs/control01.ctl II 添加日志文件 1 查看日志文件SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ---------------------------------------- --- 3 ONLINE /home/oracle/cs/redo03.log NO 2 ONLINE /home/oracle/cs/redo02.log NO 1 ONLINE /home/oracle/cs/redo01.log NO 查看日志更详细信息SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 1 52428800 512 1 NO CURRENT 933509 02-FEB-20 2.8147E+14 2 1 0 52428800 512 1 YES UNUSED 0 0 3 1 0 52428800 512 1 YES UNUSED 0 0 2 添加日志组（日志有组和成员的概念） SQL> alter database add logfile group 4 '/home/oracle/cs/redo04.log' size 100m; Database altered.--添加日志组4，并且添加一个04成员给组4添加成员05SQL> alter database add logfile member '/home/oracle/cs/redo05.log' to group 4; Database altered.验证SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ---------------------------------------- --- 3 ONLINE /home/oracle/cs/redo03.log NO 2 ONLINE /home/oracle/cs/redo02.log NO 1 ONLINE /home/oracle/cs/redo01.log NO 4 ONLINE /home/oracle/cs/redo04.log NO 4 INVALID ONLINE /home/oracle/cs/redo05.log NO 3 删除日志组SQL> alter database drop logfile group 4; Database altered.验证SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ---------------------------------------- --- 3 ONLINE /home/oracle/cs/redo03.log NO 2 ONLINE /home/oracle/cs/redo02.log NO 1 ONLINE /home/oracle/cs/redo01.log NO 注：如果你还想添加日志组5，组成员04.log，会提示错误，因为底层的04成员并没有删除[oracle@jcy1 test1]$ cd /home/oracle/cs[oracle@jcy1 cs]$ ls -l redo04.log -rw-r----- 1 oracle dba 104858112 Feb 2 11:01 redo04.log所以要删除底层04[oracle@jcy1 cs]$ rm -rf redo04.log 4 继续删除日志组4（我后来又添加了组4）要保证至少两组日志的存在SQL> alter database drop logfile group 4; alter database drop logfile group 4 * ERROR at line 1: ORA-01623: log 4 is current log for instance test1 (thread 1) - cannot drop ORA-00312: online log 4 thread 1: '/home/oracle/cs/redo04.log' --提示正在使用，于是查看日志状态SQL> select GROUP#,SEQUENCE#,STATUS from v$log; GROUP# SEQUENCE# STATUS ---------- ---------- ---------------- 1 1 ACTVE 2 2 INACTIVE 3 3 INACTIVE 4 4 CURRENT解决方法1：切归档
SQL> alter system archive log current;检查状态，再删除，如果还是不能删除，就再切归档，直到处于INACTIVE，方法2：创建检查点
SQL>alter system checkpoint;
SQL> alter system switch logfile;检查状态，再删除 III 添加数据文件 1 查看日志文件SQL> select name from v$datafile; NAME ------------------------------ /ooradata/test1/system01.dbf /ooradata/test1/sysaux01.dbf /ooradata/test1/undotbs01.dbf /ooradata/test1/users01.dbf查看表空间SQL> select name from v$tablespace; NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 USERS TEMP
查询数据文件字典
SQL>select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024 M from dba_data_files;查询表空间字典
SQL> desc dba_tablespaces
2 给SYSTEM表空间，添加一个system02.dbf文件
SQL> alter tablespace SYSTEM add datafile '/ooradata/test1/system02.dbf' size 200m; Tablespace altered.
验证
SQL> select name from v$datafile; NAME ------------------------------ /ooradata/test1/system01.dbf /ooradata/test1/sysaux01.dbf /ooradata/test1/undotbs01.dbf /ooradata/test1/users01.dbf /ooradata/test1/ system02.dbf 3 求SYSTEM表空间的总和大小SQL> select TABLESPACE_NAME,sum(BYTES/1024/1024)m from dba_data_files group by TABLESPACE_NAME; TABLESPACE_NAME M ------------------------------ ---------- UNDOTBS1 30 SYSAUX 490 USERS 5 SYSTEM 940如果表空间太多，可以加过滤条件having TABLESPACE_NAME='SYSTEM';4 修改数据文件大小i）先查看大小SQL> select FILE_NAME,BYTES/1024/1024 m from dba_data_files; FILE_NAME M --------------------------------------------- ---------- /ooradata/test1/users01.dbf 5 /ooradata/test1/undotbs01.dbf 30 /ooradata/test1/sysaux01.dbf 490 /ooradata/test1/system01.dbf 740 /ooradata/test1/system02.dbf 200ii）修改大小SQL> alter database datafile '/ooradata/test1/system02.dbf' resize 220m; Database altered.iii）验证SQL> select FILE_NAME,BYTES/1024/1024 m from dba_data_files; FILE_NAME M --------------------------------------------- ---------- /ooradata/test1/users01.dbf 5 /ooradata/test1/undotbs01.dbf 30 /ooradata/test1/sysaux01.dbf 490 /ooradata/test1/system01.dbf 740 /ooradata/test1/system02.dbf 2205 查看表空间使用多少思路：总共的-空闲的=使用的
SQL>desc dba_free_space
select z-c, a.TABLESPACE_NAME from --相减，就得到使用
--查询总的大小
(select sum(BYTES/1024/1024) z, TABLESPACE_NAME from dba_data_files group by TABLESPACE_NAME)a,
--查询剩余的大小
(select sum(BYTES/1024/1024) c, TABLESPACE_NAME from dba_free_space group by TABLESPACE_NAME)b
where a. TABLESPACE_NAME=b. TABLESPACE_NAME;
