Oracle Database migration-Basics

Oracle Database migration-Basics
Oracle Database migration-Basics

As a developer, We must master database knowledge. However, the company's database is maintained by a dedicated DBA. Generally, we provide an environment address, and then we write crud SQL. In addition, the company's product requirements can be cross-data sources, so that generally do not care about the operations of specific database platforms. As a result, your DB knowledge is seriously insufficient. Today, for some reason, we need to build a demonstration environment on the internet server. As a result, we need to operate the database by ourselves.

Task: Build a demo environment on an internet server, including the server, code, and database.

Solution:

(1) find a new product installer and install it on the server.

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

Analysis:

Solution 1: You can certainly install the product. It looks like the simplest solution. However, since the internal development version is not a problem, there is no unified and complete installation program, you need to work together to find all the products of each department, then install and patch. Because the demo is a development product, the final Code may not be able to run for a long time.

Solution 2 seems simple, but because the Internet server cannot access the database server, you need to perform database migration independently (never done ). After a tangle, I finally selected solution 2 (-_-! ), And then asked for some materials for the test:

Oracle Database Export:

-- 1: Go to sqlplus to create database directory SQL> create directory j (obtain it at will once) as 'e: \ reportback \ 20150429 ';

-- 2: grant SQL> grant read, write on directory j to nc60_fbs1 (database user to be exported) to related users );

-- 3: Exit sqlplus and execute the Export Statement expdp nc60_fbs1/1 dumpfile = nc60fbs1. dmp (name obtained during export) logfile = nc60exp. log directory = pff parallel = 2

Note: dumpfile is a backup file; logfile is a log file; directory is the directory name (create directory) created above; parallel is the parallel line; Note: After dumpfile and logfile, you only need to follow the file name, is the relative path relative to directory. Note: '/data2/expdir' in the preceding section is the path in linux. If it is windows, it is changed to the corresponding address. Remember.

The above operation information is a little too big, and Oracle databases are rarely operated in the past. I am not very familiar with the structure of Oracle databases. The above statements seem familiar (not the first love feeling), in line with the principle that DB should not be operated in disorder. First, let's add some basic knowledge about Oracle databases.

MATERIALS: (many websites)

Relational http://www.cnblogs.com/adforce/p/3312252.html for Oracle users/tablespaces/Tables

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

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

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

Organize:

1. Directory command

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

-- Create directory name as directory (note that windows and linux systems are different in writing); -- view the Created directory Select * from dba_directories; -- delete the Created directory, use drop instead of deleteDrop directory name

2. Relationships between users, tablespace, and tables

The structure of an Oracle database is very different from that of a MySql database. The structure of one database is listed in two ways: physical and logical, that is, the layer-3 mode of a database, two mappings. The physical storage structure of Oracle is very complex. It is not discussed here and has nothing to do with this data migration, the exported DMP file can be considered as a physical storage of Oracle.

In the MySql database, we can create a database and authorize the user the permissions of this database. There are also user concepts in objective El, but the so-called tablespace can be considered as a database, but only a logical organization, and a table is actually the table we operate on.

Reference an image metaphor:

The entire Oracle database is a large Cabinet, which is divided into multiple layers, each layer is a tablespace, and the files in each layer are our tables, the paper in the file and the above data are our data.

-- Create a user and specify the tablespace. Generally, You need to specify the tablespace. The Create user username identified by password is rarely placed in the default tablespace. The default tablespace name temporary tablespace temp; -- You can also add user permissions -- remove the points and add: -- quota Storage (unlimited, 8 k, 10 M etc .) on tablespace name -- alter user IUFO6360330 quota unlimited on users; -- View instance select instance_name from v $ instance; -- Query user select * from dba_users; -- disable user sessions

3. Expdp/exp/impdp/imp Tool

Expdp/impdp: a tool developed by Oracle for DBA to import and export data. It is not a command (it was first executed as a create-like tool ). If there is no problem when installing Oracle or client tools, simply enter the execution statement in cmd.

Exp/imp are client tools, and expdp and impdp are server tools, that is, if we use a remote method, we use the exp and imp tools. If we use the DB server, we use expdp and impdp.

-- Expdp export -- impdp import. Note that remap_schema can also be configured with the remap_table parameter -- remap_schema is user-based, and remap_table is based on the parameter impdp user name/password dumpfile = file. dmp logfile = log file name. log directory = referenced directory parallel = 2 remap_schema = source Username: Target Username

4. Functions of Sqlplus

The oracle operating tool in the command line environment.

Actual migration:

The migration steps are similar to the migration configuration. During implementation, I am too lazy (not a good habit for programmers, but good things are invented by lazy people .) Sqlplus is not installed on your machine. Therefore, you can use DbVisualizer to connect to the server database, execute the directory Creation statement, and then authorize the user (you need to change the user connection and then authorize the user, it is best to Log On As system admin), and then go to the server to execute the expdp command. If you are too lazy to get it, ask QA for a database that was previously backed up.

Obtain the backup file, remotely connect to the internet server, and then execute the Import Statement. Before the import, create a user and assign the table space and permissions to the user. The steps for tablespace and grant are very important. When importing data for the first time, many migration failures are caused because dba and connect permissions are not assigned to the created user.

When creating a user, you must specify tablespace. You can ask DBA for this tablespace or create one by yourself. However, you must specify some parameters for tablespace when creating the tablespace, if it is not a DBA parameter, do not set it in disorder.