Monday, March 21, 2011

PRCR-1079,CRS-2674,CRS-5804,CRS-2632 when "srvctl start database -d orcl

Environment
2 node 11.2.0.2 Oracle RAC database on OEL 5.5 platform

Problem
Today I tried to enable archive log mode in these 2 node 11.2.0.2 RAC database, so I followed this article and my previous post to change the database to archive log mode:

  1. Login to one of the nodes (i.e. linux1) and disable the cluster instance parameter by setting cluster_database to FALSE from the current instance:

    $ sqlplus "/ as sysdba"
    $ sqlplus alter system set cluster_database=false scope=spfile sid='orcl1'
    ;
  2. Shutdown all instances accessing the clustered database:

    $ srvctl stop database -d orcl
  3. Using the local instance, MOUNT the database:

    $ sqlplus "/ as sysdba"
    $ sqlplus startup mount
  4. Enable archiving and set archive location and log_archive_format

    $ sqlplus alter database archivelog;
  5. $ sqlplus alter system set log_archive_format='arch_%t_%s.arc'   scope=spfile;
    System altered.
    $ sqlplus ALTER SYSTEM SET log_archive_dest_1='location=/u11/archive' scope=spfile;
    System altered.
    $ sqlplus ALTER SYSTEM SET log_archive_dest_2='location=/u12/archive' scope=spfile;
    System altered.
  6. Re-enable support for clustering by modifying the instance parameter cluster_database to TRUE from the current instance:

    $ sqlplus alter system set cluster_database=true scope=spfile sid='orcl1';
  7. Shutdown the local instance:

    $ sqlplus shutdown immediate 
  8. Bring all instance back up using srvctl:

    $ srvctl start database -d orcl 
  9. (Optional) Bring any services (i.e. TAF) back up using srvctl: 
After I ran this "srvctl start database -d racdb" command, I got following errors:
PRCR-1079 : Failed to start resource ora.orcl.db
CRS-2674: Start of 'ora.orcl.db' on 'orcl1' failed
CRS-5804: Communication error with agent process
CRS-2632: There are no more servers to try to place resource 'ora.orcl.db' on that would satisfy its placement policy

There is only orcl2 instance is up and running, the "orcl1" was not started up:

[oracle@node2~]$ srvctl status database -d orcl
Instance orcl1 is not running on node node1
Instance orcl2 is running on node node2



Solution
The above error messages are not helpful in troubleshooting the cause of this problem, after several google and metalink search, I found this support note:  ORA-01078 when starting RAC instance within a private domain [ID 889845.1], I got a hint that I might be able to startup the "orcl1" instance at node1, so I did the following:
In node 1, which is the once couldn't be started up by "srvctl"

1 SQL> startup nomount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-19905: log_archive_format must contain %s, %t and %r

2 So I created a pfile out of the spfile while the orcl1 instance was in "nomount" state:
SQL> create pfile='/home/oracle/orcl1_init.ora" from spfile;

3 Edited the "orcl1_init.ora" file to set the right "log_archive_format" parameter value, it turned out that I missed "%r" in my original change, as the red color highlighted text shown in the above:

4 Restart the database by using the newly updated "orcl1_init.ora" file:
SQL> startup nomount pfile='/home/oracle/orcl1_init.ora";

5 Create new spfile out of the pfile:
SQL> create spfile from pfile=/home/oracle/orcl1_init.ora';

6 Shutdown the orcl1 instance
SQL> shutdown immediate;

7 After the above changes, I was able to successfully start up the "orcl" database:

[oracle@node1~]$ srvctl stop database -d orcl
Instance orcl1 is not running on node node1
Instance orcl2 is not running on node node2


[oracle@node1~]$ srvctl start database -d orcl

Instance orcl1 is running on node node1
Instance orcl2 is running on node node2




8 After successfully restarted the database, I checked the service status I created for "Server Side Transparent Application Failover" purpose:

