ORAERP.COM - The Knowledge Center for Oracle ERP Professionals - Looking Beyond the Possibilities - Oracle Database

ORACLE HOME issue with DBCA

Mon, 06 May 2013 03:26:52 -0500

Folks,
I am trying to create a new database with 11.2.0.3 binaries, which is cloned from R12 PROD, But failed due to the dbca invoking wrong OH. I opened the dbca source code[file], the OH parameter value is wrongly defined. I corrected the parameter and i tried but its taking same path again [Wrong one].

If any one please, let me know what are all the files that dbca using while creating the database.

ORA-00439: feature not enabled: Managed Standby

Sat, 16 Feb 2013 09:50:58 -0600

Hello,
I am configuring data guard. When I setting the parameter log_archive_dest_2 i got following error.
I don't know why it is not allowing to set this parameter.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=TURKDR NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TURKDR'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00439: feature not enabled: Managed Standby

Please someone help me to get rectify this issue.

SQL Performance Analyzer in Oracle Database 11g

Mon, 28 Mar 2011 02:31:06 -0500

SQL Performance Analyzer in Oracle Database 11g

The concept of SQL tuning sets, along with the DBMS_SQLTUNE package to manipulate them, was introduced in Oracle 10g as part of the Automatic SQL Tuning functionality. Oracle 11g makes further use of SQL tuning sets with the SQL Performance Analyzer, which compares the performance of the statements in a tuning set before and after a database change. The database change can be as major or minor as you like, such as:

Database, operating system, or hardware upgrades.
Database, operating system, or hardware configuration changes.
Database initialization parameter changes.
Schema changes, such as adding indexes or materialized views.
Refreshing optimizer statistics.
Creating or changing SQL profiles.

Unlike Database Replay , the SQL Performance Analyzer does not try and replicate the workload on the system. It just plugs through each statement gathering performance statistics.

The SQL Performance Analyzer can be run manually using the DBMS_SQLPA package or using Enterprise Manager. This article gives an overview of both methods.

Setting Up the Test

The SQL performance analyzer requires SQL tuning sets, and SQL tuning sets are pointless unless they contain SQL, so the first task should be to issue some SQL statements. We are only trying to demonstrate the technology, so the example can be really simple. The following code creates a test user called SPA_TEST_USER.

Quote:CONN / AS SYSDBA

CREATE USER spa_test_user IDENTIFIED BY spa_test_user
QUOTA UNLIMITED ON users;

GRANT CONNECT, CREATE TABLE TO spa_test_user;

Next, connect to the test user and create a test table called MY_OBJECTS using a query from the ALL_OBJECTS view.

Quote:CONN spa_test_user/spa_test_user

CREATE TABLE my_objects AS
SELECT * FROM all_objects;

EXEC DBMS_STATS.gather_table_stats(USER, 'MY_OBJECTS', cascade => TRUE);

This schema represents our "before" state. Still logged in as the test user, issue the following statements.

Quote:SELECT COUNT(*) FROM my_objects WHERE object_id <= 100;
SELECT object_name FROM my_objects WHERE object_id = 100;
SELECT COUNT(*) FROM my_objects WHERE object_id <= 1000;
SELECT object_name FROM my_objects WHERE object_id = 1000;
SELECT COUNT(*) FROM my_objects WHERE object_id BETWEEN 100 AND 1000;

Notice, all statements make reference to the currently unindexed OBJECT_ID column. Later we will be indexing this column to create our changed "after" state.

The select statements are now in the shared pool, so we can start creating an SQL tuning set.

Creating SQL Tuning Sets using the DBMS_SQLTUNE Package

The DBMS_SQLTUNE package contains procedures and functions that allow us to create, manipulate and drop SQL tuning sets. The first step is to create an SQL tuning set called spa_test_sqlset using the CREATE_SQLSET procedure.

Quote:CONN / AS SYSDBA

EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'spa_test_sqlset');

Next, the SELECT_CURSOR_CACHE table function is used to retrieve a cursor containing all SQL statements that were parsed by the SPA_TEST_USER schema and contain the word "my_objects". The resulting cursor is loaded into the tuning set using the LOAD_SQLSET procedure.

Quote:DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(a)
FROM TABLE(
DBMS_SQLTUNE.select_cursor_cache(
basic_filter => 'sql_text LIKE ''%my_objects%'' and parsing_schema_name = ''SPA_TEST_USER''',
attribute_list => 'ALL')
) a;

DBMS_SQLTUNE.load_sqlset(sqlset_name => 'spa_test_sqlset',
populate_cursor => l_cursor);
END;
/

The DBA_SQLSET_STATEMENTS view allows us to see which statements have been associated with the tuning set.

Quote:SELECT sql_text
FROM dba_sqlset_statements
WHERE sqlset_name = 'spa_test_sqlset';

SQL_TEXT
--------------------------------------------------------------------------------
SELECT object_name FROM my_objects WHERE object_id = 100
SELECT COUNT(*) FROM my_objects WHERE object_id <= 100
SELECT COUNT(*) FROM my_objects WHERE object_id BETWEEN 100 AND 1000
SELECT COUNT(*) FROM my_objects WHERE object_id <= 1000
SELECT object_name FROM my_objects WHERE object_id = 1000

5 rows selected.

SQL>

Now we have an SQL tuning set, we can start using the SQL performance analyzer.

Running the SQL Performance Analyzer using the DBMS_SQLPA Package

The DBMS_SQLPA package is the PL/SQL API used to manage the SQL performance ananlyzer. The first step is to create an analysis task using the CREATE_ANALYSIS_TASK function, passing in the SQL tuning set name and making a note of the resulting task name.

Quote:CONN / AS SYSDBA

VARIABLE v_task VARCHAR2(64);
EXEC :v_task := DBMS_SQLPA.create_analysis_task(sqlset_name => 'spa_test_sqlset');

PL/SQL procedure successfully completed.

SQL> PRINT :v_task

V_TASK
--------------------------------------------------------------------------------
TASK_122

SQL>

Next, use the EXECUTE_ANALYSIS_TASK procedure to execute the contents of the SQL tuning set against the current state of the database to gather information about the performance before any modifications are made. This analysis run is named before_change.

Quote:BEGIN
DBMS_SQLPA.execute_analysis_task(
task_name => :v_task,
execution_type => 'test execute',
execution_name => 'before_change');
END;
/

Now we have the "before" performance information, we need to make a change so we can test the "after" performance. For this example we will simply add an index to the test table on the OBJECT_ID column. In a new SQL*Plus session create the index using the following statements.

Quote:CONN spa_test_user/spa_test_user

CREATE INDEX my_objects_index_01 ON my_objects(object_id);

EXEC DBMS_STATS.gather_table_stats(USER, 'MY_OBJECTS', cascade => TRUE);

Now, we can return to our original session and test the performance after the database change. Once again use the EXECUTE_ANALYSIS_TASK procedure, naming the analysis task "after_change".

Quote:BEGIN
DBMS_SQLPA.execute_analysis_task(
task_name => :v_task,
execution_type => 'test execute',
execution_name => 'after_change');
END;
/

Once the before and after analysis tasks are complete, we must run a comparison analysis task. The following code explicitly names the analysis tasks to compare using name-value pairs in the EXECUTION_PARAMS parameter. If this is ommited, the latest two analysis runs are compared.

Quote:BEGIN
DBMS_SQLPA.execute_analysis_task(
task_name => :v_task,
execution_type => 'compare performance',
execution_params => dbms_advisor.arglist(
'execution_name1',
'before_change',
'execution_name2',
'after_change')
);
END;
/

