11gr2>>>12CPDB数据库迁移 -- 中国DBA社区

                                                    11gr2>>>12CPDB数据库 迁移

（impdp 不落地方式导入12C PDB数据库）
—-能不能rman直接跨，查一个视图
col platform_name for a20;
select * from v$db_transportable_platform;
v$db_transportable_platform
——能否xtts查这个视图
v$transportable_platform

确认源端和目标端字符集相同
查看字符集
select userenv(‘language’) from dual;

修改目标端字符集
若目标端为RAC，则
alter system set cluster_database=false sid=’‘ scope=spfile;
再关闭两个节点shutdown immediate，重新启动一个节点，再执行如下语句。——执行如下语句完成之后，再alter system set cluster_database=true sid=’‘ scope=spfile;
重新启动两个节点。
若目标端为单机，则直接执行如下语句，再重新启动数据库。
startup mount;
alter system enable restricted session;
alter system set job_queue_processes=0;
alter system set aq_tm_processes=0;
alter database open;
alter database character set internal_use ZHS16GBK;

1、确保源端和目标端数据库可用状态,并配置源端TNS，SCP传送过去目标端，并可相互tnsping通(源端和目标端数据库、实例名可不同）
12C:
sqlplus / as sysdba
SQL> show con_name

CON_NAME

CDB$ROOT

—查看12C PDB数据库名
COL name FOR A30;
col pdb for a30;
SELECT name,pdb FROM v$services ORDER BY name
NAME PDB

SYS$BACKGROUND CDB$ROOT
SYS$USERS CDB$ROOT
orac CDB$ROOT
oracXDB CDB$ROOT
oracpdb ORACPDB

源端、目标端配置TNS（添加PDB数据库监听、12C数据库监听、源端数据库监听）
源端与目标端12CRAC两个节点监听配置一样，都应有下面三者（12C另外一个节点也需要配置下面监听）

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.41.176.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

ORAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.41.176.106)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orac)
)
)
ORACPDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.41.176.106)(PORT = 1521))

(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = ORACPDB)
)

)

在源端、目标端相互tnsping

1、查看数据库版本
源端：select from v$version where rownum<2;
目标端：select from v$version where rownum<2;
{ 这两步骤可省略不做
2、源端相关参数
查看实例名、主机名
select instance_name,host_name from v$instance;
—-确保参数parallel_max的值为0，以避免源端库导出性能慢
show parameter parallel_max
3、目标端相关参数
查看实例名、主机名
select instance_name,host_name from v$instance;
—确保参数cursor_sharing的值为exact,以避免性能问题
show parameter cursor_sha;
}

4、创建目录directory,写日志需要，或者存放数据（目标端执行）
目标端12C上执行：
切换到PDB数据库 pdb数据库实例
alter session set container = ORACPDB
SQL> create directory user_backup as ‘/u01/oracle/12.2.0/dbhome_1/data’;

Directory created.

SQL> grant read,write on directory user_backup to system;

Grant succeeded.
——创建PUBLIC db_link连接
SQL> create public database link ORCL connect to system identified by yunq111 using ‘ORCL’;

Database link created.
——授与DBMS_utility包的执行权限到导入用户（源端、客户端都执行）
SQL> grant execute on sys.dbms_utility to system;

Grant succeeded.

——导入错误
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid

oracle@rac12c1:/home/oracle>oerr ora 39002
39002, 00000, “invalid operation”
// Cause: The current API cannot be executed because of inconsistencies
// between the API and the current definition of the job.
// Subsequent messages supplied by DBMS_DATAPUMP.GET_STATUS
// will further describe the error.
// Action: Modify the API call to be consistent with the current job or
// redefine the job in a manner that will support the specified API.
SQL> grant execute on dbms_datapump to system;

Grant succeeded.

6、PDB数据库上目标端创建与源端相对应的表空间
—-目标端执行如下语句：
源端数据库名orcl/目标端数据库名orac
SELECT ‘create tablespace ‘ || tablespace_name || ‘ datafile ‘’’ || REPLACE (file_name, ‘orcl’, ‘orac’) || ‘’’ size ‘
|| TO_CHAR (CEIL (size_mb)) || ‘ M autoextend on;’ as cmd
FROM ( SELECT tablespace_name, file_name, (bytes / 1024 / 1024) size_mb FROM dba_data_files@orcl where tablespace_name IN
(SELECT DISTINCT tablespace_name FROM dba_segments@orcl WHERE owner = ‘TT’));

——-owner = /IN 导出某个用户下的数据，IN(’q’,’w’)—-owner =schema用户

—- 查询出直接复制查询出的语句再目标端执行

CMD

create tablespace USERS datafile ‘+DATA/orac/datafile/users.263.946744345’ size
5 M autoextend on;

假如有多个表空间创建可以创建脚本执行、或者逐个执行

