oracle redolog杂记

 

 

  

select utl_raw.CAST_FROM_BINARY_DOUBLE(-1.385) from dual;

'4009d70a3d70a3d6'

SELECT dump(colc, 16) FROM tzm.NEWTABLE;

SELECT utl_raw.CAST_TO_BINARY_DOUBLE('4009d70a3d70a3d6',0) FROM dual

 

  

/**

 * 更改redolog

 */

  select name,log_mode from v$database;

SELECT * FROM v$archived_log ORDER BY SEQUENCE# desc;

SELECT * FROM v$log ;

SELECT * FROM v$logfile ;

SELECT * FROM v$log_history 

select * from v$log　a　where a.THREAD# = 1 ;

select b.SEQUENCE#, b.FIRST_TIME,a.SEQUENCE#,a.FIRST_TIME,round(((a.FIRST_TIME-b.FIRST_TIME)*24)*60,2) from v$log_history a, v$log_history b where a.SEQUENCE#=b.SEQUENCE#+1 and b.THREAD#=1 order by a.SEQUENCE# desc;

select sequence#,first_time,nexttime,round(((first_time-nexttime)*24)*60,2) diff from (select sequence#,first_time,lag(first_time) over(order by sequence#) nexttime from v$log_history where thread#=1) order by sequence# desc;

 

select * from V$FLASH_RECOVERY_AREA_USAGE;

 

SELECT A_NUM,B_VARCHAR,AVG(1) FROM tzm.t1 GROUP BY A_NUM,B_VARCHAR;

ALTER DATABASE ADD logfile GROUP 12 '/opt/oracle/oradata/orcl/redo12.log' SIZE 5120m ;

ALTER DATABASE ADD logfile GROUP 13 '/opt/oracle/oradata/orcl/redo13.log' SIZE 5120m ;

ALTER DATABASE ADD logfile GROUP 14 '/opt/oracle/oradata/orcl/redo14.log' SIZE 5120m ;

ALTER DATABASE ADD logfile GROUP 15 '/opt/oracle/oradata/orcl/redo15.log' SIZE 5120m ;

ALTER DATABASE ADD logfile GROUP 16 '/opt/oracle/oradata/orcl/redo16.log' SIZE 5120m ;

ALTER DATABASE ADD logfile GROUP 17 '/opt/oracle/oradata/orcl/redo17.log' SIZE 5120m ;

ALTER DATABASE ADD logfile GROUP 18 '/opt/oracle/oradata/orcl/redo18.log' SIZE 5120m ;

ALTER DATABASE ADD logfile GROUP 19 '/opt/oracle/oradata/orcl/redo19.log' SIZE 5120m ;

 ALTER DATABASE ADD logfile GROUP 20 '/opt/oracle/oradata/orcl/redo20.log' SIZE 5120m ;

 

 

ALTER database DROP logfile group 8;

ALTER database DROP logfile group 9;

ALTER database DROP logfile group 10;

ALTER database DROP logfile group 11;

 

ALTER system switch logfile ;

alter system checkpoint;

 

alter system archive log SEQUENCE 4979;

  

  

  /*

   * redologfile，sequence，scn关系

   */

SELECT * FROM (

  select 'onlinelog',l.GROUP#,sequence#,FIRST_CHANGE# as startscn ,NEXT_CHANGE# as endscn,l.STATUS,

  to_char(FIRST_TIME,'yyyy/mm/dd:hh24:mi:ss') firsttime,

  to_char(NEXT_TIME,'yyyy/mm/dd:hh24:mi:ss') nexttime,MEMBER

  from V$log l, v$logfile lf WHERE l.GROUP#=lf.GROUP#  

  UNION ALL

   select  'archivelog',recid,SEQUENCE#,FIRST_CHANGE# as startscn ,NEXT_CHANGE# AS endscn,STATUS,

  to_char(FIRST_TIME,'yyyy/mm/dd:hh24:mi:ss') firsttime,

  to_char(NEXT_TIME,'yyyy/mm/dd:hh24:mi:ss') nexttime, name

  from v$archived_log WHERE name IS NOT NULL

) WHERE STARTscn  <= 622116275 AND 622116275 <= endscn

 

 

  /*

   * redologfile，sequence，scn关系

   */