With this final analysis run complete, we can check out the comparison report using the REPORT_ANALYSIS_TASK function. The function returns a CLOB containing the report in 'TEXT', 'XML' or 'HTML' format. Its usage is shown below.

Note. Oracle 11gR2 also includes an 'ACTIVE' format that looks more like the Enterprise Manager output.

Quote:SET PAGESIZE 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET TRIMSPOOL ON
SET TRIM ON

SPOOL /tmp/execute_comparison_report.htm

SELECT DBMS_SQLPA.report_analysis_task(:v_task, 'HTML', 'ALL')
FROM dual;

SPOOL OFF

An example of this file for each available type is shown below.

TEXT
HTML
XML
ACTIVE - Active HTML available in 11gR2 requires a download of Javascript libraries from an Oracle website, so must be used on a PC connected to the internet.

Creating SQL Tuning Sets using Enterprise Manager

Click on the "SQL Tuning Sets" link towards the bottom of the "Performance" tab.

On the "SQL Tuning Sets" screen, click the "Create" button.

Enter a name for the SQL tuning set and click the "Next" button.

Select the "Load SQL statements one time only" option, select the "Cursor Cache" as the data source, then click the "Next" button.

Set the appropriate values for the "Parsing Schema Name" and "SQL Text" filter attributes, remove any extra attributes by clicking their remove icons, then click the "Next" button.

Accept the immediate schedule by clicking the "Next" button.

Assuming the review information looks correct, click the "Submit" button.

The "SQL Tuning Sets" screen shows the confirmation of the tuning set creation and the scheduled job to populate it.

Once the population job completes, clicking on the SQL tuning set displays its contents.

Now we have an SQL tuning set, we can start using the SQL performance analyzer.

Running the SQL Performance Analyzer using Enterprise Manager

Click the "SQL Performance Analayzer" link on the "Software and Support" tab.

Click the "Guided Workflow" link on the "SQL Performance Analayzer" screen.

Click the execute icon on the first step to create the SQL Performance Analyzer task.

Enter a name for the SPA task, select the SQL tuning set to associate with it, then click the "Create" button.

When the status of the previous step becomes a green tick, click the execute icon on the second step to capture the SQL tuning set performance information of the "before" state.

Enter a "Replay Trial Name" of "before_change", check the "Trial environment established" checkbox, then click the "Submit" button.

When the status of the previous step becomes a green tick, click the execute icon on the third step to capture the SQL tuning set performance information of the "after" state.

Alter the state of the database by creating an index on the OBJECT_ID column of the test table.

Quote:CONN spa_test_user/spa_test_user@prod

CREATE INDEX my_objects_index_01 ON my_objects(object_id);

EXEC DBMS_STATS.gather_table_stats(USER, 'MY_OBJECTS', cascade => TRUE);

Enter a "Replay Trial Name" of "after_change", check the "Trial environment established" checkbox, then click the "Submit" button.

When the status of the previous step becomes a green tick, click the execute icon on the forth step to run a comparison analysis task.

Accept the default "Trial 1 Name" and "Trial 2 Name" settings by clicking the "Submit" button.

When the status of the previous step becomes a green tick, click the execute icon on the fifth step to view the comparison report.

The resulting page contains the comparison report for the SQL Performance Analyzer task.

Clicking on a specific SQL ID displays the statement specific results, along with the before and after execution plans.

Optimizer Upgrade Simulation

The SQL Performance Analyzer allows you to test the affects of optimizer version changes on SQL tuning sets. Click the "Optimizer Upgrade Simulation" link on the "SQL Performance Analyzer" page.

Enter a task name, select the two optimizer versions to compare, then click the "Submit" button.

The task is listed in the "SQL Performance Analyzer Tasks" section. Refresh the page intermittently until the task status becomes a green tick, then click on the task name.

The resulting screen shows details of the selected task. Click on the "Comparison Report" classes icon allows you to view the comparison report.

Parameter Change

