Oracle RAC Startup sequence with ASM file system

OHASD is the root for bringing up Oracle Clusterware.

 

1.      
OHASD  ------------------------------------->   OLR (Provided Needed data to complete OHASD initialization)

has  access

 

2.        OHASD     --------------------------------------------->    GPNPD & CSSD (Cluster Sync Services)

                       Brings UP

                                                                                                                                            CSSD Has access of Grid Plug and Play

GPNP-à Profile Stored on the Local File system

       

Contains

è  ASM Diskgroup Discovery String

è  ASM SPFILE location (Diskgroup Name)

è  Name of the ASM Diskgroup containing the voting files.

3.       The Voting File location on ASM disks are accessed by CSSD with well-known pointers in the ASM disk header and CSSD is able to complete initialization and start or join an existing cluster.

4.       OHASD start as ASM instance and ASM can now operate with CSSD initialization and operating. The ASM instance uses special code to locate the contents of the ASM SPFILE assuming it is stored in diskgroup.

5.       With an ASM instance operating and its Diskgroup mounted, access to Clusterware’s OCR is available to CRSD.

6.       OHASD starts CRSD with access to the OCR in an ASM Diskgroup.

7.       Clusterware complete initialization and brings up other services under its control.

When Clusterware start, three files are involved.

OLR- First file to read and opened. This file contains information regarding where the voting disk is stored. And information to Startup the ASM (e.g. ASM Diskgroup string)

VOTING DISK- This is the 2^nd file to be opened & read, this is depend on only OLR being accessible (ASM starts after CSSD or ASM does not start if CSSD is offline (i.e voting file missing).

To check voting file use- v$ASM_DISK column Voting_file

OCR- Finally the ASM instance start and mount all Diskgroup, then Clusterware daemon (CRSD) opens and reads the OCR which is stored on Diskgroup.

So, if ASM already started, ASM does not depends on OCR or OLR to be online. ASM depends on CSSD (vote disk) to be online.


- Piyush Mishra

                                                                                                                                                                 

Shared_Pool_Size error ORA-04031


http://www.dbas-oracle.com/2013/05/5-Easy-Step-to-Solve-ORA-04031-with-Oracle-Support-Provided-Tool.html

Steps to Resolve ORA-4031 Error:

Login to Oracle Support and search for "Diagnostic Tools Catalog [ID 559339.1]" in knowledge base. This is a page which has all diagnostic tools link at one place for helping DBA's. You can also explore other tools available to solve other issues. Below is the scree shot of "Diagnostic Tools Catalog [ID 559339.1]". Now click on "ORA-4031 Troubleshooting Tool" a new tool page will pop up.

Manual Physical Standby Failover – Activate Standby Database or DR sync with standby database and need to open database

One:

First Check database listener, it should be running:

[oracle@DRDG ~]$ lsnrctl status

Two:

Check database mode and role, It should be MOUNTED and PHYSICAL STANDBY:

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

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

ABC MOUNTED PHYSICAL STANDBY

Three:

Copy archive logs from primary database, those yet to apply on standby database before activate it.

SQL> recover standby database until cancel;

AUTO

Four:

Activate standby database as primary database:

[oracle@DRDG ~]$ sqlplus / as sysdba

SQL> alter database activate standby database;

Database altered.

Five:

Shutdown standby database and startup:

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 755769344 bytes

Fixed Size 2217184 bytes

Variable Size 448793376 bytes

Database Buffers 301989888 bytes

Redo Buffers 2768896 bytes

Database mounted.

Database opened.

 Standby database successfully activated as Primary database. Failover successful.

Post activity check, ensure database mode and role, it should be READ WRITE and PRIMARY.

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

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

ABC READ WRITE PRIMARY

Note: Please take full database backup after failover.

Note: Once old primary database is available for use then you can reconfigure standby database.

Dataguard Command and SQL query

Find data guard archived redo log lag gap time

How to resize Redo Log and Standby Redo Log

Oracle – Find sql Id from process id

If CPU usage high due to Oracle Process, then we can use respective PID to identify SQL id, SQL_text and much more.

Query-

SELECT SQL_TEXT

FROM v$sql

WHERE sql_id = (SELECT sql_id FROM v$session WHERE paddr = (SELECT addr

FROM v$process

WHERE spid = '&process_id'));

We can find the process id using either top (linux), topas -P (aix), or task manager (windows). Equally you can use Enterprise Manager to find this information, by looking under the performance monitor and any alerts regarding tuning / high CPU consumption.

To get more detail we can use below query-

SELECT    'USERNAME : ' || s.username  || CHR (10)   

        || 'SCHEMA : '  || s.schemaname || CHR (10) 

        || 'OSUSER : '  || s.osuser    || CHR (10)  

        || 'PROGRAM : ' || s.program   || CHR (10)  

        || 'MACHINE : ' || s.machine   || CHR (10)  

        || 'TERMINAL : ' || s.terminal  || CHR (10)  

        || 'SPID : '    || p.spid      || CHR (10)  

        || 'SID : '     || s.sid       || CHR (10)  

        || 'SERIAL# : ' || s.serial#   || CHR (10)  

        || 'TYPE : '    || s.TYPE      || CHR (10)  

        || 'SQL ID : '  || q.sql_id    || CHR (10)  

        || 'CHILD_NUMBER : '  || q.child_number    || CHR (10)  

        || 'SQL TEXT : ' || q.sql_text 

          RESULT

  FROM v$session s, v$process p, v$sql q

 WHERE s.paddr = p.addr AND s.sql_id = q.sql_id(+) AND p.spid = '&&MY_PID';

Drop all objects of a schema-

Pre-requisite-  (Very Important)

We have to login in particular schema of which we want to drop all objects. Otherwise it will drop all the objects of SYSTEM and other important table and views.

begin

for i in 1..3 loop

for r in (select object_name, object_type from user_objects

          order by object_type, object_name)

  loop

  begin

    if (r.object_type = 'MINING MODEL') then

     execute immediate ' begin dbms_data_mining.drop_model('||''''||

       r.object_name||''''||'); end;';

    elsif (r.object_type = 'TABLE') then

      execute immediate 'drop table "'||r.object_name

                    ||'" cascade constraints purge';

    else

       execute immediate 'drop '||r.object_type||' "'||r.object_name||'"';

    end if;

    exception when others then null;

  end;

  end loop;

