Migrating an Oracle 12c Database to a 18c Pluggable Database in the Oracle Cloud


Testing transport tablespace - Migrating an Oracle 12c Database to a 18c Pluggable Database in the Oracle Cloud



++++++++++++++++++++++
On-premises 12cR2 database 

++++++++++++++++++++++

Create a tablespace
CREATE SMALLFILE TABLESPACE TESTTBS
    DATAFILE
        '/opt/oracle/oradata/TSOC/tstdb01.dbf' SIZE 300M AUTOEXTEND ON NEXT 1 MAXSIZE 500M SEGMENT SPACE MANAGEMENT AUTO
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
           
Tablespace created.

Create the user test in the tablespace

SYS@TSOC > CREATE USER test IDENTIFIED BY test

DEFAULT TABLESPACE "TESTTBS"
TEMPORARY TABLESPACE "TEMP";  2    3

User created.

SYS@TSOC >  ALTER USER test QUOTA UNLIMITED ON TESTTBS;

User altered.

SYS@TSOC > GRANT CONNECT, RESOURCE to test;

Grant succeeded.



 Create a TEST TABLE
 SYS@TSOC > CREATE TABLE test.test01 (id number, name varchar2(100));

Table created.

SYS@TSOC > INSERT INTO test.test01 (ID, NAME) VALUES ('1', 'DOE');
 INSERT INTO test.test01 (ID, NAME) VALUES ('2', 'SOE');
 INSERT INTO test.test01 (ID, NAME) VALUES ('3', 'TOE');
 INSERT INTO test.test01 (ID, NAME) VALUES ('3', 'TOE');
 INSERT INTO test.test01 (ID, NAME) VALUES ('3', 'TOE');
1 row created.

SYS@TSOC >
1 row created.

SYS@TSOC >
1 row created.

SYS@TSOC >
1 row created.

SYS@TSOC >

1 row created.


Create required directories for export dump

SYS@TSOC > CREATE OR REPLACE DIRECTORY EXP_DUMP as '/opt/oradata/export/';


Directory created.

SYS@TSOC > GRANT READ, WRITE ON DIRECTORY EXP_DUMP to sys;

Grant succeeded.

SYS@TSOC > ALTER TABLESPACE TESTTBS READ ONLY;

Tablespace altered.



Take a dump for the transport_tablespaces=TESTTBS

At the command line prompt, run the Data Pump Export utility expdp as system.

expdp system@TSOC dumpfile=exporttest01.dmp logfile=export01.log directory=EXP_DUMP transport_tablespaces=TESTTBS transport_full_check=y reuse_dumpfile=y

Export: Release 12.1.0.2.0 - Production on Mon Mar 5 10:56:47 2018

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, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/********@TSOC dumpfile=exporttest01.dmp logfile=export01.log directory=EXP_DUMP transport_tablespaces=testtbs transport_full_check=y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
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:
  /opt/oradata/export/exporttest01.dmp
******************************************************************************
Datafiles required for transportable tablespace TESTTBS:
  /opt/oracle/oradata/TSOC/tstdb01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Mon Mar 5 10:57:53 2018 elapsed 0 00:00:51

scp dump file and datafile to the cloud server

++++++++++++++++++++++++
On Cloud Server 18c database:
+++++++++++++++++++++++++

Create the required directories
SQL> CREATE OR REPLACE DIRECTORY CLOUD_EXP as '/u02/app/oracle/oradata/QMON1/expcld/';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY CLOUD_EXP to sys;

Grant succeeded.

SQL> CREATE USER test IDENTIFIED BY test;

User created.

SQL> GRANT CONNECT, RESOURCE to test;

Grant succeeded.

Import tablespace and contents to the cloud pdb

$ impdp system@PDB1 dumpfile=exporttest01.dmp directory=CLOUD_EXP transport_datafiles=/u02/app/oracle/oradata/QMON1/PDB1/exporttest01.dmp

Import: Release 18.0.0.0.0 - Production on Mon Mar 5 04:38:00 2018
Version 18.1.0.0.0

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

Connected to: Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/********@PDB1 dumpfile=exporttest01.dmp directory=CLOUD_EXP transport_datafiles=/u02/app/oracle/oradata/QMON1/PDB1/exporttest01.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Mon Mar 5 04:38:33 2018 elapsed 0 00:00:25

Connect to the Pluggable database and validate
$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 Production on Mon Mar 5 04:41:29 2018
Version 18.1.0.0.0

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


Connected to:
Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> ALTER SESSION SET CONTAINER = PDB1;

Session altered.

SQL> ALTER TABLESPACE TESTTBS READ WRITE;

Tablespace altered.

SQL> select tablespace_name from dba_tablespaces;

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

6 rows selected.

SQL> select count (*) from test.test01;

  COUNT(*)
----------
         5


Comments

Post a Comment

Popular posts from this blog

Oracle DataGuard – How to resize Redo Log and Standby Redo Log

Oracle DataGuard – How to resize Redo Log and Standby Redo Log set linesize 200 col dest_name for a30 select DEST_NAME,STATUS,PROTECTION_MODE,ARCHIVED_SEQ#,APPLIED_SEQ# from v$archive_dest_status; On Standby server - make file management parameter to manual if not: show parameter standby; alter system set standby_file_management=manual; ================== +     On Primary             + ==================  SQL> select group#,thread#,status from v$log; col member for a60 set linesize 200 select member,group#,status from v$logfile; SQL> select group#,thread#,status from v$log;     GROUP#    THREAD# STATUS ---------- ---------- ----------------          1          1 ACTIVE          2       ...
Read more

Exadata - Smart Scan Testing

Image
Exadata - SMART SCAN testing -------------------------------------------- Samrt scan works with cell offloading which relate to offloaded the sql processing from the database layer to the cell (storage) layer. Smart scan is the access mechanism to offload data from storage layer. For example passing only required data blocks after applying predicate filters at the cell layer instead of sending every possible blocks from cell server to database server.                           Figure 1: Traditional Database Storage I/O and SQL Processing Model   Process Steps: ------------------- 1.    The client issues a SELECT statement . 2.    The database kernel maps this request to the database files and extents which contain the table data. 3.    The database kernel issues the I/Os to read all the table bloc...
Read more

Exadata Flash Cache - Management

Total size of Flash CellCLI>  list flashcache attributes effectiveCacheSize          5.82122802734375T Flashcache Mode:  [root@Cellc01-adm ~]# su - celladmin cellcli CellCLI>  list cell attributes flashCacheMode,status          WriteThrough    online > list celldisk where disktype=flashdisk > list physicaldisk FLASH_1_1 detail List PhysicalDisk for FlashCache: CellCLI> list physicaldisk attributes name, disktype, makemodel, physicalrpm, physicalport, status      8:0           HardDisk      "HGST    H7240AS60SUN4.0T"                              normal      8:1  ...
Read more