12c db filesystem nobackup shutdown abort delete all redologs
1 描述
在已创建好的12c single database测试环境下,实验无备份打开数据库.本实验数据库在非归档状态,实验步骤:
- shutdown abort
- delete all redologs
2 操作环境
2.1 OS info
[oracle@localhost ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.2 (Maipo)
[oracle@localhost ~]$ uname -a
Linux localhost.localdomain 3.8.13-98.7.1.el7uek.x86_64 #2 SMP Wed Nov 25 13:51:41 PST 2015 x86_64 x86_64 x86_64 GNU/Linux
2.2 DB info
[oracle@localhost ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@localhost ~]$ sqlplus / as sysdba <<EOF
> set lines 150
> COL PRODUCT FORMAT A55
> COL VERSION FORMAT A15
> COL STATUS FORMAT A15
> Col parameter for a30
> SELECT * FROM PRODUCT_COMPONENT_VERSION;
> col value for a50
> set pages 50
> select * from nls_database_parameters;
>
> archive log list;
> EOF
3 过程设计
- shutdown abort
- delete all redologs
- recover database
- open
4 详细步骤操作
4.1 数据库状态
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/t12csi/redo03.log
/u01/app/oracle/oradata/t12csi/redo02.log
/u01/app/oracle/oradata/t12csi/redo01.log
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 18
Current log sequence 20
SQL>
4.2 强制关库并删除全部日志
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/t12csi/redo03.log
/u01/app/oracle/oradata/t12csi/redo02.log
/u01/app/oracle/oradata/t12csi/redo01.log
SQL> shutdown abort
ORACLE instance shut down.
SQL> !rm -rf /u01/app/oracle/oradata/t12csi/*.log
SQL>
4.3 数据库不一致并缺失全部redolog的情况下启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 1476395008 bytes
Fixed Size 2924544 bytes
Variable Size 486539264 bytes
Database Buffers 973078528 bytes
Redo Buffers 13852672 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/t12csi/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>
4.4 Redologs丢失,recover until cancel数据库
SQL> recover database until cancel;
ORA-00279: change 2079869 generated at 12/01/2016 12:28:30 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/T12CSI/archivelog/2016_12_01/o1_mf_1_25_%u_.a
rc
ORA-00280: change 2079869 for thread 1 is in sequence #25
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/t12csi/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/t12csi/system01.dbf'
SQL> !ls /u01/app/oracle/oradata/t12csi/
/u01/app/oracle/oradata/t12csi/:
control01.ctl example01.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
You have new mail in /var/spool/mail/oracle
[oracle@localhost ~]$
alter database open resetlogs并未执行成功,因此redo file并未在操作系统上创建成功.
alert日志内容如下:
Thu Dec 01 12:53:06 2016
Parallel Media Recovery started with 2 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER database until cancel ...
ALTER DATABASE RECOVER CANCEL
Thu Dec 01 12:53:11 2016
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_pr00_18261.trc:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/t12csi/system01.dbf'
ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ...
ALTER DATABASE RECOVER CANCEL
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...
Thu Dec 01 12:53:11 2016
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_m000_18267.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/t12csi/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Thu Dec 01 12:53:11 2016
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_m000_18267.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/t12csi/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Thu Dec 01 12:53:11 2016
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_m000_18267.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/t12csi/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Checker run found 5 new persistent data failures
alter database open resetlogs
ORA-1194 signalled during: alter database open resetlogs...
因数据文件不一致,因此open resetlogs不成功. 尝试使用_allow_resetlogs_corruption=TRUE参数
4.5 添加_allow_resetlogs_corruption=TRUE到spfile或pfile
SQL> select status from v$instance;
STATUS
------------------------------------
MOUNTED
SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"=true SCOPE=SPFILE;
System altered.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1476395008 bytes
Fixed Size 2924544 bytes
Variable Size 486539264 bytes
Database Buffers 973078528 bytes
Redo Buffers 13852672 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [2079876], [0],
[2079908], [4194432], [], [], [], [], [], []
Process ID: 18440
Session ID: 237 Serial number: 6
SQL> !ls /u01/app/oracle/oradata/t12csi/
control01.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
SQL>
添加_allow_resetlogs_corruption参数后resetlogs成功,操作系统可查到新创建的redolog文件.
4.6 查询scn
4.6.1 查询数据库当前scn
SQL> select status from v$instance;
STATUS
------------------------------------
MOUNTED
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
0
SQL>
4.6.2 数据库控制文件中全局检查点 SCN
SQL> select CHECKPOINT_CHANGE# from v$database;
CHECKPOINT_CHANGE#
------------------
2079873
SQL>
4.6.3 当前控制文件中数据文件SCN checkpoint scn,表示该数据文件最近一次执行检查点操作时的SCN
SQL> select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/t12csi/system01.dbf 2079873
/u01/app/oracle/oradata/t12csi/sysaux01.dbf 2079873
/u01/app/oracle/oradata/t12csi/undotbs01.dbf 2079873
/u01/app/oracle/oradata/t12csi/example01.dbf 2079873
/u01/app/oracle/oradata/t12csi/users01.dbf 2079873
SQL>
4.6.4 数据文件头SCN，在数据文件头
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/t12csi/system01.dbf 2079873
/u01/app/oracle/oradata/t12csi/sysaux01.dbf 2079873
/u01/app/oracle/oradata/t12csi/undotbs01.dbf 2079873
/u01/app/oracle/oradata/t12csi/example01.dbf 2079873
/u01/app/oracle/oradata/t12csi/users01.dbf 2079873
SQL>
4.6.5 控制文件中数据文件结束SCN
LAST_CHANGE#，如果数据库非正常关闭值为NULL。正常关闭是关闭时的SCN。
实例恢复就是在打开数据库时检查此参数确定是否需要恢复。
数据库OPEN时LAST_CHANGE#也为NULL，因为不确定SCN多少时关闭。
SQL> select name,last_change# from v$datafile;
NAME LAST_CHANGE#
-------------------------------------------------- ------------
/u01/app/oracle/oradata/t12csi/system01.dbf
/u01/app/oracle/oradata/t12csi/sysaux01.dbf
/u01/app/oracle/oradata/t12csi/undotbs01.dbf
/u01/app/oracle/oradata/t12csi/example01.dbf
/u01/app/oracle/oradata/t12csi/users01.dbf
SQL>
4.6.6 redo日志中所含SCN范围
SQL> select GROUP#,sequence#,STATUS,FIRST_CHANGE#,first_time from V$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ------------------------------------------------ ------------- -----------------------------
1 1 CURRENT 2079870 01-DEC-2016 13:09:45
3 0 UNUSED 0
2 0 UNUSED 0
SQL>
因redolog与数据文件scn不一致导致报ora-600 [2662]错误,无法open数据库.
4.6.7 alertlog内容
Thu Dec 01 13:09:18 2016
ALTER SYSTEM SET _allow_resetlogs_corruption=TRUE SCOPE=SPFILE;
Shutting down instance (abort)
License high water mark = 4
Thu Dec 01 13:09:22 2016
USER (ospid: 18378): terminating the instance
Thu Dec 01 13:09:23 2016
Instance terminated by USER, pid = 18378
Thu Dec 01 13:09:23 2016
Instance shutdown complete
Thu Dec 01 13:09:29 2016
Starting ORACLE instance (normal) (OS id: 18381)
Thu Dec 01 13:09:29 2016
CLI notifier numLatches:3 maxDescs:519
Thu Dec 01 13:09:29 2016
**********************************************************************
Thu Dec 01 13:09:29 2016
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
Thu Dec 01 13:09:29 2016
Per process system memlock (soft) limit = 64K
Thu Dec 01 13:09:29 2016
Expected per process system memlock (soft) limit to lock
SHARED GLOBAL AREA (SGA) into memory: 1410M
Thu Dec 01 13:09:29 2016
Available system pagesizes:
4K, 2048K
Thu Dec 01 13:09:29 2016
Supported system pagesize(s):
Thu Dec 01 13:09:29 2016
PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)
Thu Dec 01 13:09:29 2016
4K Configured 2 360450 NONE
Thu Dec 01 13:09:29 2016
2048K 0 705 0 NONE
Thu Dec 01 13:09:29 2016
RECOMMENDATION:
Thu Dec 01 13:09:29 2016
1. For optimal performance, configure system with expected number
of pages for every supported system pagesize prior to the next
instance restart operation.
Thu Dec 01 13:09:29 2016
2. Increase per process memlock (soft) limit to at least 1410MB
to lock 100% of SHARED GLOBAL AREA (SGA) pages into physical memory
Thu Dec 01 13:09:29 2016
**********************************************************************
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 2
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =51
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Using default ASM root directory ASM
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/12.1.0.2.0
System name: Linux
Node name: localhost.localdomain
Release: 3.8.13-98.7.1.el7uek.x86_64
Version: #2 SMP Wed Nov 25 13:51:41 PST 2015
Machine: x86_64
Using parameter settings in server-side spfile /u01/app/oracle/product/12.1.0.2.0/dbs/spfilet12csi.ora
System parameters with non-default values:
processes = 300
sga_target = 1408M
control_files = "/u01/app/oracle/oradata/t12csi/control01.ctl"
control_files = "/u01/app/oracle/fast_recovery_area/t12csi/control02.ctl"
db_block_size = 8192
compatible = "12.1.0.2.0"
db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size= 4560M
_allow_resetlogs_corruption= TRUE
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = "us.oracle.com"
dispatchers = "(PROTOCOL=TCP) (SERVICE=t12csiXDB)"
audit_file_dest = "/u01/app/oracle/admin/t12csi/adump"
audit_trail = "DB"
db_name = "t12csi"
open_cursors = 300
pga_aggregate_target = 468M
diagnostic_dest = "/u01/app/oracle"
NOTE: remote asm mode is local (mode 0x1; from cluster type)
Starting background process PMON
Thu Dec 01 13:09:29 2016
PMON started with pid=2, OS id=18384
Starting background process PSP0
Thu Dec 01 13:09:29 2016
PSP0 started with pid=3, OS id=18386
Starting background process VKTM
Thu Dec 01 13:09:30 2016
VKTM started with pid=4, OS id=18388 at elevated (RT) priority
Starting background process GEN0
Thu Dec 01 13:09:30 2016
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Thu Dec 01 13:09:30 2016
GEN0 started with pid=5, OS id=18392
Starting background process MMAN
Thu Dec 01 13:09:30 2016
MMAN started with pid=6, OS id=18394
Starting background process DIAG
Thu Dec 01 13:09:30 2016
DIAG started with pid=8, OS id=18398
Starting background process DBRM
Thu Dec 01 13:09:30 2016
DBRM started with pid=9, OS id=18400
Starting background process VKRM
Thu Dec 01 13:09:30 2016
VKRM started with pid=10, OS id=18402
Starting background process DIA0
Thu Dec 01 13:09:30 2016
DIA0 started with pid=11, OS id=18404
Starting background process DBW0
Thu Dec 01 13:09:30 2016
DBW0 started with pid=12, OS id=18406
Starting background process LGWR
Thu Dec 01 13:09:30 2016
LGWR started with pid=13, OS id=18408
Starting background process CKPT
Thu Dec 01 13:09:30 2016
CKPT started with pid=14, OS id=18410
Starting background process SMON
Thu Dec 01 13:09:30 2016
SMON started with pid=16, OS id=18414
Starting background process RECO
Thu Dec 01 13:09:30 2016
RECO started with pid=18, OS id=18418
Starting background process LREG
Thu Dec 01 13:09:30 2016
LREG started with pid=19, OS id=18420
Starting background process PXMN
Thu Dec 01 13:09:30 2016
PXMN started with pid=20, OS id=18422
Starting background process MMON
Thu Dec 01 13:09:30 2016
MMON started with pid=21, OS id=18424
Starting background process MMNL
Thu Dec 01 13:09:30 2016
MMNL started with pid=22, OS id=18426
Thu Dec 01 13:09:30 2016
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
Thu Dec 01 13:09:30 2016
ALTER DATABASE MOUNT
Thu Dec 01 13:09:33 2016
Using default pga_aggregate_limit of 2048 MB
Thu Dec 01 13:09:34 2016
Successful mount of redo thread 1, with mount id 133987787
Thu Dec 01 13:09:34 2016
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Thu Dec 01 13:09:35 2016
ALTER DATABASE OPEN
Thu Dec 01 13:09:35 2016
Ping without log force is disabled
.
Thu Dec 01 13:09:35 2016
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_ora_18440.trc:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
ORA-1589 signalled during: ALTER DATABASE OPEN...
alter database open resetlogs
Thu Dec 01 13:09:45 2016
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 2079869 time
Resetting resetlogs activation ID 133433808 (0x7f409d0)
Thu Dec 01 13:09:45 2016
Setting recovery target incarnation to 3
Thu Dec 01 13:09:45 2016
Ping without log force is disabled
.
Starting background process TMON
Thu Dec 01 13:09:45 2016
Assigning activation ID 133987787 (0x7fc7dcb)
Thu Dec 01 13:09:45 2016
TMON started with pid=25, OS id=18442
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/t12csi/redo01.log
Successful open of redo thread 1
Thu Dec 01 13:09:47 2016
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Dec 01 13:09:47 2016
SMON: enabling cache recovery
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_ora_18440.trc (incident=19257):
ORA-00600: internal error code, arguments: [2662], [0], [2079876], [0], [2079908], [4194432], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/t12csi/t12csi/incident/incdir_19257/t12csi_ora_18440_i19257.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Dec 01 13:09:52 2016
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_ora_18440.trc:
ORA-00600: internal error code, arguments: [2662], [0], [2079876], [0], [2079908], [4194432], [], [], [], [], [], []
Thu Dec 01 13:09:52 2016
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_ora_18440.trc:
ORA-00600: internal error code, arguments: [2662], [0], [2079876], [0], [2079908], [4194432], [], [], [], [], [], []
Thu Dec 01 13:09:52 2016
Error 600 happened during db open, shutting down database
USER (ospid: 18440): terminating the instance due to error 600
Thu Dec 01 13:09:53 2016
Instance terminated by USER, pid = 18440
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (18440) as a result of ORA-1092
Thu Dec 01 13:09:53 2016
ORA-1092 : opitsk aborting process
4.7 ora-600 [2662]
|
|
|
4.7.1 检查[argument d]-[argument b].
ORA-00600: internal error code, arguments: [2662], [0], [2079876], [0], [2079908], [4194432], [], [], [], [], [], []
由于[argument d]-[argument b]相当接近,尝试重启几次数据库推进scn.
4.7.2 尝试open数据库
SQL> select status from v$instance;
STATUS
------------------------------------
MOUNTED
SQL> alter database open;
Database altered.
SQL>
关闭一次就成功open了数据库.
4.7.3 alertlog内容
Thu Dec 01 14:02:10 2016
alter database open
Thu Dec 01 14:02:10 2016
Ping without log force is disabled
.
Thu Dec 01 14:02:10 2016
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Thu Dec 01 14:02:10 2016
Started redo scan
Thu Dec 01 14:02:11 2016
Completed redo scan
read 0 KB redo, 0 data blocks need recovery
Thu Dec 01 14:02:11 2016
Started redo application at
Thread 1: logseq 1, block 3, scn 2079875
Thu Dec 01 14:02:11 2016
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/t12csi/redo01.log
Thu Dec 01 14:02:11 2016
Completed redo application of 0.00MB
Thu Dec 01 14:02:11 2016
Completed crash recovery at
Thread 1: logseq 1, block 3, scn 2079876
0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Thu Dec 01 14:02:11 2016
Starting background process TMON
Thu Dec 01 14:02:11 2016
TMON started with pid=27, OS id=18671
Thu Dec 01 14:02:11 2016
Thread 1 advanced to log sequence 2 (thread open)
Thread 1 opened at log sequence 2
Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/t12csi/redo02.log
Successful open of redo thread 1
Thu Dec 01 14:02:12 2016
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Dec 01 14:02:12 2016
SMON: enabling cache recovery
Thu Dec 01 14:02:12 2016
[18665] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:161722554 end:161722724 diff:170 ms (0.2 seconds)
Dictionary check beginning
Dictionary check complete
Verifying minimum file header compatibility (11g) for tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Thu Dec 01 14:02:12 2016
SMON: enabling tx recovery
Starting background process SMCO
Thu Dec 01 14:02:12 2016
Database Characterset is WE8MSWIN1252
Thu Dec 01 14:02:13 2016
SMCO started with pid=29, OS id=18675
No Resource Manager plan active
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_smon_18533.trc (incident=21729):
ORA-00600: internal error code, arguments: [4194], [63], [6], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/t12csi/t12csi/incident/incdir_21729/t12csi_smon_18533_i21729.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process AQPC
Thu Dec 01 14:02:15 2016
AQPC started with pid=32, OS id=18681
Thu Dec 01 14:02:15 2016
Doing block recovery for file 4 block 2442
Resuming block recovery (PMON) for file 4 block 2442
Block recovery from logseq 2, block 69 to scn 2179974
Thu Dec 01 14:02:15 2016
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/t12csi/redo02.log
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Thu Dec 01 14:02:16 2016
Dumping diagnostic data in directory=[cdmp_20161201140216], requested by (instance=1, osid=18533 (SMON)), summary=[incident=21729].
Thu Dec 01 14:02:17 2016
Block recovery stopped at EOT rba 2.94.16
Block recovery completed at rba 2.94.16, scn 0.2179972
Doing block recovery for file 4 block 208
Resuming block recovery (PMON) for file 4 block 208
Block recovery from logseq 2, block 69 to scn 2179945
Thu Dec 01 14:02:17 2016
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/t12csi/redo02.log
Block recovery completed at rba 2.72.16, scn 0.2179947
Thu Dec 01 14:02:17 2016
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_smon_18533.trc:
ORA-01595: error freeing extent (3) of rollback segment (6))
ORA-00600: internal error code, arguments: [4194], [63], [6], [], [], [], [], [], [], [], [], []
Starting background process CJQ0
Thu Dec 01 14:02:18 2016
Sweep [inc][21729]: completed
Thu Dec 01 14:02:18 2016
CJQ0 started with pid=34, OS id=18685
Completed: alter database open
Sweep [inc2][21729]: completed
Thu Dec 01 14:02:19 2016
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_mmon_18543.trc (incident=21769):
ORA-00600: internal error code, arguments: [4194], [63], [6], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/t12csi/t12csi/incident/incdir_21769/t12csi_mmon_18543_i21769.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Dec 01 14:02:26 2016
Dumping diagnostic data in directory=[cdmp_20161201140226], requested by (instance=1, osid=18543 (MMON)), summary=[incident=21769].
Thu Dec 01 14:02:32 2016
Flush retried for xcb 0xb35301a0, pmd 0xb3de1e50
Doing block recovery for file 4 block 2442
Resuming block recovery (PMON) for file 4 block 2442
Block recovery from logseq 2, block 69 to scn 2179974
Thu Dec 01 14:02:32 2016
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/t12csi/redo02.log
Block recovery completed at rba 2.94.16, scn 0.2179975
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_mmon_18543.trc (incident=21770):
ORA-00600: internal error code, arguments: [4194], [63], [6], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [63], [6], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/t12csi/t12csi/incident/incdir_21770/t12csi_mmon_18543_i21770.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Dec 01 14:02:36 2016
Dumping diagnostic data in directory=[cdmp_20161201140236], requested by (instance=1, osid=18543 (MMON)), summary=[incident=21770].
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_mmon_18543.trc (incident=21771):
ORA-00603: ORACLE server session terminated by fatal error
ORA-24557: error 600 encountered while handling error 600; exiting server process
ORA-00600: internal error code, arguments: [4194], [63], [6], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [63], [6], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/t12csi/t12csi/incident/incdir_21771/t12csi_mmon_18543_i21771.trc
Thu Dec 01 14:02:40 2016
Dumping diagnostic data in directory=[cdmp_20161201140240], requested by (instance=1, osid=18543 (MMON)), summary=[incident=21771].
Thu Dec 01 14:02:42 2016
opidrv aborting process MMON ospid (18543) as a result of ORA-603
Thu Dec 01 14:02:45 2016
Flush retried for xcb 0xb35301a0, pmd 0xb3de1e50
Doing block recovery for file 4 block 2442
Resuming block recovery (PMON) for file 4 block 2442
Block recovery from logseq 2, block 69 to scn 2179974
Thu Dec 01 14:02:45 2016
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/t12csi/redo02.log
Block recovery completed at rba 2.94.16, scn 0.2179975
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_pmon_18503.trc (incident=21617):
ORA-00600: internal error code, arguments: [4194], [63], [6], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/t12csi/t12csi/incident/incdir_21617/t12csi_pmon_18503_i21617.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Dec 01 14:02:46 2016
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_pmon_18503.trc:
ORA-00600: internal error code, arguments: [4194], [63], [6], [], [], [], [], [], [], [], [], []
USER (ospid: 18503): terminating the instance due to error 472
Thu Dec 01 14:02:46 2016
System state dump requested by (instance=1, osid=18503 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_diag_18517_20161201140246.trc
Thu Dec 01 14:02:46 2016
Dumping diagnostic data in directory=[cdmp_20161201140246], requested by (instance=1, osid=18503 (PMON)), summary=[abnormal instance termination].
Thu Dec 01 14:02:46 2016
Instance terminated by USER, pid = 18503
数据库成功open后,alertlog中出现其他600错误.
4.8 ora-600 [4194]
|
|
|
ORA-600 [4194] "Undo Record Number Mismatch While Adding Undo Record" (Doc ID 39283.1) |
|
|
PURPOSE: This article discusses the internal error "ORA-600 [4194]", what it means and possible actions. The information here is only applicable to the versions listed and is provided only for guidance.
ERROR:
Format: ORA-600 [4194] [a] [b]
VERSIONS: versions 6.0 to 12.1
DESCRIPTION:
A mismatch has been detected between Redo records and rollback (Undo) records.
We are validating the Undo record number relating to the change being applied against the maximum undo record number recorded in the undo block.
This error is reported when the validation fails.
ARGUMENTS: Arg [a] Maximum Undo record number in Undo block Arg [b] Undo record number from Redo block
FUNCTIONALITY: Kernel Transaction Undo called from Cache layer
IMPACT: PROCESS FAILURE POSSIBLE ROLLBACK SEGMENT CORRUPTION
SUGGESTIONS:
This error may indicate a rollback segment corruption.
This may require a recovery from a database backup depending on the situation.
If the Known Issues section below does not help in terms of identifying a solution, please submit the trace files and alert.log to Oracle Support Services for further analysis. |
4.8.1 更换默认undo tbs
因数据库无备份,无法按提示中的操作做恢复操作. 修改默认undo tbs, 然后删除原有undo tbs.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1476395008 bytes
Fixed Size 2924544 bytes
Variable Size 486539264 bytes
Database Buffers 973078528 bytes
Redo Buffers 13852672 bytes
Database mounted.
SQL> select name from v$datafile;
NAME
---------------------------------------------------------------------
/u01/app/oracle/oradata/t12csi/system01.dbf
/u01/app/oracle/oradata/t12csi/sysaux01.dbf
/u01/app/oracle/oradata/t12csi/undotbs01.dbf
/u01/app/oracle/oradata/t12csi/example01.dbf
/u01/app/oracle/oradata/t12csi/users01.dbf
SQL> alter database open;
Database altered.
SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/t12csi/undotbs02.dbf' size 10M reuse AUTOEXTEND ON;
create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/t12csi/undotbs02.dbf' size 10M reuse AUTOEXTEND ON
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [4194], [63], [6], [], [], [], [], [], [], [], [], []
SQL> select status from v$instance;
STATUS
------------------------------------
OPEN
SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/t12csi/undotbs02.dbf' size 10M reuse AUTOEXTEND ON;
Tablespace created.
SQL> alter system set undo_tablespace=undotbs2;
System altered.
SQL> alter tablespace undotbs1 offline;
Tablespace altered.
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SQL>
4.8.2 alertlog内容
Thu Dec 01 14:56:35 2016
CJQ0 started with pid=33, OS id=19028
Completed: alter database open
Thu Dec 01 14:56:36 2016
Dumping diagnostic data in directory=[cdmp_20161201145636], requested by (instance=1, osid=18984 (SMON)), summary=[incident=26531].
create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/t12csi/undotbs02.dbf' size 10M reuse AUTOEXTEND ON
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_ora_19010.trc (incident=26459):
ORA-00600: internal error code, arguments: [4194], [63], [6], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/t12csi/t12csi/incident/incdir_26459/t12csi_ora_19010_i26459.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Dec 01 14:56:42 2016
Doing block recovery for file 4 block 2442
Resuming block recovery (PMON) for file 4 block 2442
Block recovery from logseq 4, block 65 to scn 2380336
Thu Dec 01 14:56:42 2016
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/t12csi/redo01.log
Block recovery completed at rba 4.98.16, scn 0.2380337
Doing block recovery for file 4 block 208
Resuming block recovery (PMON) for file 4 block 208
Block recovery from logseq 4, block 65 to scn 2380367
Thu Dec 01 14:56:42 2016
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/t12csi/redo01.log
Block recovery completed at rba 4.134.16, scn 0.2380369
ORA-600 signalled during: create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/t12csi/undotbs02.dbf' size 10M reuse AUTOEXTEND ON...
Thu Dec 01 14:56:42 2016
Dumping diagnostic data in directory=[cdmp_20161201145642], requested by (instance=1, osid=19010), summary=[incident=26459].
Thu Dec 01 14:56:47 2016
Shared IO Pool defaulting to 64MB. Trying to get it from Buffer Cache for process 18994.
===========================================================
Dumping current patch information
===========================================================
No patches have been applied
===========================================================
Thu Dec 01 14:56:47 2016
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_m002_19164.trc (incident=26771):
ORA-00600: internal error code, arguments: [4194], [63], [6], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/t12csi/t12csi/incident/incdir_26771/t12csi_m002_19164_i26771.trc
Thu Dec 01 14:56:47 2016
db_recovery_file_dest_size of 4560 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.
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Dec 01 14:56:49 2016
Sweep [inc][26771]: completed
Sweep [inc][26531]: completed
Sweep [inc][26459]: completed
Sweep [inc2][26531]: completed
Sweep [inc2][26459]: completed
Thu Dec 01 14:56:50 2016
Dumping diagnostic data in directory=[cdmp_20161201145650], requested by (instance=1, osid=19164 (M002)), summary=[incident=26771].
Thu Dec 01 14:56:50 2016
Doing block recovery for file 4 block 2442
Resuming block recovery (PMON) for file 4 block 2442
Block recovery from logseq 4, block 65 to scn 2380336
Thu Dec 01 14:56:50 2016
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/t12csi/redo01.log
Block recovery completed at rba 4.98.16, scn 0.2380337
Doing block recovery for file 4 block 208
Resuming block recovery (PMON) for file 4 block 208
Block recovery from logseq 4, block 65 to scn 2380397
Thu Dec 01 14:56:50 2016
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/t12csi/redo01.log
Block recovery completed at rba 4.706.16, scn 0.2380407
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_m002_19164.trc (incident=26772):
ORA-00600: internal error code, arguments: [4194], [63], [6], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/t12csi/t12csi/incident/incdir_26772/t12csi_m002_19164_i26772.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Dec 01 14:56:51 2016
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_m002_19164.trc:
ORA-00600: internal error code, arguments: [4194], [63], [6], [], [], [], [], [], [], [], [], []
Thu Dec 01 14:56:51 2016
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_m000_19186.trc (incident=26787):
ORA-00600: internal error code, arguments: [4194], [63], [6], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/t12csi/t12csi/incident/incdir_26787/t12csi_m000_19186_i26787.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Dec 01 14:56:51 2016
Dumping diagnostic data in directory=[cdmp_20161201145651], requested by (instance=1, osid=19164 (M002)), summary=[incident=26772].
Thu Dec 01 14:56:52 2016
Dumping diagnostic data in directory=[cdmp_20161201145652], requested by (instance=1, osid=19186 (M000)), summary=[incident=26787].
Thu Dec 01 14:56:52 2016
Doing block recovery for file 4 block 2442
Resuming block recovery (PMON) for file 4 block 2442
Block recovery from logseq 4, block 65 to scn 2380336
Thu Dec 01 14:56:52 2016
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/t12csi/redo01.log
Block recovery completed at rba 4.98.16, scn 0.2380337
Doing block recovery for file 4 block 208
Resuming block recovery (PMON) for file 4 block 208
Block recovery from logseq 4, block 65 to scn 2380978
Thu Dec 01 14:56:52 2016
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/t12csi/redo01.log
Block recovery completed at rba 4.1217.16, scn 0.2380979
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_m000_19186.trc (incident=26788):
ORA-00600: internal error code, arguments: [4194], [63], [6], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/t12csi/t12csi/incident/incdir_26788/t12csi_m000_19186_i26788.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Dec 01 14:56:53 2016
Errors in file /u01/app/oracle/diag/rdbms/t12csi/t12csi/trace/t12csi_m000_19186.trc:
ORA-00600: internal error code, arguments: [4194], [63], [6], [], [], [], [], [], [], [], [], []
create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/t12csi/undotbs02.dbf' size 10M reuse AUTOEXTEND ON
Completed: create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/t12csi/undotbs02.dbf' size 10M reuse AUTOEXTEND ON
Thu Dec 01 14:57:21 2016
[19010] Successfully onlined Undo Tablespace 5.
[19010] Undo Tablespace 2 successfully switched out.
Thu Dec 01 14:57:21 2016
ALTER SYSTEM SET undo_tablespace='UNDOTBS2' SCOPE=BOTH;
Thu Dec 01 14:57:32 2016
TABLE SYS.WRP$_REPORTS: ADDED INTERVAL PARTITION SYS_P327 (2527) VALUES LESS THAN (TO_DATE(' 2016-12-02 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE SYS.WRP$_REPORTS_DETAILS: ADDED INTERVAL PARTITION SYS_P328 (2527) VALUES LESS THAN (TO_DATE(' 2016-12-02 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE SYS.WRP$_REPORTS_TIME_BANDS: ADDED INTERVAL PARTITION SYS_P331 (2526) VALUES LESS THAN (TO_DATE(' 2016-12-01 01:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
alter tablespace undotbs1 offline
Completed: alter tablespace undotbs1 offline
drop tablespace undotbs1 including contents and datafiles
Thu Dec 01 14:57:49 2016
Deleted file /u01/app/oracle/oradata/t12csi/undotbs01.dbf
Completed: drop tablespace undotbs1 including contents and datafiles
Thu Dec 01 14:57:49 2016
Sweep [inc][26788]: completed
Sweep [inc][26787]: completed
Sweep [inc][26772]: completed
Sweep [inc2][26788]: completed
Sweep [inc2][26787]: completed
Sweep [inc2][26772]: completed
Sweep [inc2][26771]: completed
除第一次创建undotbs2的时候报错外,其他时间恢复正常.
4.9 恢复完成reset_allow_resetlogs_corruption
SQL> set lines 200
SQL> show parameter _allow_reset
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
_allow_resetlogs_corruption boolean TRUE
SQL> ALTER SYSTEM RESET "_allow_resetlogs_corruption" SCOPE = SPFILE SID = '*';
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1476395008 bytes
Fixed Size 2924544 bytes
Variable Size 486539264 bytes
Database Buffers 973078528 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
SQL> show parameter _allow_rese
SQL>
5 个人总结
本篇到些暂为结束,由于是个人实验环境,因此恢复过程很简单.尝试另建复杂实验环境,模仿生产环境存储损坏的无备份开库.
6 资料参考引用
oracle中的各种scn查询 http://www.2cto.com/database/201309/243476.html
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/11780477/viewspace-2129492/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- Deepin 20.7 安装 xrdp
- python中使用oracledb模块连接oracle数据库
- OEL 8.6 卸载 21cRAC
- 修改单机oracle ASM环境主机hostname
- mysql部分实现类似oracle sequence功能的测试
- 查看oracle数据库session事务设置的是哪个隔离级别
- Outlook for Microsoft 365 所有命令行参数
- mysql 5.6.46 安装到winserver2012(x64) 遇到 MSVCR100.dll is missing
- 18000 single db installation on CENTOS 6 x64
- 12102 Using the DUPLICATE Command to Create a Standby Database
