Resize operation completed for file#, old size, new size

Oracle Database 12c logs the extension of autoextensible datafiles to the alert log, for example:

Resize operation completed for file# 6, old size 5120K, new size 6400K
Resize operation completed for file# 6, old size 6400K, new size 7680K
Resize operation completed for file# 6, old size 7680K, new size 8960K
Resize operation completed for file# 6, old size 8960K, new size 10240K
Resize operation completed for file# 6, old size 10240K, new size 11520K
Resize operation completed for file# 6, old size 11520K, new size 12800K
Resize operation completed for file# 6, old size 12800K, new size 14080K
Resize operation completed for file# 6, old size 14080K, new size 15360K
Resize operation completed for file# 6, old size 15360K, new size 16640K
Resize operation completed for file# 6, old size 16640K, new size 17920K

This can be disabled by setting the “_disable_file_resize_logging” parameter:

alter system set "_disable_file_resize_logging" = true;

Outer join Filter Push Down (FPD)

Version is 12.1.0.2. Given the below tables:

drop table t1 purge;
drop table t2 purge;

create table t1 (id1 number, id2 number, x varchar2(100));
create table t2 (id1 number, id2 number, x varchar2(100));

insert into t1 select rownum, rownum, lpad('X', 100) from dual connect by level <= 1e5; 
insert into t2 select rownum, NULL, lpad('X', 100) from dual connect by level <= 1e5;  

commit; 

create index t1_id1 on t1(id1); 
create index t1_id2 on t1(id2); 
create index t2_id1 on t2(id1); 
create index t2_id2 on t2(id2); 

exec dbms_stats.gather_table_stats(user, 'T1', estimate_percent=> NULL, cascade=> true);
exec dbms_stats.gather_table_stats(user, 'T2', estimate_percent=> NULL, cascade=> true);

Read the rest of this entry »

Oracle 12c Flex Cluster Installation in VirtualBox

Installing a flex cluster requires additional configuration, because GNS (Grid Naming Service) is mandatory for this new type of Oracle cluster that was introduced in Oracle Grid Infrastructure 12c. For this specific setup I have used VirtualBox with 5 virtual machines: 4-node cluster and a DHCP/DNS server VM. I don’t want to go into the details of the regular parts of the installation, this post is mainly about the GNS configuration (DHCP and DNS) and the new parts in the installation.

Read the rest of this entry »

Oracle Database 12c New Features – Data Redaction

Introduction

Starting with Oracle Database version 12.1.0.1 (and 11.2.0.4!) the Advanced Security option contains a new feature: Data Redaction. Data Redaction provides in-built support for hiding sensitive data on-the-fly in query results on certain conditions.

Read the rest of this entry »

Enterprise Manager Cloud Control 12.1.0.4 upgrade and downgrade

This post is about upgrading Enterprise Manager Cloud Control from version 12.1.0.3 to 12.1.0.4 with a fallback plan. As of now, there is no supported method for downgrading Enterprise Manager Cloud Control, so actually this is carried out by making the required backups, and reverting to the older state from them.

Preparations

One of the requirements for upgrading is to copy the emkey to the repository database:

$ export OMS_HOME=/oracle/base/product/mw12103/oms
$ $OMS_HOME/bin/emctl config emkey -copy_to_repos_from_file -repos_host emdb.sequoia.vbox -repos_port 1521 -repos_sid emrep -repos_user sysman -emkey_file $OMS_HOME/sysman/config/emkey.ora

Next export the OMS configuration, which should be part of the regular backups also. The file created by this operation can be used to recreate the OMS from a software-only installation in case the binaries are lost.

$ $OMS_HOME/bin/emctl exportconfig oms

Read the rest of this entry »

INDEX SKIP SCAN not considered

This post was inspired by the below question:

http://dba.stackexchange.com/questions/64880/why-does-the-cbo-not-chose-a-lower-cost-skip-scan-without-stats

Short version: INDEX SKIP SCAN is not considered by the optimizer, when optimizer statistics are missing.
Read the rest of this entry »

DBMS_METADATA.GET_DDL database link password missing

Another annoying “feature” that was introduced in 11.2.0.4.

11.2.0.3:

SQL> create database link dblink connect to user1 identified by password1 using 'ORCL';

Database link created.

SQL> select dbms_metadata.get_ddl('DB_LINK', 'DBLINK', user) as ddl from dual;

DDL
----------------------------------------------------------------------------------------------------

  CREATE DATABASE LINK "DBLINK"
   CONNECT TO "USER1" IDENTIFIED BY VALUES '063E28DC19A9C9D6724B8F76A9980676231666CA76CF563B1B91ABCC
DE3CA1352BF3BA1D50EF14843F59ED6289B6709A0360C3B4DBA099A09A88C4F40E317362DBF0171A43E87AF4F5E2E03BED6F
F7CB1DB100DDC531257D0CF9B11EB2E1773E53A82812F39BDEA33AA3BC837C367FFA5A3CBBD4B48B9B33CAC55F3306CA4A2B
'
   USING 'ORCL'

Now, do the same on 11.2.0.4:

SQL> create database link dblink connect to user1 identified by password1 using 'ORCL';

Database link created.

SQL> select dbms_metadata.get_ddl('DB_LINK', 'DBLINK', user) as ddl from dual;

DDL
----------------------------------------------------------------------------------------------------

  CREATE DATABASE LINK "DBLINK"
   CONNECT TO "USER1" IDENTIFIED BY VALUES ':1'
   USING 'ORCL'

IDENTIFIED BY VALUES ‘:1′, not what I wanted. Let’s check what happens at a Data Pump export/import:

$ expdp test/test directory=data_pump_dir include=DB_LINK
$ impdp test/test directory=data_pump_dir dumpfile=expdat.dmp sqlfile=dblink.sql
$ cat dblink.sql
-- CONNECT TEST
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/DB_LINK
CREATE DATABASE LINK "DBLINK"
   CONNECT TO "USER1" IDENTIFIED BY VALUES ':1'
   USING 'ORCL';

Even in the SQL file. Now try to really import this time:

SQL> drop database link dblink;

$ impdp test/test directory=data_pump_dir dumpfile=expdat.dmp

Import: Release 11.2.0.4.0 - Production on Sat Apr 12 15:18:29 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01":  test/******** directory=data_pump_dir dumpfile=expdat.dmp
Processing object type SCHEMA_EXPORT/DB_LINK
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 12 15:18:30 2014 elapsed 0 00:00:00

No errors.

$ sqlplus test/test
...
SQL> select dbms_metadata.get_ddl('DB_LINK', 'DBLINK', user) as ddl from dual;

DDL
----------------------------------------------------------------------------------------------------

  CREATE DATABASE LINK "DBLINK"
   CONNECT TO "USER1" IDENTIFIED BY VALUES ':1'
   USING 'ORCL'

As per Oracle Support, this is not a bug, but a feature.

Bug 18461318 : DBMS_METADATA.GET_DDL FOR DBLINK ALWAYS RETURNS IDENTIFIED BY VALUES ':1'
Status: 44 - Not Feasible to fix, to Filer

Conclusion: use Data Pump as above.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: