Wednesday, 11 July 2012

log file switch (private strand flush incomplete)/Private Redo Strands( Private Redo )


Earlier there was a single redo allocation latch available for the entire log buffer. This was a major contention point as till the time one won’t be able to pin the latch, he wont be able to write his changed redo vectors in the log buffer. From 10.1 onwards, the log buffer is broken in to smaller sub-buffers called Private Redo Strands(shown in the x$kcrfstrand). These are also allocated in the shared pool. These all are written by redo allocation latches. There are allocation latches now for each private strand. The data when changed now, doesn’t contend to go into the log buffer as like before but goes into the private redo strands and from there, it goes into the log buffer when the buffer pools become full or when the transaction is committed. We can see the private strands shown in the shared pool from the V$sgastat

SQL> select name,pool from V$sgastat
2 where name like ‘%private%’;

NAME POOL
————————– ————
private strands shared pool
KCB private handles shared pool
At log file switch , when the data is still is in the Private redo strands, lgwr may wait to get the flush done by DBWR. Hence the event, log file switch (private strand flush incomplete) may occur

log file switch (private strand flush incomplete)
New wait 10g
Like a “log file switch Completion”

Wait for lgwr to switch log files when generating redo
Solution:
Increase redo log file size
Reduces frequency of switches


What happens when a log file switch occurs:
Get next log file from control file
Get Redo Copy and Redo Allocation latch
Flush redo
Close File
Update Controlfile
Set new file to Current
Set old file to Active
If in Archivelog mode add file to archive list
Open all members of new logfile group
Write the SCN to the headers
Enable redo log generation
DBWR makes a list of blocks that need to be written out in order to over write the Redo log file a list of blocks that need to be written out in order to over write the Redo log file

Tuesday, 10 July 2012

Oracle Error :: SP2-1503 Unable to initialize Oracle call interface SP2-0152: ORACLE may not be functioning properly

Oracle Error :: SP2-1503 Unable to initialize Oracle call interface
Cause

Indicates a library used by SQL*Plus to communicate with the database failed to initialize correctly.
Action

1)  Check that the Oracle environment variable are set properly .
2) If using the SQL*Plus Instant Client make sure the SQL*Plus and Oracle libraries are from the same release
3) Make sure you have read access to the libraries.

In my case there are both 10g and 11g are installed on same server . and 11g is default home .now i want to connect with oracle 10g home on the server

I have export the  ORACLE_BASE, ORACLE_HOME, ORACLE_SID

$export ORACLE_BASE=/home/oracle/apps
$ export ORACLE_HOME=$ORACLE_BASE/product/db_1/10.2.0
$ export ORACLE_SID=DB01

and run sqlplus and got below error

$ sqlplus / as sysdba
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly


Then i have export the path  with above environment variables which i set earlier

$ export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin
$ PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:/bin:/usr/lib64/:$ORACLE_HOME/OPatch

and it sucessfully connected


$ sqlplus / as sysdba
Connected to:
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production







Materialized View: ORA-22818 subquery expressions not allowed here


CREATE MATERIALIZED VIEW mv1 AS
(SELECT s.id AS sport_no
, c.rollno id
, (SELECT NAME FROM class c WHERE id = sport_no ) AS stu_name
from sport s);

Error: ORA-22818 subquery expressions not allowed here

This is a documented restriction of Materialized view.
Solution: rewrite the query replacing scalar subquery with outer join



create meterialized view mv1 as
select s.id AS sport_no
, c.rollno id
from sport s , class c
where s.id = c.rollno(+);

A simpler solution: creating MV on top of the view

CREATE or REPLACE VIEW view_sport_stu_name AS
(SELECT s.id AS sport_no
, c.rollno id
, (SELECT NAME FROM class c WHERE id = sport_no ) AS stu_name
from sport s);

create MATERIALIZED VIEW mview_sport_stu_name AS
select * from view_sport_stu_name;

Thursday, 28 June 2012

How to find the unused index in database

Query to find the unused  index in database

SELECT owner, table_name ,index_name,index_type ,LAST_ANALYZED FROM DBA_INDEXES WHERE index_name IN (SELECT index_name
FROM
(SELECT
owner,
index_name
FROM
DBA_INDEXES di
WHERE
di.index_type != 'LOB'
AND
owner   =&owner
MINUS
SELECT
index_owner owner,
index_name
FROM
DBA_CONSTRAINTS dc
WHERE
index_owner   =&owner
MINUS
SELECT
p.object_owner owner,
p.object_name index_name
FROM
DBA_HIST_SNAPSHOT sn,
DBA_HIST_SQL_PLAN p
WHERE
sn.snap_id BETWEEN &start_snap_id AND &stop_snap_id
AND
p.object_type = 'INDEX'
))
and owner =&owner
ORDER BY 1


