Wednesday, May 30, 2018

ORA-03206: maximum file size of (6553600) blocks in AUTOEXTEND clause is out of range

I received below error while extending the datafile. You might see below error when creating tables space too.

SQL> alter database datafile '/oradata1/ORCL/orcl101.dbf' autoextend ON maxsize 50g;
alter database datafile '/oradata1/ORCL/orcl101.dbf' autoextend ON maxsize 50g;
*
ERROR at line 1:
ORA-03206: maximum file size of (6553600) blocks in AUTOEXTEND clause is out of range


The reason is maximum file size for an autoenxtendable file has exceeded the maximum number of blocks allowed. Note that Oracle allows only up to 32GB datafile with 8k blocks. If you want to create a tablespace or datafile more than 32GB then you must use BIGFILE keyword.

To calicuate max datafile size:
max datafile size = db_block_size * maximum number of blocks

Solution: create a file less than 32 GB size or if you need to create datafile more than 32GB then use BIGFILE keyword.

SQL> alter database datafile '/oradata1/ORCL/orcl101.dbf' autoextend ON maxsize 32767M ;
Tablespace altered.

Hope this helps,

Regards
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

ERROR 1827 (HY000): The password hash doesn't have the expected format

When creating a user with Grant option we have received below error message

ERROR 1827 (HY000): The password hash doesn't have the expected format
That means you have to use hash password. Here is the simple solution that you can use

