Compression of dumpfiles with EXP/IMP/EXPDP:

One of the biggest challenges for DBAs is the lack of disk space especially for the backups. Two days before, I came across the ย situation where I was need to do export/import schema from UAT to DEV, but none of the mount points on filesystem were having sufficient space available to fit export dumpfile. In my case, taking EXPDP schema backup followed by compresing dumpfile with GZIP/BZIP2 was not possible due to insufficient disk space.

So I started exploring different methodologies to apply compression on dumpfile which will allow me to compress dumpfile on fly.

1. Use of pipes:

Export:


cd /home/oracle
# create a named pipe
mknod exp_disc_schema_scott.pipe p
# read the pipe - output to zip file in the background
gzip < exp_disc_schema_scott.pipe > exp_disc_schema_scott.dmp.gz &
# feed the pipe
exp file=exp_disc_schema_scott.pipe log=exp_disc_schema_scott.log owner=scott

Import:


cd /home/oracle
# create a name pipe
mknod imp_disc_schema_scott.pipe p
# read the zip file and output to pipe
gunzip < imp_disc_schema_scott.dmp.gz > imp_disc_schema_scott.pipe &
# feed the pipe
imp file=imp_disc_schema_scott.pipe log=imp_disc_full.log fromuser=scott touser=scott1

Advantages:

1. Compression happens parallel with the export.
2. Compressed dumpfile can be used for import without decompression. (As shown above)
3. Can be used for 10g as well as 11g.

Disavantages:

1. Can’t use this methodology for EXPDP/IMPDP.


2. Advanced compression:

From 11g, You can used advanced compression menthology to compress dumpfiles on fly. This can be used for compressing data, metadata (which is the default value), both and none.


expdp directory=DATA_PUMP_DIR1 dumpfile=exp_disc_schema_scott.dmp logfile=exp_disc_schema_scott.log schemas=scott compression=all.

Advantages:

1. Compression happens parallel with the export.
2. Compressed dumpfile can be directly used for import without decompression.

Disavantages:

1. It takes more time than normal EXPDP operation.(without compression)
2. Advanced Compression License option must be enabled which is an extra cost.
3. This option is just available from Oracle 11g


 

Hope so u will find this post very useful ๐Ÿ™‚

Cheers

Regards,

Adityanath

Advertisements

How to start & stop Enterprise Manager(EM) or Oracle Management Services(OMS) in 12C

To stop enterprise manager 12c you need to follow the below steps :

1. Stop the Oracle Management Service:

Run the below command from $OMS_HOME
emctl stop oms -all

2. Stop the Management Agent:

Run the below command from $AGENT_HOME
emctl stop agent

3. Stop Oracle Management Repository

For this u need to stop database & listener services.

To Start enterprise manager 12c you need to follow the below steps :

1. Start Oracle Management Repository:

Start database & listener services.

2. Start the Oracle Management Service:

Run the below command from $OMS_HOME
emctl start oms

3. Start the Management Agent:

Run the below command from $AGENT_HOME
emctl start agent


Hope so u will find this post very useful ๐Ÿ™‚

Cheers

Regards,

Adityanath

Deploying Oracle E-Business Suite plug-in or Oracle Application Management Pack into ORACLE OEM 12C

This post will provide u steps to deploy Oracle E-Business Suite plug-in into ORACLE OEM 12C which is also known as Oracle Application Management Pack for Oracle E-Business Suite.

The Oracle Application Management Pack for Oracle E-Business Suite extends Oracle Enterprise Manager 12c Cloud Control to help monitor and manage Oracle E-Business Suite systems more effectively. The pack integrates Oracle Applications Manager with Cloud Control to provide a consolidated, end-to-end Oracle E-Business Suite management solution. The pack can be used to manage both Oracle E-Business Suite Release 11i systems and Release 12 systems.

Before proceeding with deployment u first need to download “Oracle Application Management Suite for Oracle E-Business Suite 12.1.0.3.0” from https://edelivery.oracle.com. This will be a zip file named :V46070-01.zip & with size 433MB.

Now u r ready for OAM suite deployment:


oracle@rmb-zdr-oem01:$export EMCLI_STATE_DIR=/export/home/oracle/plugins
oracle@rmb-zdr-oem01:$export PATH=$PATH:$EMCLI_STATE_DIR
oracle@rmb-zdr-oem01:$ /u01/oracle/12C/middleware/oms/bin/emcli setup -dir=$EMCLI_STATE_DIR -url=https://oracle@rmb-zdr-oem01:7799 -user=sysman
Oracle Enterprise Manager 12c Release 4.
Copyright (c) 1996, 2014 Oracle Corporation and/or its affiliates. All rights reserved.
The configuration directory "/export/home/oracle/plugins" may not be local. See the "dir" option in the help for the setup command.
Do you want to continue using this directory? [yes/no] yes
Enter password:

This process should successfully complete giving status : Emcli setup successful.

Extract V46070-01.zip which will give u this file : ~/software/EBS-PlugIn/12.1.0.3.0/12.1.0.3.0_oracle.apps.ebs_2000_0.opar. Now update this plugin into OEM.


