---只读user表空间损坏 --------------------------------模拟破坏只读user表空间损坏----------------------------------------------------------------- [oracle@station ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 8 12:58:58 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn /as sysdba Connected. SQL> create table hr.strom (a number) tablespace users; ----在HR用户下创建strom表 Table created. SQL> alter system switch logfile; System altered. SQL> insert into hr.strom values(1); 1 row created. SQL> alter system switch logfile; System altered. SQL> commit; Commit complete. SQL> insert into hr.strom values(2); 1 row created. SQL> alter system switch logfile; System altered. SQL> commit; Commit complete. SQL> alter tablespace users read only; Tablespace altered. SQL> ! [oracle@station ~]$ rman target / Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jun 8 13:05:25 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: ORCL (DBID=1312694260) RMAN> backup tablespace users; ----备份users表空间 Starting backup at 08-JUN-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=142 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7wnthzto_.dbf channel ORA_DISK_1: starting piece 1 at 08-JUN-12 channel ORA_DISK_1: finished piece 1 at 08-JUN-12 piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_06_08/o1_mf_nnndf_TAG20120608T130546_7x4d7bvs_.bkp tag=TAG20120608T130546 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 08-JUN-12 RMAN> quit Recovery Manager complete. [oracle@station ~]$ rm -rf /u01/app/oracle/oradata/ORCL/datafile/*users* [oracle@station ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 8 13:08:05 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn /as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 281018368 bytes Fixed Size 2020192 bytes Variable Size 130026656 bytes Database Buffers 146800640 bytes Redo Buffers 2170880 bytes Database mounted. ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7wnthzto_.dbf' --------------------------------模拟破坏只读user表空间损坏----------------------------------------------------------------- SQL> ! [oracle@station ~]$ rman target / Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jun 8 13:09:24 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: ORCL (DBID=1312694260, not open) RMAN> list backup of tablespace users; using target database control file instead of recovery catalog List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 2.04M DISK 00:00:01 08-JUN-12 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20120608T130546 Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_06_08/o1_mf_nnndf_TAG20120608T130546_7x4d7bvs_.bkp List of Datafiles in backup set 1 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 4 Full 888842 08-JUN-12 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7wnthzto_.dbf RMAN> run { restore tablespace users; -----还原,修复users表空间 2> recover tablespace users; 3> } Starting restore at 08-JUN-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=155 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7wnthzto_.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_06_08/o1_mf_nnndf_TAG20120608T130546_7x4d7bvs_.bkp channel ORA_DISK_1: restored backup piece 1 piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_06_08/o1_mf_nnndf_TAG20120608T130546_7x4d7bvs_.bkp tag=TAG20120608T130546 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 08-JUN-12 Starting recover at 08-JUN-12 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 08-JUN-12 RMAN> alter database open; -----在RMAN下将oracle启到OPEN状态 database opened RMAN> quit; Recovery Manager complete. [oracle@station ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 8 13:11:18 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn /as sysdba Connected. SQL> select enabled,status,name from v$datafile where name like '%users%'; ----查看表空间的是否是read write ENABLED STATUS ---------- ------- NAME -------------------------------------------------------------------------------- READ ONLY ONLINE /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7x4dj22v_.dbf SQL> alter tablespace users read write; ---将只读表空间转换成可写状态 Tablespace altered. SQL> select enabled,status,name from v$datafile where name like '%users%'; ENABLED STATUS ---------- ------- NAME -------------------------------------------------------------------------------- READ WRITE ONLINE /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7x4dj22v_.dbf SQL> select * from hr.strom; ----查看修复的表空间是否有strom表 A ---------- 1 2
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13453302/viewspace-735346/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13453302/viewspace-735346/