Oracle database backup and restore Notes

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 11.2.0.1.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 11.2.0.1.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 11.2.0.1.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 11.2.0.1.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.

Tags: Oracle Database SQL sqlplus

Posted on Sat, 12 Oct 2019 02:48:38 -0700 by warmwind