Tuesday, March 31, 2009

Recovery Catalog Creation

hello guys,

Today we are going to discuss the step-by-step procedure to create recovery catalog for your database.


This discussion describes how to create an RMAN recovery catalog, which holds RMAN repository data for one or more databases in a separate database schema, in addition to using the control files of the databases.


If you are creating your recovery catalog in an already-existing database, add enough room to hold the default tablespace to the recovery catalog schema. If you are creating a new database to hold your recovery catalog, then, in addition to the space for the recovery catalog schema itself, you must allow space for other files in the recovery catalog database:

SYSTEM tablespace
Temporary tablespaces
Rollback segment tablespaces
Online redo log files

Assume that we are having our primary database MKTG3, and other database RCAT on which we are going to crate recovery catalog.

You should not install the catalog in the target database: this tactic defeats the purpose of the catalog. Also, decide whether to operate the catalog database in ARCHIVELOG mode, which is recommended.

the process of creating recovery catalog owner is as follows:-

After choosing the recovery catalog database and creating necessary space, you are ready to create the owner of the recovery catalog and grant this user necessary privileges.

To create the recovery catalog schema in the recovery catalog database:

1) connect to the database with administrator privilages containing the recovery catalog:

conn sys/admin@rcat

2) create a user and schema for recovery catalog

create user recovery identified by catalog
temporary tablespace
default tablespace
quota unlimited on ;

3) grant the recovery_catalog_owner role to schema owner.

sql> grant recovery_catalog_owner to recovery;

4)make necessary changes to tnsnames.ora and listener.ora files and configure listeners if you are using multiple listener to proivde services to this standby database.

5) grant RECOVRY TYPE privilages to recovery catalog owner

sql> grant create type to recovery;

6) connect to the database that will contain catalog,as catalog owner

% rman catalog recovery/catalog@rcat

you can also connect from RMAN prompt

RMAN> connect catalog recovery/catalog@rcat

7) run the CREATE CATALOG command to create catalog.

RMAN> create catalog;

8) Optionally, start SQL*Plus and query the recovery catalog to see which tables were created:

sql> SELECT TABLE_NAME FROM USER_TABLES;


Registering a Database in the Recovery Catalog:-

The first step in using a recovery catalog with a target database is registering the database in the recovery catalog. Use the following procedure:

1)after making sure the recovery catalog database is open,connect RMAN to the target database.

RMAN> connect target sys/admin@mktg3

2) to register to recovery catalog the target database must be mounted or open.

3) then connect to the recovery catalog

RMAN> connect catalog recovery/catalog@rcat

4) Register the target database in the connected recovery catalog:

RMAN> register database;


RMAN creates rows in the catalog tables to contain information about the target database, then copies all pertinent data about the target database from the control file into the catalog, synchronizing the catalog with the control file.


Verify that the registration was successful by running REPORT SCHEMA:

RMAN> REPORT SCHEMA;

These are the steps to crate recovery catalog... Hope this will help you in using RMAN more efficiently.

Thursday, March 19, 2009

Oracle Control Files

Hi all,

Every person has a friend, a very good friend. who knows everything about that person. and this friend is very crucial to that person.same way oracle control file is also very crucial to oracle database. It is needed to mount the database.

Oracle control file stores the complete information about the physical structure of database and and it's status. oracle control file contains following information about oracle database, but not limited to it:

  • Database information (RESETLOGS SCN and their time stamp)
  • Archive log history
  • Tablespace and datafile records (filenames, datafile checkpoints, read/write status, offline or not)
  • Redo threads (current online redo log)
  • Database's creation date
  • database name
  • current archive log mode
  • Log records (sequence numbers, SCN range in each log)
  • RMAN catalog
  • Database block corruption information
  • Database ID, which is unique to each DB

This way control file contain contain almost all information about oracle database. The control file must be available for writing by the oracle database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.

The control file is created at the time of database creation on the location specified by initialization parameter file. It is recommended that you should multiplex your control file on same host or another machine, because you might need them in case of recovery.

To backup the control file in running instance, execute following command as sys user:

SQL> alter database backup controlfile to trace;

or if you want to backup controlfile in user readable format then execute

SQL> alter database backup controlfile to '';

you can multiplex control files with operating system copy command, but for this purpose you have to shut down your database. Once the controlfile are multiplexed, all we be synchronized by oracle if some changes are made to database. follow the following steps to multiplex the control files on you oracle host:

1.shut down database.
2. copy existing control files to the multiplexing location with operating system copy command. ('copy' in windows and 'cp' in Linux).
3. make entries in initialization parameter file, edit the control_files parameter specifying path newly multiplexed control files.
4. recreate server parameter (sp file) file from newly edited parameter file.
5. open the database.
6. check with view v$controlfile, whether multiplexed controlfile are there in list.

