Friday, September 30, 2011

How to pin nodes in RAC

0 comments

The following steps can be used to pin nodes in RAC.
Pinning a node means that the association of a node name with a node number is fixed. If a node is not pinned,node number may change if the lease expires while it is down. The lease of a pinned node never expires.

To find whether a node in a cluster is pinned,use the olsnodes command.

[grid@raclinux1 stage10g]$ olsnodes -t
raclinux1 Unpinned
raclinux2 Unpinned
 
Use the pin command to pin the nodes 

[root@raclinux1 bin]# ./crsctl pin css -n raclinux1
CRS-4664: Node raclinux1 successfully pinned.
[root@raclinux1 bin]#
 

[root@raclinux2 bin]# ./crsctl pin css -n raclinux2
CRS-4664: Node raclinux2 successfully pinned.
[root@raclinux2 bin]#
 
[grid@raclinux1 stage10g]$ olsnodes -t
raclinux1 Pinned 
raclinux2 Pinned
 

Tuesday, September 27, 2011

Using SYSDBA for datapump export and import

0 comments



Here is a quote from MOS on the usage oof SYSDBA for export and import

*How to Connect AS SYSDBA when Using Export or Import

[ID 277237.1]*

SYSDBA is used internally in the Oracle database and has specialized functions. Its behavior is not the same as for generalized users. For example, the SYS user cannot do a transaction level consistent read (read-only transaction). Queries by SYS will return changes made during the

transaction even if SYS has set the transaction to be READ ONLY. 

Therefore export parameters like CONSISTENT, OBJECT_CONSISTENT, FLASHBACK_SCN, and FLASHBACK_TIME cannot be used. Starting with Oracle10g, the export shows a warning that the export is not

consistent when the export is started with CONSISTENT=Y and connects to the database with the user SYS (or as SYSDBA):

EXP-00105: parameter CONSISTENT is not supported for this user

Note that Oracle automatically provides read consistency to a query so that all the data that the query sees comes from a single point in time (statement-level read consistency). For export this means that the export of

table data is consistent. However, if a table contains nested tables, the outer table and each inner table are exported as separate transactions. And if a table is partitioned, each partition is exported as a separate transaction. If a nested table or a partitioned table was updated during the

export, the data that is exported while connected as the SYS schema could be inconsistent.

This could be reason when you see 'duplicate key on index' and related errors when you perform datapump import using impdp. For eg :- There were some duplicate value on index of some tables which were taken using datapump export as sysdba with flashback parameters. The table does not have duplicate values in production.

Typically, there is no need to invoke Export or Import as SYSDBA, except in the following situations:

- at the request of Oracle technical support;

- when exporting a transportable tablespace set with the old style export utility (Oracle9i and Oracle8i);

- when importing a transportable tablespace set with the old-style import utility (Oracle10g, Oracle9i, and Oracle8i)."

Monday, September 26, 2011

Privileges required for generating AWR reports

0 comments
Some times you may have a requirment to generate AWR reports using non - administrator schemas.

The following privileges are required for a SCHEMA to generate AWR reports.

grant SELECT ON SYS.V_$DATABASE to SCHEMA;                     
SELECT ON SYS.V_$INSTANCE  to SCHEMA;
EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY  to  SCHEMA;
SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE  to SCHEMA;
SELECT ON SYS.DBA_HIST_SNAPSHOT  to SCHEMA;
GRANT ADVISOR  to SCHEMA;

Change oracle listener log file

0 comments

Listener log files may grow very big in size and you may sometimes want to delete the existing log file to free up some space. When you try to delete the log file in windows, you will get file already in use message.

To solve this, you can change the listener log file using the below commands

lsnrctl

set log_file listener1.log

Where listener1.log is the new log file, now you can delete the old log file.

You can also turn off listener logging as below

lsnrctl

set log_status off

Sunday, September 25, 2011

Create a Local Listener on non default port ( ports other than 1521)

0 comments
On some circumstances you may need to have separate listeners running on seperate ports for your databases.When you create a database, the default listener will be created and listens on port 1521. There may be cases when you want to run multiple listeners for your databases.

