My 11gR2 grid infrastructure completed with only one scan listener. The following log shows that ....

rac-scan.example.com IN A 133.22.67.194
IN A 133.22.67.193
IN A 133.22.67.192

[grid@rac02 grid]$ Check_CRS.txt
HA Resource                                        Target     State
-----------                                        ------     -----
ora.CRS.dg                                         ONLINE     ONLINE on rac01
ora.FRA.dg                                         ONLINE     ONLINE on rac01
ora.LISTENER.lsnr                                  ONLINE     ONLINE on rac01
ora.LISTENER_SCAN1.lsnr                            ONLINE     ONLINE on rac01
ora.RACDB_DATA.dg                                  ONLINE     ONLINE on rac01
ora.asm                                            ONLINE     ONLINE on rac01
ora.eons                                           ONLINE     ONLINE on rac01
ora.gsd                                            OFFLINE    OFFLINE
ora.net1.network                                   ONLINE     ONLINE on rac01
ora.oc4j                                           OFFLINE    OFFLINE
ora.ons                                            ONLINE     ONLINE on rac01
...
...
ora.rac02.vip                                      ONLINE     ONLINE on rac02
ora.rac10g.db                                      ONLINE     ONLINE on rac02
ora.rac10g.rac10g1.inst                            ONLINE     ONLINE on rac01
ora.rac10g.rac10g2.inst                            ONLINE     OFFLINE
ora.racpoc.askm1.svc                               ONLINE     ONLINE on rac01
ora.racpoc.db                                      ONLINE     ONLINE on rac01
ora.registry.acfs                                  ONLINE     ONLINE on rac01
ora.scan1.vip                                      ONLINE     ONLINE on rac01
ora.testdb.db                                      ONLINE     ONLINE on rac01

tnsnames.ora file :

TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb.example.com)
)
)

The above session data clearly shows there is only one scan listener configured. When we use scan name in tnsnames.ora, the client connections will be established to database only when the scan name resolves to valid IP address.
But with the above configurations, the client connections some times may fail when the SCAN is resolving to 133.22.67.193 or 133.22.67.194. It establishes connection only when the SCAN resolves to 133.22.67.192.

Now to avoid this , we need to create two more scan listeners.


[grid@rac02 grid]$ Check_CRS.txt
HA Resource                                        Target     State
-----------                                        ------     -----
ora.CRS.dg                                         ONLINE     ONLINE on rac01
ora.FRA.dg                                         ONLINE     ONLINE on rac01
ora.LISTENER.lsnr                                  ONLINE     ONLINE on rac01
ora.LISTENER_SCAN1.lsnr                            ONLINE     ONLINE on rac01
ora.LISTENER_SCAN2.lsnr                            ONLINE     ONLINE on rac02
ora.LISTENER_SCAN3.lsnr                            ONLINE     ONLINE on rac02
ora.RACDB_DATA.dg                                  ONLINE     ONLINE on rac01
ora.asm                                            ONLINE     ONLINE on rac01
ora.eons                                           ONLINE     ONLINE on rac01
ora.gsd                                            OFFLINE    OFFLINE
ora.net1.network                                   ONLINE     ONLINE on rac01
ora.oc4j                                           OFFLINE    OFFLINE
ora.ons                                            ONLINE     ONLINE on rac01
...
...
ora.rac02.vip                                      ONLINE     ONLINE on rac02
ora.rac10g.db                                      ONLINE     ONLINE on rac02
ora.rac10g.rac10g1.inst                            ONLINE     ONLINE on rac01
ora.rac10g.rac10g2.inst                            ONLINE     OFFLINE
ora.racpoc.askm1.svc                               ONLINE     ONLINE on rac01
ora.racpoc.db                                      ONLINE     ONLINE on rac01
ora.registry.acfs                                  ONLINE     ONLINE on rac01
ora.scan1.vip                                      ONLINE     ONLINE on rac01
ora.scan2.vip                                      ONLINE     ONLINE on rac02
ora.scan3.vip                                      ONLINE     ONLINE on rac02
ora.testdb.db                                      ONLINE     ONLINE on rac01
[grid@rac02 grid]$

