There are three ways of Oracle backup and restore. They are export/import, hot backup and cold backup. Export/import is a logical backup, while hot and cold backups are physical backups. [References( https://blog.csdn.net/happylee6688/article/details/13620937])
Only the way the export/import backups and restores are recorded here. In the actual work, we meet a requirement: we need to back up the database on the server and import it into our own database. It is equivalent to copy ing the corresponding database instance on the server to the local place. The main operation steps are as follows.
1. Database Export
Operating location: database to be backed up, DOS window [Note not SQLPLUS].
exp RSGL_GB_NEW/RSGL@ORCL file=d:\beifen_20190909.dmp owner=(RSGL_GB_NEW)
This command means to export the RSGL_GB_NEW library (the password is RSGL). The exported path is d: beifen_190909.dmp.
After the export is complete, copy the file to the machine that needs to be restored for the second step.
2. Creating table spaces
Operating Location: The machine that needs to restore the database, DOS window, enter the SQLPLUS window after login
C:\Users\zz>sqlplus / as sysdba; SQL*Plus: Release 22.214.171.124.0 Production on Friday, September 2719:13:21 2019 Copyright (c) 1982, 2010, Oracle. All rights reserved. ERROR: ORA-01031: Insufficient authority //Please enter user name: system //Enter password: //Connect to: Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create tablespace RSGL_GB_NEW nologging datafile 'E:\app\oracleServer\product\11.2.0\Oracle\RSGL_GB_NEW.dbf' size 100m autoextend on next 500M maxsize unlimited; //The table space has been created.
Log in to the database, select a user account with the right to create user login, create table space. Then step three.
3. Create users and grant DBA privileges
Operational location: SQLPLUS window
SQL> CREATE USER RSGL_GB_NEW IDENTIFIED BY RSGL DEFAULT TABLESPACE users; //User created. SQL> GRANT CREATE SESSION,CREATE ANY TABLE,CREATE ANY VIEW ,CREATE ANY INDEX,CREATE ANY PROCEDURE, ALTER ANY TABLE, ALTER ANY PROCEDURE,DROP ANY TABLE,DROP ANY VIEW,DROP ANY INDEX, DROP ANY PROCEDURE,SELECT ANY TABLE,INSERT ANY TABLE,UPDATE ANY TABLE,DELETE ANY TABLE TO RSGL_GB_NEW; //The authorization was successful. SQL> grant DBA to RSGL_GB_NEW;
4. Import database
Operating position: DOS window [Note: DOS]
C:\Users\zz>imp RSGL_GB_NEW/RSGL fromuser=RSGL_GB_NEW touser=RSGL_GB_NEW file=D:\RSGL_GN_NEW201909270.dmp ignore=y; Import: Release 188.8.131.52.0 - Production on Friday, September 2719:31:10 2019 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. //Connect to: Oracle Database 11g Enterprise Edition Release 184.108.40.206.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ## Start importing data...
I don't need to add @ORCL after RSGL_GB_NEW/RSGL because I import it locally. That's what the Internet says. I haven't figured out why.
Not familiar with Oracle database, there may be some explanations. If you encounter an error in the actual operation, you can search for the corresponding error prompt, the probability is that the command was written incorrectly.