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

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

--check redo log state infoselect  t.sid, n.name, t.valuefrom  v$mystat   t,   v$statname nwhere   t.statistic# = n.statistic# and n.name like 'redo %';select name,value from v$sysstatwhere 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、3alter 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、5alter database drop logfile group 4;alter database drop logfile group 5;--到操作系统下删除原日志组4、5中的文件--备份当前的最新的控制文件SQL> connect internalSQL> alter database backup controlfile to trace resetlogs--supplemental logalter database drop supplemental log dataalter database add supplemental log dataalter 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$logselect * from v$statnameselect * from v$filestatselect * from dba_free_space select * from dba_tablespacesALTER TABLESPACE UNDOTBS1 offline;ALTER TABLESPACE UNDOTBS1add datafile '/data/oradata/logtest/undotbs02.dbf' size 500m autoextend on next 2M MAXSIZE 2048M--look up  redo log sizeselect  t.sid, n.name, t.valuefrom  v$mystat   t,   v$statname nwhere   t.statistic# = n.statistic# and n.name like 'redo %';