In this demo, we will see how to use transportable tablespace feature in expdp to migrate entire database from 11g non-cdb dataases to 12c pluggable database.
Ø Oracle12c has come up with new feature to use transportable tablespace for entire system/database which simplifies the migration.
Ø Transportable tablespaces mechanism is used to move user and application data i.e., datafiles containing user and application data are physically copied to target. This results in faster migration.
Ø The parameters for expdp used for this migration are
a. TRANSPORTABLE_TABLESPACE=ALWAYS
b. FULL=Y
c. VERSION=12 . This is because our destination system is 12c version (12.1) or later version. If the source database is 12c (12.0), then no need to give this parameter.
Source System
Database: Oracle 11.2.0.3 – 64 bit
OS – RHEL 5.4 64 bit.
Database name =noasmdb
Destination System
Database : Oracle 12.1.0.1
OS – OEL 6.5 64 bit.
DB Database Name:
PDB database name:
Steps
1. Create a directory in source database to store the export dump files.
2. Set the user and application tablespace in the source database as READ ONLY
3. Export the source database using expdp with parameters version=12.0, transportable=always and full=y
4. Copy the dumpfile and datafiles for tablespaces containing user /application data.
5. Create a new PDB in the destination CDB using create pluggable database command.
6. Create a directory in the destination PDB pointing to the folder containing the dump file or create a directory for dump file and move the dump file there.
7. Create an entry in tnsnames.ora for the new PDB.
8. Import in to the target using impdp with parameters FULL=Y and TRANSPORT_DATAFILES parameters. Make sure, the account is having IMP_FULL_DATABASE.
9. Restore the tablespaces to READ-WRITE in source database.
On source system
1. SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit 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
2. Create a directory for data dump.
SQL> create directory DPDUMP as '/data1/noasmdb/dump';
Directory created.
SQL> grant read,write on directory DPDUMP to sys;
Grant succeeded.
3. Get the list of tablespaces in source database.
SQL> select tablespace_name from dba_Tablespaces;
TABLESPACE_NAME
--------------------
SYSTEM
SYSAUX
TEMP
USERS
UNDOTBS
TEST_BKP
NEWTEST
NOVALIDTBS
BLKCORRUPT
4. As you can see, the exp did not start as the tablespaces are not in READ ONLY mode.
[oracle@vm1 dump]$ expdp \'sys/sys as sysdba\' dumpfile=fulltt.dmp logfile=fulltt.log directory=DPDUMP full=y transportable=always version=12.0
Export: Release 11.2.0.3.0 - Production on Wed Apr 9 14:37:29 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01": "sys/******** AS SYSDBA" dumpfile=fulltt.dmp logfile=fulltt.log full=y transportable=always version=12.0
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 'BLKCORRUPT' is not read only
ORA-29335: tablespace 'NEWTEST' is not read only
ORA-29335: tablespace 'NOVALIDTBS' is not read only
ORA-29335: tablespace 'TEST_BKP' is not read only
ORA-29335: tablespace 'USERS' is not read only
Job "SYS"."SYS_EXPORT_FULL_01" stopped due to fatal error at 14:39:00
5. Change the tablespaces to READ ONLY mode.
alter tablespace USERS read only;
alter tablespace TEST_BKP read only;
alter tablespace NEWTEST read only;
alter tablespace NOVALIDTBS read only;
alter tablespace BLKCORRUPT read only;
6. Now, issue the Export command.
expdp \'sys/sys as sysdba\' directory=DPDUMP dumpfile=fulltt.dmp logfile=fulltt.log full=y transportable=always version=12.0
[oracle@vm1 dump]$ expdp \'sys/sys as sysdba\' directory=DPDUMP dumpfile=fulltt.dmp logfile=fulltt.log full=y transportable=always version=12.0
Export: Release 11.2.0.3.0 - Production on Wed Apr 9 14:51:54 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 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01": "sys/******** AS SYSDBA" directory=DPDUMP dumpfile=fulltt.dmp logfile=fulltt.log full=y transportable=always version=12.0
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
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 106.4 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
………………………………………………………….
…………………………………………………………………………..
………………………………………………………………………………………..
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
/data1/noasmdb/dump/fulltt.dmp
******************************************************************************
Datafiles required for transportable tablespace BLKCORRUPT:
/data1/noasmdb/datafile/blkcorrupt.dbf
Datafiles required for transportable tablespace NEWTEST:
/data1/noasmdb/datafile/newtest01.dbf
Datafiles required for transportable tablespace NOVALIDTBS:
/data1/noasmdb/datafile/novalidtbs01.dbf
Datafiles required for transportable tablespace TEST_BKP:
/data1/noasmdb/datafile/testbkp01.dbf
Datafiles required for transportable tablespace USERS:
/data1/noasmdb/datafile/users01.dbf
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 16:15:31
7. Copy the datafiles above to the destination server.
scp /data1/noasmdb/datafile/blkcorrupt.dbf oracle@10.10.1.20 :<path of the new pdb datafiles>
scp /data1/noasmdb/datafile/newtest01.dbf oracle@10.10.1.20 : :<path of the new pdb datafiles>
scp /data1/noasmdb/datafile/novalidtbs01.dbf oracle@10.10.1.20 : :<path of the new pdb datafiles>
scp /data1/noasmdb/datafile/testbkp01.dbf oracle@10.10.1.20 : :<path of the new pdb datafiles>
scp /data1/noasmdb/datafile/users01.dbf oracle@10.10.1.20 : :<path of the new pdb datafiles>
Destination database
8. Checking the version in destination database.
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
PL/SQL Release 12.1.0.1.0 - Production 0
CORE 12.1.0.1.0 Production 0
TNS for Linux: Version 12.1.0.1.0 - Production 0
NLSRTL Version 12.1.0.1.0 - Production 0
9. Check for the datafiles in destination database.
SQL> select file_name from cdb_data_files;
FILE_NAME
---------------------------------------------
/data/orcl/pdbseed/sysaux01.dbf[n1]
/data/orcl/system01.dbf
/data/orcl/sysaux01.dbf
/data/orcl/undotbs01.dbf
/data/orcl/users01.dbf
Let us query and see the name of the container database.
SQL> select name,cdb,con_id from v$database;
NAME CDB CON_ID
--------- --- ----------
ORCL YES 0
10. Let us check do we have any pluggable database existing already.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL MOUNTED
11. Now, let us create a new pluggable database from the PDB$SEED database using file name convert
SQL> create pluggable database pdb1_orcl admin user pdbadmin identified by pdb1admin file_name_convert=('pdbseed','pdb1_orc1')
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL MOUNTED
4 PDB1_ORCL MOUNTED
12. Now, let us open the PDB1_ORCL database and see the datafiles.
SQL> alter pluggable database pdb1_orcl open;
Pluggable database altered.
13. See what are the tablespaces and datafiles are created for pdb1_orcl
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL MOUNTED
4 PDB1_ORCL READ WRITE NO
SQL> select file_name from cdb_data_files where con_id=4;
FILE_NAME
---------------------------------------------
/data/orcl/pdb1_orc1/system01.dbf
/data/orcl/pdb1_orc1/sysaux01.dbf
14. Let us create a new directory in PDB1_ORCL for the dump file .
Mkdir –p /u01/app/oracle/dump
SQL> alter session set container=pdb1_orcl;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
PDB1_ORCL
SQL> create directory DPUMP as '/u01/app/oracle/dump';
Directory created.
SQL> grant read,write on directory DPUMP to sys;
Grant succeeded.
15. Move the dump file to this path.
scp /data1/noasmdb/dump/ttfull.dmp oracle@10.10.1.20: /u01/app/oracle/dump
16. Create a tns entry for this pluggable database in TNSNAMES.ora file.
PDB1ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.1.20)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1_orcl.localdomain)
)
)
17. Now, invoke the import command on the destination PDB with following parameters.
a. Full=Y and
b. TRANSPORT_DATAFILES (specify the list of user tablespace datafiles to be transported)
fulltt.par
full=y transport_datafiles='/data/orcl/pdb1_orc1/blkcorrupt.dbf','/data/orcl/pdb1_orc1/newtest01.dbf',
'/data/orcl/pdb1_orc1/novalidtbs01.dbf',
'/data/orcl/pdb1_orc1/testbkp01.dbf',
'/data/orcl/pdb1_orc1/users01.dbf' remap_tablespace=users:users_pdb1orcl
impdp \'sys/sys@pdb1orcl as sysdba\' directory=DPUMP dumpfile=/u01/app/oracle/dump/fulltt.dmp logfile=/u01/app/oracle/dump/fulltt_imp.log parfile=fulltt.par
ORA-39082: Object type TRIGGER:"SYSMAN"."MGMT_CREDS_INS_UPD" created with compilation warnings
Job "SYS"."SYS_IMPORT_FULL_01" completed with 672 error(s) at Thu Apr 10 13:20:04 2014 elapsed 0 01:59:37
18. Now, if you query for the datafiles , we can see the new datafiles would be showing in pluggable database pdb1_orcl
SQL> col file_name format a45
SQL> select file_name from cdb_data_files;
FILE_NAME
---------------------------------------------
/data/orcl/pdb1_orc1/sysaux01.dbf
/data/orcl/pdb1_orc1/system01.dbf
/data/orcl/pdb1_orc1/blkcorrupt.dbf
/data/orcl/pdb1_orc1/newtest01.dbf
/data/orcl/pdb1_orc1/novalidtbs01.dbf
/data/orcl/pdb1_orc1/testbkp01.dbf
/data/orcl/pdb1_orc1/users01.dbf
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
BLKCORRUPT ONLINE
NEWTEST ONLINE
NOVALIDTBS ONLINE
TEST_BKP ONLINE
USERS_PDB1ORCL ONLINE
SQL> conn bctest/bctest@pdb1orcl
Connected.
SQL> select tname from tab;
TNAME
---------------
INDTEST
PRODUCT
SUPPLIER
C
P
TESTIND
SMALL
TEST_SPLIT
CF_TEST
TEST_UPDATE
BLTST
RANGE_SALES
19. Now bring all the tablespaces in source database to read-write mode.
alter tablespace users read write;
alter tablespace blkcorrupt read write;
alter tablespace novalidtbs read write;
alter tablespace test_bkp read write;
No comments:
Post a Comment