Oracle 12c New Feature Datapump enhancements

Data Pump Enhancements in Oracle Database 12c Release 1 (expdp, impdp)

At this article I’ll present one of new stuff comed with Oracle Database 12c DataPump.

New parameters of DataPump:

• LOGGING
• DISABLE_ARCHIVE_LOGGING (part of the TRANSFORM parameter)
• ENCRYPTION_PWD_PROMPT
• COMPRESSION_ALGORITHM

NOLOGGING Option (DISABLE_ARCHIVE_LOGGING)

The TRANSFORM parameter of impdp has been extended to include a DISABLE_ARCHIVE_LOGGING option

The default setting of "N" has no affect on logging behaviour.

transform=disable_archive_logging:Y

For example:

impdp veysi/veysi@dba directory=exam_dir dumpfile=ocp.dmp logfile=impdp_ocp.log remap_schema=test:user transform=disable_archive_logging:y

 You can also disable the logging for specific object type :

transform=disable_archive_logging:y:index

transform=disable_archive_logging:y:table

Exporting Views as Tables

Oracle 12c introduces the possibility to export VIEWS as TABLES using the expdp parameter VIEWS_AS_TABLES:

VIEWS_AS_TABLES=[schema_name.]view_name[:table_name], ...

For example

impdp veysi/veysi@dba directory=exam_dir dumpfile=ocp.dmp logfile=impdp_ocp.log   views_as_tables=view1   remap_table:view1:table1

 

        encryption_pwd_prompt=yes

Transportable Database

Now You can to transport an whole database

expdp veysi/veysi@dba system/Password1 full=Y transportable=always version=12 directory=exam_dir dumpfile=ocp.dmp logfile=expdp_ocp.log

LOG TIME 

With Oracle Database 12c, some of these questions can be addressed by a new parameter introduced in Oracle Data Pump – LOGGING. This command-line parameter can have four values:

NONE: No timestamp information is displayed. (This is the default.)

STATUS: Timestamp messages on status are displayed.

LOGFILE: Same as STATUS, but only displayed for logfile messages.

ALL: A combination of STATUS and LOGFILE.

LOGTIME=[NONE | STATUS | LOGFILE | ALL]

For example

impdp veysi/veysi@dba directory=exam_dir dumpfile=ocp.dmp logfile=impdp_ocp.log logtime=all

Compress/Uncompressed Data During Import

COMPRESSION options is

COMPRESSION_ALGORITHM=[BASIC | LOW | MEDIUM | HIGH]

• BASIC offers the most efficient usage of CPU and effective compression ratio; it is deemed to be applicable to most sessions.
• LOW favors size in comparison to CPU and yields a larger file size with a lower compression ratio.
• MEDIUM is similar to BASIC. It uses a different algorithm as it performs the work at hand.
• HIGH is a good choice when the size of the export file is the determining factor; on the source site, it yields the smallest file, but it could be the most CPU-intensive.

For example

impdp veysi/veysi@dba directory=exam_dir dumpfile=ocp.dmp logfile=impdp_ocp.log 

     remap_schema=test:user transform=table_compression_clause:compress

expdp veysi/veysi@dba directory=exam_dir dumpfile=ocp.dmp logfile=expdp_ocp.log

        compression=all compression_algorithm=low

Encryption Password Enhancements

ENCRYPTION_PWD_PROMPT parameter enables encryption without requiring the password to be entered

ENCRYPTION_PWD_PROMPT=[YES | NO]

For example

expdp veysi/veysi@dba directory=exam_dir dumpfile=ocp.dmp logfile=expdp_ocp.log

Hope its helps...

Oracle Dataguard Disable/Enable by DataGuard Broker

At this basic article I'll sampling to Disable and Enable Dataguard.

[oracle@veysi ]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/*******
Connected.

DGMGRL> show configuration

Configuration - veysi_config

  Protection Mode: MaxPerformance
  Databases:
    istanbul- Primary database
    Germany - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


Disable Transport LOG and Apply:


DGMGRL> edit database istanbul set state='LOG-TRANSPORT-OFF';

SUCCESS

DGMGRL> edit database Germany set state='LOG-APPLY-OFF';
SUCCESS

Enable Transport LOG and Apply:


DGMGRL> edit database istanbul set state='ONLINE';
SUCCESS

DGMGRL> edit database Germany set state='ONLINE';
SUCCESS

Hope,its helps..

Invisible Columns in Oracle Database 12c

Oracle database 12C Invisible Column

At this article I’ll try to explain one of new features of oracle 12c  invisible column.

This property means no longer visible by Select * or SQL*PLUS..

External, cluster and temporary tables can not have invisible columns.

Virtual columns can be made invisible.

A table can be partitioned by an invisible column, either during or after table creation.

User-defined types can not contain invisible attributes.

You can not make system generated hidden columns visible.

The following example creates a table with an invisible column:

CREATE TABLE  TEST (id number(5), name varchar2(20), salary number(9) INVISIBLE); Table created

DESCRIBE TEST; Name      Null?  Type ----------------------------------------- -------- ---------------------- id               NUMBER(5) name           VARCHAR2(40) salary           NUMBER(9) INVISIBLE

Lets data insert To INVISIBLE COLUMN, insert into TEST (id,name,salary) values (1,'Veysi GÜNAY',500); 1 row created. SELECT * FROM TEST; ID               NAME ----------  ------------------------------      1        Veysi GÜNAY

TO MAKE COLUMN VISIBLE

ALTER TABLE Test MODIFY(salary VISIBLE);     insert into TEST (id,name,salary) values (2,'John SMIT',1100); 1 row created. SELECT * FROM TEST; ID               NAME           SALARY ----------  ----------------  --------------      1        Veysi GÜNAY         500      2       John  SMIT         1100

TO MAKE COLUMN INVISIBLE AGAIN

ALTER TABLE Test MODIFY(salary INVISIBLE);

To show Invısıble Column

*SET COLINVISIBLE ON

* SELECT TABLE_NAME,  COLUMN_NAME, HIDDEN_COLUMN          FROM USER_TAB_COLS WHERE HIDDEN_COLUMN='YES';

Also you can create a view for invisible column.

Hope you doing good…