Copy and Rename (Clone) a Database

The original DB: mydb1
The new DB: oradb1

1) Copy mydb1 data files and init.ora
a. Obtain db datafile names:

select file_name from dba_data_files
FILE_NAME
--------------------------------------------
/opt/app/oracle/oradata/mydb1/system01.dbf
/opt/app/oracle/oradata/mydb1/undotbs01.dbf
/opt/app/oracle/oradata/mydb1/user01.dbf


b. Shutdown the DB, cp and/or ftp above files to different location

cp above files to location(after shutdown the db):

/opt/app/oracle/oradata/oradb1/


c. Copy and edit init.ora for new database

Copy the initmydb1.ora to initoradb1.ora;

edit initoradb1.ora;

create bdump cdump udump etc directories


2) Create the script that will re-create the controlfile

sql> startup mount
sql> alter database backup controlfile to trace;


in USER_DUMP_DEST find the correct trace file,

create a script (cr_ctl.sql) from it;
change 'reuse' to 'set'; specify resetlogs

make any necessary edits to cr_ctl.sql, e.g. pathname etc

3) Create a password file under $ORACLE_HOME/dbs (could be optional)

% orapwd file=orapworadb1 password=oracle entries=5

(note: not necessary if using OS authentication method)


4) Set ORACLE_SID = oradb1

run the cr_ctl.sql script

% sqlplus /nolog
% connect / as sydba
% startup nomount

% @cr_ctl.sql -- database should be in startup in nomount mode

% create spfile from pfile; (not for 8i)

% alter database open resetlogs;

5) add a temp file

alter tablespace temp add tempfile '/opt/app/oracle/oradata/oradb1/temp01.dbf' size 30M


