测试redolog应用时用到的一些有用sql

       这是几年前测试一个基于redolog 来实现数据同步的一个应用时所用到的sql语句，在整理一下做个积累。

--check redo log state info
select  t.sid, n.name, t.value
from  v$mystat   t,   v$statname n
where   t.statistic# = n.statistic# and n.name like 'redo %';


select name,value from v$sysstat
where name in ('redo size','redo wastage','redo blocks written');


select * from v$log;
select * from v$logfile

--创建2个新的日志组
alter database add logfile group 4 ('D:\ORACLE\ORADATA\ORADB\REDO04_1.LOG') size 1024k;
alter database add logfile group 5 ('D:\ORACLE\ORADATA\ORADB\REDO05_1.LOG') size 1024k;

--切换当前日志到新的日志组
alter system switch logfile;
alter system switch logfile;

--删除旧的日志组
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

--操作系统下删除原日志组1、2、3中的文件

--重建日志组1、2、3
alter database add logfile group 1 ('/home/oracle/ORADATA/redotest/REDO01_1.LOG') size 500M;
alter database add logfile group 2 ('/home/oracle/ORADATA/redotest/REDO02_1.LOG') size 500M;
alter database add logfile group 3 ('/home/oracle/ORADATA/redotest/REDO03_1.LOG') size 500M;

--切换日志组
alter system switch logfile;

--删除中间过渡用的日志组4、5
alter database drop logfile group 4;
alter database drop logfile group 5;

--到操作系统下删除原日志组4、5中的文件

--备份当前的最新的控制文件
SQL> connect internal
SQL> alter database backup controlfile to trace resetlogs

--supplemental log
alter database drop supplemental log data
alter database add supplemental log data
alter database add supplemental log data (primary key ,unique index) columns
--select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui
--from v$database

--check log and redo log 
select * from v$log
select * from v$statname
select * from v$filestat
select * from dba_free_space 
select * from dba_tablespaces

ALTER TABLESPACE UNDOTBS1 offline;
ALTER TABLESPACE UNDOTBS1
add datafile '/data/oradata/logtest/undotbs02.dbf' size 500m autoextend on next 2M MAXSIZE 2048M
--look up  redo log size
select  t.sid, n.name, t.value
from  v$mystat   t,   v$statname n
where   t.statistic# = n.statistic# and n.name like 'redo %';