DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Saturday, April 27, 2019

OGG-01172 Discard file (dirrpt/REPTMP.dsc) exceeded max bytes (20000000).

OGG-01172  Discard file (dirrpt/REPTMP.dsc) exceeded max bytes (20000000).

One of my replicate got abended due to discard file reach max bytes value.

There is two option to resolve this issue.

Option : 1

Take backup of current discard file and purge it.

oracle@test dirrpt]$ du -sh REPTMP.dsc
[oracle@test dirrpt]$ ls -ltr REPTMP.dsc
-rw-r-----. 1 oracle oracle 2000930198 Apr 27 12:42 REPTMP.dsc
[oracle@test dirrpt]$ cp REPTMP.dsc REPTMP_BKP.dsc
[oracle@test dirrpt]$ >REPTMP.dsc
[oracle@test dirrpt]$ cat REPTMP.dsc

Option : 2

To increase size of the discardfile to a higher value.

Edit replicat parameter file and change DISCARDFILE parameter as below:


Save the replicat parameter file and quit from editor.
Start the replicat.

Thursday, April 25, 2019

OGG-02538 Cannot unregister REPLICAT REPIR because of the following SQL error: OCI Error ORA-00942: table or view does not exist (status = 942)

Unregistering an INTEGRATED REPLICAT from an unsupported Oracle database (

OGG-02538  Cannot unregister REPLICAT REPIR because of the following SQL error: OCI Error ORA-00942: table or view does not exist (status = 942).

Database Version:
Golden gate Version: 12.2

In this blog we are going to see how to manually remove an integrated replicat after configure on unsupported Oracle Database release


2019-04-20 11:47:36  ERROR   OGG-02538  Cannot unregister REPLICAT REPIR because of the following SQL error:

OCI Error ORA-00942: table or view does not exist (status = 942).

There is two option to delete replicat from unsupported oracle database release.

1. Try to delete replicat using force option:

GGSCI> delete  replicat REPTMP !

If may be we are getting the same error message then we need to move second option.

2. Execute dba_xstream_inbound and dba_apply packages:

Connect database using sysdba and run below two select queries.

 select * from dba_xstream_inbound;
 select * From dba_apply;

If above two queries are return any values then need to  execute below two mentioned packages.

This two packages will remove the entries from above two tables.

exec dbms_xstream_adm.drop_inbound('<your inbound server name>');

exec dbms_apply_adm.drop_apply('<your apply name>');

Then we can delete registered replicat without any error.

Tuesday, April 16, 2019

Login to Oracle Cloud .... After Oracle Cloud Promotion Account Creation

This post is in continuation of my previous post.

After you created promotion account, you will receive an confirmation email along with credentials from oracle.

Login to Oracle Cloud

1. Click over the "Get Started with Oracle Cloud" , which redirects to oracle cloud account login page. Enter the credentials you received in your email.

As this is the first time you are logging in , it will prompt you to reset your account password. Reset your password and click on submit

Once you reset, Login to oracle Cloud using :

How to Create Oracle Cloud Promotion Account

Learn how to create and set up your Oracle Cloud Promotion account. There are certain factors and points people tend to ignore. With unique pointers and tips, We are presenting you with a comprehensive set of steps that will assure a perfect set up.  This informative blog post will make it seem like a walk in the park

1. Open below link in Web Browser. Enter Valid Email Address

2. Click on Next Button. Which implicitly mean you are agreeing to the terms ( if you are using it for Commercial purpose, suggested to review it once). 

3. if you are going to use Oracle Cloud for Databases at work place, Select Business. if in case you are going to use Oracle cloud for educating yourself then select personal.

4. Cloud account name should be unique, to make it easy use your email id.

5. This post is as of April 2019, Default Data Region shows me for EMEA and North America. Select based on your region, near to you or preference.

6. Enter your First name, last name , Valid Address and Valid phone number , which will be verified.

7. you cannot use same phone number twice

8. Verification process will send otp, Once mobile number verified , Enter the credit Card details.
Please note - it will verify card but wont charge until you confirm to continue with Oracle cloud.

9. Once finished, you will be asked to click on complete signup.

10. Please note : it will take about 15 minute to provision your account

Tuesday, April 2, 2019

   18c Oracle Database Installation(On-Premise)

You have three options to install database; Starting with Oracle Database 18c release.
1) Image-based Oracle Database Installation
2) RPM-Based Oracle Database Installation
3) Read-Only Oracle Home

