Oracle database migration training

1.Overview

1.1 What is migration?

Before we discuss database migration, let's have a see migration in some other domain

• Animal migration, the travelling of long distances in search of a new habitat
• Human migration, physical movement by humans from one area to another
• Bird migration:One of the most distinct behaviors of many birds is that of migration, usually following seasonal changes. Some birds migrate over very long distances while others will only migrate shorter distances. Some birds migrate only irregularly or in one direction, such as dispersal of flocks after breeding. Non-migrating birds are referred to as sedentary. True migration occurs seasonally and these trips are some of the most exhausting and spectacular journeys of any animal on planet Earth.

• Fish migration: Many types of fish undertake migrations on a regular basis, on time scales ranging from daily to annual, and with distances ranging from a few meters to thousands of kilometers.

• Migration in Information Technology
• Storage migration
• Application migration
• Business process migration
• Database migration

Similarly, it may be necessary to move from one database vendor to another, or to upgrade the version of database software being used. The latter case is less likely to require a physical data migration, but this can happen with major upgrades. In these cases a physical transformation process may be required since the underlying data format can change significantly. This may or may not affect behavior in the applications layer, depending largely on whether the data manipulation language or protocol has changed – but modern applications are written to be agnostic to the database technology so that a change from Sybase, MySQL, DB2 or SQL Server to Oracle should only require a testing cycle to be confident that both functional and non-functional performance has not been adversely affected.

1.2 Why migration?

• Change new server.

When the hardware is too old, we bought a new server, at that time, we need to migrate the database to the new server.

• Start a new project.

In develop environment, when we add a new application, we need to create a new database and copy data from an exists database.

• To complete a test task.

In test environment, we need to test application which might broken the data, at that time, we need to copy a new database for that goal.

• Deploy a production environment.

When one project completed, we need to deploy production environment, sometimes we need to copy the database from develop environment to production environment.

1.3 How to migration?

There are 3 methods to classify migration

1. Cold backup and Hot backup
2. Physical backup and Logical backup.
3. Full backup and Increment backup

 

• Cold database backup

During a cold backup, the database is closed or locked and not available to users. The datafiles do not change during the backup process so the database is in a consistent state when it is returned to normal operation.

• Hot database backup

Some database management systems offer a means to generate a backup image of the database while it is online and usable ("hot"). This usually includes an inconsistent image of the data files plus a log of changes made while the procedure is running. Upon a restore, the changes in the log files are reapplied to bring the copy of the database up-to-date (the point in time at which the initial hot backup ended).

• Physical backup

It is actually the backup process to copy the operating system files from one place to another, usually from the disk to the tape.

• Logical backup

It use SQL language to extract data from the database and save it into binary files.

• Full backup

Full backup, just as its name implies, namely the complete copy of your entire data set. Although full backup data offers the best protection, but some organizations just to use it regularly, because to do a full backup is very time consuming, and often requires a lot of tape or disk.

• Increment backup

Since the full backup is so time consuming, therefore, as a kind of to reduce to do a backup time consuming by the way, the incremental backup is introduced. Incremental backups only changed data after the last backup.

2.Some basic knowledge before migration.

2.1 Oracle database architect.

2.1.1 Instance architect

2.2.2 Database architect

The files that constitute an Oracle database are organized into the following:

• Control files:

Contain data about the database itself (that is, physical database structure information). These files are critical to the database. Without them, you cannot open data files to access the data within the database.

• Data files:

Contain the user or application data of the database

• Online redo log files:

Allow for instance recovery of the database. If the database crashes and does not lose any data files, then the instance can recover the database with the information in these files.

• Parameter file:

Is used to define how the instance is configured when it starts up

• Password file:

Allows users to connect remotely to the database and performadministrative tasks

• Backup files:

Are used for database recovery. You typically restore a backup file when a media failure or user error has damaged or deleted the original file.

• Archive log files:

Contain an ongoing history of the data changes (redo) that are generated by the instance. Using these files and a backup of the database, you can recover a lost data file. That is, archive logs enable the recovery of restored data files.

• Trace files:

Each server and background process can write to an associated trace file. When an internal error is detected by a process, the process dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, whereas other information is for Oracle Support Services.

• Alert log files:

Also known as alert logs, these are special trace files. The alert log of a database is a chronological log of messages and errors. Oracle recommends reviewing these files.

2.2 Oracle start process.

• Starting Up an Oracle Database Instance: NOMOUNT

Starting an instance includes the following tasks:

• Searching <oracle_home>/database for a file of a particular name in this order:

- spfile<SID>.ora

- If not found, spfile.ora

- If not found, init<SID>.ora

• Allocating the SGA

• Starting the background processes

• Opening the alert<SID>.log file and the trace files Note: SID is the system ID, which identifies the instance (for example, ORCL).

• Starting Up an Oracle Database Instance: MOUNT

Mounting a database includes the following tasks:

• Associating a database with a previously started instance

• Locating and opening the control files specified in the parameter file

