Log rotation for Oracle database

Posted in: Technical Track

Most of you may already know how to configure the log rotation for your database alert log or the listener log etc,. As root, you can manually create a configuration file as below, and put it into /etc/logrotate.d/.

/u01/app/oracle/diag/rdbms/test1/TEST1/trace/alert_TEST1.log
{
        daily
        rotate 15
        compress
        copytruncate
        delaycompress
        create 0640 oracle dba
        notifempty
}

It’s easy to create the log rotation configuration file for one database. But what if you have 20+ databases running on your server, are you still planning to manually locate their alert log files? Today I would like to introduce a shell script that will automatically search the running ASM instance, databases and listeners and create the configuration files for you.

Here are the steps on how to run shell scripts.

1. Run the shell script

[oracle@testserver ~]$  ./gen_logrotate_config.sh

2. Three configuration files will be created: oracle_rdbms_log, oracle_asm_log and oracle_listener_log. Let’s say we have 4 running databases: TEST1, TEST2, TEST3 and TEST4, one ASM instance +ASM1, three listeners: listener_scan1, listener_scan2 and LISTENER. The configuration files will be like:

[oracle@testserver ~]$ cat oracle_rdbms_log
/u01/app/oracle/diag/rdbms/test1/TEST1/trace/alert_TEST1.log /u01/app/oracle/diag/rdbms/test2/TEST2/trace/alert_TEST2.log /u01/app/oracle/diag/rdbms/test3/TEST3/trace/alert_TEST3.log /u01/app/oracle/diag/rdbms/test3/TEST3/trace/alert_TEST4.log
{	
	daily
	rotate 15
	compress
	copytruncate
	delaycompress
	create 0640 oracle dba
	notifempty
}
[oracle@testserver ~]$ cat oracle_asm_log
/u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log  
{	
	daily
	rotate 15
	compress
	copytruncate
	delaycompress
	create 0640 oracle dba
	notifempty
}
[oracle@testserver ~]$ cat oracle_listener_log
/u01/app/oracle/diag/tnslsnr/TESTSERVER/listener_scan2/trace/listener_scan2.log /u01/app/oracle/diag/tnslsnr/TESTSERVER/listener_scan1/trace/listener_scan1.log /u01/app/oracle/diag/tnslsnr/TESTSERVER/listener/trace/listener.log	
{	
	daily
	rotate 15
	compress
	copytruncate
	delaycompress
	create 0640 oracle dba
	notifempty
}

3. Put three configuration files to /etc/logrotate.d/ then you will see the log files will be rotated on next day.

You can find the code here and enjoy!

email

Interested in working with Howie? Schedule a tech call.

1 Comment. Leave new

Norman Dunbar
October 29, 2019 7:28 am

Thanks for this script. Most useful, however, you appear to have a blank line at line 23 – between “END_SQL” and “done)…” for the “oracle_asm_log” output. This causes the output to lose the actual name of the log file for the ASM instance(s).

I’ve used this on our cloud (OCI) servers and found that Oracle have set up the servers with the ASM details hard coded into .bashrc under the grid user – not helpful! This obviously caused the script to fail when attempting to extract the alert file names. Adding an entry for +ASM1 into oratab solved that problem.

I’m not sure that the “oracle_rdbms_log” output is (always?) correct though. Our alert logs, in OCI, are located in the core_dump_dest (with cdump replaced by trace, of course) rather than in the location for background_dump_dest as per the script, but a quick edit fixed that too.

It could be that our cloud setup is “broken”, who knows! :)

Thanks again.

Cheers,
Norm.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *