Oracle Troubleshoot

Initial Load - Instantiation - Oracle Golden Gate using Datapump

2014-04-17T15:38:00.000+05:30

Full database export using expdp

Enable Minimal Supplemental Logging in Oracle on source

alter database add supplemental log data ;

Enable Supplemental Logging at Table Level on source

GGSCI> dblogin userid xxxxx password xxxxxx

GGSCI> add trandata <schema>.<tablename>

Add Extract, Add Exttrail, Add Pump, Add Rmttrail on source

Start Extract, Start Pump on source

Create a database directory:

SQLPLUS> create directory dumpdir as '<some directory>' ;

Get the current SCN on the source database:

SQLPLUS> select current_scn from v$database ;

28318029

Run the Data Pump Export using the flashback SCN you obtained in the previous step. The following example shows running the expdp utility at a Degree Of Parallelism (DOP) of 4. If you have sufficient system resources (CPU, memory and IO) then running at a higher DOP will decrease the amount of time it takes to take the export (up to 4x for a DOP of 4). Note that expdp uses Oracle Database parallel execution settings (e.g. parallel_max_servers) which have to be set appropriately in order to take advantage of parallelism. Other processes running in parallel may be competing for those resources. See the Oracle Documentation for more details.

>expdp directory=dumpdir full=y parallel=4 dumpfile=ora102_%u.dmp flashback_scn=28318029

>Username: system NOTE: any DB user with DBA privileges will do >Password:

Note: The export log needs to be checked for errors.

Start an import using impdp to the target database when the export step is complete.

Add and Start Replicat after import is complete:

GGSCI> add replicat <repname>, exttrail ./dirdat/<xx>

GGSCI> start replicat <repname>, aftercsn <value returned from step 6>

Example 2: schema export using exp

Enable Minimal Supplemental Logging in Oracle on source

SQLPLUS > alter database add supplemental log data;

Enable Supplemental Logging at Table Level on source

GGSCI> dblogin userid xxxxx password xxxxxx

GGSCI> add trandata <schema>.<tablename>

Add Extract, Add ExtTrail FileTrail File, Add Pump, Add RmtTrail FileTrail File on source

Start Extract, Start Pump on source

Get the current SCN on the source database:

SQLPLUS> select current_scn from v$database ;

CURRENT_SCN

-----------------------

28318029

Get a flashback snapshot from the SCN that you obtained in the previous step. You can do this with exp or expdp utility. The following example shows the use of export utility (exp):

>exp <username>/<password> owner=<schema_name> grants=n statistics=none triggers=n compress=n FLASHBACK_SCN=28318029

Note: Undo_retention has to be set high enough, and the export log needs to be checked for errors. You can speed up exp by running multiple session in parallel but you have to manually configure subsets of the data you want to export (e.g. different tables for different export files). Make sure to use the same FLASHBACK_SCN for all export sessions to ensure taking a read consistent backup.

Start an import to the target database when export is complete.

Add and Start Replicat:

GGSCI> add replicat <rename>, exttrail ./dirdat/<xx>

GGSCI> start replicat <rename>, aftercsn <SCN value returned from query on source>

Add tables to an Existing GoldenGate Configuration - Oracle Golden Gate

2014-04-17T15:24:00.000+05:30

1.     Log into the source database via GGSCI

[oracle@blue ggate]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.13 17435036 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131002.1206_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 3 2013 02:39:46

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

GGSCI (blue.color) 2> dblogin userid ggate, password ggate
Successfully logged into database.

2.     Add trandata for the new table(s).

GGSCI (blue.color) 3> add trandata song.stone

2013-12-05 15:17:07 WARNING OGG-00869 No unique key is defined for table 'STONE'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table SONG.STONE.

GGSCI (blue.color) 4> add trandata song.greystreet

2013-12-05 15:17:15 WARNING OGG-00869 No unique key is defined for table 'GREYSTREET'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table SONG.GREYSTREET.

3.     Add the two tables in the Extract Parameter file

GGSCI (blue.color) 5> edit params e_gg
...
TABLE song.stone;
TABLE song.greystreet;
...

4.     Restart the Extract e_gg

GGSCI (blue.color) 2> stop e_gg

Sending STOP request to EXTRACT E_GG ...
Request processed.

GGSCI (blue.color) 3> start e_gg

Sending START request to MANAGER ...
EXTRACT E_GG starting

5.     At this point, capture the Current SCN from the database. Do not lose this number, we need it later!

[oracle@blue ggate]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 5 15:28:31 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1113396

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

6.     Take a Data Pump export of the two tables. Please note the use of FLASHBACK_SCN.

[oracle@blue ggate]$ expdp system/oracle directory=data_pump_dir dumpfile=additional_tables.dmp flashback_scn=1113396 tables=song.stone,song.greystreet

Export: Release 11.2.0.3.0 - Production on Thu Dec 5 15:32:04 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=data_pump_dir dumpfile=additional_tables.dmp flashback_scn=1113396 tables=song.stone,song.greystreet
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "SONG"."GREYSTREET"                         5.882 KB     100 rows
. . exported "SONG"."STONE"                              5.679 KB     100 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/sky/dpdump/additional_tables.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 15:32:14

7.     Run the import on the Target database.

[oracle@blue ggate]$ impdp system/oracle directory=data_pump_dir dumpfile=additional_tables.dmp remap_schema=song:river

Import: Release 11.2.0.3.0 - Production on Thu Dec 5 15:45:29 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=data_pump_dir dumpfile=additional_tables.dmp remap_schema=song:river
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "RIVER"."GREYSTREET"                        5.882 KB     100 rows
. . imported "RIVER"."STONE"                             5.679 KB     100 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 15:45:31

8.     Here’s the important step, you need to edit the replicat’s parameter file and include the following string in the map statement.

GGSCI (blue.color) 2> edit params r_gg
...
MAP song.stone, TARGET river.stone , FILTER ( @GETENV("TRANSACTION", "CSN") > 1113396);
MAP song.greystreet, TARGET river.greystreet, FILTER ( @GETENV("TRANSACTION", "CSN") > 1113396);
...

9.     Recycle the replicat.

GGSCI (blue.color) 4> stop r_gg

Sending STOP request to REPLICAT R_GG ...
start Request processed.

GGSCI (blue.color) 5> start r_gg

Sending START request to MANAGER ...
REPLICAT R_GG starting

GGSCI (blue.color) 6> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     E_GG        00:00:00      00:00:09
REPLICAT    RUNNING     R_GG        00:00:00      00:00:03

10. Check the counts on both systems.

10.1. On the Source

SQL> select name from v$database;

NAME
---------
SKY

SQL> select count(1) from song.stone;

COUNT(1)
----------
       100

10.2. On the Target

SQL> select name from v$database;

NAME
---------
WATER

SQL> select count(1) from river.stone;

COUNT(1)
----------
       100

10.3. Insert data on the source.

SQL> INSERT INTO song.stone
SELECT dbms_random.string('A', 2) RNDMSTR
FROM all_objects
WHERE ROWNUM < 101; 2    3    4

100 rows created.

SQL> commit;

Commit complete.

SQL> select count(1) from song.stone;

COUNT(1)
----------
       200

10.4. Check the counts on the target table.

SQL> select count(1) from river.stone;

COUNT(1)
----------
       200

10.5.  Check lag on the replicat.

GGSCI (blue.color) 1> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     E_GG        00:00:00      00:00:02
REPLICAT    RUNNING     R_GG        00:00:00      00:00:08

11.     Once the table is caught up, remove the FILTER parameters from the replicat.

GGSCI (blue.color) 2> edit params r_gg
...
MAP song.stone, TARGET river.stone;
MAP song.greystreet, TARGET river.greystreet;
...

12.     Restart the replicat and we’re done!

GGSCI (blue.color) 4> stop r_gg

Sending STOP request to REPLICAT R_GG ...
start Request processed.

GGSCI (blue.color) 5> start r_gg

Sending START request to MANAGER ...
REPLICAT R_GG starting

GGSCI (blue.color) 6> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     E_GG        00:00:00      00:00:06
REPLICAT    RUNNING     R_GG        00:00:00      00:00:02

Resynchronizing an out-of-sync table - Oracle Golden Gate

2014-04-17T15:16:00.002+05:30

In this procedure, there are two Replicat groups:

● The original group, known as groupA

● A new group that you will create, known as groupB

To resynchronize a table

1.Comment out the out-of-sync table in the original Replicat parameter file.

2.Stop the original Replicat and start it again so it continues processing the tables that are not out-of-sync.

STOP REPLICAT <groupA>

START REPLICAT <groupA>

3.Write down the timestamp on the source system.

4.Try to resolve any long-running transactions on the source out-of-sync table. You will be copying the source data to the target, and if transactions are open while the copy is being made, they will not be captured.

5. Start a copy of the source data.

6.Import the copy to the target table.

7.Create a new Replicat group for the out-of-sync table, using BEGIN to start at the source timestamp that you recorded step 3, and using the existing trail for EXTTRAIL.

ADD REPLICAT <groupB>, EXTTRAIL <existing trail>,BEGIN <yyyy-mm-dd hh:mm:ss>

8. Create a parameter file for the new group and include HANDLECOLLISIONS.

EDIT PARAMS <groupB>

9.Start the new Replicat group. START REPLICAT <groupB>

10.View the lag of the new group until it shows “At EOF, no more records to process.”

SEND REPLICAT <groupB>, GETLAG

11.Stop the new Replicat group.STOP REPLICAT <groupB>

12.Edit the parameter file of the new group to comment out or remove HANDLECOLLISIONS.

13.Start the new Replicat group.

START REPLICAT <groupB>

The next steps merge the table back with the others so that only one Replicat group is needed again.

14.Stop Extract. STOP EXTRACT <extract_group>

15.View the lag of each Replic at group until it shows “At EOF, no more records to process.”

SEND REPLICAT <groupA>, GETLAG

SEND REPLICAT <groupB>, GETLAG

16.Stop both Replicat groups.

STOP REPLICAT <groupA>

STOP REPLICAT <groupB>

17.Uncomment the newly synchronized table in the original Replicat parameter file.

18. Start Extract.

START EXTRACT <extract_group>

19.Start the original Replicat.

START REPLICAT <groupA>

20.Delete the new Replicat that you created.

DELETE REPLICAT <groupB>

Note : we can use find current_scan from source database and take export using the flashback parameter and copy the tables to target, while starting dummy replicat group we use start replicat after csn.

RECOVERING FROM A LOST DATAFILE IN A UNDO TABLESPACE

2013-09-28T00:14:00.000+05:30

The approach to be followed depends on the specific scenario in which
the loss of the rollback datafile is detected:

I. THE DATABASE IS DOWN
-----------------------

Attempting to startup the database will result in ORA-1157 and ORA-1110.
The solution here depends on whether the database was cleanly shut down
or not.

I.A. THE DATABASE WAS CLEANLY SHUT DOWN
---------------------------------------

If you are ABSOLUTELY POSITIVE that the database was cleanly shutdown,
i.e., it was closed with either shutdown NORMAL or IMMEDIATE, then
the simplest solution is to offline drop the missing datafile, open the
database in restricted mode, and then drop and recreate the undo
tablespace to which the file belonged. DO NOT follow this procedure
if the database was shut down ABORT or if it crashed.

The steps are:

1. Make sure the database was last cleanly shut down.

Check the alert.log file for this instance. Go to the bottom of
the file and make sure the last time you shut the database down
you got the messages:

"Shutting down instance (immediate)"

"alter database close normal
Completed: alter database close normal"

This also includes the case of a clean shutdown followed by a
failed attempt to startup the database. In that case, Oracle will
issue error messages and shut itself down abort. For the purposes
of this solution, though, this counts as a clean shutdown.

