签到天数: 2121 天 连续签到: 9 天 [LV.Master]至尊海神IIIIII
|
楼主 |
发表于 2009-7-28 22:45
|
显示全部楼层
数据库的不完全恢复
数据库的不完全恢复
用户需要恢复一年前某几张表的数据,数据库比较大,选择恢复表所在的表空间也可以满足需求.在自己的虚拟机测试一下.
[oracle@LGX LGX1]$rman
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jul 20 16:35:24 2009
Copyright (c) 1982, 2005, Oracle.All rights reserved.
RMAN> connect target /
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 243269632 bytes
Fixed Size 1218748 bytes
Variable Size 79693636 bytes
Database Buffers 159383552 bytes
Redo Buffers 2973696 bytes
RMAN> restore controlfile from '/mnt1/backup/0akkk79g_1_1';
Starting restore at 20-JUL-09
using target database control file instead of recovery catalog
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:04
output filename=/mnt1/oradata/LGX1/control01.ctl
output filename=/mnt1/oradata/LGX1/control02.ctl
output filename=/mnt1/oradata/LGX1/control03.ctl
Finished restore at 20-JUL-09
RMAN> sql 'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1
/**********************
*
*还原打开数据库必须的表空间system和undotbs1,和生产表所在的表空间LGX.并重定向还原.
*
**********************/
RMAN> run{
2> allocate channel ch0 device type disk;
3> set newname for datafile 1 to '/mnt1/oradata/LGX1/system01.dbf';
4> set newname for datafile 2 to '/mnt1/oradata/LGX1/undotbs01.dbf';
5> set newname for datafile 5 to '/mnt1/oradata/LGX1/lgx_1.dbf';
6> restore tablespace system,undotbs1,LGX;
7> switch datafile all;
8> release channel ch0;
9> }
allocated channel: ch0
channel ch0: sid=156 devtype=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 20-JUL-09
channel ch0: starting datafile backupset restore
channel ch0: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /mnt1/oradata/LGX1/system01.dbf
restoring datafile 00002 to /mnt1/oradata/LGX1/undotbs01.dbf
restoring datafile 00005 to /mnt1/oradata/LGX1/lgx_1.dbf
channel ch0: reading from backup piece /mnt1/backup/09kkk782_1_1
channel ch0: restored backup piece 1
piece handle=/mnt1/backup/09kkk782_1_1 tag=TAG20090720T150225
channel ch0: restore complete, elapsed time: 00:00:46
Finished restore at 20-JUL-09
datafile 1 switched to datafile copy
input datafile copy recid=13 stamp=692728667 filename=/mnt1/oradata/LGX1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=14 stamp=692728667 filename=/mnt1/oradata/LGX1/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=15 stamp=692728667 filename=/mnt1/oradata/LGX1/lgx_1.dbf
released channel: ch0
RMAN> exit
Recovery Manager complete.
[oracle@LGX LGX1]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 20 16:37:58 2009
Copyright (c) 1982, 2005, Oracle.All rights reserved.
/**********************
*
*查看数据库的所有数据文件
*
**********************/
SQL> conn / as sysdba
Connected.
SQL> col name fro a40
SQL> select file#,name from v$datafile;
FILE# NAME
---------- ----------------------------------------
1 /mnt1/oradata/LGX1/system01.dbf
2 /mnt1/oradata/LGX1/undotbs01.dbf
3 /mnt1/oradata/LGX/sysaux01.dbf
4 /mnt1/oradata/LGX/users01.dbf
5 /mnt1/oradata/LGX1/lgx_1.dbf
6 /mnt1/oradata/LGX/test_1.dbf
6 rows selected.
SQL> select name from v$tablespace;
NAME
----------------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
LGX
TEST
7 rows selected.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/mnt1/oradata/LGX/redo03.log
/mnt1/oradata/LGX/redo02.log
/mnt1/oradata/LGX/redo01.log
/**********************
*
*没有restore的数据文件全部offline drop
*
**********************/
SQL> alter database datafile 3,4,6 offline drop;
Database altered.
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@LGX LGX1]$ rman
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jul 20 16:38:38 2009
Copyright (c) 1982, 2005, Oracle.All rights reserved.
RMAN> connect target /
connected to target database: LGX (DBID=2389470549, not open)
/**********************
*
*recover 数据库,跳过不restore的表空间
*
**********************/
RMAN> RECOVER DATABASE SKIP TABLESPACE SYSAUX, USERS, 'TEST', TEMP;
Starting recover at 20-JUL-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
starting media recovery
archive log filename=/mnt1/arch/1_2_692722798.dbf thread=1 sequence=2
archive log filename=/mnt1/arch/1_2_692722798.dbf thread=1 sequence=3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/20/2009 16:38:58
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/mnt1/arch/1_2_692722798.dbf'
ORA-00310: archived log contains sequence 2; sequence 3 required
ORA-00334: archived log: '/mnt1/arch/1_2_692722798.dbf'
RMAN> exit
Recovery Manager complete.
[oracle@LGX LGX1]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 20 16:39:49 2009
Copyright (c) 1982, 2005, Oracle.All rights reserved.
SQL> conn / as sysdba
Connected.
/**********************
*
*重定向online redo log新路径
*
**********************/
SQL> alter database rename file '/mnt1/oradata/LGX/redo01.log' to '/mnt1/oradata/LGX1/redo01.log';
Database altered.
SQL> alter database rename file '/mnt1/oradata/LGX/redo02.log' to '/mnt1/oradata/LGX1/redo02.log';
Database altered.
SQL> alter database rename file '/mnt1/oradata/LGX/redo03.log' to '/mnt1/oradata/LGX1/redo03.log';
Database altered.
SQL> alter database open resetlogs;
Database altered.
/**********************
*
*查看表空间LGX里的表
*
**********************/
SQL> select * from lgx;
A
----------
1
SQL> col name for a40
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ---------------------------------------- ---------------------
1 /mnt1/oradata/LGX1/system01.dbf SYSTEM
2 /mnt1/oradata/LGX1/undotbs01.dbf ONLINE
3 /mnt1/oradata/LGX/sysaux01.dbf OFFLINE
4 /mnt1/oradata/LGX/users01.dbf OFFLINE
5 /mnt1/oradata/LGX1/lgx_1.dbfONLINE
6 /mnt1/oradata/LGX/test_1.dbfOFFLINE
6 rows selected.
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@LGX LGX1]$ |
|
[复制链接]
IP卡
狗仔卡
发表于 2009-7-28 22:45
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
抢沙发
千斤顶
显身卡