3zecisi3: August 2012

Thursday, August 30, 2012

emagent_perl.trc: ERROR: Max Count Value not set properly in file

Errors in $ORACLE_HOME/host/sysman/log/emagent_perl.trc:

maxRowCount.pl: Tue Aug 21 14:34:29 2012: ERROR: Max Count Value not set properly in file /u01/app/oracle/product/11.2.0/dbhome_1/sysman/config/esa/database.properties line no. 77

1. emctl stop dbconsole

2. pwd
/u01/app/oracle/product/11.2.0/dbhome_1/sysman/config/esa
3. ls
database.properties host.properties instance.properties oracle_listener.properties
4. vi database.properties

-- remove all empty lines from database.properties

5. emctl start dbconsole

I folowed a similar approach for file instance.properties to avoid following error:

maxRowCount.pl: Tue Aug 21 14:37:56 2012: ERROR: Max Count Value not set properly in file /u01/app/oracle/product/11.2.0/dbhome_1/sysman/config/esa/instance.properties line no. 69

The last two issues were generated by bug 5091502 - errors logged in file emagent_perl.trc for blank lines in database.properties, bug fixed in Oracle Management Agents 10.2.0.5.
But for my installation (10.2.0.3), the version of the agent was 10.2.0.4.4 that doesn't have a fix for the bug.

emctl status agent
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.4.4
OMS Version : 10.2.0.4.4
Protocol Version : 10.2.0.4.4

Now console work fine.

RAC 11.2 + ASM: Multiplex control files

To add new control file after RAC installation with ASM storage:

1. View current control_files parameter (as oracle owner)

