Monday, October 19, 2009

Applying 10.2.0.4 CRS Patchset to a 10.2.0.1

Applying 10.2.0.4 CRS Patchset to a 10.2.0.1:

First Check clusterware verion.
[root@vm2 bin]# ./crsctl query crs activeversion
CRS active version on the cluster is [10.2.0.1.0]

Then Stop all database resources
Shutdown database instances, ASM instances, Listener etc..

Download 10.2.0.4 patchset from Metalink.

./runInstaller

Select CRS_HOME path






To complete the installation of this patchset, you must perform the following tasks on each node:

1. Log in as the root user.
2. As the root user, perform the following tasks:

a. Shutdown the CRS daemons by issuing the following command:
/u04/crs/oracle/product/10.2.0/crs/bin/crsctl stop crs
b. Run the shell script located at:
/u04/crs/oracle/product/10.2.0/crs/install/root102.sh
This script will automatically start the CRS daemons on the
patched node upon completion.

3. After completing this procedure, proceed to the next node and repeat.



Node1:

[root@vm1 bin]# ./crsctl stop crs
Stopping resources.
Successfully stopped CRS resources
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.
[root@vm1 bin]# cd ..
[root@vm1 crs]# cd install
[root@vm1 install]# ./root102.sh
Creating pre-patch directory for saving pre-patch clusterware files
Completed patching clusterware files to /u04/crs/oracle/product/10.2.0/crs
Relinking some shared libraries.
Relinking of patched files is complete.
WARNING: directory '/u04/crs/oracle/product/10.2.0' is not owned by root
WARNING: directory '/u04/crs/oracle/product' is not owned by root
WARNING: directory '/u04/crs/oracle' is not owned by root
WARNING: directory '/u04/crs' is not owned by root
WARNING: directory '/u04' is not owned by root
Preparing 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 3 is 10G Release 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 1: vm1 vm1-priv vm1
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
clscfg -upgrade completed successfully
[root@vm1 install]# cd ../bin
You have new mail in /var/spool/mail/root
[root@vm1 bin]# ./crsctl query crs activeversion
CRS active version on the cluster is [10.2.0.4.0]

Node2:

[root@vm2 bin]# ./crsctl stop crs
Stopping resources.
Successfully stopped CRS resources
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.
[root@vm2 bin]# cd ..
[root@vm2 crs]# cd install
[root@vm2 install]# ./root102.sh
Creating pre-patch directory for saving pre-patch clusterware files
Completed patching clusterware files to /u04/crs/oracle/product/10.2.0/crs
Relinking some shared libraries.
Relinking of patched files is complete.
WARNING: directory '/u04/crs/oracle/product/10.2.0' is not owned by root
WARNING: directory '/u04/crs/oracle/product' is not owned by root
WARNING: directory '/u04/crs/oracle' is not owned by root
WARNING: directory '/u04/crs' is not owned by root
WARNING: directory '/u04' is not owned by root
Preparing 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 3 is 10G Release 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 2: vm2 vm2-priv vm2
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
clscfg -upgrade completed successfully

[root@vm2 bin]# ./crsctl query crs activeversion
CRS active version on the cluster is [10.2.0.4.0]
[root@vm2 bin]#

Friday, October 16, 2009

How to apply CPU (Critical Patch Update )?

How to apply CPU (Critical Patch Update )Jul2006 in database server(DB Version 10.2.0.1)?
This document describes to apply CPU patch Jul2006 in Linux(DB Version 10.2.0.1).Download following patch from Metalink 5225798.

Critical patch update (CPU) patches are cumulative, which means fixes from previous Oracle security alerts and critical patch updates are included.

You must use the OPatch utility release 10.2.0.1.2. You can download it from OracleMetaLink with patch 4898608
To check accessibility to the inventory you can use the command
$ opatch lsinventory

Patch Installation for 10.2.0.1 release database:
Step 1: Ensure that your Oracle Database installation is the same release for which you are applying this patch. In other words, only apply the Release 10.2.0.1 CPUJul2006 patch to an Oracle Database Release 10.2.0.1.

Step 2: Shut down all instances and listeners associated with the Oracle home that you are updating

Step 3: Ensure that the $PATH has the following executables: make, ar, ld, and nm.

Step 4: Set your current directory to the directory where the patch is located and then run the OPatch utility by entering the following command:

$cd 5225798
$opatch apply
Post installation procedure:
After applying that patche
Step 1: Start all database instances running from the Oracle home that you are patching
Step 2: For each database instance running of the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the catcpu.sql script as follows

$cd $ORACLE_HOME/cpu/CPUJul2006
$sqlplus "/as sysdba"
SQL> STARTUP
SQL> @catcpu.sql
SQL> QUIT

Step 3: Check the log file for any errors

Step 4:
If catcpu.sql reports any Invalid Objects, run the following commands:
$cd $ORACLE_HOME/rdbms/admin
$sqlplus "/as sysdba"
SQL> STARTUP
SQL> @utlrp.sql
You can check for any invalid objects by executing following statement:
SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS= 'INVALID';

