-- 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
【Oracle 】实验2 Oracle数据库物理存储结构管理
最新推荐文章于 2023-03-09 18:06:48 发布