Oracle database Backup, recovery, and FAQs

Typically, we use PL/SQL developer to manage data objects and data in Oracle. If we want to quickly restore or deploy Oracle to a new machine, how to backup the database, import and restore it?

Here's what we have to consider: How to create an Oracle tablespace, create an Oracle database, and export Oracle objects and table data into SQL scripts.

1. Database backup

(1) Exporting database objects

Tools-->exportuser Objects, select the database objects you want to export, including: tables, sequences, stored procedures, views, functions, and so on.

(2) Export table data

Tools-->export Tables, export the data to the PL/SQL database script.

2. New table space and Oracle database

File-->new-->sql windows, open the SQL window input:

Table space, user name is IGSC.

CreateTablespace IGSC datafile'E:\IGSOracle\IGSC.dbf'size 10240M;--DROP tablespace IGSC including CONTENTS and datafiles CASCADE CONSTRAINTS;Create UserIGSC identified byIGScdefaulttablespace IGSC; GrantConnect,resource toIGSC; GrantDba toIGSC;--Revoke dba from IGSC;

Click Execute SQL. At this point, the database table space, table structure, table data is complete, you can begin to migrate out a complete database.

3. Import databases and objects

Tools-->import Tables, import the database object first, then import the table data, at this time, your table data file can be imported into several files will not be error, because the table structure already exists.

The following interface will appear during execution:

Here, the migration of the entire Oracle database has been completed. Note: The Oracle objects and databases that we export are still the table spaces and database user names of the original Oracle database. If you want to specify a different tablespace and database user in the new database server, you need to replace the table space and user name settings in the generated SQL script, and specify a reasonable tablespace and database user in the second step.

4. Frequently Asked Questions

(1) That's the note above.

The problem I encountered: The IGs tablespace is present in the database of the new environment, and the database file igsoracle.sql table space I exported is also igs by default, resulting in a successful import of the database object into the new tablespace IGSC, but imported into the existing IGs tablespace. View Igsoracle.sql:

Discover the exported Oracle objects and databases by default or the original Oracle database tablespace and database user name, we want to change the tablespace and user names to IGSC and re-import.

(2) ORACLE initialization or shutdown in progress

Want to delete the database and DBF files that you do not want, stop the service after deleting, restart the service start DB instance times wrong.

Solve:

Start-to-Enter, Sqlplus/nolog

Sql> Connect System/hope;

Error:ora-01033:oracle initialization or shutdown in progress "that's the mistake."sql> Connect Sys/hope as Sysdbais connected.sql> shutdown NormalORA-01109: The database is not open and the database has been unloaded. The ORACLE routine has been closed.sql> Start Mountsp2-0310: Can't open File "Mount.sql"sql> Startup MountThe ORACLE routine has been started. Total System Global area 612368384 bytesfixed size 1332348 bytesvariable size 183151492 byte Sdatabase buffers 423624704 Bytesredo buffers 4259840 bytes database loaded.sql> ALTER DATABASE open;ALTER DATABASE open* 1th line error: ORA-01157: Unable to identify/lock data file 8-see DBWR trace file ORA-01110: Data file 8: ' C:\TYKM. DBF 'sql> ALTER DATABASE datafile 8 offline drop; "8 is 8 in data file"The database has changed.sql> ALTER DATABASE open; "Loop this statement until the error is no longer prompted"The database has changed.sql> shutdown NormalThe database is closed. The database has been uninstalled. The ORACLE routine has been closed.sql> StartupThe ORACLE routine has been started. Total System Global area 612368384 bytesfixed size 1332348 bytesvariable size 187345796 byte Sdatabase buffers 419430400 Bytesredo buffers 4259840 bytes database loaded. The database is already open. (3) ORA-1658 cannot create initial for a segment of a table space: tablespace size

WORKAROUND: Because the table data is relatively large, I will change the size of step two to 10240M.

Oracle database Backup, recovery, and FAQs