If you want PMON to register with a local listener that does not use TCP/IP, port 1521, configure the LOCAL_LISTENER parameter in the initialization parameter file to locate the local listener.

For a shared server environment, you can alternatively use the LISTENER attribute of the DISPATCHERS parameter in the initialization parameter file to register the dispatchers with a nondefault local listener. Because both the LOCAL_LISTENER parameter and the LISTENER attribute enable PMON to register dispatcher information with the listener, it is not necessary to specify both the parameter and the attribute if the listener values are the same.

The steps are as follows.

1. Create new non default listener
    Create a new listener using $ORACLE_HOME/netca for this.


2.Set the LOCAL_LISTENER parameter for your database. You can set this in two ways.
a ) Initialization parameter file (PFILE)
     a ) Ensure you have ORACLE_HOME and ORACLE_SID environment variable set
     b) sqlplus / as sysdba
     c) create pfile='$ORACLE_HOME/dbs/init.ora from spfile;
     d) Open $ORACLE_HOME/dbs/init.ora and add the following entry
            *.local_listener=listener1
                   where listener1 is the name you want to give for your new listener.
     e) shut immediate;
     f) startup pfile='$ORACLE_HOME/dbs/init.ora'


b) Server parameter file ( SPFILE )
     a ) Ensure you have ORACLE_HOME and ORACLE_SID environment variable set
     b) sqlplus / as sysdba
     c) alter system set local_listener=listener1 scope=spfile sid='*';
     d) shut immediate;
     e) startup;

2.Resolve listener name with tnsnames.ora
listener name ( in this case listener1) is then resolved to the listener protocol addresses through a naming method, such as a tnsnames.ora file on the database server.

Add the following entry in your database server tnsnames.ora

listener1=
 (DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST= )(PORT=<port you wish to configure>)))

3.Restart the database
a ) Ensure you have ORACLE_HOME and ORACLE_SID environment variable set
     a) sqlplus / as sysdba
     b) shut immediate;
     c) startup;

You will see that database is registered with new listener.

Refer : http://download.oracle.com/docs/cd/B13789_01/network.101/b10775/listenercfg.htm

Saturday, September 24, 2011

Change SYSMAN account password Oracle RAC / Single Instance database

0 comments
Following steps can  be used to change SYSMAN account password for Oracle RAC / single instance database. This can be done online.

Login to the server where target database runs as oracle software owner os user.

ibs-ash-sr133 oraaires [MYDBSTG2]:emctl status dbconsole
Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name.
ibs-ash-sr133 oraaires [MYDBSTG2]:sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Aug 12 11:17:08 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter db_uni
NAME                                 TYPE        VALUE
------------------------------------------------------
db_unique_name                       string      MYDBSTG
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

ibs-ash-sr133 oraaires [MYDBSTG2]:export ORACLE_UNQNAME=MYDBSTG
ibs-ash-sr133 oraaires [MYDBSTG2]:emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
https://ibs-ash-sr133.ibsdc.com:5500/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /projects/aires/product/11.2.0/aires_stg/ibs-ash-sr133_MYDBSTG/sysman/log

ibs-ash-sr133 oraaires [MYDBSTG2]:emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
https://ibs-ash-sr133.ibsdc.com:5500/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
 ...  Stopped.
ibs-ash-sr133 oraaires [MYDBSTG2]:emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
https://ibs-ash-sr133.ibsdc.com:5500/em/console/aboutApplication
Oracle Enterprise Manager 11g is not running.

ibs-ash-sr133 oraaires [MYDBSTG2]:sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Aug 12 11:19:42 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> alter user sysman identified by we#t04q;

User altered.

SQL> conn sysman/we#t04q
Connected.
SQL> show user
USER is "SYSMAN"
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

ibs-ash-sr133 oraaires [MYDBSTG2]:emctl setpasswd dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
https://ibs-ash-sr133.ibsdc.com:5500/em/console/aboutApplication
Please enter new repository password:
Repository password successfully updated.

