[FATAL] [DBT-11101] The block size (0MB) for the tablespace SYSAUX does not match the configured block size (0MB)

On creation of database through OEM with a block size of 32K is failing @ pre-requiste step(error is below).

[FATAL] [DBT-11101] The block size (0MB) for the tablespace SYSAUX does not match the configured block size (0MB).

ACTION: To use block size of (0MB), set initialization parameter db_8k_cache_size.

On checking the template file (QC_12201_32k_DB_TEMPLATE.dbt), default tablespaces block sizes are explicitly set to “8192” where this doesn’t work, as the database block size is set to “32K”. Per oracle, If we want to create tablespaces with non-standard block size(other than database block size), we may need to set the non standard block size buffers  parameter db_8k_cache_size to certain MB(per requirement), Legimate  values are from 2K to 32K,so that the oracle can load the content of the datafile with the block size of “8k” into “32k” buffer size.

For now, created with dbca & tablespaces have been picked with blocksize of 32K (No issue). We might face issue only when mismatches happens.

TABLESPACE_NAME      BLOCK_SIZE

------------------------------ ----------

SYSTEM                              32768

SYSAUX                              32768

UNDOTBS1                        32768

TEMP                                  32768

USERS                                 32768

In our case, since db blocksize and tablespace blocksize have mismatches, Assuming that we may need to add “blocksize” parameter to ‘32K’ in the template” or “set the ‘db_nk_cache_size’ parameter with certain memory.

Content of template:

         

8192

--

8192

Refer below link for explanation :

https://docs.oracle.com/cd/B28359_01/server.111/b28310/memory004.htm#i1014186 (Navigate to “Setting the Buffer Cache Initialization Parameters“)

Read More

Oracle 12c Database mandatory background processes, new processes

Process: Oracle database having several types of processes, those are Oracle process, Server process, background processes and client process.

Oracle has introduced new mandatory background process in oracle 12c release and increased no.of process in existing mandatory BG process.

Mandatory Background process in Oracle 12c:

PMON (Process Monitor process): The process Monitor (PMON) monitors the background processes and performs process recovery when a server process or dispatcher process terminates abnormally.

PMON is responsible for cleaning up the database buffer cache and freeing the resources that the client process was using.

For example, PMON will resets the values of active transaction table, releases the locks that are no longer required, and removes the process ID from the list of active processes.

Transactional Table: The data structure with in an undo segment that holds transactional identifiers of the transactions using undo segments.

LERG (Listener registration process): The Listener registration process registers the information about database instance and dispatcher’s processes with oracle net listener.

LERG provides the listener with information about the following:

1.       Names of the database services provided by the database.

2.       Name of the database instance associated with the services and its current and maximum load.

3.       Service handlers (Dispatchers and dedicated servers) available for the instance, including their type, protocol addresses and current and maximum load.

SMON (System Monitor Process):

DBW (Database writer process):

LGWR (Log writer process):

CKPT (Check point process):

MMON and MMNL (Manageability Monitor process):

Recoverer Process (RECO):

Newly added background processes:

BWnn (Database writer process):

FENC (Fence Monitor Process):

IPCO (IPC service background process):

LDDn (Global enqueue service Daemon helper slave):

LGnn (Log writer worker):

LREG (Listener registration process):

OFSD (Oracle File server background process):

RPOP (Instant recovery repopulation daemon):

SAnn (SGA Allocator):

SCRB (ASM Disk Scrubbing Master process):

SCRn (ASM disk scrubbing slave repair process):

SCVn (ASM disk scrubbing slave verify process):

Read More