If that is not the case, i.e., if the last time YOU shut the database
down it was in abort mode, or the database crashed itself, it is
NOT safe to proceed. You should follow the instructions for
case I.B below.

2. If using automatic UNDO_MANAGEMENT, comment out this entry from the parameter
file, or set it to MANUAL.

If using rollback segments, remove all the rollback segments in the
tablespace to which the lost datafile belongs from the ROLLBACK_SEGMENTS
parameter in the init.ora file for this instance. If you are not sure about which rollbacks are
in that tablespace, simply comment out the whole ROLLBACK_SEGMENTS entry.

3. Mount the database in restricted mode.

SQL> STARTUP RESTRICT MOUNT

4. Offline drop the lost datafile.

SQL> ALTER DATABASE DATAFILE '<full_path_file_name>' OFFLINE DROP;

5. Open the database.

SQL> ALTER DATABASE OPEN

You should receive the message "Statement processed,".

If instead you get ORA-604, ORA-376, and ORA-1110, it is likely the shutdown
was not normal/immediate. Review the rest of the options available and/or
contact Oracle Support Services.

6. Drop the undo tablespace or tablespace which contains rollback segments
to which the datafile belonged.

SQL> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;

7. Recreate the undo tablespace. If using rollback segments, recreate the
rollback segment tablespace and all it's rollback segments. Remember to
bring the rollbacks online after you create them.

8. Edit the parameter file setting:
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=<new undo tablespace name>

If using rollback segments, reinclude the rollbacks you just recreated in
the ROLLBACK_SEGMENTS parameter in the init.ora file for this instance.
As rollback segments were brought online in step #7, no need to proceed
with shutdown/startup as needed for undo tablespace. All that is required
is:

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

9. If using UNDO tablespace, Shutdown and startup the the database.

I.B. THE DATABASE WAS NOT CLEANLY SHUT DOWN
-------------------------------------------

This is the situation where the database was last shut down abort or crashed.
In this case, it is almost certain that the rollback segments that had
extents in the lost datafile still contain active transactions. Therefore,
the file cannot be offlined or dropped. You must restore the lost datafile
from a backup and apply media recovery to it. If the database is in
NOARCHIVELOG mode, you will only succeed in recovering the datafile if the
redo to be applied is within the range of your online logs. Otherwise, the
last full backup must be restored. If a backup of the datafile is not
available, please contact Oracle Support Services.

These are the steps:

1. Restore the lost file from a backup.

2. Mount the database:

SQL> STARTUP MOUNT;

3. Issue the following query:

SQL> SELECT FILE#, NAME, STATUS FROM V$DATAFILE;

If the status of the file you just restored is "OFFLINE," you must
online it before proceeding:

SQL> ALTER DATABASE DATAFILE '<full_path_file_name>' ONLINE;

4. Issue the following query:

SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# ;

This will list all your online redolog files and their respective
sequence and first change numbers.

5. If the database is in NOARCHIVELOG mode, issue the query:

SQL> SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;

If the CHANGE# is GREATER than the minimum FIRST_CHANGE# of your
logs, the datafile can be recovered. Just keep in mind that all the
logs to be applied will be online logs, and move on to step 6.

If the CHANGE# is LESSER than the minimum FIRST_CHANGE# of your logs,
the file cannot be recovered. Your options at this point include
restoring a full backup if one is available or forcing the database
to open in an inconsistent state to get a full export out of it.
For further details and to assist you in your decision, please contact
Oracle Support Services.

6. Recover the datafile:

SQL> RECOVER DATAFILE '<full_path_file_name>'

7. Confirm each of the logs that you are prompted for until you receive
the message "Media recovery complete". If you are prompted for a
non-existing archived log, Oracle probably needs one or more of the
online logs to proceed with the recovery. Compare the sequence number
referenced in the ORA-280 message with the sequence numbers of your online
logs. Then enter the full path name of one of the members of the redo group
whose sequence number matches the one you are being asked for. Keep entering
online logs as requested until you receive the message "Media recovery
complete".

8. Open the database:

SQL> ALTER DATABASE OPEN;

II. THE DATABASE IS UP
----------------------

If you have detected the loss of the rollback datafile and the database
is still up and running, DO NOT SHUT IT DOWN. In most cases, it is
simpler to solve this problem with the database up than with it down.

Two approaches are possible in this scenario:

A) The first approach involves creating a new UNDO (or rollback segment)
tablespace, altering the system to use the new and dropping the old.

B) The other approach involves offlining the lost datafile, restoring it from
backup, and then applying media recovery to it to make it consistent
with the rest of the database. This method can only be used if
the database is in ARCHIVELOG mode.

In general, approach IIA is simpler to try first, however, depending on
activity and undo usage, the drop of the older tablespace may result
in error regarding active transactions. There are a few things to try to
get the older tablespace to drop, but if all fail, approach IIB may be required.

APPROACH II.A: RECREATING THE ROLLBACK TABLESPACE
-------------------------------------------------

This approach can be used regardless of the archival mode of the database.
The steps are:

For undo tablespaces:
.....................

1. Create a new undo tablespace:

SQL> CREATE UNDO TABLESPACE <new undo tablepace name>
DATAFILE '<new location and name of datafile>' SIZE ....;

2. Alter the system to use the new undo tablespace:

SQL> ALTER SYSTEM SET UNDO_TABLESPACE='<new undo tablepace name>';

At this point, all new undo will be allocated in undo segments managed within
the UNDO_TBS2 tablespace.

3. Try dropping older undo tablespace:

SQL> DROP TABLESPACE <old undo tablespace name> INCLUDING CONTENTS;

If this succeeds, you are done. If it returns an error, please see
"Handling errors dropping undo/rollback segments".

For rollback segment tablespaces:
.................................

1. Create new tablespace to house rollback segments. For spacing and extent
sizing, see information in dba_tablespaces for old rollback segment tablespace.

SQL> CREATE TABLESPACE .....

2. Create rollback segments in this tablespace

SQL> CREATE ROLLBACK SEGMENT.....

3. Online the rollback segments in this new tablespace:

SQL> ALTER ROLLBACK SEGMENT <new_rollback_segment> ONLINE;

Repeat for all rollback segments.

4. Try to offline all the rollback segments in the tablespace to which
the lost datafile belongs.

SQL> ALTER ROLLBACK SEGMENT <rollback_segment> OFFLINE;

Repeat this statement for all rollbacks in the tablespace.

5. Check the status of the rollbacks.

They must all be offline before they can be dropped. Issue the query:

SQL> SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = '<TABLESPACE_NAME>';

6. Drop all offlined rollback segments.

For each rollback returned by the query in step 2 with status
"OFFLINE," issue the statement:

SQL> DROP ROLLBACK SEGMENT <rollback_segment>;

7. Drop the rollback segment tablespace:

SQL> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;

If this succeeds, you are done. If it returns an error, please see
"Handling errors dropping undo/rollback segments".

HANDLING ERRORS DROPPING UNDO/ROLLBACK SEGMENTS:
-----------------------------------------------

NOTE, although automatic undo and undo tablespace is used by the database,
the following procedure is the same. The only difference is that the segment
names are assigned by Oracle.

1. Check for any rollback segments online.

SQL> SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = '<OLD TABLESPACE_NAME>';

If any of the rollbacks you tried to offline still has an "ONLINE" status,
this is usually an indication that this segment contains active transactions.

2. Check for active transactions with the following query:

SQL> SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS
FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = '<TABLESPACE_NAME>' AND SEGMENT_ID = USN;

If the above query returns no rows, it means all the rollbacks in
the affected tablespace are already offline. Repeat the query in
step 1 to retrieve the names of the rollbacks that just became
offline and attempt to drop the undo tablespace or individual
rollback segments as described above.

If the above query returns one or more rows, they should show
status "PENDING OFFLINE". Next, check the ACTIVE_TX
column for each rollback. If it has a value of 0, it implies
there are no pending transactions left in the rollback, and it
should go offline shortly. Repeat the query in step 1 a few
more times until it shows the rollback segments being offline and
then attempt the drop again. Once the drop is successful, you are done.

If any of the "pending offline" rollbacks has a value of 1 or
greater in the ACTIVE_TX column, move on to step 3.

3. Force rollbacks with active transactions to go offline.

At this point, the only way to move forward is to have the
"pending offline" rollbacks released. The active transactions
in these rollbacks must either be committed or rolled back.
The following query shows which users have transactions assigned
to which rollbacks:

SQL> SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME "ROLLBACK"
FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R
WHERE R.NAME IN ('<PENDING_ROLLBACK_1>', ... , '<PENDING_ROLLBACK_N>')
AND S.TADDR = T.ADDR AND T.XIDUSN = R.USN;

You may directly contact the users with transactions in the
"pending offline" rollbacks and ask them to commit (preferably)
or rollback immediately. If that is not feasible, you can force
that to happen by killing their sessions. For each of the entries
returned by the above query, issue the statement:

SQL> ALTER SYSTEM KILL SESSION '<SID>, <SERIAL#>';

where <SID> and <SERIAL#> are those returned by the previous
query. After the sessions are killed, it may take a few minutes
before Oracle finishes rolling back and doing cleanup work. Go back
to step 1 and repeat the query in there periodically until all
rollbacks in the affected tablespace are offline and ready to be
dropped.

If you are unable to drop the older undo tablespace or rollback segments,
try to restore the file from backup and recover it fully (approach II.B).
Once the file is recovered try to drop the older undo tablespace. If this
is not possible, contact Oracle Support Services.

APPROACH II.B: RESTORING THE DATAFILE FROM BACKUP
-------------------------------------------------

As mentioned before, this approach can only be followed if the database is
in ARCHIVELOG mode. Here are the steps:

1. Offline the lost datafile.

SQL> ALTER DATABASE DATAFILE '<full_path_file_name>' OFFLINE;

NOTE: Depending on the current amount of database activity,
you may have to create additional rollback segments in a different
tablespace to keep the database going while you take care of the problem.

2. Restore the datafile from a backup.

3. Issue the following query:

SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# ;

This will list all your online redolog files and their respective
sequence numbers.

4. Recover the datafile:

SQL> RECOVER DATAFILE '<full_path_file_name>'

5. Confirm each of the logs that you are prompted for until you receive
the message "Media recovery complete". If you are prompted for a
non-existing archived log, Oracle probably needs one or more of the
online logs to proceed with the recovery. Compare the sequence number
referenced in the ORA-280 message with the sequence numbers of your online
logs. Then enter the full path name of one of the members of the redo group
whose sequence number matches the one you are being asked for. Keep
entering online logs as requested until you receive the message
"Media recovery complete".

6. Bring the datafile back online.

SQL> ALTER DATABASE DATAFILE '<full_path_file_name>' ONLINE

Dataguard Issues - How to resolve ORA-16792

2013-09-27T23:30:00.002+05:30

If the setting of dataguard related parameter is different between database and the broker, when doing the show configuration within DGMGRL, the following error will appear:

DGMGRL> show configuration;

Configuration
Name: DGA24L
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
DGA24L - Primary database
DGA24LPS - Physical standby database

Current status for "DGA24L":
Warning: ORA-16608: one or more databases have warnings

DGMGRL> show database verbose 'DGA24L';

Database
Name: DGA24L
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
DGA24L

Properties:
...

o find out what is the differences, by using this command:
DGMGRL> show database '<db_name>' 'InconsistentProperties';



DGMGRL> show database 'DGA24L' 'InconsistentProperties';

INCONSISTENT PROPERTIES

   INSTANCE_NAME  PROPERTY_NAME    MEMORY_VALUE   SPFILE_VALUE    BROKER_VALUE 

          DGA24L  LogArchiveTrace             1            513             513

