Table Partitioning in Oracle

In this section I’ll be discussing about the some types of partitioning options available in Oracle , its advantages and also provide some examples.

Why Partitioning?

Oracle partitioning is mainly used for manageability, availability and performance of oracle tables. Partitioning allows tables, indexes, materialized views and Index-organized tables to be further divided in to smaller manageable pieces. Partitioning enables the database objects to be managed and accessed at a finer level of granularity.

Partitioning for manageability

The partitioning option allows indexes and tables to be partitioned in to smaller manageable units. Using partition tables, DBA’s can perform maintenance on certain partitions while the rest of the partitions are still accessed by the applications.

A typical usage of partitioning for manageability is tos upport a “rolling window” load process in a data warehouse. Imagine you have to load a table with data on a monthly basis. You can take advantage of the range partition option so that each partition contains a months worth of data.
If you have to purge 6 month old data from a table on a monthly basis. The range partitioning offers a better solution. You can just delete a partition rather than issuing a DELETE command which creates additional load on the database.

Partitioning for Performance

By limiting the amount of data to be examined or operated on and by enabling parallel execution, the Oracle Partitioning option provides a number of performance benefits.

Partition Pruning
Partition pruning is the simplest and also the most substantial means to improve performance. Partition pruning can often improve query performance by several orders of magnitude.
Imagine a Orders table containing historical records of orders and the table data is partitioned by week. A query requesting data for a single week would only access a single partition of the orders table, thus by improving the performance by a bigger magnitude. Partition pruning works with all of Oracle’s other performance features. Oracle will utilize partition pruning in Conjunction with any indexing technique, join technique or parallel access method.

Partition Wise Joins

Partitioning can also improve the performance of multi-table joins, by using a technique known as partition-wise join. Partition-wise join can be applied with two tables being joined together and both of these tables are partitioned on the join key. Partition-wise joins breaks a large join in to smaller joins that occur between each of the partitions, completing the overall join in less time. This offers significant performance benefits both for serial and parallel execution.

Parallel Execution

Partitioning enables parallel execution of UPDATE, DELETE and MERGE statements. Oracle will parallelize SELECT statements and INSERT statements when accessing both partitioned and non-partitioned database objects. UPDATE, DELETE and MERGE statements can be parallelized for both partitioned and non-partitioned database objects when no bit map indexes are present. In order to parallelize the operations on objects having bit map indexes , the target table must be partitioned. Parallel execution of sql statements can vastly improve performance, particularly for UPADTE, DELETE or MERGE operations involving large volumes data.

Partitioning for Availability

The DBA can store different partitions in different tablespaces which would allow him/her to perform backup/recovery operations on each individual partition, independent of the other partitions in the table.

Partitioned database objects provide partition independence. If any one of the partitions become unavailable, all other partitions of the table remain online and available. Applications can still use the available partitions while the DBA can work on fixing the failed partition/partitions.


Types of Partitioning

· Range Partitioning
· Hash Partitioning
· List Partitioning
· Composite partitioning

In the next post, I’ll be discussing the above-mentioned partitioning options in detail.

Uni directional streams set up..

Oracle streams allows for back and forth repliaction between two active database servers.

Here I'll discuss the basic steps involved in setting up a streams replication in oracle 10g.

In the example provided I am setting up a upstream replication for table customer residing on binfo schema at source_db to binfo.customer at dest_db.

Init parameters
--------------------

certain initilalization parameters are required to be set at both source and destination instances before setting up streams.

Here are the parameters.

compatible - This parameter should be same on both sides.
Global_names = True --> Database links should match the global names.
Job_queue_process --> This parameter should be set to atleast 2(two).
Parallel_max_servers --> this parameter should be set to atleast 6 (six).
SGA_TARGET --> should be set .. else you need to set "streams_pool_size"
domain_name --> make sure this value is set.

Tns Names
-----------

Add Tnsnames entries for the source and destination databases on both sides.

Source_db = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521)) ) (CONNECT_DATA = (service_name = source_db) ) )
dest_db = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521)) ) (CONNECT_DATA = (service_name = dest_db) ) )

Create Tablespace
-----------------
Create separate tablespace for streams admin user objects on both sides.

