How to Apply patch on Oracle RAC

How to Apply patch on Oracle RAC
 
Prerequisite:-
 
>> Check Oracle service status
[oracle@rac-node1 ~]$crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE dbsr...ode1
ora....E1.lsnr application ONLINE ONLINE dbsr...ode1
ora....de1.gsd application ONLINE ONLINE dbsr...ode1
ora....de1.ons application ONLINE ONLINE dbsr...ode1
ora....de1.vip application ONLINE ONLINE dbsr...ode1
ora....SM2.asm application ONLINE ONLINE dbsr...ode2
ora....E2.lsnr application ONLINE ONLINE dbsr...ode2
ora....de2.gsd application ONLINE ONLINE dbsr...ode2
ora....de2.ons application ONLINE ONLINE dbsr...ode2
ora....de2.vip application ONLINE ONLINE dbsr...ode2
ora.prod.db application ONLINE ONLINE dbsr...ode1
ora....b1.inst application ONLINE ONLINE dbsr...ode1
ora....b2.inst application ONLINE ONLINE dbsr...ode2
[oracle@rac-node1 ~]$

>> Stop database instance one by one
[oracle@rac-node1 ~]$ srvctl stop instance -i prod1 -d prod
[oracle@rac-node1 ~]$ srvctl stop instance -i prod2 -d prod
 
>>  Stop ASM instance on each node one by one
[oracle@rac-node1 ~]$ srvctl stop asm -n rac-node1
[oracle@rac-node1 ~]$ srvctl stop asm -n rac-node2
 
>> Stop node applications one by one on each node
[oracle@rac-node1 ~]$ srvctl stop nodeapps -n rac-node1
[oracle@rac-node1 ~]$ srvctl stop nodeapps -n rac-node2
 