For example, the inconsistent is this parameter log_archive_trace
Either change the value using sqlplus or DGMGRL to make it consistent.

1. sqlplus / as sysdba

   SQL> alter system set log_archive_trace=513;

2. DGMGRL

   DGMGRL> edit database '<db_name>' set property 'LogArchiveTrace'=1;

How to quickly check that Database is consistent after incomplete recovery (Point in Time Recovery) before OPEN RESETLOGS

2013-09-25T23:55:00.000+05:30

At first, It's helpful to enable the session to display the timestamp for DATE type columns :

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS' ;

Check 1:

Objective: Verify that the datafiles are recovered to the intended point in time (PIT) and they are consistent (FUZZY=NO)
Query the current status and PIT (P-oint I-n T-ime upto which the datafiles have been recovered) of datafiles by reading datafile headers directly from the physical datafiles:

SQL> select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ;

FUZ STATUS ERROR           REC CHECKPOINT_CHANGE# CHECKPOINT_TIME        COUNT(*)
--- ------- --------------- --- ------------------ -------------------- ----------
NO ONLINE                                 5311260 31-AUG-2011 23:10:14          6
YES ONLINE                                 5311260 31-AUG-2011 23:10:14          1

+ Verify that the checkpoint_time / checkpoint_change# is in line with your intended UNTIL TIME/SCN. If not, recover the database further if you have more archived logs available.

+ If FUZZY=YES for some datafiles, it means more recovery is required. If no more archived logs are available, identify such datafiles and determine if we can take them offline because we will loose the data in those datafiles. If the datafiles belong to SYSTEM or UNDO tablespace, we can / MUST not bring such datafile offline without proper analysis. Please consult Oracle Support for further actions.

SQL> select file#, substr(name, 1, 50), substr(tablespace_name, 1, 15), undo_opt_current_change# from v$datafile_header where fuzzy='YES' ;

FILE# SUBSTR(NAME,1,50) SUBSTR(TABLESPA UNDO_OPT_CURRENT_CHANGE#
---------- -------------------------------------------------- --------------- ------------------------
3 /u01/app/oracle/oradata/prod111/undotbs01.dbf UNDOTBS1 5117431

Occasionally, if the tablespace name doesn't indicate it to be UNDO tablespace, if we see non-zero value in column UNDO_OPT_CURRENT_CHANGE#, it indicates that the datafile contains undo segments.

To bring a datafile offline :

SQL> alter database datafile <file#> offline ;

Check 1 can be considered Passed when :

+ Verified that all the datafiles have been recovered upto the intended Point in time.

+ Fuzzy=NO for SYSTEM, UNDO and all intended datafiles. For datafiles with Fuzzy=YES, either recover them further or bring them OFFLINE if no further archived logs available.

Check 2:

Objective: Verify that the files with status=RECOVER are not OFFLINE unintentionally

SQL> select status, enabled, count(*) from v$datafile group by status, enabled ;

STATUS ENABLED      COUNT(*)
------- ---------- ----------
SYSTEM DISABLED            1
ONLINE READ WRITE          4
RECOVER DISABLED            2

If the files are in RECOVER status, verify if they are OFFLINE :

SQL> select file#, substr(name, 1, 50), status, error, recover from v$datafile_header ;

If you want the data for these files to be accessible, then bring them ONLINE :

SQL> alter database datafile <file#> ONLINE ;

If a file remains offline at the time of OPEN RESETLOGS, the datafile may not be brought back online again in the same OPENED database.

Check 2 can be considered Passed when:

a) All the intended datafiles are not OFFLINE

Check 3:

Objective: Additional Fuzzy check (Absolute Fuzzy check)

Occasionally, it is possible to see Fuzzy=NO and same checkpoint_change# for all the intended datafiles ; still some of the datafiles might be fuzzy and OPEN RESETLOGS will return error, e.g.

SQL> select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ;

FUZ STATUS ERROR REC CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)
--- ------- --------------- --- ------------------ -------------------- ----------
NO ONLINE 5311260 31-AUG-2011 23:10:14 7

SQL> ALTER DATABASE OPEN RESETLOGS ;

ORA-01194: file 4 needs more recovery to be consistent
ORA-01110: data file 3: '/u01/app/oracle/oradata/prod111/undotbs02.dbf'

Hence, we should perform additional fuzzy check known as Absolute Fuzzy Check:

SQL> select hxfil file#, substr(hxfnm, 1, 50) name, fhscn checkpoint_change#, fhafs Absolute_Fuzzy_SCN, max(fhafs) over () Min_PIT_SCN from x$kcvfh where fhafs!=0 ;

FILE#      NAME                                               CHECKPOINT_CHANG ABSOLUTE_FUZZY_S     MIN_PIT_SCN
---------- -------------------------------------------------- ---------------- ---------------- ----------------
         4 /u01/app/oracle/oradata/prod111/undotbs01.dbf               5311260          5311524          5311524
         6 /u01/app/oracle/oradata/prod111/system01.dbf                5311260          5311379          5311524

Note: Column Min_PIT_SCN will return same value even for multiple rows as we have applied ANALYTICAL "MAX() OVER ()" function on it.

Above query indicates that the recovery must be performed at least UNTIL SCN 5311524 to make datafiles consistent and ready to OPEN. Since the checkpoint_change# is smaller than Min_PIT_SCN, the datafiles will ask for more recovery.

Check 3 can be considered Passed when,

a) No rows selected from above query (i.e. Min_PIT_SCN is 0 (Zero) for all the datafiles)

b) Min_PIT_SCN is returned less than Checkpoint_Change#

Check 4 (After successful OPEN RESETLOGS) :

