Zero down time upgrade with OGG -from 11g to 12c.
High level steps upgrade from 11g to 12c database:
1) Check network between source and target.
2) Prepare goldengate Software.
3) Setup extract and datapump for source site.
4) Setup replict for target side.
5) Start extract.
6) Export and import initial load.
7) Start the replicat.
This hands-on lab will demonstrate how to migrate the ‘TEST’ schema from Oracle database 11.2.0.4 to Oracle database 12.2.0.1.0 pdb with zero down time.
1) Check network between source and target. For example, ping 10.182.242.176
2) Prepare GoldenGate Software, we are using DIPC remote agent in this tutorial
Unzip it, the structure like following
./dicloud
|---- gghome11g
|---- gghome
|---- oci11g
|---- oci
…
The gghome11g is for the 11g source database, and the gghome is for the 12c target database.
Setup the tnsnames for source database and target database.
$mkdir –p ./dicloud/oci11g/network/admin/
$mkdir –p ./dicloud/oci/network/admin/
Prepare the tnsname.ora file.
src =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.182.242.176)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
tar =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.182.211.15)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tar)
)
)
The environment parameters for ogg source like following:
export ORACLE_HOME=/home/oracle/ogg/dicloud/oci11g
export LD_LIBRARY_PATH=$ORACLE_HOME:/lib:/usr/lib
export TNS_ADMIN='/home/oracle/ogg/dicloud/oci11g/network/admin/'
The environment parameters for ogg target like following:
export ORACLE_HOME=/home/oracle/ogg/dicloud/oci
export LD_LIBRARY_PATH=$ORACLE_HOME:/lib:/usr/lib
export TNS_ADMIN='/home/oracle/ogg/dicloud/oci/network/admin/'
After setting above environment parameters, you can run the ./ggsci command under the gghome or gghome11g.
For Source |
For Target |
Setting for Source Database: SQL>shutdown immediate; SQL>startup mount; SQL>alter database archivelog; SQL>alter database open; SQL>alter system switch logfile; SQL>archive log list; SQL>SELECT supplemental_log_data_min, force_logging FROM v$database; SQL>alter database add supplemental log data; SQL>alter database force logging; SQL>SELECT supplemental_log_data_min, force_logging FROM v$database; SQL> alter system set enable_goldengate_replication = true scope=both; SQL>create user ogg identified by ogg; SQL>GRANT DBA to ogg; SQL>BEGIN dbms_goldengate_auth.grant_admin_privilege ( grantee => 'ogg', privilege_type => 'CAPTURE', grant_select_privileges => TRUE ); END; / --create test user SQL>create user test identified by test default tablespace users quota unlimited on users; SQL>grant create table, create session, execute on dbms_lock to test; |
|
OGG setting for Source: GGSCI> view param extsrc extract extsrc userid ogg@src,password ogg exttrail ./dirdat/es table TEST.*; GGSCI> add extract extsrc, integrated tranlog, begin now GGSCI > add exttrail ./dirdat/es, extract extsrc GGSCI> dblogin userid ogg@src, password ogg GGSCI> register extract extsrc database GGSCI> add schematrandata TEST GGSCI> info trandata TEST.* GGSCI> view param pumsrc extract pumsrc passthru rmthost 10.182.209.212,mgrport 7910 rmttrail ./dirdat/rs table TEST.*; GGSCI> add extract pumsrc, exttrailsource ./dirdat/es GGSCI> add rmttrail ./dirdat/rs, extract pumsrc |
|
Prepare the target PDB: sqlplus / as sysdba SQL>create pluggable database tar admin user adm identified by adm file_name_convert=('/u01/app/oracle/oradata/dbaas/pdbseed', '/u01/app/oracle/oradata/dbaas/tar'); SQL>alter session set container=tar; SQL>create user ogg identified by ogg; SQL>GRANT DBA to ogg; SQL>create bigfile tablespace users datafile '/u01/app/oracle/oradata/dbaas/tar/users.dbf' size 100m autoextend on maxsize 5G; SQL>BEGIN dbms_goldengate_auth.grant_admin_privilege ( grantee => 'ogg', privilege_type => 'APPLY', grant_select_privileges => TRUE ); END; / |
|
OGG setting for the target: GGSCI > view param repsrc replicat repsrc userid ogg@tar, password ogg discardfile repsrc.dsc, append DBOPTIONS ENABLE_INSTANTIATION_FILTERING map TEST.*, target TEST.*; GGSCI > dblogin userid ogg@tar, password ogg GGSCI > add replicat repsrc, integrated ,exttrail ./dirdat/rs |
|
For the source database without Patch 17030189. You can apply follow workaround now. Or later back to this step if the capture abended with the errors mentioned the patch 17030189. There is a script "prvtlmpg.plb" under gghome11g directory. Need to run this script in the source database. sqlplus / as sysdba SQL>@prvtlmpg.plb |
|
Open a new terminal to run the sample application in source database. This PL/SQL block simulate a running application. SQL>conn test/test SQL>create table tmp_check (s integer); SQL>declare i pls_integer:=0; begin loop insert into tmp_check values(i); commit; i:=i+1; dbms_lock.sleep(2); end loop; end; / --Please note that the above PL/SQL is running forever. You can press ‘Ctrl’ + ‘c’ from your keyboard to stop it. |
|
Start the OGG capture. GGSCI>start EXTRACT EXTSRC GGSCI>start EXTRACT PUMSRC Check the status. GGSCI>info all |
|
Create the directory for exporting. sqlplus / as sysdba SQL>create directory ogg_dir as ‘/home/oracle/migration’; Export Schema ‘TEST’ from the source database. expdp \' / as sysdba\' directory=ogg_dir dumpfile=test_%U.dmp schemas=test statistics=none parallel=2 |
|
Move the dump files from source to target. scp *.dmp <target_ip>:<target directory> |
|
Create directory for importing. sqlplus / as sysdba SQL> alter session set container=tar; SQL> create directory ogg_dir as ‘/home/oracle/migration’; Import the schema ‘TEST’ to the target PDB. impdp system/oracle@tar directory=ogg_dir dumpfile=test_%U.dmp parallel=2 |
|
After the above import successfully. Start the replicate to sync the source and target data. GGSCI>start REPLICAT REPSRC |
|
Stop the running PL/SQL application. |
|
Check the records in Source database. SQL>conn test/test SQL>select * from tmp_check order by 1; |
|
Check the records in Target database. The results should be the same as the results from the Source database. SQL>conn test/test@tar SQL>select * from tmp_check order by 1; |
Summary
With OGG, it is easy to achieve zero down time migration + upgrade from Oracle 11g to Oracle 12c.We use DBOPTIONS ENABLE_INSTANTIATION_FILTERING - Tells Replicat to filter records per table based on Data Pump system table data, no SCN record or used in the data initialization. However, in real case, you need to consider more according to your specific environment.
最新文章
- win10 install JDK&;&;JRE
- C++之路进阶——HDU1880(魔咒词典)
- SQL注入备忘单
- PRINCE2七大原则(2)
- javass 视频笔记二 (关键字,标示符,常量变量,运算符和if-else)
- ab apache Benchmarking中链接的写法 记得加上/
- 【iOS】iOS之Button segue弹出popOver消除(dismiss)问题
- matlab里的nargin
- SQL Server 2008 2005删除或压缩数据库日志的方法
- TortoiseSVN 安装时出现 please install the universal crt
- [转载]如何快速下载、安装和配置chromedriver ?
- ARM 汇编学习笔记
- 3ds max学习笔记(十一)-- 修改器
- 使用h2数据库
- css给列表添加序号
- HBase 的MOB压缩分区策略介绍
- 《剑指offer》第五十九题(队列的最大值)
- 2017/2/11CSS基础
- centos7 安装django
- 亿级PV请求的三种负载均衡技术
热门文章
- CF 453C. Little Pony and Summer Sun Celebration
- 【GYM101409】2010-2011 ACM-ICPC, NEERC, Western Subregional Contest
- dotnet 通过 WMI 获取指定进程的输入命令行
- Vue学习笔记-基本语法
- nodejs的nvm与.net的dnvm使用对比
- Jmeter阶梯加压监听
- linux下安装MariaDB数据库
- 1040 有几个PAT (25 分)C语言
- Spring Security 实战干货: 简单的认识 OAuth2.0 协议
- C# 数独求解算法。