ORACLE RAC Troubleshooting Best Practices

When we are working with Oracle RAC setup before start the database we want to know weather the Cluster environment service are on line.The below commands are generally used to check the status of Cluster environment.

Checking CRS Status:

The below two commands are generally used to check the status of CRS. The first command lists the status of CRS on the local node where as the other command shows the CRS status across all the nodes in Cluster.

crsctl check crs <<-- for the local node
crsctl check cluster <<-- for remote nodes in the cluster

[grid@rac1 ~]$ crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
For the below command to run, CSS needs to be running on the local node. The "ONLINE" status for remote node says that CSS is running on that node. When CSS is down on the remote node, the status of "OFFLINE" is displayed for that node.

[grid@rac1 ~]$ crsctl check cluster
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

No Of Nodes configured in Cluster:
The below command can be used to find out the number of nodes registered into the cluster. It also displays the node's Public name, Private name and Virtual name along with their numbers.
[grid@rac1 ~]$ olsnodes -n -i -s
rac1    1       rac1-vip        Active
rac2    2       rac2-vip        Active

[grid@rac1 ~]$ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.1.0]

[oracle@rac1 ~]$ cluvfy comp crs -n all -verbose

Verifying CRS integrity

Checking CRS integrity...
The Oracle clusterware is healthy on node "rac2"
The Oracle clusterware is healthy on node "rac1"

CRS integrity check passed

Verification of CRS integrity was successful.

Backups & Restore of MySQL Databases Using mysqldump

If you work with MySQL at any level you may need to backup your databases & restore.MySQL is most popular open source RDBMS widely use.MySQL that has always disappointed me  because of there are so many free tools for backing up databases confused when selecting the exact tool.Backups are extremely easy in MySQL to create with mysqldump."mysqldump" is the only backup tool that most MySQL users know and use, and it’s available whatever MySQL.

Database Backup

Backup a single database
 you can also use the shortcuts -u and -p to specify username and password
mysqldump --user=... --password=...   your_database_name >  your_database_name.sql

Backup multiple databases
mysqldump --user=... --password=... --databases db1 db2 > backup.sql

All the databases
mysqldump --user=... --password=... --all-databases > backup.sql

Restoring DB Backup

Created the destination database & then created the users & give appropriate privileges.Restoring is usually done this way:

mysql –u root –p[password] restoredbname < dbname_backup.sql

Watching Content of Embedded H2 Database Through Browser in WSO2 Products

H2 is a RDBMS written in Java.This database can be used in embedded mode,only thing is add the h2*.jar to the class path & do not have any other dependencies.All the WSO2 products are default shipped with H2 database.Graphical interfaces to run SQL commands and display their results. there are H2 Database Query Tool also available.


1. Download a  WSO2 product and extract

2. Open the file "carbon.xml" in the location repository/conf

3. Enable the followe H2DatabaseConfiguration only

<H2DatabaseConfiguration>
        <property name="web" />
        <property name="webPort">8082</property>
        <property name="webAllowOthers" />
        <!--property name="webSSL" />
        <property name="tcp" />
        <property name="tcpPort">9092</property>
        <property name="tcpAllowOthers" />
        <property name="tcpSSL" />
        <property name="pg" />
        <property name="pgPort">5435</property>
        <property name="pgAllowOthers" />
        <property name="trace" />
        <property name="baseDir">${carbon.home}</property-->
    </H2DatabaseConfiguration>
</Server>

In here we do not need to start PG server therefor only enabling
        <property name="web" />
        <property name="webPort">8082</property>
        <property name="webAllowOthers" />
is enough

4. Save the file and close

5. Start the server


So now that If wanted to watch the content of this db browser page should open with the URL http://localhost:8082 to in browser


Login

Select [Generic H2] and JDBC URL : jdbc:h2:<file path to CARBON_HOME>/repository/database/WSO2CARBON_DB
username : wso2carbon
password  : wso2carbon

 You are now logged in & ow h2 database use WSO2 client that can run SQL commands and display their results

How to Convert WSDL to Java Using WSO2 Application Server

WSDL(Web Services Development Language) is an XML document used to describe web services & how to access them.

To convert WSDL to Java in there are tool/command available(EX:-Using Maven,Apache ant). WSDL facilitate to write the web service using the tool and command available.However the WSO2 Application Server support to achieve this substantially without a depth knowledge of java .To develop a  web services two approaches can be used.Java-first & WSDL-first.Further WSDL-first is a thinking design and Java-first coding is an implementation.Hence it is  easy to develop WSDL-first approach.

Therefore we need to install WSO2 AS which is not a big task and steps such as downloading, running the system, accessing service via browser should follow.

1. Download the WSO2 Application Server binary distribution.
2. Extract the zip archive where you want the WSO2 AS installed
3. Set the JAVA_HOME environment variable to your Java installation, and the PATH environment variable to the Java /bin directory.
4. Execute the WSO2 AS start script from the bin folder.(Linux/Unix :sh wso2server.sh/Windows :wso2server.bat)
5. Check your WSO2 AS instance using the URL https://localhost:9443/carbon which will take you to the WSO2 AS Management Console.
6. Login as "admin" using the default password "admin"


There in the user interface you will find the tool WSDL2Java among few other tools like Java2WSDL in the left-hand side.

When you go for WSDL2Java tool it will allow you to enter the relevant path of WSDL file and few other options. You can select them as want and click on "Generate" button.

Then it will automatically download the generated code with stub classes to your local machine and you can place it any where accordingly and carry on developing the client.Further we can write our business logic and then expose the service to the client.