create tablespace streams_ts datafile '/u01/oradata/sorce_db/streams_ts_001.dbf' size 250M;

and

create tablespace streams_ts datafile '/u01/oradata/dest_db/streams_ts_001.dbf' size 250M;

Create streams administrator user on both sides
------------------------------------------------

$> sqlplus / as sysdba

create user "STRMADMIN" identified by "xxxxxxx"
default tablespace streams_ts
temporary tablespace temp
quota unlimited on streams_ts;

Grant the following privs to strmsadmin user at the dest_db by looging in as sysdba

-----------------------------------------------------------------------------------
$> sqlplus / as sysdba
grant CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE to "STRMADMIN"; GRANT DBA TO STRMADMIN; GRANT EXECUTE ON sys.dbms_aq TO STRMADMIN; GRANT EXECUTE ON sys.dbms_aqadm TO STRMADMIN;


BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE
(
privilege => 'ENQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE
);
END;
/
BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE
(
privilege => 'DEQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE
);
END;
/

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE
(
privilege => 'MANAGE_ANY',
grantee => 'STRMADMIN',
admin_option => TRUE
);
END;
/

BEGIN
DBMS_AQADM.GRANT_TYPE_ACCESS
(
user_name => 'STRMADMIN'
);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
(
privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE
);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE
);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE
(
privilege => DBMS_RULE_ADM.EXECUTE_ON_EVALUATION_CONTEXT, object_name => 'SYS.STREAMS$_EVALUATION_CONTEXT',
grantee => 'STRMADMIN',
grant_option => FALSE
);
END;
/


GRANT EXECUTE ON sys.dbms_capture_adm TO STRMADMIN; GRANT EXECUTE ON sys.dbms_apply_adm TO STRMADMIN; GRANT EXECUTE ON sys.dbms_rule_adm TO STRMADMIN;GRANT SELECT_CATALOG_ROLE TO STRMADMIN;


BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT, grantee => 'STRMADMIN',
grant_option => TRUE
);
END;
/

GRANT EXECUTE ON SYS.dbms_streams_adm TO STRMADMIN; GRANT ALL PRIVILEGES TO STRMADMIN;

Create application user(schema) on both sides

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

create user binfo identified by binfo
default tablespace binfo_data
temporary tablespace temp
quota unlimited on binfo_data;
grant connect, dba to binfo_data;

Connect to Binfo at source_db

---------------------------------------
create table customer
( cust_id number(10) not null,
cust_Fn varchar2(20) not null,
cust_Ln Varchar2(20) not null,
Addr Varchar2 (50),
state varchar2(2));

alter table customer add constraint customer_pk primary key (cust_id);

Connect to strmadmin at the remote_db

--------------------------------------------------
$> sqlplus strmadmin/@remote_db
Create streams queue
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_user => 'STRMADMIN');
END;
/

Add apply rules for tables at the destination database
-------------------------------------------------------------

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES
( table_name => '"BINFO"."CUSTOMER"',
streams_type => 'APPLY',
streams_name => 'STRMADMIN_SOURCE_DB_',
queue_name => '"STRMADMIN"."STREAMS_QUEUE"',
include_dml => true,
include_ddl => true,
source_database => 'SOURCE_DB.XXX.COM');
END;
/

Connect to sysdba at the source database

-------------------------------------------------------
Enable supplemental logging at table level (optional) Supplemental logging helps capture additional information to redologs for row identification

alter table binfo.customer add supplemental log group cust_loggrp (cust_id, cust_fn, cust_ln);
Switch log file

sqlplus> ALTER SYSTEM SWITCH LOGFILE;

Grant the required privileges to strmadmin user

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

grant CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE to "STRMADMIN";

GRANT DBA TO STRMADMIN;GRANT EXECUTE ON sys.dbms_aq TO STRMADMIN;

