Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, oracle.

Asked: March 07, 2017 - 5:29 am UTC

Last updated: September 21, 2017 - 2:46 am UTC

Version: 12.1.0

Viewed 10K+ times! This question is

You Asked

Hi team,

I have seen alert log it is showing message like this. DB has not been restarted then why the spfile read it is showing because as per my understanding it shows message like this when we start the db.


DB IS - 12c
ASM also there.


Creating new log segment:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1
System name: Linux
Node name: RNTL-SRV-06
Release: 2.6.32-642.6.1.el6.x86_64
Version: #1 SMP Wed Oct 5 00:36:12 UTC 2016
Machine: x86_64
Using parameter settings in client-side pfile
System parameters with non-default values:
processes = 2000
sga_max_size = 35G
use_large_pages = "TRUE"
resource_manager_plan = ""
sga_target = 35G
control_files = "+DATA/ABGPROD/CONTROLFILE/current.261.926110577"
control_files = "+RECO/ABGPROD/CONTROLFILE/current.256.926110577"
db_block_size = 8192
compatible = "12.1.0.2.0"
log_archive_format = "%t_%s_%r.dbf"
db_create_file_dest = "+DATA"
db_recovery_file_dest = "+RECO"
db_recovery_file_dest_size= 4560M
undo_tablespace = "UNDOTBS1"
sec_case_sensitive_logon = FALSE
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=abgprodXDB)"
local_listener = "LISTENER_ABGPROD"
session_cached_cursors = 100
audit_file_dest = "/u01/app/oracle/admin/abgprod/adump"
audit_trail = "DB"
db_name = "abgprod"
open_cursors = 1000
pga_aggregate_target = 12G
diagnostic_dest = "/u01/app/oracle"
Deprecated system parameters with specified values:
sec_case_sensitive_logon
End of deprecated system parameter listing
===========================================================
Dumping current patch information
===========================================================
Patch Id: 20373598
Patch Description:
Patch Apply Time: 2016-12-13 23:30:33 GMT+05:30
Bugs Fixed: 20373598
===========================================================

Thanks

and Connor said...

I just went back and looked at my alert logs for my 12c instances, and (like you said) the only time I see those messages are when I've restarted the instance.

But if all the values are ok, ie, you dont have some *other* instance incorrectly configured so that its dumping junk into the wrong alert log, I wouldn't lose sleep over it :-)

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

oracle oracle, March 08, 2017 - 6:38 am UTC

Not found any solution from this post . Don't understand what you are trying to say.

More Information on This

Paul Viola, April 25, 2017 - 3:12 pm UTC

This document in Meta Link explains this behavior: 2049516.1

It is new new behavior in 12C.
Connor McDonald
April 26, 2017 - 4:37 am UTC

Cool - something new learned today !

Thanks for the info.

Thank and fyi

Vineet Arneja, August 03, 2017 - 8:49 am UTC

Hi guys,

Thanks for posting out this information. It is useful.

I would like to cascade further information on it:

In 12c, Alert log is divided into two formats stored in the file system, one is the old Alert log and the other is the XML file format which is used primarily for OEM. Oracle dumps the startup parameters and installed patches information at the creation of a log.xml file.

When the size of the XML file exceeds 10M, Oracle creates a new file log.xml which is created in <alertlog_location>../alert/ directory. and the old file is named log_<number>.xml. e.g. below

-rw-r----- 1 oraap2 sapsys 10485982 Aug 4 2016 log_1.xml
-rw-r----- 1 oraap2 dba 10485999 Dec 5 2016 log_2.xml
-rw-r----- 1 oraap2 dba 10485852 Mar 21 12:28 log_3.xml
-rw-r----- 1 oraap2 dba 10485788 Jun 19 05:00 log_4.xml
-rw-r----- 1 oraap2 dba 10485951 Aug 1 11:28 log_5.xml
-rw-r----- 1 oraap2 dba 370349 Aug 3 09:27 log.xml

so these xml files are considered as a segment to oracle So at their creation, oracle writes "Creating new log segment: " and then writes down the parameters and installed patches information in the alertlogs.


You can set the below dynamic parameter to false if you want to stop this behavior.


_log_segment_dump_parameter = FALSE

_log_segment_dump_patch = FALSE

if they are not defined in the spfile or pfile in your database, then this behaviour is enabled by default.

Regards
Vineet Arneja

Connor McDonald
August 04, 2017 - 1:49 am UTC

Good additional info.

Being inside Oracle I'll add my standard "Please get approval from Support before using underscore parameters" official line.

