Oracle Backup, restore

---- First step: Set up empty table export

----The empty table cannot be exported because Oracle 11G is exported with export. The workaround is as follows: (There is a new feature in 11G that does not allocate segment to save space when the table has no data)

Select ' ALTER TABLE ' | | table_name| | ' Allocate extent (size 64k); ' From tabs t
Where NOT EXISTS (select Segment_name from user_segments s where s.segment_name=t.table_name);

--Execute the above statement in Plsql and copy the query result set, execute the copied statement data

2, the first installation of the Oracle database, with SYSDBA login Plsql execute the following statement, then the table space and user-created:

Set the parameter for creating segment to False, and an empty table can import and export at any later time.

alter system set DEFERRED_SEGMENT_CREATION=FALSE;

---Note: After you modify this setting, the tablespace that creates the library can implement any import and export of empty tables, but before you modify the settings database, you must perform the first step of exporting the empty table settings statement.

--- Step two: Back up the database

---Start-run, enter CMD, enter BACKUP statement

Exp userid=abc/[email protected] file=d:\01abc\ database backup \abc20160520.dmp log=d:\01abc\ database backup \abc20160520.log

---Description: Exp userid= username/password @net service name file= (Backup database file path) \ backup file name. DMP log= (log path) \ log path.

--- Step three: Restore the database

---Start-run, enter CMD, enter a RESTORE statement

Imp userid=abc/[email protected] file=d:\01abc\ database backup \abc20160520.dmp full=y log=d:\01abc\ database backup \ Abc20160907.log

---Description: Imp userid= username/password @net service name file= (Backup database file path) \ backup file name. DMP full=y log= (log path) \ log path. Log, note and backup statements differ in that the ① command is the Imp ② added Full=y.

-- other

1, if it is to back up a table or a few tables, you can use the "tool-Export table" in Plsql, select one or more tables to complete, but this way can not back up the view, etc., the whole library backup needs to be backed up by the statement.

2. The password of the Oracle user must be changed within 180 days, otherwise the connection failure will be prompted when starting the database. Prevent Oracle user passwords from expiring:

ALTER profile DEFAULT LIMIT password_life_time UNLIMITED;

Oracle Backup, restore