Remember your dreams

Remember your dreams
Remember your dreams

Monday, June 4, 2012

Purging or deleting audit trail files in 11g

Whether you are using audit_trail = OS or DB there will be audit files accumulating in the audit_file_dest. Much more if audit_trail parameter is set to DB. This is the script I use to delete the files in the adump directory.

More on Oracle audit files.

Where are these audit files?

SQL> select value from v$parameter where name = 'audit_file_dest';

VALUE
--------------------------------------------------------------------------------
/app/oracle/admin/db251/adump

I run this file through corntab every night.
It tells me 
  • how many audit trail files there are
  • how many audit trail files are going to be deleted
  • After purging, how many audit files are left
 Create a file in your common scripts or desired directory.

vi delete_aud_files.sh

Make it executable.
chmod 770 delete_aud_files.sh

Add it to your crontab to run every day.
## Delete Audit files 8:30pm every night
30 20 * * * /oracle/scripts/cleanup/delete_aud_files.sh

This is an example of the script I use.
NOTE: items in RED should be editing to your environment.

#!/bin/sh
ORACLE_BASE=/app/oracle; export ORACLE_BASE
ORACLE_SID=db251; export ORACLE_SID
DAY_AFTER=3; export DAY_AFTER
OUTF=/oracle/scripts/cleanup/cleanup_audit_files.log ; export OUTF
#
echo `date '+%m/%d/%y %A %X'` > ${OUTF}
echo >> ${OUTF}
echo "SCRIPT NAME:   $0" >> ${OUTF}
echo "SERVER:        "`uname -n` >> ${OUTF}
echo "KEEP DURATION: $DAY_AFTER days" >> ${OUTF}
echo >> ${OUTF}
echo >> ${OUTF}
#
echo "Delete audit files owned by oracle..." >> ${OUTF}
echo >> ${OUTF}
echo >> ${OUTF}
# change directory to the audit file directory
cd ${ORACLE_BASE}/admin/${ORACLE_SID}/adump
echo >> ${OUTF}
echo "Deleting from directory:" >> ${OUTF}
echo "[${ORACLE_BASE}/admin/${ORACLE_SID}/adump]" >> ${OUTF}
echo >> ${OUTF}
echo "The total number of files in directory is:" >> ${OUTF}
# output the total count of audit files to outfile
ls -al | wc -l >> ${OUTF}
echo >> ${OUTF}
echo "Total number of files to be deleted is:" >> ${OUTF}
# output the total number of audit files that will be deleted
find . -maxdepth 1 -type f -mtime +$DAY_AFTER -name "*.aud" | wc -l >> ${OUTF}
echo >> ${OUTF}
# delete the audit files
find . -maxdepth 1 -type f -mtime +$DAY_AFTER -name "*.aud" | xargs rm
echo "Files successfully deleted." >> ${OUTF}
echo "Total number of files remaining:" >> ${OUTF}
# output the remaining count of audit files in the adump directory
ls -al | wc -l >> ${OUTF}
echo >> ${OUTF}
echo >> "Complete with delete." >> ${OUTF}
#
# Now email results
echo >> $OUTF
echo `date '+%m/%d/%y %A %X'` >> $OUTF
cat $OUTF | /bin/mailx -s "`uname -n` : delete old Oracle audit files" my_email@company.com
exit


This is what the email output looks like:

06/04/12 Monday 20:30:01

SCRIPT NAME:   /oracle/scripts/cleanup/delete_aud_files.sh
SERVER:        neutron
KEEP DURATION: 3 days


Delete audit files owned by oracle...

Deleting from directory:
[/app/oracle/admin/db251/adump]

The total number of files in directory is:
38677

Total number of files to be deleted is:
16589

Files successfully deleted.
Total number of files remaining:
22094

06/04/12 Monday 20:30:03


Oracle 11g Audit Trail

If you don't care about auditing the DML in your database then you will agree with me that Oracle should not have made auditing mandatory. Yes you can set this parameter to NONE, but you will still get some level of auditing. This is only a brief overview and I am not going into too much depth here.

The possible values for the audit_trail parameter are
 SQL> show parameters audit_trail

DB
DB_EXTENDED
OS
XML
NONE

If the value is DB you should set up a cron script to purge these files:
Script to purge audit trail files in Oracle 11g.

Where are the audit files

SQL> select value from v$parameter where name = 'audit_file_dest';

VALUE
--------------------------------------------
/app/oracle/admin/db251/adump


To update this parameter
SQL> alter system set audit_trail=db scope=spfile;
SQL> shutdown immediate;
SQL>startup;
SQL> show parameter audit_trail;
AUDIT_TRAIL TYPE   VALUE
-----------------   ------    -------
audit_trail           string   DB