[oracle@node1~]$ srvctl status service -d orcl
Service orcl_taf is running on instance(s) orcl1,orcl2

9 I checked both instances to see if they recognize the 2 log_archive_dest_n location I set in the above steps:
In node1:



NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=/u11/archive
log_archive_dest_2                   string      location=/u12/archive




In node2:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=/u11/ora_arch
log_archive_dest_2                   string      location=/u12/ora_arch

As you see that in node2, the instance still kept its original log destination values I set months ago, that's not correct, I want it to point to the new /u11/archive and /u12/archive locations. 

10 To fix the step 9 problem, I did the same steps listed in the "Problem" section, except changing "orcl1" to "orcl2", and running those commands in node2 rather than node1.  After those changes, both instances point to the same archive locations:
In node1:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=/u11/archive
log_archive_dest_2                   string      location=/u12/archive


In node2:



NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=/u11/archive
log_archive_dest_2                   string      location=/u12/archive


Labels: , ,

Monday, March 14, 2011

Lsnrctl Services Displays "status UNKNOWN"

Environment
11gR2 2 nodes Oracle Database on OEL 5.5 platformt

Problem
There are two Metalink notes explaining this issue:
1 Lsnrctl Services Displays "status UNKNOWN", MODOSE, And Multiple Handlers [ID 151223.1]
Listener Control Services Shows Status Unknown for a Database [ID 301877.1]
Issues Affecting Automatic Service Registration [ID 235562.1]

I have a 2-nodes 11gR2 RAC database with 11gR1 Grid Contrl Agent installed on both servers, the database was configured with conventional listener service rather than the new "Single Client Access Name (SCAN)", so the database should use the listener.ora parameter file found under $ORACLE_HOME, which is:
/u02/oracle/product/11.2.0/dbhome_1/network/admin,
However, when I did "lsnrctl" to check listener status, I got the following:

[oracle@node1~]$ lsnrctl


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 14-MAR-2011 12:05:19


Copyright (c) 1991, 2009, Oracle.  All rights reserved.


Welcome to LSNRCTL, type "help" for information.


LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1.host.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                18-JAN-2011 16:54:26
Uptime                    54 days 18 hr. 10 min. 53 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/11.2.0.2/grid/network/admin/listener.ora
Listener Log File         /u02/oracle/diag/tnslsnr/cash/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.130)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.211)(PORT=1521)))
Services Summary...
Service "node1.host.com" has 1 instance(s).
  Instance "node1", status READY, has 1 handler(s) for this service...
Service "nodeoXDB.wealthengine.com" has 1 instance(s).
  Instance "node1", status READY, has 1 handler(s) for this service...
The command completed successfully

As seen in the above highlighted text, the database is actually using a listener.ora file out of /u01/11.2.02/grid/network/admin, rather than the one from $ORACLE_HOME (/u02).  So I checked the listener.ora file under /u01, here it's:

[oracle@node1 admin]$ more /u01/11.2.0.2/grid/network/admin/listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent


So according to the comments found above, it seems that Grid Control Agent made the above changes and forced the database to use the listener.ora file under /u01.  The two nodes do have Grid Control Agent installed. 


Awaiting for Oracle Support for instructions.




References

  1. NOTE:460982.1 - How To Configure Server Side Transparent Application Failover
  2. How to Setup SCAN Listener and Client for TAF and Load Balancing [Video] [ID 1188736.1]
  3. NOTE:887522.1 - 11gR2 Grid Infrastructure Single Client Access Name (SCAN) Explained
  4. NOTE:972500.1 - How to Modify SCAN Setting after Installation
  5.  NOTE:887522.1 - 11gR2 Grid Infrastructure Single Client Access Name (SCAN) Explained
  6. Setting Environment Variables Through Srvctl [ID 733567.1]
  7. LISTENER_SCAN Fails to Start Using SRVCTL [ID 1050953.1]
  8. 11.2 Scan and Node TNS Listener Setup Examples [ID 1070607.1]
  9. http://www.oracle.com/technetwork/database/clustering/overview/scan-129069.pdf

