windows 无法启动 redis 1053 Windows 无法启动Diagnostic

转载

mob64ca13f40f3d 2024-08-27 21:07:59

文章标签 oracle windows database delete file 文章分类 Redis 数据库

大家好,好久没有写oracle的案例了,非常抱歉.

　　　　由于最近的工作上的波动,一直无法专心下来,研究oracle.

　　　　今天写一个正在遇到的问题。

　　　　oracle 11g for windows 启动失败。

启动监听 lsnrctl start

启动服务在 widows 服务里面启动。

进入字符界面：

sqlplus / as sysdba

startup nomount;

alter database mount;

alter database open;

报如下错误：

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 252
Session ID: 1 Serial number: 3

我也从来没有遇到这个错误，只好上google 搜索，看了一堆资料。

其中有一篇文章

http://www.dbanotes.net/Oracle/ORA-03113.htm

提醒了我，其实遇到数据库出错的时候，最先需要做的事就是查看

alter_orcl.log 这个文件。这个不是技术，而是方法论。看来以后要记好了。

然后我去找 app/admin/orcl/bdump 目录下的alter 告警文件。结果连目录都无法找到。

其实这个时候我可以上google去搜索。但是我没有。

我知道 oracle 会告诉我，如何找到alter告警文件在哪里。

再来：

c:/> sqlplus / nolog

sql> conn / as sysdba

　　　　Connected to an idle instance.

sql> startup nomount

ORACLE instance started.

Total System Global Area 426852352 bytes
Fixed Size                  　　1375060 bytes
Variable Size                    301991084 bytes
Database Buffers             117440512 bytes
Redo Buffers                    6045696 bytes

sql> alter database mount;

Database altered.

到这一个步骤，就停下来。

大家知道

nomount 状态数据库获取spfile的配置来启动数据库，其实这个时候我就可以

通过 show parameter 查看 background_dump_dest 这个参数来了解

alter 文件到底存放在那里了。

mount 状态，数据库实例已经获取了控制文件。控制文件里面有scn

有数据文件的指针，等等很多信息。

background_dump_dest d:/app/diag/rdbms/orcl/orcl/trace

11G 把告警文件藏这么深，不爽。

不过在建立数据库的时候，我发现11G 比10g 做了不少改进，至少2个控制文件不会存放一个目录下，

原来10g 太傻了，明明很重要的文件，虽然复制了3份，可都放在同一个目录下。还要让DBA 自己去改。

