【Oracle 】实验2 Oracle数据库物理存储结构管理

-- 1
alter tablespace users add datafile 'E:\Oracle\Oracle11gapp\admin\booksales\users02.dbf' size 10m;
-- 2
alter tablespace temp add tempfile 'E:\Oracle\Oracle11gapp\admin\booksales\temp02.dbf' size 10m;
-- 3
alter tablespace users add datafile 'E:\Oracle\Oracle11gapp\admin\booksales\users03.dbf' size 5m autoextend on next 1m maxsize 100m;
-- 4
alter  database datafile 'E:\Oracle\Oracle11gapp\admin\booksales\users02.dbf' autoextend off;
-- 5  修改数据文件的名称或位置时，需要先脱机处理
alter tablespace users offline;
alter tablespace users rename datafile 'E:\Oracle\Oracle11gapp\admin\booksales\users02.dbf' to 'E:\Oracle\Oracle11gapp\admin\booksales\users002.dbf';
alter tablespace users online;
-- 6 
select tablespace_name,file_id,bytes/1024/1024 filesize,file_name from dba_data_files order by file_id;
-- 7
select name from v$controlfile;
alter system set control_files = 
'E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\CONTROL01.CTL',
'E:\ORACLE\ORACLE11GAPP\FLASH_RECOVERY_AREA\BOOKSALES\CONTROL02.CTL',
'E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\CONTROL03.CTL' scope=spfile;
shutdown immediate;
host copy E:\Oracle\Oracle11gapp\admin\booksales\control01.CTL E:\Oracle\Oracle11gapp\admin\booksales\control03.CTL
startup
select name from v$controlfile;
-- 8 
alter database backup controlfile to 'E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\control.bkp';
-- 9
alter database backup controlfile to trace;
select value from v$diag_info where name='Default Trace File'; 
-- 10
alter system set control_files = 
'E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\CONTROL01.CTL',
'E:\ORACLE\ORACLE11GAPP\FLASH_RECOVERY_AREA\BOOKSALES\CONTROL02.CTL';
shutdown immediate;
del E:\Oracle\Oracle11gapp\admin\booksales\control03.CTL 
startup
select name from v$controlfile;
-- 11
select * from v$controlfile;
-- 12
alter database add logfile group 4 
('E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\undo04a.log') size 10m;
-- 13
alter database add logfile member 
'E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\undo04b.log' to group 4;
-- 14
select group#,status from v$log;
host move E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\UNDO04A.LOG  E:\ORACLE\ORACLE11GAPP\ORADATA\UNDO04A.LOG;
host move E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\UNDO04B.LOG  E:\ORACLE\ORACLE11GAPP\ORADATA\UNDO04B.LOG;
alter tablespace users offline; 
alter database rename file 'E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\UNDO04A.LOG','E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\UNDO04B.LOG'
 to 'E:\ORACLE\ORACLE11GAPP\ORADATA\UNDO04A.LOG','E:\ORACLE\ORACLE11GAPP\ORADATA\UNDO04B.LOG';
-- 15
select * from v$log;
-- 16
select group#,status,member  from v$logfile;
-- 17
alter database drop logfile member 'E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\UNDO04B.LOG';
host del E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\UNDO04B.LOG;
-- 18
alter database drop logfile group 4;
host del E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\UNDO04A.LOG;
host del E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\UNDO04B.LOG;
-- 19
select name,log_mode from v$database;
-- 20
shutdown immediate
alter system set log_archive_dest = 'E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\archive';
startup mount
alter database archivelog;
alter database open;
-- 21 
shutdown immediate
alter system set log_archive_dest_n = 3
alter system set log_archive_dest_1 = 'location=E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\archive';
alter system set log_archive_dest_2 = 'location=E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\archive';
alter system set log_archive_dest_3 = 'location=E:\ORACLE\ORACLE11GAPP\ORADATA\BOOKSALES\archive mandatory';
startup mount
alter database archivelog;
alter database open;
-- 22
alter system switch logfile;
select * from v$logfile