ibs-ash-sr133 oraaires [MYDBSTG2]:emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
https://ibs-ash-sr133.ibsdc.com:5500/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ....... started.
------------------------------------------------------------------
Logs are generated in directory /projects/aires/product/11.2.0/aires_stg/ibs-ash-sr133_MYDBSTG/sysman/log
ibs-ash-sr133 oraaires [MYDBSTG2]:emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
https://ibs-ash-sr133.ibsdc.com:5500/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /projects/aires/product/11.2.0/aires_stg/ibs-ash-sr133_MYDBSTG/sysman/log
ibs-ash-sr133 oraaires [MYDBSTG2]:


Reference : How To Change the Password of the Database User Sysman (DB Control Repository Schema) [ID 259379.1]

Unix : Split / Print / Grep lines before and after search pattern

0 comments
You can use ggrep command to print or grep a certain number of lines before or after a particular search string / pattern from a file.
This will be quite useful when you know a search string / pattern, for eg : ORA-00257: archiver error. Connect internal only. You might want to see from when onwards this error has been reported in alert log. The log might be very huge , a vi /  tail might be time consuming in this case. Use the below command.
/usr/sfw/bin/ggrep -A 5 -B 5 'ORA-19815:' alert_idckf.log > 1    

Here -A 5 and -B 5 , where 5 stands for number of lines before and after the search string "ORA-19815:'"

/usr/sfw/bin/ggrep -B 10 'ORA-19815: WARNING: db_recovery_file_dest_size of 26754416640 bytes is 100.00% used' alert_idckf.log > 1
  Here , the search is only for printing 10 lines after the search string  'ORA-19815: WARNING: db_recovery_file_dest_size of 26754416640 bytes is 100.00% used' from the file alert_idckf.log. The output is redirected to 1, which can be viewed.









Thursday, September 22, 2011

Reboot-less node fencing in Oracle Clusterware 11g Release 2

0 comments
There has been several improvements in the node eviction and split brain scenarios with Oralce 11G R2 RAC.

Oracle Clusterware uses a STONITH (Shoot The Other Node In The Head) comparable fencing algorithm to ensure data integrity in cases, in which cluster integrity is
endangered and split-brain scenarios need to be prevented. In case of Oracle Clusterware, this means that a local process enforces the removal of one or more nodes from the cluster (fencing).
Until Oracle Clusterware 11g Release 2, Patch Set One (11.2.0.2) the fencing of a node was performed by a “fast reboot” of the respective server. A “fast reboot” in this context summarizes a shutdown and restart procedure that does not wait for any IO to finish or for file systems to synchronize on shutdown. With Oracle Clusterware 11g Release 2, Patch Set One (11.2.0.2) this mechanism has been changed in order to prevent such a reboot as much as possible.

Already with Oracle Clusterware 11g Release 2 this algorithm was improved so that failures of certain, Oracle RAC-required subcomponents in the cluster do not necessarily cause an
immediate fencing (reboot) of a node. Instead, an attempt is made to clean up the failure within the cluster and to restart the failed subcomponent. Only, if a cleanup of the failed component appears to be unsuccessful, a node reboot is performed in order to force a cleanup.

With Oracle Clusterware 11g Release 2, Patch Set One (11.2.0.2) further improvements were made so that Oracle Clusterware will try to prevent a split-brain without rebooting the node. It thereby implements a standing requirement from those customers, who were requesting to preserve the node and to prevent a reboot, since the node runs applications not managed by Oracle Clusterware, which would otherwise be forcibly shut down by the reboot of a node.

With the new algorithm and when a decision is made to evict a node from the cluster, Oracle Clusterware will first attempt to shutdown all resources on the machine that was chosen to be the subject of an eviction. Especially IO generating processes are killed and it is ensured that those processes are completely stopped before continuing. If, for some reason, not all resources can be stopped or IO generating processes cannot be stopped completely, Oracle Clusterware will still perform a reboot or use IPMI to forcibly evict the node from the cluster.
If all resources can be stopped and all IO generating processes can be killed, Oracle Clusterware will shut itself down on the respective node, but will attempt to restart after the stack has been stopped. The restart is initiated by the Oracle High Availability Services Daemon, which has been introduced with Oracle Clusterware 11g Release 2.