The default is OS. If the setting is OS then you should also periodically delete rows from the AUD$ table.


SQL> select count(*) from aud$;
COUNT(*)
----------
344273

SQL> select count(*) from aud$ where ntimestamp# > sysdate - 21;
 COUNT(*)
----------
53550

SQL> delete from aud$ where ntimestamp# > sysdate - 21;

53550 rows deleted.

Friday, May 18, 2012

Clear alerts in Oracle Enterprise Manager OEM

I am using 11g, but this work in 10g as well.
I had this annoying alert appearing under my alert tab on OEM for some time. A failed job, yes I knew it failed and fixed it that day. I waited for the alert to disappear, but alas, it did not. I acknowledged it and thought that was the end of that. Well it's been hanging around for some time and when I click on it the alert it take me to a jobs page with no information on how to clear.

I started hunting around for information on how to clear alerts that have no purpose in OEM and I found a lot of posting that said to log in as sysman and query the mgmt_current_severity and then use em_severity.delete_current_severity to remove the desired alert.

Okay that sounds good, but then I found a post by a Said Ahmed. He posted the following query which I found to be very useful so here it is.

First log in as SYSMAN or your repository owner.


select t.target_name
, t.target_type
, collection_timestamp
, message
, 'exec em_severity.delete_current_severity(''' ||
t.target_guid || ''',''' ||
metric_guid || ''',''' ||
key_value || ''')' em_severity
from sysman.mgmt_targets t
inner join
sysman.mgmt_current_severity s
on
t.target_guid = s.target_guid;


This will give you information about all the alerts in OEM. Copy the result from the em_severity column that you want removed. Run it exactly as it appears. Be sure you are logged in as sysman.

For example:
SQL> exec em_severity.delete_current_severity('stuff','moretuff','SCHEMA')

Before deletion
After deletion




Wednesday, April 18, 2012

RMAN unregister and register after duplication

If you were using the RMAN catalog for the your auxiliary instance before duplication you will need to reregister it. The newly created duplicated instance now has a new DBID. This example will demonstrate how to reincarnate your database or simple cut to the chase and register a database with an rman catalog.

In my example I just completed an Active Duplication of my production instance to a development instance I call dev311.

Log onto the development server and dev311 database.

Using rman connect to the target and catalog.

% rman target / catalog login/xxxx@vcatdb

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Apr 12 11:01:44 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: dev311 (DBID=3811178510)
connected to recovery catalog database

Note your new DBID. Now Query the catalog for the incarnation of dev311. You see that the DBID is not the same as your newly duplicated database.

RMAN> list incarnation of database dev311;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1841905798 1841905799 DEV311 3805185786 CURRENT 9804844295329 02-DEC-10

You will notice that the DBID for the registered version of dev311 is different than your current DBID of the dev311 database. The DBID has changed so we need to unregister and register our new DBID.

Exit RMAN and log in without connecting to the target, connect to the catalog only.

Set your DBID to the dev311 instance already known to the catalog and unregister the old instance.

# rman
Recovery Manager: Release 11.2.0.2.0 - Production on Thu Feb 24 08:01:44 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN>

RMAN> connect catalog login/passwd@catdb;
connected to recovery catalog database

RMAN> set dbid 3805185786; # this is the old DEV311 dbid.
executing command: SET DBID
database name is "DEV311" and DBID is 3805185786

RMAN> unregister database;
database name is "DEV311" and DBID is 3805185786
Do you really want to unregister the database (enter YES or NO)? y
database unregistered from the recovery catalog

RMAN> exit

Now exit RMAN and reconnect to the target and catalog.

RMAN> connect target / catalog login/passwd@catdb;

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Apr 12 11:12:42 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DEV311 (DBID=3811178510)
connected to recovery catalog database

Register the new database instance.

RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> list incarnation of database dev311;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1992522226 1992522424 DEV311 3811178510 PARENT 1 02-DEC-10
1992522226 1992522227 DEV311 3811178510 CURRENT 9864858231054 12-APR-12

RMAN> list backup;
specification does not match any backup in the repository

Since you recreated the registration in the catalog you need to also reconfigure your default settings. Some of my important configuration are the following:

RMAN> run {
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 35 DAYS;
CONFIGURE DEFAULT DEVICE TYPE TO sbt;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE sbt TO '%F';
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 4;
CONFIGURE BACKUP OPTIMIZATION ON;
}

RMAN> crosscheck archivelog all;


Now backup your database.

Tuesday, April 17, 2012

Recreating the OEM .

Recreating the Oracle Enterprise Manager (OEM) for your newly duplicated database.
Enterprise Manager Grid Control - Version: 10.1.0.2 to 11.2.0.2 - Release: 10.1 to 11.2

If there is a change in SID, DBID, or hostname for the database that has been restored then you need to recreate the dbconsole.

Refer How To Reconfigure DB Control After a Hostname, Domain name or Listener Change Has Occurred On The Server
(MOS Doc ID 293678.1) Information in this document applies to any platform.

This is the process:

Stop OEM

# emctl stop dbconsole

Drop the existing OEM installation.

# cd $ORACLE_HOME
# cd ./sysman/admin/emdrep/bin/
#./RepManager servername.domain.com 1526 dev311 -action drop

Enter SYS user's password :
Enter repository user name : sysman
Getting temporary tablespace from database...
.
.
.
Dropping Repos User ... Done.
Dropping Roles/Synonymns/Tablespaces ... Done.
Dropped Repository Successfully.

Install the OEM

# cd $ORACLE_HOME/bin
./emca -config dbcontrol db -repos create

STARTED EMCA at Apr 12, 2012 2:26:40 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: dev311
Database Control is already configured for the database dev311
You have chosen to configure Database Control for managing the database dev311
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]:y

Listener ORACLE_HOME [ /app/oracle/product/11.2.0 ]:
Password for SYS user: xxxx
Password for DBSNMP user: xxxx
Password for SYSMAN user: xxxxx
Email address for notifications (optional): email@company.com
Outgoing Mail (SMTP) server for notifications (optional): smtp-server.com

-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /app/oracle/product/11.2.0.2

Local hostname ................ servername.com
Listener ORACLE_HOME ................ /app/oracle/product/11.2.0.2
Listener port number ................ 1526
Database SID ................ dev311
Email address for notifications ............... email@company.com
Outgoing Mail (SMTP) server for notifications ............... smtp-server.com

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Apr 12, 2012 2:28:25 PM oracle.sysman.emcp.EMConfig perform
.
.
.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 12, 2012 2:34:42 PM

I like to finish up with a stop and start of the emctl services.

# emctl stop dbconsole

# emctl start dbconsole

Using my browser I can access OEM and review my newly cloned database.


Oracle 11g, Active duplication.

Oracle Active Duplication, database duplication and cloning.
Oracle Enterprise Edition 11.2.0.2
Linux x86_64, RH 5

I have a previous post describing how I used duplication in 10g to clone my production database to a development instance on a development server. Now in 11g we have the option of using Active Duplication to achieve the same results. Active Duplication is more streamline and so much more simple than previous methods of duplication. This is my example of using Oracle 11gR2 Active Duplication to clone my production database instance to a development instance on the development database server.

Active Duplication does not require a backup of your database. This is a big plus! From the production database server we will login and push the production instance across the network to the development server using RMAN. The downside is that there is some utilization of the production database as well as increased network traffic. A small price to pay to alleviate the headaches the former method of duplication gave me.

There are two methods of Active duplication. Pfile and Spfile. I much prefer the Pfile method (and you will see why), but I will describe the differences and give examples of both methods.

In this example the production instance is named prod311 and our cloned copy will be called dev311.

Terms:
Target – The production database or database you are going to clone. I am using prod311. 
Auxiliary – The duplicated database. I am using dev311.

On the DEVELOPMENT server:
The auxiliary database server must have a matching directory structure to that of the target database server. If your auxiliary environment already exists then the duplication process is much simpler. If not then you must create the environment and directory structure and the simplest way to do that is by creating a new database on the auxiliary server, in this case it would be called dev311. Just be sure all the datafile directories exist (irregardless of the tablespace or datafile names) as they do on the target with the exception of the SID.


For example the prod311 database will have a paths of
/u01/oracle/oradata/prod311

/app/oracle/diag/rdbms/prod311/prod311/trace

and dev311 instance will have
/u01/oracle/oradata/dev311

/app/oracle/diag/rdbms/dev311/dev311/trace

If this is a repeat of the duplication process and your dev311 instance already exists then you are good to go. 

spfile method
Move the current pfile and create a new pfile with only the db_name parameter.
% more $ORACLE_HOME/dbs/initdev311.ora
db_name='dev311' 


pfile method
Before shutting down the auxiliary database, obtain a current pfile if you do not have one already.
SQL> create pfile from spfile;
File created.
 

For the pfile method use your current pfile and add the following parameters: 
*.db_file_name_convert='prod311','dev311' 
*.log_file_name_convert='prod311','dev311' 

Both pfile and spfile methods:
Everything else applies to both the pfile and spfile methods with the exception of the RMAN script which I describe further down.

Oracle Net Connectivity:
Establish Oracle Net Connectivity to the Auxiliary instance (dev311) from the Target Host (production, prod311, server). Edit the tnsnames.ora on the Target (prod311) server and add the dev311 (Auxiliary) database.

Now test from the Target -
% tnsping dev311
TNS Ping Utility for Linux: Version 11.2.0.2.0
Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Auxiliary-Server.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dev311.Auxiliary-Server.com)))
OK (320 msec)

Password file on the Auxiliary server for dev311:
Password file must exist for the Auxiliary database and it MUST MATCH THE TARGET sys user:

Insure there is a password file for the auxiliary instance on the remote server. If not then create a password file on the auxiliary server for the auxiliary database. Be sure the password matches the sys password on your production instance since this is the database we will be cloning.
   
On the Auxiliary server
% cd $ORACLE_HOME/dbs
% orapwd file=orapwdev password=sys_same_on_prod311

Shutdown the auxiliary database.

Delete archive logs from the dev311 instance:
There will likely be existing archive log files on disk for the dev311 instance. Having any existing archive logs for your dev311 environment will cause problems during the duplication.
Delete all archive log folders and/or archive logs for the dev311 instance on the auxiliary server.

% cd $ORACLE_BASE/flash_recovery_area/dev311/archivelog/
% rm -rf *

Move the existing spfile to a new name:
# mv spfiledev311.ora spfiledev311.old 

Restart in nomount with the updated pfile:
SQL> shutdown immediate;

SQL> quit 

sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 12 09:21:34 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.


Start the auxiliary database in nomount using the pfile:

SQL> startup nomount pfile='/app/oracle/product/11.2.0.2/dbs/initdev311.ora'
ORACLE instance started.

On the target prod311 database server start RMAN. Connect to the target and auxiliary. 

The RMAN catalog is not required for Active Duplication.

# rman

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Apr 12 02:31:44 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


Note: Do not use the syntax: connect target /

Specify it as: connect target sys/password@SID


Connect to the target (the database you are cloning) and include the alias.
RMAN> connect target sys/xxxxx@prod311;

connected to target database: prod311 (DBID=132331749)


Connect to the auxiliary, dev311,
and include the alias.
RMAN> connect auxiliary sys/xxxxx@dev311;

connected to auxiliary database: dev311 (not mounted)


Notice the auxiliary database in not mounted.


Increase the parallelism to 4 (or more).
RMAN> configure device type disk parallelism 4;


Executing the RMAN duplication command:


The first 2 RMAN examples are using the spfile method. The 3rd example, and simplest, is using the pfile method.

This is the basic spfile example:

RMAN> duplicate target database to dev311
2> from active database
3> db_file_name_convert 'prod311','dev311'
4> spfile
5> parameter_value_convert 'prod311','dev311'
6> set log_file_name_convert 'prod311','dev311'
7> nofilenamecheck;

This spfile method example demonstrates how to modify parameters from your target to your auxiliary database:

RMAN> duplicate target database to dev311
2> from active database
3> db_file_name_convert 'prod311','dev311'
4> spfile
5> parameter_value_convert 'prod311','dev311'
6> set log_file_name_convert 'prod311','dev311'
7> set memory_max_target '10536091648'
8> set memory_target '9462349824'
9> set db_flashback_retention_target '1440'
10> set db_recovery_file_dest_size '104857600'
11> nofilenamecheck;


Finally, saving the best for last, using the pfile method
Pfile Method.

RMAN> duplicate target database to dev
2> from active database
3> nofilenamecheck;

RMAN> exit

Recovery Manager complete.

On the newly duplicated database -

I like to shutdown the new cloned database and start it again.


If you used the pfile method make a new spfile.
SQL> create spfile from pfile;
File created.
 

If you used the spfile method then create a new pfile.
SQL> create pfile from spfile;
File created.
 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup

ORACLE instance started.


Total System Global Area 1.0489E+10 bytes
Fixed Size 2235656 bytes
Variable Size 6811550456 bytes
Database Buffers 3623878656 bytes
Redo Buffers 51380224 bytes
Database mounted.
Database opened.

SQL> select name, created, version_time, open_mode from v$database;


NAME CREATED VERSION_T OPEN_MODE

--------- --------- --------- --------------------

DEV311 12-APR-12 12-APR-12 READ WRITE


SQL> quit


Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


If you were using the RMAN catalog for the dev311 instance before duplication you will need to reincarnate it. The newly created dev311 instance (from prod311) now has a new DBID.
Log onto the development server and dev311 database.

RMAN unregister and register after duplication.

Now backup your database.

If you were using OEM you will need to drop and recreate it for the newly duplicated database.
The following post describes how to recreate OEM after duplication,
How to recreate OEM.


Proactive Oracle DBA

This is a series of posts. I am working this to share some of the many scripts I schedule to automatically run to alert me of any current o...