Monthly Archives: March 2014

12c : Transportable Database

Oracle 12c introduces full transportable export/import,  an  exciting new feature that greatly simplifies the process of database migration. It  employs

  •  Oracle Data Pump export  to extract  all of the system, user, and application metadata needed for a database migration
  •   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.

Thus, you can upgrade or migrate to Oracle Database 12c easily and quickly with a single import command. This requires that  your source database is at least Oracle Database 11g Release 2 (11.2.0.3).

This post focuses on the use of full transportable export/import  to migrate an 11.2.0.3 database to a pluggable database , although this feature can be used for migrations to a Oracle Database 12c non-CDB database as well. 

The steps for migrating from an 11.2.0.3 non-CDB  into a pluggable database are as follows:

  • Set the user and application tablespaces in the source database to be READ ONLY
  •  Export from the source database using expdp with the FULL=Y, VERSION=12.0 and TRANSPORTABLE=ALWAYS
  • Copy the  dump file and data files for tablespaces containing user/application data to the destination
  • Create a new PDB in the destination CDB using the create pluggable database command
  • Create a directory object in the destination PDB pointing to the folder containing dumpfile
  • Create an entry for the destination PDB in tnsnames.ora
  •   Using an account that has the DATAPUMP_IMP_FULL_DATABASE privilege, import into the target database using impdp with the FULL=Y and TRANSPORT_DATAFILES parameters
  • Restore the user tablespaces in source database to read/write mode

Current scenario:
Source
version : 11.2.0.3
Database
: orcl
Platform 32 bit RHEL 5.4
Tablespaces : system, sysaux, users, example, undotbs1, temp
Target  
container database : cdb1
pluggable database : pdb_orcl
version : 12.1.0.1
Platform 64 bit OEL 5.4
Tablespaces : system, sysaux

Objective :

– Upgrade orcl (11.2.0.3 32 bit linux) to pdb_orcl(12.1.0.1 64 bit linux) using expdp transportable database
- While import rename users tablespace to users_orcl in target db

– Implementation –

— Source Database (ORCL) —

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

– Issue export command 
. TRANSPORTABLE=ALWAYS and FULL=Y : These parameter values tell Data Pump to use full transportable rather than conventional export methods.
.  VERSION=12   : Since source database is  11g Release 2 (11.2.0.3), we  must also specify the Data Pump parameter VERSION=12 to denote the fact that the result of the export will be imported into an Oracle Database 12c Release 1 (12.1) or later database. This is the only time that a version number greater than the current version is allowed by the expdp command. If the source database is Oracle Database 12c, with COMPATIBLE=12.0 or higher, then the VERSION parameter is not required.

Note that command fails as user tablespaces have not been put in read only mode.

[oracle@node1 bin]$ expdp system/oracle full=y transportable=always version=12.0 dumpfile=exporcl.dmp logfile=exporcl.log
Export: Release 11.2.0.3.0 - Production on Mon Mar 24 16:53:13 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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y version=12.0 transportable=always
Estimate in progress using BLOCKS method...
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39185: The transportable tablespace failure list is

ORA-29335: tablespace 'EXAMPLE' is not read only
 ORA-29335: tablespace 'USERS' is not read only
Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at 16:53:20

– Set the user and application tablespaces in the source database to be READ ONLY

SQL> alter tablespace example read only;

              alter tablespace users read only;

– Issue expdp command again
After the export command completes, the export log file shows a list of all of the tablespace data files that need to be moved to the target.

[oracle@node1 bin]$ expdp system/oracle full=y transportable=always version=12.0 dumpfile=exporcl.dmp logfile=exporcl.log

Export: Release 11.2.0.3.0 - Production on Tue Mar 25 09:38:23 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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y transportable=always version=12.0 dumpfile=exporcl.dmp logfile=exporcl.log
Estimate in progress using BLOCKS method...

...

Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/u01/app/oracle/admin/orcl/dpdump/exporcl.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
 /u01/app/oracle/oradata/orcl/example01.dbf
 Datafiles required for transportable tablespace USERS:
 /u01/app/oracle/oradata/orcl/users01.dbf
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 09:51:17

— TARGET CONTAINER DATABASE (CDB1) –

