分类： Oracle
2013-07-08 16:25:39
一、案例说明
利用rman备份数据库后，因为人工误删除表空间，现在需要使用非完全恢复来找回被误删除的表空间
二 环境准备
[oracle@RAC2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 6 18:58:03 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEST
RMAN_TS
TEMP
7 rows selected.
SQL> select open_mode,log_mode from v$database;
OPEN_MODE LOG_MODE
---------- ------------
READ WRITE ARCHIVELOG
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/datafile/system.dbf
/opt/oracle/oradata/datafile/undotbs1.dbf
/opt/oracle/oradata/datafile/sysaux.dbf
/opt/oracle/oradata/datafile/users.dbf
/opt/oracle/oradata/datafile/test.dbf
/opt/oracle/oradata/datafile/rman_ts.dbf
6 rows selected.
SQL> conn test/test
Connected.
SQL> slect count(*) from tabs;
SP2-0734: unknown command beginning "slect coun..." - rest of line ignored.
SQL> select count(*) from tabs;
COUNT(*)
----------
2
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
TEST1
TEST
SQL> create table test3 as select * from dual;
Table created.
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
TEST1
TEST3
TEST
SQL> conn / as sysdba
Connected.
SQL> desc dba_users;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
SQL> col default_tablespace for a10
SQL> select username,user_id,default_tablespace from dba_users where user_id=55;
USERNAME USER_ID DEFAULT_TA
------------------------------ ---------- ----------
TEST 55 TEST
SQL> conn test/test
Connected.
SQL> select * from test3;
D
-
X
SQL>
SQL>
SQL>
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@RAC2 ~]$
[oracle@RAC2 ~]$
[oracle@RAC2 ~]$
[oracle@RAC2 ~]$
[oracle@RAC2 ~]$
[oracle@RAC2 ~]$
[oracle@RAC2 ~]$ pwd
/home/oracle
[oracle@RAC2 ~]$ ls -l
total 40
drwxr-xr-x 2 oracle oinstall 4096 Jan 9 2012 backup
-rw-r--r-- 1 oracle oinstall 323 Oct 5 2012 current_sql.sql
drwxr-xr-x 2 oracle oinstall 4096 Oct 25 2009 Desktop
-rw-r--r-- 1 oracle oinstall 259 Oct 5 2012 find_current.sql
-rw-r--r-- 1 oracle oinstall 193 Oct 4 2012 free_ts.sql
drwxr-xr-x 2 oracle oinstall 4096 Jul 6 01:44 rman
drwxr-xr-x 4 oracle oinstall 4096 Oct 7 2012 SQL
-rw-r--r-- 1 oracle oinstall 3552 Jul 6 02:10 sqlnet.log
-rw-r--r-- 1 oracle oinstall 199 Mar 9 20:39 total_MB.sql
-rw-r--r-- 1 oracle oinstall 193 Oct 4 2012 total_ts.sql
[oracle@RAC2 ~]$
[oracle@RAC2 ~]$
[oracle@RAC2 ~]$ cd /opt/backup/
[oracle@RAC2 backup]$ ls -l
total 0
[oracle@RAC2 backup]$
[oracle@RAC2 backup]$
[oracle@RAC2 backup]$ pwd
/opt/backup
[oracle@RAC2 backup]$
[oracle@RAC2 backup]$
[oracle@RAC2 backup]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sat Jul 6 19:03:49 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: RACDB1 (DBID=4241907545)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters 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 '/opt/backup/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle10g/product/10.2.0/db_1/dbs/snapcf_racdb2.f'; # default
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
142 B F A DISK 06-JUL-13 1 1 NO LIYF
143 B F A DISK 06-JUL-13 1 1 NO TAG20130706T130158
144 B F A DISK 06-JUL-13 1 1 NO TAG20130706T130330
145 B F A DISK 06-JUL-13 1 1 NO TAG20130706T130609
RMAN> delete backup;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
142 142 1 1 AVAILABLE DISK /opt/oracle10g/product/10.2.0/db_1/dbs/59oe2gtc_1_1
143 143 1 1 AVAILABLE DISK /opt/backup/c-4241907545-20130706-03
144 144 1 1 AVAILABLE DISK /opt/backup/c-4241907545-20130706-04
145 145 1 1 AVAILABLE DISK /opt/backup/c-4241907545-20130706-05
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/opt/oracle10g/product/10.2.0/db_1/dbs/59oe2gtc_1_1 recid=142 stamp=820069292
Deleted 1 objects
RMAN-06207: WARNING: 3 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece /opt/backup/c-4241907545-20130706-03
RMAN-06214: Backup Piece /opt/backup/c-4241907545-20130706-04
RMAN-06214: Backup Piece /opt/backup/c-4241907545-20130706-05
RMAN> crosscheck backup;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/backup/c-4241907545-20130706-03 recid=143 stamp=820069319
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/backup/c-4241907545-20130706-04 recid=144 stamp=820069410
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/backup/c-4241907545-20130706-05 recid=145 stamp=820069570
Crosschecked 3 objects
RMAN> delete backup;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
143 143 1 1 EXPIRED DISK /opt/backup/c-4241907545-20130706-03
144 144 1 1 EXPIRED DISK /opt/backup/c-4241907545-20130706-04
145 145 1 1 EXPIRED DISK /opt/backup/c-4241907545-20130706-05
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/opt/backup/c-4241907545-20130706-03 recid=143 stamp=820069319
deleted backup piece
backup piece handle=/opt/backup/c-4241907545-20130706-04 recid=144 stamp=820069410
deleted backup piece
backup piece handle=/opt/backup/c-4241907545-20130706-05 recid=145 stamp=820069570
Deleted 3 objects
RMAN> list backup summary;
RMAN> backup database format '/opt/backup/%d_%T_%s_%p.bak'tag='liyf';
Starting backup at 06-JUL-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/opt/oracle/oradata/datafile/system.dbf
input datafile fno=00003 name=/opt/oracle/oradata/datafile/sysaux.dbf
input datafile fno=00002 name=/opt/oracle/oradata/datafile/undotbs1.dbf
input datafile fno=00006 name=/opt/oracle/oradata/datafile/rman_ts.dbf
input datafile fno=00005 name=/opt/oracle/oradata/datafile/test.dbf
input datafile fno=00004 name=/opt/oracle/oradata/datafile/users.dbf
channel ORA_DISK_1: starting piece 1 at 06-JUL-13
channel ORA_DISK_1: finished piece 1 at 06-JUL-13
piece handle=/opt/backup/RACDB1_20130706_173_1.bak tag=LIYF comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:25
Finished backup at 06-JUL-13
Starting Control File and SPFILE Autobackup at 06-JUL-13
piece handle=/opt/backup/c-4241907545-20130706-06 comment=NONE
Finished Control File and SPFILE Autobackup at 06-JUL-13
RMAN> exit
Recovery Manager complete.
[oracle@RAC2 backup]$
[oracle@RAC2 backup]$
[oracle@RAC2 backup]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 6 19:10:19 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@RAC2 backup]$
[oracle@RAC2 backup]$
[oracle@RAC2 backup]$
[oracle@RAC2 backup]$ pwd
/opt/backup
[oracle@RAC2 backup]$ ls -tl
total 780600
-rw-r----- 1 oracle oinstall 7667712 Jul 6 19:11 c-4241907545-20130706-07
-rw-r----- 1 oracle oinstall 7667712 Jul 6 19:09 c-4241907545-20130706-06
-rw-r----- 1 oracle oinstall 783204352 Jul 6 19:09 RACDB1_20130706_173_1.bak
通过alert日志，查找出删除表空间test的时间Sat Jul 6 19:11:12 2013
三 恢复测试
[oracle@RAC2 backup]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sat Jul 6 19:13:19 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> list backup summary;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 07/06/2013 19:13:29
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
RMAN> startup nomount;
Oracle instance started
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 79694068 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
RMAN> restore controlfile from '/opt/backup/c-4241907545-20130706-06';
Starting restore at 06-JUL-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/opt/oracle/stage/std.ctl
output filename=/opt/oracle/stage/std1.ctl
Finished restore at 06-JUL-13
RMAN> sql 'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 06-JUL-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/datafile/system.dbf
restoring datafile 00002 to /opt/oracle/oradata/datafile/undotbs1.dbf
restoring datafile 00003 to /opt/oracle/oradata/datafile/sysaux.dbf
restoring datafile 00004 to /opt/oracle/oradata/datafile/users.dbf
restoring datafile 00005 to /opt/oracle/oradata/datafile/test.dbf
restoring datafile 00006 to /opt/oracle/oradata/datafile/rman_ts.dbf
channel ORA_DISK_1: reading from backup piece /opt/backup/RACDB1_20130706_173_1.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/backup/RACDB1_20130706_173_1.bak tag=LIYF
channel ORA_DISK_1: restore complete, elapsed time: 00:01:16
Finished restore at 06-JUL-13
RMAN> run
2> { sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
3> set until time='2013-07-06 19:11:12';
4> recover database;
5> }
sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"
executing command: SET until clause
Starting recover at 06-JUL-13
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file /opt/oracle/oradata/group_2.260.708527535
archive log filename=/opt/oracle/oradata/group_2.260.708527535 thread=1 sequence=2
media recovery complete, elapsed time: 00:00:02
Finished recover at 06-JUL-13
RMAN> alter database open resetlogs;
database opened
RMAN> exit
Recovery Manager complete.
[oracle@RAC2 backup]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 6 19:27:56 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEST
RMAN_TS
TEMP
7 rows selected.
SQL> conn test/test
Connected.
SQL> slect count(*) from tabs;
SP2-0734: unknown command beginning "slect coun..." - rest of line ignored.
SQL> select count(*) from tabs;
COUNT(*)
----------
3
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
TEST1
TEST3
TEST
SQL> select * from test3;
D
-
X
