Oracle in World: 8/3/08

Tuesday, August 5, 2008

Solution of ORA-01873: the leading precision of the interval is too small

Error 45 initializing SQL*Plus Internal error

If you are executing normal SQL script then review the script. I ran one SQL script and I saw after executing the script at the end it generated Error 45 initializing SQL*Plus Internal error. I review the script and I saw at the end of it there are some spool output. So I deleted that I ran again and it solved the problem.

However there is related oracle bug when fired whenever you run a Sql Script From OCFS. This bug retained from SQL*Plus - Version 9.0.1.0 to 10.1.0.2. When SQL script file is on a OCFS filesystem created on shared device then executing that script from SQL*Plus will fire the bug.

You can reproduce the bug by following steps.

1.Create a test.sql script on OCFS filesystem containing sql:
startup nomount

2. Check that test.sql script is in correct directory:
$ ls -l /ocfs/scripts
total 1
-rw-r--r-- 1 oracle dba 17 Mar 25 03:12 test.sql

3. Invoke SQL*Plus:
$ sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Aug 25 18:17:41 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.

4. Connect to database:
SQL> connect / as sysdba
Connected.

5. Execute script:
SQL> @/ocfs/scripts/test.sql
Error 45 initializing SQL*Plus
Internal error

Solution of The Problem
If this happens while you execute your script from OCFS then just copy the SQL script file to a local disk and then execute the script.

How to know database initialization parameter in oracle

1)SHOW PARAMETER option: If you use SQL*Plus then you can use SHOW PARAMETER parameter_name to display the values of parameters currently in use. Note that with toad or tora you can't use it as it is SQL*plus option.

SQL>SHOW PARAMETERS sga_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 160M

2)Query from V$PARAMETER: You can query value column of this view to display the values of parameters currently in effect.

SQL> SELECT VALUE FROM v$PARAMETER WHERE name='sga_target';
VALUE
--------------------------------------------------------------------------------
167772160

3)Query from V$PARAMETER2: You can query value column of this view to display the values of parameters currently in effect. It is easier to distinguish list parameter values in this view because each list parameter value appears as a row.

4)Query from V$SPPARAMETER: You can query value column of this view to display the current contents of the server parameter file. The view returns FALSE values in the ISSPECIFIED column if a server parameter file is not being used by the instance.

5)Query from V$SYSTEM_PARAMETER: In this view it displays what will be the value if a new session inherits parameter values from the instance-wide values.

6)Query from V$SYSTEM_PARAMETER2: It displays the information about initialization parameters that are currently in effect for the instance, with each list parameter value appearing as a row in the view. A new session inherits parameter values from the instance-wide values.

Related Documents
Difference between V$parameter and v$spparameter

Difference between V$parameter and v$spparameter

V$PARAMETER
It displays the information about initialization parameters that are currently in effect for the session.

V$SYSTEM_PARAMETER
In this view it displays what will be the value if a new session inherits parameter values from the instance-wide values.

V$SPPARAMETER
It displays the information about contents of the server parameter file. If a server parameter file was not used to start the instance, then ISSPECIFIED column contains FALSE value.

V$SYSTEM_PARAMETER2
It displays the information about initialization parameters that are currently in effect for the instance, with each list parameter value appearing as a row in the view.

You may mess up in this stage. An example will make you clear through these parameter as well as ALTER SYSTEM SET value settings.

Let's experiment over audit_file_dest parameter.


SQL> show parameter audit_file_dest

NAME                        TYPE           VALUE
------------------          ------        --------------------------------------
audit_file_dest             string /oracle/app/oracle/product/10.2.0/db_1/admin/ARJU/adump

Only setting this in memory.
SQL> alter system set audit_file_dest='/oradata2' DEFERRED scope=memory;
System altered.

SQL> select value from V$SYSTEM_PARAMETER where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oradata2

SQL> select value from V$SYSTEM_PARAMETER2 where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oradata2

SQL> select value from v$parameter where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oracle/app/oracle/product/10.2.0/db_1/admin/ARJU/adump

SQL> select value from v$spparameter where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oracle/app/oracle/product/10.2.0/db_1/admin/ARJU/adump

Change only in spfile.
SQL> alter system set audit_file_dest='/backup1' scope=spfile;
System altered.

SQL> select value from V$SYSTEM_PARAMETER where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oradata2

SQL> select value from V$SYSTEM_PARAMETER2 where name='audit_file_dest';

VALUE
----------------------------------------------------------------------
/oradata2

SQL> select value from v$parameter where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oracle/app/oracle/product/10.2.0/db_1/admin/ARJU/adump

SQL> select value from v$spparameter where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/backup1

Related Documents
Using ALTER SYSTEM to Change Initialization Parameter Values

Monday, August 4, 2008

RMAN-06426: RECOVERY_CATALOG_OWNER role must be granted to user