[oracle@oracle-node-1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 30 10:29:53 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA/oradb/controlfile/curren
t.260.792506879
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

2. Stop RAC database

[oracle@oracle-node-1 ~]$ srvctl stop database -d oradb

3. Start database in nomount

[oracle@oracle-node-1 ~]$ srvctl start database -d oradb -o nomount

4. Copy the current controlfile in new location with RMAN

[oracle@oracle-node-1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Aug 30 10:32:57 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORADB (not mounted)

RMAN> restore controlfile to '+FRA' from '+DATA/ oradb /controlfile/current.260.792506879';

Starting restore at 30-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=351 instance= oradb 1 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 30-AUG-12

RMAN> exit

Recovery Manager complete.

5. View location and name of the new control file with asmcmd (as grid owner)

[grid@oracle-node-1 ~]$ asmcmd

ASMCMD> ls

DATA/
FRA/
ASMCMD> cd fra/ oradb /controlfile
ASMCMD> ls -l
Type Redund Striped Time Sys Name
CONTROLFILE UNPROT FINE AUG 30 10:00:00 Y current.270.792671641
ASMCMD> exit

6. Alter parameter control_files to contain both control files names

[oracle@oracle-node-1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 30 10:36:01 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> alter system set control_files='+DATA/oradb/controlfile/current.260.792506879','+FRA/ oradb/controlfile/current.270.792671641' scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 4008546304 bytes
Fixed Size 2234560 bytes
Variable Size 838862656 bytes
Database Buffers 3154116608 bytes
Redo Buffers 13332480 bytes
Database mounted.
Database opened.
SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA/ oradb /controlfile/curren
t.260.792506879, +FRA/ oradb /co
ntrolfile/current.270.79267164
1
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

7. View RAC database status (as grid owner)

[grid@oracle-node-1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
......................
ora.oradb.db
1 ONLINE ONLINE oracle-node-1 Open
2 ONLINE INTERMEDIATE oracle-node-2 Dismounted
....................

8. Restart RAC database

[oracle@oracle-node-1 ~]$ srvctl stop database -d oradb
[oracle@oracle-node-1 ~]$ srvctl start database -d oradb

9. Check new setting of parameter control_files

[oracle@oracle-node-1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 30 10:40:38 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA/oradb/controlfile/curren
t.260.792506879, +FRA/oradb/co
ntrolfile/current.270.79267164
1
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>

Wednesday, August 29, 2012

RAC 11.2: Reconfigure SCAN Addresses

At install time there is the possibility to install RAC with one SCAN address specified in /etc/hosts file. If after installation, SCAN IPs are available through DNS (Oracle recomend 3 ip addresses to SCAN name), then the SCAN must be reconfigured

[grid@oracle-node-2 ~]$ srvctl stop scan_listener

[grid@oracle-node-2 ~]$ srvctl stop scan

[grid@oracle-node-1 ~]$ more /etc/hosts

......

SCAN IPs

172.18.16.68 oracle-rac-scan.oracle.com oracle-rac-scan

#172.18.16.69 oracle-rac-scan.oracle.com oracle-rac-scan

#172.18.16.70 oracle-rac-scan.oracle.com oracle-rac-scan

........

[root@oracle-node-1 ~]$ vi /etc/hosts

SCAN IPs

#172.18.16.68 oracle-rac-scan.oracle.com oracle-rac-scan

[grid@oracle-node-1 ~]$ srvctl config scan

SCAN name: oracle-rac-scan, Network: 1/172.18.16.0/255.255.254.0/eth0

SCAN VIP name: scan1, IP: /oracle-rac-scan/172.18.16.68

[root@oracle-node-1 ~]# cd /u01/app/11.2.0/grid/bin/

[root@oracle-node-1 bin]# ./srvctl modify scan -n oracle-rac-scan

[root@oracle-node-1 bin]# ./srvctl config scan

SCAN name: oracle-rac-scan, Network: 1/172.18.20.0/255.255.254.0/eth0

SCAN VIP name: scan1, IP: /oracle-rac-scan/172.18.20.69

SCAN VIP name: scan2, IP: /oracle-rac-scan/172.18.20.70

SCAN VIP name: scan3, IP: /oracle-rac-scan/172.18.20.68

[root@oracle-node-1 bin]# ./srvctl modify scan_listener -u

[root@oracle-node-1 bin]# ./srvctl start scan

[root@oracle-node-1 bin]# ./srvctl start scan_listener

[root@oracle-node-1 bin]# ./srvctl status scan_listener

SCAN Listener LISTENER_SCAN1 is enabled

SCAN listener LISTENER_SCAN1 is running on node oracle-node-2

SCAN Listener LISTENER_SCAN2 is enabled

SCAN listener LISTENER_SCAN2 is running on node oracle-node-1

SCAN Listener LISTENER_SCAN3 is enabled

SCAN listener LISTENER_SCAN3 is running on node oracle-node-1

[root@oracle-node-1 bin]# ./srvctl status scan

SCAN VIP scan1 is enabled

SCAN VIP scan1 is running on node oracle-node-2

SCAN VIP scan2 is enabled

SCAN VIP scan2 is running on node oracle-node-1

SCAN VIP scan3 is enabled

SCAN VIP scan3 is running on node oracle-node-1

[root@oracle-node-1 bin]#

From Oracle documentation:

srvctl modify scan -n scan_name

Modifies the SCAN VIP configuration to the match that of another SCAN VIP you specify with scan_name. If scan_name currently resolves to more IP addresses than when it was initially configured, then the utility creates new Oracle Clusterware resources for those additional IP addresses. If scan_name currently resolves to fewer IP addresses, then the utility removes Oracle Clusterware resources for SCAN VIP addresses with numerically higher ordinal numbers until the remaining SCAN VIP resources match the number of IP addresses to which scan_name resolves.

Install on 11.2.0.3 RAC: Patch 13440962: DIFFERENT SUBNET FAILED TO CONNECT TO VIP AFTER RESTART VIP (11.2.0.3)

As oracle

[oracle@oracle-node-2 ~]$ vi .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

# User specific environment and startup programs

ORACLE_UNQNAME=oraclerac

ORACLE_SID= oraclerac2

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export ORACLE_SID ORACLE_BASE ORACLE_HOME ORACLE_UNQNAME

CRS_HOME=/u01/app/11.2.0/grid

RDBMS_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export CRS_HOME RDBMS_HOME

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch

export PATH

As grid

[grid@oracle-node-2 ~]$ more .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

# User specific environment and startup programs

umask 022

ORACLE_SID=+ASM2

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/11.2.0/grid

export ORACLE_SID ORACLE_BASE ORACLE_HOME

GRID_HOME=/u01/app/11.2.0/grid

export GRID_HOME

CRS_HOME=/u01/app/11.2.0/grid

RDBMS_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export CRS_HOME RDBMS_HOME

PATH=$PATH:$HOME/bin:$GRID_HOME/bin:$GRID_HOME/OPatch

export PATH

[grid@oracle-node-2 ~]$

As root

[root@oracle-node-2 u01]# unzip p13440962_112030_Linux-x86-64.zip

As grid

[grid@oracle-node-2 ~]$ opatch lsinventory -detail -oh $CRS_HOME

Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7

Oracle Home : /u01/app/11.2.0/grid

Central Inventory : /u01/app/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.2.0.1.7

OUI version : 11.2.0.3.0

Log file location : /u01/app/11.2.0/grid/cfgtoollogs/opatch/opatch2012-08-29_10-06-31AM.log

Lsinventory Output file location : /u01/app/11.2.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2012-08-29_10-06-31AM.txt

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

Installed Top-level Products (1):

Oracle Grid Infrastructure 11.2.0.3.0

There are 1 products installed in this Oracle Home.

Installed Products (88):

.............

There are 88 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

Rac system comprising of multiple nodes

Local node = oracle-node-1

Remote node = oracle-node-2

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

OPatch succeeded.

[grid@oracle-node-2 ~]$

[oracle@oracle-node-2 ~]$ opatch lsinventory -detail -oh $RDBMS_HOME

Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7

Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory : /u01/app/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.2.0.1.7

OUI version : 11.2.0.3.0

Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2012-08-29_10-07-40AM.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2012-08-29_10-07-40AM.txt

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

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.3.0

There are 1 products installed in this Oracle Home.

Installed Products (136):

..................

There are 136 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

Rac system comprising of multiple nodes

Local node = oracle-node-1

Remote node = oracle-node-2

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

OPatch succeeded.

As oracle

[oracle@oracle-node-2 ~]$ emctl stop dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0

https://oracle-node-1:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ...

... Stopped.

[oracle@oracle-node-2 ~]$

As oracle

[oracle@oracle-node-2 ~]$ $RDBMS_HOME/bin/srvctl stop home -o $RDBMS_HOME -s /home/oracle/status -n oracle-node-2

As root

[root@oracle-node-2 opatch]# cd /u01/app/11.2.0/grid/crs/install/

[root@oracle-node-2 install]# ./rootcrs.pl -unlock

Using configuration parameter file: ./crsconfig_params

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'oracle-node-2'

CRS-2673: Attempting to stop 'ora.crsd' on 'oracle-node-2'

CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'oracle-node-2'

CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'oracle-node-2'

CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'oracle-node-2'

CRS-2673: Attempting to stop 'ora.FRA.dg' on 'oracle-node-2'

CRS-2673: Attempting to stop 'ora.DATA.dg' on 'oracle-node-2'

CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'oracle-node-2' succeeded

CRS-2673: Attempting to stop 'ora.oracle-node-2.vip' on 'oracle-node-2'

CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'oracle-node-2' succeeded

CRS-2673: Attempting to stop 'ora.scan1.vip' on 'oracle-node-2'

CRS-2677: Stop of 'ora.oracle-node-2.vip' on 'oracle-node-2' succeeded

CRS-2672: Attempting to start 'ora.oracle-node-2.vip' on 'oracle-node-1'

CRS-2677: Stop of 'ora.scan1.vip' on 'oracle-node-2' succeeded

CRS-2672: Attempting to start 'ora.scan1.vip' on 'oracle-node-1'

CRS-2676: Start of 'ora.oracle-node-2.vip' on 'oracle-node-1' succeeded

CRS-2676: Start of 'ora.scan1.vip' on 'oracle-node-1' succeeded

CRS-2672: Attempting to start 'ora.LISTENER_SCAN1.lsnr' on 'oracle-node-1'

CRS-2676: Start of 'ora.LISTENER_SCAN1.lsnr' on 'oracle-node-1' succeeded

CRS-2677: Stop of 'ora.FRA.dg' on 'oracle-node-2' succeeded

CRS-2677: Stop of 'ora.DATA.dg' on 'oracle-node-2' succeeded

CRS-2673: Attempting to stop 'ora.asm' on 'oracle-node-2'

CRS-2677: Stop of 'ora.asm' on 'oracle-node-2' succeeded

CRS-2673: Attempting to stop 'ora.ons' on 'oracle-node-2'

CRS-2677: Stop of 'ora.ons' on 'oracle-node-2' succeeded

CRS-2673: Attempting to stop 'ora.net1.network' on 'oracle-node-2'

CRS-2677: Stop of 'ora.net1.network' on 'oracle-node-2' succeeded

CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'oracle-node-2' has completed

CRS-2677: Stop of 'ora.crsd' on 'oracle-node-2' succeeded

CRS-2673: Attempting to stop 'ora.mdnsd' on 'oracle-node-2'

CRS-2673: Attempting to stop 'ora.ctssd' on 'oracle-node-2'

CRS-2673: Attempting to stop 'ora.evmd' on 'oracle-node-2'

CRS-2673: Attempting to stop 'ora.asm' on 'oracle-node-2'

CRS-2677: Stop of 'ora.evmd' on 'oracle-node-2' succeeded

CRS-2677: Stop of 'ora.mdnsd' on 'oracle-node-2' succeeded

CRS-2677: Stop of 'ora.asm' on 'oracle-node-2' succeeded

CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'oracle-node-2'

CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'oracle-node-2' succeeded

CRS-2677: Stop of 'ora.ctssd' on 'oracle-node-2' succeeded

CRS-2673: Attempting to stop 'ora.cssd' on 'oracle-node-2'

CRS-2677: Stop of 'ora.cssd' on 'oracle-node-2' succeeded

CRS-2673: Attempting to stop 'ora.crf' on 'oracle-node-2'

CRS-2677: Stop of 'ora.crf' on 'oracle-node-2' succeeded

CRS-2673: Attempting to stop 'ora.gipcd' on 'oracle-node-2'

CRS-2677: Stop of 'ora.gipcd' on 'oracle-node-2' succeeded

CRS-2673: Attempting to stop 'ora.gpnpd' on 'oracle-node-2'

CRS-2677: Stop of 'ora.gpnpd' on 'oracle-node-2' succeeded

CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'oracle-node-2' has completed

CRS-4133: Oracle High Availability Services has been stopped.

Successfully unlock /u01/app/11.2.0/grid

[root@oracle-node-2 install]#

As oracle

[oracle@oracle-node-2 scripts]$ pwd

/u01/13440962/custom/server/13440962/custom/scripts

[oracle@oracle-node-2 scripts]$

[oracle@oracle-node-2 scripts]$ ./prepatch.sh -dbhome $RDBMS_HOME

./prepatch.sh completed successfully.

As grid

[grid@oracle-node-2 ~]$ cd /u01/13440962/

[grid@oracle-node-2 13440962]$ ls

custom etc files README.txt

[grid@oracle-node-2 13440962]$ opatch napply -local -oh $CRS_HOME -id 13440962

Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7

UTIL session

Oracle Home : /u01/app/11.2.0/grid

Central Inventory : /u01/app/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.2.0.1.7

OUI version : 11.2.0.3.0

Log file location : /u01/app/11.2.0/grid/cfgtoollogs/opatch/opatch2012-08-29_11-02-29AM.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches: 13440962

Do you want to proceed? [y|n]

User Responded with: Y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/app/11.2.0/grid')

Is the local system ready for patching? [y|n]

User Responded with: Y

Backing up files...

Applying interim patch '13440962' to OH '/u01/app/11.2.0/grid'

Patching component oracle.crs, 11.2.0.3.0...

Copying file to "/u01/app/11.2.0/grid/crs/install/crsconfig_lib.pm"

Copying file to "/u01/app/11.2.0/grid/crs/install/crspatch.pm"

Copying file to "/u01/app/11.2.0/grid/crs/install/s_crsconfig_lib.pm"

Patches 13440962 successfully applied.

Log file location: /u01/app/11.2.0/grid/cfgtoollogs/opatch/opatch2012-08-29_11-02-29AM.log

OPatch succeeded.

[grid@oracle-node-2 13440962]$

As oracle

[oracle@oracle-node-2 ~]$ cd /u01/13440962/

[oracle@oracle-node-2 13440962]$ opatch napply custom/server/ -local -oh $RDBMS_HOME -id 13440962

Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7

UTIL session

Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory : /u01/app/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.2.0.1.7

OUI version : 11.2.0.3.0

Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2012-08-29_11-04-29AM.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches: 13440962

Do you want to proceed? [y|n]

User Responded with: Y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/app/oracle/product/11.2.0/dbhome_1')

Is the local system ready for patching? [y|n]

User Responded with: Y

Backing up files...

Applying interim patch '13440962' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.rdbms, 11.2.0.3.0...

Patches 13440962 successfully applied.

Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2012-08-29_11-04-29AM.log

OPatch succeeded.

[oracle@oracle-node-2 13440962]$

As oracle

[oracle@oracle-node-2 13440962]$ cd custom/server/13440962/custom/scripts

[oracle@oracle-node-2 scripts]$ pwd

/u01/13440962/custom/server/13440962/custom/scripts

[oracle@oracle-node-2 scripts]$ ls

postpatch.sh prepatch.sh

[oracle@oracle-node-2 scripts]$ ./postpatch.sh -dbhome $RDBMS_HOME

Reading /u01/app/oracle/product/11.2.0/dbhome_1/install/params.ora..

Parsing file /u01/app/oracle/product/11.2.0/dbhome_1/bin/racgwrap

Parsing file /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl

Parsing file /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvconfig

Parsing file /u01/app/oracle/product/11.2.0/dbhome_1/bin/cluvfy

Verifying file /u01/app/oracle/product/11.2.0/dbhome_1/bin/racgwrap

Verifying file /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl

Verifying file /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvconfig

Verifying file /u01/app/oracle/product/11.2.0/dbhome_1/bin/cluvfy

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/racgwrap

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvconfig

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/cluvfy

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/diskmon.bin

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/lsnodes

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/osdbagrp

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/rawutl

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/srvm/admin/ractrans

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/srvm/admin/getcrshome

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/gnsd

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/crsdiag.pl

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libhasgen11.so

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libclsra11.so

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libdbcfg11.so

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libocr11.so

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libocrb11.so

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libocrutl11.so

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libuini11.so

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/librdjni11.so

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libgns11.so

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libgnsjni11.so

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libagfw11.so

[oracle@oracle-node-2 scripts]$

As root

[root@oracle-node-2 install]# cd /u01/app/11.2.0/grid/crs/install/

[root@oracle-node-2 install]# ./rootcrs.pl -patch

Using configuration parameter file: ./crsconfig_params

CRS-4123: Oracle High Availability Services has been started.

As oracle

[oracle@oracle-node-2 ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/bin

[oracle@oracle-node-2 bin]$ ./srvctl start home -o /u01/app/oracle/product/11.2.0/dbhome_1 -s /home/oracle/status -n oracle-node-2

As oracle

[oracle@oracle-node-2 bin]$ opatch lsinventory -oh $RDBMS_HOME

Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7

Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory : /u01/app/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.2.0.1.7

OUI version : 11.2.0.3.0

Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2012-08-29_11-12-50AM.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2012-08-29_11-12-50AM.txt

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

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.3.0

There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch 13440962 : applied on Wed Aug 29 11:04:42 EEST 2012

Unique Patch ID: 14422521

Created on 28 Dec 2011, 08:46:48 hrs PST8PDT

Bugs fixed:

13440962

Rac system comprising of multiple nodes

Local node = oracle-node-2

Remote node = oracle-node-1

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

OPatch succeeded.

[oracle@oracle-node-2 bin]$

As grid

[grid@oracle-node-2 ~]$ opatch lsinventory -oh $CRS_HOME

Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7

Oracle Home : /u01/app/11.2.0/grid

Central Inventory : /u01/app/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.2.0.1.7

OUI version : 11.2.0.3.0

Log file location : /u01/app/11.2.0/grid/cfgtoollogs/opatch/opatch2012-08-29_11-13-32AM.log

Lsinventory Output file location : /u01/app/11.2.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2012-08-29_11-13-32AM.txt

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

Installed Top-level Products (1):

Oracle Grid Infrastructure 11.2.0.3.0

There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch 13440962 : applied on Wed Aug 29 11:03:03 EEST 2012

Unique Patch ID: 14422521

Created on 28 Dec 2011, 08:48:56 hrs PST8PDT

Bugs fixed:

13440962

Rac system comprising of multiple nodes

Local node = oracle-node-2

Remote node = oracle-node-1

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

OPatch succeeded.

[grid@oracle-node-2 ~]$

Repeat the same steps on other node in cluster ...

As grid

[grid@oracle-node-1 ~]$ crsctl stat res -t

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

NAME TARGET STATE SERVER STATE_DETAILS

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

Local Resources

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

ora.DATA.dg

ONLINE ONLINE oracle-node-1

ONLINE ONLINE oracle-node-2

ora.FRA.dg

ONLINE ONLINE oracle-node-1

ONLINE ONLINE oracle-node-2

ora.LISTENER.lsnr

ONLINE ONLINE oracle-node-1

ONLINE ONLINE oracle-node-2

ora.asm

ONLINE ONLINE oracle-node-1 Started

ONLINE ONLINE oracle-node-2 Started

ora.gsd

OFFLINE OFFLINE oracle-node-1

OFFLINE OFFLINE oracle-node-2

ora.net1.network

ONLINE ONLINE oracle-node-1

ONLINE ONLINE oracle-node-2

ora.ons

ONLINE ONLINE oracle-node-1

ONLINE ONLINE oracle-node-2

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

Cluster Resources

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

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE oracle-node-1

ora.LISTENER_SCAN2.lsnr

1 ONLINE ONLINE oracle-node-2

ora.LISTENER_SCAN3.lsnr

1 ONLINE ONLINE oracle-node-2

ora.cvu

1 ONLINE ONLINE oracle-node-2

ora.oc4j

1 ONLINE ONLINE oracle-node-2

ora.oracle-node-1.vip

1 ONLINE ONLINE oracle-node-1

ora.oracle-node-2.vip

1 ONLINE ONLINE oracle-node-2

ora.oraclerac.db

1 ONLINE ONLINE oracle-node-1 Open

2 ONLINE ONLINE oracle-node-2 Open

ora.scan1.vip

1 ONLINE ONLINE oracle-node-1

ora.scan2.vip

1 ONLINE ONLINE oracle-node-2

ora.scan3.vip

1 ONLINE ONLINE oracle-node-2

Same steps must be done on all the nodes in cluster.

RAC 11.2. change to ARCHIVELOG mode

[oracle@oracle-node-1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 28 13:54:49 2012

SQL> conn / as sysdba

Connected.

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

oraclerac1

SQL> select name, log_mode from v$database;

NAME LOG_MODE

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

ORACLERAC NOARCHIVELOG

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination +FRA

Oldest online log sequence 5

Current log sequence 6

SQL>

SQL> alter system set cluster_database=false scope=spfile sid='oraclerac1';

System altered.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

[oracle@oracle-node-1 ~]$ srvctl stop database -d oraclerac

[oracle@oracle-node-1 ~]$

[oracle@oracle-node-1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 28 13:58:10 2012

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 4008546304 bytes

Fixed Size 2234560 bytes

Variable Size 838862656 bytes

Database Buffers 3154116608 bytes

Redo Buffers 13332480 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter system set cluster_database=true scope=spfile sid='oraclerac1';

System altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

[oracle@oracle-node-1 ~]$ srvctl start database -d oraclerac

[oracle@oracle-node-1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 28 14:00:49 2012

SQL> conn / as sysdba

Connected.

SQL> select name, log_mode from v$database;

NAME LOG_MODE

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

ORACLERAC ARCHIVELOG

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination +FRA

Oldest online log sequence 5

Next log sequence to archive 6

Current log sequence 6

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

[oracle@oracle-node-1 ~]$

RUNCLUVFY: PRVF-5436 : The NTP daemon running on one or more nodes lacks the slewing option "-x"

[oracle@oracle-node-1 grid]$ ./runcluvfy.sh stage -pre dbinst -n oracle-node-1,oracle-node-2 -r 11gR2 –verbose

…..

Check for NTP daemon or service alive passed on all nodes

Checking NTP daemon command line for slewing option "-x"

Check: NTP daemon command line

Node Name Slewing Option Set?

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

oracle-node-1 no

oracle-node-2 no

Result:

NTP daemon slewing option check failed on some nodes

PRVF-5436 : The NTP daemon running on one or more nodes lacks the slewing option "-x"

Result: Clock synchronization check using Network Time Protocol(NTP) failed

…..

[root@oracle-node-1 sysconfig]# vi /etc/sysconfig/ntpd

# Drop root to id 'ntp:ntp' by default.

OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid -g"

[root@oracle-node-1 sysconfig]# service ntpd restart

Shutting down ntpd: [ OK ]

Starting ntpd: [ OK ]

[root@oracle-node-1 sysconfig]#

RUNCLUVFY: PRVF-5637 : DNS response time could not be checked on following nodes

[oracle@oracle-node-1 grid]$ ./runcluvfy.sh stage -pre dbinst -n oracle-node-1,oracle-node-2 -r 11gR2 –verbose

Checking DNS response time for an unreachable node

Node Name Status

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

oracle-node-1 failed

oracle-node-2 failed

PRVF-5637 : DNS response time could not be checked on following nodes: oracle-node-1,oracle-node-2

This is a bug on 11.2.0.3 and can be ignored (MOS ID 1480242.1)

RUNCLUVFY: PRVF-7617 : Node connectivity

Before installing grid infrastructure, when i ran runcluvfy.sh i received following ERROR: PRVF-7617 : Node connectivity between "oracle-node-1 : 172.18.16.64" and "oracle-node-2 : 172.18.16.65" failed

[grid@oracle-node-1 grid]$ ./runcluvfy.sh comp nodecon -i eth0 -n oracle-node-1,oracle-node-2 -verbose

....................

Check: TCP connectivity of subnet "172.18.16.0"

Source Destination Connected?

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

oracle-node-1:172.18.16.64 oracle-node-2:172.18.16.65 failed

ERROR:

PRVF-7617 : Node connectivity between "oracle-node-1 : 172.18.16.64" and "oracle-node-2 : 172.18.16.65" failed

Result: TCP connectivity check failed for subnet "172.18.16.0"

Checking subnet mask consistency...

Subnet mask consistency check passed for subnet "172.18.16.0".

Subnet mask consistency check passed for subnet "192.168.1.0".

Subnet mask consistency check passed.

Result: Node connectivity check failed

Verification of node connectivity was unsuccessful on all the specified nodes.

Solution: Stop any firewall running on nodes, in my case IPTABLES

[root@oracle-node-2 ~]# service iptables stop

iptables: Flushing firewall rules: [ OK ]

iptables: Setting chains to policy ACCEPT: filter [ OK ]

iptables: Unloading modules: [ OK ]

[grid@oracle-node-1 grid]$ ./runcluvfy.sh stage -pre crsinst -n oracle-node-1,oracle-node-2 -verbose

...........

Check: TCP connectivity of subnet "172.18.16.0"

Source Destination Connected?

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

oracle-node-1:172.18.16.64 oracle-node-2:172.18.16.65 passed

Result: TCP connectivity check passed for subnet "172.18.16.0"

ASM 11.2: Instantiating disk: failed at createdisk

[root@oracle-node-2 ~]$ more /etc/redhat-release

Red Hat Enterprise Linux Server release 6.3 (Santiago)

[root@oracle-node-2 dev]# oracleasm createdisk DISK1 /dev/sdb1

Writing disk header: done

Instantiating disk: failed

Clearing disk header: done

It appears that Security Linux was blocking acces to the header of disk.

[root@oracle-node-2 log]# cd /etc/selinux

[root@oracle-node-2 selinux]# more config

SELINUX=enforcing

SELINUXTYPE=targeted

Disable SELINUX

[root@oracle-node-2 selinux]# vi config

SELINUX=disabled

After REBOOT

[root@oracle-node-2 ~]# getenforce

Disabled

[root@oracle-node-2 ~]# oracleasm createdisk DISK1 /dev/sdb1

Writing disk header: done

Instantiating disk: done

[root@oracle-node-2 ~]# cd /dev/oracleasm/disks

[root@oracle-node-2 disks]# ls

DISK1

Then scan ASM disks on other node:

[root@oracle-node-1 dev]# oracleasm scandisks

Reloading disk partitions: done

Cleaning any stale ASM disks...

Scanning system for ASM disks...

[root@oracle-node-1 dev]#

Controlfile autobackup file not in FRA

When running backup with control autobackup enabled, even though the destination was not set and by default it sould be created in fast recovery area, the autobackup file was created in Oracle Home.

RMAN> show all;

RMAN configuration parameters for database with db_unique_name SAPCC are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 8 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_sapcc1.f'; # default

RMAN> backup incremental level 0 database plus archivelog;

........

Starting Control File and SPFILE Autobackup at 29-AUG-12

piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-1925572543-20120829-02 comment=NONE

Finished Control File and SPFILE Autobackup at 29-AUG-12

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

old RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';

RMAN configuration parameters are successfully reset to default value

RMAN> backup incremental level 0 database plus archivelog;

.......

Starting Control File and SPFILE Autobackup at 29-AUG-12

piece handle=+FRA/sapcc/autobackup/2012_08_29/s_792594079.266.792594081 comment=NONE

Finished Control File and SPFILE Autobackup at 29-AUG-12

RMAN>

Tuesday, August 21, 2012

ERROR vpxoci: ORA-01017: invalid username/password; logon denied

After changing DBSNMP and SYSMAN password from Enterprise Manager console (11.2.0.3), because they were about to expire, in trace file emagent.trc there was following error:

ERROR vpxoci: ORA-01017: invalid username/password; logon denied
WARN vpxoci: Login 0xac0012a0 failed, error=ORA-01017: invalid username/password; logon denied

These are the steps done to fix it:

1. cd $ORACLE_HOME/node_SID/sysman/emd
2. cp targets.xml targets.xml.orig
3. vi targets.xml

change
<Property NAME="password" VALUE="31668048b077a956" ENCRYPTED="TRUE"/>
into
<Property NAME="password" VALUE="oracle" ENCRYPTED="FALSE"/>

4. emctl start dbconsole
5. more targets.xml -- to see that the new password was encrypted

<Property NAME="password" VALUE="768d4318a832681b" ENCRYPTED="TRUE"/>

Bacause that was a RAC database, i done the same procedure on the other node.

The password for SYSMAN had to be changed also.

1. emctl stop dbconsole
2. emctl setpasswd dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
https://node1:1158/em/console/aboutApplication
Please enter new repository password:
Repository password successfully updated.
3. emctl start dbconsole

Thursday, August 16, 2012

Bitmap indexes: ORA-00060: deadlock detected while waiting for resource

Bitmap indexes can generate frequent deadlock on insert, because of internal locking procedures that involves all the rows containing one index key.

SQL> desc my_table_btm_index;

Name Null? Type

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

ID NUMBER

STATUS VARCHAR2(1)

SQL> select i.table_name,

2 i.index_name,

3 i.index_type,

4 c.column_name

5 from user_indexes i

6 join user_ind_columns c

7 on i.index_name=c.index_name

8 where i.index_name='IDX_BTM_MY_TABLE';

TABLE_NAME INDEX_NAME

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

INDEX_TYPE

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

COLUMN_NAME

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

MY_TABLE_BTM_INDEX IDX_BTM_MY_TABLE

BITMAP

STATUS

Session 1:

SQL> insert into MY_TABLE_BTM_INDEX values (1,'a');

1 row created.

Session 2:

SQL> insert into MY_TABLE_BTM_INDEX values (1,'b');

1 row created.

Session 1:

SQL> insert into MY_TABLE_BTM_INDEX values (2,'b');

Session 2:

SQL> insert into MY_TABLE_BTM_INDEX values (2,'a');

1 row created.

Session 1:

insert into MY_TABLE_BTM_INDEX values (2,'b')

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

Friday, August 10, 2012

DataPump - expdp, impdp VERSION parameter

To move data between Oracle versions, from a higher version to a smaller one, with DataPump, there is the VERSION parameter, which should be set at export time and import time at destination database level

For example to import in 10.1.0.4 database export from 10.2.0.4:

Run export:

expdp danco directory=WORK_DIR schemas=test dumpfile=test.dmp logfile=test.log version=10.1.0.4.0

Copy dump file from source server to destination server.
Run Import:

impdp danco directory=WORK_DIR dumpfile=test.dmp logfile=test_imp.dmp version=10.1.0.4.0

Tuesday, August 7, 2012

UNISTR - Insert special characters

To insert special characters into database with utf-8 support, you can use UNISTR function

CREATE TABLE NTEST (C NVARCHAR2(10));

INSERT INTO NTEST VALUES (UNISTR('\0409'));
INSERT INTO NTEST VALUES (UNISTR('\040D'));

SELECT * FROM NTEST;

Љ
Ѝ

For a list of UTF-8 encoding codes: http://www.utf8-chartable.de/unicode-utf8-table.pl

Monday, August 6, 2012

Enterprise Manager - Error starting ORMI server

When startinf Enterprise Manager console in emdb.nohup i received folowing errors

----- Mon Aug 6 17:32:27 2012::joptions used are -server -Xmx384M -XX:MaxPermSize=400M -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=40 -----
12/08/06 17:32:28 Error starting ORMI server b7scp2 port 5,520: Address already in use
12/08/06 17:32:28 Shutting down OC4J...
----- Mon Aug 6 17:32:30 2012::Checking status of DBConsole : 2043 -----
----- Mon Aug 6 17:32:30 2012::DBConsole exited at Mon Aug 6 17:32:30 2012 with return value 7. -----
----- Mon Aug 6 17:32:30 2012::Restarting DBConsole. -----

After i stoped EM console

emctl stop dbconsole

I checked that the port 5520 is not used, but it was used

netstat -na |grep 5520

*.5520 *.* 0 0 49152 0 LISTEN

I changed file $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_<HOST_SID>/config/rmi.xml and put different port (5521)

<?xml version = '1.0' standalone = 'yes'?>

<rmi-server xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://xmlns.oracle.com/oracleas/schema/rmi-server

-10_0.xsd" port="5521" ssl-port="5521" schema-major-version="10" schema-minor-version="0">

After that the console started succesfully with

emctl start dbconsole