Thursday, April 25, 2013

April 2013 PSU (11.2.0.3) Manual Steps for Apply/Rollback Patch

April PSU for 11.2 also follows the same pattern as the January PSU when it comes to manual steps for applying the patch. The document ID listed for manual steps (1494646.1) is same one listed for January PSU and it is not updated with the information for the April PSU. This leaves the question of identifying which is the "GI_Components_number" and which is the "DB_PSU_number". There's no information in the GI PSU read me file to help with this fact. However there are indirect ways of identifying the GI component number and the DB PSU number.
1. When the April GI PSU (16083653 - 11.2.0.6) is extracted it will have two patch directories (16056266 and 16315641). One will have the same ID as the (unbundled) April DB PSU (16056266 - 11.2.0.6). Once this is identified then the other one is the GI component number (in this case it is 16315641). For this approach before manually applying the GI PSU one must also refer the unbundled DB PSU to get the DB PSU number (even though this is not needed in GI environment as it's bundled with the GI PSU patch).



2. Another method is to useopatch query -get_base_bug to identify the GI PSU component. This command only works with the GI component part of the patch and will return an error with the DB portion of the patch. For example running against the DB portion of the patch
$ORACLE_HOME/OPatch/opatch query -get_base_bug /usr/local/patches/16056266/
Oracle Interim Patch Installer version 11.2.0.3.4
...
Failed to load the patch object.  Possible causes are:
  The specified path is not an interim Patch shiphome
  Meta-data files are missing from the patch area
  Patch location = /usr/local/patches/16056266/
  Details = Input metadata files are missing.

Patch Location "/usr/local/patches/16056266/" doesn't point to a valid patch area.

OPatch failed with error code 75
However running against the GI component part of the patch will give the GI component number.
 $ORACLE_HOME/OPatch/opatch query -get_base_bug /usr/local/patches/16315641
Oracle Interim Patch Installer version 11.2.0.3.4
...
--------------------------------------------------------------------------------

 List of bugs to be fixed:
   16315641: GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.6 (GI COMPONENTS)
   15876003: GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.5 (GI COMPONENTS)
   14275572: GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.4 (INCLUDES DB PSU 11.2.0.3.4)
   13919095: GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.3 (INCLUDES DB PSU 11.2.0.3.3)
   13696251: GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.2 (INCLUDES DB PSU 11.2.0.3.2)
   13348650: GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.1 (INCLUDES DB PSU 11.2.0.3.1)
   12659561: INSTANCE DOES NOT REGISTER SERVICES WHEN SCAN FAILOVERED
   14305980: IMPROVE CRSD RESOURCE AUTO START PROCESSING
   14277586: INCORRECT SHUTDOWN REASON CODE IN FAN EVENTS - BREAKING CLIENTS
   ...
   ...
Once the GI component is known the remaining directory's number is the DB PSU number.

Related Post
January 2013 PSU (11.2.0.3) Manual Steps for Apply/Rollback Patch vs OPatch Auto

Sunday, April 14, 2013

ORA-31693: Table data object failed to load/unload and ORA-01031: insufficient privileges when using expdp

ORA-31693 and ORA-01031 could be seen when exporting the full schema with flashback_time clause.
ORA-31693: Table data object "SCHEMA"."Table_name" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
Database version is 11.1.0.7. The error only appears intermittently and only for some tables. Metalink note 13715680.8 says the issue is also confirmed on 11.2.0.2 and 11.2.0.3
This is caused by user doing the export not having the flash back privileges. Granting flashback any table privilege to the user doing the export resolves the issue (confirmed on 11.1.0.7).




Useful metalink notes
Getting ORA-39127 ORA-1031 Error With DataPump Export (EXPDP) [ID 414996.1]
Bug 13715680 - Import fails with ORA-1031 for some TIMESTAMP data [ID 13715680.8]
Ora-01031: Insufficient Privileges, When Using The Flashback_time Option with EXPDP/IMPDP Network_link [ID 436106.1]

Related Post
Using flashback time with expdp

Tuesday, April 2, 2013

Changing Public IP, VIP and SCAN IP in 11gR2 RAC

The post list the steps for changing the public IP, VIP and SCAN IP. It must be mentioned that only the IPs are changed in this case. The subnet mask, the public and VIP interface all remain the same. Also private interconnect is not part of the change. Steps were tested on the 11gR2 RAC on RHEL 6
The IP change is as follows. The SCAN IP is set up in /etc/hosts (not supported by Oracle).
NameOld IPNew IP
rhel6m1192.168.0.85192.168.0.93
rhel6m2192.168.0.86192.168.0.94
rhel6m-scan192.168.0.91192.168.0.92
The steps could be done parallel (on all nodes) or in a rolling fashion on each node. Steps executed on one node is shown here and changes were done parallel on all nodes. Since the information modified is stored in OCR it is better to take a manual backup of the OCR before proceeding.
1. First step is to change the VIP configuration. Current VIP configuration is
srvctl config nodeapps
Network exists: 1/192.168.0.0/255.255.255.0/eth0, type static
VIP exists: /rhel6m1-vip/192.168.0.89/192.168.0.0/255.255.255.0/eth0, hosting node rhel6m1
VIP exists: /rhel6m2-vip/192.168.0.90/192.168.0.0/255.255.255.0/eth0, hosting node rhel6m2
2. Stop the database instance and the VIP resource
[oracle@rhel6m1 ~]$ srvctl stop instance -d std11g2 -i std11g21
[grid@rhel6m1 ~]$ srvctl stop vip -n `hostname -s` -f
3. Verify VIP resource is down by using ifconfig on the OS and using crsctl
crsctl stat res -t 

ora.rhel6m1.vip
      1        OFFLINE OFFLINE
ora.rhel6m2.vip
      1        OFFLINE OFFLINE
4. Add the new IP information to /etc/hosts (and DNS) and proceed to modifying the VIP. The modify command must be run as root user
[root@rhel6m1 grid]# srvctl modify nodeapps -n rhel6m1 -A 192.168.0.97/255.255.255.0/eth0
Verify that changes are taken effect
[grid@rhel6m2 ~]$ srvctl config nodeapps -a
Network exists: 1/192.168.0.0/255.255.255.0/eth0, type static
VIP exists: /rhel6m1-vip/192.168.0.97/192.168.0.0/255.255.255.0/eth0, hosting node rhel6m1
VIP exists: /rhel6m2-vip/192.168.0.98/192.168.0.0/255.255.255.0/eth0, hosting node rhel6m2
If the VIP is now on a different network then do not start VIP until public IP and SCAN IP is also changed into the same network.




5. Next is to update the SCAN IP information. Current SCAN IP configuration
srvctl config scan
SCAN name: rhel6m-scan, Network: 1/192.168.0.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /rhel6m-scan/192.168.0.91
6. Stop the SCAN IP and the scan lister and verify it is stopped.
[root@rhel6m1 grid]# srvctl stop scan_listener

[root@rhel6m1 grid]# srvctl stop scan

[root@rhel6m1 grid]# srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is not running

[root@rhel6m1 grid]# srvctl status scan_listener
SCAN Listener MYLISTENER_SCAN1 is enabled
SCAN listener MYLISTENER_SCAN1 is not running
7. Modify the SCAN IP by executing the modify command as root user with the same SCAN name. (It is assumed that if DNS is used, the SCAN name now resolve to new IPs) This will pick up the new IP and update cluster records with the new SCAN IP.
[root@rhel6m1 grid]# srvctl modify scan -n rhel6m-scan
8. Verify the changes have taken effect
[root@rhel6m1 grid]# srvctl config scan
SCAN name: rhel6m-scan, Network: 1/192.168.0.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /rhel6m-scan/192.168.0.92
If the SCAN IP is now on a different network than the public IP then don't start the SCAN IP until the pubilc IP is also changed.

9. Updating public IP requires no cluster related work. Shutdown the cluster stack
[root@rhel6m1 grid]# crsctl stop crs
10. Change IP address on OS (eg. editing ifcfg-eth*) files and restart the network for the changes to take effect. Once the IP changes are made it maybe worthwhile to do ssh between nodes using the new IP so it is added to known host list and no prompts are shown during the ssh between the nodes. As the final step restart the cluster stack with
# crsctl start crs
Useful metalink notes
How to update the IP address of the SCAN VIP resources (ora.scan.vip) [ID 952903.1]
Modifying the VIP or VIP Hostname of a 10g or 11g Oracle Clusterware Node [ID 276434.1]
How to Modify Public Network Information including VIP in Oracle Clusterware [ID 276434.1]

Related Posts
Changing Listener and SCAN Listener Port in 11gR2 RAC
Changing Listener and SCAN Listener Name in 11gR2 RAC