moving data between the data bases in database

Moving data between Data bases

1. Moving data between databases Group Members: ~ Suhana Sultan. ~Aleena Farooq(GR) ~ Maheen Yameen ~ Maham Shahid

2. Subtopics: Describe and use methods to move data SQL Loader, objects, External Tables Oracle data pump, use of data pump Export and Import of data

3. What is data moving: Data moving is the process of transferring data from one storage system or computing environment to another. It refers to the data transfer between different types of file formats, databases and storage systems.

4. Moving data between databases: It is the process of Moving data between two database engines.
It requires careful planning and testing as there are number of sub tasks involved in the process like storage capacity estimation of the target database, testing application and ensuring data confidentially.

5. Data migration process: It involves the following 5 steps; 1)Scope identification 2) Data mapping
3) Option selection
4) Migration
5) Validation

6. Data migration flow:

7. Methods of moving data: Three techniques for moving a database: 1.detach and copy
2.take the database offline and copy the files
3.back up the database and restore it on the other server.

8. Detach and attach A database: Once a database is detached, no users can connect to it, the files are unlocked, and you are free to copy the files to a new location. When you attach the database to a new SQL Server, you have the option of changing the locations of the data and log files

9. Taking the database offline: Taking the Database Offline:-
You can choose to take a database offline and copy the files to a new location as a method for moving the server.

10. Using database backup and restore: Like the attach and detach method, the backup and restore GUI and Transact-SQL commands support changing the file locations to the new SQL Server instance.
After you’ve backed up the database to a disk file or to tape, you can then restore it to the new database location.

11. Sql loader: SQL*Loader is the primary method to load data from external flat files (ASCII/TEXT/CSV) into tables of an Oracle Database.

12. Cont. SQL*Loader works based on a control file, which specifies the location of data, how data should be parsed and interpreted, and target table
where data should be loaded. It also takes inputs from one or more datafiles , which contain data to be loaded into the database.
Output from SQL*Loader for a load session is a table where data is loaded, a log file, and a bad file (with details of bad formatted records) or

13. Data migration using Sql loader:

14. Characteristics: Can load data from multiple datafiles and into multiple tables during same load session.
Can perform remote data loading
Selective data loading (load records based on record values)
Supports complex object relational data.
Supports both conventional and direct path loading Supports loading from non-Oracle database

15. Loading data from non-oracle database: First Non-Oracle database will export the data into a pre-defined file format.
SQL loader will read this file and load the data into Oracle database tables

16. What is external table: External table is a table that come from flat files stored on outside the database .
The flat files are files that stored on OS level .

17. Difference between sql loader and external table: So here is the Difference between SQL loader and external table
If there are multiple primary input datafiles with SQL*Loader loads, a bad file and a discard file are created for each input datafile . With external table loads, there is only one bad file and one discard file for all input datafiles . It is the read only data For modify come to the data File and do the modification

18. ORACLE DATA PUMP:- Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another. It is a Server-based technology. Oracle data pump is available in Orcale 10 g version and above.

19. Oracle data pump migration :

20. CHARACTERISTICS OF DATA PUMP: Enables high speed movement of bulk data and metadata. Simplest approach to export objects from one source to target. Enhanced version of original/legacy import and export utilities. Performed as a server side job. Hence more efficient ( unlike legacy exp /imp where dump file is created at the client side/location)

21. How does data pump move data: There are several Oracle Data Pump methods that you can use to move data in and out of databases. You can select the method that best fits your use case.

22. Expdp and impdp : Data pump is a utility for unloading data and metadata into a set of OS files called a “Dump File Set”. The dump file set can be imported only by the Data pump import utility. The Dump file can be imported on the same or as well as on the other system. The dump file set is made up of one or more disl files that contain table data, metadata, and control information.

23. Privileges: DATAPUMP_EXP_FULL_DATABASE DATAPUMP_IMP_FULL_DATABASE

24. DIRECTORY OBJECT: CREATE DIRECTORY DIR_NAME AS ‘PATH’; GRANT READ WRITE ON DIR TO USER_NAME

25. QUIZ: 1) How many ways of moving data into database. A)2. B)3 C)4 D) Many 2) Which version of oracle support Expdp / Impdp . A) 8i. B) 11g C) 12c. D) 10g

26. ~ Practical work will be performed practically. THANK YOU!