oracle基於時間點的不完全恢復
下面我們做一個實驗,演示如何對oracle進行基於時間點的不完全恢復(在實驗之前請確保資料庫具有有效備份):
獲取此時的時間並記錄下來:
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2014-08-23 00:47:49
把hh使用者下的h2刪除:
SQL> drop table h2;
Table dropped.
使用rman將資料庫恢復到刪除h2之前:
關閉資料庫,啟動rman,重啟資料庫到mount:
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@localhost ~]$ rman target / nocatalog
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Aug 23 00:55:12 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 553651792 bytes
Database Buffers 289406976 bytes
Redo Buffers 5132288 bytes
使用rman將資料庫恢復到2014-08-23 00:47:49
獲取此時的時間並記錄下來:
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2014-08-23 00:47:49
把hh使用者下的h2刪除:
SQL> drop table h2;
Table dropped.
使用rman將資料庫恢復到刪除h2之前:
關閉資料庫,啟動rman,重啟資料庫到mount:
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@localhost ~]$ rman target / nocatalog
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Aug 23 00:55:12 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 553651792 bytes
Database Buffers 289406976 bytes
Redo Buffers 5132288 bytes
使用rman將資料庫恢復到2014-08-23 00:47:49
RMAN> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"
RMAN> restore database until time '2014-08-23 00:47:49';
Starting restore at 23-AUG-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
skipping datafile 6; already restored to file /u01/app/datafile/test01.dbf
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 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/backup/fulldb_0qpgkgjo_1_1
channel ORA_DISK_1: piece handle=/u01/app/backup/fulldb_0qpgkgjo_1_1 tag=FULLDB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:06
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 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/orcl/hh01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/backup/fulldb_0ppgkgjo_1_1
channel ORA_DISK_1: piece handle=/u01/app/backup/fulldb_0ppgkgjo_1_1 tag=FULLDB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
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/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/backup/fulldb_0opgkgjo_1_1
channel ORA_DISK_1: piece handle=/u01/app/backup/fulldb_0opgkgjo_1_1 tag=FULLDB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 23-AUG-14
RMAN> recover database until time '2014-08-23 00:47:49';
Starting recover at 23-AUG-14
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/archivelog/dest1/1_1_856304357.dbf
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/archivelog/dest1/1_2_856304357.dbf
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/archivelog/dest1/1_3_856304357.dbf
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/archivelog/dest1/1_1_856310126.dbf
archived log file name=/u01/app/archivelog/dest1/1_1_856304357.dbf thread=1 sequence=1
archived log file name=/u01/app/archivelog/dest1/1_2_856304357.dbf thread=1 sequence=2
archived log file name=/u01/app/archivelog/dest1/1_3_856304357.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:16
Finished recover at 23-AUG-14
使用resetlogs開啟資料庫:
SQL> alter database open resetlogs;
Database altered.
SQL> select * from hh.h2;
ID
----------
1
2
sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"
Starting restore at 23-AUG-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
skipping datafile 6; already restored to file /u01/app/datafile/test01.dbf
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 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/backup/fulldb_0qpgkgjo_1_1
channel ORA_DISK_1: piece handle=/u01/app/backup/fulldb_0qpgkgjo_1_1 tag=FULLDB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:06
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 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/orcl/hh01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/backup/fulldb_0ppgkgjo_1_1
channel ORA_DISK_1: piece handle=/u01/app/backup/fulldb_0ppgkgjo_1_1 tag=FULLDB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
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/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/backup/fulldb_0opgkgjo_1_1
channel ORA_DISK_1: piece handle=/u01/app/backup/fulldb_0opgkgjo_1_1 tag=FULLDB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 23-AUG-14
RMAN> recover database until time '2014-08-23 00:47:49';
Starting recover at 23-AUG-14
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/archivelog/dest1/1_1_856304357.dbf
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/archivelog/dest1/1_2_856304357.dbf
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/archivelog/dest1/1_3_856304357.dbf
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/archivelog/dest1/1_1_856310126.dbf
archived log file name=/u01/app/archivelog/dest1/1_1_856304357.dbf thread=1 sequence=1
archived log file name=/u01/app/archivelog/dest1/1_2_856304357.dbf thread=1 sequence=2
archived log file name=/u01/app/archivelog/dest1/1_3_856304357.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:16
Finished recover at 23-AUG-14
SQL> alter database open resetlogs;
Database altered.
SQL> select * from hh.h2;
ID
----------
1
2
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29067253/viewspace-2138588/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORACLE資料庫基於時間點的不完全恢復Oracle資料庫
- Oracle基於時間點的恢復Oracle
- Oracle 12c PDB基於時間點的不完全恢復記錄Oracle
- RMAN基於時間點恢復Oracle資料庫Oracle資料庫
- oracle實驗記錄 (恢復-表空間基於時間點恢復(rman))Oracle
- oracle基於scn的不完全恢復Oracle
- oracle 閃回基於時間的恢復Oracle
- oracle實驗記錄 (恢復-表空間基於時間點恢復(手動))Oracle
- 基於時間執行資料庫不完全恢復資料庫
- mongodb 基於oplog的時間點恢復MongoDB
- 使用RMAN的不完全恢復-基於時間/SCN/日誌序列
- Oracle 11g 主動選擇的不完全恢復,基於時間,DDL操作Oracle
- 恢復到特定點(時間點、scn、日誌序列號),rman不完全恢復
- oracle 基於使用者管理的不完全恢復Oracle
- 【RMAN】表空間基於時間點的RMAN恢復-TSPITR
- 【TSPITR】RMAN表空間基於時間點的自動恢復
- Oracle 基於 RMAN 的不完全恢復(incomplete recovery by RMAN)Oracle
- Backup And Recovery User's Guide-執行基於時間的或基於改變的不完全恢復GUIIDE
- 備份與恢復:Polardb資料庫資料基於時間點恢復資料庫
- Oracle 不完全恢復Oracle
- RAC 資料庫恢復到單例項下並且基於時間點恢復資料庫單例
- 基於percona xtrabackup之innobackupex實現基於時間點資料庫恢復資料庫
- 2.MongoDB 4.2副本集環境基於時間點的恢復MongoDB
- 用mysqlbinlog做基於時間點的資料恢復一例MySql資料恢復
- Oracle表空間時間點恢復技術TSPITROracle
- mongodb異機做時間點恢復(基於時間範圍查詢匯出oplog)MongoDB
- 備份與恢復:用user模式基於日誌序列的不完全恢復模式
- 備份與恢復:用rman方式基於日誌序列的不完全恢復
- oracle實驗記錄 (恢復-不完全恢復)Oracle
- 小記基於控制檔案的scn不完全恢復
- rman恢復到指定時間點
- oracle小知識點10--表空間時間點恢復(TSPITR)Oracle
- 【RMAN】使用RMAN備份將資料庫不完全恢復到指定時間點資料庫
- [RMAN]使用RMAN備份將資料庫不完全恢復到指定時間點資料庫
- Oracle 11g 主動選擇的不完全恢復,基於SCN的,DML操作Oracle
- oracle基於SCN增量恢復Oracle
- MySQL binlog基於時間點恢復資料失敗是什麼鬼?MySql
- 1.MongoDB 2.7主從複製(master –> slave)環境基於時間點的恢復MongoDBAST