Sunday 30 October 2011

Oracle 11gR2 Dataguard Switchover to Physical Standby Database

Overview:
This article is a comprehensive guide for the switchover of primary database to physical standby datbase in a Dataguard configuration with Database 11g Release 2 (11.2.0.1.0) x64 on the Oracle Enterprise Linux 5 (OEL5) x64 Operating System Environment.
Please do keep in mind that this article should not be considered a substitution of the official guide from Oracle (http://www.oracle.com). The below mentioned link can be used to download the official dataguard configuration guide for Oracle Database 11g.

 
 
Dataguard Configuration:
For configuration of Dataguard, please visit the below mentioned link:
www.sensehaze.com

 For the screenshot of the steps for switchover activity, please visit the link::  www.sensehaze.com


 Switchover to Physical Standby Database:
Connect to the primary database (DG1) and fire the below mentioned query:
SQL> select name , database_role from v$database ;

Similarly connect to the standby database (DG2) and fire the below mentioned query:
SQL> select name , database_role from v$database ;

Then query "V$DATABASE" for finding whether the primary database (DG1) is capable of performing a switchover. Query "V$DATABASE" for the value of the "SWITCHOVER_STATUS" column. As shown below:
SQL> select switchover_status from v$database ;

If the "SWITCHOVER_STATUS" column’s value is anything other than "TO STANDBY", it is not possible to perform the switchover. If the column’s value is "SESSIONS ACTIVE", we should terminate active user sessions.
If the "SWITCHOVER_STATUS" column's value is "TO STANDBY", then we can initiate its transition to the physical standby database role with the following command:
SQL> alter database commit to switchover to physical standby;

If switchover is successful then after few moments the sql prompt will be returned to us without any error.
Then query "V$DATABASE" of the standby database (DG2). Query "V$DATABASE" for the value of the "SWITCHOVER_STATUS" column. As shown below:
SQL> select switchover_status from v$database ;


For performing switchover of the standby database to primary, fire the below mentioned command:
SQL> alter database commit to switchover to primary ;
As shown in the below screenshot:

For checking the database role (DG2), fire the below mentioned command:
SQL> select name , database_role from v$database ;
As shown in the below screenshot:

To open the new Primary database(DG2), fire the below mentioned command:
SQL> alter database open ;


Shutdown the Standby Database (Prevoius Primary (DG1)) and start it in mount mode and place it in recovery mode. For doing so, fire the below mentioned command:
SQL> shutdown immediate ;
SQL> startup nomount
SQL> alter database mount standby database ;
SQL> alter database recover managed standby database disconnect ;

To check the new standby database(DG1) "current log sequence", fire the below mentioned command:
SQL> archive log list ;


For the screenshot of the steps for switchover activity, please visit the link::  www.sensehaze.com




Friday 28 October 2011

Oracle Database Dataguard 11g R2 configuration on Linux

Oracle Dataguard 11g R2_x64 configuration on OEL5_x64.

Overview:
This article is a comprehensive guide for the configuration of Oracle Dataguard with Database 11g Release 2 (11.2.0.1.0) x64 on the Oracle Enterprise Linux 5 (OEL5) x64 Operating System Environment.
Please do keep in mind that this article should not be considered a substitution of the official guide from Oracle (http://www.oracle.com). The below mentioned link can be used to download the official dataguard configuration guide for Oracle Database 11g.



Objective of the Article:
By the time we finish this article, we should be able to understand the following:
(1)Installation of oracle database (11.2.0.1.0) x64 on Oracle Enterprise Linux 5 (OEL5) x64.
(3)Creation & configuration of general-purpose Oracle11g (11.2.0.1.0) database that makes use of the local file system for physical database file storage (control files, data files, flash recovery area, online redo log files).
(4)Configuration of Dataguard ( Physical Standby ) with oracle database (11.2.0.1.0) x64 on Oracle Enterprise Linux 5 (OEL5) x64.




Installation of OEL5 x64:
Install Oracle Enterprise Linux 5 x64 on your machine.
For More information about OEL5 installation visit the below site::



Parameters Used in the Article:
 Node1(Primary)  Node2(Standby)
 Machine Name  DG1  DG2
 IP Address  192.168.15.100  192.168.15.101
 Oracle Database Version  11g Ent. R2(11.2.0.1)- 64bit  11g Ent. R2(11.2.0.1)- 64bit
 DB Service Name  orcl  stdby
 Oracle Software Owner  O/S User – oracle  O/S User – oracle
Do the below mentioned activity on both the nodes ( dg1 , dg2 ).
Open the "/etc/hosts" file and add the entries similar to the following:
192.168.15.100 DG1
192.168.15.101 DG2
[root@dg1 ~]# vim /etc/hosts
[root@dg2 ~]# vim /etc/hosts




Prerequisites check & Database Installation:
For completing the prerequisites and installation of Oracle Database 11g R2 x64 on OEL5 x64, please visit the below mentioned link:
Link for database 11gR2 installtion on Oralce Enterprise Linux x64
Follow the instructions given in the above link and follow the steps till "Listener Creation" on both the machines ( DG1 and DG2 ) including "Listener Creation".


Database Creation:
For creation of Database, please visit the below mentioned link:
Link for database creation
Create database on node1 (DG1) only. For the purpose of this article, we will use "orcl" as databae name.
Global Database Name - orcl



Setting User Environment:
Setting the environment for "oracle" user. For setting so, do the following( Do the below activity on both the nodes(dg1,dg2)):
[root@dg1 ~]# su - oracle
[oracle@dg1 ~]$ vim ~/.bash_profile
Add the lines, similar to below and save the file ( According to your path, please use the values).
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

 For screenshots ::: www.sensehaze.com

Listener Entries:
Edit the file "tnsnames.ora" on both the nodes and add the entries for both the nodes (dg1,dg2) database (orcl , stdby).
Enable "force logging" and "archive logging":
Do the below activities only on the first node ( DG1 ).
To check whether the database is in "archivelog mode", connect to the database and fire the command:
SQL> archive log list ;
If the output shows "Database Log mode - No Archive Mode", then we have to enable archive logging.
For enabling archive logging, first we have to open the database in mount state.



If suppose we had shutdown the database, then start the database in mount mode, for doing so type the below commands:
SQL> startup mount
SQL> ALTER SYSTEM SET log_archive_format = 'orcl_%s_%t_%r.arc' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET db_unique_name = 'orcl' SCOPE=SPFILE;
SQL> ALTER DATABASE FORCE LOGGING;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

  For screenshots ::: www.sensehaze.com

Setup Standby Redo Log Groups:
Create standby redo logfile groups ( The number of standby redo logfile groups should be atleast one more than the number of online redo logfile groups ).
This is to be done only at the primary site (DG1).
Here, we have 3 online redo logfile groups, so we will create 4 standby redo logfile groups. For creating standby redo logfiles, fire the below mentioned commands:
SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/orcl/srl01.log' SIZE 50M REUSE;
SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/orcl/srl02.log' SIZE 50M REUSE;
SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/orcl/srl03.log' SIZE 50M REUSE;
SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/orcl/srl04.log' SIZE 50M REUSE;
N.B: Size of standby redofiles should be atleast equal to the size of the online redo logfiles.






Modify Primary Initialization Parameters:
The below mentioned steps to be performed on the Primary Site (DG1).
We have to set the below mentioned parameters for configuring dataguard:
—log_archive_dest_1
—log_archive_dest_state_1
—log_archive_dest_2
—log_archive_dest_state_2
—standby_file_management
—log_archive_config
—fal_server
—fal_client
For setting these parameters, fire the below mentioned commands:
SQL> ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=/u01/app/oracle/flash_recovery_area/ORCL/ DB_UNIQUE_NAME=orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES)';
SQL> ALTER SYSTEM SET log_archive_dest_state_1 = 'ENABLE';
SQL> ALTER SYSTEM SET log_archive_dest_2 = 'SERVICE=stdby DB_UNIQUE_NAME=stdby VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)';
SQL> ALTER SYSTEM SET log_archive_dest_state_2 = 'ENABLE';
SQL> ALTER SYSTEM SET standby_file_management = 'AUTO';
SQL> ALTER SYSTEM SET log_archive_config = 'DG_CONFIG=(orcl,stdby)';
SQL> ALTER SYSTEM SET fal_client = 'orcl' ;
SQL> ALTER SYSTEM SET fal_server = 'stdby' ;


  For screenshots ::: www.sensehaze.com


Preparing the Standby Site:
Do these activities on second node ( DG2 ).
Create Required Directories:
(1) Create "oradata" directory and its sub-directory "oradata/stdby".
(2) Create "flash_recovery_area" directory and its sub-directory "flash_recovery_area/STDBY".
(3) Create audit files directory ( adump,dpdump etc diretories ).

Set Up Password File:
Create a Password File for the standby database. For creating password file , please run the following command:
[oracle@dg2 ~]$ orapwd file='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstdby'
When prompted for password of SYS, Enter password for SYS.




Create Standby Initialization Parameter File:
For creating Initialization parameter file , please use any of the editors. As shown in the above screenshot:
[oracle@dg2 ~]$ vim initsby.ora
Add the line "DB_NAME=stdby" and save the file.




Start the Standby Database in nomount mode:
Now start the standby database by using this newly created pfile in nomount stage.
For doing so, please follow the steps mentioned below:
[oracle@dg2 ~]$ export ORACLE_SID=orcl
[oracle@dg2 ~]$ sqlplus sys/sys@stdby as sysdba
SQL> startup nomount pfile='/home/oracle/initsby.ora'


  For screenshots ::: www.sensehaze.com


Cloning the Standby Database:
Do this from the Primary Site ( DG1 ).
For cloning the standby database Via DUPLICATE DATABASE command, connect to the primary and auxiliary database via RMAN. As mentioned below:
[oracle@dg1 ~]$ rman target sys/sys@orcl auxiliary sys/sys@stdby




In the rman prompt type the following::
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
DORECOVER SPFILE
SET db_unique_name='stdby'
SET control_files='/u01/app/oracle/oradata/stdby/control01.ctl'
SET log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/stdby/'
SET db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/stdby/'
SET log_archive_dest_1='location=/u01/app/oracle/flash_recovery_area/STDBY/ valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=stdby'
SET log_archive_dest_2='service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'
SET fal_client='stdby'
SET fal_server='orcl'
SET standby_file_management='AUTO'
SET log_archive_config='dg_config=(orcl,stdby)'
NOFILENAMECHECK;





Put Standby Database on Recovery Mode:
To put the standby database on recovery mode. Do the following:
SQL> alter database recover managed standby database disconnect;




  For screenshots of the steps, please visit the link::: www.sensehaze.com















Monday 24 October 2011

Export of full database

 Overview:
This article is a comprehensive guide for taking full export of Database.
The Export utility provides a simple way for us to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.

Please do keep in mind that this article should not be considered a substitution of the official guide from Oracle (http://www.oracle.com). The below mentioned link can be used to download the official guide for Oracle Database Users Management:



Objective of the Article:
By the time we finish this article, we should be able to understand the following:
(1)Various Exporting Options.
(2)Invoking Export.



Exporting Options:
In command prompt, type the command "exp -help".As shown below:
[oracle@asmpc]$ exp -help

 For the screenshots. Please visit the link:: http://www.sensehaze.com/ 


Oracle export utility enables the user to specify many parameters, as shown in the above screenshot.
We are going to discuss few of the paramenters.
  • constraints : Specifies whether table constraints should be exported with Oracle table data.
  • file : The name of the export file. The default extension is .dmp, but you can specify any extension.
  • compress :Specifies how Export and Import manage the initial extent for table data.
  • filesize :The maximum file size, specified in bytes.
  • full :The entire Oracle database is exported with a full Oracle export.
  • grants :Specifies object grants to Oracle export.
  • log :The filename used by Oracle export to write messages.
  • owner :Indicates that the Export is a user-mode Export and lists the users whose objects will be exported.

Invoking Export:
For invoking export, type the below mentioned command.
[oracle@asmpc]$ exp system/sys@mydb file=exp.dmp grants=y full=y buffer=1000000
Here we have used basic options, as per our requirement we can use various options.


For the screenshots. Please visit the link:: http://www.sensehaze.com/





Sunday 23 October 2011

User Management in Oracle Database

Overview:
This article is a comprehensive guide for the management of users in Oracle Database.
To access a database, a user must connect to the database using a valid user name defined in the database. Oracle Database enables us to set up security for our users in a variety of ways. When we create user accounts, we can specify limits to the user account.

Please do keep in mind that this article should not be considered a substitution of the official guide from Oracle (http://www.oracle.com). The below mentioned link can be used to download the official guide for Oracle Database Users Management:



Objective of the Article:
By the time we finish this article, we should be able to understand the following:
(1)User Creation.
(2)Altering User Accounts.
(3)Configuring User Resources.
(4)Deleting User Accounts.


 For the screenshot of the step please visit the link::: http://www.sensehaze.com/



Creation of User Accounts:
To know about the users, query the table "DBA_USERS". (There are other tables also, i'm using DBA_USERS here.)
For database users info, type the below mentioned command:
SQL> desc dba_users;

For database users info, type the below mentioned command:
SQL> select username from dba_users;
For user creaton, type the below mentioned command:
SQL> create user test identified by test default tablespace users temporary tablespace temp ;
Here i have used:
user name --- test
user password --- test
default tablespace name --- users
temporary tablespace name --- temp
Here i have used basic options for user creation, as per our requirement we can use various options.


For the screenshot of the step please visit the link::: http://www.sensehaze.com/ 

Altering User Accounts:
For unlocking user account:
Fire the below mentioned command:
SQL> alter user test account unlock ;
For changing password of the user:
Fire the below mentioned command:
SQL> alter user test identified by test123 ;


 For the screenshot of the step please visit the link::: http://www.sensehaze.com/



Configuring User Resources:
For granting privileges/resources to the user:
Fire the below mentioned command:
SQL> grant create session , connect to test ;


Deleting User Accounts:
Suppose our user is connected and we want to delete the user.
For deleting the user account fire the below mentioned command:
SQL> drop user test cascade ;



For the screenshot of the step please visit the link::: http://www.sensehaze.com/





 
 

Thursday 20 October 2011

Tablespace Management in Oracle Database

In this section we are going to learn how to manage tablespaces in Oracle Database.

Overview:
This article is a comprehensive guide for the management of Tablespaces in Oracle Database.
Using multiple tablespaces allows us more flexibility in performing database operations. When a database has multiple tablespaces, we can:
  • Take individual tablespaces offline while others remain online, providing better overall availability.
  • Back up individual tablespaces
  • Separate user data from data dictionary data to reduce I/O contention.
  • Separate data of one application from the data of another.
Please do keep in mind that this article should not be considered a substitution of the official guide from Oracle (http://www.oracle.com). The below mentioned link can be used to download the official guide for Oracle Database Tablespace Management:




Objective of the Article:
By the time we finish this article, we should be able to understand the following:
(1)Tablespace Creation.
(2)Altering Tablespace Availability.
(3)Making Tablespaces Read-Only.
(4)Renaming Tablespaces.
(5)Deleting Tablespaces.

For the screenshots . Please follow the link:::  http://www.sensehaze.com/



Tablespace Creation:
For tablespace creaton type the below mentioned command:
SQL> create tablespace myts datafile 'location_data_file\myts.dbf' SIZE 100M extent management local ;


Here i have used:
tablespace name --- myts
datafile name --- myts.dbf
datafile size --- 100M
extent management --- local.
Here i have used basic options for tablespace creation, as per our requirement we can use various options.

 For the screenshots . Please follow the link:::  http://www.sensehaze.com/

Altering Tablespace Availability:
For knowing information of tablespaces and their status, query the table "DBA_DATA_FILES". As mentioned below:
SQL> select tablespace_name , online_status from dba_data_files;

Taking a tablespace offline:
SQL> alter tablespace myts offline normal;

Taking a tablespace online:
SQL> alter tablespace myts online normal;

 For the screenshots . Please follow the link:::  http://www.sensehaze.com/

Making Tablespaces Read-Only:
Making a tablespace Read-Only:
SQL> alter tablespace myts read only;



Making a tablespace Read-Write:
SQL> alter tablespace myts read write;



Renaming Tablespaces:
For renaming a tablespace, fire the below command:
SQL> alter tablespace myts rename to mytsnew;


Deleting Tablespaces:
For deleting a tablespace, fire the below command:

drop tablespace myts including contents and datafiles;
For the screenshots . Please follow the link:::  http://www.sensehaze.com/







 
 

Tuesday 18 October 2011

Oracle Database Statspack Report Generation

Overview:
This article is a comprehensive guide for the generation of STATSPACK Report for Oracle Database.
STATSPACK is a performance diagnosis tool, available since Oracle8i. STATSPACK can be considered BSTAT/ESTAT's successor, incorporating many new features. The Statspack package is a set of SQL, PL/SQL and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes:
  • An instance health and load summary page
  • High resource SQL statements
  • traditional wait events and initialization parameters
Using Statspack we can collect statistics which are put in specific tables. When we need, we can run reports based on these tables (snapshots) to tune the database.
Remember to set "timed_statistics" to "true" for the instance. Setting this parameter provides timing data, which is invaluable for performance tuning.
Please do keep in mind that this article should not be considered a substitution of the official guide from Oracle (http://www.oracle.com). The below mentioned link can be used to acceess the official STATSPACK Report Generation guide for Oracle Database.



Objective of the Article:
By the time we finish this article, we should be able to understand the following:
(1)Basic idea of STATSPACK Report.
(2)Installation of STATSPACK Utility.
(3)Generation of STATSPACK Report.
(4)Removal/DeInstall of STATSPACK Utility.



 How STATSPACK Works?
When we run the Statspack installation script(spcreate), the PERFSTAT user is created automatically. PERFSTAT owns all objects needed by the Statspack package and is granted limited query-only privileges on the "V$ views" required for performance tuning.
Each time a new collection is taken, a new SNAP_ID is generated.
After snapshots are taken, we can run the performance report. The report prompts us for start and end snapshot IDs and then calculates activity on the instance between the two snapshots, much like a "BSTAT/ESTAT" report. To compare, the first "SNAP_ID" supplied can be considered the equivalent of running "BSTAT"; the second "SNAP_ID" specified can be considered the equivalent of "ESTAT". 



 For screenshot of the steps please visit :: http://www.sensehaze.com

Installing STATSPACK:
The STATSPACK utility requires an isolated tablespace to obtain all of the objects and data. We are going to create "TOOLS" tablespace for the purpose.
For creating the tablespace, fire the below mentioned SQL command:
SQL> create tablespace TOOLS datafile 'our_data_file_path\tools.dbf' SIZE 100M;
According to your database size/requirement choose the size and options for "TOOLS" tablespace.

Run the Create Scripts:
SQL> @?/rdbms/admin/spcreate;


Set "timed_statistics" to true:
FOr doing so run the below mentioned command:
SQL> alter system set timed_statistics = true; 

 Taking STATSPACK Snapshots:
Connect to the database as "PERFSTAT" user.

For generating statspack report we need two snapshots ( begin snap , end snap ). So , now we are going to take snapshots , as mentioned below:
For taking the snapshot, fire the below mentioned SQL command:
SQL> execute statspack.snap ;
For viewing the snapshots taken, we can query "stats$snapshot,v$database".
SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS') "Date/Time" from stats$snapshot,v$database;  

For screenshot of the steps please visit :: http://www.sensehaze.com

 Generate STATSPACK Reports:
To generate a report for the database, we have to fire the below mentioned query:
SQL> @?/rdbms/admin/spreport

 
 Removing STATSPACK:
To remove/deinstall "STATSPACK", connect as a user with "SYSDBA" privilege and run the following "SPDROP" script from SQL*Plus.
SQL> CONNECT / AS SYSDBA
SQL> @?/rdbms/admin/spdrop
SQL> drop tablespace TOOLS including contents and datafiles;

For screenshot of the steps please visit :: http://www.sensehaze.com 

Saturday 15 October 2011

Oracle Database 11g R2_x64 installation on OEL5_x64

Overview:
This article is a comprehensive guide for the installation of Oracle Database 11g Release 2 (11.2.0.1.0) x64 on the Oracle Enterprise Linux 5 (OEL5) x64 Operating System Environment.
Please do keep in mind that this article should not be considered a substitution of the official installation guide and release note from Oracle (http://www.oracle.com). The below mentioned link can be used to download the official installation guide for Oracle Database 11g Release 2 (11.2.0.1.0) x64 on Linux x64 Operating Environment.


Objective of the Article:
By the time we finish this article, we should be able to understand the following:
(1)Installation of oracle database (11.2.0.1.0) x64 on Oracle Enterprise Linux 5 (OEL5) x64.
(3)Creation & configuration of general-purpose Oracle11g (11.2.0.1.0) database that makes use of the local file system for physical database file storage (control files, data files, flash recovery area, online redo log files).


Installation of OEL5 x64:
Install Oracle Enterprise Linux 5 x64 on your machine.
For More information about OEL5 installation visit the below site::




Download Oracle Database Software:
We first have to download the required Oracle Database software packages from the Oracle Technology Network (OTN) or Oracle Edelivery Site and then extract the Oracle Database Software packages.
Download the Oracle Database 11g Release 2 (11.2.0.1.0) Software for Linux x64 Operating System Environment.
Link for downloading oracle Database Software Package:: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html



Hardware Requirements Check:
Note:Login to the system as a root user for performing the below activities.
Memory Requirements:
Minimum:1 GB of RAM
To determine the RAM size, enter the following command:
[root@dg1 ~]# grep MemTotal /proc/meminfo
Note:If the size of the RAM is less than the required size, then you must install more memory before continuing.
Available RAM SWAP Space Requirement
Between 1 GB and 2 GB 1.5 times the size of the RAM
Between 2 GB and 16 GB Equal to the size of the RAM
More than 16 GB 16 GB
To determine the size of the configured swap space, enter the following command:
[root@dg1 ~]# grep SwapTotal /proc/meminfo
Note:If there is a need of increasing swap space, see O/S documentation for increasing the swap space.

Disk Space Requirements:
At least 1 GB of free disk space should be available in the /tmp directory.
To determine the amount of disk space available in the /tmp directory, enter the following command:
[root@dg1 ~]# df -h /tmp
If there is less than 1 GB of free disk space available in the /tmp directory, then do one of the below mentioned steps:
Ξ Delete unnecessary files from the tmp directory.
Ξ Increase the size of tmp directory as follows (Extend the file system that contains the /tmp directory):
[root@dg1 ~]# vim /etc/fstab 


To determine the amount of free disk space on the system, enter the following command:
Installation Type Minimum Requirement for Software Files (GB)
Enterprise Edition 4.35
Standard Edition 4.22
Installation Type Minimum Requirement for Data Files (GB)
Enterprise Edition 1.7
Standard Edition 1.5
[root@dg1 ~]# df -h
If your diskspace is not enough, please contact administrator for increasing the disk space.



Package Requirements Check:
After the installation of OEL5_x64, the next task is to install all packages required by Oracle Database 11g R2 x64. During the installation process the Oracle Universal Installer performs prerequisites checks before the actual installation of database. For ensuring that these prerequisite checks complete successfully, ensure that all the packages listed below are installed properly.The following are the list of packages required for Oracle Database 11g Release 2 (11.2) x64 on OEL5 x64:
  • binutils-2.17.50.0.6
  • compat-libstdc++-33-3.2.3
  • compat-libstdc++-33-3.2.3 (32 bit)
  • elfutils-libelf-0.125
  • elfutils-libelf-devel-0.125
  • gcc-4.1.2
  • gcc-c++-4.1.2
  • glibc-2.5-24
  • glibc-2.5-24 (32 bit)
  • glibc-common-2.5
  • glibc-devel-2.5
  • glibc-devel-2.5 (32 bit)
  • glibc-headers-2.5
  • ksh-20060214
  • libaio-0.3.106
  • libaio-0.3.106 (32 bit)
  • libaio-devel-0.3.106
  • libaio-devel-0.3.106 (32 bit)
  • libgcc-4.1.2
  • libgcc-4.1.2 (32 bit)
  • libstdc++-4.1.2
  • libstdc++-4.1.2 (32 bit)
  • libstdc++-devel 4.1.2
  • make-3.81
  • sysstat-7.0.2
The packages listed above can be found on the installer disc/image of Oracle Enterprise Linux 5 x64 or can be downloaded from the vendor's website.
To determine whether the required packages are installed, enter commands similar to the following:
[root@dg1 ~]# rpm -q package_name
e.g:  [root@dg1 ~]# rpm –q binutils-2.17.50.0.6
if the package is not installed then install the package as mentioned below:
[root@dg1 ~]# rpm -ivh package_name 



Creation of Required O/S Users and Groups:
The following local operating system user and groups are required for oracle database installation:
  • Oracle Inventory Group -- oinstall
  • OSBDA Group -- dba
  • Oracle Software Owner User -- Oracle
Check whether the groups/user exists , if doesn't exist plese create them. Follow the below mentioned steps...
  1. To determine whether oinstall group exists, type the following command:
    [root@dg1 ~]# more /etc/oraInst.loc
    If the output shows oinstall group then the group exists and need not to be created.
    .
  2. To determine whether dba group exists, type the following command:
    [root@dg1 ~]#grep dba /etc/group
    If the output shows dba group then the group exists and need not to be created.
    .
  3. To determine whether oracle user exists, type the following command:
    [root@dg1 ~]#id oracle
    If the output shows oracle user then the user exists and need not to be created.
    The output should be similar to following, indicating "oinstall" as primary and "dba" as secondary group.
    uid=500(oracle) gid=500(oinstall) groups=501(dba).
    If the primary group is not "oinstall" and the user "oracle" is not the member of "dba" group then we have to modify the user "oracle" as mentioned below::
    [root@dg1 ~]#usermod -g oinstall -G dba oracle
    .
  4. If the user or the required groups doesn't exist then create them as mentioned below:
    [root@dg1 ~]# groupadd oinstall
    [root@dg1 ~]# groupadd dba
    [root@dg1 ~]# useradd -g oinstall -G dba oracle
    Now check the status of "oracle" user.
    [root@dg1 ~]# id oracle
    The output should be similar to following, indicating "oinstall" as primary and "dba" as secondary group.
    uid=500(oracle) gid=500(oinstall) groups=501(dba).
    Nw set the password of "oracle" user.
    [root@dg1 ~]# passwd oracle
    and input a password for "oracle" user.


Verify That the User nobody Exists:
Before installtion of oracle software, complete the following steps to verify that the user nobody exists on the system:
To determine if the user exists, enter the following command:
[root@dg1 ~]# id nobody
If this command displays information about the nobody user, then you do not have to create that user.
If the user nobody does not exist, then enter the following command to create it:
[root@dg1 ~]# useradd nobody




 Configuration of Kernel Parameters:
Verify that the kernel parameters shown in the following table are set to values greater than or equal to the minimum value shown.
Using any text editor, edit the /etc/sysctl.conf file, and add or edit lines similar to the following:
 fs.aio-max-nr  1048576
 fs.file-max  6815744
 kernel.shmall  2097152
 kernel.shmmax  536870912
 kernel.shmmni  4096
 kernel.sem  250 32000 100 128
 net.ipv4.ip_local_port_range  9000 65500
 net.core.rmem_default  262144
 net.core.rmem_max  4194304
 net.core.wmem_default  262144
 net.core.wmem_max  1048586
As shown in the below screenshot.
[root@dg1 ~]# vim /etc/sysctl.conf



After adding the above lines to the /etc/sysctl.conf file, they persist each time the system reboots. If you would like to make these kernel parameter value changes to the current system without having to first reboot, enter the following command:
[root@dg1 ~]# sysctl -p



To improve the performance on Linux systems, Oracle recommends us to increase the shell limits for the oracle software owner user "oracle".
Update the resource limits in the /etc/security/limits.conf configuration file for the installation owner.
----------------------------
oracle  soft  nproc  2047
oracle  hard  nproc  16384
oracle  soft  nofile  1024
oracle  hard  nofile  65536
oracle  soft  stack  10240
----------------------------
[root@dg1 ~]# vim /etc/security/limits.conf



Creation of Required Directories:
The next step is to create a new directory that will be used to store the Oracle Database software, oracle database datafiles and flash recovery area.
After the directory is created, we must then specify the correct owner, group, and permissions for it.
For example:
[root@dg1 ~]# mkdir -p /u01/app/
[root@dg1 ~]# chown -R oracle:oinstall /u01/
[root@dg1 ~]# chmod -R 775 /u01/



Configuring the oracle User’s Environment:
Set the default file mode creation mask (umask) to 022 for "oracle" user. For setting so, do the following:
[root@dg1 ~]# su - oracle
[oracle@dg1 ~]$ vim ~/.bash_profile
Add a line as written below and save the file.
umask 022
N.B:Before starting the Oracle Universal Installer, we should first verify that the server from where we are doing the installation, have X server installed on it. For verification we can type "xclok" in the terminal, it will show a clock.
Before starting the installation we have to run the xhost + command as root from the console to allow X Server connections.
[root@dg1 ~]# xhost +

Installation of Oracle Database:
After configuring the oracle user’s environment, start Oracle Universal Installer (OUI) and install Oracle Database as follows:
[root@dg1 ~]# su - oracle
Go to the directory where our installation software is copied and type "./runInstaller" , then your installation will begin:
[oracle@dg1 ~]$cd /oracle_soft/
[oracle@dg1 ~]$ ./runInstaller


Set Oracle User Profile:
Open the profile file and add the below mentioned lines:

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
[oracle@dg1 ~]$ vim ~/.bash_profile
Or use any editor of your choice, to edit the profile of "Oracle" user.



Listener Creation:
Open command prompt(terminal) & type “netca”

Database Creation:
Open command prompt and type dbca,















 

Thursday 13 October 2011

Oracle Database AWR Report Generation::

In this section we are going to learn how to generate AWR Report on Oracle Database.

Overview:
This article is a comprehensive guide for the generation of AWR report on Oracle Database.
AWR periodically gathers system activity and workload data. Every layer of Oracle is equipped with instrumentation that gathers information on workload which will then be used to make self-managing decisions. AWR is the place where this data is stored. AWR looks periodically at the system performance (by default every 60 minutes) and stores the information found. The information is stored in the SYSAUX tablespace. This information is the basis for all self-management decisions. For example, it is possible to identify the following statistics:
  • Top 5 Waits Section
  • Instance Activity Statistics
  • Tablespace I/O Statistics
  • Operating Systems Statistics
  • Datafile I/O Statistics
  • PGA Statistics
  • Buffer Waits Statistics
Please do keep in mind that this article should not be considered a substitution of the official guide from Oracle (http://www.oracle.com). The below mentioned link can be used to download the official AWR report generation and reading guide for Oracle Database.


Objective of the Article:
By the time we finish this article, we should be able to understand the following:
(1)Basic idea of AWR Report.
(2)Automatic AWR Report generation on oracle database.
(3)Manual AWR Report generation on oracle database.


Query of Table DBA_HIST_SNAPSHOT:
 Connect to the database.
Query the Table DBA_HIST_SNAPSHOT. (SQL>DESC DBA_HIST_SNAPSHOT)  .

Query the table DBA_HIST_SNAPSHOT for SNAP_IDs for which you want to generate the AWR Report. As shown in the below screenshot.
SQL>select snap_id from dba_hist_snapshot order by snap_id ;

For the screenshot of the steps for generating AWR Report . Please go to the link:: www.sensehaze.com Link

Select the two snap_ids ( begin_snap_id , end_snap_id )
For generation of AWR Report , Execute the below mentioned sql.
SQL>@?/rdbms/admin/awrrpt.sql







Friday 7 October 2011

Oracle Database 11g R2_x64 installation on windows 7_x64

Oracle Database 11g R2_x64 installation on windows 7_x64::

In this section we are going to learn how to install oracle database 11gR2_x64 on Windows 7_x64.

Overview:
This article is a comprehensive guide for the installation of Oracle Database 11g Release 2 (11.2.0.1.0) x64 on the Windows 7 x64 Operating System Environment.
Please do keep in mind that this article should not be considered a substitution of the official installation guide and release note from Oracle (http://www.oracle.com). The below mentioned link can be used to download the official installation guide for Oracle Database 11g Release 2 (11.2.0.1.0) x64 on Windows x64 Operating Environment.


Objective of the Article:
By the time we finish this article, we should be able to understand the following:
(1)Installation of oracle database (11.2.0.1.0) x64 on Windows 7 x64
(3)Creation & configuration of general-purpose Oracle11g (11.2.0.1.0) database that makes use of the local file system for physical database file storage (control files, data files, flash recovery area, online redo log files).



 Installation of Windows 7:

Install Windows 7 x64 on your machine.
For More information about windows 7 installation visit the below site::


Hardware Requirements:
Requirements Minimum Value
Physical memory (RAM) 1GB Minimum
Virtual memory Double the amount of RAM
Disk space Total: 5.39 GB
Processor Type AMD64, or Intel Extended memory (EM64T)



Download Oracle Database Software:
 
We first have to download the required Oracle Database software packages from the Oracle Technology Network (OTN) or Oracle Edelivery Site and then extract the Oracle Database Software packages.
Download the Oracle Database 11g Release 2 (11.2.0.1.0) Software for Windows x64 Operating System Environment.
Link for downloading oracle Database Software Package:: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html



 Installation of Oracle Database:
Go to the extracted folder of Oracle Database Software package downloaded in the previous step. Go inside the “database” named directory and you will find “setup.exe” there.
Launch “setup.exe” from there to begin your installation.
For the Screenshot of the installation steps please visit :: http://www.sensehaze.com



Listener Creation:
on command prompt type netca.
 For the Screenshot of the listener creation steps please visit :: http://www.sensehaze.com



 Database Creation:
on command prompt type dbca.
 For the Screenshot of the database creation steps please visit :: http://www.sensehaze.com


  Connection to Database:
Now its time to test the database.
For the screenshot of the methods of connecting to the datbase please visit ::  http://www.sensehaze.com