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