Dear All,
I am working in one instance called awsmapping and it is often getting down 2-5 time in every month.
Whenever the server is getting down, I issue the startup command then server will restart perfectly.
Whenever server is shutdown abnormally it has been ran by heavy load.
It is giving trouble every month. Is there any way to fix it permanently.?
Please find alert log details, which i faced at the time of server down
------------ alert log-----------------
Thread 1 cannot allocate new log, sequence 3956
Private strand flush not complete
Current log# 1 seq# 3955 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AWSMAPPING\REDO01_01.LOG
Current log# 1 seq# 3955 mem# 1: D:\APP\ADMINISTRATOR\ORADATA\AWSMAPPING\REDO01_02.LOG
Current log# 1 seq# 3955 mem# 2: D:\APP\ADMINISTRATOR\ORADATA\AWSMAPPING\REDO01_03.LOG
Thread 1 advanced to log sequence 3956 (LGWR switch)
Current log# 2 seq# 3956 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AWSMAPPING\REDO02_01.LOG
Current log# 2 seq# 3956 mem# 1: D:\APP\ADMINISTRATOR\ORADATA\AWSMAPPING\REDO02_02.LOG
Current log# 2 seq# 3956 mem# 2: D:\APP\ADMINISTRATOR\ORADATA\AWSMAPPING\REDO02_03.LOG
Fri May 08 01:29:06 2015
Thread 1 advanced to log sequence 3957 (LGWR switch)
Current log# 3 seq# 3957 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\AWSMAPPING\REDO03_01.LOG
Current log# 3 seq# 3957 mem# 1: D:\APP\ADMINISTRATOR\ORADATA\AWSMAPPING\REDO03_02.LOG
Current log# 3 seq# 3957 mem# 2: D:\APP\ADMINISTRATOR\ORADATA\AWSMAPPING\REDO03_03.LOG
Fri May 08 01:30:00 2015
Errors in file d:\app\administrator\diag\rdbms\awsmapping\awsmapping\trace\awsmapping_j001_2760.trc:
ORA-12012: error on auto execute of job 270898
ORA-04063: package body "SYSTEM.QUEST_PPCM_COLLECTOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYSTEM.QUEST_PPCM_COLLECTOR"
ORA-06512: at line 1
Fri May 08 01:31:06 2015
Errors in file d:\app\administrator\diag\rdbms\awsmapping\awsmapping\trace\awsmapping_m000_4760.trc (incident=99640):
ORA-01578: ORACLE data block corrupted (file # 2, block # 59168)
ORA-01110: data file 2: 'D:\APP\ADMINISTRATOR\ORADATA\AWSMAPPING\SYSAUX01.DBF'
ORA-26040: Data block was loaded using the NOLOGGING option
Incident details in: d:\app\administrator\diag\rdbms\awsmapping\awsmapping\incident\incdir_99640\awsmapping_m000_4760_i99640.trc
Fri May 08 01:31:35 2015
********************* ATTENTION: ********************
The controlfile header block returned by the OS
has a sequence number that is too old.
The controlfile might be corrupted.
PLEASE DO NOT ATTEMPT TO START UP THE INSTANCE
without following the steps below.
RE-STARTING THE INSTANCE CAN CAUSE SERIOUS DAMAGE
TO THE DATABASE, if the controlfile is truly corrupted.
In order to re-start the instance safely,
please do the following:
(1) Save all copies of the controlfile for later
analysis and contact your OS vendor and Oracle support.
(2) Mount the instance and issue:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
(3) Unmount the instance.
(4) Use the script in the trace file to
RE-CREATE THE CONTROLFILE and open the database.
*****************************************************
USER (ospid: 3704): terminating the instance
Fri May 08 01:31:37 2015
opiodr aborting process unknown ospid (4812) as a result of ORA-1092
Fri May 08 01:31:39 2015
ORA-1092 : opitsk aborting process
Fri May 08 01:32:11 2015
Instance terminated by USER, pid = 3704
Fri May 08 10:04:07 2015
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
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 =35
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEAWSMAPPING.ORA
System parameters with non-default values:
processes = 200
memory_target = 6560M
control_files = "D:\APP\ADMINISTRATOR\ORADATA\AWSMAPPING\CONTROL01.CTL"
control_files = "D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\AWSMAPPING\CONTROL02.CTL"
db_block_size = 8192
compatible = "11.2.0.0.0"
log_checkpoint_timeout = 0
db_recovery_file_dest = "D:\app\Administrator\flash_recovery_area"
db_recovery_file_dest_size= 3912M
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
audit_sys_operations = FALSE
db_domain = ""
global_names = FALSE
dispatchers = "(PROTOCOL=TCP) (SERVICE=AWSMAPPINGXDB)"
job_queue_processes = 1000
audit_file_dest = "D:\APP\ADMINISTRATOR\ADMIN\AWSMAPPING\ADUMP"
audit_trail = "DB_EXTENDED"
db_name = "AWSMAPPI"
db_unique_name = "AWSMAPPING"
open_cursors = 300
diagnostic_dest = "D:\APP\ADMINISTRATOR"
Fri May 08 10:04:08 2015
PMON started with pid=2, OS id=1992
Fri May 08 10:04:08 2015
VKTM started with pid=3, OS id=1884 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Fri May 08 10:04:08 2015
GEN0 started with pid=4, OS id=4976
Fri May 08 10:04:08 2015
DIAG started with pid=5, OS id=4540
Fri May 08 10:04:08 2015
DBRM started with pid=6, OS id=4460
Fri May 08 10:04:08 2015
PSP0 started with pid=7, OS id=3692
Fri May 08 10:04:08 2015
DIA0 started with pid=8, OS id=4556
Fri May 08 10:04:08 2015
MMAN started with pid=9, OS id=2868
Fri May 08 10:04:08 2015
DBW0 started with pid=10, OS id=3660
Fri May 08 10:04:08 2015
LGWR started with pid=11, OS id=5104
Fri May 08 10:04:08 2015
CKPT started with pid=12, OS id=4712
Fri May 08 10:04:08 2015
SMON started with pid=13, OS id=4048
Fri May 08 10:04:08 2015
RECO started with pid=14, OS id=2436
Fri May 08 10:04:08 2015
MMON started with pid=15, OS id=3156
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Fri May 08 10:04:08 2015
MMNL started with pid=16, OS id=2428
starting up 1 shared server(s) ...
ORACLE_BASE from environment = D:\app\Administrator
Fri May 08 10:04:08 2015
ALTER DATABASE MOUNT
Fri May 08 10:04:11 2015
Sweep [inc][99640]: completed
Informational message:
Control file 1 has seq# 1181769, lowest 1181768 file 0
Successful mount of redo thread 1, with mount id 3372561624
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Fri May 08 10:04:13 2015
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
parallel recovery started with 5 processes
Started redo scan
Fri May 08 10:04:38 2015
Completed redo scan
read 802194 KB redo, 43877 data blocks need recovery
Fri May 08 10:04:48 2015
Started redo application at
Thread 1: logseq 3956, block 890197
Recovery of Online Redo Log: Thread 1 Group 2 Seq 3956 Reading mem 0