Category Archives: Database 12c

Oracle Database 12.2.0.1 – Application PDB unable to sync

Recently, Tim Hall posted an excellent article about Application containers, a new feature in Oracle Database 12c Release 2 (12.2.0.1). With application containers, it is now possible to create a single master application definition in the application root of the application container. The common objects defined in the master application definition are visible to all application PDBs within the application container when the application PDBs have been synchronized with the application root.

Tim has demonstrated the concept of applilcation containers using Oracle Managed Files (OMF) . I tried out the same example without OMF and discovered that without OMF it works only if the application doesn’t use any new tablespaces/datafiles. If the application creates new datafiles, application PDB’s always fails to sync with the  application.

Here is my example:

– Create a new application root container appcon1 (non-OMF – Using FILE_NAME_CONVERT)

CDB$ROOT>conn / as sysdba

         CREATE PLUGGABLE DATABASE appcon1 AS APPLICATION CONTAINER
         ADMIN USER app_admin IDENTIFIED BY Password1
         FILE_NAME_CONVERT = ('/pdbseed/', '/appcon1/');

– Connect to the application root container and create an application PDB apppdb1 from CDB seed

CDB$ROOT>ALTER PLUGGABLE DATABASE appcon1 OPEN;
         ALTER SESSION SET container = appcon1;

APPCON1>CREATE PLUGGABLE DATABASE apppdb1
        ADMIN USER pdb_admin IDENTIFIED BY Password1
        FILE_NAME_CONVERT = ('/pdbseed/', '/appcon1/apppdb1/');

– Open the application PDB apppdb1

APPCON1>ALTER PLUGGABLE DATABASE apppdb1 open;
        SHOW PDBS

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 APPCON1 READ WRITE NO
5 APPPDB1 READ WRITE NO

-- Connected to the application root container, create a simple application ref_app

APPCON1>ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN INSTALL '1.0';

– Create application components

. Create a tablespace ref_app_ts

APPCON1>CREATE TABLESPACE ref_app_ts
        DATAFILE '/u01/app/oracle/oradata/orclcdb/appcon1/app_tbs1.dbf'
        SIZE 1M AUTOEXTEND ON NEXT 1M;

.  Create a common application user ref_app_user with default tablespace ref_app_ts created above

APPCON1>CREATE USER ref_app_user IDENTIFIED BY ref_app_user
        default tablespace ref_app_ts
        quota unlimited on ref_app_ts
        CONTAINER=ALL;

– Grant appropriate privileges to common appication user ref_app_user

APPCON1>GRANT CREATE SESSION, CREATE TABLE
-- , unlimited tablespace
TO ref_app_user;

– Create and populate a table reference_data owned by ref_app_user whose data will be shared across all the application PDB’s synced with the application ref_app

APPCON1>CREATE TABLE ref_app_user.reference_data SHARING=DATA (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT t1_pk PRIMARY KEY (id)
);

APPCON1>INSERT INTO ref_app_user.reference_data
SELECT level, 'Description of ' || level
FROM dual
CONNECT by level <= 5;
COMMIT;

– After all the application components have been defined, end the installation process.

APPCON1>ALTER PLUGGABLE DATABASE APPLICATION ref_app END INSTALL '1.0';

-- Verify that application ref_app version 1.0 has been created in application root appcon1 as listed in the DBA_APPLICATIONS view.

APPCON1>COLUMN app_name FORMAT A20
COLUMN app_version FORMAT A10

SELECT app_name,
app_version,
app_status
FROM dba_applications
WHERE app_name = 'REF_APP';

APP_NAME APP_VERSIO APP_STATUS
-------------------- ---------- ---------------
REF_APP 1.0 NORMAL

– Connect to application PDB apppdb1 and verify it cannot access common application table ref_app_user.reference_data as it has not been synced with the application ref_app yet

APPCON1>CONN / AS SYSDBA

ALTER SESSION SET container = apppdb1;
APPPDB1>DESC ref_app_user.reference_data;

ERROR:
ORA-04043: object ref_app_user.reference_data does not exist

– When an attempt is made to sync the application PDB apppdb1 with the application ref_app, ORA-01537 is encountered.

APPPDB1>ALTER PLUGGABLE DATABASE APPLICATION ref_app SYNC;

ALTER PLUGGABLE DATABASE APPLICATION ref_app SYNC
*
ERROR at line 1:
ORA-01537: cannot add file
'/u01/app/oracle/oradata/orclcdb/appcon1/app_tbs1.dbf' - file already part of database

However, above command succeeds if tablespace ref_app_ts not created and ref_app_user give unlimited tablespace privilege.

Please go through following update on above issue.

References:
Multitenant: Application Containers in Oracle Database 12c Release 2

————————————————————————————————————————-

Related Links:

Home

Oracle 12c Index

Oracle Database 12.2.0.1 – Application PDB unable to sync – An update

12c: Enhancements to Partition Exchange Load

Statistics for Partitioned Tables
Gathering statistics on partitioned tables consists of gathering statistics at both the table level and partition level. Prior to Oracle Database 11g, whenever a new partition was added, the entire table had to be scanned to refresh table-level statistics which could be very expensive, depending on the size of the table.

Incremental Global Statistics
With the introduction of incremental global statistics in 11g, the database, instead of performing a full table scan to compute global statistics, can derive global statistics from the partition level statistics. Some of the statistics, for example the number of rows, can be accurately derived by aggregating the values from partition statistics . However, the NDV of a column cannot be derived by aggregating partition-level NDVs. Hence, a structure called synopsis is maintained by the database for each column at the partition level which can be viewed as a sample of distinct values. The synopses for various partitions are merged by the database to accurately derive the NDV for each column.

Hence, when a new partition is added to a table, the database

  • gathers statistics and creates synopses for the newly added partition,
  • retrieves synopses for the existing partitions of the table and
  • aggregates the partition-level statistics and synopses to create global statistics.

Thus, the need to scan the entire table to gather table level statistics on adding a new partition has been eliminated.

However, if partition exchange loads are performed and statistics for source table are available, statistics still need to be gathered for the partition after the exchange to obtain its synopsis.

Enhancements in Oracle 12c
Oracle Database 12c introduces new enhancements for maintaining incremental statistics. Now, DBMS_STATS can create a synopsis on a non-partitioned table as well. As a result, if you are using partition exchange loads, the statistics / synopsis for the source table will become the partition level statistics / synopsis after the load, so that the database can maintain incremental statistics without having to explicitly gather statistics on the partition after the exchange.

Let’s demonstrate …

Overview:

Source non-partitioned table : HR.SRC_TAB
Destination partitioned table: HR.PART_TAB
Destination partition                  : PMAR

— Create a partitioned table HR.PART_TAB with 3 partitions

  • only 2 partitions contain data initially
  • set preference incremental = true
  • gather stats for the table – gathers statistics and synopses for 2 partitions

— create a non partitioned table HR.SRC_TAB which will used to load the 3rd partition using partition exchange

  •  Set table preferences for HR.SRC_TAB
    • INCREMENTAL = TRUE
    • INCREMENTAL_LEVEL = TABL
  • Gather stats for the source table: DBMS_STATS gathers table-level synopses also for the table

— Perform the partition exchange
— After the exchange, the the new partition has both statistics and a synopsis.
— Gather statitstics for PART_TAB – Employs partition level statistics and synopses to derive global statistics.

Implementation

– Create and populate partitioned table part_tab with 3 partitions
PJAN, PFEB and PMAR

SQL>conn hr/hr

drop table part_tab purge;
create table part_tab
(MNTH char(3),
ID number,
txt char(10))
partition by list (mnth)
(partition PJAN values ('JAN'),
partition PFEB values ('FEB'),
partition PMAR values ('MAR'));

insert into part_tab values ('JAN', 1, 'JAN1');
insert into part_tab values ('JAN', 2, 'JAN2');
insert into part_tab values ('JAN', 3, 'JAN3');

insert into part_tab values ('FEB', 2, 'FEB2');
insert into part_tab values ('FEB', 3, 'FEB3');
insert into part_tab values ('FEB', 4, 'FEB4');
commit;

– Note that

  •   partition PMAR does not have any data
  •  there are 4 distinct values in column ID i.e. 1,2,3 and 4
select 'PJAN' Partition, mnth, id from part_tab partition (PJAN)
union
select 'PFEB' Partition, mnth, id from part_tab partition (PFEB)
union
select 'PMAR' Partition, mnth, id from part_tab partition (PMAR)
order by 1 desc;

PART MNT ID
---- --- ----------
PJAN JAN 1
PJAN JAN 2
PJAN JAN 3
PFEB FEB 2
PFEB FEB 3
PFEB FEB 4

– Set preference Incremental to true for the table part_tab

SQL>begin
dbms_stats.set_table_prefs ('HR','PART_TAB','INCREMENTAL','TRUE');
end;
/

select dbms_stats.get_prefs ('INCREMENTAL','HR','PART_TAB') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL','HR','PART_TAB')
----------------------------------------------------
TRUE

-- Gather statistcs for part_tab

SQL> exec dbms_stats.gather_table_stats('HR','PART_TAB');

– Note that global statistics have been gathered and the table has been analyzed at 16:02:31

SQL>alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

col table_name for a12
select table_name, num_rows, last_analyzed from user_tables
where table_name='PART_TAB';

TABLE_NAME NUM_ROWS LAST_ANALYZED
------------ ---------- --------------------
PART_TAB 6 17-nov-2014 16:02:31

– A full table scan was performed and stats were gathered for each of the partitions
All the partitions have been analyzed at the same time as table i.e. at 16:02:31

SQL> col partition_name for a15

select partition_name, num_rows,last_analyzed
from user_tab_partitions
where table_name = 'PART_TAB' order by partition_position;

PARTITION_NAME NUM_ROWS LAST_ANALYZED
--------------- ---------- --------------------
PJAN 3 17-nov-2014 16:02:31
PFEB 3 17-nov-2014 16:02:31
PMAR 0 17-nov-2014 16:02:31

– NUM_DISTINCT correctly reflects that there are 4 distinct values in column ID

SQL> col column_name for a15
select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT
from user_tab_col_statistics
where table_name = 'PART_TAB' and column_name = 'ID';

TABLE_NAME COLUMN_NAME NUM_DISTINCT
------------ --------------- ------------
PART_TAB ID 4

– Create source unpartitioned table SRC_TAB
– Populate SRC_TAB with records for mnth = MAR
and introduce two new values for column ID i.e. 0 and 5

SQL>drop table src_tab purge;
create table src_tab
(MNTH char(3),
ID number,
txt char(10));

insert into src_tab values ('MAR', 0, 'MAR0');
insert into src_tab values ('MAR', 2, 'MAR2');
insert into src_tab values ('MAR', 3, 'MAR3');
insert into src_tab values ('MAR', 5, 'MAR5');
commit;

– Set preferences for table src_tab

  • INCREMENTAL = TRUE
  • INCREMENTAL_LEVEL = TABLE
SQL>begin
dbms_stats.set_table_prefs ('HR','SRC_TAB','INCREMENTAL','TRUE');
dbms_stats.set_table_prefs ('HR','SRC_TAB','INCREMENTAL_LEVEL','TABLE');

end;
/

col incremental for a15
col incremental_level for a30

select dbms_stats.get_prefs ('INCREMENTAL','HR','SRC_TAB') incremental,
dbms_stats.get_prefs ('INCREMENTAL_LEVEL','HR','SRC_TAB') incremental_level
from dual;

INCREMENTAL INCREMENTAL_LEVEL
--------------- ------------------------------
TRUE TABLE

– Gather stats and synopsis for table SRC_TAB and note that table is analyzed at 16:06:03

SQL>exec dbms_stats.gather_table_stats('HR','SRC_TAB');

col table_name for a12
select table_name,num_rows, last_analyzed from user_tables
where table_name='SRC_TAB';

TABLE_NAME NUM_ROWS LAST_ANALYZED
------------ ---------- --------------------
SRC_TAB 4 17-nov-2014 16:06:33

– Exchange partition –

SQL>alter table part_tab exchange partition PMAR with table SRC_TAB;

– Note that table level stats for part_tab are still as earlier
as stats have not been gathered for it after partition exchange

SQL> col table_name for a12
select table_name, num_rows, last_analyzed from user_tables
where table_name='PART_TAB';

TABLE_NAME NUM_ROWS LAST_ANALYZED
------------ ---------- --------------------
PART_TAB 6 17-nov-2014 16:02:31

– NDV for col ID is still same as earlier i.e. 4 as stats
have not been gathered for table after partition exchange

SQL> col column_name for a15
select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT
from user_tab_col_statistics
where table_name = 'PART_TAB' and column_name = 'ID';