Monitor the alert.log for the time of OPEN RESETLOGS activities. You might see some messages like below during dictionary check:

Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary, <(============================== (1)
but not in the controlfile. Adding to controlfile.
Tablespace 'USERS' #4 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #4 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00004' in the controlfile. <(==================== (2)
File #5 is online, but is part of an offline tablespace. <(==================== (3)
data file 5: '/u01/app/oracle/oradata/prod111/example01.dbf'
File #7 found in data dictionary but not in controlfile. <(==================== (2)
Creating OFFLINE file 'MISSING00007' in the controlfile.
File #8 is offline, but is part of an online tablespace. <(==================== (4)
data file 8: '/u01/app/oracle/oradata/prod111/mydata02.dbf'
File #9 is online, but is part of an offline tablespace. <(==================== (3)
data file 9: '/u01/app/oracle/oradata/prod111/example02.dbf'
Dictionary check complete

We discuss below the points highlighted :

(1) Check if the temp files exist. If not, add them as per your preference:

SQL> select file#, name from v$tempfile ;

no rows selected

SQL> select file#, name from dba_temp_files ;

no rows selected

SQL> select tablespace_name, status, contents from dba_tablespaces where contents='TEMPORARY' ;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
TEMP                           ONLINE    TEMPORARY

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/temp01.dbf' size 10m ;

Tablespace altered.

SQL> select file#, substr(name, 1, 50), status, enabled from v$tempfile

FILE#    SUBSTR(NAME,1,50)                                  STATUS ENABLED
-------- -------------------------------------------------- ------- ----------
       1 /u01/app/oracle/oradata/temp01.dbf                 ONLINE  READ WRITE

(2) It appears that the tablespace was brought offline using "ALTER TABLESPACE USERS OFFLINE" command. So, verify if the missing files really exist with original name. You may need to consult your pear DBAs, or refer alert.log / RMAN backup log or any such information which may provide clue about the actual file name.

If you find the file, try to rename them. If not, we can offline the datafile or drop associated tablespace:

SQL> select file#, status, enabled, substr(name, 1, 50) from v$datafile where name like '%MISSING%' ;

FILE#    STATUS ENABLED    SUBSTR(NAME,1,50)
-------- ------- ---------- --------------------------------------------------
       4 OFFLINE DISABLED   /u01/app/oracle/product/11.1.0/db_1/dbs/MISSING000
       7 OFFLINE DISABLED   /u01/app/oracle/product/11.1.0/db_1/dbs/MISSING000

SQL> alter database datafile 4 online ;
alter database datafile 4 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01111: name for data file 4 is unknown - rename to correct file
ORA-01110: data file 4: '/u01/app/oracle/product/11.1.0/db_1/dbs/MISSING00004'

SQL> alter database rename file 'MISSING00004' to '/u01/app/oracle/oradata/prod111/users01.dbf' ;

Database altered.

SQL> alter database rename file 'MISSING00007' to '/u01/app/oracle/oradata/prod111/users02.dbf' ;

Database altered.

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name in (select tablespace_name from dba_data_files where file_id in (4, 7)) ;

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          OFFLINE

SQL> ALTER TABLESPACE USERS ONLINE ;

Tablespace altered.

Before proceedig further, let's query the status for these files in alert.log:

SQL> select a.file#, substr(a.name, 1, 50) file_name, a.status file_status, a.error, substr(a.tablespace_name, 1, 10) tablespace_name, b.status tablespace_status from v$datafile_header a, dba_tablespaces b
where a.tablespace_name=b.tablespace_name /* and a.file# in (4, 5, 7, 8, 9) */ ;
FILE# FILE_NAME                                     FILE_STATUS ERROR           TABLESPA TABLESPACE_STATUS
----- --------------------------------------------- ----------- --------------- -------- ------------------
    1 /u01/app/oracle/oradata/prod111/system01.dbf ONLINE                      SYSTEM   ONLINE
    2 /u01/app/oracle/oradata/prod111/sysaux01.dbf ONLINE                      SYSAUX   ONLINE
    3 /u01/app/oracle/oradata/prod111/undotbs01.dbf ONLINE                      UNDOTBS1 ONLINE
    4 /u01/app/oracle/oradata/prod111/users01.dbf   OFFLINE     OFFLINE NORMAL USERS    OFFLINE <(== related to (2) in alert.log excerpt above
    5 /u01/app/oracle/oradata/prod111/example01.dbf ONLINE                      EXAMPLE OFFLINE <(== related to (3) in alert.log excerpt above
    6 /u01/app/oracle/oradata/prod111/mydata01.dbf ONLINE                      MYDATA   ONLINE
    7 /u01/app/oracle/oradata/prod111/users02.dbf   OFFLINE     OFFLINE NORMAL USERS    OFFLINE <(== related to (2) in alert.log excerpt above
    8 /u01/app/oracle/oradata/prod111/mydata02.dbf OFFLINE     WRONG RESETLOGS MYDATA   ONLINE <(=== related to (4) in alert.log excerpt above
    9 /u01/app/oracle/oradata/prod111/example02.dbf ONLINE                      EXAMPLE OFFLINE <(== related to (3) in alert.log excerpt above
9 rows selected.

So, we can attempt to correct the "ERROR" as displayed in above query depending on the availability of file / archived logs and other possible factors.

Let's continue,
(3) It seems that tablespace was brought offline inconsistently ( ALTER TABLESPACE EXAMPLE OFFLINE IMMEDIATE ). If the archived log generated at that time has got applied, the file may be back online :

SQL> alter tablespace example ONLINE ;

Tablespace altered.

(4) This tablespace MYDATA has 2 datafiles File# 6 & 8. It appears that File# 8 was brought offline ( using ALTER DATABASE DATAFILE 8 OFFLINE ) and it was OFFLINE before OPEN RESETLOGS. If the archived log generated at that time has got applied during recovery or all the archived logs are available for recovery since that time, the file may be back online :

SQL> alter database datafile 8 online ;
alter database datafile 8 online
*
ERROR at line 1:
ORA-01190: control file or data file 8 is from before the last RESETLOGS
ORA-01110: data file 8: '/u01/app/oracle/oradata/prod111/mydata02.dbf'

SQL> alter tablespace mydata online ;
alter tablespace mydata online
*
ERROR at line 1:
ORA-01190: control file or data file 8 is from before the last RESETLOGS
ORA-01110: data file 8: '/u01/app/oracle/oradata/prod111/mydata02.dbf'

SQL> recover datafile 8 ;
Media recovery complete.
SQL> alter database datafile 8 online ;

Database altered.

SQL> alter tablespace mydata online ;

Tablespace altered.

Please note that it is not always possible to recover and bring the file online which is failing with error " ORA-01190: control file or data file x is from before the last RESETLOGS".

LEAST amount of recovery that has to be done before a database restored

2013-08-18T17:09:00.002+05:30

What is the LEAST amount of recovery that has to be done before a database restored from a hot rman backup can be opened?

A:

ALL archivelogs from backup start to backup end must be applied before the database can be opened - this is the MINIMUM amount of recovery needed.

To determine which log was current at the time the backup completed, note the COMPLETION time of the rman database backup - take this from the backup log.

If the backup log is not longer available query the rman metadata but make sure you set NLS_DATE_FORMAT before invoking rman so that timestamps as well as date are returned:

- for unix:     %export NLS_DATE_FORMAT='dd-mon-rr hh24:mi:ss'
                %rman target /

- for windows: >set nls_date_format=dd-mon-rr:hh24:mi:ss
                >rman target /

To find your backup:

RMAN>LIST BACKUP OF DATABASE COMPLETED AFTER '<date>';
or
RMAN>LIST BACKUP OF DATABASE COMPLETED AFTER 'sysdate -n';

Set <date> to limit the output to the backups that you are interested and note the completion time - for a multi-piece backup, note the completion time of the LAST backuppiece created.

Lets say, the backup completed 04-Jun-11 13:13:05 query the controlfile:

SQL>ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';
SQL> SELECT THREAD#, SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG WHERE '04-JUN-11 13:13:05' BETWEEN FIRST_TIME AND NEXT_TIME;

If the above query does not return any rows, it may be that the information has aged out of the controlfile - run the same query against v$log_history.

The sequence# returned by the above query is the log sequence current at the time the backup ended - let say 530 thread 1. For minimum recovery use:

RMAN>RUN { SET UNTIL SEQUENCE 531 THREAD 1;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}

If this is a RAC implementation the use this SQL instead to query the controlfile:

SQL>SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG WHERE '04-JUN-11 13:13:05' BETWEEN FIRST_TIME AND NEXT_TIME;

For minimum recovery use the log sequence and thread that has the lowest NEXT_CHANGE# returned by the above query.

Reference : How to determine minimum end point for recovery of an RMAN backup (Doc ID 1329415.1)

Slow Statistics purging (SYSAUX grows) - Delete Taking long

2013-02-27T16:57:00.002+05:30

Recently i came accross a problem where one delete statement as below was taking almost more than 10 hours :

DELETE /*+ dynamic_sampling(4) */ FROM sys.wri_optstat_histgrm_history WHERE savtime < :1 AND ROWNUM <= NVL (:2, ROWNUM)

It is actually purging the old statistics from sysaux table and pointing towoards a bug

For an workaround we use below, before applying any patch:

Bug 10279045 - Slow Statistics purging (SYSAUX grows) [ID 10279045.8]

SQL> alter table sys.WRI$_OPTSTAT_HISTGRM_HISTORY move tablespace SYSAUX;
Table altered.

SQL> alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild nologging parallel 8;
Index altered.

SQL> alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST noparallel;
Index altered.
　
SQL> alter index sys.I_WRI$_OPTSTAT_H_ST rebuild nologging parallel 8;
Index altered.

SQL> alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST noparallel;
Index altered.

Datapump - Space Reduction

2013-02-19T15:04:00.000+05:30

With the introduction of 11g feature with expdp/expdp compression parameter , we all are using it and greatly helps in reducing our dump size.
But do we know this is even better then gzip and produce half size then gzip. here are details:

The parameter has three options:
METDATA_ONLY - only the metadata is compressed; the data is left as it is (available in Oracle Database 10.2 as well).
... DATA_ONLY - only the data is compressed; the metadata is left alone.
ALL - both the metadata and data are compressed.
NONE - this is the default; no compression is performed.
Here is how you compress the export of the table UNITS_FACT:
$ expdp global/global directory=dump_dir tables=units_fact dumpfile=units_fact_comp.dmp compression=all

For comparison purposes, export without compression:
$ expdp global/global directory=dump_dir tables=units_fact dumpfile=units_fact_uncomp.dmp

Now if you check the files created:
$ ls -l
-rw-r----- 1 oracle dba 2576384 Jul 6 22:39 units_fact_comp.dmp
-rw-r----- 1 oracle dba 15728640 Jul 6 22:36 units_fact_uncomp.dmp

The compression ratio is 100*(15728640-2576384)/15728640 or about 83.61%! That's fairly impressive; the uncompressed dumpfile is 15MB while the compressed one is 1.5MB.
If you compress the dumpfile using gzip:
$ gzip units_factl_uncomp.dmp

-rw-r----- 1 oracle dba 3337043 Jul 6 22:36 units_fact_uncomp.dmp.gz

The compressed file is about 3.2MB, double the size of the compressed file in Data Pump. So, in addition to the compression being potentially more efficient, the decompression really adds value. When importing the dumpfile, the import does not have to decompress the file first; it decompresses as it reads it, making the process really fast.

Colored SQL - 11g New Feature

2013-02-18T12:15:00.000+05:30

Colored SQL

This new feature doesn't paint the SQL statement in color; rather, it sort of marks it as "important."

Suppose you are troubleshooting a performance issue and you suspect a specific SQL statement contributing to the problem. You want the SQL statement to be captured in every Automatic Workload Repository (AWR) snapshot. But AWR snapshots do not capture all SQL statements; just the top ones. How can you force a specific SQL to be captured, regardless of its inclusion in the top SQLs?

The procedure add_colored_sql() in the package dbms_workload_repository marks the SQL as "colored", or important enough to be captured in every AWR snapshot regardless of whether the SQL is in top SQLs. First, identify the SQL statement and then get its SQL_ID. To color it, use:

  
begin
  dbms_workload_repository.add_colored_sql(
     sql_id => 'ff15115dvgukr' 
  );
end;

To find out which SQLs have been colored, you can query the AWR table WRM$_COLORED_SQL:

SQL> SELECT * FROM wrm$_colored_sql;

DBID  SQL_ID  OWNER      CREATE_TI
---------- ------------- ----------      --------
2965581158 ff15115dvgukr 1 05-APR-08

Thereafter the SQL statement with ID ff15115dvgukr will be captured in every snapshot, even if it's not in the top SQL statements. (Of course, the SQL must be present in the library cache to be captured in the AWR snapshots.)

But what if the SQL ceases to be that colorful—that is, not important enough to be captured? You can turn it off by issuing the obverse of the procedure.

begin
  dbms_workload_repository.remove_colored_sql(
    sql_id => 'ff15115dvgukr' 
  );
end;

This feature is extremely useful when you want to focus on a specific SQL in your tuning exercises

Oracle 11gR2 ASM Spfile is in Disk Group ( How ASM starts then?)

2012-11-08T20:56:00.000+05:30

I recently got very intresting confusion ASM Spfile is stored in ASM diskgroup in 11gR2 in first default diskgroup created during GI installtion and in order to start ASM we need spfile.
So if my ASM is down, how do i start it as my Spfile lies in ASM diskgroup.

Here lies the answer to this question:

$ asmcmd spget
+DEV_DATA01/asm/asmparameterfile/registry.253.754437197

While Clusterware (GI) Startup GPnP ( Grid plug and play ) profile provides the ASM discovery string

$gpnptool getpval -asm_dis

ORCL:*

These string is used to scan the device headers which contain the ASM spfile i.e. (kfdhdb.spfflg=1).

$ asmcmd lsdsk -G RDPDEV_DATA01
Path
ORCL:ASMSDB12
ORCL:ASMSDB2
ORCL:ASMSDB3
ORCL:ASMSDB5

Let's scan above disk devices :

$ kfed read ORCL:ASMSDB12 | grep -E 'spf|ausize'
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000
kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000

$ kfed read ASMSDB2 | grep -E 'spf|ausize'

$ kfed read ORCL:ASMSDB3 | grep -E 'spf|ausize'
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000
kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000

$ kfed read ORCL:ASMSDB5 | grep -E 'spf|ausize'
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.spfile:                       19 ; 0x0f4: 0x00000013
kfdhdb.spfflg:                        1 ; 0x0f8: 0x0000000   --> here is your spfile on this disk

Final Story:

To read the ASM spfile during the ASM instance startup, it is not necessary to open the disk group. All information necessary to access the data is stored in the device's header. By the way, the same technique is used to access the Clusterware voting files which are also stored in an ASM disk group. In this case, Clusterware does not need a running ASM instance to access the cluster voting files:
grid@iudb007:~/ [+ASM5] kfed read ORCL:ASMSDB5 | grep vf
kfdhdb.vfstart:                     256 ; 0x0ec: 0x00000100 <- START offset of the voting file
kfdhdb.vfend:                       288 ; 0x0f0: 0x00000120 <- END offset of the voting file

OEM Agent : The agentTZRegion value not in Agreement

2012-11-06T17:32:00.000+05:30

Due to DST changes some days back we see couple of OEM Agents failed to start with below mentioned errors:

oracle@machine[TEST01]> ./emctl start agent
Starting agent ......... failed.
The agentTZRegion value in /orcl/app/oracle/agent11g/sysman/config/emd.properties is not in agreement with what agent thinks it should be.Please verify your environment to make sure that TZ setting has not changed since the last start of the agent.
If you modified the timezone setting in the environment, please stop the agent and exectute 'emctl resetTZ agent' and also execute the script mgmt_target.set_agent_tzrgn(<agent_name>, <new_tz_rgn>) to get the value propagated to repository.
Consult the log files in: /orcl/app/oracle/agent11g/sysman/log

OS level time as changed but it's not changed in Oracle Agent binaries. We have manaully fix it using below menthod:

1. Set the Correct Env variable to TZ parameter

oracle@machine[TEST01]> echo $TZ
Europe/Moscow
export TZ=Etc/GMT-4

2. Reset the agent time zone using emctl resetTZ agent

oracle@machine[TEST01]> ./emctl resetTZ agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
Updating /orcl/app/oracle/agent11g/sysman/config/emd.properties...
Successfully updated /orcl/app/oracle/agent11g/sysman/config/emd.properties.

Login as the em repository user and run the script: ( OEM Grid )
exec mgmt_target.set_agent_tzrgn('machinename2:1830','Etc/GMT-4')
and commit the changes

This can be done for example by logging into sqlplus and doing
SQL> exec mgmt_target.set_agent_tzrgn('machinename:1830','Etc/GMT-4')
SQL> commit

3. Agent started:

oracle@machine[TEST01]> ./emctl start agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
Starting agent ..................... started.

Reference Metalink ID note:
DST Compliance for Grid Control OMS, Repository and Agents: Checks and Updates [ID 457792.1]

ORA-12801: error signaled in parallel query server P001 ORA-01722: invalid number

2012-11-05T18:46:00.000+05:30

Today I came accross with above two errors. These are two sets of errors

1. Parallel Query Failing
2. Invalid Comparision Operation.

Query Failing:

SELECT COUNT(*) FROM SALES_MASTER_HISTORY WHERE TREASURYNUMBER < 20000;

ORA-12801: error signaled in parallel query server P001

ORA-01722: invalid number

When i check degree on this table it was set to 1 so no problem found there but degree with above table indexes was set to 4. I changed the degree to 1 to sort first issue as below:

SQL> SQL> select owner,table_name,degree from dba_tables where table_name in ('SALES_MASTER_HISTORY');

OWNER TABLE_NAME DEGREE
------------------------------ ------------------------------ ----------
CAP SALES_MASTER_HISTORY 1

SQL> select index_name,degree from dba_indexes where table_name in ('SALES_MASTER_HISTORY');

INDEX_NAME                     DEGREE
------------------------------ ----------------------------------------
IDX_TMH_NETTEDBOOKINGREF       4
IDX_VALUEDATE                  4
PK_TRADES_MASTER_HISTORY       4
IDX_TMH_CORESYSREF             4
IDX_TMH_TRUNC_BOOKEDDATE_IDX   4
IDX_TMH_BRANCH_CODE            4
IDX_TMH_CUSTOMER_NUMBER        4
IDX_TMH_BROKERCODE             4
IDX_BOOKEDDATE                 4
IDX_TMH_CONTRACTNUMBER         4
IDX_TMH_CUSTOMER               4

11 rows selected.

SQL> alter index CAP.IDX_BOOKEDDATE parallel 1;

Index altered.

SQL> alter index CAP.IDX_TMH_BRANCH_CODE parallel 1;

Index altered.

SQL> alter index CAP.IDX_TMH_BROKERCODE parallel 1;

Index altered.

SQL> alter index CAP.IDX_TMH_CONTRACTNUMBER parallel 1;

Index altered.

SQL> alter index CAP.IDX_TMH_CUSTOMER_NUMBER parallel 1;

Index altered.

SQL> alter index CAP.IDX_TMH_NETTEDBOOKINGREF parallel 1;

Index altered.

SQL> alter index CAP.IDX_VALUEDATE parallel 1;

Index altered.

SQL> alter index CAP.PK_TRADES_MASTER_HISTORY parallel 1;

Index altered.

SQL> alter index CAP.IDX_TMH_TRUNC_BOOKEDDATE_IDX parallel 1;

Index altered.

SQL> select owner,index_name,degree from dba_indexes where table_name in ('SALES_MASTER_HISTORY') order by INDEX_NAME;

OWNER                          INDEX_NAME                     DEGREE
------------------------------ ------------------------------ ----------------------------------------
CAPFX                          IDX_BOOKEDDATE                 1
CAPFX                          IDX_TMH_BRANCH_CODE            1
CAPFX                          IDX_TMH_BROKERCODE             1
CAPFX                          IDX_TMH_CONTRACTNUMBER         1
CAPFX                          IDX_TMH_CORESYSREF             1
CAPFX                          IDX_TMH_CUSTOMER               1
CAPFX                          IDX_TMH_CUSTOMER_NUMBER        1
CAPFX                          IDX_TMH_NETTEDBOOKINGREF       1
CAPFX                          IDX_TMH_TRUNC_BOOKEDDATE_IDX   1
CAPFX                          IDX_VALUEDATE                  1
CAPFX                          PK_TRADES_MASTER_HISTORY       1

After that when i execute query it was giving just :

SQL> select count(*) FROM SALES_MASTER_HISTORY WHERE TREASURYNUMBER < 20000;
select count(*) FROM SALES_MASTER_HISTORY WHERE TREASURYNUMBER < 20000
*
ERROR at line 1:
ORA-01722: invalid number

This is not really a database problem, here problem is TREASURYNUMBER is char(8) and when we compare it with number it causing implicit datatype conversion which causes error. This can be resolved by using 2000 value in quotes as below:

SQL> SELECT COUNT(*) FROM SALES_MASTER_HISTORY WHERE TREASURYNUMBER < '20000';

COUNT(*)
----------
109090909

So, this is simple solution but beneficial to know.

SQL Profile - Play with Execution plan

2012-08-24T17:17:00.000+05:30

Although i don't have too much experience working with outlines, but these days i am working significantly with SQL Profile. My Favourite blogger Kerry osborne has written a wonderfull blog on
this topic which gives me very clear idea of sql profile.

Outlines basically locks the profile , but sql profile plays with emperical value of the sql plan which normaly known as fudge factore. It's give the flexibility to optimizter to look for alternate plan.
But we can say sql profile advantages will fade with time.

Hints used in sql profile, these hinsts are very likely documents so wanted to give as much information as possible:

1. Setting Optimer Mode : ALL_ROWS

2. Disable hints present in sql : IGNORE_OPTIM_EMBEDDED_HINTS

3.Setting OPTIMIZER_FEATURES_ENABLE to it's default Value i.e active all available feature :OPTIMIZER_FEATURES_ENABLE (default)

4.Adjusting number of rows returned from the table:
OPT_ESTIMATE(@"SEL$1", TABLE, "L"@"SEL$1", SCALE_ROWS=10)
Eg : 10 times as many rows are expected to return

5. Adjusting number of rows returned from index scan:
OPT_ESTIMATE(@"SEL$1", INDEX_SKIP_SCAN, "A"@"SEL$1", PK_TABLE_XXXX_ASSIGNMENT, SCALE_ROWS=.4)
Eg: 4 times fewer rows will be returned using index skip scan.

6. Table, index and column statistics adusted using below hints:
COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL3", scale, length=12 distinct=2 nulls=0)
TABLE_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", scale, blocks=5 rows=2400)
OPTIMIZER_FEATURES_ENABLE(default)
Below are some scripts to find out hints from a sql profile and awr snapshots ( scripts are borrowed from kerry blogs )

SQL> set echo on
SQL> @sql_profile_hints
set lines 155
col hint for a150
select attr_val hint
from dba_sql_profiles p, sqlprof$attr h
where p.signature = h.signature
and name like ('&profile_name')
order by attr#
/
Enter value for profile_name: PROFILE_0qa98gcnnza7h

HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
OPT_ESTIMATE(@"SEL$1", TABLE, "L"@"SEL$1", SCALE_ROWS=0.0536172171)
OPT_ESTIMATE(@"SEL$1", INDEX_SKIP_SCAN, "A"@"SEL$1", PK_TABLE_XXXX_ASSIGNMENT, SCALE_ROWS=4)
COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL1", scale, length=6 distinct=1234 nulls=0 min=1000000014 max=1026369632)
COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL2", scale, length=12 distinct=2 nulls=0)
COLUMN_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", "COL3", scale, length=12 distinct=2 nulls=0)
TABLE_STATS("APP_OWNER"."TABLE_XXXX_ASSIGNMENT", scale, blocks=5 rows=2400)
OPTIMIZER_FEATURES_ENABLE(default)

SQL> @sql_hints_awr
select
extractvalue(value(d), '/hint') as outline_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
dba_hist_sql_plan
where
sql_id = '&sql_id'
and plan_hash_value = &plan_hash_value
and other_xml is not null
)
) d;
Enter value for sql_id: 0qa98gcnnza7h
Enter value for plan_hash_value: 568322376

OUTLINE_HINTS
-----------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "SKEW"@"SEL$1")

Scripts to create sql profile ( Kerry osborne blog reference ) :

create_sql_profile.sql – uses cursor from the shared pool
create_sql_profile_awr.sql – uses AWR tables
sql_profile_hints.sql – shows the hints in a SQL Profile for 10g

Basic Commands:
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE
DBMS_SQLTUNE.ALTER_SQL_PROFILE
DBMS_SQLTUNE.DROP_SQL_PROFILE

set verify off exec dbms_sqltune.accept_sql_profile(task_name => '&task_name',category => '&category');

set verify off
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name => '&profile_name', attribute_name => 'STATUS', value => 'DISABLED');

Reference Links :

http://kerryosborne.oracle-guy.com/2009/04/oracle-sql-profiles/

Table Fragmentation - Oracle

2012-07-25T10:37:00.003+05:30

Table fragmentation –

If a table is only subject to inserts, there will not be any fragmentation.
Fragmentation comes with when we update/delete data in table.

The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation.

To understand it more clearly, we need to be clear on how oracle manages space for tables.
“High water mark” of table actually defines the border line between (ever) used and unused (never) space. While performing full table scan, Oracle will always read the data up to HWM. And if there is lot of free space with-in HWM, that is read too, and hence degrading the performance of FTS.

Now lets see how to identify HWM, unused (never used) space and free space (used but deleted/updated) and then take a call whether the concerned table is candidate for a reorganization or not.

SQL> create table test as select * from dba_tables; -- Create a table
Table created.

SQL> exec dbms_stats.gather_table_stats('SYS','TEST); -- Gather Stats on this table

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
2 from user_tables where table_name='TEST'; -- The number of blocks used/free

Ever Used Never Used Total rows
---------- ---------- ----------
49 6 1680

SQL> delete from test where owner='SYS'; --- Im deleting almost half the number of rows.
764 rows deleted.

SQL> commit;
Commit complete.

SQL> exec dbms_stats.gather_table_stats('SYS','TEST); -- Gather Stats on this table again

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
2 from user_tables where table_name='TEST'; -- No difference in blocks usage

Ever Used Never Used Total rows
---------- ---------- ----------
49 6 916

Even though you deleted almost half the rows, the above shows that table HWM is up to 49 blocks, and to perform any FTS, Oracle will go up to 49 blocks to search the data. If your application is so-written that there are many FTS on this table, you may consider, reorganizing this table.

Reasons to reorganization

a) Slower response time (from that table)
b) High number of chained (actually migrated) rows.
c) Table has grown many folds and the old space is not getting reused.
Note: Index based queries may not get that much benefited by reorg as compared to queries which does Full table scan.

How to reorganize?

Before Oracle 10g, there were mainly 2 ways to do the reorganization of the table

a) Export, drop, and import.
b) Alter table move (to another tablespace, or same tablespace).

