[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

Oracle Dataguard background process, log apply service internals

Dataguard Background process:

1.       Logwriter process

2.       Archiver process

3.       Logwriter network server (LNS)

4.       Fetch archive logs (FAL) For client server mechanism

5.       Remote file server

6.       Managed recovery process for physical standby

7.       Logical standby process for logical standby

8.       Data guard broker monitor (DMON) – If incase dg broker enabled

Apply services at High Level: -          Data guard apply services comprises of two parts

o   Redo Apply for physical standby

o   Sql Apply for logical standby

-        The Physical standby and logical standby databases utilize the same redo transport and role management services, only apply service is different.

Redo apply: A physical standby database applies redo data received from the primary database using oracle media recovery process.

1.    Redo applies uses a specialized process called the managed recovery process.
2.   As the RFS process is writing redo data to standby redo logs (SRL’s). MRP process reads the redo data and applies it to the physical standby database.
3.    MRP is started in the physical standby database by mounting the database using below command.

-          Sql > alter database recover managed standby database using current logfile disconnect from the session;

Sql Apply: Sql apply uses a collection of background process that perform the task of applying changes from the primary database to the logical standby database.

 Logical standby database:

• -          Logical standby database contains the same logical information at standby DB.
• -          Physical organization and structure of the data can be different.
• -          Synchronization with the primary database through sql apply.
• -          Can be used for queries and reporting at any time.

-         Service to be involved for logical standby : Log transport services, Log apply services

Log Transport services:  Transmit the redo data from the primary database to standby. Enforce the data protection modes.

Log apply services: Automatically apply the archived redo logs on the standby database. Maintains transitional synchronization with primary database. Allows transitionally consistent read-only access to the data.

Read More

What are the Oracle Database upgradation method’s?

What is database upgradation:

Database upgradation means moving existing version of database to higher release version. In the process of upgradation of database dictionary compatible with a newer oracle database version. Below actions has included in the part of upgrading.

1.        Adding, dropping, or modifying columns in system tables and views.
2.        Creating new system packages or procedures.
3.        Modifying existing system packages or procedures.
4.        Creating, modifying, or dropping database users, roles, and privileges.
5.        Modifying seed data that is used by Oracle database components.

     Why go for upgradation:

    There are numerous features releasing in oracle version by version. Those newly released features will simplifying the DBA tasks and time.

Database upgradation methods:

1.        DBUA and command line
2.        Transportable tablespace or Full Transportable Export/Import
3.        Oracle Datapump Export / Import
4.        Traditional export and import methods.

For upgradation of from lower version to higher, must verify the Database upgrade compatibility matrix and fall back strategy.

Database upgrade/downgrade compatibility matrix Doc ID 551141.1

• Prerequisites for the database upgradation / check lists:
• Cleanup: Purge the recycle bin, check the invalid and duplicate objects in SYS and SYSTEM users.
• Components: Check for mandatory, invalid components and remove the obsolete components.
• Check the materialized views: Verify the all materialized view status, refresh in-case any materialized views that are not fresh.
• Create baseline: Preserve the performance statistics.
• Backup: Take the full backup of database, binaries backup.
• Dictionary statistics: Gather the dictionary statistics, it fasts the upgradation and reduce downtime.
• Time zone:  Verify the source database timezone and target database timezone, if source database timezone is higher version of target apply the timezone patch to match the source and target databases.

Note: Must be empty the recycle bin during the upgrade process to avoid possible ORA-00600 errors, and to minimize the upgrade time.

Read More

What are the requirements for Oracle RAC / Grid 11g, 12c installation

What is Cluster, requirements to build cluster database.

Oracle clusterware enables one or more than servers into single unit and communicate each other. Oracle cluster manages the resources such as server names, VIP’s, private IP’s, Public IP’s, scan IP’s, databases, listeners, instances, services and so on, and these resources doesn’t allow to modify. For example resources names like as ora.host_name.resource_name.

Hardware requirements for the Oracle RAC

Hardware components (Servers) of the clusterware is similar. However, a server is part of the cluster requires second network to communicate each other, this second network is called as the interconnect. This interconnect network is a private network using a switch that only the nodes in the cluster can access.

Note: Oracle doesn’t support using cross cables as clusterware interconnects.

1.       At least two network interfaces to the public network.

2.       At least two network interfaces to the private interconnect network.

 Storage requirements for the Oracle Clusterware / Grid infrastructure:

The clusterware servers required common shared storage (cluster-aware storage) mounted in all servers. Oracle clusterware supports NFS, iSCSI, DAS, NAS and SAN storage's.

Oracle RAC/Grid Components:
OCR

Voting Disk

OLR

Networking Configurations:
Scan IP’s

Public IP’s

Private IP’s

Virtual IP’s

Oracle Clusterware Stack:

1.       Cluster ready services Stack

2.       Oracle high availability services stack

Cluster Ready Services Stack:

1.       Cluster Ready Services (CRS)

2.       Cluster Synchronization Services (CSS)

3.       Oracle ASM

4.       Cluster Time Synchronization Service

5.       Event Management (EVM)

6.       Oracle Notification Service (ONS)

7.       Oracle Agent (oraagent)

8.       Oracle Root Agent (orarootagent)

Oracle High Availability Service Stack:

1.       Cluster Logger service (ologgerd)

2.       System Monitor Service (osysmond)

3.       Grid Plug and Play (GPNPD)

4.       Grid interprocess Communication (GIPC)

5.       Multicast Domain Name Service (mDNS)

6.       Oracle Grid Naming Service (GNS)

Read More