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 »
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 »
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.
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 »
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 220.127.116.11. If somebody experienced similar issues, I give below the way how to install Sample Schemas manually. Read more »
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 »