How to upgrade Oracle 10.2.0.1 to 10.2.0.2 and apply Jan’07 CPU on Ubuntu Linux

This task is done on a non critical test system on your Ubuntu machine. In real time environment, we should create a detailed plan for the upgrade. Before upgrade, the software/database/Database config files needs to be backed up.
Preinstallation Steps

oracle@labster:oracle@labster:~$ pwd
/home/oracle

oracle@labster:oracle@labster:~$ mkdir upgrade
oracle@labster: oracle@labster:~$ cd upgrade
oracle@labster:~/upgrade$ ls
p4547817_10202_LINUX.zip p5844311_10202_LINUX.zip
oracle@labster:~/upgrade$ ls -l

Script to track Database Changes

This script monitors your database environments for any newly changed objects, this will help DBAs to keep track of Database changes and also if you follow change control procedures.

#!/bin/sh
###################################################
#PROGRAM NAME: Weekly Reports for monitoring Database Environments
#WRITTEN BY : Milind
#Version: 1.0
#DATE : 07/12/2007
################PLEASE APPEND REVISION HISTORY BELOW##################
set -x
#Check Input And Print Usage.
NUMARGS=$#
if [ $NUMARGS != 1 ]
then
echo “Please review the script before running”

Example of Media Recovery using online redo logs

oracle@myserver.dbalifeline.com>mydb>sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.3.0 - Production on Fri May 23 11:46:40 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> @cr_ctl2
ORACLE instance started.

Total System Global Area 8556380160 bytes
Fixed Size 2086416 bytes
Variable Size 1358957040 bytes
Database Buffers 7180648448 bytes
Redo Buffers 14688256 bytes

Control file created.

SQL> alter database open;
alter database open
*
ERROR at line 1:

How to Install Oracle 11g on Linux

* Login to your Linux workstation using the primary account you created during install. (in my case it is geeko)
* Download the Oracle software to your Linux http://www.oracle.com/technology/products/database/oracle11g/index.html
* Install and configure the necessary packages/libraries

sudo apt-get install build-essential lesstif2-dev alien gcc rpm make binutils gawk alien lesstif2 libc6 libaio1 libstdc++5 libaio libc6-dev rpm libmotif3 libstdc++5 libg++2.8.1.3-glibc2.2 ksh gcc-3.3 g++-3.3 libstdc++5 gcc libaio1

Remote install of Oracle client in silent mode using responsefile on windows desktops without special software

Scope: Windows and Oracle client software install.

Objective:

1. The user clicks on a batch script (.bat) to initiate install.

2. Users dont have to download the oracle client software on their computers.

3. Make use of a shared drive and Response File.

Attached: Logfile from the silent installation process.

Procedure:

1. Download Oracle client software and uncompress it to a folder.

2. Open command prompt window and cd (change directory) to this directory.

3. Run the setup.exe from command prompt as :

setup.exe -record -destinationFile newResponseFile

Shell Script to generate a report for World Writable files on your server

Please find below the single script to run this report. I will include the logic to exclude/include the filesystems from this monitoring:

The file is also attached to this post.

#!/bin/sh
###########################################################
#PROGRAM NAME: REPORT TO FIND WORLD-WRITABLE FILES
#WRITTEN BY : Milind Sarambale
#Version: 1.0
#DATE : 07/20/2007
################PLEASE APPEND REVISION HISTORY BELOW##################
set -x
#Check Input And Print Usage.
NUMARGS=$#
if [ $NUMARGS != 0 ]
then
echo "Please review the script before running"

Script to Automate the AWR reports generation from the snaphots every 12 hours

Prior to running the shell script, please update the variables with the values specific to your environment. And update the paths to create logfiles/reports etc.

#!/bin/sh
####################################################
#PROGRAM NAME: AWR Reporter
#WRITTEN BY : Milind Sarambale
#Version: 1.2
#DATE : 07/12/2007
################PLEASE APPEND REVISION HISTORY BELOW##################
#DATE:07/23/2007 Milind –> Modified the check_alert function to check only changed alert log entries

Do you know that 11g now supports password case sensitivity?

Do you know that 11g now supports password case sensitivity? Well, yes for users other than SYS. Please see below

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Dec 12 15:30:15 2007

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> sho parameter sec

NAME TYPE VALUE
———————————— ———– ——————————
db_securefile string PERMITTED
optimizer_secure_view_merging boolean TRUE

RMAN TIPS : What tapes you need to recall ?

You are doing your rman backup to tape. And lets say that you are trying to restore the database that was backed up to tape. So, what tapes do we need to recall ? Here’s the query to find the tapes that you need to recall.

set heading off
column TAPE format a15 trunc
column LEVEL format 99999
column TYPE format a4 trunc
select SET_COUNT, MEDIA as "TAPE",
to_char(START_TIME, ‘MM/DD/YYYY HH24:MI:SS’) as "BACKUP TIME",
INCREMENTAL_LEVEL as "LEVEL", BACKUP_TYPE as "TYPE"
from RC_BACKUP_PIECE
where DB_KEY=

RMAN TIPS : Recatalog archives

Recatalog’ing your archivelogs :

Lets say your archivelog destination got full because there was a contention in your backups or whatever be the case. Now your database is hung and you had to move the archives to a different location. So, how do we backup those archives without moving them back to the originial location.
I’ve created a softlink to the files that are moved but that did not work. RMAN did not recognize the softlinks. Here’s what you need to do.
Step 1 :