- It could be a missing archivelog file
- It could be a file mentioned in your init.ora file .
In case 1 :
Try to switch log file and resolve the issue. If rman is missing crosscheck the archvielog and backup once again.
Sunday, October 18, 2009
Free Space available in TEMP Segment
Error : ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
In database there may be permanent and temporary tablespace. The view DBA_FREE_SPACE allows us to show about how much free space in a tablespace have but DBA_FREE_SPACE shows information only about permanent tablespace. It does not show information about temporary tablespace.
v$temp_space_header and v$sort_segment are not supposed to reconcile with each other. The blocks in temp_space_header are the total number of "initialized" blocks in the tempfiles. The blocks in sort_segment are the total number of allocated and usable blocks. Which is evident in following example.
V$TEMP_SPACE_HEADER
ODSPROD1> SELECT TABLESPACE_NAME, FILE_ID, BYTES_USED, BYTES_FREE FROM V$TEMP_SPACE_HEADER;
TABLESPACE_NAME FILE_ID BYTES_USED BYTES_FREE
---------------------------------------- ---------- ---------- ----------
TEMP 3 3145719808 0
TEMP 4 3145719808 0
TEMP 5 3145719808 0
TEMP 6 1073741824 0
TEMP 7 838860800 0
TEMP 1 3145719808 0
TEMP 2 3145719808 0
7 rows selected.
V$SORT_SEGMENT
SELECT tablespace_name,
total_blocks,
used_blocks,
free_blocks,
total_blocks*16/1024 as total_MB,
used_blocks*16/1024 as used_MB,
free_blocks*16/1024 as free_MB
FROM v$sort_segment;
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS TOTAL_MB USED_MB FREE_MB
---------------------------------------- ------------ ----------- ----------- -------
TEMP 2152576 0 2152576 33634 0 33634
SEGMENT USED:
SELECT b.TABLESPACE,
b.segfile#,
b.segblk#,
b.blocks,
b.blocks*16/1024 as MB,
a.SID,
a.serial#,
a.status
FROM v$session a,
v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.TABLESPACE,
b.segfile#,
b.segblk#,
b.blocks;
Oracle 9i does not release allocated TEMP segments until you shutdown the database. While the TEMP segment is allocated, it does not mean that it is unavailable for use. When a user requests a disk sort, Oracle will allocate a TEMP segment. Once that sort is done, Oracle releases this TEMP segment for future use, but does not deallocate it. When the next user requests a disk sort, Oracle does not have to allocate a new TEMP segment. It uses the same one that no user is currently using. Oracle manages this for you in 9i. And there is really only one TEMP segment in the TEMP tablespace. Multiple users can utilitize this one segment.
There have been problems in 9i where users running a TEMPORARY TEMP tablespace with TEMPFILES that is Locally Managed where Oracle does not release the sort space once it is no longer being used. This is a known bug. The workaround has been to revert back to Dictionary Managed tablespace for TEMP.
In database there may be permanent and temporary tablespace. The view DBA_FREE_SPACE allows us to show about how much free space in a tablespace have but DBA_FREE_SPACE shows information only about permanent tablespace. It does not show information about temporary tablespace.
v$temp_space_header and v$sort_segment are not supposed to reconcile with each other. The blocks in temp_space_header are the total number of "initialized" blocks in the tempfiles. The blocks in sort_segment are the total number of allocated and usable blocks. Which is evident in following example.
V$TEMP_SPACE_HEADER
ODSPROD1> SELECT TABLESPACE_NAME, FILE_ID, BYTES_USED, BYTES_FREE FROM V$TEMP_SPACE_HEADER;
TABLESPACE_NAME FILE_ID BYTES_USED BYTES_FREE
---------------------------------------- ---------- ---------- ----------
TEMP 3 3145719808 0
TEMP 4 3145719808 0
TEMP 5 3145719808 0
TEMP 6 1073741824 0
TEMP 7 838860800 0
TEMP 1 3145719808 0
TEMP 2 3145719808 0
7 rows selected.
V$SORT_SEGMENT
SELECT tablespace_name,
total_blocks,
used_blocks,
free_blocks,
total_blocks*16/1024 as total_MB,
used_blocks*16/1024 as used_MB,
free_blocks*16/1024 as free_MB
FROM v$sort_segment;
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS TOTAL_MB USED_MB FREE_MB
---------------------------------------- ------------ ----------- ----------- -------
TEMP 2152576 0 2152576 33634 0 33634
SEGMENT USED:
SELECT b.TABLESPACE,
b.segfile#,
b.segblk#,
b.blocks,
b.blocks*16/1024 as MB,
a.SID,
a.serial#,
a.status
FROM v$session a,
v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.TABLESPACE,
b.segfile#,
b.segblk#,
b.blocks;
Oracle 9i does not release allocated TEMP segments until you shutdown the database. While the TEMP segment is allocated, it does not mean that it is unavailable for use. When a user requests a disk sort, Oracle will allocate a TEMP segment. Once that sort is done, Oracle releases this TEMP segment for future use, but does not deallocate it. When the next user requests a disk sort, Oracle does not have to allocate a new TEMP segment. It uses the same one that no user is currently using. Oracle manages this for you in 9i. And there is really only one TEMP segment in the TEMP tablespace. Multiple users can utilitize this one segment.
There have been problems in 9i where users running a TEMPORARY TEMP tablespace with TEMPFILES that is Locally Managed where Oracle does not release the sort space once it is no longer being used. This is a known bug. The workaround has been to revert back to Dictionary Managed tablespace for TEMP.
Profile
How to execute .profile without logging in
. ~/.profile
ways to switch over to oracle
/usr/local/bin/sudo su - oracle
/usr/local/bin/ss -u oracle
Some time after sudo home will not be set
$ id
uid=1001(oracle) gid=5006(dba)
$ pwd
/home/ffxfzq
in that case just do like this & later run the profile script
cd ~oracle
. ./.profile
. ~/.profile
ways to switch over to oracle
/usr/local/bin/sudo su - oracle
/usr/local/bin/ss -u oracle
Some time after sudo home will not be set
$ id
uid=1001(oracle) gid=5006(dba)
$ pwd
/home/ffxfzq
in that case just do like this & later run the profile script
cd ~oracle
. ./.profile
Saturday, October 17, 2009
ORA-00230: operation disallowed: snapshot control file enqueue unavailable
To determine which job is holding the conflicting enqueue.
SELECT s.sid, username AS "User", program, module,
action, logon_time "Logon", l.*
FROM v$session s, v$enqueue_lock l
WHERE l.sid = s.sid
and l.type = 'CF'
and l.id1 = 0
and l.id2 = 2;
SID User Program Module Action Logon
--- ---- -------------------- ------------------------- ---------------- ---------
- Wait until the job creating the enqueue completes
- Cancel the current job and restart it once the job creating the enqueue completes
- Cancel the job creating the enqueue
SELECT s.sid, username AS "User", program, module,
action, logon_time "Logon", l.*
FROM v$session s, v$enqueue_lock l
WHERE l.sid = s.sid
and l.type = 'CF'
and l.id1 = 0
and l.id2 = 2;
SID User Program Module Action Logon
--- ---- -------------------- ------------------------- ---------------- ---------
- Wait until the job creating the enqueue completes
- Cancel the current job and restart it once the job creating the enqueue completes
- Cancel the job creating the enqueue
Tuesday, October 13, 2009
Query Tuning
xml requests going to the golden weblogic servlet aren't reaching the crossworlds servers- packets are coming out of the servlet however was stick on waiting for a response from crossworlds.
Application team was worried as query which was suppose to take 2-3 minutes was taking more than 30 mins to get executed.
Last analyzed is more than 3 months old
SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from DBA_TABLES WHERE
TABLE_NAME='GD_INVOICE_HEADER';
OWNER TABLE_NAME LAST_ANAL------------------------------ ------------------------------ ---------GDCWODS GD_INVOICE_HEADER 12-JUL-09
SQL> select count(*) from GDCWODS.GD_INVOICE_DETAIL;
COUNT(*)
----------
1605055
SQL> select count(*) from GDCWODS.GD_INVOICE_HEADER;
COUNT(*)
----------
250578
Create Explain Plan Table:
SQL> @?/rdbms/admin/utlxplan.sql
Explain Plan for the query:
SQL> EXPLAIN PLAN SET STATEMENT_ID='GD_ISSUE' FOR
select * from GDCWODS.GD_INVOICE_HEADER IH where SYSTEM_ID='NA' AND BILL_TO_PARTY='500818'
AND EXISTS (SELECT MAT_NUMBER FROM GDCWODS.GD_INVOICE_DETAIL WHERE DOCNUM=IH.DOCNUM AND
MAT_NUMBER ='FA10003') ;
Explained.
SQL> @?/rdbms/admin/utlxpls GD_ISSUE
Plan Table
--------------------------------------------------------------------------------
Operation Name Rows Bytes Cost Pstart Pstop
--------------------------------------------------------------------------------
SELECT STATEMENT 47 20K 1632
FILTER
TABLE ACCESS FULL GD_INVOIC 47 20K 1632
TABLE ACCESS FULL GD_INVOIC 1 20 1769
--------------------------------------------------------------------------------
Columns which are Indexed earlier:
SQL> select INDEX_NAME, TABLE_NAME, COLUMN_NAME from dba_ind_columns where
TABLE_NAME='GD_INVOICE_HEADER' and TABLE_OWNER='GDCWODS';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------------------
---------
GD_INV_HDR_IDX1 GD_INVOICE_HEADER
SYSTEM_ID
GD_INV_HDR_IDX1 GD_INVOICE_HEADER
DOCNUM
SQL> select INDEX_NAME, TABLE_NAME, COLUMN_NAME from dba_ind_columns where
TABLE_NAME='GD_INVOICE_DETAIL' and TABLE_OWNER='GDCWODS';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------------------
---------
GD_INV_DET_IDX1 GD_INVOICE_DETAIL
SYSTEM_ID
GD_INV_DET_IDX1 GD_INVOICE_DETAIL
DOCNUM
GD_INV_DET_IDX1 GD_INVOICE_DETAIL
SEQNUM
EXPLAIN 2:( no change is cost)
SQL> EXPLAIN PLAN SET STATEMENT_ID='GD_ISSUE1' FOR
select * from GDCWODS.GD_INVOICE_HEADER IH
where SYSTEM_ID='NA' AND BILL_TO_PARTY='500818'
AND EXISTS (SELECT 1 FROM GDCWODS.GD_INVOICE_DETAIL WHERE DOCNUM=IH.DOCNUM AND MAT_NUMBER
='FA10003') ; 2 3 4
Explained.
SQL> @?/rdbms/admin/utlxpls GD_ISSUE1
Plan Table
--------------------------------------------------------------------------------
Operation Name Rows Bytes Cost Pstart Pstop
--------------------------------------------------------------------------------
SELECT STATEMENT 47 20K 1632
FILTER
TABLE ACCESS FULL GD_INVOIC 47 20K 1632
TABLE ACCESS FULL GD_INVOIC 1 20 1769
--------------------------------------------------------------------------------
7 rows selected.
EXPLAIN 3 using Hint: (cost got increased)
/// USING HINT ///
SQL> EXPLAIN PLAN SET STATEMENT_ID='GD_ISSUE2' FOR
2 select * from GDCWODS.GD_INVOICE_HEADER IH where SYSTEM_ID='NA' AND
BILL_TO_PARTY='500818' AND EXISTS (SELECT /*+ INDEX(GD_INVOICE_DETAIL GD_INV_DET_IDX1) */
MAT_NUMBER FROM GDCWODS.GD_INVOICE_DETAIL WHERE DOCNUM=IH.DOCNUM AND MAT_NUMBER ='FA10003')
;
Explained.
SQL> @?/rdbms/admin/utlxpls GD_ISSUE2
Plan Table
--------------------------------------------------------------------------------
Operation Name Rows Bytes Cost Pstart Pstop
--------------------------------------------------------------------------------
SELECT STATEMENT 47 20K 1632
FILTER
TABLE ACCESS FULL GD_INVOIC 47 20K 1632
TABLE ACCESS BY INDEX ROGD_INVOIC 1 20 5262
INDEX FULL SCAN GD_INV_DE 1 5261
--------------------------------------------------------------------------------
8 rows selected.
Created Index on MAT_NUMBER:
SQL> create index GD_INV_DET_MAT_NUM_IDX1 on GDCWODS.GD_INVOICE_DETAIL(MAT_NUMBER);
Index created.
EXPLAIN PLAN 4 : (after indexing cost reduced drastically)
SQL> EXPLAIN PLAN SET STATEMENT_ID='GD_ISSUE3' FOR
select * from GDCWODS.GD_INVOICE_HEADER IH where SYSTEM_ID='NA' AND BILL_TO_PARTY='500818'
AND EXISTS (SELECT MAT_NUMBER FROM GDCWODS.GD_INVOICE_DETAIL WHERE DOCNUM=IH.DOCNUM AND
MAT_NUMBER ='FA10003') ;
2
Explained.
SQL> @?/rdbms/admin/utlxpls GD_ISSUE3
Plan Table
--------------------------------------------------------------------------------
Operation Name Rows Bytes Cost Pstart Pstop
--------------------------------------------------------------------------------
SELECT STATEMENT 47 20K 1632
FILTER
TABLE ACCESS FULL GD_INVOIC 47 20K 1632
TABLE ACCESS BY INDEX ROGD_INVOIC 1 20 2
INDEX RANGE SCAN GD_INV_DE 1 1
--------------------------------------------------------------------------------
8 rows selected.
Note: For 16lakhs records will take at least 5-6hrs and 25thousand record will take 1-2hrs depeding on cpu, memory, colums, etc so analyze will be taken in off peak hours
Application team was worried as query which was suppose to take 2-3 minutes was taking more than 30 mins to get executed.
Last analyzed is more than 3 months old
SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from DBA_TABLES WHERE
TABLE_NAME='GD_INVOICE_HEADER';
OWNER TABLE_NAME LAST_ANAL------------------------------ ------------------------------ ---------GDCWODS GD_INVOICE_HEADER 12-JUL-09
SQL> select count(*) from GDCWODS.GD_INVOICE_DETAIL;
COUNT(*)
----------
1605055
SQL> select count(*) from GDCWODS.GD_INVOICE_HEADER;
COUNT(*)
----------
250578
Create Explain Plan Table:
SQL> @?/rdbms/admin/utlxplan.sql
Explain Plan for the query:
SQL> EXPLAIN PLAN SET STATEMENT_ID='GD_ISSUE' FOR
select * from GDCWODS.GD_INVOICE_HEADER IH where SYSTEM_ID='NA' AND BILL_TO_PARTY='500818'
AND EXISTS (SELECT MAT_NUMBER FROM GDCWODS.GD_INVOICE_DETAIL WHERE DOCNUM=IH.DOCNUM AND
MAT_NUMBER ='FA10003') ;
Explained.
SQL> @?/rdbms/admin/utlxpls GD_ISSUE
Plan Table
--------------------------------------------------------------------------------
Operation Name Rows Bytes Cost Pstart Pstop
--------------------------------------------------------------------------------
SELECT STATEMENT 47 20K 1632
FILTER
TABLE ACCESS FULL GD_INVOIC 47 20K 1632
TABLE ACCESS FULL GD_INVOIC 1 20 1769
--------------------------------------------------------------------------------
Columns which are Indexed earlier:
SQL> select INDEX_NAME, TABLE_NAME, COLUMN_NAME from dba_ind_columns where
TABLE_NAME='GD_INVOICE_HEADER' and TABLE_OWNER='GDCWODS';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------------------
---------
GD_INV_HDR_IDX1 GD_INVOICE_HEADER
SYSTEM_ID
GD_INV_HDR_IDX1 GD_INVOICE_HEADER
DOCNUM
SQL> select INDEX_NAME, TABLE_NAME, COLUMN_NAME from dba_ind_columns where
TABLE_NAME='GD_INVOICE_DETAIL' and TABLE_OWNER='GDCWODS';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------------------
---------
GD_INV_DET_IDX1 GD_INVOICE_DETAIL
SYSTEM_ID
GD_INV_DET_IDX1 GD_INVOICE_DETAIL
DOCNUM
GD_INV_DET_IDX1 GD_INVOICE_DETAIL
SEQNUM
EXPLAIN 2:( no change is cost)
SQL> EXPLAIN PLAN SET STATEMENT_ID='GD_ISSUE1' FOR
select * from GDCWODS.GD_INVOICE_HEADER IH
where SYSTEM_ID='NA' AND BILL_TO_PARTY='500818'
AND EXISTS (SELECT 1 FROM GDCWODS.GD_INVOICE_DETAIL WHERE DOCNUM=IH.DOCNUM AND MAT_NUMBER
='FA10003') ; 2 3 4
Explained.
SQL> @?/rdbms/admin/utlxpls GD_ISSUE1
Plan Table
--------------------------------------------------------------------------------
Operation Name Rows Bytes Cost Pstart Pstop
--------------------------------------------------------------------------------
SELECT STATEMENT 47 20K 1632
FILTER
TABLE ACCESS FULL GD_INVOIC 47 20K 1632
TABLE ACCESS FULL GD_INVOIC 1 20 1769
--------------------------------------------------------------------------------
7 rows selected.
EXPLAIN 3 using Hint: (cost got increased)
/// USING HINT ///
SQL> EXPLAIN PLAN SET STATEMENT_ID='GD_ISSUE2' FOR
2 select * from GDCWODS.GD_INVOICE_HEADER IH where SYSTEM_ID='NA' AND
BILL_TO_PARTY='500818' AND EXISTS (SELECT /*+ INDEX(GD_INVOICE_DETAIL GD_INV_DET_IDX1) */
MAT_NUMBER FROM GDCWODS.GD_INVOICE_DETAIL WHERE DOCNUM=IH.DOCNUM AND MAT_NUMBER ='FA10003')
;
Explained.
SQL> @?/rdbms/admin/utlxpls GD_ISSUE2
Plan Table
--------------------------------------------------------------------------------
Operation Name Rows Bytes Cost Pstart Pstop
--------------------------------------------------------------------------------
SELECT STATEMENT 47 20K 1632
FILTER
TABLE ACCESS FULL GD_INVOIC 47 20K 1632
TABLE ACCESS BY INDEX ROGD_INVOIC 1 20 5262
INDEX FULL SCAN GD_INV_DE 1 5261
--------------------------------------------------------------------------------
8 rows selected.
Created Index on MAT_NUMBER:
SQL> create index GD_INV_DET_MAT_NUM_IDX1 on GDCWODS.GD_INVOICE_DETAIL(MAT_NUMBER);
Index created.
EXPLAIN PLAN 4 : (after indexing cost reduced drastically)
SQL> EXPLAIN PLAN SET STATEMENT_ID='GD_ISSUE3' FOR
select * from GDCWODS.GD_INVOICE_HEADER IH where SYSTEM_ID='NA' AND BILL_TO_PARTY='500818'
AND EXISTS (SELECT MAT_NUMBER FROM GDCWODS.GD_INVOICE_DETAIL WHERE DOCNUM=IH.DOCNUM AND
MAT_NUMBER ='FA10003') ;
2
Explained.
SQL> @?/rdbms/admin/utlxpls GD_ISSUE3
Plan Table
--------------------------------------------------------------------------------
Operation Name Rows Bytes Cost Pstart Pstop
--------------------------------------------------------------------------------
SELECT STATEMENT 47 20K 1632
FILTER
TABLE ACCESS FULL GD_INVOIC 47 20K 1632
TABLE ACCESS BY INDEX ROGD_INVOIC 1 20 2
INDEX RANGE SCAN GD_INV_DE 1 1
--------------------------------------------------------------------------------
8 rows selected.
Note: For 16lakhs records will take at least 5-6hrs and 25thousand record will take 1-2hrs depeding on cpu, memory, colums, etc so analyze will be taken in off peak hours
Monday, October 12, 2009
coalesce
Coalescing the tablespace will NEVER increase the the amount of free space.
It is used on dictionary managed tablespaces to coalesce two or more adjacent FREE extents into one - it will NOT create new free ones.
It is used on dictionary managed tablespaces to coalesce two or more adjacent FREE extents into one - it will NOT create new free ones.
DP RMAN Script for Archivelog backup
DP-RMAN Script for Archivelog backup
run
{allocate channel 'dev_0' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=Database,OB2BARLIST=Server_Database_RMAN_ARC_ST_RA)'; sql 'alter system switch logfile';
crosscheck archivelog all;
backup format 'Server_Database_RMAN_ARC_ST_RA.arc'
archivelog all delete input;
}
*Crosscheck archivelog all was added to avoid backup failure due to missing archive files.
run
{allocate channel 'dev_0' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=Database,OB2BARLIST=Server_Database_RMAN_ARC_ST_RA)'; sql 'alter system switch logfile';
crosscheck archivelog all;
backup format 'Server_Database_RMAN_ARC_ST_RA
}
*Crosscheck archivelog all was added to avoid backup failure due to missing archive files.
Subscribe to:
Posts (Atom)