• Reading the control files to obtain the names and statuses of the data files and online redo log files. However, no checks are performed to verify the existence of the data files and online redo log files at this time.

To perform specific maintenance operations, start an instance and mount a database, but do not open the database. For example, the database must be mounted but must not be opened during the following tasks:

• Renaming data files (Data files for an offline tablespace can be renamed when the database is open.)

• Starting Up an Oracle Database Instance: OPEN

Opening the database includes the following tasks:

• Opening the online data files

• Opening the online redo log files

If any of the data files or online redo log files are not present when you attempt to open the database, then the Oracle server returns an error.

During this final stage, the Oracle server verifies that all the data files and online redo log files can be opened and checks the consistency of the database. If necessary, the System Monitor (SMON) background process initiates instance recovery.

You can start up a database instance in restricted mode so that it is available to users with administrative privileges only. To start an instance in restricted mode, select the “Restrict access to database” option on the Advanced Startup Options page.

2.3 Oracle shutdown process.

• Shutdown Normal

Normal is the default shutdown mode. A normal database shutdown proceeds with the following conditions:

• No new connections can be made.
• The Oracle server waits for all users to disconnect before completing the shutdown.
• Database and redo buffers are written to disk.
• Background processes are terminated and the SGA is removed from memory.
• The Oracle server closes and dismounts the database before shutting down the instance.
• The next startup does not require an instance recovery.

 

• Shutdown Transactional

A transactional shutdown prevents clients from losing data, including the results from their current activity. A transactional database shutdown proceeds with the following conditions:

• No client can start a new transaction on this particular instance.
• A client is disconnected when the client ends the transaction that is in progress.
• When all transactions have been completed, a shutdown occurs immediately.
• The next startup does not require an instance recovery.

 

• Shutdown Immediate

Immediate database shutdown proceeds with the following conditions:

• Current SQL statements being processed by the Oracle database are not completed.
• The Oracle server does not wait for the users who are currently connected to the database to disconnect.
• The Oracle server rolls back active transactions and disconnects all connected users.
• The Oracle server closes and dismounts the database before shutting down the instance.
• The next startup does not require an instance recovery.

 

• Shutdown Abort

If the NORMAL and IMMEDIATE shutdown options do not work, you can abort the current database instance. Aborting an instance proceeds with the following conditions:

• Current SQL statements being processed by the Oracle server are immediately terminated.
• The Oracle server does not wait for users currently connected to the database to disconnect.
• Database and redo buffers are not written to disk.
• Uncommitted transactions are not rolled back.
• The instance is terminated without closing the files.
• The database is not closed or dismounted.
• The next startup requires instance recovery, which occurs automatically. Note: It is not advisable to back up a database that is in an inconsistent state.

2.4 Practice: Start/Shutdown Database

2.4.1 Practice 1: Start/Shutdown Database on linux

 

Step 1: start/stop db

1. export ORACLE_SID=orcl
2. sqlplus "/as sysdba"
3. startup open/ shutdown immediate

Step 2 : start/stop listener

1. lsnrctl start / lsnrctl stop

2.4.2 Practice 2: Start/Shutdown Database on windows

Method 1 (The same as linux)

Method 2 (Start or stop service)

1. start db: start the service of "oracleserviceSID"
2. start listener: start the service of "OracleOraDb11g_home1TNSListener"

3.Migration practice

3.1 Cold backup/restore

Cold backup is also called offline backup.The process to cold backup is

1. Shutdown the source database completely.
2. Copy the spfile,control file, datafile from source database to target database.
3. Modify some settings and start target database.

• Notice:
• Must shutdown the source database.
• Must copy all datafiles.

 

• Error case:
• Error case 1: Only copy one file.
• Error case 2: Only delete one file.

 

3.1.1 Practice 3: Migration database on windowns by cold backup/recover

Prepare:

2 servers with the same OS and oracle version.

Need to do:

Need to migration one DB to another server.

Step:

1. To check which file need to move.
2. Shutdown source db.
3. Move files.
4. Create instance on target db.
5. Post configuration. Startup target db.

 

3.2 Hot backup/restore

3.2.1 Exp/imp

3.2.2 Expdp/Impdp

What Is Data Pump Export?

Data Pump Export (hereinafter referred to as Export for ease of reading) is a utility for unloading data and metadata into a set of operating system files called a dump file set. The dump file set can be imported only by the Data Pump Import utility. The dump file set can be imported on the same system or it can be moved to another system and loaded there.The dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. The files are written in a proprietary, binary format. During an import operation, the Data Pump Import utility uses these files to locate each database object in the dump file set.

What EXPDP can do?

• export table
• export schema
• export tablespace
• export database

Useful expdp functions:

• COMPRESSION Reduce the size of a dump file. Valid keyword values are: ALL, DATA_ONLY, METADATA_ONLY] and NONE.

What Is Data Pump Import?

Data Pump Import (hereinafter referred to as Import for ease of reading) is a utility for loading an export dump file set into a target system. Import can also be used to load a target database directly from a source database with no intervening dump files. This is known as a network import.