Error Description
Whenever you try to create recovery catalog in rman with a user other than RECOVERY_CATALOG_OWNER role owner it fails with RMAN-06426: RECOVERY_CATALOG_OWNER role must be granted to user ARJU.

-bash-3.00$ rman target sys/@jupiter:/ARJU.JUPITER.ARJU.NET catalog arju/a

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Aug 4 05:15:32 2008

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

target database Password:
connected to target database: ARJU (DBID=2855033675)
connected to recovery catalog database

RMAN> create catalog;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06426: RECOVERY_CATALOG_OWNER role must be granted to user ARJU

Cause of The Problem
The userid that was supplied in the CATALOG connect string does not have the RECOVERY_CATALOG_OWNER role granted as a DEFAULT role.

Solution of The Problem
Grant the RECOVERY_CATALOG_OWNER role to the user ARJU in the recovery catalog database. Here from the rman connection string we see the recovery catalog database is the local database. So in the local database issue as a sys user,

SQL> grant RECOVERY_CATALOG_OWNER to arju;
Grant succeeded.

Now create catalog by,

RMAN> create catalog;
recovery catalog created

Related Documents
Interact with RMAN Client.
How to create recovery catalog and use it

Difference or Advantage between AWR and STATSPACK report

1)The AWR is the next evolution of the STATSPACK utility.

2)The AWR repository holds all of the statistics available in STATSPACK as well as some additional statistics which are not.

3)STATSPACK does not store the Active Session History (ASH) statistics which are available in the AWR dba_hist_active_sess_history view.

4)An important difference between STATSPACK and the AWR is that STATSPACK does not store history for new metric statistics introduced in Oracle10g. The key AWR views, dba_hist_sysmetric_history and dba_hist_sysmetric_summary.

5)The AWR also contains views such as dba_hist_service_stat , dba_hist_service_wait_class and dba_hist_service_name , which store history for performance cumulative statistics tracked for specific services.

6)The latest version of STATSPACK included with Oracle10g contains a set of specific tables, which track history of statistics that reflect the performance of the Oracle Streams feature. These tables are stats$streams_capture , stats$streams_apply_sum , stats$buffered_subscribers , stats$rule_set , stats$propagation_sender , stats$propagation_receiver and stats$buffered_queues . The AWR does not contain the specific tables that reflect Oracle Streams activity; therefore, if a DBA relies heavily on the Oracle Streams feature, it would be useful to monitor its performance using STATSPACK utility.

7)Statspack snapshots must be run by an external scheduler (dbms_jobs, CRON, etc.). AWR snapshots are scheduled every 60 minutes by default. Administrators can manually adjust the snapshot interval if so desired.

8)ADDM captures a much greater depth and breadth of statistics than Statspack does. During snapshot processing, MMON transfers an in-memory version of the statistics to the permanent statistics tables.

9)Statspack snapshot purges must be scheduled manually. When the Statspack tablespace runs out of space, Statspack quits working. AWR snapshots are purged automatically by MMON every night. MMON, by default, tries to keep one week's worth of AWR snapshots available. If AWR detects that the SYSAUX tablespace is in danger of running out of space, it will free space in SYSAUX by automatically deleting the oldest set of snapshots. If this occurs, AWR will initiate a server-generated alert to notify administrators of the out-of-space error condition. Administrators can manually adjust the amount of information retained by invoking the MODIFY_SNAPSHOT_SETTINGS PL/SQL stored procedure and specifying the RETENTION parameter input variable.

10)AWR snapshots provide a persistent view of database statistics. They are stored in the system-defined schema, which resides in a new tablespace called SYSAUX. A snapshot is a collection of performance statistics that are captured at a specific point in time. The snapshot data points are used to compute the rate of change for the statistic being measured. A unique SNAP_ID snapshot identifier identifies each snapshot.

Related Documents
http://arjudba.blogspot.com/2008/08/how-to-invoke-collect-and-run-awr-and.html

Improving Index creation speed in Oracle

New features of oracle perfomance in 10.2g
How to find Top Sql through Queries
Ways of Identify High Load ot top SQL
Oracle INSERT Performance
Optimize Data Access Path in Oracle
When you would make Index and when not.

Sunday, August 3, 2008

How to invoke, collect and run AWR and statspack report

By default, AWR automatically generates snapshots of the performance data once every hour and retains the statistics in the workload repository for 7 days.

The data in the snapshot interval is then analyzed by the Automatic Database Diagnostic Monitor (ADDM).

However if you want to create, drop or modify snapshot manually then you can use DBMS_WORKLOAD_REPOSITORY procedures. To invoke these procedures, a user must be granted the DBA role.

To see the result by using AWR just create snapshot by DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT and then execute your transaction or SQL statements and again create another snapshot by DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT and then follow Generating Automatic Workload Repository Reports section to see report between these two snapshots.

Creating Snapshot
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

