Define SecureFile LOBs during import Oracle Database 12C release 1 (12.1)

In Oracle 12C it’s possible to define LOB storage method during import time independent from export settings.

It gives more flexibility during importing data.

impdp .. TRANSFORM=LOB_STORAGE:SECUREFILE|BASICFILE|DEFAULT|NO_CHANGE
  • DEFAULT – no lob storage clause is set for CREATE TABLE
  • NO_CHANGE – use settings from dump file
  • BASICFILE – creates LOBs as basicfile
  • SECUREFILE – creates LOBs as securefile
  • Can be set in PL/SQL using DBMS_DATAPUMP.METADATA_TRANSFORM

Example

Imports data and sets LOBs as SECUREFILE

impdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp
TRANSFORM=LOB_STORAGE:SECUREFILE

Have a fun :)

Tomasz

Compress tables during import Oracle Database 12C release 1 (12.1)

In Oracle 12C it’s possible to specify during import compressions settings for a table independent from export settings.

It gives more flexibility during importing data.

impdp .. 
TRANSFORM=TABLE_COMPRESSION_CLAUSE:NONE|"<compression type>"
  • NONE – compression ignored during import, taken from tablespace settings
  • <compression type> – valid compression clause:
    • NOCOMPRESS : Disables table compression.
    • COMPRESS : Enables basic table compression.
    • ROW STORE COMPRESS: Same as COMPRESS.
    • ROW STORE COMPRESS BASIC : Same as COMPRESS.
    • ROW STORE COMPRESS ADVANCED : Enables advanced compression, also known as OLTP compression.
    • COLUMN STORE COMPRESS FOR {QUERY|ARCHIVE} : Hybrid Columnar Compression (HCC) available in Exadata and ZFS storage appliances.
  • Can be set in PL/SQL using DBMS_DATAPUMP.METADATA_TRANSFORM

Example

Import data with dedicated compression type

impdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp
TRANSFORM=TABLE_COMPRESSION_CLAUSE:"ROW STORE COMPRESS"

Have a fun :)
Tomasz

ENCRYPTION_PWD_PROMPT=Y Oracle Database 12C release 1 (12.1)

There is new option to pass in silent mode encryption password in Oracle 12C during exporting sensitive data. In new version user is asked about password during execution of export.

expdp ... ENCRYPTION_PWD_PROMPT=Y ...
...
Password: <user is asked for encryption password>

In previous version encryption password was explicit passed to expdp using parameter ENCRYPTION_PASSWORD. So could be visible via ps command or in a expdp parameter script.

Features:

  • new parameter ENCRPTION_PWD_PROMPT as default set to N
  • if the parameter is set to Y then user is asked for encryption password during export
  • the password is not visible via commands like PS nor stored in scripts
  • old 11g ENCRYPTION_PASSWORD and ENCRYPTION_PWD_PROMPT=Y can’t be used in the same time and generate an error

Have a fun :)
Tomasz

Exporting views as tables Oracle Database 12C release 1 (12.1)

This feature extends functionality of Data Pump 12C. It allows to export views as tables.

expdp .. VIEWS_AS_TABLES=[schema_name.]view_name ...

Features

  • new parameter for expdp VIEWS_AS_TABLES
  • it exports view as table definition plus data
  • it exports dependent objects like constraints(on the view) and grants(on the view)
  • very useful to export subset of a data from table(s) via complex view
  • during import as first step table is created then data is imported
  • it can be exported in PL/SQL using DBMS_DATAPUMP.METADATA_FILTER
  • doesn’t support views that reference object type or functions
  • unloads data as unencrypted format

Examples

Export of view emp_view”as table and table departments

expdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp
VIEWS_AS_TABLES=emp_view TABLES=departments

Export of two views as tables emp_view and oe.orders_v

expdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp
VIEWS_AS_TABLES=emp_view,oe.orders_v

Import of exported view as table emp_view and rename the table as emp_new_table

impdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp
VIEWS_AS_TABLES=emp_view REMAP_TABLE=emp_view:emp_new_table

Import through data base link db_link via view emp_v and loading it directly to existing table employees

impdp hr/hr VIEW_AS_TABLE=emp_v NETWORK_LINK=db_link
REMAP_TABLE=emp_v:employees TABLE_EXIST_ACTION=append

Have a fun :)

Tomasz

 

 

Disabling LOGGING for import Oracle Database 12C release 1 (12.1)

It’s very nice feature available in Data Pump 12C. It allows to disable generation of redo logs during import of  a data into database.

impdp .. TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y|N[:TABLE|INDEX]