Redundant Interconnect Usage in 11G R2 RAC

0 comments
We have thought of implementing this new feature with Oracle 11G as we have frequent private NIC down events which further results in node evictions.

Redundant Interconnect without any 3rd-party IP failover technology (bond, IPMP or similar) is supported natively by Grid Infrastructure starting from 11.2.0.2.  Multiple private network adapters can be defined either during the installation phase or afterward using the oifcfg.  Oracle Database, CSS, OCR, CRS, CTSS, and EVM components in 11.2.0.2 employ it automatically.

Grid Infrastructure can activate a maximum of four private network adapters at a time even if more are defined. The ora.cluster_interconnect.haip resource will start one to four link local  HAIP on private network adapters for interconnect communication for Oracle RAC, Oracle ASM, and Oracle ACFS etc.

Grid automatically picks link local addresses from reserved 169.254.*.* subnet for HAIP, and it will not attempt to use any 169.254.*.* address if it's already in use for another purpose. With HAIP, by default, interconnect traffic will be load balanced across all active interconnect interfaces, and corresponding HAIP address will be failed over transparently to other adapters if one fails or becomes non-communicative. .

The number of HAIP addresses is decided by how many private network adapters are active when Grid comes up on the first node in the cluster .  If there's only one active private network, Grid will create one; if two, Grid will create two; and if more than two, Grid will create four HAIPs. The number of HAIPs won't change even if more private network adapters are activated later, a restart of clusterware on all nodes is required for new adapters to become effective

New HAIP reource

--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       ibs-ash-sr118        Started
ora.cluster_interconnect.haip
      1        ONLINE  ONLINE       ibs-ash-sr118
ora.crf
      1        ONLINE  ONLINE       ibs-ash-sr118
ora.crsd
      1        ONLINE  ONLINE       ibs-ash-sr118
ora.cssd
      1        ONLINE  ONLINE       ibs-ash-sr118
ora.cssdmonitor
      1        ONLINE  ONLINE       ibs-ash-sr118
ora.ctssd
      1        ONLINE  ONLINE       ibs-ash-sr118        OBSERVER
ora.diskmon
      1        ONLINE  ONLINE       ibs-ash-sr118
ora.drivers.acfs
      1        ONLINE  ONLINE       ibs-ash-sr118
ora.evmd
      1        ONLINE  ONLINE       ibs-ash-sr118
ora.gipcd
      1        ONLINE  ONLINE       ibs-ash-sr118
ora.gpnpd
      1        ONLINE  ONLINE       ibs-ash-sr118
ora.mdnsd
      1        ONLINE  ONLINE       ibs-ash-sr118
While in previous releases bonding, trunking, teaming, or similar technology was required to make use of redundant network connections between the nodes to be used as redundant,dedicated, private communication channels or “interconnect”, Oracle Clusterware now provides an integrated solution to ensure “Redundant Interconnect Usage”. This functionality is available starting with Oracle Database 11g Release 2, Patch Set One (11.2.0.2).

The Redundant Interconnect Usage feature does not operate on the network interfaces directly. Instead, it is based on a multiple-listening-endpoint architecture, in which a highly available virtual IP (the HAIP) is assigned to each private network (up to a total number of 4 interfaces).By default, Oracle Real Application Clusters (RAC) software uses all of the HAIP addresses for private network communication, providing load balancing across the set of interfaces identified as the private network. If a private interconnect interface fails or becomes non-communicative,then Oracle Clusterware transparently moves the corresponding HAIP address to one of the remaining functional interfaces

Oracle RAC Databases, Oracle Automatic Storage Management (clustered ASM), and Oracle Clusterware components such as CSS, OCR, CRS, CTSS, and EVM components employ
Redundant Interconnect Usage starting with Oracle Database 11g Release 2, Patch Set One (11.2.0.2)

Steps
=============

