آسان ترین راه برای مهاجرت از 11g به 12c Multitenant mode

سلام خدمت دوستان عزیز امروز میخواهم در مورد ساده ترین راه ( استفاده از دستورات پرکاربرد و آشنا برای شما نه لزوما بهترین و کم هزینه ترین روش ) مهاجرت از 11g به 12c pdb بگویم پس با من همراه باشید تا با یک سناریو این کار را انجام دهیم:

 

• وضعیت جاری:

Oracle 11g:

 

Hostname: RestoreDB

SID: RCIS

Schema:mas11g

 

 

Oracle 12c:

 

Hostname: oel7

SID:  DB12C

pdb: mas12c

 

• در پایگاه داده و ماشین مبدا (11g)

در این جا فرض میکنیم که در سایت عملیاتی schema اصلی ما mas11g هست و نیاز هست پایگاه داده جدید این schema را دارا باشد. پس در اولین قدم برای شروع سناریو یک schema را می سازیم:

[oracle@RestoreDB ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 16 10:11:25 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SYS@RCIS> def;
DEFINE _DATE           = "16-JAN-18" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "RCIS" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR)
DEFINE _EDITOR         = "ed" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000400" (CHAR)

SYS@RCIS> create tablespace mas11g;

Tablespace created.

SYS@RCIS> select ts#, name from v$tablespace where name='MAS11G';

       TS# NAME
---------- ------------------------------
         9 MAS11G

1 rows selected.

SYS@RCIS> select name from v$datafile where ts#=9;

NAME
--------------------------------------------------------------------------------
+DATA/rcis/datafile/mas11g.422.965556953

SYS@RCIS> create user mas11g account unlock identified by mas default tablespace mas11g temporary tablespace temp quota unlimited on mas11g;

User created.

SYS@RCIS> grant create session, create table, connect, create sequence to mas11g;

Grant succeeded.

SYS@RCIS> disc;

@> conn mas11g/mas
Connected.
MAS11G@RCIS> create table masoud (c1 number primary key, c2 date);

Table created.

MAS11G@RCIS> insert into masoud values (1, sysdate);

1 row created.

MAS11G@RCIS> insert into masoud values (2, sysdate);

1 row created.

MAS11G@RCIS> commit;

Commit complete.

MAS11G@RCIS> create sequence mas_seq start with 3;

Sequence created.

بسیار خوب حالا یک dump از پایگاه داده 11g از schema مورد نظر میگیریم:

[oracle@RestoreDB ~]$ expdp schemas=mas11g dumpfile=mas11g.dmp logfile=mas11g.log

Export: Release 11.2.0.4.0 - Production on Tue Jan 16 10:50:04 2018

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA schemas=mas11g dumpfile=mas11g.dmp logfile=mas11g.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "MAS11G"."MASOUD"                           5.437 KB       3 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/RCIS/dpdump/mas11g.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jan 16 10:50:25 2018 elapsed 0 00:00:15

دقت کنید در دستور schema آورده ایم یعنی تمام object ها ای که در این schema وجود دارد. سپس فایل dump را به ماشین مقصد oel7 منتقل میکنیم:

scp /u01/app/oracle/admin/RCIS/dpdump/mas11g.dmp oracle@oel7:/tmp

 

 

• درپایگاه داده و ماشین مقصد (12c):

اول وضعیت پایگاه داده مقصد را بررسی میکنیم:

[oracle@oel7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 16 12:31:52 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> def;
DEFINE _DATE           = "16-JAN-18" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "db12c" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)
DEFINE _O_RELEASE      = "1202000100" (CHAR)
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MYPDB                          READ WRITE NO
SQL>

همانگونه که میبینید پایگاه داده ما تنها یک pdb قابل استفاده دارد به نام mypdb که برای یکی دیگر از بیزینس های ما در حال استفاده است. حالا برای پایگاه داده جدید یک pdb میسازیم و در نظر داریم که OMF نباشد:

SQL> select name from v$datafile where con_id=2;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/db12c/pdbseed/system01.dbf
/u01/app/oracle/oradata/db12c/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/db12c/pdbseed/undotbs01.dbf

SQL> create pluggable database mas12c admin user pdb_mas identified by mas
  2  file_name_convert=('/u01/app/oracle/oradata/db12c/pdbseed/','/u01/app/oracle/oradata/db12c/mas12c/');

Pluggable database created.

SQL> select name from v$datafile where con_id=4;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/db12c/mas12c/system01.dbf
/u01/app/oracle/oradata/db12c/mas12c/sysaux01.dbf
/u01/app/oracle/oradata/db12c/mas12c/undotbs01.dbf

SQL> alter pluggable database mas12c open;

Pluggable database altered.

SQL> sho pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MYPDB                          READ WRITE NO
         4 MAS12C                         READ WRITE NO

SQL> select name from v$active_services;

NAME
----------------------------------------------------------------
mas12c
db12c
SYS$BACKGROUND
SYS$USERS
db12cXDB
mypdb

6 rows selected.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@oel7 ~]$ lsnrctl  status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-JAN-2018 12:50:04

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel7)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                13-JAN-2018 12:29:24
Uptime                    3 days 0 hr. 20 min. 40 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oel7/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel7)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oel7)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/db12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "62a5b72ff0115788e05380efa8c0a311" has 1 instance(s).
  Instance "db12c", status READY, has 1 handler(s) for this service...
Service "62e227d3146a2b60e05380efa8c0bc3a" has 1 instance(s).
  Instance "db12c", status READY, has 1 handler(s) for this service...
Service "db12c" has 1 instance(s).
  Instance "db12c", status READY, has 1 handler(s) for this service...
Service "db12cXDB" has 1 instance(s).
  Instance "db12c", status READY, has 1 handler(s) for this service...
Service "mas12c" has 1 instance(s).
  Instance "db12c", status READY, has 1 handler(s) for this service...
Service "mypdb" has 1 instance(s).
  Instance "db12c", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oel7 ~]$

همانگون که مشاهده نمودید pdb mas12c ایجاد شده و سرویس آن نیز قابل استفاده می باشد:

 

به نظر من بهترین راه وصل شدن به pdb ها استفاده از tns میباشد پس برای mas12c نیز یک tns میسازیم و به آن متصل میشویم:

vi $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_DB12C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521))


DB12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db12c)
    )
  )

MYPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mypdb)
    )
  )

# new masoud's pdb
MAS12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mas12c)
    )
  )
[oracle@oel7 ~]$ tnsping mas12c 3

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 16-JAN-2018 12:58:38

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.2/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mas12c)))
OK (0 msec)
OK (0 msec)
OK (0 msec)
[oracle@oel7 ~]$ sqlplus sys@mas12c as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 16 12:59:27 2018

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
SQL>
SQL> def;
DEFINE _DATE           = "16-JAN-18" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "mas12c" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)
DEFINE _O_RELEASE      = "1202000100" (CHAR)
SQL>

بسیار خوب الان آماده هستیم که dump  را بر روی pdb جدید یعنی mas12c برگردانیم. پس شروع میکنیم:

 

اول یک directory برای import یا انتخاب میکنیم یا میسازیم. لازم به ذکر است در pdb ها از چیزی به نام GUID برای unique ساختن pdb ها استفاده میشود که در نام مسیر OMF فایل ها و همچنین directory پیشفرض استفاده میشود:

 SQL> select GUID from v$containers where name='MAS12C';

GUID
--------------------------------
62E227D3146A2B60E05380EFA8C0BC3A

SQL> select DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------
/u01/app/oracle/admin/db12c/dpdump/62E227D3146A2B60E05380EFA8C0BC3A

ما میخواهیم از directory پیشفرض استفاده کنیم پس فقط اطمینان حاصل میکینم که مسیر وجود داشته باشد:

SQL> ! mkdir -p /u01/app/oracle/admin/db12c/dpdump/62E227D3146A2B60E05380EFA8C0BC3A

SQL> ! ls -ltr /u01/app/oracle/admin/db12c/dpdump/
total 4
-rw-r-----. 1 oracle oinstall 116 Jan 13 12:34 dp.log
drwxr-x---. 2 oracle oinstall   6 Jan 14 09:33 62A5B72FF0115788E05380EFA8C0A311
drwxr-x---. 2 oracle oinstall   6 Jan 16 13:18 62E227D3146A2B60E05380EFA8C0BC3A

الان import میکنیم:

[oracle@oel7 tmp]$ cp mas11g.dmp $ORACLE_BASE/admin/db12c/dpdump/62E227D3146A2B60E05380EFA8C0BC3A/

[oracle@oel7 tmp]$ sqlplus sys@mas12c as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 16 13:34:42 2018

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create tablespace mas12c datafile '/u01/app/oracle/oradata/db12c/mas12c/mas12c01.dbf' size 100m autoextend on;

Tablespace created.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@oel7 tmp]$ impdp schemas=mas11g dumpfile=mas11g.dmp remap_tablespace=MAS11G:mas12c;

Import: Release 12.2.0.1.0 - Production on Tue Jan 16 13:33:18 2018

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

