oracle数据迁移至centos步骤

Oracle Data Migration to CentOS: Step-by-Step Guide
Migrating Oracle databases to CentOS involves careful planning, tool selection, and validation to ensure data consistency and minimal downtime. Below is a structured approach covering common tools (Data Pump, GoldenGate, physical file copy) and critical steps.

1. Pre-Migration Preparation

Before starting, complete these essential tasks to avoid disruptions:

• Backup Source Database: Use expdp (logical) or RMAN (physical) to create a full backup of the source database. For example, a logical backup with Data Pump:

  expdp username/password@source_db directory=data_pump_dir dumpfile=full_backup.dmp logfile=backup.log full=y
  

• Check Compatibility: Verify that the CentOS environment meets Oracle’s hardware requirements (CPU, memory, disk space) and that the target Oracle Database version is compatible with the source (check Oracle’s Compatibility Matrix).
• Install Oracle Software on CentOS: Download and install the same Oracle Database version on the CentOS server. Ensure the installation directory structure (e.g., $ORACLE_HOME) matches the source for easier migration.
• Configure Network & Listeners: On the target CentOS server, set up the tnsnames.ora file (in $ORACLE_HOME/network/admin) to define the target database connection. Restart the listener to apply changes:

  lsnrctl stop && lsnrctl start
  

• Prepare Source and Target Directories: Create directories for Data Pump exports (e.g., /u01/app/oracle/dumpfiles) on both servers with appropriate permissions (Oracle user ownership).

2. Method 1: Logical Migration Using Oracle Data Pump (expdp/impdp)

Data Pump is the most common tool for logical migration, ideal for homogeneous environments (Oracle-to-Oracle). It exports metadata (schemas, tables, views) and data into a .dmp file, which is transferred and imported into the target.

Step 1: Export Data from Source Server

Connect to the source database using sqlplus (as SYSDBA) and create a directory object pointing to the export directory:

CREATE OR REPLACE DIRECTORY data_pump_dir AS '/u01/app/oracle/dumpfiles';
GRANT READ, WRITE ON DIRECTORY data_pump_dir TO your_schema;

Run expdp to export the desired schema/database. For a full export:

expdp username/password@source_db directory=data_pump_dir dumpfile=source_export.dmp logfile=export.log full=y

For a schema-specific export (recommended for partial migrations):

expdp username/password@source_db directory=data_pump_dir dumpfile=schema_export.dmp logfile=export.log schemas=your_schema

Step 2: Transfer Dump File to Target Server

Use scp (secure copy) to transfer the .dmp file from the source to the target CentOS server. For example:

scp username@source_centos:/u01/app/oracle/dumpfiles/source_export.dmp /u01/app/oracle/dumpfiles/

Step 3: Import Data into Target Server

On the target CentOS server, create the same directory object and grant permissions:

CREATE OR REPLACE DIRECTORY data_pump_dir AS '/u01/app/oracle/dumpfiles';
GRANT READ, WRITE ON DIRECTORY data_pump_dir TO your_schema;

Run impdp to import the data. For a full import:

impdp username/password@target_db directory=data_pump_dir dumpfile=source_export.dmp logfile=import.log full=y

For a schema-specific import (to avoid conflicts):

impdp username/password@target_db directory=data_pump_dir dumpfile=schema_export.dmp logfile=import.log schemas=your_schema remap_schema=source_schema:target_schema

• Key Flags: Use remap_schema (to change the schema owner), remap_tablespace (to map source tablespaces to target), and table_exists_action (to handle existing tables: skip, append, replace, truncate).

3. Method 2: Physical Migration Using RMAN (for Homogeneous Environments)

Physical migration copies the database files (datafiles, control files, redo logs) directly from the source to the target. This method is faster for large databases but requires identical OS configurations (endian-ness, file system) between source and target.

Step 1: Prepare Source Database

Put the source database in ARCHIVELOG mode (required for RMAN backups) and back it up:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
RUN {
  BACKUP DATABASE FORMAT '/u01/app/oracle/backup/%U';
  BACKUP CURRENT CONTROLFILE TO '/u01/app/oracle/backup/controlfile.bkp';
}

Step 2: Copy Files to Target Server

Transfer all backup files (datafiles, control files, redo logs) from the source to the target CentOS server using scp or rsync:

scp /u01/app/oracle/backup/* username@target_centos:/u01/app/oracle/backup/

Step 3: Restore Files on Target Server

On the target server, restore the control file and mount the database:

STARTUP NOMOUNT;
RESTORE CONTROLFILE FROM '/u01/app/oracle/backup/controlfile.bkp';
ALTER DATABASE MOUNT;

Restore the datafiles:

RESTORE DATABASE;

Recover the database to apply any archived logs:

RECOVER DATABASE;

Open the database:

ALTER DATABASE OPEN RESETLOGS;

Step 4: Validate the Migration

Check database status, schema objects, and data consistency to ensure the migration succeeded.

4. Method 3: Real-Time Migration Using Oracle GoldenGate

GoldenGate is ideal for minimal downtime migrations (near-zero downtime). It replicates data changes from the source to the target in real-time, allowing you to cut over with almost no interruption.

Step 1: Install GoldenGate on Source and Target

Download and install Oracle GoldenGate on both servers. Configure the ggsci (GoldenGate Command Interface) environment on both machines.

Step 2: Configure Extract (Source) and Replicat (Target)

• Extract Process: Captures changes from the source database. Create a parameter file (extract.prm) on the source:

  EXTRACT ext1
  USERID username/password, ROLE SYSDBA
  EXTTRAIL /u01/app/oracle/goldengate/dirdat/ext1
  TABLE your_schema.*;
  

  Start the Extract process:

  ggsci
  ADD EXTRACT ext1, TRANLOG, BEGIN NOW
  START EXTRACT ext1
  

• Replicat Process: Applies changes to the target database. Create a parameter file (replicat.prm) on the target:

  REPLICAT rep1
  USERID username/password, ROLE SYSDBA
  REPTRAIL /u01/app/oracle/goldengate/dirdat/rep1
  MAP your_schema.*, TARGET your_schema.*;
  

  Start the Replicat process:

  ggsci
  ADD REPLICAT rep1, EXTTRAIL /u01/app/oracle/goldengate/dirdat/ext1
  START REPLICAT rep1
  

Step 3: Cutover to Target

Once the target database is in sync with the source (verify with GoldenGate reports), stop applications, finalize the target database (e.g., open it if in read-only mode), and redirect application connections to the target.

5. Post-Migration Validation

Regardless of the method, perform these checks to ensure success:

• Data Integrity: Compare row counts, checksums (e.g., DBMS_CRYPTO.HASH), or sample data between source and target.
• Schema Consistency: Verify that all schemas, tables, indexes, views, and procedures exist and match the source.
• Performance Testing: Run benchmarks (e.g., SQL queries, transactions) to ensure the target database performs as expected.
• Application Testing: Validate that applications connect to the target database and function correctly.

6. Switch Applications to Target Database

Update application configuration files (e.g., tnsnames.ora, JDBC URLs) to point to the target CentOS server’s database. Restart applications and monitor logs for errors. Perform final smoke tests to confirm all features work as intended.

Key Considerations

• Downtime Planning: Use Data Pump for scheduled outages or GoldenGate for near-zero downtime.
• Security: Encrypt data transfers (e.g., SCP with -i for SSH keys) and secure Oracle credentials.
• Logging & Monitoring: Review expdp/impdp logs and GoldenGate reports for errors. Use Oracle Enterprise Manager (OEM) for real-time monitoring.
• Rollback Plan: Keep the source database intact until the target is validated to ensure business continuity.