oracle@zbx-oem01:$ /u01/oracle/12C/middleware/oms/bin/emcli import_update -file="/export/home/oracle/plugins/software/EBS-PlugIn/12.1.0.3.0/12.1.0.3.0_oracle.apps.ebs_2000_0.opar" -omslocal
Processing update: Plug-in - Enterprise Manager for Oracle E-Business Suite consists of System Management and Change Management Feature Sets
Successfully uploaded the update to Enterprise Manager. Use the Self Update Console to manage this update.

Now U need to use OEM/OMS console to deploy plugin. PFB screenshots for the same: Go to Setup => Extensibility => Plug-ins:

ebiz pluggin

 

ebiz pluggin

ebiz pluggin2

ebiz pluggin3

ebiz pluggin4

ebiz pluggin5

ebiz pluggin7

After this U need to monitor progress using command : emctl status oms -details:

Once everything is back up & running, U can log out and login to OEM console to have deployed Oracle E-Business Suite plug-in ๐Ÿ™‚

ebiz plugin screenshots status

ebiz pluggin9

ebiz pluggin10

Hope so u will find this post very useful ๐Ÿ™‚

Cheers

Regards,

Adityanath

Flashback truncate table Using Oracle Total Recall OR Flashback Data Archive

From version 11g Oracle has come with new enhancement – Flashback Data Archive also known as Oracle Total Recall.

With the “Oracle Total Recall” option, Oracle database 11g has been specifically enhanced to track history with minimal performance impact and to store historical data in compressed form to minimize storage requirements, completely transparent to applications, easy to setup. It is a logical container for storing historical information. It is stored in one or more tablespaces and tracks the history for one or more tables.You specify retention duration for each flashback data archive (could be # of years).

Normally traditional flashback table has its own limitation as it cannot flashback table on which any DDL performed including truncate. Look at example given below:


13:40:10 SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
 YES
13:40:38 SQL> create table test as select * from dba_users;
Table created.
13:41:20 SQL> select count(0) from test;
COUNT(0)
----------
 10
13:41:29 SQL> insert into test select * from test;
10 rows created.
13:42:00 SQL> commit;
Commit complete.
13:42:03 SQL> select count (0) from test;
COUNT(0)
----------
 20
13:45:06 SQL> alter table test enable row movement;
Table altered.
13:45:20 SQL> FLASHBACK TABLE test TO TIMESTAMP TO_TIMESTAMP('2014-08-21 13:41:29', 'YYYY-MM-DD HH24:MI:SS');
Flashback complete.
13:45:30 SQL> select count(0) from test;
COUNT(0)
----------
 10
13:46:08 SQL> truncate table test;
Table truncated.
13:46:28 SQL> select count(0) from test;
COUNT(0)
----------
 0
13:46:40 SQL> FLASHBACK TABLE test TO TIMESTAMP TO_TIMESTAMP('2014-08-21 13:41:29', 'YYYY-MM-DD HH24:MI:SS');
FLASHBACK TABLE test
 *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
14:04:26 SQL> select count(*) from test as of timestamp TO_TIMESTAMP('2014-08-21 13:41:29', 'YYYY-MM-DD HH24:MI:SS');
select count(*) from test as of timestamp TO_TIMESTAMP('2014-08-21 13:41:29', 'YYYY-MM-DD HH24:MI:SS')
 *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

Here we can conclude that we can not flashback table which has been truncated. We can overcome with this limitation using Oracle Total Recall. Please find below steps to use this feature :

14:31:33 SQL> create flashback archive flash_test tablespace FATBS retention 10 year;
Flashback archive created.
14:32:15 SQL> alter table test flashback archive flash_test;
Table altered.
14:32:56 SQL> insert into test select * from dba_users;
10 rows created.
14:33:24 SQL> commit;
Commit complete.
14:33:28 SQL> select count(0) from test;
COUNT(0)
----------
 20
14:33:37 SQL> truncate table test;
Table truncated.
14:34:05 SQL> FLASHBACK TABLE test TO TIMESTAMP TO_TIMESTAMP('2014-08-21 14:33:36', 'YYYY-MM-DD HH24:MI:SS');
FLASHBACK TABLE test TO TIMESTAMP TO_TIMESTAMP('2014-08-21 14:33:36', 'YYYY-MM-DD HH24:MI:SS')
 *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
14:34:25 SQL> select count(*) from test as of timestamp TO_TIMESTAMP('2014-08-21 14:33:36', 'YYYY-MM-DD HH24:MI:SS');
COUNT(*)
----------
 20
14:34:56 SQL> insert into test select * from test as of timestamp TO_TIMESTAMP('2014-08-21 14:33:36', 'YYYY-MM-DD HH24:MI:SS');
20 rows created.
14:35:59 SQL> commit;
Commit complete.
14:36:05 SQL> select count(0) from test;
COUNT(0)
----------
 20

I got my data back even though table was truncated. ๐Ÿ™‚

Flashback archive restrictions:

  • If flashback archive is enabled some DDL operations can cause ORA-55610 โ€“ Invalid DDL statement on history-tracked table like drop table, alter table etc.
  • You canโ€™t enable Flashback Data Archive for temporary, external tables & tables containing long datatype

Reference Note : 11g feature: Flashback Data Archive Guide. (Doc ID 470199.1)

Hope so u will find this post very useful ๐Ÿ™‚

Cheers

Regards,

Adityanath