What can Impdp do?

• import table
• import schema
• import tablespace
• import database

Some useful impdp functions:

• REMAP_SCHEMA Objects from one schema are loaded into another schema.
• REMAP_TABLESPACE Tablespace object are remapped to another tablespace.

3.2.3 Practice 2: Migration ATG schemas(CATA, CATB, CORE, PUB)

• export dump file from source db.
• copy the dump file to target db.
• create empty user on target db.
• import the dump file to target db.

• --expdp system/oracle@orcl DIRECTORY=db_backup DUMPFILE=FALABELLA_AR_20130423.DMP SCHEMAS=FALABELLA_AR_PUB,FALABELLA_AR_CATA,FALABELLA_AR_CATB,FALABELLA_AR_CORE,FALABELLA_AR_TAX logfile=expdp_FALABELLA_AR_20130423.log CONTENT=ALL compression=ALL
  createdirectory db_backup as"/opt/oracle/db_backup";
  grantallondirectory db_backup topublic;
  dropuserFALABELLA_AR2_CATBcascade;
  dropuserFALABELLA_AR2_CATAcascade;
  dropuserFALABELLA_AR2_PUBcascade;
  dropuserFALABELLA_AR2_COREcascade;
  dropuserFALABELLA_AR2_TAXcascade;
  CREATEUSERFALABELLA_AR2_CATA IDENTIFIED BYFALABELLA_AR2_CATA QUOTA UNLIMITED ONTBS_FALABELLA
  DEFAULTTABLESPACE TBS_FALABELLA
  TEMPORARYTABLESPACETEMP
  /
  GRANTCONNECT, RESOURCE,DBA TOFALABELLA_AR2_CATA;
  CREATEUSERFALABELLA_AR2_CATB IDENTIFIED BYFALABELLA_AR2_CATB QUOTA UNLIMITED ONTBS_FALABELLA
  DEFAULTTABLESPACE TBS_FALABELLA
  TEMPORARYTABLESPACETEMP
  /
  GRANTCONNECT, RESOURCE,DBA TOFALABELLA_AR2_CATB;
  CREATEUSERFALABELLA_AR2_CORE IDENTIFIED BYFALABELLA_AR2_CORE QUOTA UNLIMITED ONTBS_FALABELLA
  DEFAULTTABLESPACE TBS_FALABELLA
  TEMPORARYTABLESPACETEMP
  /
  GRANTCONNECT, RESOURCE TOFALABELLA_AR2_CORE;
  CREATEUSERFALABELLA_AR2_PUB IDENTIFIED BYFALABELLA_AR2_PUB QUOTA UNLIMITED ONTBS_FALABELLA
  DEFAULTTABLESPACE TBS_FALABELLA
  TEMPORARYTABLESPACETEMP
  /
  GRANTCONNECT, RESOURCE,DBA TOFALABELLA_AR2_PUB;
   
  CREATEUSERFALABELLA_AR2_TAX IDENTIFIED BYFALABELLA_AR2_TAX QUOTA UNLIMITED ONTBS_FALABELLA
  DEFAULTTABLESPACE TBS_FALABELLA
  TEMPORARYTABLESPACETEMP
  /
  GRANTCONNECT, RESOURCE,DBA TOFALABELLA_AR2_TAX;
  alteruserFALABELLA_AR2_CATA account lock;
  alteruserFALABELLA_AR2_CATB account lock;
  alteruserFALABELLA_AR2_CORE account lock;
  alteruserFALABELLA_AR2_PUB account lock;
  alteruserFALABELLA_AR2_TAX account lock;
   
  --impdp system/oracle@orcl DIRECTORY=db_backup DUMPFILE=FALABELLA_AR_20121129.DMP LOGFILE=impdp_FALABELLA_AR2_20121129.log SCHEMAS=FALABELLA_AR_TAX,FALABELLA_AR_CORE,FALABELLA_AR_PUB,FALABELLA_AR_CATA,FALABELLA_AR_CATB REMAP_SCHEMA=FALABELLA_AR_TAX:FALABELLA_AR2_TAX,FALABELLA_AR_CORE:FALABELLA_AR2_CORE,FALABELLA_AR_PUB:FALABELLA_AR2_PUB,FALABELLA_AR_CATA:FALABELLA_AR2_CATA,FALABELLA_AR_CATB:FALABELLA_AR2_CATB CONTENT=ALL PARALLEL=1 REMAP_TABLESPACEA=TBS_FALABELLA:TABLE_USER TABLE_EXISTS_ACTION=REPLACE
    
  alteruserFALABELLA_AR2_CATA account unlock;
  alteruserFALABELLA_AR2_CATB account unlock;
  alteruserFALABELLA_AR2_CORE account unlock;
  alteruserFALABELLA_AR2_PUB account unlock;
  alteruserFALABELLA_AR2_TAX account unlock;

  
  
  

4.Q&A