– Find out names of  the datafiles of   seed  database in cdb1

SQL> select file_name from cdb_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/users01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf

– create pluggable database pdb_orcl

SQL> create pluggable database pdb_orcl admin user pdbadmin identified by oracle file_name_convert=('pdbseed','pdb_orcl');

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB_ORCL MOUNTED

-- check that pdb_orcl has  datafiles for system/sysaux tablespaces only

SQL> alter pluggable database pdb_orcl open;

     select file_name from cdb_data_files where con_id=4;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdb_orcl/system01.dbf
 /u01/app/oracle/oradata/cdb1/pdb_orcl/sysaux01.dbf

– Add  tnsnames.ora entry for pdb_orcl

PDB_ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = em12.example.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb_orcl)
)
)

– Copy the  export dump file and the data files for example and users tablespaces   from source to target

— copy example01.dbf and users01.dbf belonging to orcl to folder /u01/app/oracle/oradata/cdb1/pdb_orcl  on target machine

— copy exporcl.dmp containing  metadata  to the folder /u01/app/oracle/admin/cdb1 on target machine

– Create a directory object on the destination PDB (PDB_ORCL)  pointing to location of exporcl.dmp i.e. /u01/app/oracle/admin/cdb1

SQL> alter session set container=pdb_orcl;

sho con_name

CON_NAME
------------------------------
PDB_ORCL

SQL> create directory dump_dir as '/u01/app/oracle/admin/cdb1';

– Invoke full transportable import on the destination PDB

Specify following parameters :
- the dumpfile name
– full = Y and
– TRANSPORT_DATAFILES : to secify  the list of user tablespace data files to be transported

[oracle@em12 admin]$ impdp system/oracle@pdb_orcl full=y directory=dump_dir dumpfile=exporcl.dmp transport_datafiles='/u01/app/oracle/oradata/cdb1/pdb_orcl/example01.dbf', '/u01/app/oracle/oradata/cdb1/pdb_orcl/users01.dbf' remap_tablespace=users:users_orcl logfile=import_orcl.log

