Below are steps to upgrade the timezone from version 4 to 14 in 11.2.0.4 database. When you upgrade a 10g (10.2.0.4) database to 11g (11.2.0.4) database, while running the utlu112i.sql script, you will get a warning for old timezone of database which needs to be upgraded 14. The warning is like below:

WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.4.0 database timezone version
.... to the latest version which comes with the new release.

One database is upgraded successfully to 11.2.0.4, follow the below steps to upgrade the timezone as well. Also refer to oracle support doc 1358166.1 for detailed information on timezone upgrade. It’s recommended to go through this document to understand the affects of below commands and any deviation from below steps if slight change in versions involved in this upgrade.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;  2    3    4

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION         4
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
         4

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

SQL> EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_DST.BEGIN_PREPARE(14);

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
  2    3    4
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION         4
DST_SECONDARY_TT_VERSION       14
DST_UPGRADE_STATE              PREPARE

SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$affected_tables;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$error_table;

Table truncated.

SQL> set serveroutput on
SQL> BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
  2    3    4    5    6    7
  8  /

PL/SQL procedure successfully completed.

SQL>

SQL> SELECT * FROM sys.dst$affected_tables;

no rows selected

SQL> SELECT * FROM sys.dst$error_table;

no rows selected

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';

no rows selected

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';

no rows selected

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');

no rows selected

SQL> EXEC DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'
 ORDER BY PROPERTY_NAME;
  2    3    4
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION         4
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

SQL>
SQL> select name from v$database;

NAME
---------
TEST11G

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1904128000 bytes
Fixed Size                  2222600 bytes
Variable Size             721421816 bytes
Database Buffers         1174405120 bytes
Redo Buffers                6078464 bytes
Database mounted.
Database opened.
SQL> set serveroutput on
SQL>
SQL> set timing on
SQL>
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;  2    3    4

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION         4
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

Elapsed: 00:00:00.40
SQL> purge dba_recyclebin;

DBA Recyclebin purged.

Elapsed: 00:00:00.17
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

Table truncated.

Elapsed: 00:00:00.18
SQL> TRUNCATE TABLE sys.dst$affected_tables;

Table truncated.

Elapsed: 00:00:00.03
SQL> TRUNCATE TABLE sys.dst$error_table;

Table truncated.

Elapsed: 00:00:00.01
SQL> alter session set "_with_subquery"=materialize;

Session altered.

Elapsed: 00:00:00.00
SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

Elapsed: 00:00:00.00
SQL> EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.61

SQL> SELECT * FROM sys.dst$error_table;

no rows selected

Elapsed: 00:00:00.03
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
  2    3    4
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION         14
DST_SECONDARY_TT_VERSION       4
DST_UPGRADE_STATE              UPGRADE

Elapsed: 00:00:00.01
SQL> select OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS from ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

no rows selected

Elapsed: 00:00:02.44
SQL>
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1904128000 bytes
Fixed Size                  2222600 bytes
Variable Size             721421816 bytes
Database Buffers         1174405120 bytes
Redo Buffers                6078464 bytes
Database mounted.
Database opened.
SQL>

SQL> alter session set "_with_subquery"=materialize;

Session altered.

Elapsed: 00:00:00.33
SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

Elapsed: 00:00:00.00
SQL> EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
SQL> VAR numfail number
SQL> BEGIN
    DBMS_DST.UPGRADE_DATABASE(:numfail,
    parallel => TRUE,
    log_errors => TRUE,
    log_errors_table => 'SYS.DST$ERROR_TABLE',
    log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
    error_on_overlap_time => FALSE,
    error_on_nonexisting_time => FALSE);
    DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
   END;
   /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.48
SQL> SELECT * FROM sys.dst$error_table;

no rows selected

Elapsed: 00:00:00.02
SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
SQL>   2    3    4    5

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.32
SQL> SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
  2    3    4
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION         14
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

Elapsed: 00:00:00.00
SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_14.dat              14

Elapsed: 00:00:00.00
SQL>

Remember to follow all the steps without fail as missing any step would lead to serious issue. Hope, this will help.

Advertisements

