Transporting Tablespaces from 11g Database to 12c Database

This article covers the Transporting Data: Scenarios of “Transportable Tablespaces” https://community.oracle.com/docs/DOC-991671

Introduction:
This is existing feature onwards Oracle 8i.

You can transport data at any of the following levels:
Database
You can use the full transportable export/import feature to move an entire database to a different database instance.
 Tablespaces
You can use the transportable tablespaces feature to move a set of tablespaces between databases.
 Tables, partitions, and subpartitions
You can use the transportable tables feature to move a set of tables, partitions, and subpartitions between databases.

Transporting data is much faster than performing either an export/import or unload/load of the same data. It is faster because, for user-defined tablespaces, the data files containing all of the actual data are copied to the target location, and you use Data Pump to transfer only the metadata of the database objects to the new database.

The Procedure:
Scenario having Source Database on Standalone 11g DB v11.2.0.3.0 on Solaris 10 64bit, Data files stored on File system and Destination Database on 2-Node Oracle RAC 12c DB v12.1.0.2.0 on Linux 7.0 64bit, Data files will be stored on Oracle ASM

Step-1# It is assumed the following datafiles and tablespaces exist in source database:

set linesize 100 pagesize 100
SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS
WHERE USERNAME IN
(‘MYAPP1’,
‘MYAPP2’,
‘MYAPP3’,
‘MYAPP4’,
‘MYAPP5’,
‘MYAPP6’,
‘MYAPP7’);

USERNAME DEFAULT_TABLESPACE
————– ——————————
MYAPP5 MYAPP5
MYAPP4 MYAPP4
MYAPP1 MYAPP1
MYAPP3 MYAPP3
MYAPP6 MYAPP6
MYAPP7 MYAPP7
MYAPP2 MYAPP2

select tablespace_name,file_name from dba_data_files where tablespace_name in
(‘MYAPP1’,
‘MYAPP2’,
‘MYAPP3’,
‘MYAPP4’,
‘MYAPP5’,
‘MYAPP6’,
‘MYAPP7’);

TABLESPACE_NAME FILE_NAME
—————————— ———————————————
MYAPP7 /u01/app/oracle/oradata/mydb/MYAPP7.dbf
MYAPP2 /u01/app/oracle/oradata/mydb/MYAPP2.dbf
MYAPP6 /u01/app/oracle/oradata/mydb/MYAPP6.dbf
MYAPP3 /u01/app/oracle/oradata/mydb/MYAPP3.dbf
MYAPP1 /u01/app/oracle/oradata/mydb/MYAPP1.dbf
MYAPP4 /u01/app/oracle/oradata/mydb/MYAPP4.dbf
MYAPP5 /u01/app/oracle/oradata/mydb/MYAPP5.dbf

select tablespace_name,status from dba_tablespaces where tablespace_name in (‘MYAPP1’,
‘MYAPP2’,
‘MYAPP3’,
‘MYAPP4’,
‘MYAPP5’,
‘MYAPP6’,
‘MYAPP7’);

Step-2# Determine if Platforms are Supported and Determine Endianness
This task is only necessary if you are transporting the tablespace set to a platform different from the source platform.

If you are transporting the tablespace set to a platform different from the source platform,
then determine if cross-platform tablespace transport is supported for both the source and
destination platforms, and determine the endianness of each platform. If both platforms have
the same endianness, no conversion is necessary. Otherwise you must do a conversion of the
tablespace set either at the source or destination database.

SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME; 2 3

Source
=========
PLATFORM_NAME ENDIAN_FORMAT
————————————————
Solaris[tm] OE (64-bit) Big

Destination
============
PLATFORM_NAME ENDIAN_FORMAT
————————————————-
Linux x86 64-bit Little
Step-3# The following statement can be used to determine whether tablespaces are self-contained, with referential integrity constraints taken into consideration (indicated by TRUE).

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘MYAPP5,MYAPP4,MYAPP1,MYAPP3,MYAPP6,MYAPP7,MYAPP2’, TRUE);

PL/SQL procedure successfully completed.

#After invoking this PL/SQL package, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, this view is empty.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

Step-4# Make all tablespaces in the set read-only.

SQL> ALTER TABLESPACE MYAPP5 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE MYAPP4 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE MYAPP1 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE MYAPP3 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE MYAPP6 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE MYAPP7 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE MYAPP2 READ ONLY;

Tablespace altered.

TABLESPACE_NAME STATUS
—————————— ———
MYAPP4 READ ONLY
MYAPP5 READ ONLY
MYAPP6 READ ONLY
MYAPP7 READ ONLY
MYAPP1 READ ONLY
MYAPP2 READ ONLY
MYAPP3 READ ONLY

Step-5# Invoke the Data Pump export utility as user system and specify the tablespaces in the transportable set.

expdp system dumpfile=expdat05012016.dmp directory=data_pump_dir transport_tablespaces=MYAPP5,MYAPP4,MYAPP1,MYAPP3,MYAPP6,MYAPP7,MYAPP2 logfile=tts_export_05012016.log