Import: Release 12.1.0.1.0 - Production on Tue Mar 25 11:41:44 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
Master table "SYSTEM"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Source timezone version is +00:00 and target timezone version is -07:00.
Starting "SYSTEM"."SYS_IMPORT_FULL_02": system/********@pdb_orcl full=y directory=dump_dir dumpfile=exporcl.dmp transport_datafiles=/u01/app/oracle/oradata/cdb1/pdb_orcl/example01.dbf, /u01/app/oracle/oradata/cdb1/pdb_orcl/users01.dbf remap_tablespace=users:users_orcl logfile=import_orcl.log

....
Job "SYSTEM"."SYS_IMPORT_FULL_02" completed with 3395 error(s) at Tue Mar 25 12:07:27 2014 elapsed 0 00:25:40

– Check that target PDB’s metadata is updated with datafiles for example and users tablespaces  

SQL> select name from v$datafile;

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

–  Check that the application tablespaces are automatically placed in read/write mode on the destination database and users tablespace has been renamed to users_orcl

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
EXAMPLE ONLINE
USERS_ORCL ONLINE

 — Check that user HR has been created on the target PDB also 

SQL> conn hr/hr@pdb_orcl
    select tname from tab;

TNAME
--------------------------------------------------------------------------------
COUNTRIES
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
EMP_DETAILS_VIEW

– Restore user tablespaces to read-write mode on the source database

SQL> alter tablespace USERS read write;

alter tablespace example read write;
              select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE

References: 

http://docs.oracle.com/cd/E16655_01/server.121/e17636/transport.htm#ADMIN13727
Full Transportable Export / Import – Things to know
Full Transportable Export / Import – Migrating an 11.2.0.4 Database to Oracle Database 12c – Into the Cloud

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

Related Links:

Home

Database 12c

12c: Transport Database Over Network

 

 

 

 

 

ORA-65023: active transaction exists in container CDB$ROOT

I received this error message in the following scenario:

– Connect to CDB

SQL> conn / as sysdba
sho con_name

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

– Start a transaction

SQL> create table test(c number);
     insert into test values(1) ;

– Change the container to PDB without completing the transaction

SQL> alter session set container=pdb2;

– Try to shutdown the pluggable database

– It causes ORA-65023 because  on switching the container (not   making a connection to  PDB using connect), pending transactions in the original container are still in a pending state

SQL> shu immediate;
ORA-65023: active transaction exists in container CDB$ROOT

That’s what oracle documentation says for this error:

Cause

A statement attempted to create a new transaction in the current container while there was an active transaction in another container.

Action

Switch to the container with the active transaction and commit, rollback or detach the active transaction before attempting to issue any statement that will attempt to create a new transaction in another container.

– Switch back to original container (cdb$root) and commit the transaction

SQL> alter session set container=cdb$root;
commit;

– Switch the container to PDB and close the PDB

— The attempt succeeds as there is no pending transaction in the parent container.

SQL> alter session set container=pdb2;
shu immediate;
Pluggable Database closed.

SQL> sho pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB2                           MOUNTED

References:

Oracle Documentation

http://www.oracle-base.com/articles/12c/multitenant-runing-scripts-cdb-and-pdb-12cr1.php

 

——————————————————————————————–

Related Links:

Home

Database 12c

 

 

12c : DDL log does not identify the source PDB

In 12c, the location of DDL logs has changed from alert log to DDL log.

Oracle Database 11g

If instance parameter ENABLE_DDL_LOGGING = TRUE

DDL statements are logged into the alert log.

Oracle Database 12c

If instance parameter ENABLE_DDL_LOGGING = TRUE

DDL statement logging has been moved from alert log to DDL log
i.e. $ADR_HOME/log/ddl/log.xml
This file  contains only the DDL statements and dates.
– Let’s demonstrate

–Check that by default DDL logging is disabled

SQL> sho parameter ddl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     FALSE

– Enable DDL logging

SQL> alter system set enable_ddl_logging=true;

sho parameter ddl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     TRUE

– Remove all the trace files including alert log

[oracle@em12 ~]$ cd /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/

rm -rf *

ls

– check that there is no DDL log file in /u01/app/oracle/diag/rdbms/cdb1/cdb1/log/ddl

[oracle@em12 ddl]$ cd /u01/app/oracle/diag/rdbms/cdb1/cdb1/log/ddl
ls

– Issue a DDL while connected to CDB1

SQL> sho con_name

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

SQL>  create table test(x number);

– check that alert log has not been created as DDL’s are no longer logged to alert log

[oracle@em12 log]$ ls /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/

– check that ddl log file log.xml has been created in $ORACLE_BASE/diag/rdbms/cdb1/cdb1/log/ddl($ADR_HOME/log/ddl)

[oracle@em12 ddl]$ ls /u01/app/oracle/diag/rdbms/cdb1/cdb1/log/ddl/

log.xml

– check that the contents of the file give info about the DDL that was issued
Note that name of the database issuing the DDL (CDB$ROOT) is not there in the log

[oracle@em12 ddl]$ cat log.xml

<msg time='2014-03-05T13:08:33.207+05:30' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='em12.example.com' host_addr='192.9.201.150'
version='1'>
<txt>create table test(x number)
</txt>
</msg>

– Issue another DDL from PDB1

SQL> alter session set container=pdb1;
create table test(x number);

– check that the contents of the file give info about this DDL also
   Again note that name of the database issuing the DDL(PDB1) is not there in the log

[oracle@em12 ddl]$ cat log.xml

<msg time='2014-03-05T13:08:33.207+05:30' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='em12.example.com' host_addr='192.9.201.150'
version='1'>
<txt>create table test(x number)
</txt>
</msg>
<msg time='2014-03-05T13:09:11.104+05:30' org_id='oracle' comp_id='rdbms'
msg_id='kpdbLogDDL:15115:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='em12.example.com' host_addr='192.9.201.150'>
<txt> create table test(x number)
</txt>
</msg>

It can be seen that it is not possible to identify  from this log whether the  DDL was issued from CDB$ROOT or which PDB (in case CDB has multiple PDB’s)

In 11g or 12c non-CDB, this issue is not there since the alert log belongs to one database only.

References:

Oracle Documentation

Oracle 12c: DDL Logging : Will it serve the purpose?

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

Related Links:

Home

Database 12c