oracle startup open ora 03113,解决oracle数据库ORA-03113问题

启动数据库

SYS@PROD1> startup

ORACLE instance started.

Total System Global Area 521936896 bytes

Fixed Size 2254824 bytes

Variable Size 373295128 bytes

Database Buffers 138412032 bytes

Redo Buffers 7974912 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 10579

Session ID: 63 Serial number: 5

SYS@PROD1> shutdown abort

ORACLE instance shut down.

SYS@PROD1> startup mount

ORACLE instance started.

Total System Global Area 521936896 bytes

Fixed Size 2254824 bytes

Variable Size 373295128 bytes

Database Buffers 138412032 bytes

Redo Buffers 7974912 bytes

Database mounted.

SYS@PROD1> show parameter dump;

NAME TYPE

**VALUE

——————————**

background_core_dump string

partial

background_dump_dest string

/u01/app/oracle/diag/rdbms/pro

d1/PROD1/trace

core_dump_dest string

/u01/app/oracle/diag/rdbms/pro

d1/PROD1/cdump

max_dump_file_size string

NAME TYPE

**VALUE

——————————**

unlimited

shadow_core_dump string

partial

user_dump_dest string

/u01/app/oracle/diag/rdbms/pro

d1/PROD1/trace

[oracle@openlab1 ~]$ cd /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/

最新日志跟踪

[oracle@openlab1 trace]$ tail -f alert_PROD1.log

ARC1: Becoming the heartbeat ARCH

Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ora_10708.trc:

ORA-19815: WARNING: db_recovery_file_dest_size of 2097152000 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.

ARCH: Error 19809 Creating archive log file to ‘/u01/app/oracle/archive_dest/PROD1/archivelog/2018_08_12/o1_mf_1_19_%u_.arc’

Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ora_10708.trc:

ORA-16038: log 1 sequence# 19 cannot be archived

ORA-19809: limit exceeded for recovery files ##超出了恢复文件数的限制

ORA-00312: online log 1 thread 1: ‘/u01/app/oracle/oradata/PROD1/redo01.log’

USER (ospid: 10708): terminating the instance due to error 16038

Sun Aug 12 01:10:57 2018

System state dump requested by (instance=1, osid=10708), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_diag_10658_20180812011057.trc

Dumping diagnostic data in directory=[cdmp_20180812011057], requested by (instance=1, osid=10708), summary=[abnormal instance termination].

Instance terminated by USER, pid = 10708

解决方法如下

下列选项可从恢复区域释放空间:

1、考虑改变RMAN保留策略。

2、使用备份恢复区域命令备份文件第三设备磁盘如磁盘。

3、添加磁盘空间并增加 db_recovery_file_dest_size 参数映射新的空间。

4、使用RMAN DELETE命令删除不必要的文件。如果操作系统命令用于删除文件,然后使用 RMAN CROSSCHECK和DELETE EXPIRED的命令。

第一种方法如下

[oracle@openlab1 ~]$ rman target /

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name PROD1 are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default

CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_PROD1.f’; # default

RMAN> configure retention policy to redundancy 5;

new RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO REDUNDANCY 5;

new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name PROD1 are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 5;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default

CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_PROD1.f’; # default

RMAN方式登录,手动删除不需要的归档日志(删除三天以前的归档记录)

RMAN> delete archivelog all completed before ‘sysdate-3’ ;

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=5 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=67 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=130 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=193 device type=DISK

**List of Archived Log Copies for database with db_unique_name PROD1

=====================================================================**

Key Thrd Seq S Low Time

19 1 1 A 09-JUL-18

Name: /home/oracle/flash/PROD1/archivelog/2018_07_15/o1_mf_1_1_fnpldxj3_.arc

20 1 2 A 15-JUL-18

Name: /home/oracle/flash/PROD1/archivelog/2018_07_15/o1_mf_1_2_fnpn0mkz_.arc

21 1 3 A 15-JUL-18

Name: /home/oracle/flash/PROD1/archivelog/2018_07_28/o1_mf_1_3_forpnyrn_.arc

SYS@PROD1> alter database open;

Database altered.

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值