—7、PDB数据库创建相应的临时表空间，这是由于被导入的schema可能缺省的临时表空间并非temp的问题（创建过程如同第6步）
——若没有查询出来，则说明被导入的schema可能缺省的临时表空间为temp，则跳过这步，执行下步
SELECT ‘create temporary tablespace ‘
|| tablespace_name
|| ‘ tempfile ‘’’
|| REPLACE (file_name, ‘orcl’, ‘orac’)
|| ‘’’ size ‘
|| TO_CHAR (CEIL (bytes / 1024 / 1024))
|| ‘ M autoextend on;’ as cmd
FROM dba_temp_files@orcl
WHERE tablespace_name IN (SELECT temporary_tablespace
FROM dba_users@orcl
WHERE username =’TT’ AND temporary_tablespace <> ‘TEMP’);

8、实施数据迁移（不落地）

—-数据迁移前，关闭源端数据库监听，断开与应用的连接，确保当前源端数据库没有数据的修改或插入，以避免源端与目标端数据不一致。
grid@rac1:/home/grid>srvctl stop listener
grid@rac1:/home/grid>lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-JUL-2017 16:26:49

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 2: No such file or directory

——检查是否还有事务仍在执行，直接kill session或者重新启动数据库

SQL>SELECT v$session.sid, v$session.serial#, v$sqltext.SQL_TEXT
FROM v$transaction,v$session,v$sqltext
WHERE v$transaction.ADDR=v$session.TADDR
AND v$sqltext.ADDRESS=v$session.SQL_ADDRESS

—-检查系统有多少个逻辑cpu,就开启parallel为几
$cat /proc/cpuinfo| grep “processor”| wc -l

—-若环境是RAC，要么需要把cluster参数设置为0，关闭RAC环境、开并行parallel，要么把并行parallel参数设置为1，不开并行，保留RAC
—参数compression=ALL 压缩数据
—expdp /’/ as sysdba/’ 用DBA账户导
—-开并行全库导出expdp dumpfile=full_%U.dmp dumpfile格式需要增加%U格式才行，否则开了并行也是单线程导。
$ impdp system/yunq111@ORACPDB directory=user_backup network_link=ORCL logfile=imp_orcl.log version= 11.2.0.4.0 parallel=1 cluster=no schemas=tt

the default Data Pump behavior is that worker processes can run on any instance in an ORACLE RAC configuration. Therefore,
workers on those ORACLE RAC instances must have physical access to the location defined by the directory object,such as shared
storage media,if the configuration does not have shared storage for this purpose,but you still require parallelism ,
then you cn use the culsteer=no parameter to constrain all worker processes to teh instance where the Data pump job was started
默认的数据泵行为是工作进程可以在ORACLE RAC配置中的任何实例上运行。因此，
那些ORACLE RAC实例上的工作人员必须具有对目录对象定义的位置（如共享）的物理访问权限
存储介质，如果配置没有为此目的共享存储，但您仍然需要并行性，
那么你可以使用culsteer = no参数来限制所有的工作进程到数据泵作业启动的实例

9、查看EXPDP/IMPDP的进度，当你导入导出的时候，如果数据量比较大，中途有些人会着急，不免想看看进度如何

1、查看两个视图/alter日志

DBA_DATAPUMP_JOBS;

DBA_DATAPUMP_SESSIONS;

SQL>col owner_name for a20;
col job_name for a20;
col operation for a20;
col job_mode for a20;
col state for a20;
set linesize 1000;

SQL>select from DBA_DATAPUMP_JOBS;
SQL>select from DBA_DATAPUMP_SESSIONS;

2、查看impdp/expdp控制台job status

    schema用户/密码        DBA_DATAPUMP_JOBS视图内的job_name
          ||                        ||

$ impdp system/yunq111 attach= SYS_IMPORT_SCHEMA_02

10、迁移后，连接查看数据是否成功
源端查看schema下有哪些表
sqlplus / as sysdba

SQL> col table_name for a20;
SQL> set linesize 1000;
SQL> SELECT DISTINCT tablespace_name ,table_name FROM dba_tables WHERE owner = ‘TT’;
SQL> SELECT owner , count(table_name) FROM dba_tables where group by owner;

TABLESPACE_NAME TABLE_NAME

USERS BA
USERS LHQ_TEST2
USERS LHQ_TEST1
USERS TEST1
USERS T

12C 连接PDB
sqlplu 导入的schema用户/密码@PDB数据库监听字符串

sqlplus tt/yun@ORACPDB

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jul 19 18:53:23 2017

Last Successful login time: Wed Jul 19 2017 18:21:41 +08:00

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

TABLESPACE_NAME TABLE_NAME

USERS BA
USERS LHQ_TEST2
USERS LHQ_TEST1
USERS TEST1
USERS T

—-对比，无差别则成功迁移！！！//或者对比用户下表数是否相同

—-重新配置目标端监听listener，连接应用测试。

签到成功

CNDBA社区