TABLE_NAME COLUMN_NAME NUM_DISTINCT
------------ --------------- ------------
PART_TAB ID 4

– Note that stats for partition PMAR have been copied from
src_tab. Last_analyzed column for Pmar has been updated
and shows same value as for table src_tab i.e. 16:06:33
Also, num_rows are shown as 4

SQL> col partition_name for a15

select partition_name, num_rows,last_analyzed
from user_tab_partitions
where table_name = 'PART_TAB' order by partition_position;

PARTITION_NAME NUM_ROWS LAST_ANALYZED
--------------- ---------- --------------------
PJAN 3 17-nov-2014 16:02:31
PFEB 3 17-nov-2014 16:02:31
PMAR 4 17-nov-2014 16:06:33

– Gather stats for table part_tab

SQL>exec dbms_stats.gather_table_stats('HR','PART_TAB');

– While gathering stats for the table, partitions have not been
scanned as indicated by the same value as earlier in column LAST_ANALYZED.

SQL> col partition_name for a15

select partition_name, num_rows,last_analyzed
from user_tab_partitions
where table_name = 'PART_TAB' order by partition_position;

PARTITION_NAME NUM_ROWS LAST_ANALYZED
--------------- ---------- --------------------
PJAN 3 17-nov-2014 16:02:31
PFEB 3 17-nov-2014 16:02:31
PMAR 4 17-nov-2014 16:06:33

– Note that num_rows for the table part_tab has been updated by adding up the values from various partitions using partition level statistics
Column LAST_ANALYZED has been updated for the table

SQL> col table_name for a12
select table_name, num_rows, last_analyzed from user_tables
where table_name='PART_TAB';

TABLE_NAME NUM_ROWS LAST_ANALYZED
------------ ---------- --------------------
PART_TAB 10 17-nov-2014 16:11:26

– NDV for column ID has been updated to 6 using the synopsis for partition PMAR as copied from table src_tab

SQL> col column_name for a15
select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT
from user_tab_col_statistics
where table_name = 'PART_TAB' and column_name = 'ID';

TABLE_NAME COLUMN_NAME NUM_DISTINCT
------------ --------------- ------------
PART_TAB ID 6

– We can also confirm that we really did use incremental statistics by querying the dictionary table sys.HIST_HEAD$, which should have an entry for each column in the PART_TAB table.

SQL>conn / as sysdba
col tabname for a15
col colname for a15
col incremental for a15

select o.name Tabname , c.name colname,
decode (bitand (h.spare2, 8), 8, 'yes','no') incremental
from sys.hist_head$ h, sys.obj$ o, sys.col$ c
where h.obj# = o.obj#
and o.obj# = c.obj#
and h.intcol# = c.intcol#
and o.name = 'PART_TAB'
and o.subname is null;

TABNAME COLNAME INCREMENTAL
--------------- --------------- ---------------
PART_TAB MNTH yes
PART_TAB ID yes
PART_TAB TXT yes

I hope this post was useful.

Your comments and suggestions are always welcome.

References:

http://oracle-randolf.blogspot.in/2012/01/incremental-partition-statistics-review.html
https://docs.oracle.com/database/121/TGSQL/tgsql_stats.htm#TGSQL413
https://blogs.oracle.com/optimizer/entry/incremental_statistics_maintenance_what_statistics
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-statistics-concepts-12c-1963871.pdf
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf
https://blogs.oracle.com/optimizer/entry/maintaining_statistics_on_large_partitioned_tables
———————————————————————————-

Related Links:

Home

Database 12c Index

===================================================================

 

12c: Access Objects Of A Common User Non-existent In Root

In a multitenant environment, a common user is a database user whose identity and password are known in the root and in every existing and future pluggable database (PDB). Common users can connect to the root and perform administrative tasks specific to the root or PDBs. There are two types of common users :

  • All Oracle-supplied administrative user accounts, such as SYS and SYSTEM
  •  User created common users- Their names  must start with C## or c##.

When a PDB having a user created common user is plugged into another CDB and the target CDB does not have  a common user with the same name, the common user in a newly plugged in PDB becomes a locked account.
To access such common user’s objects, you can do one of the following:

  • Leave the user account locked and use the objects of its schema.
  • Create a common user with the same name as the locked account.

Let’s demonstrate …

Current scenario:

Source CDB : CDB1
– one PDB (PDB1)
– Two common users C##NXISTS and C##EXISTS

Destination CDB : CDB2
– No PDB
– One common user C##EXISTS

Overview:
– As user C##NXISTS, create and populate a table in PDB1@CDB1
– Unplug PDB1 from CDB1 and plug into CDB2 as PDB1_COPY
– Open PDB1_COPY and Verify that

  •  user C##NXISTS has not been created in root
  • users C##NXISTS and C##EXISTS both have been created in PDB1_COPY. Account of C##EXISTS is open whereas account of C##NXISTS is closed.

– Unlock user C##NXISTS account in PDB1_COPY.
– Try to connect to pdb1_copy as C##NXISTS  – fails with internal error.
– Create a local user  LUSER in PDB1_COPY with privileges on C##NXISTS’  table and verify that LUSER can access C##NXISTS’ table.
– Create user C##NXISTS in root with PDB1_COPY closed. Account of
C##NXISTS is automatically opened on opening PDB1_COPY.
– Try to connect as C##NXISTS to pdb1_copy – succeeds

Implementation:

– Setup –

CDB1>sho con_name

CON_NAME
------------------------------
CDB$ROOT

CDB1>sho pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED                       READ ONLY  NO
3 PDB1                           READ WRITE NO

CDB1>select username, common from cdb_users where username like 'C##%';

no rows selected

- Create 2 common users in CDB1
    – C##NXISTS
    – C##EXISTS

CDB1>create user C##EXISTS identified by oracle container=all;
     create user C##NXISTS identified by oracle container=all;

     col username for a30
     col common for a10
     select username, common from cdb_users where   username like 'C##%';

USERNAME                       COMMON
------------------------------ ----------
C##NXISTS                      YES
C##EXISTS                      YES
C##NXISTS                      YES
C##EXISTS                      YES

- Create user C##EXISTS  in CDB2

CDB2>sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ -----------
db_name                        string      cdb2

CDB2>sho pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED                       READ ONLY  NO

CDB2>create user C##EXISTS identified by oracle container=all;
     col username for a30
     col common for a10

     select username, common from cdb_users where username like 'C##%';

USERNAME                       COMMON
------------------------------ ----------
C##EXISTS                      YES

