Oracle 11g RAC - Implementation Steps on Linux

http://www.oracle-base.com/articles/11g/OracleDB11gR1RACInstallationOnLinuxUsingNFS.php

DETERMINE CPU CORES IN LINUX

There are different types of cores, namely

1-Single core

2-Dual core

4-Quad core

6-Hex core

8-Oct core

   # cat /proc/cpuinfo

    # grep cores /proc/cpuinfo

    # grep “core id” /proc/cpuinfo

    # grep -i core /proc/cpuinfo

example for finding physical cores, here each CPU holds 6 cores(HEXA)

[root@server1 ~]# cat /proc/cpuinfo | egrep "processor|physical\ id|core\ id|cpu\ cores"

processor       : 0

physical id     : 0

core id         : 0

cpu cores       : 6

psrinfo -v :for Solaris


To find CPU Sockets


dmidecode|grep CPU

will display the number of CPU sockets

dmidecode|grep Populated

will display the populated sockets.

Oracleadm

SQLPLUS shortcuts:

/ -will execute the last run statement in the sqlplus buffer
1 -will display the last run statement in the sqlplus buffer
change /old/new -Changes the first occurrence of the text on the current line in the sqlplus buffer.


 ---------------------------------------------------------------------------------------------------
Oracle EZCONNECT:

sqlplus user/pwd@//192.168.1.20:1521/ORCL


With this we can connect to a database without TNSNAMES configuration.
---------------------------------------------------------------------------------------------------
Oracle Analyze a table:

alter table table_name estimate statistics;

Analyze a schema:

login as sys user

begin
exec dbms_stats.gather_schema_stats("SCOTT");
end;
---------------------------------------------------------------------------------------------------
HP UX commands:

machinfo - to find the machine information
swapinfo - to find the swap partition size
bdf - to check the partition information (in kbytes)
---------------------------------------------------------------------------------------------------
Configuring SAMBA server in RHEL5.5.

-give full permission on the sharable directory( chmod 777 /pack/sample
-samba package should be installed or do yum install samba if you have yum repository
-$vi /etc/samba/smb.conf
-add the following lines in the file mentioned to share the mentioned directory
- [Share]

   path = /home/share

   writable = yes

   guest ok = yes

   guest only = yes

   create mode = 0777

   directory mode = 0777

   share modes = yes

use testparm command to check and verify the samba configuration file.
---------------------------------------------------------------------------------------------------
ORATAB file is created during oracle installation. This file resides in /etc directory or in /var/opt/oracle directory.

This file is used to list the databases installed and the oracle versions. Oratab file contains ORACLE_SID, ORACLE_HOME, and automatic startup , shutdown of database with OS startup and shutdown. The environment variables in OS level are taken from this file.
For example:

college:/oracle/app/product/11.2.0/db_home1:Y

The Y in the above line mentions to autostart the db and listener while OS startup. This oratab file works for only  one instance and you cannot add multiple instances of database into this file.

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

Oracle DB link


Creating dblink from oracle 10g database to a user in oracle 11g database :

create database link link_name connect to "username" identified by "password" using 'connection_string';

The username and password should be given within double quotes.

There are other conditions too for a dblink, that is

  Both the database servers should be in same domain

  The user should have 'CREATE DATABASE LINK' privilege

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

Oracle - Create Tablespace


SQL> create tablespace tbs_name datafile '/vol01/oradata/sample/datafile/exp101.dbf' size 100M autoextend on next 2M maxsize unlimited extent management local segment space management auto;
 
Force Drop a tablespace:
------------------------------
SQL> alter tablespace tbs_name offline drop;

Ensure no active connections writing on that particular table.

Change DB name:
-----------------------

nid is the utility to change the dbname.

The database should be in the mounted exclusive state,

nid TARGET=sys/password@tnsname
Compile Oracle Schema using sqlplus
---------------------------------------

execute dbms_utility.compile_schema('SCOTT');

as a 'SYS' user.

Managing Rouge sessions in Oracle:
------------------------------------------------

1. Identify the session to be killed

SELECT a.inst_id,

       a.sid,

       a.serial#,

       b.spid,

       a.username,

       a.program

FROM   gv$session a

       JOIN gv$process b ON b.addr = a.paddr AND b.inst_id = a.inst_id

WHERE  a.type not like  'BACKGROUND';

2. Alter system kill session 'sid,serial#';

This command will kill the session. Run as sys user. If still the session persists you can use "immediate" clause with the above command. like

Alter system kill session 'sid,serial#' immediate;

If its a RAC instance then

Alter system kill session 'sid,serial#,@inst_id' immediate;\

3. The other way is Disconnect Session

This option does not kill the session but it will disconnect the session and make the dedicated server process(dedicated server mode)  in os level to shut for that particular session.

Alter  system disconnect session 'sid,serial#' post_transaction;
the post_transaction option will wait for the current transaction to complete.

Alter system disconnect session 'sid,serial#'  immediate;
The immediate option will stop the current transaction too. (recommended)

4. Killing in OS level

If you want to kill a OS level oracle process(server process) responsible for the session then issue

orakill ORACLE_SID spid;

from command prompt for windows.

kill -9 spid

for unix flavours.(the spid for the session can be acquired from the v$process, refer the query in point 1)

Oracle archive_lag_target

The archive_lag_target parameter is very useful for DataGuard setup, since this parameter will switch the logfile in the specified time interval. By default the parameter is 0 sec. The unit for this parameter is in seconds.

Responsibilities of an ORACLE dba

    As I am a DBA for more than a year I have came to know lot of things happening in the industry. I would like to share all those with you. A DBA is solely responsible for the database, so ensure before firing any commands. When you are working with OLTP environment, you have to be more cautious. You have to know all the features and differences between oracle versions.version info. He should be able to work on any OS platforms, also should have some idea about hardware setup, memory management like RAID, san storage etc. You all may think that a DBA should do only backup, recovery, monitoring operations. But other than this there are lot of roles a DBA has. It differs based on the industry and institutions you work.oracle.


Installing, upgrading, and patching Oracle Database software

Designing databases based on requirements

Creating Oracle databases

Testing backup and recovery strategy, and prepare a plan for failure cases.

Backup data regularly

Making connectivity for clients to connect the database across network.

Startup and shutdown the database

Managing tablespaces and datafiles

Managing users and security

Managing database objects

tuning database for performance

Proper usage of Oracle corp support while needy

Evaluating and testing new database features