-bash-3.00$ expdp system dumpfile=expdat05012016.dmp directory=data_pump_dir transport_tablespaces=MYAPP5,MYAPP4,MYAPP1,MYAPP3,MYAPP6,MYAPP7,MYAPP2 logfile=tts_export_05012016.log

Export: Release 11.2.0.3.0 – Production on Tue Jan 5 15:36:29 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_TRANSPORTABLE_01″: system/******** dumpfile=expdat05012016.dmp directory=data_pump_dir transport_tablespaces=MYAPP5,MYAPP4,MYAPP1,MYAPP3,MYAPP6,MYAPP7,MYAPP2 logfile=tts_export_05012016.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table “SYSTEM”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/product/11.2.0/db_1/rdbms/log/expdat05012016.dmp
******************************************************************************
Datafiles required for transportable tablespace MYAPP4:
/u01/app/oracle/oradata/mydb/MYAPP4.dbf
Datafiles required for transportable tablespace MYAPP5:
/u01/app/oracle/oradata/mydb/MYAPP5.dbf
Datafiles required for transportable tablespace MYAPP6:
/u01/app/oracle/oradata/mydb/MYAPP6.dbf
Datafiles required for transportable tablespace MYAPP7:
/u01/app/oracle/oradata/mydb/MYAPP7.dbf
Datafiles required for transportable tablespace MYAPP1:
/u01/app/oracle/oradata/mydb/MYAPP1.dbf
Datafiles required for transportable tablespace MYAPP2:
/u01/app/oracle/oradata/mydb/MYAPP2.dbf
Datafiles required for transportable tablespace MYAPP3:
/u01/app/oracle/oradata/mydb/MYAPP3.dbf
Job “SYSTEM”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully completed at 15:46:31

Step-6# Prepare Source to transfer datafile and backup to target database
#tnsnames.ora
CSMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.139)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = csmdb)
)
)

-bash-3.00$ tnsping CSMDB
TNS Ping Utility for Solaris: Version 11.2.0.3.0 – Production on 05-JAN-2016 16:58:29
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.139)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = csmdb)))
OK (0 msec)

SQL> CREATE OR REPLACE DIRECTORY db_files_dir1 AS ‘/u01/app/oracle/oradata/mydb/’;

Directory created.

CREATE DATABASE LINK remote CONNECT TO system IDENTIFIED BY eliteAAA123 USING ‘csmdb’;

Destination:
SQL> CREATE OR REPLACE DIRECTORY db_files_dir2 as ‘+DATA’;

Directory created.

Step-7# Copy the datafile from source to target using the Oracle Utility -DBMS_FILE_TRANSFER.PUT_FILE
Source:
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(source_directory_object =>
‘DB_FILES_DIR1’, source_file_name => ‘MYAPP4.dbf’,
destination_directory_object => ‘DB_FILES_DIR2’,
destination_file_name => ‘MYAPP4.dbf’,
destination_database => ‘REMOTE’);
END;
/

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(source_directory_object =>
‘DB_FILES_DIR1’, source_file_name => ‘MYAPP5.dbf’,
destination_directory_object => ‘DB_FILES_DIR2’,
destination_file_name => ‘MYAPP5.dbf’,
destination_database => ‘REMOTE’);
END;
/

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(source_directory_object =>
‘DB_FILES_DIR1’, source_file_name => ‘MYAPP6.dbf’,
destination_directory_object => ‘DB_FILES_DIR2’,
destination_file_name => ‘MYAPP6.dbf’,
destination_database => ‘REMOTE’);
END;
/

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(source_directory_object =>
‘DB_FILES_DIR1’, source_file_name => ‘MYAPP7.dbf’,
destination_directory_object => ‘DB_FILES_DIR2’,
destination_file_name => ‘MYAPP7.dbf’,
destination_database => ‘REMOTE’);
END;
/

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(source_directory_object =>
‘DB_FILES_DIR1’, source_file_name => ‘MYAPP1.dbf’,
destination_directory_object => ‘DB_FILES_DIR2’,
destination_file_name => ‘MYAPP1.dbf’,
destination_database => ‘REMOTE’);
END;
/

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(source_directory_object =>
‘DB_FILES_DIR1’, source_file_name => ‘MYAPP2.dbf’,
destination_directory_object => ‘DB_FILES_DIR2’,
destination_file_name => ‘MYAPP2.dbf’,
destination_database => ‘REMOTE’);
END;
/

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(source_directory_object =>
‘DB_FILES_DIR1’, source_file_name => ‘MYAPP3.dbf’,
destination_directory_object => ‘DB_FILES_DIR2’,
destination_file_name => ‘MYAPP3.dbf’,
destination_database => ‘REMOTE’);
END;
/

.oraenv
+ASM2

asmcmd
asmcmd> lsdg
confirm the location of copied datafile.

Step-8# The RMAN CONVERT command is used to do the conversion. Start RMAN and connect to the target database:
CONVERT DATAFILE
‘+DATA/MYAPP4.dbf’,
‘+DATA/MYAPP5.dbf’,
‘+DATA/MYAPP6.dbf’,
‘+DATA/MYAPP7.dbf’,
‘+DATA/MYAPP1.dbf’,
‘+DATA/MYAPP2.dbf’,
‘+DATA/MYAPP3.dbf’
TO PLATFORM=”Linux x86 64-bit”
FROM PLATFORM=”Solaris[tm] OE (64-bit)”
DB_FILE_NAME_CONVERT=’/home/oracle/Demo_Db’,’+DATA’
PARALLELISM=2;

Above command failed with error
ORA-19563: cross-platform datafile header validation failed for file +RECO/soets_9.tf

MetaLink and OTN Community
—————————
Known issue Using DBMS_FILE_TRANSFER

=> Unpublished Bug 13636964- ORA-19563 from RMAN convert on datafile copy transferred with DBMS_FILE_TRANSFER (Doc ID 13636964.8)
Versions confirmed as being affected
11.2.0.3
This issue is fixed in
12.1.0.1 (Base Release)
11.2.0.4 (Future Patch Set)

Description

A file transferred using DBMS_FILE_TRANSFER fails during an RMAN convert
operation.
eg:
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of conversion at target command at 01/24/2012 16:22:23
ORA-19563: cross-platform datafile header validation failed for file +RECO/soets_9.tf

Rediscovery Notes:
If RMAN convert fails on a file transferred using DBMS_FILE_TRANSFER
then it may be due to this bug

Workaround
Transfer the file using OS facilities.

Step-9##Copy the datafile from source to target using OS Utility
scp MYAPP1.dbf oracle@target_server:/home/oracle/Demo_Db
scp MYAPP2.dbf oracle@target_server:/home/oracle/Demo_Db
scp MYAPP3.dbf oracle@target_server:/home/oracle/Demo_Db
scp MYAPP4.dbf oracle@target_server:/home/oracle/Demo_Db
scp MYAPP5.dbf oracle@target_server:/home/oracle/Demo_Db
scp MYAPP6.dbf oracle@target_server:/home/oracle/Demo_Db
scp MYAPP7.dbf oracle@target_server:/home/oracle/Demo_Db

Step-10# The RMAN CONVERT command is used to do the conversion. Start RMAN and connect to the target database:
-bash-4.2$ rman target /

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Jan 5 17:07:43 2016

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

connected to target database: CSMDB (DBID=3698469423)

RMAN> CONVERT DATAFILE
‘/home/oracle/Demo_Db/MYAPP4.dbf’,
‘/home/oracle/Demo_Db/MYAPP5.dbf’,
‘/home/oracle/Demo_Db/MYAPP6.dbf’,
‘/home/oracle/Demo_Db/MYAPP7.dbf’,
‘/home/oracle/Demo_Db/MYAPP1.dbf’,
‘/home/oracle/Demo_Db/MYAPP2.dbf’,
‘/home/oracle/Demo_Db/MYAPP3.dbf’
TO PLATFORM=”Linux x86 64-bit”
FROM PLATFORM=”Solaris[tm] OE (64-bit)”
DB_FILE_NAME_CONVERT=’/home/oracle/Demo_Db’,’+DATA’
PARALLELISM=2;

Starting conversion at target at 05-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=867 instance=csmdb2 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1058 instance=csmdb2 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/Demo_Db/MYAPP2.dbf
channel ORA_DISK_2: starting datafile conversion
input file name=/home/oracle/Demo_Db/MYAPP3.dbf
converted datafile=+DATA/MYAPP3.dbf
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_2: starting datafile conversion
input file name=/home/oracle/Demo_Db/MYAPP1.dbf
converted datafile=+DATA/MYAPP1.dbf
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:03
channel ORA_DISK_2: starting datafile conversion
input file name=/home/oracle/Demo_Db/MYAPP4.dbf
converted datafile=+DATA/MYAPP4.dbf
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_2: starting datafile conversion
input file name=/home/oracle/Demo_Db/MYAPP5.dbf
converted datafile=+DATA/MYAPP5.dbf
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_2: starting datafile conversion
input file name=/home/oracle/Demo_Db/MYAPP6.dbf
converted datafile=+DATA/MYAPP6.dbf
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_2: starting datafile conversion
input file name=/home/oracle/Demo_Db/MYAPP7.dbf
converted datafile=+DATA/MYAPP2.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
converted datafile=+DATA/MYAPP7.dbf
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 05-JAN-16

Step-11# Import the tablespace metadata using the Data Pump Import utility, impdp:
impdp system dumpfile=expdat05012016.dmp directory=data_pump_dir transport_datafiles=+DATA/MYAPP4.dbf,+DATA/MYAPP5.dbf,+DATA/MYAPP6.dbf,+DATA/MYAPP7.dbf,+DATA/MYAPP1.dbf,+DATA/MYAPP2.dbf,+DATA/MYAPP3.dbf logfile=tts_import.log

#The REMAP_SCHEMA parameter changes the ownership of database objects.
If you do not specify REMAP_SCHEMA, all database objects (such as tables and indexes)
are created in the same user schema as in the source database, and those users must
already exist in the destination database. If they do not exist, then the import utility
returns an error.

#vi create_users.sql
create user &&username identified by &&username;
grant connect,resource,create any view to &&username;
grant create job to &&username;
grant read,write on directory data_pump_dir to &&username;

#Import
-bash-4.2$ impdp system dumpfile=expdat05012016.dmp directory=data_pump_dir transport_datafiles=+DATA/MYAPP4.dbf,+DATA/MYAPP5.dbf,+DATA/MYAPP6.dbf,+DATA/MYAPP7.dbf,+DATA/MYAPP1.dbf,+DATA/MYAPP2.dbf,+DATA/MYAPP3.dbf logfile=tts_import.log

Import: Release 12.1.0.2.0 – Production on Tue Jan 5 17:26:57 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
Source time zone version is 14 and target time zone version is 18.
Source time zone is +05:30 and target time zone is +00:00.
Starting “SYSTEM”.”SYS_IMPORT_TRANSPORTABLE_01″: system/******** dumpfile=expdat05012016.dmp directory=data_pump_dir transport_datafiles=+DATA/MYAPP4.dbf,+DATA/MYAPP5.dbf,+DATA/MYAPP6.dbf,+DATA/MYAPP7.dbf,+DATA/MYAPP1.dbf,+DATA/MYAPP2.dbf,+DATA/MYAPP3.dbf logfile=tts_import.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
ORA-39082: Object type TRIGGER:”MYAPP1″.”TBLRADIUSCUSTOMER_SEQ_TRIGGER” created with compilation warnings
ORA-39082: Object type TRIGGER:”MYAPP3″.”TBLRADIUSCUSTOMER_SEQ_TRIGGER” created with compilation warnings
Job “SYSTEM”.”SYS_IMPORT_TRANSPORTABLE_01″ completed with 2 error(s) at Tue Jan 5 17:31:23 2016 elapsed 0 00:04:22

#Check Alert logs
Tue Jan 05 17:27:02 2016
Archived Log entry 132 added for thread 2 sequence 77 ID 0xdc71d92f dest 1:
Tue Jan 05 17:27:03 2016
Plug in tablespace MYAPP4 with datafile
‘+DATA/MYAPP4.dbf’
Plug in tablespace MYAPP5 with datafile
‘+DATA/MYAPP5.dbf’
Plug in tablespace MYAPP6 with datafile
‘+DATA/MYAPP6.dbf’
Plug in tablespace MYAPP7 with datafile
‘+DATA/MYAPP7.dbf’
Plug in tablespace MYAPP1 with datafile
‘+DATA/MYAPP1.dbf’
Plug in tablespace MYAPP2 with datafile
‘+DATA/MYAPP2.dbf’
Plug in tablespace MYAPP3 with datafile
‘+DATA/MYAPP3.dbf’
Tue Jan 05 17:27:04 2016
Thread 2 cannot allocate new log, sequence 79
Checkpoint not complete

heckpoint not complete
Current log# 3 seq# 87 mem# 0: +DATA/CSMDB/ONLINELOG/group_3.270.899316471
Current log# 3 seq# 87 mem# 1: +DATA/CSMDB/ONLINELOG/group_3.271.899316471
Tue Jan 05 17:31:08 2016
Thread 2 advanced to log sequence 88 (LGWR switch)
Current log# 4 seq# 88 mem# 0: +DATA/CSMDB/ONLINELOG/group_4.272.899316471
Current log# 4 seq# 88 mem# 1: +DATA/CSMDB/ONLINELOG/group_4.273.899316473
Tue Jan 05 17:31:08 2016
Archived Log entry 145 added for thread 2 sequence 87 ID 0xdc71d92f dest 1:
ALTER TABLESPACE “MYAPP4” READ WRITE
Completed: ALTER TABLESPACE “MYAPP4” READ WRITE
ALTER TABLESPACE “MYAPP4” READ ONLY
Tue Jan 05 17:31:21 2016
Converting block 0 to version 10 format
Completed: ALTER TABLESPACE “MYAPP4” READ ONLY
ALTER TABLESPACE “MYAPP5” READ WRITE
Completed: ALTER TABLESPACE “MYAPP5” READ WRITE
ALTER TABLESPACE “MYAPP5” READ ONLY
Converting block 0 to version 10 format
Completed: ALTER TABLESPACE “MYAPP5” READ ONLY
ALTER TABLESPACE “MYAPP6” READ WRITE
Completed: ALTER TABLESPACE “MYAPP6” READ WRITE
ALTER TABLESPACE “MYAPP6” READ ONLY
Converting block 0 to version 10 format
Completed: ALTER TABLESPACE “MYAPP6” READ ONLY
ALTER TABLESPACE “MYAPP7” READ WRITE
Completed: ALTER TABLESPACE “MYAPP7” READ WRITE
ALTER TABLESPACE “MYAPP7” READ ONLY
Converting block 0 to version 10 format
Completed: ALTER TABLESPACE “MYAPP7” READ ONLY
ALTER TABLESPACE “MYAPP1” READ WRITE
Completed: ALTER TABLESPACE “MYAPP1” READ WRITE
ALTER TABLESPACE “MYAPP1” READ ONLY
Converting block 0 to version 10 format
Completed: ALTER TABLESPACE “MYAPP1” READ ONLY
ALTER TABLESPACE “MYAPP2” READ WRITE
Completed: ALTER TABLESPACE “MYAPP2” READ WRITE
ALTER TABLESPACE “MYAPP2” READ ONLY
Converting block 0 to version 10 format
Completed: ALTER TABLESPACE “MYAPP2” READ ONLY
ALTER TABLESPACE “MYAPP3” READ WRITE
Completed: ALTER TABLESPACE “MYAPP3” READ WRITE
ALTER TABLESPACE “MYAPP3” READ ONLY
Converting block 0 to version 10 format
Completed: ALTER TABLESPACE “MYAPP3” READ ONLY

Step-12#Make all tablespaces in the set read-write.

ALTER TABLESPACE MYAPP5 READ WRITE;

ALTER TABLESPACE MYAPP4 READ WRITE;

ALTER TABLESPACE MYAPP1 READ WRITE;

ALTER TABLESPACE MYAPP3 READ WRITE;

ALTER TABLESPACE MYAPP6 READ WRITE;

ALTER TABLESPACE MYAPP7 READ WRITE;

ALTER TABLESPACE MYAPP2 READ WRITE;
Step-13 #Confirm the data dictionaries of DBA_USERS/DBA_TABLESPACES
set linesize 100 pagesize 100
SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS
WHERE USERNAME IN
(‘MYAPP1’,
‘MYAPP2’,
‘MYAPP3’,
‘MYAPP4’,
‘MYAPP5’,
‘MYAPP6’,
‘MYAPP7’);

USERNAME DEFAULT_TABLESPACE
——————————
MYAPP2 USERS
MYAPP7 USERS
MYAPP6 USERS
MYAPP3 USERS
MYAPP1 USERS
MYAPP4 USERS
MYAPP5 USERS

TABLESPACE_NAME FILE_NAME
—————————— ——————-
MYAPP2 +DATA/MYAPP2.dbf
MYAPP7 +DATA/MYAPP7.dbf
MYAPP6 +DATA/MYAPP6.dbf
MYAPP3 +DATA/MYAPP3.dbf
MYAPP1 +DATA/MYAPP1.dbf
MYAPP4 +DATA/MYAPP4.dbf
MYAPP5 +DATA/MYAPP5.dbf

Step-14#Assign the user to his respective default tablespace
ALTER USER MYAPP2 DEFAULT TABLESPACE MYAPP2;
ALTER USER MYAPP7 DEFAULT TABLESPACE MYAPP7;
ALTER USER MYAPP6 DEFAULT TABLESPACE MYAPP6;
ALTER USER MYAPP3 DEFAULT TABLESPACE MYAPP3;
ALTER USER MYAPP1 DEFAULT TABLESPACE MYAPP1;
ALTER USER MYAPP4 DEFAULT TABLESPACE MYAPP4;
ALTER USER MYAPP5 DEFAULT TABLESPACE MYAPP5;

set linesize 100 pagesize 100
SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS
WHERE USERNAME IN
(‘MYAPP1’,
‘MYAPP2’,
‘MYAPP3’,
‘MYAPP4’,
‘MYAPP5’,
‘MYAPP6’,
‘MYAPP7’);

USERNAME DEFAULT_TABLESPACE
——————————
MYAPP4 MYAPP4
MYAPP5 MYAPP5
MYAPP1 MYAPP1
MYAPP3 MYAPP3
MYAPP6 MYAPP6
MYAPP7 MYAPP7
MYAPP2 MYAPP2

Step-15# Check Invalid objects

SQL> select object_name,owner from dba_objects where status != ‘VALID’;

OBJECT_NAME                                                                   OWNER
——————————————————————————–
TBLRADIUSCUSTOMER_SEQ_TRIGGER                            MYAPP1
TBLRADIUSCUSTOMER_SEQ_TRIGGER                            MYAPP3

SQL> select text from user_source where name=’TBLRADIUSCUSTOMER_SEQ_TRIGGER’;

TEXT
——————————————————————————–
TRIGGER TBLRADIUSCUSTOMER_SEQ_TRIGGER
BEFORE INSERT ON TBLRADIUSCUSTOMER
FOR EACH ROW

BEGIN

IF :new.ID IS NULL
THEN
SELECT SEQ_RADIUSCUSTOMER.nextval INTO :new.ID FROM DUAL;
END IF;
END;

17 rows selected.

SQL> select SEQ_RADIUSCUSTOMER.nextval from dual;
select SEQ_RADIUSCUSTOMER.nextval from dual
*
ERROR at line 1:
ORA-02289: sequence does not exist

SQL> select sequence_name from user_sequences;

no rows selected

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
-bash-4.2$ sqlplus MYAPP3/MYAPP3

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 5 17:51:45 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select sequence_name from user_sequences;

no rows selected

SQL>

I am trying to transport tablespaces from one DB Instance to another db instances on Solaris 64bit to Linux 64bit
It works fine for Tables/Index and constrains but unable to transport Functions and Sequences.

The only objects that are exported as part of transportable are objects associated with the
tablespaces that you list. If you want to see the complete list, you can query the view:

select unique full_path from SYS.datapump_paths where het_type = ‘TRANSPORTABLE_EXPORT’;

This will give you what gets exported as part of a datapump transportable export job.
If you need to export synonyms or packages, you can use datapump, but you will either
need to use the schema mode or full mode. You can add the include parameter to only get
what you want.

Step-16# export and import sequence,procedure,function,package,PROCOBJ,synonyms from source database to target database
First Method
——————–
Source:
alter tablespace MYAPP4 read write;
grant read,write on directory data_pump_dir to MYAPP4;

-bash-3.00$ expdp MYAPP5 dumpfile=expdat06012016_other.dmp directory=data_pump_dir schemas=MYAPP5 logfile=tts_export_06012016_other.log include=sequence,procedure,function,package,PROCOBJ,synonyms

Export: Release 11.2.0.3.0 – Production on Wed Jan 6 10:47:46 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “MYAPP5”.”SYS_EXPORT_SCHEMA_01″: MYAPP5/******** dumpfile=expdat06012016_other.dmp directory=data_pump_dir schemas=MYAPP5 logfile=tts_export_06012016_other.log include=sequence,procedure,function,package
Estimate in progress using BLOCKS method…
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39168: Object path FUNCTION was not found.
ORA-39168: Object path PACKAGE was not found.
Master table “MYAPP5”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for MYAPP5.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/product/11.2.0/db_1/rdbms/log/expdat06012016_other.dmp
Job “MYAPP5”.”SYS_EXPORT_SCHEMA_01″ completed with 2 error(s) at 10:48:21

Destination:
alter user MYAPP4 quota unlimited on MYAPP4;
impdp MYAPP4 dumpfile=expdat06012016_MYAPP4.dmp directory=data_pump_dir schemas=MYAPP4 logfile=tts_export_06012016_MYAPP4.log include=sequence,procedure,function,package,PROCOBJ

select count(sequence_name) from user_sequences;
select count(distinct name) from user_source;
select count(job_name) from user_scheduler_jobs;

Second Method
————————-
expdp system dumpfile=expdat06012016_full.dmp directory=data_pump_dir full=y logfile=tts_export_06012016_full.log include=sequence,procedure,function,package,PROCOBJ,synonyms

-bash-3.00$ expdp system dumpfile=expdat06012016_full.dmp directory=data_pump_dir full=y logfile=tts_export_06012016_full.log include=sequence,procedure,function,package,PROCOBJ,synonyms

Export: Release 11.2.0.3.0 – Production on Wed Jan 6 11:43:49 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/******** dumpfile=expdat06012016_full.dmp directory=data_pump_dir full=y logfile=tts_export_06012016_full.log include=sequence,procedure,function,package,PROCOBJ,synonyms
Estimate in progress using BLOCKS method…
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
ORA-39168: Object path SYNONYMS was not found.
Master table “SYSTEM”.”SYS_EXPORT_FULL_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/u01/app/oracle/product/11.2.0/db_1/rdbms/log/expdat06012016_full.dmp
Job “SYSTEM”.”SYS_EXPORT_FULL_01″ completed with 1 error(s) at 11:46:37

-bash-3.00$ cd /u01/app/oracle/product/11.2.0/db_1/rdbms/log/
-bash-3.00$ scp expdat06012016_full.dmp oracle@192.168.2.138:/u01/app/oracle/product/12.1.0.2/db_1/rdbms/log/
oracle@192.168.2.138’s password:
expdat06012016_full. 100% |*********************************************************************************| 3328 KB 00:00

Destination:
impdp system dumpfile=expdat06012016_full.dmp directory=data_pump_dir logfile=tts_export_06012016_full.log include=sequence,procedure,function,package,PROCOBJ,synonyms
or
impdp &username/&password dumpfile=expdat06012016_full.dmp directory=data_pump_dir logfile=tts_export_06012016_full.log include=sequence,procedure,function,package,PROCOBJ,synonyms

Step-17#Confirm data dictionaries of DBA_SEQUENCES/DBA_SOURCE/DBA_SCHEDULER_JOBS/DBA_SYNONYMS
col sequence_owner format a15
col CNT format 9999
SELECT SEQUENCE_OWNER,COUNT(SEQUENCE_NAME) CNT FROM DBA_SEQUENCES
WHERE SEQUENCE_OWNER IN (‘MYAPP1’,
‘MYAPP2’,
‘MYAPP3’,
‘MYAPP4’,
‘MYAPP5’,
‘MYAPP6’,
‘MYAPP7’)
GROUP BY SEQUENCE_OWNER
ORDER BY SEQUENCE_OWNER;

SEQUENCE_OWNER CNT
————— —–
MYAPP4 109
MYAPP5 112
MYAPP6 151
MYAPP7 152
MYAPP1 152
MYAPP2 152
MYAPP3 151

COL OWNER FORMAT A15
COL CNT FORMAT 9999
SELECT OWNER,COUNT(DISTINCT NAME)CNT FROM DBA_SOURCE
WHERE OWNER IN (‘MYAPP1’,
‘MYAPP2’,
‘MYAPP3’,
‘MYAPP4’,
‘MYAPP5’,
‘MYAPP6’,
‘MYAPP7’)
GROUP BY OWNER
ORDER BY OWNER;

OWNER CNT
————— —–
MYAPP4 5
MYAPP5 1
MYAPP6 1
MYAPP7 1
MYAPP1 3
MYAPP2 1
MYAPP3 3

COL OWNER FORMAT A15
COL CNT FORMAT 9999
SELECT OWNER,COUNT(JOB_NAME) FROM DBA_SCHEDULER_JOBS
WHERE OWNER IN (‘MYAPP1’,
‘MYAPP2’,
‘MYAPP3’,
‘MYAPP4’,
‘MYAPP5’,
‘MYAPP6’,
‘MYAPP7’)
GROUP BY OWNER
ORDER BY OWNER;

COL OWNER FORMAT A15
COL CNT FORMAT 9999
SELECT OWNER,COUNT(SYNONYM_NAME) FROM DBA_SYNONYMS
WHERE OWNER IN (‘MYAPP1’,
‘MYAPP2’,
‘MYAPP3’,
‘MYAPP4’,
‘MYAPP5’,
‘MYAPP6’,
‘MYAPP7’)
GROUP BY OWNER
ORDER BY OWNER;

Step-18# Check Invalid objects

SQL> select object_name,owner from dba_objects where status != ‘VALID’;

OBJECT_NAME OWNER
——————————————————————————–
TBLRADIUSCUSTOMER_SEQ_TRIGGER MYAPP1
TBLRADIUSCUSTOMER_SEQ_TRIGGER MYAPP3

EXEC DBMS_UTILITY.COMPILE_SCHEMA(MYAPP1);
EXEC DBMS_UTILITY.COMPILE_SCHEMA(MYAPP2);

SQL> select object_name,owner from dba_objects where status != ‘VALID’;
no rows selected

Conclusion:
Above This article has showed that Oracle Transportable Tablespaces Solution has been used as data migrate between cross platform (like solari(big) to linux(little) endian format) and cross database version (from source DB 11g v11.2.0.3.0 to target DB 12c v12.1.0.2.0.)

References:
Oracle11g Oracle® Database Administrator’s Guide have–> 14 Managing Tablespaces–> Transporting Tablespaces Between Databases
Oracle12c Database Administrator’s Guide15 Transporting Data
https://community.oracle.com/thread/958704?start=0&tstart=0
https://community.oracle.com/thread/2277648?tstart=0
https://community.oracle.com/thread/2277648?tstart=0

About Author:
Hitesh Gondalia
Working as Sr. Database Administrator having 8.6 years of experience with good exposure of handling the Production Database Challenges of Telecom, Banking and Finance Applications
OCE Certified in RACv11g
OCP Certified in v12c/11g/10g/9i
CCNA Certified
OTN Community – Master Tag
https://hiteshgondalia.wordpress.com

[client(17856)]CRS-2101:The OLR was formatted using version 3.

Environment
Grid 11.2.0.4.0
RHEL 7.1

#] /u01/app/11.2.0/grid/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME=  /u01/app/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of “dbhome” have not changed. No need to overwrite.
The contents of “oraenv” have not changed. No need to overwrite.
The contents of “coraenv” have not changed. No need to overwrite.

Creating /etc/oratab file…
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation
Installing Trace File Analyzer
OLR initialization – successful
root wallet
root wallet cert
root cert export
peer wallet
profile reader wallet
pa wallet
peer wallet keys
pa wallet keys
peer cert request
pa cert request
peer cert
pa cert
peer root cert TP
profile reader root cert TP
pa root cert TP
peer pa cert TP
pa peer cert TP
profile reader pa cert TP
profile reader peer cert TP
peer user cert
pa user cert
Adding Clusterware entries to inittab
ohasd failed to start
Failed to start the Clusterware. Last 20 lines of the alert log follow:
2015-12-08 10:20:24.544:
[client(17856)]CRS-2101:The OLR was formatted using version 3.

Solution

Follow the Oracle MetaLink Document

Install of Clusterware fails while running root.sh on OL7 – ohasd fails to start (Doc ID 1959008.1)

🙂

[INS-06006] Passwordless SSH connectivity not set up between the following nodes

Environment
GI – 11.2.0.4.0
RHEL – 6.7

Untitled

Learning:
Hostname should be use either in small letter or capital letter do not mix it.

Solution:
11.2.0.4 runInstaller: [INS-06006] Passwordless SSH connectivity not set up between the following nodes(s) (Doc ID
1597212.1)

11g R2 clusterware and ASM, which one gets up first and which one depends on other?

In Oracle 10g RAC and 11gR1 RAC, Oracle clusterware and ASM are installed
in the different Oracle homes, and the Clusterware has to be up before ASM
instance can be started because ASM instance uses the clusterware to access
the shared storage.
Oracle 11g R2 introduced the grid infrastructure home which combines Oracle
clusterware and ASM.
The OCR and voting disk of 11g R2 clusterware can be stored in ASM.
So it seems that ASM needs the clusterware up first to access the shared
storage  and the clusterware needs ASM up first before it can access its
key data structure: OCR and voting disk.
So really clusterware and ASM, which one needs to be up first, and which
one has to wait for other? This seemed to be the chicken or the ego problem.
Oracle’s solution to this problem is to combines  the clusterware and ASM
into a single Grid Infrastructure home and  comes up a  procedure with  a
complex  start up sequence which  mixes  the different components of clusterware
and ASM  instance in order.
clusterware startup command  $GI_HOME/bin/crsctl start crs follows this
sequence to bring both clusterware and ASM online
From this sequence of the log message and timestamp, we get some understanding
about the sequence of clusterware and ASM instance:
1)	CSSD and CTSSD are up before ASM
2)	Voting disks used by CSSD are discovered by reading the header of
the disks, not through ASM
3)	Startup of CRS service has to wait until ASM instance is up and the
diskgroup for OCR and voting disk is mounted.
4)	EVEMD and ACFS are up after the ASM starts.

https://kyuoracleblog.wordpress.com/2011/01/19/in-11g-r2-rac-clusterware-and-asm-which-one-is-up-first-and-which-one-is-depending-on-other/

gpnptool

In cluster environment, the location of  SPfile for ASMread from GPnP profile.
[grid@host01 peer]$ gpnptool getpval -asm_spf
Warning: some command line parameters were defaulted. Resulting command line:
         /u01/app/11.2.0/grid/bin/gpnptool.bin getpval -asm_spf -p=profile.xml -o-
+DATA/cluster01/asmparameterfile/registry.253.793721441
The oputput of the query shows that SPfile is on ASM in DATA diskgroup. To find out the
 location of ASM disks, following query is issued :
[root@host01 peer]# gpnptool getpval -asm_dis
ASM-Profile id=”asm” DiscoveryString=””
The  device headers of every device in the disk string returned by the above query are scanned  (if configured by you at ASM initial setup time). Here Discovery String is blank is as ASMDISKSTRINGS parameter has not been set. Hence, headers of all the ASM disks are scanned .
Here, I have shown the output of the query only on the disk which contains SPfile.(spfflg is not null)
[root@host01 ~]#  kfed read /dev/sdb3 | grep -E ‘spf|ausize’
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.spfile:                       16 ; 0x0f4: 0x00000010
kfdhdb.spfflg:                        1 ; 0x0f8: 0x00000001
In the output above, we see that
     the device /dev/sdb3 contains a copy of the ASM spfile (spfflg=1).
     The ASM spfile location starts at the disk offset of 16 (spfile=16)
Considering the allocation unit size (kfdhdb.ausize = 1M), let’s dump the ASM spfile from the device:
[root@host01 ~]#  dd if=/dev/sdb3 of=spfileASM_Copy2.ora skip=16  bs=1M count=1
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.170611 seconds, 6.1 MB/s
[root@host01 ~]# strings spfileASM_Copy2.ora
+ASM1.__oracle_base=’/u01/app/grid’#ORACLE_BASE set from in memory value
+ASM2.__oracle_base=’/u01/app/grid’#ORACLE_BASE set from in memory value
+ASM3.__oracle_base=’/u01/app/grid’#ORACLE_BASE set from in memory value
+ASM3.asm_diskgroups=’FRA’#Manual Mount
+ASM2.asm_diskgroups=’FRA’#Manual Mount
+ASM1.asm_diskgroups=’FRA’#Manual Mount
*.asm_power_limit=1
*.diagnostic_dest=’/u01/app/grid’
*.instance_type=’asm’
*.large_pool_size=12M
*.remote_login_passwordfile=’EXCLUSIVE’
Using the parameters in SPfile, ASM is started.
Once ASM is up, OCR is read by CRSD and various resources on the node are started.
Each node reads network information in GPnP profile and using GNS,  negotiates appropriate network identity for itself . Hence, nodes can be dynamically added/deleted.

http://oracleinaction.com/gpnp-profile/

Whenever GPnP profile is not present, it is automatically copied from existing nodes by GPnPd as pending.xml . Whenever, profile is updated, it is automatically renamed to profile.xml.

http://oracleinaction.com/lose-gpnp-profile/