How To Install Oracle 10g R2 on Linux 5.4

As you know being a Software Test Engineer we should have great love for Database. and If you wish to use database checkpoint you should know how to setup oracle so that you can use Database checkpoints. here are the steps that can help you to install  Oracle 10g R2 on Linux 5.4 Machine

1. Step 1 Install Linux 5.4 (we are not going to discuss how to install Linux in this session)
2. Right Click on Desktop and Open Terminal

vi /etc/hosts

press i

   

 172.16.0.101    lab3    localdomain    lab3

press ESC
:wq

3. Set Karnal Parameters
vi /etc/sysctl.conf
press i

#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
press ESC
:wq
4. Enter Security Limits for Oracle user
vi /etc/security/limits.conf
press i


oracle                soft    nproc   2047
oracle                hard    nproc   16384
oracle                soft    nofile  1024
oracle                hard    nofile  65536

press ESC
:wq
5. Verify that Firewall is disabled
vi /etc/selinux/config
press i
SELINUX=disabled

press ESC
:wq
6. Install the Required RPM's using RedHat Media
rpm -Uvh setarch-2*
rpm -Uvh make-3*
rpm -Uvh glibc-2*
rpm -Uvh libaio-0*
rpm -Uvh compat-libstdc++-33-3*
rpm -Uvh compat-gcc-34-3*
rpm -Uvh compat-gcc-34-c++-3*
rpm -Uvh gcc-4*
rpm -Uvh libXp-1*
rpm -Uvh openmotif-2*
rpm -Uvh compat-db-4*
7. Create new Groups and Users
groupadd oinstall
groupadd dba
groupadd oper

useradd -g oinstall -G dba oracle
passwd oracle
*Set password for oracle user

8.Create the directories in which the Oracle software will be installed:
to perform step 8 user must be login as root so
go to terminal and write su - root
write the following commands
mkdir -p /u01/app/oracle/product/10.2.0/db_1
chown -R oracle.oinstall /u01
9. Make Xhost Entry 
xhost +lab3
//    lab3 is my machine name
10. Change Release number (10g R2 can't be installed on 5.4 so change version of installed media
 vi /etc/redhat-release

press i

Redhat-4
press ESC
:wq

11. Switch user to Oracle to enter bash_profile file
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/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


press esc
:wq

12. System is ready so install Oracle (Always from Oracle user)

go to media file where installer is exist

execute command

./runInstaller

press enter

What is the Purpose of Oracle Control Files?

People often ask what is Control file and what is the purpose of Control file so here is my thoughts on Control Files

Control files are very important component of database and they have the information that is not available in any of the files in Oracle  The information in control files is

1. What is the name of the database
2. Name location size of data and Redo log files.
3. If your database crash, these files have recovery information

When these Control Files are Generated:
Control files are generated on the same date when your data base is created

How these Control files are updated?
Oracle Control files are updated with a background process called CKPT, this process update each and every control file every time the content is changed.

How many Control Files?
According to my knowledge 2 Control files are must for any Database but normally when you install Oracle 10g three control files are generated.

How can i see my Oracle Control files?
 You can see your control files using query

What is Purpose of Oracle Data Files?

What is DataFile?
Data Files like the name says are Data Files. They have actual data that has been inserted into tables in the database.

What is the Size of DataFile?
Size of datafile depend on the size of data stored in the tables.

What is Tablespace?
Datafiles are the physical structure behind another database storage known as tablespace. A tablespace is a logical storage area within the database.

Name of Few Tablespace and description
1. System (Home of Data dictionary and PL/SQL code,functions)
2. SYSAUX (Store Segments such as AWR Automatic Workload Repository, OLAP)
3. TEMP (This table space is use to perform large data operations, This is an Optional Tablespace)

How Can I see my Tablespace?
You can use the query
SQL> Select tablespace_name from dba_tablespaces;
SQL> Select tablespace_name, file_name from dba_data_files

Show me the tablespace in Enterprise Manager

What is the purpose of Database Write and what it writes ?

DBWn is the background process that writes in datafiles when ever the situation arise. The situation can be
1. when a tablespace is placed in backup
2. a tablespace is taken offline to make it unavailable or changed ro Read only
3. when ever user drop segment
4. when buffers in the database buffer grows large
5. users initiated server process searched too long for buffer space when reading into buffer cache

Oracle Background Processes

People often ask in DBA interview what are the oracle background process and describe then

There are many type of oracle background processes each performing specified job but oracle
require five background process
1. System Monitor (SMON)
Perform instance recovery when instance crash, this process also manage space that is used during sorting
2. Process Monitor (PMON)
Process monitor cleans up failed user database connections.
3. Database Writer (DBwn*)
Write modified database blocks from SGA buffer cache to datafiles on disk(n means it can be more then one 20 max)
4. Log Writer (LGWR)
Write transactions recovery information from Redo log Buffer to online Redo Log Files
5. Checkpoint (CKPT)
Updates the database files following a checkpoint eve

Oracle Products and Services

People often question about what oracle products and services avilable
here is the list of oracle products and services
1. Oracle database
oracle database is the first database that is designed to support grid computing. Grid computing is the most flexible and affordable way to manage both information and different applications 
2. Oracle Application Server
Oracle application server is the J2EE platform,  Enterprise Edition, Certified Server that integrates every thing required to develop, depoly web based applications including plsql, forms, reports and all other J2EE applications 
3. Oracle Applications
Oracle applications is complete set of business applications for managing and automating processes across your application
4. Oracle Developer Suite
Oracle developer suite is a set of complete integrated environment  that combine application development(forms, reports) and business intelligence tools
5. Oracle Services
This include Oracle Services like oracle university and  oracle consulting

Oracle Memory Structures

My teacher often ask in every lecture.. Explain oracle memory structure and explain different SGA Buffers. Every time i was asked about different pools i mix up, So finally i decide to write them on my blog so that i can quickly refresh them.

Oracle Memory Structure

Oracle Memory Structure:
The oracle memory structure associated with Oracle instance include.
1. System Global Area (SGA)
2. Program Global Area (PGA)


1. System Global Area (SGA)
SGA Include data structures
1.1 Database buffer cahce:  Cache blocks of data retrieved from the database.
1.2 Redo log Buffer:  This buffer stores redo information (if you instance crash then recovery process can be initiated using this buffer)
1.3 Shared Pool: This pool caches various constructs that can be shared amount different DB users
1.4 Large Pool: This is an optional pool that provide large memory space to the processes that require large memory like Oracle Backup and Recovery Operations.
1.5 Java Pool: Is used for all session-specific Java Code and data within the Java Virtual Machine (JVM)
1.6 Streams Pool:  Is used by oracle streams to copy one data file to other location/files.


How to Check Memory Allocated for the SGA?
When Oracle instance is started using Enterprise Manager or SQL*Plus, the amount of memory allocated to SGA is displayed.

2. Program Global Area (PGA)
PGA is a memory region that contains data and control information for each server process. When an oracle client connect to an oracle server a PGA is allocated to that client.

How to Enable Archiving

Perform the following operation step wise to enable Archiving in Oracle 10g archiving.

1. SQL> startup 
First start oracle to check that oracle is installed or not
2. SQL> shutdown immediate
Shutdown oracle to start in mount state
3. SQL> startup mount;
 Your oracle is now in mount state
4. SQL> alter database archivelog;
Use the above command to alter database to use archivelogs
5. SQL> alter database open;
Use command # 5 to open database 

What happen before and after user Commit Fire in Oracle

The COMMIT statement ends a transaction successfully. All changes made by all SQL statements since
the transaction began are recorded permanently in the database. Before the COMMIT statement is
issued, the changes may not be visible to other transactions.
You can commit a transaction by using either of the following statements, which make the
changes permanent:
SQL> COMMIT;
3 things that happen Before Commit:
1. Oracle generates undo records in the undo segment buffers in the SGA. As you know, the
undo records contain the old values of the updated and deleted table rows.
2. Oracle generates redo log entries in the redo log buffers in the SGA.
3. Oracle modifies the database buffers in the SGA.
3 things that happen AFTER Commit:
1. The transaction tables in the redo records are tagged with the unique system change number
(SCN) of the committed transaction.
2. The log writer writes the redo log information for the transaction from the redo log buffer to
the redo log files on disk, along with the transaction’s SCN. This is the point at which a commit
is considered complete in Oracle.
3. Any locks that Oracle holds are released, and Oracle marks the transaction as complete.

How to Configure RMAN

Use the following Steps to Configure RMAN

1.export ORACLE_SID= 'rmandb'
2.dbca
3.netmgr
4.we have 2 database 
5. start lsnrctl orcl em rmandb shut em orcl rmandb lsnrctl
6.startup nomount
7. alter system set sga_target=512M scope=spfile;
8. shutdown immediate;
9.conn to Rmandb
10. alter system set sga_target=512M scope=spfile;
11 emctl stop dbconsole 
12. export ORACLE_SID='rmandb' emctl stop dbconsole
13.sys/oracle@rmandb as sysdba
14.create tablespace rman_tbs datafile '/u01/app/oracle/oradata/rmandb/rman_tbs01.dbf' size 100M;

15. create user rman1 identified by rman1
15.1 grant connect ,resource,dba to rman1


16 grant recovery_catalog_owner to rman1

17 alter user rman1 default tablespace rman_tbs
18.
conn rman1/rman1@rmandb select * from cat;

19. rman catalog rman1/rman1@rmandb  create catalog tablespace rman_tbs

Open new terminal 

20. rman target sys/oracle@orcl catalog rman1/rman1@rmandb

21. register database;


22. EM recovery catalog setting

23. preferred credentials


24  RMAN show all

Detail Steps are as under:

1. orcl (Target database)
2. create rmandb (catalog database)
3. To create catalog database perform the following steps
3.1.export ORACLE_SID= 'rmandb'
3.2.dbca
3.2.1.netmgr (register your newly created database with lisner
4.start listner and verify that both db instance are registered
(start em Target database and stop em of catalog database)
4.1 lsnrctl start
4.2 Open new terminal and start both databases using command
4.3 startup nomount (alter database open)
4.4 alter system set sga_target=512M scope=spfile;
4.4 export ORACLE_SID= 'rmandb'
4.4.1 startup nomount (this will startup rmandb database in nomount state)
4.4.2 alter system set sga_target=512M scope=spfile;

5
Now Start both databases to open state

To mount database use command -->alter database mount;
To open database use command -->alter database open
To shutdown database -->alter database immediate;
starup data base -->startup





5.2 on rmandb terminal
5.2.1 startup
5.2.2 sys/oracle@rmandb as sysdba

6. Create database in rmandb
6.1 create tablespace rman_tbs datafile '/u01/app/oracle/oradata/rmandb/rman_tbs01.dbf' size 100M;
6.2 create user rman1 identified by rman1  grant connect ,resource,dba to rman1
6.3 grant recovery_catalog_owner to rman1
6.4 alter user rman1 default tablespace rman_tbs
6.5 connect via user rman1
6.5.1 conn rman1/rman1@rmandb
6.5.2 select * from cat;

in new terminal
7. rman catalog rman1/rman1@rmandb
7.1  create catalog tablespace rman_tbs
Open new terminal

8 rman target sys/oracle@orcl catalog rman1/rman1@rmandb

Recovery Manager: Release 10.2.0.1.0 - Production on Sat Jan 28 23:01:23 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: ORCL (DBID=1300214037)
connected to recovery catalog database



8.2 RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

How to Perform Import/Export in SQL Developer 3.1

How to Perform Import/Export in SQL Developer 3.1

In my old days i have to remember all the Import Export Commands and still remember the hard way i learned how to import/Export data from oracle database.
In SQL Developer 3.1 its as easy as it could to import/export data from database.


Question: Where is this Functionality?


Answer: In SQL Developer 3.1 its called Data Pump Functionality and it located in
View > DBA

Once you create a Connection and then its all wizard to perform import export functionality. For details steps go to the webpage

http://www.oracle-base.com/articles/misc/SqlDeveloper31DataPumpWizards.php