oracle刪除使用者後的恢復測試
用到2個機子
主節點 192.168.119.145 hostname=psd
輔助節點192.168.119.146 hostname=test
要求有rman備份
方法一:如果庫上只有這一個業務使用者,就可以直接用rman 做全庫的時間點恢復。
方法二:如果有多個使用者,大致思路,在psd節點上備份資料庫,然後刪除使用者,之後將備份恢復到test節點上,然後將test節點的使用者資料邏輯匯出,最後匯入到psd節點。
方法二的測試
確認資料庫是歸檔模式,首先在scott使用者下建立一個t1表,並插入資料
接下來開始備份資料庫,備份完在向t1表插入資料,然後建立t2表,之後刪掉scott使用者
現在scott使用者已經被刪掉了,要為恢復做準備,我們檢視pfile檔案,把相關的目錄在輔助節點(192.168.119.146)都建立好,下面標紅的目錄
把剛剛的rman備份、pfile檔案以及密碼檔案複製到192.168.119.146上,然後我們就要確定drop scott的具體時間,用logminer來搞定
從這裡我們可以判斷,drop使用者的時間是 2015-03-17 11:46:13,我的測試機器上沒有多少歸檔,但是在生產環境歸檔會很多,想確定drop的時間就得多看幾個歸檔日誌了。
確定時間,我們就開始在輔助節點做恢復操作
這是報了個錯無,說17號歸檔找不到,我們把主庫的17號歸檔考過來,我考到了/home/oracle/backup 下面了,然後繼續恢復
這時候資料庫已經開啟,我們看看scott使用者下的資料
發現scott回收站裡有很多垃圾表,可以無視,我們發現t1,t2的資料都正常,這時候只要把scott的資料匯出來再匯入到主庫就好了
使用者誤刪恢復完成。
主節點 192.168.119.145 hostname=psd
輔助節點192.168.119.146 hostname=test
要求有rman備份
方法一:如果庫上只有這一個業務使用者,就可以直接用rman 做全庫的時間點恢復。
方法二:如果有多個使用者,大致思路,在psd節點上備份資料庫,然後刪除使用者,之後將備份恢復到test節點上,然後將test節點的使用者資料邏輯匯出,最後匯入到psd節點。
方法二的測試
確認資料庫是歸檔模式,首先在scott使用者下建立一個t1表,並插入資料
-
[oracle@psd ~]$ sqlplus / as sysdba
-
-
SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 17 10:39:53 2015
-
-
Copyright (c) 1982, 2011, Oracle. All rights reserved.
-
-
-
Connected to:
-
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
-
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
-
SQL> archive log list
-
Database log mode Archive Mode
-
Automatic archival Enabled
-
Archive destination USE_DB_RECOVERY_FILE_DEST
-
Oldest online log sequence 14
-
Next log sequence to archive 16
-
Current log sequence 16
-
SQL>
-
SQL> show parameter db_re
-
-
NAME TYPE VALUE
-
------------------------------------ ----------- ------------------------------
-
db_recovery_file_dest string /oracle/fast_recovery_area
-
db_recovery_file_dest_size big integer 4122M
-
db_recycle_cache_size big integer 0
-
SQL>
-
SQL> conn scott/scott
-
Connected.
-
SQL> create table t1 (id number);
-
-
Table created.
-
-
SQL> begin
-
2 for i in 1..5 loop
-
3 insert into t1 values (i);
-
4 end loop;
-
5 end;
-
6 /
-
-
PL/SQL procedure successfully completed.
-
-
SQL> commit;
-
-
Commit complete.
-
-
SQL> select * from t1;
-
-
ID
-
----------
-
1
-
2
-
3
-
4
-
5
-
SQL>
-
SQL> exit
-
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
-
With the Partitioning, OLAP, Data Mining and Real Application Testing options
- [oracle@psd backup]$
接下來開始備份資料庫,備份完在向t1表插入資料,然後建立t2表,之後刪掉scott使用者
-
[oracle@psd backup]$ rman target /
-
-
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Mar 17 11:38:55 2015
-
-
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
-
-
connected to target database: PSD (DBID=2007794869)
-
-
RMAN> backup database format '/home/oracle/backup/psd_%U.bak' plus archivelog format '/home/oracle/backup/arch_%U.bak';
-
-
-
Starting backup at 17-MAR-15
-
current log archived
-
using target database control file instead of recovery catalog
-
allocated channel: ORA_DISK_1
-
channel ORA_DISK_1: SID=41 device type=DISK
-
channel ORA_DISK_1: starting archived log backup set
-
channel ORA_DISK_1: specifying archived log(s) in backup set
-
input archived log thread=1 sequence=12 RECID=11 STAMP=873219765
-
input archived log thread=1 sequence=13 RECID=12 STAMP=874489166
-
input archived log thread=1 sequence=14 RECID=13 STAMP=874489173
-
input archived log thread=1 sequence=15 RECID=14 STAMP=874533680
-
input archived log thread=1 sequence=16 RECID=15 STAMP=874582892
-
channel ORA_DISK_1: starting piece 1 at 17-MAR-15
-
channel ORA_DISK_1: finished piece 1 at 17-MAR-15
-
piece handle=/home/oracle/backup/arch_02q224re_1_1.bak tag=TAG20150317T114134 comment=NONE
-
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
-
Finished backup at 17-MAR-15
-
-
Starting backup at 17-MAR-15
-
using channel ORA_DISK_1
-
channel ORA_DISK_1: starting full datafile backup set
-
channel ORA_DISK_1: specifying datafile(s) in backup set
-
input datafile file number=00001 name=/oracle/oradata/psd/system01.dbf
-
input datafile file number=00002 name=/oracle/oradata/psd/sysaux01.dbf
-
input datafile file number=00003 name=/oracle/oradata/psd/undotbs01.dbf
-
input datafile file number=00004 name=/oracle/oradata/psd/users01.dbf
-
channel ORA_DISK_1: starting piece 1 at 17-MAR-15
-
channel ORA_DISK_1: finished piece 1 at 17-MAR-15
-
piece handle=/home/oracle/backup/psd_03q224s8_1_1.bak tag=TAG20150317T114200 comment=NONE
-
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:46
-
channel ORA_DISK_1: starting full datafile backup set
-
channel ORA_DISK_1: specifying datafile(s) in backup set
-
including current control file in backup set
-
including current SPFILE in backup set
-
channel ORA_DISK_1: starting piece 1 at 17-MAR-15
-
channel ORA_DISK_1: finished piece 1 at 17-MAR-15
-
piece handle=/home/oracle/backup/psd_04q224vi_1_1.bak tag=TAG20150317T114200 comment=NONE
-
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
-
Finished backup at 17-MAR-15
-
-
Starting backup at 17-MAR-15
-
current log archived
-
using channel ORA_DISK_1
-
channel ORA_DISK_1: starting archived log backup set
-
channel ORA_DISK_1: specifying archived log(s) in backup set
-
input archived log thread=1 sequence=17 RECID=16 STAMP=874583030
-
channel ORA_DISK_1: starting piece 1 at 17-MAR-15
-
channel ORA_DISK_1: finished piece 1 at 17-MAR-15
-
piece handle=/home/oracle/backup/arch_05q224vm_1_1.bak tag=TAG20150317T114350 comment=NONE
-
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
-
Finished backup at 17-MAR-15
-
-
RMAN>
-
RMAN> exit
-
-
-
Recovery Manager complete.
-
[oracle@psd backup]$ sqlplus /nolog
-
-
SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 17 11:44:27 2015
-
-
Copyright (c) 1982, 2011, Oracle. All rights reserved.
-
-
SQL> conn scott/scott
-
Connected.
-
SQL> begin
-
2 for i in 6..10 loop
-
3 insert into t1 values (i);
-
4 end loop;
-
5 end;
-
6 /
-
-
PL/SQL procedure successfully completed.
-
-
SQL> commit;
-
-
Commit complete.
-
-
SQL> select * from t1;
-
-
ID
-
----------
-
1
-
2
-
3
-
4
-
5
-
6
-
7
-
8
-
9
-
10
-
-
10 rows selected.
-
-
SQL> create table t2 as select * from t1;
-
-
Table created.
-
-
SQL> conn /as sysdba
-
Connected.
-
SQL> drop user scott cascade;
-
- User dropped.
現在scott使用者已經被刪掉了,要為恢復做準備,我們檢視pfile檔案,把相關的目錄在輔助節點(192.168.119.146)都建立好,下面標紅的目錄
-
SQL> create pfile from spfile;
-
-
File created.
-
-
SQL> exit
-
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
-
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
[oracle@psd backup]$ cd $ORACLE_HOME/dbs
-
[oracle@psd dbs]$ cat initpsd.ora
-
psd.__db_cache_size=96468992
-
psd.__java_pool_size=4194304
-
psd.__large_pool_size=4194304
-
psd.__oracle_base='/oracle'#ORACLE_BASE set from environment
-
psd.__pga_aggregate_target=142606336
-
psd.__sga_target=272629760
-
psd.__shared_io_pool_size=0
-
psd.__shared_pool_size=159383552
-
psd.__streams_pool_size=0
-
*.audit_file_dest='/oracle/admin/psd/adump'
-
*.audit_trail='db'
-
*.compatible='11.2.0.0.0'
- *.control_files='/oracle/oradata/psd/control01.ctl','/oracle/fast_recovery_area/psd/control02.ctl'
-
*.db_block_size=8192
-
*.db_domain=''
-
*.db_name='psd'
-
*.db_recovery_file_dest='/oracle/fast_recovery_area'
-
*.db_recovery_file_dest_size=4322230272
-
*.diagnostic_dest='/oracle'
-
*.dispatchers='(PROTOCOL=TCP) (SERVICE=psdXDB)'
-
*.memory_target=414187520
-
*.open_cursors=300
-
*.processes=150
-
*.remote_login_passwordfile='EXCLUSIVE'
- *.undo_tablespace='UNDOTBS1'
把剛剛的rman備份、pfile檔案以及密碼檔案複製到192.168.119.146上,然後我們就要確定drop scott的具體時間,用logminer來搞定
-
[oracle@psd backup]$ cd /oracle/fast_recovery_area/PSD/archivelog/
-
[oracle@psd archivelog]$ cd 2015_03_17/
-
[oracle@psd 2015_03_17]$ ll
-
total 10248
-
-rw-r-----. 1 oracle oinstall 7795200 Mar 17 11:41 o1_mf_1_16_bjh8mbov_.arc
-
-rw-r-----. 1 oracle oinstall 2048 Mar 17 11:43 o1_mf_1_17_bjh8qp80_.arc
-
-rw-r-----. 1 oracle oinstall 2674688 Mar 17 12:03 o1_mf_1_18_bjh9wyqj_.arc
-
-rw-r-----. 1 oracle oinstall 1024 Mar 17 12:03 o1_mf_1_19_bjh9x0hy_.arc
-
-rw-r-----. 1 oracle oinstall 9728 Mar 17 12:03 o1_mf_1_20_bjh9x3xb_.arc
-
-
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/fast_recovery_area/PSD/archivelog/2015_03_17/o1_mf_1_16_bjh8mbov_.arc',OPTIONS => DBMS_LOGMNR.NEW);
-
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/fast_recovery_area/PSD/archivelog/2015_03_17/o1_mf_1_17_bjh8qp80_.arc',OPTIONS => DBMS_LOGMNR.ADDFILE);
-
-
PL/SQL procedure successfully completed.
-
-
SQL>
-
PL/SQL procedure successfully completed.
-
-
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/fast_recovery_area/PSD/archivelog/2015_03_17/o1_mf_1_18_bjh9wyqj_.arc',OPTIONS => DBMS_LOGMNR.ADDFILE);
-
-
PL/SQL procedure successfully completed.
-
-
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
-
-
PL/SQL procedure successfully completed.
-
-
SQL> col sql_redo format a50
-
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
-
SQL> select sql_redo,scn,start_scn,TIMESTAMP,START_TIMESTAMP from V$LOGMNR_CONTENTS where lower(sql_redo) like \'%drop%\' and lower(sql_redo) like \'%scott%\';
-
-
SQL_REDO SCN START_SCN TIMESTAMP START_TIMESTAMP
-
------------------------------------------------------------ ---------- ---------- ------------------- -------------------
-
drop table scott.emp1 AS "BIN$EWlpmtpQdoXgU5F3qMAiMw==$0" ; 1272611 2015-03-16 22:10:29
drop table "SCOTT"."BIN$EWNzFGZja8TgU5F3qMDpoQ==$0" purge; 1278724 2015-03-17 11:46:13
drop table "SCOTT"."BIN$EWOQLk80bF3gU5F3qMBi4g==$0" purge; 1278746 2015-03-17 11:46:14
drop table "SCOTT"."BIN$EWNb3bwua9TgU5F3qMA7BA==$0" purge; 1278770 2015-03-17 11:46:14
drop table "SCOTT"."BIN$EWNb3bwva9TgU5F3qMA7BA==$0" purge; 1278791 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWNzFGZka8TgU5F3qMDpoQ==$0" purge; 1278813 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWOU7d6fbGPgU5F3qMA5vw==$0" purge; 1278834 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWOQLk81bF3gU5F3qMBi4g==$0" purge; 1278856 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWOniXMLbHvgU5F3qMAS1g==$0" purge; 1278878 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWOniXMMbHvgU5F3qMAS1g==$0" purge; 1278900 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWOniXMNbHvgU5F3qMAS1g==$0" purge; 1278921 2015-03-17 11:46:15
SQL_REDO SCN START_SCN TIMESTAMP START_TIMESTAMP
------------------------------------------------------------ ---------- ---------- ------------------- -------------------
drop table "SCOTT"."BIN$EWOniXMObHvgU5F3qMAS1g==$0" purge; 1278943 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWPg+H8lbNLgU5F3qMCj4g==$0" purge; 1278965 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWPg+H8mbNLgU5F3qMCj4g==$0" purge; 1278986 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWPg+H8nbNLgU5F3qMCj4g==$0" purge; 1279008 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTPDfF1bmPgU5F3qMBohA==$0" purge; 1279030 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTPDfF2bmPgU5F3qMBohA==$0" purge; 1279052 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTPDfF3bmPgU5F3qMBohA==$0" purge; 1279074 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTSV1AibnLgU5F3qMCKBg==$0" purge; 1279096 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTaumLtbn7gU5F3qMAgNQ==$0" purge; 1279118 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTdffDnboXgU5F3qMCDZg==$0" purge; 1279140 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTdffDoboXgU5F3qMCDZg==$0" purge; 1279162 2015-03-17 11:46:16
SQL_REDO SCN START_SCN TIMESTAMP START_TIMESTAMP
------------------------------------------------------------ ---------- ---------- ------------------- -------------------
drop table "SCOTT"."BIN$EWTdffDpboXgU5F3qMCDZg==$0" purge; 1279184 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWVlDuUgb2jgU5F3qMAyEQ==$0" purge; 1279206 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWi11zOecYPgU5F3qMAYuA==$0" purge; 1279228 2015-03-17 11:46:17
drop table "SCOTT"."BIN$EWjjuPdrc7fgU5F3qMD+ag==$0" purge; 1279250 2015-03-17 11:46:17
drop table "SCOTT"."BIN$EWjqnThDc77gU5F3qMC90A==$0" purge; 1279272 2015-03-17 11:46:17
drop table "SCOTT"."BIN$EWjqnThEc77gU5F3qMC90A==$0" purge; 1279294 2015-03-17 11:46:17
drop table "SCOTT"."BIN$EWlYdSbqdmHgU5F3qMBLJg==$0" purge; 1279314 2015-03-17 11:46:17
drop table "SCOTT"."BIN$EWlpmtpQdoXgU5F3qMAiMw==$0" purge; 1279336 2015-03-17 11:46:17
drop table "SCOTT"."T2" cascade constraints purge force; 1279359 2015-03-17 11:46:22
drop table "SCOTT"."T1" cascade constraints purge force; 1279379 2015-03-17 11:46:22
drop table "SCOTT"."DEPT1" cascade constraints purge force; 1279401 2015-03-17 11:46:22
SQL_REDO SCN START_SCN TIMESTAMP START_TIMESTAMP
------------------------------------------------------------ ---------- ---------- ------------------- -------------------
drop table "SCOTT"."SALGRADE" cascade constraints purge forc 1279424 2015-03-17 11:46:22
e;
drop table "SCOTT"."BONUS" cascade constraints purge force; 1279447 2015-03-17 11:46:22
drop table "SCOTT"."EMP" cascade constraints purge force; 1279459 2015-03-17 11:46:23
drop table "SCOTT"."DEPT" cascade constraints purge force; 1279497 2015-03-17 11:46:23
drop procedure "SCOTT"."P_EMP1"; 1279533 2015-03-17 11:46:23
drop procedure "SCOTT"."P_INSERT"; 1279555 2015-03-17 11:46:24
drop procedure "SCOTT"."P_INSERT_T"; 1279573 2015-03-17 11:46:24
drop user scott cascade; 1279609 2015-03-17 11:46:40
-
-
41 rows selected.
-
- SQL>
從這裡我們可以判斷,drop使用者的時間是 2015-03-17 11:46:13,我的測試機器上沒有多少歸檔,但是在生產環境歸檔會很多,想確定drop的時間就得多看幾個歸檔日誌了。
確定時間,我們就開始在輔助節點做恢復操作
-
[oracle@test backup]$ rman target /
-
-
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Mar 17 12:27:21 2015
-
-
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
-
-
connected to target database (not started)
-
-
RMAN> startup nomount;
-
-
Oracle instance started
-
-
Total System Global Area 413372416 bytes
-
-
Fixed Size 2228904 bytes
-
Variable Size 310381912 bytes
-
Database Buffers 96468992 bytes
-
Redo Buffers 4292608 bytes
-
-
RMAN> restore controlfile from '/home/oracle/backup/psd_04q224vi_1_1.bak';
-
-
Starting restore at 17-MAR-15
-
using target database control file instead of recovery catalog
-
allocated channel: ORA_DISK_1
-
channel ORA_DISK_1: SID=19 device type=DISK
-
-
channel ORA_DISK_1: restoring control file
-
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
-
output file name=/oracle/oradata/psd/control01.ctl
-
output file name=/oracle/fast_recovery_area/psd/control02.ctl
-
Finished restore at 17-MAR-15
-
-
RMAN> alter database mount;
-
-
database mounted
-
released channel: ORA_DISK_1
-
-
RMAN> restore database until time "to_date(\'2015-03-17 11:46:10\',\'yyyy-mm-dd hh24:mi:ss\')";
-
-
Starting restore at 17-MAR-15
-
using channel ORA_DISK_1
-
-
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 /oracle/oradata/psd/system01.dbf
-
channel ORA_DISK_1: restoring datafile 00002 to /oracle/oradata/psd/sysaux01.dbf
-
channel ORA_DISK_1: restoring datafile 00003 to /oracle/oradata/psd/undotbs01.dbf
-
channel ORA_DISK_1: restoring datafile 00004 to /oracle/oradata/psd/users01.dbf
-
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/psd_03q224s8_1_1.bak
-
channel ORA_DISK_1: piece handle=/home/oracle/backup/psd_03q224s8_1_1.bak tag=TAG20150317T114200
-
channel ORA_DISK_1: restored backup piece 1
-
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
-
Finished restore at 17-MAR-15
-
-
RMAN> recover database until time "to_date(\'2015-03-17 11:46:10\',\'yyyy-mm-dd hh24:mi:ss\')";
-
-
Starting recover at 17-MAR-15
-
using channel ORA_DISK_1
-
-
starting media recovery
-
-
unable to find archived log
-
archived log thread=1 sequence=17
-
RMAN-00571: ===========================================================
-
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
-
RMAN-00571: ===========================================================
-
RMAN-03002: failure of recover command at 03/17/2015 12:35:48
- RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 17 and starting SCN of 1278496
-
RMAN> catalog start with '/home/oracle/backup';
-
-
searching for all files that match the pattern /home/oracle/backup
-
-
List of Files Unknown to the Database
-
=====================================
-
File Name: /home/oracle/backup/psd_04q224vi_1_1.bak
-
File Name: /home/oracle/backup/o1_mf_1_17_bjh8qp80_.arc
-
File Name: /home/oracle/backup/o1_mf_1_16_bjh8mbov_.arc
-
File Name: /home/oracle/backup/o1_mf_1_20_bjh9x3xb_.arc
-
File Name: /home/oracle/backup/o1_mf_1_18_bjh9wyqj_.arc
-
File Name: /home/oracle/backup/o1_mf_1_19_bjh9x0hy_.arc
-
File Name: /home/oracle/backup/arch_05q224vm_1_1.bak
-
-
Do you really want to catalog the above files (enter YES or NO)? yes
-
cataloging files...
-
cataloging done
-
-
List of Cataloged Files
-
=======================
-
File Name: /home/oracle/backup/psd_04q224vi_1_1.bak
-
File Name: /home/oracle/backup/o1_mf_1_17_bjh8qp80_.arc
-
File Name: /home/oracle/backup/o1_mf_1_16_bjh8mbov_.arc
-
File Name: /home/oracle/backup/o1_mf_1_20_bjh9x3xb_.arc
-
File Name: /home/oracle/backup/o1_mf_1_18_bjh9wyqj_.arc
-
File Name: /home/oracle/backup/o1_mf_1_19_bjh9x0hy_.arc
-
File Name: /home/oracle/backup/arch_05q224vm_1_1.bak
-
-
RMAN> recover database until time "to_date(\'2015-03-17 11:46:10\',\'yyyy-mm-dd hh24:mi:ss\')";
-
-
Starting recover at 17-MAR-15
-
using channel ORA_DISK_1
-
-
starting media recovery
-
-
archived log for thread 1 with sequence 17 is already on disk as file /home/oracle/backup/o1_mf_1_17_bjh8qp80_.arc
-
archived log for thread 1 with sequence 18 is already on disk as file /home/oracle/backup/o1_mf_1_18_bjh9wyqj_.arc
-
archived log file name=/home/oracle/backup/o1_mf_1_17_bjh8qp80_.arc thread=1 sequence=17
-
archived log file name=/home/oracle/backup/o1_mf_1_18_bjh9wyqj_.arc thread=1 sequence=18
-
media recovery complete, elapsed time: 00:00:01
-
Finished recover at 17-MAR-15
-
-
RMAN> alter database open;
-
-
RMAN-00571: ===========================================================
-
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
-
RMAN-00571: ===========================================================
-
RMAN-03002: failure of alter db command at 03/17/2015 12:38:58
-
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
-
-
RMAN> alter database open resetlogs;
-
- database opened
這時候資料庫已經開啟,我們看看scott使用者下的資料
點選(此處)摺疊或開啟
-
SQL> conn scott/scott
-
Connected.
-
SQL> select * from tab;
-
-
TNAME TABTYPE CLUSTERID
-
------------------------------ ------- ----------
-
BIN$EWNb3bwua9TgU5F3qMA7BA==$0 TABLE
-
BIN$EWNb3bwva9TgU5F3qMA7BA==$0 TABLE
-
BIN$EWNzFGZja8TgU5F3qMDpoQ==$0 TABLE
-
BIN$EWNzFGZka8TgU5F3qMDpoQ==$0 TABLE
-
BIN$EWOQLk80bF3gU5F3qMBi4g==$0 TABLE
-
BIN$EWOQLk81bF3gU5F3qMBi4g==$0 TABLE
-
BIN$EWOU7d6fbGPgU5F3qMA5vw==$0 TABLE
-
BIN$EWOniXMLbHvgU5F3qMAS1g==$0 TABLE
-
BIN$EWOniXMMbHvgU5F3qMAS1g==$0 TABLE
-
BIN$EWOniXMNbHvgU5F3qMAS1g==$0 TABLE
-
BIN$EWOniXMObHvgU5F3qMAS1g==$0 TABLE
-
-
TNAME TABTYPE CLUSTERID
-
------------------------------ ------- ----------
-
BIN$EWPg+H8lbNLgU5F3qMCj4g==$0 TABLE
-
BIN$EWPg+H8mbNLgU5F3qMCj4g==$0 TABLE
-
BIN$EWPg+H8nbNLgU5F3qMCj4g==$0 TABLE
-
BIN$EWTPDfF1bmPgU5F3qMBohA==$0 TABLE
-
BIN$EWTPDfF2bmPgU5F3qMBohA==$0 TABLE
-
BIN$EWTPDfF3bmPgU5F3qMBohA==$0 TABLE
-
BIN$EWTSV1AibnLgU5F3qMCKBg==$0 TABLE
-
BIN$EWTaumLtbn7gU5F3qMAgNQ==$0 TABLE
-
BIN$EWTdffDnboXgU5F3qMCDZg==$0 TABLE
-
BIN$EWTdffDoboXgU5F3qMCDZg==$0 TABLE
-
BIN$EWTdffDpboXgU5F3qMCDZg==$0 TABLE
-
-
TNAME TABTYPE CLUSTERID
-
------------------------------ ------- ----------
-
BIN$EWVlDuUgb2jgU5F3qMAyEQ==$0 TABLE
-
BIN$EWi11zOecYPgU5F3qMAYuA==$0 TABLE
-
BIN$EWjjuPdrc7fgU5F3qMD+ag==$0 TABLE
-
BIN$EWjqnThDc77gU5F3qMC90A==$0 TABLE
-
BIN$EWjqnThEc77gU5F3qMC90A==$0 TABLE
-
BIN$EWlYdSbqdmHgU5F3qMBLJg==$0 TABLE
-
BIN$EWlpmtpQdoXgU5F3qMAiMw==$0 TABLE
-
BONUS TABLE
-
DEPT TABLE
-
DEPT1 TABLE
-
EMP TABLE
-
-
TNAME TABTYPE CLUSTERID
-
------------------------------ ------- ----------
-
SALGRADE TABLE
-
T1 TABLE
-
T2 TABLE
-
-
36 rows selected.
-
-
SQL> select * from t1;
-
-
ID
-
----------
-
1
-
2
-
3
-
4
-
5
-
6
-
7
-
8
-
9
-
10
-
-
10 rows selected.
-
-
SQL> select * from t2;
-
-
ID
-
----------
-
1
-
2
-
3
-
4
-
5
-
6
-
7
-
8
-
9
-
10
-
-
10 rows selected.
-
- SQL>
發現scott回收站裡有很多垃圾表,可以無視,我們發現t1,t2的資料都正常,這時候只要把scott的資料匯出來再匯入到主庫就好了
- [oracle@test ~]$ expdp scott/scott dumpfile=scott.dmp directory=dir schemas=scott;
- Export: Release 11.2.0.3.0 - Production on Tue Mar 17 13:17:05 2015
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
- Starting \"SCOTT\".\"SYS_EXPORT_SCHEMA_01\": scott/******** dumpfile=scott.dmp directory=dir schemas=scott
- Estimate in progress using BLOCKS method...
- Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
- Total estimation using BLOCKS method: 384 KB
- Processing object type SCHEMA_EXPORT/USER
- Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
- Processing object type SCHEMA_EXPORT/ROLE_GRANT
- Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
- Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
- Processing object type SCHEMA_EXPORT/TABLE/TABLE
- Processing object type SCHEMA_EXPORT/TABLE/COMMENT
- Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
- Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
- Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
- Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
- Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
- Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
- Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
- Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
- . . exported \"SCOTT\".\"DEPT\" 5.937 KB 4 rows
- . . exported \"SCOTT\".\"DEPT1\" 5.515 KB 4 rows
- . . exported \"SCOTT\".\"EMP\" 8.570 KB 14 rows
- . . exported \"SCOTT\".\"SALGRADE\" 5.867 KB 5 rows
- . . exported \"SCOTT\".\"T1\" 5.070 KB 10 rows
- . . exported \"SCOTT\".\"T2\" 5.070 KB 10 rows
- . . exported \"SCOTT\".\"BONUS\" 0 KB 0 rows
- Master table \"SCOTT\".\"SYS_EXPORT_SCHEMA_01\" successfully loaded/unloaded
- ******************************************************************************
- Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
- /home/oracle/scott.dmp
- Job \"SCOTT\".\"SYS_EXPORT_SCHEMA_01\" successfully completed at 13:18:34
- [oracle@test ~]$ scp scott.dmp psd:/home/oracle
- The authenticity of host \'psd (192.168.119.145)\' can\'t be established.
- RSA key fingerprint is 67:e2:04:8e:aa:42:f1:97:c6:14:69:36:ef:86:2e:b0.
- Are you sure you want to continue connecting (yes/no)? yes
- Warning: Permanently added \'psd,192.168.119.145\' (RSA) to the list of known hosts.
- oracle@psd\'s password:
- scott.dmp 100% 288KB 288.0KB/s 00:00
- [oracle@test ~]$
- [oracle@psd ~]$ impdp system/oracle dumpfile=scott.dmp directory=dir ;
- Import: Release 11.2.0.3.0 - Production on Tue Mar 17 13:20:05 2015
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
- Master table \"SYSTEM\".\"SYS_IMPORT_FULL_01\" successfully loaded/unloaded
- Starting \"SYSTEM\".\"SYS_IMPORT_FULL_01\": system/******** dumpfile=scott.dmp directory=dir
- Processing object type SCHEMA_EXPORT/USER
- Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
- Processing object type SCHEMA_EXPORT/ROLE_GRANT
- Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
- Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
- Processing object type SCHEMA_EXPORT/TABLE/TABLE
- Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
- . . imported \"SCOTT\".\"DEPT\" 5.937 KB 4 rows
- . . imported \"SCOTT\".\"DEPT1\" 5.515 KB 4 rows
- . . imported \"SCOTT\".\"EMP\" 8.570 KB 14 rows
- . . imported \"SCOTT\".\"SALGRADE\" 5.867 KB 5 rows
- . . imported \"SCOTT\".\"T1\" 5.070 KB 10 rows
- . . imported \"SCOTT\".\"T2\" 5.070 KB 10 rows
- . . imported \"SCOTT\".\"BONUS\" 0 KB 0 rows
- Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
- Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
- Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
- ORA-39082: Object type ALTER_PROCEDURE:\"SCOTT\".\"P_INSERT\" created with compilation warnings
- ORA-39082: Object type ALTER_PROCEDURE:\"SCOTT\".\"P_INSERT_T\" created with compilation warnings
- Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
- Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
- Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
- Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
- Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
- Job \"SYSTEM\".\"SYS_IMPORT_FULL_01\" completed with 2 error(s) at 13:20:33
- [oracle@psd ~]$ sqlplus scott/scott
- SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 17 13:20:47 2015
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
- SQL> select * from t1;
- ID
- ----------
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 10 rows selected.
- SQL> select * from t2;
- ID
- ----------
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 10 rows selected.
使用者誤刪恢復完成。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29339009/viewspace-1462709/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle使用小記、刪除恢復Oracle
- Oracle 檔案意外刪除恢復(Linux)OracleLinux
- 【北亞資料恢復】誤刪除oracle表和誤刪除oracle表資料的資料恢復方法資料恢復Oracle
- Oracle RMAN恢復測試Oracle
- linux下恢復誤刪除oracle的資料檔案LinuxOracle
- QQ恢復解散後的群聊或刪除後的好友的方法
- win10 自帶應用刪除後怎麼恢復_win10自帶應用刪除後的恢復方法Win10
- hbase 恢復 誤刪除
- NTFS刪除及恢復
- 恢復Oracle資料庫誤刪除資料的語句Oracle資料庫
- Git恢復被刪除的分支Git
- Git恢復刪除的檔案Git
- [Oracle]Oracle資料庫資料被修改或者刪除恢復資料Oracle資料庫
- 行動硬碟刪除的檔案能恢復嗎,怎麼恢復硬碟刪除的檔案硬碟
- Oracle恢復誤刪資料Oracle
- windows10系統刪除Windows defender後如何恢復Windows
- 【資料庫資料恢復】LINUX環境下ORACLE資料庫誤刪除的資料恢復資料庫資料恢復LinuxOracle
- [20190130]刪除tab$記錄的恢復.txt
- MySQL 如何重建/恢復刪除的 sys SchemaMySql
- 如何恢復被刪除的 GitLab 專案?Gitlab
- [20210803]刪除user$的恢復準備.txt
- sd卡刪除的檔案如何恢復SD卡
- [20210930]bbed恢復刪除的資料.txt
- Shift + Delete刪除的檔案如何恢復?delete
- 微軟“粗暴”刪除 WSATools 後續:“道歉”並恢復了WSATools,但刪除理由是“正當的”微軟
- oracle級聯刪除使用者,刪除表空間Oracle
- Mysql資料庫delete刪除後資料恢復報告MySql資料庫delete資料恢復
- 【oracle資料庫資料恢復】誤操作導致的資料庫誤刪除的資料恢復案例Oracle資料庫資料恢復
- oracle關閉狀態刪除活動日誌報錯恢復(一)Oracle
- 相機sd卡刪除的照片如何恢復SD卡
- 如何使用 testdisk 恢復已刪除的檔案
- 在LVM中恢復已刪除的物理卷LVM
- U盤的東西刪除了怎麼恢復,怎麼恢復U盤刪除的檔案
- 【資料庫資料恢復】HP-UX系統ORACLE資料庫被誤刪除的資料恢復資料庫資料恢復UXOracle
- 【NetApp資料恢復案例】針對NetApp誤刪除資料的恢復APP資料恢復
- 儲存刪除資料後恢復方法-適用netAPP儲存APP
- 虛擬機器vmdk檔案刪除後如何恢復資料虛擬機
- SQLSERVER恢復測試SQLServer
- Linux系統中檔案被刪除後的恢復方法(ext4)Linux