The SQL Performance Analyzer provides a shortcut for setting up tests of initialization parameter changes on SQL tuning sets. Click the "Parameter" link on the "SQL Performance Analyzer" page.

Enter a task name and the parameter you wish to test. Enter the base and changed value, then click the "Submit" button.

The task is listed in the "SQL Performance Analyzer Tasks" section. Refresh the page intermittently until the task status becomes a green tick, then click on the task name.

The resulting screen shows details of the selected task. Click on the "Comparison Report" classes icon allows you to view the comparison report.

Transferring SQL Tuning Sets

In the examples listed above, the tests have been performed on the same system. In reality you are more likely to want to create a tuning set on your production system, then run the SQL Performance Analyzer against it on a test system. Fortunately, the DBMS_SQLTUNE package allows you to transport SQL tuning sets by storing them in a staging table.

First, create the staging table using the CREATE_STGTAB_SQLSET procedure.

Quote:CONN sys/password@prod AS SYSDBA

BEGIN
DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'SQLSET_TAB',
schema_name => 'SPA_TEST_USER',
tablespace_name => 'USERS');
END;
/

Next, use the PACK_STGTAB_SQLSET procedure to export SQL tuning set into the staging table.

Quote:BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'SPA_TEST_SQLSET',
sqlset_owner => 'SYS',
staging_table_name => 'SQLSET_TAB',
staging_schema_owner => 'SPA_TEST_USER');
END;
/

Once the SQL tuning set is packed into the staging table, the table can be transferred to the test system using Datapump, Export/Import or via a database link. Once on the test system, the SQL tuning set can be imported using the UNPACK_STGTAB_SQLSET procedure.

Quote:BEGIN
DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => '%',
sqlset_owner => 'SYS',
replace => TRUE,
staging_table_name => 'SQLSET_TAB',
staging_schema_owner => 'SPA_TEST_USER');
END;
/

The SQL tuning set can now be used with the SQL Performance Analyzer on the test system.

More details you can find from attached Doc.

Oracle SQL Performance Analyzer.pdf (Size: 55 KB / Downloads: 29)

Script for Undo Information

Fri, 28 Jan 2011 08:35:29 -0600

REM:**********************************************************************************************
REM: Script : Undo Informations
REM: Author: Kumar Menon
REM: Date Submitted: 16-July-2009
REM:FileName: Undoinfo.sql
REM:
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
****************************************************************************************************

spool d:\undoinfo.txt
SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(SELECT value AS DBS FROM v$parameter WHERE name = 'db_block_size') ;

SELECT r.name rbs,
NVL(s.username, 'None') oracle_user,
s.osuser client_user,
p.username unix_user,
TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) as sid_serial,
p.spid unix_pid,
t.used_ublk * TO_NUMBER(x.value)/1024 as undo_kb
FROM v$process p,
v$rollname r,
v$session s,
v$transaction t,
v$parameter x
WHERE s.taddr = t.addr
AND s.paddr = p.addr(+)
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size'
ORDER
BY r.name ;

select l.sid, s.segment_name from dba_rollback_segs s, v$transaction t, v$lock l
where t.xidusn=s.segment_id and t.addr=l.addr ;
select to_char(begin_time,'hh24:mis'),to_char(end_time,'hh24:mis')
, maxquerylen,ssolderrcnt,nospaceerrcnt,undoblks,txncount from v$undostat
order by undoblks ;
set lines 160 pages 40
col machine format A20
col username format A15
select xidusn, xidslot, trans.status, start_time, ses.sid, ses.username, ses.machine ,proc.spid, used_ublk
from v$transaction trans, v$session ses , v$process proc
where trans.ses_addr =ses.saddr and ses.paddr=proc.addr
order by start_time ;

select to_char(begin_time,'hh24:mis'),to_char(end_time,'hh24:mis')
, maxquerylen,ssolderrcnt,nospaceerrcnt,undoblks,txncount from v$undostat
order by undoblks ;
Promot "following to show how much undo is being used:"

