remove applied archive logs on primary and standby servers using batch scripts

Implementing RMAN is the best option to remove the applied archive logs. But in some scenario like in this case for me, I have to delete the applied archive log files manually on primary and standby servers to avoid the shortage of disk space.

Though this manual deletion was a temporary task till rman implementation in couple of months, I decided to write the scripts to clear the logs automatically.

The below scripts are written to check the primary and standby servers for the applied log sequence number and delete the old log files.

To remove the applied archive logs on Standby server,

1. Create a batch file as remove_archive_logs.bat
2. Open the above batch file in notepad and copy the below code

set oracle_sid=Stby
e:
cd D:\ARCH\Stby\scripts
sqlplus / as sysdba @stby_arch_files.sql
remove_stby_logfiles.bat

3. Create a sql file stby_arch_files.sql

set head off
set pagesize 0
set feedback off
set linesize 100
spool D:\ARCH\Stby\scripts\remove_stby_logfiles.bat
select 'del '||name from v$archived_log where sequence# between (select applied_seq# - 1700 from v$archive_dest_status where dest_name='STANDBY_ARCHIVE_DEST') and (select applied_seq# - 20 from v$archive_dest_status where dest_name='STANDBY_ARCHIVE_DEST');
/
spool off
exit

And schedule the remove_archive_logs.bat script on the windows scheduler on standby server.

To remove the applied archive logs on Primary server,

1. Create a batch file as remove_archive_logs_primary.bat
2. Open the above batch file in notepad and copy the below code

set oracle_sid=DEV
e:
cd D:\ARCH\DEV\scripts
sqlplus / as sysdba @remove_logs.sql
remove_logfiles.bat

3. Create a sql file remove_logs.sql

set head off
set pagesize 0
set feedback off
set linesize 100
spool D:\ARCH\DEV\scripts\remove_logfiles.bat
select 'del '||name from v$archived_log where name <>'DEV' and sequence# between (select applied_seq# - 1700 from v$archive_dest_status where applied_seq# <> '0') and (select applied_seq# - 20 from v$archive_dest_status where applied_seq# <> '0');
/
spool off
exit

And schedule the remove_archive_logs_primary.bat script on the windows scheduler on primary server.

To use the above script in your database environment, consider changing the values to suit your environment

1700 - This is the number of files to be removed from the standby server. You can increase or decrease depending on the number of archive logs shipped to the standby server each day.

20 - This specifies the log file deletion starts from applied log number - 20.

For example, If the applied log sequence is 1021 then the log files will be deleted till 1001. You may retain the log files from 1001 -1021 or remove the number 20 to delete all the archive logs upto the applied archive logs.

What is naPrdMgr.exe?

Breakfast challenges - Pardon my english please, trying to improve my skill on writing

What is naPrdMgr.exe?

Started the work today with the tiredness of travel for work, Though washed my face to look active and fresh,My face showed i am tired as usual. Breakfast challenge for today was when a user complained about the performance issue / freezing, I checked the Application server and found that the process naPrdMgr.exe is occupying the maximum CPU. I searched through about this exe and found to be part of Mc-Cafee anti virus software.

This process is part epolicy Orchestrator. I tried stopping this process as a temporary solution and in the mean time the Application server completely frozen due to high CPU usage and i have to reboot the server. Though the reboot fixed the problem, I am still not convinced that the problem won't come back. I think the solution would be to upgrade the groupshield and patch upto the latest version.

Hanged report server engine brings down the Oracle Application Server

Hanged / Hung report server engine brings down the Oracle Application Server

Hanged reports server engine queues any subsequent requests and freezes the whole Oracle Application Server (OAS).

All the users will see the frozen session.

This is a bug on the Oracle application Server Reports. We cannot manually kill the hung report server engine.

The solution is to set the parameter "engineResponseTimeout" to the engine in the reports server configuration file.

Specify a maximum amount of time in minutes. If the engine takes longer time to update the job status then the report server terminates the job.


To edit the configuration file go to the OAS home and click the report server. "Edit configuration file" link is in the Administration section.

Example is below



Change the values accordingly for your setup.

Connection Refused - error message for targets in Enterprise Manager Grid Control

"Connection Refused" - error message for targets in Enterprise Manager Grid Control

The Oracle Management agent 10g is installed on a AIX box. On the Oracle Enterprise Manager Grid Control, the target host is displayed with the status up.

When you try to connect to the target database, you receive "Connection Refused" error message.

The problem is the Enterprise Manager repository database has two entries for the target.

To fix this problem, Go to Enterprise Manager Grid Control Host page and click targets tab. You may find two entries for agent10g on two different ports

eg:
host.host1.com:1830/
host.host1.com:3872/

1. In the agent server, check the "Agent URL" for active url

/bin>emctl status agent

For eg: If "https://host.host1.com:3872/emd/main" is the active url then

2. Stop the agent

/bin>emctl stop agent

3. Verify no processes are running on the agent, If you find a process then kill the process.

ps -ef | grep emagent

4. Go to /sysman/emd and remove the below files

rm -rf agntstmp.txt protocol.ini lastupld.xml recv/* upload/* state/*

5. On the Enterprise Manager Repository Database login as sysman using sqlplus

$ sqlplus sysman/password

SQL>exec mgmt_admin.cleanup_agent('host.host1.com:1830');
SQL>commit;

6. Go to the agent and start the agent and check the status

/bin>emctl start agent

/bin>emctl status agent

7. Upload the agent details to repository

/bin>emctl upload agent

8. Refresh and Confirm on the Enterprise Manager Grid Control Host page > targets tab

There should be only one entry for the agent10g host.host1.com:1830/

Rotate Oracle Application Server logs

How to rotate Oracle Application Server logs

We are using the Oracle10g Application Server version 10.1.2.2.0. As the logfiles generated on OAS are occupying the space some times we need to stop the OAS to clear the log files. Alternatively we can set the parameters on the OAS config to rotate the log files.

We can achieve the log rotation using Oracle Diagnostic Logging (ODL). Follow the below steps to rotate the logs.

1. Go to the $ORACLE_HOME/j2ee/oc4j_bi_forms/config
2. Take a backup of the application.xml file.
3. Find the below lines on the application.xml file.



and change it to



Note: specify the max-file-size and max-directory-size values appropriately.

4. Take a backup of the j2ee-logging.xml file in the same directory
5. Add the below code into the log loader

Check the top 10 space using files and the directories

To identify the top 10 space using files and directories use the du commnad as below.

du -a /opt/oracle | sort -n -r | head -n 10

If you want to list n or more files then change the value 10 to the required number of files. eg: 30

Unix - Delete files older than 7 days

We are using IBM AIX servers. We wanted to delete the log files older than 7 days to reclaim the space.

First i need to find the files older than 7 days and then remove them.

$find . -mtime +7 -exec rm -f {} \;

Here i have used find command to find the files in current directory("." means current directory) alternatively you can specify a directory path like "/opt/oracle/admin/log"

And used the parameter -mtime to specify the number of days where i have given +7.

If you specify 7 then seven or more days old files are selected
If you specify +7 then 8 or more days old files are selected.

Then execute the rm command with the -exec option.