>> Check status again
[oracle@rac-node1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application OFFLINE OFFLINE
ora....E1.lsnr application OFFLINE OFFLINE
ora....de1.gsd application OFFLINE OFFLINE
ora....de1.ons application OFFLINE OFFLINE
ora....de1.vip application OFFLINE OFFLINE
ora....SM2.asm application OFFLINE OFFLINE
ora....E2.lsnr application OFFLINE OFFLINE
ora....de2.gsd application OFFLINE OFFLINE
ora....de2.ons application OFFLINE OFFLINE
ora....de2.vip application OFFLINE OFFLINE
ora.prod.db application OFFLINE OFFLINE
ora....b1.inst application OFFLINE OFFLINE
ora....b2.inst application OFFLINE OFFLINE
[oracle@rac-node1 ~]
 
>> Download Patch from Metalink.

>> Unzip patch folder after that read readme file.

>> Go to Opatch top.
[oracle@rac-node1 rman]$ cd 9949948
OPatch should be in your path as follows
Set Oracle_Home.
[oracle@rac-node1 9949948]$ export PATH=$PATH:/u01/app/oracle/product/10.2.0 /db_1/OPatch
 
>> Start apply Opatch
[oracle@rac-node1 9949948]$ opatch apply
Invoking OPatch 10.2.0.4.9
Oracle Interim Patch Installer version 10.2.0.4.9
Copyright (c) 2009, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/10.2.0/db_1
Central Inventory : /u01/app/oracle/OraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.4.9
OUI version : 10.2.0.5.0
OUI location : /u01/app/oracle/product/10.2.0/db_1/oui
Log file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2012-09-20_23-38-29PM.log
Patch history file: /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt
ApplySession applying interim patch '9949948' to OH '/u01/app/oracle/product/10.2.0/db_1'
Running prerequisite checks...
OPatch detected the node list and the local node from the inventory. OPatch will patch the local system then propagate This node is part of an Oracle Real Application Cluster.
Remote nodes: 'rac-node2'
Local node: 'rac-node1'
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/10.2.0/db_1')
Is the local system ready for patching? [y|n]
Y ==> I put yes
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '9949948' for restore. This might take a while...
Backing up files affected by the patch '9949948' for rollback. This might take a while...
Patching component oracle.rdbms, 10.2.0.5.0...
Updating archive file "/u01/app/oracle/product/10.2.0/db_1/lib/libserver10.a" with "lib/libserver10.a/ksfd.o"
Running make for target ioracle
ApplySession adding interim patch '9949948' to inventory
Verifying the update...
Inventory check OK: Patch ID 9949948 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 9949948 are present in Oracle Home.
The local system has been patched. You can restart Oracle instances on it.
Patching in rolling mode.
The node 'rac-node2' will be patched next.
Please shutdown Oracle instances running out of this ORACLE_HOME on 'rac-node2'.
(Oracle Home = '/u01/app/oracle/product/10.2.0/db_1')
Is the node ready for patching? [y|n]
y ==> I put yes for node2
User Responded with: Y
Updating nodes 'rac-node2'
Apply-related files are:
FP = "/u01/app/oracle/product/10.2.0/db_1/.patch_storage/9949948_Aug_13_2010_06_35_20/rac/copy_files.txt"
DP = "/u01/app/oracle/product/10.2.0/db_1/.patch_storage/9949948_Aug_13_2010_06_35_20/rac/copy_dirs.txt"
MP = "/u01/app/oracle/product/10.2.0/db_1/.patch_storage/9949948_Aug_13_2010_06_35_20/rac/make_cmds.txt"
RC = "/u01/app/oracle/product/10.2.0/db_1/.patch_storage/9949948_Aug_13_2010_06_35_20/rac/remote_cmds.txt"
Instantiating the file "/u01/app/oracle/product/10.2.0/db_1/.patch_storage/9949948_Aug_13_2010_06_35_20/rac/copy_Propagating files to remote nodes...
Instantiating the file "/u01/app/oracle/product/10.2.0/db_1/.patch_storage/9949948_Aug_13_2010_06_35_20/rac/copy_Propagating directories to remote nodes...
Instantiating the file "/u01/app/oracle/product/10.2.0/db_1/.patch_storage/9949948_Aug_13_2010_06_35_20/rac/make_Running command on remote node 'rac-node2':
cd /u01/app/oracle/product/10.2.0/db_1/rdbms/lib; /usr/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/The node 'rac-node2' has been patched. You can restart Oracle instances on it.
There were relinks on remote nodes. Remember to check the binary size and timestamp on the nodes 'rac-node2' .
The following make commands were invoked on remote nodes:
'cd /u01/app/oracle/product/10.2.0/db_1/rdbms/lib; /usr/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/'
OPatch succeeded.
Start Oracle services on both nodes
[oracle@rac-node1 9949948]$ srvctl start nodeapps -n rac-node1
[oracle@rac-node1 9949948]$ srvctl start nodeapps -n rac-node2
[oracle@rac-node1 9949948]$ srvctl start asm -n rac-node1
[oracle@rac-node1 9949948]$ srvctl start asm -n rac-node2
[oracle@rac-node1 9949948]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE dbsr...ode1
ora....E1.lsnr application ONLINE ONLINE dbsr...ode1
ora....de1.gsd application ONLINE ONLINE dbsr...ode1
ora....de1.ons application ONLINE ONLINE dbsr...ode1
ora....de1.vip application ONLINE ONLINE dbsr...ode1
ora....SM2.asm application ONLINE ONLINE dbsr...ode2
ora....E2.lsnr application ONLINE ONLINE dbsr...ode2
ora....de2.gsd application ONLINE ONLINE dbsr...ode2
ora....de2.ons application ONLINE ONLINE dbsr...ode2
ora....de2.vip application ONLINE ONLINE dbsr...ode2
ora.prod.db application OFFLINE OFFLINE
ora....b1.inst application OFFLINE OFFLINE
ora....b2.inst application OFFLINE OFFLINE
 
>> Start database instances on both nodes:-
[oracle@rac-node1 9949948]$ srvctl start instance -i prod1 -d prod
[oracle@rac-node1 9949948]$ srvctl start instance -i prod1 -d prod
[oracle@rac-node1 9949948]$ crs-stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE dbsr...ode1
ora....E1.lsnr application ONLINE ONLINE dbsr...ode1
ora....de1.gsd application ONLINE ONLINE dbsr...ode1
ora....de1.ons application ONLINE ONLINE dbsr...ode1
ora....de1.vip application ONLINE ONLINE dbsr...ode1
ora....SM2.asm application ONLINE ONLINE dbsr...ode2
ora....E2.lsnr application ONLINE ONLINE dbsr...ode2
ora....de2.gsd application ONLINE ONLINE dbsr...ode2
ora....de2.ons application ONLINE ONLINE dbsr...ode2
ora....de2.vip application ONLINE ONLINE dbsr...ode2
ora.prod.db application ONLINE ONLINE dbsr...ode2
ora....b1.inst application ONLINE ONLINE dbsr...ode1
ora....b2.inst application ONLINE ONLINE dbsr...ode2
 
[oracle@rac-node1 script]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 – Production on Thu Sep 20 23:44:01 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
 
SQL>conn /as sysdba
Connected.
SQL>

Retrieve old versions of procedures with Flashback Query

Retrieve old versions of procedures with Flashback Query

SQL> select text from dba_source as of timestamp systimestamp - interval '30' minute where name='XXXX' order by line;

Configuring Unix / Linux File and Directory Access Rights

Configuring Unix / Linux File and Directory Access Rights
 
Below are some other examples of how to use the chmod command:

>> full access for everybody:
chmod 777 filename
>> full access for owner and group privileges but other users can only read and execute:
chmod 775 filename
>> full access for owner, but restricting group and other user privileges to only read and execute for files in the directory:
chmod 755 dirname
>> full access for the owner with no access rights or privileges for anyone else:
chmod 700 filename
chmod 700 -r /path/to/folder
>> no access to files in directory for group or other users and owner restricted to read and execute privileges to prevent the accidental deletion or modification of

files in the directory:
chmod 500 dirname
>> allowing the owner and group read and write access to a file, allowing others in the group to edit or delete the file as well as the owner, but with no access for

other users:
chmod 660 filename

===============================

APPS_TS_TX_DATA and APPS_TS_TX_IDX size keep on increasing

APPS_TS_TX_DATA and APPS_TS_TX_IDX size keep on increasing

Step1: Truncate these tables:-


SQL>truncate table xla.XLA_DIAG_SOURCES;

SQL>truncate table xla.XLA_DIAG_EVENTS;

Step2: Turn OFF the “SLA: Diagnostics Enabled” profile option at all levels.

Step3: Check the tables, tablespace and archive logs status

===============================

How to set the path and environment variables in Windows

How to set the path and environment variables in Windows

Windows 8 users
From the Desktop, right-click the very bottom left corner of the screen to get thePower User Task Menu.
From the Power User Task Menu, click System.
Click the Advanced System Settings link in the left column.
In the System Properties window, click on the Advanced tab, then click theEnvironment Variables button near the bottom of that tab.
In the Environment Variables window (pictured below), highlight the Path variable in the "System variables" section and click the Edit button. Add or modify the path lines with the paths you want the computer to access. Each different directory is separated with a semicolon as shown below.

C:\Program Files;C:\Winnt;C:\Winnt\System32



Note: You can edit other environment variables by highlighting the variable in the "System variables" section and clicking Edit. If you need to create a new environment variable, click New and enter the Variable name and Variable value.

To view and set the path in the Windows command line, use the path command.
Windows Vista and Windows 7 users
From the Desktop, right-click the Computer icon and select Properties. If you don't have a Computer icon on your desktop, click the Start button, right-click theComputer option in the Start menu, and select Properties.
Click the Advanced System Settings link in the left column.
In the System Properties window, click on the Advanced tab, then click theEnvironment Variables button near the bottom of that tab.
In the Environment Variables window (pictured below), highlight the Path variable in the "System variables" section and click the Edit button. Add or modify the path lines with the paths you want the computer to access. Each different directory is separated with a semicolon as shown below.

C:\Program Files;C:\Winnt;C:\Winnt\System32



Note: You can edit other environment variables by highlighting the variable in the "System variables" section and clicking Edit. If you need to create a new environment variable, click New and enter the Variable name and Variable value.

To view and set the path in the Windows command line, use the path command.
Windows 2000 and Windows XP users

The path is now managed by Windows 2000 and Windows XP and not the autoexec.bat or autoexec.nt files as was done with earlier versions of Windows. To change the system environment variables, follow the steps below.
From the Desktop, right-click My Computer and click Properties. If you don't have a My Computer icon on your desktop, click the Start button, right-click the My Computer option in the Start menu, and select Properties.
In the System Properties window, click on the Advanced tab.
In the "Advanced" section, click the Environment Variables button.
Finally, in the Environment Variables window (as shown below), highlight the Pathvariable in the Systems Variable section and click the Edit button. Add or modify the path lines with the paths you want the computer to access. Each different directory is separated with a semicolon as shown below.

C:\Program Files;C:\Winnt;C:\Winnt\System32



Note: You can edit other environment variables by highlighting the variable in the "System variables" section and clicking Edit. If you need to create a new environment variable, click New and enter the Variable name and Variable value.

To view and set the path in the Windows command line, use the path command.
What is the default Windows Environment Path?

The path is based on programs installed on the computer, so there is no "default path". However, the Windows minimum path is typically the path below.

%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem

Note: Keep in mind that as you install programs, the path is updated with the paths for the newly installed programs. So, if you have erased your path after installing other programs, those programs may be affected.