oracle實驗記錄 （恢復-不完全恢復）

恢復過程也很簡單

 d:\backupscript.sql

host copy E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF   d:\backup\SYSTEM01.DBF
host copy E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF   d:\backup\UNDOTBS01.DBF
host copy E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF   d:\backup\SYSAUX01.DBF
host copy E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF   d:\backup\USERS01.DBF
host copy E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBF  d:\backup\EXAMPLE01.DBF
host copy D:\TEST.DBF  d:\backup\test.dbf

SQL> alter database begin backup;(會產生N多 redo record  記錄整個塊,防止塊分裂)

SQL> @ d:\backupscript.sql
已複製         1 個檔案。

已複製         1 個檔案。

已複製         1 個檔案。

已複製         1 個檔案。

已複製         1 個檔案。

已複製         1 個檔案。
SQL> alter database end backup;
SQL>
SQL> select count(*) from t2;

  COUNT(*)
----------
    200000

1 row selected.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    7653502

1 row selected.

SQL> delete from t2;

200000 rows deleted.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

~現在執行恢復 恢復到INSERT 前
restore.txt
host copy   d:\backup\SYSTEM01.DBF E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
host copy   d:\backup\UNDOTBS01.DBF  E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
host copy   d:\backup\SYSAUX01.DBF E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
host copy   d:\backup\USERS01.DBF E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF
host copy   d:\backup\EXAMPLE01.DBF E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBF
host copy   d:\backup\test.dbf D:\TEST.DBF

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.

SQL> @d:\restore.txt
已複製         1 個檔案。

已複製         1 個檔案。

已複製         1 個檔案。

已複製         1 個檔案。

已複製         1 個檔案。

已複製         1 個檔案。

SQL>
SQL> recover database until change 7653502;還可以until  change 到指定SCN
until time 到指定時間(NLS_DATE_FORMAT)  UNTIL cancle 日誌到哪用到哪

ORA-00279: change 7653004 generated at 08/20/2009 17:17:45 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\XHTEST\ARCHIVELOG\2009_08_20\O1_MF_

1_91_%U_.ARC
ORA-00280: change 7653004 for thread 1 is in sequence #91

Specify log: {=suggested | filename | AUTO | CANCEL}
auto~
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;~~~~~~~~~~~`

Database altered.

SQL> select count(*) from t2;

  COUNT(*)
----------
    200000

1 row selected.

~~~~~~~~~~~~~~~~~~~~~RMAN進行不完全恢復
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------

E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF
E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
D:\TEST.DBF

6 rows selected.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    7687698

SQL> select * from t1;

         A
----------
         1

SQL> truncate table t1;

Table truncated.

SQL> show user;
USER is "XH"
SQL> select * from t1;

no rows selected
SQL> conn / as sysdba
Connected.
SQL> startup force mount;
Database mounted.

RMAN> run{set until scn=7687698;
2> restore database;
3> recover database;
4> }

starting media recovery
media recovery complete

Finished recover at 21-8月 -09

SQL> alter database open resetlogs;

Database altered.

SQL> select sequence# from v$log;(resetlog重置 序號)

 SEQUENCE#
----------
         0
         0
         0
         1
SQL> conn xh/a831115
Connected.
SQL> select * from t1;

         A
----------
         1

可以 until sequence,  until time 沒有until cancel
resetlogs後 之前備份無法使用了(當然還是有辦法使用的,會專門實驗),需要重新備份
del 後重新備份