Status :

[grid@rac01 ~]$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node rac01
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node rac02
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node rac02
[grid@rac01 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node rac01
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node rac02
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node rac02
[grid@rac01 ~]$

Hope it helps
SRI

11gR2 grid infrastructure uses SCAN, which is a new concept we use for all client connections. SCAN is single client access name and it is a new feature in 11g Release 2 that provides a single name for clients to access an Oracle Database running in a cluster. The benefit is clients using SCAN do not need to change if you add or remove nodes in the cluster.

The only difference is that , we specify the scan name instead of all VIPs in the tnsnames.ora file.

So what is the benifit with it?

Suppose if you are adding any node to the cluster, you need to specify corresponding VIP entry for that node in the tnsnames.ora prior to 11gR2. But with SCAN name, we dont need to do any modifications to the tnsnames.ora file. SCAN name will resolve and load balance the client connections to all the nodes in the RAC.

SCAN configuration in the cluster :

The SCAN is configured during the installation of the grid infrastructure that is distributed with Oracle Database 11g Release2. So in order to configure SCAN, you need to create a single name that resolves to 3 IP addresses using a round robin algorithm. The IP addresses must be on the same subnet as your public network in the cluster.
rac-scan.example.com IN A 133.22.67.194
IN A 133.22.67.193
IN A 133.22.67.192



Hope it helps
SRI

We have 2 node 10gRAC database running on 11gR2 grid infra structure.
When the database was created, both the instances were running good.
Recently one instance was down due to some reason and we are not able to start the instance successfully.

When we are trying to start the instance , it says , it cant identify the control file. But the other instance is able to start and stop successfully.

When we start the database , we get the following error ...

ORA-00205: error in identifying control file, check alert log for more info


Then we changed the group ownership of Oracle executable to OS ASM group using below command after logging as ROOT user :

"This allows the database instance to access the ASM disks without having to make the database software owner a member of the OS ASM group."

Then re-login newly as ORACLE user and then started ORACLE Database instance.

Hope it helps
SRI

What is meant by COLD BACKUP? Is it possible to take COLD BACKUP with RMAN?

In the normal backup and recovery terms , COLD BACKUP is the backup taken when the database is completely down. Then how do we connect to database with RMAN when the database is down?
The RMAN differentiates between "inconsistent" and "consistent" backups on the basis of whether the database is OPEN or not during the Backup. It will not use the terms "COLD BACKUP" and "HOT BACKUP".

For consistance RMAN RAC backup :

Now we have the RMAN cold backup. Does it include online redo logs. How do we restore the RMAN backup taken earlier. Can we directly restore and open the database.

No.

RMAN will not backup online redologs. So when you restore the database from the backup taken above , you cant directly open the database as you dont have the online redologs. You have to open the database with reset logs.

This article is an example to backup RAC database with ASM. When the RAC with ASM Storage provides the high availability , why should we think of cold backup !!!!!!. This articles is only for concepts purpose and the real time RAC backups with RMAN  are completely different.

Hope it helps ....

--SRI

The present article discusses the tracing of DBCA. Sometimes you may be landing into issues when using the DBCA and you may not have any clue for the  error. Enabling the tracing for DBCA will give more detailed output showing some clue for the issue.

Till release 10g , we have to manually enable tracing ...

Modify the jre command at the end of the  file : $ORACLE_HOME/bin/dbca  as ...

$JRE_DIR/bin/jre -DORACLE_HOME=$OH -DJDBC_PROTOCOL=thin -mx64m -DTRACING.ENABLED=true -DTRACING.LEVEL=2 -classpath $CLASSPATH oracle.sysman.assistants.dbca.Dbca $ARGUMENTS

But from Release 10g onwards , this tracing is automatically enabled.

We can find the tracing file at

$ORACLE_HOME/cfgtoollogs/dbca/trace.log   ==> in 10g

$ORACLE_BASE/cfgtoollogs/dbca//trace.log  ==> in 11g

Hope it helps

--SRI

If you dont know how to use the exact options of the exp/imp and expdp/impdp utilities for your requirement, here is a front end tool to use

http://www.alderprogs.com/

It is a licenced software but you can install the trial version to test.

Hope it helps

--SRI

By default Quick Launch is disabled in Windows 7. This will show you how to enable or disable Quick Launch on the taskbar in Windows 7 as a toolbar with small or large icons.

Pls follow the below link to have the step by step procedure to enable quick launch tool bar in windows 7.

http://www.howtogeek.com/howto/windows-7/add-the-quick-launch-bar-to-the-taskbar-in-windows-7/

Hope it helps

SRI

Assume that we have some objects in a schema TEST1 with default tablespace TS1. Suppose if there is an application running on this database which requires these objects to be in two different schemas.
Say the second schema is TEST2 with default tablespace TS2.
Now we have to move some objects from TEST1 schema to TEST2 schema.

Consider the following syntax ..

Does export and import work ?   Lets try ....

This should actually move the table TABLE1 from TEST1 to TEST2.
But if you observe the table TABLE1 after the import it is in schema TEST2 which is ok but tablespace is still TS1. WHY ?
The user TEST2 has default tablespace TS2, so when we import the table it should be created in TS2 tablespace. Why is it created in TS1?

Then what is the solution to move an object to TEST2 with tablespace TS2. !!!!!!!

Follow the following steps to move the objects to different schema with different tablespace.

Then verify the object schema and tablespace. It should be TEST2 with TS2.

Hope it helps ....

-- SRI

This article shows the table partition management and also how to export and import the table partitions.

Hope it helps ...
--SRI

During the installation of Oracle RAC , OUI needs to copy files to and execute programs on the other nodes in the cluster. In order to allow OUI to do that, you must configure SSH to allow user equivalence. Establishing user equivalence with SSH provides a secure means of copying files and executing programs on other nodes in the cluster without requiring password prompts.

The first step is to generate public and private keys for SSH. There are two versions of the SSH protocol; version 1 uses RSA and version 2 uses DSA, so we will create both types of keys to ensure that SSH can use either version. The ssh-keygen program will generate public and private keys of either type depending upon the parameters passed to it.

From ORACLE DATABASE 11gR2, this process is automated in OUI itself. You can find the script "sshUserSetup.sh" in the 11gR2 grid media.

You can use this script for ssh setup in releases prior to 11gR2 RAC.

Note : Password only 4 times
Note : Enter password carefully

Verification :
[oracle@rac1 sshsetup]$ ssh rac1 date;hostname
Sun Aug 14 09:41:33 IST 2010
rac1.localdomain
[oracle@rac1 sshsetup]$ ssh rac2 date;hostname
Sun Aug 14 09:41:39 IST 2010
rac1.localdomain
[oracle@rac1 sshsetup]$

1. ssh both public hostname
2. ssh both private hostname
3. ssh both public ip address
4. ssh both Private ip address

Screen shots showing how this is implemented in 11gR2 RAC ....





Hope it helps ...

--SRI

11g New Background Processes:

SQL> select
2        extract( day from snap_interval) *24*60+
3        extract( hour from snap_interval) *60+
4        extract( minute from snap_interval ) "Snapshot Interval",
5        extract( day from retention) *24*60+
6        extract( hour from retention) *60+
7        extract( minute from retention ) "Retention Interval"
8  from dba_hist_wr_control;

Snapshot Interval Retention Interval
----------------- ------------------
60              10080

SQL> set linesize 100
col snap_interval format a20
col retention format a20
col topnsql format a20
select * from dba_hist_wr_control;SQL> SQL> SQL> SQL>

DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- --------------------
3438851159 +00000 01:00:00.0    +00007 00:00:00.0    DEFAULT

SQL> execute dbms_workload_repository.modify_snapshot_settings( interval => 30,retention => 43200);

PL/SQL procedure successfully completed.

NOTE : all the values are specified in minutes

SQL> select
2        extract( day from snap_interval) *24*60+
3        extract( hour from snap_interval) *60+
4        extract( minute from snap_interval ) "Snapshot Interval",
5        extract( day from retention) *24*60+
6        extract( hour from retention) *60+
7        extract( minute from retention ) "Retention Interval"
8  from dba_hist_wr_control;

Snapshot Interval Retention Interval
----------------- ------------------
30              43200

SQL> set linesize 100
col snap_interval format a20
col retention format a20
col topnsql format a20
select * from dba_hist_wr_control;SQL> SQL> SQL> SQL>

DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- --------------------
3438851159 +00000 00:30:00.0    +00030 00:00:00.0    DEFAULT

SQL>

==> SYSAUX tablespace size can be estimated using the script utlsyxsz.sql

SQL> @$ORACLE_HOME/rdbms/admin/utlsyxsz.sql

This script estimates the space required for the SYSAUX tablespace.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is utlsyxsz.txt.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: askm

Using the report name askm
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Estimated at
12:10:21 on Mar 01, 2011 ( Tuesday ) in Timezone -05:00

DB_NAME     HOST_PLATFORM                             INST STARTUP_TIME      PAR
----------- ---------------------------------------- ----- ----------------- ---
* EPPERF    atlsdbrfl01.seo.int - Linux x86 64-bit       1 09:58:44 (02/28)  NO

~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size:                         119.3 MB
|
| Total size of SM/AWR                        71.1 MB (  59.6% of SYSAUX )
| Total size of SM/OPTSTAT                    18.8 MB (  15.7% of SYSAUX )
| Total size of SM/ADVISOR                     6.4 MB (   5.3% of SYSAUX )
| Total size of LOGMNR                         6.0 MB (   5.0% of SYSAUX )
| Total size of SM/OTHER                       4.8 MB (   4.0% of SYSAUX )
| Total size of EM_MONITORING_USER             1.6 MB (   1.3% of SYSAUX )
| Total size of LOGSTDBY                       0.9 MB (   0.7% of SYSAUX )
| Total size of XSOQHIST                       0.8 MB (   0.6% of SYSAUX )
| Total size of AO                             0.8 MB (   0.6% of SYSAUX )
| Total size of STREAMS                        0.5 MB (   0.4% of SYSAUX )
| Total size of JOB_SCHEDULER                  0.4 MB (   0.3% of SYSAUX )
| Total size of TSM                            0.3 MB (   0.2% of SYSAUX )
| Total size of Others                         7.1 MB (   6.0% of SYSAUX )
|

~~~~~~~~~~~~~~~~~~~~
AWR Space Estimation
~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Automatic Workload Repository (AWR)
| in SYSAUX, we need the following values:
|
|     - Interval Setting (minutes)
|     - Retention Setting (days)
|     - Number of Instances
|     - Average Number of Active Sessions
|     - Number of Datafiles

|
| For 'Interval Setting',
|   Press <return> to use the current value:     30.0 minutes
|   otherwise enter an alternative
|
Enter value for interval:

**   Value for 'Interval Setting': 30

|
| For 'Retention Setting',
|   Press <return> to use the current value:     30.0 days
|   otherwise enter an alternative
|
Enter value for retention:

**   Value for 'Retention Setting': 30

|
| For 'Number of Instances',
|   Press <return> to use the current value:   1.00
|   otherwise enter an alternative
|
Enter value for num_instances: 1

**   Value for 'Number of Instances': 1

|
| For 'Average Number of Active Sessions',
|   Press <return> to use the current value:   0.06
|   otherwise enter an alternative
|
Enter value for active_sessions: 50

**   Value for 'Average Number of Active Sessions': 50

| ***************************************************
| Estimated size of AWR:                   3,921.8 MB
|
|   The AWR estimate was computed using
|   the following values:
|
|            Interval -        30 minutes
|           Retention -     30.00 days
|       Num Instances -         1
|     Active Sessions -     50.00
|           Datafiles -        79
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Optimizer Stat History Space Estimation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Optimizer Statistics History
| we need the following values:
|
|     - Number of Tables in the Database
|     - Number of Partitions in the Database
|     - Statistics Retention Period (days)
|     - DML Activity in the Database (level)

|
| For 'Number of Tables',
|   Press <return> to use the current value:  4,488.0
|   otherwise enter an alternative <a positive integer>
|
Enter value for number_of_tables:

**   Value for 'Number of Tables': 4488

|
| For 'Number of Partitions',
|   Press <return> to use the current value:   0.00
|   otherwise enter an alternative <a positive integer>
|
Enter value for number_of_partitions:

**   Value for 'Number of Partitions': 0

|
| For 'Statistics Retention',
|   Press <return> to use the current value:     31.0 days
|   otherwise enter an alternative <a positive integer>
|
Enter value for stats_retention:

**   Value for 'Statistics Retention': 31

|
| For 'DML Activity',
|   Press <return> to use the current value:        2 <medium>
|   otherwise enter an alternative <1=low, 2=medium, 3=high>
|
Enter value for dml_activity:

**   Value for 'DML Activity': 2

| ***************************************************
| Estimated size of Stats history            102.2 MB
|
|   The space for Optimizer Statistics history was
|   estimated using the following values:
|
|                         Tables -   4,488
|                        Indexes -   6,282
|                        Columns -  37,471
|                     Partitions -       0
|          Indexes on Partitions -       0
|          Columns in Partitions -       0
|        Stats Retention in Days -      31
|          Level of DML Activity -  Medium
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~
Estimated SYSAUX usage
~~~~~~~~~~~~~~~~~~~~~~

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of AWR:                   3,921.8 MB
|
|   The AWR estimate was computed using
|   the following values:
|
|            Interval -        30 minutes
|           Retention -     30.00 days
|       Num Instances -         1
|     Active Sessions -     50.00
|           Datafiles -        79
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history            102.2 MB
|
|   The space for Optimizer Statistics history was
|   estimated using the following values:
|
|                         Tables -   4,488
|                        Indexes -   6,282
|                        Columns -  37,471
|                     Partitions -       0
|          Indexes on Partitions -       0
|          Columns in Partitions -       0
|        Stats Retention in Days -      31
|          Level of DML Activity -  Medium
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|   For all the other components, the estimate
|   is equal to the current space usage of
|   the component.
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
| ***************************************************
| Summary of SYSAUX Space Estimation
| ***************************************************

| Est size of SM/ADVISOR                       6.4 MB
| Est size of LOGMNR                           6.0 MB
| Est size of SM/OTHER                         4.8 MB
| Est size of EM_MONITORING_USER               1.6 MB
| Est size of LOGSTDBY                         0.9 MB
| Est size of XSOQHIST                         0.8 MB
| Est size of AO                               0.8 MB
| Est size of STREAMS                          0.5 MB
| Est size of JOB_SCHEDULER                    0.4 MB
| Est size of TSM                              0.3 MB
| Est size of Others                           7.1 MB

| Est size of SM/AWR                       3,921.8 MB
| Est size of SM/OPTSTAT                     102.2 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size:             4,053.4 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************

End of Report
SQL>

NOTE :

STATSPACK has many more settings that can be configured.  These are kept in the stats$statspack_parameter table. This table stores a single row for the database parameters with the corresponding STATSPACK settings.  These settings influence the amount of information STATSPACK gathers from the v$ views.

Hope It helps

SRI

How do you check the User failed login attempts without auditing the user?

A user created with default profile and the profile is modified to have the resource name FAILED_LOGIN_ATTEMPTS set to 10. Now the user account will lock when the attempts to connect to the user is more than 10.

How do we verify that there are so many number of failed login attempts earlier.
Is auditing the user the only solution ?
No

You can find the FAILED_LOGIN_ATTEMPTS value from the lcount column of user$ table.

Conclusions :
============
In order to check if the FAILED_LOGIN_ATTEMPTS is working or not , we need to try giving wrong passwords 10 times continuously without any successful attempts. Then 11th attempt will lock the user account. But if there is atleast one single successful attempt to connect to the user , the value resets to "0" again.

Purging LOBs:

If we purge a table which contains LOBs , we may not reclaim space. BLOBs are stored out of line once they exceed 4,000 bytes (you can also specify that they are always stored out of line as well).

If the BLOBs were all inline, each taking 4000 or less bytes, then you did free space in your table.  The table will not shrink in size (tables NEVER shrink) but it will have more blocks on its free list for subsequent inserts.