Oracle 10g provides us a new way of reorganizing the data.

Shrink command: This command is only applicable for tables which are tablespace with auto segment space management.
Before using this command, you should have row movement enabled.

SQL> alter table test enable row movement;
Table altered.

There are 2 ways of using this command.

1. Break in two parts: In first part rearrange rows and in second part reset the HWM.

Part 1: Rearrange (All DML's can happen during this time)

SQL> alter table test shrink space compact;
Table altered.

Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)
SQL> alter table sa shrink space;
Table altered.

2. Do it in one go:

SQL> alter table sa shrink space; (Both rearrange and restting HWM happens in one statement)
Table altered.

Few advantages over the conventional methods

1. Unlike "alter table move ..", indexes are not in UNUSABLE state. After shrink command, indexes are updated also.
2. Its an online operation, So you dont need downtime to do this reorg.
3. It doesnot require any extra space for the process to complete.

Conclusion
Its a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management.

EMD upload error: Upload was successful but collections currently disabled - disk full

2012-06-27T16:43:00.000+05:30

This errors occures while uploading OEM agent becuase your filesystem has breached the level to which agent is set.

We had two solutions here:

1. Clear the filesystem, if not perform second step.
2. Change the value of below parameters in emd.properties file in
<AGENT_HOME>/sysman/config/emd.properties.

Parameter before change:
UploadMaxDiskUsedPct=98
UploadMaxDiskUsedPctFloor=95

Parameter After Change:
UploadMaxDiskUsedPct=99
UploadMaxDiskUsedPctFloor=99
./emctl secure agent password
./emctl upload agent
./emctl clearstate agent

Above step will fix the problem and we can upload the agent now:

./emctl upload agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

Reference Metalink Note : 317242.1

Values populated in DBA_TAB_MODIFICATIONS

2012-06-19T20:39:00.002+05:30