- As user C##NXISTS, create and populate a table in PDB1@CDB1

CDB1>alter session set container=pdb1;
     alter user C##NXISTS quota unlimited on users;
     create table C##NXISTS.test(x number);
     insert into C##NXISTS.test values (1);
     commit;

- Unplug PDB1 from CDB1

CDB1>alter session set container=cdb$root;
     alter pluggable database pdb1 close immediate;
     alter pluggable database pdb1 unplug into '/home/oracle/pdb1.xml';

CDB1>select name from v$datafile where con_id = 3;

NAME
-----------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/SAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/example01.dbf

- Plug in PDB1 into CDB2 as PDB1_COPY

CDB2>create pluggable database pdb1_copy using '/home/oracle/pdb1.xml'      file_name_convert =
('/u01/app/oracle/oradata/cdb1/pdb1','/u01/app/oracle/oradata/cdb2/pdb1_copy');

sho pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED                       READ ONLY  NO
3 PDB1_COPY                      MOUNTED

– Verify that C##NXISTS user is not visible as PDB1_COPY is closed

CDB2>col username for a30
col common for a10
select username, common from cdb_users where username like 'C##%';

USERNAME                       COMMON
------------------------------ ----------
C##EXISTS                      YES

- Open PDB1_COPY and Verify that
  . users C##NXISTS and C##EXISTS both have been created in PDB.
  . Account of C##EXISTS is open whereas account of C##NXISTS is  locked.

CDB2>alter pluggable database pdb1_copy open;
col account_status for a20
select con_id, username, common, account_status from cdb_users  where username like 'C##%' order by con_id, username;

CON_ID USERNAME                       COMMON     ACCOUNT_STATUS
---------- ------------------------------      ----------      --------------------------
1 C##EXISTS                      YES        OPEN
3 C##EXISTS                      YES        OPEN
3 C##NXISTS                      YES        LOCKED

– Unlock user C##NXISTS account on PDB1_COPY

CDB2>alter session set container = pdb1_copy;
     alter user C##NXISTS account unlock;
     col account_status for a20
     select con_id, username, common, account_status from cdb_users   where username like 'C##%' order by con_id, username;

CON_ID USERNAME                       COMMON     ACCOUNT_STATUS
---------- ------------------------------     -------------  ---------------------------
 3 C##EXISTS                      YES        OPEN
 3 C##NXISTS                      YES        OPEN

– Try to connect as C##NXISTS to pdb1_copy – fails with internal error