GRANT EXECUTE ON sys.dbms_aqadm TO STRMADMIN;

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE
( privilege => 'ENQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/
BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE
( privilege => 'DEQUEUE_ANY',
grantee => 'STRMADMIN',
admin_option => FALSE);
END;
/
BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE
( privilege => 'MANAGE_ANY',
grantee => 'STRMADMIN',
admin_option => TRUE);
END;
/
BEGIN
DBMS_AQADM.GRANT_TYPE_ACCESS
( user_name => 'STRMADMIN');
END;
/
BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
( privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE)
;
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
( privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
( privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
( privilege => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
( privilege => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
( privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => TRUE);

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
( privilege => DBMS_RULE_ADM.CREATE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
( privilege => DBMS_RULE_ADM.ALTER_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
( privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

BEGIN
DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE
( privilege => DBMS_RULE_ADM.EXECUTE_ON_EVALUATION_CONTEXT,
object_name => 'SYS.STREAMS$_EVALUATION_CONTEXT',
grantee => 'STRMADMIN',
grant_option => FALSE );
END;
/

GRANT EXECUTE ON sys.dbms_capture_adm TO STRMADMIN;GRANT EXECUTE ON sys.dbms_apply_adm TO STRMADMIN;GRANT EXECUTE ON sys.dbms_rule_adm TO STRMADMIN;GRANT SELECT_CATALOG_ROLE TO STRMADMIN;

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE
( privilege => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
grantee => 'STRMADMIN',
grant_option => TRUE);
END;
/

GRANT EXECUTE ON SYS.dbms_streams_adm TO STRMADMIN;

GRANT ALL PRIVILEGES TO STRMADMIN;

Connect to strmadmin at source_db

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

Create a database link

CREATE DATABASE LINK
"dest_db.xxx.COM" connect to "STRMADMIN" identified by "STRMADMIN" using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dest_db.xx.com)))';

Create streams queue

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE
( queue_user => 'STRMADMIN');
END;
/

Add capture rules for tables at the source site

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

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES
( table_name => '"BINFO"."CUSTOMER"',
streams_type => 'CAPTURE',
streams_name => 'STRMADMIN_CAPTURE',
queue_name => '"STRMADMIN"."STREAMS_QUEUE"',
include_dml => true,
include_ddl => true,
source_database => 'SOURCE_DB.XX.COM');
END;
/

Add propagation rules for tables at the source database

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES
( table_name => '"BINFO"."CUSTOMER"',
streams_name => 'STRMADMIN_PROPAGATE',
source_queue_name => '"STRMADMIN"."STREAMS_QUEUE"',
destination_queue_name => '"STRMADMIN"."STREAMS_QUEUE"@SOURCE_DBxx.COM',
include_dml => true,
include_ddl => true,
source_database => 'source_db.xx.COM');
END;
/

Export the table from source_db and import them in to dest_db.

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

Here you will instantiate the scn at dest_db along with import. You can use data pump as well.

exp USERID="STRMADMIN"@source_db TABLES="binfo"."customer" FILE=tables.dmp GRANTS=Y ROWS=N LOG=exportTables.log OBJECT_CONSISTENT=Y INDEXES=Y

imp USERID="STRMADMIN"@dest_db FULL=Y CONSTRAINTS=Y FILE=tables.dmp IGNORE=Y GRANTS=Y ROWS=N COMMIT=Y LOG=importTables.log STREAMS_CONFIGURATION=N STREAMS_INSTANTIATION=Y

Startup operations

----------------------------
You are pretty much done with the set up at this point .you need to start the capture and apply process.
connect to strmadmin@dest_db and start the apply process first

DECLARE v_started number;
BEGIN
SELECT decode
(status, 'ENABLED', 1, 0) INTO v_started FROM DBA_APPLY WHERE APPLY_NAME = 'STRMADMIN_source_db_'; if (v_started = 0) then
DBMS_APPLY_ADM.START_APPLY(apply_name => 'STRMADMIN_source_db_');
end if;
END;
/

Connect to strmadmin at the source database and start the capture process

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

DECLARE
v_started number;
BEGIN
SELECT decode(status, 'ENABLED', 1, 0) INTO v_started FROM DBA_CAPTURE WHERE CAPTURE_NAME = 'STRMADMIN_CAPTURE';
if (v_started = 0) then
DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'STRMADMIN_CAPTURE');
end if;
END;
/

Now test the replication.

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

Insert some values in to the source table and see if its getting replicated.

I'll discuss more about streams troubleshooting in Future posts.

Building the 10g rac Database

Once you have successfully installed Oracle 10g binaries and CRS. Its time to build the database. This is not a complicated task for a DBA who has built oracle instances and databases. There are few RCA specific parameters that need to be added to the init.ora file. I'll not cover this topic in detail, but will provide enough information to build a new database.
1) The first task is to create the initialization file. I'll list the init parameters that are requuired for RAC.
db_name =
instance_name =
instance_number = 1
cluster_database_instances = 2local_listener = '(address=(protocol=tcp)(host=hostname)(port=1521))
cluster_database = true
thread = 1
2) I use DBCA to generate scripts to build the database. If you already have the scripts to build the database, you could use that as well.
3) Make sure the "cluster_database" parameter is set to "false" before building the database else the create database command will fail.
4) Create the database as you would create a non-RAC database.
5) Make sure you execute the script "$ORACLE_HOME/rdbms/admin/catclust.sql" after executing the create catalog scripts.
6) The next job is to create additional undo segments for other instances on the RAC cluster.Please add the corresponding undo tablespace name in the respective init.ora for instances.
create undo tablespace UNDO02 datafile size 10240m reuse autoextend off;
7) Create additional redo thread for other instances in the RAC cluster.
alter database add logfile thread 2
group 21 (‘//RD21.rdo’) size
…
1024m reuse;
alter database enable thread 2;
8) Shutdown the first instance.
sqlplus > shutdown immediate
9) Edit the init file and set "cluster_database to true"
startup the first instance.
10) Copy the initfile from the primary node and create initfile for second instance.
Make sure you edited the following parameters.
Thread = 2
instance_name =
instance_number = 2
local_listener = '(address=(protocol=tcp)(host=hostname)(port=1521))'
undo_tablespace = UNDO02
11) startup the second instance.
sqlplus> startup
12) verify both the databases are up and running from any node.
sqlplus> select * from gv$instance;
The query result should dsiplay all the instances you created for the RAC database.
13)Add database to CRS using "srvctl" commands.
$srvctl add database -d -o $ORACLE_HOME -y manual
$srvctl add instance -d -i -n
$srvctl add instance -d -i -n
14) if everything goes well, the crs_stat -t command should list the database and instances.
$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....re2.gsd application ONLINE ONLINE nodeA
ora....re2.ons application ONLINE ONLINE nodeA
ora....re2.vip application ONLINE ONLINE nodeA
ora.fvdev1.db application ONLINE ONLINE nodeA
ora....11.inst application ONLINE ONLINE nodeA
ora....12.inst application ONLINE ONLINE nodeB
ora....b02.gsd application ONLINE ONLINE nodeB
ora....b02.ons application ONLINE ONLINE nodeB
ora....b02.vip application ONLINE ONLINE nodeB

