tag:blogger.com,1999:blog-62126363740454427822024-02-19T03:38:30.899+02:00ORACLE DBAHints & Tips for Oracle Databases
and Oracle E-Business SuiteLefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.comBlogger90125tag:blogger.com,1999:blog-6212636374045442782.post-50633178490373292542023-12-27T12:22:00.001+02:002023-12-27T14:18:26.824+02:00APEX application to display tablespace usage of databases registered in an Oracle Enterprise Manager installation<p> </p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZlye5V01GfjS9qe0aggzBwED5byUYfMz_DsfsHZV0XaLMknSF4Ek-1bAbxVu8zxuVC_8aB3B8urODS7JTl1ZE9ljudp9ZAVVNIw8Y15w2HShXFfhEd3d_AL-YPoZRegqpnPgkxa4pbAsOWkPgaym2KRHPd4E-sOFVYwBJfG4EP3-hdnv9opUXNh9f2YE/s2529/tbs_usage_app.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1239" data-original-width="2529" height="157" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZlye5V01GfjS9qe0aggzBwED5byUYfMz_DsfsHZV0XaLMknSF4Ek-1bAbxVu8zxuVC_8aB3B8urODS7JTl1ZE9ljudp9ZAVVNIw8Y15w2HShXFfhEd3d_AL-YPoZRegqpnPgkxa4pbAsOWkPgaym2KRHPd4E-sOFVYwBJfG4EP3-hdnv9opUXNh9f2YE/s320/tbs_usage_app.jpg" width="320" /></a></div><br /><p></p><p>This is an APEX application (created with APEX 23.2) to present tablespace usage data, retrieved from your OEM 13c. It uses <i>spaceUsed</i>, <i>bytesFree </i>and <i>pctUsed </i>metrics to display graphs about <i>Used (GB)</i> space, <i>Free (GB)</i> space and <i>Used (%)</i>.</p><p>First of all, you will need to create three views under your APEX workspace schema (<a href="https://drive.google.com/file/d/1FQegzMkjjRrofs9GNwXa7XCPqsf93S8V/view?usp=drivesdk" target="_blank">tbs_usage_objs.sql</a>). If your APEX installation lies in the same database with your OEM installation, you will need to give to your APEX workspace schema/user the necessary SELECT privileges on the appropriate SYSMAN objects (MGMT$TARGET_TYPE, MGMT$METRIC_DAILY, etc). If your APEX installation is in another database than your OEM installation, then create a database link from your APEX workspace schema/user to the OEM's SYSMAN and make the appropriate changes to the views and procedure's definitions.</p><p>Finally, import in your APEX workspace the application (<a href="https://drive.google.com/file/d/1MKG-3A1_DIDxoaLmBPTcmJGqKsrvsCiJ/view?usp=drivesdk" target="_blank">f1002.sql</a>).</p>Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com0tag:blogger.com,1999:blog-6212636374045442782.post-68021441927782981032014-11-18T11:23:00.000+02:002014-11-18T11:26:55.669+02:00Shrink a datafile, when ALTER TABLESPACE COALESCE is not working.In a 11.2.0.3 database, we tried to reclaim space from USERS tablespace.<br />
We faced a problem.<br />
We use the following query to the the end of a particular USERS' datafile [file_id=72, filename=/oradb/SBLCCPRD/oradata4/users03.dbf]<br />
<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT
A.FILE_ID,
A.BLOCK_ID,
A.BLOCK_ID + A.BLOCKS - 1 END_BLOCK,
A.BLOCKS,
--ROUND(A.BLOCKS*B.BLOCK_SIZE/1024/1024,2) MBYTES,
A.BLOCKS*B.BLOCK_SIZE "SIZE",
A.OWNER,
A.SEGMENT_NAME,
A.PARTITION_NAME,
A.SEGMENT_TYPE
FROM
DBA_EXTENTS A, DBA_TABLESPACES B
WHERE
A.FILE_ID=:A AND A.TABLESPACE_NAME=B.TABLESPACE_NAME
UNION ALL
SELECT
A.FILE_ID,
A.BLOCK_ID,
A.BLOCK_ID + A.BLOCKS - 1 END_BLOCK,
A.BLOCKS,
--ROUND(A.BLOCKS*B.BLOCK_SIZE/1024/1024,2) MBYTES,
A.BLOCKS*B.BLOCK_SIZE "SIZE",
'free' OWNER,
'free' SEGMENT_NAME,
NULL PARTITION_NAME,
NULL SEGMENT_TYPE
FROM
DBA_FREE_SPACE A, DBA_TABLESPACES B
WHERE
A.FILE_ID=:A AND A.TABLESPACE_NAME=B.TABLESPACE_NAME
ORDER BY 2 DESC;</code></pre>
<br />
<br />
We can see there were 5 contiguous free extents of 64 Kbytes each, total 320 Kbytes.<br />
<br />
<br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4; width: 400px;"><tbody>
<tr><td>FILE_ID</td><td>BLOCK_ID</td><td>END_BLOCK</td><td>BLOCKS</td><td>SIZE</td><td>OWNER</td><td>SEGMENT_NAME</td><td>PARTITION_NAME</td><td>SEGMENT_TYPE</td></tr>
<tr><td>72</td><td>1614952</td><td>1614959</td><td>8</td><td>65536</td><td>free</td><td>free</td><td></td><td></td></tr>
<tr><td>72</td><td>1614944</td><td>1614951</td><td>8</td><td>65536</td><td>free</td><td>free</td><td></td><td></td></tr>
<tr><td>72</td><td>1614936</td><td>1614943</td><td>8</td><td>65536</td><td>free</td><td>free</td><td></td><td></td></tr>
<tr><td>72</td><td>1614928</td><td>1614935</td><td>8</td><td>65536</td><td>free</td><td>free</td><td></td><td></td></tr>
<tr><td>72</td><td>1614920</td><td>1614927</td><td>8</td><td>65536</td><td>free</td><td>free</td><td></td><td></td></tr>
<tr><td>72</td><td>1614912</td><td>1614919</td><td>8</td><td>65536</td><td>AMARGARI</td><td>CX_OTE_LOV_BACKUP_06052014</td><td></td><td>TABLE</td></tr>
<tr><td>72</td><td>1614904</td><td>1614911</td><td>8</td><td>65536</td><td>AMARGARI</td><td>CX_OTE_LOV_BACKUP_06052014</td><td></td><td>TABLE</td></tr>
<tr><td>72</td><td>1614896</td><td>1614903</td><td>8</td><td>65536</td><td>EFOTEINOP</td><td>CX_OTE_LOV_0106</td><td></td><td>TABLE</td></tr>
<tr><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td></tr>
</tbody></table>
<br />
<br />
We tried to coalesce the tablespace, but it did not. The free extents did not get merged to one of 320 Kbytes.<br />
<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> alter tablespace users coalesce;
Tablespace altered.
</code></pre>
<br />
<br />
This is the datafile's size in bytes:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT FILE_NAME, BYTES
FROM DBA_DATA_FILES
WHERE FILE_ID=72;</code></pre>
<br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4; width: 400px;"><tbody>
<tr><td>FILE_NAME</td><td>BYTES</td></tr>
<tr><td>/oradb/SBLCCPRD/oradata4/users03.dbf</td><td>13229744128</td></tr>
</tbody></table>
<br />
<br />
When we tried to shrink the datafile, even by just 8 Kbytes, we got ORA-03297:<br />
<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> ALTER DATABASE DATAFILE 72 RESIZE 13229735936;
ALTER DATABASE DATAFILE 72 RESIZE 13229735936
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value</code></pre>
<br />
<br />
The solution was to purge the tablespace's recycle bin, even if there were no recycled objects near the end of the datafile. Furthermore, before I begin all this procedure, I had purged the database's recycle bin, using "PURGE DBA_RECYCLEBIN".<br />
<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> PURGE TABLESPACE USERS;
Tablespace purged.
SQL> ALTER DATABASE DATAFILE 72 RESIZE 13229735936;
Database altered.</code></pre>
<br />
<br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4; width: 400px;"><tbody>
<tr><td>FILE_ID</td><td>BLOCK_ID</td><td>END_BLOCK</td><td>BLOCKS</td><td>SIZE</td><td>OWNER</td><td>SEGMENT_NAME</td><td>PARTITION_NAME</td><td>SEGMENT_TYPE</td></tr>
<tr><td>72</td><td>1614920</td><td>1614951</td><td>32</td><td>262144</td><td>free</td><td>free</td><td></td><td></td></tr>
<tr><td>72</td><td>1614912</td><td>1614919</td><td>8</td><td>65536</td><td>AMARGARI</td><td>CX_OTE_LOV_BACKUP_06052014</td><td></td><td>TABLE</td></tr>
<tr><td>72</td><td>1614904</td><td>1614911</td><td>8</td><td>65536</td><td>AMARGARI</td><td>CX_OTE_LOV_BACKUP_06052014</td><td></td><td>TABLE</td></tr>
<tr><td>72</td><td>1614896</td><td>1614903</td><td>8</td><td>65536</td><td>EFOTEINOP</td><td>CX_OTE_LOV_0106</td><td></td><td>TABLE</td></tr>
<tr><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td></tr>
</tbody></table>
Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com0tag:blogger.com,1999:blog-6212636374045442782.post-54764300572400865002014-08-04T22:28:00.001+03:002014-08-04T22:39:44.694+03:00ORA-01653, ORA-01658, ORA-01688, ORA-03233 even when there is free space in the tablespace.<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: #100010; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>ORA-01653: Unable to extend table %s.%s by string in tablespace %s
ORA-01658: Unable to create INITIAL extent for segment in tablespace %s
ORA-01688: Unable to extend table %s.%s partition %s by %s in tablespace %s
ORA-03233: Unable to extend table %s.%s subpartition %s by %s in tablespace %s
ORA-01654: Unable to extend index %s.%s by %s in tablespace %s
ORA-01683: Unable to extend index %s.%s partition %s by %s in tablespace %s
ORA-03234: Unable to extend index %s.%s subpartition %s by %s in tablespace %s</code></pre>
<br />
You may get the above errors, even if the is a lot of free space in the tablespace of the segment to be extended. This is because that free space is very fragmented.<br />
When a segment asks for a new extent, the database will try to fit it in an equal or larger free extent. It will not fit it in more than one smaller free extents.<br />
So, for instance, it the next extent to be created will be 2 MB and the largest available free extent is 1MB, you will get the above errors, even if the tablespace has gigabytes of total free space.<br />
<br />
To check if this is the case, run the following query, which displays the free extents of a tablespace ordered by their size descending.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: #100010; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT C.NAME "DATAFILE NAME",
A.FILE_ID "DATAFILE #",
A.BLOCK_ID "BLOCK #",
A.BLOCKS,
ROUND(A.BLOCKS*B.BLOCK_SIZE/1024/1024,2) "MBYTES"
FROM
DBA_FREE_SPACE A,
DBA_TABLESPACES B,
V$DATAFILE C
WHERE
A.TABLESPACE_NAME = '[TABLESPACE NAME]'
AND A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND C.FILE# = A.FILE_ID
ORDER BY 4 DESC;</code></pre>
<br />
If this is the case, you need to create larger free extents by reorganizing the tablespace or to add a new datafile, which at the moment it is created is a continuous free extent by itself.Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com0tag:blogger.com,1999:blog-6212636374045442782.post-67456523091370227882013-09-15T21:24:00.000+03:002013-09-15T21:31:53.365+03:00Use DBMS_SQLTUNE.IMPORT_SQL_PROFILE to force a hint to a queryDBMS_SQLTUNE.IMPORT_SQL_PROFILE is actually the procedure used when you implement a SQL Tuning Advisor's recommendation involving a better execution plan. But the SQL Tuning Advisor may not recommend the execution plan you know is the correct one or may even not recommend a new execution plan. If you know the hint that will improve your query's performance, then you may want to use DBMS_SQLTUNE.IMPORT_SQL_PROFILE to force it.<br />
<br />
Let's create a scenario. Firstly, create 2 tables and 1 index in each one.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>sqlplus system
SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 15 20:32:10 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
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> CREATE TABLE SYSTEM.OBJECTS AS SELECT * FROM DBA_OBJECTS;
Table created.
SQL> CREATE INDEX SYSTEM.OBJ_NAME ON SYSTEM.OBJECTS(OBJECT_NAME);
Index created.
SQL> CREATE TABLE SYSTEM.SEGMENTS AS SELECT * FROM DBA_SEGMENTS;
Table created.
SQL> CREATE INDEX SYSTEM.SEG_NAME ON SYSTEM.SEGMENTS(SEGMENT_NAME);
Index created.</code></pre>
<br />
We will use a query that joins those tables using the indexed columns.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> SET AUTOT TRACEONLY
SQL> SET TIMING ON
SQL> SET LINESIZE 200
SQL> SELECT OBJECT_ID,
OBJECT_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME
FROM SYSTEM.OBJECTS, SYSTEM.SEGMENTS
WHERE OBJECT_NAME = SEGMENT_NAME AND SEGMENT_NAME LIKE 'DBA%';
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 98178177
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 895 | 130K| 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 895 | 130K| 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| SEGMENTS | 784 | 54880 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SEG_NAME | 141 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| OBJECTS | 4970 | 383K| 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | OBJ_NAME | 895 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"="SEGMENT_NAME")
3 - access("SEGMENT_NAME" LIKE 'DBA%')
filter("SEGMENT_NAME" LIKE 'DBA%')
5 - access("OBJECT_NAME" LIKE 'DBA%')
filter("OBJECT_NAME" LIKE 'DBA%')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
892 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed</code></pre>
<br />
Now, let's find our query's SQL_ID.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> SELECT SQL_ID, SQL_TEXT
FROM V$SQLAREA
WHERE SQL_TEXT LIKE 'SELECT OBJECT_ID%';</code></pre>
<br />
<table border="1" bordercolor="#000000" cellpadding="2" cellspacing="3" style="background-color: #d4d4d4; width: 400%px;"><tbody>
<tr><td>SQL_ID</td><td>SQL_TEXT</td></tr>
<tr><td>gy6fj888vt27y</td><td>SELECT OBJECT_ID, OBJECT_NAME, SEGMENT_TYPE, TABLESPACE_NAME FROM
SYSTEM.OBJECTS, SYSTEM.SEGMENTS WHERE OBJECT_NAME = SEGMENT_NAME AND
SEGMENT_NAME LIKE 'DBA%'</td></tr>
</tbody></table>
<br />
Both indexes are used, but let's assume we want to force full table scans on both tables.<br />
<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> DECLARE
2 SQL_FTEXT CLOB;
BEGIN
SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = 'gy6fj888vt27y';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
SQL_TEXT => SQL_FTEXT,
PROFILE => SQLPROF_ATTR('FULL(@"SEL$1" "OBJECTS"@"SEL$1") FULL(@"SEL$1" "SEGMENTS"@"SEL$1")'),
NAME => 'PROFILE_gy6fj888vt27y',
REPLACE => TRUE,
FORCE_MATCH => TRUE
);
END;
/
PL/SQL procedure successfully completed.</code></pre>
<br />
Let's see the new execution plan.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>Execution Plan
----------------------------------------------------------
Plan hash value: 1823853794 <b>[New plan hash value]</b>
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 895 | 130K| 388 (2)| 00:00:05 |
|* 1 | HASH JOIN | | 895 | 130K| 388 (2)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| SEGMENTS | 784 | 54880 | 53 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| OBJECTS | 4970 | 383K| 334 (1)| 00:00:05 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"="SEGMENT_NAME")
2 - filter("SEGMENT_NAME" LIKE 'DBA%')
3 - filter("OBJECT_NAME" LIKE 'DBA%')
Note
-----
- SQL profile "PROFILE_gy6fj888vt27y" used for this statement <b>[The SQL profile we created, is used]</b>
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1419 consistent gets
192 physical reads
0 redo size
892 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed</code></pre>
<br />
You've may noticed the hint's format.<br />
<i>/*+ FULL(OBJECTS) FULL(SEGMENTS) */</i> is equal to <i>'FULL(@"SEL$1" "OBJECTS"@"SEL$1") FULL(@"SEL$1" "SEGMENTS"@"SEL$1")'</i>.<br />
Similarly, <i>/*+ INDEX(OBJECTS OBJ_NAME) */</i> is equal to <i>'INDEX(@"SEL$1" "OBJECTS"@"SEL$1" "OBJ_NAME")'.</i>Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com4tag:blogger.com,1999:blog-6212636374045442782.post-19965997377355625732013-06-04T11:21:00.002+03:002013-06-19T10:29:08.131+03:00Upgrade from OEM 11g to 12c: Step DeployAgentNotRequired ignores agent deploymentWe created a new OEM 12c [12.1.0.2.2], using the 2-system upgrade approach from our OEM 11g.<br />
<br />
I tried to run the "Deploy and Configure Agent" step on 2 servers A and B and the installation proceeded only to server A. For server B, I was getting a "DeployAgentNotRequired" step with output "Ignoring deployment".<br />
<br />
The problem was that there is wrong information in the repository and OEM considered some agents as NFS shared, even though they were not.<br />
<br />
Execute the following query as SYSMAN in your OEM 11g repository database and identify which of those agents considered as NFS shared, actually are not.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT TARGET_NAME, MASTER_AGENT_GUID, TARGET_GUID, IS_SHARED
FROM SYSMAN.PRE_UPGC_AGT_STAT_MGMT
WHERE IS_SHARED=1;</code></pre>
<br />
Keep the TARGET_NAME of those agents (for our example "B:3872") and set IS_SHARED=0 for them:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>UPDATE SYSMAN.PRE_UPGC_AGT_STAT_MGMT
SET IS_SHARED = '0', MASTER_AGENT_GUID=TARGET_GUID
WHERE TARGET_NAME='B:3872';
COMMIT;</code></pre>
<br />
Also, check their predeployment status [PRE_DEP_STATUS column] and if the value is STATUS_SUCCESS, change it to STATUS_FAILED:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT TARGET_NAME, PRE_DEP_STATUS
FROM SYSMAN.PRE_UPGC_AGT_STAT_MGMT
WHERE TARGET_NAME = 'B:3872';
UPDATE SYSMAN.PRE_UPGC_AGT_STAT_MGMT
SET PRE_DEP_STATUS='STATUS_FAILED'
WHERE TARGET_NAME = 'B:3872';
COMMIT;</code></pre>
<br />
Now, retry the "Deploy and Configure Agent" step for those servers.Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com0tag:blogger.com,1999:blog-6212636374045442782.post-35981400977179494052013-04-24T09:53:00.000+03:002013-04-24T10:20:49.090+03:00Upgrade from OEM 11g to 12c: Step unzipAndDeploySoftware failsWe created a new OEM 12c [12.1.0.2.2], using the 2-system upgrade approach from our OEM 11g.<br />
<br />
I tried to run the "Deploy and Configure Agent" step on a Linux 64-bit machine and unzipAndDeploySoftware step failed with error:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>Check complete: Failed <<<<
Problem: The 32bit software cannot be installed on a 64 bit platform. Similarly the vice-versa is also true where 64bit product cannot be installed on a 32bit machine.
Recommendation: Please install the software on a 32-bit machine if the product is 32bit, otherwise install the product on the 64bit machine if it is a 64bit software.</code></pre>
<br />
This is Bug 15995299.<br />
The problem is the OS has been discovered as a Linux 32-bit by the upgrade process.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT TARGET_NAME, PLATFORM_ID, PLATFORM_NAME, CURRENT_VERSION
FROM SYSMAN.PRE_UPGC_AGT_STAT_MGMT
WHERE TARGET_NAME='your_server:3872';</code></pre>
<br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4; width: 400%px;"><tbody>
<tr><td>TARGET_NAME</td><td>PLATFORM_ID</td><td>PLATFORM_NAME</td><td>CURRENT_VERSION</td></tr>
<tr><td>your_server:3872</td><td>46</td><td>Linux x86</td><td>11.1.0.1.0</td></tr>
</tbody></table>
<br />
Also, the targets which are monitored by that agent have wrong information:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT EMD_URL, AGENT_NAME, TARGET_NAME, TARGET_TYPE, PLATFORM_ID, PLATFORM_NAME
FROM SYSMAN.PRE_UPGC_TGT_SW
WHERE AGENT_NAME='your_server:3872';</code></pre>
<br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4; width: 400%px;"><tbody>
<tr><td>EMD_URL</td><td>AGENT_NAME</td><td>TARGET_NAME</td><td>TARGET_TYPE</td><td>PLATFORM_ID</td><td>PLATFORM_NAME</td></tr>
<tr><td>https://your_server:3872/emd/main/</td><td>your_server:3872</td><td>your_server:3872</td><td>oracle_emd</td><td>46</td><td>Linux x86</td></tr>
<tr><td>https://your_server:3872/emd/main/</td><td>your_server:3872</td><td>your_server</td><td>host</td><td>46</td><td>Linux x86</td></tr>
</tbody></table>
<br />
You have to update these tables with the correct OS information:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>UPDATE SYSMAN.PRE_UPGC_AGT_STAT_MGMT
SET PLATFORM_ID='226', PLATFORM_NAME='Linux x86-64'
WHERE TARGET_NAME ='your_server:3872';
UPDATE SYSMAN.PRE_UPGC_TGT_SW SW
SET (PLATFORM_ID,PLATFORM_NAME,OS_VERSION ) =
(SELECT DISTINCT AGT.PLATFORM_ID,AGT.PLATFORM_NAME,AGT.OS_VERSION
FROM SYSMAN.PRE_UPGC_AGT_STAT_MGMT AGT, SYSMAN.PRE_UPGC_TGT_SW TGT
WHERE TGT.TARGET_GUID=AGT.TARGET_GUID AND SW.EMD_URL=TGT.EMD_URL);
COMMIT;</code></pre>
<br />
Rerun the "Deploy and Configure Agent" step for the particular agent.<br />
<br />
You may also check Oracle Note 1519366.1: EM 12c: PreUpgrade Console shows Linux 64-bit Host As 32-bit for RHEL / OEL 5 64-bit servers. Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com0tag:blogger.com,1999:blog-6212636374045442782.post-58386984173787090172013-04-19T09:51:00.000+03:002013-04-19T10:03:24.183+03:00Upgrade from OEM 11g to 12c: EM Console Service & EM Jobs Service have status Pending, Database System discovered had no members and status Pending.We created a new OEM 12c [12.1.0.2.2], using the 2-system upgrade approach from our OEM 11g.<br />
The first agent I installed, in the machine where I installed OMS and repository database had every target discovered with status Up, except the Database System, which had status Pending.<br />
Also, EM Console Service & EM Jobs Service had status Pending.<br />
<br />
Strangely, both issues had the same solution.<br />
Although, in our old 11g repository database the parameter job_queue_processes had a value of 1000, some where in the upgrade process, in the new 12c repository database this changed to 0.<br />
Probably, that happened during the database upgrade from 11.2.0.2 to 11.2.0.3 using dbua.<br />
<br />
So the solution was:<br />
Run as SYS:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> show parameter job_queue_processes
NAME TYPE VALUE
------------------- -------- -----
job_queue_processes integer 0
SQL> ALTER SYSTEM SET job_queue_processes=1000 SCOPE=BOTH;
System altered.
SQL> show parameter job_queue_processes
</code><code><code>NAME TYPE VALUE
------------------- -------- -----</code>
job_queue_processes integer 1000</code></pre>
<br />
<br />
Run as SYSMAN:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> exec emd_maintenance.remove_em_dbms_jobs;
PL/SQL procedure successfully completed.
SQL> exec emd_maintenance.submit_em_dbms_jobs;
PL/SQL procedure successfully completed.</code></pre>
Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com0tag:blogger.com,1999:blog-6212636374045442782.post-43456813653101183462013-04-10T19:43:00.001+03:002013-04-10T19:43:48.285+03:00Upgrade from OEM 11g to 12c: ORA-06532 during repository upgradeIn our 2-system upgrade, during the repository upgrade step, we got an ORA-06532 error:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>2013-04-08 13:19:07,497 [Thread-514] ERROR - ERROR:ORA-06532: Subscript outside of limit
ORA-06512: at "SYSMAN.NOTIFICATION_MIGRATION_HELPER", line 576
ORA-06512: at "SYSMAN.NOTIFICATION_MIGRATION_HELPER", line 898
ORA-06512: at "SYSMAN.NOTIFICATION_MIGRATION_HELPER", line 1686
ORA-06512: at "SYSMAN.NOTIFICATION_MIGRATION_HELPER", line 2526
ORA-06512: at line 5
File:/oms/sysman/admin/emdrep/sql/core/11.2.0.1/notification/em_notification_data_upgrade.sql
Statement:DECLARE
l_upgrade_type VARCHAR2(64);
BEGIN
-- Migrate notification rules
NOTIFICATION_MIGRATION_HELPER.MIGRATE_RULES(p_batch_size => 50);
-- Find the upgrade type
l_upgrade_type := em_upgsoln_util.get_upgrade_property(EM_UPGSOLN_UTIL.G_UPGRADE_TYPE);
-- Perform pending violation specific upgrade for 1-system upgrade
IF l_upgrade_type IS NOT NULL AND
l_upgrade_type = EM_UPGSOLN_UTIL.G_UPG_ONE_SYSTEM
THEN
-- Migrate pending violations from aq$mgmt_notify_qtable
NOTIFICATION_MIGRATION_HELPER.MIGRATE_DELIVERY_BUS_DATA(p_batch_size => 25);
-- Migrate pending violations from mgmt_notify_requeue table
NOTIFICATION_MIGRATION_HELPER.MIGRATE_REQUEUE_DATA(p_batch_size => 25);
-- Migrate pending rule engine entries from aq$mgmt_notify_input_qtable
NOTIFICATION_MIGRATION_HELPER.MIGRATE_RULE_BUS_DATA(p_batch_size => 25);
END IF;
END;</code></pre>
<br />
<span style="text-align: left; width: 575px;">Bug 15904251 : UPGRADE OF 11G NOTIFICATION RULES FAIL FOR RULE WITH LARGE NUMEBRS OF CONDITIONS was relevant, but applying it, did not solve the issue.</span><br />
<span style="text-align: left; width: 575px;">post_config.sh failed, with error: "</span><span style="text-align: left; width: 575px;">No OMS is found to be associated with the current Oracle Home"</span><br />
<br />
<span style="text-align: left; width: 575px;">To continue, first, run these commands as SYSMAN to the new repository database:</span><br />
<span style="text-align: left; width: 575px;"></span><br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><span style="text-align: left; width: 575px;"><code>ALTER TYPE EM_RULE_EXPRESSION_ARRAY MODIFY LIMIT 100000 CASCADE;
ALTER TYPE EM_RULE_DETAILS_OBJ compile;
ALTER TYPE EM_RULE_SET_DETAILS_OBJ compile;
ALTER TYPE EM_RULE_COND_ACTIONS_OBJ compile;
ALTER TYPE EM_RULE_DETAILS_OBJ compile;
ALTER TYPE EM_RULE_DETAILS_OBJ compile;
ALTER TYPE EM_RULE_DETAILS_OBJ compile;
ALTER TYPE EM_RULE_COND_ACTIONS_OBJ compile;
ALTER TYPE EM_RULE_SET_DETAILS_OBJ compile;
ALTER TYPE EM_RULE_COND_ACTIONS_OBJ compile;
ALTER TYPE EM_RULE_COND_ACTIONS_OBJ compile;
ALTER TYPE EM_RULE_SET_DETAILS_OBJ compile;
ALTER TYPE EM_RULE_DETAILS_OBJ compile;
ALTER TYPE EM_RULE_SET_DETAILS_OBJ compile;
ALTER TYPE EM_RULE_COND_ACTIONS_OBJ compile body;
ALTER TYPE EM_RULE_DETAILS_OBJ compile body;
ALTER TYPE EM_RULE_SET_DETAILS_OBJ compile body;
ALTER PACKAGE EM_RULE_BAM_INTG compile;
ALTER PACKAGE EM_INCIDENT_RULES compile;
ALTER PACKAGE MGMT_RCA compile;
ALTER PACKAGE NOTIFICATION_MIGRATION_HELPER compile;
ALTER PACKAGE EM_RULE_BAM_INTG compile body;
ALTER PACKAGE EM_INCIDENT_RULES compile body;
ALTER PACKAGE MGMT_RCA compile body;
ALTER PACKAGE NOTIFICATION_MIGRATION_HELPER compile body;</code></span></pre>
<span style="text-align: left; width: 575px;">
</span><br />
<span style="text-align: left; width: 575px;"><br /></span>
<span style="text-align: left; width: 575px;">After that, run:</span><br />
<span style="text-align: left; width: 575px;"></span><br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><span style="text-align: left; width: 575px;"><code>$OMS_HOME/oui/bin/runConfig.sh ORACLE_HOME=$OMS_HOME ACTION=configure MODE=perform</code></span></pre>
<span style="text-align: left; width: 575px;">
</span><br />
<span style="text-align: left; width: 575px;"><br /></span>
<span style="text-align: left; width: 575px;">At the end, your OMS processes will be running ["emctl status oms" to verify] and you may login to your new 12c OEM.</span>Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com0tag:blogger.com,1999:blog-6212636374045442782.post-12052314976943936502013-04-01T22:35:00.003+03:002013-04-05T08:42:11.965+03:00Upgrade from OEM 11g to 12c: Clustered agents reported as "agents with Invalid Inventory"We are in the process of upgrading our OEM 11g installation to 12c.<br />
In the "Upgrade Console", agents installed to monitor a RAC were reported as "agents with Invalid Inventory".<br />
These agents were installed using <a href="http://lefterhs.blogspot.gr/2011/07/oracle-enterprise-manager-11g-111010.html" target="_blank">this</a> method, in a custom Oracle inventory.<br />
Additionally, from the "Setup", "Agents" tab, you can see a particular agent's page. There, by pressing the "<span class="x8">Oracle Home" link, I was getting the following error:</span><br />
<span class="x8"></span><br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><span class="x8"><code>Could not find Oracle Home <ORACLE_HOME> in the inventory collected for <HOSTNAME> on <TIMESTAMP></code></span></pre>
<span class="x8">
</span><br />
<span class="x8"><br /></span>
<span class="x8">As described in Oracle note </span><span style="text-align: left; width: 575px;">735952.1 - "Error Could not find Oracle Home <oracle_home> in the inventory collected for <hostname> on <timestamp>", although some changes were necessary, to fix this problem:</timestamp></hostname></oracle_home></span><br />
<ul>
<li><span style="text-align: left; width: 575px;">Edit $AGENT_HOME/<hostname>sysman/config/OUIinventories.add</hostname></span></li>
<li><span style="text-align: left; width: 575px;">Put an entry for your custom oraInst.loc, for example: inventory: /var/opt/oragrid/oraInst.loc</span></li>
<li><span style="text-align: left; width: 575px;">Restart the agent</span></li>
<li><span style="text-align: left; width: 575px;">From the OEM, go to the particular host's page, "Configuration" tab and press the "Refresh Host Configuration" button.</span></li>
<li><span style="text-align: left; width: 575px;">Wait for it to complete and then go to your </span>"Upgrade Console" and press the "Refresh Agents and Targets List" button.</li>
</ul>
Now, those agents are removed from the "agents with Invalid Inventory" list.<br />
<span class="x8"><br /></span>Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com0tag:blogger.com,1999:blog-6212636374045442782.post-40651189237569531562013-03-21T10:27:00.000+02:002013-03-21T11:28:44.749+02:00Procedure to check if the filesystems can accommodate a database's autoextensible datafilesThe following procedure will sum the space still to be claimed by the autoextensible datafiles per filesystem and compare those sums will the filesystems' free space. Be aware, this assumes there are no more than one database's autoextensible datafiles per filesystem. Otherwise, you may try to use database links to gather all required information in one database and get an aggregated result.<br />
<br />
First of all, we need to retrieve information about the filesystems in our server, so since this is a Linux server, we will use the "df" command. In the home directory [/home/oracle], create a df.sh:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>#!/bin/sh
/bin/df -Pl</code></pre>
<br />
And make it executable:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>chmod u+x df.sh</code></pre>
<br />
Execute it:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>Filesystem<code></code></code><code><code><code> </code></code></code><code><code><code><code> </code></code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code><code><code> </code></code></code></code></code><code></code>1024-blocks<code></code></code><code><code><code> </code></code>Used<code></code></code><code><code><code> </code></code>Available<code></code></code><code><code><code> </code></code>Capacity</code><code><code><code><code> </code></code></code>Mounted on
/dev/mapper/rootvg00-lvol_root<code></code></code><code><code><code> </code></code></code><code><code><code><code> </code></code></code>18368380</code><code><code> </code></code><code><code><code></code></code>5043772</code><code> </code><code></code><code><code></code>12391540</code><code> </code><code><code></code>29%<code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code><code><code><code><code> </code></code></code></code></code></code></code>/
tmpfs</code><code><code> </code></code><code><code><code> </code></code></code><code><code><code><code> </code></code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code><code><code> </code></code></code></code></code>1961068</code><code><code> </code></code><code><code><code> </code></code>683460</code><code> </code><code></code><code>1277608</code><code> </code><code><code><code><code> </code></code></code>35%<code></code></code><code><code><code><code><code> </code></code></code></code><code></code></code><code><code><code><code><code> </code></code></code></code>/dev/shm
/dev/sda1</code><code><code> </code></code><code><code><code> </code></code></code><code><code><code><code> </code></code></code></code><code><code><code><code><code> </code></code></code></code>198337</code><code><code> </code></code><code><code> </code>73094</code><code><code> </code></code><code><code><code></code></code>115003<code></code></code><code><code><code> </code></code></code><code><code><code><code> </code></code></code>39%<code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code><code><code><code><code> </code></code></code></code></code></code></code>/boot
/dev/mapper/rootvg00-lvol_home<code></code></code><code><code><code> </code></code></code><code><code><code><code> </code></code></code><code></code>2064208</code><code> </code><code> </code><code><code></code>137952</code><code><code> </code></code><code><code><code></code></code>1821400<code></code></code><code><code><code> </code></code></code><code><code><code><code><code><code> </code></code></code></code></code>8%<code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code><code><code><code><code> </code></code></code></code></code></code></code>/home
/dev/mapper/rootvg00-lvol_tmp<code></code></code><code><code><code> </code></code></code><code><code><code><code> </code></code></code><code></code>4128448<code></code></code><code><code><code> </code></code></code><code><code> </code>139296</code><code><code> </code></code><code><code><code></code></code>3779440</code><code><code><code> </code></code></code><code><code><code><code> </code></code></code>4%<code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code><code><code><code><code> </code></code></code></code></code></code></code>/tmp
/dev/mapper/rootvg00-lvol_var<code></code></code><code><code><code> </code></code></code><code><code><code><code> </code></code></code><code></code>8260812<code></code></code><code><code><code> </code></code></code><code><code> </code>3771552<code></code></code><code><code><code></code></code></code><code><code><code><code> </code></code></code>4069628<code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code> </code></code></code>49%<code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code><code><code><code><code> </code></code></code></code></code></code></code>/var
/dev/mapper/rootvg00-lvol_vartmp<code></code></code><code><code><code></code></code></code><code><code><code><code> </code></code></code>4128448<code></code></code><code><code><code> </code></code></code><code><code> </code>227884</code><code></code><code> </code><code><code></code>3690852<code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code><code><code><code><code> </code></code></code></code></code></code></code>6%<code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code><code><code><code><code> </code></code></code></code></code></code></code>/var/tmp
/dev/mapper/OracleVG-lvbinaries<code></code></code><code><code><code> </code></code></code><code><code><code><code> </code></code></code><code></code>11123304</code><code> </code><code><code></code>6210032</code><code><code></code></code><code><code><code> </code></code>4348232<code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code><code><code><code><code> </code></code></code></code></code></code></code>59%<code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code><code><code><code><code> </code></code></code></code></code></code></code>/orabin
/dev/mapper/OracleVG-lvredo2<code></code></code><code><code><code></code></code></code><code><code><code><code> </code></code></code></code><code><code><code><code><code> </code></code></code></code><code></code>444276<code></code></code><code><code><code> </code></code></code><code><code> </code>177541</code><code></code><code> </code><code><code></code>243798<code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code> </code></code></code>43%<code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code><code><code><code><code> </code></code></code></code></code></code></code><code></code>/redo2
/dev/mapper/OracleVG-lvredo1<code></code></code><code><code><code></code></code></code><code><code><code><code> </code></code></code></code><code><code><code><code><code> </code></code></code></code><code></code>444276<code></code></code><code><code><code> </code></code></code><code><code> </code>177541</code><code></code><code> </code><code><code></code>243798<code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code> </code></code></code>43%<code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code><code><code><code><code> </code></code></code></code></code></code></code>/redo1
/dev/mapper/OracleVG-lvarch<code></code></code><code><code><code></code></code></code><code><code><code><code> </code></code></code></code><code><code><code><code><code> </code></code></code></code><code></code>21778916<code></code></code><code><code><code> </code></code>35900</code><code></code><code> </code><code><code></code>20644052<code></code></code><code><code><code><code><code> </code></code></code></code>1%<code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code><code><code><code><code> </code></code></code></code></code></code></code>/orarch
/dev/mapper/OracleVG-lvdata<code></code></code><code><code><code></code></code></code><code><code><code><code> </code></code></code></code><code><code><code><code><code> </code></code></code></code><code></code>9079280</code><code> </code><code><code> </code>7751656</code><code><code> </code></code><code><code><code></code></code>866416<code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code><code><code><code><code> </code></code></code></code></code></code></code><code></code>90%<code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code><code><code><code><code> </code></code></code></code></code></code></code>/oradata
/dev/mapper/vgoradata2-lvoradata2<code></code></code><code><code><code></code></code></code><code><code><code><code></code></code></code></code><code><code><code><code><code> </code></code></code></code>20428668<code></code></code><code><code><code> </code></code>6516652</code><code> </code><code></code><code>12874296<code></code></code><code><code><code><code><code> </code></code></code></code>34%<code></code></code><code><code><code><code><code> </code></code></code></code></code><code><code><code><code><code><code><code><code> </code></code></code></code></code></code></code>/oradata2</code></pre>
<br />
For instance, we can see /oradata filesystem has 866416 Kbytes of unused space.<br />
<br />
Now, we need to make this information available to our database.<br />
We create a directory to point to our home directory:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>CREATE OR REPLACE DIRECTORY HOME_DIR AS '/home/oracle';</code></pre>
<br />
And an external table to query this information:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>CREATE TABLE FILESYSTEMS
(FSNAME VARCHAR2(100), BLOCKS NUMBER, USED NUMBER, AVAILABLE NUMBER, CAPACITY VARCHAR2(5), MOUNT VARCHAR2(100))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY HOME_DIR
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
NOLOGFILE
PREPROCESSOR
HOME_DIR:'df.sh'
SKIP 1
FIELDS TERMINATED BY WHITESPACE LDRTRIM)
LOCATION
(HOME_DIR:'df.sh'));</code></pre>
<br />
Let's use it:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT * FROM FILESYSTEMS;</code></pre>
<br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4; width: 400%px;"><tbody>
<tr><td>FSNAME</td><td>BLOCKS</td><td>USED</td><td>AVAILABLE</td><td>CAPACITY</td><td>MOUNT</td></tr>
<tr><td>/dev/mapper/rootvg00-lvol_root</td><td>18368380</td><td>5043772</td><td>12391540</td><td>29%</td><td>/</td></tr>
<tr><td>tmpfs</td><td>1961068</td><td>683460</td><td>1277608</td><td>35%</td><td>/dev/shm</td></tr>
<tr><td>/dev/sda1</td><td>198337</td><td>73094</td><td>115003</td><td>39%</td><td>/boot</td></tr>
<tr><td>/dev/mapper/rootvg00-lvol_home</td><td>2064208</td><td>137952</td><td>1821400</td><td>8%</td><td>/home</td></tr>
<tr><td>/dev/mapper/rootvg00-lvol_tmp</td><td>4128448</td><td>139296</td><td>3779440</td><td>4%</td><td>/tmp</td></tr>
<tr><td>/dev/mapper/rootvg00-lvol_var</td><td>8260812</td><td>3772356</td><td>4068824</td><td>49%</td><td>/var</td></tr>
<tr><td>/dev/mapper/rootvg00-lvol_vartmp</td><td>4128448</td><td>227952</td><td>3690784</td><td>6%</td><td>/var/tmp</td></tr>
<tr><td>/dev/mapper/OracleVG-lvbinaries</td><td>11123304</td><td>6210100</td><td>4348164</td><td>59%</td><td>/orabin</td></tr>
<tr><td>/dev/mapper/OracleVG-lvredo2</td><td>444276</td><td>177541</td><td>243798</td><td>43%</td><td>/redo2</td></tr>
<tr><td>/dev/mapper/OracleVG-lvredo1</td><td>444276</td><td>177541</td><td>243798</td><td>43%</td><td>/redo1</td></tr>
<tr><td>/dev/mapper/OracleVG-lvarch</td><td>21778916</td><td>197192</td><td>243798</td><td>1%</td><td>/orarch</td></tr>
<tr><td>/dev/mapper/OracleVG-lvdata</td><td>9079280</td><td>7751656</td><td>866416</td><td>90%</td><td>/oradata</td></tr>
<tr><td>/dev/mapper/vgoradata2-lvoradata2</td><td>20428668</td><td>6516652</td><td>12874296</td><td>34%</td><td>/oradata2</td></tr>
</tbody></table>
<br />
We create one more table [FILESYSTEMS_INFO], identical to FILESYSTEMS, plus 2 more columns: TO_BE_USED, where we will sum the space still to be claimed by the autoextensible datafiles from DBA_DATA_FILES and DBA_TEMP_FILES, and OK, which will get value 'YES' if TO_BE_USED<AVAILABLE and 'NO' otherwise.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>CREATE TABLE FILESYSTEMS_INFO
(FSNAME VARCHAR2(100), MOUNT VARCHAR2(100), BLOCKS NUMBER, USED NUMBER, AVAILABLE NUMBER, CAPACITY VARCHAR2(5), TO_BE_USED NUMBER, OK VARCHAR2(3))
TABLESPACE USERS;</code></pre>
<br />
We run the following PL/SQL code:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>DECLARE
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE FILESYSTEMS_INFO';
INSERT INTO FILESYSTEMS_INFO(FSNAME, MOUNT, BLOCKS, USED, AVAILABLE, CAPACITY, TO_BE_USED, OK)
SELECT FSNAME, MOUNT, BLOCKS, USED, AVAILABLE, CAPACITY, 0, 'YES'
FROM FILESYSTEMS;
UPDATE FILESYSTEMS_INFO B SET (B.TO_BE_USED, B.OK)=
(SELECT NVL(SUM(A.MAXBYTES - A.USER_BYTES)/1024, 0), CASE WHEN SUM(A.MAXBYTES - A.USER_BYTES)/1024 >= B.AVAILABLE THEN 'NO' ELSE 'YES' END
FROM (SELECT FILE_NAME, AUTOEXTENSIBLE, MAXBYTES, USER_BYTES FROM DBA_DATA_FILES UNION SELECT FILE_NAME, AUTOEXTENSIBLE, MAXBYTES, USER_BYTES FROM DBA_TEMP_FILES) A
WHERE A.AUTOEXTENSIBLE = 'YES'
AND INSTR(A.FILE_NAME, B.MOUNT||'/', 1, 1) = 1);
COMMIT;
END;
/</code></pre>
<br />
Now, we query FILESYSTEMS_INFO:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT MOUNT, ROUND(AVAILABLE/1024/1024, 2) "AVAILABLE GB", ROUND(TO_BE_USED/1024/1024, 2) "TO BE USED GB", OK
FROM FILESYSTEMS_INFO;</code></pre>
<br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4; width: 400%px;"><tbody>
<tr><td>MOUNT</td><td>AVAILABLE GB</td><td>TO BE USED GB</td><td>OK</td></tr>
<tr><td>/</td><td>11,82</td><td>0</td><td>YES</td></tr>
<tr><td>/dev/shm</td><td>1,22</td><td>0</td><td>YES</td></tr>
<tr><td>/boot</td><td>0,11</td><td>0</td><td>YES</td></tr>
<tr><td>/home</td><td>1,74</td><td>0</td><td>YES</td></tr>
<tr><td>/tmp</td><td>3,6</td><td>0</td><td>YES</td></tr>
<tr><td>/var</td><td>3,88</td><td>0</td><td>YES</td></tr>
<tr><td>/var/tmp</td><td>3,52</td><td>0</td><td>YES</td></tr>
<tr><td>/orabin</td><td>4,15</td><td>0</td><td>YES</td></tr>
<tr><td>/redo2</td><td>0,23</td><td>0</td><td>YES</td></tr>
<tr><td>/redo1</td><td>0,23</td><td>0</td><td>YES</td></tr>
<tr><td>/orarch</td><td>19,57</td><td>0</td><td>YES</td></tr>
<tr><td>/oradata</td><td>0,83</td><td>0,63</td><td>YES</td></tr>
<tr><td>/oradata2</td><td>12,28</td><td>35,09</td><td>NO</td></tr>
</tbody></table>
<br />
/oradata filesystem is OK, because it has 0,83 GBytes of free space and all autoextensible datafiles in it could claim 0,63 GBytes maximum.<br />
On the other hand, /oradata2 has 12,28 GBytes of free space and 35,09 GBytes could be claimed. We should decrease one or more autoextensible datafiles' MAXSIZE.Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com0tag:blogger.com,1999:blog-6212636374045442782.post-47746662518905304222013-03-08T12:06:00.000+02:002013-03-08T12:18:23.924+02:00Optimize Siebel Repository Import in Oracle Database<div dir="ltr" style="text-align: left;" trbidi="on">
It was noticed during siebel repository import in an Oracle Database 11.2.0.3, most of database wait time was log file switch.<br />
<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>srvrupgwiz /m master_imprep.ucf</code></pre>
<br />
It took 163 minutes for 3,639,432 rows (98% waits log file switch).<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>2012-11-18 09:53:02 TOTAL TABLES: 328
2012-11-18 09:53:02 TOTAL ROWS : 3639432
2012-11-18 09:53:02 Cleaning up, disconnecting from the database.
2012-11-18 09:53:02 Elapsed time: 163 min 28 sec.</code></pre>
<br />
The database session did ~730.000 commits (meaning 1 commit every 5 rows).<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>repimexp /a I /G ENU /u sadmin /p ***** /c siebelpro_DSN /d siebel /r "Siebel Repository" /Z 5000 /h 5000 /f siebel_rep.data /l imprep_prim.log</code></pre>
<br />
If the /Z and /h option are used (with 5000 as argument) the time is less than 10 minutes for the same repository.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>2013-01-27 11:34:35 Verified successfully.
2013-01-27 11:34:35 TOTAL TABLES: 328
2013-01-27 11:34:35 TOTAL ROWS : 3928282
2013-01-27 11:34:35 Cleaning up, disconnecting from the database.
2013-01-27 11:34:35 Elapsed time: 9 min 46 sec.</code></pre>
<br />
There is no way (until Dec 2012) to put /Z or /h in ucm file, so the only solution is to use the command line interface (Oracle support replied that is a product defect).<br />
<br />
Reference from Oracle documentation:<br />
<a href="http://docs.oracle.com/cd/B40099_02/books/SiebInstWIN/SiebInstCOM_ConfigDB14.html" target="_blank">Importing a Siebel Repository</a><br />
<br />
NOTE: When you import data into the Siebel Repository tables, a commit is performed once for each table into which repository data is imported. Alternatively, the commit frequency can be set to a specified number of rows by including the command-line option /h num_rows_per_commit when the repimexp.exe utility is invoked.<br />
/H [number] (Optional) Number of rows per commit<br />
/Z [number] (Optional) Array Insert Size (Default: 5) </div>
George Giannoukoshttp://www.blogger.com/profile/11616957664089063344noreply@blogger.com1tag:blogger.com,1999:blog-6212636374045442782.post-67688866807246279112013-02-04T13:40:00.000+02:002013-02-05T12:43:29.456+02:00A database Create User Like... procedureThe following procedure creates the set of commands to duplicate an existing database user.<br />
The new user will have identical password, default and temporary tablespaces, profile, lock status, tablespace quotas, roles, system, object, column privileges and audit options with the existing user.<br />
The procedure has 2 arguments. The first is the name of the existing user and the second the name your new user will have. If the second argument is left blank, the existing user's name will be used.<br />
<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SET SERVEROUTPUT ON
SET LINESIZE 200
DECLARE
TYPE T_CURSOR IS REF CURSOR;
V_CURSOR T_CURSOR;
V_USER VARCHAR2 (30) := UPPER ('&1');
V_NEW_USER VARCHAR2 (30) := UPPER ('&2');
V_PASSWORD1 VARCHAR2 (30);
V_PASSWORD2 VARCHAR2 (30);
V_D_TBS VARCHAR2 (30);
V_T_TBS VARCHAR2 (30);
V_PROFILE VARCHAR2 (30);
V_LOCK_DATE DATE;
V_COUNTER NUMBER;
V_MAX_BYTES NUMBER;
V_PRIVILEGE VARCHAR2 (40);
V_ADMIN_OPT VARCHAR2 (3);
V_ROLE VARCHAR2 (30);
V_DEF_ROLE VARCHAR2 (3);
V_DEFROLE NUMBER;
V_OUTPUT VARCHAR2 (500);
V_TABLE_NAME VARCHAR2 (30);
V_OWNER VARCHAR2 (30);
V_GRANT_OPT VARCHAR2 (3);
V_HIER_OPT VARCHAR2 (3);
V_COL_NAME VARCHAR2 (30);
V_AUDIT_OPT VARCHAR2 (40);
V_SUCCESS VARCHAR2 (10);
V_FAILURE VARCHAR2 (10);
BEGIN
SELECT COUNT (*)
INTO V_COUNTER
FROM DBA_USERS
WHERE USERNAME = V_USER;
IF V_COUNTER <> 0
THEN
IF V_NEW_USER IS NULL
THEN
V_NEW_USER := V_USER;
END IF;
SELECT B.PASSWORD, A.PASSWORD, A.DEFAULT_TABLESPACE, A.TEMPORARY_TABLESPACE, A.PROFILE, A.LOCK_DATE
INTO V_PASSWORD1, V_PASSWORD2, V_D_TBS, V_T_TBS, V_PROFILE, V_LOCK_DATE
FROM DBA_USERS A, SYS.USER$ B
WHERE A.USERNAME = V_USER
AND A.USERNAME = B.NAME;
--Create User Command * Begin
DBMS_OUTPUT.PUT_LINE ('--Create User command');
DBMS_OUTPUT.PUT_LINE ('CREATE USER ' || V_NEW_USER);
IF V_PASSWORD2 = 'EXTERNAL'
THEN
DBMS_OUTPUT.PUT_LINE ('IDENTIFIED EXTERNALLY');
ELSE
DBMS_OUTPUT.PUT_LINE ('IDENTIFIED BY VALUES ''' || V_PASSWORD1 || '''');
END IF;
DBMS_OUTPUT.PUT_LINE ('DEFAULT TABLESPACE ' || V_D_TBS);
DBMS_OUTPUT.PUT_LINE ('TEMPORARY TABLESPACE ' || V_T_TBS);
DBMS_OUTPUT.PUT_LINE ('PROFILE ' || V_PROFILE);
IF V_LOCK_DATE IS NULL THEN
DBMS_OUTPUT.PUT_LINE ('ACCOUNT UNLOCK;');
ELSE
DBMS_OUTPUT.PUT_LINE ('ACCOUNT LOCK;');
END IF;
--Create User Command * End
--Tablespace Quotas * Begin
SELECT COUNT (*)
INTO V_COUNTER
FROM DBA_TS_QUOTAS
WHERE USERNAME = V_USER;
IF V_COUNTER > 0
THEN
DBMS_OUTPUT.PUT_LINE ('-- ' || V_COUNTER || ' tablespace quotas');
OPEN V_CURSOR FOR
SELECT TABLESPACE_NAME, MAX_BYTES
FROM DBA_TS_QUOTAS
WHERE USERNAME = V_USER;
LOOP
FETCH V_CURSOR
INTO V_D_TBS, V_MAX_BYTES;
EXIT WHEN V_CURSOR%NOTFOUND;
IF V_MAX_BYTES = -1
THEN
DBMS_OUTPUT.PUT_LINE ('ALTER USER ' || V_NEW_USER || ' QUOTA UNLIMITED ON ' || V_D_TBS || ';');
ELSE
DBMS_OUTPUT.PUT_LINE ('ALTER USER ' || V_NEW_USER || ' QUOTA ' || V_MAX_BYTES || ' ON ' || V_D_TBS || ';');
END IF;
END LOOP;
CLOSE V_CURSOR;
END IF;
--Tablespace Quotas * End
--Roles * Begin
SELECT COUNT (*)
INTO V_COUNTER
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = V_USER;
IF V_COUNTER > 0
THEN
DBMS_OUTPUT.PUT_LINE ('-- ' || V_COUNTER || ' Roles');
SELECT DEFROLE
INTO V_DEFROLE
FROM SYS.USER$
WHERE NAME = V_USER;
CASE V_DEFROLE
WHEN 0
THEN
DBMS_OUTPUT.PUT_LINE (
'ALTER USER ' || V_NEW_USER || ' DEFAULT ROLE NONE;');
WHEN 1
THEN
DBMS_OUTPUT.PUT_LINE (
'ALTER USER ' || V_NEW_USER || ' DEFAULT ROLE ALL;');
ELSE
NULL;
END CASE;
OPEN V_CURSOR FOR
SELECT GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = V_USER;
LOOP
FETCH V_CURSOR
INTO V_ROLE, V_ADMIN_OPT, V_DEF_ROLE;
EXIT WHEN V_CURSOR%NOTFOUND;
V_OUTPUT := 'GRANT ' || V_ROLE || ' TO ' || V_NEW_USER;
IF V_ADMIN_OPT = 'NO'
THEN
V_OUTPUT := V_OUTPUT || ';';
ELSE
V_OUTPUT := V_OUTPUT || ' WITH ADMIN OPTION;';
END IF;
DBMS_OUTPUT.PUT_LINE (V_OUTPUT);
IF V_DEFROLE = 2 AND V_DEF_ROLE='YES'
THEN
DBMS_OUTPUT.PUT_LINE ('ALTER USER ' || V_NEW_USER || ' DEFAULT ROLE ' || V_ROLE || ';');
END IF;
END LOOP;
CLOSE V_CURSOR;
END IF;
--Roles * End
--System Privileges * Begin
SELECT COUNT (*)
INTO V_COUNTER
FROM DBA_SYS_PRIVS
WHERE GRANTEE = V_USER;
IF V_COUNTER > 0
THEN
DBMS_OUTPUT.PUT_LINE ('-- ' || V_COUNTER || ' system privileges');
OPEN V_CURSOR FOR
SELECT PRIVILEGE, ADMIN_OPTION
FROM DBA_SYS_PRIVS
WHERE GRANTEE = V_USER;
LOOP
FETCH V_CURSOR
INTO V_PRIVILEGE, V_ADMIN_OPT;
EXIT WHEN V_CURSOR%NOTFOUND;
V_OUTPUT := 'GRANT ' || V_PRIVILEGE || ' TO ' || V_NEW_USER;
IF V_ADMIN_OPT = 'NO'
THEN
V_OUTPUT := V_OUTPUT || ';';
ELSE
V_OUTPUT := V_OUTPUT || ' WITH ADMIN OPTION;';
END IF;
DBMS_OUTPUT.PUT_LINE (V_OUTPUT);
END LOOP;
CLOSE V_CURSOR;
END IF;
--System Privileges * End
--Object Privileges * Begin
SELECT COUNT (*)
INTO V_COUNTER
FROM DBA_TAB_PRIVS
WHERE GRANTEE = V_USER;
IF V_COUNTER > 0
THEN
DBMS_OUTPUT.PUT_LINE ('-- ' || V_COUNTER || ' object privileges');
OPEN V_CURSOR FOR
SELECT PRIVILEGE, OWNER, TABLE_NAME, GRANTABLE, HIERARCHY
FROM DBA_TAB_PRIVS
WHERE GRANTEE = V_USER;
LOOP
FETCH V_CURSOR
INTO V_PRIVILEGE, V_OWNER, V_TABLE_NAME, V_GRANT_OPT, V_HIER_OPT;
EXIT WHEN V_CURSOR%NOTFOUND;
V_OUTPUT := 'GRANT ' || V_PRIVILEGE || ' ON ' || V_OWNER || '.' || V_TABLE_NAME || ' TO ' || V_NEW_USER;
IF V_HIER_OPT = 'YES'
THEN
V_OUTPUT := V_OUTPUT || ' WITH HIERARCHY OPTION';
END IF;
IF V_GRANT_OPT = 'NO'
THEN
V_OUTPUT := V_OUTPUT || ';';
ELSE
V_OUTPUT := V_OUTPUT || ' WITH GRANT OPTION;';
END IF;
DBMS_OUTPUT.PUT_LINE (V_OUTPUT);
END LOOP;
CLOSE V_CURSOR;
END IF;
--Object Privileges * End
--Column Privileges * Begin
SELECT COUNT (*)
INTO V_COUNTER
FROM DBA_COL_PRIVS
WHERE GRANTEE = V_USER;
IF V_COUNTER > 0
THEN
DBMS_OUTPUT.PUT_LINE ('-- ' || V_COUNTER || ' column privileges');
OPEN V_CURSOR FOR
SELECT PRIVILEGE, OWNER, TABLE_NAME, COLUMN_NAME, GRANTABLE
FROM DBA_COL_PRIVS
WHERE GRANTEE = V_USER;
LOOP
FETCH V_CURSOR
INTO V_PRIVILEGE, V_OWNER, V_TABLE_NAME, V_COL_NAME, V_GRANT_OPT;
EXIT WHEN V_CURSOR%NOTFOUND;
V_OUTPUT := 'GRANT ' || V_PRIVILEGE || ' (' || V_COL_NAME || ') ON ' || V_OWNER || '.' || V_TABLE_NAME || ' TO ' || V_NEW_USER;
IF V_GRANT_OPT = 'NO'
THEN
V_OUTPUT := V_OUTPUT || ';';
ELSE
V_OUTPUT := V_OUTPUT || ' WITH GRANT OPTION;';
END IF;
DBMS_OUTPUT.PUT_LINE (V_OUTPUT);
END LOOP;
CLOSE V_CURSOR;
END IF;
--Column Privileges * End
--Auditing options * Begin
SELECT COUNT (*)
INTO V_COUNTER
FROM DBA_STMT_AUDIT_OPTS
WHERE USER_NAME = V_USER;
IF V_COUNTER > 0
THEN
DBMS_OUTPUT.PUT_LINE ('-- ' || V_COUNTER || ' auditing options');
OPEN V_CURSOR FOR
SELECT AUDIT_OPTION, SUCCESS, FAILURE
FROM DBA_STMT_AUDIT_OPTS
WHERE USER_NAME = V_USER;
LOOP
FETCH V_CURSOR
INTO V_AUDIT_OPT, V_SUCCESS, V_FAILURE;
EXIT WHEN V_CURSOR%NOTFOUND;
IF V_SUCCESS <> 'NOT SET'
THEN
DBMS_OUTPUT.PUT_LINE('AUDIT '||V_AUDIT_OPT||' BY '||V_NEW_USER||' '||V_SUCCESS||' WHENEVER SUCCESSFUL;');
END IF;
IF V_FAILURE <> 'NOT SET'
THEN
DBMS_OUTPUT.PUT_LINE('AUDIT '||V_AUDIT_OPT||' BY '||V_NEW_USER||' '||V_FAILURE||' WHENEVER NOT SUCCESSFUL;');
END IF;
END LOOP;
CLOSE V_CURSOR;
END IF;
--Auditing options * End
ELSE
DBMS_OUTPUT.PUT_LINE ('User ' || V_USER || ' does not exist.');
END IF;
END;
/</code></pre>
<br />Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com1tag:blogger.com,1999:blog-6212636374045442782.post-18136355661940204672012-12-21T12:48:00.000+02:002013-01-10T11:29:47.908+02:00Use Sqltxplain or DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE to change execution plansThis demonstration is based on <b>Metalink Note 1487302.1 "Using Sqltxplain to create a 'SQL Profile' to consistently reproduce a good plan"</b>.<br />
<br />
First of all, we need to install the Sqltxplain tool. Go to <b>Metalink Note 215187.1 "SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly or one that produces wrong results"</b>, download <b>sqlt.zip</b> and install it under your ORACLE_HOME.<br />
<br />
Let's test it. First we create 2 tables, based on DBA_OBJECTS and DBA_SEGMENTS and indexes on OBJECT_NAME and SEGMENT_NAME columns respectively.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>sqlplus helpdesk
SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 21 11:55:47 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
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> CREATE TABLE HELPDESK.OBJECTS AS SELECT * FROM DBA_OBJECTS;
Table created.
SQL> CREATE INDEX HELPDESK.OBJ_NAME ON HELPDESK.OBJECTS(OBJECT_NAME);
Index created.
SQL> CREATE TABLE HELPDESK.SEGMENTS AS SELECT * FROM DBA_SEGMENTS;
Table created.
SQL> CREATE INDEX HELPDESK.SEG_NAME ON HELPDESK.SEGMENTS(SEGMENT_NAME);
Index created.</code></pre>
<br />
We will use a query that joins those tables using the indexed columns.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> SET AUTOT TRACEONLY
SQL> SET TIMING ON
SQL> SET LINESIZE 200
SQL> SELECT A.OBJECT_ID,
A.OBJECT_NAME,
B.SEGMENT_TYPE,
B.TABLESPACE_NAME
FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B
WHERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP%';
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 917395572
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 298 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2 | 298 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| OBJECTS | 2 | 158 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | OBJ_NAME | 2 | | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SEG_NAME | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | SEGMENTS | 1 | 70 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."OBJECT_NAME" LIKE 'HELP%')
filter("A"."OBJECT_NAME" LIKE 'HELP%')
5 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME")
filter("B"."SEGMENT_NAME" LIKE 'HELP%')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
855 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed</code></pre>
<br />
In this execution plan with plan hash value 917395572, both indexes are used.<br />
Let's find out our query's SQL_ID. Run as SYSDBA:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT SQL_ID, SQL_TEXT
FROM V$SQL
WHERE SQL_TEXT LIKE 'SELECT A.OBJECT_ID%';</code></pre>
<br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4; width: 400%px;"><tbody>
<tr><td>SQL_ID</td><td>SQL_TEXT</td></tr>
<tr><td>8qu7mmcwnygtv</td><td>SELECT A.OBJECT_ID, A.OBJECT_NAME, B.SEGMENT_TYPE, B.TABLESPACE_NAME FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B WHERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP%'</td></tr>
</tbody></table>
<br />
Now, we will drop index HELPDESK.OBJ_NAME, in order to get a new execution plan for our query, where a full table scan will be used on table HELPDESK.OBJECTS.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> DROP INDEX HELPDESK.OBJ_NAME;
Index dropped.
SQL> SELECT A.OBJECT_ID,
A.OBJECT_NAME,
B.SEGMENT_TYPE,
B.TABLESPACE_NAME
FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B
WHERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP%';
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2874684176
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 298 | 83 (2)| 00:00:01 |
|* 1 | HASH JOIN | | 2 | 298 | 83 (2)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| SEGMENTS | 2 | 140 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SEG_NAME | 2 | | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | OBJECTS | 3 | 237 | 79 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME")
3 - access("B"."SEGMENT_NAME" LIKE 'HELP%')
filter("B"."SEGMENT_NAME" LIKE 'HELP%')
4 - filter("A"."OBJECT_NAME" LIKE 'HELP%')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
54 recursive calls
0 db block gets
424 consistent gets
0 physical reads
0 redo size
855 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed</code></pre>
<br />
A new plan was created with a full table scan on HELPDESK.OBJECTS and plan hash value 2874684176.<br />
We recreate the index and we check the old plan is being used again.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> CREATE INDEX HELPDESK.OBJ_NAME ON HELPDESK.OBJECTS(OBJECT_NAME);
Index created.
SQL> SELECT A.OBJECT_ID,
A.OBJECT_NAME,
B.SEGMENT_TYPE,
B.TABLESPACE_NAME
FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B
WHERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP%';
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 917395572
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 298 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2 | 298 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| OBJECTS | 2 | 158 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | OBJ_NAME | 2 | | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SEG_NAME | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | SEGMENTS | 1 | 70 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."OBJECT_NAME" LIKE 'HELP%')
filter("A"."OBJECT_NAME" LIKE 'HELP%')
5 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME")
filter("B"."SEGMENT_NAME" LIKE 'HELP%')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
139 consistent gets
1 physical reads
0 redo size
855 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed</code></pre>
<br />
Now, we have 2 execution plans for our query.<br />
We create an AWR snapshot as SYSDBA and we will use a query from Document 1487302.1 to compare those execution plans.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
PL/SQL procedure successfully completed.</code></pre>
<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>WITH
P AS (
SELECT PLAN_HASH_VALUE
FROM GV$SQL_PLAN
WHERE SQL_ID = TRIM('8qu7mmcwnygtv')
AND OTHER_XML IS NOT NULL
UNION
SELECT PLAN_HASH_VALUE
FROM DBA_HIST_SQL_PLAN
WHERE SQL_ID = TRIM('8qu7mmcwnygtv')
AND OTHER_XML IS NOT NULL ),
M AS (
SELECT PLAN_HASH_VALUE,
SUM(ELAPSED_TIME)/SUM(EXECUTIONS) AVG_ET_SECS
FROM GV$SQL
WHERE SQL_ID = TRIM('8qu7mmcwnygtv')
AND EXECUTIONS > 0
GROUP BY
PLAN_HASH_VALUE ),
A AS (
SELECT PLAN_HASH_VALUE,
SUM(ELAPSED_TIME_TOTAL)/SUM(EXECUTIONS_TOTAL) AVG_ET_SECS
FROM DBA_HIST_SQLSTAT
WHERE SQL_ID = TRIM('8qu7mmcwnygtv')
AND EXECUTIONS_TOTAL > 0
GROUP BY
PLAN_HASH_VALUE )
SELECT P.PLAN_HASH_VALUE,
ROUND(NVL(M.AVG_ET_SECS, A.AVG_ET_SECS)/1E6, 3) AVG_ET_SECS
FROM P, M, A
WHERE P.PLAN_HASH_VALUE = M.PLAN_HASH_VALUE(+)
AND P.PLAN_HASH_VALUE = A.PLAN_HASH_VALUE(+)
ORDER BY
AVG_ET_SECS NULLS LAST;</code></pre>
<br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4; width: 400%px;"><tbody>
<tr><td>PLAN_HASH_VALUE</td><td>AVG_ET_SECS</td></tr>
<tr><td>917395572</td><td>0,008</td></tr>
<tr><td>2874684176</td><td>0,019</td></tr>
</tbody></table>
<br />
Obviously, the execution plan with the 2 index range scans (917395572) is faster, but for the sake of our scenario we want to force plan 2874684176 to be used, when both indexes exist.<br />
We will use Sqltxplain's <b>coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE]</b> as SYSDBA.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> @$ORACLE_HOME/sqlt/utl/coe_xfr_sql_profile.sql '8qu7mmcwnygtv' '2874684176';
Parameter 1:
SQL_ID (required)
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
917395572 .008
2874684176 .019
Parameter 2:
PLAN_HASH_VALUE (required)
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "8qu7mmcwnygtv"
PLAN_HASH_VALUE: "2874684176"
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
SQL>BEGIN
2 IF :other_xml IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
Execute coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql
on TARGET system in order to create a custom SQL Profile
with plan 2874684176 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.</code></pre>
<br />
Now, we need to execute as SYSDBA the generated coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL>@$ORACLE_HOME/sqlt/utl/coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql 11.4.4.4 2012/12/21 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM This script is generated by coe_xfr_sql_profile.sql
SQL>REM It contains the SQL*Plus commands to create a custom
SQL>REM SQL Profile for SQL_ID 8qu7mmcwnygtv based on plan hash
SQL>REM value 2874684176.
SQL>REM The custom SQL Profile to be created by this script
SQL>REM will affect plans for SQL commands with signature
SQL>REM matching the one for SQL Text below.
SQL>REM Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM SQL> START coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql;
SQL>REM
SQL>REM NOTES
SQL>REM 1. Should be run as SYSTEM or SYSDBA.
SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM 3. SOURCE and TARGET systems can be the same or similar.
SQL>REM 4. To drop this custom SQL Profile after it has been created:
SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_8qu7mmcwnygtv_2874684176');
SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM for the Oracle Tuning Pack.
SQL>REM 6. If you modified a SQL putting Hints in order to produce a desired
SQL>REM Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL>REM By doing so you can create a custom SQL Profile for the original
SQL>REM SQL but with the Plan captured from the modified SQL (with Hints).
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>VAR signaturef NUMBER;
SQL>REM
SQL>DECLARE
2 sql_txt CLOB;
3 h SYS.SQLPROF_ATTR;
4 PROCEDURE wa (p_line IN VARCHAR2) IS
5 BEGIN
6 DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
7 END wa;
8 BEGIN
9 DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
10 DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
11 -- SQL Text pieces below do not have to be of same length.
12 -- So if you edit SQL Text (i.e. removing temporary Hints),
13 -- there is no need to edit or re-align unmodified pieces.
14 wa(q'[SELECT A.OBJECT_ID,
15 A.OBJECT_NAME,
16 B.SEGMENT_TYPE,
17 B.TA]');
18 wa(q'[BLESPACE_NAME
19 FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B
20 WH]');
21 wa(q'[ERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP]');
22 wa(q'[%']');
23 DBMS_LOB.CLOSE(sql_txt);
24 h := SYS.SQLPROF_ATTR(
25 q'[BEGIN_OUTLINE_DATA]',
26 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
27 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
28 q'[DB_VERSION('11.2.0.3')]',
29 q'[ALL_ROWS]',
30 q'[OUTLINE_LEAF(@"SEL$1")]',
31 q'[INDEX_RS_ASC(@"SEL$1" "B"@"SEL$1" ("SEGMENTS"."SEGMENT_NAME"))]',
32 q'[FULL(@"SEL$1" "A"@"SEL$1")]',
33 q'[LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")]',
34 q'[USE_HASH(@"SEL$1" "A"@"SEL$1")]',
35 q'[END_OUTLINE_DATA]');
36 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
37 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
38 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
39 sql_text => sql_txt,
40 profile => h,
41 name => 'coe_8qu7mmcwnygtv_2874684176',
42 description => 'coe 8qu7mmcwnygtv 2874684176 '||:signature||' '||:signaturef||'',
43 category => 'DEFAULT',
44 validate => TRUE,
45 replace => TRUE,
46 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
47 DBMS_LOB.FREETEMPORARY(sql_txt);
48 END;
49 /
PL/SQL procedure successfully completed.
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
5858216496569750688
SIGNATUREF
---------------------
1896484766659468535
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_8qu7mmcwnygtv_2874684176 completed</code></pre>
<br />
Now, the plan 2874684176 will be used, every time our query is run.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> SELECT A.OBJECT_ID,
A.OBJECT_NAME,
B.SEGMENT_TYPE,
B.TABLESPACE_NAME
FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B
WHERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP%';
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2874684176
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 436 | 64964 | 98 (2)| 00:00:02 |
|* 1 | HASH JOIN | | 436 | 64964 | 98 (2)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| SEGMENTS | 417 | 29190 | 18 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SEG_NAME | 75 | | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | OBJECTS | 1176 | 92904 | 79 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME")
3 - access("B"."SEGMENT_NAME" LIKE 'HELP%')
filter("B"."SEGMENT_NAME" LIKE 'HELP%')
4 - filter("A"."OBJECT_NAME" LIKE 'HELP%')
Note
-----
- SQL profile "coe_8qu7mmcwnygtv_2874684176" used for this statement <b><-- This is the SQL profile created by coe_xfr_sql_profile_8qu7mmcwnygtv_2874684176.sql</b>
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
288 consistent gets
1 physical reads
0 redo size
855 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed</code></pre>
<br />
In 11g databases, it's recommended to use DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE function, instead:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> SET SERVEROUTPUT ON
VAR N NUMBER
BEGIN
:N:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'8qu7mmcwnygtv', PLAN_HASH_VALUE=>2874684176, FIXED =>'NO', ENABLED=>'YES');
DBMS_OUTPUT.PUT_LINE('Number of plans loaded: '||:N);
END;
/
Number of plans loaded: 1
PL/SQL procedure successfully completed.</code></pre>
<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> SELECT A.OBJECT_ID,
A.OBJECT_NAME,
B.SEGMENT_TYPE,
B.TABLESPACE_NAME
FROM HELPDESK.OBJECTS A, HELPDESK.SEGMENTS B
WHERE A.OBJECT_NAME = B.SEGMENT_NAME AND B.SEGMENT_NAME LIKE 'HELP%';
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2874684176
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 436 | 64964 | 98 (2)| 00:00:02 |
|* 1 | HASH JOIN | | 436 | 64964 | 98 (2)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| SEGMENTS | 417 | 29190 | 18 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SEG_NAME | 75 | | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | OBJECTS | 1176 | 92904 | 79 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OBJECT_NAME"="B"."SEGMENT_NAME")
3 - access("B"."SEGMENT_NAME" LIKE 'HELP%')
filter("B"."SEGMENT_NAME" LIKE 'HELP%')
4 - filter("A"."OBJECT_NAME" LIKE 'HELP%')
Note
-----
- SQL plan baseline "SQL_PLAN_52m4hvq7pz550a442f8ad" used for this statement <b><-- This is the SQL plan baseline we have just created</b>
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
288 consistent gets
1 physical reads
0 redo size
855 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed</code></pre>
Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com0tag:blogger.com,1999:blog-6212636374045442782.post-63948653661590672222012-12-20T13:33:00.000+02:002012-12-20T14:20:11.481+02:00RAC: How data blocks are loaded into the buffer cacheIn this scenario, we will demonstrate how buffer cache is populated by data blocks in a RAC environment.<br />
The following information was copied by Oracle9i Real Application Clusters Concepts Guide and describes the states of a block, when loaded into the buffer cache. It's strange, this information was removed from 10g/11g documentation:<br />
<br />
<pre style="background-color: #f0f0f0; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code><b>Block Access Modes and Buffer States</b>
An additional concurrency control concept is the <b>buffer state</b> which is the state of a buffer in the local cache of an instance.
The buffer state of a block relates to the access mode of the block.
For example, if a buffer state is exclusive current (XCUR), an instance owns the resource in exclusive mode.
To see a buffer’s state, query the STATUS column of the V$BH dynamic performance view.
The V$BH view provides information about the block access mode and their buffer state names as follows:
* With a block access mode of NULL the buffer state name is <b>CR</b>:
An instance can perform a consistent read of the block.
That is, if the instance holds an older version of the data.
* With a block access mode of S the buffer state name is <b>SCUR</b>:
An instance has shared access to the block and can only perform reads.
* With a block access mode of X the buffer state name is <b>XCUR</b>:
An instance has exclusive access to the block and can modify it.
* With a block access mode of NULL the buffer state name is <b>PI</b>:
An instance has made changes to the block but retains copies of it as past images to record its state before changes.
Only the SCUR and PI buffer states are Real Application Clusters-specific.
There can be only one copy of any one block buffered in the XCUR state in the cluster database at any time.
To perform modifications on a block, a process must assign an XCUR buffer state to the buffer containing the data block.
For example, if another instance requests read access to the most current version of the same block,
then Oracle changes the access mode from exclusive to shared, sends a current read version of the block to the requesting instance
and keeps a PI buffer if the buffer contained a dirty block.
At this point, the first instance has the current block and the requesting instance also has the current block in shared mode.
Therefore, the role of the resource becomes global.
There can be multiple shared current (SCUR) versions of this block cached throughout the cluster database at any time.</code></pre>
<br />
First, we create a new LMT/ASSM tablespace:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> CREATE TABLESPACE HELPDESK2 DATAFILE
'/oradb/helpdesk2_01.dbf' SIZE 5M AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
Tablespace created.</code></pre>
<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: #100010; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> ALTER USER HELPDESK QUOTA UNLIMITED ON HELPDESK2;
User altered.</code></pre>
<br />
We connect to Instance 1 and create a new table:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>1> CREATE TABLE HELPDESK.TESTTBL (ID NUMBER, TEXT VARCHAR2(100)) TABLESPACE HELPDESK2;
Table created.</code></pre>
<br />
We query DBA_EXTENTS:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT *
FROM DBA_EXTENTS
WHERE SEGMENT_NAME='TESTTBL'
AND OWNER='HELPDESK';</code></pre>
<br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4; width: 400%px;"><tbody>
<tr><td>OWNER</td><td>SEGMENT_NAME</td><td>PARTITION_NAME</td><td>SEGMENT_TYPE</td><td>TABLESPACE_NAME</td><td>EXTENT_ID</td><td>FILE_ID</td><td>BLOCK_ID</td><td>BYTES</td><td>BLOCKS</td><td>RELATIVE_FNO</td></tr>
<tr><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>HELPDESK2</td><td>0</td><td>2483</td><td>9</td><td>65536</td><td>8</td><td>437</td></tr>
</tbody></table>
<br />
LMT/ASSM tablespaces have a minimum extent size of 64K, that's why table's initial size is 64K.<br />
Since tablespace's block size is 8K, table's initial extent occupies 8 blocks and since the starting block's ID is 9, it occupies blocks 9 to 16.<br />
Are all 8 blocks free?<br />
Let's find out:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> SET SERVEROUTPUT ON
DECLARE
V_OWNER DBA_SEGMENTS.OWNER%TYPE;
V_NAME DBA_SEGMENTS.SEGMENT_NAME%TYPE;
V_TYPE DBA_SEGMENTS.SEGMENT_TYPE%TYPE;
V_TBL NUMBER;
V_TBB NUMBER;
V_UBL NUMBER;
V_UBB NUMBER;
V_LUFID NUMBER;
V_LUBID NUMBER;
V_LUB NUMBER;
BEGIN
V_OWNER := 'HELPDESK';
V_NAME := 'TESTTBL';
V_TYPE := 'TABLE';
DBMS_SPACE.UNUSED_SPACE (V_OWNER, V_NAME, V_TYPE, V_TBL, V_TBB, V_UBL, V_UBB, V_LUFID, V_LUBID, V_LUB);
DBMS_OUTPUT.PUT_LINE ('OWNER: ' || V_OWNER);
DBMS_OUTPUT.PUT_LINE ('SEGMENT NAME: ' || V_NAME);
DBMS_OUTPUT.PUT_LINE ('SEGMENT TYPE: ' || V_TYPE);
DBMS_OUTPUT.PUT_LINE ('TOTAL BLOCKS: ' || V_TBL);
DBMS_OUTPUT.PUT_LINE ('TOTAL BYTES: ' || V_TBB);
DBMS_OUTPUT.PUT_LINE ('UNUSED BLOCKS: ' || V_UBL);
DBMS_OUTPUT.PUT_LINE ('UNUSED BYTES: ' || V_UBB);
DBMS_OUTPUT.PUT_LINE ('LAST USED EXTENT FILE ID: ' || V_LUFID);
DBMS_OUTPUT.PUT_LINE ('LAST USED EXTENT BLOCK ID: ' || V_LUBID);
DBMS_OUTPUT.PUT_LINE ('LAST USED BLOCK: ' || V_LUB);
END;
/
OWNER: HELPDESK
SEGMENT NAME: TESTTBL
SEGMENT TYPE: TABLE
TOTAL BLOCKS: 8
TOTAL BYTES: 65536
UNUSED BLOCKS: 5
UNUSED BYTES: 40960
LAST USED EXTENT FILE ID: 2483
LAST USED EXTENT BLOCK ID: 9
LAST USED BLOCK: 3
PL/SQL procedure successfully completed.</code></pre>
<br />
No, only 5 blocks are unused.<br />
Now, let's see what was loaded into the buffer caches, when we created the table:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT A.INST_ID,
C.TABLESPACE_NAME,
C.FILE_NAME,
A.BLOCK#,
B.OWNER,
B.OBJECT_NAME,
B.SUBOBJECT_NAME,
B.OBJECT_TYPE,
DECODE (A.CLASS#,
1, 'data block',
2, 'sort block',
3, 'save undo block',
4, 'segment header',
5, 'save undo header',
6, 'free list',
7, 'extent map',
8, '1st level bmb',
9, '2nd level bmb',
10, '3rd level bmb',
11, 'bitmap block',
12, 'bitmap index block',
13, 'file header block',
14, 'unused',
15, 'system undo header',
16, 'system undo block',
17, 'undo header',
18, 'undo block',
A.CLASS#)
CLASS,
DECODE (A.STATUS,
'free', 'Not currently in use',
'xcur', 'Exclusive',
'scur', 'Shared current',
'cr', 'Consistent read',
'read', 'Being read from disk',
'mrec', 'In media recovery mode',
'irec', 'In instance recovery mode',
A.STATUS)
STATUS
FROM GV$BH A, DBA_OBJECTS B, DBA_DATA_FILES C
WHERE A.OBJD = B.DATA_OBJECT_ID
AND A.FILE# = C.FILE_ID
AND B.OWNER='HELPDESK'
AND B.OBJECT_NAME='TESTTBL'
ORDER BY 4, 1;</code></pre>
<br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4; width: 400%px;"><tbody>
<tr><td>INST_ID</td><td>TABLESPACE_NAME</td><td>FILE_NAME</td><td>BLOCK#</td><td>OWNER</td><td>OBJECT_NAME</td><td>SUBOBJECT_NAME</td><td>OBJECT_TYPE</td><td>CLASS</td><td>STATUS</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>9</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>1st level bmb</td><td>Exclusive</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>10</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>2nd level bmb</td><td>Exclusive</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>11</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>segment header</td><td>Exclusive</td></tr>
</tbody></table>
<br />
So, TESTTBL's blocks 9 (1st level bitmap block), 10 (2nd level bitmap block) and 11(segment header) were loaded into Instance 1's buffer cache.<br />
These are the 3 used blocks. Let's take a segment header's (11) dump to verify it:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> EXEC DBMS_SPACE_ADMIN.SEGMENT_DUMP('HELPDESK2','437','11');
PL/SQL procedure successfully completed.</code></pre>
<br />
We read in the generated trace file:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>Segment Dump: segment dba = 644 : 0x6d40000b <b><--Block 11</b>
Segment Type - Pagetable Segment
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2720
Highwater:: 0x6d40000c ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x6d40000c </code><code><b><code><b><-- Highwater Mark at block 12</b></code></b><code></code> ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x6d400009
Level 1 BMB for Low HWM block: 0x6d400009
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001438
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x6d40000a
Last Level 1 BMB: 0x6d400009 <b><-- Block 9</b>
Last Level II BMB: 0x6d40000a <b><-- Block 10</b>
Last Level III BMB: 0x00000000 <b><-- There is no 3rd level bitmap block</b>
Map Header:: next 0x00000000 #extents: 1 obj#: 1667683 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x6d400009 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x6d400009 Data dba: 0x6d40000c
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x6d40000a
Dump of Second Level Bitmap Block
number: 1 nfree: 1 ffree: 0 pdba: 0x6d40000b
Inc #: 0 Objd: 1667683 </code><code><code><b><-- TESTTBL's data_object_id</b></code>
opcode:0
xid:
L1 Ranges :
--------------------------------------------------------
0x6d400009 Free: 5 Inst: 1
--------------------------------------------------------
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1 parent dba: 0x6d40000a poffset: 0
unformatted: 5 total: 8 first useful block: 3 <b><-- 8 blocks total, 5 blocks unused</b>
owning instance : 1 </code><code><code><b><-- Instance 1 has the ownership</b></code>
instance ownership changed at
Last successful Search
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0
Extent Map Block Offset: 4294967295
First free datablock : 3
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Inc #: 0 Objd: 1667683
HWM Flag: HWM Set
Highwater:: 0x6d40000c ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x6d400009 Length: 8 Offset: 0
0:Metadata 1:Metadata 2:Metadata 3:unformatted
4:unformatted 5:unformatted 6:unformatted 7:unformatted
--------------------------------------------------------<!------><!------><!------></code></pre>
<br />
We insert 1 row from Instance 1:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>1> INSERT INTO HELPDESK.TESTTBL VALUES(1, 'TEXT1');
1 row created.</code></pre>
<br />
All TESTTBL's blocks were loaded into Instance 1's buffer cache: <br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4; width: 400%px;"><tbody>
<tr><td>INST_ID</td><td>TABLESPACE_NAME</td><td>FILE_NAME</td><td>BLOCK#</td><td>OWNER</td><td>OBJECT_NAME</td><td>SUBOBJECT_NAME</td><td>OBJECT_TYPE</td><td>CLASS</td><td>STATUS</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>9</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>1st level bmb</td><td>Exclusive</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>10</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>2nd level bmb</td><td>Shared current</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>11</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>segment header</td><td>Consistent read</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>11</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>segment header</td><td>Exclusive</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>12</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>data block</td><td>Exclusive</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>13</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>data block</td><td>Exclusive</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>14</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>data block</td><td>Exclusive</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>15</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>data block</td><td>Exclusive</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>16</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>data block</td><td>Exclusive</td></tr>
</tbody></table>
<br />
We commit:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>1> COMMIT;
Commit complete.</code></pre>
<br />
Let's find out the block in which this row was stored:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(A.ROWID) BLOCK#, A.*
FROM HELPDESK.TESTTBL A;</code></pre>
<br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4; width: 400%px;"><tbody>
<tr><td>BLOCK#</td><td>ID</td><td>TEXT</td></tr>
<tr><td>16</td><td>1</td><td>TEXT1</td></tr>
</tbody></table>
<br />
The block where the row was stored (16) remained in exclusive (xcur) state and all other data blocks changed their state to shared current (scur): <br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4; width: 400%px;"><tbody>
<tr><td>INST_ID</td><td>TABLESPACE_NAME</td><td>FILE_NAME</td><td>BLOCK#</td><td>OWNER</td><td>OBJECT_NAME</td><td>SUBOBJECT_NAME</td><td>OBJECT_TYPE</td><td>CLASS</td><td>STATUS</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>9</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>1st level bmb</td><td>Exclusive</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>10</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>2nd level bmb</td><td>Shared current</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>11</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>segment header</td><td>Consistent read</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>11</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>segment header</td><td>Shared current</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>12</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>data block</td><td>Shared current</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>13</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>data block</td><td>Shared current</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>14</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>data block</td><td>Shared current</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>15</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>data block</td><td>Shared current</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>16</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>data block</td><td>Exclusive</td></tr>
</tbody></table>
<br />
We insert one more row from Intance 2, this time, and we commit:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>2> INSERT INTO HELPDESK.TESTTBL VALUES(2, 'TEXT2');
1 row created.
2> commit;
Commit complete.</code></pre>
<br />
This row was placed in block 12:<br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4; width: 400%px;"><tbody>
<tr><td>BLOCK#</td><td>ID</td><td>TEXT</td></tr>
<tr><td>12</td><td>2</td><td>TEXT2</td></tr>
<tr><td>16</td><td>1</td><td>TEXT1</td></tr>
</tbody></table>
<br />
Instance 1 kept an image of 1st level bmb (pi), Instance 2 got exclusive access (xcur) to 1st level bmb and 2nd level bmb, read (cr) the segment header and got exclusive access (xcur) to block 12, where the new row was stored: <br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4; width: 400%px;"><tbody>
<tr><td>INST_ID</td><td>TABLESPACE_NAME</td><td>FILE_NAME</td><td>BLOCK#</td><td>OWNER</td><td>OBJECT_NAME</td><td>SUBOBJECT_NAME</td><td>OBJECT_TYPE</td><td>CLASS</td><td>STATUS</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>9</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>1st level bmb</td><td>pi</td></tr>
<tr><td>2</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>9</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>1st level bmb</td><td>Exclusive</td></tr>
<tr><td>2</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>9</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>1st level bmb</td><td>Consistent read</td></tr>
<tr><td>2</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>9</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>1st level bmb</td><td>Consistent read</td></tr>
<tr><td>2</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>10</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>2nd level bmb</td><td>Exclusive</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>11</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>segment header</td><td>Shared current</td></tr>
<tr><td>2</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>11</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>segment header</td><td>Consistent read</td></tr>
<tr><td>2</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>11</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>segment header</td><td>Shared current</td></tr>
<tr><td>2</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>12</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>data block</td><td>Exclusive</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>16</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>data block</td><td>Exclusive</td></tr>
</tbody></table>
<br />
We, now, delete the 2nd row from Instance 1:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>1> DELETE FROM HELPDESK.TESTTBL WHERE ID=2;
1 row deleted.
1> COMMIT;
Commit complete.</code></pre>
<br />
Instance 1 got block 12 in exclusive (xcur) state: <br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4; width: 400%px;"><tbody>
<tr><td>INST_ID</td><td>TABLESPACE_NAME</td><td>FILE_NAME</td><td>BLOCK#</td><td>OWNER</td><td>OBJECT_NAME</td><td>SUBOBJECT_NAME</td><td>OBJECT_TYPE</td><td>CLASS</td><td>STATUS</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>9</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>1st level bmb</td><td>pi</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>9</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>1st level bmb</td><td>Exclusive</td></tr>
<tr><td>2</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>9</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>1st level bmb</td><td>pi</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>10</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>2nd level bmb</td><td>pi</td></tr>
<tr><td>2</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>10</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>2nd level bmb</td><td>Exclusive</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>11</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>segment header</td><td>Shared current</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>11</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>segment header</td><td>Consistent read</td></tr>
<tr><td>2</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>11</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>segment header</td><td>Shared current</td></tr>
<tr><td>2</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>11</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>segment header</td><td>Consistent read</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>12</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>data block</td><td>Exclusive</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>12</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>data block</td><td>Consistent read</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>12</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>data block</td><td>Consistent read</td></tr>
<tr><td>2</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>12</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>data block</td><td>pi</td></tr>
<tr><td>1</td><td>HELPDESK2</td><td>/oradb/helpdesk2_01.dbf</td><td>16</td><td>HELPDESK</td><td>TESTTBL</td><td></td><td>TABLE</td><td>data block</td><td>Exclusive</td></tr>
</tbody></table>
Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com0tag:blogger.com,1999:blog-6212636374045442782.post-50259553075890019512012-12-05T14:31:00.001+02:002013-03-01T09:46:45.476+02:00OMS 11g: Copy agent binaries to another server and reconfigureSystem admins cloned a server, including OMS agent's binaries, and I did not want to reinstall it.<br />
At start, agent should be down.<br />
Edit $OMS_HOME/sysman/config/emd.properties and change EMD_URL entry to your new server's hostname:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>EMD_URL=http://newserver:3872/emd/main/</code></pre>
<br />
Delete a few files:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>cd $OMS_HOME/sysman/emd
rm -rf state
rm -rf upload
rm lastupld.xml
rm agntstmp.txt
rm protocol.ini
rm blackouts.xml
rm targets.xml</code></pre>
<br />
Create a new targets.xml:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code><?xml version = "1.0"?>
<Targets>
</Targets></code></pre>
<br />
Start agent:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>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.</code></pre>
<br />
Upload:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>emctl upload
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</code></pre>
<br />
Get its status and check "Agent URL" is pointing to new server and if upload was successful:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 11.1.0.1.0
OMS Version : 11.1.0.1.0
Protocol Version : 11.1.0.0.0
Agent Home : /oragrid/agent11g
Agent binaries : /oragrid/agent11g
Agent Process ID : 8061080
Parent Process ID : 5701676
Agent URL : http://newserver:3872/emd/main/
Repository URL : http://OMSserver:4889/em/upload/
Started at : 2012-12-05 13:01:57
Started by user : oragrid
Last Reload : 2012-12-05 13:03:06
Last successful upload : 2012-12-05 13:03:09
Total Megabytes of XML files uploaded so far : 3.09
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 44.41%
Last successful heartbeat to OMS : 2012-12-05 13:03:00
---------------------------------------------------------------
Agent is Running and Ready</code></pre>
<br />
Now, if you login to your OMS, you will see your new agent running, but it will not monitor the host installed in. OMS will not even know in which host this agent runs.<br />
<br />
Stop agent:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>emctl stop agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.</code></pre>
<br />
Edit $OMS_HOME/sysman/emd/targets.xml and add the host target:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code><Targets AGENT_TOKEN="23e6c87aa252cb56f70800785be11d6071944de7">
<Target TYPE="oracle_emd" NAME="newserver:3872"/>
<b><Target TYPE="host" NAME="newserver"/></b>
</Targets>
</code></pre>
<br />
Start, upload and status once more:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>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.
emctl upload
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
emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 11.1.0.1.0
OMS Version : 11.1.0.1.0
Protocol Version : 11.1.0.0.0
Agent Home : /oragrid/agent11g
Agent binaries : /oragrid/agent11g
Agent Process ID : 5308612
Parent Process ID : 9633920
Agent URL : http://newserver:3872/emd/main/
Repository URL : http://OMSserver:4889/em/upload/
Started at : 2012-12-05 14:04:03
Started by user : oragrid
Last Reload : 2012-12-05 14:04:03
Last successful upload : 2012-12-05 14:04:15
Total Megabytes of XML files uploaded so far : 0.51
Number of XML files pending upload : 1
Size of XML files pending upload(MB) : 1.15
Available disk space on upload filesystem : 44.40%
Last successful heartbeat to OMS : 2012-12-05 14:04:05
---------------------------------------------------------------
Agent is Running and Ready</code></pre>
<br />
Now, login to your OMS add under Targets tab, All Targets subtab search for type Agent and your new server's hostname.<br />
You will find your new agent with only one monitoring target, your host.Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com0tag:blogger.com,1999:blog-6212636374045442782.post-39885075583762047952012-11-03T17:59:00.000+02:002013-02-20T09:27:08.073+02:00Linux: Create a service to automate Oracle BI Publisher 11g Trial Edition startup & shutdownI used OEL 6.3 and Oracle BI Publisher 11g Trial Edition is installed under "oracle" user in:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>/oracle/BI_Publisher_Trial</code></pre>
<br />
Create <b>bi_start.sh</b> in /home/oracle. This will start Publisher:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>#!/bin/bash
# Script to start BI Publisher
. ~/.bash_profile
# Start BI Publisher
nohup /oracle/BI_Publisher_Trial/bin/startBIP.sh &
exit 0</code></pre>
<br />
Create <b>bi_stop.sh</b> in /home/oracle. This will stop Publisher:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>#!/bin/bash
# Script to stop BI Publisher
. ~/.bash_profile
# Stop BI Publisher
/oracle/BI_Publisher_Trial/bin/stopBIP.sh
exit 0</code></pre>
<br />
Execute:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>chmod u+x /home/oracle/bi*.sh</code></pre>
<br />
Login as root and create <b>/etc/init.d/publisher</b>:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>#! /bin/bash
# chkconfig: 2345 99 10
# description: BI Publisher auto start-stop script.
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.
ORA_OWNER=oracle
RETVAL=0
case "$1" in
'start')
# Start BI Publisher:
su - $ORA_OWNER -c "/home/oracle/bi_start.sh"
touch /var/lock/subsys/publisher
;;
'stop')
# Stop BI Publisher:
su - $ORA_OWNER -c "/home/oracle/bi_stop.sh"
rm -f /var/lock/subsys/publisher
;;
*)
echo $"Usage: $0 {start|stop}"
RETVAL=1
esac
exit $RETVAL</code></pre>
<br />
Execute as root:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>chgrp dba /etc/init.d/publisher
chmod 750 /etc/init.d/publisher
chkconfig --add publisher</code></pre>
<br />
Check, if the service was created: <br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>chkconfig|grep publisher</code></pre>
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code> publisher 0:off 1:off 2:on 3:on 4:on 5:on 6:off</code></pre>
<br />
Test it:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>service publisher start
service publisher stop</code></pre>
Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com3tag:blogger.com,1999:blog-6212636374045442782.post-70914106893862540392012-10-16T11:50:00.000+03:002013-02-11T08:14:22.317+02:00RAC: AWR File IO Stats reportThe following query, basically, shows you the same information you get from the File IO Stats section of an AWR Report.<br />
In an AWR Roport this information is sorted by tablespace name and file name and if you have thousands of datafiles, you'll have a hard time to figure out which of them have, for instance, the highest reads.<br />
With this query, you can get your information sorted by any column you want:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>
SELECT SNAP1.INSTANCE_NUMBER "INSTANCE",
SNAP1.FILENAME "DATAFILE",
SNAP1.TABLESPACE_NAME "TABLESPACE",
SNAP1.CONTENTS "CONTENTS",
ROUND (SNAP1.BYTES / 1024 / 1024, 2) "SIZE MB",
SNAP2.PHYRDS - SNAP1.PHYRDS "PHYSICAL READS",
ROUND ((SNAP2.PHYRDS - SNAP1.PHYRDS) / (SNAP2.SECS - SNAP1.SECS), 2) "AVERAGE READS/SEC",
DECODE (SNAP2.PHYRDS - SNAP1.PHYRDS, 0, 0, ROUND ((SNAP2.PHYBLKRD - SNAP1.PHYBLKRD) / (SNAP2.PHYRDS - SNAP1.PHYRDS), 2)) "AVERAGE BLOCKS/READ",
DECODE (SNAP2.PHYRDS - SNAP1.PHYRDS, 0, 0, ROUND (((SNAP2.READTIM - SNAP1.READTIM) * 10) / (SNAP2.PHYRDS - SNAP1.PHYRDS), 2)) "AVERAGE READ (MS)",
SNAP2.PHYWRTS - SNAP1.PHYWRTS "PHYSICAL WRITES",
ROUND ((SNAP2.PHYWRTS - SNAP1.PHYWRTS) / (SNAP2.SECS - SNAP1.SECS), 2) "AVERAGE WRITES/SEC",
DECODE (SNAP2.PHYWRTS - SNAP1.PHYWRTS, 0, 0, ROUND ((SNAP2.PHYBLKWRT - SNAP1.PHYBLKWRT) / (SNAP2.PHYWRTS - SNAP1.PHYWRTS), 2)) "AVERAGE BLOCKS/WRITE",
DECODE (SNAP2.PHYWRTS - SNAP1.PHYWRTS, 0, 0, ROUND (((SNAP2.WRITETIM - SNAP1.WRITETIM) * 10) / (SNAP2.PHYWRTS - SNAP1.PHYWRTS), 2)) "AVERAGE WRITE (MS)",
SNAP2.WAIT_COUNT - SNAP1.WAIT_COUNT "BUFFER WAITS",
DECODE (SNAP2.WAIT_COUNT - SNAP1.WAIT_COUNT, 0, 0, ROUND (((SNAP2.TIME - SNAP1.TIME) * 10) / (SNAP2.WAIT_COUNT - SNAP1.WAIT_COUNT), 2)) "AVERAGE BUFFER WAIT (MS)"
FROM (SELECT A.INSTANCE_NUMBER,
A.FILENAME,
D.TABLESPACE_NAME,
D.CONTENTS,
E.BYTES,
TO_NUMBER(TO_CHAR(S.END_INTERVAL_TIME, 'SSSSS')) "SECS",
A.PHYRDS,
A.PHYWRTS,
A.WAIT_COUNT,
A.PHYBLKRD,
A.PHYBLKWRT,
A.READTIM,
A.WRITETIM,
A.TIME
FROM DBA_HIST_FILESTATXS A,
V$TABLESPACE C,
DBA_TABLESPACES D,
DBA_DATA_FILES E,
DBA_HIST_SNAPSHOT S
WHERE A.SNAP_ID = :BEGIN_SNAP
AND A.TS# = C.TS#
AND C.NAME = D.TABLESPACE_NAME
AND A.SNAP_ID = S.SNAP_ID
AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
AND A.FILENAME = E.FILE_NAME
UNION
SELECT A.INSTANCE_NUMBER,
A.FILENAME,
D.TABLESPACE_NAME,
D.CONTENTS,
E.BYTES,
TO_NUMBER(TO_CHAR(S.END_INTERVAL_TIME, 'SSSSS')) "SECS",
A.PHYRDS,
A.PHYWRTS,
A.WAIT_COUNT,
A.PHYBLKRD,
A.PHYBLKWRT,
A.READTIM,
A.WRITETIM,
A.TIME
FROM DBA_HIST_TEMPSTATXS A,
V$TABLESPACE C,
DBA_TABLESPACES D,
DBA_TEMP_FILES E,
DBA_HIST_SNAPSHOT S
WHERE A.SNAP_ID = :BEGIN_SNAP
AND A.TS# = C.TS#
AND C.NAME = D.TABLESPACE_NAME
AND A.SNAP_ID = S.SNAP_ID
AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
AND A.FILENAME = E.FILE_NAME) SNAP1,
(SELECT A.INSTANCE_NUMBER,
A.FILENAME,
D.TABLESPACE_NAME,
D.CONTENTS,
E.BYTES,
TO_NUMBER(TO_CHAR(S.END_INTERVAL_TIME, 'SSSSS')) "SECS",
A.PHYRDS,
A.PHYWRTS,
A.WAIT_COUNT,
A.PHYBLKRD,
A.PHYBLKWRT,
A.READTIM,
A.WRITETIM,
A.TIME
FROM DBA_HIST_FILESTATXS A,
V$TABLESPACE C,
DBA_TABLESPACES D,
DBA_DATA_FILES E,
DBA_HIST_SNAPSHOT S
WHERE A.SNAP_ID = :END_SNAP
AND A.TS# = C.TS#
AND C.NAME = D.TABLESPACE_NAME
AND A.SNAP_ID = S.SNAP_ID
AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
AND A.FILENAME = E.FILE_NAME
UNION
SELECT A.INSTANCE_NUMBER,
A.FILENAME,
D.TABLESPACE_NAME,
D.CONTENTS,
E.BYTES,
TO_NUMBER(TO_CHAR(S.END_INTERVAL_TIME, 'SSSSS')) "SECS",
A.PHYRDS,
A.PHYWRTS,
A.WAIT_COUNT,
A.PHYBLKRD,
A.PHYBLKWRT,
A.READTIM,
A.WRITETIM,
A.TIME
FROM DBA_HIST_TEMPSTATXS A,
V$TABLESPACE C,
DBA_TABLESPACES D,
DBA_TEMP_FILES E,
DBA_HIST_SNAPSHOT S
WHERE A.SNAP_ID = :END_SNAP
AND A.TS# = C.TS#
AND C.NAME = D.TABLESPACE_NAME
AND A.SNAP_ID = S.SNAP_ID
AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
AND A.FILENAME = E.FILE_NAME) SNAP2
WHERE SNAP1.FILENAME = SNAP2.FILENAME
AND SNAP1.INSTANCE_NUMBER = SNAP2.INSTANCE_NUMBER
ORDER BY 3, 2;</code></pre>
<br />
Also, AWR reports are per instance. It does not give you an option to get those metrics aggregated for all your instances.<br />
The following query does that:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT SNAP1.FILENAME "DATAFILE",
SNAP1.TABLESPACE_NAME "TABLESPACE",
SNAP1.CONTENTS "CONTENTS",
ROUND (SNAP1.BYTES / 1024 / 1024, 2) "SIZE MB",
SNAP2.PHYRDS - SNAP1.PHYRDS "PHYSICAL READS",
ROUND ((SNAP2.PHYRDS - SNAP1.PHYRDS) / (SNAP2.SECS - SNAP1.SECS), 2) "AVERAGE READS/SEC",
DECODE (SNAP2.PHYRDS - SNAP1.PHYRDS, 0, 0, ROUND ((SNAP2.PHYBLKRD - SNAP1.PHYBLKRD) / (SNAP2.PHYRDS - SNAP1.PHYRDS), 2)) "AVERAGE BLOCKS/READ",
DECODE (SNAP2.PHYRDS - SNAP1.PHYRDS, 0, 0, ROUND (((SNAP2.READTIM - SNAP1.READTIM) * 10) / (SNAP2.PHYRDS - SNAP1.PHYRDS), 2)) "AVERAGE READ (MS)",
SNAP2.PHYWRTS - SNAP1.PHYWRTS "PHYSICAL WRITES",
ROUND ((SNAP2.PHYWRTS - SNAP1.PHYWRTS) / (SNAP2.SECS - SNAP1.SECS), 2) "AVERAGE WRITES/SEC",
DECODE (SNAP2.PHYWRTS - SNAP1.PHYWRTS, 0, 0, ROUND ((SNAP2.PHYBLKWRT - SNAP1.PHYBLKWRT) / (SNAP2.PHYWRTS - SNAP1.PHYWRTS), 2)) "AVERAGE BLOCKS/WRITE",
DECODE (SNAP2.PHYWRTS - SNAP1.PHYWRTS, 0, 0, ROUND (((SNAP2.WRITETIM - SNAP1.WRITETIM) * 10) / (SNAP2.PHYWRTS - SNAP1.PHYWRTS), 2)) "AVERAGE WRITE (MS)",
SNAP2.WAIT_COUNT - SNAP1.WAIT_COUNT "BUFFER WAITS",
DECODE (SNAP2.WAIT_COUNT - SNAP1.WAIT_COUNT, 0, 0, ROUND (((SNAP2.TIME - SNAP1.TIME) * 10) / (SNAP2.WAIT_COUNT - SNAP1.WAIT_COUNT), 2)) "AVERAGE BUFFER WAIT (MS)"
FROM ( SELECT A.FILENAME,
D.TABLESPACE_NAME,
D.CONTENTS,
E.BYTES,
SUM (TO_NUMBER (TO_CHAR (S.END_INTERVAL_TIME, 'SSSSS'))) "SECS",
SUM (A.PHYRDS) "PHYRDS",
SUM (A.PHYWRTS) "PHYWRTS",
SUM (A.WAIT_COUNT) "WAIT_COUNT",
SUM (A.PHYBLKRD) "PHYBLKRD",
SUM (A.PHYBLKWRT) "PHYBLKWRT",
SUM (A.READTIM) "READTIM",
SUM (A.WRITETIM) "WRITETIM",
SUM (A.TIME) "TIME"
FROM DBA_HIST_FILESTATXS A,
V$TABLESPACE C,
DBA_TABLESPACES D,
DBA_DATA_FILES E,
DBA_HIST_SNAPSHOT S
WHERE A.SNAP_ID = :BEGIN_SNAP
AND A.TS# = C.TS#
AND C.NAME = D.TABLESPACE_NAME
AND A.SNAP_ID = S.SNAP_ID
AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
AND A.FILENAME = E.FILE_NAME
GROUP BY A.FILENAME,
D.TABLESPACE_NAME,
D.CONTENTS,
E.BYTES
UNION
SELECT A.FILENAME,
D.TABLESPACE_NAME,
D.CONTENTS,
E.BYTES,
SUM (TO_NUMBER (TO_CHAR (S.END_INTERVAL_TIME, 'SSSSS'))) "SECS",
SUM (A.PHYRDS) "PHYRDS",
SUM (A.PHYWRTS) "PHYWRTS",
SUM (A.WAIT_COUNT) "WAIT_COUNT",
SUM (A.PHYBLKRD) "PHYBLKRD",
SUM (A.PHYBLKWRT) "PHYBLKWRT",
SUM (A.READTIM) "READTIM",
SUM (A.WRITETIM) "WRITETIM",
SUM (A.TIME) "TIME"
FROM DBA_HIST_TEMPSTATXS A,
V$TABLESPACE C,
DBA_TABLESPACES D,
DBA_TEMP_FILES E,
DBA_HIST_SNAPSHOT S
WHERE A.SNAP_ID = :BEGIN_SNAP
AND A.TS# = C.TS#
AND C.NAME = D.TABLESPACE_NAME
AND A.SNAP_ID = S.SNAP_ID
AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
AND A.FILENAME = E.FILE_NAME
GROUP BY A.FILENAME,
D.TABLESPACE_NAME,
D.CONTENTS,
E.BYTES) SNAP1,
( SELECT A.FILENAME,
D.TABLESPACE_NAME,
D.CONTENTS,
E.BYTES,
SUM (TO_NUMBER (TO_CHAR (S.END_INTERVAL_TIME, 'SSSSS'))) "SECS",
SUM (A.PHYRDS) "PHYRDS",
SUM (A.PHYWRTS) "PHYWRTS",
SUM (A.WAIT_COUNT) "WAIT_COUNT",
SUM (A.PHYBLKRD) "PHYBLKRD",
SUM (A.PHYBLKWRT) "PHYBLKWRT",
SUM (A.READTIM) "READTIM",
SUM (A.WRITETIM) "WRITETIM",
SUM (A.TIME) "TIME"
FROM DBA_HIST_FILESTATXS A,
V$TABLESPACE C,
DBA_TABLESPACES D,
DBA_DATA_FILES E,
DBA_HIST_SNAPSHOT S
WHERE A.SNAP_ID = :END_SNAP
AND A.TS# = C.TS#
AND C.NAME = D.TABLESPACE_NAME
AND A.SNAP_ID = S.SNAP_ID
AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
AND A.FILENAME = E.FILE_NAME
GROUP BY A.FILENAME,
D.TABLESPACE_NAME,
D.CONTENTS,
E.BYTES
UNION
SELECT A.FILENAME,
D.TABLESPACE_NAME,
D.CONTENTS,
E.BYTES,
SUM (TO_NUMBER (TO_CHAR (S.END_INTERVAL_TIME, 'SSSSS'))) "SECS",
SUM (A.PHYRDS) "PHYRDS",
SUM (A.PHYWRTS) "PHYWRTS",
SUM (A.WAIT_COUNT) "WAIT_COUNT",
SUM (A.PHYBLKRD) "PHYBLKRD",
SUM (A.PHYBLKWRT) "PHYBLKWRT",
SUM (A.READTIM) "READTIM",
SUM (A.WRITETIM) "WRITETIM",
SUM (A.TIME) "TIME"
FROM DBA_HIST_TEMPSTATXS A,
V$TABLESPACE C,
DBA_TABLESPACES D,
DBA_TEMP_FILES E,
DBA_HIST_SNAPSHOT S
WHERE A.SNAP_ID = :END_SNAP
AND A.TS# = C.TS#
AND C.NAME = D.TABLESPACE_NAME
AND A.SNAP_ID = S.SNAP_ID
AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
AND A.FILENAME = E.FILE_NAME
GROUP BY A.FILENAME,
D.TABLESPACE_NAME,
D.CONTENTS,
E.BYTES) SNAP2
WHERE SNAP1.FILENAME = SNAP2.FILENAME
ORDER BY 2, 1;</code></pre>
Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com0tag:blogger.com,1999:blog-6212636374045442782.post-58979688824165613062012-10-10T09:40:00.000+03:002012-10-10T09:40:29.258+03:00Procedure to automate datafiles' resizing taking into account filesystems' free spaceI wanted to create a mechanism for our operators to be able to give more space to tablespaces, automatically, when there is a need to.<br />
The operators don't know the datafiles' location and the filesystems' free space, so the only thing they will do is to run a schell script with the tablespace name as a parameter. <br />
<br />
Unfortunately, oracle database does not store any information about the filesystems and their space usage, so if you are not careful you may end up with completely full filesystems.<br />
We can only get this info, using OS commands like bdf and df, so to make it available to our database, we will create a text file in our server, containing the output of those commands and an external table in the database to read that file.<br />
<br />
I created this example in HP-UX, so you will need to do a few modifications to make it run in Linux or other Unix.<br />
For example, bdf is an HP-UX command and you should use df in other Unix/Linux. <br />
<br />
First, create a directory pointing to the location, where our scripts and output files will be stored, e.g. /oradb/orabin/scripts:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>CREATE DIRECTORY SCRIPT_DIR AS '/oradb/orabin/scripts';</code></pre>
<br />
Now, create the external table, called df, which reads /oradb/orabin/scripts/bdf.txt:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>CREATE TABLE DF
(FILESYSTEM VARCHAR2(200),
KBYTES NUMBER,
USED NUMBER,
AVAIL NUMBER,
USED_PER VARCHAR2(20),
MOUNTED VARCHAR2(100)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY SCRIPT_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE SCRIPT_DIR:'bdf%a_%p.bad'
LOGFILE SCRIPT_DIR:'bdf%a_%p.log'
FIELDS TERMINATED BY ' '
MISSING FIELD VALUES ARE NULL
)
LOCATION ('bdf.txt')
)
REJECT LIMIT UNLIMITED;</code></pre>
<br />
Our script will make some of the given tablespace's datafiles autoextensible, taking into account the filesystems' free space.<br />
We discard any datafiles there are in filesystems with > 99% of used space.<br />
For all others, we autoextend them to a size equal to:<br />
CURRENT DATAFILE SIZE + 10% OF FILESYSTEM'S FREE SPACE<br />
If the above size is greater than tablespace's block size * 4194302, then we set MAXSIZE UNLIMITED, to avoid to get an ORA error.<br />
So, create file /oradb/orabin/scripts/extend.sql:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SET SERVEROUTPUT ON
SPOOL OUT.TXT
DECLARE
V_STMT VARCHAR2 (1000);
V_BSIZE NUMBER;
CURSOR C1
IS
SELECT A.FILE_NAME,
A.BYTES,
B.AVAIL,
(BYTES / 1024) + (AVAIL * 0.1) NEW_SIZE
FROM DBA_DATA_FILES A, DF B
WHERE A.TABLESPACE_NAME = UPPER ('&1')
AND SUBSTR (A.FILE_NAME, 1, INSTR (A.FILE_NAME, '/', -1, 2) - 1) = B.MOUNTED
AND SUBSTR (B.USED_PER, 1, INSTR (B.USED_PER, '%') - 1) < 99;
BEGIN
SELECT BLOCK_SIZE
INTO V_BSIZE
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = UPPER ('&1');
FOR DFILE IN C1
LOOP
DBMS_OUTPUT.PUT_LINE ('Filename: ' || DFILE.FILE_NAME);
DBMS_OUTPUT.PUT_LINE ('Size (KB): ' || DFILE.BYTES / 1024);
DBMS_OUTPUT.PUT_LINE ('Avail (KB): ' || DFILE.AVAIL);
IF DFILE.NEW_SIZE < V_BSIZE * 4194302 / 1024
THEN
V_STMT := 'ALTER DATABASE DATAFILE ''' || DFILE.FILE_NAME || ''' AUTOEXTEND ON NEXT 128M MAXSIZE ' || DFILE.NEW_SIZE || 'K';
ELSE
V_STMT := 'ALTER DATABASE DATAFILE ''' || DFILE.FILE_NAME || ''' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED';
END IF;
DBMS_OUTPUT.PUT_LINE (V_STMT);
EXECUTE IMMEDIATE V_STMT;
DBMS_OUTPUT.PUT_LINE ('--------------------------');
END LOOP;
END;
/
SPOOL OFF</code></pre>
<br />
Finally, we create the shell script /oradb/orabin/scripts/extend.sh, which creates the bdf.txt with the appropriate format for our external table. It also sends an email to the DBAs, so they get informed that the procedure run and which datafiles were affected.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>#!/bin/sh
ORACLE_HOME=/oradb/orabin/app/product/db1
export ORACLE_HOME
ORACLE_SID=OTE
export ORACLE_SID
rm bdf*
rm original.txt
bdf|grep -v File|grep -v vol|grep -v odm|grep -v shared>original.txt
sed -e "s/ */ /g" original.txt > bdf.txt
cd /oradb/orabin/scripts
sqlplus -s /nolog << EOF
connect / as sysdba
@extend.sql $1
exit
EOF
cat OUT.TXT|mailx -s "Autoextend script run for $ORACLE_SID" name@ote.gr</code></pre>
<br />
Now, the only thing the operator has to do is run extend.sh with the tablespace name as a parameter:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>extend.sh user_data</code></pre>
Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com2tag:blogger.com,1999:blog-6212636374045442782.post-9331198443014591802012-09-24T14:16:00.000+03:002012-10-03T09:18:21.169+03:00View alert log from sqlIn 11g, there is an easy way to read you database's alert log, using table X$DBGALERTEXT:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT ORIGINATING_TIMESTAMP,
MODULE_ID,
PROCESS_ID,
MESSAGE_TEXT
FROM X$DBGALERTEXT
ORDER BY ORIGINATING_TIMESTAMP DESC;</code></pre>
<br />
In pre-11g databases, you have to read it, using another way, like UTL_FILE package.<br />
The following procedure, will put in an ALERT.LOG file a percent [default 5%] of your database's alert log.<br />
If you want all your alert log to be fetched, then set V_PERCENT = 1.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SET SERVEROUTPUT ON
SPOOL ALERT.LOG
DECLARE
V_PERCENT NUMBER := .05;
V_BDUMP VARCHAR2 (1000);
V_NAME VARCHAR2 (20);
V_FILE VARCHAR2 (20);
V_LINE VARCHAR2 (8000);
V_HANDLE UTL_FILE.FILE_TYPE;
V_DIR_STMT VARCHAR2 (100);
V_SIZE INTEGER;
V_BSIZE INTEGER;
V_EXISTS BOOLEAN;
BEGIN
SELECT VALUE
INTO V_BDUMP
FROM V$PARAMETER
WHERE NAME = 'background_dump_dest';
DBMS_OUTPUT.PUT_LINE (
'**************************** INFO *************************');
DBMS_OUTPUT.PUT_LINE ('BDUMP: ' || V_BDUMP);
SELECT VALUE
INTO V_NAME
FROM V$PARAMETER
WHERE NAME = 'instance_name';
DBMS_OUTPUT.PUT_LINE ('DB NAME: ' || V_NAME);
V_FILE := 'alert_' || V_NAME || '.log';
DBMS_OUTPUT.PUT_LINE ('FILENAME: ' || V_FILE);
V_DIR_STMT := 'CREATE DIRECTORY ALERT AS ''' || V_BDUMP || '''';
DBMS_OUTPUT.PUT_LINE ('COMMAND: ' || V_DIR_STMT);
EXECUTE IMMEDIATE V_DIR_STMT;
V_HANDLE := UTL_FILE.FOPEN ('ALERT', V_FILE, 'r');
UTL_FILE.FGETATTR ('ALERT',
V_FILE,
V_EXISTS,
V_SIZE,
V_BSIZE);
DBMS_OUTPUT.PUT_LINE ('ALERT SIZE: ' || V_SIZE / 1024 / 1024 || ' MB');
UTL_FILE.FSEEK (V_HANDLE, NULL, V_SIZE * (1 - V_PERCENT));
DBMS_OUTPUT.PUT_LINE (
'FETCHED ALERT SIZE: ' || V_SIZE * (V_PERCENT / 1024 / 1024) || ' MB');
DBMS_OUTPUT.PUT_LINE (
'********************************************************');
LOOP
BEGIN
UTL_FILE.GET_LINE (V_HANDLE, V_LINE);
DBMS_OUTPUT.PUT_LINE (V_LINE);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;
END LOOP;
UTL_FILE.FCLOSE (V_HANDLE);
EXECUTE IMMEDIATE 'DROP DIRECTORY ALERT';
END;
/
SPOOL OFF;</code></pre>
Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com1tag:blogger.com,1999:blog-6212636374045442782.post-67405546006653849022012-08-04T09:12:00.001+03:002012-08-04T09:20:38.264+03:00Calculate actual free space of datafiles and tablespacesLet's say, we have a 10GB datafile, autoexendible to 20GB. Querying DBA_FREE_SPACE we see this datafile has 2GB free space.<br />
The actual free space is <b>MAX_SIZE - SIZE + FREE_SPACE</b>, so for our example is 20GB - 10GB + 2GB = 12GB.<br />
The following query will display this datafiles' free space [TOTAL FREE SPACE GB] per tablespaces.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code> SELECT B.TABLESPACE_NAME,
B.FILE_NAME,
ROUND (B.BYTES / 1024 / 1024 / 1024, 2) "SIZE GB",
ROUND (B.MAXBYTES / 1024 / 1024 / 1024, 2) "MAX SIZE GB",
ROUND (SUM (A.BYTES) / 1024 / 1024 / 1024, 2) "FREE SPACE GB",
CASE
WHEN MAXBYTES = 0
THEN
ROUND (SUM (A.BYTES) / 1024 / 1024 / 1024, 2)
ELSE
ROUND (
( (B.MAXBYTES - B.BYTES) + SUM (A.BYTES)) / 1024 / 1024 / 1024, 2)
END
"TOTAL FREE SPACE GB"
FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
WHERE A.FILE_ID = B.FILE_ID AND B.TABLESPACE_NAME IN ('CBAM')
GROUP BY B.TABLESPACE_NAME,
B.FILE_NAME,
B.BYTES,
B.MAXBYTES
ORDER BY 1, 2;</code></pre>
<br />
The output is: <br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4;" width="100%"><tbody>
<tr><td>TABLESPACE_NAME</td><td>FILE_NAME</td><td>SIZE GB</td><td>MAX SIZE GB</td><td>FREE SPACE GB</td><td>TOTAL FREE SPACE GB</td></tr>
<tr><td>CBAM</td><td>/oradb/SOADB/oradata1/cbam.dbf</td><td>9.77</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>CBAM</td><td>/oradb/SOADB/oradata6/cbam14.dbf</td><td>19.53</td><td>19.53</td><td>0</td><td>0</td></tr>
<tr><td>CBAM</td><td>/oradb/SOADB/oradata6/cbam18.dbf</td><td>19.53</td><td>19.53</td><td>0.01</td><td>0.01</td></tr>
<tr><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td></tr>
<tr><td>CBAM</td><td>/oradb/SOADB/oradata6/cbam27.dbf</td><td>12.5</td><td>25</td><td>11.92</td><td>24.42</td></tr>
<tr><td>CBAM</td><td>/oradb/SOADB/oradata7/cbam25.dbf</td><td>25</td><td>25</td><td>0.03</td><td>0.03</td></tr>
<tr><td>CBAM</td><td>/oradb/SOADB/oradata7/cbam28.dbf</td><td>12.11</td><td>24.41</td><td>11.8</td><td>24.1</td></tr>
</tbody></table>
<br />
This query will not return all tablespaces' datafiles, only those with at least a few KB of free space and ,thus, at least 1 row in DBA_FREE_SPACE.<br />
<br />
Another query to sum this free space per tablespace:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code> SELECT B.TABLESPACE_NAME,
ROUND (SUM (B.BYTES) / 1024 / 1024 / 1024, 2) "SIZE GB",
ROUND (SUM (B.MAXBYTES) / 1024 / 1024 / 1024, 2) "MAX SIZE GB",
A.FREE_SPACE_GB "FREE SPACE GB",
ROUND (
(SUM (B.MAXBYTES - B.BYTES) / 1024 / 1024 / 1024) + A.FREE_SPACE_GB,
2)
"TOTAL FREE SPACE GB"
FROM DBA_DATA_FILES B,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / 1024 / 1024 / 1024, 2) FREE_SPACE_GB
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME IN ('CBAM')
GROUP BY TABLESPACE_NAME) A
WHERE B.TABLESPACE_NAME IN ('CBAM')
AND B.TABLESPACE_NAME = A.TABLESPACE_NAME
AND B.MAXBYTES != 0
GROUP BY B.TABLESPACE_NAME, A.FREE_SPACE_GB
ORDER BY 1;</code></pre>
<br />
The output:<br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4;" width="100%"><tbody>
<tr><td>TABLESPACE_NAME</td><td>SIZE GB</td><td>MAX SIZE GB</td><td>FREE SPACE GB</td><td>TOTAL FREE SPACE GB</td></tr>
<tr><td>CBAM</td><td>284.58</td><td>309.38</td><td>23.86</td><td>48.66</td></tr>
</tbody></table>
<br />
Again, this query will not show you the tablespace's full size. In "SIZE GB" column are added the datafiles' sizes which have free space.Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com3tag:blogger.com,1999:blog-6212636374045442782.post-31498107846403611342012-07-30T11:30:00.000+03:002013-02-13T08:38:38.849+02:00Script to check if AWR snapshot collection has stoppedIn one database, after the archive log destination got full, snapshot collection stopped.<br />
There was no way to know this had happened. Only in the MMON trace file I got the following message, for which there is no reference in Metalink:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>Unable to schedule a MMON slave at: Auto Flush Main 1
Slave action has been temporarily suspended
- Slave action had prior policy violations.
Unknown return code: 101</code></pre>
<br />
So, I had 2 choices. Either to restart the database or to kill MMON and MMNL processes.<br />
I chose the latter.<br />
You will get these in your alert log, when they restart:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>Restarting dead background process MMON
Restarting dead background process MMNL
Mon Jul 30 08:18:24 2012
MMON started with pid=18, OS id=10223826
Mon Jul 30 08:18:24 2012
MMNL started with pid=19, OS id=15204612 </code></pre>
<br />
Now snapshot collection is restored.<br />
<br />
If you compare the time passed from the last snapshot collected and the interval you have set for snapshot collection, you will find out if the collection is halted.<br />
For instance, if your interval is set to 1 hour (the default) and the last snapshot was collected more than 1 hour ago, then you may assume something is wrong.<br />
The following script will return 0, if [time from last snapshot] < [snapshot interval] and there is no problem and 1 otherwise.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT (CASE WHEN NUMTODSINTERVAL ( (SYSDATE - MAX(CAST(END_INTERVAL_TIME AS DATE))), 'DAY') < SNAP_INTERVAL THEN 0 ELSE 1 END) "RESULT"
FROM DBA_HIST_SNAPSHOT, DBA_HIST_WR_CONTROL
GROUP BY SNAP_INTERVAL;</code></pre>
<br />
If you want to create a <a href="http://lefterhs.blogspot.gr/2009/02/grid-control-create-user-defined.html">database user-defined metric</a> to monitor this, you should use a function.<br />
The CASE statement confuses OEM and rejects the query.<br />
First, connect as SYSDBA and grant SELECT on DBA_HIST_SNAPSHOT and DBA_HIST_WR_CONTROL to SYSTEM.<br />
The function is:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>CREATE OR REPLACE FUNCTION SYSTEM.CHECKAWR RETURN NUMBER
IS
V_RESULT NUMBER;
BEGIN
SELECT (CASE WHEN NUMTODSINTERVAL ( (SYSDATE - MAX(CAST(END_INTERVAL_TIME AS DATE))), 'DAY') < SNAP_INTERVAL THEN 0 ELSE 1 END) "RESULT" INTO V_RESULT
FROM DBA_HIST_SNAPSHOT, DBA_HIST_WR_CONTROL
GROUP BY SNAP_INTERVAL;
RETURN V_RESULT;
END;
/</code></pre>
<br />
Now, in the "SQL Query" box, put:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT SYSTEM.CHECKAWR FROM DUAL; </code></pre>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_6i0rNE9DeU1nP2vMq1IkZyGuya-DzkSA2OR_AHw3XYFSfdFXp8fvS6lgjzChb8_cl2urdjl0m8vWHpgy5TF0WIukkOLnQoeKX_8_KK97hoxW9zMOR0lUCbNzGz6klyDKEHqAvDaBBc4/s1600/Clipboard01.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="201" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_6i0rNE9DeU1nP2vMq1IkZyGuya-DzkSA2OR_AHw3XYFSfdFXp8fvS6lgjzChb8_cl2urdjl0m8vWHpgy5TF0WIukkOLnQoeKX_8_KK97hoxW9zMOR0lUCbNzGz6klyDKEHqAvDaBBc4/s320/Clipboard01.jpg" width="320" /></a></div>
<br />Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com2tag:blogger.com,1999:blog-6212636374045442782.post-33097518641366233022012-07-24T17:42:00.000+03:002012-07-24T17:42:55.784+03:00RAC: Log switch frequency scriptThe following script reports how much time passed between log switches in a time period.<br />
Although it's written to provide information per instance in RACs, it will also work in Single Instances.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT C.INSTANCE,
C.THREAD#,
B.SEQUENCE# "START SEQUENCE",
TO_CHAR (B.FIRST_TIME, 'DD-MM-YYYY HH24:MI:SS') "START TIME",
A.SEQUENCE# "END SEQUENCE",
TO_CHAR (A.FIRST_TIME, 'DD-MM-YYYY HH24:MI:SS') "END TIME",
TO_CHAR (
TRUNC (SYSDATE)
+ NUMTODSINTERVAL ( (A.FIRST_TIME - B.FIRST_TIME) * 86400,
'SECOND'),
'HH24:MI:SS')
DURATION
FROM V$LOG_HISTORY A, V$LOG_HISTORY B, V$THREAD C
WHERE A.SEQUENCE# = B.SEQUENCE# + 1
AND A.THREAD# = C.THREAD#
AND B.THREAD# = C.THREAD#
AND A.FIRST_TIME BETWEEN TO_DATE ('23-07-2012 00:00:00',
'DD-MM-YYYY HH24:MI:SS')
AND TO_DATE ('24-07-2012 00:00:00',
'DD-MM-YYYY HH24:MI:SS')
ORDER BY 4;</code></pre>
<br />
Let's try it for a day, e.g. 23/07/12: <br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4;"><tbody>
<tr><td>INSTANCE</td><td>THREAD#</td><td>START SEQUENCE</td><td>START TIME</td><td>END SEQUENCE</td><td>END TIME</td><td>DURATION</td></tr>
<tr><td>OTE1</td><td>1</td><td>436571</td><td>22-07-12 23:59:44</td><td>436572</td><td>23-07-12 00:08:12</td><td>00:08:27</td></tr>
<tr><td>OTE2</td><td>2</td><td>402951</td><td>22-07-12 23:59:46</td><td>402952</td><td>23-07-12 00:15:31</td><td>00:15:45</td></tr>
<tr><td>OTE1</td><td>1</td><td>436572</td><td>23-07-12 00:08:12</td><td>436573</td><td>23-07-12 00:11:48</td><td>00:03:36</td></tr>
<tr><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td></tr>
<tr><td>OTE2</td><td>2</td><td>403315</td><td>23-07-12 23:57:51</td><td>403316</td><td>23-07-12 23:59:42</td><td>00:01:51</td></tr>
<tr><td>OTE1</td><td>1</td><td>436873</td><td>23-07-12 23:58:15</td><td>436874</td><td>23-07-12 23:59:10</td><td>00:00:54</td></tr>
<tr><td>OTE1</td><td>1</td><td>436874</td><td>23-07-12 23:59:10</td><td>436875</td><td>23-07-12 23:59:40</td><td>00:00:29</td></tr>
</tbody></table>Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com0tag:blogger.com,1999:blog-6212636374045442782.post-23276493532665415712012-07-24T09:07:00.000+03:002012-11-20T18:14:32.664+02:00ORA-13767 ORA-13780 creating SQL Tuning Task in 11.2.0.3<div class="separator" style="clear: both; text-align: left;">
</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiL_XGaGvr-AtDzB6YRIxhLsYwIpEgeL5B1d4Nmatc3XfU_KT7Y88Bv-MIbQSEcAtJOjPjPKT0mxBAjQV_MrDzjc6AMeqeXCKahtIU4UZP08-OjXakj6t-yBFqmHIfiHUsKbL0e1cnumSg/s1600/ora-13767b.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="210" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiL_XGaGvr-AtDzB6YRIxhLsYwIpEgeL5B1d4Nmatc3XfU_KT7Y88Bv-MIbQSEcAtJOjPjPKT0mxBAjQV_MrDzjc6AMeqeXCKahtIU4UZP08-OjXakj6t-yBFqmHIfiHUsKbL0e1cnumSg/s320/ora-13767b.jpg" width="320" /></a>Tried to create a SQL Tuning Task from Grid Control 11g in a 11.2.0.3 database and I got a ORA-13767 error.</div>
<div style="text-align: left;">
Tried to create it by running <i>DBMS_SQLTUNE.CREATE_TUNING_TASK</i> directly to the target database and got a ORA-13780.</div>
<div style="text-align: left;">
</div>
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 L_SQL_TUNE_TASK_ID VARCHAR2(100);
3 BEGIN
4 L_SQL_TUNE_TASK_ID := DBMS_SQLTUNE.CREATE_TUNING_TASK (
5 SQL_ID => 'fcs0af97qvuft',
6 SCOPE => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
7 TIME_LIMIT => 600,
8 TASK_NAME => 'fcs0af97qvuft_task',
9 DESCRIPTION => 'Tuning task for fcs0af97qvuft');
10 DBMS_OUTPUT.PUT_LINE('l_sql_tune_task_id: ' || L_SQL_TUNE_TASK_ID);
11 END;
12 /
DECLARE
*
ERROR AT LINE 1:
ORA-13780: SQL statement does not exist.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 125
ORA-06512: at "SYS.DBMS_SQLTUNE", line 655
ORA-06512: at line 4</code></pre>
<br />
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMJ8zjFDRLh4A8Z4inIWNG9RHHKnVLpN1Sew5HfISLWnbxrVBqtd_jJcIdLhjAifZUf4Eg0xCMP09L3MiMv_T9DkMFICPC1SrVqoS83TMCfAB7yB08zadHVy9E4xAtBJsudGSOXPYSBd8/s1600/ora-13780_dbconsoleb.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="209" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMJ8zjFDRLh4A8Z4inIWNG9RHHKnVLpN1Sew5HfISLWnbxrVBqtd_jJcIdLhjAifZUf4Eg0xCMP09L3MiMv_T9DkMFICPC1SrVqoS83TMCfAB7yB08zadHVy9E4xAtBJsudGSOXPYSBd8/s320/ora-13780_dbconsoleb.jpg" width="320" /></a>Installed DB Control in the target database and I got a ORA-13780 again.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
If I specify in the task 2 snapshots that include an execution of the particular SQL statement, I get no error:</div>
<div style="text-align: left;">
</div>
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 L_SQL_TUNE_TASK_ID VARCHAR2(100);
3 BEGIN
4 L_SQL_TUNE_TASK_ID := DBMS_SQLTUNE.CREATE_TUNING_TASK (
5 BEGIN_SNAP=>3935,
6 END_SNAP=>3937,
7 SQL_ID => 'fcs0af97qvuft',
8 SCOPE => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
9 TIME_LIMIT => 600,
10 TASK_NAME => 'fcs0af97qvuft_task',
11 DESCRIPTION => 'Tuning task for fcs0af97qvuft');
12 DBMS_OUTPUT.PUT_LINE('l_sql_tune_task_id: ' || L_SQL_TUNE_TASK_ID);
13 END;
14 /
l_sql_tune_task_id: fcs0af97qvuft_task
PL/SQL procedure successfully completed.</code></pre>
<br />
You can find in which snapshots your query exists, by running the following query:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT SNAP_ID
FROM DBA_HIST_SQLSTAT
WHERE SQL_ID='fcs0af97qvuft'
ORDER BY SNAP_ID;</code></pre>
<br />
<div style="text-align: left;">
The only relevant Oracle Note is <span style="text-align: left; width: 575px;">1445188.1, but it states that Bug 9957725 is fixed in DB Control 11.2.0.3 and 12g.</span></div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
Oracle filed Bug 14342949 for this issue.<br />
<br />
<b>20/11/2012</b>: From Oracle Development:<br />
<blockquote class="tr_bq">
<i><span style="text-align: left; width: 575px;">It looks like the error message is expected. Once the SQL is selected and you are on the SQL Details page : If the Statistics tab / panel shows "Snapshot Time : date/time to date/time : No data is available for this statement in this snapshot duration".</span><br /><span style="text-align: left; width: 575px;"></span><br /><span style="text-align: left; width: 575px;">Selecting the "Schedule SQL Tuning Advisor" will run the dbms_sqltune.create_tuning_task with that begin_snap and end_snap, which correctly results in the ORA-13780 error. The screen</span><span style="text-align: left; width: 575px;">
will default to the current date/time, so customers need to select a
snapshot on the statistics tab/pane with data prior to calling the
"Schedule SQL Tuning Advisor".</span><br /><span style="text-align: left; width: 575px;"></span><br /><span style="text-align: left; width: 575px;">It seems to be expected behaviour.</span></i></blockquote>
</div>
Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com0tag:blogger.com,1999:blog-6212636374045442782.post-89678116325968631582012-07-17T12:53:00.000+03:002012-10-15T11:33:55.209+03:00Use of alternate archive destinationIf you want to specify a location to be an archive destination only in the event of a
failure of another destination, you can make it an alternate
destination.<br />
In our scenario, we have a rather small archive destination, which we will fill and see how the alternate will be used.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> SELECT DEST_NAME,STATUS,DESTINATION
FROM V$ARCHIVE_DEST
WHERE DESTINATION IS NOT NULL;
DEST_NAME STATUS DESTINATION
----------------------------------------------
LOG_ARCHIVE_DEST_1 VALID /oratest/archives</code></pre>
<br />
According to Oracle Note 369120.1, LOG_ARCHIVE_DEST_1 must have the NOREOPEN attribute set, for this to work. So:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/optim2/oratest/TESTDB/arch' SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/oratest/archives NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2' SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ALTERNATE SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ALTERNATE SCOPE=BOTH
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16028: new LOG_ARCHIVE_DEST_STATE_2 causes less destinations than
LOG_ARCHIVE_MIN_SUCCEED_DEST requires
SQL> SELECT DEST_NAME,STATUS,DESTINATION
FROM V$ARCHIVE_DEST
WHERE DESTINATION IS NOT NULL;
DEST_NAME STATUS DESTINATION
------------------------------------------------------------
LOG_ARCHIVE_DEST_1 ALTERNATE /oratest/archives
LOG_ARCHIVE_DEST_2 ALTERNATE /optim2/oratest/TESTDB/arch
SQL> show parameter LOG_ARCHIVE_DEST_STATE_1
NAME TYPE VALUE
------------------------ ------ ------
log_archive_dest_state_1 string ENABLE</code><code></code></pre>
<br />
I cannot understand why I get this error. I haven't changed LOG_ARCHIVE_DEST_1's state to ALTERNATE and the parameter shows its state is ENABLE not ALTERNATE.<br />
<br />
I connect from another terminal and execute the same query:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> SELECT DEST_NAME,STATUS,DESTINATION
FROM V$ARCHIVE_DEST
WHERE DESTINATION IS NOT NULL;
DEST_NAME STATUS DESTINATION
------------------------------------------------------------
LOG_ARCHIVE_DEST_1 VALID /oratest/archives
LOG_ARCHIVE_DEST_2 ALTERNATE /optim2/oratest/TESTDB/arch</code></pre>
<br />
<b>!?!?!?</b> I don't know why those query's results are different. If anyone has the time to test this case or know why this is happening, please, inform me.<br />
The database version is 11.2.0.3.<br />
<br />
Anyway, now we have an alternate destination and we will fill the default log destination.<br />
This is the part from the alert log, where the default archive log destination fills and all new archive logs are placed in the alternate destination:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>Thread 1 advanced to log sequence 144 (LGWR switch)
Current log# 3 seq# 144 mem# 0: /optim2/oratest/TESTDB/redo/redo03.log
Tue Jul 17 12:21:10 2012
ARC3: Error 19502 Closing archive log file '/oratest/archives/1_143_787911367.arc'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance TESTDB - Archival Error </code><b><code>LOG_ARCHIVE_DEST_1 filled</code></b><code>
ORA-16038: log 2 sequence# 143 cannot be archived
ORA-19502: write error on file "", block number (block size=)
ORA-00312: online log 2 thread 1: '/optim2/oratest/TESTDB/redo/redo02.log'
Archived Log entry 139 added for thread 1 sequence 143 ID 0x9941a747 dest 2:
Archiver process freed from errors. No longer stopped </code><b><code>LOG_ARCHIVE_DEST_2 starts being used.</code></b></pre>
<br />
The default destination is DISABLED and the ALTERNATE has become VALID:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> SELECT DEST_NAME,STATUS,DESTINATION
FROM V$ARCHIVE_DEST
WHERE DESTINATION IS NOT NULL;
DEST_NAME STATUS DESTINATION
----------------------------------------------------------
LOG_ARCHIVE_DEST_1 DISABLED /oratest/archives
LOG_ARCHIVE_DEST_2 VALID /optim2/oratest/TESTDB/arch
SQL> show parameter LOG_ARCHIVE_DEST_STATE
NAME TYPE VALUE
------------------------ ------ -------
log_archive_dest_state_1 string ENABLE
log_archive_dest_state_2 string ALTERNATE</code></pre>
<br />
We backup and delete the archive logs using RMAN to free all space from the destinations.<br />
The destinations' status will not change. All new archive logs will be placed in LOG_ARCHIVE_DEST_2.<br />
So, to go back to previous state, we have to reconfigure the destinations:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH;
System altered.
SQL> SELECT DEST_NAME,STATUS,DESTINATION
FROM V$ARCHIVE_DEST
WHERE DESTINATION IS NOT NULL;
DEST_NAME STATUS DESTINATION
--------------------------------------------------------
LOG_ARCHIVE_DEST_1 VALID /oratest/archives
LOG_ARCHIVE_DEST_2 VALID /optim2/oratest/TESTDB/arch</code></pre>
<br />
In this state, archive logs will be written in BOTH destinations, so:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ALTERNATE SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/oratest/archives NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2' SCOPE=BOTH;
System altered.
SQL> SELECT DEST_NAME,STATUS,DESTINATION
FROM V$ARCHIVE_DEST
WHERE DESTINATION IS NOT NULL;
DEST_NAME STATUS DESTINATION
----------------------------------------------------------
LOG_ARCHIVE_DEST_1 ALTERNATE /oratest/archives
LOG_ARCHIVE_DEST_2 ALTERNATE /optim2/oratest/TESTDB/arch</code></pre>
<br />
<b>AGAIN, V$ARCHIVE_DEST output is not correct</b>. We can't have 2 alternate and no valid destination.<br />
From the second session:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>SELECT DEST_NAME,STATUS,DESTINATION
FROM V$ARCHIVE_DEST
WHERE DESTINATION IS NOT NULL;
DEST_NAME STATUS DESTINATION
-----------------------------------------------------------
LOG_ARCHIVE_DEST_1 VALID /oratest/archives
LOG_ARCHIVE_DEST_2 ALTERNATE /optim2/oratest/TESTDB/arch</code></pre>
<br />
This is the correct configuration. Now, new archive logs will be placed in LOG_ARCHIVE_DEST_1 and not in LOG_ARCHIVE_DEST_2.<br />
<br />
<b>23/07/12:</b> I opened an SR for this incident and filed Bug 14360414.<br />
<b>25/09/12:</b> Oracle closed my SR, since bug's impact is not high, but kept bug open with status <i><span class="bugOutputText">11 - Code Bug (Response/Resolution).</span></i> Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com0tag:blogger.com,1999:blog-6212636374045442782.post-27101729113418723712012-07-16T12:15:00.004+03:002012-11-15T08:17:13.040+02:00Tablespace growth and usage monitoringWe want to create a mechanism to monitor growth of our tablespaces and how much space is used in them. We will use DBA_TABLESPACE_USAGE_METRICS view and store its output in a table called HELPDESK.TABLESPACE_STATS.<br />
<br />
If you use autoextendible datafiles, according to Oracle Note 455715.1:<br />
<blockquote class="tr_bq" style="color: #0b5394;">
<span style="font-size: small;"><span id="pt1:sd_r1:0:dv_rDoc:ot71">Tablespace_size
in DBA_TABLESPACE_USAGE_METRICS takes the maximum file size for
autoextensible tablespace which corresponds to maxblocks in
dba_data_files.</span></span></blockquote>
We create HELPDESK.TABLESPACE_STATS and its indexes:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>CREATE TABLE HELPDESK.TABLESPACE_STATS
(
TABLESPACE_NAME VARCHAR2(30 BYTE),
TABLESPACE_SIZE NUMBER,
USED_SPACE NUMBER,
USED_PERCENT NUMBER,
COLLECTION_DATE DATE
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE INDEX HELPDESK.DATE_IDX ON HELPDESK.TABLESPACE_STATS
(COLLECTION_DATE)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX HELPDESK.NAME_IDX ON HELPDESK.TABLESPACE_STATS
(TABLESPACE_NAME)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
</code></pre>
<br />
Now, schedule a job to run the following INSERT. I choose to run it once per week.<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code>INSERT INTO HELPDESK.TABLESPACE_STATS (TABLESPACE_NAME,
USED_SPACE,
TABLESPACE_SIZE,
USED_PERCENT,
COLLECTION_DATE)
SELECT A.TABLESPACE_NAME,
A.USED_SPACE * B.BLOCK_SIZE,
A.TABLESPACE_SIZE * B.BLOCK_SIZE,
A.USED_PERCENT,
SYSDATE
FROM DBA_TABLESPACE_USAGE_METRICS A, DBA_TABLESPACES B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;</code></pre>
<br />
Let's see a scenario, where we have collected 3 weeks of data:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code> SELECT TRUNC (COLLECTION_DATE) "DATE",
TABLESPACE_NAME,
ROUND (TABLESPACE_SIZE / 1024 / 1024, 2) "SIZE MB",
ROUND (USED_SPACE / 1024 / 1024, 2) "USED SPACE MB",
ROUND (USED_PERCENT, 2) "USED %"
FROM HELPDESK.TABLESPACE_STATS
ORDER BY 1, 2;</code></pre>
<br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4;"><tbody>
<tr><td>DATE</td><td>TABLESPACE_NAME</td><td>SIZE MB</td><td>USED SPACE MB</td><td>USED %</td></tr>
<tr><td>02/07/2012</td><td>IDX</td><td>30</td><td>20</td><td>66.67</td></tr>
<tr><td>02/07/2012</td><td>SYSAUX</td><td>600</td><td>418.56</td><td>69.76</td></tr>
<tr><td>02/07/2012</td><td>SYSTEM</td><td>700</td><td>362.25</td><td>51.75</td></tr>
<tr><td>02/07/2012</td><td>TBL</td><td>40</td><td>22</td><td>55</td></tr>
<tr><td>02/07/2012</td><td>TEMP</td><td>20</td><td>0</td><td>0</td></tr>
<tr><td>02/07/2012</td><td>UNDOTBS1</td><td>245</td><td>3.25</td><td>1.33</td></tr>
<tr><td>02/07/2012</td><td>USERS</td><td>5</td><td>4</td><td>80</td></tr>
<tr><td>09/07/2012</td><td>IDX</td><td>30</td><td>25</td><td>83.33</td></tr>
<tr><td>09/07/2012</td><td>SYSAUX</td><td>600</td><td>418.56</td><td>69.76</td></tr>
<tr><td>09/07/2012</td><td>SYSTEM</td><td>700</td><td>362.25</td><td>51.75</td></tr>
<tr><td>09/07/2012</td><td>TBL</td><td>45</td><td>26</td><td>57.78</td></tr>
<tr><td>09/07/2012</td><td>TEMP</td><td>20</td><td>0</td><td>0</td></tr>
<tr><td>09/07/2012</td><td>UNDOTBS1</td><td>245</td><td>3.25</td><td>1.33</td></tr>
<tr><td>09/07/2012</td><td>USERS</td><td>5</td><td>4.8</td><td>96</td></tr>
<tr><td>16/07/2012</td><td>IDX</td><td>35</td><td>28.8</td><td>82.29</td></tr>
<tr><td>16/07/2012</td><td>SYSAUX</td><td>600</td><td>418.56</td><td>69.76</td></tr>
<tr><td>16/07/2012</td><td>SYSTEM</td><td>700</td><td>362.25</td><td>51.75</td></tr>
<tr><td>16/07/2012</td><td>TBL</td><td>45</td><td>35</td><td>77.78</td></tr>
<tr><td>16/07/2012</td><td>TEMP</td><td>20</td><td>0</td><td>0</td></tr>
<tr><td>16/07/2012</td><td>UNDOTBS1</td><td>245</td><td>3.25</td><td>1.33</td></tr>
<tr><td>16/07/2012</td><td>USERS</td><td>7</td><td>5.5</td><td>78.57</td></tr>
</tbody></table>
<br />
If we want to see the differences between two dates:<br />
<pre style="background-color: #d4d4d4; border: 1px dashed #999999; color: black; font-family: Consolas, Courier, Courier New, Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 11pt; line-height: 14pt; overflow: auto; padding: 5px; white-space: pre-wrap; width: 100%;"><code> SELECT LAST.TABLESPACE_NAME,
ROUND ( (LAST.TABLESPACE_SIZE - FIRST.TABLESPACE_SIZE) / 1024 / 1024,
2)
"SIZE GROWTH MB",
ROUND ( (LAST.USED_SPACE - FIRST.USED_SPACE) / 1024 / 1024, 2)
"USED SPACE GROWTH MB",
ROUND (LAST.USED_PERCENT - FIRST.USED_PERCENT, 2) "USED % GROWTH"
FROM (SELECT TABLESPACE_NAME,
TABLESPACE_SIZE,
USED_SPACE,
USED_PERCENT
FROM HELPDESK.TABLESPACE_STATS
WHERE TRUNC (COLLECTION_DATE) = '16-JUL-12') LAST,
(SELECT TABLESPACE_NAME,
TABLESPACE_SIZE,
USED_SPACE,
USED_PERCENT
FROM HELPDESK.TABLESPACE_STATS
WHERE TRUNC (COLLECTION_DATE) = '02-JUL-2012') FIRST
WHERE LAST.TABLESPACE_NAME = FIRST.TABLESPACE_NAME
ORDER BY 1;</code></pre>
<br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="3" style="background-color: #d4d4d4;"><tbody>
<tr><td>TABLESPACE_NAME</td><td>SIZE GROWTH MB</td><td>USED SPACE GROWTH MB</td><td>USED % GROWTH</td></tr>
<tr><td>IDX</td><td>5</td><td>8.8</td><td>15.62</td></tr>
<tr><td>SYSAUX</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>SYSTEM</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>TBL</td><td>5</td><td>13</td><td>22.78</td></tr>
<tr><td>TEMP</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>UNDOTBS1</td><td>0</td><td>0</td><td>0</td></tr>
<tr><td>USERS</td><td>2</td><td>1.5</td><td>-1.43</td></tr>
</tbody></table>
<br />
So, for instance, in 2 weeks, for tablespace TBL we gave 5 MB extra space, but 13 MB were additionally used, thus increasing its used percent by 22,78%.Lefteris Tsekourashttp://www.blogger.com/profile/13896531464569159841noreply@blogger.com1