Recently I tried to create an incident package for Oracle support and find it very easy and convenient to use, so thought of sharing…. Basically through ADRCI we can combine all incident related files to one and send to oracle support. Apart from this it can be used for viewing diagnostic data with in automatic diagnostic repository (ADR). Diagnostic data includes incident and problem descriptions, trace files, dumps, health monitor reports, alert log entries, and more. Below are steps that I used for creating two incident packages.

First, go to adrci prompt and check the reported incidents. In my case, I have two databases GLDB & ACCDB running from same oracle home, so it will show incidents for both databases. When I tried to create package I got below error:

adrci> IPS CREATE PACKAGE INCIDENT 156129
DIA-48448: This command does not support multiple ADR homes

Reason for this is that we need to set the home right from which we need to create incident

adrci> show homes
ADR Homes:
diag/rdbms/gldb/GLDB
diag/rdbms/testdb1/TESTDB1
adrci> set home diag/rdbms/gldb/GLDB

Below is full text for commands that are used to create a package.

/u01/app/oracle/product/11.2.0.3 =>adrci

ADRCI: Release 11.2.0.3.0 - Production on Sun Mar 30 09:34:48 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle/admin"
adrci>
adrci>
adrci>
adrci> show incident

ADR Home = /u01/app/oracle/admin/diag/rdbms/gldb/GLDB:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
108209               ORA 445                                                     2014-02-13 01:17:01.410000 +01:00
108210               ORA 445                                                     2014-02-13 12:02:03.713000 +01:00
108211               ORA 445                                                     2014-02-14 07:02:22.391000 +01:00
156129               ORA 445                                                     2014-03-30 03:02:50.743000 +02:00
156130               ORA 445                                                     2014-03-30 04:40:54.699000 +02:00
156217               ORA 445                                                     2014-03-30 05:59:43.103000 +02:00
156218               ORA 445                                                     2014-03-30 06:01:48.879000 +02:00
156219               ORA 445                                                     2014-03-30 06:03:57.081000 +02:00
156220               ORA 445                                                     2014-03-30 06:06:00.327000 +02:00
156221               ORA 445                                                     2014-03-30 06:08:03.929000 +02:00

ADR Home = /u01/app/oracle/admin/diag/rdbms/accdb/ACCDB:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
67665                ORA 445                                                     2014-02-13 11:49:06.173000 +01:00
67425                ORA 445                                                     2014-02-13 11:51:06.115000 +01:00
2 rows fetched

adrci> IPS CREATE PACKAGE INCIDENT 156129
DIA-48448: This command does not support multiple ADR homes

adrci> show homes
ADR Homes:
diag/rdbms/gldb/GLDB
diag/rdbms/testdb1/TESTDB1
adrci> set home diag/rdbms/gldb/GLDB
adrci> IPS CREATE PACKAGE INCIDENT 156129
Created package 1 based on incident id 156129, correlation level typical
adrci> IPS ADD INCIDENT 156129 PACKAGE 1
Added incident 156129 to package 1
adrci> IPS GENERATE PACKAGE 1 IN /u01/app/oracle/admin
Generated package 1 in file /u01/app/oracle/admin/ORA445_20140330094558_COM_1.zip, mode complete
adrci>

adrci> adrci>
adrci> IPS CREATE PACKAGE INCIDENT 156130
Created package 2 based on incident id 156130, correlation level typical
adrci> IPS ADD INCIDENT 156130 PACKAGE 2
Added incident 156130 to package 2
adrci> IPS GENERATE PACKAGE 2 IN /u01/app/oracle/admin
Generated package 2 in file /u01/app/oracle/admin/ORA445_20140330095106_COM_1.zip, mode complete
adrci> exit
/u01/app/oracle/product/11.2.0.3 =>

Once package is created it’s basically a zip file containing all the required files for the particular incident like alert.log file, trc & trm files. I just unzipped the one of the files generated from packaging incident and below is directory structure for this.

ADRCI zip file directory structure

ADRCI zip file directory structure

Also I have posted a poll to know, how many people are using this feature for oracle support. Please give your inputs on this.

Today i seen that “create user identified by values” command was not working. Below is the error message:

SQL> CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP";
CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP"
*
ERROR at line 1:
ORA-02153: invalid VALUES password string

Well, the reason for above error is that i have taken the above script from a 11g database and running it on 10g database. 11g has bring some changes in password management. Below code is executed on 11g and user created successfully, which is expected result.

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 20 06:36:55 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> create user myuser identified by myuser default tablespace user_data temporary tablespace temp;