Query to find the frequency  of  index usage

SELECT
p.object_name search_columns,
ROUND (COUNT(*)/15 ,2 ) COUNT
FROM
DBA_HIST_SNAPSHOT sn,
DBA_HIST_SQL_PLAN p,
DBA_HIST_SQLSTAT st
WHERE
st.sql_id = p.sql_id
AND
sn.snap_id = st.snap_id
AND
p.object_type = 'INDEX'
AND sn.snap_id BETWEEN &start_snap_id AND &stop_snap_id
AND p.object_owner =&owner
GROUP BY
p.object_name ORDER BY 2 DESC, 1

Wednesday, 27 June 2012

ORA-00257:archiver error


ORA-00257:

archiver error. Connect internal only, until freed.
Cause:The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.
Action:Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.

In My case the device associated with ARCHIVE_LOG_DEST  was 100 % full

Tuesday, 26 June 2012

Life Cycle of a Cursor


Having a good understanding of life cycle of cursors is required knowledge for optimizing application
that execute SQL statements. The following are the steps carried out during the processing of a cursor:
Open cursor: A memory structure for the cursor is allocated in the server-side private memory
of the server process associates with the session,a user global area (UGA).Note that no SQL statement is associated with the cursor yet
Parse cursor: A SQL statement is associate with the cursor It's parse representation that include
the execution plan (which describe how the SQl engine will execute the SQl statement)is loaded in the shared pool ,specifically,in the literary cache. The structure in the uga is updated to store a pointer to the location of the sharable cursor in the library cache
Define output variables:  If the SQL statements returns a data , the variable receiving must be declare .This is not necessary not only for quires but also for DELETE,INSERT,UPDATE statement that use the returning clause
Bind input variables : If the SQL statement use bind variables, their value must be provided .
No check is performed during the binding.If invalid data is passed , a runtime error will be raised during the execution.
Execute cursor: The SQL statements is executed.But be careful , becuase the database engine doesn't always do anything significant during this phase. In fact, for many types of queries,

the real processing is usually delayed to the fetch phase.
Fetch cursor: If the SQL statement returns data, this step retrieves it. Especially for queries,
this step is where most of the processing is performed. In the case of queries, rows might
be partially fetched. In other words, the cursor might be closed before fetching all the rows.
Close cursor: The resources associated with the cursor in the UGA are freed and consequently
made available for other cursors.The shareable cursor in the library cache is not
removed. It remains there in the hope of being reused in the future.



Tuesday, 22 May 2012

Converting to AL32UTF8 Character Set from the Oracle Default of WE8MSWIN1252


Scenario

Current database uses WE8MSWIN1252 we need to migrate new database uses AL32UTF8 encoding.

Method Opted:
Export/Import you can use data pump utility to do the same

STEPS:
1) Explicitly set the NLS_LANG enviornment variable to the characterset of the source database.

#export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

2) Export the source database with export utility .take export backup using full option

#exp system/sys file=orcl_full.dmp log=orcl_full.log full=y consistent=y statistics=none buffer=10000000

3) Create a new database with AL32UTF8.

4) Set old nls_lang enviorment variable before import in new databas

#export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

5) Import data in schema

# imp Username/password file=sigma.dmp log=import_SIGMAUAT_BACKUP.log fromuser= scott_backup touser=scott

Related Issues :

Ora-12899 value to large for the column string

Cause : column length is less the n data nedd to import/ insert into perticuler column

Solution: Increase the column length

Explanation: When a database is created using byte semantics, the size of the char and varchar data types are specified in bytes ,not character .when database use single byte encoding then number of character is equivalent to the number of bytes .But if database using multiple charcter set then charcter is no longer equivalent to the bytes

During migration to new character set ,it is important to verify the column width of exisiting char and varchar column because thay may need to extended to support an encoding the required multibyte storage

 Single-Byte and Multibyte Encoding



Character
WE8MSWIN 1252 Encoding
AL32UTF8 Encoding
Ä
E4
C3 A4
Ö
F6
C3 B6
©
A9
C2 A9
€
80
E2 82 AC







Friday, 18 May 2012