Goal :
The goal is to explain why the view DBA_TAB_MODIFICATIONS does sometimes have no values
even when the parameter STATISTICS_LEVEL is set to TYPICAL and the specific schema has been analyzed successful using the package DBMS_STATS.GATHER_SCHEMA_STATS.
In addition all the tables in that schema shows MONITORING=YES in the view dba_tables.

Fix:
The updates to the table *_tab_modifications are related to the volumne of updates for a table.
There is a need of approximatly 10% of datavolumn changes. Just only on single update of the row for example might not lead to fill the *_tab_modifications.

Example :

SQL> create table test ( num varchar2(32));
Table created.

Lets insert 100 rows to table test:

SQL> begin
2 for i in 1..100 loop
3 insert into sys.test values (i);
4 end loop;
5 commit;
end; 6
7 /
PL/SQL procedure successfully completed.

SQL> select count(*) from test;
COUNT(*)
----------
       100

Gather stats for this table:

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','TEST');
PL/SQL procedure successfully completed.

SQL> SELECT OWNER,TABLE_NAME,STALE_STATS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TAB_STATISTICS where OWNER ='SYS' AND TABLE_NAME='TEST';
OWNER                          TABLE_NAME                     STA   NUM_ROWS
------------------------------ ------------------------------ --- ----------
    BLOCKS EMPTY_BLOCKS
---------- ------------
SYS                            TEST                           NO         100
         1            0

SQL> select * from dba_tab_modifications where TABLE_OWNER='SYS' AND TABLE_NAME='TEST';

no rows selected

Now lets manually flush the modifications from SGA:

Note: The procedure DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO flushes in-memory monitoring information for all tables in the dictionary.
Corresponding entries in the *_TAB_MODIFICATIONS, *_TAB_STATISTICS and *_IND_STATISTICS
views are updated immediately, without waiting for the Oracle database to flush them periodically (per default every 3 hours). This procedure is useful when you need up-to-date information in those views.

SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.

SQL> SELECT OWNER,TABLE_NAME,STALE_STATS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TAB_STATISTICS where OWNER ='SYS' AND TABLE_NAME='TEST';
OWNER                          TABLE_NAME                     STA   NUM_ROWS
------------------------------ ------------------------------ --- ----------
    BLOCKS EMPTY_BLOCKS
---------- ------------
SYS                            TEST                           NO         100
         1            0

Now lets insert 1000 more values and check if it put entry in dba_tab_modifications:

SQL> begin
for i in 1..1000 loop
insert into sys.test values (i);
end loop;
commit;
end;
/
2    3    4    5    6    7
PL/SQL procedure successfully completed.

SQL> select count(*) from test;
COUNT(*)
----------
      1100

SQL> SELECT OWNER,TABLE_NAME,STALE_STATS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TAB_STATISTICS where OWNER ='SYS' AND TABLE_NAME='TEST';
OWNER                          TABLE_NAME                     STA   NUM_ROWS
------------------------------ ------------------------------ --- ----------
    BLOCKS EMPTY_BLOCKS
---------- ------------
SYS                            TEST                           NO         100
         1            0
no rows selected

SQL> select * from dba_tab_modifications where TABLE_OWNER='SYS' AND TABLE_NAME='TEST';
no rows selected

SQL> SELECT OWNER,TABLE_NAME,STALE_STATS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TAB_STATISTICS where OWNER ='SYS' AND TABLE_NAME='TEST';
OWNER                          TABLE_NAME                     STA   NUM_ROWS
------------------------------ ------------------------------ --- ----------
    BLOCKS EMPTY_BLOCKS
---------- ------------
SYS                            TEST                           NO         100
         1            0

SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.

SQL> SELECT OWNER,TABLE_NAME,STALE_STATS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TAB_STATISTICS where OWNER ='SYS' AND TABLE_NAME='TEST';
OWNER                          TABLE_NAME                     STA   NUM_ROWS
------------------------------ ------------------------------ --- ----------
    BLOCKS EMPTY_BLOCKS
---------- ------------
SYS                            TEST                           YES        100
         1            0

SQL> select * from dba_tab_modifications where TABLE_OWNER='SYS' AND TABLE_NAME='TEST';
TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS
------------------------------ ------------------------------ ----------
   UPDATES    DELETES TIMESTAMP TRU DROP_SEGMENTS
---------- ---------- --------- --- -------------
SYS                            TEST
                                                                    1000
         0          0 19-JUN-12 NO              0

Playing with Optimizer Mode - Execution Plan

2012-06-06T21:19:00.002+05:30

Today i need to talk about optimizer modes in oracle and in this post i will specially talk about ALL_ROWS and FIRST_ROWS modes.

Possbile values can be:

OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.
Values:
first_rows_n
The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).
first_rows
The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.
all_rows
The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).
Lets create a test table and index over it with statistics:

SQL> create table opt_test as select * from dba_objects;
Table created.

SQL> create index opt_testindex on opt_test(OBJECT_TYPE);
Index created.

SQL> exec dbms_stats.gather_table_stats('sys','opt_test');
PL/SQL procedure successfully completed.

SQL> select count(*) from opt_test;
COUNT(*)
----------
     62952

SQL> set pagesize 200
SQL> set linesize 200
SQL> select object_type,count(*) from dba_objects group by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
CONSUMER GROUP               5
INDEX PARTITION            228
SEQUENCE                   314
QUEUE                       21
SCHEDULE                     1
TABLE PARTITION            557
RULE                         1
JAVA DATA                  301
PROCEDURE                  283
OPERATOR                    57
LOB PARTITION                1
WINDOW                       2
DATABASE LINK                1
LOB                        679
PACKAGE                    983
PACKAGE BODY               921
LIBRARY                    154
RULE SET                    11
PROGRAM                     12
TYPE BODY                  174
CONTEXT                      7
JAVA RESOURCE              773
XML SCHEMA                  24
TRIGGER                    435
JOB CLASS                    2
UNDEFINED                    6
DIRECTORY                   18
MATERIALIZED VIEW            5
TABLE                     4163
INDEX                     4524
SYNONYM                  24462
VIEW                      4977
FUNCTION                   342
WINDOW GROUP                 1
JAVA CLASS               16474
INDEXTYPE                   10
JAVA SOURCE                  5
CLUSTER                     10
TYPE                      1990
RESOURCE PLAN                3
EVALUATION CONTEXT           8
JOB                          8
42 rows selected.

You we can say totaly table rows i.e. 63k , synonyms is in maximum quantity almost 25k. Now when we select objects with object_type as
synonyms it should go for full table scan rather than going for index scan than fetch data from the tables.
But things are not simple as it looks we can cheat around oracle using different optimizer mode as shown below:

SQL> set autotrace traceonly exp
SQL> alter session set optimizer_mode=ALL_ROWS;
Session altered.
SQL> select * from opt_test where object_type='SYNONYM';
Execution Plan
----------------------------------------------------------
Plan hash value: 1427440082
------------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          | 23810 | 2185K|   197   (4)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| OPT_TEST | 23810 | 2185K|   197   (4)| 00:00:03 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_TYPE"='SYNONYM')
SQL> alter session set optimizer_mode=FIRST_ROWS;
Session altered.
SQL> select * from opt_test where object_type='SYNONYM';
Execution Plan
----------------------------------------------------------
Plan hash value: 743984098
---------------------------------------------------------------------------------------------
| Id | Operation                   | Name          | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               | 23810 | 2185K| 1133   (1)| 00:00:14 |
|   1 | TABLE ACCESS BY INDEX ROWID| OPT_TEST      | 23810 | 2185K| 1133   (1)| 00:00:14 |
|* 2 |   INDEX RANGE SCAN          | OPT_TESTINDEX | 23810 |       |    66   (2)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_TYPE"='SYNONYM')
SQL>

As we need first rows quickly optimizer compromises on cost of execution plan and chooses the plan which will deliver first rows fastly.

This does not declare your optimer mode is not good, but it is beneficial in OLTP environment where user need first rows fastly on their on screen , then plan is real good.

Conclusion
Cost based optimizer gives you flexibility to choose response time or throughput. So use them based on your business requirement.

Manually add a host target to Grid Control / OEM

2012-05-20T23:18:00.001+05:30

Recently i went through the situation where is incedently drop host target from agent while moving my agent
to another grid.

So, i got following solution and it works for me:

Solution: Create a temporary file /tmp/hosttarget with one line:

<Target TYPE="host" NAME="hostname.abc.com"/>

Where hostname.abc.com is the host name. Then add the target with:

emctl config agent addtarget -f /tmp/hosttarget

Similary we can do this for other targets as well.

Oracle Join Nested/Hash/Sort Merge- Performance Tuning

2012-05-05T16:21:00.000+05:30

Normally we have three types of Oracle join:

1. Nested - Loop Join
2. Hash Join
3. Sort Merge Join

I will discuss about characteristics of these three joins today:

1. Nested Loop Joins

Nested loop joins are useful when small subsets of data are being joined and if the join condition is an efficient way of accessing the second table.
It is very important to ensure that the inner table is driven from (dependent on) the outer table. If the inner table's access path is independent of the outer table, then the same rows are retrieved for every iteration of the outer loop, degrading performance considerably. In such cases, hash joins joining the two independent row sources perform better.

1.1 When the Optimizer Uses Nested Loop Joins

The optimizer uses nested loop joins when joining small number of rows, with a good driving condition between the two tables. You drive from the outer loop to the inner loop, so the order of tables in the execution plan is important.
The outer loop is the driving row source. It produces a set of rows for driving the join condition. The row source can be a table accessed using an index scan or a full table scan. Also, the rows can be produced from any other operation. For example, the output from a nested loop join can be used as a row source for another nested loop join.
The inner loop is iterated for every row returned from the outer loop, ideally by an index scan. If the access path for the inner loop is not dependent on the outer loop, then you can end up with a Cartesian product; for every iteration of the outer loop, the inner loop produces the same set of rows. Therefore, you should use other join methods when two independent row sources are joined together.

1.2 Nested Loop Join Hints

If the optimizer is choosing to use some other join method, you can use the USE_NL(table1 table2) hint, where table1 and table2 are the aliases of the tables being joined.

2 Hash Joins

Hash joins are used for joining large data sets. The optimizer uses the smaller of two tables or data sources to build a hash table on the join key in memory. It then scans the larger table, probing the hash table to find the joined rows.
This method is best used when the smaller table fits in available memory. The cost is then limited to a single read pass over the data for the two tables.

2.1 When the Optimizer Uses Hash Joins

The optimizer uses a hash join to join two tables if they are joined using an equijoin and if either of the following conditions are true:

A large amount of data needs to be joined.
A large fraction of a small table needs to be joined.

2.2 Hash Join Hints

Apply the USE_HASH hint to instruct the optimizer to use a hash join when joining two tables together.

3. Sort Merge Joins

Sort merge joins can be used to join rows from two independent sources. Hash joins generally perform better than sort merge joins. On the other hand, sort merge joins can perform better than hash joins if both of the following conditions exist:

The row sources are sorted already.
A sort operation does not have to be done.

However, if a sort merge join involves choosing a slower access method (an index scan as opposed to a full table scan), then the benefit of using a sort merge might be lost.
Sort merge joins are useful when the join condition between two tables is an inequality condition (but not a nonequality) like <, <=, >, or >=. Sort merge joins perform better than nested loop joins for large data sets. You cannot use hash joins unless there is an equality condition.
In a merge join, there is no concept of a driving table. The join consists of two steps:

Sort join operation: Both the inputs are sorted on the join key.
Merge join operation: The sorted lists are merged together.

If the input is already sorted by the join column, then a sort join operation is not performed for that row source. However, a sort merge join always creates a positionable sort buffer for the right side of the join so that it can seek back to the last match in the case where duplicate join key values come out of the left side of the join.

