Skip to content

Category: DATABASE

Oracle EXPDP/IMPDP (DATAPUMP) Monitoring Scripts

Published August 6, 2017 By Brijesh Gogia

Usually we monitor the EXPDP/IMPDP jobs by monitoring the log files generated by expdp/impdp process.  Also we monitor alert log too just in case some error pops up. This helps most of the time. If you have a long running expdp/impdp sessions as you are exporting/importing huge GBs then it helps to have a more detailed monitoring of the expdp/impdp jobs. Some of the useful queries which can be used to monitor the  Data Pump Jobs are mentioned below.

To start with some of the important tables/views that you should refer to monitor Data Pump Jobs are:

Continue ReadingOracle EXPDP/IMPDP (DATAPUMP) Monitoring Scripts
2 Comments

Connecting Oracle Database and SQL Server – Basic Concepts

Published July 8, 2017 By Brijesh Gogia

It is required sometime to connect oracle database to SQL Server database using uni-direction or bi-direction methods. Since these are two different database so something as simple as direct DBLINK will not work here. Below are the basic concepts of making connection from SQL SERVER to Oracle and vice-versa.

Continue ReadingConnecting Oracle Database and SQL Server – Basic Concepts
Leave a Comment

“Wait for a undo record” OR “Wait for stopper event to be increased” database wait events

Published June 19, 2017 By Brijesh Gogia

We faced a scenario in our database where users were complaining about database performance. Database seems to be hanging and queries were either stuck or not running at all.

It was also seen that UNDO tablespace consumption was increasing.

Continue Reading“Wait for a undo record” OR “Wait for stopper event to be increased” database wait events
Leave a Comment

Oracle ASM – ORA-19504: failed to create file – SVR4 Error: 13: Permission denied

Published June 8, 2017 By Brijesh Gogia

During one of our 11gR2 database restore activity we found below error message in the RMAN “duplicate database command”

channel aux4: ORA-19870: error while restoring backup piece /oraclone/ORCL/rman_clone/ORCL_F_20170605_c1s60alv_1_1
ORA-19504: failed to create file "+DATA3"
ORA-15045: ASM file name '+DATA3' is not in reference form
ORA-17502: ksfdcre:5 Failed to create file +DATA3
ORA-15081: failed to submit an I/O operation to a disk
ORA-27091: unable to queue I/O
ORA-27041: unable to open file
SVR4 Error: 13: Permission denied
Continue ReadingOracle ASM – ORA-19504: failed to create file – SVR4 Error: 13: Permission denied
1 Comment

Reorg tables and indexes in Oracle EBS Applications – Best Practices

Published May 7, 2017 By Brijesh Gogia

Usually in OLTP environment like EBS Applications, tables are often get fragmented due to multiple DML activities that happens.Fragmented tables cause queries on those tables to slow down. It is very important to de-fragment this table and to reclaim the fragmented space from these objects.

For EBS we have also seen  that usually gathered statistics, indexing and proper SQL tuning is plenty to improve and maintain acceptable performance but sometime it is required to reorg the table.

One primary cause of fragmentation is that when you run delete command on the tables it delete the rows but doesn’t frees up the memory and also do not changes the high water mark.

Continue ReadingReorg tables and indexes in Oracle EBS Applications – Best Practices
7 Comments

Migrating on-premise 12c pluggable database to Oracle Cloud Database

Published January 7, 2017 By Brijesh Gogia

In this post we will explore one of the method for moving your on-premise database to the Database Cloud Service. There are multiple options for solving this data movement challenge. In this post our focus will be to use SQL*Developer and command line tools to clone and move a pluggable database from our on-premise database to our cloud database. Pluggable databases is a feature of Oracle database 12c. We are using version 12.1.0.2 of Oracle database 12c.

There can be other simpler methods also if you have infrastructure/network in place to connect from your cloud databse to your on-premise database directly, like:

  • Using OEM, you can do this work very easily in a few click-and-watch steps.
  • Using DBLINK. This is the also one of the easiest way
  • Using SQLDVELOPER and choosing “Clone PDB to oracle Cloud” option. All required ports b/w on-premise and Oracle Cloud must be open and network/firewall related requirements must be met to use this option.

For this example we are using SQL Developer to clone the PDB but will not be using direct “Clone PDB to Oracle Cloud” option. We will be making a clone copy of the on-premise database, unplug it, copy over the data files to Oracle Cloud and plug it there.

Continue ReadingMigrating on-premise 12c pluggable database to Oracle Cloud Database
Leave a Comment

Clone Oracle 12c Pluggable database from one Oracle Cloud Service to another

Published January 5, 2017 By Brijesh Gogia

In this post we will be cloning  an Oracle Database 12c Pluggable Database from one Database Cloud Service to another.

We can have multiple options for doing this data migration. For our example we will use remote cloning of a PDB using Database Links. Note that this post will use the unique features of Oracle Database 12c Multitenant and Pluggable Databases (PDB). This was a new and the key feature of Oracle 12c which is not present in Oracle database version 11g.

Continue ReadingClone Oracle 12c Pluggable database from one Oracle Cloud Service to another
2 Comments

Transparent Data Encryption (TDE) – Oracle Database Cloud

Published December 31, 2016 By Brijesh Gogia

Transparent Data Encryption (TDE) has been a standard feature of Oracle database for quite a long time. Since database in cloud is increasingly becoming popular so this feature has taken a great importance keeping in mind that primary concerns that administrators have in keeping data in cloud (remote) servers is security.

Oracle Advanced Security Transparent Data Encryption (TDE) stops would-be attackers from bypassing the database and reading sensitive information from storage by enforcing data-at-rest encryption in the database layer. In this post we are going to demonstrate how the Transparent Data Encryption ensures that the data stored in the Oracle Database is protected and cannot be accessed from malicious users that have gained access at the OS level.

Continue ReadingTransparent Data Encryption (TDE) – Oracle Database Cloud
2 Comments