# $GRID_HOME/bin/oifcfg getif
eth0 10.2.156.0 global public
eth1 192.168.12.0 global cluster_interconnect

The interfaces that are currently stored in the GPnP profile, their subnets, and their role (public or cluster_interconnect) are displayed.

2. Add the remaining LLT links to the GPnP profile:
# $GRID_HOME/bin/oifcfg setif -global \
eth2/192.168.12.0:cluster_interconnect

3. Verify that the correct interface subnet is in use:
# $GRID_HOME/bin/oifcfg getif
eth0 10.2.156.0 global public
eth1 192.168.12.0 global cluster_interconnect
eth2 192.168.2.0 global cluster_interconnect


4.You must restart Oracle Clusterware on all members of the cluster when you make global changes. For local changes, you only need to perform a node restart.

Interconnect changes for the database occur at instance startup. However, the interconnect for Oracle Clusterware might be different.

11gR2 Grid Infrastructure Redundant Interconnect and ora.cluster_interconnect.haip [ID 1210883.1]
How to Modify Private Network Interface in 11.2 Grid Infrastructure [ID 1073502.1]
http://download.oracle.com/docs/cd/B28359_01/rac.111/b28255/oifcfg.htm
How to Change Interconnect/Public Network (Interface or Subnet) in Oracle Clusterware [ID 283684.1]
[11gR2 Grid Infrastructure Redundant Interconnect and ora.cluster_interconnect.haip [ID 1210883.1]]
 
Known Issues
================

A few bug fixes for haip has come with 11.2.0.3

Drop and Create enterprise manager database control ( emca) for 11G R2 RAC

0 comments
With 11G R2 , drop ,creation, de-config,re creation of enterprise manager database console does not put the database in quiesce mode.
The followed to drop , create , de config and re create emca for 11G R2 single instance or RAC.

I have tried ./bin/emca -config dbcontrol db -repos drop to drop enterprise manager repository objects, but it got hung at some point. Finally, I have tried the manual drop option which is very simple and you are almost sure that it will work !

1.Drop db control repository manually

1. stop dbconsole , if running and the following process from ORACLE_HOME (from each node)
$ps -ef | grep console                                           
$ps -ef| grep emwd
$ps -ef | grep emagent
$ps -ef |grep java

2. Set environment  variables
SET ORACLE_HOSTNAME=< Hostname of your machine "
SET ORACLE_HOME=< Location where your database is installed >
SET ORACLE_SID=< name of your database "
SET PATH=%ORACLE_HOME%\bin;%PATH%
Note : You will have to replace SET command with EXPORT for *nix environments

3. Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:

a. drop user sysman cascade;
b. drop role MGMT_USER;
c. drop user MGMT_VIEW cascade;
d. drop public synonym MGMT_TARGET_BLACKOUTS;
e. drop public synonym SETEMVIEWUSERCONTEXT;
f. drop public synonym MGMT_target_blackouts;                  
g. drop public synonym mgmt_severity_array;
h. drop public synonym mgmt_guid_obj;                
 Verify any left over object owned by SYSMAN

SQL> SELECT owner,TABLE_NAME, synonym_name name FROM dba_synonyms WHERE table_owner = 'SYSMAN';

if yo notice any public synonym or any rows returned by the above query .
SQL>

DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;     
END IF;
END LOOP;
END;
/

4.Move or rename config folders on each node :
$rm -rf $ORACLE_HOME/
$rm -rf $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole__

You would replace the values in the <> with the correct values.

5.Create DBconsole repos and configure dbconsole : (Use -cluster when its RAC DB)

$export ORACLE_HOME= (Please give the physical path avoid symbolic links)
$



2.Create emca  repository objects

Execute the below command to create emca db console for RAC
./bin/emca -config dbcontrol db -repos create -cluster
-bash-3.00$ ./bin/emca -config dbcontrol db -repos create -cluster

