RAC/Grid startup sequence in Oracle 11gR2

OHASD has access to OLR (oracle local registry). OHASD then reads the OLR content and initialize accordingly. 

OHASD brings up GPnP (ora.gpnpd)Daemon and CSS (ora.cssd) Daemon. 

CSS Daemon has access to the GPNP Profile stored on the local file system. I even found a copy of GPNP Profile directly stored  in OLR (in Oracle 12c release 2)

The Voting Files locations on ASM Disks are accessed by CSSD with well-known pointers in the ASM Disk headers and CSSD is able to complete initialization and start or join an existing cluster.

OHASD starts an ASM instance. The ASM instance uses special code to locate the contents of the ASM SPFILE, if it is stored in a Diskgroup. 

With an ASM instance operating and its Diskgroups mounted, access to Clusterware’s OCR is available to CRS.

OHASD then starts CRSD (ora.crsd)damon with access to the OCR in an ASM Diskgroup.

And thus Clusterware completes initialization and brings up other cluster managed resources defined in OCR.

Level 1: OHASD Spawns:

• cssdagent – Agent responsible for spawning CSSD.
• orarootagent – Agent responsible for managing all root owned ohasd resources.
• oraagent – Agent responsible for managing all oracle owned ohasd resources.
• cssdmonitor – Monitors CSSD and node health (along wth the cssdagent).

Level 2: OHASD rootagent spawns:

• CRSD – Primary daemon responsible for managing cluster resources.
• CTSSD – Cluster Time Synchronization Services Daemon
• Diskmon
• ACFS (ASM Cluster File System) Drivers

Level 2: OHASD oraagent spawns:

• MDNSD – Used for DNS lookup
• GIPCD – Used for inter-process and inter-node communication
• GPNPD – Grid Plug & Play Profile Daemon
• EVMD – Event Monitor Daemon
• ASM – Resource for monitoring ASM instances

Level 3: CRSD spawns:

• orarootagent – Agent responsible for managing all root owned crsd resources.
• oraagent – Agent responsible for managing all oracle owned crsd resources.

Level 4: CRSD rootagent spawns:

• Network resource – To monitor the public network
• SCAN VIP(s) – Single Client Access Name Virtual IPs
• Node VIPs – One per node
• ACFS Registery – For mounting ASM Cluster File System
• GNS VIP (optional) – VIP for GNS

Level 4: CRSD oraagent spawns:

• ASM Resouce – ASM Instance(s) resource
• Diskgroup – Used for managing/monitoring ASM diskgroups.
• DB Resource – Used for monitoring and managing the DB and instances
• SCAN Listener – Listener for single client access name, listening on SCAN VIP
• Listener – Node listener listening on the Node VIP
• Services – Used for monitoring and managing services
• ONS – Oracle Notification Service
• eONS – Enhanced Oracle Notification Service
• GSD – For 9i backward compatibility
• GNS (optional) – Grid Naming Service – Performs name resolution

Read More

Long running queries troubleshooting in oracle database

In general application team come to DBA’s and complaint on one application query, last time or previous times it was done in few seconds now it is taking 1 minute or more time. For this issue no concreate answer. Every DBA has their own approaches to solve the issue.

Step:1

Find the long running query:

Long running session are currently running, then we can use v$session find the long running query sql_id, using sql_id get the sql using v$sqltext view.

Sql>select sql_id from v$session where sid=1234;

      Sql_id

 btdzd9ktsa55n

sql> select sql_text from v$sqltext where sql_id=’ btdzd9ktsa55n’ order by piece;

Sql_text

select name, accno, htown, amt, transactions from abd.transact where mobile_no=’9933445599’;

If in-case query already completed, then we can find the sql_id from AWR, ASH and dba_hist_active_session_history views on specific time interval.

Step2:

1.     Run the Sql_Tuning_advisor for the sql_id:

SET SERVEROUTPUT ON

DECLARE

  l_sql_tune_task_id  VARCHAR2(200);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          sql_id      => 'btdzd9ktsa55n',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 60,

                          task_name   => 'btdzd9ktsa55n_tuning',

                          description => 'Tuning for btdzd9ktsa55n');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

2.     Execute the DBMS_SQLTUNE using task_name

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'btdzd9ktsa55n_tuning');

3.     Once tuning task done, execute report tuning task.

SELECT DBMS_SQLTUNE.report_tuning_task('btdzd9ktsa55n_tuning') FROM dual;

It gives us few recommendations have to take the actions accordingly.

Recommendations could be gather statistics, creating indexes, drop indexes……..

Step 3:

Few time we didn’t get any recommendations from the Sql_tune, then that time we have to verify is there any plan changed for the query using dba_sqlstat, dba_hist_snapshot and DBA_HIST_SQL_PLAN.

select distinct b.BEGIN_INTERVAL_TIME as Snap, a.PLAN_HASH_VALUE as plan, a.EXECUTIONS_DELTA as EXECUTIONS, a.ELAPSED_TIME_DELTA/1000000 as ELAPSED_SEC, ROWS_PROCESSED_DELTA as "ROWS" , a.ROWS_PROCESSED_DELTA/CASE WHEN a.EXECUTION_DELTA = 0 THEN -1 ELSE a.EXECUTIONS_DELTA END "Avg Rows", a.ELAPSED_TIME_DELTA/1000000/CASE WHEN a. EXECUTION_DELTA = 0 THEN -1 ELSE a.EXECUTION_DELTA END "Avg Elapsed", a.optimizer_cost, a.SQL_PROFILE from DBA_HIST_SQLSTAT a, DBA_HIST_SNAPHOT b where a.SQL_ID = '&sqlid' and a.snap_id = b.snap_id order by b.BEGIN_INTERVAL_TIME;

or

SELECT DISTINCT sql_id, plan_hash_value

FROM dba_hist_sqlstat q,

    (

    SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap

    FROM dba_hist_snapshot ss

    WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE

    ) s

WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap

  AND q.sql_id IN ( '&SQLID')

/

Above queries gives us sql paln from number of days, from the list we can choose the best plan and pin the best plan.

How to pin the sql plan:

Read More

pre and post migration overview of database migration to cloud Azure, AWS

As you prepare for migrating to the cloud, verify that your source environment is supported and the you have addressed any prerequisites.

1. Pre-Migration Overview:

• Find the Database size, it helps us to how long the data copy will take.
• How many schema's and tables going to migrate and check the large objects.
• Database users, roles and privileges.
• How busy/critical of source database.
• Under standing the network, firewalls and security.
• How much down time afford.
• Limitations of source database.

2. Planing migration methods and Execution phase:

• Are we have any additional resources available to assist with various data migration scenarios.
• Relatively small databases that allow for downtime during migration, relatively large databases that can't afford downtime during migration.

3. Post migration overview:

• Developing validation test on both source and target databases using same queries.
• Set up test environment
• Run validation tests against the source and the target and then analyze the result.
• Run the performance tests against the source and target and then analyze and compare result.

Read More

[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