基於時間執行資料庫不完全恢復

在基於時間點恢復：

---準備環境：RMAN全庫備份：

---檢視資料庫的歸檔模式：

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     11

Next log sequence to archive   13

Current log sequence           13

SQL>

 

---使用rman並檢視引數：

[oracle@enmo ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Nov 7 11:30:28 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=338469376)

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name PROD are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/app/backup/db_%U.rmn';

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_PROD.f'; # default

RMAN>

---備份全庫：

RMAN> backup as compressed backupset full database;

Starting backup at 07-NOV-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=37 device type=DISK

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/PROD/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/PROD/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/PROD/example01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/PROD/undotbs01.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/PROD/ts_xxf_01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/PROD/users01.dbf

channel ORA_DISK_1: starting piece 1 at 07-NOV-16

channel ORA_DISK_1: finished piece 1 at 07-NOV-16

piece handle=/u01/app/backup/db_09rkah18_1_1.rmn tag=TAG20161107T113152 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:17

Finished backup at 07-NOV-16

Starting Control File and SPFILE Autobackup at 07-NOV-16

piece handle=/u01/app/oracle/fast_recovery_area/PROD/autobackup/2016_11_07/o1_mf_s_927286449_d1zxkkxq_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 07-NOV-16

#備份完成。

---準備環境：測試表及資料：

suxing@PROD>create table t1 as select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as dd from dual;

Table created.

suxing@PROD>

--檢視錶t1的資料：

suxing@PROD>select * from t1;

DD

-------------------

2016-11-07 11:37:02

suxing@PROD>create table t2 as select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as dd from dual;

Table created.

suxing@PROD>select * from t2;

DD

-------------------

2016-11-07 11:40:42

--刪除表t1：

suxing@PROD>drop table t1 purge;

Table dropped.

--重啟資料庫到mount狀態並透過rman恢復資料庫：

sys@PROD>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

---刪除所有資料檔案：

[oracle@enmo ~]$ cd /u01/app/oracle/oradata/PROD/

[oracle@enmo PROD]$ ls

control01.ctl  myspace01.dbf  redo01.log  redo03.log    system01.dbf  ts_xxf_01.dbf  users01.dbf

example01.dbf  myspace1.dbf   redo02.log  sysaux01.dbf  temp01.dbf    undotbs01.dbf

[oracle@enmo PROD]$

[oracle@enmo PROD]$ rm *.dbf

[oracle@enmo PROD]$ la

-bash: la: command not found

[oracle@enmo PROD]$ ls

control01.ctl  redo01.log  redo02.log  redo03.log

[oracle@enmo PROD]$

---透過rman做不完全恢復資料庫：

[oracle@enmo ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Nov 7 11:48:54 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN>

RMAN> startup mount

Oracle instance started

database mounted

Total System Global Area     835104768 bytes

Fixed Size                     2257840 bytes

Variable Size                507513936 bytes

Database Buffers             322961408 bytes

Redo Buffers                   2371584 bytes

#RMAN> run

{

sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';

set until time = '2016-11-07 11:37:02';

restore database;

recover database;

alter database open resetlogs;

}#草稿

RMAN> run

2> {

3> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';

4> set until time = '2016-11-07 11:37:02';

5> restore database;

6> recover database;

7> alter database open resetlogs;

8> }

using target database control file instead of recovery catalog

sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"

executing command: SET until clause
 

Starting restore at 07-NOV-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PROD/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD/example01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/PROD/ts_xxf_01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/backup/db_09rkah18_1_1.rmn

channel ORA_DISK_1: piece handle=/u01/app/backup/db_09rkah18_1_1.rmn tag=TAG20161107T113152

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:04:32

Finished restore at 07-NOV-16

Starting recover at 07-NOV-16

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:02

Finished recover at 07-NOV-16

database opened

RMAN>

---檢視錶：

suxing@PROD>select tname from tab where tname like 'T_';

TNAME

------------------------------

T1

suxing@PROD>

---檢視系統的資料檔案：

[oracle@enmo ~]$ cd -

/u01/app/oracle/oradata/PROD

[oracle@enmo PROD]$

[oracle@enmo PROD]$

[oracle@enmo PROD]$ ls

control01.ctl  redo01.log  redo03.log    system01.dbf  ts_xxf_01.dbf  users01.dbf

example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    undotbs01.dbf

#已經恢復成功。