STARTED EMCA at Aug 19, 2011 8:47:46 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database unique name: bvnprd
Service name: bvnprd
Listener ORACLE_HOME [ /project/11.2.0/grid ]:
Password for SYS user: ^R < Give SYS password in single quotes if there are special characters >
Password for SYS user: ^R
Password for SYS user: ^R
Password for SYS user:
Database Control is already configured for the database bvnprd
You have chosen to configure Database Control for managing the database bvnprd
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: yes
Password for DBSNMP user:
Password for SYSMAN user:
Cluster name: crs
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ /project/11.2.0/grid ]:
ASM port [ 1521 ]:
ASM username [ ASMSNMP ]: SYS < You can also give ASMSNMP user and password below >
ASM user password:
Invalid username/password or database/scan listener not up or database service is not registered with scan listener.
ASM user password:
Invalid username/password or database/scan listener not up or database service is not registered with scan listener.
ASM user password: 3LHj*5x
Invalid username/password or database/scan listener not up or database service is not registered with scan listener.
ASM user password:
Invalid username/password or database/scan listener not up or database service is not registered with scan listener.
ASM user password:  LHj*5x
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /project/oracle/orabase/product/11.2.0/dbhome_1

Database instance hostname ................ dom-ash-sr147.domdc.com
Listener ORACLE_HOME ................ /project/11.2.0/grid
Listener port number ................ 1521
Cluster name ................ crs
Database unique name ................ bvnprd
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
ASM ORACLE_HOME ................ /project/11.2.0/grid
ASM port ................ 1521
ASM user role ................ SYSDBA
ASM username ................ SYS

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: yes
Aug 19, 2011 9:19:52 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /project/oracle/orabase/cfgtoollogs/emca/bvnprd/emca_2011_08_19_08_47_45.log.
Aug 19, 2011 9:19:55 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Aug 19, 2011 9:20:02 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Aug 19, 2011 9:30:49 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Aug 19, 2011 9:31:06 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Aug 19, 2011 9:31:49 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Aug 19, 2011 9:31:50 AM oracle.sysman.emcp.EMDBCConfig instantiateOC4JConfigFiles
INFO: Propagating /project/oracle/orabase/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_dom-ash-sr147_bvnprd to remote nodes ...
Aug 19, 2011 9:31:53 AM oracle.sysman.emcp.EMDBCConfig instantiateOC4JConfigFiles
INFO: Propagating /project/oracle/orabase/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_dom-ash-sr149_bvnprd to remote nodes ...
Aug 19, 2011 9:32:02 AM oracle.sysman.emcp.EMAgentConfig deployStateDirs
INFO: Propagating /project/oracle/orabase/product/11.2.0/dbhome_1/dom-ash-sr147_bvnprd to remote nodes ...
Aug 19, 2011 9:32:11 AM oracle.sysman.emcp.EMAgentConfig deployStateDirs
INFO: Propagating /project/oracle/orabase/product/11.2.0/dbhome_1/dom-ash-sr149_bvnprd to remote nodes ...
Aug 19, 2011 9:32:16 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO:  Location /project/oracle/orabase/product/11.2.0/dbhome_1/EMStagePatches_bvnprd is not shared. Software library could not be configured. Provisioning archives will not be deployed. Please configure it manually.
Aug 19, 2011 9:32:16 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Aug 19, 2011 9:32:32 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Aug 19, 2011 9:33:10 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Aug 19, 2011 9:33:10 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://dom-ash-sr147.domdc.com:1158/em <<<<<<<<<<<
Aug 19, 2011 9:33:12 AM oracle.sysman.emcp.EMDBPostConfig showClusterDBCAgentMessage
INFO:
****************  Current Configuration  ****************
 INSTANCE            NODE           DBCONTROL_UPLOAD_HOST
----------        ----------        ---------------------

bvnprd            dom-ash-sr147     dom-ash-sr147.domdc.com
bvnprd            dom-ash-sr149     dom-ash-sr147.domdc.com


Aug 19, 2011 9:33:12 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: /project/oracle/orabase/product/11.2.0/dbhome_1/dom-ash-sr147_bvnprd/sysman/config/emkey.ora.   Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Aug 19, 2011 9:33:12 AM
-bash-3.00$

Calculate next run time in dbms_scheduler.create_job (job_interval )