end loop;

end;

/

What happens at backend when we fire alter database commit to switchover to physical standby-

Below Process will take place in background

1.) Notifies the primary database that a switchover is about to occur

2.) Disconnect all users from the primary database

3.) Generate a special redo record that signals the End of Redo (EOR)

4.) Converts the primary database into a standby database

5.) Once the standby database applies the final EOR record, guaranteeing that no data loss has been lost, converts the standby database into the primary database.

The new standby database (old primary) starts to receive the redo records and continues process until we switch back again. It is important to remember that both databases receive the EOR record so both databases know the next redo that will be received.

Steps to create ASM DISK and adding it to ASM DISKGROUP

Steps to create ASM DISK and adding it to ASM DISKGROUP

Step 1:

Login to the server host01 switch yourself to the root execute below commands.

/etc/init.d/oracleasm createdisk ASMDISK_VD046p1 '/dev/mapper/host1_VD046p1'
/etc/init.d/oracleasm createdisk ASMDISK_VD047p1 '/dev/mapper/host1_VD047p1'
/etc/init.d/oracleasm createdisk ASMDISK_VD048p1 '/dev/mapper/host1_VD048p1'


Step 2:

Login as grid

/etc/init.d/oracleasm listdisks


Step 3:

run scan as root
/etc/init.d/oracleasm scandisks

run as grid
/etc/init.d/oracleasm listdisks

Step 4:
Login to grid user setup ASM environment and login as “sys as sysasm” execute below command in SQL environment.

Sql> alter diskgroup DATA add disk 'ORCL:ASMDISK_VD046p1','ORCL:ASMDISK_VD046p1','ORCL:ASMDISK_VD046p1' rebalance power 10;

Step 5:

Sql>select *From v$asm_operation;

Note: Keep running until you see no rows.