SELECT * FROM (

  select 'onlinelog',l.GROUP#,sequence#,FIRST_CHANGE# as startscn ,NEXT_CHANGE# as endscn,l.STATUS,

  to_char(FIRST_TIME,'yyyy/mm/dd:hh24:mi:ss') firsttime,

  to_char(NEXT_TIME,'yyyy/mm/dd:hh24:mi:ss') nexttime,MEMBER

  from V$log l, v$logfile lf WHERE l.GROUP#=lf.GROUP#  

  UNION ALL

   select  'archivelog',recid,SEQUENCE#,FIRST_CHANGE# as startscn ,NEXT_CHANGE# AS endscn,STATUS,

  to_char(FIRST_TIME,'yyyy/mm/dd:hh24:mi:ss') firsttime,

  to_char(NEXT_TIME,'yyyy/mm/dd:hh24:mi:ss') nexttime, name

  from v$archived_log WHERE name IS NOT NULL

) WHERE startscn <> 0 AND endscn <> 0

 

SELECT min(startscn), max(startscn), min(endscn), max(endscn) FROM (

  select 'onlinelog',l.GROUP#,sequence#,FIRST_CHANGE# as startscn ,NEXT_CHANGE# as endscn,l.STATUS,

  to_char(FIRST_TIME,'yyyy/mm/dd:hh24:mi:ss') firsttime,

  to_char(NEXT_TIME,'yyyy/mm/dd:hh24:mi:ss') nexttime,MEMBER

  from V$log l, v$logfile lf WHERE l.GROUP#=lf.GROUP#  

  UNION ALL

   select  'archivelog',recid,SEQUENCE#,FIRST_CHANGE# as startscn ,NEXT_CHANGE# AS endscn,STATUS,

  to_char(FIRST_TIME,'yyyy/mm/dd:hh24:mi:ss') firsttime,

  to_char(NEXT_TIME,'yyyy/mm/dd:hh24:mi:ss') nexttime, name

  from v$archived_log WHERE name IS NOT NULL

) WHERE startscn <> 0 AND endscn <> 0

  

  SELECT * FROM V$log ;

  select * from v$logfile;

  SELECT * FROM v$archived_log;

  

  select value from v$diag_info where name ='Diag Trace';

  select value from v$diag_info where name ='Diag Alert';

  

  select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;

 

  

  SELECT UTL_RAW.CAST_TO_NUMBER('c20202'), 

  UTL_RAW.CAST_TO_VARCHAR2('ccecbdf2c4cfb4f3cda8d3c3cafdbeddbcbccaf5b9c9b7ddd3d0cfdeb9abcbbe7635'), 

  UTL_RAW.CAST_TO_VARCHAR2('ccecbdf2c4cfb4f3cda8d3c3cafdbeddbcbccaf5b9c9b7ddd3d0cfdeb9abcbbe763620202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020'), 

  UTL_RAW.CAST_TO_NVARCHAR2('59296d2553575927901a75286570636e6280672f80a14efd67099650516c53f80076003700200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020'), 

  UTL_RAW.CAST_TO_VARCHAR2('ccecbdf2c4cfb4f3cda8d3c3cafdbeddbcbccaf5b9c9b7ddd3d0cfdeb9abcbbe7638'), 

  UTL_RAW.CAST_TO_NVARCHAR2('59296d2553575927901a75286570636e6280672f80a14efd67099650516c53f800760039') ,

  UTL_RAW.CAST_TO_NUMBER('c103'),

  UTL_RAW.CAST_TO_NUMBER('c10624'),

  UTL_RAW.CAST_TO_NUMBER('c107')

   FROM dual