CDB2>conn c##nxists/oracle@localhost:1522/pdb1_copy
ERROR:
ORA-00600: internal error code, arguments: [kziaVrfyAcctStatinRootCbk: 

!user],
[C##NXISTS], [], [], [], [], [], [], [], [], [], []

- Since user C##NXISTS cannot connect pdb1_copy, we can lock the account again  

CDB2>conn sys/oracle@localhost:1522/pdb1_copy as sysdba
     alter user C##NXISTS account lock;

     col account_status for a20
     select username, common, account_status from dba_users     where username like 'C##%' order by username;

USERNAME                       COMMON     ACCOUNT_STATUS
------------------------------ ---------- --------------------
C##EXISTS                      YES        OPEN
C##NXISTS                      YES        LOCKED

– Now if C##NXISTS tries to log in to PDB1_COPY, ORA-28000 is returned    instead of internal error

CDB2>conn c##nxists/oracle@localhost:1522/pdb1_copy
ERROR:
ORA-28000: the account is locked

How to access C##NXISTS objects?

SOLUTION – I

- Create a local user in PDB1_COPY with appropriate object privileges on C##NXISTS’ table

CDB2>conn sys/oracle@localhost:1522/pdb1_copy  as sysdba

     create user luser identified by oracle;
     grant select on c##nxists.test to luser;
     grant create session to luser;

–Check that local user can access common user C##NXISTS tables

CDB2>conn luser/oracle@localhost:1522/pdb1_copy;
     select * from c##nxists.test;
X
----------
1

SOLUTION – II :  Create the common user C##NXISTS in CDB2

- Check that C##NXISTS has not been created in CDB$root

CDB2>conn sys/oracle@cdb2 as sysdba
     col account_status for a20
     select con_id, username, common, account_status from cdb_users    where username like 'C##%' order by con_id, username;

CON_ID USERNAME                       COMMON     ACCOUNT_STATUS
---------- ------------------------------   -------------     -------------------------
1 C##EXISTS                      YES        OPEN
3 C##EXISTS                      YES        OPEN
3 C##NXISTS                      YES        LOCKED

- Try to create user C##NXISTS with PDB1_COPY open – fails

CDB2>create user c##NXISTS identified by oracle;
create user c##NXISTS identified by oracle
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in pluggable database PDB1_COPY
ORA-01920: user name 'C##NXISTS' conflicts with another user or role  name

- Close PDB1_COPY and Create user C##NXISTS in root and verify that his account is automatically unlocked on opening PDB1_COPY

CDB2>alter pluggable database pdb1_copy close;
     create user c##NXISTS identified by oracle;
     alter pluggable database pdb1_copy open;

     col account_status for a20
     select con_id, username, common, account_status from cdb_users   where username like 'C##%' order by con_id, username;

CON_ID USERNAME                       COMMON     ACCOUNT_STATUS
----------   ------------------------------ ----------      --------------------
1 C##EXISTS                      YES        OPEN
1 C##NXISTS                      YES        OPEN
3 C##EXISTS                      YES        OPEN
3 C##NXISTS                      YES        OPEN

– Connect to PDB1_COPY as C##NXISTS after granting appropriate privilege – Succeeds

CDB2>conn c##nxists/oracle@localhost:1522/pdb1_copy
ERROR:
ORA-01045: user C##NXISTS lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.

CDB2>conn sys/oracle@localhost:1522/pdb1_copy as sysdba
     grant create session to c##nxists;
     conn c##nxists/oracle@localhost:1522/pdb1_copy

CDB2>sho con_name

CON_NAME
------------------------------
PDB1_COPY

CDB2>sho user

USER is "C##NXISTS"

CDB2>select * from test;

X
----------
1

References:
http://docs.oracle.com/database/121/DBSEG/users.htm#DBSEG573
———————————————————————————————

Related Links:

Home

Oracle 12c Index

 

—————-

12c: Does PDB Have An SPfile?

In a multi-tenant container database, since there are many PDB’s per CDB, it is possible for set some parameters for each individual PDB. The SPFILE for CDB stores parameter values associated with the root which apply to the root, and serve as default values for all other containers. Different values can be set in PDBs for those parameters where the column ISPDB_MODIFIABLE in V$PARAMETER is TRUE. The parameters are set for a PDB and  are stored in table PDB_SPFILE$ remembered across PDB close/open and across restart of the CDB.

– Currently  I have a CDB called CDB1 having one PDB – PDB1.

SQL> sho con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> sho pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED                       READ ONLY  NO
3 PDB1                           READ WRITE NO

– There is a table pdb_spfile for each of the containers (cdb$root and PDB1)

SQL>  select con_id,  table_name from cdb_tables  where table_name = 'PDB_SPFILE$';

CON_ID TABLE_NAME
---------- --------------------
3 PDB_SPFILE$
1 PDB_SPFILE$

– pdb_spfile contains only those parameters which have been specifically   set for a container hence currently there are  no records

SQL>   col container_name for a10
col parameter for a20
col value$ for a30

select container.name container_name, par.name PARAMETER,
par.value$
from pdb_spfile$ par, v$containers container
where par.pdb_uid = container.con_uid
and par.name = 'cursor_sharing';

no rows selected

– Let’s explicitly set cursor_sharing = ‘similar’ in root and check if   it is reflected in pdb_spfile$

SQL> alter system set cursor_sharing='similar';

col container_name for a10
col parameter for a20
col value$ for a30
select container.name container_name, par.name PARAMETER,
par.value$
from pdb_spfile$ par, v$containers container
where par.pdb_uid = container.con_uid
and par.name = 'cursor_sharing';

no rows selected

– It does not show any results but v$spparameter has been updated    probably implying that spfile for the root is maintained in the    operating system only and pdb_spfile does not contain info about parameters in cdb$root.

SQL> select name, value from v$spparameter where name='cursor_sharing';

NAME                           VALUE
------------------------------ -------
cursor_sharing                 similar

-- v$parameter shows the value of parameter for root

SQL> col name for a30
col value for a30

select con_id, name, value from v$parameter
where name = 'cursor_sharing';


CON_ID NAME                           VALUE
---------- ------------------------------ --------
1 cursor_sharing                 similar

– To see parameters for the CDB and all the PDB’s (except PDB$SEED),  v$system_parameter can be accessed. It can be seen that currently it shows only the value for the CDB which will be inherited by all the PDB’s.

SQL>select con_id, name, value from v$system_parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ ---------
0 cursor_sharing                 similar

– Change container to PDB1 and verify that PDB has inherited the value from CDB

SQL> alter session set container=pdb1;

sho parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ -------     ---------------
cursor_sharing                       string      similar

– Since parameter has not been explicitly specified    in PDB ,  v$spparameter shows record  for con_id = 0 and null in value column

SQL> select con_id, name, value from v$spparameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ ----------
0 cursor_sharing

– Let’s check if the parameter can be modified for the PDB

SQL> col ispdb_modifiable for a17
select con_id, name, value, ispdb_modifiable

from v$parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE      ISPDB_MODIFIABLE
---------- ------------------------------ ---------- -----------------
3 cursor_sharing                 similar    TRUE

– Since the parameter can be modified in PDB, let us modify its value in PDB to ‘FORCE’

SQL> alter system set cursor_sharing = 'FORCE';

sho parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
cursor_sharing                       string      FORCE

SQL> select con_id, name, value from v$parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ ----------
3 cursor_sharing                 FORCE

– v$spparameter shows updated value but con_id is still 0 (bug??)

SQL> select con_id, name, value from v$spparameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ ----------
 0 cursor_sharing                 FORCE

– Current value of the parameter  for PDB can be viewed from root using v$system_parameter

SQL> alter session set container=cdb$root;
select con_id, name, value from v$system_parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ -------
0 cursor_sharing                 similar
3 cursor_sharing                 FORCE

– Current value in spfile of PDB can be seen from pdb_spfile$

SQL> col value$ for a30
select pdb.name PDB_NAME, par.name PARAMETER, par.value$
from pdb_spfile$ par, v$pdbs pdb
where par.pdb_uid = pdb.con_uid
and par.name = 'cursor_sharing';

PDB_NAME   PARAMETER            VALUE$
---------- -------------------- ------------------------------
PDB1       cursor_sharing       'FORCE'

– The parameter still has earlier value of similar for cdb$root

SQL> sho parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
cursor_sharing                       string      similar

SQL> col name for a30
col value for a30

select con_id, name, value from v$parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ --------
1 cursor_sharing                 similar

– Let’s check if modified value persists across closing / opening of the PDB

SQL> alter pluggable database pdb1 close;

– After PDB is closed, entry in its spfile is still visible    but current value cannot be seen as PDB is closed

SQL> col value$ for a30
select pdb.name PDB_NAME, par.name PARAMETER, par.value$
from pdb_spfile$ par, v$pdbs pdb
where par.pdb_uid = pdb.con_uid
and par.name = 'cursor_sharing';

PDB_NAME   PARAMETER            VALUE$
---------- -------------------- -------------
PDB1       cursor_sharing       'FORCE'

SQL> select con_id, name, value from v$system_parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ ------------
0 cursor_sharing                 similar

– It can be seen that after PDB is re-opened, the updated
   value still persists

SQL>  alter pluggable database pdb1 open;

select con_id, name, value from v$system_parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ -------------
0 cursor_sharing                 similar
3 cursor_sharing                 FORCE

– Let’s verify that parameter change persists across CDB shutdown

SQL> shu immediate;
     startup
     alter pluggable Database  pdb1 open;

     select con_id, name, value 
     from   v$system_parameter
     where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ -----------
0 cursor_sharing                 similar
3 cursor_sharing                 FORCE

SQL> col value$ for a30
     select pdb.name PDB_NAME, par.name 
            PARAMETER, par.value$
     from pdb_spfile$ par, v$pdbs pdb
     where par.pdb_uid = pdb.con_uid
      and par.name = 'cursor_sharing';

PDB_NAME   PARAMETER            VALUE$
---------- -------------------- ------------------------------
PDB1       cursor_sharing       'FORCE'

– Now we will change the parameter in PDB spfile only

SQL> alter session set container=pdb1;

     alter system set cursor_sharing = 'EXACT' scope=spfile;

– Current value still remains FORCE

sho parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
cursor_sharing                       string      FORCE

–Value has been changed to EXACT in SPfile only

SQL> select con_id, name, value
     from     v$spparameter
     where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ -----------
0 cursor_sharing                 EXACT

– The above changes can be seen from root as well

SQL> alter session set container=cdb$root;

-- The current value is shown as FORCE

SQL> select con_id, name, value 
      from  v$system_parameter
     where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ ----------
0 cursor_sharing                 similar
3 cursor_sharing                 FORCE

– The value in SPFILE is ‘EXACT’ as set

SQL> col value$ for a30
     select pdb.name PDB_NAME, par.name 
            PARAMETER, par.value$
     from pdb_spfile$ par, v$pdbs pdb
      where par.pdb_uid = pdb.con_uid
     and par.name = 'cursor_sharing';

PDB_NAME   PARAMETER            VALUE$
---------- -------------------- ------------------------------
PDB1       cursor_sharing       'EXACT'

– Let’s close and re-open PDB to vefify that value in spfile is
   applied

SQL> alter pluggable database pdb1 close;

    alter pluggable database pdb1 open;
  
     select con_id, name, value 
     from   v$system_parameter
     where name = 'cursor_sharing';  2

CON_ID NAME                           VALUE
---------- ------------------------------ ---------------
0 cursor_sharing                 similar
3 cursor_sharing                 EXACT

– Since the value in spfile is same as default, we can remove this
entry by resetting the value of the parameter.

SQL> alter session set container=pdb1;
     alter system reset cursor_sharing;
     sho parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
cursor_sharing                       string      EXACT

– The entry has been deleted from spfile

SQL> select con_id, name, value 
     from v$spparameter
     where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ -------------
0 cursor_sharing

SQL> alter session set container=cdb$root;

     col value$ for a30
     select pdb.name PDB_NAME, par.name 
            PARAMETER, par.value$
     from pdb_spfile$ par, v$pdbs pdb
     where par.pdb_uid = pdb.con_uid
     and par.name = 'cursor_sharing';

no rows selected

I hope this post was useful. Your comments and suggestions are always welcome!!

References:

Oracle documentation

——————————————————————————————-

Related Links:

Home

Oracle 12c Index

 

 

12c: Optimizer_Dynamic_Sampling = 11

With default sampling level of 2 (from 10g onwards) , dynamic sampling is performed only for the objects for which statistics do not exist. If the statistics are stale or insufficient, dynamic  sampling is not done.

12c introduces a new value of 11 for OPTIMIZER_DYNAMIC_SAMPLING . This value allows the optimizer to automatically perform dynamic sampling using an appropriate level for a SQL statement, even if all basic table statistics exist but they are found to be stale or insufficient. The results of the dynamically sampled queries are persisted in the cache, as dynamic statistics, allowing other SQL statements to share these statistics. This level will no doubt generate a better plan during the first execution of the statement itself but will also lead to dynamic sampling being triggered more frequently and sometime unnecessarily as well.

This example demonstrates that if  OPTIMIZER_DYNAMIC_SAMPLING is set to  11, dynamic sampling will be performed even in case of stale or insufficient statistics. Moreover dynamic sampling  may be unnecessary triggered in some scenarios.

Insufficient Statistics 

I have created a table HR.BIRTHDAYS having 10000 rows whose column MM is indexed and contains numeric month of birth with NDV = 12. The data distribution in the column is skewed . Statistics have been gathered for the table without histogram.

DB12c>select mm, count(*) from hr.birthdays group by mm order by mm;

MM   COUNT(*)
---------- ----------
1       9989
2         1
3         1
4         1
5         1
6         1
7         1
8         1
9         1
10        1
11        1
12        1

12 rows selected.

If OPTIMIZER_DYNAMIC_SAMPLING were set to 2 (default), dynamic sampling will not be done, as statistics are present for the table. However, if the parameter is set to 11 (new in 12c), in view of skewed data distribution, existing statistics  are found to be insufficient (missing histogram) and  dynamic sampling is performed  leading to accurate cardinality estimates for both  MM = 1 which occurs 0.01% times and MM = 12 which occurs 99.89% times .

DB12c>alter session set optimizer_dynamic_sampling=11;
set autot trace explain

select * from hr.birthdays where mm = 12;

Execution Plan
----------------------------------------------------------
Plan hash value: 3569291752
-----------------------------------------------------------------------------
|Id |Operation                          |Name    |Rows|Bytes|Cost(%CPU)|Time|
-----------------------------------------------------------------------------
|  0|SELECT STATEMENT                    |         | 1 |  37| 2(0)| 00:00:01|
|  1| TABLE ACCESS BY INDEX ROWID BATCHED|BIRTHDAYS| 1 |  37| 2(0)| 00:00:01|
|* 2|  INDEX RANGE SCAN                  |BDAY_IDX | 1 |    | 1(0)| 00:00:01|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MM"=12)

Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)