set pagesize 24
set lin 132
set verify off
col owner format a13
col segment_name format a25 heading 'Segment Name'
col segment_type format a15 heading 'Segment Type'
col tablespace_name format a15 heading 'Tablespace Name'
col extents format 99999999 heading 'Extent'
select
owner, segment_name, segment_type, tablespace_name,
(bytes / 1048576) "Mbytes",
extents
from sys.dba_segments
where tablespace_name = '&UNDO01'
order by owner, segment_name ;

spool off

Oracle 11g Upgrade

Mon, 24 Jan 2011 23:30:07 -0600

I want to upgrade the oracle 10g databases to oracle 11g databases. What is the best method to upgrade to 11g.
For some databases one DBA is doing exp/imp method for other databases he is using conventional upgrade (manual upgrade through scripts, Database Upgrade Assistant). Please let me know when to use exp/imp method ? and when to use conventional upgrade ?

any update

How to measure the data rate between RAC nodes

Sat, 22 Jan 2011 08:15:54 -0600

Hello,

I would like to know how to measure the data rate between the 2 RAC nodes . What i mean here is the cable over which cache fusion data transmit happens.

Oracle Export and Import Utility

Thu, 20 Jan 2011 12:31:15 -0600

1. Introduction

Oracle provides Export and Import utilities for the transfer of data from one database to another. It enables writing of data i.e. Export of one database into Operating system files in binary format. These OS files in binary format can only be traced by IMPORT utility to move the data to the new owner.

Efficiency of Export and Import Utilities
1. Archive data for future reference and restore database object definitions
2. Enable data movement between the databases which may be of different versions
3. Transport tablespace(s) between databases

Export/Import utility prove to be useful in situations of database corruptions and user errors. They can be used for logical database recovery. They can also be used for partial exporting of objects. User can take export and import few tables or specified tablespace.

2. Export

Export utility extract database objects along with their dependent and referenced objects. The export utility creates a binary format dump file at the specified location.

Export Syntax

Code :
exp username/password file=file.dmp log=log.txt statistics=none
In the syntax,
username/password is the source database username and password
file.dmp is the name of the file where the data gets backed up.
log.txt is the log file which contains relevant information about the export status

3. Import

The Import utility reads the object definitions and table data from an Export dump file. It inserts the data objects with or without data into an Oracle database.

Import Syntax

Code :
imp username/password fromuser=User1 touser=User2 file=samepage.dmp log=implog.txt
In the syntax,
Username/Password is the username and password of the target Oracle Database
User1 is the original DB user from where the data was exported
User2 is the new DB user into which the data is being imported
file.dmp is the exported database dump file
log.txt is file to store the import logs

4. Utility Invoking methods

Export and Import can be invoked using below methods.
1. Command-line entries
2. Interactive prompts
3. Parameter files
4. Oracle Enterprise Manager Data Management Wizard

Since command line method is the most conventional and widely used method, we shall concentrate our discussion on it.

5. User Requirements

•User must possess the CREATE SESSION privilege on source or target Oracle database.
•To export or import tables which owned by another user, user must possess the EXP_FULL_DATABASE and IMP_FULL_DATABASE role enabled.

6. Export Parameters and Modes

Below table shows the parameter which can be specified during EXPORT utility.

Below are the modes of operation of EXPORT.

7. Import Parameter and Modes

Below table shows the parameter which can be specified during IMPORT utility.

Below are the modes of operation of IMPORT.

Order of database object Import
Import utility maintains a sequence of object import to avoid redundancy of trigger firing and other considerable scenarios of referential integrity. This sequence is less preferential in case of Full Database Import.

8. Examples

1. Below command line EXPORT command takes export of EMPLOYEE and EXP_ARCHIVE table from CLUB schema. Note that it also includes rows(data) from the table