0 comments
You control when and how often a job repeats by setting the repeat_interval attribute of the job itself or of the named schedule that the job references. You can set repeat_interval with DBMS_SCHEDULER package procedures or with Enterprise Manager.

The question I had was what will be the next execution date of a job ? Will it be based on start time of one instance of job  or completion time of one instance of job ?

According to Oracle Documentation  http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/scheduse004.htm#i1023312

The result of evaluating the repeat_interval is a set of timestamps.The Scheduler runs the job at each timestamp. Note that the start date from the job or schedule also helps determine the resulting set of timestamps. (See Oracle Database PL/SQL Packages and Types Reference for more information about repeat_interval evaluation.) If no value for repeat_interval is specified, the job runs only once at the specified start date.

Immediately after a job is started, the repeat_interval is evaluated to determine the next scheduled execution time of the job. It is possible that the next scheduled execution time arrives while the job is still running. A new instance of the job, however, will not be started until the current one completes.


This is something that I took sometime to figure out !

Saturday, September 17, 2011

ASM DiskGroup NOT Mounted in 11G R2

1 comments

I was just going to create a 11G R2 RAC  database on my RHEL 5.4 two node cluster. But dbca exists stating that ASM diskgroup where I am planning to put my database files are not mounted.

When I checked status using GRID_HOME/asmca,it shows the disks as mounted. But still dbca reports the same problem

I wanted to login to ASM instance and query the V$ASM_DISKGROUP.

On Node 1, I saw that ASM instance is up.

n310 oracle [+ASM1]:ps -ef | grep smon               
    oracle 355 30041 0 15:06 pts/2 00:00:00 grep smon          
    oracle 6730 1 0 12:51 ? 00:00:00 asm_smon_+ASM1
    oracle 14649 1 0 14:30 ? 00:00:00 ora_smon_nfr1
    oracle 14968 1 0 14:31 ? 00:00:00 ora_smon_nfr3

I thought I will query the V$ASM_DISKGROUP dynamic view to see the state of my ASM diskgroups.

Check the ORACLE_HOME variable from which ASM instance was started.

From the above grep command , I got the PID 6730 for ASM instance.
Go to /proc/PID/environ and check the environment variables with which the ASM instance was started.


n310 oracle [+ASM1]:cat /proc/6730/environ
Here I can see the ORACLE_HOME as /app/oracle/grid.            

export ORACLE_HOME=app/oracle/grid
Since now I got the ORACLE_HOME, Lets see what V$ASM_DISKGROUP shows
 n310 oracle [+ASM1]:export ORACLE_SID=+ASM1
    n310 oracle [+ASM1]:./bin/sqlplus / as sysasm

    SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 15 16:12:43 2011
    Copyright (c) 1982, 2009, Oracle. All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
    With the Real Application Clusters and Automatic Storage Management options

    SQL> select NAME,TOTAL_MB,FREE_MB,STATE from v$ASM_DISKgroup;

    NAME TOTAL_MB FREE_MB STATE
    —————————— ———- ———- ———–
    DATA 8610 7684 MOUNTED
    VOL 0 0 DISMOUNTED

    SQL> alter diskgroup VOL mount;

    Diskgroup altered.

    SQL> select NAME,TOTAL_MB,FREE_MB,STATE from v$ASM_DISKgroup;

    NAME TOTAL_MB FREE_MB STATE
    —————————— ———- ———- ———–
    DATA 8610 7684 MOUNTED
    VOL 38170 37980 MOUNTED

Here, When I logged in as sysdba,I got the following error.

    SQL> alter diskgroup VOL mount;
    alter diskgroup VOL mount
    *
    ERROR at line 1:
    ORA-15032: not all alterations performed
    ORA-15260: permission denied on ASM disk group             

So, I used SYSASM to do ASM management operations.

When I quered the DISKGROUP status in the second node, it shows as MOUNTED.

Find files created in last 24 hours in Unix

0 comments

I just had a requirement recently to start a process and see which all files it write when it is started.
I used a ‘find’ command from the home directory for this.

find . -mtime -1 -print