Enter the password that you want to set and you will get HASH password.
mysql> select password(‘enter password you want');
+-------------------------------------------+
| password(‘enter password you want') |
+-------------------------------------------+
| *B535BN128KK03E74BE2AC0EE23D07ABX6AD8165E |
+-------------------------------------------+
1 row in set (0.00 sec)

Use the has password while creating the user

mysql> GRANT ALL ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*B535BN128KK03E74BE2AC0EE23D07ABX6AD8165E ' WITH GRANT OPTION;

Query OK, 0 rows affected (0.01 sec)

Thanks
Satishbabu Gunukula, Oracle ACE

Friday, May 25, 2018

ORA-01555: snapshot too old: rollback segment number

There are many reasons for this error. In this post I am providing all the possible scenarios and related Oracle Notes/Links.

Concepts/Definitions

The ORA-1555 errors can happen when a query is unable to access enough undo to build
a copy of the data at the time the query started. Committed "versions" of blocks are
maintained along with newer uncommitted "versions" of those blocks so that queries can
access data as it existed in the database at the time of the query. These are referred to as
"consistent read" blocks and are maintained using Oracle undo management.

See Document 40689.1 - ORA-1555 "Snapshot too old" - Detailed Explanation for more about
these errors.


Diagnosing
Due to space limitations, it is not always feasible to keep undo blocks on hand for the life of the instance. Oracle Automatic Undo Management (AUM) helps to manage the time frame that undo blocks are stored. The time frame is the "retention" time for those blocks.

There are several ways to investigate the ORA-1555 error. In most cases, the error is a legitimate problem with getting to an undo block that has been overwritten due to the undo "retention" period having passed.

AUM will automatically tune up and down the "retention period, but often space limitations or configuration of the undo tablespace will throttle back continuous increases to the "retention" period.
The error message is reported in the user session and often is not captured in the alert log. The user could see a message like

Using rollback segment functionality:

ORA-1555: snapshot too old (rollback segment too small)

or

Using AUM:

ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9$" too small

If the error is captured in the alert.log, you would see something like

Tue May 26 16:16:57 2009

ORA-01555 caused by SQL statement below (SQL ID: 54yn3n36w24ft, Query Duration=922 sec, SCN: 0x0007.8a55f4e3)

Initial Investigation

Rollback Segments:

With Oracle 10g and later versions of Oracle, you can still use a Rollback Segments configuration. ORA-1555 errors in that environment still follow older guidelines as described in


Document 10579.1 - How many Rollback Segments to Have
Document 107085.1 - Tuning Rollback Segments
Document 69464.1 - Rollback Segment Configuration & Tips
Automatic Undo Management:

The database will be self tuning for undo when using Automatic Undo Management. This does not eliminate ORA-1555 completely, but does minimize ORA-1555 as long as there is adequate space in the undo tablespace and workloads tend to follow repeatable patterns. In some cases with periodic changes to workload (large data updates particularly with LOB data) the self tuning of undo can become aggressive and lead to undo issues.

Document 461480.1 - FAQ Automatic Undo Management (AUM) / System Managed Undo (SMU)
Document 135053.1 - How to Create a Database with Automatic Undo Management
Document 268870.1 - How to Shrink the datafile of Undo Tablespace
Document 231776.1 - How to switch a Database from Automatic Undo Management (AUM) back to using Rollback Segments
Document 396863.1 - How to Keep All UNDO Segments from Being Offlined in Oracle 10g - Fast Ramp-Up

LOB Issues:

Out-of-row LOB undo is maintained in the LOB segment. So the UNDO tablespace and undo retention is not associated with most LOB ORA-1555 issues. Instead the LOB column is created using either PCT_VERSION or RETENTION to manage how much space within blocks or time transpires before the LOB undo is overwritten. In environments with high updates, deletes on rows including LOBs, the chances of ORA-1555 on LOB undo is very high.

PCT_VERSION and RETENTION are not auto-tuned. To "tune" those configuration settings, you must change the values for PCT_VERSION or RETENTION. Changes to UNDO_RETENTION does not change LOB retention time frames.


Document 162345.1 - LOBS - Storage, Read-consistency and Rollback
Document 386341.1 - How to determine the actual size of the LOB segments and how to free the deleted/unused space above/below the HWM
Document 563470.1 'Lob retention not changing when undo_retention is changed
Document 422826.1 How to identify LOB Segment Use PCTVERSION or RETENTION from Data Dictionary

Error Tracing

Undo error tracing can be done for normal undo operations using the following events:

NOTE: Normal undo operations will be indicated in the error message in that the error message includes a segment name like

'¦. name "_SYSSMU1$" too small

If the error doesn't show a segment name

'¦ name "" too small

the problem is often related to LOB undo

If using pfile:
event="10442 trace name context forever, level 10"

If using spfile:
Alter system set events '10442 trace name context forever, level 10';

Reproduce the ORA-1555 error and upload the trace file to Oracle Support.

LOB undo error tracing is more difficult. Set additional tracing events as follows:

Start Session 1
Alter session set events '10046 trace name context forever, level 12';
Reproduce the error
Exit Session 1

Start Session 2
Alter session set events '10051 trace name context forever, level 1';
Reproduce the error
Exit Session 2

Start Session
Alter session set events '1555 trace name errorstack forever, level 3';
Reproduce the error
Exit Session 3

Additional resources to review:

Document 846079.1 - LOBs and ORA-1555 troubleshooting
Document 253131.1 - Concurrent Writes May Corrupt LOB Segment When Using Auto Segment Space Management
Document 467872.1 - TROUBLESHOOTING GUIDE (TSG) - ORA-1555
V$UNDOSTAT Analysis

The V$UNDOSTAT view holds undo statistics for 10 minute intervals. This view
represents statistics across instances, thus each begin time, end time, and
statistics value will be a unique interval per instance.

This does not track undo related to LOB
Document 262066.1 - How To Size UNDO Tablespace For Automatic Undo Management

Document 1112363.1 - When Does Undo Used Space Become Available?
Document 240746.1 - 10g NEW FEATURE on AUTOMATIC UNDO RETENTION

Diagnostics Scripts
Refer to Document 746173.1 : Common Diagnostic Scripts for AUM problems
and Document 877613.1 : AUM Common Analysis/Diagnostic Scripts
Common Causes/Solutions
Document 1555.1 - Known defects for ora-1555 error
Using Rollback Segments functionality:

* Problem happening on SYSTEM tablespace that still uses old Rollback Segment functionality even when configured for Automatic Undo Management (AUM).

* There are not enough rollback segments to manage the undo needed for long running queries.

* Rollback Segments are too small and undo is overwritten before long running queries complete.
Reference:

Document 69464.1 - Rollback Segment Configuration & Tips
Document 10630.1 - ORA-1555: 'Snapshot too old' - Overview
Document 862469.1 - ORA-604 & ORA-1555 Rollback Segment 0 with Name "System" Too Small

Using Automatic Undo Management (AUM):

* TUNED_UNDORETENTION in V$UNDOSTAT around the time of the error is lower than the QUERY DURATION indicated in the error message. This is a legitimate ORA-1555 and if queries are going to run for very long time frames, UNDO_RETENTION may need to be larger. Auto-tuned retention may not be able to keep up with the undo workload and staying within space limitations on the UNDO tablespace.

* LOB updates and/or deletes are frequent and a higher PCT_VERSION is required to provide enough space in the LOB Segment to accommodate the LOB undo. RETENTION on LOBs that are updated or deleted frequently can run into problems holding UNDO long enough for queries.

* QUERY DURATION shown in the error message is 30+ years and therefore, no amount of undo will satisfy the consistent read blocks.

Document 750195.1 - ORA-1555 Shows Unrealistic Query Duration (billions of seconds)

* QUERY DURATION shown in the error message is 0. NOTE: This has been filed as a bug on many release levels and has been very difficult to narrow down to a specific problem.

Document 761128.1 - ORA-1555 Error when Query Duration as 0 Seconds

* QUERY DURATION is lower than TUNED_UNDRETENTION. Undo header information can sometimes get overwritten or you could be seeing a bug.

* TUNED_UNDORETENTION stays very high and UNDO tablepsace continues to grow continuously or getting space errors.

Document 1112431.1 - Undo Remains Unexpired When Using Non-autoextensible Datafiles for Undo Tablespace.

* How to find the complete SQL statement caused ORA-1555 :
If the Database was not restarted after the error ORA-1555 , so the Statement can be obtained from :

select SQL_TEXT from SQL_TEXT where SQL_ID='<sql id from the error message>';

If the Database was restarted after the error ORA-1555 and an AWR snapshot was gathered before the restart , so the Statement can be obtained from :

select SQL_TEXT from DBA_HIST_SQLTEXT where SQL_ID='<sql id from the error message>';

Thanks
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

Monday, April 23, 2018

Webinar: How to Convert Single Instance to RAC?

This Webinar helps you to understand the benefits of Oracle RAC, available methods to convert single instance to RAC.


Date and time: May 4th 2018 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.
  • Oracle RAC and its Benefits 
  • Overview of different RAC conversion methods 
  • Overview of different Storage options 
  • Convert Single instance to Oracle RAC Using RMAN 
    • Overview 
    • Prerequisites 
    • Demonstration 
  • Check the logs 
  • Post Conversion Steps 
  • References 
  • Contact Info 
  • Q&A 
To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com.

Note that registrations are limited and first come and first serve basis.
You will receive an email confirmation with meeting session link.

For presentation link Click here

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Tuesday, April 10, 2018

Oracle Flashback Technology and features

Oracle Flashback Technology is a group of Database features that that will help you to view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.

With flashback features, you can do the following:
· Queries that return past data
· Recover rows or tables to a previous point in time
· Track and archive transactional data changes
· Roll back a transaction and its dependent transactions while the db online

Oracle flashback uses automatic undo management (AUM) for all flashback transactions.

Here are few Oracle flashback features.
· Flashback Database
· Flashback Table
· Flashback drop
· Flashback query
· Flashback Version/Transaction Query
· Flashback Data Archive (From Oracle 11g)
· Flashback Recovery Area:-

Flashback Database:- The FLASHBACK DATABASE is a fast alternative to performing an incomplete recovery. The database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation. You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.

To enable logging for Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement.

DB_FLASHBACK_RETENTION_TARGET- length of the desired flashback window in minutes (1440 min)

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

For ex-
FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn;

Limitations:
· It cannot be used to repair media failures, or to recover from accidental deletion of data files.
· You cannot use Flashback Database to undo a shrink data file operation.
· If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded
· Flashback database cannot be used against block corruptions.

Flashback Table The FLASHBACK TABLE used to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system and cannot restore a table to an earlier state across any DDL operation.

SQL> FLASHBACK TABLE test TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);

Flashback Drop:

The Oracle 10g provides the ability to reinstating an accidentally dropped table from recyclebin.
SQL> FLASHBACK TABLE test TO BEFORE DROP;

Flashback Query The feature allows the DBA to see the value of a column as of a specific time, as long as the before-image copy of the block is available in the undo segment.

For ex:-
SQL> SELECT comments FROM employee AS OF TIMESTAMP TO_TIMESTAMP ('2004-03-29 13:34:12', 'YYYY-MM-DD HH24:MI:SS');

SQL> SELECT comments FROM employee AS OF SCN 722452;

Flashback Version/Transaction Query Flashback Query only provides a fixed snapshot of the data as of a time. Use Flashback Version Query feature to see the changed data between two time points.

Ex: - The following query shows the changes made to the table:

SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation, rate from rates versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME

Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries using FLASHBACK_TRANSACTION_QUERY view. The UNDO_SQL column in the table shows the actual SQL Statement.

For Ex:-
SQL> SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql FROM flashback_transaction_query

Oracle 11g has more enhancements in Flashback Transaction and LogMiner. The LogMiner Viewer has been incorporated into Enterprise Manager and integrated with the new Flashback Transaction feature, making it simple to recover transactions. Flashback Transaction allows the changes made by a transaction to be undone.

Flashback Data Archive (From Oracle 11g) : Flashback data archive allows long-term retention (for ex years) of changed data to a table or set of tables for a user-defined period of time. Flashback Data Archive (which can logically span one or more table spaces) specifies a space quota and retention period for the archive, and then creates the required tables in the archive.

User needs the FLASHBACK ARCHIVE ADMINISTER, FLASHBACK ARCHIVE privileges

SQL> CREATE FLASHBACK ARCHIVE flash_back_archive

TABLESPACE flash_back_archive QUOTA 10G RETENTION 5 YEARS;

Flashback Recovery Area:-
Flash recovery area is a disk location in which the database can store and manage files related to Backup and Recovery. To setup a flash recovery area, you must choose a directory location or Automatic Storage Management disk group to hold the files.

Flash recovery area simplifies the administration of your database by automatically naming recovery-related files, retaining the files as long as they are needed for restore and recovery activities, and deleting the files when they are no longer needed to restore your database and space is needed for some other backup and recovery-related purpose.

To Setup Flash Recovery Area (FRA), you just need to specify below two parameters.
1. DB_RECOVERY_FILE_DEST_SIZE (Specifies max space to use for FRA)

2. DB_RECOVERY_FILE_DEST (Location of FRA)


Performance Guidelines for Oracle Flashback Technology
· For Oracle Flashback Version Query, use index structures.

· Not to scan entire tables and use indexes to query small set of past data. If you need to scan a full table then use parallel hint to the query

· Keep the statistics current as cost-based optimized relies on statistics and use the DBMS_STATS package to generate statistics for tables involved in a Oracle Flashback Query.

· In a Oracle Flashback Transaction Query, the xid column is of the type RAW(8). Use the HEXTORAW conversion function: HEXTORAW(xid)to take advantage of inbuilt function

· The I/O performance cost is mainly paging in the data and undo blocks I the buffer cache. The CPU performance cost is to apply undo.

· A Oracle Flashback Query against a materialized view does not take advantage of query rewrite optimization.

Regards
Satishbabu Gunukula, Oracle ACE

Thursday, March 15, 2018

Oracle Application Express is INVALID and Remove Oracle Apex

We are trying to upgrade a database and noticed that “Oracle Application Express” component is “INVALID” .

SQL> select COMP_ID,VERSION,STATUS from dba_registry;
COMP_ID VERSION STATUS
----------------------------------- --------------- ------------------------------
APEX 3.2.1.00.12 INVALID
CATALOG 11.2.0.4.0 VALID
CATPROC 11.2.0.4.0 VALID

SQL> Select COMP_NAME,VERSION,STATUS from dba_registry where COMP_ID=’APEX’;
COMP_NAME STATUS VERSION
----------------------------------- --------------- ------------------------------
Oracle Application Express INVALID 3.2.1.00.10

To avoid issues during the upgrade we decided to remove the Oracle Apex as it is not in use. We have followed below steps to remove the Oracle Application Express.

$ sqlplus / as sysdba
SQL> @$ORACLE_HOME/apex/apxremov.sql


After removing Application Express started the upgrade but received warning that “Oracle Server” component is INVALID.

SQL> select COMP_ID,VERSION,STATUS from dba_registry;
COMP_ID VERSION STATUS
----------------------------------- --------------- ------------------------------
CATALOG 11.2.0.4.0 INVALID
CATPROC 11.2.0.4.0 INVALID

We noticed that object SYS.HTMLDB_SYSTEM is shown as invalid after removing the Oracle APEX. After investigation I have noticed that apexremov.sql script removes APEX, but leaves the HTMLDB_SYSTEM package and synonym.

I ran below command to drop the invalid packages
SQL> drop package htmldb_system;
SQL> drop public synonym htmldb_system;

Now I don’t see any more INVALID objects but I still see that CATALOG,CATPROC are still INVALID.

SQL> select object_name, object_type from dba_objects
2 where owner = 'SYS' and status = 'INVALID';
no rows selected

SQL> select COMP_ID,VERSION,STATUS from dba_registry;
COMP_ID VERSION STATUS
----------------------------------- --------------- ------------------------------
CATALOG 11.2.0.4.0 INVALID
CATPROC 11.2.0.4.0 INVALID

I ran below script to validate CATPROC and CATALOG. Note that run the same CATALOG you need to change the two entries in the script. (i.e. replace CATPROC with CATALOG before running).

sqlplus / as sysdba
set serveroutput on;
declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$
WHERE cid = 'CATPROC';
SELECT obj#,name into object_id,object_name
FROM obj$
WHERE status > 1 AND
(ctime BETWEEN start_time AND end_time OR
mtime BETWEEN start_time AND end_time OR
stime BETWEEN start_time AND end_time) AND
ROWNUM <=1;
dbms_output.put_line('Please compile Invalid object '||object_name||'
Object_id '||object_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('CATPROC can be validated now' );
end;
/

Ran utlrp.sql script and now the CATALOG and CATPROC showing as VALID.

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> select COMP_ID,VERSION,STATUS from dba_registry;
COMP_ID VERSION STATUS
----------------------------------- --------------- ------------------------------
CATALOG 11.2.0.4.0 VALID
CATPROC 11.2.0.4.0 VALID

We are able to upgrade the database successfully.

Thanks,
Satishbabu Gunukula, Oracle ACE

Webinar: Oracle Database Upgrade to 12c and Available Methods

Oracle Database 12c has many exciting new features and in order to take advantage of these features you need to upgrade the databases from older versions to Oracle 12c.

In this webinar, I will explain best practices and availble methods for you to upgrade/migrate your databases to 12c.

Date and time: Mar 30th 2018 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)


To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com.

Note that registrations are limited, first come and first serve basis only. You will receive an email confirmation with meeting session link.

For Presentation link "Click here". Don’t miss the chance…!

Thanks,
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

Thursday, March 8, 2018

ORA-31613: Master process DM00 failed during startup

While taking export backup using data pump user received following error.

ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1587
ORA-39062: error creating master process DM00
ORA-31613: Master process DM00 failed during startup.

Normally user receives this error when maximum numbers of processes are exceeded. You will see below error in trace files.

ORA-00020: maximum number of processes (150) exceeded

Solution: To fix the issue increase PROCESSES initialization parameter and restart the database.

If you don’t see the error ORA-00020 trace file then look for other error messages which will help to fix the issue. 

I had faced similar issue and  but don't see ORA-00020 error. After looking the other error messages I see that child processes are terminated and in my case restarting the database resolved the issue.

Regards,
Satishbabu Gunukula, Oracle ACE

Thursday, March 1, 2018

One Database Solution for your Enterprise Business – Oracle 12c

Oracle 12c offers new architecture and features, you can manage many databases as ONE by efficient use of resources and reduce IT costs for your Enterprise Business. It also offers one solution for your traditional and cloud environments.

Join the Webinar to learn how this one database solution works for your enterprise business.

Date and time: Mar 16th 2018 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com.

Note that registrations are limited and first come and first serve basis.
You will receive an email confirmation with meeting session link.

For Presentation link "Click here"

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Wednesday, February 28, 2018

Oracle Database Release Lifetime Support

Oracle Support info:

Oracle Premier Support provides a standard five-year support policy for Oracle Technology products. You can extend support for an additional three years with Extended Support for specific releases or receive indefinite technical support with Sustaining Support.


Oracle Release Extended support info.






Please review below link for more details about Oracle Lifetime Support Policy.
http://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf

Regards
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com


Thursday, February 22, 2018

CATPROC and CATALOG are INVALID in the Registry

We are working on pre-upgrade steps and noticed that “Oracle Database Packages and Types” and “Oracle Database Catalog Views “ components are  showing as INVALID.

SQL> select comp_id, comp_name,version, statusfrom dba_registry;
COMP_ID    COMP_NAME                                VERSION                        STATUS
---------- ---------------------------------------- ------------------------------ --------------------------------------------
ORDIM      Oracle Multimedia                                        11.2.0.4.0                     VALID
XDB        Oracle XML Database                                       11.2.0.4.0                     VALID
EXF        Oracle Expression Filter                                    11.2.0.4.0                     VALID
RUL        Oracle Rules Manager                                       11.2.0.4.0                     VALID
OWM        Oracle Workspace Manager                         11.2.0.4.0                     VALID
CATALOG    Oracle Database Catalog Views                 11.2.0.4.0                     INVALID
CATPROC    Oracle Database Packages and Types       11.2.0.4.0                     INVALID
JAVAVM     JServer JAVA Virtual Machine                     11.2.0.4.0                     VALID
XML        Oracle XDK                                                          11.2.0.4.0                     VALID
CATJAVA    Oracle Database Java Packages                   11.2.0.4.0                     VALID

I see that CATPROC, CATALOG  is INVALID in the registry and followed below steps to validate.

Step1: Validate ‘packages and types’ by running catalog and catproc scripts

$sqlplus "/as sysdba"
SQL> spool cata_logfile.txt
SQL> startup upgrade
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql
SQL> spool off

Note that you need to use Use 'startup migrate' instead of ‘startup upgrade’ if database version is lower than 10g.

This should resolve the issue. If CATPROC, CATALOG is still invalid in the registry then follow next step
Step2:- Run below script as SYSDBA until it returns 'CATPROC can be validated now'. This script recompiles all dependent objects. 
 Note that you need to replace CATPROC with CATALOG in below script and execute to recompile the objects related to CATALOG.
REM ***************
REM CHECKVALID.SQL
REM ***************
set serveroutput on;
declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE
cid = 'CATPROC';
SELECT obj#,name into object_id,object_name
FROM obj$
WHERE status > 1 AND
(ctime BETWEEN start_time AND end_time OR
mtime BETWEEN start_time AND end_time OR
stime BETWEEN start_time AND end_time) AND
ROWNUM <=1;
dbms_output.put_line('Please compile Invalid object '||object_name||'
Object_id '||object_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('CATPROC can be validated now' );
end;
/
Validate CATPROC. CATALOG by executing following command
SQL> execute DBMS_REGISTRY_SYS.VALIDATE_CATPROC;
SQL> execute DBMS_REGISTRY_SYS.VALIDATE_CATALOG;

Now I can see that all Database components are objects are VALID.

SQL> select comp_id, comp_name,version, statusfrom dba_registry;
COMP_ID    COMP_NAME                                VERSION                        STATUS
---------- ---------------------------------------- ------------------------------ --------------------------------------------
ORDIM      Oracle Multimedia                                        11.2.0.4.0                     VALID
XDB        Oracle XML Database                                       11.2.0.4.0                     VALID
EXF        Oracle Expression Filter                                    11.2.0.4.0                     VALID
RUL        Oracle Rules Manager                                       11.2.0.4.0                     VALID
OWM        Oracle Workspace Manager                         11.2.0.4.0                     VALID
CATALOG    Oracle Database Catalog Views                 11.2.0.4.0                     VALID
CATPROC    Oracle Database Packages and Types       11.2.0.4.0                     VALID
JAVAVM     JServer JAVA Virtual Machine                     11.2.0.4.0                     VALID
XML        Oracle XDK                                                          11.2.0.4.0                     VALID
CATJAVA    Oracle Database Java Packages                   11.2.0.4.0                     VALID

Thanks,
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

Wednesday, February 21, 2018

Why Oracle RAC is the best High Availability (HA) Solution?

Join the Webinar to learn about the best practices, cost effective solutions to meet your business needs.

Date and time: Mar 5th 2018 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com.

Note that registrations are limited and first come and first serve basis.
You will receive an email confirmation with meeting session link.

For Presentation link "Click here"

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Wednesday, January 10, 2018

MySQL: Too many connection errors.

When user trying to connect MySQL Database, he got below error

ERROR 1040 (hy000): Too many connections
By looking the error we can see that the max_connections got exhausted. You can check the connection info by running below commands.

-- To find max_connections value run below command
SHOW VARIABLES LIKE '%max_connections%';

-- To see the all processes and connections run below command
SHOW FULL PROCESSLIST;

Note that when you get this error you will be able to login using root. Becoz My SQL by default will consider maximum allowed connections as MAX_CONNECTIONS +1 for super user.

But if you have used root to connect to any other app you will not able to connect to run the above commands. That means you should use root for only Administration purpose only.

You can run below command to change the MAX_CONNECTIONS

SET GLOBAL max_connections = 200;
But note that you need to find out is there any application issue which is creating more connections and causing this issue and fix that later.

Regards
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

Best practices for running Tableau on a VM (Virtual Machine)

Tableau Server is resource-intensive and latency-sensitive, that’s why if you are running Tableau on a VM then dedicated resources required.

1.       Dedicated vCPU
100% dedicated vCPU allocation – should not be pooled or shared.
Core count is based on "physical" cores. Physical cores can represent actual server hardware or cores on a virtual machine (VM). Hyper-threading is ignored.
2.       Dedicated RAM
Fully (100%) pre-allocated, no pooling or sharing, no dynamic RAM.
Contiguous on the host server.
Suggested 128GB RAM per 8 core for Enterprise Deployments.
3.       Disk
Write speed is often the bottle neck, faster the better!
150MB/s or less = BAD
250MB/s+ WRITE = GOOD
400MB/s to 1GB/s+ = GREAT
250MB/s Write & 1GB/s Read = Good performance
Tiered SAN: Tableau should be on higher tier with better IO than typical storage level tier.
No underlying network attached storage (NAS) for disk.

Always note that Poorly tuned VM’s results “Poor performance” and Wasted $$ on core purchases

VM Tuning Tips Directly From VMWare
  
                 (http://www.vmware.com/pdf/Perf_Best_Practices_vSphere5.5.pdf)
        See page 17 for recommended BIOS setting
       Deploying Extremely Latency-Sensitive Applications in vSphere 5.5 (http://www.vmware.com/files/pdf/techpaper/latency-sensitive-perf-vsphere55.pdf)
        See page 15 for best practices for latency-sensitive applications
       Upgrade network adapter driver in VM cluster

Other things needs to consider

       Virus Scan can impact performance
       Network latency between worker machines should be less than 10ms.
       Single network hop between servers (contiguous network access).

Regards
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com