Code sql:
exp CLUB/CLUB TABLES=EMPLOYEE,EMP_ARCHIVE rows=y file=tabexp.dmp
2. Below EXPORT command takes export only for the tablespace TBS_ORDERS. Note that transportable tablespace can be exported or imported only by SYSDBA.

Code :
exp \'username/password AS SYSDBA\' TRANSPORT_TABLESPACE=y TABLESPACES=tbs_orders file-exptbs.dmp log=exptbs.log
3. The below command line EXPORT command imports the objects owned by CLUB schema into ORCL user of SYSTEM database using the export file EXPFULL.dmp

Code sql:
imp system/manager FROMUSER=CLUB TOUSER=ORCL file=EXPFULL.dmp

Oracle Architecture in less than 10 minutes

Fri, 07 Jan 2011 15:34:12 -0600

Oracle Architecture in less than 10 minutes

Manual Oracle Uninstall

Mon, 03 Jan 2011 23:58:48 -0600

The two methods listed below should only be used as a last resort and will remove all Oracle software allowing a reinstall. If you make any mistakes they can be quite destructive so be careful

Windows
In the past I've had many problems uninstalling all Oracle products from Windows systems. Here's my last resort method:

•Uninstall all Oracle components using the Oracle Universal Installer (OUI).
•Run regedit.exe and delete the HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE key. This contains registry entires for all Oracle products.
•Delete any references to Oracle services left behind in the following part of the registry:
HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Ora*
It should be pretty obvious which ones relate to Oracle.
•Reboot your machine.
•Delete the "C:\Oracle" directory, or whatever directory is your ORACLE_BASE.
•Delete the "C:\Program Files\Oracle" directory.
•Empty the contents of your "c:\temp" directory.
•Empty your recycle bin.
At this point your machine will be as clean of Oracle components as it can be without a complete OS reinstall.

Remember, manually editing your registry can be very destructive and force an OS reinstall so only do it as a last resort.
UNIX
Uninstalling all products from UNIX is a lot more consistent. If you do need to resort to a manual uninstall you should do something like:

•Uninstall all Oracle components using the Oracle Universal Installer (OUI).
•Stop any outstanding processes using the appropriate utilities:

# oemctl stop oms user/password
# agentctl stop
# lsnrctl stopAlternatively you can kill them using the kill -9 pid command as the root user.
•Delete the files and directories below the $ORACLE_HOME:

# cd $ORACLE_HOME
# rm -Rf *•With the exception of the product directory, delete directories below the $ORACLE_BASE.
# cd $ORACLE_BASE
# rm -Rf admin doc jre o*•Delete the /etc/oratab file. If using 9iAS delete the /etc/emtab file also.
# rm /etc/oratab /etc/emtab
Hope this helps

WFMLRSVCApp.ear file missing 11g Installation

Sun, 26 Dec 2010 00:45:23 -0600

If you are installing 11g database and hit error “File not found… WFMLRSVCApp.ear” ensure that you have downloaded two disks (for software) and both disks are unzipped in same folder

Droping a Tablespace

Wed, 20 Oct 2010 00:10:24 -0500

Dear all,

can you please tell me how to drop a tablespace which has materialized views.I tried the following
drop tablespace portal including contents and datafiles;

but i get the following error message

SQL> drop tablespace portal including contents and datafiles;
drop tablespace portal including contents and datafiles
*
ERROR at line 1:
ORA-23515: materialized views and/or their indices exist in the tablespace

there are about 200 indices in the tablespace dropping the all one by one is very cumbersome.Is there any other way i can do this and drop the tablespace.

Converting RAC Instance to Single Instance

Thu, 07 Oct 2010 03:44:41 -0500

Can you please share the details as how to convert RAC instance to Single instance.

Is it just making the parameter "cluster_database" and then drop the addition redo threads and undo tablespaces in the mount stage?

Or

Do we have to re-create the control file?