Next step is creating Listeners for RAC
--------------------------------------
1) Set the TNS_ADMIN variable in CRS.
$srvctl setenv nodeapps -n -t TNS_ADMIN=
$srvctl setenv nodeapps -n -t TNS_ADMIN=
2) Start netca from any one node after setting the "DISPLAY" variable.
$netca
3) select Cluster configuration and hit next.

4) select both the nodes to configure.

5) select listener configuration and add listener.

6) select the default listener name.

7) Select protocol "tcp" and port 1521 on the next screen.

8)sample content of the listener.ora file.

SID_LIST_LISTENER_name =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0)
(PROGRAM = extproc)
)
)

LISTENER_name =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521)(IP = FIRST))
)
)

9) set up client side TAF by adding TAF tnsnames entry into tnsnames.ora file of the Oracle clients.

e.g.

name =
(DESCRIPTION =
(load_balance = off) --> you can set it to on if you choose
(failover = on)
(ADDRESS = (PROTOCOL = TCP)(HOST = nodea)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = nodeb)(PORT = 1521))
(CONNECT_DATA =

(SERVICE_NAME = db_name)
(failover_mode =
(type = select)
(method = basic)
(retries = 5)
(delay = 1)
)
)
)

Validation

----------

Try the following commands.

1) crs_stop -all

2) crs_start -all

3)crs_stat -t

4)srvctl start/stop database –d $DB_NAME

5)srvctl start/stop instance –d $DB_NAME –i $INSTANCE_NAME

