Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2837138
  • 博文数量: 631
  • 博客积分: 10716
  • 博客等级: 上将
  • 技术积分: 8397
  • 用 户 组: 普通用户
  • 注册时间: 2008-04-01 22:35
文章分类

全部博文(631)

文章存档

2020年(2)

2019年(22)

2018年(4)

2017年(37)

2016年(22)

2015年(1)

2013年(12)

2012年(20)

2011年(19)

2010年(20)

2009年(282)

2008年(190)

分类: 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

阅读(3848) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~