Labels: , ,

Wednesday, March 9, 2011

How to check the value of NLS related values like national character set or NLS_NCHAR_CHARACTERSET?

SQL> select value from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

VALUE
----------------------------------------
AL16UTF16

SQL> select value$ from sys.props$ where name='NLS_NCHAR_CHARACTERSET';

VALUE$
--------------------------------------------------------------------------------
AL16UTF16

SQL> select property_value from database_properties where property_name
='NLS_NCHAR_CHARACTERSET';

PROPERTY_VALUE
--------------------------------------------------------------------------------
AL16UTF16

References

Labels:

Tuesday, March 8, 2011

sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied

Problem
After I installed Oracle 11.2.0.1 software on an OEL 5.5 server, "sqlplus /nolog" ran into following error message:
"sqlplus: error while loading shared libraries: /u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied"

Cause
According to Metalink Note: 454196.1 - ./sqlplus: error on libnnz11.so: cannot restore segment prot after reloc [ID 454196.1] , it's SELinux causing this problem. 

Solution
Disable SELinux
Switch SELinux from the default "Enforcing" mode that it is running in, to the "Permissive" mode.

Commands, as root:
======================
getenforce       (returns "Enforcing")
setenforce 0
getenforce       (returns "Permissive")


References


Labels:

E558: Terminal entry not found in terminfo

I am getting this error message while trying to edit a file in OEL 5.5 by using "vi" editior:
E558: Terminal entry not found in terminfo


This note is the best one I googled out of numerous pages to offer solution on how to resolve this kind of problem:
"$TERM was xterm in gnome-terminal, while it was dumb in terminatorfor my user account and unknown in terminator for the root account.
In the end, adding the line export TERM=xterm to ~/.bashrc and /root/.bashrc seems to have fixed the problem…"


Labels: ,

Viewing Information About Partitioned Tables and Indexes

A handy table to check partitioned tables and indexes viewing information:

Viewing Information About Partitioned Tables and Indexes

The following views display information specific to partitioned tables and indexes:
ViewDescription
DBA_PART_TABLESALL_PART_TABLES
USER_PART_TABLES
DBA view displays partitioning information for all partitioned tables in the database. ALL view displays partitioning information for all partitioned tables accessible to the user. USER view is restricted to partitioning information for partitioned tables owned by the user.
DBA_TAB_PARTITIONSALL_TAB_PARTITIONS
USER_TAB_PARTITIONS
Display partition-level partitioning information, partition storage parameters, and partition statistics generated by the DBMS_STATS package or the ANALYZE statement.
DBA_TAB_SUBPARTITIONSALL_TAB_SUBPARTITIONS
USER_TAB_SUBPARTITIONS
Display subpartition-level partitioning information, subpartition storage parameters, and subpartition statistics generated by the DBMS_STATS package or the ANALYZE statement.
DBA_PART_KEY_COLUMNSALL_PART_KEY_COLUMNS
USER_PART_KEY_COLUMNS
Display the partitioning key columns for partitioned tables.
DBA_SUBPART_KEY_COLUMNSALL_SUBPART_KEY_COLUMNS
USER_SUBPART_KEY_COLUMNS
Display the subpartitioning key columns for composite-partitioned tables (and local indexes on composite-partitioned tables).
DBA_PART_COL_STATISTICSALL_PART_COL_STATISTICS
USER_PART_COL_STATISTICS
Display column statistics and histogram information for the partitions of tables.
DBA_SUBPART_COL_STATISTICSALL_SUBPART_COL_STATISTICS
USER_SUBPART_COL_STATISTICS
Display column statistics and histogram information for subpartitions of tables.
DBA_PART_HISTOGRAMSALL_PART_HISTOGRAMS
USER_PART_HISTOGRAMS
Display the histogram data (end-points for each histogram) for histograms on table partitions.
DBA_SUBPART_HISTOGRAMSALL_SUBPART_HISTOGRAMS
USER_SUBPART_HISTOGRAMS
Display the histogram data (end-points for each histogram) for histograms on table subpartitions.
DBA_PART_INDEXESALL_PART_INDEXES
USER_PART_INDEXES
Display partitioning information for partitioned indexes.
DBA_IND_PARTITIONSALL_IND_PARTITIONS
USER_IND_PARTITIONS
Display the following for index partitions: partition-level partitioning information, storage parameters for the partition, statistics collected by the DBMS_STATS package or the ANALYZE statement.
DBA_IND_SUBPARTITIONSALL_IND_SUBPARTITIONS
USER_IND_SUBPARTITIONS
Display the following information for index subpartitions: partition-level partitioning information, storage parameters for the partition, statistics collected by the DBMS_STATS package or the ANALYZE statement.
DBA_SUBPARTITION_TEMPLATESALL_SUBPARTITION_TEMPLATES
USER_SUBPARTITION_TEMPLATES
Display information about existing subpartition templates.