3.1 When the Optimizer Uses Sort Merge Joins

The optimizer can choose a sort merge join over a hash join for joining large amounts of data if any of the following conditions are true:

The join condition between two tables is not an equi-join.
Because of sorts already required by other operations, the optimizer finds it is cheaper to use a sort merge than a hash join.

3.2 Sort Merge Join Hints

To instruct the optimizer to use a sort merge join, apply the USE_MERGE hint. You might also need to give hints to force an access path.
There are situations where it is better to override the optimizer with the USE_MERGE hint. For example, the optimizer can choose a full scan on a table and avoid a sort operation in a query. However, there is an increased cost because a large table is accessed through an index and single block reads, as opposed to faster access through a full table scan.

Note : if we consider on the broader look Nested Loop join is performed on small tables with index on driven (inner) column will add edge to it, on the other hand Hash Join is used on Large tables with no indexes and use pga for preparing hash table. Sort Merge join is used in case of medium sized tables.

Above note is referenced from Oracle Internals and manuals.

Example:

SQL> conn hr/*****
Connected.
SQL> create table e as select * from emp;
Table created.
SQL> create table d as select * from dept;
Table created.
SQL> create index e_deptno on e(deptno);
Index created.
Gather D stats as it is
SQL> exec dbms_stats.gather_table_stats('hr','D')
PL/SQL procedure successfully completed.

Set artificial stats for E:
SQL> exec dbms_stats.set_table_stats(ownname => 'hr', tabname => 'E', numrows => 100, numblks => 100, avgrlen => 124);
PL/SQL procedure successfully completed.

Set artificial stats for E_DEPTNO index
SQL> exec dbms_stats.set_index_stats(ownname => 'hr', indname => 'E_DEPTNO', numrows => 100, numlblks => 10);
PL/SQL procedure successfully completed.

Check out the plan:
A) With less number of rows(100 in E), you will see Nested loop getting used.

SQL> select e.ename,d.dname from e, d where e.deptno=d.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 3204653704
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2200 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| E | 25 | 225 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 100 | 2200 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | D | 4 | 52 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | E_DEPTNO | 33 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

B) Let us set some more artificial stats to see which plans is getting used:

SQL> exec dbms_stats.set_table_stats(ownname => 'hr', tabname => 'E', numrows => 1000000, numblks => 10000, avgrlen => 124);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_index_stats(ownname => 'hr', indname => 'E_DEPTNO', numrows => 1000000, numlblks => 1000);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_table_stats(ownname => 'hr', tabname => 'D', numrows => 1000000,numblks => 10000 , avgrlen => 124);
PL/SQL procedure successfully completed.

Now we have 1000000 number of rows in E and D table both and index on E(DEPTNO) reflects the same.
Plans changes !!
SQL> select e.ename,d.dname from e, d where e.deptno=d.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 51064926
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 250G| 5122G| | 3968K(100)| 13:13:45 |
|* 1 | HASH JOIN | | 250G| 5122G| 20M| 3968K(100)| 13:13:45 |
| 2 | TABLE ACCESS FULL| E | 1000K| 8789K| | 2246 (3)| 00:00:27 |
| 3 | TABLE ACCESS FULL| D | 1000K| 12M| | 2227 (2)| 00:00:27 |
-----------------------------------------------------------------------------------

C) Now to test MERGE JOIN, we set moderate number of rows and do some ordering business.
SQL> exec dbms_stats.set_table_stats(ownname => 'hr', tabname => 'E', numrows => 10000, numblks => 1000, avgrlen => 124);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_index_stats(ownname => 'hr', indname => 'E_DEPTNO', numrows => 10000, numlblks => 100);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_table_stats(ownname => 'hr', tabname => 'D', numrows => 1000, numblks => 100, avgrlen => 124);
PL/SQL procedure successfully completed.
SQL> select e.ename,d.dname from e, d where e.deptno=d.deptno order by e.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 915894881
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500K| 52M| 167 (26)| 00:00:02 |
| 1 | MERGE JOIN | | 2500K| 52M| 167 (26)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| E | 10000 | 90000 | 102 (1)| 00:00:02 |
| 3 | INDEX FULL SCAN | E_DEPTNO | 10000 | | 100 (0)| 00:00:02 |
|* 4 | SORT JOIN | | 1000 | 13000 | 25 (4)| 00:00:01 |
| 5 | TABLE ACCESS FULL | D | 1000 | 13000 | 24 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Invisible Indexes - 11g New Feature

2012-05-04T12:59:00.000+05:30

Invisible Indexes in Oracle Database 11g Release 1 New Feature

Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level. Indexes can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command.

1. Create a table t1 with 2 columns n1 and n2

Hint : Create table t1(n1 number,n2 number);

SQL> show parameter visible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE
SQL> Create table t1(n1 number,n2 number);

Table created.

2. Populate Records

    Begin
          For i in 1..1000 loop
           Insert into t1 values(i,i);
          end loop;
   end;
/

SQL>   Begin
          For i in 1..1000 loop
           Insert into t1 values(i,i);
          end loop;
   end;
/ 2    3    4    5    6

PL/SQL procedure successfully completed.

SQL>

3.Create a Invisible index on column n1

Hint :-

SQL> create index t1_n1 on t1(n1) invisible;

create index t1_n1 on t1(n1) invisible;

4
SQL> explain plan for select count(*) from t1 where n1=:b1;

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |      |     1 |    13 |            |          |
|* 2 |   TABLE ACCESS FULL| T1   |    10 |   130 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("N1"=TO_NUMBER(:B1))

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.
If you see above it's not using index as we have give it to be in invisible mode.

5 alter index t1_n1 visible;

6 Define a bind variable b1

sql> variable b1 number

sql>begin
        :b1:=5;
     end;

7 SQL> explain plan for select count(*) from t1 where n1=:b1;

8 SQL> select * from table(dbms_xplan.display);
---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1|     5|      1   (0)| 00:00:01 |
|   1|   SORT AGGREGATE    |       |      1|     5|             |          |
|* 2|     INDEX RANGE SCAN| T1_N1 |      1|     5|      1   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
   2 - access("N1"=TO_NUMBER(:B1))

9 alter index t1_n1 visible;

10 SQL> explain plan for select count(*) from t1 where n1=:b1;

SQL> explain plan for select count(*) from t1 where n1=:b1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 73337487

---------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    13 |     1   (0)| 00:00:01 |
|   1 | SORT AGGREGATE   |       |     1 |    13 |            |          |
|* 2 |   INDEX RANGE SCAN| T1_N1 |    10 |   130 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("N1"=TO_NUMBER(:B1))

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

SQL>

Benefits:Invisible indexes can be useful for processes with specific indexing needs, where the presence of the indexes may adversely affect other functional areas. They are also useful for testing the impact of dropping an index.

View:The current visibility status of an index is indicated by the VISIBILITY column of the [DBA|ALL|USER]_INDEXES views.

Bottleneck: Invisble index are just not visible to select statement, rather it will operate normally in case of DML's.

Row chaining and Row Migration - Perfomance Tuning

2012-05-03T15:51:00.001+05:30

Proof of Concept: There are two circumstances when this can occur, the data for a row in a table may be too large to fit into a single data block. This can be caused by either row chaining or row migration.

Row Chaining: Occurs when the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG, LONG RAW, LOB, etc. Row chaining in these cases is unavoidable.

Row Migration: Occurs when a row that originally fitted into one data block is updated so that the overall row length increases, and the block’s free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row: the rowid of a migrated row does not change.
When a row is chained or migrated, performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for that row.

o INSERT and UPDATE statements that cause migration and chaining perform poorly, because they perform additional processing.

o SELECTs that use an index to select migrated or chained rows must perform additional I/Os.

Detection: Migrated and chained rows in a table or cluster can be identified by using the ANALYZE command with the LIST CHAINED ROWS option. This command collects information about each migrated or chained row and places this information into a specified output table. To create the table that holds the chained rows,

1. Execute script UTLCHAIN.SQL. i.e @?/rdbms/admin/utlchain.sql --> to crated the table chained_Rows which will contain the data after analyze.

2.ANALYZE TABLE scott.emp LIST CHAINED ROWS; --> Analyze table

3.SELECT * FROM chained_rows; --> it will populate data in chained_rows regarding row chaining and migrations

4.You can also detect migrated and chained rows by checking the ‘table fetch continued row’ statistic in the v$sysstat view.
SQL> SELECT name, value FROM v$sysstat WHERE name = ‘table fetch continued row’;
NAME VALUE
—————————————————————- ———
table fetch continued row 308

Although migration and chaining are two different things, internally they are represented by Oracle as one. When detecting migration and chaining of rows you should analyze carrefully what you are dealing with.

Resolving:
o In most cases chaining is unavoidable, especially when this involves tables with large columns such as LONGS, LOBs, etc. When you have a lot of chained rows in different tables and the average row length of these tables is not that large, then you might consider rebuilding the database with a larger blocksize.

e.g.: You have a database with a 2K block size. Different tables have multiple large varchar columns with an average row length of more than 2K. Then this means that you will have a lot of chained rows because you block size is too small. Rebuilding the database with a larger block size can give you a significant performance benefit.
o Migration is caused by PCTFREE being set too low, there is not enough room in avoid migration, all tables that are updated should have their PCTFREE set so that there is enough space within the block for updates.
You need to increase PCTFREE to avoid migrated rows. If you leave more free space available in the block for updates, then the row will have more room to grow.
SQL Script to eliminate row migration :
— Get the name of the table with migrated rows:
ACCEPT table_name PROMPT ‘Enter the name of the table with migrated rows: ‘

— Clean up from last execution
set echo off
DROP TABLE migrated_rows;
DROP TABLE chained_rows;
— Create the CHAINED_ROWS table
@…/rdbms/admin/utlchain.sql
set echo on
spool fix_mig
— List the chained and migrated rows
ANALYZE TABLE &table_name LIST CHAINED ROWS;

— Copy the chained/migrated rows to another table
create table migrated_rows as
SELECT orig.*
FROM &table_name orig, chained_rows cr
WHERE orig.rowid = cr.head_rowid
AND cr.table_name = upper(‘&table_name’);

— Delete the chained/migrated rows from the original table
DELETE FROM &table_name WHERE rowid IN (SELECT head_rowid FROM chained_rows);

— Copy the chained/migrated rows back into the original table
INSERT INTO &table_name SELECT * FROM migrated_rows;

spool off

Tips

1. Analyze the table and check the chained count for that particular table
8671 Chain Count

analyze table tbl_tmp_transaction_details compute statistics

select table_name,chain_cnt,pct_free,pct_used from dba_tables where table_name=’TBL_TMP_TRANSACTION_DETAILS’

2. Increase the pctfree size to 30

alter table tbl_tmp_transaction_details pctfree 30

3. Regenerate Report (When rows get updated only we will have Chained rows)

tbl_report_generation_status

begin dbms_job.run(190); end;

4. Analyze the table and check the chained count for that particular table
0 Chain Count

analyze table tbl_tmp_transaction_details compute statistics

select table_name,chain_cnt,pct_free,pct_used from dba_tables where table_name=’TBL_TMP_TRANSACTION_DETAILS’

Note:
If we want to do the procedure to delete the chained rows from original table and insert the same again, then we need chained_rows table
To create chained rows we need to run the utlchain.sql from $ORACLE_HOME/rdbms

Find out the chained rows.

analyze table tbl_tmp_transaction_details list chained count;

The above command will move the chained rows to chained_row table
Based on the rowid in chained_row table we can move those record to temp table and delete those chained rows from original table then insert the same again into original table.

select * from tbl_tmp_transaction_details where rowid=’AAAG8DAAGAAAGOKABD’:

Example:

SQL> Create table frag_tab(code number,x1 char(2000),x2 char(2000),
                            x3 char(2000),x4 char(2000)); 2

Table created.

SQL> Insert into frag_tab(code) values(1);
       Insert into frag_tab(code) values(2);
       Insert into frag_tab(code) values(3);
       commit;
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>

Commit complete.

SQL>
SQL>
SQL>
SQL> update frag_tab set x1='x1',x2='x2',x3='x3',x4='x4' where code=2;
   update frag_tab set x1='x1',x2='x2',x3='x3',x4='x4' where code=1;
   update frag_tab set x1='x1',x2='x2',x3='x3',x4='x4' where code=3;
commit;

1 row updated.

SQL>
1 row updated.

SQL>
1 row updated.

SQL>
Commit complete.

SQL>
SQL>
SQL>
SQL>
SQL> @?/rdbms/admin/utlchain.sql

Table created.

SQL> Analyze table frag_tab list chained rows;

Table analyzed.

SQL> select * from chained_rows;

OWNER_NAME                     TABLE_NAME
------------------------------ ------------------------------
CLUSTER_NAME                   PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME              HEAD_ROWID         ANALYZE_T
------------------------------ ------------------ ---------
SYS                            FRAG_TAB

N/A                            AAASOtAABAAAU9ZAAA 03-MAY-12

SYS                            FRAG_TAB

N/A                            AAASOtAABAAAU9ZAAB 03-MAY-12

OWNER_NAME                     TABLE_NAME
------------------------------ ------------------------------
CLUSTER_NAME                   PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME              HEAD_ROWID         ANALYZE_T
------------------------------ ------------------ ---------

SYS                            FRAG_TAB

N/A                            AAASOtAABAAAU9ZAAC 03-MAY-12

SQL> Create table duptab as select * from frag_tab where 1=0;

Table created.

SQL> Insert into duptab select * from frag_tab
       where rowid in(select head_rowid from chained_rows); 2

3 rows created.

SQL> delete frag_tab where rowid in(select head_rowid from chained_rows);

3 rows deleted.

SQL> Insert into frag_tab
                   as select * from duptab 2
3 /
                   as select * from duptab
                   *
ERROR at line 2:
ORA-00926: missing VALUES keyword

SQL> Insert into frag_tab
                    select * from duptab 2
3 /

3 rows created.

SQL> commit;

Commit complete.

SQL> truncate table chained_rows;

Table truncated.

SQL> Create tablespace bigtbs
         datafile '/home/oracle/bigtbs.dbf' size 10m
         blocksize 16k; 2    3
Create tablespace bigtbs
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes

SQL> ALter system set db_16k_cache_size=10m;

System altered.

SQL> Create tablespace bigtbs
         datafile '/home/oracle/bigtbs.dbf' size 10m
         blocksize 16k; 2    3
Create tablespace bigtbs
*
ERROR at line 1:
ORA-01119: error in creating database file '/home/oracle/bigtbs.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory

SQL> l
1 Create tablespace bigtbs
2           datafile '/home/oracle/bigtbs.dbf' size 10m
3*          blocksize 16k
SQL> Create tablespace bigtbs
         datafile '/tmp/bigtbs.dbf' size 10m
         blocksize 16k;
2    3
Tablespace created.

SQL> Alter table frag_tab move tablespace bigtbs;

Table altered.

SQL> Analyze table frag_tab list chained rows;

Table analyzed.

SQL> select * from chained_rows
2 /

no rows selected

SQL>

Increemental Statistics Gathering Feature -11g

2012-05-02T20:17:00.000+05:30

Increemental Statistics Gathering Feature -11g

Expensive global statistics collection

In data warehouse environment it is very common to do a bulk load directly into one or more empty partitions. This will make the partition statistics stale and may also make the global statistics stale. Re-gathering statistics for the effected partitions and for the entire table can be very time consuming. Traditionally, statistics collection is done in a two-pass approach:

In the first pass we will scan the table to gather the global statistics
In the second pass we will scan the partitions that have been changed to gather their partition level statistics.

The full scan of the table for global statistics collection can be very expensive depending on the size of the table. Note that the scan of the entire table is done even if we change a small subset of partitions.

In Oracle Database 11g, we avoid scanning the whole table when computing global statistics by deriving the global statistics from the partition statistics. Some of the statistics can be derived easily and accurately from partition statistics. For example, number of rows at global level is the sum of number of rows of partitions. Even global histogram can be derived from partition histograms. But the number of distinct values (NDV) of a column cannot be derived from partition level NDVs. So, Oracle maintains another structure called a synopsis for each column at the partition level. A synopsis can be considered as sample of distinct values. The NDV can be accurately derived from synopses. We can also merge multiple synopses into one. The global NDV is derived from the synopsis generated by merging all of the partition level synopses. To summarize

Gather statistics and create synopses for the changed partitions only
Oracle automatically merges partition level synopses into a global synopsis
The global statistics are automatically derived from the partition level statistics and global synopses

Incremental maintenance feature is disabled by default. It can be enabled by changing the INCREMENTAL table preference to true. It can also be enabled for a particular schema or at the database level. If you are interested in more details of the incremental maintenance feature

Assume we have table called SALES that is range partitioned by day on the SALES_DATE column. At the end of every day data is loaded into latest partition and partition statistics are gathered. Global statistics are only gathered at the end of every month because gathering them is very time and resource intensive. Use the following steps in order to maintain global statistics after every load.

1 -Turn on incremental feature for the table.

EXEC DBMS_STATS.SET_TABLE_PREFS('SH','SALES','INCREMENTAL','TRUE');

2 -At the end of every load gather table statistics using GATHER_TABLE_STATS command. You don't need to specify the partition name. Also, do not specify the granularity parameter. The command will collect statistics for partitions where data has change or statistics are missing and update the global statistics based on the partition level statistics and synopsis.

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','SALES');

Note: that the incremental maintenance feature was introduced in Oracle Database 11g Release 1. However, we also provide a solution in Oracle Database10g Release 2 (10.2.0.4) that simulates the same behavior. The 10g solution is a new value, 'APPROX_GLOBAL AND PARTITION' for the GRANULARITY parameter of the GATHER_TABLE_STATS procedures. It behaves the same as the incremental maintenance feature except that we don't update the NDV for non-partitioning columns and number of distinct keys of the index at the global level. For partitioned column we update the NDV as the sum of NDV at the partition levels. Also we set the NDV of columns of unique indexes as the number of rows of the table. In general, non-partitioning column NDV at the global level becomes stale less often. It may be possible to collect global statistics less frequently then the default (when table changes 10%) since approx_global option maintains most of the global statistics accurately.

Let's take a look at an example to see how you would effectively use the Oracle Database 10g approach.
After the data load is complete, gather statistics using DBMS_STATS.GATHER_TABLE_STATS for the last partition (say SALES_11FEB2009), specify granularity => 'APPROX_GLOBAL AND PARTITION'. It will collect statistics for the specified partition and derive global statistics from partition statistics (except for NDV as described before).

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SH', 'SALES', 'SALES_11FEB2009', GRANULARITY => 'APPROX_GLOBAL AND PARTITION');

It is necessary to install the one off patch for bug 8719831 if you are using the above features in 10.2.0.4 (patch 8877245) or in 11.1.0.7 (patch 8877251)

Let’s take the ORDERS2 table, which is partitioned by month on order_date. We will begin by enabling incremental statistics for the table and gathering statistics on the table.

After the statistics gather the last_analyzed date for the table and all of the partitions now show 13-Mar-12.

And we now have the following column statistics for the ORDERS2 table.

We can also confirm that we really did use incremental statistics by querying the dictionary table sys.HIST_HEAD$, which should have an entry for each column in the ORDERS2 table.

So, now that we have established a good baseline, let’s move on to the DML. Information is loaded into the latest partition of the ORDERS2 table once a month. Existing orders maybe also be update to reflect changes in their status. Let’s assume the following transactions take place on the ORDERS2 table this month.

After these transactions have occurred we need to re-gather statistic since the partition ORDERS_MAR_2012 now has rows in it and the number of distinct values and the maximum value for the STATUS column have also changed.

Now if we look at the last_analyzed date for the table and the partitions, we will see that the global statistics and the statistics on the partitions where rows have changed due to the update (ORDERS_FEB_2012) and the data load (ORDERS_MAR_2012) have been updated.

The column statistics also reflect the changes with the number of distinct values in the status column increase to reflect the update.

Note : Above info is referenced from Oracle blogs and Oracle Manuals.

Recreate Targets.xml File for Grid Control agent version : 10.2

2012-04-15T20:00:00.002+05:30

Some when you targets.xml is corrupted or accidently dropped , you need to create it manually. Steps are as below:

1. Create a blank targets.xml file :
cd $ORACLE_HOME/sysman/emd
touch targets.xml

2. After created targets.xml file you need to copy values from $ORACLE_HOME/sysman/config/emd.properties files as below format:

<Targets AGENT_SEED="agentseed from emd.properties">
<Target TYPE="oracle_emd" NAME="hostname and port from emd.properties EMD_URL"/>
<Target TYPE="host" NAME="hostname as it appears in the emd_url"/>
</Targets

Example :

<Targets AGENT_SEED="256896864">
<Target TYPE="oracle_emd" NAME="hostname:3872"/>
<Target TYPE="host" NAME="hostname"/>
</Targets>

First time you start the agent with above file it will itself convert AGENT_SEED parameter to AGENT_TOKEN and value comes in encrypted form

3. To force target re-discovery using below methods:

Run agentca -d as

cd <AGENT_HOME>/bin
agentca -d

Maintenance Of Archivelogs On Standby Databases

2012-03-29T22:17:00.000+05:30

Maintenance Of Archivelogs On Standby Databases

Maintenance of archivelogs which are already applied on standby database in Oracle 10g and above From Oracle 10g onwards, RMAN has a specific configuration for archivelog deletion policy i.e. ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY

This parameter can be set on primary and standby databases. To enable automatic maintenance of the archivelogs on standby perform the below steps on standby database :
In a dataguard configuration, the archivelogs from the FRA will be automatically purged when the following conditions are met by the database. If you need it purged in at the Primary site, set it on Primary database.

Step1:
prior to 11g, if not using mandatory archivelog destinations, the database (primary and standby) must be restarted with the following parameter:
SQL> alter system set "_log_deletion_policy"='ALL' scope=spfile;

Step 2 :
Enable the flash recovery area on the standby database so that all the archivelogs are shipped in the flash recovery area.

Example :
To enable Flash Recovery Area and to allow the archivelogs to be shipped to Flash Recovery Area set the below parameters
SQL> alter system set DB_RECOVERY_FILE_DEST='/disk1/FRA';
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=80G;
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

Step 3 :
Set the parameter Archive log deletion policy to applied on standby

Example :
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

Note : With the above settings whenever there is a space pressure in Flash Recovery Area all the archivelogs that are applied on standby database will be automatically deleted.
To list the obsolete objects, run the following query:
RMAN> SHOW RETENTION POLICY
RMAN> REPORT OBSOLETE;

Step 4:
there is space pressure in the FRA
When an archivelog is automatically deleted from the FRA, you will see this in the database's alert.log:
Sat Jun 18 01:00:32 2011
Deleted Oracle managed file /opt/app/oracle/FRA/ORA102/archivelog/2011_05_30/o1_mf_1_151_6y71q675_.arc
Deleted Oracle managed file /opt/app/oracle/FRA/ORA102/archivelog/2011_05_31/o1_mf_1_151_6y87pzg4_.arc