Rollback Patch:
Step 1: Set your current directory to the directory where the patch is located, for example:
$cd 5225798

Step 2: Shut down all instances and listeners associated with the Oracle home that you are updating. Run the OPatch utility using the rollback argument. For example:

$opatch rollback -id 5225798

Post Deinstallation Instructions:

Step 1: Start all database instances running from the Oracle home that you had patched

Step 2: For each database instance running out of the ORACLE_HOME that was patched, connect to the database using SQL*Plus as SYSDBA and run catcpu_rollback.sql as follows:
$cd $ORACLE_HOME/cpu/CPUJul2006
$sqlplus "/as sysdba"
SQL> STARTUP
SQL> @catcpu_rollback.sql
SQL> QUIT

Step 3: Inspect the logfile for any errors.
If catcpu_rollback.sql reports any Invalid Objects, Oracle recommends that you compile the invalid objects as follows:

$cd $ORACLE_HOME/rdbms/admin
sqlplus "/as sysdba"
SQL> STARTUP
SQL> @utlrp.sql

You can check for any invalid objects by executing following statement:
SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = 'INVALID';

Wednesday, October 14, 2009

Upgrade database from 10.2.0.2 from 10.2.0.4 using coldbackp

Upgrade database from 10.2.0.2 from 10.2.0.4 using:
Following procedure to upgrade Oracle 10.2.0.2 to 10.2.0.4 using cold backup of 10.2.0.2 database
Step 1: backup database (it is running on 10.2.0.2 version) and control file script
Step 2: create parameter file,dump directories and password file for cloning database (10.2.0.4 version)
Step 3: restore backup in clone database server
Step 4: Login as oracle user in clone database server
Step 5: sqlplus "/as sysdba"
sql> startup nomount

sql>CREATE CONTROLFILE SET DATABASE "testdb" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 100
LOGFILE
GROUP 1 '/u01/oradata/log1a.log' SIZE 50M,
GROUP 2 '/u01/oradata/log2a.log' SIZE 50M,
GROUP 3 '/u01/oradata/log3a.log' SIZE 50M,
DATAFILE'/u01/oradata/users.dbf',
'/u01/oradata/tools.dbf',
'/u01/oradata/system01.dbf',
'/u01/oradata/undotbs01.dbf',
'/u01/oradata/sysaux01.dbf'
CHARACTER SET US7ASCII;
/
Control file created.

sql> alter database open resetlogs upgrade;

After that run following scripts
sql>@$ORACLE_HOME/rdbms/admin/catupgrd.sql

The following script was executed for the utility upgrade status check which was successful as well.
SQL> @$ORACLE_HOME/rdbms/admin/utlu102s.sql
Recompile invalid objects run following scripts
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
sql>select * from global_name;
SQL> alter database rename global_name to testdb;

Then check,
SQL> select COMP_NAME,VERSION,STATUS from dba_registry ;

COMP_NAME VERSION STATUS
---------------------------------------- ---------- ---------------------------------
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 VALID
Status must be VALID
if Oracle Database Packages and Types compenent will be in INVALID status we have to run catproc.sql script in upgrade mode.

Tuesday, October 6, 2009

How to export single table xls format?

Export single table xls format:
We can use use third party tools like toad,plsql developer etc.. to export table data in xls format .
In toad
1.excute query
2.right click and select save as and choose XLS format

Following procedure can be used for single table export in xls format.

SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET TIMING OFF
SET PAUSE OFF
SET PAGESIZE 0
SET LINESIZE 255
SET TRIMSPOOL ON
spool table.xls
set feed off markup html on spool on
@@your Sql
set markup html off spool off

Monday, October 5, 2009

Attach Oracle Home

Attach Oracle Home:

First copy oracle sofware binaries from one server to another server using unix command tar etc.. .After copying that binaries

you must first set your ORACLE_HOME environment variable to the new directory you just copied over. for example , the ORACLE_HOME variable for the solution is set to /apps/oracle/product/10.2.0/db_1 .Next change your dircetory to the oui/bin subdirectory.

cd $ORACLE_HOME/oui/bin

From the oui/bin directory,execute the runInstaller commands as shown here to attach the new oracle home to oraInventory:

./runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc \

ORACLE_HOME=/apps/oracle/product/10.2.0/db_1 ORACLE_HOME_NAME='ora10ghome3'

upon successfull attach of the OracleHome you will receive message stating that AttachHome was successful

The invPtrLoc parameter specifies the fully qualified file name that contains location of the oraInventory. the oraInst.loc file is located in the /etc directory in the Linux Operating system and look like this

inventory_loc=/apps/oracle/oraIventory

inst_group=oinstall

What is oraInventory ?

oraInventory is repository(directory) which store/records oracle software products & their ORACLE_HOME location on a machine.