1) Image-based Oracle Database Installation (new in Oracle 18c, on-premise)
Image-based Installation enables you to download zip file and extract the image software into your Oracle Home directory and run Installer script to start database installation.
Unzip the download and Re-link binaries...that's it.

2) RPM-Based Oracle Database Installation
Here, using RPM-based Database Installation (RDI), use rpm -ivh command to install database installation, which performs pre-validations, extract packages assigns ownership, oracle inventory, users and groups, and executes all other scripts and root operations to complete database software installation.

3) Read-Only Oracle Home (new in Oracle 18c, on-premise)
In this option, you are privileged to make your Oracle Home in read-only mode. Database tools and processes writes under ORACLE_BASE path rather ORACLE_HOME directory.
This helps to do version control and patching oracle home. Oracle has include built-in tool, called "rhpctl", that enables you to switch from the current Oracle Database home to a patched Oracle Database home.

I am using Image-based Oracle Database Installation

Download Oracle Dabase 18c from this link.

Unzip the installer.
$ unzip -d /u01/DB18c

Database binaries are installed. 

Now you can use dbca to configure database.
Create and configure single instance database.

SYSTEM Tablespace is space increasing Abnormally in 12c

SYSTEM tablespace was growing rapidly. We observe that we were not doing much on this database. Only few users were working to test the application
There was no much load as well, still it's went up to 100gb. 

Database version is 12cR1

Possibly there are few reasons for the same.

1)    Some has assigned default tablespace as SYSTEM
2)    Auditing is ON and consuming more space.
3)    SYS_LOB Objects uses lot of disk space
4)    Data dictionary objects uses lot of disk space

How to find the root cause and what could be the solution?

Let’s query to dba_segments table to find the root cause. Here we have few scenarios in different databases, discussing those scenarios, what could be the problem and solution of it.

Use below query to find out what are those segments which are consuming lot of space in SYSTEM tablespace.

select owner,segment_name,segment_type
 ,bytes/(1024*1024) size_mb
 from dba_segments
 where tablespace_name = 'SYSTEM'
 order by size_mb desc

Scenario 1

Here in above database my AUD$ table is growing rapidly and it is almost 30gb in size.

Auditing is enabled hence AUD$ table is growing very fast. Organization wants to do auditing and there is no option to disable it.

Few solutions which Oracle always recommended,
 ‘-  Move AUD$ to different tablespace 
‘- Purge audit data
‘- Delete/truncate older data

Scenario 2

ARGUMENT$, I_ARGUMENT1, I_ARGUMENT2, IDL_UB1$, SOURCE$ ; tables and indexes showing very huge in size. 
These tables are core dictionary tables. Consider like these tables stores your database/schema/table procedure’s meta data information.

You cannot move those tables to another table space or truncate or reorg of these table. Oracle strictly says that don’t touch those tables as these are metadata tables. If you move or truncate tables, you might lose database information and this will corrupts your database as well.

So, is this the ideal scenario? 
Can’t we control the growth of those tables like AUD$ tables?

Answer is, Yes, this is the ideal scenario and we don’t have control over it.

Scenario 3
SYSTEM tablespace growing unexpectedly after database upgrade OR after IMPDP

If you are upgrading your database from 11g to 12c, this might happen that you observer some unexpected growth of SYSTEM tablespace.

Here I would like to highlight that when you do the data import; import do lots of compilation of your function, procedure and packages. Metadata is associated with PL/SQL library. During compilation it generates duplicate rows in ARGUMENT$ table, which cause to huge size of ARGUMENT$ table.

