Useful shortcuts for vi editor

Thursday, 5 January 2017

How to define redo log files?

e.g.
check current redo log file
select member from v$logfile; --redo log files

add log group and its members
SQL>
ALTER DATABASE 
  ADD LOGFILE GROUP 1 ('/oradata/ALPERDB/redo_g1_m00.log', '/oradata/ALPERDB/redo_g1_m01.log', '/oradata/ALPERDB/redo_g1_m02.log', '/oradata/ALPERDB/redo_g1_m03.log')
      SIZE 56M;

SQL>
ALTER DATABASE 
  ADD LOGFILE GROUP 2 ('/oradata/ALPERDB/redo_g2_m00.log', '/oradata/ALPERDB/redo_g2_m01.log', '/oradata/ALPERDB/redo_g2_m02.log', '/oradata/ALPERDB/redo_g2_m03.log')
      SIZE 56M;

SQL>
ALTER DATABASE 
  ADD LOGFILE GROUP 3 ('/oradata/ALPERDB/redo_g3_m00.log', '/oradata/ALPERDB/redo_g3_m01.log', '/oradata/ALPERDB/redo_g3_m02.log', '/oradata/ALPERDB/redo_g3_m03.log')
      SIZE 56M;

SQL>
ALTER DATABASE
  ADD LOGFILE GROUP 4 ('/oradata/ALPERDB/redo_g4_m00.log', '/oradata/ALPERDB/redo_g4_m01.log', '/oradata/ALPERDB/redo_g4_m02.log', '/oradata/ALPERDB/redo_g4_m03.log')
      SIZE 56M;

switch log file for activing above log files
SQL> ALTER SYSTEM SWITCH LOGFILE;

check physical_block_size and logical_block_size if needed
$ cd /sys/block/sda/queue
$ cat physical_block_size
$ cat logical_block_size

drop logfile group if necessary
SQL> ALTER DATABASE DROP LOGFILE GROUP 4;