User created.

SQL> set long 100000
SQL> set line 1000
SQL>
SQL> select dbms_metadata.get_ddl('USER','MYUSER') from dual;

DBMS_METADATA.GET_DDL('USER','MYUSER')
--------------------------------------------------------------------------------

   CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A
009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3'
      DEFAULT TABLESPACE "USER_DATA"
      TEMPORARY TABLESPACE "TEMP"


SQL> drop user myuser cascade;

User dropped.

SQL> CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP";

User created.

SQL> show parameter case

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE

SQL> conn myuser/myuser
Connected.
SQL>
SQL> conn myuser/MYUSER
Connected.
SQL>

Now, what to do for running this in 10g database and here comes the new thing in password.

SQL> select d.username,u.password,d.password_versions,u.spare4 from dba_users d,user$ u where d.username=u.name and d.username='MYUSER';

USERNAME   PASSWORD                       PASSWORD SPARE4
---------- ------------------------------ -------- ------------------------------------------------------------------------------------------
MYUSER     1205E0F44EE803D3               10G 11G  S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD

See in above query there are two types of passwords in above query which are taken from user$ table. Our old 10g format password is saved in column PASSWORD and new 11g format password is stored in column SPARE4 in user$ table. Just to verify the above thing, if we closely monitor the user creation script then there is “;” in password seperating the 10g & 11g version od password. See the below code:

'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3'

The shorter password is for 10g and longer one if for 11g only. Below code created user successfully in 10g database while the user creation script is captured from 11g database in above steps.

SQL*Plus: Release 10.2.0.4.0 - Production on Tue May 20 06:40:46 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL>
SQL> CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP";
CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP"
*
ERROR at line 1:
ORA-02153: invalid VALUES password string


SQL> CREATE USER "MYUSER" IDENTIFIED BY VALUES '1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP";

User created.

SQL> conn myuser/myuser
ERROR:
ORA-01045: user MYUSER lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant create session to myuser;

Grant succeeded.

SQL> conn myuser/myuser
Connected.

When the password version field contains both values like “10G 11G” then your password will contains both the 10g & 11g encrypted password and “sec_case_sensitive_logon” parameter is false then you can use case insensitive passwords, like I have logged in successfully with password “myuser” and “MYUSER”.

SQL> select d.username,u.password,d.password_versions,u.spare4 from dba_users d,user$ u where d.username=u.name and d.username='MYUSER';

USERNAME   PASSWORD                       PASSWORD SPARE4
---------- ------------------------------ -------- ------------------------------------------------------------------------------------------
MYUSER     1205E0F44EE803D3               10G 11G  S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD

SQL> show parameter case

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE

SQL> conn myuser/myuser
Connected.
SQL>
SQL> conn myuser/MYUSER
Connected.
SQL>

Now, say you created user with only 11g encrypted password (longer version of string) then you must set the “sec_case_sensitive_logon” parameter to true. Then only you will be able to login with old password as shown in below code. Notice that password_version field only contains 11G now and only spare4 column has encrypted value not the password column as in case of “10G 11G” password.

SQL> CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP";

User created.

SQL> grant create session to myuser;

Grant succeeded.

SQL> conn myuser/myuser
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn myuser/MYUSER
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=TRUE scope=both;

System altered.

SQL> show parameter case

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL>

SQL> conn myuser/myuser
Connected.
SQL>
SQL> conn myuser/MYUSER
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

SQL> select d.username,u.password,d.password_versions,u.spare4 from dba_users d,user$ u where d.username=u.name and d.username='MYUSER';

USERNAME   PASSWORD                       PASSWORD SPARE4
---------- ------------------------------ -------- ------------------------------------------------------------------------------------------
MYUSER                                    11G      S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD

Now, time to create use with 10g encrypted password only. Below is code having details of same. Now, password column has encrypted password while spare4 column is empty. Also, with 10g encrypted password it doesn’t matter what value of parameter “sec_case_sensitive_logon” you set, user will be able to login as in below code.

SQL> CREATE USER "MYUSER" IDENTIFIED BY VALUES '1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP";

User created.

SQL> grant create session to myuser;

Grant succeeded.

SQL> conn myuser/myuser
Connected.
SQL>
SQL> conn myuser/MYUSER
Connected.