Features

  • DISABLE_ARCHIVE_LOGGING as default is set to N
  • it’s possible to set it only for TABLES or INDEXES or for both
  • if used then once import is finished original logging settings for imported objects are restored
  • redo data is not written into disk
  • faster load data into database less I/O operations
  • great for large data loads or populating new databases
  • It’s recommended to make a full database backup  after such load
  • small amount of log activity is still still generated by the import
  • FORCE LOGGING set on database level will ignore the settings
  • operations against master data pump table(the table stores activity of data pump jobs)  are always using logging option

Examples

Disable logging for whole imported file so for tables and indexes

impdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp SCHEMAS=hr 
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

Disable logging just for indexes

impdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp SCHEMAS=hr 
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX

Disable logging just for indexes – different way

impdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp SCHEMAS=hr 
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
TRANSFORM=DISABLE_ARCHIVE_LOGGING:N:TABLE

Have a fun :)
Tomasz

Grant READ privilege Oracle Database 12C release 1 (12.1)

New privileges READ, READ ANY TABLE have appeared  in Oracle Database 12c.

They are available since release 12.1.0.2.

They work almost the same as standard SELECT and SELECT ANY TABLE except SELECT and SELECT ANY TABLE can do additionally

--acquires exclusive lock on a table
LOCK TABLE <TABLE_NAME> IN EXCLUSIVE MODE;

--acquires row lock on a table rows
SELECT … FROM <TABLE_NAME> FOR UPDATE;

So simple grant SELECT lets users to make harm(can block other users) in a database just by executing above commands. It’s time to start using READ and READ ANY TABLE to avoid it and improve security.

You can grant/revoke them to the same objects like SELECT:

  • tables
  • views
  • materialized views
  • synonyms
GRANT READ ON test_table TO tomasz;
GRANT READ ON test_view TO tomasz;
GRANT READ ANY TABLE TO tomasz;

REVOKE READ ON test_view FROM tomasz;
REVOKE READ ANY TABLE FROM tomasz;

Have a fun :)

Tomasz

SWITCHOVER, FAILOVER physical standby database Oracle Database 12C release 1 (12.1)

This article presents SWITCHOVER and FAILOVER methods available for physical standby database in Oracle Database 12C release 1.

This article is based on following article: Configure physical standby database Oracle Database 12C release 1 (12.1)

Optional step – Turn on flashback logs on standby and primary database

  • Flashback on primary – Useful however not necessary to test FAILOVER. It allows to restore previous primary database to consistent state just before FAILOVER so it can be used as standby again
--primary database
SYS@ORA12C> 
ALTER DATABASE FLASHBACK ON;

SELECT flashback_on
FROM v$database;

FLASHBACK_ON
------------------
YES
  • Flashback on standby – enables to switch standby database to READ WRITE. Standby database still receives changes from primary database but doesn’t apply them as long as standby work in READ WRITE mode. To turn flashback on standby database recover process must be turned off.
--standby database
SYS@SORA12C> 

--cancel recovery mode and turn on flashback log
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE FLASHBACK ON;

SELECT flashback_on
FROM v$database;

FLASHBACK_ON
------------------
YES

--restore recovery mode
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE 
DISCONNECT FROM SESSION;

Continue reading

Configure physical standby database Oracle Database 11G release 2 (11.2)

This article presents how to configure physical standby database for Oracle Database 11G release 2 (11.2). It’s not focusing on transportation method, duplication method, protection modes nor extra functionality available for physical standbys. All above will be part of future articles :).

This presentation is based on the following article Install Oracle 11G Release 2 (11.2) on Oracle Linux 7 (OEL7).

Architecture

Final standby configuration is very simple:

  • one host: oel7.dbaora.com – already configured with latest binaries Oracle Database 11G
  • primary database: ORA11G
  • listener LISTENER – already installed
  • standby database: SORA11G – this article shows how to configure it
  • standby configuration will work in default mode MAXIMUM PERFORMANCE

standby_physical_11g_01

Continue reading

Install Oracle 11G Release 2 (11.2) on Fedora 21

This article presents how to install Oracle 11G on Fedora 21.

Read following article how to install Fedora 21 Linux: Install Fedora 21 (for comfort set 4G memory for your virtual machine before proceeding with Oracle software installation).

Installation software is available on OTN version 11.2.0.1 or metalink 11.2.0.4. In this installation I’m presenting installation for 11.2.0.4 but for previous version 11.2.0.X it shouldn’t be different.

Check article for new release Install Oracle 12C Release 1 (12.1) on Fedora 21

Oracle software that was verified

release 11.2.0.4

p13390677_112040_Linux-x86-64_1of7.zip 
p13390677_112040_Linux-x86-64_2of7.zip

Continue reading