DB12c>select * from hr.birthdays where mm = 1;
set autot off

Execution Plan
----------------------------------------------------------
Plan hash value: 3605468880

-----------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time   |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |  9989 |   360K|    17   (0)|00:00:01|
|*  1 |  TABLE ACCESS FULL| BIRTHDAYS |  9989 |   360K|    17   (0)|00:00:01|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MM"=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
Stale Statistics

Now to make the statistics stale, I have modified the data in the table and have not refreshed  statistics. Note that there are no rows for MM = 1 or 2, so that actual NDV = 10.
Although there are 8388 rows in table, dictionary statistics still shows NUM_ROWS = 10000 and NDV = 12

DB12c> select count(*), count(distinct mm) from hr.birthdays;

COUNT(*) COUNT(DISTINCTMM)
---------- -----------------
 8388                10

DB12c>select owner, table_name, num_rows
from dba_tables
where owner = 'HR'
and table_name = 'BIRTHDAYS';

OWNER   TABLE_NAME        NUM_ROWS
------- --------------- ----------
HR      BIRTHDAYS            10000

DB12c>select owner, table_name, column_name, num_distinct
from dba_tab_cols
where table_name= 'BIRTHDAYS' and column_name = 'MM';

OWNER   TABLE_NAME      COLUMN_NAME     NUM_DISTINCT
------- --------------- --------------- ------------
HR      BIRTHDAYS       MM                        12

If OPTIMIZER_DYNAMIC_SAMPLING = 2 (default),  dynamic sampling will not be done, as statistics (although stale) are present for the table.

With  OPTIMIZER_DYNAMIC_SAMPLING =11, since statistics are stale, dynamic sampling is performed  and almost correct no. of rows are estimated for both MM = 1 and 4