This behavior found in 12c database due to new features introduced and reported as BUG. Here is the bug information.

Here the workaround is to recompile the objects. This will decrease the logical space, not the physical space.
Run this command after impdp,
1) alter session set events ='10946 trace name context forever, level 8454144'; 

2) exec utl_recomp.recomp_parallel('4','<USERNAME>');   

Scenario 4

Data dictionary tables are occupying more space like BOOTSTRAP$, FILE$, UNDO$, IDL_UB1$, SOURCE$ etc.
Such issue found in Oracle Database 9i, 10g, 11g. Still I haven’t faced this type of issue in 12c or 18c.

To resolve this issue run catalog.sql and catproc.sql and re-query to dba_segments. This will work only in certain scenarios only, this is not for all.

Monday, April 1, 2019

Terraform : Creating New EC2 instance via Terraform

Terraform is dominating the market due to its compatibility with multiple cloud support. It is a tool that allows anyone to customize the infrastructure, proficiently. It can work with the pre-existing services or adapt to the custom. Learn how to install and use the tool proficiently with this blog post.

What is Terraform

Terraform is a tool for building, changing, and versioning infrastructure safely and efficiently. Terraform can manage existing and popular service providers as well as custom in-house solutions.

The infrastructure Terraform can manage includes low-level components such as compute instances, storage, and networking, as well as high-level components such as DNS entries, SaaS features, etc.

Terraform supports Multiple Cloud providers, Oracle , AWS, Microsoft..List goes on

terraform works based on or using configuration files with extension .tf 

How to Install

its Pretty Simple , 
1. Download the zip file which contains the executable based on your platform from

2. Extract the zip file

3. Set the Path Environment Variable mapping to location where you extracted the zip file

4. open bash prompt or command prompt, and check , terraform --version

C:\Users\dummy> terraform --version
Terraform v0.11.13

How to create configuration file

The set of files used to describe infrastructure in Terraform is simply known as a Terraform configuration. We're going to write our first configuration now to launch a single AWS EC2 instance.
The format of the configuration files is documented here. Configuration files can also be JSON, but we recommend only using JSON when the configuration is generated by a machine.
The entire configuration is shown below. We'll go over each part after. Save the contents to a file named Verify that there are no other *.tf files in your directory, since Terraform loads all of them.
open notepad or vi , , copy the below highlight contents and make required changes , and save it.
Below case we are using provider as aws , ie amazon web services.
Resource - Type of resource , we are planning to provision or manage
provider "aws" {
  access_key = "vandendapaalkaren"
  secret_key = "adaddaaapasumattapathipadaporen"
  region     = "us-east-2"

resource "aws_instance" "example" {
  ami           = "ami-0cd3dfa4e37921605"
  instance_type = "t2.micro"


Note: The above configuration is designed to work on most EC2 accounts, with access to a default VPC. For EC2 Classic users, please use t1.micro for instance_type, and ami-408c7f28 for the ami. If you use a region other than us-east-1 then you will need to choose an AMI in that region as AMI IDs are region specific.

As mentioned above , Replace the ACCESS_KEY_HERE and SECRET_KEY_HERE with your AWS access key and secret key, available from this page.

How to configure a resource in Cloud?

Once the configuration file is created, There are two step process

go to the location where created the *.tf file, Ensure there is no other tf created over there

Step 1 : init - Execute "terraform init" from the command prompt,

init will download the plugin required ,and initializes various local settings and data that will be used by subsequent commands

c:\terraform>terraform init

Step 1.2  : Plan - if in case there are any changes to the configuration after init, then you need to Execute "terraform plan" from the command prompt,

In the same directory as the file you created, run terraform plan

Step 2 : apply - Once all set with configuration, then we need to apply the configuration. Execute "terraform apply" from the command prompt,


Check now in your AWS Console....Congratulations.