Oracle database housekeeping methods – Unix log rotation

January 10, 2014 by Leave a comment 

Since Oracle Database 11g, all traces, incident dumps and packages, the alert log, Health Monitor reports, core dumps, and more files are stored in the ADR, a file-based Automatic Diagnostic Repository for simplified database diagnostic data management. In spite the housekeeping or purging of those files got not simplified, unfortunately the new Oracle utility ADRCI can not purge all the files that Oracle database generates in ADR. Thus, you should use the other OS methods and tools to accomplish Oracle database housekeeping.

This time I share a method of rotating Oracle database log files using Unix logrotate mechanism. The below example will rotate the Oracle alert.log and listener.log files in the following way:

– Rotation is based on the file size (you can use “M” or “k” for Mb and Kb)
– Unix Logrotate utility creates a copy of the original log file, then truncates it
– The rotated files will be compressed with Unix gzip utility with the following name <logfile>-yyyymmdd.gz
– Only recent 20 rotated log files are kept
– Rotation frequency is default = weekly
– No errors generated if the log file is missing
Read more »

Enable Oracle database to send emails via SMTP server

July 21, 2013 by 35 Comments 

Often in a complex enterprise Infrastructure Oracle DBAs face issues by enabling SMTP mail traffic on the databases through corporate email gateway servers. Imagine you have to provide your database applications an ability to send emails via Simple Mail Transfer Protocol (SMTP) protocol from Oracle database. Below I give a detail action plan to accomplish the same. My test example includes an Oracle database 11gR2 running on Linux RedHat 6 and a Microsoft Exchange corporate server.

1. Oracle packages SYS.UTL_SMTP and SYS.UTL_TCP

Check if Oracle packages SYS.UTL_SMTP and SYS.UTL_TCP are available on Oracle database and you have EXECUTE grants on them.

2. Check SMTP access of database Linux server on mail server

– Check whether you are able to contact the email gateway server via SMTP from the database Linux box:

$ telnet smtp_server 25

If you see blank screen or an error: “telnet: Unable to connect to remote host: Connection refused”,
your DB server is not recognized by the SMTP server. In this case you have to apply for mail SMTP access.
Otherwise type the following commands to test sending email from Linux to your corporate email account:

helo
mail from: my_email@my_company.com # you should see "Sender OK'
rcpt to: my_email@my_company.com   # you should see "Recipient OK"
data                               # Start mail input
test email via SMTP and orcl DB
[Enter]
.                                  # mail should be sent
[Enter]
quit

3. Apply for mail SMTP access

Contact your mail (exchange) admins and apply for SMTP access on your corporate smtp mail gateway server. Below is an example: Read more »

How to troubleshoot Oracle remote database connection

June 7, 2013 by 26 Comments 

Many Oracle DBAs, developers and just end-users often encounter an issue when they can not connect remotely to an Oracle database. There can be different reasons of the connection problems. I give below a short cookbook on resolving those database connection issues. But first let’s explore a bit Oracle connectivity concepts and terminology.

Oracle NET Client Listener tnsnames

Oracle NET Client Server configuration

An Oracle database is represented to clients as a service; that is, the database performs work on behalf of clients. The service name is included in the connect data part of the connect descriptor. To connect to a database service, clients use a connect descriptor that provides the location of the database and the name of the database service. The address portion of the connect descriptor is actually the protocol address of the listener. To connect to a database service, clients first contact a listener process that typically resides on the database server. The listener receives incoming client connection requests and hands these requests to the database server. Once the connection is established, the client and database server communicate directly. Read more »

How to create Sample Schemas in Oracle 11g database – ORA-19613

May 10, 2013 by 3 Comments 

The Oracle Database Sample Schemas provide a common platform for examples in each release of Oracle Database. They are easy to use for testing and training purposes. All Oracle Database documentation and training materials refer to the Sample Schemas environment.

Following are Oracle Sample Schemas

– OE (Order Entry schema) – useful for dealing with matters of intermediate complexity. Many data types are available in this schema, including non-scalar data types.

– HR (Human Resources schema) – useful for introducing basic topics. An extension to this schema supports Oracle Internet Directory demos.

– PM (Product Media schema) is dedicated to multimedia data types.

– IX (Information Exchange main schema) includes a set of schemas for demonstrate Oracle Advanced Queuing capabilities

– SH (Sales History schema) is designed to allow for demos with large amounts of data. An extension to this schema provides support for advanced analytic processing.

– SCOTT – old-fashioned famous schema with its two prominent tables EMP and DEPT that is used by Oracle for many years.

– BI – includes only synonyms on SH schema

With this simplicity I personally found difficulties in creating Sample Schemas in Oracle 11g database. In fact I could not do it in a standard way with Oracle installation binaries before 11.2.0.3. If somebody experienced similar issues, I give below the way how to install Sample Schemas manually. Read more »

How to install Oracle 11gR2 64-bit client on Windows 7

March 22, 2013 by 11 Comments 

Installation of Oracle 11gR2 64-bit client on Windows 7 is even trickier compared to Oracle 32-bit client installation. In fact after some series of installations, I do recommend installing Oracle client 32-bit on Windows 7 64-bit version, unless you have solid technical restrictions of that. This will avoid various installation issues as well as some client software incompatibility issues. I give however below a short installation plan for Oracle 11gR2 64-bit client with possible issues you can encounter during and after it. Read also my other article for some more tips on that: Oracle 11gR2 client installation on Windows 7 troubleshooting.
Read more »

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email