分类目录归档：Oracle

有一个朋友自己想测试只用system文件open库,闲着没事给他测试了下,顺利open成功（主要还是经验比较多,规避了很多坑）
1. 准备参数文件

*.audit_file_dest='C:\app\XFF\admin\ORCL\adump'
*.audit_trail='none'
*.compatible='11.2.0.3.0'
*.control_files='H:\TEMP\11203\control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DBM'
*.diagnostic_dest='C:\app\XFF'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=2147483648
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=170
*.sga_target=6442450944
*.undo_tablespace='UNDOTBS1'
undo_management=MANUAL
_corrupted_rollback_segments=
_allow_resetlogs_corruption=true

2. 准备重建ctl语句

CREATE CONTROLFILE REUSE DATABASE "DBM" RESETLOGS  NOARCHIVELOG  
    MAXLOGFILES 50  
    MAXLOGMEMBERS 5  
    MAXDATAFILES 100  
    MAXINSTANCES 8  
    MAXLOGHISTORY 226  
LOGFILE  
  GROUP 1 'H:\TEMP\11203\redo01.log'  SIZE 50M,  
  GROUP 2 'H:\TEMP\11203\redo02.log'  SIZE 50M,  
  GROUP 3 'H:\TEMP\11203\redo03.log'  SIZE 50M  
DATAFILE  
'H:\TEMP\11203\system01.dbf'
CHARACTER SET ZHS16GBK  
;  

3. 重建ctl并且resetogs open库

SQL> recover database using backup controlfile until cancel;
ORA-00279: 更改 40438873410 (在 10/21/2022 14:06:16 生成) 对于线程 1 是必需的
ORA-00289: 建议:
C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\RDBMS\ARC0000000093_1118545292.0001
ORA-00280: 更改 40438873410 (用于线程 1) 在序列 #93 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件 1 需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: 'H:\TEMP\11203\SYSTEM01.DBF'


ORA-01112: 未启动介质恢复


SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01176: data dictionary has more than the 100 files allowed by the
controlfie
进程 ID: 3952
会话 ID: 14 序列号: 3

MAXDATAFILES值不对修改正确值，重建ctl，open库

SQL> RECOVER DATABASE;
完成介质恢复。
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number  with name "" too small
进程 ID: 6916
会话 ID: 14 序列号: 1

alert日志内容

Database Characterset is ZHS16GBK
Errors in file C:\APP\XFF\diag\rdbms\dbm\test\trace\test_smon_9384.trc:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01555: 快照过旧: 回退段号  (名称为 "") 过小
Errors in file C:\APP\XFF\diag\rdbms\dbm\test\trace\test_ora_6916.trc:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01555: 快照过旧: 回退段号  (名称为 "") 过小
Errors in file C:\APP\XFF\diag\rdbms\dbm\test\trace\test_ora_6916.trc:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01555: 快照过旧: 回退段号  (名称为 "") 过小
Error 604 happened during db open, shutting down database
USER (ospid: 6916): terminating the instance due to error 604
Errors in file C:\APP\XFF\diag\rdbms\dbm\test\trace\test_smon_9384.trc  (incident=2521):
ORA-00600: 内部错误代码, 参数: [2662], [9], [1784188335], [9], [1784216952], [6019273], [], [], [], [], [], []
Incident details in: C:\APP\XFF\diag\rdbms\dbm\test\incident\incdir_2521\test_smon_9384_i2521.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Non-fatal internal error happenned while SMON was doing temporary segment drop.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
Tue Nov 01 10:17:49 2022
Instance terminated by USER, pid = 6916
ORA-1092 signalled during: ALTER DATABASE OPEN...

修改文件头scn，并正常open库

SQL> startup nomount pfile='d:/pfile.txt'
ORACLE 例程已经启动。

Total System Global Area 6413680640 bytes
Fixed Size                  2267184 bytes
Variable Size            1107298256 bytes
Database Buffers         5284823040 bytes
Redo Buffers               19292160 bytes
SQL> alter database mount;

数据库已更改。

SQL> set numw 16
SQL> col CHECKPOINT_TIME for a40
SQL> set lines 150
SQL> set pages 1000
SQL> SELECT status,
  2  to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,FUZZY,checkpoint_change#,
  3  count(*) ROW_NUM
  4  FROM v$datafile_header
  5  GROUP BY status, checkpoint_change#, to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss'),fuzzy
  6  ORDER BY status, checkpoint_change#, checkpoint_time;

STATUS  CHECKPOINT_TIME                          FUZ CHECKPOINT_CHANGE#          ROW_NUM
------- ---------------------------------------- --- ------------------ ----------------
OFFLINE                                                               0              121
ONLINE  2022-11-01 10:17:44                      YES        40438893615                1

SQL> alter database open;

数据库已更改。

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------
H:\TEMP\11203\SYSTEM01.DBF
C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\MISSING00002
C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\MISSING00003
C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\MISSING00004
C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\MISSING00005
………………
C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\MISSING00121
C:\APP\XFF\PRODUCT\11.2.0.3\DBHOME_1\DATABASE\MISSING00122

已选择122行。

恢复完成