Oracle: How to find number of redo log switch

Following query outputs redo log switch/day broken down to each hour. If you see too many switches at any point of time, think about increasing redo log size

select to_char(first_time,'YYYY-MM-DD') day,
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'99')
"00",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'99')
"01",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'99')
"02",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'99')
"03",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'99')
"04",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'99')
"05",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'99')
"06",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'99')
"07",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'99')
"08",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'99')
"09",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'99')
"10",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'99')
"11",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'99')
"12",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'99')
"13",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'99')
"14",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'99')
"15",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'99')
"16",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'99')
"17",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'99')
"18",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'99')
"19",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'99')
"20",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'99')
"21",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'99')
"22",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'99')
"23"
from v$log_history
group by to_char(first_time,'YYYY-MM-DD')

Oracle: How to find number of redo log switch

Following query outputs redo log switch/day broken down to each hour. If you see too many switches at any point of time, think about increasing redo log size

select to_char(first_time,'YYYY-MM-DD') day,
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'99')
"00",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'99')
"01",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'99')
"02",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'99')
"03",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'99')
"04",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'99')
"05",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'99')
"06",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'99')
"07",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'99')
"08",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'99')
"09",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'99')
"10",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'99')
"11",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'99')
"12",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'99')
"13",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'99')
"14",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'99')
"15",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'99')
"16",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'99')
"17",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'99')
"18",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'99')
"19",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'99')
"20",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'99')
"21",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'99')
"22",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'99')
"23"
from v$log_history
group by to_char(first_time,'YYYY-MM-DD')

Oracle: How to increase redo log size

Below is list of sql's to increase the size of redo log files. In order to increase the size, you need to create new redo log files, check point the old one and drop the old one. Replace the path to match your redo location.

In this example, I am creating 3 more redolog files with group number 4,5 and 6 and dropping the old ones.

Log in as sys

Select * from v$log --- Get max group no

Max log files you can have in your database can be got from

Select * from v$controlfile_record_section
where type ='REDO LOG'

Alter database add logfile group 4 'c:\oracle\oradata\orcldb\redo04.log'
size 250M reuse;
Alter database add logfile group 5 'c:\oracle\oradata\orcldb\redo05.log'
size 250M reuse;
Alter database add logfile group 6 'g:\oracle\oradata\orcldb\redo06.log'
size 250M reuse;

Now switch log file 3 times to make these new files online.

Alter system switch logfile;
Alter system switch logfile;
Alter system switch logfile;

force checkpoint to write all contents to data files.

Alter system checkpoint;

Drop the old log files.
Alter database drop logfile group 1;
Alter database drop logfile group 2;
Alter database drop logfile group 3;

Oracle: How to increase redo log size

Below is list of sql's to increase the size of redo log files. In order to increase the size, you need to create new redo log files, check point the old one and drop the old one. Replace the path to match your redo location.

In this example, I am creating 3 more redolog files with group number 4,5 and 6 and dropping the old ones.

Log in as sys

Select * from v$log --- Get max group no

Max log files you can have in your database can be got from

Select * from v$controlfile_record_section
where type ='REDO LOG'

Alter database add logfile group 4 'c:\oracle\oradata\orcldb\redo04.log'
size 250M reuse;
Alter database add logfile group 5 'c:\oracle\oradata\orcldb\redo05.log'
size 250M reuse;
Alter database add logfile group 6 'g:\oracle\oradata\orcldb\redo06.log'
size 250M reuse;

Now switch log file 3 times to make these new files online.

Alter system switch logfile;
Alter system switch logfile;
Alter system switch logfile;

force checkpoint to write all contents to data files.

Alter system checkpoint;

Drop the old log files.
Alter database drop logfile group 1;
Alter database drop logfile group 2;
Alter database drop logfile group 3;

Oracle: How to capture errors using "Log Errors Into" Clause

1) Create an error table to capture the errors from Insert, Update and Delete. To create this table,use Oracle's built in create_error_log function

Exec dbms_errlog.create_error_log("SOURCE_TABLE")

SOURCE_TABLE: Replace SOURCE_TABLE with name of your table. By default, error table will be prefixed by "ERR$_"

If you want to create a error table with a different name,

Exec dbms_errlog.create_error_log("SOURCE_TABLE","ERROR_TABLE_NAME")

By Default Error table will contain all columns from source table.
If your source_table contains long or blob columns, you need to skip them in the error table

Exec dbms_errlog.create_error_log("SOURCE_TABLE", skip_unsupported =>true)

Example: Insert into table_name ( col1,col2....) Values (Col1,Col2....)
log errors into err$_table_name
reject limit unlimited;

Update table_name
Set Col1 = ....
Where.....
log errors into err$_table_name
reject limit unlimited;

Delete table_name
Where.....
log errors into err$_table_name
reject limit unlimited;

If you don't use reject limit unlimited your transaction will fail and only 1 record will be inserted in the err$ table.