n310 oracle [CHANGEME]:find . -mtime -1 -print                   
./bin
./dbs
./install
./install/.ee

Table not present in datapump export 11G R2 - Deferred Segment Creation

0 comments
After upgrading database to 11G R2 developers keep complaining that some tables doesn’t come in the export file.They noticed it when they imported this and found that some objects were not in a compiled state.When they tried to compile, they came to know that tables was not imported. There is no error in the export / import log, but some tables are not exported.
BTW, they used imp /exp utilities.
The tables which were not exported were empty- which means they had zero rows in it.

Understand Deferred Segment Creation

Beginning with Oracle Database 11g Release 2, when you create heap-organized tables in a locally managed tablespace, the database defers table segment creation until the first row is inserted. In addition, segment creation is deferred for any LOB columns of the table, any indexes created implicitly as part of table creation, and any indexes subsequently explicitly created on the table.This means that,no segments are allocated until a row is inserted to these table.You can verify this by querying user_tables

SQL> SELECT table_name, segment_created FROM user_tables where rownum < 4;
The advantages of this space allocation method are the following:
  1. It saves a significant amount of disk space in applications that create hundreds or thousands of tables upon installation, many of which might never be populated
  2. It reduces application installation time. There is a small performance penalty when the first row is inserted, because the new segment must be created at that time. 
Here is how you can solve this problem
  1. Set the parameter DEFERRED_SEGMENT_CREATION initialization parameter to FALSE, which is TRUE by default
  2. ALTER table MOVE for the tables which are returned from the above query
  3. INSERT a record to these tables and then rollback

Script to find duplicates for primary key or Unique Index

0 comments
This script will help you to find duplicate values coming in any primary key or unique key or unique index fields.
This will be very useful when you encounter any issues during datapump export or import, when a unique index creation fails with "ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found"
REM This is an example SQL*Plus Script to delete duplicate rows from
REM a table.
REM
set echo off
set verify off heading off
undefine t
undefine c
prompt
prompt
prompt Enter name of table with duplicate rows
prompt
accept t prompt 'Table: '
prompt
select 'Table '||upper('&&t') from dual;
describe &&t
prompt
prompt Enter name(s) of column(s) which should be unique. If more than
prompt one column is specified , you MUST separate with commas.
prompt
accept c prompt 'Column(s): '
prompt
delete from &&t
where rowid not in (select min(rowid) from &&t group by &&c)
/

Find Explain Plan for a given sql_id

0 comments
You can check the explain plan for a given sql_id using following commands.

Suppose the sql_id given was ’6rata4x2qurst’.
The source of sql_id can be a badly performing query from statspack, awr etc.

SQL> select sql_fulltext from gv$sql where sql_id=’6rata4x2qurst’;
SQL_FULLTEXT
——————————————————————————–——————–——————–——————–——————–
select empno from emp where sal > 1000

SQL> explain plan for select empno from emp where sal > 1000;
Explained.
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
—————————————————————————–——–——–——–——–——————–——————–——————–

Plan hash value: 3956160932
————————————————————————–——–——–——————–——————–——————–——————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–——–——–——–——————–——————–——————–——————–
| 0 | SELECT STATEMENT | | 13 | 104 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 13 | 104 | 3 (0)| 00:00:01 |
————————————————————————–——————–——————–——————–——————–——————–
Predicate Information (identified by operation id):
———————————————————————–——————–——————–——————–——————–——————–
PLAN_TABLE_OUTPUT
—————————————————————————–——————–——————–——————–——————–——————–
1 – filter(“SAL”>1000)
13 rows selected.

Find Cluster Name in 11g R2 RAC

0 comments
When you configure enterprise manager db control in RAC, using emca command, it asks for cluster name . You can find the cluster name using cemutlo command.

You should change your path to GRID_HOME/bin directory and execute the following command.

-bash-3.00$ ./cemutlo -n                                         
crs
 

ORA-BLOG. Copyright 2008 All Rights Reserved Revolution Two Church theme by Brian Gardner Converted into Blogger Template by Bloganol dot com