undo表空间文件丢失恢复(1)--有备份

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/lihuarongaini/article/details/99173992

undo表空间文件丢失恢复(1)--有备份

undo表空间的数据文件丢失,如果有备份的情况下,恢复非常简单,下边给出一个例子:

 

 

 

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 12 18:02:13 2015

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.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     1

Next log sequence to archive   2

Current log sequence           2

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rhel6_lhr ~]$ rman target /

 

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Mar 12 18:02:23 2015

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

connected to target database: ORA1024G (DBID=2698093861)

 

RMAN> backup database;

 

Starting backup at 2015-03-12 18:02:30

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=144 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/u03/app/oracle/oradata/ora1024g/system01.dbf

input datafile fno=00003 name=/u03/app/oracle/oradata/ora1024g/sysaux01.dbf

input datafile fno=00005 name=/u03/app/oracle/oradata/ora1024g/example01.dbf

input datafile fno=00002 name=/u03/app/oracle/oradata/ora1024g/undotbs01.dbf

input datafile fno=00004 name=/u03/app/oracle/oradata/ora1024g/users01.dbf

channel ORA_DISK_1: starting piece 1 at 2015-03-12 18:02:31

channel ORA_DISK_1: finished piece 1 at 2015-03-12 18:03:49

piece handle=/u03/app/oracle/flash_recovery_area/ORA1024G/backupset/2015_03_12/o1_mf_nnndf_TAG20150312T180231_bj2s1vh3_.bkp tag=TAG20150312T180231 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:19

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 2015-03-12 18:03:53

channel ORA_DISK_1: finished piece 1 at 2015-03-12 18:03:54

piece handle=/u03/app/oracle/flash_recovery_area/ORA1024G/backupset/2015_03_12/o1_mf_ncsnf_TAG20150312T180231_bj2s49dm_.bkp tag=TAG20150312T180231 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 2015-03-12 18:03:54

 

RMAN> report schema;

 

Report of database schema

 

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1    480      SYSTEM               ***     /u03/app/oracle/oradata/ora1024g/system01.dbf

2    30       UNDOTBS1             ***    /u03/app/oracle/oradata/ora1024g/undotbs01.dbf

3    250      SYSAUX               ***     /u03/app/oracle/oradata/ora1024g/sysaux01.dbf

4    5        USERS                ***     /u03/app/oracle/oradata/ora1024g/users01.dbf

5    100      EXAMPLE              ***     /u03/app/oracle/oradata/ora1024g/example01.dbf

 

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1    20       TEMP                 32767       /u03/app/oracle/oradata/ora1024g/temp01.dbf

 

RMAN> exit

 

 

Recovery Manager complete.

[oracle@rhel6_lhr ~]$ rm /u03/app/oracle/oradata/ora1024g/undotbs01.dbf

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 12 18:05:00 2015

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> startup force;

ORACLE instance started.

 

Total System Global Area  448790528 bytes

Fixed Size                  2084616 bytes

Variable Size             130023672 bytes

Database Buffers          310378496 bytes

Redo Buffers                6303744 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file

ORA-01110: data file 2: '/u03/app/oracle/oradata/ora1024g/undotbs01.dbf'

 

 

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rhel6_lhr ~]$ rman target /

 

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Mar 12 18:05:37 2015

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

connected to target database: ORA1024G (DBID=2698093861, not open)

 

RMAN> restore tablespace undotbs1;

 

Starting restore at 2015-03-12 18:05:56

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: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00002 to /u03/app/oracle/oradata/ora1024g/undotbs01.dbf

channel ORA_DISK_1: reading from backup piece /u03/app/oracle/flash_recovery_area/ORA1024G/backupset/2015_03_12/o1_mf_nnndf_TAG20150312T180231_bj2s1vh3_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/u03/app/oracle/flash_recovery_area/ORA1024G/backupset/2015_03_12/o1_mf_nnndf_TAG20150312T180231_bj2s1vh3_.bkp tag=TAG20150312T180231

channel ORA_DISK_1: restore complete, elapsed time: 00:00:06

Finished restore at 2015-03-12 18:06:03

 

RMAN> recover tablespace undotbs1;

 

Starting recover at 2015-03-12 18:06:17

using channel ORA_DISK_1

 

starting media recovery

media recovery complete, elapsed time: 00:00:02

 

Finished recover at 2015-03-12 18:06:19

 

RMAN> alter database open;

 

database opened

 

--注意这里一定要让undo在线

RMAN> sql 'alter database datafile 2 online';

 

using target database control file instead of recovery catalog

sql statement: alter database datafile 2 online

 

RMAN> exit

 

RMAN> report schema;

 

Report of database schema

 

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1    480      SYSTEM               ***     /u03/app/oracle/oradata/ora1024g/system01.dbf

2    30       UNDOTBS1             ***     /u03/app/oracle/oradata/ora1024g/undotbs01.dbf