DB12c>alter session set optimizer_dynamic_sampling=11;
select /*+ gather_plan_statistics */ count(*)
from hr.birthdays where mm = 1;

select * from table(dbms_xplan.display_cursor(format => 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID  ghg0pr81m1ha3, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from hr.birthdays where
mm = 1

Plan hash value: 4218648105
-----------------------------------------------------------------------------
|Id  |Operation             |Name    |Starts|E-Rows|A-Rows|  A-Time| Buffers|
-----------------------------------------------------------------------------
|  0 |SELECT STATEMENT      |        |    1 |      |    1 |00:00:00.01|  40 |
|  1 | SORT AGGREGATE       |        |    1 |    1 |    1 |00:00:00.01|  40 |
|* 2 |  INDEX FAST FULL SCAN|BDAY_IDX|    1 |    1 |    0 |00:00:00.01|  40 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MM"=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)

DB12c>select /*+ gather_plan_statistics */ count(*)
from hr.birthdays where mm = 4;

select * from table(dbms_xplan.display_cursor(format => 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------SQL_ID  bhrdb027v2pnt, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from hr.birthdays where
mm = 4

Plan hash value: 3164848757
-----------------------------------------------------------------------------
|Id  |Operation         |Name    |Starts|E-Rows|A-Rows|  A-Time   | Buffers |
-----------------------------------------------------------------------------|  0 |SELECT STATEMENT  |        |    1 |      |    1 |00:00:00.0 |       5 |
|  1 | SORT AGGREGATE   |        |    1 |    1 |    1 |00:00:00.0 |       5 |
|* 2 |  INDEX RANGE SCAN|BDAY_IDX|    1 |  810810 |00:00:00.0 |       5 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MM"=4)

Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)  
 Unnecessary Sampling

To demonstrate unnecessary sampling due to sampling level = 11, I have created unique index on NAME column and deleted the statistics for the table.
A search for a NAME would not have triggered dynamic sampling with a  sampling level of  2  since there is a unique index on NAME column.
On the contrary, if  OPTIMIZER_DYNAMIC_SAMPLING =11, dynamic sampling is unnecessarily performed .

DB12c> alter session set optimizer_dynamic_sampling=11;

select /*+ gather_plan_statistics */ count(*)
from hr.birthdays where NAME = 'NAME OCTOBER  8802';


select * from table(dbms_xplan.display_cursor(format => 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------SQL_ID  d79yg9wq02swy, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from hr.birthdays where
NAME = 'NAME OCTOBER  8802'

Plan hash value: 480407801
-----------------------------------------------------------------------------
|Id  |Operation          |Name         |Starts|E-Rows|A-Rows|A-Time |Buffers|
-----------------------------------------------------------------------------
|  0 |SELECT STATEMENT   |             |    1 |      |    1 |00:00:00.01| 2 |
|  1 | SORT AGGREGATE    |             |    1 |    1 |    1 |00:00:00.01| 2 |
|* 2 |  INDEX UNIQUE SCAN|BDAY_NAME_IDX|    1 |    1 |    1 |00:00:00.01| 2 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"='NAME OCTOBER  8802')
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)

Hence, If OPTIMIZER_DYNAMIC_SAMPLING = 11, sampling is performed in case of
•    missing statistics (as earlier with sampling level of 2),
•    stale statistics and
•    insufficient statistics
possibly leading to an optimal plan during first execution of the statement itself. But this has the disadvantage that dynamic sampling will be unnecessarily triggered in some cases leading to performance degradation. To have the best of both worlds, sampling level can be set to its default value of 2 and SPD’s  can be employed to guide the optimizer to perform dynamic sampling in case of stale / insufficient statistics too.

References:

http://www.google.co.in/url?sa=t&rct=j&q=&esrc=s&source=web&cd=5&ved=0CDQQFjAE&url=http%3A%2F%2Fwww.hroug.hr%2Fhr%2Fcontent%2Fdownload%2F14418%2F236628%2Ffile%2F401_Senegacnik_What_is_new_in_CBO.pdf&ei=p_AjVIjqHYyPuATD4IDAAQ&usg=AFQjCNEAXoYoFbMqYIXNimGgXLQ7N2Ra8Q&sig2=WbVNvkAsluX2Y0rp-N0QNw&bvm=bv.76247554,d.c2E
http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm#PFGRF30101

————————————————————————————

Related Links:

Home

Database 12c Index

 

12c : Transport Database Over Network

Oracle 12c introduces full transportable database import over network . It  employs

  •  Oracle Data Pump import  to extract  all of the system, user, and application metadata needed to transport the database from the source database over network.
  •   transportable tablespaces mechanism to move user and application data i.e. datafiles containing user and application data are physically copied to the target. This results in a migration that is very fast, even for very large volumes of data.

This post focuses on the use of full transportable import  to transport user and application tablespaces from a non-CDB to another non-CDB over the network . The intermediate dumpfile containing the metadata need not be created   as metadata is transferred over network by means of a database link.

– source database : orcl on RHEL5.4 64-bit server
. with sample schemas
. filesystem
. noarchivelog

-- destination database dest on same server
. no sample schemas
. filesystem
. noarchivelog

Overview:

– Create a source non-CDB  orcl with sample schemas
– Create destination non-CDB dest on the same  server without sample schemas
– Set the user and application tablespaces in the source database (orcl) to be READ ONLY
– Copy the  data files for tablespaces containing user/application data to the destination location
– Set system tablespace as default permanent tablespace in destination database dest
– Drop users tablespace from destination database
– Create a database link from destination  to source database orcl which connects as the user with datapump_imp_full_database privilege (system)
– Using an account that has the DATAPUMP_IMP_FULL_DATABASE privilege (system), import into the target database using
impdp with network_link , FULL=Y, TRANSPORT_DATAFILES parameters
– Restore the user tablespaces in source database to read/write mode

Implementation :

– Put application tablespaces (example and users) in read only mode in source database (orcl)

ORCL>conn / as sysdba

select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf

ORCL> alter tablespace example read only;
      alter tablespace users  read only;

-  Copy the  data files for tablespaces containing user/application data to the destination

ORCL> ho cp /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/oradata/dest/example01_orcl.dbf

ho cp /u01/app/oracle/oradata/orcl/users01.dbf /u01/app/oracle/oradata/dest/users01_orcl.dbf

– Create a database link from destination database (dest) to source database (orcl) which connects as the user (system) with datapump_imp_full_database privilege

DEST>create public database link orcl_link connect to system identified by oracle using 'orcl';

- Using an account(system)  that has the DATAPUMP_IMP_FULL_DATABASE privilege , import into the target database using impdp with  network_link , FULL=Y, TRANSPORT_DATAFILES parameters

DEST> ho impdp system/oracle full=y network_link=orcl_link transportable=always transport_datafiles='/u01/app/oracle/oradata/dest/users01_orcl.dbf','/u01/app/oracle/oradata/dest/example01_orcl.dbf' version=12 logfile=import.log

Import: Release 12.1.0.1.0 - Production on Tue Aug 5 04:19:47 2014

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** full=y network_link=orcl_link transportable=always directory=imp_dir transport_datafiles=/u01/app/oracle/oradata/dest/users01_orcl.dbf,/u01/app/oracle/oradata/dest/example01_orcl.dbf version=12 logfile=import.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29349: tablespace 'USERS' already exists

Job "SYSTEM"."SYS_IMPORT_FULL_01" stopped due to fatal error at Tue Aug 5 04:20:30 2014 elapsed 0 00:00:30

DEST> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

DEST>drop tablespace users including contents and datafiles;

drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

– Assign system as default permanent tablespace and drop users tablespace.

– Perform import

DEST> alter database default tablespace system;

      drop tablespace users including contents and datafiles;

       ho impdp system/oracle full=y network_link=orcl_link transportable=always transport_datafiles='/u01/app/oracle/oradata/dest/users01_orcl.dbf','/u01/app/oracle/oradata/dest/example01_orcl.dbf' version=12 logfile=import.log

Import: Release 12.1.0.1.0 - Production on Tue Aug 5 04:25:58 2014

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** full=y network_link=orcl_link transportable=always directory=imp_dir transport_datafiles=/u01/app/oracle/oradata/dest/users01_orcl.dbf,/u01/app/oracle/oradata/dest/example01_orcl.dbf version=12 logfile=import.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE

...

Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/AUDIT
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 30 error(s) at Tue Aug 5 04:36:40 2014 elapsed 0 00:10:38

– Verify that tablespaces example and users have been successfully imported into target database

DEST> col tablespace_name for a10
col file_name for a50
set pagesize 200
set line 500

select tablespace_name, file_name, status, online_status from dba_data_files;

TABLESPACE FILE_NAME                                          STATUS    ONLINE_
---------- -------------------------------------------------- --------- -------
EXAMPLE    /u01/app/oracle/oradata/dest/example01_orcl.dbf    AVAILABLE ONLINE
UNDOTBS1   /u01/app/oracle/oradata/dest/undotbs01.dbf         AVAILABLE ONLINE
SYSAUX     /u01/app/oracle/oradata/dest/sysaux01.dbf          AVAILABLE ONLINE
SYSTEM     /u01/app/oracle/oradata/dest/system01.dbf          AVAILABLE SYSTEM
USERS      /u01/app/oracle/oradata/dest/users01_orcl.dbf      AVAILABLE ONLINE

– Restore the user tablespaces in source database to read/write mode

ORCL> alter tablespace example read write;
           alter tablespace users read write;

I hope this post was useful. Your comments and suggestions are always welcome!

References :

http://docs.oracle.com/database/121/ADMIN/transport.htm#ADMIN13729

—————————————————————————————

Related Links:

Home

Database 12c

 12c: Transportable Database

———————————————————————————

12c: USE_SID_AS_SERVICE_listener_name

In an Oracle 12c container database, the client must specify a service name in order to connect to the PDB. Many  Database clients with earlier releases of Oracle Database might have hard-coded connect descriptors to use SID to connect to the database. Now if they choose to plug in their non-CDB’s into a CDB, they will have to modify the connect descriptors in tnsnames.ora on all the client sites.

This problem can be resolved by the use of listener.ora parameter USE_SID_AS_SERVICE_listener_name.  Setting this parameter to on instructs the listener to use the SID in the connect descriptor as a service name and connect the client to the specified database. The default value for this parameter is off and it has to be explicitly set to on to use this functionality.

Here is the demonstration :

Presently, I have a CDB called CDB1 into which I have plugged in a non-CDB (ncdb).

SQL> sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb1
SQL> sho pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED                       READ ONLY  NO
3 NCDB                           READ WRITE NO

Currently connect descriptor in tnsnames.ora entry for ncdb points to SID as follows:

NCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ncdb)
)
)

In listener.ora , since there is no entry for USE_SID_AS_SERVICE_listener_name, it defaults to off.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

– If I try to connect to NCDB, I get following error :

[oracle@host01 ~]$ export ORACLE_SID=ncdb
[oracle@host01 ~]$ sqlplus system/oracle@ncdb

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 30 15:24:00 2014

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

ERROR:
 ORA-12505: TNS:listener does not currently know of SID given in connect
 descriptor

– I  edited  listener.ora to set USE_SID_AS_SERVICE_LISTENER to on

USE_SID_AS_SERVICE_LISTENER=on
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

[oracle@host01 admin]$ lsnrctl reload

[oracle@host01 admin]$ lsnrctl stat

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 30-MAY-2014 15:24:55

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host01.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                29-MAY-2014 11:03:14
Uptime                    1 days 4 hr. 21 min. 40 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/host01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host01.example.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=host01.example.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "cdb1" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1XDB" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "em12rep" has 1 instance(s).
Instance "em12rep", status READY, has 1 handler(s) for this service...
Service "em12repXDB" has 1 instance(s).
Instance "em12rep", status READY, has 1 handler(s) for this service...
Service "ncdb" has 1 instance(s).
 Instance "cdb1", status READY, has 1 handler(s) for this service...
The command completed successfully

– Now I can connect  to NCDB easily ..

[oracle@host01 ~]$ sqlplus system/oracle@ncdb

SQL> sho con_name

CON_NAME
------------------------------
NCDB

Hope this post was useful.

Your comments and suggestions are always welcome!!

References:

http://docs.oracle.com/cd/E16655_01/network.121/e17611/listener.htm#NETRF2090
http://www.freelists.org/post/oracle-l/DB12c-in-Production,21
Oracle 12c: OS authentication and Multitenant Architecture

 —————————————————————————————-

Related Links:

Home

Database 12c Index

12c : Connecting to CDB/PDB – Set Container vs Connect
12c : Connecting To PDB’s With Same Name

—————————————————————————