SQL> conn / as sysdba
Connected.
SQL>
SQL> select d.username,u.password,d.password_versions,u.spare4 from dba_users d,user$ u where d.username=u.name and d.username='MYUSER';

USERNAME   PASSWORD                       PASSWORD SPARE4
---------- ------------------------------ -------- ------------------------------------------------------------------------------------------
MYUSER     1205E0F44EE803D3               10G

SQL> alter system set sec_case_sensitive_logon=TRUE scope=both;

System altered.

SQL> conn myuser/myuser
Connected.
SQL> conn myuser/MYUSER
Connected.

SQL> conn / as sysdba
Connected.
SQL>
SQL> show parameter sec_case_sensitive_logon

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL>
SQL> select d.username,u.password,d.password_versions,u.spare4 from dba_users d,user$ u where d.username=u.name and d.username='MYUSER';

USERNAME   PASSWORD                       PASSWORD SPARE4
---------- ------------------------------ -------- ------------------------------------------------------------------------------------------
MYUSER     1205E0F44EE803D3               10G

Hope this will help 🙂

This is just a quick comparison between timing & filesize for a 35GB database which we backed up using datapump with & without COMPRESSION.

First time I took a backup without COMPRESSION. Following is logs for this activity.

Export: Release 11.2.0.3.0 - Production on Wed Mar 5 07:46:00 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "BACKUP"."SYS_EXPORT_FULL_01":  backup/********@TESTDB DIRECTORY=oraclebackup FULL=Y DUMPFILE=Full_EXPORT_TESTDB_yya_20140305t.dmp LOGFILE=FULL_EXPORT_TESTDB_yya.LOG PARALLEL=4 CONTENT=ALL
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 33.64 GB
Processing object type DATABASE_EXPORT/TABLESPACE


. . exported "ORDDATA"."ORDDCM_MAPPING_DOCS"             7.890 KB       1 rows
Master table "BACKUP"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BACKUP.SYS_EXPORT_FULL_01 is:
  /orabackup/TESTDB/export/Full_EXPORT_TESTDB_yya_20140305t.dmp
Job "BACKUP"."SYS_EXPORT_FULL_01" successfully completed at 09:04:05

Filesize generated is about 32GB in size and it took about 1 hour 15 mins.

-rw-r-----    1 testdb_t oinstall 35114491904 Mar  5 09:04 Full_EXPORT_TESTDB_yya_20140305t.dmp

Now, second run of same database with COMPRESSION parameters is done. Following is the logs for this activity.

Export: Release 11.2.0.3.0 - Production on Wed Mar 5 09:24:22 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "BACKUP"."SYS_EXPORT_FULL_01":  backup/********@TESTDB DIRECTORY=oraclebackup FULL=Y DUMPFILE=Full_EXPORT_TESTDB_yya_comp_20140305t.dmp LOGFILE=FULL_EXPORT_TESTDB_yya_comp.LOG PARALLEL=4 COMPRESSION=ALL CONTENT=ALL
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 33.64 GB
Processing object type DATABASE_EXPORT/TABLESPACE
. . exported "TS_APPSXTY_V2"."TS_CONFIG_REPORTS_X"   486.4 MB   12283 rows



. . exported "TS_APPSXXT_V2"."TS_EXCEL_TEMPLATES"        0 KB       0 rows
Master table "BACKUP"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BACKUP.SYS_EXPORT_FULL_01 is:
  /orabackup/TESTDB/export/Full_EXPORT_TESTDB_yya_comp_20140305t.dmp
Job "BACKUP"."SYS_EXPORT_FULL_01" successfully completed at 11:29:51

This time file generated is of size 18GB and it took about 2 hours.

-rw-r-----    1 testdb_t oinstall 19814805504 Mar  5 11:29 Full_EXPORT_TESTDB_yya_comp_20140305t.dmp

So, COMPRESSION parameter is no doubt very useful in space constraint situations but it comes at cost of time as well as higher CPU utilization and additional license cost 😦

Recently, i checked and found that Oracle database 12c release 1 is now available for AIXPPC 64 & HP-UX itanium. So, i thought of sharing with all. It has been almost 8 months that 12c is available for Windows, Solaris & Linux. But now, it’s available on remaining platforms too. It’s good news for users like me who are working on AIX 🙂

Oracle12c_Release