This is the first day of my participation in the Gwen Challenge in November. Check out the details: the last Gwen Challenge in 2021

preface

  • Online music poke me ah!
  • Music blog source code online!
  • The previous several chapters talked about the installation and deployment of Docker, the operation is relatively simple.
  • Next, we will share how to migrate (import) server A’s database into server B’s database.
  • Are you ready?

Last week I went to shaoguan Yunmen Temple to worship incense. The clouds of smoke remind me of my days in the fairy world. Miss ah ~

Import the Oracle database on server A to the database on server B

  • How do I create a tablespace?

  • How do I create users?

  • Start importing.

When importing a database, not to say to create a user, database, you want to directly import. The difference between Oracle and mysql is that Oracle has tablespaces. So don’t forget to create the tablespace first.

The environment

  • Database: Oracle

  • Server: Oracle database is running in Docker

How to create a tablespace?

1.1 Creating a Folder for storing tablespace files

Create tablespace folder under /home/oracle.

mkdir /home/oracle/tablespace

1.2 Creating a tablespace

There are two types of table Spaces, temporary and regular.

  • Create a temporary tablespace TEST_TEMP
CREATE TEMPORARY TABLESPACE TEST_TEMP

         TEMPFILE '/home/oracle/tablespace/TEST_TEMP.DBF'

         SIZE 32M

         AUTOEXTEND ON

         NEXT 32M MAXSIZE UNLIMITED

         EXTENT MANAGEMENT LOCAL;
Copy the code
  • Create tablespace TEST
CREATE TABLESPACE TEST

         LOGGING

         DATAFILE '/home/oracle/tablespace/TEST.DBF'

         SIZE 32M

         AUTOEXTEND ON

         NEXT 32M MAXSIZE UNLIMITED

         EXTENT MANAGEMENT LOCAL;
Copy the code

How to create a user?

Create user test with password 123456

CREATE USER test IDENTIFIED BY 123456

         ACCOUNT UNLOCK

         DEFAULT TABLESPACE TEST

         TEMPORARY TABLESPACE TEST_TEMP;
Copy the code

Grant user rights.

GRANT CONNECT,RESOURCE TO ithinkdt;

GRANT DBA TOithinkdt; (Optional, DBA is the database administrator.)Copy the code

Now I accidentally created a user, but I want to reassign tablespace to this user, what should I do?

alter user username defaulttablespace userspace; Syg tablespace u1;alter user u1 default tablespace syg;
Copy the code

3. Start importing

The pre-work (table Spaces, users) is ready for import.

3.1 Clearing Procedures

The requirement is to import the database of server A into the database of server B.

The DMP file of database A must be uploaded to server B, and then copied to Oracle container of Docker.

3.2 How do I Copy the DMP File to the Oracle Container?

The command output is as follows: docker cp/server file path Container ID :/ Storage path

3.3 the import

If the DMP file is already in the Oracle container, enter the Oracle container, switch to user root, and run the command to import the database.

Oracle container will not be deployed. Here, teach you to play

Command to import DMP files:

imp test/123456@ 39542.126.84.:1521/hellowinxDB file=/home/data.dmp full=y;
Copy the code

Parameters that

  • Imp:

    • Import command, whose sibling export is exp.

  • The test / 123456:

    • User name/password.

  • 39.542.126.84:1521 / hellowinxDB:

    • Server IP: port number/instance name.

    • Notice that it’s connected by the @ sign.

  • The file = / home/data. DMP:

    • Path to the DMP file.

  • Full = y:

    • Import the entire content of the file, there may be multiple users of the content.

    • Export except ORDSYS MDSYS, CTXSYS ORDPLUGINS, LBACSYS these users of the system of all users of the data.

    • If the default tablespace of the user you are joining to is set to System, the contents of the imported file will be imported to system.

3.4 Import May Fail.

If you are lucky, you will not be able to do this, but if you fail to import friends, please read on.

One of the reasons for import failures: version issues.

Check what the database version is:

select * from product_component_version;
Copy the code

It is clear that the two database versions are different, so how can they be resolved?

There are two options:

  • If it is A newly created Oracle database, delete it to the same version as server A’s database.

  • If you don’t want to delete the database, change the oracle version of the DMP file ETX ETX TEXPORT

Modifying the Oracle database version:

3.5 Garbled characters may occur.

Different codes will result in garbled code. It’s a very common thing.

The root cause is the inconsistency of data coding. According to the principle of troubleshooting data coding, basic can solve the problem of garbled code.

Solution: Look at the code.

SQL > alter database character set;select userenv('language') fromdual; SQL > select * from oracle databaseselect * from nls_database_parameters where parameter ='NLS_CHARACTERSET';
Copy the code

I changed the server character set to GBK and it worked.

alter database character set internal_use ZHS16GBK;
Copy the code

The river

3.5.1 track of oracle and Java

If PLSQL detects no garbled characters, but Java interrupts detects garbled characters, it is possible that the Oracle database encoding is not UTF-8, because Java defaults to UTF-8

3.5.2 Oracle and System Environment Variables

If it’s still garbled, look at the local environment variables.

Add the system environment variable NLS_LANG and keep its value consistent with the database encoding setting, that is, the query result SIMPLIFIED Chinese_china.zhs16GBk obtained in the previous step. Click Save.

Close the current client query tool and open it again to check and verify whether the Chinese encoding has been restored to normal.

Afterword.

When I came into contact with Oracle, I was still working on both front and back ends. In fact, I think it is very helpful for new people to have contact with their personal career.

Because both the front and back end understand, there are many benefits, such as: there is a requirement, maybe the front end to do is possible, but the back end to do, may be better (for performance and so on)

More can know which end will be more appropriate, better ~

If it helps, your likes are my lubricant.

Related literature

Docker-oracle database command line create tablespace and user

View and modify Oracle encoding format methods

Oracle database data displays garbled characters

In the past to recommend

Especially big said my code all do not add semicolons

Lao Shi said that everything is the object, you also believe?

Vue-cli3 builds the component library

Vue implements dynamic routing (and the interviewer blows project highlights)

Axios manipulation in a project you didn’t know about (Core principles of handwriting, compatibility)

VuePress builds project component documentation

Vue koa2 + + nginx deployment

Vue-typescript-admin-template background management system

The original link

Juejin. Cn/post / 703097…