3    250      SYSAUX               ***     /u03/app/oracle/oradata/ora1024g/sysaux01.dbf

4    5        USERS                ***     /u03/app/oracle/oradata/ora1024g/users01.dbf

5    100      EXAMPLE              ***     /u03/app/oracle/oradata/ora1024g/example01.dbf

 

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1    20       TEMP                 32767       /u03/app/oracle/oradata/ora1024g/temp01.dbf

 

RMAN>

 

RMAN>

 

展开阅读全文

ORACLE释放undo表空间

08-20

在日常的数据库维护和数据库编程中经常会遇到犹豫对大数据量做DML操作后是得ORACLE的undo表空间扩展到十几个G或者几十个G 但是这些表空间的所占用磁盘的物理空间又不会被oracle所释放,如果你用的是PC机很可能会遇到磁盘空间不足的问题,经过个人整理经过如下操作可以重构undo表空间,同样temp表空间也可能在你查询大数据或则创建索引的时候无限扩大导致磁盘空间不足,同样可以用如下方式解决此问题:rnrn  --查看各表空间名称rnrn  select name from v$tablespacernrn  --查看某个表空间信息rnrn  select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS1';rnrn  --查看回滚段的使用情况,哪个用户正在使用回滚段的资源,如果有用户最好更换时间(特别是生产环境)。rnrn  select s.username, u.name from v$transaction t,v$rollstat r, v$rollname u,v$session srnrn  where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username;rnrn  --检查UNDO Segment状态rnrn  select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;rnrn  --创建新的UNDO表空间,并设置自动扩展参数;rnrn  create undo tablespace undotbs2 datafile 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS02.DBF' size 10m reuse autoextend on next 100m maxsize unlimited;rnrn  -- 动态更改spfile配置文件;rnrn  alter system set undo_tablespace=undotbs2 scope=both;rnrn  --等待原UNDO表空间所有UNDO SEGMENT OFFLINE;rnrn  select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;rnrn  --再执行看UNDO表空间所有UNDO SEGMENT ONLINE;rnrn  select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;rnrn  -- 删除原有的UNDO表空间;rnrn  drop tablespace undotbs1 including contents;rnrn  --确认删除是否成功;rnrn  select name from v$tablespace;rnrn最后需要在重启数据库或者重启计算机后到存储数据文件的路径下删除数据文件rn 论坛

删除undo表空间

09-01

rnrnUNDOTB2为undo表空间下面的为添加的扩展表空间 rnrnUNDO_TB3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\TAXDEV\ rnUNDO_TB5 E:\ORACLE\PRODUCT\10.2.0\ORADATA\TAXDEV\ rnUNDO_TB2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\TAXDEV\ rnUNDOTBS01.DBF E:\ORACLE\PRODUCT\10.2.0\ORADATA\TAXDEV\rnUNDO_TB31 E:\ORACLE\PRODUCT\10.2.0\ORADATA\TAXDEV\ rn rn新建立一个UNDOTB3 undo表空间,切换undo表空间rnrnrn切换UNDO表空间rn• 可以从使用一个UNDO 表空间切换到使用另一个UNDO表空间。 • 一次只能将一个UNDO 表空间分配给某个数据库。 • 一个例程中可以存在多个UNDO 表空间,但只能有一个处于活动状态。 • 使用ALTER SYSTEM 命令,可以在各个UNDO 表空间之间进行动态切换。 rnrnALTER SYSTEM SET UNDO_TABLESPACE=UNDOTB3;rnrnrn 删除UNDO表空间rn• 使用DROP TABLESPACE 命令,可以删除UNDO 表空间。 DROP TABLESPACE UNDOTB2; • 某个UNDO 表空间只有在当前未由任何例程使用的时候才能被删除。 • 要删除活动的UNDO 表空间,请执行以下操作: – 切换到新的UNDO 表空间rnALTER SYSTEM SET UNDO_TABLESPACE=UNDOTB3;rnrn– 完成当前所有事务处理后,删除该表空间 要确定是否存在任何一个活动的事务处理,请使用以下查询:rn SQL> SELECT a.name,b.status 2 FROM v$rollname a, v$rollstat b 3 WHERE a.name IN ( SELECT segment_name 4 FROM dba_segments 5 WHERE tablespace_name = 'UNDOTBS‘ ) 6 AND a.usn = b.usn; rn查询结果为空。rnrn状态为PENDING OFFLINE 的某个还原段仍包含活动的事务处理。如果查询没有返回任 何行,则表明所有事务处理均已完成,并且可以使用以下命令删除该表空间。rnrn SQL> DROP TABLESPACE UNDOTB2;rnrn删除提示 无法删除 undotb2下面的扩展表空间 UNDO_TB2 rnrn ORA-01548: 已找到活动回退段 '_SYSSMU6$', 终止删除表空间 rnrnrnrn求助如何:删除 UNDOTB2 在切换undotb3是 已经把undotb2状态变为挂起 offline 论坛

没有更多推荐了,返回首页