RandyDBA | Database - Tips & Techniques

Automating RMAN backup Jobs with CRON utility DBAs often debate whether they should use Oracle Scheduler or the Linux/Unix cron utility for scheduling and automating tasks. There are many benefits that Oracle Scheduler  has over cron, but yet despite these benefits, many DBAs prefer to use a scheduling utility such as cron to automate many tasks. There are some advantages of cron:- Easy to use, simple, tried and true, only takes seconds to create or modify jobs. Almost universally available on all Linux/Unix boxes; for the most part, runs nearly identically, regardless of the Linux/Unix platform (yep!, there are minor differences) Database agnostic; operates independently of the database and works the same, regardless of the database vendor or version. Works whether or not the database is available. When your Linux server boots up, a cron background process is automatically started to manage all the cron jobs in the  system. On your Linux box, you can check...

Sometimes as a DBA you may find yourself in a database server environment without any graphical interface and you are forced to use the command line to setup the network. The procedure below shows you how to do that:- You setup a dynamic IP by editing the file: /etc/sysconfig/network-scripts/ifcfg-eth0 as root user It should look like this: DEVICE=eth0 HWADDR=08:00:27:37:A4:23 -------- ( ! replace with your network device macc addr ) TYPE=Ethernet UUID=6c8852cb-7819-46a3-9d ------- ( ! must not be the same) ONBOOT=yes --- ( ! very important) NM_CONTROLLED=yes BOOTPROTO=dhcp For static ip edit the same file if you are using the device eth0 and just add the following: DEVICE=eth0 BOOTPROTO=STATIC IPADDR=192.168.0.5 NETMASK=255.255.255.0 GATEWAY=192.168.0.1 ONBOOT=yes After saving the changes, you need to restart the network daemon using the following command:- $ sudo /etc/init.d/network stop $ sudo /etc/init.d/network start This should provide IP address...

It is sometime desirable by most DBAs to have the oracle database and listener automatically shutdown and startup when the server reboots. The following steps shows you how to automate your database and listener shutdown and startup. Please note that this demonstration is linux centric (I am working on OEL 6.8), so if you are working with a different Unix platform, refer to the Oracle Database Admin’s Guide. 1. Edit the /etc/oratab file, and place a Y at the end of the entry for the database you want to restart automatically when the system reboots. You will need root priv to edit the file: 2. Paste within the file a line similar to the one below, for your environment: db11g:/u01/app/oracle/product/11.2.0/dbhome_1:Y Note: The entries are of the form: #   $ORACLE_SID:$ORACLE_HOME:N|Y: 3. As root, navigate to the /etc/init.d directory, and create a file named dbora: Paste the following lines in the dbora file. Make sure you...

There are various aspects of implementing and maintaining indexes. The table below summarizes some to the guidelines and techniques for creating indexes. Guideline Reasoning Create as many indexes as you need, but try to keep the number to a minimum. Add indexes judiciously. Test first to determine quantifiable performance gains. Indexes increase performance, but also consume disk space and processing resources. Don’t add indexes unnecessarily. The required performance of queries you execute against a table should form the basis of your indexing strategy. Indexing columns used in SQL queries will help performance the most. Consider using the SQL Tuning Advisor or the SQL Access Advisor for indexing recommendations. These tools provide recommendations and a second set of "eyes" on your indexing decisions. Create primary key constraints for all tables. This will automatically create a B-tree index (if the colu...

Some key differences for DBAs between Oracle and MySQL database servers include: Oracle architecture is process based, MySQL architecture is thread based. Different tools used for backup and recovery. Database specific SQL syntax. Database specific SQL functions. Different  syntax for stored routines. MySQL has no packages. MySQL  routines are not compiled and run in each session thread and not in global memory. MySQL  only supports row-level triggers. Different  tools used to manage and monitor database servers. Different  startup and shutdown processes. Oracle  RAC is a shared disk solution while MySQL Cluster is a shared nothing solution. The  default configuration for MySQL is very lenient in terms of data integrity. A MySQL DBA must tighten down data integrity for it to work like traditional databases. With  Oracle, the CREATE DATABASE command is used to create the physical storage for...