[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

What is the difference between Automatic memory management & Automatic shared memory management?

AMM: Automatic memory management  is introduced from 11g, we can specify the target size for the instance memory. The instance automatically tunes the target memory size, redistributing the memory as per the demand of SGA and the instance PGA.

ASMM:  Automatic Shared memory management is introduced from 10g, it is partially automated, in this mode we can set the target size for the SGA and then setting the aggregate target size for the PGA or managing PGA area individually.

Read More

What is the PGA in Oracle? What is the use of it?

In Oracle PGA is specific to operation system process or thread that is not shared by the other process or threads and it is process specific, it could not establish in SGA.

PGA is a heap that contains session dependent variables required by a dedicated or shard server process, server process allocates memory structures that requires in PGA.

Private Sql Area:

A private sql area contains the information about parsed sql statements and other session specific information for processing. When a server process executes a sql or pl/sql statements/codes the process uses the private sql area to store the bind variable values, query execution state information and query execution work areas.

Private sql area can be placed Program global are (PGA) in dedicated server architecture, In shared server architecture private sql area creates at SGA.

Read More

What are the bind variable in oracle? What is the use of bind variables?

How to create the bind variable in oracle, types of bind variables.

A bind variable is a placeholder of a sql statement that must be replaced with the valid value or value address for the statement to execute successfully. Using the bind variables can write the sql statements that accepts inputs or parameters at run time.

Below example will explain in detail about bind variable:

Select * from emp where emp_id= :v_empid; In this statement v_empid is bind varaiable.

Referencing bind variables:

We can reference bind variable in pl/sql by typing : colon and immediate followed by the variable.

Ex: :retrive_val :=1;

To change this bind variable in sql*plus you must enter pl/sql block

Begin

  :retrive_val := 4;

End;

/

Displaying the bind variable

Sql> print retrive_val;

Read More

Standby database creation steps for oracle standard edition in windows

Manual standby creation in oracle standard edition, manual log shipping method for creation standby database

In general, we use Data guard for Oracle standby database creation in enterprise edition, comes to standard edition we are unable to create the Disaster Recovery site using Data guard, because this feature disabled in oracle standard edition.

In Oracle standard edition standby database can creates using log shipping method, here we go with the complete steps.

For standby database creation proceed with rman recovery and user managed recovery process.

Steps to create standby DB in windows: 

1.       Make sure primary database is in archive log mode.

2.       Install oracle binaries at standby side and create same folder structure at standby server as like primary.

3.       Create pfile from spfile and send it to standby server ‘C:\app\oracle\product\11.2.0\database\’.

4.       Create standby control file send standby controlfile to DR server.

Sql> alter database create standby controlfile as ‘Ç:\app\standbycontrol.ctl’;

5.       Update the controlfile information in pfile.

6.       Create oracle service using oradim command and start the service.

oradim -NEW -SID ORCL -STARTMODE manual -PFILE C:\app\oracle\product\11.2.0\database\INITORCL.ORA"

7.       Take the backup of database from primary side.

Sql> alter database begin backup;

Copy all datafiles to standby server

Sql> alter database end backup;

8.       Start the standby database in mount state.

Sql> shut immediate

Sql> startup nomount

Sql> alter database mount standby database;

9.       Create standby archive log folder as a network drive at primary database side.

10.   Schedule the archives copy script at primary server and recover database script at standby server side.

11.   Archives_copy.bat

copy C:\app\oracle\oradata\ORCL\archive\*.0001 E:\dba\backup\arc_backup

12.   Recover_database.bat

@echo off

C:

cd \oracle\product\11202\BIN

set ORACLE_HOME=C:\oracle\product\11202

set ORACLE_SID=orcl

sqlplus / as sysdba @D:\oradata\orcl\archive\recover.sql

3. recover.sql

spool D:\oradata\orcl\archive\log.recovery replace

select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

SELECT MAX(RECID) "Log id now" FROM V$LOG_HISTORY;

recover standby database;

auto

spool off

exit;

Read More

Oracle database migration from server to server using RMAN

Datafiles move from one disk to another disk, Database move from one disk to another disk, switch database to rman incrementally updated backup copy images.

Complete database migration from one server to another using rman backup in scheduled window / downtime:

Source server:

1.       Check the all application services are in down or not. Have make sure all the application services down before starting.

Sql>select process, program, username from v$session where username not like '%oracle%';

2.       Make sure the switch log file / check point at database level.

Sql> alter system switch logfile;

Or

Sql> alter system checkpoint;

3.       Take the rman full backup.

Rman > backup database plus archivelog;

Above command takes the full database backup. Also takes backup of spfile and controlfile but have to configure controlfile auto backup on then it takes the backup as $ORACLE_HOME/dbs/c-dbid-date.

4.       Using scp send the all backup files to target server.

5.       Copy all password file, listener, tnsnames, sqlnet.ora file to target server.

 Target server:

1.       Install oracle binaries, place the password file in $ORACLE_HOME/dbs location, listener, tnsnames and sqlnet.ora file in $ORACLE_HOME/network/admin location.

2.       Set the bash_profile.sh for oracle user, export ORACLE_SID=orcl (instance name).

3.       Connect to the rman.

Rman target sys/pwd

Rman> set dbid 43123423;

Rman> restore spfile from ‘/u01/backup/c-43123423-20170823’ to ‘/u01/app/oracle/product/12.2.0/dbhome_1/dbs/spfileorcl.ora’;

Sql> startup nomount

Rman> restore controlfile from ‘/u01/backup/c-43123423-20170823’; (please make sure the path of the controlfile from pfile)

Sql> startup mount;

Rman>  run { 

catalog start with '/backup location/';

set newname for database to '/new database files location/%b';

restore database;

switch datafile all;

recover database;

}

Above run block

1.    Catalog start with ‘backup sets location’ : rman catalogs the backup copies.

2.    Set newname for database to ‘/destination/%b’ : it renames the all datafiles location to new location in target server.

%b specifies stripped datafiles to single location

3.    Restore database: restore the database

4.    Switch datafile all: it switches all the datafile to new location

5.    Recover database: it recovers the database

4.       Rename the all redo log files.

Sql> select member from v$logfile;

Sql> alter database rename file '/previous path/redo01.log' to '/new path/redo01.log';

5.       Open database using open reset logs.

Sql> alter database open reset logs;

6.       Create temporary tablespace make as default.

Read More

Oracle DBA 6 years experience interview questions

1. What is the pga?
2. What are the pga parameters?
3. What is the relation between pga and temporary tablespace?
4. If incase PGA memory is having 2gb, and the statement required more memory then what hapen?
5. What is the smon?
6. What are the states of the redo logfiles?
7. What happens if we deletes all the redo log members? how to restore and recover?
8. What happens once we place the tablespace in begin backup mode?
9. why archives generates heavily at begin backup mode?
10.what is the difference between avialable and expired in rman backup?
11. what is the crosscheck?
12. What is the checkpoint?
13. what is the difference between cpu and psu patches?
14. What are the steps to applying the psu patches?
15. What process can fetch data into buffer cahce from datafiles?
16. is uncommitted data can writes into datafiles?
17. What is the chache coherence? What maintaince this?
18. What is the GRD, where it placed and which instance it resides?
19. what is node eviction? what causes for the node eviction?
20. What parameters need to create standby?
21. Which background process applies redo at standby side?
22.In which view we can verify the psu patch applied or not?
23. What are the statistics? what is the use of it and what are the incremental statistics?
24. How to migrate database from one os to another os?
25. Why we can run the roo.sh and rootinst.sh in oracle installation?
26. Tell me the select statement execution process?
27. tel me about update statement in oracle?
28. what are the sequence of steps in rac 11gr2?
29. What are the rac specific background process?
30. How to move the datafile created at file system to RAC (ASM)?
31. What is your recently resolved performance issue?

1.how to know which databases are using asm instance
2.show parameter in exp
3.check the status of the  exp dump file
4.restore datafile to perticuler location using rman
5.how to check size of a directory in linux
6.status of  partitioned disk after it assigened to asm disk group
7.how to stop job in expdp
8.how many types of load balances available in RAC

9.when we issue startup upgrade command what will happen
10.what is db replay
11.compression
12.rman 11g features
13.total db size
14.rman backup size and time
15.logical backup size,time and schema size
16.where can we find node membership polling machanism in RAC
17.how much database size  grows daily

18.are the idexes are always benficial or not
19.how to stop crs without connecting to root user
20.how to see listener status if the listener is passwor protected
21.how to improve performance of a impdp command
22.what is default time and location for ocr backup
23.difference between cpu,psu,bug fixes patch

24.How to check backup type in list backup command.
25.Can we rename asm instance
26.Asm instance control file
27.Can we create more than one asm instance on one box
28.What is conflict check in opatch

29.Crs should be up or down to take voting disks backups using dd command
30.Why expdp is faster than normal exp
31.Difference between active dataguard and normal dataguard
32.Will rman create temporary tablespace after cloing or restore process.
33.Difference between 10g load balance machanism and 11gr2 load balance machanism

34.What is the use of pwd file
35.Why mrp process runs in single on standby 3 node rac
36.Name of view that stores plsql compilation errors
37.Diffrence between cloning and refresh
38.How to demonstrate to the developper taht if they created index on perticular column it would hep
39.Order of background process when we issue startup command
40.What is the shortest way to restore a table if we have only rman backup of db size 1.5tb
41.How will you recover spfile if we dont have backup and it got corrupted
42.How does scan identify that node is a least loaded node
43.How to change the rebalance value in asm
44.Where cluster checks the details of asm diskgroups before starting the css process and pass these names to kfed
45.Which process starts 1st after issuing  crs start command
46.What will be state of database if we kill dwr background process
47.How to find scan name of a cluster
48.How to find number of nodes in cluster from os level with out using rac or database commands
49.How to find location of inventory file
50.What information contains in inventory.xml file
51.What is use of napply in opatch
52.What is use of CONSISTENT parameter in exp
53.How to find sql query of  session if  i have only  os process id
54.How to create spfile for asm instance

Read More