by Kevin Klapak
Learn how to use commonly available and understood tools to migrate, convert, and upgrade Oracle Database instances when moving from Linux to Oracle MiniCluster.
|
Introduction
This article is organized into three main parts; background information; a summary of the database migration process; and a detailed step-by-step example of a database migration, conversion and upgrade including commands, screen output, and notes that reflect things to be aware of.
The process utilizes a combination of tools including Oracle Data Pump, Transportable Table Spaces, and Oracle Recovery Manager (Oracle RMAN). These tools are readily available, require no additional licensing, and are commonly understood by experienced Oracle Database administrators. During this process, the databases will be offline and not available to users. The licensed options Oracle Active Data Guard and Oracle GoldenGate can alternatively be used for migrations either for convenience or to minimize downtime during the transition process.
| |
|
Oracle Optimized Solutions provide tested and proven best practices for how to run software products on Oracle systems. Learn more.
|
|
For more information on planning considerations for the migration process and a summary of the available techniques, please see "Architecture Matters, Part 1: Assessing Application Migration Impact."
Background on Oracle MiniCluster S7-2
Oracle MiniCluster S7-2 is a simple and efficient engineered system designed to run enterprise databases and applications with designed-in security. The following list outlines some advantages of using Oracle MiniCluster over general-purpose Linux systems for Oracle Database workloads.
-
Automation and simplified management. Built-in browser-based management dramatically simplifies and accelerates day-to-day operation, eliminating the need for specialized system administration skills and training (see Figure 1 and Figure 2 as examples). Oracle MiniCluster provides an easy, error-free approach to implement Oracle Real Application Clusters (Oracle RAC) and highly available Oracle Database instances, to configure security compliance according to policies and regulations, and to execute full system patches though pretested and verified rolling upgrades. The automation provided enables administration and maintenance with predictable outcomes and less risk than operating general-purpose Linux-based implementations.
Figure 1: Example view of the Oracle MiniCluster home and status screen.
-
Secure by design. Configuring a standard Linux distribution to comply with security best practices requires between 200 to over 250 specific items to be set, altered, or verified, depending on the regulatory compliance environment. In contrast, Oracle MiniCluster automates the security setup with built-in tooling and enables users to select among security configuration profiles to easily comply with DISA-STIG, PCI-DSS, and CIS equivalent profiles. Figure 2 shows the simplicity of applying a security profile to an Oracle MiniCluster virtual machine group. Current compliance with the selected security policies can also be easily verified by running a compliance report with the click of a button.
Figure 2: Selecting the database system security profile during setup
-
Optimized for Oracle Database performance. Integrated, shared flash storage coupled with dual compute nodes based on Oracle's SPARC S7 processor provide leading performance and efficiency for running Oracle Database. Innovative Software in Silicon features built into the SPARC S7 processor accelerate query and analytic performance, provide high-speed data encryption, and incorporate Silicon Secured Memory, the first ever memory protection to secure data while it is in memory. To learn more about Software in Silicon, please see "Understanding the Security and Performance Advantages of a Complete Oracle Solution with Software in Silicon" or "Software in Silicon: What It Does and Why."
For more information about Oracle MiniCluster design considerations, see the following video: "3 Advances in Oracle MiniCluster Engineered System."
https://www.youtube.com/watch?v=WiNoG_Rem6s
Summary of the Migration Process
Implications of Converting Between Processor Types
With SPARC S7 processors at the core, Oracle MiniCluster uses big-endian byte ordering while Intel processors utilize little-endian byte ordering. Therefore, when a database is transferred from a Linux and Intel-based source to an Oracle MiniCluster target, a conversion will need to take place. Fortunately, Oracle RMAN provides for easy conversion during the migration process.
Additionally, if a database incorporates stored procedures, those procedures will require a recompile step to ensure they operate properly in the new environment.
Migration Steps
The following provides a short summary of the steps required to migrate an Oracle Database instance from a Linux system based on an x86 processor architecture to Oracle MiniCluster S7-2.
- Run the
expdp command to export the tablespaces. This command exports the necessary metadata needed to import the data on the destination system as a .dmp file in the dpump directory.
- Use the
rman command to convert tablespaces from Linux x86-64 little-endian format to Oracle Solaris and SPARC 64-bit big-endian format. rman converts the selected database tablespaces to the proper endianness, and outputs the converted tablespace as a .dbf file at the location specified by the format parameter.
- Move the exported files to a directory on the target system. To access the files generated by
rman and expdp on the target system, copy them directly to the machine or to a shared storage device that can be mounted by the target system.
- Copy the datafiles into the Oracle Automatic Storage Management (Oracle ASM) +DATA diskgroup, which is the file system used for the databases on an Oracle MiniCluster S7-2 engineered system.
- On the target system, do some initial setup (for example, time zone, locks, user privileges, and so on) and set parameters to locate the import and destination directories.
- Run the
impdp command to import the exported database. This will import the tablespaces and metadata files generated earlier on the x86 system.
- Check the import log file for errors. For any compilation errors of stored procedures, recompile any invalid objects.
- Run validation tests on the database to determine if the database has been migrated properly.
The general steps outlined above will apply for most database applications. The next section provides a detailed step-by-step procedure for migration and database upgrade from a Linux system to an Oracle MiniCluster S7-2 engineered system.
Prior to using the steps in the next section, it is recommended that you use the Oracle MiniCluster virtual assistant to create a database instance, which will be used as the target database. Information on creating a database instance using the virtual assistant can be found in the Oracle MiniCluster S7-2 Administration Guide.
Step-by-Step Migration Example
For this example, the source is an Oracle Database 11_g_ Release 2 instance running in a VMware virtual machine (VM) hosted on Red Hat Enterprise Linux 7 running on an Intel-based server (rhel-vmware-db). The target environment is an Oracle Database 12.1.0.2 instance hosted in a database VM on an Oracle MiniCluster S7-2 engineered system running Oracle Solaris 11.3 (oos-sn1-01).
1. Connect to the SQL server on source system:
[oracle@rhel-vmware-db]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 16 22:39:21 2015
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
SQL>
2. Create a directory for the exported tablespace metadata dump files:
SQL> create directory dpump_dir as '/shared/dpump';
Note: Database accounts require the EXP_FULL_DATABASE role to export full databases and the IMP_FULL_DATABASE role to import full databases. The SYSTEM account has these roles by default.
3. Check the source and destination endian format and determine if a conversion is required:
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
------------------------------------------------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux x86 64-bit Little
16 Apple Mac OS Big
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
20 Apple Mac OS (x86-64) Little
4. Invoke the Oracle Data Pump export utility expdp, which requires the EXP_FULL_DATABASE user role.
[oracle@rhel-vmware-db dev]$ expdp system/password@odbonrhel FULL=Y TRANSPORTABLE=ALWAYS VERSION=12 directory=dpump_dir dumpfile=export.dmp
Export: Release 11.2.0.4.0 - Production on Tue Jul 21 06:20:12 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. 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 Starting
"SYSTEM"."SYS_EXPORT_FULL_01": system/********@odbonrhel
TRANSPORTABLE=ALWAYS FULL=Y VERSION=12 directory=dpump_dir
dumpfile=export.dmp Estimate in progress using BLOCKS method... Processing
object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE Processing
object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK Processing object type
DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object
type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA Processing object type
DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object
type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Total estimation using BLOCKS
method: 87.62 MB ... ... ... Processing object type
DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER . . exported
"SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 0 KB 0 rows . .
exported "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB 0 rows .
. exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
*****************************************************************************
* Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/shared/dpump/export.dmp
*****************************************************************************
* Datafiles required for transportable tablespace SOE:
+DATA/odbonrhel/datafile/soe.275.885484559
Datafiles required for transportable tablespace USERS:
+DATA/odbonrhel/datafile/users.259.881417871
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Tue Jul 21
06:23:06 2015 elapsed 0 00:02:53
Note:
- FULL=Y TRANSPORTABLE=ALWAYS, as specified for a full transportable export.
- VERSION=12 if the COMPATIBLE setting for the source database is lower than 12.0. The source database must be Oracle Database 11_g_ Release 2 (11.2.0.3) or higher for a full transportable import.
Note: Warnings can be generated if unneeded schemas are exported. In this example, warnings were observed regarding ORDSYS. Use the exclude command to remove schemas from the export, for example:
exclude=SCHEMA:"IN ('ORDSYS','ORDPLUGINS','SI_INFORMTN_SCHEMA','ORDDATA','MDSYS)"
Note: In the export output above, the datafiles needed for import are listed (in bold).
5. Set the tablespaces required for export (listed in the expdp output) to read-only mode prior to launching Oracle RMAN:
SQL> alter tablespace soe read only;
Tablespace altered.
SQL> alter tablespace users read only;
Tablespace altered.
SQL> quit
Note: Oracle RMAN requires tablespaces to be in read-only mode to ensure the database image created is consistent. The Oracle RMAN export will fail if the tablespace is in read/write mode.
6. Use the rman command to perform the endianness conversion of each tablespace listed in the Oracle Data Pump output. Below is an example of converting the soe tablespace:
[oracle@rhel-vmware-db dev]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jun 17 09:54:52
2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights
reserved. connected to target database: ODBONRHE (DBID=2884168843)
RMAN> convert tablespace soe
2> to platform "Solaris[tm] OE (64-bit)"
3> format "/shared/converted/soe.dbf";
Starting conversion at source at 17-JUN-15 using target database control file
instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1:
SID=39 device type=DISK channel ORA_DISK_1: starting datafile conversion
input datafile file number=00005
name=+DATA/odbonrhel/datafile/soe.275.882517531 converted
datafile=/shared/converted/soe.dbf channel ORA_DISK_1: datafile conversion
complete, elapsed time: 01:33:17 Finished conversion at source at 17-JUN-15
RMAN> quit
Note: In this example, the converted datafiles are being create at /shared/converted, which is an NFS share accessible to both the source and target machines. This saves time by not needing to copy files locally to the target. If you do not have shared storage, you will need to transfer the files to the target machine before copying them to the Oracle ASM diskgroup.
7. From the Oracle MiniCluster, transfer the Oracle Database datafiles to the Oracle ASM +DATA diskgroup on Oracle MiniCluster:
Copied soe.dbf users.dbf to ASM diskgroup (see note below).
Copied export.dmp to target server '/export/home/oracle/dpump'
| Details on moving the datafiles into the Oracle ASM Diskgroup on Oracle MiniCluster S7-2.
The following steps show how to migrate datafiles into an Oracle ASM diskgroup, which is used on the Oracle MiniCluster S7-2 engineered system.
a. On the target system, check where your datafiles are located on the Oracle ASM diskgroup:
SQL> SELECT NAME FROM V$DATAFILE;
FILE_NAME -------------------------------------------
+DATA/ORCL/DATAFILE/system.272.915393283
+DATA/ORCL/DATAFILE/sysaux.264.915393239
+DATA/ORCL/DATAFILE/undotbs1.263.915393341
+DATA/ORCL/DATAFILE/undotbs2.273.915393435
4 rows selected.
b. Verify that Oracle environment variables are set via the oraenv script located in the database home /bin directory. ORACLE_SID should be set to +ASM_node_ if you are using Oracle RAC or to +ASM otherwise.
-bash-4.1$ **cd $ORACLE_HOME/bin** -bash-4.1$ . **oraenv** ORACLE_SID = [orcl1] ? +ASM1 The Oracle base has been set to /u01/app/oracle -bash-4.1$
c. Start asmcmd to copy the datafiles to the Oracle ASM diskgroup:
-bash-4.1$ **asmcmd -p** ASMCMD [+] > cp /shared/converted/soe.dbf +DATA/ORCL/DATAFILE copying cp /shared/converted/soe.dbf -> +DATA/ORCL/DATAFILE/soe.dbf ASMCMD [+] > cp /shared/converted/users.dbf +DATA/ORCL/DATAFILE copying cp /shared/converted/users.dbf -> +DATA/ORCL/DATAFILE/users.dbf
d. Check to see if the datafiles exist in Oracle ASM:
ASMCMD [+] > **ls +DATA/ORCL/DATAFILE** system.272.915393283 sysaux.264.915393239 undotbs1.263.915393341 undotbs2.273.915393435 soe.dbf users.dbf
e. Remove the old datafiles:
-bash-4.1$ **rm /shared/converted/soe.dbf /shared/converted/users.dbf** |
8. Connect to the destination database server:
oracle@oos-sn1-01:~$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 22 04:28:50 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved. 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
SQL>
9. Create a directory for the metadata import:
SQL> create directory dpump_dir as '/export/home/oracle/dpump';
10. Verify or set the destination database time zone to be consistent with the source database:
SQL> select dbtimezone from dual;
DBTIME ------ +00:00 1 row selected.
SQL> alter database SET TIME_ZONE '+00:00'
SQL> quit
Note: Time zone mismatches might prevent completion of the database import. Additionally, changing the time requires a database restart.
11. On the target system, drop the existing "users" tablespace; otherwise, the import will fail with an error indicating that the "users" tablespace already exists:
SQL> alter database default tablespace system;
SQL> drop tablespace users including contents and datafiles;
SQL> quit
12. Create the parameter input file import.par with the parameters below. To avoid issues with whitespace and quotation marks in the command line, using a text editor is recommended.
DUMPFILE=export.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES=
'+DATA/ORCL/DATAFILE/soe.dbf', '+DATA/ORCL/DATAFILE/users.dbf'
LOGFILE=import.log
Note: For the TRANSPORT_DATAFILES parameter, use the locations generated during the earlier step for moving datafiles into an Oracle ASM diskgroup.
13. Run the impdp utility as a user with IMP_FULL_DATABASE role:
oracle@oos-sn1-01:~$ impdp system/password parfile='import.par'
14. Check the import log file for errors. The log file will be located in the dpmp_dir directory; for this example, it is located at /export/home/oracle/dpump.
Note: For Oracle Database versions prior to 12.1.0.1, ORA-31684 errors might be observed but can be ignored. For Oracle Database 12.1.0.1, Oracle Data Pump does not export certain default system objects that already exist in the target; therefore, these errors messages will not occur.
Note: For stored procedure compilation errors, execute the $ORACLE_HOME/rdbms/admin/utlrp.sql script to recompile any invalid objects.
15. Perform post-migration validation or testing according to your normal practice.
16. Verify that the database is operational on the Oracle MiniCluster S7-2 engineered system:
oracle@oos-sn1-01:~$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 22 04:28:50 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved. 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
SQL> conn soe/soe
Connected.
SQL> select count(*) from product_descriptions;
COUNT(*) ---------- 1000
SQL> select count(*) from logon;
COUNT(*) ---------- 357447600 ...
Conclusion
Oracle MiniCluster S7-2 is an extremely simple and efficient engineered system designed to run enterprise databases and applications with uncompromising security. Its simplicity, out-of-the-box performance and reliability, and small form factor make it an excellent choice for remote offices, small offices, and agile software development (DevOps) environments.
The secure-by-design approach serves to deliver the utmost security and compliance with a host of regulations all while radically simplifying the process. To learn more about Oracle MiniCluster security, please see "Oracle MiniCluster S7-2 Platform Security." Additionally, for more information on migration practices and options please see the references below.
See Also
About the Author
Kevin Klapak is a product manager on the Oracle Optimized Solutions team. He joined the team in January 2015 after completing his master's degree from Carnegie Mellon University. He has a background in computer science and over five years of IT experience. Since joining Oracle, he has been working on projects related to database migration, Apache Spark and big data analytics, and systems security.
Follow us:
Blog | Facebook | Twitter | YouTube