Username: sys@mas12c as sysdba
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  sys/********@mas12c AS SYSDBA schemas=mas11g dumpfile=mas11g.dmp remap_tablespace=MAS11G:mas12c
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MAS11G"."MASOUD"                           5.437 KB       3 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Tue Jan 16 13:33:36 2018 elapsed 0 00:00:08

به نظر میاد که همه چی به درستی انجام شده، الان وقت آن هست که بررسی کنیم:

Alert log:
#########
create pluggable database mas12c admin user pdb_mas identified by *
file_name_convert=('/u01/app/oracle/oradata/db12c/pdbseed/','/u01/app/oracle/oradata/db12c/mas12c/')
2018-01-16T12:44:26.729345+03:30
PDB$SEED(2): AUDSYS.AUD$UNIFIED (SQL_TEXT) - CLOB populated
2018-01-16T12:44:35.081448+03:30
Warning: VKTM detected a forward time drift.
Please see the VKTM trace file for more details:
/u01/app/oracle/diag/rdbms/db12c/db12c/trace/db12c_vktm_23694.trc
2018-01-16T12:44:48.237126+03:30
MAS12C(4):Endian type of dictionary set to little
****************************************************************
Pluggable Database MAS12C with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x00000000000000e1
****************************************************************
2018-01-16T12:44:51.934213+03:30
MAS12C(4):Autotune of undo retention is turned on.
2018-01-16T12:44:58.236021+03:30
MAS12C(4):[76640] Successfully onlined Undo Tablespace 2.
MAS12C(4):Undo initialization finished serial:0 start:264185920 end:264186491 diff:571 ms (0.6 seconds)
MAS12C(4):Database Characterset for MAS12C is AL32UTF8
MAS12C(4):JIT: pid 76640 requesting stop
2018-01-16T12:45:02.884741+03:30
Completed: create pluggable database mas12c admin user pdb_mas identified by *
file_name_convert=('/u01/app/oracle/oradata/db12c/pdbseed/','/u01/app/oracle/oradata/db12c/mas12c/')
2018-01-16T12:47:40.722781+03:30
alter pluggable database mas12c open
MAS12C(4):Autotune of undo retention is turned on.
2018-01-16T12:47:40.858347+03:30
MAS12C(4):Endian type of dictionary set to little
2018-01-16T12:47:44.292991+03:30
MAS12C(4):[76640] Successfully onlined Undo Tablespace 2.
MAS12C(4):Undo initialization finished serial:0 start:264350366 end:264352556 diff:2190 ms (2.2 seconds)
MAS12C(4):Deleting old file#5 from file$
MAS12C(4):Deleting old file#6 from file$
MAS12C(4):Deleting old file#8 from file$
MAS12C(4):Adding new file#13 to file$(old file#5)
MAS12C(4):Adding new file#14 to file$(old file#6)
MAS12C(4):Adding new file#15 to file$(old file#8)
2018-01-16T12:47:45.465437+03:30
MAS12C(4):Successfully created internal service mas12c at open
****************************************************************
Post plug operations are now complete.
Pluggable database MAS12C with pdb id - 4 is now marked as NEW.
****************************************************************
MAS12C(4):Database Characterset for MAS12C is AL32UTF8
2018-01-16T12:47:47.163318+03:30
MAS12C(4):Opatch validation is skipped for PDB MAS12C (con_id=0)
2018-01-16T12:48:14.999355+03:30
MAS12C(4):Opening pdb with no Resource Manager plan active
2018-01-16T12:48:18.120379+03:30
Pluggable database MAS12C opened read write
Completed: alter pluggable database mas12c open
2018-01-16T13:08:26.870085+03:30
MAS12C(4):Resize operation completed for file# 14, old size 337920K, new size 358400K
2018-01-16T13:27:02.656796+03:30

Schema Objets:
#########
[oracle@oel7 ~]$ sqlplus mas11g@mas12c

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 16 13:51:55 2018

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

Enter password:
Last Successful login time: Tue Jan 16 2018 13:51:49 +03:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> def;
DEFINE _DATE           = "16-JAN-18" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "mas12c" (CHAR)
DEFINE _USER           = "MAS11G" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)
DEFINE _O_RELEASE      = "1202000100" (CHAR)
SQL> select object_name, object_type from user_objects order by object_type;

OBJECT_NAME                              OBJECT_TYPE
---------------------------------------- --------------------------------------------------
SYS_C007400                              INDEX
MAS_SEQ                                  SEQUENCE
MASOUD                                   TABLE

SQL> select segment_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME                             SEGMENT_TYPE                             TABLESPACE_NAME
---------------------------------------- ---------------------------------------- ------------------------------
MASOUD                                   TABLE                                    MAS12C
SYS_C007400                              INDEX                                    MAS12C

SQL> select * from masoud;

        C1 C2
---------- ---------
         1 16-JAN-18
         2 16-JAN-18
         4 16-JAN-18

همانگونه که مشاهده نمودید به درستی و کامل از 11g به 12c مهاجرت کردیم.

 

دوستان عزیز امیدوارم مطلب کاربردی بوده باشد.

 

شاد و پیروز باشید.