Article Catalog
preamble
Daily operation and maintenance we will encounter a variety of Oracle files are too large, resulting in disk burst, the database is unusually Down, the following share some automatic processing scripts to you.
1. Archive log deletion
Overview of archiving
Oracle Archive Log (Archive Log) is a technique used to record database transaction logs. It can be changed in the database (such as insert, update and delete) to record the operation, in order to restore data in the event of failure. Archive Log using incremental recording , that is, after each change operation , the log file will gradually become larger .
If the space for archiving is full, the
ORA-00257: archiver error. Connect internal only, until freed.
scheduled deletion
The following script is timed to delete archive logs from 7 days ago
[root@mesdb1 ~]# su – oracle
[oracle@mesdb1 ~]$ mkdir -p /home/oracle/script
[oracle@mesdb1 ~]$ mkdir -p /home/oracle/arch/log
vi /home/oracle/script/arch_del.sh
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=mesdb1
dt=`date '+%Y%m%d_%H%M'`
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
rman target / log /home/oracle/arch/log/log_arch_$dt.log <<EOF
run{
allocate channel ch1 type;
allocate channel ch2 type;
crosscheck archivelog all;
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
delete expired archivelog all;
delete archivelog until time 'sysdate-7' ;
release channel ch1;
release channel ch2;
}
EOF
0 1 * * * sh /home/oracle/script/arch_del.sh
2. Listening to logs
Listening Overview
In oracle 10g, there is only text format listener.log; in version 11.2, due to the ADR feature, there are xml/log two formats of listener log, xml format log will reach a certain size after automatic slicing to form log_1.xml this kind of numerical number of the file; log format log will reach a certain size after automatic slicing to form log_1.log this kind of numerical number of the file. log format logs will be automatically sliced after reaching a certain size to form log_1.xml, a file with a number sequence; log format logs will be automatically sliced after reaching a certain size to form log_1.log, a file with a number sequence.
Oracle11g’s new feature, Automatic Diagnostic Repository (ADR), is enabled by default.
Listening logs are stored in xml format by default, if the space occupied is too large, you can turn off the
It is recommended to adjust the listener parameter file DIAG_ADR_ENABLED_listener = OFF , the
Then reload the listener, and the listener won’t be stored as an XML file format, but will use the old 10g way.
It is also recommended to set the parameter DIAG_ADR_ENABLED = OFF in the SQLNET.ORA file
to block ADR from writing error messages (such as ORA-609) to ALERT as follows:
Add the following line inside listener.ora:
DIAG_ADR_ENABLED_ LISTENER = OFF where LISTENER is the listener name
sqlnet.ora add the following line:
DIAG_ADR_ENABLED = OFF
Restart listening:
lsnrctl reload
Listening to process queries, RAC generally uses ASM's Listening
ps -ef |grep tnslsnr
grid 12386 1 0 2023 ? 05:00:31 /u01/app/19.0.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
grid 12460 1 0 2023 ? 00:47:24 /u01/app/19.0.0/grid/bin/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit
oracle 70467 68189 0 08:40 pts/2 00:00:00 grep --color=auto tnslsnr
scheduled deletion
The listening position is at the following location
cd $ORACLE_BASE/diag/tnslsnr/{hostname}
asmnet1lsnr_asm
listener
listener_scan1
crontable -l
0 23 * * * /home/oracle/scirpt/listener_clear.sh > /dev/null 2>&1;
#!/bin/sh
#listener
find /u01/app/grid/diag/tnslsnr/rac1/listener/alert -mtime +7 -name “log_.xml” | xargs rm -rf ;
find /u01/app/grid/diag/tnslsnr/rac1/listener/trace -mtime +7 -name “listener_.llog” | xargs rm -rf ;
#asmnet1lsnr_asm
find /u01/app/grid/diag/tnslsnr/rac1/asmnet1lsnr_asm/alert -mtime +7 -name “log_.xml” | xargs rm -rf ;
find /u01/app/grid/diag/tnslsnr/rac1/asmnet1lsnr_asm/trace -mtime +7 -name “asmnet1lsnr_asm_.log” | xargs rm -rf ;
3. Alarm log
Alarm log description overview
There are three types of Oracle trace files
1) Background alarm log files
2) Background trace files created by DBWR, LGWR, SMON and other background processes
(3) connected to Oracle’s user processes (Server Processes) generated by the user tracking file
scheduled deletion
There are two parameters involved here:
LONGP_POLICY: 365 days of events and warnings are retained by default.
SHORTP_POLICY: default retention of trace file or core dump file for 30 days
[oracle@rac1 ]$ adrci
adrci> show home
ADR Homes:
diag/kfod/mes-all-db1/kfod
diag/rdbms/mesdb/mesdb1
diag/clients/user_oracle/host_314373652_110
adrci> set home diag/rdbms/mesdb/mesdb1
Deleting an alert log from a day ago
adrci> PURGE -age 1440 -type ALERT
Deleting a day old trace file
adrci> PURGE -age 1440 -type TRACE
adrci> show control ## in hours
Change SHORTP_POLICY to enable reserved dates.
adrci> set control (SHORTP_POLICY=360)
Clean up trace files (in minutes, before the previous day)
adrci> purge -age 1440 -type TRACE
By default the day’s trace file cannot be cleaned up!
SYS@mesdb1> SELECT * FROM V$DIAG_INFO WHERE NAME = ‘Diag Trace’;
4. Backup Delete
Full Script
#!/bin/bash
databaseName='PROD'
baseDir='/usr01/backup/'
backupDate=`date "+%Y-%m-%d"`
if [ `whoami` != 'oracle' ];then
echo You must use the oracle user to perform the backup, the backup stops
exit
fi
if [ ! -e $baseDir ]
then
echo Backup root $baseDir directory does not exist, backup stopped
exit
fi
backupDir=$baseDir$backupDate
if [ ! -e $backupDir ];then
mkdir -p $backupDir/arc
mkdir -p $backupDir/data
mkdir -p $backupDir/ctl
else
echo Backup directory $backupDir already exists, backup is stopped.
exit
fi
logFile=$backupDir/backup_log_$backupDate
echo `date` Backup start
# To add a catalog
rman target / log=${logFile} <<EOF
run {
allocate channel c1 device type disk ;
allocate channel c2 device type disk ;
CONFIGURE BACKUP OPTIMIZATION ON;
backup as compressed backupset
incremental level 0
tag '${databaseName}_{backupDate}'
filesperset 8
format '${backupDir}/data/${databaseName}_data_%U.bak'
database
include current controlfile ;
sql 'alter system archive log current';
backup current controlfile format '${backupDir}/ctl/${databaseName}_ctl_%U.bak';
backup as compressed backupset
skip inaccessible
filesperset 32
format '${backupDir}/arc/${databaseName}_arc_%U.bak'
archivelog all;
delete noprompt archivelog until time = 'sysdate-3';
release channel c1;
release channel c2;
}
EOF
echo `date` Backup complete >> $logFile
Backup Delete
[oracle@mes-all-db1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sat Jan 6 10:11:34 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: MESDB (DBID=3146269603)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name MESDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_mesdb1.f'; # default
--Backup policy adjusted to keep backups for 7 days
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