6) srvctl start/stop nodeapps –n $node

Back up the vote disk on both nodes. This step needs to be run any time after a new node is added or an existing node is removed.

$ cp /u01/app/oracle/dbdata/vote/vote_file /u01/app/oracle/cluster/crs/backup/crs/vote_file.`date +%y%m%d`

$verify all components are ONLINE using "crs_stat -t"

Name Type Target State Host

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

ora....E2.lsnr application ONLINE ONLINE nodeA

ora....re2.gsd application ONLINE ONLINE nodeA

ora....re2.ons application ONLINE ONLINE nodeA

ora....re2.vip application ONLINE ONLINE nodeA

ora.fvdev1.db application ONLINE ONLINE nodeA

ora....11.inst application ONLINE ONLINE nodeA

ora....12.inst application ONLINE ONLINE nodeB

ora....02.lsnr application ONLINE ONLINE nodeB

ora....b02.gsd application ONLINE ONLINE nodeB

ora....b02.ons application ONLINE ONLINE nodeB

ora....b02.vip application ONLINE ONLINE nodeB

Patching RDBMS binaries for Oracle 10g R2

Patching RDBMS binaries is pretty straight forward. I'll not be pasting any screen shots, but explain the steps required.

1) DBA sets the Oracle home to RDBMS home directory.

2) Execute "runInstaller" from staging area.

3) Validate the Oracle_home and hit next.

4) The installer automatically picks the cluster installation, if "/etc/oraInst.loc or /var/opt/oracle/oraInst.loc (in solaris) " is pointing to CRS_HOME.
hit next

5) Verify the summary and hit Install.

6) Excute "root.sh" on all nodes in the cluster and then exit OUI.

Installing 10G R2 binaries in Oracle RAC environment.

Once you have successfully installed CRS, The Oracle 10gR2 binary installation is relatively easy.
1) DBA sets the ORACLE_HOME for the RDBMS binary and the verify the environment variables.
$> . oarenv
$> env grep -i oracle
2) Execute "runInstaller" from the staging area and select the custom installation.

3) Specify the Oracle_home details and hit next

4) specify cluster installation and check all nodes on the cluster.

5) Specify the required components for installation.

6) Make sure that the prerequisite checks have no errors.

7) Provide the "dba" group name.

8) Select "Install software only "(I prefer to build the database later).

9) Review the summary screen and hit continue.

10) execute "root.sh" on both nodes from the location displayed on the GUI.

$rootsh=/path/to/root.sh

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/10.2.0

Enter the full pathname of the local bin directory: [/usr/local/bin]: ..

Entries will be added to the /var/opt/oracle/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.

11) Hit O.K. and exit the GUI.

Oracle CRS 10.2.0.4 patching..

It has been a while, I have posted something on my blog. I hate my blog to look like a ghost town. I'll discuss the Oracle 10.2.0.4 patching proceess here.

I used to install Oracle 10g r2 base binaries right after installing Oracle CRS. I was hit by a OCR corruption issue which created lot of core dumps and was forced to rebuilt my OCR files. The avoid any such issue, I would suggest Patch oracle CRS first and the install RDBMS software.

1) The first step to apply 10.2.0.4 patch to CRS is to shutdwon CRS on both nodes.

Usually the "root" user will have the privileges to shitdown the CRS. The SA or root user can shutdown crs using the command

$> crsctl stop crs or

$/etc/init.d/init.crs stop

Make sure CRS is shut down.

$ps -ef grep crs

root 968 1 0 12:11:45 ? 0:00 /bin/sh /etc/init.d/init.crsd run

2) Make sure you change the ownership of "VIPCA" back to "oracle" from "root".

3) Set the environment varaiable for "crs".

$> ./.oraenv crs

check the oracle_home variable is to CRS_HOME.

env grep ORACLE_HOME

4) cd to the 10.2.0.4 patch set directory and execute "runInstaller". The crs and rdbms use the same 10.20.4 patset(there is no seperate 10204 patch set for CRS).

5) DBA confirms the CRSHOME and hit next

6) DBA confirms the node names in cluster and hit next

7) Validate the summary page and start installation.

8) At the end of the installation, Pl execute the root102.sh on all the nodes as instructed in the GUI.