Using this WSDL Converter easily generate WSDL version 2.0 document
by using  existing WSDL 1.1 document

All About TABLESPACES

An Oracle tablespace is a big unit of logical storage in an Oracle database. It is managed and used by the Oracle server to store structures data objects, like tables and indexes.
An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.

Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.
A database's data is collectively stored in the datafiles that constitute each tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).

If you want to get a list of all tablespaces used in the current database instance, you can use the DBA_TABLESPACES view as shown in the following SQL script example:

SQL> connect SYSTEM/fyicenter
Connected.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS
  2  FROM USER_TABLESPACES;
TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
UNDO                           ONLINE    UNDO
SYSAUX                         ONLINE    PERMANENT
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT

If you want a new tablespace, you can use the CREATE TABLESPACE ... DATAFILE statement as shown in the following script:
SQL> CREATE TABLESPACE my_space
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS
  2  FROM USER_TABLESPACES;
TABLESPACE_NAME  STATUS          CONTENTS
---------------- --------------- ---------
SYSTEM           ONLINE          PERMANENT
UNDO             ONLINE          UNDO
SYSAUX           ONLINE          PERMANENT
TEMP             ONLINE          TEMPORARY
USERS            ONLINE          PERMANENT
MY_SPACE         ONLINE          PERMANENT

If you have an existing tablespace and you don't want it anymore. You can delete a tablespace by using the DROP TABLESPACE statement as shown in the example below:
SQL> CREATE TABLESPACE my_space
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> DROP TABLESPACE my_space;
Tablespace dropped.

After you have created a new tablespace, you can give it to your users for them to create tables in the new tablespace. To create a table in a specific tablespace, you need to use the TABLESPACE clause in the CREATE TABLE statement. Here is a sample script:
SQL> connect SYSTEM/fyicenter
Connected.

SQL> CREATE TABLESPACE my_space
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE my_team TABLESPACE my_space
  2  AS SELECT * FROM employees;
Table created.

SQL> SELECT table_name, tablespace_name, num_rows
  2  FROM USER_TABLES
  3  WHERE tablespace_name in ('USERS', 'MY_SPACE');

TABLE_NAME                     TABLESPACE_NAME    NUM_ROWS
------------------------------ ---------------- ----------
MY_TEAM                        MY_SPACE           - 
EMPLOYEES                      USERS              107
...

If you created a tablespace with a data file a month ago, now 80% of the data file is used, you should add another data file to the tablespace. This can be done by using the ALTER TABLESPACE ... ADD DATAFILE statement. See the following sample script:
SQL> connect HR/fyicenter

SQL> CREATE TABLESPACE my_space
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> ALTER TABLESPACE my_space
  2  DATAFILE '/temp/my_space_2.dbf' SIZE 5M;
Tablespace altered.

SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES
  2  FROM DBA_DATA_FILES;
TABLESPACE_NAME FILE_NAME                             BYTES
--------------- --------------------------------- ---------
USERS           C:\ORACLEXE\ORADATA\XE\USERS.DBF  104857600
SYSAUX          C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 461373440
UNDO            C:\ORACLEXE\ORADATA\XE\UNDO.DBF    94371840
SYSTEM          C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 356515840
MY_SPACE        C:\TEMP\MY_SPACE.DBF               10485760
MY_SPACE        C:\TEMP\MY_SPACE_2.DBF              5242880

SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES
  2  FROM USER_FREE_SPACE
  3  WHERE TABLESPAE_NAME IN ('MY_SPACE');
TABLESPACE_NAME                   FILE_ID      BYTES
------------------------------ ---------- ----------
MY_SPACE                                6    5177344
MY_SPACE                                5   10354688
This script created one tablespace with two data files.

ORA-00959: tablespace ‘TB001′ does not exist When Data Pump Import

When importing data from different database, sometimes you get errors like:

ORA-39083: Object type TABLESPACE_QUOTA failed to create with error:
ORA-00959: tablespace 'TB001' does not exist
Failing sql is:

This means that the tablespace “TB001″ doesn’t exist in the database where you’re importing the data. For this, you can use REMAP_TABLESPACE option. If you have more than one tablespace that doesn’t exist in the second database, use comma as follows:


REMAP_TABLESPACE=db01_tb001:db02_tbs,db_01_tb002:db02_tbs

To get which remap script you need to create, check TABLESPACE_NAME column for the DBA_SEGMENTS view and find in which tablespaces your objects are reside

Simply View Oracle Session Usage

Here are some of PL/SQL scripts related to gather session statistics that I have developed to monitor the session usage.If the database getting slow it is possible to analyze using this that the resource usage by user connected

 SELECT
  'Currently, '
  || (SELECT COUNT(*) FROM V$SESSION)
  || ' out of '
  || DECODE(VL.SESSIONS_MAX,0,'unlimited',VL.SESSIONS_MAX)
  || ' connections are used.' AS USAGE_MESSAGE
FROM
  V$LICENSE VL

 SELECT
  'Currently, '
  || (SELECT COUNT(*) FROM V$SESSION)
  || ' out of '
  || VP.VALUE
  || ' connections are used.' AS USAGE_MESSAGE
FROM
  V$PARAMETER VP
WHERE VP.NAME = 'sessions'

 SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
       ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
       used_mb,
       NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
       FROM v$datafile df,dba_free_space dfs
       WHERE df.file# = dfs.file_id(+)
       GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
       ORDER BY file_name;