Tag Archives: rac

Flush Shared pool & Buffer Cache in Oracle

Flush Shared pool & Buffer Cache in Oracle

Flush Shared pool means flushing the cached execution plan and SQL Queries from memory.
FLush buffer cache means flushing the cached data of objects from memory.
Both is like when we restart the oracle database and all memory is cleared.

Flushing the data buffer cache & Shared pool is not recommend on Production Environment.
It may lead to increase the performance overhead, especially on RAC databases.
Using the flush buffer cache was only for test system. Please don’t use on production environment. It lead to disk I/0 overhead.

Note: Cleared the shared pool and buffer cache without restart the database:

Clear all objects and items from shared pool and buffer cache by running following commands:

For single instances:

alter system flush buffer_cache;

alter system flush shared_pool;

For RAC Environment:

alter system flush buffer_cache global;

alter system flush shared_pool global;

Check the Buffer Cache cleared:

-- For all buffer cache is empty
select * from v$bh where status != 'free';

-- For particular file or block such as particular row: (RAC Environment)
select inst_id,file#,block#,status,dirty from gv$bh where file# = 1 and block# = 101736 and status != 'free' order by inst_id,status;

-- For Single instance for specific user
select o.OBJECT_TYPE, substr(o.OBJECT_NAME,1,10) objname , b.objd , b.status, count(b.objd) from v$bh b, dba_objects o where b.objd = o.data_object_id and o.owner = 'SCOTT' group by o.object_type, o.object_name,b.objd, b.status ;

Note: Find out in which file and block our table record is located:

select name,num ,dbms_rowid.rowid_relative_fno(rowid) fileno, dbms_rowid.rowid_block_number(rowid) block_no from t1 where name='RAM';

Advertisements

Starting and Stopping commands for RAC instances

Starting and Stopping commands for RAC instances

Starting and Stopping of RAC instance is managed from OS with srvctl command.
It provide different option at database , instance and service level to stop and start the database or instances present in RAC Environment.

Following are the command for Sart or Stop the database/instances:

Start or Stop the Instance of database:
command start/stop the Particular|all instances with services.

srvctl start/stop instance -db ORCL -node NODE1 -instance ORCL_1 -STARTOPTION|STOPOPTION
[OPEN | MOUNT | NOMOUNT | NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ]

Start or stop all instance of database with database
It start/stop the cluster database with enable all instances and services

srvctl start|stop database -db ORCL -startoption|stopoption
[OPEN | MOUNT | NOMOUNT | NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ]

Starting or stopping with SQLPLUS:
Note: you cannot start/stop multiple instance in SQLPLUS. Only current instance you can start or shutdown with sqlplus prompt:

sqlplus sys@orcl_1 as sysdba
shutdown immediate;
startup;

sqlplus sys@orcl_2 as sysdba
shutdown immediate;
startup;

Examples of start or stop with srvctl commands:

Check the status of instances in the database:

srvctl status database -db ORCL
Instance ORCL_1 is running on node NODE1
Instance ORCL_2 is running on node NODE2
Instance ORCL_3 is running on node NODE3

Stop the instance ORCL_1 with srvctl command

srvctl stop instance -db ORCL -node NODE1 -stopoption abort
OR
srvctl stop instance -db ORCL -instance ORCL_1 -stopption abort

Check the status of one instance is stopped in ORCL RAC:

srvctl status database -db ORCL
Instance ORCL_1 is not running on node NODE1
Instance ORCL_2 is running on node NODE2
Instance ORCL_3 is running on node NODE3

Stop all the instance then we used database option to stop:

srvctl stop database -db ORCL -stopoption transactional

Check the database status all instances must be stopped.

srvctl status database -db ORCL
Instance ORCL_1 is not running on node NODE1
Instance ORCL_2 is not running on node NODE2
Instance ORCL_3 is not running on node NODE3

Start the one instance of ORCL database:

srvctl start database -db ORCL -node NODE1
or
srvctl start database -instance ORCL_1

Check status node1 is started:

srvctl status database -db ORCL
Instance ORCL_1 is running on node NODE1
Instance ORCL_2 is not running on node NODE2
Instance ORCL_3 is not running on node NODE3

Start all the instances with database command:

srvctl start database -ORCL

Check status of all instances:

srvctl status database -db ORCL
Instance ORCL_1 is running on node NODE1
Instance ORCL_2 is running on node NODE2
Instance ORCL_3 is running on node NODE3

Manage the RAC pluggable database start/stop by services
Note: Need to assign the service to pluggable database for manage start or stop of PDB database.

1. Assign a service to pdb1 pluggable database :

srvctl add service -db raccdb -pdb pdb1 -service pdb1srv -serverpool sp1

2. For start or stop the pluggable database:

srvctl start service -db raccdb -service pdb1srv

srvctl stop service -db raccdb -service pdb1srv

3. Check the status for pluggable database:

srvctl status service -db raccdb -service pdb1arv

Parallel execution in RAC Environment

Parallel execution in RAC Environment

In RAC, a SQL statement is executed in parallel can run across all the nodes in the cluster environment.

Handle the parallel execution in RAC as follows

PARALLEL_FORCE_LOCAL parameter is true. with this parameter parallel execution happen only on the node where the SQL started execution.
Services: Parallel execution happen on only that instance which is the member of the services. Client connect direct to this service.
PARALLEL_INSTANCE_GROUP: It specify the service name or instance group where the parallel execution need to be happen. It can be set at session or system level.

Get the sql id for sql query which try to execute parallel as follows:

alter table tran_sales parallel 10;
select * from tran_sales order by tran_id;
-- Finding SQL Id for sql queries
select sql_id from v$sql where sql_fulltext like '%select * from tran_sales order by tran_id%' and sql_fulltext not like '%V$SQL%';

Check the parallel operation running on all nodes:

select INST_ID,USERNAME,OWNERID,PQ_STATUS from gv$session where sqlid = 'abcdefghi' order by inst_id;

Change the behaviour with parameter:

ALTER session set parallel_instance_group='SERVICE1';

SERVICE1 is used only one node as preferred server to run parallel execution in one node only.

Check all the service running in ORCL database:

srvctl status service -db orcl