oracle oracle, August 08, 2017 - 8:20 am UTC

Thanks for sharing the information.

But in my case i checked the log_1.xml is created on 9am but the creating new segment and read pfile message showing in alert log was on 7am So, how can i check and verify the log.xml is created and the alert showing at the sametime. Ideally the time should be same for created log.xml and alert message.
Connor McDonald
August 14, 2017 - 1:25 am UTC

Probably need to take that one up with Support - I can't see that on any system I've got here. I get a *minor* discrepancy because every xml message has a new timestamp but thats about it, eg

2017-08-13T14:27:18.905136+08:00          <=================
NOTE: Using default ASM root directory ASM
NOTE: Cluster configuration type = NONE [2]
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production.
Windows NT Version V6.2  
ORACLE_HOME =  c:\oracle\product\12.2.0.1
Node name           : XPS13
CPU                 : 4 - type 8664, 2 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:8616M/16235M, Ph+PgF:9229M/23403M 
Using parameter settings in server-side spfile C:\ORACLE\PRODUCT\12.2.0.1\DATABASE\SPFILEDB122.ORA
System parameters with non-default values:
  processes                = 320



versus


<msg time='2017-08-13T14:27:18.905+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='XPS13'
 host_addr='fe80::4114:a4f:3ca6:25f4%26' pid='14096'>
 <txt>NOTE: Using default ASM root directory ASM
 </txt>
</msg>
<msg time='2017-08-13T14:27:18.905+08:00' org_id='oracle' comp_id='rdbms'
 type='UNKNOWN' level='16' host_id='XPS13'
 host_addr='fe80::4114:a4f:3ca6:25f4%26' pid='14096'>
 <txt>NOTE: Cluster configuration type = NONE [2]
 </txt>
</msg>
<msg time='2017-08-13T14:27:18.952+08:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:23297:3284844642' type='NOTIFICATION' group='startup'
 level='16' host_id='XPS13' host_addr='fe80::4114:a4f:3ca6:25f4%26'
 pid='14096'>
 <txt>Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production.
 </txt>
</msg>
<msg time='2017-08-13T14:27:18.952+08:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:23310:4222364190' type='NOTIFICATION' group='startup'
 level='16' host_id='XPS13' host_addr='fe80::4114:a4f:3ca6:25f4%26'
 pid='14096'>
 <txt>Windows NT Version V6.2  
ORACLE_HOME =  c:\oracle\product\12.2.0.1
Node name           : XPS13
CPU                 : 4 - type 8664, 2 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:8616M/16235M, Ph+PgF:9229M/23403M 
 </txt>
</msg>
<msg time='2017-08-13T14:27:18.952+08:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:23392:1489803337' type='NOTIFICATION' group='startup'
 level='16' host_id='XPS13' host_addr='fe80::4114:a4f:3ca6:25f4%26'
 pid='14096'>
 <txt>Using parameter settings in server-side spfile C:\ORACLE\PRODUCT\12.2.0.1\DATABASE\SPFILEDB122.ORA
 </txt>
</msg>
<msg time='2017-08-13T14:27:18.967+08:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:23421:144004252' type='NOTIFICATION' group='startup'
 level='16' host_id='XPS13' host_addr='fe80::4114:a4f:3ca6:25f4%26'
 pid='14096'>
 <txt>System parameters with non-default values:
 </txt>
</msg>
<msg time='2017-08-13T14:27:18.967+08:00' org_id='oracle' comp_id='rdbms'
 msg_id='kspdmp:23463:527288951' type='NOTIFICATION' group='startup'
 level='16' host_id='XPS13' host_addr='fe80::4114:a4f:3ca6:25f4%26'
 pid='14096'>
 <txt>  processes                = 320
 </txt>
</msg>




SPFILE values

KIMT, September 20, 2017 - 12:27 pm UTC

First of all, thanks for the information and detailed explanation.

I am just wondering, the startup parameters in the log file will then not always reflect current running values. AS it is an SPFILE read, some changes could have been made only on SPFILE ie. no instance restart.




Connor McDonald
September 21, 2017 - 2:46 am UTC

"I am just wondering, the startup parameters in the log file"

Correct.

In fact, as soon as you do:

alter system set xxx = yyy scope=spfile;

then the SPFILE no longer reflects the current running parameters, and similarly:

alter system set xxx = yyy scope=system;

means the system has been changed away from the current spfile settings.

But you would typically see those 'alter' statements in the alert.log

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.