This way you can multiplexed your control files. If lost in worst situation you can recreate your control file for disaster recovery. following script will explain how to recreate your control file.

startup nomount
create controlfile reuse database 'dbname' noresetlogs noarchivelog/archivelog
maxlogfiles 30
maxlogmembers 5
maxdatafiles 30
maxinstances 1
maxloghistory 500
logfile
group 1 '/uo1/oracle/dbs/redo01.log' size 100m,
group 2 'u01/oracle/dbs/redo02.log' size 100m
datafile
'/u01/oracle/dbs/system01.dbf',
'/u01/oracle/dbs/tools01.dbf',
'/u01/oracle/dbs/index01.dbf',
'/u01/oracle/dbs/users01.dbf',
'/u01/oracle/dbs/undotbs01.dbf',
'/u01/oracle/dbs/fin01.dbf',
add temporary tablespace temp
tempfile ''/u01/oracle/dbs/temp01.dbf',
character set WE8DEC
;

This way you can create your control file in needed. but always backup and multiplex control files for safe reasons.





Thursday, March 5, 2009

Installing Oracle 10g R2 on RHEL 5

Hi guyes,

This article will discuss the installation process to Install Oracle Database 10g R2 on Red Hat Enterprise Linux 5 (RHEL5).

This article is based on server installation, with SE linux disabled and following group packages installaed:

* GNOME Desktop Environment
* Editors
* Graphical Internet
* Text-based Internet
* Development Libraries
* Development Tools
* Legacy Software Development
* Server Configuration Tools
* Administration Tools
* Base
* Legacy Software Support
* System Tools
* X Window System

The following steps instruct you to install oracle and set kernel parameter required to work oracle safely.

Download database software:- Download database software for oracle 10g R2 from OTN or Metalink.

Unzip the files in a directory

tar -zxvf 10201_database_linux.tar.gz

Say we are unzipping this tar file in /temp/oracle directory.

Edit the /etc/host file by adding following lines with specifications



Example:- 192.168.0.200 dbserver.test.com dbserver

Set kernel Parameters:- By adding following lines in /etc/sysctl.conf file

***********************************************************************************************************************************************

#kernel.shmall = 2097152

#kernel.shmmax = 2147483648

kernel.shmmni = 4096

#semaphores: semmsl, semmns, semopm, semmni

kernel.sem = 250 32000 100 128

#fs.file_max = 65536

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default = 262144

net.core.rmem_max = 262144

net.core.wmem_default = 262144

net.core.wmem_max = 262144

***********************************************************************************************************************************************

As the kernel parameters are set, run the following command to change the current parameters:

/sbin/sysctl -p

Edit the /etc/security/limits.conf file by adding following lines to it:

* soft nproc 2047

* hard nproc 16384

* soft nofile 1024

* hard nofile 65536

Add the following lines to /etc/pam.d/login file, if it does not already exist

session required /lib/security/pam_limits.so

Disable SELINUX by editing flag as follows in /etc/selinux/config file,

SELINUX=disable

Now all the prerequisite are set. We are going to start a database installation, but before that check whether following packages are installed or not:

To check whether package is installed execute following command as root:

rpm -qa | grep

This will give you the output and package version and name if installed. If package version is lower that what we need, then insert media of RHEL 5 and execute following command as root:

rpm -Uvh

List Of Packages:
binutils
setarch-2*
make-3*
glibc-2*
libaio-2*
compat-libstdc++-33-3*
compat-gcc-34-3*
compat-gcc-34-c++-3*
gcc-4*
libxp-1*
openmotif-2*
compat-db-4*
control-center
glibc-common
gnome-libs
libstdc++-devel
pdksh

As the all packages are installed, create the new groups and users:

groupadd oinstall
groupadd dba
groupadd oper

useradd -g oinstall -G dba oracle
passwd oracle

With this command enter new password for oracle.

Now create the directories where oracle software will be installed

mkdir -p /u01/product/server/db_1
chown -R oracle.oinstall /u01

In this way we created directories and make oracle the owner.

Now login as root user and issue this command

xhost +

Edit the /etc/redhat-release file replacing the current release information (Red Hat Enterprise Linux Server 5 (Tikanga)) with following:

redhat-4

Now login as oracle user and ad the following lines to /home/.bash_profile file at the end

*********************************************************************************************************************************************

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/product; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/server/db_1; export ORACLE_HOME
ORACLE_SID=TSH1; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
*********************************************************************************************************************************************

Now execute following command

DISPLAY=:0.0; export DISPLAY

By navigating to oracle bib directory in $ORACLE_HOME execute

./runinstaller

Hope this will help you to install oracle on Linux system.....