Oracle Database Migration-Fundamentals

Oracle Database Migration-Fundamentals

As a developer, database knowledge must be in our hands. But now the company's database is dedicated DBA maintenance, usually give us an environment address, and then we write Crud SQL. And the company's product requirements can cross the data source, so usually not too concerned about the operation of a particular database platform. As a result, your DB knowledge is severely inadequate. Today for some reasons, need to build a demo environment on the external network server, the result needs "tragedy" of their own operational database.

Task: Build the demo environment on the external network server, including the server, code, database.

Solution:

(1) Find a new product installer and then go to the server to install it.

(2) Copy your local development environment to the server.

Analysis:

Scenario 1, certainly can be installed on the product, seems to be the simplest solution. However, due to the internal development version is not a problem, there is no unified complete installation procedures, need to patchwork all departments of the products are gathered up, then installed, and then patched. Because the demo is the development of the product, so the final code is not necessarily able to run, the time will be longer.

Scenario 2, it looks relatively simple, but because the external network server cannot access the database server, so you need to do a separate database migration (never done). Tangled up a bit, and finally to avoid trouble the option 2 (-_-! put himself in the hole), and then with the test for some information:

Oracle Database Export:

--1: Enter sqlplus CREATE database directory    sql> creation directory J (random Fetch, build once) as ' E:\reportback\20150429 ';

Description: DumpFile for backup files, logfile for log files, directory for directory created above (create directory), parallel for parallel, note: DumpFile and logfile only need to follow the file name, is relative to the directory relative path; Note: The above '/data2/expdir ' is the path under Linux, if Windows is the appropriate address, remember.

See the above operation data, a bit large, previously rarely operate the Oracle database, the structure of the Oracle database is not very well understood. The above statement has a sense of déjà vu (definitely not the first love feeling), in the DB do not operate the principle of chaos. First of all, add the basics of Oracle database first.

Information: (Many online)

Oracle User/Table Space/table Relationship http://www.cnblogs.com/adforce/p/3312252.html

EXPDP Tools: http://jingyan.baidu.com/article/9113f81b2040862b3314c757.html

Sqlplus use: http://blog.chinaunix.net/uid-74941-id-85228.html

Directory lives: http://blog.csdn.net/bisal/article/details/24667609

Finishing:

1.Directory command

Directory is used to define a directory object, Oracle uses our defined directory objects to export the data to the specified location, as if we often define a directory variable when programming, and then directly use this variable when programming, so that you do not have to write the variable every time the directory location is inconsistent. Directory information is stored in the system's dba_directories table.

--Create directory directory name as directory (note that Windows and Linux systems are not the same);---View the directory you have created select * FROM dba_directories;--Delete the established directory, Use drop not deletedrop directory directory name

2.User, tablespace, table relationships

The structure of Oracle database and MySQL is very different, while the structure of the database we all from two aspects of the enumeration, physical and logical level, that is, we often say the database of three-layer mode, two kinds of mapping. The physical storage structure of Oracle is complex, not discussed here, and is not related to this data migration, and if hard to say, the exported DMP file can be considered as one of the physical storage of Oracle.

In the MySQL database, we can create a new database and then grant permissions to the user for the library. In Oracel, there is also the concept of the user, but the so-called tablespace can be considered as the database, but the logical organization, table is the actual tables we operate.

A metaphor for quoting an image:

The entire Oracle database is a large cabinet, the cabinet will be divided into multiple layers, each layer is a tablespace, each layer of the file is our table, the paper and the above data is our database.  

--Create user, and specify table space, generally need to specify, put to default tablespace of very few create user username identified by password  default tablespace  tablespace name Temporary tablespace Temp --can also add user rights--to remove the good points, plus:--quota Storage (Unlimited, 8k, 10M etc.) on tablespace tablespace name-Modify user permissions on tablespaces alter users IUFO6360330 Quo Ta Unlimited on users;--View instance select instance_name from v$instance;--query user select  * from dba_users;--Close user session

3.expdp/exp/impdp/imp Tools

The tool developed by Expdp/impdp:oracle for DBAs to import and export data is not a command (the first to be executed as a create-like life). If there is no problem when installing Oracle or client-side tools, you can enter the execution statement directly from the cmd command.

Exp/imp is the client's tool, EXPDP and IMPDP are server-side tools, that is, if we use the EXP and IMP tools in a remote way, with EXPDP and IMPDP on the DB server.

--EXPDP Export--IMPDP Import, note Remap_schema can also configure remap_table parameters--remap_schema user-based, remap_table parameter-based IMPDP username/password dumpfile= File. dmp logfile=log file name. Log directory= referenced directory parallel=2 remap_schema= source user name: Target user name

The role of 4.Sqlplus

Oracle Operations tools in the command-line environment.

Actual migration:

The migration steps and the migration configuration to understand the almost, the implementation of the time, because I was too lazy (not a good habit for programmers, but the good things are invented by lazy people.) ) did not install Sqlplus on their own machine, so with Dbvisualizer connected to the server database, the creation of directory to execute the statement, and then to the user authorization (to change the user connection re-authorization, preferably with the system admin login), After going to the server to execute EXPDP life, too lazy to get, to QA to a previous backup of the library.

Get the backup file, remote to the extranet server, then execute the import statement, create a new user before performing the import, and assign the user table space and permissions. The steps to Tablespace and grant separately are very important. The first time the data was imported, because the user after create was not assigned the DBA and connect permissions, many migration failures were caused.

When creating a user, pay attention to specifying tablespace, the tablespace can ask the DBA, or create one yourself, but you create a time to specify the parameters of Tablespace, if not dba these parameters are not set up in disorder.

Oracle Database Migration-Fundamentals