9)log in as root and execute on all nodes one by one. you canignore the warnings below.

$/u01/app/oracle/cluster/crs/install/root102.shCreating pre-patch directory for saving pre-patch clusterware filesCompleted patching clusterware files to /u01/app/oracle/cluster/crsRelinking some shared libraries.ar: writing /u01/app/oracle/cluster/crs/lib/libn10.aar: writing /u01/app/oracle/cluster/crs/lib32/libn10.aar: writing /u01/app/oracle/cluster/crs/lib/libn10.aRelinking of patched files is complete.WARNING: directory '/u01/app/oracle/cluster' is not owned by rootWARNING: directory '/u01/app/oracle' is not owned by rootPreparing to recopy patched init and RC scripts.Recopying init and RC scripts.Startup will be queued to init within 30 seconds.Starting up the CRS daemons.Waiting for the patched CRS daemons to start. This may take a while on some systems..10204 patch successfully applied.clscfg: EXISTING configuration version 3 detected.clscfg: version 4 is 10G Release 2.Successfully accumulated necessary OCR keys.Using ports: CSS=32845 CRS=45632 EVMC=43567 and EVMR=34834.node : node 0: nodeA nodea-priv1 nodeBCreating OCR keys for user 'root', privgrp 'dba'..Operation successful.clscfg -upgrade completed successfully

10) Check CRS by issueing crs_stat -t and exit OUI.

$>crs_stat -t

Name Type Target State Host

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

ora.node1.gsd application ONLINE ONLINE node1

ora.node1.ons application ONLINE ONLINE node1

ora.node1.vip application ONLINE ONLINE node1

ora.node2.gsd application ONLINE ONLINE node2

ora.node2.ons application ONLINE ONLINE node2

ora.node2.vip application ONLINE ONLINE node2

Oracle RAC Install on AIX using Veritas Clusterware.

Oracle RAC Install on AIX using Veritas Clusterware.

This document highlights the steps to install and new Oracle RAC binary on AIX cluster system using Veritas Clusterware without ASM. I’ll be discussing only the CRS/ORACLE installation and database setup. There is a lot of work that needs to be done prior to installing the Oracle software,(ie os, hardware an network setup) which we’ll not be covering here.

