Posts
恢复损坏的撤销表空间
讲述了通过设置 _offline_rollback_segments / _corrupted_rollback_segments 隐含参数, 恢复损坏的undo表空间的例子
因在netapp存储上误操作, 清除了文件锁, 导致数据库意外终止
Thu Mar 5 23:05:20 2009
KCF: write/open error block=0x724 online=1
file=2 /home/oracle/app/oracle/oradata/rcat/undotbs01.dbf
error=27061 txt: 'Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 32768'
Automatic datafile offline due to write error on
file 2: /home/oracle/app/oracle/oradata/rcat/undotbs01.dbf
KCF: write/open error block=0x2b31 online=1
file=3 /home/oracle/app/oracle/oradata/rcat/sysaux01.dbf
error=27061 txt: 'Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 8192'
Thu Mar 5 23:05:20 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_25061.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
Automatic datafile offline due to write error on
file 3: /home/oracle/app/oracle/oradata/rcat/sysaux01.dbf
Thu Mar 5 23:05:20 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_25061.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
Thu Mar 5 23:05:21 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_25061.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
Thu Mar 5 23:05:22 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_25061.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
KCF: write/open error block=0x7671 online=1
file=1 /home/oracle/app/oracle/oradata/rcat/system01.dbf
error=27061 txt: 'Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 8192'
Thu Mar 5 23:05:23 2009
Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_dbw0_25055.trc:
ORA-01243: system tablespace file suffered media failure
ORA-01114: IO error writing block to file 1 (block # 30321)
ORA-01110: data file 1: '/home/oracle/app/oracle/oradata/rcat/system01.dbf'
ORA-27061: waiting for async I/Os failed
Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 8192
DBW0: terminating instance due to error 1243
Instance terminated by DBW0, pid = 25055
重启后报错
ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'和
ORA-376 encountered when generating server alert SMG-3600
告警日志:
Sat May 30 10:40:04 2009 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 500 LICENSE_SESSIONS_WARNING = 150 Picked latch-free SCN scheme 3 Autotune of undo retention is turned on. IMODE=BR ILAT =182 LICENSE_MAX_USERS = 0 SYS auditing is enabled ksdpec: called for event 13740 prior to event group initialization Starting up ORACLE RDBMS Version: 10.2.0.4.0. System parameters with non-default values: processes = 1500 sessions = 1655 resource_limit = TRUE license_max_sessions = 500 license_sessions_warning = 150 __shared_pool_size = 306184192 __large_pool_size = 4194304 __java_pool_size = 4194304 __streams_pool_size = 0 filesystemio_options = setall sga_target = 524288000 control_files = /home/oracle/app/oracle/oradata/rcat/control01.ctl, /home/oracle/app/oracle/oradata/rcat/control02.ctl, /home/oracle/app/oracle/oradata/rcat/control03.ctl control_file_record_keep_time= 30 db_block_size = 8192 __db_cache_size = 201326592 compatible = 10.2.0.4.0 log_archive_dest_1 = LOCATION=/home/oracle/app/oracle/oradata/rcat/archive log_archive_dest_state_1 = ENABLE log_archive_format = %t_%s_%r.arc archive_lag_target = 1800 db_file_multiblock_read_count= 16 db_recovery_file_dest = /home/oracle/app/oracle/oradata/rcat/flash_recovery_area db_recovery_file_dest_size= 2147483648 fast_start_mttr_target = 300 undo_management = AUTO undo_tablespace = UNDOTBS1 undo_retention = 10800 remote_login_passwordfile= EXCLUSIVE audit_sys_operations = TRUE db_domain = session_cached_cursors = 200 utl_file_dir = /home/oracle/app/oracle/oradata/rcat/utl job_queue_processes = 10 background_dump_dest = /home/oracle/app/oracle/admin/rcat/bdump user_dump_dest = /home/oracle/app/oracle/admin/rcat/udump core_dump_dest = /home/oracle/app/oracle/admin/rcat/cdump audit_file_dest = /home/oracle/app/oracle/admin/rcat/adump audit_trail = DB_EXTENDED db_name = rcat open_cursors = 3000 pga_aggregate_target = 209715200 aq_tm_processes = 1 PMON started with pid=2, OS id=12880 PSP0 started with pid=3, OS id=12882 MMAN started with pid=4, OS id=12884 DBW0 started with pid=5, OS id=12886 LGWR started with pid=6, OS id=12888 CKPT started with pid=7, OS id=12890 SMON started with pid=8, OS id=12892 RECO started with pid=9, OS id=12894 CJQ0 started with pid=10, OS id=12896 MMON started with pid=11, OS id=12898 MMNL started with pid=12, OS id=12900 Sat May 30 10:40:05 2009 ALTER DATABASE MOUNT Sat May 30 10:40:09 2009 Setting recovery target incarnation to 1 Sat May 30 10:40:09 2009 Successful mount of redo thread 1, with mount id 460376325 Sat May 30 10:40:09 2009 Database mounted in Exclusive Mode Completed: ALTER DATABASE MOUNT Sat May 30 10:40:09 2009 ALTER DATABASE OPEN Sat May 30 10:40:09 2009 Beginning crash recovery of 1 threads parallel recovery started with 3 processes Sat May 30 10:40:10 2009 Started redo scan Sat May 30 10:40:10 2009 Completed redo scan 21 redo blocks read, 3 data blocks need recovery Sat May 30 10:40:10 2009 Started redo application at Thread 1: logseq 7202, block 231, scn 6268440 Sat May 30 10:40:10 2009 Recovery of Online Redo Log: Thread 1 Group 2 Seq 7202 Reading mem 0 Mem# 0: /home/oracle/app/oracle/oradata/rcat/redo02.rdo Sat May 30 10:40:10 2009 Completed redo application Sat May 30 10:40:10 2009 Completed crash recovery at Thread 1: logseq 7202, block 252, scn 6288463 3 data blocks read, 3 data blocks written, 21 redo blocks read Sat May 30 10:40:10 2009 LGWR: STARTING ARCH PROCESSES ARC0 started with pid=17, OS id=12913 Sat May 30 10:40:10 2009 ARC0: Archival started ARC1: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC1 started with pid=18, OS id=12915 Sat May 30 10:40:10 2009 Thread 1 advanced to log sequence 7203 (thread open) Thread 1 opened at log sequence 7203 Current log# 3 seq# 7203 mem# 0: /home/oracle/app/oracle/oradata/rcat/redo03.rdo Successful open of redo thread 1 Sat May 30 10:40:10 2009 ARC1: Becoming the 'no FAL' ARCH ARC1: Becoming the 'no SRL' ARCH Sat May 30 10:40:10 2009 ARC0: Becoming the heartbeat ARCH Sat May 30 10:40:10 2009 SMON: enabling cache recovery Sat May 30 10:40:10 2009 Successfully onlined Undo Tablespace 1. Sat May 30 10:40:10 2009 SMON: enabling tx recovery Sat May 30 10:40:10 2009 Database Characterset is AL32UTF8 Opening with internal Resource Manager plan where NUMA PG = 1, CPUs = 4 replication_dependency_tracking turned off (no async multimaster replication found) Sat May 30 10:40:11 2009 Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_12892.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf' Starting background process QMNC QMNC started with pid=19, OS id=12917 Sat May 30 10:40:12 2009 Completed: ALTER DATABASE OPEN Sat May 30 10:40:13 2009 db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. ORA-376 encountered when generating server alert SMG-3600 Sat May 30 10:45:13 2009 Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_12892.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf' Sat May 30 10:50:14 2009 Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_12892.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf' Sat May 30 10:55:15 2009 Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_12892.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf' Sat May 30 11:00:08 2009 MMNL absent for 1203 secs; Foregrounds taking over Sat May 30 11:00:09 2009 MMNL absent for 1203 secs; Foregrounds taking over MMNL absent for 1203 secs; Foregrounds taking over MMNL absent for 1203 secs; Foregrounds taking over Sat May 30 11:00:17 2009 Errors in file /home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_12892.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf'
跟踪文件1:
/home/oracle/app/oracle/admin/rcat/bdump/rcat_smon_12892.trc Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Data Mining and Real Application Testing options ORACLE_HOME = /home/oracle/app/oracle/product/10.2 System name: Linux Node name: DEV-DB-2 Release: 2.6.9-34.ELsmp Version: #1 SMP Fri Feb 24 16:56:28 EST 2006 Machine: x86_64 Instance name: rcat Redo thread mounted by this instance: 1 Oracle process number: 8 Unix process pid: 12892, image: oracle@DEV-DB-2 (SMON) *** SERVICE NAME:() 2009-05-30 10:40:10.958 *** SESSION ID:(1649.1) 2009-05-30 10:40:10.958 SMON: about to recover undo segment 1 SMON: mark undo segment 1 as needs recovery SMON: about to recover undo segment 2 SMON: mark undo segment 2 as needs recovery SMON: about to recover undo segment 3 SMON: mark undo segment 3 as needs recovery SMON: about to recover undo segment 4 SMON: mark undo segment 4 as needs recovery SMON: about to recover undo segment 5 SMON: mark undo segment 5 as needs recovery SMON: about to recover undo segment 6 SMON: mark undo segment 6 as needs recovery SMON: about to recover undo segment 7 SMON: mark undo segment 7 as needs recovery SMON: about to recover undo segment 8 SMON: mark undo segment 8 as needs recovery SMON: about to recover undo segment 9 SMON: mark undo segment 9 as needs recovery SMON: about to recover undo segment 10 SMON: mark undo segment 10 as needs recovery *** 2009-05-30 10:40:11.610 SMON: following errors trapped and ignored: ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/home/oracle/app/oracle/oradata/rcat/undotbs01.dbf' *** 2009-05-30 10:45:13.135 SMON: about to recover undo segment 1 SMON: mark undo segment 1 as needs recovery ... ...
跟踪文件2:
/home/oracle/app/oracle/admin/rcat/bdump/rcat_mmon_12898.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORACLE_HOME = /home/oracle/app/oracle/product/10.2
System name: Linux
Node name: DEV-DB-2
Release: 2.6.9-34.ELsmp
Version: #1 SMP Fri Feb 24 16:56:28 EST 2006
Machine: x86_64
Instance name: rcat
Redo thread mounted by this instance: 1
Oracle process number: 11
Unix process pid: 12898, image: oracle@DEV-DB-2 (MMON)
*** SERVICE NAME:(SYS$BACKGROUND) 2009-05-30 10:40:13.257
*** SESSION ID:(1646.1) 2009-05-30 10:40:13.257
KEWRCTLRD: OCIStmtFetch Error. ctl_dbid= 435472619, sga_dbid= 435472619
KEWRCTLRD: Retcode: -1, Error Message: ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/home/oracle/app/oracle/oradata/rcat/sysaux01.dbf'
*** SQLSTR: total-len=328, dump-len=240,
STR={select snap_interval, retention,most_recent_snap_time, most_recent_snap_id, status_flag, most_recent_purge_time, most_recent_split_id, most_recent_split_time
, mrct_snap_time_num, mrct_purge_time_num, snapint_num, retention_num, swrf_version}
*** kewrwdbi_1: Error=13509 encountered during run_once
keaInitAdvCache: failed, err=604
05/30/09 10:40:13 >ERROR: exception at dbms_ha_alerts_prvt.post_instance_up308: SQLCODE -13917,ORA-13917: Posting system
alert with reason_id 135 failed with code [5] [post_error]
05/30/09 10:40:13 >ERROR: exception at dbms_ha_alerts_prvt.check_ha_resources637: SQLCODE -13917,ORA-13917: Posting syst
em alert with reason_id 136 failed with code [5] [post_error]
05/30/09 10:40:13 >parameter dump for dbms_ha_alerts_prvt.check_ha_resources
05/30/09 10:40:13 > - local_db_unique_name (rcat)
05/30/09 10:40:13 > - local_db_domain (==N/A==)
05/30/09 10:40:13 > - rows deleted (0)
05/30/09 10:40:13 >ERROR: exception at dbms_ha_alerts_prvt.check_ha_resources637: SQLCODE -13917,ORA-13917: Posting syst
em alert with reason_id 136 failed with code [5] [post_error]
05/30/09 10:40:13 >parameter dump for dbms_ha_alerts_prvt.check_ha_resources
05/30/09 10:40:13 > - local_db_unique_name (rcat)
05/30/09 10:40:13 > - local_db_domain (==N/A==)
05/30/09 10:40:13 > - rows deleted (0)
****KELR Apply Log Failed, return code 376
*** 2009-05-30 10:41:11.684
****KELR Apply Log Failed, return code 376
*** 2009-05-30 10:42:11.742
****KELR Apply Log Failed, return code 376
*** 2009-05-30 10:43:11.803
****KELR Apply Log Failed, return code 376
*** 2009-05-30 10:44:11.862
****KELR Apply Log Failed, return code 376
*** 2009-05-30 10:45:08.910
KEWRCTLRD: OCIStmtFetch Error. ctl_dbid= 435472619, sga_dbid= 435472619
KEWRCTLRD: Retcode: -1, Error Message: ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/home/oracle/app/oracle/oradata/rcat/sysaux01.dbf'
*** SQLSTR: total-len=328, dump-len=240,
STR={select snap_interval, retention,most_recent_snap_time, most_recent_snap_id, status_flag, most_recent_purge_time, most_recent_split_id, most_recent_split_time
, mrct_snap_time_num, mrct_purge_time_num, snapint_num, retention_num, swrf_version}
...
...
undo和sysaux表空间都需要恢复
SQL> set pages 50000 line 130
SQL> col name for a60
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ------------------------------------------------------------ -------
1 /home/oracle/app/oracle/oradata/rcat/system01.dbf SYSTEM
2 /home/oracle/app/oracle/oradata/rcat/undotbs01.dbf RECOVER
3 /home/oracle/app/oracle/oradata/rcat/sysaux01.dbf RECOVER
4 /home/oracle/app/oracle/oradata/rcat/users01.dbf ONLINE
5 /home/oracle/app/oracle/oradata/rcat/rman01.dbf ONLINE
SQL> select segment_name,tablespace_name,owner,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME OWNER STATUS
------------------------------ ------------------------------ ------ ----------------
SYSTEM SYSTEM SYS ONLINE
_SYSSMU1$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU2$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU3$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU4$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU5$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU6$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU7$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU8$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU9$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU10$ UNDOTBS1 PUBLIC NEEDS RECOVERY
11 rows selected.
SQL>
设置 _offline_rollback_segments 参数, 重启
alter system set "_offline_rollback_segments"='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$' scope=spfile; alter system set undo_management=manual scope=spfile; shutdown immediate startup
_offline_rollback_segments 和 _corrupted_rollback_segments 隐含参数的讲解见dsi401 Chapter 6 - Page 20
----begin----
When opening a database, any rollback segments listed in _offline or _corrupted parameters:
* Arenot scanned, and any active transactions are neither marked as dead nor rolled back
* Appear offline in dba_rollback_segs(undo$)
* Cannot be acuired by the instance for new transactions
...
If an open ITL is found to be associated with an _offline segment, the segment is read to find the transaction status
* If committed, the block is cleaned out
* If active and you want to read the block, a CR copy is constructed using undo from the segment
* If active and you want to lock the row, undesirable behavior may result
...
If an open ITL is found to be associated with a _corrupted segment, the segment is not read to find the transaction status
* It is as though the rollback segment had been dropped; the transaction is assumed to be committed and delayed block cleanout is performed
* If the transaction was not committed, logical corruption will occur
Most important, the Oracle server does not read the segment in this case. It is as if the segment has been dropped. This is the most important difference between _offline and _corrupted.
...
----end----
删除原来的撤销段, 恢复撤销表空间和系统辅助表空间
drop rollback segment "_SYSSMU1$"; drop rollback segment "_SYSSMU2$"; drop rollback segment "_SYSSMU3$"; drop rollback segment "_SYSSMU4$"; drop rollback segment "_SYSSMU5$"; drop rollback segment "_SYSSMU6$"; drop rollback segment "_SYSSMU7$"; drop rollback segment "_SYSSMU8$"; drop rollback segment "_SYSSMU9$"; drop rollback segment "_SYSSMU10$"; alter database recover automatic datafile 2,3; alter tablespace undotbs1 online; alter tablespace sysaux online;
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ------------------------------------------------------------ -------
1 /home/oracle/app/oracle/oradata/rcat/system01.dbf SYSTEM
2 /home/oracle/app/oracle/oradata/rcat/undotbs01.dbf RECOVER
3 /home/oracle/app/oracle/oradata/rcat/sysaux01.dbf RECOVER
4 /home/oracle/app/oracle/oradata/rcat/users01.dbf ONLINE
5 /home/oracle/app/oracle/oradata/rcat/rman01.dbf ONLINE
SQL> select segment_name,tablespace_name,owner,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME OWNER STATUS
------------------------------ ------------------------------ ------ ----------------
SYSTEM SYSTEM SYS ONLINE
_SYSSMU1$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU2$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU3$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU4$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU5$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU6$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU7$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU8$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU9$ UNDOTBS1 PUBLIC NEEDS RECOVERY
_SYSSMU10$ UNDOTBS1 PUBLIC NEEDS RECOVERY
11 rows selected.
SQL> drop rollback segment "_SYSSMU1$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU2$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU3$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU4$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU5$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU6$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU7$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU8$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU9$";
Rollback segment dropped.
SQL> drop rollback segment "_SYSSMU10$";
Rollback segment dropped.
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ------------------------------------------------------------ -------
1 /home/oracle/app/oracle/oradata/rcat/system01.dbf SYSTEM
2 /home/oracle/app/oracle/oradata/rcat/undotbs01.dbf RECOVER
3 /home/oracle/app/oracle/oradata/rcat/sysaux01.dbf RECOVER
4 /home/oracle/app/oracle/oradata/rcat/users01.dbf ONLINE
5 /home/oracle/app/oracle/oradata/rcat/rman01.dbf ONLINE
SQL> alter database recover automatic datafile 2,3;
Database altered.
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ------------------------------------------------------------ -------
1 /home/oracle/app/oracle/oradata/rcat/system01.dbf SYSTEM
2 /home/oracle/app/oracle/oradata/rcat/undotbs01.dbf OFFLINE
3 /home/oracle/app/oracle/oradata/rcat/sysaux01.dbf OFFLINE
4 /home/oracle/app/oracle/oradata/rcat/users01.dbf ONLINE
5 /home/oracle/app/oracle/oradata/rcat/rman01.dbf ONLINE
SQL> alter tablespace undotbs1 online;
Tablespace altered.
SQL> alter tablespace sysaux online;
Tablespace altered.
SQL>
日志:Sat May 30 13:39:26 2009 Completed: ALTER DATABASE OPEN Sat May 30 13:39:26 2009 db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. ORA-376 encountered when generating server alert SMG-3600 Sat May 30 13:40:35 2009 drop rollback segment "_SYSSMU1$" Sat May 30 13:40:35 2009 Completed: drop rollback segment "_SYSSMU1$" Sat May 30 13:41:16 2009 drop rollback segment "_SYSSMU2$" Sat May 30 13:41:16 2009 Completed: drop rollback segment "_SYSSMU2$" Sat May 30 13:41:18 2009 drop rollback segment "_SYSSMU3$" Completed: drop rollback segment "_SYSSMU3$" Sat May 30 13:41:21 2009 drop rollback segment "_SYSSMU4$" Completed: drop rollback segment "_SYSSMU4$" Sat May 30 13:41:25 2009 drop rollback segment "_SYSSMU5$" Completed: drop rollback segment "_SYSSMU5$" Sat May 30 13:41:28 2009 drop rollback segment "_SYSSMU6$" Sat May 30 13:41:28 2009 Completed: drop rollback segment "_SYSSMU6$" Sat May 30 13:41:30 2009 drop rollback segment "_SYSSMU7$" Completed: drop rollback segment "_SYSSMU7$" Sat May 30 13:41:36 2009 drop rollback segment "_SYSSMU8$" Completed: drop rollback segment "_SYSSMU8$" Sat May 30 13:41:38 2009 drop rollback segment "_SYSSMU9$" Sat May 30 13:41:38 2009 Completed: drop rollback segment "_SYSSMU9$" Sat May 30 13:41:40 2009 drop rollback segment "_SYSSMU10$" Completed: drop rollback segment "_SYSSMU10$" Sat May 30 13:43:12 2009 alter database recover automatic datafile 2,3 Sat May 30 13:43:12 2009 Media Recovery Start parallel recovery started with 3 processes Sat May 30 13:43:13 2009 Media Recovery Log /home/oracle/app/oracle/oradata/rcat/archive/1_7192_663322987.arc Sat May 30 13:43:13 2009 Media Recovery Complete (rcat) Completed: alter database recover automatic datafile 2,3 Sat May 30 13:44:04 2009 alter tablespace undotbs1 online Sat May 30 13:44:04 2009 Completed: alter tablespace undotbs1 online Sat May 30 13:44:07 2009 alter tablespace sysaux online Completed: alter tablespace sysaux online
恢复参数,重启
alter system reset "_offline_rollback_segments" scope=spfile sid='*'; alter system set undo_management=auto scope=spfile; shutdown immediate startup
至此, 恢复完成.
-fin-
No comments:
Post a Comment