7. Oracle 数据库12c更新迁移与整合
Oracle Database 11g 升级 12c 详细图文教程_V1.2
Oracle Database 11g 升级 12c详细图文教程Version 1.2关键字：升级、数据库、11g、12c、OraclechanrevivalChan Revival InfoTech Lab2017年8月版本控制目录1概述 (4)2相关信息 (4)3操作过程 (4)3.1备份工作 (4)3.2安装新版数据库软件 (4)3.3升级数据库实例 (20)升级结果 (33)升级前检查 (33)升级详细信息 (33)执行步骤信息: (33)初始化参数更改 (34)Enterprise Manager (34)3.4检查升级后的数据库 (35)1概述本文详细介绍在Windows平台下的Oracle Database 11g升级12c的过程。
11.2.0.1不能直接升级为12.1.0.1，会报错，相关信息会在后续文中体现，可以把11.2.0.1先升级为11.2.0.4，再升级到12c版本。
本图文教程以11.2.0.4升级到12.1.0.1为例，其它版本的升级请查阅本人相关文档，大多都有涉及。
如有谬误，欢迎指正。
2相关信息3操作过程3.1备份工作官方提供的升级方案虽然比较可靠，但是任何升级工作均存在风险，因此，做好数据备份工作十分重要。
备份工作可分为操作系统备份和数据库系统备份，数据备份工作细说起来内容也不少，不是本文讨论范围，就不展开讨论了，有兴趣的朋友可以查阅本人相关文档。
此处，建议大家用Oracle自带的RMAN工具给数据库做全备。
参考命令如下：3.2安装新版数据库软件1)检查源数据库版本2)运行Oracle Universal Installer3)配置安全更新取消复选框的勾选，即，不接收MOS的安全更新，下一步4)下载软件更新跳过软件更新，下一步如果有MOS账号，且需要安装最新的更新，可选择第一项。
升级现有的数据库，下一步如果是安装一个全新的数据库和数据库实例，应选第一项；如果只想安装新版本的数据库软件，应选第二项；选择简体中文，英语即可，下一步。
Oracle12 数据库导入以及数据源设置
Oracle12c 数据库导入以及数据源设置1.数据库的的导入数据库是Oracle10的备份，直接导入到Oracle12c中。
如上图，把备份的文件foodmart.dmp放到c:\Douuments and Setting\Administrator这个文件夹中，然后进入命令行运行如上的命令。
这个数据库就能导入进oracle12中2.数据源的设置如上图：Server Name：安装oracle12的机器的ip地址Port：Oracle默认的监听端口为1521SID：是oracle的实例的名字。
在SQL-Plus中使用命令：select instance_name from v$instance;查询实例名字。
Default Username：就是oracle的用户名。
和使用导入数据库的时候的用户名相同。
Default Password：对应的密码注意：1.这里在连接数据源之前，应该去安装oracle的机器启动oracle的监听服务，进入cmd命令行，输入命令：lsnrctl start2.安装ABX的时候，系统的jdk为1.7的话，选择jdbc的时候使用ojdbc7.jar，把其他的ojdbc全部都删除。
至此，数据库的导入和数据源的连接就完成了。
附加资料：1.orale 新建账户：create user 用户名 identified by 密码;grant create session to 用户名;grant create table to 用户名;grant create tablespace to 用户名;grant create view to 用户名;grant unlimited tablespace to 用户名;2.相关命令以下DOS下操作如下：DOS下启动监听: lsnrctl start启动对应的数据库服务： net start OracleServiceTIANSHAN连接到对应的数据库： sqlplus ts/ts@tianshan as sysdba 或者 sqlplus "/ as sysdba"修改密码： sql> alter user ts identified by xxxxx;查询对应的数据库名： SQL> select name from v$database查询对应的实例名： SQL> select instance_name from v$instance;查看用户下所有的表： SQL>select * from user_tables;创建用户： SQL> create user tsxy identified by tsxy;分配权限： SQL>grant dba to tsxy;查看有哪些数据库实例： SQL>select instance_name from v$instance; 【查看有哪些实例】查看有哪些用户： SQL> select username from dba_users; 【查看对应的用户】退出： SQL> exit停止服务： net stop OracleServiceTIANSHAN停止监听：lsnrctl stopDOS下面导入导出：导出： exp 用户名/密码@实例名 file=路径 owner=用户导入： imp 用户名/密码@实例名 file=路径 full=y具体请参考如下图：查看字段命令窗口:desc 表名或者sql窗口select * from user_tab_columns where table_name=大写表名重启数据库Shutdown immediate;startup;3.Oracle的特点Oracle一般情况下，只有一个数据库，这个特点和其他的数据库不同。
Oracle数据库数据迁移
1.概述1.1. 文档描述此文档适用于减灾中心数据迁移使用。
1.2. 系统现状减灾应用与运行管理系统的分为主中心（位于百子湾机房）和同城灾备中心（位于白广路机房）两个部分。
主中心业务区与同城备份中心通过裸光纤连接光纤交换机。
建议在两个站点间使用光纤适配器设备，最大化的利用光纤资源。
主中心在逻辑结构上又分为：核心业务区、用户服务与信息发布业务区和运行管理业务区。
拓扑示意图如下：数据中心NBU备份系统，信息如下：备份系统信息：备份软件名称Symantec Netbackup 版本Netbackup 7补丁7.0.1备份服务器主机名IP地址操作系统Windows Server 2003 sp22.环境准备2.1. 备份系统安装及配置在容灾中心搭建NBU备份系统，信息如下：备份系统信息：备份软件名称Symantec Netbackup 版本Netbackup 7补丁7.0.1备份服务器主机名IP地址操作系统Windows Server 2003 sp22.1.1.备份服务器安装步骤说明1. 安装windowsServer 2003企业版2. 设置主机名及IP地址Hostname：IP地址：网关：3. 编辑主机名4. 放入NBU 6.5安装光盘，选择安装ServerSoftware5. 选择Next6. 接受license，并点击“NEXT”7. 选择本地安装，点击“NEXT”8. 输入license后，选择NetBackupMaster Server9. 输入MasterServer名称10. 输入EMM名称11. 进行安装12. 安装完成，可选择继续添加license。
2.1.2.备份服务器配置步骤说明1. 使用administrator用户登录NBU备份服务器2. 启动NBU服务C:\Program Files\Veritas\Netbackup\bin>bpup或右击任务栏Netbackup Client Job tracker图标，选择StartNetbackup3. 点击NBUmasterserver，在右侧选择ConfigureStorage Devices4. 点击“下一步”，继续配置。
OracleUpgrade12c至19c
OracleUpgrade12c⾄19cOracle Upgrade 12c升⾄19c【官⽅⽂档】1 升级与迁移1.1 区别 升级和迁移是不同类型的数据库更改 升级将现有的Oracle数据库环境（包括已安装的组件和相关应⽤程序)转换为新版本的 Oracle数据库环境。
数据库的数据字典升级到新版本。
升级不直接影响⽤户数据;在升级过程中，不会碰触、更改或移动任何数据） 迁移数据指的是将数据从⼀个Oracle数据库迁移到之前为迁移或移动数据⽽创建的另⼀个数据库。
当需要将数据库环境迁移到新的硬件或操作系统平台，或迁移到新的字符集时，可以迁移数据。
迁移不包括升级到最新版本。
数据迁移后，升级过程将单独进⾏1.2 ⽅法Oracle提供了⼀些特性和产品来⾃动化升级过程，并帮助您有效地完成升级Oracle数据库⽀持以下⽅法将数据库升级或迁移到新版本：数据库升级助⼿ (DBUA)提供图形⽤户界⾯，指导您升级数据库。
DBUA可以在安装过程中使⽤Oracle Universal Installer启动，或者您可以在以后的任何时间将DBUA作为独⽴⼯具启动。
使⽤并⾏升级实⽤程序和其他命令⾏实⽤程序⼿动升级允许使⽤shell脚本执⾏升级。
使⽤Oracle Data Pump迁移数据提供导出和导⼊实⽤程序。
Oracle Data Pump可以从您的数据库执⾏完全或部分导出，然后完全或部分导⼊到新版本的Oracle数据库。
Oracle数据泵中的导出/导⼊可以复制数据的⼀个⼦集，⽽保持数据库不变。
CREATE TABLE AS SQL语句将数据从数据库迁移到新的Oracle数据库版本。
通过使⽤此⽅法，您可以复制数据的⼀个⼦集，⽽保持数据库不变。
使⽤优先级列表升级CDB和PDB，根据其优先级对PDB进⾏分组和升级。
使⽤选项运⾏并⾏升级实⽤程序（dbupgrade、或catctl.pl）-L以使⽤优先级列表运⾏升级，并在升级运⾏时调⽤该列表。
oracle 数据迁移方案
Oracle 数据迁移方案1. 简介随着业务的发展和系统的升级，数据迁移已经成为一个不可避免的任务。
在Oracle 数据库中，数据迁移主要包括迁移数据表、迁移数据对象以及导出和导入数据等方面。
本文将介绍一些常用的 Oracle 数据迁移方案。
2. 数据表迁移2.1 导出数据表Oracle 数据表的导出可通过使用expdp命令来实现。
该命令可以将指定的数据表导出为二进制格式的文件，以供后续导入使用。
以下是导出数据表的步骤：1.打开终端或命令行窗口，登录到数据库。
2.运行以下命令导出数据表：expdp username/password@connect_string tables=table1,table2 directory=datapump_dir dumpfile=tables.dmp logfile=tables.log–username/password：登录数据库的用户名和密码。
–connect_string：数据库连接字符串。
–tables：要导出的数据表名称，多个表名之间用逗号分隔。
–directory：导出文件存储的目录。
–dumpfile：导出文件的名称。
–logfile：导出日志文件的名称。
2.2 导入数据表使用impdp命令可以将之前导出的数据表文件导入到目标数据库中。
以下是导入数据表的步骤：1.打开终端或命令行窗口，登录到目标数据库。
2.运行以下命令导入数据表：impdp username/password@connect_string directory=datapump_d ir dumpfile=tables.dmp logfile=import.log–username/password：登录目标数据库的用户名和密码。
–connect_string：目标数据库的连接字符串。
–directory：导出文件存储的目录。
–dumpfile：导出文件的名称。
–logfile：导入日志文件的名称。
Oracle数据库升级和迁移精品PPT课件
数据库升级或数据迁移应用于以下几种情况：
1． 相同主机条件下，低版本数据库向高版本数据库的升级 2． 数据库有了新的补丁级，需要安装新的补丁 3． 更换了系统主机，如从windows更换为unix系统，数据库需要迁移 4． 实现跨平台，跨数据库版本的迁移 5． 32位数据库升级到64为数据库 6． 标准版的数据库升级到企业版的数据库
数据库升级实施内容及步骤三
运行Pre-Upgrade Information Tool
分析升级到10g前要做的一些操作，包括：数据库版本、日志文件大小、 表 空间大小、 服务器选项、 初始化参数（新增的，降级的，废弃的）、数据 库组件 、sysaux表空间、 集群信息等分析。升级前要解决该脚本生成的各 种问题。
数据库升级实施内容及步骤：
4.系统测试 通过备份搭建环境之后，进行运行测试，收集相关数据，如果设备及环境允 许进行测试，则进行至少连续2周测试，此步骤需要在数据库升级之后进行。 测试环境搭建好之后，进行各种异常演练，构建完善处理文档。
5.正式割接 经过测试验证后，对生产系统进行DataGuard环境搭建，使生产系统运行在 主备模式。每次割接需要有足够的准备时间以及至少N*2个小时停机时间。
数据库升级实施内容及步骤二
安装oracle 10g r2的软件
打oracle 10g最新的补丁，注意补丁版本的奇偶数的区别 。 关于如何安装oracle补丁程序，需要认真阅读每个补丁程序提供的Readme， 按照readme的步骤进行安装。 。 同时关注操作系统的补丁。 记住，很多隐秘性很高的故障，都是由操作系统和Oracle的补丁引起的。
3. 数据库安装及升级： 根据需要，在新的环境中安装Oracle10g数据库，使用Oracle 10.2.0.4版本， 在迁移实施中对数据库进行升级，升级到最新Oracle版本，为顺利完成升 级过程，需要调整数据库运行在归档模式下，每个数据库升级准备工作需 要充分的时间，升级需要大约N*2小时停机时间，升级之后需要指定观察 期至少半月至一个月。
数据库版本升级与迁移实用教程
数据库版本升级与迁移实用教程数据库是现代软件系统中的重要组成部分，对于保证系统的稳定性和性能优化起着关键作用。
数据库版本升级和迁移是数据库管理的重要任务之一，它既可以提供新功能和修复已知问题，又可以保证数据的一致性和完整性。
本文将介绍数据库版本升级和迁移的实用教程，帮助您顺利完成这一任务。
1. 为什么需要升级和迁移数据库版本？随着业务的发展和技术的进步，数据库版本需要不断升级以满足新的需求和解决旧版本存在的问题。
数据库版本升级可以提供更好的性能、更高的安全性、更多的功能以及更好的兼容性。
另外，当系统需要迁移到新的硬件环境或云服务平台时，数据库迁移也是必须的。
2. 准备工作在进行数据库版本升级和迁移之前，需要先进行一些准备工作，包括以下几个方面：- 创建备份：在升级和迁移之前，务必先创建数据库的备份。
备份可以作为紧急情况下的恢复手段，以防止升级或迁移过程中出现意外情况。
- 测试环境：在正式环境之外，建立一个测试环境进行升级和迁移的试验。
这样可以避免因版本升级或迁移导致的系统故障和数据丢失。
- 了解新版本：在进行升级之前，深入了解新版本的特性和变化。
理解这些变化可以帮助您更好地规划升级策略和优化系统。
3. 数据库版本升级数据库版本升级是将数据库从旧版本升级到新版本的过程。
以下是一些常见的数据库版本升级方法：- 官方升级工具：大部分数据库厂商提供了官方的升级工具，可以通过该工具一键升级数据库。
这种方式通常安全可靠，但是需要注意备份数据以防万一。
- 脚本升级：对于某些特殊需求和定制化环境，可能需要自行编写升级脚本。
这种方式需要确保脚本的正确性，同时也需要备份数据以防止意外情况发生。
- 平滑升级：在生产环境中进行数据库升级时，我们需要保证系统的连续性，即无需中断业务操作。
为了实现平滑升级，可以使用主从复制、数据库镜像等技术，在备用服务器上进行升级，然后将主服务器切换到新版本的数据库。
无论使用哪种升级方法，都需要遵循以下几点：- 事先测试：在正式环境之前，先在测试环境进行全面测试，确保升级不会造成意外的后果。
Oracle12c升级指南
Oracle12c升级指南1. 概述升级路线图从18c开始，如果想要直接升级到Oracle 18c，对于源库版本要求越来越高了。
Oracle已经彻底放弃了Oracle 11.2.0.3之前版本直接升级到18c。
具体升级路线，请查看下表当前版本号说明12.2.0.1,12.1.0.1,12.1.0.211.2.0.3, 11.2.0.4支持直接升级到18c11.2.0.1, 11.2.0.211.1.0.6, 11.1.0.710.2.0.2,10.2.0.3, 10.2.0.4 和10.2.0.510.1.0.59.2.0.8及更早版本不支持直接升级到18c.解决方法：1）只能先升级到支持直接升级到18c的中间版本，然后再次升级到18c2）使用数据泵直接将数据迁移到新版本数据库中源库过度版本目标数据库版本11.2.0.1/11.2.0.2-->11.2.0.3/11.2.0.4-->18.x11.1.0.6/11.1.0.7-->11.2.0.3/11.2.0.4-->18.x10.2.0.2, 10.2.0.3, 10.2.0.4, 10.2.0.5-->11.2.0.3/11.2.0.4/12.1.0.1/12.1.0.2-->18.x10.1.0.5-->11.2.0.3/11.2.0.4/12.1.0.1/12.1.0.2-->18.x9.2.0.8 or earlier-->11.2.0.3/11.2.0.4-->18.xOracle版本发布与支持时间参考文档Oracle 12cR1 Upgrade Companion (文档 ID 1462240.1)Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) (文档 ID 1503653.1)Complete Checklist for Upgrading to Oracle Database 12c Release 1 using DBUA (文档 ID 1516557.1)Release Schedule of Current Database Releases (文档 ID 742060.1)How to Upgrade to Oracle Database 12c Release1 (12.1.0) and Known Issues (文档 ID 2085705.1)Master Note For Oracle Database 12c Release 1 (12.1) Database/Client Installation/Upgrade/Migration Standalone Environment (Non-RAC) (文档 ID 1520299.1) Database Server Upgrade/Downgrade Compatibility Matrix (文档 ID 551141.1)2. 10gR2、11gR1或11gR2升级12cR12.1. 操作系统要求Oracle Database (RDBMS) on Unix AIX,HP-UX,Linux,Solaris and MS Windows Operating Systems Installation and Configuration Requirements Quick Reference (12.1) (文档 ID 1587357.1)Document 1517948.1 Requirements for Installing Oracle Database 12.1 on Solaris 10 SPARCDocument 1525614.1 Requirements for Installing Oracle Database 12.1 on Solaris 11 SPARCDocument 1529433.1 Requirements for Installing Oracle Database 12.1 on RHEL5 or OL5 64-bit (x86-64)Document 1529864.1 Requirements for Installing Oracle Database 12.1 on RHEL6 or OL6 64-bit (x86-64)Document 1961997.1 Requirements for Installing Oracle Database 12.1 on RHEL7 or OL7 64-bit (x86-64)Document 1519770.1 Requirements for Installing Oracle Database 12.1 64-bit (AMD64/EM64T) on SLES 11Document 1961277.1 The Oracle Database 12c Install Options and the Installed Components2.2. 12.1.0.2重要的补丁包为了避免升级过程中出现问题，安装12.1.0.2版本后打上如下补丁包，避免相关问题20369415、215507772.3. 源库要求和建议1)版本要求根据升级要求，在原数据库上升级，对原数据库的版本要求如下：10gR2不能低于10.2.0.511gR1不能低于11.1.0.711gR2不能低于11.2.0.2PSU补丁：Quick Reference to Patch Numbers for Database/GI PSU, SPU(CPU), Bundle Patches and Patchsets (文档 ID 1454618.1)2)升级前建议做个备份 3)确保升级前Oracle系统对象和组件全部是VALID状态。
Oracle数据库12c升级指南说明书
Trouble-free Upgrade to Oracle Database 12c with Real Application TestingKurt EngeleiterPrincipal Product ManagerSafe Harbor StatementThe following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.Program Agenda▪Oracle Database Upgrades▪Validating Upgrades with Real Application Testing ▪Two Upgrade Use Cases:‒Consolidation into Multi-Tenant‒Database In-Memory▪Customer StoriesOracle Database UpgradesWhy Upgrade to Database 12c? •Database Consolidation to Multitenant–Ease management–Standardize on most recent version–Simplify future upgrades•Database In-Memory–Real time analytics•Migration to a new environment–Hardware / Software•Take advantage of other new database featuresValidating Upgradeswith Real Application TestingDatabase Upgrade Testing - Why•Every customer has a unique environment–Hardware configuration–Application use of the database•Upgrade testing in your environment provides validation ofperformance and correctness–Validates your unique hardware and software stack–Assures most important databases / applications perform to meet SLAsReal Application Testing provides real-workload testing solution that is comprehensive & easy-to-use for system change assessmentResponse Time TestingLoadTestingComprehensiveTestingSolutionSQL Performance Analyzer DatabaseReplayRealApplicationTestingReal Application Testing – Two Complementary SolutionsSQL Performance Analyzer •Helps users predict the impact of system changes on SQL workload response time•Low overhead capture of SQL workload to SQL Tuning Set (STS) on production system•Build different SQL trials (experiments) of SQL statements performance by test execution •Analyzes performance differences•Offers fine-grained performance analysis on individual SQL•Integrated with STS, SQL Plan Baselines, & SQL Tuning Advisor to form an end-to-end solutionAnalysis ReportCompareSQL PerformanceSQL plans + stats SQL plans + stats Pre-change Trial Post-change TrialSQL WorkloadSTSSPA Report1 2 345Database Replay •Database load and performance testing with real production workloads–Production workload characteristicssuch as timing, transactiondependency, think time, etc., fullymaintained•Identify and remediate application scalability and concurrency problems in multitenant and non-CDB databases •Allows scheduling, scaleup, subsetting, of multiple workloads•Concurrent database replay available for 11.2.0.2 and above, MOS Note: 1453789.1Database Upgrade Testing Best Practices•Always use SPA first–Unit test before load test•Capture and replay a manageable amount of time – e.g. 1 to 2 hours •Key metric for Database Replay is DB Time•Capture SQL Tuning Sets during capture and replay for additional validation•Use Enterprise Manager 12c with the latest DB Plugin–Implements best practices–Wizards guide you through the capture and replay process–Long term repository for storing and analyzing test results*Check MOS Note 560977.1 for recommended patch bundlesDB Time Definition•Total time in database calls by foreground sessions •Includes–CPU time –IO time–Active wait (non-idle wait) time•Basic unit for Oracle performance analysisCPUI/O WaitDB TimeDatabase Consolidation Into MultitenantDatabase Workload Compatibility: Challenges•Applications have different workload profiles –CPU–Memory –Storage –Network •Will my Multitenant database handle peak workloads and co-exist? •Is there enough headroom? •How do I minimize risk? •Use Real Application Testing to assess Multitenantworkload compatibility and conduct capacity planning ✓✗✓ ✓Using SPA in Multitenant EnvironmentCRM DW ERP HCMValidate SQL performance forconsolidated database:•Capture SQL workload foreach database in STS•Execute SPA for all workloadstogether in consolidatedenvironment•Identify errors & SQLregressions•Review response time•RemediateERP STSCRM STSHCM STSDWSTSSALESHRERPCRMMultitenant Load Testing: Use Consolidated DB Replay•Capture workload on different databases that needs to co-exists concurrently •Works for schema consolidation and Pluggable Databases•Use scaleup, subsetting, scheduling of multiple workloads•Use for schema and CDB consolidation •Identify and remediate inter-application scalability and concurrency problems •Available for 11.2.0.2 and above, MOS Note: 1453789.1TestCapture Process ReplayAnalysis & ReportingProductionReplay DriverClientsStorage StorageMultitenant Load Testing: Use Consolidated Database ReplayWindows DB 10.2 Capture 1Capture 2Capture 3Production SystemsCRM- DB1 HCM-DB2 DW-DB3DirectoriesAIXDB 9.2.0.8 HP-UX DB 11gMay: Month-end Close June15: Daily PeakJune 18: DW - ETL…CDB – DB12cReplay on CDBCapture 1Capture 2Capture 3Test SystemConsolidated Replay DirectoryDirectoriesHow to Test For Consolidation•If consolidating a few databases–Use SPA–Use Consolidated Replay•If consolidating a large number of databases–Identify databases with similar workload profiles–Categorize them into small, medium and large–Scale up each category with workload scheduling and Consolidated ReplayUse Case Example•Move to new platform and consolidate to multitenant databaseCapture SQL Tuning Sets on each databaseCapture Workload on each database with Database ReplayUpgrade Database to12cConfigure PDBsRun SPA TestRun Database ReplayTarget1 Node, 4 CPU, 16 GB MemoryProduction2 Nodes with 2 CPUs, 8 GB MemoryCapture Production WorkloadDatabase 1 Database 2•Capture SQL for each database into SQL Tuning Sets for SPA test •Capture workloads for Database ReplayOn Test System•Install Database 12c with Multitenant configuration•Restore production backups into individual PDBs and run upgrade scripts•Run SPA–Convert captured production STS into trial 1–Execute SPA trial 2 on each PDB–Evaluate results–Remediate any regressions before database replayConsolidated Database SPA - Execution Plans Unchanged•For consolidation into multitenant,most SQL should have unchangedexecution plan•For OLTP workload- ‘Buffer Gets’ isbest metric•Most SQL also have unchangedperformanceTest System – Consolidated Replay •Execute Consolidated Replay on test system–Remap captured TNS connections to individuals PDBs –Run Consolidated Replay–Evaluate resultsConsolidated Database Replay Result•Replay ‘user calls’ identical tocapture – same amount ofdatabase work was done•DB Time slightly reduced•Conclusion: Upgraded andconsolidated databaseperformance matches sourceStress Test with Workload Scheduling•Consolidated Database Replay also includes workload scheduling •Workload peaks can be aligned to maximize stress on replay systemUpgrading to Database In-MemoryOracle Database In-MemoryBoth row and column in-memory formatsSimultaneously active and transactionally consistentensuring access to freshest dataEliminates manual tuning and expensive analytic indexesUnique Dual-Format ArchitectureColumn FormatMemoryRow Format MemoryAnalyticsOLTPSales Sales Up-to-date analyticsOptimizing Transaction and Query Performance Row Format Databases versus Column Format DatabasesRow ▪Transactions run faster on row format –Insert or query a sales order–Fast processing few rows, many columnsColumn▪Analytics run faster on column format–Report on sales totals by state–Fast accessing few columns, many rows ORDERSALESSALESSTATEOracle 12c: Stores Data in Both Formats SimultaneouslySimple Implementation1.Upgrade to database 12.1.0.2 (or higher)2.Configure Memory Capacity‒inmemory_size = XXX GB3.Configure tables or partitions to be in memory ‒alter table | partition … inmemory;4.Drop analytic indexes to speed up OLTPUse Case Example•Upgrade to database 12.1.0.2 and Database In-MemoryRun In-MemoryAdvisorCapture SQLTuning SetCapture Workload withDatabase ReplayUpgrade Database to12cConfigureDatabase In-Memory UsingAdvisor OutputRun SPA TestRunDatabaseReplayProduction TestUpgrade and Testing Steps•Run Database In-Memory Advisor in existing database 11.2 environment•Capture SQL Tuning Set•Capture workload with Database Replay•In test environment–Upgrade database and implement Database In-Memory Advisor recommendations–Run SPA trial–Run Database Replay–Evaluate resultsRun Oracle Database In-Memory Advisor•New In-Memory Advisor •Analyzes existing DB workloadvia AWR & ASH repositories•Provides list of objects that would benefit most from being populated into IM column store•Works on database versions 11.2 and aboveObject TypeObjectEstimated In-Memory Size EstimatedAnalyticPerformance Improve mentTable SALES.ORDERS 2.19 GB 9.3X TableSALES.LINEITEM1.03 GB5.2XPartition SALES.PRODUCTS.201404 415 MB 4.3X Sub-partitionSALES.PRODUCTS.20140330200 MB2.7XCapture Production Workload•Capture SQL into a SQL Tuning Set •Capture workload for Database ReplayConfigure In-Memory on Test System•Restore production database•Upgrade to database 12.1.02 or higher•Set init.ora parameter inmemory_size to size In-Memory column store•Restart database•Execute SQL script from In-Memory Advisor to configure objects In-MemoryValidate In-Memory ConfigurationEnterprise Manger 12c In-Memory Central Object map – displaysrelative sizes of objectsIn-MemoryObject Table – Listsdetails of segmentsloaded In-MemorySPA – Look for Expected Plan Changes•Convert production STS to trial 1•Execute trial SPA trial 2•Produce comparison report•Best metric for analytic queryworkload is ‘Elapsed Time’•Analytic SQL have changedexecution plan with plan line‘Table Access In Memory Full’•SQL are benefitting from In-MemoryIdentify Plan Change Improvements Real Time SQL MonitoringExecute Database Replay•Validate concurrency and loadimprovement for consolidatedworkload•Database Replay trial results:•User calls identical – same workloadwas executed•DB Time substantially reduced•SuccessDatabase In-Memory DB Time ImprovementDatabase 11.2.0.4 Database 12.1.0.2 + In-MemoryCustomer Case StudiesSumitomo Heavy Industries,Ltd. Database Upgrade ProjectUsing RAT & GG VeridataCustomer ProfileCompany Name: Sumitomo Heavy Industries, Ltd.Head Office: ThinkPark Tower, 1-1 Osaki 2-chome, Shinagawa-ku, Tokyo 141-6025, JapanFounded: November 20, 1888Incorporated: November 1, 1934Capital: JPY 30,871,650,000 (as of March 31, 2014) Employees: Consolidated: 17,941 (as of March 31, 2014)Annual Revenue: Consolidated: JPY 615,270,000,000(for the 2013 Fiscal Year)Challenge & SolutionDatabase Upgrade 9i /10g -> 11gR2Solution•Perform application test with completely the same workload using Oracle Real Application Testing •Automate the Performance management using Diagnostics Pack & Tuning Pack•Guarantee data consistency after testing on new environment using Oracle GoldenGate Veridata •Use Oracle GoldenGate for minimizing downtime and fallback plan (under proposing)Challenge •Aim to reduce costs to around $2.5 M to 30-50% •Upgrading in parallel in a short period of time about 30DB •The data migration in a short period of time large-scale DB and Mission-Critical DB environment, to minimize business downtime •Standardizing method for migrating and upgrading multiple databases efficiently •Performing and automating application test by DBA for minimize costSolution ArchitectureArchitecture•1st step: Measure the SQL Elapsed timeCheck the workload consistency by using Veridata•2nd step: Check whether the new database has been created successfully by using Veridata•3rd step: Compare the whole throughput and workload between the production and the new production by using RATPerformanceTest Performance and Consistency TestProduction (9i) Test (9i)Test (11g)Veridata Server New Production(11g)1st Step3rd StepDatabaseConfiguration Test2nd StepVeridata ServerSumitomo Heavy Industries, Ltd.30DBs Upgrade, reduce the verification Cost to take advantage of support toolsCustomer ProfileName： Sumitomo Heavy Industires, Ltd.（http://www.shi.co.jp/english/）Location： Tokyo (Japan)Industry： manufacturing IndustryEmployees： 17,961Oracle Products•Oracle Real Application Testing (RAT)•Oracle Diagnostics Pack /Oracle Tuning Pack (Diag/Tuning) •Oracle GoldenGate Veridata(Veridata)•Oracle GoldenGate (GG) CUSTOMER PERSPECTIVE“I heard that Other Oracle Customer has efficiently implementedDB upgrade, to take advantage of tools(RAT, GG etc).Therefore ,We accept the Challenge!!”Engineer / Takayuki Okoshi INFORMATION SYSTEMS DEVELOPMENT DEPT.Sumitomo Heavy Industries Business Associates, Ltd.CHALLENGES/OPPORTUNITIES• Upgrading in parallel in a short period of time about 30DB •Aim to reduce costs to around $2.5 M to 30-50% •Establishing an upgrade method utilizing a tool for efficient operation while ensuring the quality• The data migration in a short period of time large-scale DB environment, to minimize business downtime• Adopt the DB performance improvement technique method RESULTS•RAT realized the accurate verification and efficient online processing performance•Diag/Tuning realized the performance improvement technique method•Veridata realized the data validation of the old and new environment accurate and efficiently to a large extent•To maintain business continuity, used GG to data migrate on the Critical DBs.。
10g迁移12c的注意事项
10g迁移12c的注意事项
迁移10g数据到12c数据库是一项复杂的任务，需要一定的经验和技巧。
为了确保迁移顺利进行，有几个注意事项需要注意。
备份数据是迁移过程中至关重要的一步。
在迁移之前，务必先进行全量备份，以防数据丢失或损坏。
可以使用Oracle提供的备份工具或其他第三方工具进行备份。
确保源数据库和目标数据库的版本兼容性。
在迁移过程中，需要确保源数据库的版本不低于10g，目标数据库的版本为12c。
如果版本不兼容，需要先升级源数据库至10g或更高版本，再进行迁移。
迁移过程中需要注意数据的一致性。
可以使用Oracle提供的数据校验工具检查数据的完整性和一致性。
如果发现数据不一致，需要及时修复或重新迁移。
迁移过程中还需要考虑网络带宽和性能的限制。
如果网络带宽不足，可以考虑增加带宽或使用数据压缩技术来减少网络传输的数据量。
同时，可以调整数据库参数和优化SQL语句来提高性能。
迁移过程中的日志记录和监控也是非常重要的。
可以使用Oracle提供的日志记录工具或第三方监控工具来监控迁移过程中的性能和错误日志，以便及时发现和解决问题。
迁移10g数据到12c数据库需要仔细计划和准备。
遵循上述注意事
项，可以确保迁移过程顺利进行，数据完整性得到保障。
希望以上内容对您有所帮助。
oracle数据库升级迁移方案
oracle数据库升级迁移方案
一、前期准备
（1）制定升级/迁移的项目计划，确定升级/迁移的内容，规划整体的计划，记录实施需要的资源；
（2）收集升级/迁移前的环境，包括数据库版本，物理机，操作系统版本，存储情况及相关参数；
（3）收集升级/迁移后的环境，并确认配置参数，确定新的数据库版本和物理机；
（4）确定迁移的对象、数据量及时间，根据实施过程需要，分析应用影响，制定迁移方案，准备迁移脚本；
（5）备份升级/迁移前的数据库，确认备份文件的完整性；
（7）安装升级/迁移新版本的数据库，检查系统参数，检查新数据库服务器内存，cpu，硬盘空间等；
（8）组织升级/迁移前的会议，确定安全措施，监控及备份等；
（9）获取外部技术支持服务；
二、安装升级
（1）安装新版本的数据库，根据要求安装所需的数据库软件；
（2）检查系统配置，查看本地缓冲池大小；
（3）连接到升级/迁移的服务器，检查新安装的版本是否正确；
（4）确保最新补丁被正确安装；。
oracle数据库升级迁移实施方案
数据库系统和网络存储系统工程数据库迁移实施方案. v .文档控制文档修订记录审阅分发第一章文档介绍31.1背景31.2目标4第二章系统硬件选型42.1存储设备42.1.1 设备选型42.1.2 设备功能及实现42.2效劳器设备52.1.1 数据库效劳器5第三章系统安装73.1主机系统安装73.2配置SAN网络、磁盘阵列83.3配置HACMP83.4安装数据库软件9. v .第四章数据移植104.1移植准备工作104.2移植过程114.3系统检查12数据库检查12导入后系统需要完成的工作12应用检查134.4系统回退13第五章应用迁移13第六章新系统上线后的工作13第七章工作界面和工作容14第八章实施方案15：错误!未定义书签。
1.设备、软件验收交付记录错误!未定义书签。
2.操作系统安装错误!未定义书签。
3.操作系统镜像错误!未定义书签。
4.设备配置清单(需确认)错误!未定义书签。
4.1 IBM p570效劳器错误!未定义书签。
4.2 光纤交换机配置错误!未定义书签。
第一章文档介绍1.1背景HP公司全面转向X86芯片，使用PA-RISC芯片的HP 9000效劳器现已停产，虽然Oracle R12已经可以支持Itanium平台上的HP-UX，但某电厂应用系统目前是VXX.X.XX，而某应用软件VXX版本目前尚不能运行于Itanium平台,故准备将系统迁移至新硬件平台(IBM power处理器)。
本次工程的主要目标是对包括如下几点：1) 存储设备及小型机设备的选购采购一台新磁盘阵列提供效劳，替换过去的旧存储设备，磁盘按现有存储容量预期的1.3至1.5倍配置, (RAID10或RAID5提供冗余保护，热备盘提供磁盘的在线. v .替换)，空间考虑为_T〔为以后的扩容考虑需要，最大支持在_T〕,如可能涉及到系统日后的扩容、容灾及测试空间需求，可对存储适当增加扩展柜来扩大容量。
2)系统硬件规划及配置当前硬件系统按应用规划要求划分LPAR分区，并基于两台效劳器分区之间实现集群配置。
CON1399-利用OracleDatabase12c整合数据库
DBA 面临的挑战： • 修打补和丁升和级升级 • 供应
需求 性能和可扩展性 可靠性和可用性
安全性和租户隔离 无需更改应用程序
5 版权所有 © 2013，Oracle 和/或其分支机构。保留所有权利。
趋势 私有云
大规模的高性能服务器 (Exadata)
虚拟化
简化 • 标准化服务 • 自助服务
CDB 实例 3
节点 1
节点 3
CDB 实例 2 节点 2
多租户容器数据库 (CDB)
31 版权所有 © 2013，Oracle 和/或其分支机构。保留所有权利。
通过可插拔移植性实现前所未有的敏捷性
随着 PDB 变成关键业务，PDB 在 SLA 之间迁移
黄金级
RAC、Data Guard、每日增量备份
1 版权所有 © 2013，Oracle 和/或其分支机构。保留所有权利。
Oracle 多租户
使用 Oracle Database 12c 简化整合
Bryn Llewellyn 资深产品经理 数据库服务器技术部 Oracle 总部
以下内容旨在概述产品的总体发展方向。该内容仅供参考，不可纳入任何合同。 其内容不构成提供任何材料、代码或功能的承诺，并且不应该作为制定购买决策的 依据。此处所述有关 Oracle 产品的任何特性或功能的开发、发布以及相应的日程 安排均由 Oracle 自行决定。
11 版权所有 © 2013，Oracle 和/或其分支机构。保留所有权利。
多租户架构
多租户容器数据库 (CDB) 的组件
可插拔数据库 (PDB)
12 版权所有 © 2013，Oracle 和/或其分支机构。保留所有权利。
PDB Root CDB
详细讲解Oracle数据库的数据迁移方法
详细讲解Oracle数据库的数据迁移方法（1）随着数据库管理系统和操作系统平台的更新换代的速度的加快，数据库管理员经常需要在两个不同的数据库之间或在两种不同的系统平台之间进行数据迁移。
本文介绍了数据库数据迁移的一般步骤以及实现向Oracle8i数据库进行数据迁移的几种方法，并对它们的优缺点做了对比分析。
在开发环境向运行环境转换、低版本数据库向高版本数据库转换以及两个不同数据库之间进行转换时，数据库中的数据（包括结构定义）需要被转移并使之正常运行，这就是数据库中的数据迁移。
对于中小型数据库，如Foxpro 中的＊.dbf，这种迁移非常简单，一般只需通过简单的Copy就能完成。
但对于大型数据库系统，如Oracle 数据库，数据迁移就不那么简单了，它需要利用一定的技术和经验，有步骤按计划地完成。
数据迁移的一般步骤对数据库管理人员来说，数据库数据迁移极具挑战性，一旦措施不当，珍贵的数据资源将面临丢失的危险，要成功地实现数据库数据平滑迁移，需要周密计划和充分准备，并按照一定的步骤来完成。
设计数据迁移方案设计数据迁移方案主要包括以下几个方面工作：研究与数据迁移相关的资料，或在网站上查询相关内容、评估和选择数据迁移的软硬件平台、选择数据迁移方法、选择数据备份和恢复策略、设计数据迁移和测试方案等。
进行数据模拟迁移根据设计的数据迁移方案，建立一个模拟的数据迁移环境，它既能仿真实际环境又不影响实际数据，然后在数据模拟迁移环境中测试数据迁移的效果。
数据模拟迁移前也应按备份策略备份模拟数据，以便数据迁移后能按恢复策略进行恢复测试。
测试数据模拟迁移根据设计的数据迁移测试方案测试数据模拟迁移，也就是检查数据模拟迁移后数据和应用软件是否正常，主要包括：数据一致性测试、应用软件执行功能测试、性能测试、数据备份和恢复测试等。
准备实施数据迁移数据模拟迁移测试成功后，在正式实施数据迁移前还需要做好以下几个方面工作：进行完全数据备份、确定数据迁移方案、安装和配置软硬件等。
Oracle 12c数据库基础教程-Oracle 12c数据库备份和恢复
2．介质恢复
p 介质恢复可以实现完全恢复,或者恢复 到指定地时间点。
p 在执行完全恢复时,首先还原备份地数 据文件,然后再根据联机归档重做日志文 件将执行备份操作后发生地改变应用到数 据文件中。执行完介质恢复后,数据库将 回到发生故障时地状态,而且可以正常打 开数据库,不会有任何数据丢失。
热备份也称为ArchiveLog方式。当不能关闭数据库且数据库处于归 档模式时,可以采用热备份。热备份可以根据归档日志地时间轴来进 行备份与恢复,理论上可以恢复到前一个操作。
（2）逻辑备份指利用SQL语言从数据库中获 取数据并存于二进制文件地过程。
p逻辑备份是对物理备份地有效补充,例如 导出数据库对象（表或表空间）。但是逻 辑备份不能保护整个数据库,因此有效地 数据库备份机制必须是基于物理备份地。
冷备份也称为NonArchiveLog方式。如果可以暂时关闭数据库,则可 以利用此机会将数据文件复制到其它介质。当数据库遭到破坏时,可 以将之前备份地数据库文件复制回原来地位置。这种方法简单易行, 因为在数据库不提供服务时进行备份,所以又称为冷备份。在冷备份 模式中,数据库在备份过程中保持关闭状态,重做日志中所有提交地数 据变化都会被写入到数据文件中,因此数据文件处于事务一致性状态。 冷备份又可以称为一致性备份,当从一致性备份中恢复数据文件后,可 以立即启动并打开数据库
p CONNECT TARGET /
p 此时不需求输入用户口令,Oracle会使用当前操作系统 用户连接到数据库服务器。
p 使用LIST BACKUP命令可以显示备份集信息。
【例8.1】
LIST BACKUP OF DATABASE BY BACKUP;
Oracle数据库迁移指南：从第三方数据库到Oracle Database 12c的迁移过程说明书
Migrating to Oracle DatabaseYour Guide to a Successful Migration, On-Premises or in the CloudO R A C L E W H I T E P A P E R | F E B R U A R Y 2017IntroductionThis paper provides an in-depth look at the process of migrating third-party databases to Oracle Database 12c using Oracle SQL Developer tools. It will help you determine what’s involved with converting a third-party database to an Oracle Database instance, and also explain how to make your existing applications work with the finished product – the migrated Oracle instance. You will learn how to create a migration repository, capture the source database, convert it to the Oracle data type, and copy the data. The examples apply to on-premises and cloud scenarios, with attention to two cloud environments: Oracle Database Cloud Service (DBaaS) and Oracle Database Exadata Cloud Services.Generally speaking, the process of migrating data from a third-party RDBMS to Oracle Database is straightforward. Changing the architecture of your application so it can communicate with Oracle Database can be as simple as dropping in a new JDBC or ODBC driver. Moving stored procedures to Oracle’s PL/SQL ecosystem is slightly more complicated. Every migration is different, and some applications require changes at the lowest levels of your technology stack. Asking the right questions up front is critical so you can identify potential roadblocks, and plan accordingly.This paper assumes you have access to Oracle Database 12.1.0.2 or higher. If you have questions regarding Oracle Database licensing and pricing, please contact your Oracle account manager.Benefits of Migrating to Oracle Database CloudOracle Database 12c introduces several significant capabilities that significantly lower the cost and time required to migrate non-Oracle databases to the Oracle platform. Oracle Database Cloud Service lets you choose pay-per-use metered services, or a regular monthly subscription of Oracle 11g, 12.1 and 12.2 database services. You can automatically provision high availability configurations to elevate service levels for maintenance or unexpected failure scenarios. Provisioning choices include pre-packaged bundles of Oracle Database options for DevOps, Performance, Security, Analytics and Monitoring. The service comes with an integrated Oracle Application Express environment, allowing you move development activities to the cloud. Oracle Database 12c Multitenant makes it easy to consolidate many databases quickly and manage them as a cloud service.Identifying the Scope of Your MigrationDiscussions regarding the migration to Oracle Database usually start with a series of questions: ∙What database are you migrating from? (For example, SQL Server or DB2)o What version of this database?If it’s the latest version, e.g. SQL Server 2016, are you using any version-specificfeatures?∙How many databases are you migrating?o If more than one, are they essentially copies of each other, or is each one a different migration?∙How large are your databases (amount of disk space for the data itself in GB/TB)?∙Do your database applications rely heavily on stored procedures?o How many stored procedures and triggers need to be migrated?o Do they predominately fall under the ‘select * from table return result set to application’ category?∙What is your migration project timeline, and when do you need to “go Iive?”∙Will you be running the Oracle and third-party database systems concurrently, with plans to fail over to the Oracle implementation?∙Will your new database instance reside on-premises or in the cloud?∙What is the downtime/maintenance window, if any, allowed for this migration?∙What is the nature of your homegrown applications (.NET, Java, etc.)?o Applications provided by an ISV generally have their own migration pathIf downtime isn’t an option or you will be required to replicate database changes to the original s ystem for some time after your migration, consider using Oracle GoldenGate 12c to shorten the amount of time required to migrate the data.Database Migration ToolsOracle SQL Developer can help you identify the physical characteristics of the database(s) you are migrating. It is included with every copy of Oracle Database and also available for download on the Oracle Technology Network. This utility serves as a graphical user interface for Oracle Database and an integrated development environment for PL/SQL.Figure 1: SQL Developer version 4.1.2SQL Developer is also the official third-party database migration solution for Oracle Database. It currently supports SQL Server, Sybase ASE, DB2, and Teradata. It also assists with migrations from MySQL. For exact versions of supported databases, please consult the latest information on OTN.SQL Developer will capture the third-party database definitions, analyze and convert those definitions to their Oracle equivalents, and provide the tools you need to move and verify the data. Here is a link to the supported JDBC drivers.SQL Developer provides a 9-step wizard that you can use to create a migration project, including setting up and running the capture and conversion processes of the source third-party database.Environment SetupYou may be migrating your data to Oracle Cloud or installing an in-house Oracle Database. Whether you are migrating 1,000 databases or just one, it’s best to start small to get a feel for the migration process and the tools. You will need the following:∙Oracle Database 12c, set up with a repository to store the project metadata (as described below)∙The latest version of SQL Developer∙The correct JDBC driver to connect to your third party databaseOracle provides a VirtualBox appliance, which includes Oracle Database 12c, the latest version of SQL Developer, and hands-on labs that present examples of how to migrate a database. The virtual machine is not certified or available for commercial use, but it is helpful for learning the ins and outs of performing an Oracle Database migration.Add Third-Party JDBC Drivers and Connect to your Source DatabaseFrom the supported JDBC driver list, download the appropriate driver for your database type. In SQL Developer, go to Preferences. On the Database page, select the Third Party JDBC Drivers page. Click ‘Add Entry…’ and navigate to the appropriate jar file, as shown below.Figure 2: jtds v1.3 is recommended for Sybase ASE and Microsoft SQL Server migrations.Once the driver has been successfully added, you can define a connection to databases that are compatible with that driver.Create a connection to your source database. Verify that you can browse the objects and data to be migrated.In your target Oracle Database, create a new user/schema for the migration repository. Define a SQL Developer connection to this schema so you can use it to create the repository.Launching the Wizard in SQL DeveloperIn SQL Developer, go to the Tools menu and select ‘Migration -> Migrate…’This will launch a wizard that guides you through the process of creating a migration repository, capturing the objects, and performing the move to Oracle Database.You can create the repository in advance or you can do so via the wizard.Figure 3: The Migration WizardThe next page will ask you to pick your migration repository connection. Select the connection previously created, or create one now using the ‘Create new connection’ button.The migration repository can be created in any Oracle instance. It does not have to be the same instance that you plan to migrate to.Next you will be prompted to name your project and select a directory for migration project output. Select the third-party database to be migrated. You have two options to access the data model –a ‘live’ connection and an offline model that is captured via she ll scripts. The default selection is ‘Online.’Offline is for cases in which the engineer performing the migration does not have access to the source third-party database. In this scenario, use the ‘Tools > Migration > Create Database Capture Scripts…’ i nterface.This command will generate either a Windows Batch file or a Linux Shell script for an administrator to run. It will capture the data model of the source database and write it to files that can then be referenced in the ‘Source Database’ page of the Migration Wizard.Figure 4: Use Offline mode when you do not have access to the source third-party database.Once a connection has been established, select one or more databases to migrate. Determine the appropriate Oracle data types for the conversion. For example, the Sybase ASE data type ‘DateTime’ will be stored as a ‘Date’ in Oracle. Similarly, ‘Time’ will be stored as ‘Timestamp.’ You can override the defaults if you prefer to define your own data definition rules.Objects will be renamed in the migration process to avoid reserved word collisions and object name length limitations, such as the 30-character limitation in Oracle Database 12c Release 1. Apart from the data objects, SQL Developer will also translate constraints, views, functions, procedures, and triggers. The list of supported objects to be translated for each third-party databases can be found here.By default, SQL Developer will attempt to translate all available ‘SQL’ objects – that is, objects with embedded native SQL or T-SQL.Specifying the Target DatabaseThe Target Database Step allows you to specify how you would like to generate the migrated Oracle users and objects. There are two Modes, online and offline.Figure 5: You can send the migrated objects to any Oracle instance. Do not use the repository connection.Selecting Target Locations in Oracle CloudOracle SQL Developer facilitates connections to Oracle Database Cloud Service and Oracle Database Exadata Cloud Service. However, both services restrict traffic from the open Internet to port 22 via SSH. Before connecting to your target Oracle Database running in these Cloud environments, use the SQL Developer SSH Tunneling feature to connect to your database.Figure 6: When you create a tunnel, you can also set up a port forward.The host is the network location of your DBaaS instance. The username is the OS user on the DBaaS server you are connecting to via SSH. The port forward screen allows you to accept traffic from port 22 to the DBaaS Listener port of 1521. Once this path is defined, your DBaaS connection will connect via this tunnel, and it can be used for your migration target.Figure 7: A DBaaS connection via an SSH tunnel in Oracle SQL Developer.Once connected, you can migrate to a cloud-based or on-premises instance of Oracle Database by following the process outlined below. In both cases, SQL Developer will create a “generation script” that defines a new Oracle user, connects to that user, and creates the necessary tables, indexes, views, procedures, and so forth. No data movement will occur during this step.In Online Mode, use the wizard to choose the target Oracle Database, and then automatically run the generation script. The connection you select must have the necessary privileges to create a new user and migrate all the associated objects.In Offline Mode, you don’t have to choose the Target Oracle Database. Instead you can open the generation script in a SQL Developer SQL Worksheet, so it can be inspected first. You can then choose the target Oracle database in the SQL Worksheet and execute the script against that worksheet.Loading Data into Oracle TablesAlways use the ‘offline’ mode to migrate your data. This mode will create SQL*Loader scripts to load the data into the source Oracle tables. The ‘online’ mode is good for test runs, especially when the amount of data is small. Moving a large instance in online mode can take several days. Oracle does not advise executing this function from within the SQL Developer interface.Figure 8: Use ‘online’ mode for test runs and small databases, ‘offline’ mode for everything else.Inspecting the Migration ResultsAfter the migration has finished, you can inspect the details of how objects have been captured and converted by opening the migration project. Several reports are available that summarize issues, sorted by object. Most of the reports have a top-to-bottom structure. Selecting an item at the top of the page shows information of that type on the bottom of the page.Each object is ‘hot-linked’ so you can immedi ately drill in to the object, inspect problems, and fix them.Figure 9: This report shows a group of objects with statuses. Hot-links let you drill into individual objects.Oracle Database migrations are highly automated. Most customers experience conversion rates as high as 80 percent. However, each migration is different, and your results may vary. For example, stored procedures may compile with errors that must be addressed. In general, Oracle’s automated procedures dramaticall y reduce the overall migration effort.Dealing with Conversion IssuesThe translators that SQL Developer employs to convert non-Oracle stored procedures to Oracle PL/SQL objects do not translate 100 percent of these procedures. Unique coding practices in each RDBMS prevent some of these database artifacts from being automatically translated to Oracle. These coding gaps also vary from site to site, since each development team has its own approach to utilizing these RDBMS features.Even when all the RDBMS code is translated, make sure you thoroughly test the resulting Oracle Database for accuracy and performance. Some of the programming structures and techniques employed by third-party databases do not have direct equivalents in Oracle. Conversion issues are flagged during the migration process. In most cases, comments are inserted to advise whether additional coding is required before the migration can be considered complete.Opening the invalid objects in SQL Developer’s PL/SQL editor makes it much easy to analyze the code and correct the issues since you can take advantage of built-in error message look-ups, a profiler, and a debugger.Figure 10: Procedure Editor showing compiler errors and code to be translated for the migrated T-SQL Function.For example, the above figure reveals stored procedures that require attention at lines 37 and 38. In this case, fixing the problem could involve creating a stored procedure to collect statistics on tables, instead of inserting the DBMS_STATS package call directly in the stored procedure. Oracle allows you to generate statistics for all or part of a table (for example, you can use 25 percent of the table data to generate the optimizer statistics). This decision requires in-depth knowledge of the application and the data model—in other words, human intervention.How much intervention is required? In one recent migration project, 256 “To-Do” items were logged for 52 PL/SQL objects and 132,000 lines of code. While the number of items will vary depending on the nature of the code, you should budget sufficient time to test and fix code issues after each automated migration procedure. Perform the‘capture’ process early in the project so you can adequately assess the scope of work ahead.Moving the Data – Offline Versus Online MethodsUnless you have a small test database to migrate, use the OFFLINE method. To start the migration from your project, right-click on your project capture and select ‘Move data…’This command will advance the migration wizard to the data step, where you can choose online or offline. Choosing ONLINE will connect to both the source and destination databases, read the data from the source tables, and insert the data into the target tables. This method is only suggested for test environments with small amounts of data. Use the OFFLINE mode for large databases and production databases.The OFFLINE method captures the data to local delimited text files, which feed the Oracle SQL*Loader utility. SQL*Loader is designed to load significant amounts of data very quickly. It automatically logs rejected records so that they can be fixed and moved in a subsequent run. SQL*Loader is included with every copy of Oracle Database on the target Oracle server.To perform the capture, execute the scripts generated by SQL Developer. For SQL Server and Sybase, these scripts use the BCP utility, a Microsoft Command Line Utility that comes with each SQL Server download.Figure 11: Running the MicrosoftSQLServer_data.bat file to export data to local flat files.Once the script has finished you will see a series of scripts and SQL*Loader control and data files in your project directory’s ‘datamove’ and ‘data’ subdirectories.Figure 12: Directory listing showing the SQL*Loader data files.Running SQL*Loader requires either the Oracle Client or an Oracle Database install. You can run theoracle_loader.bat file on your local machine if you have a Client, or you can move the files directly to your Oracle Database server.If your target database is a DBaaS cloud, move the SQL*Loader files to your DBaaS machine and run the scripts directly from there.Verifying Data QualityOnce the data migration has completed, you can perform a data quality assessment, in which raw record counts are compared with the original and converted tables, and deltas are highlighted. Please note that this can take a long time for very large datasets.Figure 13: A Data Quality report, with original and converted tables shown side-by-side.Advanced Data Migrations with Oracle GoldenGate 12cSome migrations require advanced tools. Depending on your particular application needs and service level agreements, you might need to execute a faster data migration to minimize downtime. Similarly, if you need to run a third-party database application side-by-side with your new Oracle application to permit data synching, then you should consider using replication technology such as Oracle GoldenGate 12c, a comprehensive software package for real-time data integration and replication in heterogeneous environments.Oracle GoldenGate 12c is ideal for scenarios requiring high availability, real-time data integration, transactional change data capture, data replication, transformations, and verification between operational and analytical enterprise systems. It boosts performance and simplifies configuration and management, with innate support for cloud environments, expanded heterogeneity, and enhanced security. Talk to your Oracle account manager to discuss licensing, since Oracle GoldenGate 12c is not included with your Oracle Database license. Application MigrationsConnectivity between your applications and the database should be addressed at the outset of your migration project. If you are using a .NET or Java application, you will need to switch your ODBC or JDBC driver to an Oracle driver. After that you’ll want to make sure your applications can connect and interact with your new Oracle Database via the Oracle SQL dialect. While Oracle SQL supports much of the ANSI SQL standard, Oracle’s interpretation of this standard differs from SAP’s interpretation for Sybase, Microsoft’s interpretation for SQL Server, IBM’s interpretation for DB2, and so forth.SQL Developer includes application scanner scripts that allow you to audit the database code to determine what might need to be changed in your database applications. It also offers a Scratch Editor that can translate SQL and stored procedures to their Oracle equivalents, on an ad-hoc basis.A much more powerful interface is the Oracle Database 12c SQL Translation Framework. For Sybase ASE and SQL Server T-SQL, this framework can receive queries in these SQL dialects, translate them to their Oracle equivalents, and execute them on the fly.In addition, Oracle Database 12c includes many other capabilities that decrease the time and cost of migrating applications to Oracle Database. To learn more, please refer to this whitepaper: Migrating Applications and Databases with Oracle Database 12c.Some Practical AdviceStart with a small database or test database. The wizard allows you to roll through the entire process in a single step, but it makes more sense to break it into these fundamental pieces:∙Perform the capture and translateo Verify that all objects are captured and translations are acceptable∙Deploy the new Oracle objectso Verify that the objects are compiled without errors, all objects exist, and that those objects are in the expected Oracle schema∙Move the datao Make sure the time required to copy the data to Oracle meets your project requirements. You can accelerate and fine-tune this process using techniques such as External Tables as well aswith licensed products such as Oracle GoldenGate 12c∙Run a sample application or report to ensure the output is as expectedThe wizard allows you to initiate, stop, and resume the process at any step.ResourcesMigrating to Oracle Database 12c is easier with Oracle SQL Developer’s Migration feature. Suc cessful migrations start by identifying the project requirements, analyzing the data and applications that need to be moved, and adopting a mindset of trial and error. To learn more about migrating to Oracle Database 12c and Oracle Database Cloud, please take a look at the SQL Developer Migrations page and review the information on these Migrations Forums.Oracle Corporation, World Headquarters 500 Oracle ParkwayRedwood Shores, CA 94065, USAWorldwide Inquiries Phone: +1.650.506.7000 Fax: +1.650.506.7200Copyright © 2017, Oracle and/or its affiliates. All rights reserved. This document is provided for information purposes only, and the contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or fitness for a particular purpose. We specifically disclaim any liability with respect to this document, and no contractual obligations are formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without our prior written permission.Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.CO N N E C T W I T H U S //Oracle/Oracle/ /company/oracle/user/Oracle。
12c特性解读：RACMGMTDB资料库的转移与维护
12c特性解读：RACMGMTDB资料库的转移与维护戴明明（Dave）Oracle ACE-A，ACOUG核心成员，宝存科技数据库方案架构师Dave也是CSDN 认证专家，超过7年的DBA经验，擅长Oracle数据库诊断、性能调优，热衷于Oracle 技术的研究与分享。
从14年开始研究基于PCIe闪存卡的数据库高可用，高性能解决方案。
编辑手记：感谢Dave授权我们转载其技术文章，他在博客时代书写的大量文章影响了DBA领域的很多朋友，现在我们精选他的文章，让更多读者受益。
MGMTDB 的数据文件是存放在OCR voting disk的磁盘组里的，为了节省OCR 磁盘组空间，我们也可以把MGMTDB 转移走。
而有时候如果不了解12c的这一特性，可能会遇到OCR空间紧张的情况。
当然，这里的移动位置，也是从一个共享位置移动到另一个共享位置。
相关阅读：12c特性解读：RAC MGMTDB资料库新特性说明及初相识监控工具：Oracle 12c Cluster Health Monitor 详解以下测试说明这个转移的过程和步骤。
1 停止并禁用ora.crf 资源这里的ora.crf就是CHM。
在所有节点使用root用户执行如下命令：[root@rac1 ~]# crsctlstop res ora.crf -initCRS-2673: Attempting to stop 'ora.crf'on'rac1'CRS-2677: Stop of 'ora.crf' on'rac1'succeeded[root@rac1 ~]# crsctlmodify resora.crf -attr ENABLED=0 -init[root@rac1 ~]#[root@rac2 ~]# crsctl stop res ora.crf-initCRS-2673: Attempting to stop 'ora.crf'on'rac2'CRS-2677: Stop of 'ora.crf' on'rac2'succeeded[root@rac2 ~]# crsctl modify resora.crf-attr ENABLED=0 -init[root@rac2 ~]#注意：ora.mgmtlsnr 和ora.mgmtdb资源不能停，否则DBCA 时会报错。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
JAN 2009
JAN 2012
JUL 2010
JUL 2013
AUG 2012
AUG 2015
JAN 2015
JAN 2018
JUN 2018
today
Premier Support
Waived Extended Support Extended Support
JUN 2021
Sustaining Support
CREATE DATABASE cdb12 [...]
ENABLE PLUGGABLE DATABASE SEED FILE_NAME_CONVERT=('/oradata/cdb12/','/oradata/pdbseed/')
SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE […]
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Upgrade, Migrate and Consolidate to Oracle Database 12c
11
Behind the scenes
▪ One SGA, one set of background processes, and one spfile
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Upgrade, Migrate and Consolidate to Oracle Database 12c
6
Multitenant Environment – The Concept!
– DBA administers on the CDB level
▪ A CDB contains always a PDB$SEED and in addition zero, one, or many pluggable databases (PDBs)
– Applications will connect to the PDBs – Up to 252 PDBs in one CDB
▪ Consolidation ▪ Reduce maintenance ▪ Simplified and fast provisioning, cloning and migration ▪ Option is called Oracle Multitenant
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Upgrade, Migrate and Consolidate to Oracle Database 12c
7
Caution!! ☺
▪ Many of the well known concepts will get changed
– Around 150 pages new documentation in the Administrator’s Guide
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Upgrade, Migrate and Consolidate to Oracle Database 12c
4
Is It Time To Upgrade?
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
3
2025 2024 2023 2022 2021 2020 2019 2018 2017 2016 2015 2014 2013 2012 2011 2010 2009 2008 2007 2006 2005 2004 2003 2002
Lifetime Support Policy
today
Oracle 9.2
▪ You don't have to use pluggable databases ... yet ▪ Oracle Database 12c non-CDB works as expected
– But you can also use the new features – and this will require changing old habits
to Oracle Database 12c
Mike Dietrich
Database Upgrade and Utilities
Oracle Corporation
Database Upgrade Development Group
Roy Swonger
Senior Director Software Dev.
Upgrade, Migrate and Consolidate to Oracle Database 12c
12
Creation of a CDB
▪ Two options:
– DBCA ▪ Highly recommended
– Command line CREATE DATABASE
▪ Not recommend as all options will have to be created
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Upgrade, Migrate and Consolidate to Oracle Database 12c
13
Command Line Creation of a CDB
– Benefit due to resource sharing
▪ Applications connect to a PDB
Redo Control Flashback
spfile
PDB$SEED
PDB
PDB
PDB
PDB
CDB$ROOT
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
– Data dictionary – Common user – By default there's no user data in the root
PDB$SEED
OBJ$
TAB$
SOURCE$
CDB$ROOT
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
PDB$ SEED
PDB
PDB
PDB
PDB
CDB
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Upgrade, Migrate and Consolidate to Oracle Database 12c
10
Containers
Upgrade, Migrate and Consolidate to Oracle Database 12c
5
Upgrade, Migrate & Consolidate
▪ Introduction to Multitenant Environments ▪ Roads to Pluggable Databases ▪ Working with Pluggable Databases ▪ Wrap Up
Principal Member Technical Staff
Eric Wittenberg
Principal Software Engineer
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Upgrade, Migrate and Consolidate to Oracle Database 12c
▪ -u Username and optionally password ▪ -d Directory containing the script to execute (default: current directory) ▪ -e Echo on ▪ -s Spools the output of every script ▪ -l Directory to write logfiles into (default: current directory) ▪ -b Base name for logfiles (mandatory option)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Upgrade, Migrate and Consolidate to Oracle Database 12c
14
CDB-PDB: Who's who?
▪ After CDB creation:
(GA: Jul 2002)
Oracle 10.1
(GA: Jan 2004)
Oracle 10.2
(GA: Jul 2005)
Oracle 11.1
(GA: Aug 2007)
Oracle 11.2
(GA: Sep 2009)
Oracle 12.1
(GA: Jun 2013)
JAN 2007