To view a list of the snapshot Ids along with database Ids, check the DBA_HIST_SNAPSHOT view.

Dropping Range of Snapshots.
Check the DBA_HIST_SNAPSHOT view column SNAP_ID to view available snapshots. To delete contain SNAP_ID from from 32 to 42,
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 32,
high_snap_id => 42, dbid => 8999917127);
END;
/

Modifying Snapshot Settings
If you want to modify the retention period as 43200 minutes (30 days), the interval between each snapshot is specified as 30 minutes, and the number of Top SQL to flush for each SQL criteria as 100 then issue,
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,
interval => 30, topnsql => 100, dbid => 3310949047);
END;
/
Here dbid is optional.

Extracting AWR Data
The awrextr.sql script extracts the AWR data for a range of snapshots from the database into a Data Pump export file. Once created, this dump file can be transported to another system where the extracted data can be loaded. To run the awrextr.sql script, you need to be connected to the database as the SYS user.

To extract AWR data at the SQL prompt, enter:

@$ORACLE_HOME/rdbms/admin/awrextr.sql

Loading AWR Data

Once the export dump file is transported to the target system, you can load the extracted AWR data using the awrload.sql script. The awrload.sql script will first create a staging schema where the snapshot data is transferred from the Data Pump file into the database. The data is then transferred from the staging schema into the appropriate AWR tables. To run the awrload.sql script, you need to be connected to the database as the SYS user.

To load AWR data at the SQL prompt, enter:

@$ORACLE_HOME/rdbms/admin/awrload.sql

Generating Automatic Workload Repository Reports

If you have EM available then from EM you can easily generate AWR report. To generate on home page click Advisor Central and follow the links. However you can generate AWR reports by running SQL scripts:

The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.
To generate an HTML or text report for a range of snapshot Ids, run the awrrpt.sql script at the SQL prompt:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

First, you need to specify whether you want an HTML or a text report.

Enter value for report_type: text

Specify the number of days for which you want to list snapshot Ids.

Enter value for num_days: 2

After the list displays, you are prompted for the beginning and ending snapshot Id for the workload repository report.

Enter value for begin_snap: 90
Enter value for end_snap: 92

Next, accept the default report name or enter a report name. The default name is accepted in the following example:

Enter value for report_name:
Using the report name awrrpt_1_90_92

The workload repository report is generated.

The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.

The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance.

The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.

The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance. Run this report to inspect or debug the performance of a SQL statement on a specific database and instance.

The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.

The awrddrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.

Related Documents
Difference or Advantage between AWR and STATSPACK report

ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping tablespace

Problem Description
Drop undo tablespace fails with error Ora-01548 .
SQL> drop Tablespace UNDOTBS;
drop Tablespace UNDOTBS
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping
tablespace

Cause of The Problem
An attempt was made to drop a tablespace that contains active rollback segments.

Solution of The Problem
In order to get rid of this error you have to follow following steps.
1)Create pfile if you started with database with spfile.
SQL>Create PFILE from SPFILE;

2)Edit pfile and set undo management to manual.
undo_management = manual

3)Put the entries of the undo segments in the pfile by using the following statement in the pfile:

_offline_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,.....)

4)Mount the database using new pfile.
Startup mount pfile='fullpath to pfile'

5)Drop the datafiles,
sql>Alter Database datafile '&filename' offline drop;

6)Open the database.
sql>Alter Database Open;

7)Drop the undo segments,

sql>Drop Rollback Segment "_SYSSMU1$";
......
8)Drop Old undo tablespace.
sql>Drop Tablespace old_tablespace_name Including Contents;

9)Add the new undo tablespace.

10) Shutdown Immediate;

11) Change the pfile parameters back to Undo_management = AUTO and modify the parameter Undo_tablespace=new_undo_tablespace_name and remove the _offline_rollback_segments parameter.

12) Startup the Database.

Related Documents
How undo Space Allocation Works

The possible causes for Excessive Undo generation

Oracle: Undo vs Rollback Segment

Oracle in World

Most Visited Recently

Categories

Popular Posts

Which OS you like best for your oracle

Tuesday, August 5, 2008

Solution of ORA-01873: the leading precision of the interval is too small

Error 45 initializing SQL*Plus Internal error

How to know database initialization parameter in oracle

Difference between V$parameter and v$spparameter

Monday, August 4, 2008

RMAN-06426: RECOVERY_CATALOG_OWNER role must be granted to user

Difference or Advantage between AWR and STATSPACK report

New features of oracle perfomance in 10.2g

How to find Top Sql through Queries

Ways of Identify High Load ot top SQL

Oracle INSERT Performance

Optimize Data Access Path in Oracle

When you would make Index and when not.

Sunday, August 3, 2008

How to invoke, collect and run AWR and statspack report

ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping tablespace

Tag Cloud

Blog Archive

Which Oracle database version you use?