DBID problem fix: (ref: http://bmoshier.net/OracleStuff/ChangeDBID8i9i2.html )


Oracle 9i Release 2 (9.2.0.1)

In Oracle 9i Release 2 (9.2.0.1), Oracle provides the ability to change the DBID and/or DBNAME through a new feature, the DBNEWID utility, and works outside of RMAN. Complete documentation on this utility is in the Oracle 9i Database Utilities Release 2 (9.2) part number A96642-01 manual.

Oracle 8i

In Oracle 8i, Oracle provides the procedure: dbms_backup_recovery.zeroDbid for this purpose. While Oracle does not formally support it, Oracle note 174625.1 covers its usage.

If you are using RMAN and choose to use the following method based on Note 174625.1, please keep in mind Notes 105832.6 and 1062736.6 Should RMAN already know about the original and clone databases, you will need to deregister and re-register the databases with RMAN.

The basic concept is:

With the database in mount but NOT open mode. Use dbms_backup_recover.zeroDbid to zero the DBID. Recreate the control files to assign a new DBID to the database.

Step by Step Instructions

To Change an Oracle Database Identifier (DBID) for 8i

- Connect to the database in SYSDBA mode.

- Perform a clean shutdown. Do NOT abort or force the instance. This shutdown is the same as when you are going to do a cold / closed database backup. You may wish to perform a cold / closed database backup at this time, as a measure of protection.
shutdown immediate

- Startup the database in mount mode
statup mount

- Check the present (soon to be old) database ID (DBID)
select dbid, name
from v$database;

DBID NAME
---------- ---------
578456994 SNEAKERS

Elapsed: 00:00:00.20

- Generate the create controlfile statement into a trace file.
alter database backup controlfile to trace;

- Generate a new DBID using the zeroDbid function of dbms_backup_restore
execute dbms_backup_restore.zeroDbid(0)

- Get another clean shutdown.
shutdown normal

- Delete the controlfiles (Unix: rm; Windows erase).

- Start up the database in nomount mode.
startup nomount

- Recreate the control files modifying it for your database and possible new name (if this is a new clone). You can only use the instructions for recreating the controlfile (create controlfile).
Change from:

CREATE CONTROLFILE REUSE DATABASE "SNEAKERS" NORESETLOGS NOARCHIVELOG

Change to:

CREATE CONTROLFILE SET DATABASE RESETLOGS NOARCHIVELOG

Use only the CREATE CONTROLFILE command from this trace file. Remove all the other statements (e.g. recover, etc.)

- Open the database.
alter database open resetlogs;

- Check the new DBID. It should be different.
select dbid, name
from v$database;

DBID NAME
---------- ---------
689567005 SNEAKERS

Elapsed: 00:00:00.20

RMAN restore and recover to previous incarnation

(1) Startup the target database in nomount mode

otherwise may encounter: RMAN-20011: target database incarnation is not current in recovery catalog

(2) Find the incarnation key

RMAN> list incarnation;

(3) Reset datatabase

RMAN> reset database to incarnation <inc_key>

(4) Restore and recover database;

Remember to restore control file first;

You can backup not necessarily mean you can do recovery

Recently I have encountered difficult time to recover a test database. After several failure till 2:00 AM probably, finally I caught the meaning of the following error after speedy reading the document


RMAN-03023: executing command: recover(4)

RMAN-08060: unable to find archivelog

RMAN-08510: archivelog thread=1 sequence=8

RMAN-03026: error recovery releasing channel resources

RMAN-08031: released channel: d1

RMAN-08031: released channel: d2



For some reason, RMAN doesn't know there exits the archived log on the disk, by manually catalog several logs, problem solved:



catalog archivelog '/ora01/orabkup/GEN3Q/arch/GENQA/arch0000000008.arc';

catalog archivelog '/ora01/orabkup/GEN3Q/arch/GENQA/arch0000000009.arc';

Procedure of using RMAN duplicate command to create a new GEN3C from the backup of GEN3S

Procedure of using RMAN duplicate command to create a new GEN3C from the backup of GEN3S

Reference

MetaLink Note 73912.1: RMAN: Creating a Duplicate Database

Prerequisite:

GEN3C and GEN3S are located in the same server, there are enough disk space.

Procedure:

1. Obtain a backup of GEN3S

# cold_backup.sh

Notes: As GEN3S is operated in nonarchivelog mode, only rman cold backup is possible

Script location: /ora01/orabkup/GEN3S/rman/scripts

=============== start of script cold_backup.sh========= ======

#!/usr/bin/ksh

# backup for database

# Denis Sun, 8/4/2006

. /oracle/.profile_old

export ORACLE_SID=GEN3S

. oraenv ${ORACLE_SID}

LOGDATE=`date +\%m\-%d\-\%Y\_\%H\:\%M`

LOG=/ora01/orabkup/GEN3S/rman/log/rman_cold_${ORACLE_SID}_${LOGDATE}.log

START_TIME=`date`

$ORACLE_HOME/bin/sqlplus /nolog <<EOD

connect / as sysdba

shutdown immediate

startup mount

exit

EOD

echo " Now in mount state"

$ORACLE_HOME/bin/rman target / nocatalog cmdfile cold_backup_disk.rmn log $LOG

echo " finish: `date` " tee -a $LOG

echo " start : ${START_TIME} " tee -a $LOG

echo "Examine RMAN log at $LOG for any error."

~

============== end of script cold_backup.sh===============================

============== start of script cold_backup_disk.rmn ===========

run {

# allocate channel. Important: this must be done after

# database has been mounted !!!

# cold backup to disk destination

allocate channel d1 type disk;

allocate channel d2 type disk;

backup

format '/ora01/orabkup/GEN3S/rman/rman_cold_%d_%p_%s.dbf'

database include current controlfile;

sql 'alter database open';

release channel d1;

release channel d2;

}

============== end of script cold_backup_disk.rmn ===========

2. Copy and edit init.ora file

If initGEN3C.ora does not exist (not in this case), we need to copy from initGEN3S.ora. The parameters such as

db_name

instance_name

control_files

*_dump

etc should be modified accordingly

3. Create a RMAN script file: dup_gen3c.rmn which specifies the new names of all data files and redo log file:

First, the datafile name , log files group and member size of GEN3S should be obtained in order to edit this script:

====================== start of dup_gen3c.rmn ========================

run {

# allocate at least one auxiliary channel of type disk or tape

allocate auxiliary channel c1 type disk;

allocate auxiliary channel c2 type disk;

# set new filenames for the datafiles

set newname for datafile 1 to '/ora02/oradata/GEN3C/gen3c_system01.dbf';

set newname for datafile 2 to '/ora02/oradata/GEN3C/gen3c_audit_rdbs01.dbf';

set newname for datafile 3 to '/ora01/oradata/GEN3C/gen_data_01.dbf';

set newname for datafile 4 to '/ora02/oradata/GEN3C/gen_data_02.dbf';

set newname for datafile 5 to '/ora01/oradata/GEN3C/gen_data_03.dbf';

set newname for datafile 6 to '/ora02/oradata/GEN3C/gen_data_04.dbf';

set newname for datafile 7 to '/ora01/oradata/GEN3C/gen_data_05.dbf';

set newname for datafile 8 to '/ora02/oradata/GEN3C/gen_data_06.dbf';

set newname for datafile 9 to '/ora01/oradata/GEN3C/gen_data_07.dbf';

set newname for datafile 10 to '/ora02/oradata/GEN3C/gen_data_08.dbf';

set newname for datafile 11 to '/ora01/oradata/GEN3C/gen_data_09.dbf';

set newname for datafile 12 to '/ora02/oradata/GEN3C/gen_data_10.dbf';

set newname for datafile 13 to '/ora01/oradata/GEN3C/gen_data_11.dbf';

set newname for datafile 14 to '/ora01/oradata/GEN3C/gen_data_12.dbf';

set newname for datafile 15 to '/ora01/oradata/GEN3C/gen_indx_01.dbf';

set newname for datafile 16 to '/ora02/oradata/GEN3C/gen_indx_02.dbf';

set newname for datafile 17 to '/ora01/oradata/GEN3C/gen_indx_03.dbf';

set newname for datafile 18 to '/ora02/oradata/GEN3C/gen_indx_04.dbf';

set newname for datafile 19 to '/ora01/oradata/GEN3C/gen_indx_05.dbf';

set newname for datafile 20 to '/ora02/oradata/GEN3C/gen_indx_06.dbf';

set newname for datafile 21 to '/ora01/oradata/GEN3C/gen_indx_07.dbf';

set newname for datafile 22 to '/ora02/oradata/GEN3C/gen_indx_08.dbf';

set newname for datafile 23 to '/ora01/oradata/GEN3C/genp_indx01.dbf';

set newname for datafile 24 to '/ora02/oradata/GEN3C/genp_rbs01.dbf';

set newname for datafile 25 to '/ora01/oradata/GEN3C/genp_rbs02.dbf';

set newname for datafile 26 to '/ora01/oradata/GEN3C/genp_temp01.dbf';

set newname for datafile 27 to '/ora02/oradata/GEN3C/genp_temp02.dbf';

set newname for datafile 28 to '/ora01/oradata/GEN3C/genp_tools01.dbf';

set newname for datafile 29 to '/ora02/oradata/GEN3C/genp_users01.dbf';

set newname for datafile 30 to '/ora01/oradata/GEN3C/gen_rep_data_01.dbf';

set newname for datafile 31 to '/ora02/oradata/GEN3C/gen_rep_data_02.dbf';

set newname for datafile 32 to '/ora01/oradata/GEN3C/gen_rep_data_03.dbf';

set newname for datafile 33 to '/ora02/oradata/GEN3C/gen_rep_data_04.dbf';

# issue the duplicate command

duplicate target database to GEN3C

logfile

group 1 ('/ora01/oraredo/GEN3C/gen3c_redo01a.log','/ora02/oraredo/GEN3C/gen3c_redo01b.log') size 52428800,

group 2 ('/ora01/oraredo/GEN3C/gen3c_redo02a.log','/ora02/oraredo/GEN3C/gen3c_redo02b.log') size 52428800,

group 3 ('/ora01/oraredo/GEN3C/gen3c_redo03a.log','/ora02/oraredo/GEN3C/gen3c_redo03b.log') size 52428800,

group 4 ('/ora01/oraredo/GEN3C/gen3c_redo04a.log','/ora02/oraredo/GEN3C/gen3c_redo04b.log') size 52428800,

group 5 ('/ora01/oraredo/GEN3C/gen3c_redo05a.log','/ora02/oraredo/GEN3C/gen3c_redo05b.log') size 52428800,

group 6 ('/ora01/oraredo/GEN3C/gen3c_redo06a.log','/ora02/oraredo/GEN3C/gen3c_redo06b.log') size 52428800;

release channel c1;

release channel c2;

}

====================== end of dup_gen3c.rmn ========================

4. Confirm that we can start the duplicate instance (GEN3C)

i. set ORACLE_HOME and ORACLE_SID

ii. create a password file as needed

iii. start the instance in nomount state

5. configure and check SQL* Net connectivity

In this case, we already have GEN3S and GEN3C service name in the tnsname.ora.

# tnsping gen3s

# tnsping gen3c

6. Starting the duplication

# .oraenv GEN3C

# dup_gen3c.sh

================== start of dup_gen3c.sh ===============================

#!/usr/bin/ksh

# Program name: dup_gen3c.sh

# This script is to create a duplicate database called GEN3C from GEN3S

# before executing this script, the GEN3C instance should be up (in nomount)

# i.e.

# SQL> conn / as sysdba

# SQL> startup nomount

. /oracle/.profile_old

. oraenv GEN3C

export ORACLE_SID=GEN3C

export ORACLE_HOME=/oracle/product/8.1.7.4

LOGDATE=`date +\%m\-%d\-\%Y\_\%H\:\%M`

LOG=/ora01/orabkup/GEN3S/rman/log/rman_dup_${ORACLE_SID}_${LOGDATE}.log

START_TIME=`date`

# rm datafiles of GEN3C

rm /ora02/oradata/GEN3C/*.*

rm /ora01/oradata/GEN3C/*.*

sqlplus /nolog <<_EOF_

connect / as sysdba

shutdown abort;

startup nomount;

_EOF_

${ORACLE_HOME}/bin/rman target sys/strik3out@gen3s nocatalog auxiliary / cmdfile=dup_gen3c.rmn log=${LOG}

echo "End rman duplicate : `date`" >> $LOG

echo "Start rman duplicate: ${START_TIME}" >> $LOG

======================== end of dup_gen3c.sh ==========================

Notes:

(a) RMAN backup of GEN3C using 2 channels takes about 10 min

Recovery Manager complete.

finish: Wed Dec 13 18:46:03 CST 2006

start : Wed Dec 13 18:36:16 CST 2006

(b) RMAN duplicate job using 2 channels takes about 10 min

Recovery Manager complete.

End rman duplicate : Wed Dec 13 19:49:46 CST 2006

Start rman duplicate: Wed Dec 13 19:39:09 CST 2006

(c) The password of GEN3C user schema will be same as those of GEN3S

(d) After creating the GEN3C, find the global_name value is GEN3S, issue following statement to change global_name:

alter database rename global_name to GEN3C.WORLD

SQL> select * from global_name;

GLOBAL_NAME

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

GEN3C.WORLD

Applying DDL on replicated tables - A Test Case

Database DBTESTA and DBTESTB are configured with Oracle Advanced Replication. This test shows that we can not apply DDL directly to the replcated tables from SQL Plus command line.

(1) RG_MYGROUP is a replication group, including two replicated tables: tab1 and tab2

repadmin@DBTESTA> select sname, oname, status from dba_repobject where
gname='RG_MYGROUP';

SNAME ONAME STATUS
------------------------------ ------------------------------ ---------
MYSCHA TAB1 VALID
MYSCHA TAB1$RP VALID
MYSCHA TAB1$RP VALID
MYSCHA TAB2 VALID
MYSCHA TAB2$RP VALID
MYSCHA TAB2$RP VALID

(2) DML operation works:

************** DML at DBTESTA ************
myscha@DBTESTA> select * from tab2;

no rows selected

myscha@DBTESTB> select * from tab2;

no rows selected

myscha@DBTESTA> insert into tab2 values(1,2);

1 row created.

myscha@DBTESTA> commit;

Commit complete.

myscha@DBTESTA> select * from tab2;

A B
---------- ----------
1 2

**************** What happend at DBTESTB **********
myscha@DBTESTB> select * from tab2;

A B
---------- ----------
1 2

Note: replication works properly !

(3) Applying DDL through SQL Plus is NOT good for the replication

************** DDL at DBTESTA and DBTESTB *******

myscha@DBTESTA> alter table tab2 add (c number);

Table altered.

myscha@DBTESTA> select * from tab2;

A B C
---------- ---------- ----------
1 2
myscha@DBTESTB> alter table tab2 add (c number);

Table altered.

myscha@DBTESTB> select * from tab2;

A B C
---------- ---------- ----------
1 2

*************After applying DDL, DML at DBTESTA *******

myscha@DBTESTA> insert into tab2 values(2,3,4);

1 row created.

myscha@DBTESTA> commit;

Commit complete.

myscha@DBTESTA> select * from tab2;

A B C
---------- ---------- ----------
1 2
2 3 4

myscha@DBTESTA> insert into tab2 values(3,4,5);

1 row created.

myscha@DBTESTA> commit;

Commit complete.

myscha@DBTESTA> select * from tab2;

A B C
---------- ---------- ----------
1 2
2 3 4
3 4 5

************* what happened at DBTESTB ? **************

myscha@DBTESTB> select * from tab2;

A B C
---------- ---------- ----------
1 2
2 3


myscha@DBTESTB> select * from tab2;

A B C
---------- ---------- ----------
1 2
2 3
3 4

Note: replication does not work for colume C !!!!!


(4) what's the solution?

a. drop the tab2 from the RG_MYGROUP
repadmin@DBTESTA> @dr_ro

PL/SQL procedure successfully completed.

repadmin@DBTESTA> select sname, oname, status from dba_repobject where
gname='RG_MYGROUP';

SNAME ONAME STATUS
------------------------------ ------------------------------ ---------
MYSCHA TAB1 VALID
MYSCHA TAB1$RP VALID
MYSCHA TAB1$RP VALID

b. apply ddl to it at both DBTESTA and DBTESTB
alter table tab2 add (d number);

c. add tab2 to the RG_MYGROUP
resume the replicaiton

d. verify it works
*** befor apply DML

myscha@DBTESTA> select * from tab2;

A B C D
---------- ---------- ---------- ----------
1 2
2 3 4
3 4 5

myscha@DBTESTB> select * from tab2;

A B C D
---------- ---------- ---------- ----------
1 2
2 3
3 4

**** after DML
myscha@DBTESTA> insert into tab2 values(4,5,6,7);

1 row created.

myscha@DBTESTA> commit;

Commit complete.

myscha@DBTESTA> select * from tab2;

A B C D
---------- ---------- ---------- ----------
1 2
2 3 4
3 4 5
4 5 6 7

myscha@DBTESTB> select * from tab2;

A B C D
---------- ---------- ---------- ----------
1 2
2 3
3 4
4 5 6 7

Solaris Performance Monitoring & Tuning - iostat , vmstat & netstat

This is a reading note from an article on: http://www.adminschoice.com/docs/iostat_vmstat_netstat.htm

iostat
=========
iostat -xtc 5 2

The values to look from the iostat output are:

* Reads/writes per second (r/s , w/s)
* Percentage busy (%b)
* Service time (svc_t)

If a disk shows consistently high reads/writes along with , the percentage busy (%b) of the disks is greater than 5 percent, and the average service time (svc_t) is greater than 30 milliseconds, then one of the following action needs to be taken

1.)Tune the application to use disk i/o more efficiently by modifying the disk queries and using available cache facilities of application servers .

2.) Spread the file system of the disk on to two or more disk using disk striping feature of volume manager /disksuite etc.

3.) Increase the system parameter values for inode cache , ufs_ninode , which is Number of inodes to be held in memory. Inodes are cached globally (for UFS), not on a per-file system basis

4.) Move the file system to another faster disk /controller or replace existing disk/controller to a faster
one.

vmstat
========
A. CPU issues:

Following columns has to be watched to determine if there is any cpu issue

* Processes in the run queue (procs r)
* User time (cpu us)
* System time (cpu sy)
* Idle time (cpu id)

Problem symptoms:
-----------------
1.) If the number of processes in run queue (procs r) are consistently greater than the number of CPUs on the system it will slow down system as there are more processes then available CPUs .

2.) if this number is more than four times the number of available CPUs in the system then system is facing shortage of cpu power and will greatly slow down the processess on the system.

3.) If the idle time (cpu id) is consistently 0 and if the system time (cpu sy) is double the user time (cpu us) system is facing shortage of CPU resources.

B. Memory Issues:

Memory bottlenecks are determined by the scan rate (sr) . The scan rate is the pages scanned by the clock algorithm per second. If the scan rate (sr) is continuously over 200 pages per second then there is a memory shortage

netstat
=======
netstat -rn

Practice LogMiner on an Oracle 8i Database

Steps:

1. Edit init.ora to include the following parameter 
   utl_file_dir=/ora01/orabkup/GENQA/utl

2. Verify utl_file_dir 
   SQL>connect / as sysdba
   SQL>show parameter utl
    
   NAME                TYPE    VALUE
   ------------------- ------- ------------------------------
   utl_file_dir        string  /ora01/orabkup/GENQA/utl

3. create logminer packages (As SYS)

   SQL>@?/rdbms/dbmslmd.sql
   Package created.
   Package body created.
   No errors.
   Grant succeeded.

4. Perform some user activities

   SQL> select sysdate from dual;
   17-JAN-2007 11:53:44

   SQL> conn nbk9lsj/nbk9lsj
   SQL> create table t1 as select * from all_objects where rownum < 3;
   SQL> update t1 set owner=lower(owner); 
   SQL> update t2 set object_id = object_id +9000;
   SQL> commit;

   SQL> select sysdate from dual;
   17-JAN-2007 11:58:09

5. Use LogMiner (conn as sysdba)
5.1 Create a log miner dictionary file:

   execute sys.dbms_logmnr_d.build( -
   dictionary_filename =>'mylog_dict.dic', -
   dictionary_location => '/ora01/orabkup/GENQA/utl');

5.2. Create a list of logs
   execute dbms_logmnr.add_logfile( -
   logfilename => '/ora01/oraredo/GENQA/genqa_redo01a.log', -
   options => dbms_logmnr.new);

   execute dbms_logmnr.add_logfile( -
   logfilename => '/ora01/oraredo/GENQA/genqa_redo02a.log', -
   options => dbms_logmnr.addfile);

   execute dbms_logmnr.add_logfile( -
   logfilename => '/ora01/oraredo/GENQA/genqa_redo03a.log', -
   options => dbms_logmnr.addfile);

   Note: this database has only three onling log files. Add them all to the list

5.3 Start the log miner

   SQL> execute dbms_logmnr.start_logmnr( -
   dictfilename => '/ora01/orabkup/GENQA/utl/mylog_dict.dic', -
   starttime => to_date('17-JAN-2007 11:53:44', 'DD-MON-YYYY HH:MI:SS'), - 
   endtime => to_date('17-JAN-2007 11:58:09', 'DD-MON-YYYY HH:MI:SS')); 

5.4 View the output via the v$logmnr_contents table. .
   col timestamp format a11
   col sql_redo format a30
   col sql_undo format a30   
   SQL> select timestamp,sql_redo, sql_undo
   from v$logmnr_contents
   where username='NBK9LSJ';


   TIMESTAMP   SQL_REDO                       SQL_UNDO
   ----------- ------------------------------ ------------------------------
   17-JAN-2007 set transaction read write;
    11:56:56
    
   17-JAN-2007 update "NBK9LSJ"."T1" set "OWN update "NBK9LSJ"."T1" set "OWN
    11:56:56   ER" = 'sys' where ROWID = 'AAA ER" = 'SYS' where ROWID = 'AAA
        CmjAAaAAAAADAAA';              CmjAAaAAAAADAAA';
    
   17-JAN-2007
    11:56:56
    
   17-JAN-2007 update "NBK9LSJ"."T1" set "OWN update "NBK9LSJ"."T1" set "OWN
    11:56:56   ER" = 'sys' where ROWID = 'AAA ER" = 'SYS' where ROWID = 'AAA
        CmjAAaAAAAADAAB';              CmjAAaAAAAADAAB';
    
   17-JAN-2007 update "NBK9LSJ"."T1" set "OBJ update "NBK9LSJ"."T1" set "OBJ
    11:57:27   ECT_ID" = 91290 where ROWID =  ECT_ID" = 1290 where ROWID = '
        'AAACmjAAaAAAAADAAA';          AAACmjAAaAAAAADAAA';
    
   17-JAN-2007 update "NBK9LSJ"."T1" set "OBJ update "NBK9LSJ"."T1" set "OBJ
    11:57:27   ECT_ID" = 91251 where ROWID =  ECT_ID" = 1251 where ROWID = '
        'AAACmjAAaAAAAADAAB';          AAACmjAAaAAAAADAAB';
    
   17-JAN-2007 commit;
    11:57:28
    
5.5 End the log miner
   SQL> execute dbms_logmnr.end_logmnr;

Unix shell scripts used extensively through cron

In interviews, I have been frequently asked about unix shell scripting skills. In my working environment, shell scripts are extensively used to monitor databases or perform some repetitive tasks through cron job. The following shows the crontab from a production server which host several databases ( I only show the part of the crontab related to one database PRODDB)

------
Mornitoring PRODDB shell script from crontab:
# ---------- Monitoring PRODDB
00,05,10,15,20,25,30,35,40,45,50,55 * * * * /u01/app/oracle/local/bin/alert_err_check.ksh PRODDB >/dev/null 2>&1
00,04,08,12,16,20,24,28,32,36,40,44,48,52,56 * * * * /u01/app/oracle/local/bin/monitor_lock.ksh PRODDB 2>&1 1>/dev/null
04,09,14,19,24,29,34,39,44,49,54,59 * * * * /u01/app/oracle/local/bin/snap_top_5_buffer_hogs.ksh PRODDB 2>&1 1>/dev/null
00 06 * * * /u01/app/oracle/local/bin/monitor_ts_avg_used.ksh PRODDB 2>&1 1>/dev/null
00 * * * * /u01/app/oracle/local/bin/snap_wait_types.ksh PRODDB 2>&1 1>/dev/null
02 * * * * /u01/app/oracle/local/bin/total_session_waits.ksh PRODDB 2>&1 1>/dev/null
01 * * * * /u01/app/oracle/local/bin/high_session_waits.ksh PRODDB 2>&1 1>/dev/null
00 04 * * 0 /u01/app/oracle/local/bin/snap_td_object_sizes.ksh PRODDB 2>&1 1>/dev/null
00 04 * * * /u01/app/oracle/local/bin/snap_td_data_files.ksh PRODDB 2>&1 1>/dev/null
#00,15,30,45 * * * * /u01/app/oracle/local/bin/collector_statspack.ksh PRODDB 2>&1 1>/u01/app/oracle/admin/PRODDB/logbook/PRODDB.collector_statspack.log
05 06 * * * /u01/app/oracle/local/bin/daily_statspack_cleanup.ksh PRODDB 2>&1 1>/dev/null
30 05 * * 1-6 /u01/app/oracle/admin/PRODDB/create/statspack/get_object_stats.ksh PRODDB > /u01/app/oracle/admin/PRODDB/create/statspack/log/get_object_stats.cronlog 2>/u01/app/oracle/admin/PRODDB/create/statspack/log/get_object_stats.cronerrorlog
#54 10 * * * /u01/app/oracle/admin/PRODDB/create/security/auditing/oracle_auditfiles.ksh PRODDB AuditParms.par 2>/u01/app/oracle/admin/PRODDB/create/security/auditing/oracle_auditfiles.cronerrorlog 1>/u01/app/oracle/admin/PRODDB/create/security/auditing/oracle_auditfiles.cronlog
31 00 * * * /u01/app/oracle/admin/PRODDB/create/scripts/monitor/sessions.ksh PRODDB > /u01/app/oracle/admin/PRODDB/create/scripts/monitor/sessions_cron.log
# --------- PRODDB and AVANTIP Filesystem Space monitoring
50 04 * * * /u01/app/oracle/admin/PRODDB/create/scripts/df_k.sh 2>&1 1> /u01/app/oracle/admin/PRODDB/create/scripts/log/df_k.cronlog
30 12 * * * /u01/app/oracle/admin/PRODDB/create/scripts/df_k.sh 2>&1 1> /u01/app/oracle/admin/PRODDB/create/scripts/log/df_k.cronlog
# --------- PRODDB LOCK/UNLOCK GENSCRUB
#00 15 * * 5 /u01/app/oracle/admin/PRODDB/create/scripts/lock_genscrub.sh 2>&1 1>/u01/app/oracle/admin/PRODDB/create/scripts/log/lock_genscrub.log
#30 11 * * 5 /u01/app/oracle/admin/PRODDB/create/scripts/unlock_genscrub.sh 2>&1 1>/u01/app/oracle/admin/PRODDB/create/scripts/log/unlock_genscrub.log
# -------- PRODDB replication
#40 * * * * /u01/app/oracle/admin/PRODDB/create/scripts/push_repqueue.sh PRODDB 2>&1 1>/u01/app/oracle/admin/PRODDB/create/scripts/log/push_repqueue.cronlog
50 04 * * * /u01/app/oracle/admin/PRODDB/create/scripts/mv_audit.sh 2>&1 1>/u01/app/oracle/admin/PRODDB/create/scripts/log/mv_audit.cronlog
#01 00 * * * /u01/app/oracle/admin/PRODDB/create/scripts/chk_repjob.sh PRODDB 2> /u01/app/oracle/admin/PRODDB/create/scripts/log/chk_dba_jobs_cron.log
##00 * * * * /u01/app/oracle/admin/PRODDB/create/scripts/gen_repjob_rsv.sh 2> /u01/app/oracle/admin/PRODDB/create/scripts/log/cron_gen_repjob_rsv.log
01,16,31,46 * * * * /oracle/admin/PRODDB/create/rep/monitor/chk_reperrPRODDB.sh PRODDB 2>&1 1> /oracle/admin/PRODDB/create/rep/monitor/log/run_chk_reperrPRODDB.log
# -------- PRODDB and GENARCHP misc
# 02 01 05 * * /oracle/local/maint/cut_alert_log.sh PRODDB
00 * * * * /u01/app/oracle/local/bin/stby_sync_check.ksh AVANTIP > /u01/app/oracle/admin/PRODDB/create/scripts/log/stby_sync_check.cronlog 2>&1
00 05 * * * /u01/app/oracle/local/bin/stby_sync_check_notify.ksh AVANTIP > /u01/app/oracle/admin/PRODDB/create/scripts/log/stby_sync_check_notify.cronlog 2>&1
00 06 * * * perl -S /u01/app/oracle/local/bin/cleandir.pl /u01/app/oracle/admin/PRODDB/logbook 35 /u01/app/oracle/local/logbook/ 2>&1 1>/dev/null
00 06 * * * perl -S /u01/app/oracle/local/bin/cleandir.pl /u01/app/oracle/dba/output/PRODDB 10 /u01/app/oracle/dba/output/ 2>&1 1>/dev/null
01 05 * * * perl -S /u01/app/oracle/local/bin/cleandir.pl /oracle/admin/PRODDB/create/rep/monitor/log 5 /u01/app/oracle/local/logbook/ 2>&1 1>/dev/null
00 * * * * perl /oracle/dba/local/bin/space_alert.pl PRODDB /oracle/admin/PRODDB/local/parm/PRODDB_arch.par 50 alert
00 * * * * perl /oracle/dba/local/bin/space_alert.pl PRODDB /oracle/admin/PRODDB/local/parm/PRODDB_data.par 80 alert
00 * * * * /u01/app/oracle/admin/PRODDB/create/scripts/switch_log.ksh PRODDB 2>&1 > /u01/app/oracle/admin/PRODDB/create/scripts/log/switch_log_PRODDB.cronlog