好，既然知道告警文件在什么地方，那就去查看和分析吧。

Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile D:/APP/PRODUCT/11.2.0/DBHOME_1/DATABASE/SPFILEORCL.ORA
System parameters with non-default values:
processes                = 150
memory_target            = 408M
control_files            = "D:/APP/ORADATA/ORCL/CONTROL01.CTL"
control_files            = "D:/APP/FLASH_RECOVERY_AREA/ORCL/CONTROL02.CTL"
db_block_size            = 8192
compatible               = "11.2.0.0.0"
log_archive_format       = "ARC%S_%R.%T"
db_recovery_file_dest    = "D:/app/flash_recovery_area"
db_recovery_file_dest_size= 4977M
undo_tablespace          = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain                = ""
dispatchers              = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
audit_file_dest          = "D:/APP/ADMIN/ORCL/ADUMP"
audit_trail              = "DB"
db_name                  = "orcl"
open_cursors             = 300
diagnostic_dest          = "D:/APP"
Fri Feb 18 19:11:07 2011
PMON started with pid=2, OS id=2188
Fri Feb 18 19:11:07 2011
VKTM started with pid=3, OS id=2788 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Fri Feb 18 19:11:07 2011
GEN0 started with pid=4, OS id=3896
Fri Feb 18 19:11:07 2011
DIAG started with pid=5, OS id=248
Fri Feb 18 19:11:07 2011
DBRM started with pid=6, OS id=936
Fri Feb 18 19:11:07 2011
PSP0 started with pid=7, OS id=2972
Fri Feb 18 19:11:07 2011
DIA0 started with pid=8, OS id=2976
Fri Feb 18 19:11:07 2011
MMAN started with pid=9, OS id=3044
Fri Feb 18 19:11:07 2011
DBW0 started with pid=10, OS id=1308
Fri Feb 18 19:11:07 2011
LGWR started with pid=11, OS id=2092
Fri Feb 18 19:11:07 2011
CKPT started with pid=12, OS id=2264
Fri Feb 18 19:11:07 2011
SMON started with pid=13, OS id=3516
Fri Feb 18 19:11:07 2011
RECO started with pid=14, OS id=664
Fri Feb 18 19:11:07 2011
MMON started with pid=15, OS id=2164
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Fri Feb 18 19:11:08 2011
MMNL started with pid=16, OS id=3788
starting up 1 shared server(s) ...
ORACLE_BASE from environment = D:/app
Fri Feb 18 19:11:42 2011
alter database mount
Fri Feb 18 19:11:46 2011
Successful mount of redo thread 1, with mount id 1271263854
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount
Fri Feb 18 19:12:49 2011
alter database open
Fri Feb 18 19:12:49 2011
LGWR: STARTING ARCH PROCESSES
Fri Feb 18 19:12:49 2011
ARC0 started with pid=20, OS id=2072
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Fri Feb 18 19:12:50 2011
ARC1 started with pid=21, OS id=356
Fri Feb 18 19:12:50 2011
ARC2 started with pid=22, OS id=3132
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Fri Feb 18 19:12:50 2011
ARC3 started with pid=23, OS id=1020
Errors in file d:/app/diag/rdbms/orcl/orcl/trace/orcl_arc1_356.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 5218762752 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
Errors in file d:/app/diag/rdbms/orcl/orcl/trace/orcl_arc1_356.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 47236096 bytes disk space from 5218762752 limit
ARC1: Error 19809 Creating archive log file to 'D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_179_%U_.ARC'
ARCH: Archival stopped, error occurred. Will continue retrying
Errors in file d:/app/diag/rdbms/orcl/orcl/trace/orcl_arc1_356.trc:
ORA-16038: log 2 sequence# 179 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: 'D:/APP/ORADATA/ORCL/REDO02.LOG'
Errors in file d:/app/diag/rdbms/orcl/orcl/trace/orcl_ora_4068.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 5218762752 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
Errors in file d:/app/diag/rdbms/orcl/orcl/trace/orcl_ora_4068.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 44871680 bytes disk space from 5218762752 limit
ARCH: Error 19809 Creating archive log file to 'D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_178_%U_.ARC'
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file d:/app/diag/rdbms/orcl/orcl/trace/orcl_ora_4068.trc:
ORA-16038: log 1 sequence# 178 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: 'D:/APP/ORADATA/ORCL/REDO01.LOG'
USER (ospid: 4068): terminating the instance due to error 16038
Instance terminated by USER, pid = 4068
Fri Feb 18 19:13:49 2011
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled

以上是一次数据库启动失败的过程，静下心来，仔细地阅读。很有滋味（现在20点，我还没有吃晚饭，5555）。

闪回区目录满了，并且 oracle 提供了相应的解决方法。

那好，我们就按照方法，去解决看看。

You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.

四选一，我选择四。

在操作系统层，删除归档文件。

在操作系统层面删除目录 D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/

下时间靠前的归档日志。

然后通过rman 来删除归档信息。

c:> set oracle_sid=orcl

c:> rman target /

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
validation failed for archived log
archived log file name=D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_68_6OW6HZXS_.ARC RECID=1 STAMP=743439880
validation failed for archived log
archived log file name=D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_69_6OW6JNJM_.ARC RECID=2 STAMP=743439903
validation failed for archived log
archived log file name=D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_70_6OW6K7J3_.ARC RECID=3 STAMP=743439916
validation failed for archived log

......

RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
1       1    68      X 18-FEB-11
        Name: D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_68_6OW6HZXS_.ARC

2 1 69 X 18-FEB-11
Name: D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_69_6OW6JNJM_.ARC

3 1 70 X 18-FEB-11
Name: D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_70_6OW6K7J3_.ARC

4 1 71 X 18-FEB-11
Name: D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_71_6OW6KOPG_.ARC

5 1 72 X 18-FEB-11
Name: D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_72_6OW6LB89_.ARC

......

Do you really want to delete the above objects (enter YES or NO)? yes

deleted archived log
archived log file name=D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_68_6OW6HZXS_.ARC RECID=1 STAMP=743439880
deleted archived log
archived log file name=D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_69_6OW6JNJM_.ARC RECID=2 STAMP=743439903
deleted archived log
archived log file name=D:/APP/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2011_02_18/O1_MF_1_70_6OW6K7J3_.ARC RECID=3 STAMP=743439916
deleted archived log

......

Deleted 103 EXPIRED objects

RMAN> exit

Recovery Manager complete.

rman 真的是太强大了。最近给江苏供应链写rman 的备份实施方案。rman 也着实恶补了一把。哈哈痛快。

rman target / 是用的就是控制文件来记录备份信息。删除无用的归档信息，估计控制文件里面也会被修改

吧。

SQL> alter database open;

Database altered.

SQL>

ok，数据库启动了。

恩，保持一颗淡定的心，从容地面对困难。掌握科学的方法论。很多技术问题，也许以前