Assumptions: - The hardware is ready and the OS, network and Heart beat are setup properly. Veritas software is loaded and the storage disks are configured (A sample layout of the storage I have used for RAC is shown below (Table 1.00)

Table 1.00
FILE SYSTEM NAME SIZE COMMENT

/u0/app/oracle/cluster/crs 12GB CRS home and Logs
/u02/app/oracle/product 16GB ORACLE_HOME
/u03/app/oracle/admin 12GB Oracle dump directory
/uo4/misc 20GB Work space
/u05h/app/oracle/arch 20GB Archive space
/u06h/app/oracle/ocr 12GB For OCR
/u07h/app/oracle/vote 12GB For Vote disk
/u08h/app/oracle/oradata/system01 10GB For system datafiles
/u09h/app/oracle/oradata/system02 10GB For system datafiles
/u10h/app/oracle/oradata/system03 10GB For system data files.
/u11h/,,,….. As needed For apps data files …… As needed “
Uxxh As needed “

Note : - the letter “h” denotes that the filesystem is global (clustered). I have used local file system for Oracle and CRS binaries.These sizes are sample sizes need not be suitable for your environment.

The Oracle and CRS binaries were installed using the “oracle:dba” account. No two separate accounts were used for “oracle” and “CRS” installation. The oracle and CRS binaries were installed on non-global file systems.

Installation Process.

Before you begin to perform the installation.
· Copy the required binaries “the oracle clusterware for 10g”(The s/w can be downloaded from http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10201aixsoft.html) · Make sure that the global file systems are accessible from both the nodes.
· Make sure you could “ssh’ to both the boxes. (From node1 to node2 and viceversa).
· Make sure VCS is online and it good state.
· Verify the hearbeat connection.
· Make sure your environment is set to CRS_HOME.
ORACLE_HOME=/u01/app/oracle/crs/bin ; export ORACLE_HOME ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_SID=crs ; export ORACLE_SID umask 022 EXPORT PATH=$ORACLE_HOME/bin;$PATH EXPORT LD_LIBRARY_PATH=$ORACLE_HOME/lib
· Make sure you have set the oracle recommended kernel parameters.
· Verify the /etc/hosts file on all the nodes. It should have a public IP address for both the nodes,

private IP address for both nodes and a VIP (virtual IP ) configured for both nodes.

$> more /etc/hosts
# Public
xxx.xxx.xx.xxx node1.localdomain node1
xxx.xxx.xx.xxx node2.localdomain node2

#Private
xxx.xxx.xx.xxx node1-pvt.localdomain node1-pvt
xxx.xxx.xx.xxx node2-pvt.localdomain node2-pvt

#Virtual
xxx.xxx.xx.xxx node1-vrt.localdomain node1-vrt
xxx.xxx.xx.xxx node2-vrt.localdomain node2-vrt

CRS Installation
From a DBA perspective CRS installation can be complex, if the hardware is not not configured properly. It’s better to have a good understanding about the concepts before proceeding. Understand what the VIPS are for how RAC is using them. I would recommend reading the oracle RAC installation documents and get a thorough understanding of the hardware requirements and setup details.
1. Start the media installation for CRS.

./runInstaller

Login as root on another screen and run “rootpre.sh”. Once finished continue here by entering “y” at the prompt.


2. You’ll see the Oracle installer welcome page. Hit next


3. Enter your inventory path and group name.


4. Enter the crs_home name and path to install the crs. Hit next. You might see a warning saying that “The directory “ is not empty, in most cases this is O.k. you can proceed.


5. Hit next. You will see a screen with the results of the pre-requisite checks. Check for any warning and proceed. The next screen you see might look something similar to this.

If you don’t see the node names in the screen, it shows a problem(pl see issues faced) . If this screen is populated like shown above, then you are good. The n/w address information most of the time will be auto generated. Pl edit the address and
change it according to your settings. Hit ok and then next.



6. Edit and change the n/w address if necessary.




7.In the next screen the DBA checks the external redundancy box and enter global file name allocated for Oracle cluster registry (OCR). I have decided to go with external redundancy.


8.Next screen prompts you to enter the Voting Disk Location. Check the external redundancy and enter the voting disk location


9. The next screen shows you the summary. Review the summary and hit Install. The CRS installation begins and

10. The installer stops and asks the following scripts to be run. In some cases I have seen it asks you to run only root.sh. That’s normal. Don’t hit ok. Continue from step 11.


11. Execute the scripts and hit OK


12. Once the scripts are successfully executed. Log in as root on a separate window and start VIPCA( Virtual IP config assistant).


13.

14. select the public interface from the list


15. Enter the virtual alias. Hit next review the summary and hit Finish.Exit when done


17. If everything goes well go back to step 10 and hit OK at CRS GUI install window. The installer does a final configuration check and exits.

18. Look at the status of CRS,

I would check for the CRS processes running on the box.
ps –ef grep –i crs
You should see: - crsd.bin, ocssd.bin, evmd.bin, emvlogger.bin

You can also execute the command crs_stat –t . which should see the “gsd”, “ons” and “vip” processes.

oracle@> crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.node1.gsd application ONLINE ONLINE node1
ora.node1.ons application ONLINE ONLINE node1
ora.node1.vip application ONLINE ONLINE node1
ora.node2.gsd application ONLINE ONLINE node2
ora.node2.ons application ONLINE ONLINE node2
ora.node2.vip application ONLINE ONLINE node2

Issues Faced

• Step 5 the n/w addresses were not populated.

This shows that “oracle” user which performs the installation doesn’t have read privs on some system files. See whether oracle user has read permissions on /etc/llt* files.

• The specified nodes are not clusterable on step 5

Look in /etc/hosts file and make sure that the Ip address and names are entered in the same format as shown in the last bullet point of installation process and you IP addresses are right.

Conclusion

For first timer it’s slightly puzzling and as you do more you get to into the groove well. As I said in the beginning its better to have a good understanding of the concept before installing CRS. I’ll be discussing the Oracle binary installation and Patching process next.



I'll be discussing more of Oracle RDBMS install and pacthing process in the near future stay tuned.