Wednesday, June 27, 2012

How to get explain plan from Netezza?


explain verbose select * from tablename;





Importance of GROOM after Altering a Table...


In Netezza, when you update your table definition, like adding columns, Netezza creates a separate version of the table behind the scenes with the new schema. 

New records then get inserted into the new version. 

During query execution the multiple versions of the table are merged together using a UNION ALL operation.  

To merge the two versions of your table together do a GROOM TABLE tablename VERSIONS; 

How can I find the tables that are versioned? 


SELECT tablename
FROM _v_table
WHERE   RELVERSION<>0



Note:
If you have multiple versions of a table due to adding/dropping a column, you cannot use the groom command to clean up deleted rows until you first use the groom versions command to merge the multiple versions of the table into a single current version.

Tuesday, June 19, 2012

SRVCTL Commands List...

Here are the SRVCTL commands list summary. Click on the command to get more details from Ora Docs.

Command
Description
Adds the node applications, database, database instance, ASM instance, or service.
Removes the node applications, database, database instance, ASM instance, or service.
Lists the configuration for the node applications, database, ASM instance, or service.
Enables the database, database instance, ASM instance, or service.
Disables the database, database instance, ASM instance, or service.
Starts the node applications, database, database instance, ASM instance, or service.
Stops the node applications, database, database instance, ASM instance, or service.
Modifies the node applications, database, database instance, or service configuration.
Relocates the service from one instance to another.
Obtains the status of the node applications, database, database instance, ASM instance, or service.
Displays the environment variable in the configuration for the node applications, database, database instance, or service.
Sets and unsets the environment variable in the configuration for the node applications, database, database instance, or service.

SCAN SRVCTL Commands

Here are the list of commands that are pertaining to 11gR2 SCAN:


SRVCTL Command
What it does
srvctl config scan
Shows the current SCAN configuration
srvctl config scan_listener
Shows the existence and port numbers for the SCAN listeners
srvctl add scan -n cluster01-scan
Adds new SCAN information for a cluster
srvctl remove scan -f
Removes SCAN information
srvctl add scan_listener
Adds a new SCAN listener for a cluster on the default port of 1521
srvctl add scan_listener -p 65001 ## non default port number ##
Adds a new SCAN listener on a different port
srvctl remove scan_listener
Removes the SCAN listener
srvctl modify scan -n cluster_scan
Modifies SCAN information (used when changing SCAN to DNS after initially using /etc/hosts)
srvctl modify scan_listener -u
Modifies the SCAN listener information to match the new SCAN VIP information from the modify scan command
srvctl status scan
Shows the status of SCAN
srvctl status scan_listener
Shows the status of SCAN_LISTENERs

Monday, June 4, 2012

Cannot load ICU resource bundle 'ggMessage', error code 2

Environment:
OS: Linux x86 32bit
Oracle: 11g 32bit
GoldenGate: 11.2.1.0.1

You get the following error when ggsci is initiated from any other directory other than its own installed directory.
------------------------------------------------------------------------------------------------------
/home/oracle:(san11g)$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



GGSCI (sanoralnx.sanora) 1> dblogin userid ggs_owner, password ggs_owner
Cannot load ICU resource bundle 'ggMessage', error code 2 - No such file or directory
Aborted
------------------------------------------------------------------------------------------------------

Solution:
Initiate ggsci from the directory where GoldenGate is installed and you will be fine:
------------------------------------------------------------------------------------------------------
/home/oracle:(san11g)$ cd $GG_HOME
/home/app/ggs:(san11g)$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



GGSCI (sanoralnx.sanora) 1> dblogin userid ggs_owner, password ggs_owner
Successfully logged into database.

GGSCI (sanoralnx.sanora) 2>
------------------------------------------------------------------------------------------------------