How to genrate awr report

1) Connect with sys user

$ sqlplus / as sysdba

2) Run script awrrpt which you can find in ORACLE_HOME/rdbms/admin folder

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt


3) Specify the name of report type

Enter value for report_type: html

4) Specify the number of  days you want to interested the awr report

Enter value for num_days: 1

5)  Now you will found the list of

Listing the last day's Completed Snapshots  Snap Id  per hour from where you can choose the sanp Id of specific interval of time

Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orcl orcl 16159 18 May 2012 00:00 1
16160 18 May 2012 01:00 1
16161 18 May 2012 02:00 1
16162 18 May 2012 03:00 1
16163 18 May 2012 04:00 1
16164 18 May 2012 05:00 1



6) Mention  start time or end time :

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 16159
Begin Snapshot Id specified: 16159

Enter value for end_snap: 16162
End Snapshot Id specified: 16162

7)  Specify the Report Name

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

Enter value for report_name: path/myreport

How to find the redo genrated by DML,DDL



Sometime want to know redo generated by at any transaction  in oracle . We can simply use "  set autotrace on statistics"  



SQL> set autotrace on statistics
SQL> insert into  object1  select * from all_objects;
49951 rows created.
Statistics
----------------------------------------------------------
       6693  recursive calls
       3866  db block gets
      38572  consistent gets
        263  physical reads
    5600712  redo size
        675  bytes sent via SQL*Net to client
        579  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         15  sorts (memory)
          0  sorts (disk)
      49951  rows processed


But in few cases create like create Index we can find redo using below quires 


SQL> set verify off
SQL> column value new_val V
SQL> select a.name ,b.value value from v$statname a, v$mystat b  where a.statistic#=b.statistic# and lower(a.name) like '%redo size%' ;

NAME VALUE
---------------------------------------------------------------- ----------
redo size 185164

SQL> create table object3 as select * from dba_objects;

Table created.


SQL> select a.name , b.value ,b.value-&V redogenerated   from v$statname a, v$mystat b where a.statistic#=b.statistic#  and lower(a.name) like '%redo size%';

NAME                         VALUE                     REDOGENERATED  
--------                        ---------                       --------------------
redo size                       239880                        54716

SQL> set verify off
SQL> column value new_val V
SQL> select a.name ,b.value value from v$statname a, v$mystat b
2 where a.statistic#=b.statistic# and lower(a.name) like '%redo size%' ;

NAME VALUE
---------------------------------------------------------------- ----------
redo size 2323044

SQL> create index obind1 on object3(object_type);

Index created.

SQL> select a.name , b.value ,b.value-&V redogenerated
2 from v$statname a, v$mystat b where a.statistic#=b.statistic#
3 and lower(a.name) like '%redo size%';

NAME                  VALUE                   REDOGENERATED    
--------                  ---------                  ------------
   redo size             3503840                   1180796


Wednesday, 16 May 2012

ORA-00827: could not shrink sga_target to specified value



SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 24000M
sga_target big integer 24000M

SQL> alter system set sga_max_size=20000M scope=spfile;

System altered.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

ORACLE instance started.

Total System Global Area 2.3773E+10 bytes
Fixed Size 2126736 bytes
Variable Size 687869040 bytes
Database Buffers 2.3069E+10 bytes
Redo Buffers 14647296 bytes
Database mounted.
Database opened.

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 22672M
sga_target big integer 22672M


sga_max_size greter then  20000M even it  have been already  modified   after that i try to modify  sga_target oppps got an error

 SQL> alter system set sga_target=18000M scope=both;
alter system set sga_target=18000M scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00827: could not shrink sga_target to specified value

Then i try to find the  sga sub component size i.e  db_cache_size

SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------

db_cache_size big integer 22000M

ohhkkk there is an issue .I shrink first  db_cache_size and after taht i am able to shrink   sga_targe successfully.


SQL> alter system flush buffer_cache;

System altered.


SQL> alter system set db_cache_size=17008M scope=both;

System altered.

SQL> alter system set sga_target=18000M scope=both;

System altered.

SQL> alter system set sga_max_size=20000M scope=spfile;

System altered.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2.0972E+10 bytes
Fixed Size 2121400 bytes
Variable Size 2768240968 bytes
Database Buffers 1.8187E+10 bytes
Redo Buffers 14655488 bytes
Database mounted.
Database opened.
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 20000M
sga_target big integer 18000M
SQL>

Now max size also showing  20000M