Labels: ,

Monday, March 7, 2011

How to extend disk space in Linux LVM

Environment
Quater rack Exadata

Problem
Limited disk space in the DB server.

[oracle@thordb02 ~]$ df -h
Filesystem                                           Size  Used   Avail       Use%   Mounted on
/dev/mapper/VGExaDb-LVDbSys1
                                                          30G  4.4G    24G       16%     /
/dev/sda1                                        124M   16M   102M     14%     /boot
/dev/mapper/VGExaDb-LVDbOra1
                                                         99G   12G    82G        13%    /u01
tmpfs                                                81G   192M   81G       1%     /dev/shm

However, "vgdisplay" shows that there are 403 GB free physical extends:

[root@exadata02~]# vgdisplay
  --- Volume group ---
  VG Name               VGExaDb
  System ID
  Format                lvm2
  Metadata Areas        1
  Metadata Sequence No  4
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                3
  Open LV               3
  Max PV                0
  Cur PV                1
  Act PV                1
  VG Size               557.62 GB
  PE Size               4.00 MB
  Total PE              142751
  Alloc PE / Size       39424 / 154.00 GB
  Free  PE / Size       103327 / 403.62 GB
  VG UUID               97W2L1-SnRc-MyS2-UvNJ-hkiB-df6t-b0SdWf


Solution
I followed instructions found in this article to extend the free disk space in the server, here are the commands I used:
1 lvcreate --name u02 --size 400g VGExaDb
2 mkfs.ext3 /dev/VGExaDb/u02
3 mount /dev/VGExaDb/u02 /u02
4 Edit /etc/fstab file to add /u02 so that when server reboots, /u02 will be automatically mounted.

References

  1. A Beginner's Guide To LVM - Page 2
  2. Extending a logical volume
  3. Linux Logical Volume Manager Walkthrough
  4. Can I run fsck or e2fsck when Linux file system is mounted?
  5. Extending a Logical Volume
  6. Logical Volume Manager: How can I extend a Volume Group?
  7. Quick HOWTO : Ch27 : Expanding Disk Capacity
  8. The Linux Logical Volume Manager





Labels: ,

Thursday, March 3, 2011

EXADATA: How to obtain key information like serial numbers [ID 748983.1]

EXADATA: How to obtain key information like serial numbers [ID 748983.1]
Following was copied from the above note:

[root ~]# dmidecode -s system-product-name
ProLiant DL360 G5
[root ~]# dmidecode -s system-serial-number
USE835NAJG

Another option will be generating a file with the data generated by command dmidecode and uploading the file to the Service Request.

#dmidecode > `hostname`_dmidecode.txt

Labels: