Oracle Database Manual Recovery Memo Log

Recently because of the upgrade of Mac OS X 10.10 Yosemite, suddenly a few days ago, a night, the boot found stuck in the start-up interface, it seems that beta is not reliable, and then think of themselves this is not a few days ago back up, nothing, timemachine is what artifact, apart direct recovery. But after the recovery, I regret, one is to forget last night also work overtime, the success of overtime to retain the results are very little, the key I found timemachine since not backup virtual machine files (virtual machine file 30G, estimated directly to skip it), no wonder every time feel backup so fast.

But regret is useless, re-build the development environment. Operating system win7-> Development tools vs2012-> database Oracle. And then back to that boring database backup time of recovery. Since I didn't know much about Oracle's database, I didn't have the ability to execute scripts with a full-handwriting database without the help of the DBA, but in order not to let each of them go back and look for a tutorial, here's a deep record of the next memo.

Here, the database mainly restores the exported dmp files, specifically related to some of the relevant directives are as follows

Import of 
  data 1 Imports data from D:\daochu.dmp into the test database. Imp system/[email protected] file=d:\daochu.dmp full=y imp system/[email protected] file=d:\daochu.dmp full =y ignore=y above may be a problem, because some tables already exist, and then it is an error, the table will not be imported. Add Ignore=y to the back. 2 Import the table table1 in d:\daochu.dmp imp system/[email protected] file=d:\daochu.dmp tables= (table1) 3, import the table space im of User B from User A P system/[email protected] file=d:\daochu.dmp fromuser=jandardb TOUSER=JANDARDB Data export: 1 fully export database test, user name System Password Manager export to D:\daochu.dmp exp system/[email protected] FILE=D:\DAOCHU.DMP full=y 2 Exports the system user in the database with the SYS user's table exp System/[email protected] file=d:\daochu.dmp owner= (system,sys) 3 Export the table inner_notify, Notify_staff_relat in the database exp Aichannel/[email protected] file= d:\data\newsmgnt.dmp tables= (Inner_notify,notify_staff_relat) 4 Export the field in table Table1 in the database filed1 the data beginning with "00" Exp system/[email protected] file=d:\daochu.dmp tables= (table1) query=\ " where filed1 like ' 00% ' \ " 

The process is generally as follows:

1. Create a table space.
2. Set up the corresponding database users, assign permissions, table space and so on.
3. Import data using import directives.

Then follow the steps of the execution procedure (all of the following actions are performed on Sqlplus)

CREATE TABLE Space

tablespace meaning (table space) {Copy from Baidu Encyclopedia}:

The Oracle database is partitioned into logical areas called the table spaces--forming the logical structure of the Oracle database. An Oracle database can have one or more table spaces, while one table space corresponds to one or more physical database files. The tablespace is the smallest unit of Oracle database recovery, accommodating many database entities such as tables, views, indexes, clusters, fallback segments, and temporary segments.

The narrow understanding is that there can be more than one table space in a database, and each table in the database belongs to a table space, which is a logical concept that Oracle uses to manage the database.

/*第1步：创建临时表空间  */create temporary tablespace 临时表空间名tempfile ‘存储地址\临时表空间存储文件名.DBF‘ size 50m  autoextend on  next 50m maxsize 20480m  extent management local;   /*第2步：创建数据表空间  */create tablespace 表空间名logging datafile ‘存储地址\表空间存储文件名.DBF‘  size 50m  autoextend on  next 50m maxsize 20480m  extent management local;  

Temporary table space

Because Oracle often needs some temporary disk space when working, these spaces are used primarily as algorithms for querying with sorting (Group by,order by, etc.), released immediately after use, and are no longer used for information recorded in the disk area, and are therefore called temporary tablespace.

Build Database Users

/*建立用户*/create user 用户名 identified by 密码  default tablespace 表空间  temporary tablespace 临时表空间;  /*分配权限,connect,resource,dba是角色名*/grant connect,resource,dba to 用户名;  

A simple description of the database's scheduled database role:

Role Name	Simple Description
Connect	Have the most basic database connection permissions
Resource	Have general administrative permissions to create tables and triggers
Dba	With the exception of shutting down and restarting database permissions, with most system administration privileges, but not with privileges like sysdba,sysoper

Import DMP Backup Data

imp 用户名/密码@连接名称  file=路径/文件名.dmp full=y